#!/usr/bin/env python3 """ Detailed Margin Calculator - Syncs orders_items_margin_detailed table Replicates PHP MargincalculatorTask logic but syncs ALL order statuses Processes October 2025 data only """ import sys import pymysql import logging from datetime import datetime from decimal import Decimal from db_config import DB_CONFIG, USAUTO_SUPPLIERS, NOVEMBER_START, NOVEMBER_END # Setup logging logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler('/home/centralgoparts/public_html/profitability/sync_margins.log'), logging.StreamHandler(sys.stdout) ] ) logger = logging.getLogger(__name__) class MarginCalculator: def __init__(self): self.connections = {} self.reference_data = {} self.stats = { 'processed': 0, 'skipped': 0, 'errors': 0, 'total': 0 } def connect_db(self, db_name): """Create database connection""" try: if db_name not in self.connections: config = DB_CONFIG[db_name] self.connections[db_name] = pymysql.connect( host=config['host'], user=config['user'], password=config['password'], database=config['database'], charset=config['charset'], cursorclass=pymysql.cursors.DictCursor ) return self.connections[db_name] except Exception as e: logger.error(f"Failed to connect to {db_name}: {e}") raise def close_connections(self): """Close all database connections""" for conn in self.connections.values(): conn.close() def load_reference_data(self): """Load all reference data needed for calculations""" logger.info("Loading reference data...") conn = self.connect_db('central') cursor = conn.cursor() # Load loss rates cursor.execute("SELECT category_id, loss_rate_b2b, loss_rate_b2c FROM profitability_total_loss_rate") self.reference_data['loss_rates'] = {row['category_id']: row for row in cursor.fetchall()} logger.info(f"Loaded {len(self.reference_data['loss_rates'])} loss rates") # Load category return rates (latest date) cursor.execute(""" SELECT category, returnRate FROM profitability_return_rates WHERE date_placed = (SELECT MAX(date_placed) FROM profitability_return_rates) """) self.reference_data['return_rates'] = {row['category']: row['returnRate'] for row in cursor.fetchall()} logger.info(f"Loaded {len(self.reference_data['return_rates'])} return rates") # Load channel commissions (latest month) cursor.execute(""" SELECT p.channel, p.commission, p.fee FROM profitability_channel_commissions p INNER JOIN ( SELECT channel, MAX(month) AS latest_date FROM profitability_channel_commissions GROUP BY channel ) latest ON p.channel = latest.channel AND p.month = latest.latest_date """) self.reference_data['channel_commissions'] = { row['channel'].lower(): { 'commission': row['commission'], 'fee': row['fee'] } for row in cursor.fetchall() } logger.info(f"Loaded {len(self.reference_data['channel_commissions'])} channel commissions") # Load item statuses cursor.execute("SELECT status_id, status FROM items_status") self.reference_data['item_statuses'] = {row['status_id']: row['status'] for row in cursor.fetchall()} logger.info(f"Loaded {len(self.reference_data['item_statuses'])} item statuses") # Load suppliers cursor.execute("SELECT supplier_id, label FROM suppliers") self.reference_data['suppliers'] = {row['supplier_id']: row['label'] for row in cursor.fetchall()} logger.info(f"Loaded {len(self.reference_data['suppliers'])} suppliers") cursor.close() def get_items_to_process(self): """Fetch order items for November 2025 - ALL STATUSES""" conn = self.connect_db('central') cursor = conn.cursor() # Get ALL items from November (not filtering by status) # Join with orders to filter by date_placed query = """ SELECT oi.* FROM orders_items oi JOIN orders o ON oi.order_id = o.order_id WHERE o.date_placed >= %s AND o.date_placed <= %s ORDER BY oi.order_item_id DESC """ cursor.execute(query, (NOVEMBER_START, NOVEMBER_END)) items = cursor.fetchall() cursor.close() logger.info(f"Found {len(items)} items to process for November 2025") return items def check_item_processed(self, order_item_id): """Check if item already exists in orders_items_margin_detailed""" conn = self.connect_db('central') cursor = conn.cursor() cursor.execute( "SELECT 1 FROM orders_items_margin_detailed WHERE order_item_id = %s LIMIT 1", (order_item_id,) ) exists = cursor.fetchone() is not None cursor.close() return exists def get_order_details(self, order_id): """Fetch order information""" conn = self.connect_db('central') cursor = conn.cursor() cursor.execute("SELECT * FROM orders WHERE order_id = %s", (order_id,)) order = cursor.fetchone() cursor.close() return order def get_sales_channel(self, order): """Determine sales channel from order source""" if order['source'] == 500: return "B2B" elif order['source'] == 1: return "Web" elif order['source'] == 200: # Check ALL notes for Amazon/eBay (not just first one) conn = self.connect_db('central') cursor = conn.cursor() # First try optimized query - search for Amazon/eBay in notes cursor.execute(""" SELECT note FROM notes WHERE order_id = %s AND (LOWER(note) LIKE %s OR LOWER(note) LIKE %s) LIMIT 1 """, (order['order_id'], '%amazon%', '%ebay%')) note_row = cursor.fetchone() if note_row: note = note_row['note'].lower() if 'amazon' in note: cursor.close() return "Amazon" elif 'ebay' in note: cursor.close() return "eBay" cursor.close() return "" def get_product(self, product_id, part_num): """Fetch product information""" conn = self.connect_db('central') cursor = conn.cursor() if product_id: cursor.execute("SELECT * FROM products WHERE product_id = %s", (product_id,)) elif part_num: cursor.execute("SELECT * FROM products WHERE partslink = %s AND sku NOT LIKE '%%OGP%%' LIMIT 1", (part_num,)) else: cursor.close() return None product = cursor.fetchone() cursor.close() return product def get_category_name(self, category_id): """Get category name from category_id""" if not category_id: return None conn = self.connect_db('central') cursor = conn.cursor() cursor.execute("SELECT category FROM categories WHERE category_id = %s", (category_id,)) result = cursor.fetchone() cursor.close() return result['category'] if result else None def get_order_item_count(self, order_id): """Get count of items in order""" conn = self.connect_db('central') cursor = conn.cursor() cursor.execute("SELECT COUNT(*) as count FROM orders_items WHERE order_id = %s", (order_id,)) result = cursor.fetchone() cursor.close() return result['count'] def get_snapshot_prices(self, item_id, supplier_id): """Get recorded buy prices from independent_buy_price_recording""" conn = self.connect_db('central') cursor = conn.cursor() cursor.execute(""" SELECT buy_price, shipping, handling FROM independent_buy_price_recording WHERE item_id = %s AND supplier_id = %s ORDER BY buy_price ASC LIMIT 1 """, (item_id, supplier_id)) snapshot = cursor.fetchone() cursor.close() return snapshot def get_part_supplier_data(self, supplier_id, product_id, partslink): """Get part supplier pricing data""" conn = self.connect_db('central') cursor = conn.cursor() # First try to get partslink from product if product_id: cursor.execute("SELECT partslink FROM products WHERE product_id = %s", (product_id,)) prod = cursor.fetchone() if prod: partslink = prod['partslink'] if not partslink: cursor.close() return None # Get part supplier with best price cursor.execute(""" SELECT * FROM parts_suppliers WHERE supplier_id = %s AND partslink = %s AND qty > 0 AND in_stock = 1 ORDER BY price ASC LIMIT 1 """, (supplier_id, partslink)) part_supplier = cursor.fetchone() cursor.close() return part_supplier def get_item_credits(self, item_id, credit_type): """Get supplier or carrier credits""" conn = self.connect_db('central') cursor = conn.cursor() cursor.execute(""" SELECT cost FROM orders_additional_costs WHERE item_id = %s AND cost_type = %s """, (item_id, credit_type)) result = cursor.fetchone() cursor.close() if result: cost_str = str(result['cost']).replace('$', '') try: return Decimal(cost_str) except: return Decimal('0') return Decimal('0') def get_label_cost(self, order_id): """Get shipping label cost from data warehouse""" try: conn = self.connect_db('data_warehouse') cursor = conn.cursor() cursor.execute(""" SELECT COALESCE(SUM(cost), 0) as label_cost FROM amazon_fees_index WHERE gp_order_id = %s AND type = 'Shipping Services' """, (order_id,)) result = cursor.fetchone() cursor.close() return Decimal(str(result['label_cost'])) if result else Decimal('0') except Exception as e: logger.warning(f"Could not get label cost for order {order_id}: {e}") return Decimal('0') def get_discount(self, product_id, date_placed): """Get B2C discount for product on specific date""" conn = self.connect_db('central') cursor = conn.cursor() date_str = date_placed.strftime('%Y-%m-%d') cursor.execute(""" SELECT discount_type, discount_percentage FROM price_discount_history WHERE product_id = %s AND date_added LIKE %s ORDER BY date_added DESC LIMIT 1 """, (product_id, f"{date_str}%")) discount = cursor.fetchone() cursor.close() return discount def get_b2b_discount(self, item, date_placed): """Get B2B discount for item on specific date""" try: # Get product to find partslink product = self.get_product(item['product_id'], item['part_num']) if not product: return None partslink = product['partslink'] # Get B2B product IDs conn_b2b = self.connect_db('b2b') cursor_b2b = conn_b2b.cursor() cursor_b2b.execute("SELECT productID FROM b2b_products WHERE partNumber = %s", (partslink,)) product_ids = [row['productID'] for row in cursor_b2b.fetchall()] cursor_b2b.close() if not product_ids: return None # Get discount from history conn = self.connect_db('central') cursor = conn.cursor() date_str = date_placed.strftime('%Y-%m-%d') placeholders = ','.join(['%s'] * len(product_ids)) query = f""" SELECT discount_type, discount_percentage FROM b2b_price_discount_history WHERE product_id IN ({placeholders}) AND date_added LIKE %s ORDER BY date_added DESC LIMIT 1 """ cursor.execute(query, (*product_ids, f"{date_str}%")) discount = cursor.fetchone() cursor.close() return discount except Exception as e: logger.warning(f"Error getting B2B discount: {e}") return None def get_partslink(self, product_id, part_num): """Get partslink from product""" product = self.get_product(product_id, part_num) return product['partslink'] if product else None def safe_decimal(self, value, default='0'): """Safely convert value to Decimal, handling None and empty strings""" if value is None or value == '': return Decimal(default) return Decimal(str(value)) def calculate_margin(self, item): """Main calculation logic for a single item""" try: # Check if already processed if self.check_item_processed(item['order_item_id']): logger.debug(f"Item {item['order_item_id']} already processed") self.stats['skipped'] += 1 return False # Get order details order = self.get_order_details(item['order_id']) if not order: logger.warning(f"Order not found for item {item['order_item_id']}") self.stats['errors'] += 1 return False # Determine sales channel sales_channel = self.get_sales_channel(order) if not sales_channel: logger.warning(f"Sales channel not found for order {order['order_id']}") self.stats['skipped'] += 1 return False # Skip PS orders if order.get('ps_order_id') or \ (item.get('custom_data') and 'sku' in item['custom_data']) or \ (item.get('ordered_brand') and item['ordered_brand'].startswith('PS-')): logger.info(f"Skipping PS order item {item['order_item_id']}") self.stats['skipped'] += 1 return False # Get product product = self.get_product(item.get('product_id'), item.get('part_num')) # Initialize margin record margin = { 'order_id': order['order_id'], 'external_order_id': order.get('external_order_id'), 'date_placed': order['date_placed'], 'billing_name': f"{order.get('billing_first_name', '')} {order.get('billing_last_name', '')}".strip(), 'billing_address': f"{order.get('billing_street_address', '')}, {order.get('billing_city', '')} {order.get('billing_state', '')} {order.get('billing_country', '')}, {order.get('billing_zip', '')}".strip(), 'order_item_id': item['order_item_id'], 'item_sale_price': self.safe_decimal(item.get('sale_price')), 'sales_channel': sales_channel, 'category': self.get_category_name(product['category_id']) if product else None, 'return_rate': Decimal('0'), 'item_name': product['name'] if product else 'Unknown', 'sku': product['sku'] if product else None, 'item_status': self.reference_data['item_statuses'].get(item['current_status'], 'Unknown'), 'current_status': item['current_status'], 'is_active': 1 if item['current_status'] == 6 else 0, 'our_shipping_source': 'Snapshot', 'buy_price_source': 'Snapshot', 'category_discount_applied': 0, 'created_at': datetime.now(), 'fee_data_source': 'Estimated', 'modified_at': datetime.now(), 'additional_cost': Decimal('0'), 'amazon_additional_cost': Decimal('0'), 'commission_and_fee': Decimal('0'), 'good_sold_cost': Decimal('0'), 'payment_processor_fee': Decimal('0'), 'repayment_processor_fees': Decimal('0') } # Process based on whether supplier_id is set if item.get('supplier_id'): success = self.process_with_supplier(item, order, product, margin, sales_channel) else: success = self.process_without_supplier(item, order, product, margin) if success: self.save_margin(margin) self.stats['processed'] += 1 return True else: self.stats['skipped'] += 1 return False except Exception as e: logger.error(f"Error processing item {item.get('order_item_id')}: {e}", exc_info=True) self.stats['errors'] += 1 return False def process_with_supplier(self, item, order, product, margin, sales_channel): """Process item WITH supplier_id (PATH A)""" supplier_id = item['supplier_id'] margin['supplier_id'] = supplier_id margin['supplier'] = self.reference_data['suppliers'].get(supplier_id, 'Unknown') # Get channel commissions channel_key = sales_channel.lower() if channel_key in self.reference_data['channel_commissions']: margin['channel_commission'] = self.reference_data['channel_commissions'][channel_key]['commission'] margin['channel_advertisement_fee'] = self.reference_data['channel_commissions'][channel_key]['fee'] margin['channel_advertisement_fee_amount'] = self.reference_data['channel_commissions'][channel_key]['fee'] else: margin['channel_commission'] = Decimal('0') margin['channel_advertisement_fee'] = Decimal('0') margin['channel_advertisement_fee_amount'] = Decimal('0') # Calculate shipping per item order_item_count = self.get_order_item_count(order['order_id']) margin['item_shipping'] = self.safe_decimal(order.get('shipping')) / Decimal(str(order_item_count)) if order_item_count > 0 else Decimal('0') # Calculate sales price margin['sales_price'] = margin['item_sale_price'] + margin['item_shipping'] if margin['sales_price'] == 0: margin['remarks'] = "Sale Price is 0." return True # Save but mark as skipped # Get discounts if sales_channel == "B2B": discount = self.get_b2b_discount(item, order['date_placed']) else: discount = self.get_discount(product['product_id'], order['date_placed']) if product else None if discount: margin['category_discount_applied'] = 1 margin['category_discount_type'] = discount['discount_type'] margin['category_discount_percentage'] = discount['discount_percentage'] # Get credits margin['supplier_credits'] = self.get_item_credits(item['order_item_id'], 'Supplier Credit') margin['carrier_credits'] = self.get_item_credits(item['order_item_id'], 'Carrier Credit') # Check category if not margin['category']: margin['remarks'] = "Item Category not found" return True # Save but mark as skipped # Get return rate if margin['category'] in self.reference_data['return_rates']: margin['return_rate'] = self.reference_data['return_rates'][margin['category']] # Get buy price - try snapshot first snapshot = self.get_snapshot_prices(item['order_item_id'], supplier_id) if snapshot: if supplier_id in USAUTO_SUPPLIERS: margin['usauto_rebate'] = self.safe_decimal(snapshot['buy_price']) * Decimal('0.04') margin['supplier_price'] = self.safe_decimal(snapshot['buy_price']) margin['supplier_shipping'] = self.safe_decimal(snapshot['shipping']) margin['supplier_handling'] = self.safe_decimal(snapshot['handling']) margin['our_buy_price'] = margin['supplier_price'] + margin['supplier_shipping'] + margin['supplier_handling'] - margin['usauto_rebate'] else: margin['usauto_rebate'] = Decimal('0') margin['supplier_price'] = self.safe_decimal(snapshot['buy_price']) margin['supplier_shipping'] = self.safe_decimal(snapshot['shipping']) margin['supplier_handling'] = self.safe_decimal(snapshot['handling']) margin['our_buy_price'] = margin['supplier_price'] + margin['supplier_shipping'] + margin['supplier_handling'] else: # Fallback to part supplier part_supplier = self.get_part_supplier_data(supplier_id, item.get('product_id'), item.get('part_num')) if part_supplier: if supplier_id in USAUTO_SUPPLIERS: margin['usauto_rebate'] = self.safe_decimal(part_supplier['price']) * Decimal('0.04') margin['supplier_price'] = self.safe_decimal(part_supplier['price']) margin['supplier_shipping'] = self.safe_decimal(part_supplier.get('shipping')) margin['supplier_handling'] = self.safe_decimal(part_supplier.get('handling')) margin['our_buy_price'] = margin['supplier_price'] + margin['supplier_shipping'] + margin['supplier_handling'] - margin['usauto_rebate'] else: margin['usauto_rebate'] = Decimal('0') margin['supplier_price'] = self.safe_decimal(part_supplier['price']) margin['supplier_shipping'] = self.safe_decimal(part_supplier.get('shipping')) margin['supplier_handling'] = self.safe_decimal(part_supplier.get('handling')) margin['our_buy_price'] = margin['supplier_price'] + margin['supplier_shipping'] + margin['supplier_handling'] margin['supplier_partNumber'] = part_supplier.get('supplier_partnumber') else: margin['our_buy_price'] = Decimal('0') if margin.get('our_buy_price', 0) == 0: margin['remarks'] = "Our buy price is 0." return True # Save but mark as skipped # Get label cost margin['label_cost'] = self.get_label_cost(order['order_id']) # Get loss rate if product and product['category_id'] in self.reference_data['loss_rates']: loss_rate_data = self.reference_data['loss_rates'][product['category_id']] if sales_channel == "B2B": margin['loss_rate'] = loss_rate_data['loss_rate_b2b'] else: margin['loss_rate'] = loss_rate_data['loss_rate_b2c'] else: margin['loss_rate'] = Decimal('0') # Calculate gross profit/margin sales_price = Decimal(str(margin['sales_price'])) our_buy_price = Decimal(str(margin.get('our_buy_price', 0))) channel_commission = Decimal(str(margin.get('channel_commission', 0))) channel_ad_fee = Decimal(str(margin.get('channel_advertisement_fee', 0))) loss_rate = Decimal(str(margin.get('loss_rate', 0))) gross_profit_calc = (sales_price - our_buy_price - (sales_price * (channel_commission/Decimal('100') + channel_ad_fee/Decimal('100') + loss_rate/Decimal('100')))) / sales_price margin['gross_profit'] = gross_profit_calc * Decimal('100') margin['gross_margin'] = sales_price * gross_profit_calc # Get partslink margin['partslink'] = self.get_partslink(item.get('product_id'), item.get('part_num')) return True def process_without_supplier(self, item, order, product, margin): """Process item WITHOUT supplier_id (PATH B)""" # Map wc_supplier to supplier_id (for B2B Local orders) wc_supplier = item.get('wc_supplier', '') if wc_supplier == 'GALTL02': supplier_id = 73 elif wc_supplier == 'GALTL03': supplier_id = 1 elif wc_supplier == 'GALTL04': supplier_id = 3 else: # No wc_supplier mapping - these are typically "Needs Order" status items # Save with NULL supplier_id (intended behavior) supplier_id = None logger.info(f"Item {item['order_item_id']} has no supplier_id (likely Needs Order status)") margin['supplier_id'] = supplier_id margin['supplier'] = self.reference_data['suppliers'].get(supplier_id, 'Unknown') if supplier_id else None # No commissions or fees for warehouse orders margin['channel_commission'] = Decimal('0') margin['channel_advertisement_fee'] = Decimal('0') margin['channel_advertisement_fee_amount'] = Decimal('0') # No shipping for warehouse orders margin['item_shipping'] = Decimal('0') margin['sales_price'] = margin['item_sale_price'] if margin['sales_price'] == 0: margin['remarks'] = "Sale Price is 0." return True margin['category_discount_applied'] = 0 margin['category_discount_percentage'] = Decimal('0') margin['supplier_credits'] = Decimal('0') margin['carrier_credits'] = Decimal('0') margin['return_rate'] = Decimal('0') # Get buy price (only if supplier_id is not NULL) if supplier_id: snapshot = self.get_snapshot_prices(item['order_item_id'], supplier_id) if snapshot: margin['usauto_rebate'] = Decimal('0') margin['supplier_price'] = self.safe_decimal(snapshot['buy_price']) margin['supplier_shipping'] = Decimal('0') margin['supplier_handling'] = Decimal('0') margin['our_buy_price'] = margin['supplier_price'] else: part_supplier = self.get_part_supplier_data(supplier_id, item.get('product_id'), item.get('part_num')) if part_supplier: margin['usauto_rebate'] = Decimal('0') margin['supplier_price'] = self.safe_decimal(part_supplier['price']) margin['supplier_shipping'] = Decimal('0') margin['supplier_handling'] = Decimal('0') margin['our_buy_price'] = margin['supplier_price'] margin['supplier_partNumber'] = part_supplier.get('supplier_partnumber') else: margin['our_buy_price'] = Decimal('0') else: # No supplier_id - set buy price to 0 (Needs Order status) margin['usauto_rebate'] = Decimal('0') margin['supplier_price'] = Decimal('0') margin['supplier_shipping'] = Decimal('0') margin['supplier_handling'] = Decimal('0') margin['our_buy_price'] = Decimal('0') if margin.get('our_buy_price', 0) == 0: margin['remarks'] = "Our buy price is 0 (no supplier yet)." return True margin['label_cost'] = Decimal('0') margin['loss_rate'] = Decimal('0') # Calculate margin (FIXED - not subtracting sales_price twice like the bug in PHP) sales_price = Decimal(str(margin['sales_price'])) our_buy_price = Decimal(str(margin.get('our_buy_price', 0))) gross_profit_calc = (sales_price - our_buy_price) / sales_price margin['gross_profit'] = gross_profit_calc * Decimal('100') margin['gross_margin'] = sales_price * gross_profit_calc margin['partslink'] = self.get_partslink(item.get('product_id'), item.get('part_num')) return True def save_margin(self, margin): """Save margin record to orders_items_margin_detailed""" conn = self.connect_db('central') cursor = conn.cursor() # Build insert query fields = [ 'order_id', 'external_order_id', 'order_item_id', 'sku', 'item_status', 'current_status', 'item_sale_price', 'item_shipping', 'category', 'return_rate', 'loss_rate', 'sales_price', 'sales_channel', 'channel_commission', 'channel_advertisement_fee', 'channel_advertisement_fee_amount', 'supplier_id', 'supplier', 'supplier_partNumber', 'supplier_with_own_stock', 'supplier_price', 'supplier_shipping', 'supplier_handling', 'our_buy_price', 'gross_profit', 'gross_margin', 'remarks', 'fee_data_source', 'partslink', 'billing_name', 'billing_address', 'date_placed', 'item_name', 'label_cost', 'additional_cost', 'amazon_additional_cost', 'commission_and_fee', 'good_sold_cost', 'created_at', 'modified_at', 'usauto_rebate', 'buy_price_source', 'our_shipping_source', 'supplier_credits', 'carrier_credits', 'category_discount_applied', 'category_discount_type', 'category_discount_percentage', 'payment_processor_fee', 'repayment_processor_fees', 'is_active' ] placeholders = ', '.join(['%s'] * len(fields)) field_names = ', '.join(fields) query = f"INSERT INTO orders_items_margin_detailed ({field_names}) VALUES ({placeholders})" values = tuple(margin.get(field) for field in fields) try: cursor.execute(query, values) conn.commit() except Exception as e: logger.error(f"Failed to save margin for item {margin['order_item_id']}: {e}") conn.rollback() raise finally: cursor.close() def sync_status_and_supplier(self): """ Sync supplier_id and current_status from orders_items table for the last 180 days of orders in orders_items_margin_detailed """ from datetime import timedelta start_time = datetime.now() days_back = 180 cutoff_date = start_time - timedelta(days=days_back) logger.info("=" * 60) logger.info("Starting Status & Supplier Sync") logger.info(f"Syncing last {days_back} days (from {cutoff_date.strftime('%Y-%m-%d')})") logger.info("=" * 60) conn = self.connect_db('central') cursor = conn.cursor() try: # Update supplier_id, current_status, and item_status from orders_items for last 180 days update_query = """ UPDATE orders_items_margin_detailed oimd JOIN orders_items oi ON oimd.order_item_id = oi.order_item_id JOIN orders o ON oi.order_id = o.order_id LEFT JOIN items_status ist ON oi.current_status = ist.status_id SET oimd.supplier_id = oi.supplier_id, oimd.current_status = oi.current_status, oimd.item_status = COALESCE(ist.status, oi.current_status) WHERE o.date_placed >= %s """ cursor.execute(update_query, (cutoff_date,)) rows_updated = cursor.rowcount conn.commit() elapsed = (datetime.now() - start_time).total_seconds() logger.info("=" * 60) logger.info("Completed Status & Supplier Sync") logger.info(f"Summary:") logger.info(f" Records Updated: {rows_updated}") logger.info(f" Execution Time: {elapsed:.2f}s") logger.info("=" * 60) return rows_updated except Exception as e: logger.error(f"Error during sync: {e}", exc_info=True) conn.rollback() raise finally: cursor.close() def run(self): """Main execution method""" start_time = datetime.now() logger.info("=" * 60) logger.info("Starting Detailed Margin Calculator") logger.info(f"Processing November 2025 data (ALL order statuses)") logger.info("=" * 60) try: # Load reference data self.load_reference_data() # Get items to process items = self.get_items_to_process() self.stats['total'] = len(items) # Process each item for idx, item in enumerate(items, 1): if idx % 100 == 0: logger.info(f"Progress: {idx}/{self.stats['total']} items processed") self.calculate_margin(item) # Print summary elapsed = (datetime.now() - start_time).total_seconds() logger.info("=" * 60) logger.info("Completed Detailed Margin Calculator") logger.info(f"Summary:") logger.info(f" Total Items: {self.stats['total']}") logger.info(f" Processed: {self.stats['processed']}") logger.info(f" Skipped: {self.stats['skipped']}") logger.info(f" Errors: {self.stats['errors']}") logger.info(f" Execution Time: {elapsed:.2f}s") logger.info("=" * 60) except Exception as e: logger.error(f"Critical error in main execution: {e}", exc_info=True) raise finally: self.close_connections() if __name__ == '__main__': import argparse parser = argparse.ArgumentParser(description='Detailed Margin Calculator') parser.add_argument('--sync-status', action='store_true', help='Sync supplier_id and item_status for last 180 days') parser.add_argument('--full-sync', action='store_true', help='Run full margin calculation (default)') args = parser.parse_args() calculator = MarginCalculator() # If no arguments, run full sync (default behavior) if not args.sync_status and not args.full_sync: args.full_sync = True # Run requested operations if args.full_sync: calculator.run() if args.sync_status: calculator.sync_status_and_supplier()