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