a 0i.z@sddlZddlmZddlmZmZddlmZmZmZerBeZ neZ eZ GdddZ e e e ddd Ze ed d d Ze e e ed ddZe ed ddZe edddZdS)N)datetime) Productivo ENVIRONMENT)get_connectionbdproductivoget_connectionget_connectionERPc@seZdZddZeeedddZeeeedddZeee dd d Z eee dd d Z eed ddZ eedddZ eeeeedddZeeeeedddZedddZedddZdd Zeed!d"d#Zed$d%d&Zd'S)( BOMExplodercCs d|_dS)N) process_id)selfr ]/var/www/html/src/Consultas_SQL/SupYCtrol/GerenteSyC/UtilityMaterials/GSYCManufacBOMIngSQL.py__init__szBOMExploder.__init__)part_numrevisionreturnc Cstt|_t}zT||||}||||ddt| d}| |d|jddWSt y}z,| t|dt||jdWYd }~Sd }~00d S) uO Explota una BOM multinivel y retorna información del proceso riTzBOM explodida exitosamente)successr messageF)rerrorr N) struuiduuid4r rnow_init_process_control_get_pricingMO_explode_recursive total_seconds_update_process_stats Exception_mark_process_error)r rr start_time PricingMOZprocessing_timeer r r explode_boms$  zBOMExploder.explode_bom)rrlevel order_startc Cs|}|||}|D].}|t|}|||||||t|d7}q|||} | D]} | ddkrp|| } ndddddddddddd } td| || ||||| |d7}| dd krT|| d } | rT| t| d | |d|}qT|S) u5Explota un nivel específico de la BOM recursivamenterTypePNr CALCULADO) TranNumTranDate MtlUnitCost LbrUnitCost BurUnitCost SubUnitCostMtlBurUnitCostExtCostPOLineStatusz$$$$MPartNum) _get_operations_get_totalcost_operationr!_insert_operation_get_materials_get_pricingMaterialsprint_insert_material_get_latest_revisionr) r rrr$r%Z current_order operationsopCost materialsmaterialCostMatZchild_revisionr r r r>sH        zBOMExploder._explode_recursivecsnd}t}|stdzH|}||||fdd|jDfdd|DW|S|0dS)-Obtiene materiales de la BOM desde Epicor ERPa SELECT Erp.PartMtl.MtlSeq AS Seq, Erp.Part.TypeCode AS [Type], CASE Erp.Part.TypeCode WHEN 'M' THEN 'Fabricado' WHEN 'P' THEN 'Material' ELSE Erp.Part.TypeCode END AS TipoDesc, Erp.PartMtl.MtlPartNum AS PartNum, ( SELECT TOP 1 Erp.PartRev.RevisionNum FROM Erp.PartRev WHERE Erp.PartRev.Company = Erp.PartMtl.Company AND Erp.PartRev.PartNum = Erp.PartMtl.MtlPartNum ORDER BY Erp.PartRev.EffectiveDate DESC ) AS Rev, Erp.Part.PartDescription, LEFT(Erp.Part.PartDescription, 35) AS ShortDescription, Erp.PartMtl.QtyPer, Erp.PartMtl.UOMCode FROM Erp.PartMtl LEFT JOIN Erp.Part ON Erp.PartMtl.Company = Erp.Part.Company AND Erp.PartMtl.MtlPartNum = Erp.Part.PartNum WHERE Erp.PartMtl.Company = 'IGSA' AND Erp.PartMtl.PartNum = ? AND Erp.PartMtl.RevisionNum = ? ORDER BY Erp.PartMtl.MtlSeq Error de conexión a ERPcSsg|] }|dqSrr .0columnr r r z.BOMExploder._get_materials..csg|]}tt|qSr dictziprHrowcolumnsr r rJrKNConexionBD_ERPrcursorexecute descriptionfetchallcloser rrquery connectionrUr rQr r9rs%zBOMExploder._get_materialscsnd}t}|stdzH|}||||fdd|jDfdd|DW|S|0dS)z.Obtiene operaciones de la BOM desde Epicor ERPa- SELECT Erp.PartOpr.OprSeq AS Seq, Erp.PartOpr.SubContract AS [Type], CASE Erp.PartOpr.SubContract WHEN 0 THEN 'Mano de Obra' WHEN 1 THEN 'Subcontrato' END AS TipoDesc, Erp.PartOpr.OpCode, Erp.OpMaster.OpDesc, LEFT(Erp.OpMaster.OpDesc, 35) AS ShortDescription, Erp.PartOpr.EstProdHours, '' AS UOMCode FROM Erp.PartOpr LEFT JOIN Erp.OpMaster ON Erp.PartOpr.Company = Erp.OpMaster.Company AND Erp.PartOpr.OpCode = Erp.OpMaster.OpCode WHERE Erp.PartOpr.Company = 'IGSA' AND Erp.PartOpr.PartNum = ? AND Erp.PartOpr.RevisionNum = ? ORDER BY Erp.PartOpr.OprSeq rEcSsg|] }|dqSrFr rGr r r rJrKz/BOMExploder._get_operations..csg|]}tt|qSr rLrOrQr r rJrKNrSrZr rQr r6szBOMExploder._get_operations)rrcCsZd}t}|sdSz8|}|||f|}|r>|dndW|S|0dS)u/Obtiene la revisión más reciente de una partez SELECT TOP 1 RevisionNum FROM Erp.PartRev WHERE Company = 'IGSA' AND PartNum = ? ORDER BY EffectiveDate DESC Nr)rTrUrVfetchonerY)r rr[r\rUresultr r r r=sz BOMExploder._get_latest_revision)rrcCsVd}t}|stdz0|}|||j||f|W|n |0dS)z4Inicializa el registro de control del proceso en VPSz INSERT INTO BOM_Process_Control (ProcessID, PartNumFather, RevisionNumFather, Status, ProcessDate) VALUES (?, ?, ?, 'Processing', GETDATE()) Error de conexión a VPSN)ConexionBD_VPSrrUrVr commitrYrZr r r rs z!BOMExploder._init_process_control)rB part_father rev_fatherr$orderc Csd}t}|stdz|}|||j|||||d|d|d|d|d|d|d |d td td td tdtdtdtdtdtdtdtdf|W|n |0dS)z3Inserta un material en la tabla BOM_Materials (VPS)a INSERT INTO BOM_Materials (ProcessID, PartNumFather, RevisionNumFather, [Order], [Level], Seq, [Type], PartNum, Rev, PartDescription, ShortDescription, QtyPer, UOMCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) r_Seqr&r5ZRevZPartDescriptionShortDescriptionQtyPerUOMCoder)r*r+r,r-r.r/r0r1r2r3N)r`rrUrVr rCrarY) r rBrbrcr$rdr[r\rUr r r r<s$    zBOMExploder._insert_material) operationrbrcr$rdc Csd}t}|stdzn|}|||j|||||d|d|dd|d|d|d |d td td tf|W|n |0dS) u7Inserta una operación en la tabla BOM_Operations (VPS)a, INSERT INTO BOM_Operations (ProcessID, PartNumFather, RevisionNumFather, [Order], [Level], Seq, [Type], OpCode, Rev, OpDesc, ShortDescription, EstProdHours, UOMCode, LbrUnitCost, BurUnitCost, ExtCost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) r_rer&ZOpCodeNZOpDescrf EstProdHoursrhr,r-) r`rrUrVr r!r@rarY) r rirbrcr$rdr[r\rUr r r r8s"   zBOMExploder._insert_operation)processing_time_mscCs`d}t}|sdSz>|}|||j|j|jt||jf|W|n |0dS)u#Actualiza estadísticas del procesoa UPDATE BOM_Process_Control SET Status = 'Completed', TotalMaterials = (SELECT COUNT(*) FROM BOM_Materials WHERE ProcessID = ?), TotalOperations = (SELECT COUNT(*) FROM BOM_Operations WHERE ProcessID = ?), MaxLevel = (SELECT ISNULL(MAX([Level]), 0) FROM BOM_Materials WHERE ProcessID = ?), ProcessingTime_MS = ? WHERE ProcessID = ? N)r`rUrVr intrarY)r rkr[r\rUr r r r,s    z!BOMExploder._update_process_stats) error_messagecCsPd}t}|sdSz.|}||||jf|W|n |0dS)uMarca el proceso como erróneozx UPDATE BOM_Process_Control SET Status = 'Error', ErrorMessage = ? WHERE ProcessID = ? N)r`rUrVr rarY)r rmr[r\rUr r r rFs zBOMExploder._mark_process_errorcCsd}t}|stdzZ|}|||}|r^dd|jD}tt||W|StdW|n |0dS)z Obtiene desde VPS z` SELECT LbrUnitCost, BurUnitCost FROM CostLbr r_cSsg|] }|dqSrFr rGr r r rJmrKz.BOMExploder._get_pricingMO..z"No se encontraron datos de CostLbrN) r`rrUrVr]rWrMrNrY)r r[r\rUrPrRr r r r[s  zBOMExploder._get_pricingMO) pricingMOricCsJt|dpd}t|dp d}t|dp2d}||||}|S)Nrjrr,r-)floatget)r rnriZ est_hoursZ lbr_unit_costZ bur_unit_cost total_costr r r r7us z$BOMExploder._get_totalcost_operation)rBc Csd}t}|stdz|}|||d|d|d|d|d|df|}|rdd|jD}tt||W|StdW|n |0dS) rDuj SELECT TranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, PONum AS PO, POLine AS Line, ([MtlUnitCost] * ? + [LbrUnitCost] * ? + [BurUnitCost] * ? + [SubUnitCost] * ? + [MtlBurUnitCost] * ?) AS ExtCost, CASE -- Si está en los últimos 90 días WHEN TranDate >= DATEADD(day, -90, GETDATE()) THEN 'COSTEADOS' -- Si está entre 90 y 180 días WHEN TranDate >= DATEADD(day, -180, GETDATE()) AND TranDate < DATEADD(day, -90, GETDATE()) THEN 'REVISION' -- Si no cumple las condiciones anteriores (sin TranNum/TranDate o más de 180 días) ELSE 'INSERTAR' END AS Status FROM Erp.PartTran WHERE Erp.PartTran.Company = 'IGSA' AND Erp.PartTran.PartNum = ? ORDER BY Erp.PartTran.TranDate DESC rErgr5cSsg|] }|dqSrFr rGr r r rJrKz5BOMExploder._get_pricingMaterials..z#No se encontraron datos de PartTranN) rTrrUrVr]rWrMrNrY)r rBr[r\rUrPrRr r r r:s 0 z!BOMExploder._get_pricingMaterialsN)__name__ __module__ __qualname__r rrMr#rlrlistr9r6r=rr<r8rorrrr7r:r r r r rs$43%  r) new_ext_costr rc Csd}zzt}|s.dddWW|r,|S|}d}|||||f|j}||dkrdd|d|dWW|r|Sd d |d WW|r|Sty}z:|r|dd t|dWYd}~W|r|Sd}~00W|r|n|r|0dS) zDActualiza el ExtCost y cambia el Status a MANUAL en la base de datosNF%Error de conexión a la base de datosrrz UPDATE BOM_Materials SET ExtCost = ?, Status = 'MANUAL' WHERE ProcessID = ? AND PartNum = ? ru(No se encontró el registro. ProcessID: z , PartNum: TzCosto actualizado exitosamente)rrZ updated_rowszError en la base de datos: ) r`rYrUrVrowcountrarrollbackr)rvr rr\rU update_queryZ row_countr"r r r get_status_manualsT,   r|)r rc Csd}d}d}d}t}|s$dddSzzH|}||||fi}g}|D]} | d} t| d t| d t| d t| d t| d d|| <|| t| d t| d t| d t| d t| d dqT|sdddWW|r|S|||fg} d} |D]R} | d} | | || iddt| d t| d d| t| d 7} q|||f|} g}| D]x\} }}|| idd}||||| f|| |rt|nd|d|| idd|| idddq| t t dd| Dd }t t dd| Dd }d||t | d || ||dWW|rr|St y}z4|dt||dWYd}~W|r|Sd}~00W|r|n|r|0dS)Nu -- Materiales comprados agrupados por padre con precisión decimal WITH MaterialCosts AS ( SELECT PartNumFather AS PartNum, CAST(SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS DECIMAL(18,8)) AS MaterialCost, COUNT(*) AS MaterialComponents FROM BOM_Materials WHERE ProcessID = ? AND Type = 'P' GROUP BY PartNumFather ), -- Costos de operaciones agrupados por padre con precisión decimal OperationCosts AS ( SELECT PartNumFather AS PartNum, CAST(SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS DECIMAL(18,8)) AS OperationCost, COUNT(*) AS OperationComponents FROM BOM_Operations WHERE ProcessID = ? GROUP BY PartNumFather ), -- Costos combinados con verificación de NULLs CombinedCosts AS ( SELECT m.PartNum, CAST(COALESCE(m.MaterialCost, 0) AS DECIMAL(18,8)) AS MaterialCost, m.MaterialComponents, CAST(COALESCE(o.OperationCost, 0) AS DECIMAL(18,8)) AS OperationCost, o.OperationComponents, CAST(COALESCE(m.MaterialCost, 0) + COALESCE(o.OperationCost, 0) AS DECIMAL(18,8)) AS TotalCost FROM MaterialCosts m LEFT JOIN OperationCosts o ON m.PartNum = o.PartNum UNION SELECT o.PartNum, CAST(0 AS DECIMAL(18,8)) AS MaterialCost, 0 AS MaterialComponents, CAST(o.OperationCost AS DECIMAL(18,8)) AS OperationCost, o.OperationComponents, CAST(o.OperationCost AS DECIMAL(18,8)) AS TotalCost FROM OperationCosts o WHERE NOT EXISTS (SELECT 1 FROM MaterialCosts m WHERE m.PartNum = o.PartNum) ) SELECT PartNum, MaterialCost, MaterialComponents, OperationCost, OperationComponents, TotalCost FROM CombinedCosts WHERE PartNum IS NOT NULL ORDER BY PartNum z UPDATE BOM_Materials SET ExtCost = CAST(? AS DECIMAL(18,8)), Status = 'CALCULADO' WHERE ProcessID = ? AND PartNum = ? AND Type = 'M' -- FABRICADO z SELECT PartNum, CAST(ExtCost AS DECIMAL(18,8)) AS ExtCost, Status FROM BOM_Materials WHERE ProcessID = ? AND Type = 'M' -- FABRICADO z SELECT PartNumFather, SUM(COALESCE(CAST(ExtCost AS DECIMAL(18,8)), 0)) AS TotalMaterialCost, COUNT(*) AS Components FROM BOM_Materials WHERE ProcessID = ? AND Type = 'P' GROUP BY PartNumFather Frwrxrr) material_costmaterial_componentsoperation_costoperation_componentsrq)partNum materialCostmaterialComponents operationCostoperationComponents totalCostz+No se encontraron componentes para calcularrq)rZcalculatedCostr componentsr(rr)rZ previousCostZnewCostZ statusChangerrcss|]}|dVqdS)rqNr rHvr r r rKz(calculate_total_costs..css|]}|dVqdS)rNr rr r r rrKT)rupdatedMaterialstotalCalculatedCostZtotalMaterialCostZtotalOperationCostZverificationDataZ costBreakdown processId)rrr)r`rUrVrXrorlappendrYrpraroundsumvaluesrrzr)r Zcalculate_costs_queryZupdate_fabricated_materialsZget_fabricated_materialsZverification_queryZdatabase_connectionZdatabase_cursorZ cost_dataZcost_breakdownrPrZverification_dataZtotal_material_costZfabricated_materialsZupdated_materialsZ current_costZcurrent_statusZnew_costZtotal_calculated_costZtotal_operation_costrr r r calculate_total_costss=                @      r)r rrrc CsHd}z,zt}|std|}||||||||f|}|sbdddWW|r`|S|dddurt|ddnd}t|d kr|d ddurt|d dnd}d ||||d WW|r|Sty } z0dd t| dWYd} ~ W|r|Sd} ~ 00W|rD|n|rB|0dS) u-Función con mejor manejo de errores para SQLa SELECT SUM(COALESCE(BOM_Materials.ExtCost, 0)) AS TotalCostMaterial FROM BOM_Materials WHERE BOM_Materials.PartNumFather = ? AND BOM_Materials.RevisionNumFather = ? AND BOM_Materials.ProcessID = ? UNION ALL SELECT SUM(COALESCE(BOM_Operations.ExtCost, 0)) AS TotalCostOperation FROM BOM_Operations WHERE BOM_Operations.PartNumFather = ? AND BOM_Operations.RevisionNumFather = ? AND BOM_Operations.ProcessID = ? z&No se pudo conectar a la base de datosFzNo se encontraron registrosrxrNrrT)rrrrzError en base de datos: ) r`rrUrVrXrYrolenr) r rrr[r\rUresultsrrr"r r r get_total_cost_for_parents@ $0   rc sd}d}d}t}|sddiSz|}|||fdd|jDfdd|D}|||fd d|jDfd d|D}|||fd d|jD}|} | rtt|| nd } ||| d W|S|0d S)z,Obtiene datos completos de una BOM explodidaaJ SELECT [Level], [Order], Seq, [Type], PartNum, Rev, PartDescription, ShortDescription, QtyPer, UOMCode, TranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, ExtCost, PO, Line, Status FROM BOM_Materials WHERE ProcessID = ? ORDER BY [Order] a: SELECT [Level], [Order], Seq, [Type], OpCode, OpDesc, ShortDescription, EstProdHours, UOMCode, TranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, ExtCost, PO, Line FROM BOM_Operations WHERE ProcessID = ? ORDER BY [Order] z? SELECT * FROM BOM_Process_Control WHERE ProcessID = ? rr_cSsg|] }|dqSrFr rGr r r rJrKz get_bom_data..csg|]}tt|qSr rLrO)materials_columnsr r rJrKcSsg|] }|dqSrFr rGr r r rJrKcsg|]}tt|qSr rLrO)operations_columnsr r rJrKcSsg|] }|dqSrFr rGr r r rJ rKN)rAr>control) r`rUrVrWrXr]rMrNrY) r Zquery_materialsZquery_operationsZ query_controlr\rUrAr>Zcontrol_columnsZ control_datarr )rrr get_bom_datas2  r)r[rcsvd}t}|sgSzT|}d|d}||||fdd|jDfdd|DW|S|0dS)u+Busca partes en el ERP por nombre o códigoa SELECT TOP 10 p.PartNum, p.PartDescription, pr.RevisionNum, p.TypeCode FROM Erp.Part p LEFT JOIN Erp.PartRev pr ON p.Company = pr.Company AND p.PartNum = pr.PartNum WHERE p.Company = 'IGSA' AND p.TypeCode = 'M' -- Solo manufacturados AND (p.PartNum LIKE ? OR p.PartDescription LIKE ?) ORDER BY p.PartNum, pr.EffectiveDate DESC %cSsg|] }|dqSrFr rGr r r rJFrKz search_parts..csg|]}tt|qSr rLrOrQr r rJGrKN)rTrUrVrWrXrY)r[Z search_queryr\rUZ search_termr rQr search_parts-s r)rrconfigrrConsultas_SQL.conexionrrrr`rTrrorr|rMrrrrurr r r r s" &7BA<