# Optimized Python Code
# Archivo: DMbreakersSQL.py
# Ruta: src\Consultas_SQL\SupYCtrol\DataMaster\Tablas\SYCTabla1SQL.py
# Lenguaje: Python
from Consultas_SQL.conexion import get_connectionTEST101
def get_Consulta():
    """Obtiene los Breakers desde la base de datos."""
    query = """
     SELECT
        isnull (ScoreV2temp.OrderNum, '')as OrderNum,
        isnull (ScoreV2temp.Departamento, '')as Departamento,
        isnull (ScoreV2temp.Vendedor, '')as Vendedor,
        isnull (ScoreV2temp.Name, '')as Name,
        isnull (ScoreV2temp.TotalLines, '')as TotalLines,
        isnull (ScoreV2temp.OrderLine, '')as OrderLine,
        isnull (ScoreV2temp.PartNum, '')as PartNum,
        isnull (ScoreV2temp.LineDesc, '')as LineDesc,
        isnull (ScoreV2temp.Capacidad, '')as Capacidad,
        isnull (ScoreV2temp.Voltaje, '')as Voltaje,
        isnull (ScoreV2temp.Tipo, '')as Tipo,
        isnull (ScoreV2temp.RevisionNum, '')as RevisionNum,
        isnull (ScoreV2temp.ProdCode, '')as ProdCode,
        isnull (ScoreV2temp.DecriptionProd, '')as DecriptionProd,
        isnull (ScoreV2temp.Caseta, '')as Caseta,
        isnull (ScoreV2temp.Tanque, '')as Tanque,
        isnull (ScoreV2temp.OrderDate, '')as OrderDate,
        isnull (ScoreV2temp.NeedByDate, '')as NeedByDate,
        isnull (ScoreV2temp.LiberacionCXP, '')as LiberacionCXP,
        isnull (ScoreV2temp.[OrderNum&Line], '')as [OrderNum&Line],
        isnull (ScoreV2temp.ProjectID, '')as ProjectID,
        isnull (ScoreV2temp.JobNum2, '')as JobNum2,
        isnull (ScoreV2temp.Revision_OV, '')as Revision_OV,
        isnull (ScoreV2temp.Revision_Project, '')as Revision_Project,
        isnull (ScoreV2temp.Revision_Job, '')as Revision_Job,
        isnull (ScoreV2temp.FechaVentas, '')as FechaVentas,
        isnull (ScoreV2temp.ComentarioSyC, '')as ComentarioSyC,
        isnull (ScoreV2temp.Chk, '')as Chk,
        isnull (ScoreV2temp.FechaSyC, '')as FechaSyC,
        isnull (ScoreV2temp.FechaSimulaciones, '')as FechaSimulaciones,
        isnull (ScoreV2temp.ConsideradaPreasignacion, '')as ConsideradaPreasignacion,
        isnull (ScoreV2temp.ConsideradaMateriales, '')as ConsideradaMateriales,
        isnull (ScoreV2temp.FechaProducción, '')as FechaProducción,
        isnull (ScoreV2temp.ComentarioProducción, '')as ComentarioProducción,
        isnull (ScoreV2temp.AvanceProducción, '')as AvanceProducción,
        isnull (ScoreV2temp.FechaPlaneación, '')as FechaPlaneación,
        isnull (ScoreV2temp.EstadoFecha, '')as EstadoFecha,
        isnull (ScoreV2temp.ComentarioPlaneación, '')as ComentarioPlaneación,
        isnull (ScoreV2temp.FechaMG, '')as FechaMG,
        isnull (ScoreV2temp.FechaMat, '')as FechaMat,
        isnull (ScoreV2temp.AvanceDeSurtimiento, '')as AvanceDeSurtimiento,
        isnull (ScoreV2temp.MaterialSinSurtir, '')as MaterialSinSurtir,
        isnull (ScoreV2temp.AvisoDeTerminacion, '')as AvisoDeTerminacion,
        isnull (ScoreV2temp.TerminadoConFaltante, '')as TerminadoConFaltante,
        isnull (ScoreV2temp.FechaActualización, '')as FechaActualización,
        isnull (ScoreV2temp.MaterialFaltante, '')as MaterialFaltante,
        isnull (ScoreV2temp.ValidaciónFisica, '')as ValidaciónFisica,
        isnull (ScoreV2temp.ResultadoValidación, '')as ResultadoValidación,
        isnull (ScoreV2temp.FechaDeCierre, '')as FechaDeCierre,
        isnull (ScoreV2temp.ComentarioCalidad, '')as ComentarioCalidad,
        isnull (ScoreV2temp.InsumosDemandados, '')as InsumosDemandados,
        isnull (ScoreV2temp.InsumosEmitidos, '')as InsumosEmitidos,
        isnull (ScoreV2temp.AvaceDeEmisiones, '')as AvaceDeEmisiones,
        isnull (ScoreV2temp.FechaDeTermino, '')as FechaDeTermino,
        isnull (ScoreV2temp.AvanceManufacturaSyC, '')as AvanceManufacturaSyC,
        isnull (ScoreV2temp.AvanceEnsambleSyC, '')as AvanceEnsambleSyC,
        isnull (ScoreV2temp.ComentarioSupervisión, '')as ComentarioSupervisión,
        isnull (ScoreV2temp.EnFilaPruebasSyC, '')as EnFilaPruebasSyC,
        isnull (ScoreV2temp.PendientesCalidSyC, '')as PendientesCalidSyC,
        isnull (ScoreV2temp.PartNum_M, '')as PartNum_M,
        isnull (ScoreV2temp.Description_M, '')as Description_M,
        isnull (ScoreV2temp.PartClass_M, '')as PartClass_M,
        isnull (ScoreV2temp.RefCategory_M, '')as RefCategory_M,
        isnull (ScoreV2temp.QtyPer_M, '')as QtyPer_M,
        isnull (ScoreV2temp.IssuedQty_M, '')as IssuedQty_M,
        isnull (ScoreV2temp.Demandado_M, '')as Demandado_M,
        isnull (ScoreV2temp.OnhandQty_M, '')as OnhandQty_M,
        isnull (ScoreV2temp.Preasignado_M, '')as Preasignado_M,
        isnull (ScoreV2temp.NoSerie_M, '')as NoSerie_M,
        isnull (ScoreV2temp.Faltante_M, '')as Faltante_M,
        isnull (ScoreV2temp.En_PO_M, '')as En_PO_M,
        isnull (ScoreV2temp.NoPO_M, '')as NoPO_M,
        isnull (ScoreV2temp.EnRequisicion_M, '')as EnRequisicion_M,
        isnull (ScoreV2temp.NoRequisicion_M, '')as NoRequisicion_M,
        isnull (ScoreV2temp.Alternativa_M, '')as Alternativa_M,
        isnull (ScoreV2temp.En_PO_Altern_M, '')as En_PO_Altern_M,
        isnull (ScoreV2temp.NoPO_Alern_M, '')as NoPO_Alern_M,
        isnull (ScoreV2temp.Req_M, '')as Req_M,
        isnull (ScoreV2temp.Comment_M, '')as Comment_M,
        isnull (ScoreV2temp.FechaReq_M, '')as FechaReq_M,
        isnull (ScoreV2temp.PO_M, '')as PO_M,
        isnull (ScoreV2temp.Cantidad_Pedida_M, '')as Cantidad_Pedida_M,
        isnull (ScoreV2temp.Estatus_M, '')as Estatus_M,
        isnull (ScoreV2temp.Fecha_Llegada_M, '')as Fecha_Llegada_M,
        isnull (ScoreV2temp.PartNum_G, '')as PartNum_G,
        isnull (ScoreV2temp.Description_G, '')as Description_G,
        isnull (ScoreV2temp.PartClass_G, '')as PartClass_G,
        isnull (ScoreV2temp.RefCategory_G, '')as RefCategory_G,
        isnull (ScoreV2temp.QtyPer_G, '')as QtyPer_G,
        isnull (ScoreV2temp.IssuedQty_G, '')as IssuedQty_G,
        isnull (ScoreV2temp.Demandado_G, '')as Demandado_G,
        isnull (ScoreV2temp.OnhandQty_G, '')as OnhandQty_G,
        isnull (ScoreV2temp.Preasignado_G, '')as Preasignado_G,
        isnull (ScoreV2temp.NoSerie_G, '')as NoSerie_G,
        isnull (ScoreV2temp.Faltante_G, '')as Faltante_G,
        isnull (ScoreV2temp.En_PO_G, '')as En_PO_G,
        isnull (ScoreV2temp.NoPO_G, '')as NoPO_G,
        isnull (ScoreV2temp.EnRequisicion_G, '')as EnRequisicion_G,
        isnull (ScoreV2temp.NoRequisicion_G, '')as NoRequisicion_G,
        isnull (ScoreV2temp.Alternativa_G, '')as Alternativa_G,
        isnull (ScoreV2temp.En_PO_Altern_G, '')as En_PO_Altern_G,
        isnull (ScoreV2temp.NoPO_Alern_G, '')as NoPO_Alern_G,
        isnull (ScoreV2temp.Req_G, '')as Req_G,
        isnull (ScoreV2temp.Comment_G, '')as Comment_G,
        isnull (ScoreV2temp.FechaReq_G, '')as FechaReq_G,
        isnull (ScoreV2temp.PO_G, '')as PO_G,
        isnull (ScoreV2temp.Cantidad_Pedida_G, '')as Cantidad_Pedida_G,
        isnull (ScoreV2temp.Estatus_G, '')as Estatus_G,
        isnull (ScoreV2temp.Fecha_Llegada_G, '')as Fecha_Llegada_G,
        isnull (ScoreV2temp.PartNum_T, '')as PartNum_T,
        isnull (ScoreV2temp.Description_T, '')as Description_T,
        isnull (ScoreV2temp.PartClass_T, '')as PartClass_T,
        isnull (ScoreV2temp.RefCategory_T, '')as RefCategory_T,
        isnull (ScoreV2temp.QtyPer_T, '')as QtyPer_T,
        isnull (ScoreV2temp.IssuedQty_T, '')as IssuedQty_T,
        isnull (ScoreV2temp.OnhandQty_T, '')as OnhandQty_T,
        isnull (ScoreV2temp.PartNum_R, '')as PartNum_R,
        isnull (ScoreV2temp.Description_R, '')as Description_R,
        isnull (ScoreV2temp.PartClass_R, '')as PartClass_R,
        isnull (ScoreV2temp.RefCategory_R, '')as RefCategory_R,
        isnull (ScoreV2temp.QtyPer_R, '')as QtyPer_R,
        isnull (ScoreV2temp.IssuedQty_R, '')as IssuedQty_R,
        isnull (ScoreV2temp.OnhandQty_R, '')as OnhandQty_R,
        isnull (ScoreV2temp.ComentarioLINE, '')as ComentarioLINE
		
    FROM
        ScoreV2temp
    """
    try:
        conn = get_connectionTEST101()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        
        return [
            {
                "OrderNum": row[0],
                "Departamento": row[1],
                "Vendedor": row[2],
                "Name": row[3],
                "TotalLines": row[4],
                "OrderLine": row[5],
                "PartNum": row[6],
                "LineDesc": row[7],
                "Capacidad": row[8],
                "Voltaje": row[9],
                "Tipo": row[10],
                "RevisionNum": row[11],
                "ProdCode": row[12],
                "DecriptionProd": row[13],
                "Caseta": row[14],
                "Tanque": row[15],
                "OrderDate": row[16],
                "NeedByDate": row[17],
                "LiberacionCXP": row[18],
                "OrderNumAndLine": row[19],
                "ProjectID": row[20],
                "JobNum2": row[21],
                "Revision_OV": row[22],
                "Revision_Project": row[23],
                "Revision_Job": row[24],
                "FechaVentas": row[25],
                "ComentarioSyC": row[26],
                "Chk": row[27],
                "FechaSyC": row[28],
                "FechaSimulaciones": row[29],
                "ConsideradaPreasignacion": row[30],
                "ConsideradaMateriales": row[31],
                "FechaProducción": row[32],
                "ComentarioProducción": row[33],
                "AvanceProducción": row[34],
                "FechaPlaneación": row[35],
                "EstadoFecha": row[36],
                "ComentarioPlaneación": row[37],
                "FechaMG": row[38],
                "FechaMat": row[39],
                "AvanceDeSurtimiento": row[40],
                "MaterialSinSurtir": row[41],
                "AvisoDeTerminacion": row[42],
                "TerminadoConFaltante": row[43],
                "FechaActualización": row[44],
                "MaterialFaltante": row[45],
                "ValidaciónFisica": row[46],
                "ResultadoValidación": row[47],
                "FechaDeCierre": row[48],
                "ComentarioCalidad": row[49],
                "InsumosDemandados": row[50],
                "InsumosEmitidos": row[51],
                "AvaceDeEmisiones": row[52],
                "FechaDeTermino": row[53],
                "AvanceManufacturaSyC": row[54],
                "AvanceEnsambleSyC": row[55],
                "ComentarioSupervisión": row[56],
                "EnFilaPruebasSyC": row[57],
                "PendientesCalidSyC": row[58],
                "PartNum_M": row[59],
                "Description_M": row[60],
                "PartClass_M": row[61],
                "RefCategory_M": row[62],
                "QtyPer_M": row[63],
                "IssuedQty_M": row[64],
                "Demandado_M": row[65],
                "OnhandQty_M": row[66],
                "Preasignado_M": row[67],
                "NoSerie_M": row[68],
                "Faltante_M": row[69],
                "En_PO_M": row[70],
                "NoPO_M": row[71],
                "EnRequisicion_M": row[72],
                "NoRequisicion_M": row[73],
                "Alternativa_M": row[74],
                "En_PO_Altern_M": row[75],
                "NoPO_Alern_M": row[76],
                "Req_M": row[77],
                "Comment_M": row[78],
                "FechaReq_M": row[79],
                "PO_M": row[80],
                "Cantidad_Pedida_M": row[81],
                "Estatus_M": row[82],
                "Fecha_Llegada_M": row[83],
                "PartNum_G": row[84],
                "Description_G": row[85],
                "PartClass_G": row[86],
                "RefCategory_G": row[87],
                "QtyPer_G": row[88],
                "IssuedQty_G": row[89],
                "Demandado_G": row[90],
                "OnhandQty_G": row[91],
                "Preasignado_G": row[92],
                "NoSerie_G": row[93],
                "Faltante_G": row[94],
                "En_PO_G": row[95],
                "NoPO_G": row[96],
                "EnRequisicion_G": row[97],
                "NoRequisicion_G": row[98],
                "Alternativa_G": row[99],
                "En_PO_Altern_G": row[100],
                "NoPO_Alern_G": row[101],
                "Req_G": row[102],
                "Comment_G": row[103],
                "FechaReq_G": row[104],
                "PO_G": row[105],
                "Cantidad_Pedida_G": row[106],
                "Estatus_G": row[107],
                "Fecha_Llegada_G": row[108],
                "PartNum_T": row[109],
                "Description_T": row[110],
                "PartClass_T": row[111],
                "RefCategory_T": row[112],
                "QtyPer_T": row[113],
                "IssuedQty_T": row[114],
                "OnhandQty_T": row[115],
                "PartNum_R": row[116],
                "Description_R": row[117],
                "PartClass_R": row[118],
                "RefCategory_R": row[119],
                "QtyPer_R": row[120],
                "IssuedQty_R": row[121],
                "OnhandQty_R": row[122],
                "ComentarioLINE": row[123]

            } for row in results]
    except Exception as e:
        print(f"[Error inesperado] Error obteniendo breakers: {e}")
        return []
    finally:
        if conn:
            conn.close()
            



