<?php

require_once 'Framework/Modele.php';

class Carteassure extends Modele {

    public function getListeCartePolice() {
		
		$numeroPolice = $_SESSION['numeroPolice_C'];
		
		$sql = 'SELECT * FROM carteassure 
		        WHERE (codeSociete=?
				AND idBeneficiaire IN (
				SELECT id FROM beneficiaire
				WHERE numeroPolice = ?
				and enVigueur="1"))
				order by numeroAdherent';
		
        $resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice));
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getListeStatut() {
		
		if (est_anglophone()){
			$sql = 'SELECT `codeLienParente` AS `code`,
						`libelleEng`
					FROM `lienparente`';
		}else{
			$sql = 'SELECT `codeLienParente` AS `code`,
						`libelle`
					FROM `lienparente`';
		}
		
		$resultat = $this->executerRequete($sql);
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}
	
	public function getListeEtatCarte() {
		
		if (est_anglophone()){
			$sql = 'SELECT `codeEtatCarte` AS `code`,
						`libelleEng`
					FROM `etatcarte`
					WHERE codeEtatCarte="EDI" 
					OR codeEtatCarte="NED"';
		}else{
			$sql = 'SELECT `codeEtatCarte` AS `code`,
						`libelle`
					FROM `etatcarte`
					WHERE codeEtatCarte="EDI" 
					OR codeEtatCarte="NED"';
			
		}
		
		$resultat = $this->executerRequete($sql);
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}

   public function getCarteAssure($codeLienParente, $codeEtatCarte, $debut, $fin) {
		
		$numeroPolice = $_SESSION['numeroPolice_C'];
		
		if($codeEtatCarte =="EDI"){
			$codeEtatCarte = "edite";
		}elseif($codeEtatCarte=="NED"){
			$codeEtatCarte = "1";
		}else{
			$codeEtatCarte = "";
		}
		
		$sql = 'CALL sp_getlisecarteassure_edition(?, ?, ?, ?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice, $codeLienParente, $codeEtatCarte, $debut, $fin));
		
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	
	public function notificationnocartegroupesociete()
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		
		$sql = 'CALL sp_notification_no_carte_groupe_societe(?, ?);';
		
        $resultat = $this->executerRequete($sql, array($codeSociete, $user));
		
	}
	
	public function notificationnocartegroupepolice()
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idPolice 		= $_SESSION['idPolice_C'];
		
		$sql = 'CALL sp_notification_no_carte_groupe_police(?, ?, ?);';
		
        $resultat = $this->executerRequete($sql, array($codeSociete, $user, $idPolice));
		
	}
	
	public function notificationnocartegroupeadherent()
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idAdherent 	= $_SESSION['idAdherent_C'];
		
		$sql = 'CALL sp_notification_no_carte_groupe_adherent(?, ?, ?);';
		
        $resultat = $this->executerRequete($sql, array($codeSociete, $user, $idAdherent));
		
	}
	
	public function notificationnocarte()
	{
		$idBeneficiaire	= $_SESSION['idBeneficiaire_C'];
		
		$sql = 'CALL sp_notification_no_carte(?);';
		
        $resultat = $this->executerRequete($sql, array($idBeneficiaire));
	}
	
	public function initListeEdition($idPolice, $idCollege)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'CALL sp_init_edition_carte(?, ?, ?, ?, ?);';
		
		
		$this->executerRequete($sql, array($codeSociete, $idPolice, $idCollege, $user, $idSaisie));
	}
	
	
	public function getListeEdition($idPolice)
	{
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'CALL sp_afficher_edition_carte_temp(?, ?, ?);';
		
        $resultat = $this->executerRequete($sql, array($idPolice, $user, $idSaisie));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}
	
	public function getEditionsChoisies() 
	{
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = "CALL sp_filtre_edition_carte_temp(?, ?);";
		
		$liste = $this->executerRequete($sql, array($user,$idSaisie));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getNbreEditionsChoisies()
	{
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'SELECT fn_nombre_edition_carte_choisi(?, ?) AS nbreChoisi;';
		
		$resultat = $this->executerRequete($sql, array($user, $idSaisie))->fetch(PDO::FETCH_ASSOC);
		
		return $resultat['nbreChoisi'];
		
	}
	
	public function getChoixToute()
	{
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'SELECT fn_choix_toute_edition_carte(?, ?) AS choixToute;';
		
		$resultat = $this->executerRequete($sql, array($user, $idSaisie))->fetch(PDO::FETCH_ASSOC);
		
		return $resultat['choixToute'];
	}
	
	public function reinitedition($idPolice)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'CALL sp_reinit_edition_carte(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($codeSociete, $idPolice, $user, $idSaisie));
	}
	
	public function getListeEditionCarteAssure($idPolice)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'CALL sp_liste_edition_carte_assure(?, ?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($codeSociete, $idPolice, $user, $idSaisie));
		
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}
	
	public function majtouteliste($choix)
	{
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = "CALL sp_maj_toute_liste_edition_carte(?, ?, ?);";
		
		$this->executerRequete($sql, array($user, $idSaisie, $choix));
	}
	
	public function selectionnerUnAssure($id,$choix){
		$sql = "CALL sp_selectionner_un_assure_editer_carte(?, ?);";
		$this->executerRequete($sql, array($id,$choix));
	}
	
	public function confirmedition($idPolice){
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		$sql = 'CALL sp_confirmer_liste_carte_editee(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($codeSociete, $idPolice, $user, $idSaisie));
	}

    public function getListeCarteAdherent() {
		
		$numeroPolice = $_SESSION['numeroPolice_C'];
		
		$sql = 'SELECT * FROM carteassure 
		        WHERE (codeSociete=?
				AND idBeneficiaire IN (
				SELECT id FROM beneficiaire
				WHERE numeroPolice = ?
				and enVigueur="1"))
				order by numeroAdherent';
		
        $resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice));
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }

	public function initListeEditionAdherent($idAdherent)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		
		// $sql = 'CALL sp_init_edition_carte(?, ?, ?, ?, ?);';
		$sql = 'CALL sp_init_edition_carte_adherent(?, ?, ?, ?);';
		
		
		$this->executerRequete($sql, array($codeSociete, $idAdherent, $user, $idSaisie));
	}
	
	
}