<?php
include ('../../inc/confNetisse.php');
if(isset($GLOBALS["BORNE_CONF"]["NFC"]) && $GLOBALS["BORNE_CONF"]["NFC"])
include ("../../inc/nfcConfig.php");
$TYPE = isset($_REQUEST['type']) ? $_REQUEST['type'] : "";
$CODE = isset($_REQUEST['code']) ? $_REQUEST['code'] : "";
$IDA = isset($_REQUEST['id']) ? $_REQUEST['id'] : "";
$DATA = array();
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);
function getBorneTranslations($type){
$languages = isset($GLOBALS["BORNE_CONF"]["LANGUAGES"])?$GLOBALS["BORNE_CONF"]["LANGUAGES"]:array("FR");
foreach($languages as $lang){
$lang = strtolower($lang);
if(file_exists('../../inc/languages/'.$lang.'_type_'.$type.'.po')){
$file = '../../inc/languages/'.$lang.'_type_'.$type.'.po';
}
else{
$file = '../../inc/languages/'.$lang.'.po';
}
$translations[$lang] = array();
$po = file($file);
$current = null;
foreach ($po as $line) {
if (substr($line,0,5) == 'msgid') {
$current = trim(substr(trim(substr($line,5)),1,-1));
}
if (substr($line,0,6) == 'msgstr') {
$translations[$lang][$current] = trim(substr(trim(substr($line,6)),1,-1));
}
}
}
return $translations;
}
function getBorneTemplates($SERVER){
$dirTpl = "../../assets/templates/borne";
$return_array = array();
$templates = array(
"footer" => "./templates/includes/footer.html",
"accompanying-page" => "./templates/accompanying-page.html",
"conseiller-pagination" => "./templates/conseiller-pagination.html",
"languages" => "./templates/languages.html",
"choice-page" => "./templates/choice-page.html",
"conseiller-panel" => "./templates/conseiller-panel.html",
"motif-panel" => "./templates/motif-panel.html",
"civilite-client" => "./templates/civilite-client.html",
"conseiller-tab" => "./templates/conseiller-tab.html",
"error-popins" => "./templates/error-popins.html",
"nfc-page" => "./templates/nfc-page.html",
"conseiller-nav" => "./templates/conseiller-nav.html",
"conseiller-title" => "./templates/conseiller-title.html",
"keyboard-client" => "./templates/keyboard-client.html",
"keyboard-page" => "./templates/keyboard-page.html",
"screensaver-page" => "./templates/screensaver-page.html",
"error-popup" => "./templates/error-popup.html",
"cgu-popup" => "./templates/cgu-popup.html",
"afa-panel" => "./templates/afa-panel.html",
"message-page" => "./templates/message-page.html",
"recall-page" => "./templates/recall-page.html",
"client-page" => "./templates/client-page.html",
"waiting-page" => "./templates/waiting-page.html",
"covid-popup" => "./templates/covid-popup.html",
"rdv-calendar-page" => "./templates/rdv-calendar-page.html",
"affluence-page" => "./templates/affluence-page.html",
);
if ($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["ENABLED"]) {
$templates["event-page"] = "./templates/event-page.html";
$templates["event-panel"] = "./templates/event-panel.html";
$templates["event-nav"] = "./templates/event-nav.html";
$templates["site-map-page"] = "./templates/site-map-page.html";
$templates["site-map-panel"] = "./templates/site-map-panel.html";
$templates["site-map-nav"] = "./templates/site-map-nav.html";
}
if(is_dir($dirTpl)){
if($dh = opendir($dirTpl)){
while(($file = readdir($dh)) != false){
if($file == "." or $file == ".."){
} else {
// var_dump($SERVER."assets/templates/borne/".$file);
$templates[str_replace(".html","",$file)] = "../assets/templates/borne/".$file;
}
}
}
}
return $templates;
}
function getWaitingNb($agence, $rdv){
$nb = 0;
$q = "SELECT count(cl.id_client) as nb
FROM ca_clients cl
WHERE DATEDIFF(cl.date_arrivee,now())=:datediff
AND cl.date_priseencharge=:date_priseencharge
AND cl.id_agence=:id_agence
AND cl.rdv=:rdv";
$values = array('datediff' => 0,'date_priseencharge' => '0000-00-00 00:00:00', 'id_agence' => $agence, 'rdv' => $rdv);
$r = excuteQuery($q, $values);
if($r['status'] && $r['stmt']->rowCount())
$nb = $r['stmt']->fetchColumn();
return $nb;
}
function getAgencyChoices($type, $label = NULL){
$defaults = $GLOBALS["BORNE_CONF"]["CHOICES"];
if($GLOBALS["BO_CONF"]["AGENCE"]["MULTIPLE_TYPES"]){
if(!$label){
if($type == 0){
$defaults["SRDV"] = false;
}
}
else{
if(!strpos($label,"SRDV")){
$defaults["SRDV"] = false;
}
}
}
return $defaults;
}
function getAgencyNFC($label){
return strpos($label,"NFC") || (isset($_REQUEST["nfc"]) && $_REQUEST["nfc"] == 1) || (!isset($_REQUEST["nfc"]) && $GLOBALS["BORNE_CONF"]["NFC"]);
}
function getEvents($id){
$events = array();
$q = "SELECT * FROM ca_siege_events WHERE supp IS NULL AND is_active = 1 AND event_start <= now() AND event_end >= now() AND id_agence = ".$id;
$r =excuteQuery($q);
if($r['status']){
while($row = $r['stmt']->fetch(PDO::FETCH_OBJ)){
$events[] = $row;
}
}
return $events;
}
function getSiteMap($id){
$events = array();
$q = "SELECT id_orientation as id, label as title, salle, icon_site_map as icon FROM ca_orientations WHERE orientation = 4 AND id_agence = ".$id." AND supp = 0 ORDER BY id_orientation ASC LIMIT 6";
$r =excuteQuery($q);
if($r['status']){
while($row = $r['stmt']->fetch(PDO::FETCH_OBJ)){
$events[] = $row;
}
}
return $events;
}
function getWaitingAvg($agence){
$q = "SELECT
count(id_client) as nbVisits,
AVG(TIME_TO_SEC(case when rdv= 0 then (case when date_priseencharge and date_priseencharge >= date_arrivee then SEC_TO_TIME(TIMESTAMPDIFF(SECOND, date_arrivee, date_priseencharge)) else null end) else null end)) as srdv,
AVG(TIME_TO_SEC(case when rdv= 1 then (case when date_priseencharge and date_priseencharge >= date_arrivee then SEC_TO_TIME(TIMESTAMPDIFF(SECOND, date_arrivee, date_priseencharge)) else null end) else null end)) as rdv
FROM ca_clients
WHERE id_agence=".$agence." AND date_priseencharge AND date_priseencharge >= date_arrivee AND DATE(date_arrivee) = DATE(now())
";
$r =excuteQuery($q);
if($r['status']){
$row = $r['stmt']->fetch(PDO::FETCH_OBJ);
if($row->nbVisits){
$rdv = (float) $row->rdv;
$srdv = (float) $row->srdv;
$row->rdv = ($rdv > 3600 ? floor($row->rdv/3600) : floor($row->rdv/60));
$row->rdv_suffix = $rdv > 3600 ? 'H' : 'MIN';
$row->srdv = ($srdv > 3600 ? floor($row->srdv/3600) : floor($row->srdv/60));//toTime($row->srdv);
$row->srdv_suffix = $srdv > 3600 ? 'H' : 'MIN';
$row->rdv = $row->rdv ? $row->rdv : 1;
$row->srdv = $row->srdv ? $row->srdv : 1;
return $row;
}
}
return array('nbVisits' => 0, 'rdv' => '5', 'rdv_suffix' => 'MIN', 'srdv' => '5', 'srdv_suffix' => 'MIN');
}
function toTime($value){
if($value){
$totalSeconds = $value * 60;
$hours = (int) floor($totalSeconds / 3600);
$hours = $hours ? (str_pad($hours,2,"0",STR_PAD_LEFT).":") : "";
$minutes = (int) floor(($totalSeconds - ($hours*3600))/60);
$minutes = str_pad($minutes,2,"0",STR_PAD_LEFT);
$seconds = (int) $totalSeconds - ($hours*3600 + $minutes*60);
$seconds = str_pad($seconds,2,"0",STR_PAD_LEFT);
return $hours.$minutes.":".$seconds;
}
return "00:00";
}
function getNfcConfig($SQLSERVER_DATABASE, $SQLSERVER_DATASOURCE, $SQLSERVER_ID, $SQLSERVER_PASSWORD, $NFC_SERVER_CONNECTION_STRING, $NFC_SERVER_CARD_QUERY){
$config = null;
if(isset($SQLSERVER_DATABASE)){
$config = array(
"SQLSERVER_DATABASE" => $SQLSERVER_DATABASE,
"SQLSERVER_DATASOURCE" => $SQLSERVER_DATASOURCE,
"SQLSERVER_ID" => $SQLSERVER_ID,
"SQLSERVER_PASSWORD" => $SQLSERVER_PASSWORD,
"NFC_SERVER_CONNECTION_STRING" => $NFC_SERVER_CONNECTION_STRING,
"NFC_SERVER_CARD_QUERY" => $NFC_SERVER_CARD_QUERY,
);
}
return $config;
}
function getBorneDelays(){
$delays = array('backhomedelay' => null,'screensaverdelay' => null,'nfcwaitdelay' => null);
$q = "SELECT `option`, `value` FROM `ca_options` WHERE `option` IN ('backhome_delay','screensaver_delay','nfcwait_delay', 'rdv_delay', 'rdv_check_delay', 'rdv_alert_enabled')";
$result = excuteQuery($q);
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)){
if(in_array($row->option,array('backhome_delay','screensaver_delay','nfcwait_delay', 'rdv_delay', 'rdv_check_delay', 'rdv_alert_enabled'))){
$delays[$row->option] = $row->value;
}
}
$delays["backhomedelay"] = isset($delays['backhome_delay']) ? ($delays['backhome_delay']*1000) : (isset($GLOBALS["BORNE_CONF"]["BACKHOMEDELAY"])?$GLOBALS["BORNE_CONF"]["BACKHOMEDELAY"]:"10000"); // Default to 10 secondes
$delays["screensaverdelay"] = isset($delays['screensaver_delay']) ? ($delays['screensaver_delay']*1000) : (isset($GLOBALS["BORNE_CONF"]["SCREENSAVERDELAY"])?$GLOBALS["BORNE_CONF"]["SCREENSAVERDELAY"]:"180000"); // Default to 3 minutes
$delays["nfcwaitdelay"] = isset($delays['nfcwait_delay']) ? ($delays['nfcwait_delay']*1000) : (isset($GLOBALS["BORNE_CONF"]["NFCWAITDELAY"])?$GLOBALS["BORNE_CONF"]["NFCWAITDELAY"]:"10000"); // Default to 10 seconds
$delays["rdvdelay"] = isset($delays['rdv_delay']) ? $delays['rdv_delay'] : 60; // Default to 60 minutes
$delays["rdvcheckdelay"] = isset($delays['rdv_check_delay']) ? $delays['rdv_check_delay'] : 30; // Default to 30 minutes
$delays["rdvalertenabled"] = isset($delays['rdv_alert_enabled']) ? $delays['rdv_alert_enabled'] : 1; // Default to 30 minutes
return $delays;
}
// AFFLUENCE
function getAffluenceCalendar($aid){
$calendar = array();
$totalCreneaux = $totalVisites = 0;
$weekDays = array("Lundi","Mardi","Mercredi", "Jeudi","Vendredi","Samedi");
$creneaux = $GLOBALS["BORNE_CONF"]["AFFLUENCE"]["creneaux"];
$firstCreneau = $lastCreneau = false;
$q = "SELECT HOUR(date_arrivee) heure, COUNT(id_client) nb FROM ca_statistiques WHERE rdv=0 AND date_arrivee BETWEEN NOW() - INTERVAL 60 DAY AND NOW() AND id_agence = :id_agence
AND (HOUR(date_arrivee) = 8 OR HOUR(date_arrivee) = 18) GROUP BY HOUR(date_arrivee) ORDER BY heure";
$values = array("id_agence" => $aid);
$r = excuteQuery($q, $values);
if($r && $r['stmt']->rowCount()){
while($row = $r['stmt']->fetchObject()) {
if($row->heure == 8 && $row->nb){
$firstCreneau = true;
}
else if($row->heure == 18 && $row->nb){
$lastCreneau = true;
}
}
}
if(!$firstCreneau) {
array_shift($creneaux);
}
if(!$lastCreneau) {
array_pop($creneaux);
}
$endTime = time();
$startTime = strtotime('-61 day', $endTime);
foreach($creneaux as $creneau){
foreach($weekDays as $name){
$day = english_dow($name);
$nbCreneaux = periodcount("next ".$day,$startTime,$endTime, 0);
$calendar[$creneau][$name] = array("nbVisites"=>0, "nbCreneaux"=>$nbCreneaux, "moyenne"=> 0);
// $totalCreneaux += $nbCreneaux;
}
}
$q = "SELECT WEEKDAY(date_arrivee) semaine, HOUR(date_arrivee) heure, COUNT(id_client) nb FROM ca_statistiques WHERE rdv=0 AND (date_arrivee BETWEEN '".date('Y-m-d', $startTime)." 00:00:00' AND '".date('Y-m-d')." 23:59:59') AND id_agence = :id_agence GROUP BY semaine, heure ORDER BY semaine";
$values = array("id_agence" => $aid);
$r = excuteQuery($q, $values);
if($r && $r['stmt']->rowCount()){
while($row = $r['stmt']->fetchObject()) {
$heure = (int)$row->heure;
$creneau = $heure.'h - '.($heure+1).'h';
$semaine = $weekDays[$row->semaine];
$nbCreneaux = $calendar[$creneau][$semaine]["nbCreneaux"];
$moyenne = $nbCreneaux ? $row->nb / $nbCreneaux : 0;
if($row->nb > 0){
$totalCreneaux +=$nbCreneaux;
}
else{
$nbCreneaux = 0;
}
$calendar[$creneau][$semaine] = array("nbVisites"=>$row->nb, "nbCreneaux"=>$nbCreneaux, "moyenne" => $moyenne);
$totalVisites += $row->nb;
}
}
// var_dump($totalVisites); // 3417
// var_dump($totalCreneaux); //409
$moyenneGlobale = $totalVisites ? $totalVisites/$totalCreneaux : 0;
// var_dump($calendar);
return array("calendar" => $calendar, "moyenneGlobale" => $moyenneGlobale, "weekDays"=>$weekDays, "creneaux" => $creneaux);
}
function english_dow($day){
$days = array("", "Dimanche" => "Sunday", "Lundi" => "Monday", "Mardi" => "Tuesday", "Mercredi" => "Wednesday", "Jeudi" => "Thursday", "Vendredi" => "Friday", "Samedi" => "Saturday");
return $days[$day];
}
function periodcount($relativedate, $startdate, $enddate, $counter)
{
if(strtotime($relativedate, $startdate) > $enddate)
{
return $counter;
}
else
{
return periodcount($relativedate, strtotime($relativedate, $startdate), $enddate, ++$counter);
}
}
switch($TYPE){
case 'agence':
$DATA = array('config'=>array(),'params'=>array(),'fonctions'=>array(),'entites'=>array(),'motifs'=>array(),'orientations'=>array());
$TJOIN = $LJOIN = $TSELECT = $ASELECT = $LSELECT = $PSELECT = $MSELECT = "";
if(isset($GLOBALS["BO_CONF"]["AGENCE"]["TYPES_TABLE"]) && $GLOBALS["BO_CONF"]["AGENCE"]["TYPES_TABLE"]) {
$TJOIN = " LEFT JOIN ca_types t ON t.type = type_agence";
$TSELECT = ", t.nom as type_label";
}
if(isset($GLOBALS["BO_CONF"]["AFA"]) && $GLOBALS["BO_CONF"]["AFA"]){
$ASELECT = ", is_afa";
}
if(isset($GLOBALS["BO_CONF"]["TOGGLE_SHOW_PHOTOS"]) && $GLOBALS["BO_CONF"]["TOGGLE_SHOW_PHOTOS"]){
$PSELECT = ", show_photos";
}
if(isset($GLOBALS["BO_CONF"]["AGENCE"]["MERGING_NFC_BY_AGENCY"]) && $GLOBALS["BO_CONF"]["AGENCE"]["MERGING_NFC_BY_AGENCY"]){
$MSELECT = ', merged_nfc';
}
$agcLang = "";
if(in_array('AR',$GLOBALS["BORNE_CONF"]["LANGUAGES"])){
$LSELECT = ", l.nom_agence as nom_ar";
$LJOIN .= " LEFT JOIN ca_agences_lang l ON l.id_agence = a.id_agence ";
}
$NSELECT = "";
if(isset($GLOBALS["BO_CONF"]["NFCBYAGENCY"]) && $GLOBALS["BO_CONF"]["NFCBYAGENCY"]){
$NSELECT = ", nfc";
}
$query = "SELECT a.id_agence as 'id', eds, type_agence as 'type'".$TSELECT.$ASELECT.$PSELECT.$LSELECT.$MSELECT.$NSELECT.", code_agence as 'code', a.nom_agence as 'nom', logo, header, meeting, salle, borne_agents_legend as 'legend' FROM ca_agences a ".$TJOIN.$LJOIN." WHERE code_agence = :code_agence";
$values = array('code_agence' => $CODE);
$result = excuteQuery($query, $values);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
$DATA["params"] = new stdClass();
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$IDA = $row->id;
$DATA["params"] = $row;
if(isset($GLOBALS["BO_CONF"]["NFCBYAGENCY"]) && $GLOBALS["BO_CONF"]["NFCBYAGENCY"]){
$agency_nfc = $row->nfc ? true : false;
}
}
$DATA["params"]->primaryColor = isset($GLOBALS["BORNE_CONF"]["COLORS"]["PRIMARYCOLOR"])?$GLOBALS["BORNE_CONF"]["COLORS"]["PRIMARYCOLOR"]:"#81C600";
$DATA["params"]->errorPopinColor = isset($GLOBALS["BORNE_CONF"]["COLORS"]["ERRORPOPINCOLOR"])?$GLOBALS["BORNE_CONF"]["COLORS"]["ERRORPOPINCOLOR"]:"#E2447F";
$DATA["params"]->afa_motifs = isset($GLOBALS["BO_CONF"]["AFA_MOTIFS"]) && $GLOBALS["BO_CONF"]["AFA_MOTIFS"] && $ASELECT != "";
$DATA["params"]->subfolder = isset($GLOBALS["SUBFOLDER"]) && $GLOBALS["SUBFOLDER"] ? $SERVER : '/';
$DATA["realtime"]["status"] = new StdClass();
$DATA["realtime"]["status"]->waiting_nb[0] = $GLOBALS["BORNE_CONF"]["WAITINGNB"][0]?getWaitingNb($IDA,0):0;
$DATA["realtime"]["status"]->waiting_nb[1] = $GLOBALS["BORNE_CONF"]["WAITINGNB"][1]?getWaitingNb($IDA,1):0;
$langJoin = $visibilityCol = "";
if(isset($BORNE_CONF["LANGUAGES"]) && count($BORNE_CONF["LANGUAGES"])>1){
$langCol = ", fl.id_lang as 'lang'";
$libelleCol = ", fl.libelle as 'nom'";
$langJoin = " LEFT JOIN ca_fonctions_lang fl ON fl.id_fonction = f.id_fonction";
}
else{
$langCol = ", 'fr' as 'lang'";
$libelleCol = ", f.libelle as 'nom'";
}
if(isset($GLOBALS["BO_CONF"]["CONSEILLER"]["fonction"]["manage_visibility"]) && $GLOBALS["BO_CONF"]["CONSEILLER"]["fonction"]["manage_visibility"]){
$visibilityCol = ", f.is_visible as visible";
}
$query = "SELECT f.id_fonction as 'id'".$langCol.$libelleCol.$visibilityCol." FROM ca_fonctions f".$langJoin." WHERE f.supp=0";
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$DATA["fonctions"][] = $row;
}
$labelTri = (isset($GLOBALS["BORNE_CONF"]["ENTITY_CHOICE_LINK"]) && $GLOBALS["BORNE_CONF"]["ENTITY_CHOICE_LINK"] == true)?' libelle ASC, ':'';
$query = "SELECT id_entite as 'id', libelle as 'nom', enabled as 'is_active', picto as 'picto', borne_color, pile_color FROM ca_entites WHERE id_agence = ".$IDA." AND supp=0 AND enabled=1 ORDER BY id ASC";
$values = array('libelle' => $DATA["params"]->nom);
$result = excuteQuery($query, $values);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$row->title_without_tags = strip_tags($row->nom);
$DATA["entites"][] = $row;
}
// PCZ : 2017/10/41 : JE TRIE PAR ORDRE D' "idtri" CROISSANT POUR CHOISIR L'ORDRE D'AFFICHAGE DES MOTIFS SUR LA BORNE EN FONCTION DES CHOIX DE L'AGENCE
// MAI : Tri par me.id désactivé, le tri est désormais selon le id_motif dans la table ca_motifs
if($GLOBALS["BO_CONF"]["MOTIFS"]){
$maxmotifs = (isset($BORNE_CONF["LANGUAGES"]) && count($BORNE_CONF["LANGUAGES"])>1) ? ($GLOBALS["BO_CONF"]["MAXMOTIFS"] * count($BORNE_CONF["LANGUAGES"])) : $GLOBALS["BO_CONF"]["MAXMOTIFS"];
$limit = (isset($BORNE_CONF["PAGINATION"]["MOTIF"]) && $BORNE_CONF["PAGINATION"]["MOTIF"]["ENABLED"]) ? "" : (" LIMIT ".$maxmotifs);
$langJoin = "";
if(isset($BORNE_CONF["LANGUAGES"]) && count($BORNE_CONF["LANGUAGES"])>1){
$langCol = ", ml.id_lang as 'lang'";
$titleCol = ", ml.titre as 'firstline'";
$langJoin = "LEFT JOIN ca_motifs_lang ml ON ml.id_motif = ma.id_motif";
}
else{
$langCol = ", 'fr' as 'lang'";
$titleCol = ", m.titre as 'firstline'";
}
$query = "
SELECT ma.sort as 'idtri', ma.id_motif as 'id'".$titleCol.", ma.id_orientation as 'orientation', ma.id_agence as 'agence', '' as 'secondline', m.picto as 'picto'".$langCol."
FROM ca_motif_agence ma
LEFT JOIN ca_motifs m ON m.id_motif = ma.id_motif
".$langJoin."
LEFT JOIN ca_agences a ON a.id_agence = ma.id_agence
WHERE ma.id_agence = ".$IDA." AND (ma.supp IS NULL OR ma.supp = 0) AND m.enabled=1 AND a.supp = 0 ORDER BY idtri ASC ".$limit;
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$row->title_without_tags = strip_tags($row->firstline);
$DATA["motifs"][] = $row;
}
}
$actions = array();
if(isset($GLOBALS["BO_CONF"]["AFA"]) && $GLOBALS["BO_CONF"]["AFA"]){
$q = "SELECT titre, code_action as code, id_action as id, is_motif, picto FROM ca_actions";
$r = excuteQuery($q);
while($row = $r['stmt']->fetch(PDO::FETCH_OBJ)) {
$DATA["actions"][] = $row;
}
$q = "SELECT s.id_scenario as id,s.label, sa.titre, sa.informations, sa.id_motif, sa.switch_time, sa.is_active, GROUP_CONCAT(sa.titre) AS titre_actions, GROUP_CONCAT(cas.id_action) AS id_actions, GROUP_CONCAT(cas.id_orientation) AS orientations
FROM ca_scenarios s
LEFT JOIN ca_agence_scenarios sa ON s.id_scenario = sa.id_scenario AND sa.id_agence = ".$IDA."
LEFT JOIN ca_agence_scenarios_actions cas ON s.id_scenario = cas.id_scenario AND cas.id_agence = ".$IDA."
LEFT JOIN ca_actions a ON a.id_action = cas.id_action
WHERE sa.is_active = 1
GROUP BY s.id_scenario";
$r = excuteQuery($q);
while($row = $r['stmt']->fetch(PDO::FETCH_OBJ)) {
$id_actions = explode(',',$row->id_actions);
$id_orientations = explode(',',$row->orientations);
$row->actions = array();
foreach($DATA["actions"] as $key => $action){
$actionKey = array_search($action->id, $id_actions);
if($actionKey !== false){
$action->id_orientation = $id_orientations[$actionKey];
$row->actions[] = $action;
}
}
unset($row->titre_actions);
unset($row->id_actions);
unset($row->orientations);
$DATA["scenarios"][] = $row;
}
}
if(isset($BORNE_CONF["LANGUAGES"]) && count($BORNE_CONF["LANGUAGES"])>1){
$query = "SELECT GROUP_CONCAT(DISTINCT(id_orientation)) orientations FROM ca_conseiller_agence WHERE id_agence = ".$IDA." AND id_entite IN (SELECT id_entite FROM ca_entites WHERE supp =0) ";
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
$orientations = $result ? $result['stmt']->fetch(PDO::FETCH_OBJ)->orientations : "";
if($GLOBALS["BORNE_CONF"]["RDV_CALENDAR"]){
$query = "SELECT GROUP_CONCAT(DISTINCT(id_orientation)) orientations FROM ca_conseiller_agence ca
LEFT JOIN ca_conseillers c ON ca.id_conseiller = c.id_conseiller
INNER JOIN ca_rdv_calendar r ON r.matricule = c.id_agent
INNER JOIN ca_agences a ON a.eds = r.eds
WHERE r.date_debut >= CURRENT_DATE() AND r.date_fin >= CURRENT_DATE() AND a.id_agence =".$IDA;
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
$oq = $result['stmt']->rowCount() > 0 ? $result['stmt']->fetch(PDO::FETCH_OBJ)->orientations : "";
$orientations .= $oq ? (','.$oq) : "";
}
$orientationsQ = $orientations ? (" OR o.id_orientation IN (".$orientations.")") : "";
$query = "SELECT o.id_orientation as 'id', id_entite as 'entite', ol.texte as 'message', id_lang as 'lang', orientation as 'type', salle FROM ca_orientations o LEFT JOIN ca_orientations_lang ol ON ol.id_orientation = o.id_orientation WHERE supp=0 AND ((id_agence = ".$IDA." AND id_entite IN (SELECT id_entite FROM ca_entites WHERE supp =0))".$orientationsQ.") ORDER BY o.id_orientation ASC";
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
}
else{
$query = "SELECT GROUP_CONCAT(DISTINCT(id_orientation)) orientations FROM ca_conseiller_agence WHERE id_agence = ".$IDA." AND id_entite IN (SELECT id_entite FROM ca_entites WHERE supp =0) ";
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
$orientations = $result ? $result['stmt']->fetch(PDO::FETCH_OBJ)->orientations : "";
if($GLOBALS["BORNE_CONF"]["RDV_CALENDAR"]){
$query = "SELECT GROUP_CONCAT(DISTINCT(id_orientation)) orientations FROM ca_conseiller_agence ca
LEFT JOIN ca_conseillers c ON ca.id_conseiller = c.id_conseiller
INNER JOIN ca_rdv_calendar r ON r.matricule = c.id_agent
INNER JOIN ca_agences a ON a.eds = r.eds
WHERE r.date_debut >= CURRENT_DATE() AND r.date_fin >= CURRENT_DATE() AND a.id_agence =".$IDA;
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
$orientations .= $result ? (','.$result['stmt']->fetch(PDO::FETCH_OBJ)->orientations) : "";
}
$orientationsQ = $orientations ? (" OR id_orientation IN (".$orientations.")") : "";
$query = "SELECT id_orientation as 'id', id_entite as 'entite', texte as 'message', 'fr' as 'lang', orientation as 'type', salle FROM ca_orientations WHERE supp=0 AND (id_agence = ".$IDA."".$orientationsQ.") ORDER BY id_orientation ASC";
$result = excuteQuery($query);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
}
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$DATA["orientations"][] = $row;
}
$DATA["config"] = new StdClass();
$DATA["config"]->borne = $GLOBALS["BORNE_CONF"];
$DATA["config"]->backoffice = $GLOBALS["BO_CONF"];
$DATA["config"]->pile = $GLOBALS["PILE_CONF"];
$DATA["config"]->screensaver = isset($GLOBALS["BO_CONF"]["SCREENSAVER"])?$GLOBALS["BO_CONF"]["SCREENSAVER"]:false;
$DATA["config"]->accompanying = isset($GLOBALS["BO_CONF"]["ACCOMPANYING"])?$GLOBALS["BO_CONF"]["ACCOMPANYING"]:false;
$DATA["config"]->multiplemotifs = isset($GLOBALS["BORNE_CONF"]["MULTIPLEMOTIFS"])?$GLOBALS["BORNE_CONF"]["MULTIPLEMOTIFS"]:false;
$DATA["config"]->mergeentities = isset($GLOBALS["BORNE_CONF"]["MERGEENTITIES"])?$GLOBALS["BORNE_CONF"]["MERGEENTITIES"]:false;
$DATA["config"]->hasentities = isset($GLOBALS["BO_CONF"]["ENTITIES"])?$GLOBALS["BO_CONF"]["ENTITIES"]:false;
$DATA["config"]->colorbyentity = isset($GLOBALS["BO_CONF"]["COLORBYENTITY"])?$GLOBALS["BO_CONF"]["COLORBYENTITY"]:false;
$DATA["config"]->motifsorientations = isset($GLOBALS["BO_CONF"]["MOTIF"]["ORIENTATIONS"])?$GLOBALS["BO_CONF"]["MOTIF"]["ORIENTATIONS"]:false;
$DATA["config"]->titles = isset($GLOBALS["BORNE_CONF"]["TITLES"])?$GLOBALS["BORNE_CONF"]["TITLES"]:false;
$DATA["config"]->titles["SCREENSAVER_TEXT"] = ($DATA["config"]->titles["SCREENSAVER_TEXT"])?$DATA["config"]->titles["SCREENSAVER_TEXT"]:"Touchez l'écran pour vous annoncer";
$DATA["config"]->titles["MOTIF_VALIDATE"] = isset($DATA["config"]->titles["MOTIF_VALIDATE"])?$DATA["config"]->titles["MOTIF_VALIDATE"]:"Valider mon / mes choix";
$DATA["config"]->languages = getBorneTranslations($DATA["params"]->type);
$DATA["config"]->nfctimeup = isset($GLOBALS["BORNE_CONF"]["NFCTIMEUP"])?$GLOBALS["BORNE_CONF"]["NFCTIMEUP"]:"60"; // Default to 1 minute
if(isset($GLOBALS["BORNE_CONF"]["NFC"]) && $GLOBALS["BORNE_CONF"]["NFC"])
$DATA["config"]->nfconfig = getNfcConfig($SQLSERVER_DATABASE, $SQLSERVER_DATASOURCE, $SQLSERVER_ID, $SQLSERVER_PASSWORD, $NFC_SERVER_CONNECTION_STRING, $NFC_SERVER_CARD_QUERY);
$DATA["config"]->clientcivilite = isset($GLOBALS["BO_CONF"]["CLIENT"]["civilite"])?$GLOBALS["BO_CONF"]["CLIENT"]["civilite"]:true;
$DATA["config"]->clientcompany = isset($GLOBALS["BO_CONF"]["CLIENT"]["company"])?$GLOBALS["BO_CONF"]["CLIENT"]["company"]:false;
$DATA["config"]->clientaccomp = isset($GLOBALS["BO_CONF"]["ACCOMPANYING"])?$GLOBALS["BO_CONF"]["ACCOMPANYING"]:false;
$DATA["config"]->fields = isset($GLOBALS["BORNE_CONF"]["FIELDS"])?$GLOBALS["BORNE_CONF"]["FIELDS"]:array("COMPANY" => array("REQUIRED" => true), "FIRSTNAME" => array("REQUIRED" => false), "LASTNAME" => array("REQUIRED" => true), "GENDER" => array("REQUIRED" => true));
$DATA["config"]->waitimg = isset($GLOBALS["BO_CONF"]["WAITIMG"])?$GLOBALS["BO_CONF"]["WAITIMG"]:true;
$typeLabel = "";
if($TSELECT !="")
$typeLabel = $DATA["params"]->type_label ;
$DATA["config"]->choices = getAgencyChoices($DATA["params"]->type, $typeLabel);
$DATA["config"]->screens = isset($GLOBALS["BORNE_CONF"]["SCREENS"])?$GLOBALS["BORNE_CONF"]["SCREENS"]:array(0 => array("choice-page","srdv-page","keyboard-page","wait-page"), 1 => array("choice-page","rdv-page","keyboard-page","wait-page"));
$DATA["config"]->fields["FIRSTNAME"]["ENABLED"] = isset($GLOBALS["BORNE_CONF"]["FIELDS"]["FIRSTNAME"]["ENABLED"])?$GLOBALS["BORNE_CONF"]["FIELDS"]["FIRSTNAME"]["ENABLED"]:true;
$DATA["config"]->fields["LASTNAME"]["ENABLED"] = isset($GLOBALS["BORNE_CONF"]["FIELDS"]["LASTNAME"]["ENABLED"])?$GLOBALS["BORNE_CONF"]["FIELDS"]["LASTNAME"]["ENABLED"]:true;
$DATA["config"]->fields["GENDER"]["ENABLED"] = isset($GLOBALS["BORNE_CONF"]["FIELDS"]["GENDER"]["ENABLED"])?$GLOBALS["BORNE_CONF"]["FIELDS"]["GENDER"]["ENABLED"]:true;
$DATA["config"]->waitingnb[0] = isset($GLOBALS["BORNE_CONF"]["WAITINGNB"][0])?$GLOBALS["BORNE_CONF"]["WAITINGNB"][0]:false;
$DATA["config"]->waitingnb[1] = isset($GLOBALS["BORNE_CONF"]["WAITINGNB"][1])?$GLOBALS["BORNE_CONF"]["WAITINGNB"][1]:false;
$DATA["config"]->borne["GLOBAL_NAV_ICONS_PATH"] = isset($DATA["config"]->borne["GLOBAL_NAV_ICONS_PATH"])?$DATA["config"]->borne["GLOBAL_NAV_ICONS_PATH"]:"./assets/img";
$DATA["config"]->borne["templates"] = getBorneTemplates($SERVER);
$delays = getBorneDelays();
$DATA["config"]->backhomedelay = $delays["backhomedelay"];
$DATA["config"]->screensaverdelay = $delays["screensaverdelay"];
$DATA["config"]->nfcwaitdelay = $delays["nfcwaitdelay"];
$DATA["config"]->rdvdelay = $delays["rdvdelay"];
$DATA["config"]->rdvcheckdelay = $delays["rdvcheckdelay"];
$DATA["config"]->rdvalertenabled = $delays["rdvalertenabled"];
if(!empty($GLOBALS["BORNE_CONF"]["AFFLUENCE"])){
$DATA["AFFLUENCE"] = getAffluenceCalendar($IDA);
}
if(isset($GLOBALS["BORNE_CONF"]["SHOW_DMA"]) && $GLOBALS["BORNE_CONF"]["SHOW_DMA"]){
$DATA["config"]->borne["DMA"] = getWaitingAvg($IDA);
}
if(isset($GLOBALS["BO_CONF"]["AGENCE"]["MULTIPLE_TYPES"]) && $GLOBALS["BO_CONF"]["AGENCE"]["MULTIPLE_TYPES"] && isset($GLOBALS["BORNE_CONF"]["NFCBYAGENCY"]) && $GLOBALS["BORNE_CONF"]["NFCBYAGENCY"]){
$DATA["config"]->borne["NFC"] = getAgencyNFC($DATA["params"]->type_label);
}
if(isset($_REQUEST["nfc"])){
$DATA["config"]->borne["NFC"] = !!$_REQUEST["nfc"];
}
if(isset($GLOBALS["BO_CONF"]["AGENCE"]["MERGING_NFC_BY_AGENCY"]) && $GLOBALS["BO_CONF"]["AGENCE"]["MERGING_NFC_BY_AGENCY"]){
if($DATA["params"]->merged_nfc == 1){
$DATA["config"]->borne["MERGEKEYBOARDNFC"] = $DATA["params"]->merged_nfc;
$DATA["config"]->borne["templates"]["nfc-page"] = "../../assets/templates/borne/nfc-merged-page.html";
}
}
if(isset($GLOBALS["BO_CONF"]["NFCBYAGENCY"]) && $GLOBALS["BO_CONF"]["NFCBYAGENCY"]){
$DATA["config"]->borne["NFC"] = $agency_nfc;
}
// If NFC is active but no configs
$DATA["config"]->borne["NFC_CONFIG_ERROR"] = false;
if((empty($NFC_SERVER_CONNECTION_STRING) || empty($NFC_SERVER_CARD_QUERY)) && $GLOBALS["BORNE_CONF"]["NFC"]) {
// $DATA["config"]->borne["NFC"] = false;
$DATA["config"]->borne["NFC_CONFIG_ERROR"] = true;
}
if($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["ENABLED"] && in_array('events', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])){
$DATA["events"] = getEvents($IDA);
}
if($GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["ENABLED"] && in_array('site-map', $GLOBALS["BO_CONF"]["AGENCE"]["SIEGE"]["CHOICES"])){
$DATA["siteMap"] = getSiteMap($IDA);
}
$q_ship = "SELECT id_livraison, id_agence, texte, salle FROM ca_livraisons WHERE id_agence = ".$IDA." LIMIT 1";
$stmt_ship = excuteQuery($q_ship);
$res_ship = $stmt_ship['stmt'];
$o_ship = $res_ship->fetchObject();
$DATA["ship"] = $o_ship;
$BORNEIP = $_SERVER['REMOTE_ADDR'];
$BORNEJOUR = date('Y-m-d');
$qx = 'INSERT INTO ca_bornes (id_agence, nom_agence, ip, jour) '
. 'VALUES (:id_agence, :nom_agence, :ip, :jour)';
$values = array('id_agence' => $IDA,'nom_agence' => $DATA["params"]->nom,'ip' => $BORNEIP,'jour' => $BORNEJOUR);
$rx = excuteQuery($qx, $values);
break;
case 'conseillers':
$leftjoin = "";//$GLOBALS["BO_CONF"]["ENTITELINKBYID"] ? "" : "LEFT JOIN ca_entites e ON c.groupe = e.entite AND c.id_agence = e.id_agence";
$entiteCol = "IF(ca.id_entite AND c.id_agence != ca.id_agence, ca.id_entite, groupe) as 'entite'";//$GLOBALS["BO_CONF"]["ENTITELINKBYID"] ? "groupe as 'entite'" : "e.id_entite as 'entite'";
// $query = "SELECT id_conseiller as 'id', id_agent as 'mat', civ, nom, prenom, '0' as 'myst', photo, orientation, pro as 'mobile', inactif, IF(fonction<3, fonction, 3) as fonction, label_fonction, ".$entiteCol."
// FROM ca_conseillers c
// ".$leftjoin."
// WHERE c.id_agence = (SELECT id_agence FROM ca_agences WHERE code_agence = '".$CODE."') AND c.supp=0 AND c.inactif=0 GROUP BY c.id_conseiller ORDER BY fonction ASC, nom ASC";
$values = array('id_agence'=>$IDA);
$filterMobility = "AND c.id_agence=:id_agence";
if($GLOBALS["BO_CONF"]["MOBILITY"]){
$dla = date('Y-m-d');
$filterMobility = "AND (
((c.id_agence=:id_agence OR ca.id_agence=:id_agence) AND status = 0) OR
(
(temporaire = 1 OR status = -1) ";
// (last_agency = :id_agence OR c.id_agence=:id_agence) AND
/*$filterMobility .= "
TIMESTAMPDIFF(SECOND,ca.last_active,now())<=:last_active
)
)" ; */
$filterMobility .= " AND ca.last_active LIKE '".$dla."%')) ";
$values['last_active'] = $GLOBALS["PILE_CONF"]["KEEPALIVE"];
}
// $orientationMobility = $GLOBALS["BO_CONF"]["MOBILITY"] ? ", IF(c.id_agence='".$IDA."', orientation, (SELECT o.id_orientation FROM ca_orientations o WHERE o.id_entite = ca.id_entite AND orientation = '2' ORDER BY o.id_orientation ASC LIMIT 1)) orientation" : ", orientation";
$orientationMobility = ", IF(ca.id_orientation, ca.id_orientation, (SELECT o.id_orientation FROM ca_orientations o WHERE o.id_entite = ca.id_entite AND orientation = '2' ORDER BY o.id_orientation ASC LIMIT 1)) orientation";
$function_tri = $GLOBALS["BORNE_CONF"]["FUNCTION_TRI"] ? $GLOBALS["BORNE_CONF"]["FUNCTION_TRI"] : '';
$functionTri = (isset($GLOBALS["BORNE_CONF"]["NO_STICKED_DIRECTOR"]) && $GLOBALS["BORNE_CONF"]["NO_STICKED_DIRECTOR"]) ? $function_tri : "fonction_tri ASC, ";
$cnsLang = "";
$entite_tri = isset($GLOBALS["BORNE_CONF"]["ORDER_BY_ENTITE"]) && !empty($GLOBALS["BORNE_CONF"]["ORDER_BY_ENTITE"]) ? $GLOBALS["BORNE_CONF"]["ORDER_BY_ENTITE"] :"";
if(in_array('AR',$GLOBALS["BORNE_CONF"]["LANGUAGES"])){
$cnsLang = ", l.nom as nom_ar, l.prenom as prenom_ar";
$leftjoin .= "LEFT JOIN ca_conseillers_lang l ON l.id_conseiller = c.id_conseiller ";
}
$sort_function = (isset($CAISSENAME) && $CAISSENAME == "cabp") ? ", IF(fonction='1', 1, 0) directeur, IF(ca.id_fonction='4', 2, IF(ca.id_fonction='1', 1, 0)) AS sort_function" : "";
$functionTri = (isset($CAISSENAME) && $CAISSENAME == "cabp") ? "sort_function ASC, " : $functionTri;
$query = "SELECT c.id_conseiller as 'id', id_agent as 'mat', civ, c.nom, c.prenom, '0' as 'myst', photo".$orientationMobility.$cnsLang.", pro as 'mobile', inactif, COALESCE(ca.id_fonction, fonction) as fonction, IF(COALESCE(ca.id_fonction, fonction)<3, COALESCE(ca.id_fonction, fonction), 3) as fonction_tri, label_fonction , ".$entiteCol.$sort_function."
FROM ca_conseillers c
LEFT OUTER JOIN ca_conseiller_agence ca ON ca.id_agence = :id_agence AND ca.id_conseiller = c.id_conseiller
".$leftjoin."
WHERE c.supp=0 AND c.inactif = 0
".$filterMobility."
GROUP BY c.id_conseiller
ORDER BY ".$entite_tri.$functionTri."c.nom ASC";
$result = excuteQuery($query,$values);
if (!$result['status'])
wts_die (var_dump($result['stmt']->errorInfo()));
while($row = $result['stmt']->fetch(PDO::FETCH_OBJ)) {
$DATA[] = $row;
}
break;
}
//var_dump($DATA);
echo json_encode($DATA);
?>