<?  session_start();
set_time_limit(0);
//$self = $_SERVER['PHP_SELF']; //Obtenemos la página en la que nos encontramos
function con_atm(){
 
  	$dbcnx = mysqli_connect("35.225.233.105:3306", "root", "O7I=)O%IwT]o","intralog_veracruz") or die ("no se conecto a intralog_veracruz") ;//"sFTo3DmEC965vw"34.68.7.24
    return $dbcnx; 
}
$link=con_atm();

ini_set("display_errors",1);
include("../../comun/zfunciones.php"); 
include("../comun/funciones.php");

$tipo_fecha='';
$result = '';
$fecuno = "";
$fecdos = "";

function hora_24($hora){
$hora=$hora*1;
$ahora[1]='13';
$ahora[2]='14';
$ahora[3]='15';
$ahora[4]='16';
$ahora[5]='17';
$ahora[6]='18';
$ahora[7]='19';
$ahora[8]='20';
$ahora[9]='21';
$ahora[10]='22';	
$ahora[11]='23';
$ahora[12]='00';
$ahora['00']='00';
return $ahora[$hora];
}

function formatea_hora($fecha2,$hora){
	if(strpos($fecha2  ,'PM')!==false){
		$hora = hora_24($hora);
	}else{
		$hora = $hora;
	}
return $hora;
}

function formateo_fecha($fecha2){
$fecha2=strtoupper($fecha2);	
$anio = substr($fecha2,0,4); // echo 'anio '. $anio;
$mes = substr($fecha2,5,2); //echo 'mes '. $mes.' ';
$dia = substr($fecha2,8,2); //echo 'dia '. $dia.' ';

$hora = substr($fecha2,11,2); //echo 'hora '. $hora.'  xxx ';
$hora = formatea_hora($fecha2,$hora);
$min = substr($fecha2,14,2); //echo 'min '. $min.'  xxx ';
$seg = substr($fecha2,17,2); //echo 'seg '. $seg.'  xxx ';

$array = array($anio,$mes,$dia,$hora,$min,$seg);
return vsprintf("%02d-%02d-%02d %02d:%02d:%02d", $array);	
}
$empresa = "";
if(strpos($_SERVER['REQUEST_URI'],'ATM')!==false){
$empresa = 'ALTAMIRA';
}elseif(strpos($_SERVER['REQUEST_URI'],'ZLO')!==false){
$empresa='MANZANILLO';
}elseif(strpos($_SERVER['REQUEST_URI'],'VER')!==false){
$empresa='VERACRUZ';
}
$OFICINA['ALTAMIRA']='LPA';
$OFICINA['MANZANILLO']='LPM';
$OFICINA['VERACRUZ']='LPV';

function periodo($fecha){
	
$mes=substr($fecha,5,2);
$anio=	substr($fecha,0,4);
$mes_texto["01"]='ENERO';
$mes_texto["02"]='FEBRERO';
$mes_texto["03"]='MARZO';
$mes_texto["04"]='ABRIL';
$mes_texto["05"]='MAYO';
$mes_texto["06"]='JUNIO';
$mes_texto["07"]='JULIO';
$mes_texto["08"]='AGOSTO';
$mes_texto["09"]='SEPTIEMBRE';
$mes_texto["10"]='OCTUBRE';
$mes_texto["11"]='NOVIEMBRE';
$mes_texto["12"]='DICIEMBRE';
 

return $mes_texto[$mes].' '.$anio;
}

function anio($fecha){
$mes = substr($fecha,5,2);
$anio =	substr($fecha,0,4);
return $anio;
}


function ejecutarSQL($_rutaArchivo, $_conexionDB)
{
  $queries = explode(";", file_get_contents($_rutaArchivo));
  foreach($queries as $query)
  {
    if($query != "")
    {
      $_conexionDB->query($query);
    }
  }
}

