<?php include('config.php'); $post_data = $_POST; if((isset($post_data['agence']) && $post_data['agence'] != '') && (isset($post_data['jour']) && $post_data['jour'] != '') && (isset($post_data['semaine']) && $post_data['semaine'] != '')) { // agence siège $SIEGE = 0; if($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"] && $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"]){ foreach ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"] as $choice) { if($choice == 'shipping'){ $AID = $_POST["agence"]; $q_agency_siege = "SELECT siege FROM ca_agences WHERE id_agence = ".$AID." LIMIT 1"; $stmt_agency_siege = excuteQuery($q_agency_siege); $res_agency_siege = $stmt_agency_siege["stmt"]; $o_agency_siege = $res_agency_siege->fetchObject(); if($o_agency_siege->siege == 1){ $SIEGE = 1; } } } } $post_data['semaine'] -= 1; $totalNumberOfrecode = array(); //[]; $avgWaitingLinechart = array(); //[]; $avgMettingLinechart = array(); //[]; $piechart = array(); //[]; $rdvDistribution = array(); //[]; $weeklyTotalNumberVisits = array(); //[]; $year = $post_data['jour']; $week_no = $post_data['semaine'] + 1; $values = array(); // PCZ : 2020/06/12 : FILTRAGE DU RESEAU DE PROXIMITE AVEC LES AGENCES AUTORISEES : //$agencyFilter = ($post_data['agence'] > 0) ? (" id_agence = ".$post_data['agence']) : (' id_agence IN (SELECT id_agence FROM ca_agences WHERE is_active = 1 AND id_agence_principale IN (SELECT id_agence_principale FROM ca_agence_principales WHERE id_secteur IN (SELECT id_secteur FROM ca_secteurs WHERE id_secteur IN (SELECT id_secteur FROM ca_drs WHERE id_dr = 1))))'); // Reseau de proximite id_dr = 1 if ($post_data['agence'] > 0) { $agencyFilter = " id_agence = :id_agence"; $values['id_agence'] = $post_data['agence']; } else { $agencyFilter = ((isset($_SESSION['profil']) && ($_SESSION['profil']==1 || $_SESSION['profil']==2)) || (!hasAccess(false,true) && $filterAgencies)) ? (' id_agence IN ('.getAllowedAgencies().')') : (' id_agence IN (SELECT id_agence FROM ca_agences WHERE id_agence_principale IN (SELECT id_agence_principale FROM ca_agence_principales WHERE id_secteur IN (SELECT id_secteur FROM ca_secteurs WHERE id_dr IN (SELECT id_dr FROM ca_drs WHERE id_dr = 1))))'); //echo $agencyFilter; } $week_start_date = new DateTime(); $week_start_date->setISODate(intval($year),intval($week_no)); $week_start = $week_start_date->format('Y-m-d'); $week_end_date = $week_start_date->modify('+6 days'); $week_end = $week_end_date->format('Y-m-d'); $dateFilter = " AND date_arrivee BETWEEN :week_start AND :week_end"; $values['week_start'] = $week_start.' 00:00:00'; $values['week_end'] = $week_end.' 23:59:59'; $annualNumberOfrecode = " SELECT COUNT(*) as total FROM ". STATISTICS_TABLE ." WHERE ".$agencyFilter.$dateFilter ; $resultCout = excuteQuery($annualNumberOfrecode, $values); $totalNumberOfrecode = $resultCout['stmt']->fetch(PDO::FETCH_ASSOC); $queryStatisticsPiechart = " SELECT * FROM ". STATISTICS_TABLE ." WHERE ".$agencyFilter.$dateFilter; $queryStatisticsPieResult = excuteQuery($queryStatisticsPiechart,$values); $appointmentTotal= 0; $noappointmentTotal = 0; $shipTotal = 0; $pieChartoutput = array(); $num_rows = $queryStatisticsPieResult['stmt']->rowCount(); if($num_rows > 0) { while($row = $queryStatisticsPieResult['stmt']->fetch(PDO::FETCH_ASSOC)) { if($row['rdv'] == '0') { $noappointmentTotal ++; } if($row['rdv'] == '1') { $appointmentTotal ++; } if($row['rdv'] == '5') { $shipTotal ++; } } } $queryWeekly = " SELECT WEEKDAY(date_arrivee) as dayname, COUNT(*) as total_appointments, COUNT(CASE when rdv = '0' then 1 END) as no_appointment, COUNT(CASE when rdv = '1' then 1 END) as with_appointment ".($SIEGE == 1 ? ', COUNT(CASE when rdv = "5" then 1 END) as ship' : '')." FROM ". STATISTICS_TABLE ." WHERE ".$agencyFilter.$dateFilter." GROUP BY dayname ORDER BY date_arrivee ASC"; $getAllrecodeofWeek = excuteQuery($queryWeekly,$values); $weekly_records = array(); $availableArriveDates = array(); while($row = $getAllrecodeofWeek['stmt']->fetch(PDO::FETCH_ASSOC)) { $get_date_arrivee = date('l', strtotime("Monday +{$row['dayname']} days")); $availableArriveDates[] = $get_date_arrivee; $weekly_records[$get_date_arrivee] = $row; } $queryAvgWatingTime = " SELECT date_arrivee, WEEKDAY(date_arrivee) as dayname, COUNT(*) as total_appointments, COUNT(case when rdv= '0' then duree_attente end) as totalnoAppointment, COUNT(case when rdv= '1' then duree_attente end) as toalappointment, ".($SIEGE == 1 ? 'COUNT(case when rdv= "5" then duree_attente end) as toalship,' : '')." SUM(TIME_TO_SEC(case when rdv= '0' then duree_attente end)) as noAppointment, SUM(TIME_TO_SEC(case when rdv= '1' then duree_attente end)) as appointment ".($SIEGE == 1 ? ', SUM(TIME_TO_SEC(case when rdv= "5" then duree_attente end)) as ship' : '')." FROM ". STATISTICS_TABLE ." WHERE ".$agencyFilter.$dateFilter." AND duree_attente IS NOT NULL GROUP BY dayname ORDER BY date_arrivee ASC"; $avgWaitingTime =excuteQuery($queryAvgWatingTime, $values); $allrecodeslinechart = array(); //[]; while($row = $avgWaitingTime['stmt']->fetch(PDO::FETCH_ASSOC)) { $noAppointmentMinutes = 0; $noAppointmentSeconds = 0; $appointmentMinutes = 0; $appointmentSeconds =0; $noAppointmentValue = 0; $appointmentValue = 0; $shipMinutes = 0; $shipSeconds = 0; $shipValue = 0; $timeIntervalLineChart = date('l', strtotime("Monday +{$row['dayname']} days")); if($row['totalnoAppointment'] > 0) { $noAppointmentInit = $row['noAppointment']; $noAppointmentMinutesCount = $noAppointmentInit / $row['totalnoAppointment']; $noAppointmentValue = $noAppointmentMinutesCount / 60; } if($row['toalappointment'] > 0) { $appointmentInit = $row['appointment']; $appointmentMinutesCount = $appointmentInit/$row['toalappointment']; $appointmentValue = $appointmentMinutesCount / 60; } if($SIEGE == 1){ if($row['toalship'] > 0) { $shipInit = $row['ship']; $shipMinutesCount = $shipInit/$row['toalship']; $shipValue = $shipMinutesCount / 60; } } $row['noAppointment'] = $noAppointmentValue; $row['appointment'] = $appointmentValue; if($SIEGE == 1){ $row['ship'] = $shipValue; } $allrecodesWaitingchart[$timeIntervalLineChart] = $row; } $queryAvgMettingTime = " SELECT date_arrivee, WEEKDAY(date_arrivee) as dayname, COUNT(*) as total_appointments, COUNT(case when rdv= '0' then duree_rdv end) as totalnoAppointment, COUNT(case when rdv= '1' then duree_rdv end) as toalappointment, ".($SIEGE == 1 ? 'COUNT(case when rdv= "5" then duree_rdv end) as toalship,' : '')." SUM(TIME_TO_SEC(case when rdv= '0' then duree_rdv end)) as noAppointment, SUM(TIME_TO_SEC(case when rdv= '1' then duree_rdv end)) as appointment ".($SIEGE == 1 ? ', SUM(TIME_TO_SEC(case when rdv= "5" then duree_rdv end)) as ship' : '')." FROM ". STATISTICS_TABLE ." WHERE ".$agencyFilter.$dateFilter." AND duree_rdv IS NOT NULL GROUP BY dayname ORDER BY date_arrivee ASC "; $avgMettingTime = excuteQuery($queryAvgMettingTime, $values); $allrecodesMettingchart= array(); //[]; while($row = $avgMettingTime['stmt']->fetch(PDO::FETCH_ASSOC)){ $noAppointmentMinutes = 0; $noAppointmentSeconds = 0; $appointmentMinutes = 0; $appointmentSeconds =0; $noAppointmentValue = 0; $appointmentValue = 0; $shipMinutes = 0; $shipSeconds = 0; $shipValue = 0; $timeIntervalMettingChart = date('l', strtotime("Monday +{$row['dayname']} days")); if($row['totalnoAppointment'] > 0) { $noAppointmentInit = $row['noAppointment']; $noAppointmentMinutesCount = $noAppointmentInit / $row['totalnoAppointment']; $noAppointmentValue = $noAppointmentMinutesCount / 60; } if($row['toalappointment'] > 0) { $appointmentInit = $row['appointment']; $appointmentMinutesCount = $appointmentInit/$row['toalappointment']; $appointmentValue = $appointmentMinutesCount / 60; } if($SIEGE == 1){ if($row['toalship'] > 0) { $shipInit = $row['ship']; $shipMinutesCount = $shipInit/$row['toalship']; $shipValue = $shipMinutesCount / 60; } } $row['noAppointment'] = $noAppointmentValue; $row['appointment'] = $appointmentValue; if($SIEGE == 1){ $row['ship'] = $shipValue; } $allrecodesMettingchart[$timeIntervalMettingChart] = $row; } $num_rows1 = $avgWaitingTime['stmt']->rowCount(); $waitingTimesList = array(); $meetingTimesList = array(); if($num_rows1 > 0) { $totalPie = $noappointmentTotal + $appointmentTotal + $shipTotal; $percentRDV = round($appointmentTotal / ($totalPie / 100),1); $percentSRDV = round($noappointmentTotal / ($totalPie / 100),1); $percentShip = round($shipTotal / ($totalPie / 100),1); $pieChartoutput = array( 'TASK' => 'Part des RDV (par jour)', 'noappointment' => $noappointmentTotal, 'appointment' => $appointmentTotal, 'ship' => $shipTotal, 'noappointmentpercent' => $percentSRDV, 'appointmentpercent' => $percentRDV, 'shippercent' => $percentShip, ); $avgWaitingLinechartTitle = array('Semaine', 'DMA avec RDV', array('role' => 'annotation'), array('role' => 'certainty'), array('role' => 'tooltip'), 'DMA sans RDV', array('role' => 'annotation'), array('role' => 'certainty'), array('role' => 'tooltip')); if($SIEGE == 1){ $avgWaitingLinechartTitle[] = 'DMA Livraison'; $avgWaitingLinechartTitle[] = array('role' => 'annotation'); $avgWaitingLinechartTitle[] = array('role' => 'certainty'); $avgWaitingLinechartTitle[] = array('role' => 'tooltip'); } $avgWaitingLinechart = array($avgWaitingLinechartTitle); $avgMettingLinechartTitle = array('Semaine', 'DME avec RDV', array('role' => 'annotation'), array('role' => 'certainty'), array('role' => 'tooltip'), 'DME sans RDV', array('role' => 'annotation'), array('role' => 'certainty'), array('role' => 'tooltip')); if($SIEGE == 1){ $avgMettingLinechartTitle[] = 'DME Livraison'; $avgMettingLinechartTitle[] = array('role' => 'annotation'); $avgMettingLinechartTitle[] = array('role' => 'certainty'); $avgMettingLinechartTitle[] = array('role' => 'tooltip'); } $avgMettingLinechart = array($avgMettingLinechartTitle); $weeklyTotalNumberVisitsTitle = array("Semaine", "Total", array('role' => 'style'), array('role' => 'annotation'), "Sans RDV", array('role' => 'style'), "Avec RDV", array('role' => 'style')); if($SIEGE == 1){ $weeklyTotalNumberVisitsTitle[] = "Livraison"; $weeklyTotalNumberVisitsTitle[] = array('role' => 'style'); } $weeklyTotalNumberVisits = array($weeklyTotalNumberVisitsTitle); $rdvDistribution = array(array('Semaine', 'RDV', array('role' => 'annotation'), array("role" =>"certainty"))); $firstColumnStyle = 'stroke-color: #000000; stroke-width: 1px;'; //$dayNameliast = [ $dayNameliast = array( 'Monday' => 'Lundi', 'Tuesday' => 'Mardi', 'Wednesday' => 'Mercredi', 'Thursday' => 'Jeudi', 'Friday' => 'Vendredi', 'Saturday' => 'Samedi' //]; ); for($i = 0; $i <= 5; $i++) { $date_with_week_year = date('l', strtotime("Monday +{$i} days")); if(isset($weekly_records[$date_with_week_year])) { $weeklyTotalNumberVisitsRow = array($dayNameliast[$date_with_week_year], (int)$weekly_records[$date_with_week_year]['total_appointments'], $firstColumnStyle, '', (int)$weekly_records[$date_with_week_year]['no_appointment'], '', (int)$weekly_records[$date_with_week_year]['with_appointment'], '' ); if($SIEGE == 1){ $weeklyTotalNumberVisitsRow[] = (int)$weekly_records[$date_with_week_year]['ship']; $weeklyTotalNumberVisitsRow[] = ''; } $weeklyTotalNumberVisits[] = $weeklyTotalNumberVisitsRow; $total = ($weekly_records[$date_with_week_year]['with_appointment'] * 100) / $weekly_records[$date_with_week_year]['total_appointments']; $rdvDistribution[] = array($dayNameliast[$date_with_week_year], round($total, 2), round($total, 2).'%', false); }else { $weeklyTotalNumberVisitsRow = array($dayNameliast[$date_with_week_year], 0, $firstColumnStyle, '', 0, '', 0, ''); if($SIEGE == 1){ $weeklyTotalNumberVisitsRow[] = 0; $weeklyTotalNumberVisitsRow[] = ''; } $weeklyTotalNumberVisits[] = $weeklyTotalNumberVisitsRow; $rdvDistribution[] = array($dayNameliast[$date_with_week_year], 0, '0%', false); } if(isset($allrecodesWaitingchart[$date_with_week_year])) { $waitingTimesList[] = (float)$allrecodesWaitingchart[$date_with_week_year]['appointment']; $waitingTimesList[] = (float)$allrecodesWaitingchart[$date_with_week_year]['noAppointment']; if($SIEGE == 1){ $waitingTimesList[] = (float)$allrecodesWaitingchart[$date_with_week_year]['ship']; } $avgWaitingLinechartData = array( $dayNameliast[$date_with_week_year], (float)$allrecodesWaitingchart[$date_with_week_year]['appointment'], toTime($allrecodesWaitingchart[$date_with_week_year]['appointment']), false, "DMA RDV : ".toTime($allrecodesWaitingchart[$date_with_week_year]['appointment']), (float)$allrecodesWaitingchart[$date_with_week_year]['noAppointment'], toTime($allrecodesWaitingchart[$date_with_week_year]['noAppointment']), false, "DMA SRDV : ".toTime($allrecodesWaitingchart[$date_with_week_year]['noAppointment']), ); if($SIEGE == 1){ $avgWaitingLinechartData[] = (float)$allrecodesWaitingchart[$date_with_week_year]['ship']; $avgWaitingLinechartData[] = toTime($allrecodesWaitingchart[$date_with_week_year]['ship']); $avgWaitingLinechartData[] = false; $avgWaitingLinechartData[] = "DMA Livraison : ".toTime($allrecodesWaitingchart[$date_with_week_year]['ship']); } $avgWaitingLinechart[] = $avgWaitingLinechartData; } else { $avgWaitingLinechartData = array($dayNameliast[$date_with_week_year], 0, "0", false, "Pas de données", 0, "0", false, "Pas de données"); if($SIEGE == 1){ $avgWaitingLinechartData[] = 0; $avgWaitingLinechartData[] = "0"; $avgWaitingLinechartData[] = false; $avgWaitingLinechartData[] = "Pas de données"; } $avgWaitingLinechart[] = $avgWaitingLinechartData; } if(isset($allrecodesMettingchart[$date_with_week_year])) { $meetingTimesList[] = (float)$allrecodesMettingchart[$date_with_week_year]['appointment']; $meetingTimesList[] = (float)$allrecodesMettingchart[$date_with_week_year]['noAppointment']; if($SIEGE == 1){ $meetingTimesList[] = (float)$allrecodesMettingchart[$date_with_week_year]['ship']; } $avgMettingLinechartData = array( $dayNameliast[$date_with_week_year], (float)$allrecodesMettingchart[$date_with_week_year]['appointment'], toTime($allrecodesMettingchart[$date_with_week_year]['appointment']), false, "DME RDV : ".toTime($allrecodesMettingchart[$date_with_week_year]['appointment']), (float)$allrecodesMettingchart[$date_with_week_year]['noAppointment'], toTime($allrecodesMettingchart[$date_with_week_year]['noAppointment']), false, "DME SRDV : ".toTime($allrecodesMettingchart[$date_with_week_year]['noAppointment']) ); if($SIEGE == 1){ $avgMettingLinechartData[] = (float)$allrecodesMettingchart[$date_with_week_year]['ship']; $avgMettingLinechartData[] = toTime($allrecodesMettingchart[$date_with_week_year]['ship']); $avgMettingLinechartData[] = false; $avgMettingLinechartData[] = "DME Livraison : ".toTime($allrecodesMettingchart[$date_with_week_year]['ship']); } $avgMettingLinechart[] = $avgMettingLinechartData; } else { $avgMettingLinechartData = array($dayNameliast[$date_with_week_year], 0, "0", false, "Pas de données", 0, "0", false, "Pas de données"); if($SIEGE == 1){ $avgMettingLinechartData[] = 0; $avgMettingLinechartData[] = "0"; $avgMettingLinechartData[] = false; $avgMettingLinechartData[] = "Pas de données"; } $avgMettingLinechart[] = $avgMettingLinechartData; } } } $WaitingMax = count($waitingTimesList)>0 ? max($waitingTimesList) : 0; $MaitingMax = count($meetingTimesList)>0 ? max($meetingTimesList) : 0; $barCharts = 0; // if(count($avgWaitingLinechart) > 0 && count(array_slice($avgWaitingLinechart, 1)) > 0){ // $WaitingMax = max(max(array_column(array_slice($avgWaitingLinechart, 1), 1)), max(array_column(array_slice($avgWaitingLinechart, 1), 4))); // } // if(count($avgMettingLinechart) > 0 && count(array_slice($avgMettingLinechart, 1)) > 0){ // $MaitingMax = max(array_column(array_slice($avgMettingLinechart, 1), 1)) + max(array_column(array_slice($avgMettingLinechart, 1), 3)); // } if(count($weeklyTotalNumberVisits) > 0 && count(array_slice($weeklyTotalNumberVisits, 1)) > 0){ //pr(max(array_column(array_slice($weeklyTotalNumberVisits, 1), '1'))); $barCharts = (float)(max(array_column(array_slice($weeklyTotalNumberVisits, 1), 1))) + (float)(max(array_column(array_slice($weeklyTotalNumberVisits, 1), 3))) + (float)(max(array_column(array_slice($weeklyTotalNumberVisits, 1), 5))); } $response = array(); $response = array( 'siege' => $SIEGE, 'weeklyTotalNumberVisits' => $weeklyTotalNumberVisits, 'piechart' => $pieChartoutput, 'rdvDistribution' => $rdvDistribution, 'avgWaiting' => $avgWaitingLinechart, 'avgMetting' => $avgMettingLinechart, 'year' => $_POST['jour'], 'month' => $_POST['semaine'], 'totalNumberOfrecode' => $totalNumberOfrecode, 'date' => date("Y/m/d"), 'avgWaitingMax' => $WaitingMax, 'avgMaitingMax'=> $MaitingMax, 'barChartMax' => $barCharts ); echo json_encode($response); } ?>