-- GESTION DES PLAFONDS ADHERENTS
SELECT DISTINCT a.exercieReference,
  a.id  AS id,a.codeSociete AS codeSociete,a.idPolice,a.idCollege,   
   a.idAdherent AS idAdherent,a.numeroAdherent AS numeroAdherent,
  CONCAT(h.nom, " ", IFNULL(h.prenoms,"")) AS Adherent,
 a.codeGarantie AS codeGarantie,a.garantie,
  a.plafondAnnuel AS plafond,a.consommation AS consommation,
  (a.plafondAnnuel - a.consommation) AS solde, IFNULL(b.libelle,a.codeGarantie) AS garantie,
  IFNULL(b.ordre,'99') AS ordre
FROM garantiesbaremepriseencharge C
LEFT JOIN garantieadherent a ON ((C.`codeGarantie` = a.`codeGarantie`))
JOIN garantie b  ON (b.codeSociete = a.codeSociete AND b.codeGarantie = a.codeGarantie)
JOIN adherent h ON (h.codeSociete = a.codeSociete AND h.id = a.idAdherent AND h.idPolice = a.idPolice AND h.idCollege = a.idCollege)
AND (a.plafondAnnuel<>9999999999)  AND (a.consommation<>0)  AND a.consommation>a.plafondAnnuel
WHERE  a.exercieReference='2022'  AND a.idPolice="82"  
AND fn_id_entetecontrat_police(a.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(a.idPolice)

-- GESTION DES PLAFONDS BENEFEFICIAIRE
SELECT a.exercieReference,
  a.id  AS id,a.codeSociete AS codeSociete,a.idPolice,a.idCollege,   
  a.idAdherent AS idAdherent, a.idBeneficiaire AS idBeneficiaire,a.numeroBeneficiaire AS numeroBeneficiaire,
  CONCAT(h.nomBeneficiaire, " ", IFNULL(h.prenomsBeneficiaire,"")) AS Beneficiaire,
 a.codeGarantie AS codeGarantie,a.garantie,
  a.plafondAnnuel AS plafond,a.consommation AS consommation,
  (a.plafondAnnuel - a.consommation) AS solde, IFNULL(b.libelle,a.codeGarantie) AS garantie,
  IFNULL(b.ordre,'99') AS ordre
FROM garantiebeneficiaire a 
JOIN garantie b  ON (b.codeSociete = a.codeSociete AND b.codeGarantie = a.codeGarantie)
JOIN beneficiaire h ON (h.codeSociete = a.codeSociete AND h.idAdherent = a.idAdherent AND h.idPolice = a.idPolice AND h.idCollege = a.idCollege 
AND h.id = a.idBeneficiaire )
JOIN entetecontrat e ON (e.idPolice=a.idPolice)
JOIN actesbaremepriseencharge G ON ((b.codeSociete=G.codeSociete) AND (b.codeGarantie=G.codeGarantie))
WHERE a.idPolice="82"
AND (a.plafondAnnuel<>9999999999)  AND (a.consommation<>0)  AND a.consommation>a.plafondAnnuel
AND fn_id_entetecontrat_police(82,CURDATE())=fn_id_entetecontrat_police_courant(82)
AND a.exercieReference=2022  AND G.codeGarantie='OPT'
-- AND e.id = fn_id_entetecontrat_beneficiaire_courant(639)


-- GESTION DES PLAFONDS PAR LIENS DE PARENTE
SELECT a.exercieReference,
  a.id  AS id,a.codeSociete AS codeSociete,a.idPolice,a.idCollege,   
  a.idAdherent AS idAdherent, a.idBeneficiaire AS idBeneficiaire,a.numeroBeneficiaire AS numeroBeneficiaire,
  CONCAT(h.nomBeneficiaire, " ", IFNULL(h.prenomsBeneficiaire,"")) AS Beneficiaire,
 a.codeGarantie AS codeGarantie,a.garantie,
  a.plafondAnnuel AS plafond,a.consommation AS consommation,
  (a.plafondAnnuel - a.consommation) AS solde, IFNULL(b.libelle,a.codeGarantie) AS garantie,a.codeLienParente,
  IFNULL(b.ordre,'99') AS ordre
FROM garantiecollegelienparente a 
JOIN garantie b  ON (b.codeSociete = a.codeSociete AND b.codeGarantie = a.codeGarantie)
JOIN vw_p_beneficiaire h ON (h.codeSociete = a.codeSociete AND a.idAdherent = a.idAdherent AND a.idPolice = a.idPolice AND a.idCollege = a.idCollege 
AND a.idBeneficiaire = a.idBeneficiaire )
AND (a.plafondAnnuel<>9999999999)  AND (a.consommation<>0)  AND a.consommation=a.plafondAnnuel
AND b.codeGarantie='OPT'
-- ANALYSE AVEC KANE

SELECT   -- 83080
SUM(montantApayer)
-- *
-- fn_id_entetecontrat_police(82,date(datesysteme)) as idEnteteContrat
 FROM p_facture
WHERE idBeneficiaire=1043
AND fn_id_entetecontrat_police(82,DATE(datesysteme))=fn_id_entetecontrat_police_courant(82)
AND codeTypeFacture='PHAR'

SELECT   -- 83080
SUM(montantApayer)

-- fn_id_entetecontrat_police(82,date(datesysteme)) as idEnteteContrat
 FROM  `p_prestationactes`
WHERE idBeneficiaire=1043 -- and facture=0
AND fn_id_entetecontrat_police(82,DATE(datesysteme))=fn_id_entetecontrat_police_courant(82)
AND codeGarantieActe='PHA'



SELECT   -- 83080
SUM(p.montantApayer),SUM(p.fraisRetenu),SUM(p.montantRedresse)  
FROM  `p_prestationactes` p INNER JOIN p_facture b ON (b.id=p.idFacture)
WHERE p.idBeneficiaire=1043 
AND fn_id_entetecontrat_police(82,DATE(b.datesysteme))=fn_id_entetecontrat_police_courant(82)
AND p.codeGarantieActe='PHA'


SELECT * FROM garantiebeneficiaire
WHERE idBeneficiaire=1043
AND fn_id_entetecontrat_police(82,CURDATE())=fn_id_entetecontrat_police_courant(82)
-- AND fn_exercice_beneficiaire(idBeneficiaire,CURDATE())=2022
 AND exercieReference=2022
 AND CodeGarantie='PHA'
