<?php
require_once 'Framework/Modele.php';
class Collegepolice_d extends Modele {

    public function getCollegepoliceId($idCollege) {
		$sql = 'SELECT 	A.id AS idCollege, A.*, B.libelle as produit
		from d_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);
		return $college->fetch(PDO::FETCH_ASSOC);
    }

    public function getCollegesPolice($idPolice, $numeroOptionTarif) 
	{
		$sql = 'call sp_d_get_colleges_police(?, ?);';

        $college = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
        // return $college;
		return $college->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getTotaldcollege($idPolice, $numeroOptionTari) 
	{
        $sql = 'call sp_totalcollege_d(?, ?)';
        $totalcollege = $this->executerRequete($sql, array($idPolice, $numeroOptionTari));
		return $totalcollege->fetch(PDO::FETCH_ASSOC);
    }
	
    public function supprimer($idCollege) 
	{
		/*
		$sql = 'DELETE from d_college WHERE (id=?)';
		$this->executerRequete($sql, array($idCollege));
		
		$sql = 'DELETE FROM d_garantiecollege WHERE (idCollege=?)';
		$this->executerRequete($sql, array($idCollege));
		
		$sql = 'DELETE FROM d_prestationcollege WHERE (idCollege=?)';
		$this->executerRequete($sql, array($idCollege));
		*/
		
		$user = $_SESSION['login'];
		
		$sql  = 'call sp_d_supprimer_college(?, ?);';

		$resultat = $this->executerRequete($sql, array($idCollege, $user));
    }	
	
    public function getPrimeTtcFamille($idCollege)
	{
		$sql = 'SELECT IFNULL(primeTtcAdherent,"0") AS primeTtc from d_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) {
		$sql = 'SELECT id as `code`, libelleCollege as libelle from d_college 
		where (idPolice=?) order by 2';
		$liste = $this->executerRequete($sql, array($idPolice));
        // return $liste->fetchAll(PDO::FETCH_ASSOC);
		return $liste->fetch(PDO::FETCH_ASSOC);
    }	

    public function getListeToutes($idPolice) {
		$sql = 'SELECT id as `code`, libelleCollege as libelle from d_college where (idPolice=?) order by 2';
		$liste = $this->executerRequete($sql, array($idPolice));
       //  return $liste->fetchAll(PDO::FETCH_ASSOC);
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function getoptionstarif($idPolice) {
		$sql = 'SELECT numeroOptionTarif as `code`, libelleOptionTarif as libelle from d_optionstarif where (idPolice=?) order by 1';
		$liste = $this->executerRequete($sql, array($idPolice));
       // return $liste->fetchAll(PDO::FETCH_ASSOC);
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function supprimeroption($idPolice, $numeroOptionTarif) 
	{
		$user = $_SESSION['login'];
		
		$sql  = 'call sp_d_supprimer_option_tarif(?, ?, ?);';

		$resultat = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif, $user));
		$ligne    = $resultat->fetch(PDO::FETCH_ASSOC);
		$_SESSION['numeroOptionTarif'] = $ligne['numeroOptionTarif'];
    }
	
    public function getListeOptionTarifLong($idPolice, $numeroOptionTarif) 
	{
		$sql = 'SELECT A.id AS `code`, CONCAT(A.libelleCollege, " ( ", A.codeProduit, " ) ", " ", B.libelle, " [", B.codeModecalculPrime,"]" ) AS libelle 
		FROM d_college A  LEFT JOIN produit B ON (B.codeProduit=A.codeProduit)
		WHERE (A.idPolice=?) AND (A.numeroOptionTarif=?) ORDER BY 2';
		
		$liste = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
}