<?php
require '../dcc/vendor/autoload.php';
use JasonGrimes\Paginator;

if(isset($_REQUEST["agence"]) && isset($_REQUEST['start']) && isset($_REQUEST['end']))
{
    $postData = $_REQUEST;
    $postData['start'] = str_replace('/', '-', $postData['start']);
    $postData['end'] = str_replace('/', '-', $postData['end']);
    $startDate = date('Y-m-d', strtotime($postData['start']));
    $endDate = date('Y-m-d', strtotime($postData['end']));
    $search = isset($postData['search']) ? $postData['search'] : "";

    $itemsPerPage = 30;
    $currentPage = isset($_REQUEST['page'])  ? $_REQUEST['page'] : 1;

    // $filter = $search ? (" AND TRIM(nom) LIKE '%".$search."%' OR TRIM(prenom) LIKE '%".$search."%'") : "";
    /*
    $filter = "";
    if($search){
        $searchQ = "'".$search."'";
        $searchQ = str_replace(" ","','",$searchQ);
        $filter = " AND TRIM(nom) IN (".$searchQ.") OR TRIM(prenom) IN (".$searchQ.")";
    }
    */
    $search = preg_replace('/\s+/', ' ', $search);
    $values =array();    
    $filter = $search ? (" AND (CONCAT(TRIM(prenom),' ',TRIM(nom)) LIKE :search OR CONCAT(TRIM(nom),' ',TRIM(prenom)) LIKE :search)") : "";
    if($filter !=""){
       $values['search'] = '%'. $search .'%';
    }
    
    $recapTotalRecords = "
    SELECT COUNT(DISTINCT(CONCAT(TRIM(nom),' ',TRIM(prenom)))) as total
    FROM ". STATISTICS_TABLE ." 
    WHERE DATE(date_arrivee) BETWEEN :startDate AND :endDate AND id_agence = :id_agence " . $filter ." ";

    $values += array('startDate'=>$startDate,'endDate'=>$endDate,'id_agence'=>$postData['agence']);
    $recapTotalResult = excuteQuery($recapTotalRecords,$values);
    $totalItems = $recapTotalResult['stmt']->fetch(PDO::FETCH_ASSOC);
    $totalItems = $totalItems["total"];
    
    $recapRecords = "
    SELECT CONCAT(TRIM(nom),' ',TRIM(prenom)) AS client, 
    COUNT(id_statistique) AS nbVisits,     
    COUNT(CASE when rdv = 0 then 1 END ) as noAppointment,
    COUNT(CASE when rdv = 1 then 1 END ) as appointment,
    COUNT(CASE when rdv = 5 then 1 END ) as ship,    
    AVG(TIME_TO_SEC(case when rdv=  0 then (case when duree_attente is null then null else duree_attente end) else null end)) as noAppointmentAttente,
    AVG(TIME_TO_SEC(case when rdv=  1 then (case when duree_attente is null then null else duree_attente end) else null end)) as appointmentAttente,
    AVG(TIME_TO_SEC(case when rdv=  5 then (case when duree_attente is null then null else duree_attente end) else null end)) as shipAttente,
    AVG(TIME_TO_SEC(case when rdv=  0 then (case when duree_rdv is null then null else duree_rdv end) else null end)) as noAppointmentEntretien,
    AVG(TIME_TO_SEC(case when rdv=  1 then (case when duree_rdv is null then null else duree_rdv end) else null end)) as appointmentEntretien,
    AVG(TIME_TO_SEC(case when rdv=  5 then (case when duree_rdv is null then null else duree_rdv end) else null end)) as shipEntretien,
    COUNT(CASE when nfcprov = 1 then 1 END ) as nfc
    FROM ". STATISTICS_TABLE ." 
    WHERE DATE(date_arrivee) BETWEEN :startDate AND :endDate AND id_agence = :id_agence " . $filter . " GROUP BY CONCAT_WS('',TRIM(nom),TRIM(prenom)) LIMIT " . ( ( $currentPage - 1 ) * $itemsPerPage ) . ", $itemsPerPage";

    $recapResult = excuteQuery($recapRecords , $values);
    $num_rows = $recapResult['stmt']->rowCount();
    $table = "";

    if(isset($_REQUEST["debug"])){
        print($recapRecords);
    }

    if($num_rows > 0) { 
        
        /*** pagination ***/
        $helper = new Core\Helper();
        $query_url =  $helper->getFullUrl();
        $url_parts = parse_url($query_url);

        if( isset($url_parts['query'])) {
            parse_str($url_parts['query'], $params);
        }
        $params['page'] = '_num';
        $url_parts['query'] = http_build_query($params);
        $url = $url_parts['scheme'] . '://' . $url_parts['host'] . $url_parts['path'] . '?' . $url_parts['query'];
        $paginator = new Paginator($totalItems, $itemsPerPage, $currentPage, $url);
        $paginator->setPreviousText("Précédent");
        $paginator->setNextText("Suivant");
        $colSiege1 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>Nombre de visites avec RDV</th>" : "";
        $colSiege2 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>Temps moyen d'attente visites Livraison</th>" : "";
        $colSiege3 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>Temps moyen d'entretien Livraison</th>" : "";
        $colSiege4 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>Livraison</th>" : "";
        $colSiege5 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>DMA Livraison</th>" : "";
        $colSiege6 = ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])) ? "<th>DME Livraison</th>" : "";
        $head = "
                    <th>Nom client</th>
                    <th>Nombre de visites total</th>
                    <th>Nombre de visites sans RDV</th>
                    <th>Nombre de visites avec RDV</th>
                    ".$colSiege1."
                    <th>Temps moyen d'attente visites sans RDV</th>
                    <th>Temps moyen d'attente visites avec RDV</th>
                    ".$colSiege2."
                    <th>Temps moyen d'entretien sans RDV</th>
                    <th>Temps moyen d'entretien avec RDV</th>
                    ".$colSiege3."
                    <th>Nombre d'identification via la CB sans contact</th>";
        $head = "
                    <th>Client</th>
                    <th>Total</th>
                    <th>SRDV</th>
                    <th>RDV</th>
                    ".$colSiege4."
                    <th>DMA SRDV</th>
                    <th>DMA RDV</th>
                    ".$colSiege5."
                    <th>DME SRDV</th>
                    <th>DME RDV</th>
                    ".$colSiege6;
        $head .=    $GLOBALS["BORNE_CONF"]["NFC"] ? "<th>NFC</th>" : "";
        $table = "<table class='table table-striped' id='recap-datatable'>
                    <thead>
                    <tr>
                        ".$head."
                    </tr>
                    </thead>
                    <tbody>";
        while($res = $recapResult['stmt']->fetch(PDO::FETCH_ASSOC)) {
            if($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] && in_array('shipping', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])){
                $colSiege1 = "<td>".$res["ship"]."</td>";
                $colSiege2 = "<td>".gmdate('H:i:s', $res["shipAttente"])."</td>";
                $colSiege3 = "<td>".gmdate('H:i:s', $res["shipEntretien"])."</td>";
            }
            $line = "<tr>
                <td class='text-left'>".$res["client"]."</td>
                <td>".$res["nbVisits"]."</td>
                <td>".$res["noAppointment"]."</td>
                <td>".$res["appointment"]."</td>
                ".$colSiege1."
                <td>".gmdate('H:i:s', $res["noAppointmentAttente"])."</td>
                <td>".gmdate('H:i:s', $res["appointmentAttente"])."</td>
                ".$colSiege2."
                <td>".gmdate('H:i:s', $res["noAppointmentEntretien"])."</td>
                <td>".gmdate('H:i:s', $res["appointmentEntretien"])."</td>".$colSiege3;
            $line .= $GLOBALS["BORNE_CONF"]["NFC"] ? ("<td>".$res["nfc"]."</td>") : "";
            $line .= "</tr>";
            $table .= $line;
        }
        $table .= "
            </tbody>
        </table>".$paginator;
    }
}

?>