// ---------------------------- // AGREGAR HOJA DE INSTRUCCION // ---------------------------- if ($nr == 0 || empty($nr)) { // ---------------------------------------- // Detectar si tiene cargo Pre/On Carrier. // Si no tiene cargos entonces es el servicio es Fuera. // Generar folio de HI if ($chargePreOn == 0 || empty($chargePreOn)) { // Servicio Fuera de BL $arrDataFolio = generaFolio('F', $impoExpo); $folioHI = $arrDataFolio['folio']; $idServ = $arrDataFolio['idServicio']; } else { // Servicio Dentro de BL $arrDataFolio = generaFolio('D', $impoExpo); $folioHI = $arrDataFolio['folio']; $idServ = $arrDataFolio['idServicio']; } //echo "Folio HI: $folioHI <br>"; // ---------------------------- // BL // Buscar el Id_expo en SION // ---------------------------- $idBl = 0; $sqlF = "select Id_expo from EXPO where Bl='{$bl}' "; mysql_query($sqlF, $sionConect); $result = mysql_query($sqlF, $sionConect); while ($row = mysql_fetch_array($result)) { $idBl = $row['Id_expo']; }
function cargarHI($fechaA = "", $fechaA2 = "", $arrBlsMscu = "") { global $conn, $dbf; if (is_array($arrBlsMscu)) { $bls = implode(",", $arrBlsMscu); } if ($conn) { $idPrvS = ""; $costoONC = 0; unset($arrHI); $sql = "\n SELECT\n PT.Intermodal_Leg_id ,\n PT.Purchase_Order_Number as PO ,\n GC.Global_Name as Global_Name ,\n GC.Tax_Id as Tax_Id ,\n IC.Transport_Mode as Transport_Mode ,\n IC.IL_Estimated_Cost ,\n IC.ISC_Customer_Appointment_Date as ISC_Customer_Appointment_Date ,\n IC.Booking_Number as Booking_Number ,\n IC.Bill_Of_Lading_Number as BL ,\n IC.Vessel_Name as Vessel_Name ,\n IC.Voyage as Voyage ,\n IC.POL as POL ,\n IC.POD as POD ,\n IC.Container_Number as Container_Number ,\n IC.Req_Assign_Container as Req_Assign_Container ,\n IC.IC_Req_Assign_Container as IC_Req_Assign_Container ,\n IC.EQ_Type as EQ_Type ,\n -- IC.Tot_Container_Packages_Weight as Tot_Container_Packages_Weight,\n IC.CSC_Cargo_Weight_Kgs as CSC_Cargo_Weight_Kgs ,\n IC.LocalLogisticLocation as LocalLogisticLocation ,\n IC.Cust_Company as Cust_Company ,\n IC.DStart_Depot as DStart_Depot ,\n IC.DEnd_Depot as DEnd_Depot ,\n IC.Booking_Type_Desc as Booking_Type_Desc ,\n IC.Ex_Short_Description as Ex_Short_Description ,\n IC.Shipment_Method as Shipment_Method ,\n IC.Origin as Origin ,\n IC.FDest as FDest ,\n IC.I_E as I_E ,\n CH.Charge as ChargePreOn ,\n CH.Result_Std as Result_Std ,\n CH.Currency as Currency ,\n CH.Cargo_Shipment_id as Cargo_Shipment_id ,\n ACS.Company AS Cargo_Supplier ,\n ACS.Contact_Name as CS_Contact_Name ,\n ACS.Phone_Number AS CS_PHONE_NAME ,\n CF.Company AS AAF ,\n CF.Contact_Name AS AAF_CONTACT_NAME ,\n CF.Phone_Number AS AAF_PHONE_NAME ,\n CF.Email as AAF_Email ,\n CB.Company AS AAB ,\n CB.Contact_Name AS AAB_CONTACT_NAME ,\n CB.Phone_Number AS AAB_PHONE_NAME ,\n CB.Email as AAB_Email,\n CTX.Free_Text as Maniobras\n FROM\n Interlink_DFeed90. dfeed .FNTB_Intermodal_Customer (NULL, NULL) IC\n left join Interlink_Main90.dbo.Clause_Tx CTX (nolock) ON CTX.Clause_PK_id = IC.Intermodal_Leg_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ( 'CGSUP' ) ACS ON IC.Intermodal_Step_Cargo_id = ACS.Intermodal_Step_Cargo_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ( 'CUSTFWD') CF ON CF.Intermodal_Step_Cargo_id = ACS.Intermodal_Step_Cargo_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ( 'CUBKR') CB ON CB.Intermodal_Step_Cargo_id = ACS.Intermodal_Step_Cargo_id\n LEFT JOIN Interlink_Main90.dbo.Assoc_Doc_Company DC ( nolock) ON DC.Intermodal_Leg_id = IC.Intermodal_Leg_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany_Address GA ( nolock) ON GA.Company_Address_id = DC.Company_Address_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany GC ( nolock) ON GC .company_id = GA.Company_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Intermodal_EDI_Request_Truck (NULL, NULL) PT ON IC.Intermodal_Leg_id = PT.Intermodal_Leg_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Charge (1 , NULL) CH ON CH.Cargo_Shipment_Container_id = IC.Cargo_Shipment_Container_id\n AND CH . Charge_DTX_Code = (\n CASE\n WHEN CH .I_E = 'E' THEN\n 'PRE'\n ELSE\n 'ONC'\n END\n )\n WHERE\n "; if (!empty($bls)) { $sql .= " IC.Bill_Of_Lading_Number in ({$bls}) "; // IC .Booking_number = '190MR0000056' // AND IC.Booking_number = '190MC0000151' // AND ( IC.Bill_Of_Lading_Number='MSCUMA667627' OR IC.Booking_number = '190MR0000143' ) } elseif (!empty($fechaA)) { // $sql.=" IC.ISC_Customer_Appointment_Date >= '$fechaA' "; $sql .= " IC.ISC_Customer_Appointment_Date between '{$fechaA}' and '{$fechaA2}' "; } $sql .= "\n ORDER BY\n Intermodal_Leg_id\n "; //echo $sql; $rs = odbc_exec($conn, $sql); if (!$rs) { exit("Error en la consulta SQL"); } // Extraer campos while (odbc_fetch_row($rs)) { $rfc = odbc_result($rs, "Tax_Id"); $idPrvS = ""; // Detectar el id_prv del sion if (!empty($rfc)) { unset($result); $sqlF = "select id_proveedor from IM_PROVEEDOR where rfc='{$rfc}' "; $dbf->query($sqlF); while ($dbf->next_record()) { $idPrvS = $dbf->f('id_proveedor'); } } $pesoKgs = odbc_result($rs, "CSC_Cargo_Weight_Kgs"); $tipoMerca = odbc_result($rs, "Ex_Short_Description"); $tipoMerca = addslashes($tipoMerca); $tipoMerca = normaliza($tipoMerca); $po = odbc_result($rs, "PO"); $prv = odbc_result($rs, "Global_Name"); $prv = addslashes($prv); $bkg = odbc_result($rs, "Booking_Number"); $impoExpo = odbc_result($rs, "I_E"); $chargePreOn = odbc_result($rs, "ChargePreOn"); $bl = odbc_result($rs, "BL"); $bl = str_replace("MSCU", "", $bl); $conte = odbc_result($rs, "Container_Number"); $conteTipo = odbc_result($rs, "EQ_Type"); $transMode = odbc_result($rs, "Transport_Mode"); $IL_Estimated_Cost = odbc_result($rs, "IL_Estimated_Cost"); $ISC_Customer_Appointment_Date = odbc_result($rs, "ISC_Customer_Appointment_Date"); $Booking_Number = odbc_result($rs, "Booking_Number"); // $ml_bl= odbc_result($rs, "ml_bl"); $Vessel_Name = odbc_result($rs, "Vessel_Name"); $Voyage = odbc_result($rs, "Voyage"); $POL = odbc_result($rs, "POL"); $POD = odbc_result($rs, "POD"); $LocalLogisticLocation = odbc_result($rs, "LocalLogisticLocation"); $LocalLogisticLocation = addslashes($LocalLogisticLocation); $Cust_Company = odbc_result($rs, "Cust_Company"); $Cust_Company = addslashes($Cust_Company); $DStart_Depot = odbc_result($rs, "DStart_Depot"); $DStart_Depot = addslashes($DStart_Depot); $DEnd_Depot = odbc_result($rs, "DEnd_Depot"); $DEnd_Depot = addslashes($DEnd_Depot); $Shipment_Method = odbc_result($rs, "Shipment_Method"); $Origin = odbc_result($rs, "Origin"); $Origin = addslashes($Origin); $FDest = odbc_result($rs, "FDest"); $FDest = addslashes($FDest); $Charge = odbc_result($rs, "ChargePreOn"); $Charge_Cost = odbc_result($rs, "Result_Std"); $Currency = odbc_result($rs, "Currency"); if ($Currency == "USD") { $idMoneda = 1; } if ($Currency == "MXP") { $idMoneda = 2; } if ($Currency == "MXN") { $idMoneda = 2; } // Cargo Suplier y AA. $Cargo_Supplier = odbc_result($rs, "Cargo_Supplier"); $CS_Contact_Name = addslashes(odbc_result($rs, "CS_Contact_Name")); $CS_PHONE_NAME = odbc_result($rs, "CS_PHONE_NAME"); $AAF = odbc_result($rs, "AAF"); $AAF_CONTACT_NAME = odbc_result($rs, "AAF_CONTACT_NAME"); $AAF_CONTACT_NAME = addslashes($AAF_CONTACT_NAME); $AAF_PHONE_NAME = odbc_result($rs, "AAF_PHONE_NAME"); $AAF_Email = odbc_result($rs, "AAF_Email"); $AAB = addslashes(odbc_result($rs, "AAB")); $AAB_CONTACT_NAME = addslashes(odbc_result($rs, "AAB_CONTACT_NAME")); $AAB_CONTACT_NAME = addslashes($AAB_CONTACT_NAME); $AAB_PHONE_NAME = odbc_result($rs, "AAB_PHONE_NAME"); $AAB_Email = odbc_result($rs, "AAB_Email"); if ($impoExpo == "E") { $aaIns = $AAB; $aaContact = $AAB_CONTACT_NAME; $aaTel = $AAB_PHONE_NAME; $aaEmail = $AAB_Email; } else { $aaIns = $AAF; $aaContact = $AAF_CONTACT_NAME; $aaTel = $AAF_PHONE_NAME; $aaEmail = $AAF_Email; } $maniobras = odbc_result($rs, "Maniobras"); // ---------------------------- // Fecha del posicionamiento // ---------------------------- if (preg_match("/(\\d{4}-\\d{2}-\\d{2}) (.+)/", $ISC_Customer_Appointment_Date, $partsFP)) { $fecPos = $partsFP[1]; $horPos = $partsFP[2]; } // --------------------------------------------- // Comprobar en SION que no exista el registro. // Si existe, debe actualizar algunos datos. // No existe, entonces crear HI. $sqlS = "select id_instruccion from IM_INSTRUCCION where "; if ($impoExpo == "E") { $sqlS .= "ex_booking='{$bkg}' "; } else { $sqlS .= "bl='{$bl}' "; } $sqlS .= "and ml_proveedor='{$prv}' "; $dbf->query($sqlS); $nr = $dbf->num_rows(); // ---------------------------- // AGREGAR HOJA DE INSTRUCCION // ---------------------------- if ($nr == 0 || empty($nr)) { // ---------------------------------------- // Detectar si tiene cargo Pre/On Carrier. // Si no tiene cargos entonces es el servicio es Fuera. // Generar folio de HI if ($chargePreOn == 0 || empty($chargePreOn)) { // Servicio Fuera de BL $arrDataFolio = generaFolio('F', $impoExpo); $folioHI = $arrDataFolio['folio']; $idServ = $arrDataFolio['idServicio']; } else { // Servicio Dentro de BL $arrDataFolio = generaFolio('D', $impoExpo); $folioHI = $arrDataFolio['folio']; $idServ = $arrDataFolio['idServicio']; } echo "Folio HI: {$folioHI} <br>"; if (!empty($folioHI)) { // ---------------------------- // BL // Buscar el Id_expo en SION // ---------------------------- $idBl = 0; $sqlF = "select Id_expo from EXPO where Bl='{$bl}' "; $dbf->query($sqlF); while ($dbf->next_record()) { $idBl = $dbf->f('Id_expo'); } // Costo del PRE/ON Carrier if ($impoExpo == "E") { $costoONC = 0; $costoPRE = $Charge_Cost; $destino = $POL; $intermedio = $Origin; $id_moneda_tari_prv = 2; // Siempre es en pesos el costo unitario } else { $costoONC = $Charge_Cost; $costoPRE = 0; $destino = $POD; $intermedio = $FDest; $id_moneda_tari_prv = 2; // Siempre es en pesos el costo unitario } // --------------------------------------------- // GRABA LA INFO A SION // --------------------------------------------- if ($impoExpo == "I") { $POL = $POD; } $POL = str_replace("'", "", $POL); $POD = str_replace("'", "", $POD); $POD = str_replace("\n", "", $POD); $POD = str_replace("\r", "", $POD); $POD = addslashes($POD); $POD = normaliza($POD); //echo "<hr>$bl</hr>"; $sqlS = "\n insert into IM_INSTRUCCION (\n flg_msclink,\n origen_ins,\n tari_prv,id_moneda_tari_prv,\n fecha_pos,hora_pos,\n puerto_desc,\n tipo_merca,\n destino_ins,intermedio_ins,\n id_prv,folio_servicio,\n ml_proveedor,\n ml_proveedor_tax_id,\n ex_booking,\n bl,\n id_servicio,\n id_bl,\n ml_transport_mode,\n ex_barco,\n ex_viaje,\n ex_cliente,\n pre_carrier,\n on_carrier,\n id_moneda_on_carrier,\n id_moneda_pre_carrier,\n\n cli_nombre,cli_contacto,cli_telefono,\n aa_ins,aa_contacto,aa_tel,aa_email,\n\n IL_Estimated_Cost,\n ISC_Customer_Appointment_Date,\n Vessel_Name,\n Voyage,\n POL,\n POD,\n LocalLogisticLocation,\n Cust_Company,\n DStart_Depot,\n DEnd_Depot,\n Shipment_Method,\n Origin,\n FDest,\n Charge,\n Charge_Cost,\n Charge_Cost_Cur,\n ml_maniobras\n )\n values\n (\n '1',\n '{$POL}',\n '{$IL_Estimated_Cost}','{$id_moneda_tari_prv}',\n '{$fecPos}','{$horPos}',\n '{$POD}',\n '{$tipoMerca}',\n '{$destino}','{$intermedio}',\n '{$idPrvS}','{$folioHI}',\n '{$prv}','{$rfc}',\n '{$bkg}','{$bl}','{$idServ}',\n '{$idBl}','{$transMode}',\n '{$Vessel_Name}','{$Voyage}','{$Cust_Company}',\n '{$costoPRE}','{$costoONC}','{$idMoneda}','{$idMoneda}',\n '{$Cust_Company}','{$CS_Contact_Name}','{$CS_PHONE_NAME}',\n '{$aaIns}','{$aaContact}','{$aaTel}','{$aaEmail}',\n '{$IL_Estimated_Cost}',\n '{$ISC_Customer_Appointment_Date}',\n '{$Vessel_Name}',\n '{$Voyage}',\n '{$POL}',\n '{$POD}',\n '{$LocalLogisticLocation}',\n '{$Cust_Company}',\n '{$DStart_Depot}',\n '{$DEnd_Depot}',\n '{$Shipment_Method}',\n '{$Origin}',\n '{$FDest}',\n '{$Charge}',\n '{$Charge_Cost}',\n '{$Currency}',\n '{$maniobras}'\n )\n "; if ($dbf->query($sqlS)) { echo "[ Hoja de Instrucción ][ <font color=green><b>Cargado</b></font> ] {$folioHI}<br>"; } } } else { // Actualizar ... PENDIENTE. // echo "[<font color=blue><b>Hoja de Instr. en SION Existente</b></font>] $bl $bkg <br>"; } if (!empty($folioHI)) { // ------------------------------ // AGREGAR PO X CONTENEDOR // ------------------------------ // Id_contenedor de SION // Si no existe entonces agregar // ------------------------------ if (empty($conte)) { // Si no existe el contenedor, entonces asignar contenedor ficticio. $conte = "ZZZZ0000000"; } $sqlF = "select id_instruccion FROM IM_INSTRUCCION where folio_servicio='{$folioHI}' "; $dbf->query($sqlF); while ($dbf->next_record()) { $idHI = $dbf->f('id_instruccion'); if (isset($arrHI) && is_array($arrHI)) { if (!in_array($idHI, $arrHI)) { $arrHI[] = $idHI; } } else { $arrHI[] = $idHI; } } unset($idConte); $sqlF = "select id_contenedor FROM CONTENEDOR where numero='{$conte}' "; $dbf->query($sqlF); while ($dbf->next_record()) { $idConte = $dbf->f('id_contenedor'); } if (empty($idConte) || $idConte == 0) { // Tipo de contenedor $sqlF2 = "select id_equipo from EQUIPO where equipo='{$conteTipo}'"; $dbf->query($sqlF2); while ($dbf->next_record()) { $idEq = $dbf->f('id_equipo'); } // Agregar contenedor $sqlF2 = "insert into CONTENEDOR (numero,id_equipo) values ('{$conte}','{$idEq}')"; $dbf->query($sqlF2); // Consultar el id_contenedor $sqlF2 = "select id_contenedor FROM CONTENEDOR where numero='{$conte}' "; $dbf->query($sqlF2); while ($dbf->next_record()) { $idConte = $dbf->f('id_contenedor'); } } // Comprobar que no duplique la info. if (isset($idHI) && $idHI > 0) { $sqlF = "select id_rel_inst_cont\n from IM_REL_INST_CONT\n where id_contenedor='{$idConte}' and id_instruccion='{$idHI}' and po_num='{$po}'\n "; $dbf->query($sqlF); $nrX = $dbf->num_rows(); if ((empty($nrX) || $nrX == 0) && $idHI > 0) { $sqlF = "\n insert into IM_REL_INST_CONT (\n id_contenedor,id_instruccion,po_num,peso_kgs,pickup_at,deliver_at\n ) values (\n '{$idConte}','{$idHI}','{$po}','{$pesoKgs}','{$DStart_Depot}','{$DEnd_Depot}'\n )\n "; $dbf->query($sqlF); } // ----------------------------------------------------- } // ------------------------------------- // AGREGAR "TOMA DE CONTENEDOR" // ------------------------------------- } } } // Sumar el PESO de cada Hoja de instruccion. if (isset($arrHI) && is_array($arrHI)) { foreach ($arrHI as $idHojaIns) { $sql = "select sum(peso_kgs) as totalPeso from IM_REL_INST_CONT where id_instruccion='{$idHojaIns}' "; $dbf->query($sql); while ($dbf->next_record()) { $totalPeso = $dbf->f('totalPeso'); } // Actualizar el campo. $totalPeso = $totalPeso . " Kgs."; $sql = "update IM_INSTRUCCION set peso='{$totalPeso}' where id_instruccion='{$idHojaIns}' "; $dbf->query($sql); } } echo "[ Fin ] del proceso de Intermodal : Cargar hojas de instrucción <p>"; }