import requests from decimal import getcontext, Decimal, ROUND_HALF_UP from datetime import datetime, timedelta from trytond.model import fields from trytond.model import ModelSQL, ModelView from trytond.pool import Pool, PoolMeta from trytond.transaction import Transaction import logging from sql import Table import traceback logger = logging.getLogger(__name__) class Cron(metaclass=PoolMeta): __name__ = 'ir.cron' @classmethod def __setup__(cls): super().__setup__() cls.method.selection.append( ('automation.cron|update_shipment', "Update Shipment from freight booking info") ) class AutomationCron(ModelSQL, ModelView): "Automation Cron" __name__ = 'automation.cron' frequency = fields.Selection([ ('daily', "Daily"), ('weekly', "Weekly"), ('monthly', "Monthly"), ], "Frequency", required=True, help="How frequently rates must be updated.") last_update = fields.Date("Last Update", required=True) @classmethod def run(cls, crons): cls.update_shipment() @classmethod def update_shipment(cls): PoolObj = Pool() ShipmentIn = PoolObj.get('stock.shipment.in') Party = PoolObj.get('party.party') Vessel = PoolObj.get('trade.vessel') Location = PoolObj.get('stock.location') # Table externe t = Table('freight_booking_info') cursor = Transaction().connection.cursor() cursor.execute(*t.select( t.ShippingInstructionNumber, t.ShippingInstructionDate, t.ShippingInstructionQuantity, t.ShippingInstructionQuantityUnit, t.NumberOfContainers, t.ContainerType, t.Loading, t.Destination, t.BookingAgent, t.Carrier, t.Vessel, t.BL_Number, t.ETD_Date, t.BL_Date, t.ExpectedController, t.Comments, t.FintradeBookingKey, )) rows = cursor.fetchall() logger.info(f"Nombre total de lignes à traiter : {len(rows)}") # ---- PREMIÈRE TRANSACTION : Création des objets de référence ---- with Transaction().new_transaction() as trans1: try: logger.info("Début de la création des objets de référence...") parties_to_save = [] vessels_to_save = [] locations_to_save = [] parties_cache = {} vessels_cache = {} locations_cache = {} # Collecter les données des objets de référence for row in rows: ( si_number, si_date, si_quantity, si_unit, container_number, container_type, loading_name, destination_name, agent_name, carrier_name, vessel_name, bl_number, etd_date, bl_date, controller, comments, fintrade_booking_key ) = row # Fonction pour obtenir ou créer un Party def get_or_create_party(name): if not name: return None name_upper = str(name).strip().upper() if name_upper in parties_cache: return parties_cache[name_upper] # Chercher d'abord dans la base existing = Party.search([('name', '=', name_upper)], limit=1) if existing: parties_cache[name_upper] = existing[0] return existing[0] # Créer un nouveau new_p = Party() new_p.name = name_upper parties_cache[name_upper] = new_p parties_to_save.append(new_p) return new_p # Fonction pour obtenir ou créer un Vessel def get_or_create_vessel(name): if not name: return None name_upper = str(name).strip().upper() if name_upper in vessels_cache: return vessels_cache[name_upper] existing = Vessel.search([('vessel_name', '=', name_upper)], limit=1) if existing: vessels_cache[name_upper] = existing[0] return existing[0] new_v = Vessel() new_v.vessel_name = name_upper vessels_cache[name_upper] = new_v vessels_to_save.append(new_v) return new_v # Fonction pour obtenir ou créer une Location def get_or_create_location(name, type_): if not name: return None name_upper = str(name).strip().upper() key = f"{name_upper}_{type_}" if key in locations_cache: return locations_cache[key] existing = Location.search([ ('name', '=', name_upper), ('type', '=', type_) ], limit=1) if existing: locations_cache[key] = existing[0] return existing[0] new_loc = Location() new_loc.name = name_upper new_loc.type = type_ locations_cache[key] = new_loc locations_to_save.append(new_loc) return new_loc # Collecter les objets à créer _ = get_or_create_party(carrier_name) _ = get_or_create_party(agent_name) _ = get_or_create_vessel(vessel_name) _ = get_or_create_location(loading_name, 'supplier') _ = get_or_create_location(destination_name, 'customer') # Sauvegarder tous les objets de référence if parties_to_save: logger.info(f"Création de {len(parties_to_save)} parties...") Party.save(parties_to_save) if vessels_to_save: logger.info(f"Création de {len(vessels_to_save)} vessels...") Vessel.save(vessels_to_save) if locations_to_save: logger.info(f"Création de {len(locations_to_save)} locations...") Location.save(locations_to_save) trans1.commit() logger.info("Première transaction commitée : objets de référence créés") except Exception as e: trans1.rollback() logger.error(f"Erreur dans la création des objets de référence : {e}") logger.error(traceback.format_exc()) raise # ---- TRANSACTIONS INDIVIDUELLES pour chaque shipment ---- successful_shipments = 0 failed_shipments = [] # Recréer le curseur après la nouvelle transaction cursor2 = Transaction().connection.cursor() cursor2.execute(*t.select( t.ShippingInstructionNumber, t.ShippingInstructionDate, t.ShippingInstructionQuantity, t.ShippingInstructionQuantityUnit, t.NumberOfContainers, t.ContainerType, t.Loading, t.Destination, t.BookingAgent, t.Carrier, t.Vessel, t.BL_Number, t.ETD_Date, t.BL_Date, t.ExpectedController, t.Comments, t.FintradeBookingKey, )) rows2 = cursor2.fetchall() for i, row in enumerate(rows2, 1): ( si_number, si_date, si_quantity, si_unit, container_number, container_type, loading_name, destination_name, agent_name, carrier_name, vessel_name, bl_number, etd_date, bl_date, controller, comments, fintrade_booking_key ) = row logger.info(f"Traitement shipment {i}/{len(rows2)} : SI {si_number}") # ---- TRANSACTION INDIVIDUELLE pour ce shipment ---- try: with Transaction().new_transaction() as trans_shipment: logger.info(f"Début transaction pour SI {si_number}") # Vérifier si le shipment existe déjà existing_shipment = ShipmentIn.search([ ('reference', '=', si_number) ], limit=1) if existing_shipment: logger.info(f"Shipment {si_number} existe déjà, ignoré") trans_shipment.commit() continue # Récupérer les objets (maintenant ils existent dans la base) carrier = None if carrier_name: carrier_list = Party.search([('name', '=', str(carrier_name).strip().upper())], limit=1) if carrier_list: carrier = carrier_list[0] logger.info(f"Carrier trouvé pour {si_number}: {carrier.name}") else: logger.warning(f"Carrier NON TROUVÉ pour {si_number}: '{carrier_name}'") agent = None agent_list = Party.search([('name', '=', str(agent_name or 'TBN').strip().upper())], limit=1) if agent_list: agent = agent_list[0] vessel = None if vessel_name: vessel_list = Vessel.search([('vessel_name', '=', str(vessel_name).strip().upper())], limit=1) if vessel_list: vessel = vessel_list[0] loc_from = None if loading_name: loc_from_list = Location.search([ ('name', '=', str(loading_name).strip().upper()), ('type', '=', 'supplier') ], limit=1) if loc_from_list: loc_from = loc_from_list[0] loc_to = None if destination_name: loc_to_list = Location.search([ ('name', '=', str(destination_name).strip().upper()), ('type', '=', 'customer') ], limit=1) if loc_to_list: loc_to = loc_to_list[0] # Vérification critique du carrier if not carrier: error_msg = f"ERREUR CRITIQUE: Carrier manquant pour SI {si_number} (valeur: '{carrier_name}')" logger.error(error_msg) raise ValueError(error_msg) # Créer le shipment shipment = ShipmentIn() shipment.reference = si_number shipment.from_location = loc_from shipment.to_location = loc_to shipment.carrier = None #carrier shipment.supplier = agent shipment.agent = agent shipment.vessel = vessel shipment.cargo_mode = 'bulk' shipment.bl_number = bl_number shipment.bl_date = bl_date shipment.etd = etd_date shipment.etad = shipment.bl_date + timedelta(days=20) # Sauvegarder ce shipment uniquement ShipmentIn.save([shipment]) inv_date,inv_nb = shipment._create_lots_from_fintrade() shipment.controller = shipment.get_controller() shipment.controller_target = controller shipment.create_fee(shipment.controller) shipment.instructions = shipment.get_instructions_html(inv_date,inv_nb) ShipmentIn.save([shipment]) trans_shipment.commit() successful_shipments += 1 logger.info(f"✓ Shipment {si_number} créé avec succès") except Exception as e: # Cette transaction échoue mais les autres continuent error_details = { 'si_number': si_number, 'carrier_name': carrier_name, 'error': str(e), 'traceback': traceback.format_exc() } failed_shipments.append(error_details) logger.error(f"✗ ERREUR pour shipment {si_number}: {e}") logger.error(f" Carrier: '{carrier_name}'") logger.error(f" Agent: '{agent_name}'") logger.error(f" Vessel: '{vessel_name}'") logger.error(" Traceback complet:") for line in traceback.format_exc().split('\n'): if line.strip(): logger.error(f" {line}") # ---- RÉSUMÉ FINAL ---- logger.info("=" * 60) logger.info("RÉSUMÉ DE L'EXÉCUTION") logger.info("=" * 60) logger.info(f"Total de shipments à traiter : {len(rows2)}") logger.info(f"Shipments créés avec succès : {successful_shipments}") logger.info(f"Shipments en échec : {len(failed_shipments)}") if failed_shipments: logger.info("\nDétail des échecs :") for i, error in enumerate(failed_shipments, 1): logger.info(f" {i}. SI {error['si_number']}:") logger.info(f" Carrier: '{error['carrier_name']}'") logger.info(f" Erreur: {error['error']}") # Log supplémentaire pour debug logger.info("\nAnalyse des carriers problématiques :") problematic_carriers = {} for error in failed_shipments: carrier = error['carrier_name'] if carrier in problematic_carriers: problematic_carriers[carrier] += 1 else: problematic_carriers[carrier] = 1 for carrier, count in problematic_carriers.items(): logger.info(f" Carrier '{carrier}' : {count} échec(s)") # Vérifier si ce carrier existe dans la base existing = Party.search([('name', '=', str(carrier).strip().upper())], limit=1) if existing: logger.info(f" → EXISTE DANS LA BASE (ID: {existing[0].id})") else: logger.info(f" → N'EXISTE PAS DANS LA BASE") logger.info("=" * 60)