<?php
require_once 'Framework/Modele.php';

class Beneficiaire_d extends Modele 
{
	public function enregistrerparamimportassure($nomOrigine, $nouveau_fichier, $taille, $numeroOptionTarif)
	{
		$idPolice 		= $_SESSION['idPolice_d_C'];
		
		
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		$cheminFichier 	= $nouveau_fichier;

		$sql = 'call sp_r_enregistrer_param_import_assure_d(?, ?, ?, ?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idPolice, $codeSociete, $nomOrigine, $cheminFichier, $taille, $user, $numeroOptionTarif));
    }	

    public function getFichierImport($numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'CALL sp_afficher_fichier_import_d(?, ?, ?);';
		
		//var_dump(array($idPolice, $user, $numeroOptionTarif)); die();
				
        $resultat = $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function initIinsererLigneModeleAssure($numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user = $_SESSION['login'];
		
		//var_dump(array($idPolice, $user, $numeroOptionTarif));
		//die();

		$sql = 'CALL sp_init_inserer_ligne_modele_assure_d(?, ?, ?);';
				
        $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
    }

    public function insererLigneModeleAssure($numeroOptionTarif, $categorie, $numeroAdherent, 
			$noFamille, $nom, $prenoms, $codeLienParente, $codeNaturePiece, $numeroPiece, $sexe, 
			$codeGroupeSanguin, $dateNaissance, $telephonePortable, $numeroMatricule, $numeroCmu,
			$dateSousscription, $numeroBs, $ancienNumeroAssure, $conserverAncienNumeroAssure, 
			$ancienNumeroAdherent, $codeTypeRemboursement, $email, $codeLangue, $prefixePaiementMobile, $mobilePaiement)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$idSaisie	= $_SESSION['idSaisie'];
		$user 		= $_SESSION['login'];
		
		$sql = 'call sp_inserer_ligne_modele_assure_devis(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
        $resultat = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif, $user, $idSaisie, 
			$categorie, $numeroAdherent, $noFamille, $nom, $prenoms, $codeLienParente, $codeNaturePiece, 
			$numeroPiece, $sexe, $codeGroupeSanguin, $dateNaissance, $telephonePortable, $numeroMatricule, 
			$numeroCmu, $dateSousscription, $numeroBs, $ancienNumeroAssure, $conserverAncienNumeroAssure, 
			$ancienNumeroAdherent, $codeTypeRemboursement, $email, $codeLangue, $prefixePaiementMobile, $mobilePaiement));
    }


    public function getListeAssuresImportes($numeroOptionTarif)
	{
		$user 		= $_SESSION['login'];
		$idPolice 	= $_SESSION['idPolice_d_C'];

		$sql = 'CALL sp_afficher_lignes_importees_devis(?, ?, ?);';
				
        $beneficiaires = $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
		
		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }
	
    public function gererIncorporationSurFamExistante()
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'CALL sp_gerer_incorporation_sur_fam_existante(?, ?);';
				
        $this->executerRequete($sql, array($idPolice, $user));
    }
	
    public function majetape($numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'CALL sp_r_maj_etape_3_import_assure_d(?, ?, ?);';
				
        $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
    }

    public function getnbadhimpsanscollege($numeroOptionTarif)
    {
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

        $sql = 'SELECT fn_nb_adh_imp_sans_college_devis(?, ?, ?) AS nb_adh;';
		
        $resultat = $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		return $ligne['nb_adh'];
    }	

    public function getadhimpfamilleplusieursadherent($numeroOptionTarif)
    {
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

        $sql = 'CALL sp_adh_imp_famille_plusieurs_adherent_devis(?, ?, ?);';
		
        $resultat = $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		return $ligne['noFamille'];
    }	

    public function getListeAdherentImportes($idCollege, $numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'CALL sp_afficher_adherents_importees_devis(?, ?, ?, ?)';
				
        $beneficiaires = $this->executerRequete($sql, array($idPolice, $user, $idCollege, $numeroOptionTarif));
		
		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }
	
    public function getListeAdherentImportesCollege($idCollege, $numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'CALL sp_afficher_adherents_importees_college_devis(?, ?, ?, ?)';
				
		$beneficiaires = $this->executerRequete($sql, array($idPolice, $user, $idCollege, $numeroOptionTarif));
		
		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }

	public function ajoutertousadherentaucollege($idCollege, $numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'call sp_r_ajouter_tous_adherent_importee_college_devis(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idPolice, $user, $idCollege, $numeroOptionTarif));
    }	

	public function retirertousadherentaucollege($idCollege, $numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'call sp_r_retirer_tous_adherent_importee_college_devis(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idPolice, $user, $idCollege, $numeroOptionTarif));
    }	

	public function retirerunadherentaucollege($idBeneficiairemodel)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_r_retirer_adherent_importee_college_devis(?, ?)';
		
		$this->executerRequete($sql, array($user, $idBeneficiairemodel));
    }	

	public function ajouterunadherentaucollege($idBeneficiairemodel, $idCollege, $numeroOptionTarif)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_r_ajouter_adherent_importee_college_devis(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($user, $idBeneficiairemodel, $idCollege, $numeroOptionTarif));
    }	

	public function ajoutersanscollegeadherentaucollege($idCollege, $numeroOptionTarif)
	{
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$user 		= $_SESSION['login'];

		$sql = 'call sp_r_ajouter_adherent_sans_college_importee_college_devis(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idPolice, $user, $idCollege, $numeroOptionTarif));
    }	
		
	public function incorpoerassuresimportesdevis()
	{
		$user 		= $_SESSION['login'];
		$idPolice 	= $_SESSION['idPolice_d_C'];
		$numeroOptionTarif = $_SESSION['numeroOptionTarif'];

		$sql = 'call sp_incorporer_assures_importes_devis(?, ?, ?);';
				
        $this->executerRequete($sql, array($idPolice, $user, $numeroOptionTarif));
    }

    public function getListeAssuresDevis($numeroOptionTarif)
	{
		$user 		= $_SESSION['login'];
		$idPolice 	= $_SESSION['idPolice_d_C'];

		$sql = 'CALL sp_afficher_lignes_assures_devis(?, ?);';
				
        $beneficiaires = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
		
		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getlisteadherentdevis($idPolice, $numeroOptionTarif)
	{
		$sql = 'call sp_liste_adherent_devis(?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);		
    }	

     public function getBeneficiairesId($idAdherent) 
	{
		$sql = 'call sp_get_beneficiaires_adherent_devis(?);';		
		
        $beneficiaires = $this->executerRequete($sql, array($idAdherent));

		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }

   public function getTotalBeneficiairesId($idAdherent) {
        $sql = 'select IFNULL(sum(primeNette),"0") as primeNette, IFNULL(sum(commission),"0") as commission, 
		IFNULL(sum(primeHt),"0") as primeHt, IFNULL(sum(taxe),"0") as taxe, 
		IFNULL(sum(fraisCarte),"0") as fraisCarte, IFNULL(sum(primeTtc),"0") as primeTtc
		FROM d_beneficiaire A WHERE (idAdherent=?)';
		
        $resultat = $this->executerRequete($sql, array($idAdherent));

		return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function getBeneficiairesAexporterDevis($idPolice, $numeroOptionTarif)
	{
		$sql = 'call sp_export_assure_devis(?, ?);';

        $beneficiaires = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));

		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getEnteteBeneficiairesAimprimer($idPolice) 
	{
		$sql = 'call sp_entete_liste_assure_devis(?);';
				
        $entete = $this->executerRequete($sql, array($idPolice));
		return $entete->fetch(PDO::FETCH_ASSOC);
    }

    public function getBeneficiairesAimprimer($idPolice, $numeroOptionTarif) 
	{
		$sql = 'call sp_imprimer_assure_devis(?, ?);';

		$beneficiaires = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
		
		return $beneficiaires->fetchAll(PDO::FETCH_ASSOC);
    }
	
    public function getBeneficiairesAimprimerTotal($idPolice, $numeroOptionTarif) 
	{
		$sql = 'call sp_imprimer_assure_devis_total(?, ?);';

		$beneficiaires = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif));
		
		return $beneficiaires->fetch(PDO::FETCH_ASSOC);
    }
	
	public function getlisteassuresparcollegedevis($idPolice, $numeroOptionTarif, $idCollegePolice)
	{
		$sql = 'call sp_liste_assures_par_college_devis(?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($idPolice, $numeroOptionTarif, $idCollegePolice));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);		
    }
}