SET @garant=(NULL),@idClient=(NULL),@idPolice=156,@idCollege=(NULL),@codeApporteur=(NULL);
SET @dateDebut="2023-01-01",@dateFin="2023-03-31";
SET @XbonPha=3;
SET @seuilPlafond=20000;
SET @prest=(NULL);
SET @garantie=(NULL);
SET @acte=(NULL);
SET @adherent=(NULL);
SET @beneficiaire=(NULL);   -- 13 928 438


-- MONTANT REFERENCE
SELECT 
 SUM(IFNULL(prt.fraisReel,0)) AS reclamer,SUM(IFNULL(prt.montantApayer,0)) AS Apayer,COUNT(DISTINCT prt.idBeneficiaire) AS NbrTraite
 -- prt.*
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN  police C ON  (B.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie   AND F.idPolice = C.Id)
-- -- INNER JOIN p_affection af ON (F.codeAffection=af.codeAffection)
WHERE (LEFT(B.dateSysteme,10) BETWEEN "2023-01-01" AND "2023-03-31")
AND (CASE WHEN @garant IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur=@garant END)  
AND (CASE WHEN @idPolice IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=@idPolice END)  
AND (CASE WHEN @idClient IS NULL THEN C.idClient IS NOT NULL ELSE C.idClient=@idClient END)  
AND (CASE WHEN @idCollege IS NULL THEN e.idCollege IS NOT NULL ELSE e.idCollege=@idCollege END)  
AND (CASE WHEN @prest IS NULL THEN B.codePrestataire IS NOT NULL ELSE B.codePrestataire=@prest END)  
AND G.id!='45' AND C.idClient!='49' -- AND prt.montantApayer!=0
-- AND prt.codeGarantie='CON'
 -- AND prt.ententePrealable NOT IN ('2','9')
AND B.valide=1 
AND B.okDecompte=1 -- AND prt.tierspayant='0'
 AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')




SELECT * FROM police WHERE libellePolice LIKE '%scc%'

SELECT * FROM contrat WHERE idPolice=82


SELECT * FROM avenant WHERE idPolice=106


SELECT * FROM garantie
-- PR0544

-- 0 --- RSP PAR POLICE  //  RSP PAR COLLEGE
CALL sp_s_rapportsp_periodeAutoGarant_rp (@garant,'2023-01-01','2023-03-31',20); SELECT * FROM temp_rsp;  -- NIVEAU GARANT

CALL sp_s_rapportsp_periodeAutoNews_rp (@garant,@idPolice,@dateDebut,@dateFin,30); SELECT * FROM temp_rsp;  -- 193  -- NIVEAU POLICE

CALL sp_s_rapportsp_periodeAutoCollege_rp (@garant,@idPolice,@codeApporteur,@dateDebut,@dateFin,30); SELECT * FROM temp_rsp;  -- NIVEAU COLLEGE

-- 1 --- EvolutionPolice
CALL sp_evolution_depenseGaPolice_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin);


-- 2 --EvolutionTypePrestataire
CALL sp_fiche_suivie_type_prest_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);

CALL sp_evolution_typePrestataire_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie);


-- 3 --Evolution/Prestataire

CALL sp_fiche_suivie_prestataire_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin);

CALL sp_evolution_prestataire_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin,@garantie);

-- 4-- EvolutionGarantie
CALL sp_evolution_depenseGarantiePolice_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin,@garantie);

-- 5-- EvolutionStatut
CALL sp_evolution_depenseStatusBenef_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin,@acte);

-- 6 --EvolutionAdherent
CALL sp_ft7_evolution_depfamilleAss_rp(@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,0);

-- 7 -- NOMBRES ACTES / DEPENSES PAR GARANTIES
CALL sp_evolution_nbrActeGaPolice_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie); -- NBRE ENTENTE ACTES

CALL sp_evolution_depenseGarantieActePoliceNews_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie); -- MONTANT

-- 8 -- MEDICAMENTS PLUS D'UN MONTANT
CALL sp_fiche_suivie_detMedicament_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);


CALL sp_fiche_suivie_medCourt_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);


-- 9 --/ ACTES/PRESTATAIRES  -- CE0165   CE1257  CE1242
CALL sp_evolution_actePrestataire_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,NULL,@garantie,@acte); -- ACTES/PRESTATAIRES


-- 10 --PRIME POLICE ET PRIME POLICE /COLLEGE
-- CALL sp_s_rapportsp_periodeAuto_rp (NULL,184,'2022-01-01','2022-11-30',20);SELECT * FROM temp_rsp;  


-- CALL sp_s_rapportsp_periodeAutoCollege_rp (NULL,193,'2022-01-01','2022-11-30',20);  

-- CURSEUR
/*CALL sp_s_rapportsp_periodeAutoGarant_rp (NULL,NULL,'I00003',@dateDebut,@dateFin,20); 

CALL sp_s_rapportsp_periodeAutoGarantCollege_rp (NULL,NULL,'I00003',@dateDebut,@dateFin,30); */


-- 11 --BENEFICIAIRE AYANT DES PRESTATIONS DE PLUS DE X FCFA
CALL sp_fiche_suivie_benefDepSup_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,200000);

-- 12 --BENEFICIAIRE AYANT DES AFFECTIONS CHNONIQUES
CALL sp_fiche_suivie_AffecChronique_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,NULL);

-- 13 --BENEFICIAIRE AYANT DES AFFECTIONS CHNONIQUES EN MEDICAMENTS
CALL sp_fiche_suivie_AffecChroniquePharmacie_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,NULL);


-- A.4 -- CUMUL PAR BENEFICIAIRE AYANT X BON PHARMACIES VALIDES
CALL sp_fiche_suivie_benefXbon_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@XbonPha,@prest,NULL);




SELECT DISTINCT  `codeTypePrestation`
--  COUNT(0),COUNT(DISTINCT idAdherent),COUNT(DISTINCT idBeneficiaire)
FROM  `p_prestationactes`


SELECT  
 COUNT(idAdherent) 
FROM temp_facture_medu
































SELECT * FROM police WHERE libellePolice LIKE '%TGCC%'


SELECT * FROM primecollege WHERE idPolice=193


SELECT * FROM primecollege WHERE idPolice=184


SELECT * FROM college WHERE idPolice=184


SELECT * FROM produit
WHERE codeProduit='TGCC FAMILLE 100% CI'



UPDATE primecollege SET prime=340000 WHERE idPolice=193 AND codeLienParente='A'

