<?php

require_once 'Framework/Modele.php';

class Lettrecleacte extends Modele {

    public function getLettrecleacte() {
		
		if (est_anglophone()){
			$sql = 'SELECT id, lettreCle, descriptionEng 
			FROM p_lettrecle order by lettreCle';
		}else{
			$sql = 'SELECT id, lettreCle, description 
			FROM p_lettrecle order by lettreCle';
		}
		
		$liste = $this->executerRequete($sql);
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getListeLettrecleacte($codePrestataire) {
		
		$sql = 'SELECT * 
		FROM p_lettrecle WHERE lettreCle NOT IN 
		(SELECT lettreCle FROM prestataire_lettre_cle WHERE codePrestataire=?)
		order by lettreCle';
		
		$liste = $this->executerRequete($sql, array($codePrestataire));
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getLettrecleacteprestataire($codePrestataire) {
		
		$sql = 'SELECT * 
		FROM prestataire_lettre_cle WHERE codePrestataire=?
		order by lettreCle';
		
		$liste = $this->executerRequete($sql, array($codePrestataire));
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getLettrecleacteId($id) {
		/*
		if (est_anglophone()){
			$sql = 'SELECT id, lettreCle, descriptionEng 
			FROM p_lettrecle WHERE id=?;';
		}else{
			$sql = 'SELECT id, lettreCle, description 
			FROM p_lettrecle WHERE id=?;';
		}*/
		
		$sql = 'SELECT * 
			FROM p_lettrecle WHERE id=?;';
		
		$resultat = $this->executerRequete($sql, array($id));
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }
	
	public function existeligne($lettreCle) {
        $sql = 'select id FROM p_lettrecle WHERE (lettreCle=?);';
        $resultat = $this->executerRequete($sql, array($lettreCle));
		return ($resultat->rowCount() > 0);
    }
	
	public function ajouter($lettreCle, $description, $descriptionEng){
		
		$sql = 'INSERT INTO p_lettrecle (lettreCle, description, descriptionEng) 
		       VALUES (?, ?, ?);';
        $resultat = $this->executerRequete($sql, array($lettreCle, $description, $descriptionEng));
		
	}
	
	public function modifier($lettreCle, $description, $descriptionEng, $id){
		
		$sql = 'UPDATE p_lettrecle 
				SET lettreCle =?, description=?, descriptionEng=? 
		       WHERE (id= ?);';
        $resultat = $this->executerRequete($sql, array($lettreCle, $description, $descriptionEng, $id));
		
	}
  
	public function ajouterunelettrecleprestataire($lettreCle,$codePrestataire){
		
		$sql = 'INSERT INTO prestataire_lettre_cle(lettreCle,codePrestataire) 
		       VALUES (?,?);';
        $resultat = $this->executerRequete($sql, array($lettreCle, $codePrestataire));
		
	}
	
	public function majCoutLettrecle($idPresta, $cout){
		
		$sql = 'UPDATE prestataire_lettre_cle 
				SET cout = ? WHERE (id= ?);';
        $resultat = $this->executerRequete($sql, array($cout, $idPresta));
		
	}
	
	public function majcoutnuitprestataire($idPresta, $coutNuit){
		
		$sql = 'UPDATE prestataire_lettre_cle 
				SET coutNuit = ? WHERE (id= ?);';
        $resultat = $this->executerRequete($sql, array($coutNuit, $idPresta));
		
	}
	
	public function retirerunelettrecle($idPresta){
		
		$sql = 'DELETE FROM prestataire_lettre_cle 
				WHERE (id= ?);';
        $resultat = $this->executerRequete($sql, array($idPresta));
		
	}
	
	public function getListeTarifLettrecleacte($codeTarifActe) {
		
		$sql = 'CALL sp_afficher_lettre_cle(?);';
		
		$liste = $this->executerRequete($sql, array($codeTarifActe));
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getLettrecleactetarif($codeTarifActe, $reinitialiser) {
		
		$idSaisie 	= $_SESSION['idSaisie'];
		$user 		= $_SESSION['login'];
		
		$sql = 'CALL sp_afficher_detailtariflettrecle_temp_new(?, ?, ?, ?);';
		
		$liste = $this->executerRequete($sql, array($codeTarifActe, $user, $idSaisie, $reinitialiser));
		
		return $liste->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function ajouterunelettrecletarif($idLettre,$codeTarifActe){
		
		$idSaisie 			= $_SESSION['idSaisie'];
		$user 				= $_SESSION['login'];
		
		$codeSociete 		= $_SESSION['codeSociete'];
		
		$sql = 'call sp_ajouter_une_lettrecle_au_tarif_temp_new(?, ?, ?, ?, ?)';
		
        $resultat = $this->executerRequete($sql, array($codeSociete, $idLettre, $codeTarifActe, $user, $idSaisie));
		
	}
	
	public function retirerunelettrecletarif($idDetail){
		
		$sql = 'call sp_retirer_une_lettrecle_du_tarif(?)';
		
        $resultat = $this->executerRequete($sql, array($idDetail));
	}
	
	public function validertarif($codeTarifActe)
	{	
		$idSaisie 			= $_SESSION['idSaisie'];
		$user 				= $_SESSION['login'];
		
		$sql = 'call sp_valider_detailtariflettrecle_famille_temp_new(?, ?, ?)';
		
		$this->executerRequete($sql, array($codeTarifActe, $user, $idSaisie));
    }
	
	public function majeffet($idDetail, $dateEffet)
	{	
		$sql = 'call sp_maj_dateeffet_tariflettrecle_temp_new(?, ?)';
		
		$this->executerRequete($sql, array($idDetail, $dateEffet));
    }
	
	public function majPrix($idDetail, $prix)
	{
		$sql = 'call sp_maj_prix_tariflettre_temp_new(?, ?)';
		
		$this->executerRequete($sql, array($idDetail, $prix));
    }
	
	public function majprixnuitferier($idDetail, $prix)
	{

		$sql = 'call sp_maj_prix_nuit_tariflettrecle_temp_new(?, ?)';
		
		$this->executerRequete($sql, array($idDetail, $prix));
    }
	
	public function getsuppr($idDetail)
	{
		$sql = 'SELECT fn_get_supprdetailtariflettrecle(?) AS suppr;';
		
		$resultat = $this->executerRequete($sql, array($idDetail))->fetch(PDO::FETCH_ASSOC);
		
		return $resultat['suppr'];
	}
	
	public function supprimertarif($idDetail)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_supprimer_tariflettrecle_temp_new(?, ?)';
		
		$this->executerRequete($sql, array($idDetail, $user));
    }	
}