$currentDateTimeI = new DateTime('now - 60 day'); 
$currentDateTimeF = new DateTime('now'); 
$currentDateI = $currentDateTimeI->format('Y-m-d'); 
$currentDateF = $currentDateTimeF->format('Y-m-d'); 
$currentDateFb = $currentDateTimeF->format('Y-m-d'); 
$yeari = substr($currentDateI, 0, 4);
$yearf = substr($currentDateF, 0, 4);
$monti = substr($currentDateI, 5, 2);
$montf = substr($currentDateF, 5, 2);
$currentDateI = $yeari."-".$monti."-01";
$currentDateIa = "2023-08-01"; 
$currentDateFa = "2023-08-01"; 
$mesi = (int)$monti;
$anii = (int)$yeari;
$camp01 = "";$camp02 = "";$camp03 = "";$camp04 = "";$camp05 = "";$camp06 = "";$camp07 = "";$camp08 = "";$camp09 = "";$camp10 = "";
$camp11 = "";$camp12 = "";$camp13 = "";$camp14 = "";$camp15 = "";$camp16 = "";$camp17 = "";$camp18 = "";$camp19 = "";$camp20 = "";
$camp21 = "";$camp22 = "";$camp23 = "";$camp24 = "";$camp25 = "";$camp26 = "";$camp27 = "";$camp28 = "";$camp29 = "";$camp30 = "";
$camp31 = "";$camp32 = "";$camp33 = "";$camp34 = "";$camp35 = "";$camp36 = "1";
$movguarda = "";
$movactual = "";
$movguardat = "insert into rs_dashboard_contenedor(anio, periodo, oficina, ref_puerto, ref_cliente, tipo_carga, cliente, destino_final, puerto_carga, puerto_descarga, naviera, buque, fecha_atraque, eta_pol, zarpe, almacen, terminal, contenedor, sello, dimension, tipo, bultos_totales, total_peso, total_cbm, cantidad_partidas, linea, booking_linea, imo, procedencia_vacio, fecha_toma_vacio, fecha_documentacion, fecha_consolidacion, fecha_liberacion, tara, total_pesos, bandera) values";
$movguardae = ";";

