<?php /* MC-20170605 By M.A */ /* UPDATE MC-20170618 By M.A */ ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); session_start(); require_once('../../inc/confNetisse.php'); if(isset($_REQUEST['id_conseiller'])){ $id_conseiller = intval($_REQUEST['id_conseiller']); $id_agence = isset($_REQUEST['id_agence']) ? intval($_REQUEST['id_agence']) : ''; $id_entite = isset($_REQUEST['id_entite']) ? intval($_REQUEST['id_entite']) : ''; $default_status = $GLOBALS["PILE_CONF"]["DEFAULT_AGENT_STATUS"]?$GLOBALS["PILE_CONF"]["DEFAULT_AGENT_STATUS"]:0; switch($_REQUEST['type']){ case 'notif': $q = "UPDATE ca_conseillers SET receive_notifications=:receive_notifications WHERE supp=:supp AND id_conseiller=:id_conseiller"; $values = array(':receive_notifications' => $_REQUEST['receive_notifications'], ':supp' => 0, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); $q = "UPDATE ca_conseiller_agence SET receive_notifications=:receive_notifications WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $values = array(':receive_notifications' => $_REQUEST['receive_notifications'], ':id_agence' =>$id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); break; case 'civ': // Set civilité & agence d'affectation $agency_affectation_query = (isset($_REQUEST['is_affectable']) && $_REQUEST["is_affectable"] == 'false')?"":", id_agence=:id_agence"; $q = "UPDATE ca_conseillers SET civ=:civ".$agency_affectation_query.", last_agency=:id_agence, photo=:photo WHERE supp=:supp AND id_conseiller=:id_conseiller"; $values = array(':id_agence' => $id_agence, ':civ' => $_REQUEST['civ'], ':id_agence' => $id_agence, ':photo' => $_REQUEST['photo'], ':supp' => 0, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); $profil = getProfil($id_conseiller); $q = "INSERT INTO ca_conseiller_agence (id_agence,id_conseiller, id_entite, id_orientation,last_active,date_creation, id_fonction, id_profile) VALUES (:id_agence,:id_conseiller,(SELECT groupe FROM ca_conseillers WHERE id_conseiller=:id_conseiller2),(SELECT id_orientation FROM ca_orientations WHERE id_agence =:id_agence2 AND orientation = 2 ORDER BY id_entite ASC,date_creation ASC LIMIT 1),now(),now(),(SELECT fonction FROM ca_conseillers WHERE id_conseiller=:id_conseiller3),:id_profile)"; $values = array(':id_agence' => $id_agence, ':id_conseiller'=> $id_conseiller, ':id_conseiller2'=>$id_conseiller, ':id_conseiller3'=>$id_conseiller, ':id_profile'=>$profil, 'id_agence2' => $id_agence); $r = excuteQuery($q, $values); break; case 'borneStatus': $values = array(); if($id_entite){ $entite = ", id_entite=:id_entite"; $values[':id_entite'] = $id_entite; }else{ $entite =""; } $temporary = isset($_REQUEST['temporary'])?$_REQUEST['temporary']:false; $toRattachementQ = ($temporary && $_REQUEST['status'] != -1) ? ", temporaire = 0" : ""; $setRattachementQ = ($temporary && $_REQUEST['status'] != -1) ? "0" : "1"; if(!checkAgcCnsLink($id_agence,$id_conseiller)){ $id_entite = $id_entite ? $id_entite : "(SELECT c.groupe FROM ca_conseillers c WHERE c.id_conseiller = :id_conseiller2)"; $profil = getProfil($id_conseiller); $q = "INSERT INTO ca_conseiller_agence (id_agence,id_conseiller,id_entite, id_orientation, status, receive_notifications, temporaire, date_creation, id_fonction, id_profile) VALUES (:id_agence,:id_conseiller,:id_entite2,(SELECT id_orientation FROM ca_orientations WHERE id_agence =:id_agence2 AND id_entite=:id_entite3 AND orientation = :orientation ORDER BY date_creation ASC LIMIT 1),:status',(SELECT c.receive_notifications FROM ca_conseillers c WHERE c.id_conseiller = :id_conseiller3 AND c.id_agence = :id_agence2),".$setRattachementQ.", now(),(SELECT fonction FROM ca_conseillers WHERE id_conseiller=:id_conseiller3),:id_profile)"; $values[':id_agence'] = $id_agence;$values[':id_conseiller'] = $id_conseiller; $values[':id_conseiller2'] = $id_conseiller; $values[':id_entite2'] = $id_entite;$values[':id_agence2'] = $id_agence;$values[':id_entite3'] = $id_entite;$values[':orientation'] = 2;$values[':id_conseiller3'] = $id_conseiller; $values[':id_agence2'] = $id_agence; $values[':status'] = $_REQUEST['status']; $values[':id_conseiller3'] = $id_conseiller; $values[':id_profile'] = $profil; $r = excuteQuery($q, $values); } else{ $fpChange = $temporary ? ', id_fonction=(SELECT fonction FROM ca_conseillers WHERE id_conseiller = :id_conseiller1), id_profile=:id_profile' : ''; $values[':status'] = $_REQUEST['status'];$values[':id_agence'] = $id_agence;$values[':id_conseiller'] = $id_conseiller; if($temporary){ $values[':id_conseiller1'] = $id_conseiller; $values[':id_profile'] = getProfil($id_conseiller); } $orientationSetCA = ''; if(!$id_entite){ $q = "SELECT id_entite, id_orientation FROM ca_conseiller_agence WHERE id_conseiller = :id_conseiller AND id_agence = :id_agence"; $values2 = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values2); $res = $r['stmt']; if($res->rowCount() > 0){ $o = $res->fetchObject(); $id_entite = $o->id_entite; $orientation = $o->id_orientation; } } if($id_entite){ $q = "SELECT id_orientation FROM ca_orientations WHERE id_entite = :id_entite AND id_orientation=:id_orientation"; $values2 = array(':id_orientation' => $orientation, ':id_entite' => $id_entite); $r = excuteQuery($q, $values2); $res = $r['stmt']; if($res->rowCount() == 0){ $orientationSetCA = ', id_orientation=(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence_ca AND id_entite=:id_entite_ca AND orientation = 2 ORDER BY date_creation ASC LIMIT 1)'; $values[':id_agence_ca'] = $id_agence; $values[':id_entite_ca'] = $id_entite; } } $q = "UPDATE ca_conseiller_agence SET status=:status".$entite.$toRattachementQ.$fpChange.$orientationSetCA." WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $r = excuteQuery($q, $values); } break; case 'agencyAffectation': $values = array(); $entitySet = ""; $values[':id_agence3'] = $id_agence; if($id_entite){ $entitySet = ", groupe=:groupe"; $values[':groupe'] = $id_entite; } $values[':id_agence'] = $id_agence; $values[':id_entite2'] = $id_entite; $values[':orientation'] = 2; $values[':supp'] = 0; $values[':id_conseiller'] = $id_conseiller; $orientationSet = ', orientation=(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence AND id_entite=:id_entite2 AND orientation =:orientation ORDER BY date_creation ASC LIMIT 1)'; $q = "UPDATE ca_conseillers SET id_agence=:id_agence3".$entitySet.$orientationSet." WHERE supp=:supp AND id_conseiller=:id_conseiller"; $r = excuteQuery($q, $values); $values = array(); $q = "DELETE FROM ca_conseiller_agence WHERE id_agence = :agent_agency AND id_conseiller=:id_conseiller"; $values[':agent_agency'] = intval($_REQUEST["agent_agency"]); $values[':id_conseiller'] = $id_conseiller; $r = excuteQuery($q, $values); if(!checkAgcCnsLink($id_agence,$id_conseiller)){ $profil = getProfil($id_conseiller); $q = "INSERT INTO ca_conseiller_agence (id_agence,id_conseiller,id_entite, id_orientation, last_active, date_creation, status, id_fonction, id_profile) VALUES (:id_agence,:id_conseiller,:id_entite,(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence2 AND id_entite=:id_entite2 AND orientation = :orientation ORDER BY date_creation ASC LIMIT 1),now(), now(), :status,(SELECT fonction FROM ca_conseillers WHERE id_conseiller=:id_conseiller2),:id_profile)"; $values = [':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller, ':id_entite'=> $id_entite, ':id_agence2'=>$id_agence, ':id_entite2'=>$id_entite, ':orientation'=>2, ':status'=>$default_status, ':id_conseiller2' => $id_conseiller, ':id_profile' => $profil]; $r = excuteQuery($q, $values); } else{ $values = array(':status'=>$default_status, ':temporaire'=>0); $entitySetCA = ""; if ($id_entite) { $entitySetCA = ", id_entite=:id_entite"; $values[':id_entite'] = $id_entite; } $values[':id_agence'] = $id_agence; $values[':id_entite2'] = $id_entite; $values[':orientation'] = 2; $orientationSetCA = ', id_orientation=(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence AND id_entite=:id_entite2 AND orientation = :orientation ORDER BY date_creation ASC LIMIT 1)'; $q = "UPDATE ca_conseiller_agence SET status=:status, temporaire=:temporaire".$entitySetCA.$orientationSetCA.", id_fonction=(SELECT fonction FROM ca_conseillers WHERE id_conseiller = :id_conseiller1), id_profile=:id_profile WHERE id_agence=:id_agence2 AND id_conseiller=:id_conseiller"; $values[':id_agence2'] = $id_agence; $values[':id_conseiller'] = $id_conseiller; $values[':id_conseiller1'] = $id_conseiller; $values[':id_profile'] = getProfil($id_conseiller); var_dump($q); var_dump($values); $r = excuteQuery($q, $values); } break; case 'lastAgency': $q = "UPDATE ca_conseillers SET last_agency=:id_agence WHERE supp=0 AND id_conseiller=:id_conseiller"; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); if(!checkAgcCnsLink($id_agence,$id_conseiller)){ $temporary = (isset($_REQUEST["temporary"]) && $_REQUEST["temporary"]!='undefined')?"1":"0"; $status = (isset($_REQUEST["temporary"]) && $_REQUEST["temporary"]!='undefined')?",-1":""; $statusCol = (isset($_REQUEST["temporary"]) && $_REQUEST["temporary"]!='undefined')?", status":""; $profil = getProfil($id_conseiller); $q = "INSERT INTO ca_conseiller_agence (id_agence,id_conseiller, id_entite, id_orientation, last_active".$statusCol.", temporaire, date_creation, id_fonction, id_profile) VALUES (:id_agence,:id_conseiller,:id_entite,(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence2 AND id_entite=:id_entite2 AND orientation = 2 ORDER BY date_creation ASC LIMIT 1),now()".$status.",".$temporary.",now(),(SELECT fonction FROM ca_conseillers WHERE id_conseiller=:id_conseiller2),:id_profile)"; $values = [':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller, ':id_entite'=>$id_entite, ':id_agence2'=> $id_agence, ':id_entite2'=>$id_entite, ':id_conseiller2' => $id_conseiller, ':id_profile' => $profil]; $r = excuteQuery($q, $values); } else{ $entite = $id_entite?", id_entite=".$id_entite:""; $statusQ = (isset($_REQUEST["temporary"]) && $_REQUEST["temporary"]!='undefined')?", status=-1":""; $temporaryQ = (isset($_REQUEST["temporary"]) && $_REQUEST["temporary"]!='undefined')?", temporaire=1":""; $orientationSetCA = ''; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); if($id_entite){ $q = "SELECT count(*) FROM ca_conseiller_agence WHERE id_conseiller = :id_conseiller AND id_agence = :id_agence AND id_entite = :id_entite"; $values2 = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller, ':id_entite' => $id_entite); $r = excuteQuery($q, $values2); $res = $r['stmt']; if($res->rowCount() == 0){ $orientationSetCA = ', id_orientation=(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence2 AND id_entite=:id_entite AND orientation = 2 ORDER BY date_creation ASC LIMIT 1)'; $values[':id_agence2'] = $id_agence; $values[':id_entite'] = $id_entite; } } $q = "UPDATE ca_conseiller_agence SET last_active=now()".$entite.$statusQ.$temporaryQ.$orientationSetCA." WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $r = excuteQuery($q, $values); } break; case 'pileLastActive': $q = "UPDATE ca_conseiller_agence SET last_active=now() WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); break; case 'setAnticipationAgency': if(!checkAgcCnsLink($id_agence,$id_conseiller)){ $profil = getProfil($id_conseiller); $q = "INSERT INTO ca_conseiller_agence (id_agence,id_conseiller,id_entite,id_orientation,status,last_active, date_creation, id_fonction, id_profile) VALUES (:id_agence,:id_conseiller,:id_entite,(SELECT id_orientation FROM ca_orientations WHERE id_agence = :id_agence2 AND id_entite=:id_entite2 AND orientation = 2 ORDER BY date_creation ASC LIMIT 1),0,now(),now(),(SELECT fonction FROM ca_conseillers WHERE id_conseiller=:id_conseiller2),:id_profile)"; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller, ':id_entite' => $id_entite, ':id_agence2'=> $id_agence, ':id_entite2' => $id_entite, 'id_conseiller2' => $id_conseiller, ':id_profile' => $profil); $r = excuteQuery($q, $values); } break; case 'removeAnticipation': $q = "DELETE FROM ca_conseiller_agence WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; echo $q; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); break; case 'resetPassword': $HASH = isset($GLOBALS["BO_CONF"]["SECURITY"]["HASH"])?$GLOBALS["BO_CONF"]["SECURITY"]["HASH"]:'4Mb2llgMrcwuTu3cXWOr7kFPxxlf9SxdGwx4ofus0YJyDdOg'; if (isset($_POST["password"])) $password = md5($HASH.$_POST["password"]); $q = "SELECT id_conseiller FROM ca_conseillers WHERE `password`=:password AND supp=0"; $values = array(':password'=> $password); $r = excuteQuery($q, $values); $res = $r['stmt']; if($res->rowCount() > 0){ $result = array("status" => false, "message" => "Same password"); } else{ $q = "UPDATE ca_conseillers SET `password`=:password, date_modification= now() WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $values = array(':password'=> $password, ':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); $result = array("status" => true, "message" => "OK"); } echo json_encode($result); break; } } // Check if link exists in table ca_conseiller_agence function checkAgcCnsLink($id_agence,$id_conseiller){ $qx = "SELECT id_conseiller FROM ca_conseiller_agence WHERE id_agence=:id_agence AND id_conseiller=:id_conseiller"; $values = array(':id_agence'=> $id_agence, ':id_conseiller' => $id_conseiller); $rx = excuteQuery($qx, $values); $res = $rx['stmt']; if($res->rowCount() >0) return true; return false; } function getProfil($id_conseiller){ $q = "SELECT id_profil FROM ca_conseiller_profils WHERE id_conseiller = :id_conseiller ORDER BY id_profil ASC LIMIT 1"; $values = array(':id_conseiller' => $id_conseiller); $r = excuteQuery($q, $values); $res = $r['stmt']; $nb = $res->rowCount(); return $nb>0 ? $res->fetchObject()->id_profil : NULL; }