# Archivo: GSYCGetBomGenset.py # Ruta: src/App/SupyCtrol_Module/GerenteSyC/UtilityMaterials/GSYCGetBomGenset.py # Lenguaje: Python con Flask from flask import jsonify, request from Consultas_SQL.conexion import get_connectionERP import datetime def funciones_GetBomGenset(app): @app.route('/api/get_bom', methods=['GET']) def obtener_arbol_bom(): jobNum_input = request.args.get('job_num') if not jobNum_input: return jsonify({"error": "Debe proporcionar un JobNum"}), 400 print(f"Obteniendo árbol BOM para JobNum {jobNum_input}") connection = get_connectionERP() if connection is None: print("Error: no se pudo establecer la conexión con la base de datos ERP") return jsonify({"error": "Conexión a base de datos fallida"}), 500 try: cursor = connection.cursor() cursor.execute( "SELECT PartNum, PartDescription, ProdQty, QtyCompleted, JobComplete, ClosedDate " "FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'", (jobNum_input,) ) row = cursor.fetchone() if not row: print(f"No se encontró el JobHead para JobNum {jobNum_input}") return jsonify({"error": "Trabajo no creado, favor de revisarlo en Seguimiento de trabajo"}), 404 part_num, part_desc, prod_qty, qty_completed, job_complete, close_date = row ref_date = close_date if job_complete else datetime.datetime.now() root_node = { "Nodo": "1", "JobNum": jobNum_input, "Seq": "", "PartNum": part_num, "Descripcion": part_desc, "QtyRequerida": float(prod_qty) if prod_qty else 0.0, "QtyEmitida": float(qty_completed) if qty_completed else 0.0, "Completado": bool(job_complete), "TLAMaterialCost": 0.0, "TLALaborCost": 0.0, "TLABurdenCost": 0.0, "TLASubcontractCost": 0.0, "TLAMtlBurCost": 0.0, "Tipo": "Ensamblaje raíz", "children": [] } def obtener_componentes(job_num, prefix, referencia, cost_factor=1.0, qty_factor=1.0): componentes = [] cursor = connection.cursor() cursor.execute( "SELECT jm.MtlSeq, jm.PartNum, jm.Description, jm.RequiredQty, jm.IssuedQty, jm.IssuedComplete, " "jm.RevisionNum, jm.AssemblySeq, p.TypeCode " "FROM Erp.JobMtl jm " "INNER JOIN Erp.Part p ON p.PartNum = jm.PartNum AND p.Company = jm.Company " "WHERE jm.JobNum = ? AND jm.Company = 'IGSA'", (job_num,) ) mtl_rows = cursor.fetchall() for idx, mtl in enumerate(mtl_rows, start=1): mtl_seq, mtl_part, mtl_desc, req_qty, issued_qty, issued_complete, rev_num, asm_seq, type_code = mtl node_id = f"{prefix}.{idx}" nodo_comp = { "Nodo": node_id, "JobNum": "", "Seq": int(mtl_seq) if mtl_seq is not None else "", "PartNum": mtl_part, "Descripcion": mtl_desc, "QtyRequerida": (float(req_qty) if req_qty else 0.0) * qty_factor, "QtyEmitida": (float(issued_qty) if issued_qty else 0.0) * qty_factor, "Completado": bool(issued_complete), "TLAMaterialCost": 0.0, "TLALaborCost": 0.0, "TLABurdenCost": 0.0, "TLASubcontractCost": 0.0, "TLAMtlBurCost": 0.0, "Tipo": "", "children": [] } if type_code == 'M': cursor_prod = connection.cursor() cursor_prod.execute( "SELECT JobNum FROM Erp.JobProd " "WHERE Company = 'IGSA' AND TargetJobNum = ? AND TargetAssemblySeq = ? AND TargetMtlSeq = ?", (job_num, asm_seq, mtl_seq) ) prod = cursor_prod.fetchone() if prod: child_job = prod[0] nodo_comp["Tipo"] = "Manufacturado cruzado" cursor_jh = connection.cursor() cursor_jh.execute( "SELECT JobComplete, ClosedDate FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'", (child_job,) ) child_info = cursor_jh.fetchone() child_ref = child_info[1] if child_info and child_info[0] else datetime.datetime.now() nodo_comp["JobNum"] = child_job cursor_cost = connection.cursor() cursor_cost.execute( "SELECT TLAMaterialCost, TLALaborCost, TLABurdenCost, TLASubcontractCost, TLAMtlBurCost " "FROM Erp.JobAsmbl WHERE JobNum = ? AND AssemblySeq = 0 AND Company = 'IGSA'", (child_job,) ) c_row = cursor_cost.fetchone() if c_row: c_mat, c_lab, c_bur, c_sub, c_mtlbur = c_row nodo_comp.update({ "TLAMaterialCost": float(c_mat) * cost_factor, "TLALaborCost": float(c_lab) * cost_factor, "TLABurdenCost": float(c_bur) * cost_factor, "TLASubcontractCost": float(c_sub) * cost_factor, "TLAMtlBurCost": float(c_mtlbur) * cost_factor }) nodo_comp["children"] = obtener_componentes(child_job, node_id, child_ref, cost_factor, qty_factor) else: cursor_pt = connection.cursor() cursor_pt.execute( "SELECT TOP 1 JobNum, TranDate FROM Erp.PartTran " "WHERE Company = 'IGSA' AND PartNum = ? AND RevisionNum = ? AND TranType = 'MFG-STK' " "AND TranDate <= ? ORDER BY TranDate DESC", (mtl_part, rev_num, referencia) ) pt = cursor_pt.fetchone() if pt: child_job = pt[0] nodo_comp["Tipo"] = "Manufacturado (sin cruce)" cursor_jh = connection.cursor() cursor_jh.execute( "SELECT ProdQty, JobComplete, ClosedDate FROM Erp.JobHead WHERE JobNum = ? AND Company = 'IGSA'", (child_job,) ) child_head = cursor_jh.fetchone() if child_head: child_prodqty, child_complete, child_close = child_head else: child_prodqty = 0 if child_prodqty: proration_factor = float(req_qty) / float(child_prodqty) else: proration_factor = 0.0 cursor_cost = connection.cursor() cursor_cost.execute( "SELECT TLAMaterialCost, TLALaborCost, TLABurdenCost, TLASubcontractCost, TLAMtlBurCost " "FROM Erp.JobAsmbl WHERE JobNum = ? AND AssemblySeq = 0 AND Company = 'IGSA'", (child_job,) ) c_row = cursor_cost.fetchone() if c_row: c_mat, c_lab, c_bur, c_sub, c_mtlbur = c_row nodo_comp.update({ "TLAMaterialCost": float(c_mat) * cost_factor * proration_factor, "TLALaborCost": float(c_lab) * cost_factor * proration_factor, "TLABurdenCost": float(c_bur) * cost_factor * proration_factor, "TLASubcontractCost": float(c_sub) * cost_factor * proration_factor, "TLAMtlBurCost": float(c_mtlbur) * cost_factor * proration_factor }) child_ref = child_close if child_complete and child_close else datetime.datetime.now() nodo_comp["JobNum"] = child_job nodo_comp["children"] = obtener_componentes(child_job, node_id, child_ref, cost_factor * proration_factor, proration_factor) else: nodo_comp["Tipo"] = "Manufacturado (sin cruce)" nodo_comp["JobNum"] = "No trazable" else: nodo_comp["Tipo"] = "Material" nodo_comp["JobNum"] = "" nodo_comp["children"] = [] componentes.append(nodo_comp) return componentes root_node["children"] = obtener_componentes(jobNum_input, "1", ref_date) return jsonify([root_node]) finally: connection.close() print("Conexión a la base de datos cerrada")