<?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; } } ?>