i.zSSKrSSKJr SSKJrJr SSKJrJrJr \(a\r O\r \r "SS5r S\ S\ S \ 4S jrS\ S \4S jrS\ S \ S \ S \4SjrS\ S \4SjrS\ S \4Sjrg)N)datetime) Productivo ENVIRONMENT)get_connectionbdproductivoget_connectionget_connectionERPc \rSrSrSrS\S\S\4SjrS\S\S\S\4S jr S\S\S\ 4S jr S\S\S\ 4S jr S\S\4S jr S\S\4S jrS\S\S\S\S\4 SjrS\S\S\S\S\4 SjrS\4SjrS\4SjrSrS\S\4SjrS\4SjrSrg) BOMExplodercSUlg)N process_id)selfs ڇC:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\SupYCtrol\GerenteSyC\UtilityMaterials\GSYCManufacBOMIngSQL.py__init__BOMExploder.__init__s part_numrevisionreturnc[[R"55Ul[R "5nUR X5 UR5nURXAUSS5 [R "5U- R5S-nURU5 SURSS.$![a=nUR[U55 S[U5URS.sS nA$S nAff=f) u? Explota una BOM multinivel y retorna información del proceso riTzBOM explodida exitosamente)successrmessageF)rerrorrN) struuiduuid4rrnow_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  & & 7 "oo7     $ $SV , Q"oo  sA>B99 D2C;5D;Dlevel order_startc UnURX5nUH6nUR[U5nURXqX#XX[5 US- nM8 UR X5n U Hn U SS:XaUR U 5n OSSSSSSSSSSSS. n [ SU 5 URXX#X[5 US- nU SS :XdM[URU S 5n U (dMxUR[U S U US-U5nM U$) 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") rrrr-r. current_order operationsopCost materialsmaterialCostMatchild_revisions rr"BOMExploder._explode_recursive>s=# ))(= B00B?D  " "2-W` a Q M ''; !H3&44X> $ $#'#'#'#'&*  )  &' "  ! !(h} ^ Q M3&!%!:!:8I;N!O!>$($;$;! +& % %M7"Frc Sn[5nU(d [S5eUR5nURX1U45 URVs/sHofSPM nnUR 5Vs/sHn[ [Xx55PM snUR5 $s snfs snf!UR5 f=f)-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 ERPr ConexionBD_ERPr%cursorexecute descriptionfetchalldictzipclose rrrquery connectionrVcolumncolumnsrows rrCBOMExploder._get_materialsrs# J$% 67 7 &&(F NN5X"6 7/5/A/AB/AVay/AGB7=7HI7HDW*+7HI    CI    )1B.B$B.3B)B.$ B..Cc Sn[5nU(d [S5eUR5nURX1U45 URVs/sHofSPM nnUR 5Vs/sHn[ [Xx55PM snUR5 $s snfs snf!UR5 f=f)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 rSrrTr]s rr@BOMExploder._get_operationss .$% 67 7 &&(F NN5X"6 7/5/A/AB/AVay/AGB7=7HI7HDW*+7HI    CI    rdcSn[5nU(dgUR5nURX!45 UR5nU(aUSOSUR 5 $!UR 5 f=f)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)rUrVrWfetchoner\)rrr^r_rVresults rrG BOMExploder._get_latest_revisionsm $%  &&(F NN5+ .__&F &6!9D 0    J   s ?A''A9cSn[5nU(d [S5eUR5nURX0RX45 UR 5 UR 5 g!UR 5 f=f)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%rVrWrcommitr\)rrrr^r_rVs rr !BOMExploder._init_process_controlso  $% 67 7 &&(F NN5??H"G H        J   s =A..BrM part_father rev_fatherordercSn[5nU(d [S5eUR5nURUURX#XTUSUSUSUSUSUSUS US [ S [ S [ S [ S[ S[ S[ S[ S[ S[ S[ S45 UR 5 UR5 g!UR5 f=f)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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) rlSeqr0r?RevPartDescriptionShortDescriptionQtyPerUOMCoder3r4r5r6r7r8r9r:r;r<r=N)rmr%rVrWrrNrnr\) rrMrprqr-rrr^r_rVs rrFBOMExploder._insert_materials $% 67 7 &&(F NN5%&!18I3F*;!<+,hx.@(9BU "GJ$79O & (> @V()79+= wv0A #         J   s B7C((C: operationcnSn[5nU(d [S5eUR5nURUURX#XTUSUSUSSUSUSUS US [ S [ S [ 45 UR5 UR5 g!UR5 f=f) 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) rlrtr0OpCodeNOpDescrw EstProdHoursryr6r7) rmr%rVrWrr(rKrnr\) rr{rprqr-rrr^r_rVs rrBBOMExploder._insert_operations  $% 67 7 &&(F NN5%% )F"3Yx5Hi)95G+H.)9Y+?-(-(#         J   s A1B""B4processing_time_msc HSn[5nU(dgUR5nURUURURUR[ U5UR45 UR 5 UR 5 g!UR 5 f=f)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)rmrVrWrintrnr\)rrr^r_rVs rr$!BOMExploder._update_process_stats,s $%   &&(F NN5$//&'#         J   s A(BB! error_messagecSn[5nU(dgUR5nURX!UR45 UR 5 UR 5 g!UR 5 f=f)uMarca el proceso como erróneozx UPDATE BOM_Process_Control SET Status = 'Error', ErrorMessage = ? WHERE ProcessID = ? N)rmrVrWrrnr\)rrr^r_rVs rr&BOMExploder._mark_process_errorFsf  $%   &&(F NN5$//"B C        J   s =A$$A6c~Sn[5nU(d [S5eUR5nURU5 UR 5nU(aBUR Vs/sHoUSPM nn[ [Xd55UR5 $[S5es snf!UR5 f=f)zObtiene desde VPS z` SELECT LbrUnitCost, BurUnitCost FROM CostLbr rlrz"No se encontraron datos de CostLbr) rmr%rVrWrhrXrZr[r\)rr^r_rVrbr`ras rr!BOMExploder._get_pricingMO[s $% 67 7 &&(F NN5 !//#C393E3EF3E!93EFC-.     DEEG    sAB*&B%4B*B**B< pricingMOc[URS5=(d S5n[URS5=(d S5n[URS5=(d S5nX4-X5--nU$)Nrrr6r7)floatget)rrr{ est_hours lbr_unit_cost bur_unit_cost total_costs rrA$BOMExploder._get_totalcost_operationus`)--7<1= immM:?a@ immM:?a@ /I4MN rc Sn[5nU(d [S5eUR5nURX!SUSUSUSUSUS45 UR 5nU(aBUR Vs/sHofSPM nn[ [Xu55UR5 $[S5es snf!UR5 f=f)rRuj 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 rSrxr?rz#No se encontraron datos de PartTran) rUr%rVrWrhrXrZr[r\)rrMr^r_rVrbr`ras rrD!BOMExploder._get_pricingMaterialss @$% 67 7 &&(F NN5H#5x7I8T\K]_ghp_qs{}EtFHPQZH[#\ ]//#C393E3EF3E!93EFC-.     EFF G    sAC>B= C2CCr N)__name__ __module__ __qualname____firstlineno__rrrZr+rr"listrCr@rGr rFrBrr$r&r!rArD__static_attributes__rrr r s2"C"3"4"H232#2c2X[2h1s1c1d1f##s#t#JSS*cS&CSY\eh@4cs[^gj44*4$41d1rr new_ext_costrrc\Sn[5nU(dSSS.U(aUR5 $$UR5nSnURXPX45 URnUR 5 US:Xa#SSUSU3S.U(aUR5 $$S S US .U(aUR5 $$![ aJnU(aUR5 SS [U53S.sSnAU(aUR5 $$SnAff=f!U(aUR5 ff=f) 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)rr updated_rowszError en la base de datos: ) rmr\rVrWrowcountrnr%rollbackr)rrrr_rV update_query row_countr*s rget_status_manualrsCJ0#%  @X      O""$  |J%IJOO  > CJ<{[cZde&      7%             !23q6(;              s;B:AB:B:: D'D +D,D DDD+rc SnSnSnSn[5nU(dSSS.$UR5nURXU45 0n/nUR5Hn U Sn [ U S 5[ U S 5[ U S 5[ U S 5[ U S 5S.Xz'UR U [ U S 5[ U S 5[ U S 5[ U S 5[ U S 5S.5 M U(dSSS.U(aUR5 $$URX@45 /n Sn UR5Hgn U Sn U R U URU 05RSS5[ U S 5[ U S 5S.5 U [ U S 5- n Mi URX045 UR5n /nU HupnURU 05RSS5nURUUX 45 UR U U(a [ U5OSUSURU 05RSS5URU 05RSS5S.5 M UR5 [[SUR555S 5n[[SUR555S 5nSUU[U S 5UU UUS.U(aUR5 $$![aAnUR5 S[U5US.sSnAU(aUR5 $$SnAff=f!U(aUR5 ff=f)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 Frrrr) material_costmaterial_componentsoperation_costoperation_componentsr)partNum materialCostmaterialComponents operationCostoperationComponents totalCostz+No se encontraron componentes para calcularr)rcalculatedCostr componentsr2rr)r previousCostnewCost statusChangerrc3*# UH oSv M g7f)rNr.0vs r (calculate_total_costs..s)VCUaL/CUc3*# UH oSv M g7f)rNrrs rrrs(YFX+;)"6 1+#  &   % % '   $$&Z#    % % '     % % ' s7C(L!GL MM;M<M!MM!!M;rc |Sn[5nU(d [S5eUR5nURX1X XU45 UR 5nU(dSSS.U(aUR 5 $$USSb[ USS5OSn[U5S :aUS Sb[ US S5OSnS Xx-UUS .U(aUR 5 $$![a3n SS [U 53S.sSn A W(aUR 5 $$Sn A ff=f!W(aUR 5 ff=f) 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 registrosrrNrrT)rrrrzError en base de datos: ) rmr%rVrWrYr\rlenr) rrrr^r_rVresultsrrr*s rget_total_cost_for_parentrsJ E,#% DE E""$uxS]^_//#$/LM      18 1 0Igajm,s 14W1AgajQRmF_wqz!}-eh&7)+         P -Ec!fX+NOO      P      s7AC!9AC!! D+D;D<D!DD!!D;c $SnSnSn[5nU(dSS0$UR5nURX45 URVs/sHofSPM nnUR 5Vs/sHn[ [ Xx55PM n nURX 45 URVs/sHofSPM n nUR 5Vs/sHn[ [ X55PM n nURX045 URVs/sHofSPM n nUR5n U (a[ [ X55OSnU U US.UR5 $s snfs snfs snfs snfs snf!UR5 f=f) 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 = ? rrlrN)rLrIcontrol) rmrVrWrXrYrZr[rhr\)rquery_materialsquery_operations query_controlr_rVr`materials_columnsrbrLoperations_columnsrIcontrol_columns control_datars r get_bom_datars O M !J 344""$  65;5G5GH5G6AY5GHBH//BSTBS3T#/56BS T '76<6H6HI6HFQi6HIDJOODUVDUSd3178DU V }m4393E3EF3E!93EF( >J$s?9:PT#$  +ITJVG sM0E= E$E=/E) "E=/E.=E=E3/"E=E84E=$E==Fr^c Sn[5nU(d/$UR5nSUS3nURXU45 URVs/sHoUSPM nnUR 5Vs/sHn[ [ Xg55PM snUR5 $s snfs snf!UR5 f=f)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 %r)rUrVrWrXrYrZr[r\)r^ search_queryr_rV search_termr`rarbs r search_partsr-s L !J   ""$%l |;%?@+1+=+=>+=!9+=>39??3DE3DCS&'3DE  ?E s)7B+B!B+0B&B+! B++B=)rrconfigrrConsultas_SQL.conexionrrrrmrUr rrrrZrrrrrrrrrs  *``/N#N" ]]F 4E4s4c4n~(c~(d~(B3#333PT3B:S:T:xr