#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Smart Price Update Service für Intelectra WebShop Verhindert 50+ REPLACE INTO Abfragen pro Preisänderung! """ import json import sys import pymysql import logging from configparser import ConfigParser import os from datetime import datetime from decimal import Decimal # Logging logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler('/var/www/vhosts/intelectra.de/httpdocs/logs/price_update_service.log'), logging.StreamHandler() ] ) logger = logging.getLogger('price_update_service') class PriceUpdateService: """Intelligenter Price-Update Service mit Change Detection""" def __init__(self): self.conn = self._connect_db() self.cursor = self.conn.cursor(pymysql.cursors.DictCursor) def _connect_db(self): """DB-Connection aus config.ini""" config_file = os.path.join(os.path.dirname(__file__), 'db_config.ini') config = ConfigParser() config.read(config_file) return pymysql.connect( host=config.get('database', 'host', fallback='localhost'), user=config.get('database', 'user', fallback='tbapy'), password=config.get('database', 'password', fallback='9%%0H32ryj_N9%%0H32ryj'), database=config.get('database', 'database', fallback='webshop-sql'), charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) def update_prices_smart(self, item_id, price_data): """ Smart Price Update - NUR Änderungen! Verhindert 50+ REPLACE INTO Abfragen """ try: changes_made = False total_changes = 0 # 1. Aktuelle Preise laden self.cursor.execute( "SELECT customergroup_id, price_1, price_2, price_3, quantity_2, quantity_3, bargain_price_1, bargain_price_2, bargain_price_3 FROM item_prices WHERE item_id = %s", (item_id,) ) current_prices = {row['customergroup_id']: row for row in self.cursor.fetchall()} logger.info(f"Loaded {len(current_prices)} existing price records for item {item_id}") # 2. Preisänderungen verarbeiten for group_id, group_prices in price_data.items(): if not group_id.startswith('group_'): continue customer_group_id = int(group_id.replace('group_', '')) current_price = current_prices.get(customer_group_id, {}) # 3. Change Detection für diese Kundengruppe needs_update = self._price_changed(current_price, group_prices) if needs_update: if current_price: # UPDATE bestehenden Record self._update_existing_price(item_id, customer_group_id, group_prices) logger.info(f"Updated prices for group {customer_group_id}") else: # INSERT neuen Record self._insert_new_price(item_id, customer_group_id, group_prices) logger.info(f"Inserted new prices for group {customer_group_id}") changes_made = True total_changes += 1 else: logger.debug(f"No price changes for group {customer_group_id}") self.conn.commit() return { 'success': True, 'item_id': item_id, 'changes_made': changes_made, 'groups_updated': total_changes, 'message': f'Updated {total_changes} price groups' if changes_made else 'No price changes detected' } except Exception as e: self.conn.rollback() logger.error(f"Error updating prices for item {item_id}: {str(e)}") return { 'success': False, 'error': str(e), 'item_id': item_id } def _safe_float(self, value): """Konvertiert Wert sicher zu float - behandelt leere Strings und None""" if value is None or value == '' or value == 'null': return 0.0 try: return float(value) except (ValueError, TypeError): return 0.0 def _price_changed(self, current_price, new_prices): """Erkennt ob sich Preise geändert haben""" if not current_price: # Neue Kundengruppe return True # Alle relevanten Preisfelder prüfen price_fields = [ 'price_1', 'price_2', 'price_3', 'quantity_2', 'quantity_3', 'bargain_price_1', 'bargain_price_2', 'bargain_price_3' ] for field in price_fields: if field in new_prices: current_val = self._safe_float(current_price.get(field, 0)) new_val = self._safe_float(new_prices[field]) # Dezimal-Vergleich mit Toleranz (0.01) if abs(current_val - new_val) > 0.001: logger.debug(f"Price change detected in {field}: {current_val} -> {new_val}") return True return False def _update_existing_price(self, item_id, customer_group_id, group_prices): """Update bestehenden Preis-Record""" set_clauses = [] values = [] price_fields = [ 'price_1', 'price_2', 'price_3', 'quantity_2', 'quantity_3', 'bargain_price_1', 'bargain_price_2', 'bargain_price_3' ] for field in price_fields: if field in group_prices: set_clauses.append(f"{field} = %s") values.append(self._safe_float(group_prices[field])) if set_clauses: values.extend([item_id, customer_group_id]) sql = f"UPDATE item_prices SET {', '.join(set_clauses)} WHERE item_id = %s AND customergroup_id = %s" self.cursor.execute(sql, values) def _insert_new_price(self, item_id, customer_group_id, group_prices): """Insert neuen Preis-Record""" fields = ['item_id', 'customergroup_id'] values = [item_id, customer_group_id] price_fields = [ 'price_1', 'price_2', 'price_3', 'quantity_2', 'quantity_3', 'bargain_price_1', 'bargain_price_2', 'bargain_price_3' ] for field in price_fields: if field in group_prices: fields.append(field) values.append(self._safe_float(group_prices[field])) placeholders = ', '.join(['%s'] * len(values)) sql = f"INSERT INTO item_prices ({', '.join(fields)}) VALUES ({placeholders})" self.cursor.execute(sql, values) def get_price_statistics(self, item_id): """Debug: Preisstatistiken für ein Item""" try: self.cursor.execute(""" SELECT COUNT(*) as total_groups, MIN(price_1) as min_price, MAX(price_1) as max_price, AVG(price_1) as avg_price FROM item_prices WHERE item_id = %s AND price_1 > 0 """, (item_id,)) stats = self.cursor.fetchone() return { 'success': True, 'item_id': item_id, 'statistics': stats } except Exception as e: return { 'success': False, 'error': str(e) } def close(self): """Cleanup""" if self.cursor: self.cursor.close() if self.conn: self.conn.close() def main(): """CLI Entry Point für PHP shell_exec()""" if len(sys.argv) < 2: print(json.dumps({'error': 'No data provided'})) sys.exit(1) try: # Parse input data = json.loads(sys.argv[1]) item_id = data.get('item_id') action = data.get('action', 'update_prices') service = PriceUpdateService() if action == 'update_prices': price_data = data.get('prices', {}) result = service.update_prices_smart(item_id, price_data) elif action == 'get_statistics': result = service.get_price_statistics(item_id) else: result = {'error': f'Unknown action: {action}'} service.close() # Return JSON print(json.dumps(result)) except Exception as e: logger.error(f"Main error: {str(e)}") print(json.dumps({'error': str(e)})) sys.exit(1) if __name__ == '__main__': main()