for($a=0;$a<3;$a++){
$movguarda = "";
$movactual = "";
	
if($a < 2){
if($mesi == 1){$currentDateI = $yeari."-01-01";$currentDateF = $yeari."-01-31";}
elseif($mesi == 2){$currentDateI = $yeari."-02-01";$currentDateF = $yeari."-02-29";}
elseif($mesi == 3){$currentDateI = $yeari."-03-01";$currentDateF = $yeari."-03-31";}
elseif($mesi == 4){$currentDateI = $yeari."-04-01";$currentDateF = $yeari."-04-30";}
elseif($mesi == 5){$currentDateI = $yeari."-05-01";$currentDateF = $yeari."-05-31";}
elseif($mesi == 6){$currentDateI = $yeari."-06-01";$currentDateF = $yeari."-06-30";}
elseif($mesi == 7){$currentDateI = $yeari."-07-01";$currentDateF = $yeari."-07-31";}
elseif($mesi == 8){$currentDateI = $yeari."-08-01";$currentDateF = $yeari."-08-31";}
elseif($mesi == 9){$currentDateI = $yeari."-09-01";$currentDateF = $yeari."-09-30";}
elseif($mesi == 10){$currentDateI = $yeari."-10-01";$currentDateF = $yeari."-10-31";}
elseif($mesi == 11){$currentDateI = $yeari."-11-01";$currentDateF = $yeari."-11-30";}
elseif($mesi == 12){$currentDateI = $yeari."-12-01";$currentDateF = $yeari."-12-31";}
if($a == 0){$fecuno = $currentDateI;}
}
else{
if($mesi == 1){$currentDateI = $yearf."-01-01";$currentDateF = $currentDateFb;}
elseif($mesi == 2){$currentDateI = $yearf."-02-01";$currentDateF = $currentDateFb;}
elseif($mesi == 3){$currentDateI = $yearf."-03-01";$currentDateF = $currentDateFb;}
elseif($mesi == 4){$currentDateI = $yearf."-04-01";$currentDateF = $currentDateFb;}
elseif($mesi == 5){$currentDateI = $yearf."-05-01";$currentDateF = $currentDateFb;}
elseif($mesi == 6){$currentDateI = $yearf."-06-01";$currentDateF = $currentDateFb;}
elseif($mesi == 7){$currentDateI = $yearf."-07-01";$currentDateF = $currentDateFb;}
elseif($mesi == 8){$currentDateI = $yearf."-08-01";$currentDateF = $currentDateFb;}
elseif($mesi == 9){$currentDateI = $yearf."-09-01";$currentDateF = $currentDateFb;}
elseif($mesi == 10){$currentDateI = $yearf."-10-01";$currentDateF = $currentDateFb;}
elseif($mesi == 11){$currentDateI = $yearf."-11-01";$currentDateF = $currentDateFb;}
elseif($mesi == 12){$currentDateI = $yearf."-12-01";$currentDateF = $currentDateFb;}
$fecdos = $currentDateF;
}
if($mesi == 12){$mesi = 1; $anii = $anii + 1; $yeari = $anii;}
else{
$mesi = $mesi + 1;}

echo $currentDateI." - ".$currentDateF."<br>";

mysqli_query($link,"DELETE FROM rs_dashboard_contenedor WHERE zarpe between '".$currentDateI."' and '".$currentDateF."'");
	
	$result= mysqli_query($link,"SELECT * FROM datosbooking db FORCE INDEX (i_ets_programado) 
								  WHERE 1 = 1 
								    AND db.zarpe>='".$currentDateI."' and db.zarpe<='".$currentDateF."' 
								    AND db.id>1400 ORDER BY db.id desc");

echo mysqli_error($link);
 

if ( mysqli_num_rows($result)>0){

echo 'Cantidad de filas: '. mysqli_num_rows($result).'<br><br>';

$i=0;$j="0";
?>
<?
$gt=0;
$x=1;

while ($row = mysqli_fetch_array($result)) {
	
$queryImo=mysqli_query($link ,"select imo from detallebooking where `referencia_cliente` = '".$row[referencia_cliente]."'  ORDER BY id");
$IMO1='';
while($rqImo=mysqli_fetch_array($queryImo)){
$IMO1.=$rqImo[0].', ';
}
$IMO1=substr($IMO1,0,strlen($IMO1)-2);
$cont=$row[contenedor];
 

$bultos=0;
$Tvol=0;
$cantidad_partidas=0;
$tweight=0;
 

$A =	mysqli_query($link ,"select D.* from detailcargaexpint D inner join hblexpint H on  H.referencia = '".$row[referencia_cliente]."' and H.idhbl>10000 and D.id>40000 and H.idhbl = D.id_bl and H.status='OK' AND (H.tipo = 'HBL' or (H.tipo = 'MBL' and NOT EXISTS (SELECT * FROM `detailcargaexpint` WHERE `bookingno` LIKE H.booking  and tipo_bl='HBL')))");
$cantidad_partidas=mysqli_num_rows($A);
while($rdA=mysqli_fetch_array($A)){
	$tweight+=$rdA[weight];
	$bultos +=$rdA[bulk];
}

$dimen1 =	mysqli_query($link ,"select D.* from dimensiones D inner join hblexpint H on  H.referencia = '".$row[referencia_cliente]."' and D.id>25000 and H.idhbl>10000 and H.idhbl = D.id_partida and H.status='OK' AND (H.tipo = 'HBL' or (H.tipo = 'MBL' and NOT EXISTS (SELECT * FROM `detailcargaexpint` WHERE `bookingno` LIKE H.booking  and tipo_bl='HBL')))");

if(mysqli_num_rows($dimen1)>0){
   while($rd1=mysqli_fetch_array($dimen1)){
         
         $Tvol+=$rd1[largo]*$rd1[ancho]*$rd1[alto]*$rd1[numero_bultos]/1000000;
   }
}
				
$total=0;				
$rrr =mysqli_query($link ,"SELECT * from gastos_de_contenedor where gastos_de_contenedor.referencia = '".$row[referencia_lpv]."'");
while($rowg=mysqli_fetch_array($rrr)){
$total+= $rowg["fee"]*1+$rowg["liberacion"]*1+$rowg["terrestre"]*1+$rowg["entrega_de_vacio"]*1+$rowg["muellaje"]*1+$rowg["material_adicional"]*1+$rowg["cambio_de_buque"]*1+$rowg["cambio_de_peso"]*1+$rowg["clossing"]*1;

}
$gt+=$total;
?>
<? $camp01 = anio(str_replace('/','-',$row[zarpe])); ?>
<? $camp02 = periodo(str_replace('/','-',$row[zarpe])); ?>
<? $camp03 = $OFICINA[$empresa]; ?>
<? if($row[cancelado]=='SI'){$camp04 = $row[referencia_lpv]." *** CANCELADO *** ";}else{$camp04 = $row[referencia_lpv];} ?>
<? $camp05 = $row[referencia_cliente]; ?>
<? $camp06 = $row[tipo_de_carga]; ?>
<? $camp07 = $row[cliente]; ?>
<? $camp08 = $row[finaldestination]; ?>
<? $camp09 = $empresa; ?>
<? $camp10 = $row[pod]; ?>
<? $camp11 = $row[shippingline]; ?>
<? $camp12 = $row[vessel]; ?>
<? $camp13 = $row[atraque]; ?>
<? $camp14 = $row[ets]; ?>
<? $camp15 = $row[zarpe]; ?> 
<? $camp16 = $row[almacen_consolidacion]; ?>
<? $camp17 = $row[cfsgeneral]; ?>
<? $camp18 = $row[contenedor]; ?>
<? $camp19 = $row[sello]; ?> 
<? $camp20 = $row[dimension]; ?>
<? $camp21 = $row[tipo]; ?> 
<? $camp22 = $bultos; ?>
<? $camp23 = number_format($tweight,2); ?>
<? $camp24 = number_format($Tvol,3); ?>
<? $camp25 = $cantidad_partidas; ?>
<? $camp26 = $row[linea]; ?>
<? $camp27 = $row[booking_ssl]; ?>
<? $camp28 = $IMO1; ?>
<? $camp29 = $row[procedencia_vacio]; ?>
<? $camp30 = $row[solicitud_vacio]; ?>
<? $camp31 = $row[documentado]; ?>
<? $camp32 = $row[programado]; ?>
<? $camp33 = $row[liberado]; ?>
<? $camp34 = $row[tara]; ?>
<? $camp35 = number_format($total,2); ?>
 
<?
	$qryrsn=mysqli_query($link,"select * from rs_dashboard_contenedor where ref_puerto = '".$camp04."' ");
	//echo "select * from rs_dashboard_contenedor where ref_puerto = '".$camp04."' ".mysqli_num_rows($qryrsn);
	if(mysqli_num_rows($qryrsn)>0){	
		$movactual = $movactual." update rs_dashboard_contenedor set ".
		                        " anio = '".$camp01."', ".
								" periodo = '".$camp02."', ".
								" oficina = '".$camp03."', ".
								" ref_puerto = '".$camp04."', ".
								" ref_cliente = '".$camp05."', ".
								" tipo_carga = '".$camp06."', ".
								" cliente = '".$camp07."', ".
								" destino_final = '".$camp08."', ".
								" puerto_carga = '".$camp09."', ".
								" puerto_descarga = '".$camp10."', ".
								" naviera = '".$camp11."', ".
								" buque = '".$camp12."', ".
								" fecha_atraque = '".$camp13."', ".
								" eta_pol = '".$camp14."', ".
								" zarpe = '".$camp15."', ".
								" almacen = '".$camp16."', ".
								" terminal = '".$camp17."', ".
								" contenedor = '".$camp18."', ".
								" sello = '".$camp19."', ".
								" dimension = '".$camp20."', ".
								" tipo = '".$camp21."', ".
								" bultos_totales = '".$camp22."', ".
								" total_peso = '".$camp23."', ".
								" total_cbm = '".$camp24."', ".
								" cantidad_partidas = '".$camp25."', ".
								" linea = '".$camp26."', ".
								" booking_linea = '".$camp27."', ".
								" imo = '".$camp28."', ".
								" procedencia_vacio = '".$camp29."', ".
								" fecha_toma_vacio = '".$camp30."', ".
								" fecha_documentacion = '".$camp31."', ".
								" fecha_consolidacion = '".$camp32."', ".
								" fecha_liberacion = '".$camp33."', ".
								" tara = '".$camp34."', ".
								" total_pesos = '".$camp35."', ".
								" bandera = ".$camp36.
								" where ref_puerto = '".$camp04."';". "\n";
		   if(mysqli_num_rows($qryrsn)>1){
				$resdel= mysqli_query($link,"select min(id_das_con) as mindat from rs_dashboard_contenedor where ref_puerto = '".$camp04."'");
				if(mysqli_num_rows($resdel)>0){
					while ($rowdel = mysqli_fetch_array($resdel)) {
						$regdel = $rowdel["mindat"];
						//mysqli_query($link,"DELETE FROM rs_dashboard_contenedor WHERE id_das_con = ".$regdel);
					}
				}
		   }
	}else{
		if(strlen($movguarda) < 3){
			if($j == "0"){$movguarda = $movguarda."('".$camp01."','".$camp02."','".$camp03."','".$camp04."','".$camp05."','".$camp06."','".$camp07."','".$camp08."','".$camp09."','".$camp10."','".$camp11."','".$camp12."','".$camp13."','".$camp14."','".$camp15."','".$camp16."','".$camp17."','".$camp18."','".$camp19."','".$camp20."','".$camp21."','".$camp22."','".$camp23."','".$camp24."','".$camp25."','".$camp26."','".$camp27."','".$camp28."','".$camp29."','".$camp30."','".$camp31."','".$camp32."','".$camp33."','".$camp34."','".$camp35."',".$camp36.")";$j = "1";}
			else{
			$movguarda = $movguarda.",('".$camp01."','".$camp02."','".$camp03."','".$camp04."','".$camp05."','".$camp06."','".$camp07."','".$camp08."','".$camp09."','".$camp10."','".$camp11."','".$camp12."','".$camp13."','".$camp14."','".$camp15."','".$camp16."','".$camp17."','".$camp18."','".$camp19."','".$camp20."','".$camp21."','".$camp22."','".$camp23."','".$camp24."','".$camp25."','".$camp26."','".$camp27."','".$camp28."','".$camp29."','".$camp30."','".$camp31."','".$camp32."','".$camp33."','".$camp34."','".$camp35."',".$camp36.")";}
		}else{
			if(strpos($movguarda, $camp04) === true) {$camp04 = $camp04;}
			else{
				if($j == "0"){$movguarda = $movguarda."('".$camp01."','".$camp02."','".$camp03."','".$camp04."','".$camp05."','".$camp06."','".$camp07."','".$camp08."','".$camp09."','".$camp10."','".$camp11."','".$camp12."','".$camp13."','".$camp14."','".$camp15."','".$camp16."','".$camp17."','".$camp18."','".$camp19."','".$camp20."','".$camp21."','".$camp22."','".$camp23."','".$camp24."','".$camp25."','".$camp26."','".$camp27."','".$camp28."','".$camp29."','".$camp30."','".$camp31."','".$camp32."','".$camp33."','".$camp34."','".$camp35."',".$camp36.")";$j = "1";}
				else{
				$movguarda = $movguarda.",('".$camp01."','".$camp02."','".$camp03."','".$camp04."','".$camp05."','".$camp06."','".$camp07."','".$camp08."','".$camp09."','".$camp10."','".$camp11."','".$camp12."','".$camp13."','".$camp14."','".$camp15."','".$camp16."','".$camp17."','".$camp18."','".$camp19."','".$camp20."','".$camp21."','".$camp22."','".$camp23."','".$camp24."','".$camp25."','".$camp26."','".$camp27."','".$camp28."','".$camp29."','".$camp30."','".$camp31."','".$camp32."','".$camp33."','".$camp34."','".$camp35."',".$camp36.")";}
			}
		}
	}
	//echo $movguarda."<br>";
	//echo $movactual."<br>";
   }
   //echo $movguarda."<br>";
   if($movguarda != ""){
	   $movguarda = $movguardat.$movguarda.$movguardae;   
	   if (!mysqli_query($link,$movguarda)){
		   echo "Errormessage: ".mysqli_error($link)."<br>";
	   }
	   else{
		   echo "Registros Ok"."<br>";
	   }
   }
   if($movactual != ""){
	   If (unlink("/home/intralogs/public_html/VER/gastos/ejecutaupdater_rs1_das_con_ver.sql")) {
		 $file_handle = fopen("/home/intralogs/public_html/VER/gastos/ejecutaupdater_rs1_das_con_ver.sql", "a+");
		 fwrite($file_handle, $movactual);
		 fclose($file_handle);
		 ejecutarSQL("/home/intralogs/public_html/VER/gastos/ejecutaupdater_rs1_das_con_ver.sql",$link);
		 echo "Registros Actualizados Ok"."<br>";
	   }
	   else{
		   echo "Error en el borrado"."<br>";
	   }
   }
   
?>
<?
}
}
//mysqli_query($link,"DELETE FROM rs_dashboard_contenedor WHERE bandera = 0 AND zarpe between '".$fecuno."' and '".$fecdos."'");
mysqli_query($link,"UPDATE rs_dashboard_contenedor set bandera = 0");
echo "Mantenimiento Registros Ok"."<br>";

mysqli_close($link);
?>
