Path: C:\
<!DOCTYPE html>
<html lang="pt-br">
<head>
<meta charset="utf-8" />
<meta http-equiv="x-ua-compatible" content="ie=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1, maximum-scale=1" />
<title>Jornada - Transwar</title>
<link rel="icon" type="image/png" href="img/favicon.ico">
<link href="css/estilo.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" media="all" href="css/contato.css"/>
</head>
<?php
require('fpdf.php');
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d/m/Y");
$host = "192.168.0.203";
$db = "dbOmnilink";
$user = "sa";
$password = "";
$connectionOptions = array("Database" => $db, "UID" => $user, "PWD" => $password);
$conn = sqlsrv_connect( $host, $connectionOptions);
$db2 = "Auxiliar";
$user2 = "sa";
$password2 = "";
$connectionOptions2 = array("Database" => $db2, "UID" => $user2, "PWD" => $password2);
$conn2 = sqlsrv_connect( $host, $connectionOptions2);
if (!$conn){echo "ATENÇÃO!! FALHA NA CONEXÃO BANCO OMNILINK!";}
if (!$conn2){echo "ATENÇÃO!! FALHA NA CONEXÃO BANCO AUXILIAR!";}
if (isset($_POST['di'])) { $inicial=$_POST['di'];} else { $inicial=$dateadded;}
if (isset($_POST['df'])) { $final=$_POST['df'];} else { $final=$dateadded;}
$ano1=substr($inicial, 6);
$mes1=substr($inicial, 3,-5);
$dia1=substr($inicial, 0,-8);
$data1=$ano1."-".$mes1."-".$dia1;
$ano2=substr($final, 6);
$mes2=substr($final, 3,-5);
$dia2=substr($final, 0,-8);
$data2=$ano2."-".$mes2."-".$dia2;$l=1;
if (isset($_POST['cpf'])) { $cpf2=$_POST['cpf'];} else {$cpf2="999999";}
$tsql= "SELECT a.Texto
,a.IDTerminal
,Convert(varchar(8), DataHoraEm,108) as hora
,Convert(varchar(10), DataHoraEm,120) as ano
,b.cpf
FROM tMacroRec a INNER JOIN Auxiliar.dbo.tMotoristas b ON a.Texto = b.cpf
WHERE Convert(varchar(10), DataHoraEm,120) BETWEEN '$data1' and '$data2' ";
if ( $cpf2 !="999999") { $tsql=$tsql." AND a.Texto = '$cpf2'"; }
else {$tsql=$tsql." AND a.Texto BETWEEN '000100' AND '999999'";}
$tsql=$tsql." ORDER BY cpf ASC, ano ASC, hora ASC";
$consulta= sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if (!$consulta){echo "ATENÇÃOs!! FALHA NA CONSULTA 1!";}
$numReg1 = sqlsrv_num_rows($consulta);
$file_txt = fopen('tabela.txt', 'w');
$file_csv = fopen('tabela_original.csv', 'w');
$cpf1='999999'; $term1='999999';$dia1='9999/99/99';$hfim='';$IF='';$evento='';
while ($row = sqlsrv_fetch_array($consulta, SQLSRV_FETCH_ASSOC))
{
$term2=$row['IDTerminal'];
$dia=$row['ano'];
$cpf2=$row['cpf'];
$horainicio=$row['hora'];
if (($cpf1==$cpf2 AND $term1==$term2 AND $dia==$dia1) or ($cpf1!=$cpf2 AND $term1==$term2)){CONTINUE;}
$sql1= "SELECT a.Texto
,a.IDTerminal
,Convert(varchar(8), a.DataHoraEm,108) as hora
,Convert(varchar(10), a.DataHoraEm,103) as dia
,Convert(varchar(10), a.DataHoraEm,120) as ano
,a.idMacro
,Convert(varchar(33), b.nome) as nome
,b.pis
,b.cpf
FROM tMacroRec a LEFT JOIN Auxiliar.dbo.tMotoristas b ON a.Texto = b.cpf
WHERE Convert(varchar(8), DataHoraEm,108) >= '$horainicio'
AND (Convert(varchar(10), a.DataHoraEm,120) = '$dia'
AND a.IDTerminal = '$term2' )
ORDER BY ano ASC, hora ASC";
$consulta1= sqlsrv_query($conn, $sql1, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if (!$consulta1){echo "ATENÇÃOs!! FALHA NA CONSULTA 2!";}
while ($row1 = sqlsrv_fetch_array($consulta1, SQLSRV_FETCH_ASSOC))
{
if ($row1['cpf']!=''){$cpf3=$row1['cpf']; if ($cpf3!=$cpf2){$cpf1=$cpf2; $term1=$term2;$dia1=$dia;CONTINUE 2;}}
if ($row1['idMacro']=='' or $row1['idMacro']===null)
{ $mac=substr($row1['Texto'], 0, 2);
if ($mac == '40' or $mac == '41') {$row1['idMacro']='36';}
if ($mac == '50' or $mac == '51') {$row1['idMacro']='37';}
if ($mac == '06' or $mac == '60' or $mac == '61' or $mac == '62') {$row1['idMacro']='38';}
if ($mac == '07' or $mac == '70' or $mac == '71' or $mac == '72') {$row1['idMacro']='39';}
if ($mac == '0') {$row1['idMacro']='42';}
if ($mac == '11') {$row1['idMacro']='43';}
if ($mac == '12') {$row1['idMacro']='44';}
if ($mac == '09' or $mac == '90' or $mac == '91' or $mac == '92' or $mac == '93') {$row1['idMacro']='41';}
if ($mac == '08' or $mac == '80' or $mac == '8_' or $mac == '8') {$row1['idMacro']='40';}
}
$linha[$l] = array($row1['nome'],$row1['pis'],$row1['dia'],$row1['hora'],$hfim,$evento,$IF,$row1['idMacro']);
++$l;
} $cpf1=$cpf2; $term1=$term2;$dia1=$dia;
}
if($l==1){echo "<script type=\"text/javascript\">location.href='index.php';</script>";}
$i=1;$k=0;$p=2;$a=0;$sit="I";$id=$k;
$linha[$k][2]="99/99/9999";$linha[$l+1][7]="32";$linha[$l+1][1]="999999";$linha[$k][1]="999999";
for ( $i = 1; $i < $l; ++$i )
{ if ($linha[$i][1]=='') {$linha[$i][0]=$linha[$i-1][0];$linha[$i][1]=$linha[$i-1][1];}
if ($linha[$i][7]=="32" or $linha[$i][7]=="45")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id-1][6]="F";$linha[$id][6]="";$linha[$i][6]="I";$id=$i;}
}
else
{ $linha[$id-1][6]="F";$linha[$id][6]="";$linha[$i][6]="I";$id=$i;}
}
else
{ if ($linha[$i][7]=="33" OR $linha[$i][7]=="34" or $linha[$i][7]=="46" OR $linha[$i][7]=="47")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id][6]="F";$linha[$i][6]="I";$linha[$i][5]="TD";$id=$i;}
else
{$linha[$i][6]=".";$linha[$i][5]="TD";$linha[$id][4]=$linha[$i][3];
if ($linha[$id][6]=="I") {$linha[$id][5]="TD";}
$id=$i;
}
}
else
{ $linha[$id][6]="F";$linha[$i][5]="TD";$linha[$i][6]=".";$id=$i;}
}
else
{ if ($linha[$i][7]=="35" OR $linha[$i][7]=="36" OR $linha[$i][7]=="42" OR $linha[$i][7]=="43" OR $linha[$i][7]=="44" or $linha[$i][7]=="48" OR $linha[$i][7]=="49" OR $linha[$i][7]=="55" OR $linha[$i][7]=="56" OR $linha[$i][7]=="57")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id][6]="F";$linha[$i][6]="I";$id=$i;}
else
{$linha[$i][6]=".";$linha[$i][5]="TD";$linha[$id][4]=$linha[$i][3];
if ($linha[$id][6]=="I") {$linha[$id][5]="TD";}
$id=$i;
}
}
else
{ $linha[$id][6]="F";$linha[$i][5]="TD";$linha[$i][6]=".";$id=$i;}
}
else
{ if ($linha[$i][7]=="37" OR $linha[$i][7]=="40" OR $linha[$i][7]=="41" or $linha[$i][7]=="50" OR $linha[$i][7]=="53" OR $linha[$i][7]=="54")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id][6]="F";$linha[$i][6]="I";$id=$i;}
else
{$linha[$i][6]=".";$linha[$i][5]="TE";$linha[$id][4]=$linha[$i][3];
if ($linha[$id][6]=="I") {$linha[$id][5]="TD";}
$id=$i;
}
}
else
{ $linha[$id][6]="F";$linha[$i][5]="TE";$linha[$i][6]=".";$id=$i;}
}
else
{ if ($linha[$i][7]=="38" or $linha[$i][7]=="51")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id][6]="F";$linha[$i][6]="I";$id=$i;}
else
{$linha[$i][6]=".";$linha[$i][5]="ID";$linha[$id][4]=$linha[$i][3];
if ($linha[$id][6]=="I") {$linha[$id][5]="TD";}
$id=$i;
}
}
else
{ $linha[$id][6]="F";$linha[$i][5]="ID";$linha[$i][6]=".";$id=$i;}
}
else
{ if ($linha[$i][7]=="39" or $linha[$i][7]=="52")
{ if ($linha[$i][1]==$linha[$id][1])
{ if ($linha[$i][2]!=$linha[$id][2])
{ $linha[$id][6]="F";$linha[$i][6]="I";$id=$i; $linha[$i][8]=1; }
else
{$linha[$i][6]=".";$linha[$i][5]="IA";$linha[$id][4]=$linha[$i][3]; $linha[$i][8]=1;
if ($linha[$id][6]=="I") {$linha[$id][5]="TD";}
$id=$i;
}
}
else
{ $linha[$id][6]="F";$linha[$i][5]="IA";$linha[$i][6]=".";$id=$i; $linha[$i][8]=1;}
}
}
}
}
}
}
}
$linha[$id][6]="";$linha[$id-1][6]="F";
$id=1;
for ( $i = 2; $i < $l; ++$i )
{ if ($linha[$i][6]!='')
{ if ($linha[$i][1]==$linha[$id][1] AND $linha[$i][2]==$linha[$id][2])
{ if ($linha[$i][5]==$linha[$id][5])
{ $linha[$id][4]=$linha[$i][4]; if ($linha[$i][6]=="F") {$linha[$id][6]="F";}
$linha[$i][6]='';
} else
{$id=$i;}
} else
{$id=$i;}
}
}
for ( $i = 1; $i < $l; ++$i ) // verifica Intervalo de Alimentação IA e Tempo de Espera TE
{ if ($linha[$i][6]!='')
{ if ($linha[$i][6]=='.') {$linha[$i][9]='';} else {$linha[$i][9]=$linha[$i][6];}
$hi=strtotime($linha[$i][3]);$hf=strtotime($linha[$i][4]);$dif=$hf-$hi;$difh=substr(($dif/3600)+100,1,2); $difm=substr(($dif/60%60)+100,1,2) ;$dif=$difh.":".$difm; $linha[$i][10]=$dif;
}
}
$id=1; $IA=0;$ri=1;$rf=1;$tt=$l;
for ( $i = 2; $i < $l; ++$i )
{ if ($linha[$i][6]!='')
{ if ($linha[$i][1]!=$linha[$id][1] OR $linha[$i][2]!=$linha[$id][2])
{ if ($IA==0)
{
for ($k=$ri; $k<$rf;++$k)
{ if ($linha[$k][6]!='')
{if ($linha[$k][5]=="TE" AND $linha[$k][10]>="02:00")
{ $linha[$k][10]=date('H:i',strtotime($linha[$k][10]. ' - 1 hour'));
$horafim=$linha[$k][4];
$linha[$k][4]=date('H:i:s',strtotime($horafim. ' - 1 hour'));
if ($linha[$k][9]=='F'){$linha[$k][9]='.';$IF='F';}else{$IF='.';}
$nom=$linha[$k][0];$pis=$linha[$k][1];$chapa=$linha[$k][2];$horaini=$linha[$k][4];
$tt=$tt+1;
$extra = array($nom,$pis,$chapa,$horaini,$horafim,'IA',$IF,'','','','01:00');
array_splice($linha, $k+1, 0, array($extra));
$l=$l+1;$i=$i+1;
$ri=$i;$rf=$i;break;
}
}
}
}
$ri=$i;$rf=$i;$id=$i;$IA=0;
} else {$rf=$i;$id=$i;}
if (isset($linha[$i][5])) {if ($linha[$i][5]=="IA") {$IA=1;} }
}$rf=$i;
}
if ($IA==0)
{
for ($k=$ri; $k<$rf;++$k)
{ if ($linha[$k][6]!='')
{if ($linha[$k][5]=="TE" AND $linha[$k][10]>="02:00")
{ $linha[$k][10]=date('H:i',strtotime($linha[$k][10]. ' - 1 hour'));
$horafim=$linha[$k][4];
$linha[$k][4]=date('H:i:s',strtotime($horafim. ' - 1 hour'));
if ($linha[$k][9]=='F'){$linha[$k][9]='.';$IF='F';}else{$IF='.';}
$nom=$linha[$k][0];$pis=$linha[$k][1];$chapa=$linha[$k][2];$horaini=$linha[$k][4];
$tt=$tt+1;
$extra = array($nom,$pis,$chapa,$horaini,$horafim,'IA',$IF,'','','','01:00');
array_splice($linha, $k+1, 0, array($extra));
break;
}
}
}
}
function cmp($a, $b) {
$c = strcmp($a[0] , $b[0]);
$c .= strnatcmp($a[11] , $b[11]);
return $c;
}
$i9 = 0;
for ( $i = 0; $i < $tt; ++$i )
{ if ($linha[$i][6]!=''and $linha[$i][1]!='999999')
{ $linha9[$i9]=$linha[$i];$linha9[$i9][11]=$i9;$i9++;}
}
if(isset($linha9)){
usort($linha9, 'cmp');
for ( $i = 0; $i < $i9; ++$i )
{ $linha2 = $linha9[$i][0]. ";" .$linha9[$i][1] . ";" .$linha9[$i][2] .";".$linha9[$i][3] . ";".$linha9[$i][4].";".$linha9[$i][10].";" .$linha9[$i][5].";".$linha9[$i][9]. PHP_EOL;
fwrite($file_txt, $linha2);
$linha_csv = $linha9[$i][1] . ";" .$linha9[$i][2] .";".$linha9[$i][3] . ";".$linha9[$i][4].";" .$linha9[$i][5].";".$linha9[$i][9]. PHP_EOL;
fwrite($file_csv, $linha_csv);
}
$linha_csv = ";" . PHP_EOL;
fwrite($file_csv, $linha_csv);
fclose($file_txt);
fclose($file_csv);
include("alt_tab_csv.php");
}
echo "<script type='text/javascript'>hideCarga(this);</script>";
?>
<script language="JavaScript">
window.open("tabela.php", "_blank");
</script>
</html>