// ----------------------------
// 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>";
}
