434 lines
16 KiB
PHP
Executable File
434 lines
16 KiB
PHP
Executable File
<?php
|
|
require_once 'Framework/Modele.php';
|
|
|
|
class Police_d extends Modele {
|
|
|
|
public function getPolices($numeroClient) {
|
|
$sql = 'SELECT A.id, A.codeSociete, A.idClient, A.numeroClient, numeroPolice, numeroPoliceSociete,
|
|
B.libelle As typeAvenant, dateEffet, dateFin, dateEcheance, numeroAvenant, dateAvenant, numeroContrat,
|
|
D.libelle as etatPolice, A.codeFractionnement, IFNULL(A.primeFamille,"0") as primeFamille,
|
|
IFNULL(A.primeBeneficiaire,"0") as primeBeneficiaire, rachatTm, TM.libelle as rachattm
|
|
FROM d_police A
|
|
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.numeroClient=?)';
|
|
|
|
$polices = $this->executerRequete($sql, array($_SESSION['codeSociete'], $numeroClient));
|
|
return $polices;
|
|
}
|
|
|
|
public function getPolicesId($id) {
|
|
$sql = 'SELECT A.id, A.codeSociete, A.idClient, A.numeroClient, numeroPolice, numeroPoliceSociete, numeroContrat,
|
|
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
|
|
FROM d_police A
|
|
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.idClient=?) order by A.id';
|
|
|
|
$polices = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
return $polices;
|
|
}
|
|
|
|
public function getPolice($numeroPolice)
|
|
{
|
|
$sql = 'call sp_d_getPoliceNo(?, ?);';
|
|
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $numeroPolice));
|
|
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceId($id)
|
|
{
|
|
$sql = 'call sp_d_getPoliceId(?, ?);';
|
|
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPoliceIdSimple($id) {
|
|
$sql = 'SELECT A.* FROM d_police A WHERE (A.codeSociete=?) AND (A.id=?)';
|
|
$police = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
return $police->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getNombrePolices($numeroClient)
|
|
{
|
|
$sql = 'select count(*) as nbPolices FROM d_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 d_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($id) {
|
|
|
|
$sql = 'CALL sp_get_contexte_d_police_id(?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'], $id));
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['idClient_d_C'] = $context['idClient'];
|
|
$_SESSION['numeroClient_d_C'] = $context['numeroClient'];
|
|
$_SESSION['idPolice_d_C'] = $context['idPolice'];
|
|
$_SESSION['numeroPolice_d_C'] = $context['numeroPolice'];
|
|
$_SESSION['codeEtape_d_C'] = $context['codeEtape'];
|
|
$_SESSION['tauxTaxe_d_C'] = $context['tauxTaxe'];
|
|
$_SESSION['tauxCommission_d_C'] = $context['tauxCommission'];
|
|
$_SESSION['deduireComm_d_C'] = $context['deduireComm'];
|
|
|
|
$_SESSION['dateEffet_d_C'] = $context['dateEffet'];
|
|
$_SESSION['dateFin_d_C'] = $context['dateFin'];
|
|
$_SESSION['dateEcheance_d_C'] = $context['dateEcheance'];
|
|
|
|
$_SESSION['dateAvenant_d_C'] = $context['dateAvenant'];
|
|
|
|
$_SESSION['idPolice_d_Ajax'] = $_SESSION['idPolice_d_C'];
|
|
|
|
$_SESSION['rachatTm_d_C'] = $context['rachatTm'];
|
|
|
|
$_SESSION['collegeArchive_d_C'] = $context['collegeArchive'];
|
|
}
|
|
|
|
public function getDenierNumeroPolice($annee)
|
|
{
|
|
$sql = 'select dernierNumeroPolice from d_numerotationpolice WHERE (codeSociete=? AND annee=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$annee));
|
|
|
|
if ($resultat->rowCount() == 0)
|
|
{
|
|
$sql = 'insert into d_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 d_numerotationpolice set dernierNumeroPolice=? WHERE (codeSociete=? AND annee=?)';
|
|
$this->executerRequete($sql, array($dernierNumeroPolice, $_SESSION['codeSociete'], $annee));
|
|
}
|
|
|
|
public function ajouter($codeSociete, $idClient, $numeroClient, $numeroPolice, $codeTypeContrat,
|
|
$dateEffet, $dateFin, $dateEcheance, $numeroAvenant, $codeTypeAvenant, $dateAvenant, $codeApporteur, $codeGestionnaire,
|
|
$dateCre, $userCre, $codeEtatPolice, $codeFractionnement, $codePeriodicite, $tauxCommission,
|
|
$rachatTm, $deduireComm, $tauxRachatTm, $codeCommercial, $tauxCommissionAutre, $delaiCarencePolice,
|
|
$codeGcAssureur, $tauxCommissionGerant,$tauxCommissionGcAssureur,
|
|
$libellePolice, $plafondPolice, $codeModeProrata)
|
|
{
|
|
|
|
$sql = 'CALL sp_creer_d_police(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
|
|
|
|
/*
|
|
$array = array($codeSociete, $idClient, $numeroClient, $numeroPolice, $codeTypeContrat,
|
|
$dateEffet, $dateFin, $dateEcheance, $numeroAvenant, $codeTypeAvenant, $dateAvenant, $codeApporteur, $codeGestionnaire,
|
|
$dateCre, $userCre, $codeEtatPolice, $codeFractionnement, $codePeriodicite, $tauxCommission,
|
|
$rachatTm, $deduireComm, $tauxRachatTm, $codeCommercial, $tauxCommissionAutre, $delaiCarencePolice,
|
|
$codeGcAssureur, $tauxCommissionGerant,$tauxCommissionGcAssureur,
|
|
$libellePolice, $plafondPolice, $codeModeProrata);
|
|
|
|
var_dump($array); die();*/
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $idClient, $numeroClient, $numeroPolice, $codeTypeContrat,
|
|
$dateEffet, $dateFin, $dateEcheance, $numeroAvenant, $codeTypeAvenant, $dateAvenant, $codeApporteur, $codeGestionnaire,
|
|
$dateCre, $userCre, $codeEtatPolice, $codeFractionnement, $codePeriodicite, $tauxCommission,
|
|
$rachatTm, $deduireComm, $tauxRachatTm, $codeCommercial, $tauxCommissionAutre, $delaiCarencePolice,
|
|
$codeGcAssureur, $tauxCommissionGerant,$tauxCommissionGcAssureur,
|
|
$libellePolice, $plafondPolice, $codeModeProrata))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
|
|
return $resultat['dernierId'];
|
|
|
|
}
|
|
|
|
public function modifier($id, $codeTypeContrat, $codeApporteur, $tauxCommission, $codePeriodicite, $rachatTm,
|
|
$tauxRachatTm, $dateEffet, $dateEcheance, $codeCommercial, $tauxCommissionAutre, $tauxRistourne, $delaiCarencePolice,
|
|
$codeGcAssureur,$tauxCommissionGerant,$tauxCommissionGcAssureur)
|
|
{
|
|
$sql = 'UPDATE d_police SET
|
|
codeTypeContrat =?,
|
|
codeApporteur =?,
|
|
tauxCommission =?,
|
|
codePeriodicite =?,
|
|
rachatTm =?,
|
|
tauxRachatTm =?,
|
|
dateEffet =?,
|
|
dateEcheance =?,
|
|
codeCommercial =?,
|
|
tauxCommissionAutre =?,
|
|
tauxRistourne =?,
|
|
delaiCarencePolice =?,
|
|
codeGcAssureur =?,
|
|
tauxCommissionGerant =?,
|
|
tauxCommissionGcAssureur =?
|
|
|
|
where (id=?)';
|
|
|
|
$this->executerRequete($sql, array($codeTypeContrat, $codeApporteur, $tauxCommission, $codePeriodicite, $rachatTm,
|
|
$tauxRachatTm, $dateEffet, $dateEcheance, $codeCommercial, $tauxCommissionAutre, $tauxRistourne, $delaiCarencePolice,
|
|
$codeGcAssureur,$tauxCommissionGerant,$tauxCommissionGcAssureur,$id));
|
|
}
|
|
|
|
public function getIdPolice($numeroPolice) {
|
|
$sql = 'SELECT id FROM d_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 d_police WHERE (codeSociete=?) and (numeroPolice=?)';
|
|
$resultat = $this->executerRequete($sql, array($_SESSION['codeSociete'],$numeroPolice));
|
|
return ($resultat->rowCount() > 0);
|
|
}
|
|
|
|
public function formaterNumeroPolice($annee,$numeroPolice) {
|
|
$numeroPolice = "00000".$numeroPolice;
|
|
$numeroPolice = substr($numeroPolice, -6);
|
|
$numeroPolice = "D".$annee.$numeroPolice;
|
|
return $numeroPolice;
|
|
}
|
|
|
|
public function supprimer($idPolice) {
|
|
$sql = 'select id from d_college where (idPolice=?) limit 1';
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
if($resultat->rowCount() == 0)
|
|
{
|
|
$sql = 'DELETE FROM d_police WHERE (id=?)';
|
|
$this->executerRequete($sql, array($idPolice));
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function archivergarantie($id) {
|
|
$sql = 'UPDATE d_police SET garantieArchive="1" where (id=?)';
|
|
$this->executerRequete($sql, array($id));
|
|
}
|
|
|
|
public function archivercollege($id) {
|
|
$sql = 'UPDATE d_police SET collegeArchive="1" where (id=?)';
|
|
$this->executerRequete($sql, array($id));
|
|
}
|
|
|
|
public function getContexteAjaxPolice($id)
|
|
{
|
|
$sql = 'SELECT A.id as idPolice, idClient, codeTypeContrat, budgetDefini, codeGcAssureur FROM d_police A WHERE (A.id=?)';
|
|
|
|
$resultat = $this->executerRequete($sql, array($id));
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['idPolice_d_Ajax'] = $context['idPolice'];
|
|
$_SESSION['idPolice_d_C'] = $_SESSION['idPolice_d_Ajax'];
|
|
|
|
/*
|
|
$_SESSION['codeGarantAjax'] = $context['codeGcAssureur'];
|
|
$_SESSION['codeGcAssureur_C'] = $_SESSION['codeGarantAjax'];
|
|
$_SESSION['nomClient_C'] = $this->getNomClient($context['idClient'], $context['codeTypeContrat']);
|
|
*/
|
|
}
|
|
|
|
public function getCollegesCP($idPolice)
|
|
{
|
|
$sql = 'call sp_collesges_d_police(?);';
|
|
|
|
$colleges = $this->executerRequete($sql, array($idPolice));
|
|
return $colleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getGarantiesCollegesCP($idPolice)
|
|
{
|
|
$sql = 'call sp_garanties_colleges_d_police(?);';
|
|
|
|
$garantiescolleges = $this->executerRequete($sql, array($idPolice));
|
|
return $garantiescolleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function gettableauPrestationsCP($idPolice)
|
|
{
|
|
$sql = 'call sp_tableau_prestation_d_police(?);';
|
|
|
|
$colleges = $this->executerRequete($sql, array($idPolice));
|
|
return $colleges->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getClientsearchPol($numeroPolice)
|
|
{
|
|
$sql = 'select id as idPolice FROM d_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 transformer($idDevis, $numeroOptionTarif, $idClientExistant, $dateEffetContrat)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_transformer_devis(?, ?, ?, ?, ?);';
|
|
|
|
/*var_dump(array($idDevis, $user, $numeroOptionTarif, $idClientExistant, $dateEffetContrat));
|
|
die();*/
|
|
|
|
$resultat = $this->executerRequete($sql, array($idDevis, $user, $numeroOptionTarif, $idClientExistant, $dateEffetContrat));
|
|
|
|
if ($resultat->rowCount() == 0)
|
|
{
|
|
return "0";
|
|
} else
|
|
{
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['idPolice'];
|
|
}
|
|
}
|
|
|
|
public function getContexteClientId($idPolice) {
|
|
|
|
$sql = 'CALL sp_d_getclientpolice_id(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
$context = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$_SESSION['idClient_d_C'] = $context['idClient'];
|
|
$_SESSION['numeroClient_d_C'] = $context['numeroClient'];
|
|
$_SESSION['nomClient_d_C'] = $context['nomClient'];
|
|
$_SESSION['idClient_d_Ajax'] = $context['idClient'];
|
|
$_SESSION['codeLangueClient_d'] = $context['codeLangueClient'];
|
|
$_SESSION['codeLocalite_d'] = $context['codeLocalite'];
|
|
|
|
$_SESSION['nomMandataire_d_C'] = $context['nomMandataire'];
|
|
$_SESSION['telephonePortablemandataire_d_C'] = $context['telephonePortablemandataire'];
|
|
$_SESSION['codeModePaiement_d_C'] = $context['codeModePaiement'];
|
|
$_SESSION['telephoneMobile_d_C'] = $context['telephoneMobile'];
|
|
$_SESSION['rib_d_C'] = $context['rib'];
|
|
}
|
|
|
|
public function getEnteteCP($idPolice)
|
|
{
|
|
$sql = 'call sp_entete_devis(?);';
|
|
|
|
$entete = $this->executerRequete($sql, array($idPolice));
|
|
return $entete->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function nouvelleoptiontarif($idPolice)
|
|
{
|
|
$sql = 'insert into d_optionstarif (idPolice, numeroOptionTarif, libelleOptionTarif, user)
|
|
select id as idPolice, "1" numeroOptionTarif, "Option 1" libelleOptionTarif, userCre from d_police where (id=?)';
|
|
|
|
$this->executerRequete($sql, array($dernierId));
|
|
|
|
return $dernierId;
|
|
|
|
}
|
|
|
|
public function getTextecp($id) {
|
|
$sql = 'select texteCp from d_police where (id=?)';
|
|
$textecp = $this->executerRequete($sql, array($id));
|
|
return $textecp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function enregistrertexteCp($texteCp, $idPolice) {
|
|
$sql = 'update d_police set texteCp=? where (id=?)';
|
|
$this->executerRequete($sql, array($texteCp, $idPolice));
|
|
}
|
|
|
|
public function fn_min_numero_option_tarif($idPolice)
|
|
{
|
|
$sql = 'select fn_min_numero_option_tarif(?) as numeroOptionTarif';
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
return $ligne['numeroOptionTarif'];
|
|
}
|
|
|
|
public function getClientsearchPolId($idPolice)
|
|
{
|
|
$sql = 'select id as idPolice from d_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 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 getNomClient($idClient, $codeTypeContrat){
|
|
if($codeTypeContrat=="G"){
|
|
$sql = "SELECT nom AS nomClient FROM d_clients WHERE (id=?)";
|
|
}else{
|
|
$sql = "SELECT CONCAT(nom,' ',prenoms) AS nomClient FROM d_clients WHERE (id=?)";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($idClient))->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $resultat['nomClient'];
|
|
}
|
|
|
|
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 getClientsExistant($idPolice)
|
|
{
|
|
$sql = 'call sp_clients_existants_transformation_devis(?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
} |