<?php
require_once 'Framework/Modele.php';

class Gestionconfiee extends Modele {

	public function getGcAssureurCode($codeGcAssureur)
    {
		$sql = "SELECT * FROM gc_assureur WHERE actif = 1 AND codeGcAssureur=?";
        $resultat = $this->executerRequete($sql, array($codeGcAssureur));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
        $_SESSION['codeGcAssureur_C']	= $context['codeGcAssureur'];
        $_SESSION['GcAssureur_C']		= $context['libelle'];
		return $context;
    }

	public function getGcAssureurId($idGc)
    {
		$sql = "SELECT a.*, b.libelle as typegc FROM gc_assureur a
				JOIN gc_typegarant b ON (a.typeGarant = b.id)
				WHERE a.id=?";
        $resultat = $this->executerRequete($sql, array($idGc));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
        $_SESSION['codeGcAssureur_C']	= $context['codeGcAssureur'];
        $_SESSION['GcAssureur_C']		= $context['libelle'];
		return $context;
    }
	
	public function getCodeGcAssureur($idGc)
    {
		$sql = "SELECT codeGcAssureur FROM gc_assureur 
				WHERE id=?";
        $resultat = $this->executerRequete($sql, array($idGc));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
       
		return $context['codeGcAssureur'];
    }
	
