1059 lines
33 KiB
PHP
Executable File
1059 lines
33 KiB
PHP
Executable File
<?php
|
|
require_once 'Framework/Modele.php';
|
|
class College_temp extends Modele {
|
|
|
|
public function ajouterUneGarantie($idCollegeTemp, $codeGarantie)
|
|
{
|
|
|
|
$sql = 'call sp_ajouter_une_garantie(?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $codeGarantie));
|
|
}
|
|
|
|
public function getCollegeTemp($idCollegeTemp)
|
|
{
|
|
$sql = 'call sp_afficher_college_temp(?)';
|
|
|
|
$collegeTemp = $this->executerRequete($sql, array($idCollegeTemp));
|
|
|
|
return $collegeTemp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getCollegeTempPolice($idPolice, $user)
|
|
{
|
|
$sql = 'select * from college_temp where (idPolice=?) and (user=?)';
|
|
|
|
$collegeTemp = $this->executerRequete($sql, array($idPolice, $user));
|
|
return $collegeTemp->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function getCollegeTempPoliceRen($idPolice, $user)
|
|
{
|
|
$sql = 'select * from college_ren_temp where (idPolice=?) and (user=?) order by codeProduit, libelleCollege';
|
|
|
|
$collegeTemp = $this->executerRequete($sql, array($idPolice, $user));
|
|
return $collegeTemp->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getGarCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'select * from garantie_temp where (idCollegeTemp=?) order by ordre';
|
|
$garanties = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $garanties;
|
|
}
|
|
|
|
public function getGarAddCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'select A.*, B.garantieEng, B.plafond as plafondOld, B.plafond+A.plafond as plafondNew
|
|
from garantieaddition_temp A
|
|
join garantie_temp B on (B.user=A.user) and (B.idCollegeTemp=A.idCollegeTemp) and (B.codeGarantie=A.codeGarantie)
|
|
where (A.idCollegeTemp=?) order by A.ordre';
|
|
|
|
$garanties = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $garanties;
|
|
}
|
|
|
|
public function getGarBaseCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'select * from garantie_temp where (idCollegeTemp=?) and (optionnelle!="1") order by ordre';
|
|
$garanties = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $garanties;
|
|
}
|
|
|
|
public function getGarOptCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'select A.*, IFNULL(B.idCollegeTemp,"0") as idCollegeTemp
|
|
from garantie A
|
|
left join garantie_temp B on (B.idCollegeTemp=?) and (B.codeSociete=A.codeSociete) and (B.codeGarantie=A.codeGarantie)
|
|
Where (A.optionnelle="1") order by A.ordre';
|
|
|
|
$garanties = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $garanties;
|
|
}
|
|
|
|
public function getGarOptCollegeAcq($idCollegeTemp)
|
|
{
|
|
$sql = 'select * from garantie_temp where (idCollegeTemp=?) and (optionnelle="1") order by ordre';
|
|
$garanties = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $garanties;
|
|
}
|
|
|
|
public function getPrestation($idCollegeTemp)
|
|
{
|
|
$sql = 'call sp_get_prestation_temp(?)';
|
|
/*
|
|
$sql = 'select A.*, B.libelle as reseausoin from prestation_temp A
|
|
join reseausoins B on (B.codeReseau=A.codeReseau)
|
|
where (A.idCollegeTemp=?) order by A.tmBase, A.tm';
|
|
*/
|
|
|
|
$prestations = $this->executerRequete($sql, array($idCollegeTemp));
|
|
return $prestations;
|
|
}
|
|
|
|
public function supprimer($idCollegeTemp) {
|
|
$sql = 'DELETE FROM college_temp WHERE (id=?)';
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
|
|
$sql = 'DELETE FROM garantie_temp WHERE (idCollegeTemp=?)';
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
}
|
|
|
|
public function supprimergarantie($idGarantie) {
|
|
$sql = 'DELETE FROM garantie_temp WHERE (id=?)';
|
|
$this->executerRequete($sql, array($idGarantie));
|
|
}
|
|
|
|
public function initCollege($idPolice, $user)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_init_college(?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $codeSociete, $user));
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $codeSociete, $user));
|
|
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$idCollegeTemp = $ligne['idCollegeTemp'];
|
|
|
|
$_SESSION['idCollegeTemp'] = $idCollegeTemp;
|
|
|
|
return $idCollegeTemp;
|
|
}
|
|
|
|
|
|
public function initModifCollege($idCollege, $user)
|
|
{
|
|
$this->viderCollegTemp($user);
|
|
|
|
/*
|
|
$sql = 'insert into college_temp (idCollege, codeSociete, idPolice, numeroPolice, codeProduit, libelleCollege, nbAdherent,
|
|
nbAyantdroit, primeFamille, primeBeneficiaire, surprimeOption, surprimeTm, surprimeAnt, commission, commissionAutre,
|
|
primeNette, primeNetteStat, primeHt, taxe, fraisCarte, primeTtc, primeTtcAdherent, user, rachatTm, tauxRachatTm, tauxPrimeRachatTm,
|
|
cplFuneraire, primeCplFuneraire, primeTtcTotal, garantieArchive, deduireComm, surprimeOptionBeneficiaire,
|
|
surprimeTmBeneficiaire, surprimeAntBeneficiaire, commissionBeneficiaire, commissionAutreBeneficiaire,
|
|
primeNetteBeneficiaire, primeNetteStatBeneficiaire, primeHtBeneficiaire, taxeBeneficiaire, fraisCarteBeneficiaire,
|
|
primeTtcBeneficiaire, cplFuneraireBeneficiaire, primeCplFuneraireBeneficiaire, imposerPlafond)
|
|
|
|
select id as idCollege, codeSociete, idPolice, numeroPolice, codeProduit, libelleCollege, nbAdherent,
|
|
nbAyantdroit, primeFamille, primeBeneficiaire, surprimeOption, surprimeTm, surprimeAnt, commission, commissionAutre,
|
|
primeNette, primeNetteStat, primeHt, taxe, fraisCarte, primeTtc, primeTtcAdherent, ? as user, rachatTm, tauxRachatTm, tauxPrimeRachatTm,
|
|
cplFuneraire, primeCplFuneraire, primeTtcTotal, garantieArchive, deduireComm, surprimeOptionBeneficiaire,
|
|
surprimeTmBeneficiaire, surprimeAntBeneficiaire, commissionBeneficiaire, commissionAutreBeneficiaire,
|
|
primeNetteBeneficiaire, primeNetteStatBeneficiaire, primeHtBeneficiaire, taxeBeneficiaire, fraisCarteBeneficiaire,
|
|
primeTtcBeneficiaire, cplFuneraireBeneficiaire, primeCplFuneraireBeneficiaire, imposerPlafond
|
|
from college where id=?';
|
|
|
|
$this->executerRequete($sql, array($user, $idCollege));
|
|
|
|
$idCollegeTemp = $this->dernierId();
|
|
$_SESSION['idCollegeTemp'] = $idCollegeTemp;
|
|
|
|
$this->initModifGarantieCollege($idCollegeTemp);
|
|
|
|
$this->initModifPrestation($idCollegeTemp);
|
|
|
|
return $idCollegeTemp;
|
|
*/
|
|
|
|
$sql = 'CALL sp_init_modifier_college(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollege, $user));
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege, $user));
|
|
|
|
$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$idCollegeTemp = $ligne['idCollegeTemp'];
|
|
|
|
$_SESSION['idCollegeTemp'] = $idCollegeTemp;
|
|
|
|
return $idCollegeTemp;
|
|
}
|
|
|
|
public function recapituler($idCollegeTemp, $codeProduit, $libelleCollege, $nbAdherent, $nbAyantdroit, $primeFamille,
|
|
$surprimeAnt, $rachatTm, $cplFuneraire, $tauxRachatTm, $tauxPrimeRachatTm, $fraisCarte, $imposerPlafond)
|
|
{
|
|
$sql = 'call sp_recapituller_college_temp(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $codeProduit, $libelleCollege, $nbAdherent, $nbAyantdroit,
|
|
$primeFamille, $surprimeAnt, $rachatTm, $cplFuneraire, $tauxRachatTm, $tauxPrimeRachatTm, $fraisCarte, $imposerPlafond));
|
|
}
|
|
|
|
public function maj($idCollegeTemp, $codeProduit, $libelleCollege, $nbAdherent, $nbAyantdroit, $primeFamille,
|
|
$surprimeAnt, $rachatTm, $cplFuneraire, $tauxRachatTm, $tauxPrimeRachatTm)
|
|
{
|
|
$sql = 'call sp_maj_college_temp(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $codeProduit, $libelleCollege, $nbAdherent, $nbAyantdroit,
|
|
$primeFamille, $surprimeAnt, $rachatTm, $cplFuneraire, $tauxRachatTm, $tauxPrimeRachatTm));
|
|
}
|
|
|
|
public function enregistrer($idCollegeTemp)
|
|
{
|
|
$sql = 'insert into college (codeSociete, idPolice, numeroPolice, codeProduit, libelleCollege, nbAdherent, nbAyantdroit, primeFamille,
|
|
primeBeneficiaire, surprimeOption, surprimeTm, surprimeAnt, commission, commissionAutre, primeNette, primeNetteStat, primeHt,
|
|
taxe, fraisCarte, primeTtc, primeTtcAdherent, rachatTm, cplFuneraire, primeCplFuneraire, primeTtcTotal,
|
|
user, deduireComm, surprimeOptionBeneficiaire, surprimeTmBeneficiaire, surprimeAntBeneficiaire, commissionBeneficiaire,
|
|
commissionAutreBeneficiaire, primeNetteBeneficiaire, primeNetteStatBeneficiaire, primeHtBeneficiaire, taxeBeneficiaire, fraisCarteBeneficiaire,
|
|
primeTtcBeneficiaire, cplFuneraireBeneficiaire, primeCplFuneraireBeneficiaire, tauxRachatTm, tauxPrimeRachatTm, imposerPlafond)
|
|
|
|
select codeSociete, idPolice, numeroPolice, codeProduit, libelleCollege, nbAdherent, nbAyantdroit, primeFamille,
|
|
primeBeneficiaire, surprimeOption, surprimeTm, surprimeAnt, commission, commissionAutre, primeNette, primeNetteStat, primeHt,
|
|
taxe, fraisCarte, primeTtc, primeTtcAdherent, rachatTm, cplFuneraire, primeCplFuneraire, primeTtcTotal,
|
|
user, deduireComm, surprimeOptionBeneficiaire, surprimeTmBeneficiaire, surprimeAntBeneficiaire, commissionBeneficiaire,
|
|
commissionAutreBeneficiaire, primeNetteBeneficiaire, primeNetteStatBeneficiaire, primeHtBeneficiaire, taxeBeneficiaire, fraisCarteBeneficiaire,
|
|
primeTtcBeneficiaire, cplFuneraireBeneficiaire, primeCplFuneraireBeneficiaire, tauxRachatTm, tauxPrimeRachatTm, imposerPlafond
|
|
from college_temp where id=?';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
|
|
$idCollege = $this->dernierId();
|
|
|
|
$this->ajouterGarantieCollege($idCollege, $idCollegeTemp);
|
|
|
|
$this->ajouterTableauPrestation($idCollege, $idCollegeTemp);
|
|
|
|
$user = $_SESSION['login'];
|
|
|
|
$this->viderCollegTemp($user);
|
|
|
|
}
|
|
|
|
public function modifiercollege($idCollegeTemp, $idCollege)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_modifier_college(?, ?, ?)';
|
|
$this->executerRequete($sql, array($idCollegeTemp, $idCollege, $user));
|
|
}
|
|
|
|
|
|
public function ajouterGarantieCollege($idCollege, $idCollegeTemp)
|
|
{
|
|
$sql = 'delete from garantiecollege where idCollege=?';
|
|
$this->executerRequete($sql, array($idCollege));
|
|
|
|
$sql = 'insert into garantiecollege (codeSociete, idCollege, idPolice, numeroPolice, codeGarantie, garantie, garantieEng, optionnelle,
|
|
additionnelle, tauxPlafond, forfaitPlafond, primeOption, user, plafond, plafondLettre, primeFamille, ordre, champApplication)
|
|
|
|
select codeSociete, ? as idCollege, idPolice, numeroPolice, codeGarantie, garantie, garantieEng, optionnelle,
|
|
additionnelle, tauxPlafond, forfaitPlafond, primeOption, user, plafond, plafondLettre, primeFamille, ordre, champApplication
|
|
from garantie_temp where idCollegeTemp=?';
|
|
|
|
$this->executerRequete($sql, array($idCollege, $idCollegeTemp));
|
|
}
|
|
|
|
public function ajouterTableauPrestation($idCollege, $idCollegeTemp)
|
|
{
|
|
$sql = 'delete from prestationcollege where idCollege=?';
|
|
$this->executerRequete($sql, array($idCollege));
|
|
|
|
$sql = 'insert into prestationcollege (
|
|
codeSociete,
|
|
idCollege,
|
|
idPolice,
|
|
numeroPolice,
|
|
codeProduit,
|
|
codeReseau,
|
|
tm,
|
|
`user`,
|
|
dateSysteme,
|
|
tmBase,
|
|
rachatTm,
|
|
tauxRachatTm
|
|
)
|
|
|
|
select
|
|
codeSociete,
|
|
? as idCollege,
|
|
idPolice,
|
|
numeroPolice,
|
|
codeProduit,
|
|
codeReseau,
|
|
tm,
|
|
`user`,
|
|
dateSysteme,
|
|
tmBase,
|
|
rachatTm,
|
|
tauxRachatTm
|
|
from prestation_temp where idCollegeTemp=?';
|
|
|
|
$this->executerRequete($sql, array($idCollege, $idCollegeTemp));
|
|
}
|
|
|
|
public function initGarantieCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'insert into garantie_temp
|
|
(
|
|
codeSociete,
|
|
idCollegeTemp,
|
|
idPolice,
|
|
numeroPolice,
|
|
codeGarantie,
|
|
garantie,
|
|
garantieEng,
|
|
optionnelle,
|
|
additionnelle,
|
|
tauxPlafond,
|
|
forfaitPlafond,
|
|
user,
|
|
plafond,
|
|
ordre,
|
|
plafondLettre,
|
|
champApplication
|
|
)
|
|
select
|
|
A.codeSociete,
|
|
B.id as idCollegeTemp,
|
|
B.idPolice,
|
|
B.numeroPolice,
|
|
A.codeGarantie,
|
|
A.libelle as garantie,
|
|
A.libelleEng as garantieEng,
|
|
A.optionnelle,
|
|
A.additionnelle,
|
|
A.tauxPlafond,
|
|
A.forfaitPlafond,
|
|
B.user,
|
|
ROUND((B.primeFamille*A.tauxPlafond),0)+A.forfaitPlafond as plafond,
|
|
A.ordre, A.plafondLettre,
|
|
A.champApplication
|
|
from garantie A
|
|
join college_temp B on (B.codeSociete=A.codeSociete)
|
|
Where (B.id=?) and optionnelle!="1"';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
}
|
|
|
|
public function initPrestationCollege($idCollegeTemp)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$codeProduitBase = $_SESSION['codeProduitBase'];
|
|
|
|
$sql = 'call sp_init_prestation_college(?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $codeProduitBase, $user));
|
|
}
|
|
|
|
public function initModifGarantieCollege($idCollegeTemp)
|
|
{
|
|
$sql = 'insert into garantie_temp
|
|
(
|
|
codeSociete,
|
|
idCollege,
|
|
idCollegeTemp,
|
|
idPolice,
|
|
numeroPolice,
|
|
codeGarantie,
|
|
garantie,
|
|
garantieEng,
|
|
optionnelle,
|
|
additionnelle,
|
|
tauxPlafond,
|
|
forfaitPlafond,
|
|
primeOption,
|
|
user,
|
|
plafond,
|
|
ordre,
|
|
plafondLettre,
|
|
primeFamille,
|
|
champApplication
|
|
)
|
|
select
|
|
A.codeSociete,
|
|
A.idCollege,
|
|
B.id as idCollegeTemp,
|
|
B.idPolice,
|
|
B.numeroPolice,
|
|
A.codeGarantie,
|
|
A.garantie,
|
|
A.garantieEng,
|
|
A.optionnelle,
|
|
A.additionnelle,
|
|
A.tauxPlafond,
|
|
A.forfaitPlafond,
|
|
A.primeOption,
|
|
B.user,
|
|
A.plafond,
|
|
A.ordre, A.plafondLettre,
|
|
A.primeFamille,
|
|
A.champApplication
|
|
from garantiecollege A
|
|
join college_temp B on (B.codeSociete=A.codeSociete) and (B.idCollege=A.idCollege)
|
|
Where (B.id=?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
}
|
|
|
|
public function initModifPrestation($idCollegeTemp)
|
|
{
|
|
$sql = 'insert into prestation_temp (
|
|
codeSociete,
|
|
idCollegeTemp,
|
|
idCollege,
|
|
idPolice,
|
|
numeroPolice,
|
|
codeProduit,
|
|
codeReseau,
|
|
tm,
|
|
user,
|
|
dateSysteme,
|
|
tmBase,
|
|
rachatTm,
|
|
tauxRachatTm,
|
|
tauxPrimeRachatTm
|
|
)
|
|
select
|
|
A.codeSociete,
|
|
B.id as idCollegeTemp,
|
|
B.idCollege,
|
|
B.idPolice,
|
|
B.numeroPolice,
|
|
A.codeProduit,
|
|
A.codeReseau,
|
|
A.tm,
|
|
B.user,
|
|
now() as dateSysteme,
|
|
A.tmBase,
|
|
B.rachatTm,
|
|
B.tauxRachatTm,
|
|
B.tauxPrimeRachatTm
|
|
from prestationcollege A
|
|
join college_temp B on (B.codeSociete=A.codeSociete) and (B.idCollege=A.idCollege)
|
|
Where (B.id=?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp));
|
|
}
|
|
|
|
public function majtauxplafond($idGarantie, $tauxPlafond)
|
|
{
|
|
$sql = 'update garantie_temp set tauxPlafond=? where (id=?)';
|
|
$this->executerRequete($sql, array($tauxPlafond, $idGarantie));
|
|
|
|
$sql = 'update garantie_temp set plafond= ROUND((primeFamille*tauxPlafond),0)+forfaitPlafond where (id=?)';
|
|
$this->executerRequete($sql, array($idGarantie));
|
|
}
|
|
|
|
public function imposerplafond($idGarantie, $plafond)
|
|
{
|
|
$sql = 'update garantie_temp set plafond=? where (id=?)';
|
|
$this->executerRequete($sql, array($plafond, $idGarantie));
|
|
}
|
|
|
|
public function majtm($idPrestation, $tm)
|
|
{
|
|
$sql = 'update prestation_temp set tm=? where (id=?)';
|
|
$this->executerRequete($sql, array($tm, $idPrestation));
|
|
}
|
|
|
|
public function majtmprestatairenplus($idPrestation, $tm)
|
|
{
|
|
$sql = 'update prestationcollegeplus set tm=? where (id=?)';
|
|
$this->executerRequete($sql, array($tm, $idPrestation));
|
|
}
|
|
|
|
public function retirerprestatairenplus($idPrestation)
|
|
{
|
|
$sql = 'delete from prestationcollegeplus where (id=?)';
|
|
$this->executerRequete($sql, array($idPrestation));
|
|
}
|
|
|
|
public function ajouterunprestataireenplus($codePrestataire)
|
|
{
|
|
$idCollege = $_SESSION['idCollege'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_ajouterprestataireenplus(?, ?, ?)';
|
|
$this->executerRequete($sql, array($idCollege, $codePrestataire, $user));
|
|
}
|
|
|
|
public function ajouterunprestataireenmoins($codePrestataire)
|
|
{
|
|
$idCollege = $_SESSION['idCollege'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'call sp_ajouterprestataireenmoins(?, ?, ?)';
|
|
$this->executerRequete($sql, array($idCollege, $codePrestataire, $user));
|
|
}
|
|
|
|
public function retirerprestatairenmoins($idPrestation)
|
|
{
|
|
$sql = 'delete from prestationcollegemoins where (id=?)';
|
|
$this->executerRequete($sql, array($idPrestation));
|
|
}
|
|
|
|
// ajouterunprestatairetm
|
|
public function ajouterunprestatairetm($codePrestataire, $idCollege)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
//var_dump($idCollege, $codePrestataire, $user);
|
|
//die();
|
|
|
|
$sql = 'call sp_ajouterprestatairetm(?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codePrestataire, $user, $idCollege));
|
|
}
|
|
|
|
|
|
public function retirerprestatairetm($idPrestation)
|
|
{
|
|
$sql = 'delete from prestationcollegetm where (id=?)';
|
|
$this->executerRequete($sql, array($idPrestation));
|
|
}
|
|
|
|
public function majtmprestatairetm($idPrestation, $tm)
|
|
{
|
|
$sql = 'UPDATE prestationcollegetm SET
|
|
tm = ?
|
|
WHERE (id=?)';
|
|
$this->executerRequete($sql, array($tm, $idPrestation));
|
|
}
|
|
|
|
public function majPrimeOpt($idGarantie, $primeOption)
|
|
{
|
|
$sql = 'call sp_maj_prime_opt(?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $primeOption));
|
|
}
|
|
|
|
public function majPlafondOpt($idGarantie, $plafondOption)
|
|
{
|
|
$sql = 'call sp_maj_plafond_opt(?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $plafondOption));
|
|
}
|
|
|
|
public function majlibgar($idGarantie, $libellegarantie)
|
|
{
|
|
$sql = 'update garantie_temp set garantie=? where (id=?)';
|
|
$this->executerRequete($sql, array($libellegarantie, $idGarantie));
|
|
}
|
|
|
|
public function viderCollegTemp($user)
|
|
{
|
|
$sql = 'DELETE FROM college_temp WHERE (user=?)';
|
|
$this->executerRequete($sql, array($user));
|
|
|
|
$sql = 'DELETE FROM garantie_temp WHERE (user=?)';
|
|
$this->executerRequete($sql, array($user));
|
|
|
|
$sql = 'DELETE FROM prestation_temp WHERE (user=?)';
|
|
$this->executerRequete($sql, array($user));
|
|
}
|
|
|
|
public function init_changement_college($idAdherent)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_init_changement_college(?, ?)';
|
|
|
|
$college_temp = $this->executerRequete($sql, array($idAdherent, $user));
|
|
return $college_temp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function changer_college_temp($idAdherent, $idCollege)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_changer_college_temp(?, ?, ?)';
|
|
|
|
$college_temp = $this->executerRequete($sql, array($idAdherent, $idCollege, $user));
|
|
return $college_temp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function changer_college($idAdherent)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_changer_college(?, ?)';
|
|
$this->executerRequete($sql, array($idAdherent, $user));
|
|
}
|
|
|
|
public function getCollegesPolice()
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'SELECT * FROM college_temp WHERE (user=?) order by libelleCollege';
|
|
$college = $this->executerRequete($sql, array($user));
|
|
return $college;
|
|
}
|
|
|
|
public function getTotaldcollege($idPolice) {
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_totalcollege_temp(?, ?)';
|
|
$totalcollege = $this->executerRequete($sql, array($idPolice, $user));
|
|
return $totalcollege->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function selectioncollegetemp($idCollegeTemp, $choix) {
|
|
$sql = 'update college_temp set choix=? WHERE (id=?)';
|
|
$this->executerRequete($sql, array($choix, $idCollegeTemp));
|
|
}
|
|
|
|
public function majplafondadd($idGarantie, $plafond)
|
|
{
|
|
$sql = 'update garantieaddition_temp set plafond=? where (id=?)';
|
|
$this->executerRequete($sql, array($plafond, $idGarantie));
|
|
}
|
|
|
|
public function majprimeadd($idGarantie, $primeOption)
|
|
{
|
|
$sql = 'update garantieaddition_temp set primeOption=? where (id=?)';
|
|
$this->executerRequete($sql, array($primeOption, $idGarantie));
|
|
}
|
|
|
|
public function recapituler_maj_limite_college($idCollegeTemp)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_recapituller_maj_limite_college(?, ?)';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $user));
|
|
}
|
|
|
|
public function modifierlimitecollegetemp($idCollegeTemp)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$sql = 'call sp_modifier_limite_college_temp(?)';
|
|
$this->executerRequete($sql, array($idCollegeTemp, $user));
|
|
}
|
|
|
|
public function getCollegepoliceId($idCollege) {
|
|
$sql = 'SELECT A.id AS idCollege, A.*, B.libelle as produit
|
|
from college A
|
|
join produit B on (B.codeSociete=A.codeSociete) AND (B.codeProduit=A.codeProduit)
|
|
where A.id=?';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getprestatairehorsreseau($idCollege) {
|
|
$sql = 'call sp_getprestatairehorsreseau(?);';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getprestataireenplus($idCollege) {
|
|
$sql = 'call sp_getprestataireenplus(?);';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function supprimeprestataireenplus($id) {
|
|
$user = $_SESSION['login'];
|
|
$sql = 'CALL sp_supprimerprestataireenplus(?, ?)';
|
|
|
|
$this->executerRequete($sql, array($id, $user));
|
|
|
|
}
|
|
|
|
public function supprimeprestataireenmoins($id) {
|
|
|
|
$user = $_SESSION['login'];
|
|
$sql = 'CALL sp_supprimeprestataireenmoins(?, ?);';
|
|
|
|
var_dump($id, $user);
|
|
|
|
$this->executerRequete($sql, array($id, $user));
|
|
|
|
}
|
|
|
|
// Prestataires en moins
|
|
public function getprestatairereseau($idCollege) {
|
|
$sql = 'call sp_getprestatairereseau(?);';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getprestataireenmoins($idCollege) {
|
|
$sql = 'call sp_getprestataireenmoins(?);';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getListeCollege($idPolice) {
|
|
$sql = 'SELECT c.id AS `code`, CONCAT(c.id, " => ", c.codeProduit, " => ", c.libelleCollege, " => (", r.libelle, ")") AS libelle
|
|
FROM college c
|
|
LEFT JOIN reseausoins r ON (r.codeReseau = c.codeReseau)
|
|
WHERE (c.idPolice=?) order by 1 DESC';
|
|
$liste = $this->executerRequete($sql, array($idPolice));
|
|
return $liste->fetchAll(PDO::FETCH_ASSOC);
|
|
// return $liste->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
// Prestataires dérogation TM
|
|
public function getprestatairereseautm($idCollege) {
|
|
$sql = 'call sp_getprestatairereseau_tm(?);';
|
|
|
|
$college = $this->executerRequete($sql, array($idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getprestatairetm($codeReseau, $idCollege) {
|
|
$sql = 'call sp_getprestataire_tm(?, ?);';
|
|
|
|
$college = $this->executerRequete($sql, array($codeReseau, $idCollege));
|
|
return $college->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
|
|
public function majchampapplicationplafond($idGarantie, $choix)
|
|
{
|
|
$sql = 'update garantie_temp set champApplication=?, primeParTete=? where (id=?)';
|
|
$this->executerRequete($sql, array($choix, $choix, $idGarantie));
|
|
}
|
|
|
|
public function changecategorie($codeProduit)
|
|
{
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
// $user = $_SESSION['login'];
|
|
$idCollegeTemp = $_SESSION['idCollegeTemp'];
|
|
|
|
$sql = 'CALL sp_modifier_categorie_college_temp_vitalis(?, ?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollegeTemp, $codeSociete, $codeProduit));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function maj_libelle_college($idCollegeTemp, $nouvLibelleCollege)
|
|
{
|
|
$sql = 'CALL sp_maj_libelle_college(?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollegeTemp, $nouvLibelleCollege));
|
|
}
|
|
|
|
public function getTauxForfait(){
|
|
if (est_anglophone()){
|
|
$sql = "SELECT id AS code, libelleEng AS libelle
|
|
FROM choixforfaittaux;";
|
|
}else{
|
|
$sql = "SELECT id AS code, libelle
|
|
FROM choixforfaittaux;";
|
|
|
|
}
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getPrestatairereseausoins($codeReseau){
|
|
|
|
$codeSociete = $_SESSION['codeSociete'];
|
|
|
|
$sql = 'CALL sp_get_prestataires_reseau_affectes(?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($codeSociete, $codeReseau));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
// 09-01-2023
|
|
public function getgaranties()
|
|
{
|
|
|
|
$sql = "SELECT codeGarantie AS code, codeGarantie AS libelle
|
|
FROM garantie ORDER BY libelle;";
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function majgarantieprestatairetm($idPrestation, $codeGarantie)
|
|
{
|
|
$sql = 'UPDATE prestationcollegetm SET
|
|
codeGarantie = ?
|
|
WHERE (id=?)';
|
|
$this->executerRequete($sql, array($codeGarantie, $idPrestation));
|
|
}
|
|
|
|
public function getaffections()
|
|
{
|
|
if (est_anglophone()){
|
|
$sql = "SELECT codeAffection AS code, libelleEng AS libelle
|
|
FROM p_affection ORDER BY libelleEng;";
|
|
}else{
|
|
$sql = "SELECT codeAffection AS code, libelle
|
|
FROM p_affection ORDER BY libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function majaffectionprestatairetm($idPrestation, $codeAffection)
|
|
{
|
|
$sql = 'UPDATE prestationcollegetm SET
|
|
codeAffection = ?
|
|
WHERE (id=?)';
|
|
$this->executerRequete($sql, array($codeAffection, $idPrestation));
|
|
}
|
|
|
|
//10-01-2023
|
|
|
|
public function getaffectionstm($idCollege)
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
if ($idCollege == "") {
|
|
if (est_anglophone()){
|
|
$sql = "SELECT A.*, B.libelleEng As libelle, C.libelleCollege
|
|
FROM affectioncollegetm A
|
|
JOIN p_affection B ON (A.codeAffection = B.codeAffection)
|
|
JOIN college C ON (C.id = A.idCollege)
|
|
WHERE A.idPolice = ?
|
|
ORDER BY B.libelleEng;";
|
|
}else{
|
|
$sql = "SELECT A.*, B.libelle, C.libelleCollege
|
|
FROM affectioncollegetm A
|
|
JOIN p_affection B ON (A.codeAffection = B.codeAffection)
|
|
JOIN college C ON (C.id = A.idCollege)
|
|
WHERE A.idPolice = ?
|
|
ORDER BY B.libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
}else{
|
|
if (est_anglophone()){
|
|
$sql = "SELECT A.*, B.libelleEng As libelle
|
|
FROM affectioncollegetm A
|
|
JOIN p_affection B ON (A.codeAffection = B.codeAffection)
|
|
WHERE A.idCollege = ?
|
|
ORDER BY B.libelleEng;";
|
|
}else{
|
|
$sql = "SELECT A.*, B.libelle
|
|
FROM affectioncollegetm A
|
|
JOIN p_affection B ON (A.codeAffection = B.codeAffection)
|
|
WHERE A.idCollege = ?
|
|
ORDER BY B.libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql, array($idCollege));
|
|
}
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
}
|
|
|
|
public function ajouteruneaffectiontm($codeAffection, $idCollege, $tm, $appliquerPlafond)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
|
|
$sql = 'call sp_ajouteraffectiontm(?, ?, ?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeAffection, $user, $idCollege, $idPolice, $tm, $appliquerPlafond));
|
|
}
|
|
|
|
public function retireraffectiontm($idAffection)
|
|
{
|
|
$sql = 'delete from affectioncollegetm where (id=?)';
|
|
$this->executerRequete($sql, array($idAffection));
|
|
}
|
|
|
|
|
|
public function majtmaffectiontm($idAffection, $tm)
|
|
{
|
|
$sql = 'UPDATE affectioncollegetm SET
|
|
tm = ?
|
|
WHERE (id=?)';
|
|
$this->executerRequete($sql, array($tm, $idAffection));
|
|
}
|
|
|
|
//18-01-2022
|
|
public function getlistegaranties($idBaremePriseEnCharge)
|
|
{
|
|
$sql = 'SELECT
|
|
id,
|
|
codeGarantie,
|
|
fn_libelle_garantie(codeGarantie) AS garantie,
|
|
fn_libelle_garantie_eng(codeGarantie) AS garantieEng,
|
|
codeTypeRemboursement,
|
|
nbreLimiteGarantie
|
|
FROM garantiesbaremepriseencharge
|
|
WHERE idBaremePriseEnCharge = ?;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idBaremePriseEnCharge));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getTypeRemboursement()
|
|
{
|
|
if (est_anglophone()){
|
|
$sql = "SELECT codeTypeRemboursement AS code, libelleEng AS libelle
|
|
FROM typeremboursement ORDER BY libelleEng;";
|
|
}else{
|
|
$sql = "SELECT codeTypeRemboursement AS code, libelle
|
|
FROM typeremboursement ORDER BY libelle;";
|
|
}
|
|
|
|
$resultat = $this->executerRequete($sql);
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getallgaranties()
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'SELECT
|
|
codeGarantie,
|
|
libelle,
|
|
libelleEng
|
|
FROM garantie
|
|
WHERE codeGarantie NOT IN (
|
|
SELECT codeGarantie
|
|
FROM garantiesremboursementpolice
|
|
WHERE idPolice = ?
|
|
);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getgarantiespolice()
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
|
|
$sql = 'SELECT
|
|
id,
|
|
codeGarantie,
|
|
fn_libelle_garantie(codeGarantie) AS garantie,
|
|
fn_libelle_garantie_eng(codeGarantie) AS garantieEng,
|
|
codeTypeRemboursement,
|
|
nbreLimiteGarantie
|
|
FROM garantiesremboursementpolice
|
|
WHERE idPolice = ?;';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice));
|
|
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function ajouterunegarantiepolice($codeGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
$codeSociete= $_SESSION['codeSociete'];
|
|
|
|
$sql = 'call sp_ajoutergarantiepolice(?, ?, ?, ?)';
|
|
|
|
$this->executerRequete($sql, array($codeSociete, $codeGarantie, $user, $idPolice));
|
|
}
|
|
|
|
//19-01-2023
|
|
|
|
public function majlimitepolice($idGarantie, $nbreLimiteGarantie)
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_majlimitepolice(?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $nbreLimiteGarantie, $idPolice, $user));
|
|
}
|
|
|
|
public function majlimitecollege($idGarantie, $nbreLimiteGarantie)
|
|
{
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_majlimitecollege(?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $nbreLimiteGarantie, $user));
|
|
}
|
|
|
|
|
|
public function majtyperemboursementpolice($idGarantie, $codeTypeRemboursement)
|
|
{
|
|
$idPolice = $_SESSION['idPolice_C'];
|
|
$user = $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_majtyperemboursementpolice(?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $codeTypeRemboursement, $idPolice, $user));
|
|
}
|
|
|
|
public function majtyperemboursementcollege($idGarantie, $codeTypeRemboursement)
|
|
{
|
|
$user= $_SESSION['login'];
|
|
|
|
$sql = 'CALL sp_majtyperemboursementcollege(?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idGarantie, $codeTypeRemboursement, $user));
|
|
}
|
|
//10-04-2023
|
|
public function majplafondaffectiontm($idAffection, $appliquerPlafond)
|
|
{
|
|
$sql = 'UPDATE affectioncollegetm SET
|
|
appliquerPlafond = ?
|
|
WHERE (id=?)';
|
|
$this->executerRequete($sql, array($appliquerPlafond, $idAffection));
|
|
}
|
|
|
|
public function getCollegeTempPoliceAju($idPolice, $user)
|
|
{
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'CALL sp_afficher_college_ajustement_prime(?,?,?)';
|
|
|
|
$collegeTemp = $this->executerRequete($sql, array($idPolice, $user,$idSaisie));
|
|
return $collegeTemp->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getCollegeTempPoliceTotalAju($idPolice, $user)
|
|
{
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'CALL sp_afficher_college_ajustement_prime_total(?,?,?)';
|
|
|
|
$collegeTemp = $this->executerRequete($sql, array($idPolice, $user,$idSaisie));
|
|
return $collegeTemp->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function majprimeajustee($idCollege, $prime, $codeLienParente, $user)
|
|
{
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'CALL sp_calcul_ajustement_prime(?, ?, ?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idCollege, $codeLienParente, $user, $idSaisie, $prime));
|
|
}
|
|
|
|
public function recapitulerprimesajustees($idPolice, $user)
|
|
{
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'CALL sp_recap_ajustement_prime(?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user, $idSaisie));
|
|
}
|
|
|
|
public function enregisterprimesajustees($idPolice, $user)
|
|
{
|
|
$idSaisie = $_SESSION['idSaisie'];
|
|
|
|
$sql = 'CALL sp_avenant_ajustement_prime(?, ?, ?);';
|
|
|
|
$this->executerRequete($sql, array($idPolice, $user, $idSaisie));
|
|
}
|
|
|
|
public function getCollegeLienParenteTempPoliceRen($idPolice, $user)
|
|
{
|
|
$sql = 'call sp_afficher_renouvellement_lien_parente_temp(?, ?);';
|
|
|
|
$resultat = $this->executerRequete($sql, array($idPolice, $user));
|
|
return $resultat->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
}
|