2076 lines
66 KiB
PHP
Executable File
2076 lines
66 KiB
PHP
Executable File
<?php
|
|
require_once 'Framework/Modele.php';
|
|
class Police extends Modele {
|
|
|
|
public function getPolices($numeroClient) {
|
|
$sql = 'call sp_getPolicesClientNo(?);';
|
|
$resultat = $this->executerRequete($sql, array($numeroClient));
|
|
return $resultat ->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function getPolicesId($id) {
|
|
$sql = 'call sp_getPolicesClient(?);';
|
|
$resultat = $this->executerRequete($sql, array($id));
|
|
return $resultat ->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPolice($numeroPolice)
|
|
{
|
|
$sql = 'call sp_getPoliceNo(?, ?);';
|
|
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $numeroPolice));
|
|
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceId($id)
|
|
{
|
|
$sql = 'call sp_getPoliceId(?, ?);';
|
|
|
|
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceIdSimple($id) {
|
|
$sql = 'SELECT A.* FROM police A WHERE (A.codeSociete=?) AND (A.id=?)';
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function getTextecp($id) {
|
|
$sql = 'select texteCp from police where (id=?)';
|
|
$textecp = $this->executerRequete($sql, array($id));
|
|
return $textecp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function enregistrertexteCp($texteCp, $idPolice) {
|
|
$sql = 'update police set texteCp=? where (id=?)';
|
|
$this->executerRequete($sql, array($texteCp, $idPolice));
|
|
}
|
|
|
|
public function getNombrePolices($numeroClient)
|
|
{
|
|
$sql = 'select count(*) as nbPolices FROM polices A WHERE (A.codeSociete=?) AND (A.numeroClient=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $numeroClient));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['nbPolices'];
|
|
}
|
|
|
|
public function getNombrePolicesId($id)
|
|
{
|
|
$sql = 'select count(*) as nbPolices FROM police A WHERE (A.codeSociete=?) AND (A.id=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['nbPolices'];
|
|
}
|
|
|
|
public function getContextePoliceId($idPolice)
|
|
{
|
|
$sql = 'CALL sp_get_contexte_police_id(?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $idPolice));
|
|
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
|
|
$_SESSION['idClient_C'] = $context['idClient'];
|
|
$_SESSION['numeroClient_C'] = $context['numeroClient'];
|
|
$_SESSION['idPolice_C'] = $context['idPolice'];
|
|
|
|
$_SESSION['codeGcAssureur_C'] = $context['codeGcAssureur'];
|
|
$_SESSION['nomGcAssureur_C'] = $context['garant'];
|
|
|
|
$_SESSION['numeroPolice_C'] = $context['numeroPolice'];
|
|
$_SESSION['codeTypeContrat'] = $context['codeTypeContrat'];
|
|
$_SESSION['codeEtape_C'] = $context['codeEtape'];
|
|
$_SESSION['tauxTaxe_C'] = $context['tauxTaxe'];
|
|
$_SESSION['tauxCommissionGerant_C'] = $context['tauxCommissionGerant'];
|
|
$_SESSION['tauxCommissionGcAssureur_C'] = $context['tauxCommissionGcAssureur'];
|
|
$_SESSION['tauxCommission_C'] = $context['tauxCommission'];
|
|
$_SESSION['tauxCommissionAutre_C'] = $context['tauxCommissionAutre'];
|
|
$_SESSION['deduireComm_C'] = $context['deduireComm'];
|
|
$_SESSION['codeTypeAvenant_C'] = $context['codeTypeAvenant'];
|
|
$_SESSION['codeEtatPolice_C'] = $context['codeEtatPolice'];
|
|
|
|
$_SESSION['dateEffet_C'] = $context['dateEffet'];
|
|
|
|
$_SESSION['dateFin_C'] = $context['dateFin'];
|
|
$_SESSION['dateEcheance_C'] = $context['dateEcheance'];
|
|
|
|
$_SESSION['DateRenouvellement_C'] = $context['DateRenouvellement'];
|
|
|
|
$_SESSION['dateAvenant_C'] = $context['dateAvenant'];
|
|
|
|
$_SESSION['idPoliceAjax'] = $_SESSION['idPolice_C'];
|
|
|
|
$_SESSION['primeFamille_C'] = $context['primeFamille'];
|
|
$_SESSION['primeBeneficiaire_C'] = $context['primeBeneficiaire'];
|
|
|
|
$_SESSION['rachatTm_C'] = $context['rachatTm'];
|
|
|
|
// $_SESSION['garantieArchive_C'] = $context['garantieArchive'];
|
|
$_SESSION['collegeArchive_C'] = $context['collegeArchive'];
|
|
$_SESSION['budgetDefini_C'] = $context['budgetDefini'];
|
|
|
|
|
|
//var_dump($_SESSION['budgetDefini_C']);die();
|
|
//var_dump($_SESSION['codeTypeRemboursement_C']);die();
|
|
|
|
//var_dump($_SESSION['codeTerritoire_C']);die();
|
|
|
|
$_SESSION['idAdherent_C'] = "";
|
|
$_SESSION['numeroAdherent_C'] = "";
|
|
$_SESSION['nomAdherent_C'] = "";
|
|
|
|
$_SESSION['idBeneficiaire_C'] = "";
|
|
$_SESSION['numeroBeneficiaire_C'] = "";
|
|
$_SESSION['nomBeneficiaire_C'] = "";
|
|
$_SESSION['lienPhoto_C'] = "";
|
|
|
|
$_SESSION['beneficiaire_C'] = "";
|
|
$_SESSION['adherent_C'] = "";
|
|
$_SESSION['enVigueur_C'] = "";
|
|
|
|
$_SESSION['decede_C'] = "0";
|
|
$_SESSION['dateDeces_C'] = "";
|
|
$_SESSION['fraisFuneraireDemande_C'] = "0";
|
|
$_SESSION['fraisFunerairePaye_C'] = "0";
|
|
|
|
$_SESSION['ndAdh_C'] = $context['ndAdh'];
|
|
$_SESSION['ndDep_C'] = $context['ndDep'];
|
|
$_SESSION['ndActif_C'] = $context['ndActif'];
|
|
|
|
$resultat->closeCursor();
|
|
|
|
$this->majContextePolice($idPolice);
|
|
}
|
|
|
|
public function getDenierNumeroPolice($annee)
|
|
{
|
|
$sql = 'select dernierNumeroPolice from numerotationpolice WHERE (codeSociete=? AND annee=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$annee));
|
|
|
|
if ($resultat->rowCount() == 0)
|
|
{
|
|
$sql = 'insert into numerotationpolice (codeSociete, annee, dernierNumeroPolice) values (?,?,?)';
|
|
$this->executerRequete($sql, array($_SESSION['codeSociete'],$annee,"0"));
|
|
return "1";
|
|
} else
|
|
{
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['dernierNumeroPolice']+1;
|
|
}
|
|
}
|
|
|
|
public function setDenierNumeroPolice($annee,$dernierNumeroPolice)
|
|
{
|
|
$sql = 'update numerotationpolice set dernierNumeroPolice=? WHERE (codeSociete=? AND annee=?)';
|
|
$this->executerRequete($sql, array($dernierNumeroPolice, $_SESSION['codeSociete'], $annee));
|
|
}
|
|
|
|
public function ajouter($codeSociete, $idClient, $numeroClient, $numeroPolice, $numeroPoliceSociete, $codeTypeContrat,
|
|
$dateEffet, $dateFin, $dateEcheance, $numeroAvenant, $codeTypeAvenant, $dateAvenant, $codeApporteur,
|
|
$codeGestionnaire, $dateCre, $userCre, $codeEtatPolice, $codeFractionnement, $codePeriodicite, $tauxCommission,
|
|
$primeFamille, $primeBeneficiaire, $rachatTm, $deduireComm, $taciteReconduction, $tauxRachatTm, $codeCommercial,
|
|
$tauxCommissionAutre, $Checkup,$speciale, $limiteConsommation, $tauxRistourne, $remplacementAdherent, $delaiCarencePolice,
|
|
$codeGcAssureur,$tauxCommissionGerant,$tauxCommissionGcAssureur,$numeroPoliceGarant,$budgetDefini,$rembourserA,$libellePolice,
|
|
$avecReferencement, $tauxAvecReferencement, $tauxSansReferencement, $codeTarifActe, $appliquerTarifGarant, $seuilRdPolice,
|
|
$plafondPolice, $montantBudget, $codeModeProrata, $codeTypeRemboursement, $codeArembourser, $forfaitHonoraire, $forfaitAppelFond, $forfaitAppelCotisation)
|
|
{
|
|
|
|
$gestionConfiee = (!empty($codeGcAssureur) && $codeGcAssureur !="AUC" ) ? "1" : "0";
|
|
|
|
$sql = 'CALL sp_creer_police(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idClient, $numeroClient, $numeroPolice, $numeroPoliceSociete, $codeTypeContrat,
|
|
$dateEffet, $dateFin, $dateEcheance, $numeroAvenant, $codeTypeAvenant, $dateAvenant, $codeApporteur,
|
|
$codeGestionnaire, $dateCre, $userCre, $codeEtatPolice, $codeFractionnement, $codePeriodicite, $tauxCommission,
|
|
$primeFamille, $primeBeneficiaire, $rachatTm, $deduireComm, $taciteReconduction, $tauxRachatTm, $codeCommercial,
|
|
$tauxCommissionAutre, $Checkup,$speciale, $limiteConsommation, $tauxRistourne, $remplacementAdherent, $delaiCarencePolice,
|
|
$codeGcAssureur,$tauxCommissionGerant,$tauxCommissionGcAssureur,$numeroPoliceGarant,$budgetDefini,$rembourserA,$libellePolice,
|
|
$avecReferencement, $tauxAvecReferencement, $tauxSansReferencement, $codeTarifActe, $appliquerTarifGarant, $seuilRdPolice,
|
|
$plafondPolice, $montantBudget, $codeModeProrata, $gestionConfiee, $dateEffet, $codeTypeRemboursement, $codeArembourser, $forfaitHonoraire, $forfaitAppelFond, $forfaitAppelCotisation))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
|
|
$dernierId = $resultat['dernierId'];
|
|
|
|
|
|
$sql = 'call sp_creer_contrat(?, ?)';
|
|
$this->executerRequete($sql, array($dernierId, $userCre));
|
|
|
|
$motifAvenant = 'AFFAIRE NOUVELLE';
|
|
if (est_anglophone())
|
|
{
|
|
$motifAvenant = 'NEW BUSYNESS';
|
|
}
|
|
|
|
$sql = 'insert into avenant (codeSociete, idPolice, numeroPolice, codeTypeAvenant, numeroAvenant, dateEffet, dateCre, user,
|
|
dateSysteme, idEntetecontrat, motifAvenant)
|
|
select codeSociete, id as idPolice, numeroPolice, codeTypeAvenant, numeroAvenant, dateEffet, dateCre, userCre as `user`,
|
|
dateSysteme, fn_id_entetecontrat_police(A.id, A.dateEffet) AS idEntetecontrat, ?
|
|
from police A where (id=?)';
|
|
|
|
$this->executerRequete($sql, array($motifAvenant, $dernierId));
|
|
|
|
return $dernierId;
|
|
|
|
}
|
|
|
|
public function modifier($id, $tauxCommission, $tauxCommissionGerant, $codeApporteur, $codeCommercial, $tauxCommissionAutre, $speciale,
|
|
$limiteConsommation, $tauxRistourne,$remplacementAdherent, $delaiCarencePolice,$codeGcAssureur, $tauxCommissionGcAssureur, $rembourserA,
|
|
$numeroPoliceGarant, $budgetDefini,$rachattm,$libellePolice, $avecReferencement, $Checkup ,$tauxAvecReferencement, $tauxSansReferencement,
|
|
$codeTarifActe, $appliquerTarifGarant, $seuilRdPolice, $plafondPolice, $montantBudget, $codeModeProrata, $codeTypeRemboursement, $codeArembourser,
|
|
$forfaitHonoraire, $forfaitAppelFond, $forfaitAppelCotisation, $encaissementExige)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_modifier_police(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($id, $tauxCommission, $tauxCommissionGerant, $codeApporteur, $codeCommercial, $tauxCommissionAutre,
|
|
$speciale, $limiteConsommation, $tauxRistourne, $remplacementAdherent, $user, $delaiCarencePolice, $codeGcAssureur,
|
|
$tauxCommissionGcAssureur, $rembourserA, $numeroPoliceGarant, $budgetDefini, $rachattm,$libellePolice, $avecReferencement, $Checkup ,
|
|
$tauxAvecReferencement, $tauxSansReferencement, $codeTarifActe, $appliquerTarifGarant, $seuilRdPolice, $plafondPolice, $montantBudget,
|
|
$codeModeProrata, $codeTypeRemboursement, $codeArembourser, $forfaitHonoraire, $forfaitAppelFond, $forfaitAppelCotisation, $encaissementExige));
|
|
}
|
|
|
|
public function initresiliation($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_init_resiliation(?, ?, ?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function initretrait($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_retrait(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function initrerenouvellement($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$dateEcheanceRen = $_SESSION['dateEcheanceRen_C'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_init_renouvellement(?, ?, ?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $dateAvenant, $user, $dateEcheanceRen));
|
|
}
|
|
|
|
public function initLimiteAdditionnelle($idPolice, $dateAvenant, $motifAvenant)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_init_limite_additionnelle(?, ?, ?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function getAvenantTemp() {
|
|
$user = $_SESSION['login'];
|
|
$sql = 'SELECT * FROM avenant_temp WHERE (`user`=?) order by id desc';
|
|
$resultat = $this->executerRequete($sql, array($user));
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getEmissionTemp() {
|
|
$user = $_SESSION['login'];
|
|
$sql = 'SELECT * FROM emission_temp WHERE (`user`=?)';
|
|
$resultat = $this->executerRequete($sql, array($user));
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function creeravenant($idPolice, $codeTypeAvenant, $motifAvenant, $dateAvenant)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_creer_avenant(?, ?, ?, ?, ?)';
|
|
|
|
if($codeTypeAvenant=="QTM"){
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeTypeAvenant, $motifAvenant, $dateAvenant, $user))->fetch(PDO::FETCH_ASSOC);
|
|
$_SESSION['idAvenant'] = $resultat['idAvenant'];
|
|
|
|
}else{
|
|
$this->executerRequete($sql, array($idPolice, $codeTypeAvenant, $motifAvenant, $dateAvenant, $user));
|
|
}
|
|
}
|
|
|
|
public function enregistrerresiliation()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_resiliation(?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function enregistrerretrait()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_retrait(?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function enregistrerrenouvellement()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_renouvellement(?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function getIdPolice($numeroPolice) {
|
|
$sql = 'SELECT id FROM police WHERE (codeSociete=?) AND (numeroPolice=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $numeroPolice));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['id'];
|
|
}
|
|
|
|
public function existeligne($numeroPolice) {
|
|
$sql = 'select id FROM police WHERE (codeSociete=?) and (numeroPolice=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice));
|
|
return ($resultat->rowCount() > 0);
|
|
}
|
|
|
|
public function formaterNumeroPolice($annee,$numeroPolice) {
|
|
|
|
$sql = 'select fn_formater_numeropolice(?, ?, ?) as numeroPolice';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $annee,$numeroPolice));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $ligne['numeroPolice'];
|
|
}
|
|
|
|
public function supprimer($idPolice)
|
|
{
|
|
$sql = 'select id from college where (idPolice=?) limit 1';
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
if($resultat->rowCount() == 0)
|
|
{
|
|
$sql = 'DELETE FROM police WHERE (id=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
$sql = 'DELETE FROM contrat WHERE (idPolice=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
$sql = 'DELETE FROM entetecontrat WHERE (idPolice=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
$sql = 'DELETE FROM avenant WHERE (idPolice=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
$sql = 'DELETE FROM garantiecollege WHERE (idPolice=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
$sql = 'DELETE FROM prestationcollege WHERE (idPolice=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function getExisteQuittance($idPolice) {
|
|
$sql = 'select id from emission where (idPolice=?) limit 1';
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
if($resultat->rowCount() == 0)
|
|
{
|
|
return "0";
|
|
}
|
|
else
|
|
{
|
|
return "Q";
|
|
}
|
|
}
|
|
|
|
public function archivergarantie($id) {
|
|
$sql = 'UPDATE police SET garantieArchive="1" where (id=?)';
|
|
$this->executerRequete($sql, array($id));
|
|
}
|
|
|
|
public function archivercollege($id) {
|
|
$sql = 'UPDATE police SET collegeArchive="1" where (id=?)';
|
|
$this->executerRequete($sql, array($id));
|
|
}
|
|
|
|
public function getNomClient($idClient, $codeTypeContrat){
|
|
if($codeTypeContrat=="G"){
|
|
$sql = "SELECT nom AS nomClient FROM clients WHERE (id=?)";
|
|
}else{
|
|
$sql = "SELECT CONCAT(nom,' ',prenoms) AS nomClient FROM clients WHERE (id=?)";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($idClient))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['nomClient'];
|
|
}
|
|
|
|
public function getContexteAjaxPolice($id)
|
|
{
|
|
$sql = 'SELECT A.id as idPolice, idClient, codeTypeContrat, budgetDefini, codeGcAssureur FROM police A WHERE (A.id=?)';
|
|
|
|
$resultat = $this->executerRequete($sql, array($id));
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['idPoliceAjax'] = $context['idPolice'];
|
|
$_SESSION['idPolice_C'] = $_SESSION['idPoliceAjax'];
|
|
$_SESSION['codeGarantAjax'] = $context['codeGcAssureur'];
|
|
$_SESSION['codeGcAssureur_C'] = $_SESSION['codeGarantAjax'];
|
|
$_SESSION['nomClient_C'] = $this->getNomClient($context['idClient'], $context['codeTypeContrat']);
|
|
}
|
|
|
|
public function getEnteteCP($idPolice)
|
|
{
|
|
$sql = 'call sp_entete_cp(?);';
|
|
|
|
$entete = $this->executerRequete($sql, array($idPolice));
|
|
return $entete->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getCollegesCP($idPolice)
|
|
{
|
|
$sql = 'call sp_collesges_police(?);';
|
|
|
|
$colleges = $this->executerRequete($sql, array($idPolice));
|
|
return $colleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getGarantiesCollegesCP($idPolice)
|
|
{
|
|
$sql = 'call sp_garanties_colleges_police(?);';
|
|
|
|
$garantiescolleges = $this->executerRequete($sql, array($idPolice));
|
|
return $garantiescolleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function gettableauPrestationsCP($idPolice)
|
|
{
|
|
$sql = 'call sp_tableau_prestation_police(?);';
|
|
|
|
$colleges = $this->executerRequete($sql, array($idPolice));
|
|
return $colleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getRapportSpPolice($idPolice, $dateReference)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_rapport_sp_police(?, ?, ?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice, $dateReference, $user));
|
|
return $sppolice->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getConsommationPolice($idPolice, $dateReference)
|
|
{
|
|
$sql = 'call sp_consommation_police(?, ?);';
|
|
|
|
$consommationpolice = $this->executerRequete($sql, array($idPolice, $dateReference));
|
|
return $consommationpolice->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getExisteConsommationPolice($idPolice)
|
|
{
|
|
$dj = date("Y-m-d");
|
|
$sql = 'call sp_consommation_police(?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $dj));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
$consommationpolice = $ligne['consommation'];
|
|
|
|
if($consommationpolice>"0")
|
|
{
|
|
return "C";
|
|
}
|
|
else
|
|
{
|
|
return "0";
|
|
}
|
|
}
|
|
|
|
public function gettableauAjustementCP()
|
|
{
|
|
$sql = 'call sp_tableau_ajustement();';
|
|
|
|
$tableauajustementcps = $this->executerRequete($sql);
|
|
return $tableauajustementcps->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getClientsearchPol($numeroPolice)
|
|
{
|
|
$sql = 'select id as idPolice from police WHERE (codeSociete=?) AND (numeroPolice=?)';
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice));
|
|
|
|
if ($resultat->rowCount() == 0)
|
|
{
|
|
return "0";
|
|
} else
|
|
{
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['idPolice'];
|
|
}
|
|
}
|
|
|
|
public function getClientsearchPolId($idPolice)
|
|
{
|
|
$sql = 'select id as idPolice from police WHERE (codeSociete=?) AND (id=?)';
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$idPolice));
|
|
|
|
if ($resultat->rowCount() == 0)
|
|
{
|
|
return "0";
|
|
} else
|
|
{
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['idPolice'];
|
|
}
|
|
}
|
|
|
|
public function recapituler_maj_limite_police()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_recapituller_maj_limite_police(?)';
|
|
|
|
$this->executerRequete($sql, array($user));
|
|
}
|
|
|
|
public function enregistrerlimiteadditionnelle()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_limite_additionnelle(?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function getContexteClientId($idPolice) {
|
|
$sql = 'CALL sp_getclientpolice_id(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
|
|
|
|
$_SESSION['idClient_C'] = $context['idClient'];
|
|
$_SESSION['numeroClient_C'] = $context['numeroClient'];
|
|
$_SESSION['nomClient_C'] = $context['nomClient'];
|
|
$_SESSION['idClientAjax'] = $context['idClient'];
|
|
$_SESSION['codeLangueClient'] = $context['codeLangueClient'];
|
|
$_SESSION['codeLocalite'] = $context['codeLocalite'];
|
|
|
|
$_SESSION['nomMandataire_C'] = $context['nomMandataire'];
|
|
$_SESSION['telephonePortablemandataire_C'] = $context['telephonePortablemandataire'];
|
|
$_SESSION['codeModePaiement_C'] = $context['codeModePaiement'];
|
|
$_SESSION['telephoneMobile_C'] = $context['telephoneMobile'];
|
|
$_SESSION['rib_C'] = $context['rib'];
|
|
|
|
}
|
|
|
|
// Polices non renouvellées
|
|
|
|
public function etatpolnonren()
|
|
{
|
|
$dj = date("Y-m-d");
|
|
|
|
$sql = 'call sp_polices_non_renouvellees(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($dj));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function activergc($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_activergc(?, ?);';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function desactivergc($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_desactivergc(?, ?);';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function getRapportSpPoliceEntete($idPolice, $dateReference)
|
|
{
|
|
$sql = 'call sp_rapport_sp_police_entete(?, ?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice, $dateReference));
|
|
|
|
return $sppolice->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getRapportSpPolicePol($idPolice, $dateReference)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_rapport_sp_police_pol(?, ?, ?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice, $dateReference, $user));
|
|
|
|
return $sppolice->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getRapportSpPoliceCat($idPolice, $dateReference)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_rapport_sp_police_cat(?, ?, ?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice, $dateReference, $user));
|
|
|
|
return $sppolice->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getRapportSpPoliceCol($idPolice, $dateReference)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_rapport_sp_police_col(?, ?, ?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice, $dateReference, $user));
|
|
|
|
return $sppolice->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getsyntheseconsopolice()
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_synthese_consommation_police(?);';
|
|
|
|
$sppolice = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $sppolice->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getentetepolice($idPolice)
|
|
{
|
|
$sql = 'call sp_entete_police(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getentetecontrat($idEntetecontrat)
|
|
{
|
|
$sql = 'SELECT * FROM entetecontrat WHERE id=? limit 1;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idEntetecontrat));
|
|
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function getlisteterritoire(){
|
|
if (est_anglophone()){
|
|
$sql = "SELECT codeTerritoire as code, libelleEng as libelle
|
|
FROM etendueterritoire
|
|
WHERE (codeSociete =?)
|
|
ORDER BY libelle;";
|
|
}else{
|
|
$sql = "SELECT codeTerritoire as code, libelle
|
|
FROM etendueterritoire
|
|
WHERE (codeSociete =?)
|
|
ORDER BY libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete']));
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function getlistetyperemboursement(){
|
|
if (est_anglophone()){
|
|
$sql = "SELECT codeTypeRemboursement as code, libelleEng as libelle
|
|
FROM typeremboursement
|
|
WHERE (codeSociete =?)
|
|
ORDER BY libelle;";
|
|
}else{
|
|
$sql = "SELECT codeTypeRemboursement as code, libelle
|
|
FROM typeremboursement
|
|
WHERE (codeSociete =?)
|
|
ORDER BY libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete']));
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPolicesDuGarant($codeGcAssureur) {
|
|
$sql = 'SELECT A.id, A.codeSociete, A.codeGcAssureur, A.idClient, A.numeroClient, CONCAT(C.nom," ",C.prenoms) AS souscripteur,
|
|
numeroPolice, libellePolice, numeroPoliceSociete, numeroDevis, B.libelle AS typeAvenant, dateEffet, dateFin, dateEcheance,
|
|
numeroAvenant, dateAvenant, ADDDATE(A.dateEcheance, +1) AS DateRenouvellement, D.libelle AS etatPolice, A.codeFractionnement,
|
|
T.libelle AS typeContrat, IFNULL(A.primeFamille,"0") AS primeFamille, IFNULL(A.primeBeneficiaire,"0") AS primeBeneficiaire,
|
|
rachatTm, rachatTm, TM.libelle AS rachattm, garantieArchive, collegeArchive, A.gestionConfiee
|
|
FROM police A
|
|
LEFT JOIN clients C ON (C.id=A.idClient)
|
|
LEFT JOIN typecontrat T ON (T.codeTypeContrat=A.codeTypeContrat)
|
|
LEFT JOIN typeavenant B ON (B.codeTypeAvenant=A.codeTypeAvenant)
|
|
LEFT JOIN etatpolice D ON (D.codeEtatPolice=A.codeEtatPolice)
|
|
LEFT JOIN rachattm TM ON (TM.codeRachatTm=A.rachatTm)
|
|
WHERE (A.codeSociete=?) AND (A.codeGcAssureur=?) /*and A.codeEtatPolice = "EC"*/ order by A.id desc;';
|
|
|
|
$polices = $this->executerRequete($sql, array($_SESSION['codeSociete'], $codeGcAssureur));
|
|
return $polices->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
// public function fn_primes_non_facturees_police($idPolice)
|
|
// {
|
|
// $sql = 'select fn_primes_non_facturees_police(?) as nb_non_facturees';
|
|
// $resultat = $this->executerRequete($sql, array($idPolice));
|
|
// $ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
// return $ligne['nb_non_facturees'];
|
|
// }
|
|
|
|
public function pointventepossible()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_points_vente_possibles(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getEffectifPolice($idPolice) {
|
|
|
|
$sql = 'CALL sp_popoulation_police(?);';
|
|
|
|
$population = $this->executerRequete($sql, array($idPolice));
|
|
|
|
$effectif = $population->fetch(PDO::FETCH_ASSOC);
|
|
|
|
var_dump($effectif);
|
|
die();
|
|
|
|
$_SESSION['ndAdh_C'] = $effectif['ndAdh'];
|
|
$_SESSION['ndDep_C'] = $effectif['ndDep'];
|
|
$_SESSION['ndActif_C'] = $effectif['ndActif'];
|
|
}
|
|
|
|
public function getderniereavenantincorporation($idPolice)
|
|
{
|
|
$sql = 'call sp_get_derniere_avenant_incorporation(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['dernierIdAvenant'] = $context['dernierIdAvenant'];
|
|
$_SESSION['dernierDateAvenant'] = $context['dernierDateAvenant'];
|
|
}
|
|
|
|
public function getcollegefamiliale($idPolice)
|
|
{
|
|
$sql = 'call sp_get_college_familiale(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['dernierIdCollege'] = $context['dernierIdCollege'];
|
|
|
|
return $context;
|
|
}
|
|
|
|
public function initchangementcollege($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_changement_college_avenant(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function enregistrerchangementcollege()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_changementcollege(?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
//0067001101
|
|
|
|
public function getListeTarif() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'select P.`id` as `code`,
|
|
(case when (P.libellePolice = "" or P.libellePolice= C.souscripteur)
|
|
then concat(substring(C.souscripteur,1,50), " (",P.codeTarifActe,")")
|
|
else concat(substring(concat(C.souscripteur," / ", P.libellePolice),1,50), " (",P.codeTarifActe,")") end) AS libelle
|
|
from police P
|
|
join ( select `id`,
|
|
(case when (`prenoms` = "") then `nom` else concat(`nom`," ",`prenoms`) end) as souscripteur
|
|
from `clients`) C on (C.id = P.idClient)
|
|
where codeSociete = ? order by C.souscripteur';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getListePoliceTarif() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'select P.`id` as `code`,
|
|
(case when (P.libellePolice = "" or P.libellePolice= C.souscripteur)
|
|
then substring(C.souscripteur,1,50) else substring(concat(C.souscripteur," / ", P.libellePolice),1,50) end) AS libelle
|
|
from police P
|
|
join ( select `id`,
|
|
(case when (`prenoms` = "") then `nom` else concat(`nom`," ",`prenoms`) end) as souscripteur
|
|
from `clients`) C on (C.id = P.idClient)
|
|
where codeSociete = ? and P.codeTarifActe NOT IN ("","TFA0019","TFA0020")
|
|
order by C.souscripteur';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceComposanteTarif($codeTarifActe){
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'select P.`id` as `code`,
|
|
(case when (P.libellePolice = "" or P.libellePolice= C.souscripteur)
|
|
then C.souscripteur else concat(C.souscripteur," / ", P.libellePolice) end) AS libelle
|
|
from police P
|
|
join ( select `id`,
|
|
(case when (`prenoms` = "") then `nom` else concat(`nom`," ",`prenoms`) end) as souscripteur
|
|
from `clients`) C on (C.id = P.idClient)
|
|
where codeSociete = ? and codeTarifActe = ?
|
|
order by C.souscripteur';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'], $codeTarifActe));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
// 19-07-2021
|
|
public function getPolicePrestataireComposanteTarif($codeTarifActe) {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'SELECT P.`idPolice` as `code`,
|
|
(CASE WHEN (A.libellePolice = "" OR A.libellePolice= C.souscripteur)
|
|
THEN C.souscripteur ELSE CONCAT(C.souscripteur," / ", A.libellePolice) END) AS libelle
|
|
FROM policeprestataire P
|
|
JOIN police A ON (A.id = P.idPolice)
|
|
JOIN (SELECT `id`,
|
|
(CASE WHEN (`prenoms` = "") THEN `nom` ELSE CONCAT(`nom`," ",`prenoms`) END) AS souscripteur
|
|
FROM `clients`) C ON (C.id = A.idClient)
|
|
WHERE P.codeSociete = ? AND P.codeTarifActe = ?
|
|
ORDER BY C.souscripteur';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'], $codeTarifActe));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getListePoliceSansPrestataire($codePrestataire){
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'SELECT P.`id` as `code`,
|
|
(CASE WHEN (P.libellePolice = "" or P.libellePolice= C.souscripteur)
|
|
THEN C.souscripteur ELSE CONCAT(C.souscripteur," / ", P.libellePolice) END) AS libelle
|
|
FROM police P
|
|
JOIN ( SELECT `id`,
|
|
(CASE WHEN (`prenoms` = "") THEN `nom` ELSE CONCAT(`nom`," ",`prenoms`) END) AS souscripteur
|
|
FROM `clients`) C ON (C.id = P.idClient)
|
|
WHERE P.codeSociete = ?
|
|
AND P.id NOT IN (SELECT idPolice FROM policeprestataire
|
|
WHERE codePrestataire = ?)
|
|
order by C.souscripteur';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'], $codePrestataire));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getListePoliceTarifAutre() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_liste_police_tarif(?)';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceStats() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'SELECT id AS `code`, `libellePolice` AS `libelle`
|
|
FROM police WHERE codeSociete=?
|
|
ORDER BY libellePolice';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceGarant($codeGcAssureur) {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_liste_police_garant(?, ?);';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'],$codeGcAssureur));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
|
|
public function initquittancemanuelle($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_quittance_manuelle(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function enregistrerquittancemanuelle()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$idAvenant = $_SESSION['idAvenant'];
|
|
|
|
$sql = 'call sp_avenant_quittance_manuelle(?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user, $idAvenant));
|
|
}
|
|
|
|
public function get_doublon_police_manuel($numeroPoliceSociete)
|
|
{
|
|
$sql = 'select fn_doublon_police_societe_obligatoire(?) as nbNumeroPoliceSociete;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($numeroPoliceSociete));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['nbNumeroPoliceSociete'];
|
|
}
|
|
|
|
public function get_doublon_police_manuel_modif($numeroPoliceSociete)
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'select fn_doublon_police_societe_obligatoire_modif(?, ?) as nbNumeroPoliceSociete;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $numeroPoliceSociete));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['nbNumeroPoliceSociete'];
|
|
}
|
|
|
|
// Sorel 27/06/2022
|
|
public function getAvenantId($idAvenant) {
|
|
|
|
$sql = 'SELECT a.*, IFNULL(e.nbEcheance, "0") AS nbEcheance FROM avenant a
|
|
LEFT JOIN emission e ON (e.codeTypeAvenant = a.codeTypeAvenant
|
|
AND e.idPolice = a.idPolice AND e.numeroAvenant = a.numeroAvenant)
|
|
WHERE a.codeTypeAvenant="QTM" AND a.id=?;';
|
|
|
|
$resultat = $this->executerRequete($sql,array($idAvenant));
|
|
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
// 29/06/2022
|
|
|
|
public function avenantquittancemanuelle($idPolice, $idAvenant)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
//var_dump($codeSociete, $idPolice, $user, $idAvenant);die();
|
|
|
|
$sql = 'call sp_avenant_facturation(?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $user, $idAvenant));
|
|
}
|
|
|
|
public function getEmissionAvenant($numeroAvenant) {
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
//var_dump($numeroAvenant, $idPolice);die();
|
|
|
|
$sql = 'SELECT * FROM emission_temp WHERE (numeroAvenant=? AND idPolice =?)';
|
|
$resultat = $this->executerRequete($sql, array($numeroAvenant, $idPolice));
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getAvenantByNumero($numeroAvenant) {
|
|
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'SELECT * FROM avenant_temp WHERE (`numeroAvenant`=? AND idPolice =?)';
|
|
$resultat = $this->executerRequete($sql, array($numeroAvenant, $idPolice));
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
public function getNumeroAvenant($idAvenant) {
|
|
|
|
|
|
$sql = 'SELECT numeroAvenant as numeroAvenant FROM avenant WHERE (`id`=? );';
|
|
$liste = $this->executerRequete($sql,array($idAvenant));
|
|
return $liste->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getIdAvenant($numeroAvenant) {
|
|
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'SELECT id as idAvenant FROM avenant WHERE (`numeroAvenant`=? AND idPolice =?)';
|
|
$resultat = $this->executerRequete($sql, array($numeroAvenant, $idPolice))->fetch(PDO::FETCH_ASSOC);
|
|
return $resultat['idAvenant'];
|
|
}
|
|
|
|
public function getListeSouscrpteur() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'SELECT `id` AS `code`,
|
|
CASE WHEN (`prenoms` = "") THEN substring(`nom`,1,50) ELSE substring(CONCAT(`nom`," ",`prenoms`),1,50) END AS libelle
|
|
FROM `clients`
|
|
WHERE codeSociete = ?
|
|
ORDER BY nom';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
// 31-12-2022
|
|
public function getGarants(){
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = "SELECT codeGcAssureur as code, libelle
|
|
FROM gc_assureur WHERE codeSociete = ?";
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function getOptionsBasculement(){
|
|
|
|
if (est_anglophone()){
|
|
$sql = "SELECT id as code, libelleEng
|
|
FROM optionbasculement;";
|
|
}else{
|
|
$sql = "SELECT id as code, libelle
|
|
FROM optionbasculement;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function getListeCollegePolice($idPolice){
|
|
|
|
$sql = "SELECT id AS `code`, libelleCollege AS libelle
|
|
FROM college WHERE idPolice=?
|
|
AND (garantieArchive='1') order by 2 ;";
|
|
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function getPoliceGarantMoins($codeGcAssureur) {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'SELECT id AS `code`, `libellePolice` AS `libelle`
|
|
FROM police WHERE codeSociete=? and codeGcAssureur=?
|
|
and id != ?
|
|
ORDER BY libellePolice';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'],$codeGcAssureur, $idPolice));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
//10-04-2023
|
|
public function getIdBeneficiairefacture($idPolice)
|
|
{
|
|
$sql = "SELECT MAX(id) AS idBeneficiaire FROM beneficiaire
|
|
WHERE CONCAT(nomBeneficiaire, ' ', prenomsBeneficiaire) LIKE '%FACTURATION%'
|
|
AND nomBeneficiaire='AVENANT' AND idPolice=?;";
|
|
|
|
$resultat = $this->executerRequete($sql,array($idPolice))->fetch(PDO::FETCH_ASSOC);
|
|
return $resultat['idBeneficiaire'];
|
|
}
|
|
|
|
//07-07-2023
|
|
|
|
public function getTmcollege()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
//12-07-2023
|
|
public function getMaxDateEffetTmCollege($idCollege){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollege(?)";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmcollege($idCollege, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege(?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollege($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollege(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmcollegeacte()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege_acte();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmCollegeacte($idCollege, $codeActe, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollegeacte(?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeActe, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
//13-07-2023
|
|
public function insererTmcollegeacte($idCollege, $codeActe, $dateEffet, $tm, $codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege_acte(?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeActe, $dateEffet, $tm, $user, $codeGarantie));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollegeacte($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollegeacte(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmcollegeacteprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege_acte_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmCollegeacteprestataire($idCollege, $codeActe, $codePrestataire, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollegeacteprestataire(?, ?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeActe, $codePrestataire, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmcollegeacteprestataire($idCollege, $codeActe, $codePrestataire, $dateEffet, $tm, $codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege_acte_prestataire(?, ?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeActe, $codePrestataire, $dateEffet, $tm, $user, $codeGarantie));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollegeacteprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollegeacteprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getgarantie()
|
|
{
|
|
$sql = 'SELECT codeGarantie AS `code`,
|
|
libelle
|
|
FROM garantie;';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getTmcollegegarantie()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege_garantie();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmCollegegarantie($idCollege, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollegegarantie(?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmcollegegarantie($idCollege, $codeGarantie, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege_garantie(?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeGarantie, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollegegarantie($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollegegarantie(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmcollegegarantieprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege_garantie_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmCollegegarantieprestataire($idCollege, $codeGarantie, $codePrestataire){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollegegarantieprestataire(?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeGarantie, $codePrestataire))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmcollegegarantieprestataire($idCollege, $codeGarantie, $codePrestataire, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege_garantie_prestataire(?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codeGarantie, $codePrestataire, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollegegarantieprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollegegarantieprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
//14-07-2023
|
|
public function getTmcollegeprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmcollege_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmCollegeprestataire($idCollege, $codePrestataire){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmcollegeprestataire(?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codePrestataire))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmcollegeprestataire($idCollege, $codePrestataire, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmcollege_prestataire(?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $codePrestataire, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmcollegeprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmcollegeprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpolice()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPolice($idPolice){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpolice(?)";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpolice($idPolice, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice(?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmpolice($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpolice(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpoliceacte()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice_acte();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPoliceacte($idPolice, $codeActe, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpoliceacte(?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeActe, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpoliceacte($idPolice, $codeActe, $dateEffet, $tm, $codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice_acte(?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeActe, $dateEffet, $tm, $user, $codeGarantie));
|
|
|
|
}
|
|
|
|
public function supprimerTmpoliceacte($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpoliceacte(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpoliceacteprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice_acte_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPoliceacteprestataire($idPolice, $codeActe, $codePrestataire, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpoliceacteprestataire(?, ?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeActe, $codePrestataire, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpoliceacteprestataire($idPolice, $codeActe, $codePrestataire, $dateEffet, $tm, $codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice_acte_prestataire(?, ?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeActe, $codePrestataire, $dateEffet, $tm, $user, $codeGarantie));
|
|
|
|
}
|
|
|
|
public function supprimerTmpoliceacteprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpoliceacteprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpolicegarantie()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice_garantie();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPolicegarantie($idPolice, $codeGarantie){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpolicegarantie(?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeGarantie))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpolicegarantie($idPolice, $codeGarantie, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice_garantie(?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeGarantie, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmpolicegarantie($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpolicegarantie(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpolicegarantieprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice_garantie_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPolicegarantieprestataire($idPolice, $codeGarantie, $codePrestataire){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpolicegarantieprestataire(?, ?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeGarantie, $codePrestataire))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpolicegarantieprestataire($idPolice, $codeGarantie, $codePrestataire, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice_garantie_prestataire(?, ?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeGarantie, $codePrestataire, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmpolicegarantieprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpolicegarantieprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getTmpoliceprestataire()
|
|
{
|
|
$sql = 'CALL sp_gettmpolice_prestataire();';
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getMaxDateEffetTmPoliceprestataire($idPolice, $codePrestataire){
|
|
|
|
$sql="CALL sp_getmaxdateeffet_tmpoliceprestataire(?, ?);";
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codePrestataire))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['maxDateEffetGarantie'];
|
|
}
|
|
|
|
public function insererTmpoliceprestataire($idPolice, $codePrestataire, $dateEffet, $tm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_inserertmpolice_prestataire(?, ?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codePrestataire, $dateEffet, $tm, $user));
|
|
|
|
}
|
|
|
|
public function supprimerTmpoliceprestataire($idTm)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_supprimer_tmpoliceprestataire(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idTm, $user));
|
|
}
|
|
|
|
public function getgarantiehospit($codeGarantie)
|
|
{
|
|
$sql = 'SELECT codeGarantie AS `code`,
|
|
libelle
|
|
FROM garantie
|
|
WHERE codeGarantie=?;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeGarantie));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getTauxHonorairesGestion(){
|
|
|
|
$sql="SELECT fn_taux_honoraire_gestion(?) AS tauxHonoraires;";
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete']))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['tauxHonoraires'];
|
|
}
|
|
|
|
public function getModeProrata()
|
|
{
|
|
if (est_anglophone()){
|
|
$sql = "SELECT codeModeProrata as code, libelleEng as libelle
|
|
FROM modeprorata ORDER BY ordre;";
|
|
}else{
|
|
$sql = "SELECT codeModeProrata as code, libelle
|
|
FROM modeprorata ORDER BY ordre;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function getArembourser()
|
|
{
|
|
if (est_anglophone()) {
|
|
$sql = "SELECT codeArembourser as `code`, libelle AS libellEng FROM arembourser;";
|
|
} else {
|
|
$sql = "SELECT codeArembourser `code`, libelle FROM arembourser ;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
|
|
|
|
//
|
|
|
|
public function initsuspensionassure($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
//$sql = 'call sp_init_retrait(?, ?, ?, ?, ?)';
|
|
$sql = 'call sp_init_suspension_assure(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
//
|
|
public function enregistrersuspensionassure()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_suspension_assure(?, ?);';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function initremiseenvigueurassure($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
//$sql = 'call sp_init_retrait(?, ?, ?, ?, ?)';
|
|
$sql = 'call sp_init_remise_en_vigueur_assure(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function enregistrerremiseenvigueurassure()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_avenant_remise_en_vigueur_assure(?, ?);';
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function initincorporationgarantie($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_incorporation_garantie(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function getgarantieout($idCollegePolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_garantie_out(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getgarantiein($idCollegePolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_garantie_in(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function incorporergarantie($idCollegePolice, $codeGarantie)
|
|
{
|
|
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'call sp_ajouter_garantie_avenant(?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollegePolice, $user, $codeGarantie, $idSaisie));
|
|
}
|
|
|
|
public function getNombreGarantie()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'SELECT fn_count_garantiecollegeavenant(?) AS nbGar;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($user))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['nbGar'];
|
|
}
|
|
|
|
public function annulergarantie($idGarantie)
|
|
{
|
|
$sql = 'call sp_annuler_ajout_garantie_avenant(?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie));
|
|
}
|
|
|
|
public function enregistrerincorporationgarantie($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_avenant_incorporation_garantie(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function initretraitgarantie($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_retrait_garantie(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
|
|
public function getgarantieoutretrait($idCollegePolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_garantie_a_retirer_out(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getgarantieinretrait($idCollegePolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_garantie_a_retirer_in(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
//
|
|
public function retirergarantie($idCollegePolice, $codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'call sp_retirer_garantie_avenant(?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollegePolice, $user, $codeGarantie, $idSaisie));
|
|
}
|
|
|
|
public function annulergarantieretrait($idGarantie)
|
|
{
|
|
$sql = 'call sp_annuler_retrait_garantie_avenant(?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie));
|
|
}
|
|
|
|
public function enregistrerretraitgarantie($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_avenant_retrait_garantie(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function initretraitacte($idPolice)
|
|
{
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_init_retrait_acte(?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant));
|
|
}
|
|
|
|
public function getacteoutretrait($idCollegePolice, $codeGarantie)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_acte_a_retirer_out(?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user, $codeGarantie));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getacteinretrait($idCollegePolice, $codeGarantie)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_afficher_acte_a_retirer_in(?, ?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user, $codeGarantie));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getNombreActe()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'SELECT fn_count_actecollegeavenant(?) AS nbAct;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($user))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['nbAct'];
|
|
}
|
|
|
|
public function getgarantiecollege($idCollegePolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_getgarantiecollege(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idCollegePolice, $user));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function retireractecollege($idCollegePolice, $codeGarantie, $codeActe)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'call sp_retirer_acte_avenant(?, ?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollegePolice, $user, $codeGarantie, $codeActe, $idSaisie));
|
|
}
|
|
|
|
public function annulerretraitacte($idActe)
|
|
{
|
|
$sql = 'call sp_annuler_retrait_acte_avenant(?);';
|
|
|
|
$this->executerRequete($sql, array($idActe));
|
|
}
|
|
|
|
public function enregistrerretraitacte($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_avenant_retrait_acte(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user));
|
|
}
|
|
|
|
public function initajustementprime($idPolice)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
$dateAvenant = $_SESSION['dateAvenant_C'];
|
|
$user = $_SESSION['login'];
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
$motifAvenant = $_SESSION['motifAvenant_C'];
|
|
|
|
$sql = 'call sp_init_ajustement_prime(?, ?, ?, ?, ?, ?);';
|
|
$this->executerRequete($sql, array($codeSociete, $idPolice, $dateAvenant, $user, $motifAvenant, $idSaisie));
|
|
}
|
|
|
|
public function fn_primes_non_facturees_police($idPolice)
|
|
{
|
|
$sql = 'select fn_primes_non_facturees_police(?) as nb_non_facturees';
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['nb_non_facturees'];
|
|
}
|
|
|
|
public function getNomGarant($codeGcAssureur)
|
|
{
|
|
$sql = 'SELECT fn_garant(?) as libelle;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeGcAssureur))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['libelle'];
|
|
}
|
|
|
|
public function getArembourserPolice($idPolice)
|
|
{
|
|
$sql = 'SELECT fn_arembourser_police(?) as codeArembourser;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['codeArembourser'];
|
|
|
|
}
|
|
|
|
|
|
public function getSouscripteurRD($idPolice)
|
|
{
|
|
$sql = 'CALL sp_get_client_rd_police(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function appliquertauxajustement($idPolice, $appliquerTauxAjustement)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_appliquer_taux_ajustement(?, ?, ?)';
|
|
$this->executerRequete($sql, array($idPolice, $appliquerTauxAjustement, $user));
|
|
}
|
|
|
|
public function getReseausoincommege($idCollege)
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'call sp_get_reseausoins_college(?, ?, ?);';
|
|
|
|
$reseau = $this->executerRequete($sql, array($_SESSION['codeSociete'], $idPolice, $idCollege));
|
|
|
|
return $reseau->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getlibellereseausoincollege($idCollege) {
|
|
|
|
$sql = 'SELECT fn_reseau_soins_college(?) as nomReseau';
|
|
$resultat = $this->executerRequete($sql, array($idCollege));
|
|
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $ligne['nomReseau'];
|
|
}
|
|
|
|
public function getcollegeavenantfacturation($idPolice)
|
|
{
|
|
$sql = 'SELECT fn_college_avenant_facturation(?) AS idCollege;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $ligne['idCollege'];
|
|
}
|
|
|
|
public function getListePoliceAvecBeneficiaire() {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_liste_police_avec_beneficiaire(?)';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete']));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getCOllegePolice($idPolice) {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'SELECT id AS `code`, `libelleCollege` AS `libelle`
|
|
FROM college WHERE codeSociete=? and idPolice=?
|
|
ORDER BY libelleCollege';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'],$idPolice));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getDernierIdAvenantType($idPolice, $codeTypeAvenant){
|
|
|
|
$sql = 'select fn_dernier_idavenant_type_avenant(?, ?, ?) as idAvenant';
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $idPolice, $codeTypeAvenant))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['idAvenant'];
|
|
}
|
|
|
|
public function getPoliceEncoursGarant($codeGcAssureur) {
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_liste_police_garant_en_cours(?, ?);';
|
|
|
|
$liste = $this->executerRequete($sql,array($_SESSION['codeSociete'],$codeGcAssureur));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function majContextePolice($idPolice)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_maj_contexte_police(?, ?);';
|
|
|
|
$liste = $this->executerRequete($sql, array($user, $idPolice));
|
|
|
|
$_SESSION['contextPolice'] = $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
|
|
} |