<?php
require_once 'Framework/Modele.php';
class Avenant extends Modele {

    public function getAvenant($idPolice) 
	{
		$sql = 'call sp_avenants_police(?)';
		
        $resultat = $this->executerRequete($sql, array($idPolice));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);		
    }

	public function getAvenantimportation($idPolice) 
	{
		$sql = 'call sp_avenants_police_importation(?)';
		
        $resultat = $this->executerRequete($sql, array($idPolice));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);		
    }

    public function getListe($idPolice) 
	{
		$sql = 'SELECT A.id as `code`, concat(B.libelle, " du ",DATE_FORMAT(dateEffet, "%d/%m/%Y"))  as libelle
		from avenant A 
		join typeavenant B on (B.codeTypeAvenant=A.codeTypeAvenant)
		where (idPolice=?) and (B.ajoutAliment="1")
		order by numeroAvenant DESC';

		$liste = $this->executerRequete($sql, array($idPolice));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function getListeToutes($idPolice) 
	{
		$sql = 'SELECT A.id as `code`, concat(IFNULL(B.libelle, B.codeTypeAvenant), " du ",DATE_FORMAT(dateEffet, "%d/%m/%Y"))  as libelle
		from avenant A 
		left join typeavenant B on (B.codeTypeAvenant=A.codeTypeAvenant)
		where (idPolice=?) order by numeroAvenant DESC';
		
		$liste = $this->executerRequete($sql, array($idPolice));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	
    public function getdateavenant($idAvenant, $idPolice)	
	{
		if ($idAvenant > 0){
			$sql = 'select fn_get_date_avenant(?) as dateAvenant';
			$resultat = $this->executerRequete($sql, array($idAvenant));
		}else{
			$sql = 'select fn_get_date_effet_police(?) as dateAvenant';
			$resultat = $this->executerRequete($sql, array($idPolice));
		}
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		return $ligne['dateAvenant'];		
    }	
	
    public function getListeEnCours($idPolice) 
	{
		$codeLangue = $_SESSION['lang'];

		$sql = 'CALL sp_avenants_incorporation(?, ?);';

		$liste = $this->executerRequete($sql, array($idPolice, $codeLangue));

        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function getListeEnCoursrp($idPolice) 
	{
		if (est_anglophone())
		{
			$sql = 'SELECT A.id AS `code`,
			 CONCAT(B.libelleEng, " of ",DATE_FORMAT(A.dateEffet, "%d/%m/%Y")," || ",A.numeroAvenant) AS libelle
			 A.numeroAvenant
			FROM avenant A 
			JOIN typeavenant B ON (B.codeTypeAvenant=A.codeTypeAvenant)
			JOIN police P ON (P.id=A.idPolice)
			WHERE (A.idPolice=?) AND (B.ajoutAliment="1")
			AND (A.dateEffet>=P.dateEffet) AND A.codeTypeAvenant IN ("AFN","REN","QTM")
			ORDER BY A.numeroAvenant DESC;';
		}
		else
		{
			$sql = 'SELECT A.id AS `code`, 
			CONCAT(B.libelle, " du ",DATE_FORMAT(A.dateEffet, "%d/%m/%Y")," || ",A.numeroAvenant)  AS libelle	
			FROM avenant A 
			JOIN typeavenant B ON (B.codeTypeAvenant=A.codeTypeAvenant)
			JOIN police P ON (P.id=A.idPolice)
			WHERE (A.idPolice=?) AND (B.ajoutAliment="1")
			AND (A.dateEffet>=P.dateEffet) AND A.codeTypeAvenant IN ("AFN","REN","QTM")
			ORDER BY A.numeroAvenant DESC;';
		}
		

		$liste = $this->executerRequete($sql, array($idPolice));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	 public function getunavenant($idAvenant)	
	{
		$sql = 'select * from vw_avenants where idavenant=?';
		
        $resultat = $this->executerRequete($sql, array($idAvenant));

		return $resultat->fetch(PDO::FETCH_ASSOC);
    }	
	
	public function getListePieceAvenant($codeTypeAvenant)
   {
		$sql = 'CALL sp_afficher_edition_piece_avenant(?);';

		$liste = $this->executerRequete($sql,array($codeTypeAvenant));

		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function getLienEditionPieceAvenant($codeTypeAvenant, $codeEtat) 
	{
		if (est_anglophone())
		{
			$sql = 'call sp_get_lien_edition_piece_avenant_eng(?, ?)';
		}
		else
		{
			$sql = 'call sp_get_lien_edition_piece_avenant(?, ?)';
		}

		$liste = $this->executerRequete($sql, array($codeTypeAvenant, $codeEtat));	
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }


	public function getAvenantAfacturer($dateDebut,$dateFin) 
	{
		$user = $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$idPolice 	= $_SESSION['idPolice_C'] ;
		
		if (est_anglophone())
		{
			$sql = "CALL sp_avenants_a_facturer(?, ?, ?,?,?);";
		}
		else
		{
			$sql = "CALL sp_avenants_a_facturer(?, ?, ?,?,?);";

		}
		

		$liste = $this->executerRequete($sql, array($idPolice,$user,$idSaisie,$dateDebut,$dateFin));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }

	public function selectionnerAvenant($id,$choix){
		$sql = "CALL sp_selection_avenant_a_facture(?, ?);";
		$this->executerRequete($sql, array($id,$choix));
	}

	public function majAccessoirePrime($id,$accessoire){
		$sql = "CALL sp_maj_accessoire_prime(?, ?);";
		$this->executerRequete($sql, array($id,$accessoire));
	}

	public function majAccessoirePrimeGarant($id,$accessoire){
		$sql = "CALL sp_maj_accessoire_garant_prime(?, ?);";
		$this->executerRequete($sql, array($id,$accessoire));
	}

	public function majAccessoirePrimeGestionnaire($id,$accessoire){
		$sql = "CALL sp_maj_accessoire_gestionnaire_prime(?, ?);";
		$this->executerRequete($sql, array($id,$accessoire));
	}

	public function listerAvenantAfacturer() 
	{
		$user = $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$idPolice 	= $_SESSION['idPolice_C'] ;
		
		if (est_anglophone())
		{
			$sql = "CALL sp_lister_avenants_a_facturer(?, ?, ?);";
		}
		else
		{
			$sql = "CALL sp_lister_avenants_a_facturer(?, ?, ?);";

		}
		

		$liste = $this->executerRequete($sql, array($idPolice,$user,$idSaisie));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }

	public function getAvenantExportPdf($idPolice,$idAvenant) 
	{
		// $sql = 'call sp_avenants_police(?)';
		// $query = "SELECT * FROM beneficiaireprime WHERE idPolice=? and idAvenant=? ";
		// $query = "SELECT * FROM beneficiaireprime WHERE idPolice=? and idAvenant=? ";
		
        // $resultat = $this->executerRequete($query, array($idPolice,$idAvenant));
		// $query = "SELECT * FROM beneficiaireprime WHERE idPolice=? and idAvenant=? ";
		
        // $resultat = $this->executerRequete($query, array($idPolice,$idAvenant));
		$query = "SELECT * FROM beneficiaireprime";
		
        $resultat = $this->executerRequete($query);
		

		return $resultat->fetchAll(PDO::FETCH_ASSOC);		
    }

	public function getAvenantSansPrime($dateDebut,$dateFin) 
	{
		$user = $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$idPolice 	= $_SESSION['idPolice_C'] ;
		
		if (est_anglophone())
		{
			$sql = "CALL sp_avenants_sans_prime(?, ?, ?,?,?);";
		}
		else
		{
			$sql = "CALL sp_avenants_sans_prime(?, ?, ?,?,?);";

		}

		$liste = $this->executerRequete($sql, array($idPolice, $user, $idSaisie, $dateDebut, $dateFin));
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }

	public function clotureravenantsansprime($idAvenant){
		$user = $_SESSION['login'];

		$sql = "CALL sp_facturer_avenant_sans_prime(?, ?);";
		
		$this->executerRequete($sql, array($idAvenant, $user));
	}

}