<?php
require_once 'Framework/Modele.php';
class Collegepolice extends Modele {

    public function getCollegepoliceId($idCollege) {
		$sql = 'SELECT 	A.id AS idCollege, A.*, B.libelle as produit
		from college A
		join produit B on (B.codeSociete=A.codeSociete) AND (B.codeProduit=A.codeProduit)
		where A.id=?';

        $college = $this->executerRequete($sql, array($idCollege));
        return $college->fetch(PDO::FETCH_ASSOC);
    }

    public function getCollegesPolice($idPolice) 
	{
		$sql = 'call sp_get_colleges_police(?);';

        $college = $this->executerRequete($sql, array($idPolice));

		return $college->fetchAll(PDO::FETCH_ASSOC);
		
    }

    public function getTotaldcollege($idPolice) {
        $sql = 'call sp_totalcollege(?)';
        $totalcollege = $this->executerRequete($sql, array($idPolice));
		return $totalcollege->fetch(PDO::FETCH_ASSOC);
    }
	
    public function supprimer($idCollege) 
	{
		$sql = 'select id from adherent where (idCollege=?) limit 1';
        $resultat = $this->executerRequete($sql, array($idCollege));

        if($resultat->rowCount() == 0)
		{
			$sql = 'DELETE FROM college WHERE (id=?)';
			$this->executerRequete($sql, array($idCollege));
			
			$sql = 'DELETE FROM garantiecollege WHERE (idCollege=?)';
			$this->executerRequete($sql, array($idCollege));
			
			$sql = 'DELETE FROM prestationcollege WHERE (idCollege=?)';
			$this->executerRequete($sql, array($idCollege));
		}
    }	
	
    public function archiverGarntie($idCollege) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_archiver_garantie(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idCollege, $user));
    }	
	
    public function desarchiverGarntie($idCollege) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_de_archiver_garantie(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idCollege, $user));
    }	
	
    public function getPrimeTtcFamille($idCollege)
	{
		$sql = 'SELECT IFNULL(primeTtcAdherent,"0") AS primeTtc FROM college where (id=?)';
		$resultat = $this->executerRequete($sql, array($idCollege));
        if($resultat->rowCount() > 0)
		{
			$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
			return $ligne['primeTtc'];
		}
        else
		{
			return 0;
		}
    }	

    public function getListe($idPolice) {
		if($_SESSION['codeTypeAvenant_C']=="REN")
		{
		$sql = 'SELECT id as `code`, libelleCollege as libelle FROM college 
		where (idPolice=?) order by 2';
		} else
		{
		$sql = 'SELECT id as `code`, libelleCollege as libelle FROM college 
		where (idPolice=?) and (garantieArchive="1") order by 2';
		}

		$liste = $this->executerRequete($sql, array($idPolice));
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function getListeToutes($idPolice) {
		$sql = 'SELECT id as `code`, libelleCollege as libelle FROM college where (idPolice=?) order by 2';
		$liste = $this->executerRequete($sql, array($idPolice));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function getRapportSpCollege($idCollege, $dateReference)
	{
		$sql = 'call sp_rapport_sp_college(?, ?)';
				
        $sppolice = $this->executerRequete($sql, array($idCollege, $dateReference));
		return $sppolice->fetch(PDO::FETCH_ASSOC);
    }
	
    public function refuserderogationmedicament($idCollege) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_refuser_derogation_medicament(?, ?)';

        $resultat = $this->executerRequete($sql, array($idCollege, $user));
    }	
	
    public function accorderderogationmedicament($idCollege) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_accorder_derogation_medicament(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idCollege, $user));
    }	
	
    public function archiveradherent($idAherent) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_archiver_adherent(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idAherent, $user));
    }	
	
    public function desarchiveradherent($idAherent) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_de_archiver_adherent(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idAherent, $user));
    }	
	
    public function archiverbeneficiaire($idBeneficiaire)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_archiver_beneficiaire(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idBeneficiaire, $user));
    }	
	
    public function desarchiverbeneficiaire($idBeneficiaire) 
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_de_archiver_beneficiaire(?, ?)';
		
        $resultat = $this->executerRequete($sql, array($idBeneficiaire, $user));
    }	

    public function fn_get_college_familiale($idPolice)
    {
		$sql = 'SELECT fn_get_college_familiale(?) AS dernierIdCollege;';
		
		$resultat = $this->executerRequete($sql, array($idPolice));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);

		$dernierIdCollege = $ligne['dernierIdCollege'];
		
		$_SESSION['dernierIdCollege'] = $dernierIdCollege;
		
		return $dernierIdCollege;
    }	

}