Sum PHP and Mysql hours

Asked

Viewed 416 times

0

I picked up a project in progress, a tracking system that has workweek calculation. The system calculates right, but sometimes adding up time of the stationary vehicle and walking sum 24 hours and 59 minutes.

I’ll try to put part of the code here 

    $html .= "<table width='70%' align='center' border='1' cellspacing='0' cellpadding='0' style='background:#ffffff;border-collapse:collapse;font-family:verdana;font-size:12px;' bordercolor='#CCCCCC'>";
$html .= "<tr>";
$html .= "<td width='20%' align='center'><img src='../../imagens/logop.png'></td>";
$html .= "<td align='center'><font size='3'><strong>JORNADA DE TRABALHO<br>".BuscaVeiculo($cd_veiculo)."</strong></font><br>".$dt_ini." - ".$dt_fim."</td>";
$html .= "</tr>";
$html .= "</table>";
$html .= "<table width='70%' align='center' border='0' cellspacing='1' cellpadding='1' style='font-family:verdana;font-size:11px;'>";
// INI DETALHADO////
if($cka == 0){
$html .= "<tr bgcolor='#DDD7CA'>";
$html .= "<td><strong>Situação</strong></td>";
$html .= "<td><strong>Inicial</strong></td>";
$html .= "<td><strong>Fim</strong></td>";
$html .= "<td><strong>Tempo</strong></td>";
$html .= "</tr>";
    $aa = 0; $pp = 0;
    $Inicio         = $dt_ini;
    $Fim            = $dt_fim;
    $Veiculo        = $cd_veiculo;
    $Inicial        = $dt_ini." 00:00";
    $Final          = $dt_fim." 23:59";
    $Inicial        = DtBrToDtEua($Inicial, 4);
    $Final          = DtBrToDtEua($Final, 4);


    $SQL = "SELECT dt_hora as dt, ";
    $SQL .= " (sqrt(((vl_latitude - (vl_lati_anterior))*(vl_latitude - (vl_lati_anterior))) + ((vl_longitude - (vl_long_anterior))*(vl_longitude - (vl_long_anterior))))*111) as distancia ";
    $SQL .= " from hist_posicao where cd_veiculo = '". $Veiculo ."' ";
    $SQL .= " and dt_hora >= '". $Inicial ."' and dt_hora <= '". $Final ."' ";
    $SQL .= " order by dt_hora asc ";
    //echo $html .= $SQL;exit;
    $RSS = mysql_query($SQL, $conexao);
    if(mysql_num_rows($RSS) > 0){       
        $RS = mysql_fetch_assoc($RSS);
        $st = 0;
        $dia = date("d", strtotime($RS["dt"]));
        if($RS["distancia"] >= 0.3 && $RS["ig"] == 1){$ini = date("d/m/Y", strtotime($RS["dt"]))." 00:00"; $st = 1;}
        if($RS["distancia"] < 0.3 && $RS["ig"] == 0){$ini = date("d/m/Y", strtotime($RS["dt"]))." 00:00"; $st = 2;}
        if($cor == "#FFFFFF"){$cor = "#DDDDDD";}else{$cor = "#FFFFFF";}
        if(diadasemana(date("d/m/Y", strtotime($RS["dt"]))) != "1" && diadasemana(date("d/m/Y", strtotime($RS["dt"]))) != "7")//se dia semana não for 1 nem 7
            {$corsm = "#ffff99";}else{$corsm = "#33ff66";}//definição para cor da linha para final de semana e dia de semana
        $html .= "<tr bgcolor='".$corsm."'><td colspan='4' align='center'><b>".dsdiadasemana(date("d/m/Y", strtotime($RS["dt"])))." | ".date("d/m/Y", strtotime($RS["dt"]))."</b></td></tr>";//nome do dia da semana e data

        while($RS = mysql_fetch_array($RSS)){
            $x = $x + 1;
            if($dia != date("d", strtotime($RS["dt"]))){
                if($st == 1){
                    //echo $ini." | ".$ultimo ."<br><br><br>";
                    $html .= "<tr bgcolor='".$cor."'><td>Andando</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $ultimo." 23:59"))."</td></tr>";
                    $andando = $andando + datediff("n", $ini, $ultimo." 23:59");
                    $aa = $aa + datediff("n", $ini, $ultimo." 23:59");
                    if(diadasemana($ultimo) != "1" && diadasemana($ultimo) != "7")
                        {$saa = $saa + datediff("n", $ini, $ultimo." 23:59");}else{$fsaa = $fsaa + datediff("n", $ini, $ultimo." 23:59");}
                }
                if($st == 2){
                    //echo $ini." | ".$ultimo ."<br>";          
                    $html .= "<tr bgcolor='".$cor."'><td>Parado</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $ultimo." 23:59"))."</td></tr>";//header
                    $parado = $parado + datediff("n", $ini, $ultimo." 23:59");
                    $pp = $pp + datediff("n", $ini, $ultimo." 23:59");
                    if(diadasemana($ultimo) != "1" && diadasemana($ultimo) != "7")
                        {$spp = $spp + datediff("n", $ini, $ultimo." 23:59");}else{$fspp = $fspp + datediff("n", $ini, $ultimo." 23:59");}
                }
                $html .= "<tr bgcolor='".$cor."'><td colspan='3'><b>Andando no Dia</b></td><td><b>".MinToHrMin($andando)."</b></td></tr>";
                $html .= "<tr bgcolor='".$cor."'><td colspan='3'><b>Parado no Dia</b></td><td><b>".MinToHrMin($parado)."</b></td></tr>";
                if(diadasemana(date("d/m/Y", strtotime($RS["dt"]))) != "1" && diadasemana(date("d/m/Y", strtotime($RS["dt"]))) != "7")
                    {$corsm = "#ffff99";}else{$corsm = "#33ff66";}
                $html .= "<tr bgcolor='".$corsm."'><td colspan='4' align='center'><b>".dsdiadasemana(date("d/m/Y", strtotime($RS["dt"])))." | ".date("d/m/Y", strtotime($RS["dt"]))."</b></td></tr>";

                if($cor == "#FFFFFF"){$cor = "#DDDDDD";}else{$cor = "#FFFFFF";}
                $andando = 0;
                $parado = 0;
                if($RS["distancia"] >= 0.3){$ini = date("d/m/Y", strtotime($RS["dt"]))." 00:00"; $st = 1;}
                if($RS["distancia"] < 0.3){$ini = date("d/m/Y", strtotime($RS["dt"]))." 00:00"; $st = 2;}
            }
            if($x > 1){
                if($RS["distancia"] >= 0.3){
                    if($st == 1){
                        $fim = date("d/m/Y H:i", strtotime($RS["dt"]));
                        $st = 1;
                    }
                    if($st == 2){
                        if($ini != $fim){
                            $html .= "<tr bgcolor='".$cor."'><td>Parado</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $fim))."</td></tr>";
                        }
                        $parado = $parado + datediff("n", $ini, $fim);
                        $pp = $pp + datediff("n", $ini, $fim);
                        if(diadasemana($fim) != "1" && diadasemana($fim) != "7")
                            {$spp = $spp + datediff("n", $ini, $fim);}else{$fspp = $fspp + datediff("n", $ini, $fim);}
                        $ini = $fim;
                        $st = 1;
                    }
                }
                if($RS["distancia"] < 0.03){
                    if($st == 1){
                        if($ini != $fim){
                            $html .= "<tr bgcolor='".$cor."'><td>Andando</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $fim))."</td></tr>";
                        }
                        $andando = $andando + datediff("n", $ini, $fim);
                        $aa = $aa + datediff("n", $ini, $fim);
                        if(diadasemana($fim) != "1" && diadasemana($fim) != "7")
                            {$saa = $saa + datediff("n", $ini, $fim);}else{$fsaa = $fsaa + datediff("n", $ini, $fim);}
                        $ini = $fim;
                        $st = 2;
                    }
                    if($st == 2){
                        $fim = date("d/m/Y H:i", strtotime($RS["dt"]));
                        $st = 2;
                    }
                }
            }

            $dia    = date("d", strtotime($RS["dt"]));
            $ultimo = date("d/m/Y", strtotime($RS["dt"]));
        }
    }

    if($st == 1){
        $html .= "<tr bgcolor='".$cor."'><td>Andando</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $ultimo." 23:59"))."</td></tr>";
        $andando = $andando + datediff("n", $ini, $ultimo." 23:59");
        $aa = $aa + datediff("n", $ini, $ultimo." 23:59");
        if(diadasemana($ultimo) != "1" && diadasemana($ultimo) != "7")
            {$saa = $saa + datediff("n", $ini, $ultimo." 23:59");}else{$fsaa = $fsaa + datediff("n", $ini, $ultimo." 23:59");}
    }

    if($st == 2){
        $html .= "<tr bgcolor='".$cor."'><td>Parado</td><td>".$ini."</td><td>".$fim."</td><td>".MinToHrMin(datediff("n", $ini, $ultimo." 23:59"))."</td></tr>";
        //$teste = number_format((1439/60),1)-substr(number_format((1439/60),1),-2);
        //if($teste == 24){$teste = 23;}
        //echo $teste;
        //echo /*MinToHrMin(*/datediff("n", $ini, $ultimo." 23:59")/*)*/;
        $parado = $parado + datediff("n", $ini, $ultimo." 23:59");
        $pp = $pp + datediff("n", $ini, $ultimo." 23:59");
        if(diadasemana($ultimo) != "1" && diadasemana($ultimo) != "7")
            {$spp = $spp + datediff("n", $ini, $ultimo." 23:59");}else{$fspp = $fspp + datediff("n", $ini, $ultimo." 23:59");}
    }

    $html .= "<tr bgcolor='".$cor."'><td colspan='3'><b>Andando no Dia</b></td><td><b>".MinToHrMin($andando)."</b></td></tr>";
    $html .= "<tr bgcolor='".$cor."'><td colspan='3'><b>Parado no Dia</b></td><td><b>".MinToHrMin($parado)."</b></td></tr>";
    $html .= "<tr bgcolor='#ffff99'><td colspan='3'><b>Total Andando no Período Semana</b></td><td><b>".MinToHrMin($saa)."</b></td></tr>";
    $html .= "<tr bgcolor='#ffff99'><td colspan='3'><b>Total Parado no Período Semana</b></td><td><b>".MinToHrMin($spp)."</b></td></tr>";
    $html .= "<tr bgcolor='#33ff66'><td colspan='3'><b>Total Andando no Período Final de Semana</b></td><td><b>".MinToHrMin($fsaa)."</b></td></tr>";
    $html .= "<tr bgcolor='#33ff66'><td colspan='3'><b>Total Parado no Período Final de Semana</b></td><td><b>".MinToHrMin($fspp)."</b></td></tr>";
    $html .= "<tr bgcolor='#FFDFB0'><td colspan='3'><b>Total Andando no Período</b></td><td><b>".MinToHrMin($aa)."</b></td></tr>";
    $html .= "<tr bgcolor='#FFDFB0'><td colspan='3'><b>Total Parado no Período</b></td><td><b>".MinToHrMin($pp)."</b></td></tr>";
    $html .= "<tr><td colspan='4' style='color:red' align='center'><b>* Entende-se por ANDANDO: Deslocamento Maior 300 metros</b></td></tr>";

$html .= "</table>";
}
php mysql

  • Oops! Blz! I don’t quite understand your doubt! I think you have to ask a question in your own question exactly what you want.

  • This calculation in the code, results in a total of hours with ignition on and off, totaling 23:59 every day, but has days q ends up adding the two and calculating total 24:59, in this case is wrong.

  • Would have to see the database right, how can I give you an access, use anydesk?

  • Calm down, man, you don’t need this! I’m still analyzing what this code is doing. What total should give in this case?

  • The total adding up walking and standing on the day, always has to give 23:59

  • Look at the image, the circled day gave 24:59 https://imgsafe.org/image/9d0f8bdadf

  • Opa, is it possible to analyze on top of the information provided?

  • Anything new?

  • I couldn’t pay much attention, man. I’m kind of on the run. =/

  • Okay, in case you can soon, I’m still unsolved

Show 5 more comments
No answers

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.