$postData['date_preset'], "startDate" => $postData['start'], "endDate" => $postData['end'], "splitMode" => $postData['split_mode'] ); if(isset($_REQUEST["level_filter"])){ if(!empty($postData["rdv"])){ $rdv = ($postData["rdv"] == "rdv") ? 1 : 0; $filter .= " AND s.rdv = ".$rdv; } if($date_preset == "custom-year"){ $selects .= ", YEAR(date_arrivee) as annee"; $groupBy[] = "YEAR(date_arrivee)"; $orderBy[] = "YEAR(date_arrivee)"; } if($split_mode){ switch($split_mode){ case 'non_split': break; case 'agence': $joins .= " LEFT JOIN ca_agences agence ON agence.id_agence = s.id_agence"; $selects .= (strpos($selects, 'agence.nom_agence') == FALSE) ? ", agence.nom_agence" : ""; $filter .= " AND agence.supp = 0"; $splitModeHeader = "Agence"; $splitModeColumn = "nom_agence"; $groupBy[] = "s.id_agence"; $orderBy[] = "nom_agence"; break; case 'conseiller': $joins .= " LEFT JOIN ca_conseillers c ON c.id_conseiller = s.".$action_split; $selects .= ", CONCAT(c.nom, ' ', c.prenom) as nom_conseiller"; $filter .= " AND c.supp=0"; $splitModeHeader = "Conseiller"; $splitModeColumn = "nom_conseiller"; $groupBy[] = "s.".$action_split; $orderBy[] = "nom_conseiller"; break; case 'day': $selects .= ", DATE_FORMAT(date_arrivee,'%d/%m/%Y') as day"; $splitModeHeader = "Jour"; $splitModeColumn = "day"; $groupBy[] = "DATE(date_arrivee)"; $orderBy[] = "DATE(date_arrivee)"; break; case 'week': $selects .= ", CONCAT('S',WEEK(date_arrivee,7),' ',SUBSTRING(YEARWEEK(date_arrivee,7),1,4)) AS week"; $splitModeHeader = "Semaine"; $splitModeColumn = "week"; $groupBy[] = "YEARWEEK(date_arrivee,7)"; $orderBy[] = "YEARWEEK(date_arrivee,7)"; break; case 'month': $selects .= ", DATE_FORMAT(date_arrivee,'%m/%Y') AS month"; $splitModeHeader = "Mois"; $splitModeColumn = "month"; $groupBy[] = "EXTRACT(YEAR_MONTH FROM date_arrivee)"; $orderBy[] = "EXTRACT(YEAR_MONTH FROM date_arrivee)"; break; case 'dow': $selects .= ", DAYOFWEEK(date_arrivee) AS dow"; $splitModeHeader = "Jour de la semaine"; $splitModeColumn = "dow"; $groupBy[] = "DAYOFWEEK(date_arrivee)"; $orderBy[] = "DAYOFWEEK(date_arrivee)"; break; case 'hour': $selects .= ", HOUR(date_arrivee) AS hour"; $splitModeHeader = "Heure"; $splitModeColumn = "hour"; $groupBy[] = "HOUR(date_arrivee)"; $orderBy[] = "HOUR(date_arrivee)"; break; case 'half_hour': $selects .= ", CONCAT(HOUR(date_arrivee),'|',FLOOR(MINUTE(date_arrivee)/30)) AS half_hour"; $splitModeHeader = "Demi heure"; $splitModeColumn = "half_hour"; $groupBy[] = "CONCAT(HOUR(date_arrivee),'|',FLOOR(MINUTE(date_arrivee)/30))"; $orderBy[] = "HOUR(date_arrivee), FLOOR(MINUTE(date_arrivee)/30)"; break; case 'dow_hour': $selects .= ", CONCAT(DAYOFWEEK(date_arrivee), '-', HOUR(date_arrivee)) AS dow_hour"; $splitModeHeader = "Jour de la semaine + heure"; $splitModeColumn = "dow_hour"; $groupBy[] = "CONCAT(DAYOFWEEK(date_arrivee), '-', HOUR(date_arrivee))"; $orderBy[] = "DAYOFWEEK(date_arrivee), HOUR(date_arrivee)"; break; case 'dow_half_hour': $selects .= ", CONCAT(DAYOFWEEK(date_arrivee), '-', HOUR(date_arrivee),'|',FLOOR(MINUTE(date_arrivee)/30)) AS dow_half_hour"; $splitModeHeader = "Jour de la semaine + heure"; $splitModeColumn = "dow_half_hour"; $groupBy[] = "CONCAT(DAYOFWEEK(date_arrivee), '-', HOUR(date_arrivee),'|',FLOOR(MINUTE(date_arrivee)/30))"; $orderBy[] = "DAYOFWEEK(date_arrivee), HOUR(date_arrivee), FLOOR(MINUTE(date_arrivee)/30)"; break; default: if($split_mode){ $joins .= (strpos($joins,"LEFT JOIN ca_agences") == FALSE) ? " LEFT JOIN ca_agences agence ON agence.id_agence = s.id_agence" : ""; $agencyParent = $levels[count($levels)-1]; $reverse_levels = array_reverse($levels); foreach($reverse_levels as $key => $level){ if($level == $agencyParent){ $joins .= " LEFT JOIN ca_".$level."s ".$level." ON ".$level.".id_".$level." = agence.id_parent"; } else{ $parent = $reverse_levels[$key-1]; $joins .= " LEFT JOIN ca_".$level."s ".$level." ON ".$level.".id_".$level." = ".$parent.".id_".$parent; } if($level == $split_mode){ break; } } $selects .= (strpos($selects, $split_mode.'.nom_'.$split_mode) == FALSE) ? (", ".$split_mode.".nom_".$split_mode) : ""; $filter .= " AND agence.supp = 0"; $splitModeHeader = "".ucfirst($split_mode).""; $splitModeColumn = "nom_".$split_mode; $groupBy[] = $split_mode.".id_".$split_mode; $orderBy[] = "nom_".$split_mode; } break; } } if($hierarchical_mode){ if($levels){ $joins .= (strpos($joins,"LEFT JOIN ca_agences") == FALSE) ? " LEFT JOIN ca_agences agence ON agence.id_agence = s.id_agence" : ""; $agencyParent = $levels[count($levels)-1]; $selects .= (strpos($selects,"agence.nom_agence") == FALSE) ? ", agence.nom_agence " : ""; $selects .= ", ".$agencyParent.".nom_".$agencyParent." "; $joins .= (strpos($joins,"LEFT JOIN ca_".$agencyParent."s") == FALSE) ? (" LEFT JOIN ca_".$agencyParent."s ".$agencyParent." ON ".$agencyParent.".id_".$agencyParent." = agence.id_parent") : ""; $reverse_levels = array_reverse($levels); foreach($reverse_levels as $key => $level){ if($level != $agencyParent){ $selects .= ", ".$level.".nom_".$level." "; $parent = $reverse_levels[$key-1]; $joins .= (strpos($joins,"LEFT JOIN ca_".$level."s") == FALSE) ? (" LEFT JOIN ca_".$level."s ".$level." ON ".$level.".id_".$level." = ".$parent.".id_".$parent) : ""; } $groupBy[] = $level.".id_".$level; $hierarchicalModeColumns[] = "nom_".$level; $hierarchicalModeHeaders[] = "".ucfirst($level).""; } $groupBy[] = "agence.id_parent"; $hierarchicalModeColumns = array_reverse($hierarchicalModeColumns); $hierarchicalModeHeaders = array_reverse($hierarchicalModeHeaders); } } if($nfc_enabled){ $selects .= ", COUNT(CASE WHEN nfcprov=1 THEN 1 END) AS nb_nfc"; if($nfc){ $filter .= " AND nfcprov = 1"; } } if($motifs_enabled){ $presta_split = filterActionMotifs($presta_splits); $subquery = ""; $subQueryFilter = $subQuerySelects = array(); if(count($presta_split["prestas"])){ $subQueryFilter[] = "cm.id_motif IN (".implode(",",$presta_split["prestas"]).")"; } if(count($presta_split["actions"])){ $subQueryFilter[] = "rdv_type IN (".implode(",",$presta_split["actions"]).")"; } // $joins .= " LEFT JOIN ca_historique_client_motifs cm ON cm.id_client = s.id_client"; foreach($motifs as $motif) { if(isset($motif["is_action"]) && $motif["is_action"]){ $actionID = str_replace("action_","",$motif["id_motif"]); $subQuerySelects[] = 'COUNT(CASE WHEN rdv_type = "'.$actionID.'" THEN 1 END) AS presta_'.$motif["id_motif"]; } else{ $subQuerySelects[] = 'COUNT(CASE WHEN cm.id_motif='.$motif["id_motif"].' THEN 1 END) AS presta_'.$motif["id_motif"]; } $selects .= ', COALESCE(SUM(presta_'.$motif["id_motif"].'),0) AS nb_presta_'.$motif["id_motif"]; } $joinType = (count($presta_split["prestas"]) || count($presta_split["prestas"])) ? " INNER" : " LEFT"; $joins .= $joinType." JOIN ( SELECT id_client, ".implode(',',$subQuerySelects)." FROM ca_historique_client_motifs cm ".($subQueryFilter ? ('WHERE '.implode("AND ",$subQueryFilter)) : '')." GROUP BY id_client )motifs ON motifs.id_client = s.id_client"; } $groupBy = $groupBy ? (" GROUP BY ".implode(',',$groupBy)) : ""; $orderBy = $orderBy ? (" ORDER BY ".implode(',',$orderBy)) : ""; $recapRecords = " SELECT 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, 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= '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 duree_attente is null then null else duree_attente end)) as avgWaiting, AVG(TIME_TO_SEC(case when duree_rdv is null then null else duree_rdv end)) as avgMeeting ".$selects." FROM ". STATISTICS_TABLE ." s ".$joins." WHERE DATE(date_arrivee) BETWEEN :date_end AND :date_start " . $filter.$groupBy.$orderBy;// ." GROUP BY client";// LIMIT " . ( ( $currentPage - 1 ) * $itemsPerPage ) . ", $itemsPerPage"; if(isset($_REQUEST["debug"])){ print $recapRecords; } $values = array('date_end' => $postData['start'], 'date_start' => $postData['end']); $recapResult = excuteQuery($recapRecords,$values); $num_rows = $recapResult['stmt']->rowCount(); $table = ""; $chartData = array(); if($num_rows > 0) { $table = ""; $table .= ($date_preset == "custom-year") ? "" : ""; $table .= $split_mode ? $splitModeHeader : ""; $table .= $hierarchical_mode ? implode('',$hierarchicalModeHeaders) : ""; $table .= ''; $table .= $time_split ? '' : ""; $table .= (!$rdv_filter || $rdv_filter == "rdv") ? '' : ""; $table .= (!$rdv_filter || $rdv_filter == "srdv") ? '' : ""; $table .= $nfc_enabled ? "" : ""; $table .= ''; $table .= (!$rdv_filter || $rdv_filter == "rdv") ? ('') : ""; $table .= (!$rdv_filter || $rdv_filter == "srdv") ? ('') : ""; $table .= $motifs_enabled ? getMotifsHeaders($motifs) : ""; $table .= " "; while($res = $recapResult['stmt']->fetch(PDO::FETCH_ASSOC)) { $line = ""; $line .= ($date_preset == "custom-year") ? ("") : ""; if($split_mode && $split_mode != "non_split"){ $res[$splitModeColumn] = formatSplitColumn($split_mode, $res[$splitModeColumn]); $line .= ""; } if($hierarchical_mode){ foreach($hierarchicalModeColumns as $hierarchicalModeColumn){ $res[$hierarchicalModeColumn] = $res[$hierarchicalModeColumn]; $line .= ""; } } $line .= ""; if($time_split){ $avg = get_avg($res['nbVisits'],$startDate,$endDate,$split_mode,$res[$splitModeColumn]); $line .= ""; } $line .= (!$rdv_filter || $rdv_filter == "rdv") ? ("") : ""; $line .= (!$rdv_filter || $rdv_filter == "srdv") ? ("") : ""; $line .= ($nfc_enabled) ? ("") : ""; $line .= " "; $line .= (!$rdv_filter || $rdv_filter == "rdv") ? (" ") : ""; $line .= (!$rdv_filter || $rdv_filter == "srdv") ? (" ") : ""; $line .= ($motifs_enabled) ? getMotfisValues($motifs, $res) : ""; $line .= " "; $table .= $line; $chartData[] = $res; } $table .= "
AnnéeNombreMoyenneRDVSRDVNFCDMA DMEDMA RDV DME RDVDMA SRDV DME SRDV
".$res["annee"]."".($res[$splitModeColumn])."".$res[$hierarchicalModeColumn]."".$res["nbVisits"]."".$avg."".$res["appointment"]."".$res["noAppointment"]."".$res["nb_nfc"]."".gmdate('H:i:s',$res["avgWaiting"])." ".gmdate('H:i:s',$res["avgMeeting"])."".gmdate('H:i:s',$res["appointmentAttente"])." ".gmdate('H:i:s',$res["appointmentEntretien"])."".gmdate('H:i:s',$res["noAppointmentAttente"])." ".gmdate('H:i:s',$res["noAppointmentEntretien"])."
"; if(!empty($graph_mode)){ drawChart($chartData, $graph_mode, $split_mode, $motifs); } } } function getMotifsHeaders($motifs){ $headers = ""; foreach($motifs as $motif){ $headers .= ''.($motif["titre"]).''; } return $headers; } function getMotfisValues($motifs, $data){ $rows = ""; foreach($motifs as $motif){ $rows .= "".$data["nb_presta_".$motif["id_motif"]].""; } return $rows; } function calcPercent($total, $val){ if(!$total) { return 0; } $percent = $val / $total; return number_format($percent * 100, 2 ); } function rand_color() { return sprintf('#%06X', mt_rand(0, 0xFFFFFF)); } function drawChart($data, $graph_mode, $split_mode, $motifs){ $graph_modes = ["count" => "Nombre de visiteurs", "rdv_count" => "RDV/SRDV (nombre)", "rdv_percent" => "RDV/SRDV (%)", "presta_count" => "Motif (nombre)", "presta_percent" => "Motif (%)","dma" => "DMA", "dme" => "DME", "dma-dme" => "DMA + DME"]; $split_modes = ["" => "Nombre de visiteurs", "secteur" => "nom_secteur", "agence" => "nom_agence", "conseiller" => "nom_conseiller", "day" => "day","month" => "month", "week" => "week", "dow" => "dow", "hour" => "hour", "half_hour" => "half_hour", "dow_hour" => "dow_hour", "dow_half_hour" => "dow_half_hour"]; switch($graph_mode){ case 'count': $standardChartData = array(array('Type', $graph_modes[$graph_mode], array('role' => 'style'))); $nb = count($data); foreach($data as $row){ $color = "#C0504D";//$nb > 1 ? rand_color() : "#C0504D"; $legendColors[] = $color; $title = ($row[$split_modes[$split_mode]]); $standardChartData[] = array($title, (int)$row['nbVisits'],$color); } break; case 'rdv_count': $nb = count($data); $colors = array("RDV"=>'#C0504D', "SRDV"=>'#4F81BD'); $legendColors = array($colors['RDV'], $colors['SRDV']); if($split_mode == 'non_split'){ $standardChartData = array(array('Type', 'RDV', array('role' => 'style'), 'SRDV', array('role' => 'style'))); $standardChartData[] = array('Nombre de visiteurs',(int)$data[0]['appointment'],$colors['RDV'], (int)$data[0]['noAppointment'],$colors['SRDV']); } else{ $standardChartData = array(array('Type', 'RDV', array('role' => 'style'), 'SRDV', array('role' => 'style'))); foreach($data as $row){ $titles = array("RDV"=>$row[$split_modes[$split_mode]], "SRDV"=>$row[$split_modes[$split_mode]]); $standardChartData[] = array($titles['RDV'], (int)$row['appointment'],$colors['RDV'], (int)$row['noAppointment'],$colors['SRDV']); } } break; case 'rdv_percent': $nb = count($data); $colors = array("RDV"=>'#C0504D', "SRDV"=>'#4F81BD'); $legendColors = array($colors['RDV'], $colors['SRDV']); if($split_mode == 'non_split'){ $standardChartData = array(array('Type', 'RDV/SRDV (%)', array('role' => 'style'))); $titles = array("RDV"=>'RDV', "SRDV"=>'SRDV'); $standardChartData[] = array('RDV',(int) $data[0]['appointment'],$colors['RDV']); $standardChartData[] = array('SRDV',(int) $data[0]['noAppointment'],$colors['SRDV']); } else{ $standardChartData = array(array('Type', 'RDV', array('role' => 'style'), 'SRDV', array('role' => 'style'))); foreach($data as $row){ $titles = array("RDV"=>$row[$split_modes[$split_mode]], "SRDV"=>$row[$split_modes[$split_mode]]); $standardChartData[] = array($titles['RDV'], (int) $row['appointment'],$colors['RDV'], (int) $row['noAppointment'],$colors['SRDV']); } } break; case 'presta_count': $legendColors = getMotifsColors($motifs); if($split_mode == 'non_split'){ $standardChartData = array(array('Type', $graph_modes[$graph_mode], array('role' => 'style'))); foreach($motifs as $k => $motif){ $standardChartData[] = array(($motif["titre"]), (int) $data[0]['nb_presta_'.$motif["id_motif"]],array("#4f81bd")); } } else{ $titles = array(); $standardChartData = array(); foreach($motifs as $k => $motif){ $motifHeaders[] = ($motif["titre"]); $motifHeaders[] = array('role' => 'style'); } $standardChartData[] = array_merge(array('Type'),$motifHeaders); foreach($data as $key => $row){ $rowChartData = array($row[$split_modes[$split_mode]]); $motifsChart = array(); foreach($motifs as $k => $motif){ $motifsChart[] = (int) $data[$key]['nb_presta_'.$motif["id_motif"]]; $motifsChart[] = $legendColors[$k]; } $standardChartData[] = array_merge($rowChartData,$motifsChart); } } break; case 'presta_percent': $legendColors = getMotifsColors($motifs); if($split_mode == 'non_split'){ $standardChartData = array(array('Type', $graph_modes[$graph_mode], array('role' => 'style'))); foreach($motifs as $k => $motif){ $standardChartData[] = array(($motif["titre"]), (float) calcPercent($data[0]['nbVisits'],$data[0]['nb_presta_'.$motif["id_motif"]]),$legendColors[$k]); } } else{ $titles = array(); $standardChartData = array(); foreach($motifs as $k => $motif){ $motifHeaders[] = ($motif["titre"]); $motifHeaders[] = array('role' => 'style'); } $standardChartData[] = array_merge(array('Type'),$motifHeaders); foreach($data as $key => $row){ $rowChartData = array($row[$split_modes[$split_mode]]); $motifsChart = array(); foreach($motifs as $k => $motif){ $motifsChart[] = (int) $data[$key]['nb_presta_'.$motif["id_motif"]]; $motifsChart[] = $legendColors[$k]; } $standardChartData[] = array_merge($rowChartData,$motifsChart); } } break; case 'dma': $nb = count($data); $standardChartData = array(array('Type', "Durée moyenne d'attente", array('role' => 'annotation'), array('role' => 'tooltip'), array('role' => 'style'))); foreach($data as $row){ $attente = (float) ($row['avgWaiting'] / 60); $attenteTime = toTime($attente); $title = $nb > 1 ? $row[$split_modes[$split_mode]] : "DMA"; $color = '#C0504D';//($split_mode == "non_split") ? '#C0504D' : rand_color(); $legendColors[] = $color; $standardChartData[] = array($title, $attente, $attenteTime, "Durée moyenne d'attente:" . $attenteTime, $color); } break; case 'dme': $nb = count($data); $standardChartData = array(array('Type', "Durée moyenne d'entretien", array('role' => 'annotation'), array('role' => 'tooltip'), array('role' => 'style'))); foreach($data as $row){ $entretien = (float) ($row['avgMeeting'] / 60); $entretienTime = toTime($entretien); $title = $nb > 1 ? $row[$split_modes[$split_mode]] : "DME"; $color = '#C0504D' ;//($split_mode == "non_split") ? '#C0504D' : rand_color(); $legendColors[] = $color; $standardChartData[] = array($title, $entretien, $entretienTime, "Durée moyenne d'entretien:" . $entretienTime, $color); } break; case 'dma-dme': $nb = count($data); $legendColors = array('#C0504D','#4F81BD'); if($split_mode == 'non_split'){ $standardChartData = array(array('Type', $graph_modes[$graph_mode], array('role' => 'annotation'), array('role' => 'tooltip'), array('role' => 'style'))); $appointmentAttente = (float) ($data[0]['avgWaiting'] / 60); $appointmentEntretien = (float) ($data[0]['avgMeeting'] / 60); $appointmentAttenteTime = toTime($appointmentAttente); $appointmentEntretienTime = toTime($appointmentEntretien); $standardChartData[] = array('DMA', $appointmentAttente, $appointmentAttenteTime, "Durée moyenne d'attente:" . $appointmentAttenteTime, '#C0504D'); $standardChartData[] = array('DME', $appointmentEntretien, $appointmentEntretienTime, "Durée moyenne d'entretien :" . $appointmentEntretienTime, '#C0504D'); } else{ $standardChartData = array(array('Type', 'DMA', array('role' => 'annotation'), array('role' => 'tooltip'), array('role' => 'style'), 'DME', array('role' => 'annotation'), array('role' => 'tooltip'), array('role' => 'style'))); foreach($data as $row){ $appointmentAttente = (float) ($row['avgWaiting'] / 60); $appointmentEntretien = (float) ($row['avgMeeting'] / 60); $appointmentAttenteTime = toTime($appointmentAttente); $appointmentEntretienTime = toTime($appointmentEntretien); $standardChartData[] = array($row[$split_modes[$split_mode]], $appointmentAttente, $appointmentAttenteTime, "Durée moyenne d'attente:" . $appointmentAttenteTime, '#C0504D', $appointmentEntretien, $appointmentEntretienTime, "Durée moyenne d'entretien :" . $appointmentEntretienTime, '#4F81BD'); } } break; default: break; } echo ''; } function getMotifsColors($motifs){ $motifs_colors = array(); foreach($motifs as $motif){ $motifs_colors[] = rand_color(); } return $motifs_colors; } function english_dow($day){ $days = array("", "Dimanche" => "Sunday", "Lundi" => "Monday", "Mardi" => "Tuesday", "Mercredi" => "Wednesday", "Jeudi" => "Thursday", "Vendredi" => "Friday", "Samedi" => "Saturday"); return $days[$day]; } function month($month){ $months = array("", "janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre"); return $months[$month+0]; } function dow($dow){ $days = array("", "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"); return $days[$dow]; } function hour($hour) { return $hour.'h-'.($hour+1).'h'; } function half_hour($half_hour) { $components = explode('|', $half_hour); $starthour = $components[0]; $startminute = '00'; $endhour = $starthour; $endminute = '30'; if($components[1] == 1){ $startminute = '30'; $endhour++; $endminute = '00'; } return $starthour.':'.$startminute .'-'. $endhour .':'. $endminute; } function formatSplitColumn($splitMode, $value){ switch($splitMode){ case 'month': $components = explode('/', $value); $value = ucfirst(month($components[0]))." ".$components[1]; break; case 'dow': $value = dow($value); break; case 'hour': $value = $value.'h-'.($value+1).'h'; break; case 'half_hour': $components = explode('|', $value); $starthour = $components[0]; $startminute = '00'; $endhour = $starthour; $endminute = '30'; if($components[1] == 1){ $startminute = '30'; $endhour++; $endminute = '00'; } $value = $starthour.':'.$startminute .'-'. $endhour .':'. $endminute; break; case 'dow_hour': $components = explode('-',$value); $value = dow($components[0]). ' - '. hour($components[1]); break; case 'dow_half_hour': $components = explode('-',$value); $day = $components[0]; $half_hour = $components[1]; $components = explode('|', $half_hour); $starthour = $components[0]; $startminute = '00'; $endhour = $starthour; $endminute = '30'; if($components[1] == 1){ $startminute = '30'; $endhour++; $endminute = '00'; } $value = dow($day). ' '. $starthour.':'.$startminute .'-'. $endhour .':'. $endminute; break; default: break; } return $value; } function periodcount($relativedate, $startdate, $enddate, $counter) { if(strtotime($relativedate, $startdate) > $enddate) { return $counter; } else { return periodcount($relativedate, strtotime($relativedate, $startdate), $enddate, ++$counter); } } function is_start_day_dow($startTime,$currentDay){ $days = array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"); $startDay = date('w', $startTime); return $days[$startDay] === $currentDay; } function get_avg($count, $startDate, $endDate, $split_mode, $split){ $startTime = strtotime($startDate); $endTime = strtotime($endDate); switch ($split_mode){ case "dow": $startNb = 0; $currentDay = english_dow($split); if(is_start_day_dow($startTime,$currentDay)){ $startNb = 1; } $nb = periodcount("next ".$currentDay,$startTime,$endTime, $startNb); break; case "hour": $split_hours = explode("h-",$split); $fromHour = $split_hours[0]; $toHour = str_replace("h","",$split_hours[1]); $startTime = strtotime($startDate." ".$fromHour.":00:00"); $endTime = strtotime($endDate." 23:59:59"); $nbSaturdays = periodcount("next saturday",$startTime,$endTime, 0); $nbSundays = periodcount("next sunday",$startTime,$endTime, 0); $nbDays = periodcount("next day",$startTime,$endTime, 1); $nb = $nbDays - $nbSaturdays - $nbSundays; break; case "half_hour": $split_hours = explode("-",$split); $fromHour = $split_hours[0]; $startTime = strtotime($startDate." ".$fromHour.":00"); $endTime = strtotime($endDate." 23:59:59"); $nbSaturdays = periodcount("next saturday +30 minutes",$startTime,$endTime, 0); $nbSundays = periodcount("next sunday +30 minutes",$startTime,$endTime, 0); $nbHalfHours = periodcount("next day +30 minutes",$startTime,$endTime, 1); $nb = $nbHalfHours - $nbSaturdays - $nbSundays; break; case "dow_hour": $startNb = 0; $split_dow_hour = explode(" - ",$split); $day = $split_dow_hour[0]; $split_hours = explode("h-",$split_dow_hour[1]); $fromHour = $split_hours[0]; $startTime = strtotime($startDate." ".$fromHour.":00"); $endTime = strtotime($endDate." 23:59:59"); $currentDay = english_dow($day); if(is_start_day_dow($startTime,$currentDay)){ $startNb = 1; } $nb = periodcount("next ".$currentDay,$startTime,$endTime, $startNb); break; case "dow_half_hour": $startNb = 0; $split_dow_hour = explode(" ",$split); $day = $split_dow_hour[0]; $split_hours = explode("-",$split_dow_hour[1]); $fromHour = $split_hours[0]; $startTime = strtotime($startDate." ".$fromHour.":00"); $endTime = strtotime($endDate." 23:59:59"); $currentDay = english_dow($day); if(is_start_day_dow($startTime,$currentDay)){ $startNb = 1; } $nb = periodcount("next ".$currentDay,$startTime,$endTime, $startNb); break; } return $nb ? round($count/$nb,2) : 0; } function load_motifs(){ $motifsRecords = "SELECT id_motif, titre FROM ca_motifs WHERE enabled=1"; $motifsResult = excuteQuery($motifsRecords); $num_rows =$motifsResult['stmt']->rowCount(); $motifs = array(); if($num_rows > 0) { while($res = $motifsResult['stmt']->fetch(PDO::FETCH_ASSOC)) { $res["titre"] = strip_tags($res["titre"]); $motifs[] = $res; } } if($GLOBALS["BO_CONF"]["AFA"] && !$GLOBALS["BO_CONF"]["AFA_MOTIFS"]){ $actions = load_motif_actions(); foreach($actions as $action){ $motifs[] = array("titre" => strip_tags($action->titre), "id_motif" => "action_".$action->id_action, "is_action" => true); } } return $motifs; } function load_motif_actions(){ $actions = array(); $actionsRecords = "SELECT id_action, titre, code_action, is_motif FROM ca_actions WHERE is_active=1 AND is_motif=1 GROUP BY id_action"; $actionsResult = excuteQuery($actionsRecords); $num_rows =$actionsResult['stmt']->rowCount(); if($num_rows > 0) { while($res = $actionsResult['stmt']->fetch(PDO::FETCH_OBJ)) { $actions[$res->id_action] = $res; } } return $actions; } function filterActionMotifs($motifs){ $actions = array(); foreach($motifs as $key => $value){ if(strpos($value, "action_") !== FALSE){ $actions[] = str_replace("action_","",$value); unset($motifs[$key]); } } return array("prestas" => $motifs, "actions" => $actions); } function getLevels(){ if(!$GLOBALS["BO_CONF"]["HIERARCHICAL"]["ENABLED"]){ return array(); } $levels = $GLOBALS["BO_CONF"]["HIERARCHICAL"]["HIERARCHY"]; array_pop($levels); return $levels; } function getDropDownData($levels, $filterAgencies, $options = true){ $levels = getLevels(); $selects = $joins = array(); $from = $orders = ""; $is_stats = isset($_SESSION['profil']) && $_SESSION['profil'] == 1; $filters = (($is_stats || !hasAccess(false,true)) && $filterAgencies) ? ('AND id_agence IN ('.getAllowedAgencies().')') : ''; if(empty($levels)){ $query = 'select nom_agence, id_agence, code_agence from '.AGENCIES_TABLE.' where supp = 0 and is_active = 1 '.$filters.' order by nom_agence'; } else{ foreach($levels as $level){ $selects[] = $level.".id_".$level.", ".$level.".nom_".$level; if($levels[0] == $level){ $from = "ca_".$level."s as ".$level; } else{ $parent = getLevelParent($levels, $level); $joins[] = "LEFT JOIN ca_".$level."s as ".$level." ON ".$level.".id_".$parent." = ".$parent.".id_".$parent; $orders .= $parent.".id_".$parent.","; } } $agencyParent = $levels[count($levels)-1]; $selects[] = "agence.id_agence, agence.nom_agence"; $joins[] = "LEFT JOIN ".AGENCIES_TABLE." agence ON agence.id_parent = ".$agencyParent.".id_".$agencyParent; $query = "SELECT ".implode(',',$selects)." FROM ".$from." ".implode(' ',$joins)." WHERE agence.supp=0 AND agence.is_active = 1 ".$filters. " ORDER BY ".$orders." agence.nom_agence"; } $result = excuteQuery($query); $num_rows = $result['stmt']->rowCount(); $flat = false; if($levels && !$num_rows){ $flat = true; $query = 'select agence.nom_agence, agence.id_agence, agence.code_agence from '.AGENCIES_TABLE.' agence where agence.supp = 0 and agence.is_active = 1 '.$filters.' order by agence.nom_agence'; $result = excuteQuery($query); } return $options ? getDropDownOptions($result['stmt'], $levels, $filterAgencies,$flat) : $result['stmt']; } function getDatePresets($date_preset, $postData){ switch ($date_preset) { case 'today': $startDate = 'today'; $endDate = 'today'; break; case 'yesterday': $startDate = 'yesterday'; $endDate = 'yesterday'; break; case 'current-week': $startDate = 'next monday -1 week'; $endDate = 'sunday this week'; break; case 'last-week': $startDate = 'monday last week'; $endDate = 'sunday last week'; break; case 'last-7-days': $startDate = '-7 days'; $endDate = 'yesterday'; break; case 'current-month': $startDate = date('Y-m-d', mktime(0,0,0,date('m'),1,date('Y'))); $endDate = date('Y-m-d',mktime(0, 0, 0, date('m')+1, 0, date('Y'))); break; case 'last-month': $startDate = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1)); $endDate = date("Y-m-d", mktime(0, 0, 0, date("m"), 0)); break; case 'last-30-days': $startDate = '-30 days'; $endDate = 'yesterday'; break; case 'current-year': $startDate = '1/1 this year'; $endDate = 'today'; break; case 'last-year': $startDate = '1/1 last year'; $endDate = '1/1 this year -1 day'; break; case 'custom': $startDate = $postData['start']; $endDate = $postData['end']; break; case 'custom-year': $startDate = $postData['startYear']; $endDate = $postData['endYear']; break; } return array("start" => $startDate, "end" => $endDate); } function getDropDownOptions($data, $levels, $filterAgencies, $flat = false){ $options = ""; if((isset($_SESSION['profil']) && $_SESSION['profil'] == 1) || hasAccess(false,true) || $filterAgencies){ $options .= ''; } if(empty($levels) || $flat){ while($row = $data->fetch(PDO::FETCH_ASSOC)) { $options .= ''; } } else{ $levels_filters = array(); while($row = $data->fetch(PDO::FETCH_ASSOC)) { /* $row = array_filter($row, function($k) { return !is_numeric($k); }); */ $levels_filters[] = $row; } $tree = array(); $result_levels = $levels_filters[0]; $filter_keys = array_keys($result_levels); $filter_keys = array_filter($filter_keys, function($k) { return gettype($k) == 'string'; }); $filter_keys = array_values($filter_keys); $nb_hierarchy = count($filter_keys); foreach ($levels_filters as $filter){ $i = 1; if(isset($filter_keys[$i+4])){ $colors = array("#CCCCCC", "#DDDDDD", "#EEEEEE", "#FFFFFF"); $first = ($filter[$filter_keys[$i]])."|".$filter[$filter_keys[$i-1]]."|".$colors[0]; //nom_cr|id_cr|couleur $second = ($filter[$filter_keys[$i+2]])."|".$filter[$filter_keys[$i+1]]."|".$colors[1]; //nom_dr|id_dr|couleur $third = ($filter[$filter_keys[$i+4]])."|".$filter[$filter_keys[$i+3]]."|".$colors[2]; //nom_secteur|id_secteur|couleur $fourth = ($filter[$filter_keys[$i+6]])."|".$filter[$filter_keys[$i+5]]."|".$colors[3]; //nom_agence|id_agence|couleur $tree[$first][$second][$third][] = $fourth; } else if(isset($filter_keys[$i+2])){ $colors = array("#DDDDDD", "#EEEEEE", "#FFFFFF"); $first = ($filter[$filter_keys[$i]])."|".$filter[$filter_keys[$i-1]]."|".$colors[0]; $second = ($filter[$filter_keys[$i+2]])."|".$filter[$filter_keys[$i+1]]."|".$colors[1]; $third = ($filter[$filter_keys[$i+4]])."|".$filter[$filter_keys[$i+3]]."|".$colors[2]; $tree[$first][$second][] = $third; } else if(isset($filter_keys[$i+1])){ $colors = array("#EEEEEE", "#FFFFFF"); $first = ($filter[$filter_keys[$i]])."|".$filter[$filter_keys[$i-1]]."|".$colors[0]; $second = ($filter[$filter_keys[$i+2]])."|".$filter[$filter_keys[$i+1]]."|".$colors[1]; $tree[$first][] = $second; } else{ if($filter[$filter_keys[$i]]){ $colors = array("#FFFFFF"); $first = ($filter[$filter_keys[$i]])."|".$filter[$filter_keys[$i-1]]."|".$colors[0]; $tree[] = $first; } } } $deep = $nb_hierarchy/2; $treeOptions = ''; toDropdown($tree, $deep-1, $treeOptions); $options .= $treeOptions; } return $options; } function getLevelParent($levels, $level){ if($levels && $levels[0] == $level){ return false; } for($i=0; $i $row) { $segments = explode('|', $row); if (count($segments) !== 3) { // error } else { list($name, $id, $style) = $segments; $levelStyle = $style ? ' style="background-color:'.$style.';"' : ''; } if($name && $id) $options .= ''.htmlspecialchars($name).''; } } else{ foreach ($data as $key => $row) { $depthChar = '  '; $_spaces = str_repeat($depthChar, $depth); $segments = explode('|', $key); if (count($segments) !== 3) { // error } else { list($name, $id, $style) = $segments; $levelStyle = $style ? ' style="background-color:'.$style.';"' : ''; } if($name && $id) $options .= ''.$_spaces.htmlspecialchars($name).''; if (!is_array(array_values($row)[0])) { $_spaces .= $depthChar; foreach($row as $item){ $segments = explode('|', $item); if (count($segments) !== 3) { // error } else { list($name, $id, $style) = $segments; $levelStyle = $style ? ' style="background-color:'.$style.';"' : ''; } if($name && $id) $options .= ''.$_spaces.htmlspecialchars($name).''; } } else{ if($maxDepth >= $depth){ toDropdown($row, $maxDepth, $options, $depth+1); } } } } } ?>