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

?>