
-- Création des colonnes pour l'ientification des beneficiaires facturer et/ou Ristourner
ALTER TABLE beneficiaire ADD COLUMN factureVitalis BOOLEAN DEFAULT 0;

SELECT * FROM beneficiaire WHERE dateNaissance='0000-00-00';

ALTER TABLE beneficiaire ADD COLUMN ristourneVitalis BOOLEAN DEFAULT 0;

 -- B -- POUR LES RISTOURNES
SELECT A.*
FROM beneficiaire A
JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
JOIN police p ON (p.id=A.idPolice)
JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
JOIN college C ON (C.id=A.idCollege)
LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))	
WHERE  A.id<>0  
AND (CASE WHEN 66 IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice=66 END)  	
AND (CASE WHEN 'R' IS NULL THEN A.codeEtatBeneficiaire IS NOT NULL ELSE A.codeEtatBeneficiaire='R' END)  --  pour les retires
AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= A.dateEffet ELSE A.dateSortie  IS NULL END 	
AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND p.codeEtatPolice NOT IN ('RE','EX','AN') AND g.id!='45' AND p.idClient!='49' 
AND (A.dateSortie BETWEEN p.dateEffet AND p.dateFin)
AND (A.dateEffet BETWEEN p.dateEffet AND p.dateFin) AND ristourneVitalis=0

-- C -- POUR LES ENCAISSEMENTS
SELECT A.*
FROM beneficiaire A
JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
JOIN police p ON (p.id=A.idPolice)
JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
JOIN college C ON (C.id=A.idCollege)
LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))	
WHERE A.idPolice>0
AND (CASE WHEN 66 IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice=66 END)  	
AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.id!='45' AND p.idClient!='49' 
AND p.codeEtatPolice NOT IN ('RE','EX','AN')
AND (A.dateEffet BETWEEN p.dateEffet AND p.dateFin) AND factureVitalis=0


-- D -- MISE A JOUR DES BENEFICAIRES RITOURNES POUR NE PLUS LES PRENDRE EN COMPTE
UPDATE beneficiaire A
JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
JOIN police p ON (p.id=A.idPolice)
JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
JOIN college C ON (C.id=A.idCollege)
LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))	
SET A.ristourneVitalis=1
WHERE  A.id<>0  
AND (CASE WHEN 66 IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice=66 END)  	
AND (CASE WHEN 'R' IS NULL THEN A.codeEtatBeneficiaire IS NOT NULL ELSE A.codeEtatBeneficiaire='R' END)  --  pour les retires
AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= A.dateEffet ELSE A.dateSortie  IS NULL END 	
AND A.nomBeneficiaire NOT LIKE "%AVENANT%" AND p.codeEtatPolice NOT IN ('RE','EX','AN') AND g.id!='45' AND p.idClient!='49' 
AND (A.dateSortie BETWEEN p.dateEffet AND p.dateFin)
AND (A.dateEffet BETWEEN p.dateEffet AND p.dateFin) AND ristourneVitalis=0


-- E -- MISE A JOUR DES BENEFICAIRES ENCAISSE POUR NE PLUS LES PRENDRE EN COMPTE
UPDATE  beneficiaire A
JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
JOIN police p ON (p.id=A.idPolice)
JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
JOIN college C ON (C.id=A.idCollege)
LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
LEFT JOIN primecollege r ON ((r.codeLienParente=A.codeLienParente) AND (r.idPolice=p.id) AND (r.idCollege=A.idCollege))	
SET A.factureVitalis=1
WHERE A.idPolice>0
AND (CASE WHEN 66 IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice=66 END)  	
AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.id!='45' AND p.idClient!='49' 
AND p.codeEtatPolice NOT IN ('RE','EX','AN')
AND (A.dateEffet BETWEEN p.dateEffet AND p.dateFin) AND factureVitalis=0

