<?php
date_default_timezone_set('America/La_Paz');
include('../config/database.php');
$metodo=$_SERVER['REQUEST_METHOD'];

if($metodo=='GET'){
    //Cabecera
    if(isset($_GET['t'])){
        //AND fecha_asignacion BETWEEN '$d' AND '$h'
        $d = $_GET['desde'].' 00:00:00';
        $h = $_GET['hasta'].' 23:59:59';
        $query1 = mysqli_query($mysqli, "SELECT COUNT(id_guia) tasig FROM guias WHERE estado_guia >= 200 AND fecha_asignacion BETWEEN '$d' AND '$h'");
        $query2 = mysqli_query($mysqli, "SELECT COUNT(id_guia) tgest FROM guias WHERE estado_guia > 300 AND fecha_asignacion BETWEEN '$d' AND '$h'");

        $json = array();
        $row1 = mysqli_fetch_array($query1);
        $row2 = mysqli_fetch_array($query2);
        if($row1['tasig']==0){$c=0;}else{$c=(100*$row2['tgest'])/$row1['tasig'];}
            $json[] = array(
                'tasig' => $row1['tasig'],
                'tgest' => $row2['tgest'],
                'tpend' => ($row1['tasig']-$row2['tgest']),
                'cumpli' => number_format($c)
            );
        $jsonstring = json_encode($json[0]);
        echo $jsonstring; 
    }

    //Listado de usuarios
    if(isset($_GET['du1'])){
        $d = $_GET['desde'].' 00:00:00';
        $h = $_GET['hasta'].' 23:59:59';
        $json = array();
        $queryu = mysqli_query($mysqli , "SELECT * FROM usuarios WHERE permisos_acceso='Operario' AND status='activo'");
        while ($data = mysqli_fetch_array($queryu)) {
            $u=$data['username'];
            $query1 = mysqli_query($mysqli, "SELECT COUNT(id_guia) tasig FROM guias WHERE estado_guia >= 200 AND usuario_asignado='$u' AND fecha_asignacion BETWEEN '$d' AND '$h'");
            $query2 = mysqli_query($mysqli, "SELECT COUNT(id_guia) tgest FROM guias WHERE estado_guia > 300 AND usuario_asignado='$u' AND fecha_asignacion BETWEEN '$d' AND '$h'");
            $row1 = mysqli_fetch_array($query1);
            $row2 = mysqli_fetch_array($query2);
            if($row1['tasig']==0){$c=0;}else{$c=(100*$row2['tgest'])/$row1['tasig'];}
            $json[] = array(
                'usuario' => $data['name_user'],
                'vehiculo' => $data['vehiculo'],
                'tasig' => $row1['tasig'],
                'tgest' => $row2['tgest'],
                'cumpli' => round($c),
                'username' => $data['username']
            );
        }
        $jsonstring = json_encode($json);
        echo $jsonstring; 
    }
    if (isset($_GET['du'])) {

        $d = $_GET['desde'] . ' 00:00:00';
        $h = $_GET['hasta'] . ' 23:59:59';
    
        $sql = "
            SELECT 
                u.name_user AS usuario,
                u.vehiculo,
                u.username,
                COALESCE(t1.tasig, 0) AS tasig,
                COALESCE(t2.tgest, 0) AS tgest,
                CASE 
                    WHEN COALESCE(t1.tasig, 0) = 0 THEN 0
                    ELSE ROUND((100 * COALESCE(t2.tgest, 0)) / t1.tasig)
                END AS cumpli
            FROM usuarios u
            LEFT JOIN (
                SELECT usuario_asignado, COUNT(id_guia) AS tasig
                FROM guias
                WHERE estado_guia >= 200 
                  AND fecha_asignacion BETWEEN '$d' AND '$h'
                GROUP BY usuario_asignado
            ) t1 ON t1.usuario_asignado = u.username
            LEFT JOIN (
                SELECT usuario_asignado, COUNT(id_guia) AS tgest
                FROM guias
                WHERE estado_guia > 300 
                  AND fecha_asignacion BETWEEN '$d' AND '$h'
                GROUP BY usuario_asignado
            ) t2 ON t2.usuario_asignado = u.username
            WHERE u.permisos_acceso = 'Operario' 
              AND u.status = 'activo'
        ";
    
        $result = mysqli_query($mysqli, $sql);
        $json = [];
    
        while ($row = mysqli_fetch_assoc($result)) {
            $json[] = $row;
        }
    
        echo json_encode($json);
    }

    //Listado de guias
    if(isset($_GET['u'])){
        $ua = $_GET['u'];
        $d = $_GET['desde'].' 00:00:00';
        $h = $_GET['hasta'].' 23:59:59';
        $query = mysqli_query($mysqli, "SELECT * FROM guias g, estados e WHERE e.codigo_estado=g.estado_guia AND usuario_asignado='$ua' AND fecha_asignacion BETWEEN '$d' AND '$h' ORDER BY estado_guia desc");

        $json = array(); $est='';
        while ($row = mysqli_fetch_array($query)) {
            $json[] = array(
                'id' => $row['id_guia'],
                'guia' => $row['codigo_guia'],
                'estado' => $row['label'],
                'pedido' => $row['pedido_cliente']
            );
        }            
        $jsonstring = json_encode($json);
        echo $jsonstring; 
    }

}