U i.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 C:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\SupYCtrol\GerenteSyC\UtilityMaterials\GSYCManufacBOMIngSQL.py__init__szBOMExploder.__init__)part_numrevisionreturnc Cstt|_t}zT||||}||||ddt| d}| |d|jddWSt k r}z(| t|dt||jdWYSd }~XYnXd 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_recursivecshd}t}|stdzB|}||||fdd|jDfdd|DWS|XdS)-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 sz.BOMExploder._get_materials..csg|]}tt|qSr dictziprHrowcolumnsr r rJsNConexionBD_ERPrclosecursorexecute descriptionfetchallr rrquery connectionrUr rPr r9rs%zBOMExploder._get_materialscshd}t}|stdzB|}||||fdd|jDfdd|DWS|XdS)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 rJsz/BOMExploder._get_operations..csg|]}tt|qSr rKrNrPr r rJsNrRrYr rPr r6szBOMExploder._get_operations)rrcCsTd}t}|sdSz2|}|||f|}|r>|dndWS|XdS)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)rSrTrUrVfetchone)r rrZr[rUresultr r r r=sz BOMExploder._get_latest_revision)rrcCsNd}t}|stdz(|}|||j||f|W5|XdS)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_VPSrrTrUrVr commitrYr 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|W5|XdS)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_rrTrUrVr rCr`) r rBrarbr$rcrZr[rUr r r r<sD zBOMExploder._insert_material) operationrarbr$rcc Csd}t}|stdzf|}|||j|||||d|d|dd|d|d|d |d td td tf|W5|XdS) 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^rdr&ZOpCodeNZOpDescre EstProdHoursrgr,r-) r_rrTrUrVr r!r@r`) r rhrarbr$rcrZr[rUr r r r8s4 zBOMExploder._insert_operation)processing_time_mscCsXd}t}|sdSz6|}|||j|j|jt||jf|W5|XdS)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_rTrUrVr intr`)r rjrZr[rUr r r r,s  z!BOMExploder._update_process_stats) error_messagecCsHd}t}|sdSz&|}||||jf|W5|XdS)uMarca el proceso como erróneozx UPDATE BOM_Process_Control SET Status = 'Error', ErrorMessage = ? WHERE ProcessID = ? N)r_rTrUrVr r`)r rlrZr[rUr r r rFs zBOMExploder._mark_process_errorcCsrd}t}|stdzL|}|||}|rXdd|jD}tt||WStdW5|XdS)z Obtiene desde VPS z` SELECT LbrUnitCost, BurUnitCost FROM CostLbr r^cSsg|] }|dqSrFr rGr r r rJmsz.BOMExploder._get_pricingMO..z"No se encontraron datos de CostLbrN) r_rrTrUrVr\rWrLrM)r rZr[rUrOrQr r r r[s  zBOMExploder._get_pricingMO) pricingMOrhcCsJt|dpd}t|dp d}t|dp2d}||||}|S)Nrirr,r-)floatget)r rmrhZ est_hoursZ lbr_unit_costZ bur_unit_cost total_costr r r r7us z$BOMExploder._get_totalcost_operation)rBc Csd}t}|stdzr|}|||d|d|d|d|d|df|}|r~dd|jD}tt||WStdW5|XdS) 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 rErfr5cSsg|] }|dqSrFr rGr r r rJsz5BOMExploder._get_pricingMaterials..z#No se encontraron datos de PartTranN) rSrrTrUrVr\rWrLrM)r rBrZr[rUrOrQr r r r:s 0 z!BOMExploder._get_pricingMaterialsN)__name__ __module__ __qualname__r rrLr#rkrlistr9r6r=rr<r8rnrrrr7r:r r r r rs$43%  r) new_ext_costr rc Csd}zz|t}|s"dddWWS|}d}|||||f|j}||dkrrdd|d|dWWbSd d |d WWPStk r}z,|r|dd t|dWYWSd}~XYnXW5|r|XdS) 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: ) rTr_rUrVrowcountr`rrollbackr)rur rr[rU update_queryZ row_countr"r r r get_status_manuals8     $r{)r rc Csd}d}d}d}t}|s$dddSzz4|}||||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|sdddWWS|||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 || ||dWWJSt k r}z$|dt||dWYWSd}~XYnXW5|r|XdS)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 Frvrwrr) material_costmaterial_componentsoperation_costoperation_componentsrp)partNum materialCostmaterialComponents operationCostoperationComponents totalCostz+No se encontraron componentes para calcularrp)rZcalculatedCostr componentsr(rr)rZ previousCostZnewCostZ statusChangerrcss|]}|dVqdS)rpNr rHvr r r sz(calculate_total_costs..css|]}|dVqdS)rNr rr r r rsT)rupdatedMaterialstotalCalculatedCostZtotalMaterialCostZtotalOperationCostZverificationDataZ costBreakdown processId)rrr)r_rTrUrVrXrnrkappendror`roundsumvaluesrryr)r Zcalculate_costs_queryZupdate_fabricated_materialsZget_fabricated_materialsZverification_queryZdatabase_connectionZdatabase_cursorZ cost_dataZcost_breakdownrOrZverification_dataZtotal_material_costZfabricated_materialsZupdated_materialsZ current_costZcurrent_statusZnew_costZtotal_calculated_costZtotal_operation_costrr r r calculate_total_costss=                    $r)r rrrc Csd}zzt}|std|}||||||||f|}|sXdddWWS|dddk rxt|ddnd}t|d kr|d ddk rt|d dnd}d ||||d WWFStk r} z dd t| dWYWSd} ~ XYnXW5|r|XdS) 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 registrosrwrNrrT)rrrrzError en base de datos: ) rTr_rrUrVrXrnlenr) r rrrZr[rUresultsrrr"r r r get_total_cost_for_parents*$0 2rc sd}d}d}t}|sddiSz|}|||fdd|jDfdd|D}|||fd d|jDfd d|D}|||fd d|jD}|} | rtt|| nd } ||| d WS|Xd 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 rJsz get_bom_data..csg|]}tt|qSr rKrN)materials_columnsr r rJscSsg|] }|dqSrFr rGr r r rJscsg|]}tt|qSr rKrN)operations_columnsr r rJscSsg|] }|dqSrFr rGr r r rJ sN)rAr>control) r_rTrUrVrWrXr\rLrM) r Zquery_materialsZquery_operationsZ query_controlr[rUrAr>Zcontrol_columnsZ control_datarr )rrr get_bom_datas.   r)rZrcspd}t}|sgSzN|}d|d}||||fdd|jDfdd|DWS|XdS)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 rJFsz search_parts..csg|]}tt|qSr rKrNrPr r rJGsN)rSrTrUrVrWrX)rZZ search_queryr[rUZ search_termr rPr search_parts-s r)rrconfigrrConsultas_SQL.conexionrrrr_rSrrnrr{rLrrrrtrr r r r s" &7BA<