	public function gcTauxGarant($codeGcAssureur)
    {
		$sql = "SELECT gcTauxFraisReel, gcTauxCommissionGerant, 
				gcLimiteConsommation, gcTauxRistourne, seuilRdGarant
				FROM gc_assureur WHERE codeSociete=? AND codeGcAssureur=?";
				
        $resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $codeGcAssureur));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
		
    }
	
	
    public function getListe() 
	{
		$sql = 'SELECT codeGcAssureur  AS `code`, CONCAT(libelle, " [",codeGcAssureur,"]") as libelle 
				FROM gc_assureur WHERE codeSociete= ? AND actif = 1 ORDER BY libelle;';
		$resultat = $this->executerRequete($sql,array($_SESSION['codeSociete']));
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }


    public function getGcAssureurs() 
	{
		$sql = "SELECT a.*, b.libelle as typegc FROM gc_assureur a
				JOIN gc_typegarant b ON (a.typeGarant = b.id)
				WHERE a.actif = 1 order by libelle";		
		$resultat = $this->executerRequete($sql);
		
		//var_dump($sql); die();
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	
    public function getGcPolices() 
	{
		$sql = "select * from vw_gc_police_potentiel order by souscripteur, idPolice;";		
		$resultat = $this->executerRequete($sql);
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }

    public function ajouter($libelle, $email, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxTva,
	$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $typeGarant, $telephoneGarant, $representant,$siglerepresentant,
	$servicerepresentant,$telephonerepresentant,$gcTauxProvision, $gcForfaitProvision, $codeTarifActe,
	$gcTauxCommissionGerant, $gcTauxRistourne, $gcLimiteConsommation, $seuilRdGarant, $codeTypeRemboursement, 
	$adressePostale, $numerodeCompteComptableRD, $adresseGeographique, $numerodeCompteComptableTP, $numeroCompteContribuable, 
	$codeModePaiementDemande, $encaissementExige)
	{
		
		$codeSociete	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
		
		$sql = "call sp_creer_gcassureur(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		
        $this->executerRequete($sql, array($codeSociete, $libelle, $email, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxTva,
		$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $typeGarant, $telephoneGarant, $representant,$siglerepresentant,$servicerepresentant,
		$telephonerepresentant, $user, $gcTauxProvision, $gcForfaitProvision, $codeTarifActe, $gcTauxCommissionGerant, $gcTauxRistourne, 
		$gcLimiteConsommation, $seuilRdGarant, $codeTypeRemboursement, $adressePostale, $numerodeCompteComptableRD, $adresseGeographique, 
		$numerodeCompteComptableTP, $numeroCompteContribuable, $codeModePaiementDemande, $encaissementExige));
    }	
	
     public function enregistrermodif($idGc, $codeGcAssureur, $libelle, $email, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxTva,
	$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $typeGarant, $telephoneGarant, $representant,$siglerepresentant,$servicerepresentant,
	$telephonerepresentant,$gcTauxProvision,$gcForfaitProvision, $codeTarifActe, $gcTauxCommissionGerant, $gcTauxRistourne, $gcLimiteConsommation,
	$seuilRdGarant, $codeTypeRemboursement, $adressePostale, $numerodeCompteComptableRD, $adresseGeographique, $numerodeCompteComptableTP, 
	$numeroCompteContribuable, $codeModePaiementDemande, $encaissementExige)
	{
		$user = $_SESSION['login'];
	
		$sql = 'call sp_modifier_gcassureur(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
        $this->executerRequete($sql, array($idGc, $codeGcAssureur, $libelle, $email, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxTva,
		$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $typeGarant, $telephoneGarant, $representant, $siglerepresentant,$servicerepresentant,
		$telephonerepresentant,$user, $gcTauxProvision,$gcForfaitProvision, $codeTarifActe, $gcTauxCommissionGerant, $gcTauxRistourne, 
		$gcLimiteConsommation, $seuilRdGarant, $codeTypeRemboursement, $adressePostale, $numerodeCompteComptableRD, $adresseGeographique, 
		$numerodeCompteComptableTP, $numeroCompteContribuable, $codeModePaiementDemande, $encaissementExige));
    }
	
	public function majparamfacturegarant(
			$idGc,	$codeConvention, $codePeriodiciteAppelFond, $codePeriodiciteHonoraire, $codePeriodiciteFacturationDeficit,
			$montantBudget, $forfaitCautionPermanant, $forfaitHonoraire, $gcTauxFraisReel, $gcTauxRedressement,$gcTauxTva,
			$gcTauxCommissionGerant, $codeSourceFondGarantAppelFond, $codeAssietteFacturationGarantAppelFond, /*$forfaitAppelFond,*/
			$codeSourceFondGarantHonoraire,$nbMoisCaution, $seuilComplementCaution, $codeAssietteFacturationGarantHonoraire,
			$codeSourceFondGarantCaution, $codeAssietteFacturationGarantCaution, $nbAdherent, $nbBeneficiaire
		)
		
	{
		$user = $_SESSION['login'];
	
		$sql = 'call sp_maj_paramfacturegarant(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
		//var_dump($codePeriodiciteHonoraire);
		
        $this->executerRequete($sql, array(
			$idGc, $codeConvention, $codePeriodiciteAppelFond, $codePeriodiciteHonoraire, $codePeriodiciteFacturationDeficit,
			$montantBudget, $forfaitCautionPermanant, $forfaitHonoraire, $user, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxTva,
			$gcTauxCommissionGerant, $codeSourceFondGarantAppelFond, $codeAssietteFacturationGarantAppelFond, /*$forfaitAppelFond,*/
			$codeSourceFondGarantHonoraire, $nbMoisCaution, $seuilComplementCaution, $codeAssietteFacturationGarantHonoraire,
			$codeSourceFondGarantCaution, $codeAssietteFacturationGarantCaution, $nbAdherent, $nbBeneficiaire
		));
    }
	
	
    public function supprimer($id) {
		$sql = 'DELETE FROM gc_assureur WHERE (id=?)';
        $this->executerRequete($sql, array($id));
    }		
	
    public function existeligne($codeGcAssureur) {
        $sql = 'select id FROM gc_assureur WHERE(codeGcAssureur=?)';
        $resultat = $this->executerRequete($sql, array($codeGcAssureur));
		return ($resultat->rowCount() > 0);
    }
	
    public function getEmailGcAssureur($codeGcAssureur)
    {
		$sql = 'SELECT ifnull(email, "kanelazeni@gmail.com") as email, 
		libelle FROM gc_assureur WHERE (codeGcAssureur=?)';

        $resultat = $this->executerRequete($sql, array( $codeGcAssureur));
        return $resultat->fetch(PDO::FETCH_ASSOC);
    }
	
    public function getGcAssureurslike($codeGcAssureur, $nomsearch)
	{
		$nomsearch 		= contruireParamLike($nomsearch);
		$codeGcAssureur = contruireParamLike($codeGcAssureur);

		$sql = 'call sp_r_gc_assureurs_like(?, ?)';
        $resultat = $this->executerRequete($sql, array($codeGcAssureur, $nomsearch));
		$resultat = $this->executerRequete($sql);
        return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function getGcAssureursvide() 
	{		
		$sql 		= 'CALL sp_r_gc_assureurs_vide();';		
        $prestataires 	= $this->executerRequete($sql);
		
        return $prestataires;
    }
	
	public function listemailGcAssureur()
	{
		$sql = "SELECT codeGcAssureur,	libelle, email FROM gc_assureur WHERE (email>' ') ORDER BY codeGcAssureur;";
		
		$resultat = $this->executerRequete($sql);
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	
    public function sp_getGcPoliceId($idPolice)
    {
		$sql = 'call sp_getGcPoliceId(?)';

        $resultat = $this->executerRequete($sql, array( $idPolice));
        return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function getInfoGcAssureur($codeGcAssureur)
    {
		$sql = 'call sp_get_infos_gc_assureur(?)';

        $resultat = $this->executerRequete($sql, array( $codeGcAssureur));
        return $resultat->fetch(PDO::FETCH_ASSOC);
    }
	

		
    public function creerpolicegc($idPolice, $codeGcAssureur, $tauxFraisReel, $tauxRedressement, $tauxTva,
	$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp)	
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_creer_gc_police(?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
        $this->executerRequete($sql, array($idPolice, $codeGcAssureur, $tauxFraisReel, $tauxRedressement, $tauxTva,
		$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $user));
    }	
	
    public function modifierpolicegc($idPolice, $tauxFraisReel, $tauxRedressement, $tauxTva,
	$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_modifier_gc_police(?, ?, ?, ?, ?, ?, ?, ?);';
		
        $this->executerRequete($sql, array($idPolice, $tauxFraisReel, $tauxRedressement, $tauxTva,
		$fraisCarteAfn, $fraisCarteRen, $fraisCarteImp, $user));
    }	
	
	public function requetesfacturegc($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_gc(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function requetesfacturegcexport($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_gc_export(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function getdecomptesgc($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_get_decompte_gc(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function gcinittempreglement()
	{
		$codeGcAssureur		= $_SESSION['codeGcAssureur'];
		$user 				= $_SESSION['login'];
		$idSaisie 			= $_SESSION['idSaisie'];
		
		$sql = 'call sp_r_gc_init_temp_reglement(?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $idSaisie, $user));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
        $idGcTempReglement = $ligne['idGcTempReglement'];
		return $idGcTempReglement;
    }	
	
	
    public function confirminitreglement($observations)
	{
		$codeSociete		= $_SESSION['codeSociete'];
		$idGcTempReglement 	= $_SESSION['idGcTempReglement'];
		$user 				= $_SESSION['login'];
		
		$sql = 'call sp_r_gc_init_reglement(?, ?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($codeSociete, $idGcTempReglement, $user, $observations));

		$context = $resultat->fetch(PDO::FETCH_ASSOC);
        $_SESSION['idGcReglement']	= $context['idGcReglement'];
    }	
	
	public function getGcNewFactureId()
    {
		$idGcTempReglement 	= $_SESSION['idGcTempReglement'];
		
		$sql = "select * from vw_r_temp_reglement_gc where (idGcTempReglement=?);";
		
        $resultat = $this->executerRequete($sql, array($idGcTempReglement));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }
	
	public function getGcContextById($idGcReglement)
    {
		$sql = "SELECT gcNumeroDecompte FROM gc_reglement WHERE id=?";
        $resultat = $this->executerRequete($sql, array($idGcReglement));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
		
		$_SESSION['idGcReglement'] 		= $idGcReglement;
		$_SESSION['gcNumeroDecompte'] 	= $context['gcNumeroDecompte'];
    }
	
	public function getGcContextByNo($gcNumeroDecompte)
    {
		$sql = "SELECT id AS idGcReglement FROM gc_reglement WHERE (codeSociete=?) AND (gcNumeroDecompte=?);";
        $resultat = $this->executerRequete($sql, array($gcNumeroDecompte));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
		
		$_SESSION['gcNumeroDecompte'] 	= $context['gcNumeroDecompte'];
		$_SESSION['idGcReglement'] 		= $context['idGcReglement'];
    }
	
    public function recapgcreglement()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$sql = 'call sp_r_recap_gc_reglement(?)';
		$this->executerRequete($sql, array($idGcReglement));
    }
	
	public function getgcreglement($idGcReglement) 
	{
		$sql = 'call sp_r_get_gc_reglement(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }	
	
	public function get_factures_gc_non_sel($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$idGcReglement 		= $_SESSION['idGcReglement'];
		$gcNumeroDecompte 	= $_SESSION['gcNumeroDecompte'];
		$user 				= $_SESSION['login'];
		$idSaisie 			= $_SESSION['idSaisie'];

		$sql = 'call sp_r_get_factures_gc_non_sel(?, ?, ?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $gcNumeroDecompte, $idGcReglement, $dateFacture1, $dateFacture2, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_factures_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_factures_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function getListeCompsanteReglement() 
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeComposante as `code`, libelleEng as libelle	
			FROM gc_composante_reglement order by ordre, libelle';
		}
		else
		{
			$sql = 'SELECT codeComposante as `code`, libelle	
			FROM gc_composante_reglement order by ordre, libelle';
		}

		/*
		$sql = 'SELECT codeComposante as `code`, libelle	
		FROM gc_composante_reglement order by ordre, libelle';
		*/
		$liste = $this->executerRequete($sql);
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function getListeCompsanteReglementCons() 
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeComposante as `code`, libelleEng as libelle 
			FROM gc_composante_reglement 
			where (valide="1") order by ordre, libelle';
		}
		else
		{
			$sql = 'SELECT codeComposante as `code`, libelle 
			FROM gc_composante_reglement 
			where (valide="1") order by ordre, libelle';
		}
		
		$liste = $this->executerRequete($sql);
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function valider_factures_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_selection_factures_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	
	
	public function selectionner_facture_gc($idFactureTemp)
	{
		$sql = 'call sp_r_selectionner_facture_gc(?)';
		$this->executerRequete($sql, array($idFactureTemp));
    }	
	
	public function de_selectionner_facture_gc($idFactureTemp)
	{
		$sql = 'call sp_r_de_selectionner_facture_gc(?)';
		$this->executerRequete($sql, array($idFactureTemp));
    }	

	public function re_afficher_factures_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_factures_gc_non_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function re_afficher_factures_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_factures_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function valider_factures_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_de_selection_factures_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	
	
	public function selectionner_facture_gc_sel($idFactureTemp)
	{
		$sql = 'call sp_r_selectionner_facture_gc_sel(?)';
		$this->executerRequete($sql, array($idFactureTemp));
    }	
	
	public function de_selectionner_facture_gc_sel($idFactureTemp)
	{
		$sql = 'call sp_r_de_selectionner_facture_gc_sel(?)';
		$this->executerRequete($sql, array($idFactureTemp));
    }	
	
	public function requetescartegc($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_cartes_gc(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function requetescartegcexport($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_cartes_gc_export(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_cartes_gc_non_sel($codeGcAssureur)
	{
		$idGcReglement 		= $_SESSION['idGcReglement'];
		$gcNumeroDecompte 	= $_SESSION['gcNumeroDecompte'];
		$user 				= $_SESSION['login'];
		$idSaisie 			= $_SESSION['idSaisie'];

		$sql = 'call sp_r_get_cartes_gc_non_sel(?, ?, ?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $gcNumeroDecompte, $idGcReglement, $dateFacture1, $dateFacture2, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function re_afficher_cartes_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_cartes_gc_non_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function de_selectionner_carte_gc($idCarteTemp)
	{
		$sql = 'call sp_r_de_selectionner_carte_gc(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	

	public function selectionner_carte_gc($idCarteTemp)
	{
		$sql = 'call sp_r_selectionner_carte_gc(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	
	
	public function valider_cartes_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_selection_cartes_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	
	
	public function get_cartes_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_cartes_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function de_selectionner_carte_gc_sel($idCarteTemp)
	{
		$sql = 'call sp_r_de_selectionner_carte_gc_sel(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	
	
	public function selectionner_carte_gc_sel($idCarteTemp)
	{
		$sql = 'call sp_r_selectionner_carte_gc_sel(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	

	public function re_afficher_cartes_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_cartees_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function valider_cartes_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_de_selection_cartes_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	
	
	
	public function selectionner_carte_gc_sel_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_carte_gc_sel_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	
	
	public function selectionner_carte_gc_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_carte_gc_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	
	
	// facture
	public function selectionner_facture_gc_sel_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_facture_gc_sel_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	
	
	public function selectionner_facture_gc_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_facture_gc_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	

    public function enregistrermodificationgcreglement($observations)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		
		$sql = 'call sp_r_gc_modif_reglement(?, ?, ?);';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $user, $observations));
    }	
	
    public function validerdecomptepremier()
	{
		$idGcReglement 		= $_SESSION['idGcReglement'];
		$user 				= $_SESSION['login'];

		$sql = 'call sp_r_valider_reglement_gc_premier(?, ?)';
		$this->executerRequete($sql, array($idGcReglement, $user));
    }
	
    public function validerdecompte()
	{
		$idGcReglement 		= $_SESSION['idGcReglement'];
		$user 				= $_SESSION['login'];

		$sql = 'call sp_r_valider_reglement_gc(?, ?)';
		$this->executerRequete($sql, array($idGcReglement, $user));
    }
	
	public function get_factures_gc_cons()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_factures_gc_cons(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_cartes_gc_cons()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_cartes_gc_cons(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function enregistrerencaissementgc($codeModePaiement, $referencePaiement, $nomTireur, $banquePayeur, $dateComptable)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_encaisser_gc(?, ?, ?, ?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $codeModePaiement, $referencePaiement, $nomTireur, $banquePayeur, $dateComptable, $user));
    }	
	

	public function getbeneficiairecartegc($numeroBeneficiaire)
	{
		$sql = 'call sp_r_get_beneficiaire_carte_gc(?)';
		
		$resultat = $this->executerRequete($sql, array($numeroBeneficiaire));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function getcartesbeneficiairegc($numeroBeneficiaire)
	{
		$sql = 'call sp_r_get_cartes_beneficiaire_gc(?)';
		
		$resultat = $this->executerRequete($sql, array($numeroBeneficiaire));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function creercartegc($idBeneficiaire, $dateCarte)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_r_creer_carte_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idBeneficiaire, $dateCarte, $user));
    }	

    public function supprimercarte($idGcCarte)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_r_supprimer_carte_gc(?, ?)';
		
		$this->executerRequete($sql, array($idGcCarte, $user));
    }	
	

    public function modifiercarte($idGcCarte, $dateCarte, $montantCarte)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_r_modifier_carte_gc(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcCarte, $dateCarte, $montantCarte, $user));
    }	

	public function get_factures_gc_xls()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_factures_gc_sel_xls(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_cartes_gc_sel_xls()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_cartes_gc_sel_xls(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function requetesfacturegccumul($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_gc_cumul(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function requetesfacturegccumulexport($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_gc_cumul_export(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	// 29/03/2019 => factures GC vérifiées
	
	public function requetesfactureverifgc($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_verif_gc(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function requetesfactureverifgcexport($codeGcAssureur, $dateFacture1, $dateFacture2)
	{
		$sql = 'call sp_gc_requetes_factures_verif_gc_export(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $dateFacture1, $dateFacture2));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	// 25/05/2019 => frais de gestion
    public function get_idEnteteGcFraisgestion($codeGcAssureur, $codeExercice, $codeMois)
	{
		$sql = 'select fn_get_idEnteteGcFraisgestion(?, ?, ?) as idEnteteGcFraisgestion';
		
        $resultat = $this->executerRequete($sql, array($codeGcAssureur, $codeExercice, $codeMois));

		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		return $ligne['idEnteteGcFraisgestion'];		
    }	


    public function initiergcfraisgestion($codeGcAssureur)
	{
		$codeSociete 		= $_SESSION['codeSociete'];
		$user 				= $_SESSION['login'];
		$codeExercice 		= $_SESSION['codeExercice_C'];
		$codeMois 			= $_SESSION['codeMois_C'];
		
		$sql = 'call sp_r_init_gc_frais_gestion(?, ?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($codeSociete, $codeGcAssureur, $user, $codeExercice, $codeMois));
    }	

	public function getinfosdecompte($idEnteteGcFraisgestion) 
	{
		$sql = 'call sp_r_get_infos_gc_fraisgestion(?)';
		
		$resultat = $this->executerRequete($sql, array($idEnteteGcFraisgestion));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }	

    public function recapfraisgestion($idEnteteGcFraisgestion)
	{
		$sql = 'call sp_r_recap_fraisgestion(?)';
		
		$this->executerRequete($sql, array($idEnteteGcFraisgestion));
    }
	
	public function getentetefraisgestion($idEnteteGcFraisgestion) 
	{
		$sql = 'call sp_r_get_gc_entetefraisgestion(?)';
		
		$resultat = $this->executerRequete($sql, array($idEnteteGcFraisgestion));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }	

	public function getdetailfraisgestioncumul($idEnteteGcFraisgestion) 
	{
		$sql = 'call sp_r_get_detail_fraisgestion_cumul(?)';
		
		$resultat = $this->executerRequete($sql, array($idEnteteGcFraisgestion));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function getdetailfraisgestion($idEnteteGcFraisgestion) 
	{
		$sql = 'call sp_r_get_detail_fraisgestion(?)';
		
		$resultat = $this->executerRequete($sql, array($idEnteteGcFraisgestion));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

    public function rechargergcfraisgestion($idEnteteGcFraisgestion, $codeGcAssureur, $codeExercice, $codeMois)
	{
		$user = $_SESSION['login'];		
		
		$sql = 'call sp_r_recharger_fraisgestion(?, ?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idEnteteGcFraisgestion, $codeGcAssureur, $codeExercice, $codeMois, $user));
    }	
	
	public function listeentetegcfraisgestion($codeGcAssureur, $codeExercice)
	{
		$sql = 'call sp_r_lister_entete_gc_fraisgestion(?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $codeExercice));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_frais_gestion_gc_non_sel($codeGcAssureur)
	{
		$idGcReglement 		= $_SESSION['idGcReglement'];
		$gcNumeroDecompte 	= $_SESSION['gcNumeroDecompte'];
		$user 				= $_SESSION['login'];
		$idSaisie 			= $_SESSION['idSaisie'];

		// $sql = 'call sp_r_get_cartes_gc_non_sel(?, ?, ?, ?, ?, ?, ?)';
		$sql = 'call sp_r_get_frais_gestion_gc_non_sel(?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur, $gcNumeroDecompte, $idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function selectionner_frais_gestion_gc_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_frais_gestion_gc_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	

	public function re_afficher_frais_gestion_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_frais_gestion_gc_non_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function de_selectionner_frais_gestion_gc($idCarteTemp)
	{
		$sql = 'call sp_r_de_selectionner_frais_gestion_gc(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	

	public function selectionner_frais_gestion_gc($idCarteTemp)
	{
		$sql = 'call sp_r_selectionner_frais_gestion_gc(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	
	
	public function valider_frais_gestion_gc_non_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_selection_frais_gestion_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	

	public function get_frais_gestion_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_frais_gestion_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function re_afficher_frais_gestion_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$user 			= $_SESSION['login'];
		$idSaisie 		= $_SESSION['idSaisie'];

		$sql = 'call sp_r_re_afficher_frais_gestion_gc_sel(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function selectionner_frais_gestion_gc_sel_tout($choix)
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_selectionner_frais_gestion_gc_sel_tout(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user, $choix));
    }	

	public function de_selectionner_frais_gestion_gc_sel($idCarteTemp)
	{
		$sql = 'call sp_r_de_selectionner_frais_gestion_gc_sel(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	
	
	public function selectionner_frais_gestion_gc_sel($idCarteTemp)
	{
		$sql = 'call sp_r_selectionner_frais_gestion_gc_sel(?)';
		$this->executerRequete($sql, array($idCarteTemp));
    }	

	public function valider_frais_gestion_gc_sel()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_valider_de_selection_frais_gestion_gc(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
    }	
	
	public function get_frais_gestion_gc_sel_xls()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];
		$idSaisie 		= $_SESSION['idSaisie'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_r_get_frais_gestion_gc_sel_xls(?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement, $idSaisie, $user));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_frais_gestion_gc_souscripteur()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];

		$sql = 'call sp_r_get_frais_gestion_gc_souscripteur(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_frais_gestion_gc_souscripteur_xls()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];

		$sql = 'call sp_r_get_frais_gestion_gc_souscripteur_xls(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function get_frais_gestion_gc_detail()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];

		$sql = 'call sp_r_get_frais_gestion_gc_detail(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	

	public function get_frais_gestion_gc_detail_xls()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];

		$sql = 'call sp_r_get_frais_gestion_gc_detail_xls(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
	public function get_synthese_gc()
	{
		$idGcReglement 	= $_SESSION['idGcReglement'];

		$sql = 'call sp_r_get_frais_gestion_gc_synthese(?)';
		
		$resultat = $this->executerRequete($sql, array($idGcReglement));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }	
	
    public function getusersgc($codeGcAssureur)	
	{
		$sql = 'call sp_get_users_gc(?)';
		
		$resultat = $this->executerRequete($sql, array($codeGcAssureur));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }

    public function reinitpaswd($codeUtilisateur)
	{
		$sql = 'call sp_r_reinit_utilisateur_gc(?)';
		
		$this->executerRequete($sql, array($codeUtilisateur));
    }

    public function desactiver($codeUtilisateur)
	{
		$sql = 'call sp_r_desactiver_utilisateur_gc(?)';
		
		$this->executerRequete($sql, array($codeUtilisateur));
    }
	
    public function activer($codeUtilisateur)
	{
		$sql = 'call sp_r_activer_utilisateur_gc(?)';
		
		$this->executerRequete($sql, array($codeUtilisateur));
    }

    public function gereraccesactes($codeUtilisateur, $actVisible)
	{
		$sql = 'call sp_r_gerer_accesactes_gc(?, ?)';
		
		$this->executerRequete($sql, array($codeUtilisateur, $actVisible));
    }

    public function getunusergc($idUtilisateur)
	{
		$sql = 'call sp_un_user_gc(?)';
		
		$resultat = $this->executerRequete($sql, array($idUtilisateur));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function enregistrermodifusergc($idUtilisateur, $nom, $prenoms, $actif, $actVisible, $codeLangue, $AffectionVisible, $telephone, $email)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_modifier_user_gc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idUtilisateur, $nom, $prenoms, $actif, $actVisible, $codeLangue, $AffectionVisible, $user, $telephone, $email));
    }
	// $_SESSION['codeGcAssureur_C']
	
    public function existelogin($codeUtilisateur) {
        $sql = 'select id FROM gc_utilisateur WHERE (codeUtilisateur=?)';
        $resultat = $this->executerRequete($sql, array($codeUtilisateur));
		return ($resultat->rowCount() > 0);
    }
	
    public function creerusergc($codeGcAssureur, $codeUtilisateur, $nom, $prenoms, $telephone, $email, $motPass, $codeLangue, 
	$actVisible, $AffectionVisible, $codeModeGenerationPass, $codeModeEnvoiPass)
	{
		$codeSociete 		= $_SESSION['codeSociete'];
		$user 				= $_SESSION['login'];

		$passAutoGen 		= "";
		
		switch ($codeModeGenerationPass) 
		{
			case "0":
				$hash = password_hash($motPass, PASSWORD_DEFAULT);
				break;
			case "1":
				$passAutoGen = uniqid();
				$hash = password_hash($passAutoGen, PASSWORD_DEFAULT);
				$motPass = $passAutoGen;
				break;
			case 2:
				$hash = "";
				break;
			default:
			   $hash = password_hash($motPass, PASSWORD_DEFAULT);
		}

		$sql = 'call sp_creer_user_gc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($codeSociete, $codeGcAssureur, $codeUtilisateur, $nom, $prenoms, $telephone, 
		$email, $hash, $codeLangue, $actVisible, $AffectionVisible, $user, $codeModeGenerationPass, $passAutoGen, $codeModeEnvoiPass, $motPass));
    }

    public function gereraccesaffection($codeUtilisateur, $AffectionVisible)
	{
		$sql = 'call sp_r_gerer_accesaffection_gc(?, ?)';
		
		$this->executerRequete($sql, array($codeUtilisateur, $AffectionVisible));
    }
	
	/*
		TYPE GARANT 06/08/2020
	*/
	public function getGcTypeGarant() 
	{
		$sql = "SELECT * FROM gc_typegarant 
				order by libelle";		
		$resultat = $this->executerRequete($sql);
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getGcTypeGarantId($idGc)
    {
		$sql = "SELECT * FROM gc_typegarant WHERE id=?";
        $resultat = $this->executerRequete($sql, array($idGc));
		return $resultat->fetch(PDO::FETCH_ASSOC);
      
    }
	
	public function getListeTypeGarant() 
	{
		$sql = 'SELECT id as `code`, libelle FROM gc_typegarant 
				WHERE codeSociete = ? order by libelle';
		$resultat = $this->executerRequete($sql,array($_SESSION['codeSociete']));
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function ajouterTypeGarant($codeTypeGarant, $libelle)
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'INSERT INTO gc_typegarant(codeSociete, codeTypeGarant, libelle) VALUES (?, ?, ?);';
		
        $this->executerRequete($sql, array($codeSociete, $codeTypeGarant, $libelle));
    }
	
	public function modifierTypeGarant($codeTypeGarant, $libelle, $id)
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'UPDATE gc_typegarant SET codeTypeGarant=?, libelle=?
		WHERE (id = ? AND codeSociete = ?);';
		
        $this->executerRequete($sql, array($codeTypeGarant, $libelle, $id, $codeSociete));
    }
	
	public function supprimerTypeGarant($id)
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'DELETE FROM gc_typegarant WHERE (id = ? AND codeSociete = ?);';
		
        $this->executerRequete($sql, array($id, $codeSociete));
    }
	
	public function existeTypeGarant($code) {
        $sql = 'select id FROM prestataire WHERE (codePrestataire=?)';
        $resultat = $this->executerRequete($sql, array($code));
		return ($resultat->rowCount() > 0);
    }
	
	// 10/08/2020
	public function getgarantsparametrage($typeGarant, $libelle, $codeGcAssureur)
	{
		$typeGarant 	= $typeGarant;
		$libelle 		= contruireParamLike($libelle);
		$codeGcAssureur = contruireParamLike($codeGcAssureur);
		
		$sql = 'call sp_liste_garants_parametrage(?, ?, ?)';
	
		
		$resultat = $this->executerRequete($sql, array($typeGarant, $libelle, $codeGcAssureur));
		
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	// 11/08/2020
	public function getGcAssureursInactifs() 
	{
		$sql = "SELECT a.*, b.libelle as typegc FROM gc_assureur a
				JOIN gc_typegarant b ON (a.typeGarant = b.id)
				WHERE a.actif = 0 order by libelle";		
		$resultat = $this->executerRequete($sql);
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function desactivergcassureur($id){
		
		$sql = "UPDATE gc_assureur SET actif=0
		        WHERE id=?";
		
		$this->executerRequete($sql, array($id));
	}
	
	public function reactivergcassureur($id){
		
		$sql = "UPDATE gc_assureur SET actif=1
		        WHERE id=?";
		
		$this->executerRequete($sql, array($id));
	}
	
	public function getContexteAjaxGarant($codeGcAssureur) 
	{
		$sql = 'SELECT A.id as idGarant FROM gc_assureur A WHERE (A.codeGcAssureur=?)';
	    $resultat = $this->executerRequete($sql, array($codeGcAssureur));
		$context = $resultat->fetch(PDO::FETCH_ASSOC);
		$_SESSION['codeGcAssureur_C'] = $context['idGarant'];
		return $context['idGarant'];
    }
	
	public function getListeGarantDansProduit() 
	{
		$sql = 'SELECT codeGcAssureur AS `code`, CONCAT(libelle, " [",codeGcAssureur,"]") AS libelle  FROM gc_assureur 
				WHERE codeSociete= ? AND actif = 1 AND codeGcAssureur IN (
				SELECT DISTINCT codeGcAssureur FROM produit WHERE codeSociete= ?)
				ORDER BY libelle';
		$resultat = $this->executerRequete($sql,array($_SESSION['codeSociete'], $_SESSION['codeSociete']));
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getgarantssansterritoire($codeTerritoire)
	{
		$codeSociete 	= $_SESSION['codeSociete'];

		
		$sql = 'CALL sp_get_garants_sans_accessoires_territoire(?, ?);';
		
				
		$resultat = $this->executerRequete($sql, array($codeSociete, $codeTerritoire));		
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
    public function getgarantsavecterritoire($codeTerritoire)
	{
		$codeSociete 	= $_SESSION['codeSociete'];

		$sql = 'CALL sp_get_garants_avec_accessoires_territoire(?, ?);';
		
		$resultat = $this->executerRequete($sql, array($codeSociete, $codeTerritoire));		
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function ajouterungarant($codeTerritoire, $codeGcAssureur)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_ajouterungarant_accessoire_territoire(?, ?, ?, ?)';
		
		$this->executerRequete($sql, array($codeSociete, $codeTerritoire, $codeGcAssureur, $user));
    }	
	
	public function ajoutertousgarantsterritoire($codeTerritoire)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_ajouter_tous_garants_accessoire_territoire(?, ?, ?)';
		
		$this->executerRequete($sql, array($codeSociete, $codeTerritoire, $user));
    }	
	
	public function retirertousgarantsterritoire($codeTerritoire)
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];

		$sql = 'call sp_retirer_tous_garants_accessoires_territoire(?, ?, ?)';
		
		$this->executerRequete($sql, array($codeSociete, $codeTerritoire, $user));
    }
	
	public function retirerungarant($idGarant)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_retirer_un_garant_accessoires_territoire(?, ?)';
		
		$this->executerRequete($sql, array($idGarant, $user));
    }
	
	public function majmontantaccessoire($idGarant, $montant)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_majmontant_accessoires_territoire(?, ?, ?)';
		
		$this->executerRequete($sql, array($idGarant, $user, $montant));
	}
	
	public function getListeAssiettesFactureGarant($codeSourceFondGarant)
	{
		if (est_anglophone())
		{
			if($codeSourceFondGarant=="FOR" || $codeSourceFondGarant=="BUD"){
				$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelleEng as libelle 
				FROM assiettefacturationgarant WHERE actif="1" AND codeAssietteFacturationGarant!="" ORDER BY ordre;';
			}else{
				$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelleEng as libelle 
				FROM assiettefacturationgarant WHERE actif="1" AND codeAssietteFacturationGarant="";';
			}
		}
		else
		{
			if($codeSourceFondGarant=="FOR" || $codeSourceFondGarant=="BUD"){
				$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelle 
				FROM assiettefacturationgarant WHERE actif="1" AND codeAssietteFacturationGarant!="" ORDER BY ordre;';
			}else{
				$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelle 
				FROM assiettefacturationgarant WHERE actif="1" AND codeAssietteFacturationGarant="";';
			}
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function getListeSourceFondGarant()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelleEng as libelle 
			FROM sourcefondgarant ORDER BY libelle';
		}
		else
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelle 
			FROM sourcefondgarant ORDER BY libelle';
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);		
	}
	
	public function getListeSourceFondGarantAppelFond()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelleEng as libelle 
			FROM sourcefondgarant WHERE appelFond="1" ORDER BY ordre, libelle;';
		}
		else
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelle 
			FROM sourcefondgarant WHERE appelFond="1" ORDER BY ordre, libelle;';
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);		
	}
	
	public function getListeSourceFondGarantHonoraire()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelleEng as libelle 
			FROM sourcefondgarant WHERE honoraire = "1"
			ORDER BY ordre, libelle;';
		}
		else
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelle 
			FROM sourcefondgarant WHERE honoraire = "1"
			ORDER BY ordre, libelle;';
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);		
	}
	
	public function getListeAssiettes()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelleEng as libelle 
					FROM assiettefacturationgarant WHERE actif="1";';
		}
		else
		{
			$sql = 'SELECT codeAssietteFacturationGarant as `code`, libelle 
					FROM assiettefacturationgarant WHERE actif="1";';
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function getListeSourceFondGarantCaution()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelleEng as libelle 
			FROM sourcefondgarant WHERE caution = "1"
			ORDER BY ordre, libelle;';
		}
		else
		{
			$sql = 'SELECT codeSourceFondGarant as `code`, libelle 
			FROM sourcefondgarant WHERE caution = "1"
			ORDER BY ordre, libelle;';
		}
		
		$liste = $this->executerRequete($sql);
		
        return $liste->fetchAll(PDO::FETCH_ASSOC);		
	}
	
	public function getParametreFacturationProduit($codeGcAssureur, $codeProduit)
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'call sp_gc_param_facturation_produit(?, ?, ?)';
		
		
		$liste = $this->executerRequete($sql, array($codeSociete, $codeGcAssureur, $codeProduit));
		
        return $liste->fetch(PDO::FETCH_ASSOC);		
	}
	
	public function insererParametreProduit($codeGcAssureur, $codeProduit, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxCommissionGerant,
	$montantBudget, $montantBudgetEnfantSupl, $montantBudgetConjointSupl, $forfaitHonoraire, $nbAdherent, $nbBeneficiaire)
	{
		
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'call sp_gc_inserer_param_facturation_produit(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
		
		$this->executerRequete($sql, array($codeSociete, $codeGcAssureur, $codeProduit, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxCommissionGerant,
		$montantBudget, $montantBudgetEnfantSupl, $montantBudgetConjointSupl, $forfaitHonoraire, $nbAdherent, $nbBeneficiaire));
		
		
	}
	
	public function enregistrerParametreProduit($codeGcAssureur, $codeProduit, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxCommissionGerant,
	$montantBudget, $montantBudgetEnfantSupl, $montantBudgetConjointSupl, $forfaitHonoraire, $nbAdherent, $nbBeneficiaire)
	{
		
		$codeSociete	= $_SESSION['codeSociete'];
		$user			= $_SESSION['login'];
		
		
		$sql = 'call sp_gc_enregistrer_param_facturation_produit(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
		
		
		
		$this->executerRequete($sql, array($codeSociete, $codeGcAssureur, $codeProduit, $gcTauxFraisReel, $gcTauxRedressement, $gcTauxCommissionGerant,
		$montantBudget, $montantBudgetEnfantSupl, $montantBudgetConjointSupl, $forfaitHonoraire, $nbAdherent, $nbBeneficiaire, $user));
		
		
	}
	
	public function getParametreFacturationGarant($codeGcAssureur)
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'call sp_gc_get_param_facturation(?, ?);';
		
		$liste = $this->executerRequete($sql, array($codeSociete, $codeGcAssureur));
		
        return $liste->fetch(PDO::FETCH_ASSOC);
	}
	
	public function getParametresFacturationGarantProduit()
	{
		$codeSociete	= $_SESSION['codeSociete'];
		
		$sql = 'SELECT fn_get_paramFactGcProduit(?) AS parametresFacturationGarantProduit';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));

		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		return $ligne['parametresFacturationGarantProduit'];		
    }
	
	public function changerLogo($codeGcAssureur, $nomImage)
    {
        $sql = 'update gc_assureur set logo_garant=? WHERE (codeGcAssureur=?)';
        $this->executerRequete($sql, array($nomImage, $codeGcAssureur));
    }
	
}