+ i.z^RIt^RIHt^RIHtHt^RIHtHtHt]'d]t M]t ]t !RR4t RRlt R R lt R R ltR RltRRltR#)N)datetime) Productivo ENVIRONMENT)get_connectionbdproductivoget_connectionget_connectionERPca]tRt^toRtV3RlRltV3RlRltV3RlRltV3RlR ltV3R lR lt V3R lR lt V3RlRlt V3RlRlt V3RlRlt V3RlRltRtV3RlRltV3RlRltRtVtR#) BOMExplodercRVnR#)N process_id)selfs&ڇC:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\SupYCtrol\GerenteSyC\UtilityMaterials\GSYCManufacBOMIngSQL.py__init__BOMExploder.__init__s c,<V^8dQhRS[RS[RS[/#part_numrevisionreturnstrdict)format __classdict__s"r __annotate__BOMExploder.__annotate__s"""C"3"4"rc \\P!44Vn\P !4pVP W4VP4pVPWAV^^4\P !4V, P4R,pVPV4RRRVPRR/# \d@pTP\T44RRR\T4RTP/uR p?#R p?ii;i) u? Explota una BOM multinivel y retorna información del proceso isuccessTr messagezBOM explodida exitosamenteFerrorN) ruuiduuid4r rnow_init_process_control_get_pricingMO_explode_recursive total_seconds_update_process_stats Exception_mark_process_error)rrr start_time PricingMOprocessing_timees&&& r explode_bomBOMExploder.explode_bomsdjjl+\\^    & &x :++-I  # #I1a H (||~ :IIKdRO  & & 74doo7     $ $SV ,5Qdoo  sBC D 4DD D c2<V^8dQhRS[RS[RS[RS[/#)rrrlevel order_start)rint)rrs"rrr>s*2232#2c2X[2rc RTpVPW4pVF:pVP\V4pVPWqW#WX\4V^, pK< VP W4p V Fp V R,R8XdVP V 4p MRRRRRRRRRRR RR RR ^R RR RRR/ p \ RV 4VPWW#W[4V^, pV R,R8XgKsVPV R,4p V 'gKVP\V R,V V^,V4pK V#)u5Explota un nivel específico de la BOM recursivamenteTypePTranNumNTranDate MtlUnitCost LbrUnitCost BurUnitCost SubUnitCostMtlBurUnitCostExtCostPOLineStatus CALCULADOz$$$$MPartNum) _get_operations_get_totalcost_operationr/_insert_operation_get_materials_get_pricingMaterialsprint_insert_material_get_latest_revisionr)) rrrr5r6 current_order operationsopCost materialsmaterialCostMatchild_revisions &&&&& rr)BOMExploder._explode_recursive>sS# ))(= B00B?D  " "2-W` a Q M ''; !H3&44X>t!4!4!4!4$dq$Dk  &' "  ! !(h} ^ Q M3&!%!:!:8I;N!O!>$($;$;! +& % %M7"Frc,<V^8dQhRS[RS[RS[/#rrlist)rrs"rrrrs"11s1c1d1rc  Rp\4pV'g \R4hVP4pVPW1V34VPUu.uF qf^,NK ppVP 4Uu.uFp\ \Wx44NK upVP4#uupiuupi TP4i;i)-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 ERP ConexionBD_ERPr,cursorexecute descriptionfetchallrzipclose rrrquery connectionrbcolumncolumnsrows &&& rrLBOMExploder._get_materialsrs# J$% 67 7 &&(F NN5X"6 7/5/A/AB/AVayy/AGB7=7HI7HDW*+7HI    CI    )1B1B'#B17B,B1' B11Cc,<V^8dQhRS[RS[RS[/#rr[)rrs"rrrs"###s#t#rc  Rp\4pV'g \R4hVP4pVPW1V34VPUu.uF qf^,NK ppVP 4Uu.uFp\ \Wx44NK upVP4#uupiuupi TP4i;i)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 r_r`rhs &&& rrIBOMExploder._get_operationss .$% 67 7 &&(F NN5X"6 7/5/A/AB/AVayy/AGB7=7HI7HDW*+7HI    CI    roc&<V^8dQhRS[RS[/#)rrrr)rrs"rrrsSSrc Rp\4pV'gR#VP4pVPW!34VP4pV'd V^,MRVP 4# TP 4i;i)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 N)rarbrcfetchonerg)rrrirjrbresults&& rrP BOMExploder._get_latest_revisionsm $%  &&(F NN5+ .__&F &6!9D 0    J   s AA..Bc&<V^8dQhRS[RS[/#)rrrrt)rrs"rrrscSrc Rp\4pV'g \R4hVP4pVPW0PW34VP 4VP 4R# TP 4i;i)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_VPSr,rbrcr commitrg)rrrrirjrbs&&& rr'!BOMExploder._init_process_controlso  $% 67 7 &&(F NN5??H"G H        J   s =A00Bc 8<V^8dQhRS[RS[RS[RS[RS[/#)rrV part_father rev_fatherr5orderrrr7)rrs"rrrs2CSY\ehrc Rp\4pV'g \R4hVP4pVPVVPW#WTVR,VR,VR,VR,VR,VR,VR ,VR ,\ R ,\ R ,\ R ,\ R,\ R,\ R,\ R,\ R,\ R,\ R,\ R,34VP 4VP4R# TP4i;i)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{Seqr9rHRevPartDescriptionShortDescriptionQtyPerUOMCoder;r<r=r>r?r@rArBrCrDrEN)r|r,rbrcr rWr}rg) rrVrrr5rrirjrbs &&&&&& rrOBOMExploder._insert_materials $% 67 7 &&(F NN5%&!18I3F*;!<+,hx.@(9BU "GJ$79O & (> @V()79+= wv0A #         J   s DD66Ec 8<V^8dQhRS[RS[RS[RS[RS[/#)r operationrrr5rr)rrs"rrrs24cs[^gjrc Rp\4pV'g \R4hVP4pVPVVPW#WTVR,VR,VR,RVR,VR,VR ,VR ,\ R ,\ R ,\ 34VP4VP4R# TP4i;i) 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{rr9OpCodeNOpDescr EstProdHoursrr>r?) r|r,rbrcr r/rTr}rg) rrrrr5rrirjrbs &&&&&& rrKBOMExploder._insert_operations  $% 67 7 &&(F NN5%% )F"3Yx5Hi)95G+H.)9Y+?-(-(#         J   s BCCc <V^8dQhRS[/#)rprocessing_time_ms)float)rrs"rrr,src  NRp\4pV'gR#VP4pVPVVPVPVP\ V4VP34VP 4VP 4R# TP 4i;i)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|rbrcr r7r}rg)rrrirjrbs&& rr+!BOMExploder._update_process_stats,s $%   &&(F NN5$//&'#         J   s A(BB$c <V^8dQhRS[/#)r error_messagert)rrs"rrrFsrc Rp\4pV'gR#VP4pVPW!VP34VP 4VP 4R# TP 4i;i)uMarca el proceso como erróneozx UPDATE BOM_Process_Control SET Status = 'Error', ErrorMessage = ? WHERE ProcessID = ? N)r|rbrcr r}rg)rrrirjrbs&& rr-BOMExploder._mark_process_errorFsf  $%   &&(F NN5$//"B C        J   s =A''A9c Rp\4pV'g \R4hVP4pVPV4VP 4pV'dFVP Uu.uF qU^,NK pp\ \Wd44VP4#\R4huupi TP4i;i)zObtiene desde VPS z` SELECT LbrUnitCost, BurUnitCost FROM CostLbr r{z"No se encontraron datos de CostLbr) r|r,rbrcrvrdrrfrg)rrirjrbrmrkrls& rr(BOMExploder._get_pricingMO[s $% 67 7 &&(F NN5 !//#C393E3EF3E!993EFC-.     DEEG    s#8B/B/(B*9B/B//Cc&<V^8dQhRS[RS[/#)r pricingMOrr)rrs"rrrus$4rc\VPR4;'g^4p\VPR4;'g^4p\VPR4;'g^4pW4,W5,,pV#)rr>r?)rget)rrr est_hours lbr_unit_cost bur_unit_cost total_costs&&& rrJ$BOMExploder._get_totalcost_operationusi)--7<<1= immM:??a@ immM:??a@ /I4MN rc <V^8dQhRS[/#)rrVr)rrs"rrrs11d1rc  Rp\4pV'g \R4hVP4pVPW!R,VR,VR,VR,VR,VR,34VP 4pV'dFVP Uu.uF qf^,NK pp\ \Wu44VP4#\R4huupi TP4i;i)r^uj 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 r_rrHz#No se encontraron datos de PartTran) rar,rbrcrvrdrrfrg)rrVrirjrbrmrkrls&& rrM!BOMExploder._get_pricingMaterialss @$% 67 7 &&(F NN5H#5x7I8T\K]_ghp_qs{}EtFHPQZH[#\ ]//#C393E3EF3E!993EFC-.     EFF G    sA7CC)CCC1r N)__name__ __module__ __qualname____firstlineno__rr2r)rLrIrPr'rOrKr+r-r(rJrM__static_attributes____classdictcell__)rs@rr r s""H22h11f##J*&@44*411rr c<V^8dQhR\R\R\/#)r new_ext_costr r)rr)rs"rrrs!44E4s4c4rcxRp\4pV'g RRRR/V'dVP4##VP4pRpVPWPW34VPpVP 4V^8Xd&RRRRV RV 2/V'dVP4##RR R R R V/V'dVP4## \ dNpT'dTP4RRRR \T4 2/uRp?T'dTP4##Rp?ii;i T'dTP4ii;i)zDActualiza el ExtCost y cambia el Status a MANUAL en la base de datosNr!Fr#%Error de conexión a la base de datosz UPDATE BOM_Materials SET ExtCost = ?, Status = 'MANUAL' WHERE ProcessID = ? AND PartNum = ? u(No se encontró el registro. ProcessID: z , PartNum: Tr"zCosto actualizado exitosamente updated_rowszError en la base de datos: ) r|rgrbrcrowcountr}r,rollbackr)rr rrjrb update_query row_countr1s&&& rget_status_manualrsYJ0#% 5@X      O""$  |J%IJOO  >5CJ<{[cZde&       t 7 I             ! u 23q6(;              s;CAC"C D)D7D8DDDD9c0V^8dQhR\R\/#rr rr)rs"rrrs~(~(c~(d~(rcRpRpRpRp\4pV'gRRRR/#VP4pVPWV34/p.pVP4Fp V ^,p R\ V ^,4R \ V ^,4R \ V ^,4R \ V ^,4R \ V ^,4/Wz&VP R V R\ V ^,4R\ V ^,4R\ V ^,4R\ V ^,4R\ V ^,4/4K V'g RRRR/V'dVP4##VPW@34.p Rp VP4F~p V ^,p V P R V RVPV /4PR ^4R\ V ^,4R\ V ^,4/4V \ V ^,4, p K VPW034VP4p .pV FwrpVPV /4PR ^4pVPVVW 34TP R T RV'd \ V4M^RVRRRVPV /4PR ^4RVPV /4PR ^4/4K VP4\\RVP444^4p\\RVP444^4pRRRVRVR \V ^4R!VR"V R#VR$V/V'dVP4## \dEpTP4RRR\T4R$T/uR%p?T'dTP4##R%p?ii;i T'dTP4ii;i)&u -- 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 r!Fr#r material_costmaterial_componentsoperation_costoperation_componentsrpartNum materialCostmaterialComponents operationCostoperationComponents totalCostz+No se encontraron componentes para calcularcalculatedCost components previousCostnewCost statusChangerFc32"TF qR,xK R#5i)rN.0vs& r (calculate_total_costs..s)VCUaL//CUc32"TF qR,xK R#5i)rNrrs& rrrs(YFX+;)<) "6  1 ^   &   % % '   $$& u SZ     % % '     % % ' sCDM2<M2G=M2M22 O= N<OO<OOOcHV^8dQhR\R\R\R\/#)rr rrrr)rs"rrrs)33#333PT3rc Rp\4pV'g \R4hVP4pVPW1W WV34VP 4pV'g RRRR/V'dVP 4##V^,^,e\ V^,^,4MRp\V4^8d,V^,^,e\ V^,^,4MRpRR R Wx,R VR V/V'dVP 4## \d6p RRRR \T 4 2/uRp ? X'dTP 4##Rp ? ii;i X'dTP 4ii;i)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 datosr!Fr#zNo se encontraron registrosNrTrrrzError en base de datos: ) r|r,rbrcrergrlenr) r rrrirjrbresultsrrr1s &&& rget_total_cost_for_parentrsZ E,#% DE E""$uxS]^_//#ug/LM      18 1 0Igajm,s 14W1AgajQRmF_wqz!}-eh t 7 M ^         P5'-Ec!fX+NOO      P      s7AD=A9D EE ,E-E EEE.c0V^8dQhR\R\/#rr)rs"rrrs::S:T:rc :RpRpRp\4pV'gRR/#VP4pVPW34VPUu.uF qf^,NK ppVP 4Uu.uFp\ \ Wx44NK p pVPW 34VPUu.uF qf^,NK p pVP 4Uu.uFp\ \ W44NK p pVPW034VPUu.uF qf^,NK p pVP4p V 'd\ \ W44MRpRV RV R V/VP4#uupiuupiuupiuupiuupi TP4i;i) 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 = ? r#r{NrUrRcontrol) r|rbrcrdrerrfrvrg)r query_materialsquery_operations query_controlrjrbrkmaterials_columnsrmrUoperations_columnsrRcontrol_columns control_datars& r get_bom_datars O M !J 344""$  65;5G5GH5G6AYY5GHBH//BSTBS3T#/56BS T '76<6H6HI6HFQii6HIDJOODUVDUSd3178DU V }m4393E3EF3E!993EF( >J$s?9:PT  * w  +ITJVG sM0FE/F3E4"F2E9FE>4"FF'7F/FFc0V^8dQhR\R\/#)rrirr[)rs"rrr-src Rp\4pV'g.#VP4pRV R2pVPWV34VPUu.uF qU^,NK ppVP 4Uu.uFp\ \ Wg44NK upVP4#uupiuupi TP4i;i)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 %)rarbrcrdrerrfrg)ri search_queryrjrb search_termrkrlrms& r search_partsr -s L !J   ""$%l |;%?@+1+=+=>+=!99+=>39??3DE3DCS&'3DE  ?E s)7B.B$ B.4B)B.$ B..C)r$rconfigrrConsultas_SQL.conexionrrrr|rar rrrrr rrrr sU  *``/N#N" ]]F 4n~(B3B:xr