SELECT * FROM police WHERE libellePolice LIKE "%tsp%"


-- A---  GESTION DES PROVISOIRES PAR GARANT

CALL `sp_evolution_depenseProvisoireGarant_rp` (NULL,165,NULL,NULL,"2023-01-01","2023-12-31")

-- B---  GESTION DES PROVISOIRES PAR GARANT / POLICE

CALL `sp_evolution_depenseProvisoirePolice_rp` (NULL,NULL,NULL,NULL,"2023-01-01","2023-12-31")


-- C---  GESTION DES VALIDES PAR GARANT

CALL sp_evolution_depenseGARANT_rp(NULL,NULL,NULL,NULL,"2023-01-01","2023-05-31"); --  EVOLUTION DES DEPENSES PAR GARANT /

CALL sp_evolution_depenseGaPolice_rp(NULL,NULL,NULL,NULL,NULL,"2023-01-01","2023-05-31"); --  EVOLUTION DES DEPENSES PAR GARANT / POLICE

-- VERIFICATION DES MONTANTS TOTAUX
CALL sp_evolution_depenseTotale_rp("2023-01-01","2023-05-31"); --  EVOLUTION MENSUELLES DES DEPENSES /


SELECT * FROM clients WHERE nom LIKE '%khamus%'


SELECT p.codeGcAssureur,p.* FROM police p WHERE p.libellePolice LIKE '%khamus%'

SELECT * FROM prestataire WHERE libelle LIKE '%tsp%'


SELECT * FROM prestataire
WHERE codePrestataire='CE1441'


-- PARAMETRES VALIDES
SET @garant='GNA',@idClient=(NULL),@idPolice=168,@idCollege=(NULL),@codeApporteur=(NULL);
SET @dateDebut="2023-01-01",@dateFin="2023-03-31";
SET @XbonPha=(NULL);
SET @seuilPlafond=(NULL);
SET @prest=(NULL);
SET @garantie=(NULL);
SET @acte=(NULL);
SET @adherent=(NULL);
SET @beneficiaire=(NULL);
-- PR0544



-- 669 591
-- MONTANT REFERENT VALIDES   -- 17 613 999  -- 41 828 589   -- 12 826 144
SELECT 
 -- prt.*
 -- B.idPolice,e.idCollege ,fn_libelle_collegeID(e.idCollege ) AS college,
 -- ,prt.id ,IFNULL(prt.montantApayer,0) AS  montant
SUM(IFNULL(prt.fraisReel,0)) AS reclamer,SUM(IFNULL(prt.montantApayer,0)) AS Apayer,COUNT(DISTINCT prt.idBeneficiaire) AS NbrTraite
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
-- INNER JOIN p_reglement N ON (B.`numeroDecompte` = N.numeroDecompte)
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 @dateDebut AND @dateFin)
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 e.codeLienParente="E" 230 553 901
AND B.valide=1 AND B.okDecompte=1  AND prt.ententePrealable NOT IN ('2','9') -- 15 808 695  -- 17 758 071
 AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
-- AND B.codePrestataire IN ('CE02194')
GROUP BY  B.idPolice,e.idCollege -- 2 753 876
ORDER BY Apayer DESC



-- A---  GESTION DES SINISTRES VALIDES PERIODES
CALL sp_evolution_depense_rp  (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie,@prest)

CALL sp_evolution_nombreActes_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie,@prest)

CALL sp_indicateurTBSinistre_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);
 
CALL sp_etat_detaille_depense_famAssure_rpNEWS(@garant,@idClient,@idPolice,@idCollege,@beneficiaire,@dateDebut,@dateFin,@prest);	



-- PAR FAMILLE
CALL sp_ft6_depense_familleAss_rp  (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,0); -- FT6 :DEPENSE DE TOUTES LES DEPENSES/CONTRAT

-- A. -- RAPPORT SINISTRE / POLICE / RSP  
-- CALL sp_s_rapportsp_periode_export_rp(@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,"QTM",30) -- QUE LES COMPAGNIES
CALL sp_ft1_donnelemn2_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,30,'AVANT');   -- FT1B :DONNEES ELEMENTAIRES  -- 30 687 688 / CHARGEMENT* -- 

CALL sp_s_rapportsp_periodeAuto_rp (@garant,@idPolice,@dateDebut,@dateFin,30);   -- 193

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





-- A.0 -- Evolution mensuelles des sinistres
CALL sp_evolution_depenseGaPolice_rp (@garant,@idClient,@idPolice,@idCollege,@codeApporteur,@dateDebut,@dateFin);

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



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

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

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

/*
-- EVOLUTION MENSUELLE DE TOUTES LES PRESTATIONS
-- ACTE EFFECTUE
CALL sp_evolution_nbrActeGaPolice_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@garantie); -- NBRE ENTENTE ACTES


-- ENTENTE PREALABLE
CALL sp_evolution_nbrEntenteGaPolice_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin); -- NBRE ENTENTE PREALABRE

*/
-- AND prt.tiersPayant='1'

-- A.1 -- CUMUL PAR TYPE DE PRESTATAIRE VALIDES
CALL sp_fiche_suivie_type_prest_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);

-- A.2 -- CUMUL PAR PRESTATAIRE VALIDES
CALL sp_fiche_suivie_prestataire_Valide_rp (@garant,@idClient,@idPolice,NULL,@idCollege,@dateDebut,@dateFin);


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


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



CALL sp_fiche_suivie_prestataireGarant_rp(@prest,@dateDebut,@dateFin);

-- A.3 -- CUMUL PAR ADHERENT VALIDES
CALL sp_fiche_suivie_adherent_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);

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

-- 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,@garantie);

-- A.5 -- DETAILS DES SINISTRES VALIDES DES PRESTATIONS
CALL sp_fiche_suivie_detSinistre_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@adherent,@beneficiaire,@prest);


-- A.6 -- DETAILS DES SINISTRES (¨RESCRIPTION MEDICAMENTEUSES) VALIDES DES PRESTATIONS
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);


-- LAST_DAY(DATE_ADD(CURDATE(), INTERVAL -1 MONTH))
-- 


-- PARAMETRES VALIDES
-- PARAMETRES VALIDES
SET @garant='GNA',@idClient=(NULL),@idPolice=168,@idCollege=(NULL),@codeApporteur=(NULL);
SET @dateDebut="2023-01-01",@dateFin="2023-03-31";
SET @XbonPha=(NULL);
SET @seuilPlafond=0;
SET @garantie=(NULL);
SET @acte=(NULL);
SET @adherent=(NULL);
SET @beneficiaire=(NULL);

-- A.7 -- SUIVI HOSPITALISATION / PRESTATAIRES
CALL sp_fiche_suivie_Hospit_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);

-- A.8 -- SUIVI HOSPITALISATION / POICE
CALL sp_fiche_suivie_HospitPolice_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);

-- A.9 -- SUIVI EVALUATION HOSPITALISATION TOTAL
 CALL sp_evolution_hospitValide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);

-- A.10 -- SUIVI DETAIL HOSPITALISATION
CALL sp_fiche_suivie_DetailHospit_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);

-- A.11 -- SUIVI EVALUATION PAR AFFECTION
CALL sp_ft14_depense_AffecHospiT_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin);
 
 
-- A.12 -- SUIVI DETAIL AMBULATOIRE
CALL sp_fiche_suivie_DetailAmbulatoire_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);

-- A.13 -- SUIVI EVALUATION AMBULATOIRE TOTAL
CALL sp_fiche_suivie_Ambulatoire_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);
 
 
 -- A.12 -- SUIVI DETAIL MATERNITE
CALL sp_fiche_suivie_DetailMaternite_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);

-- A.13 -- SUIVI EVALUATION MATERNITE TOTAL
CALL sp_fiche_suivie_Maternite_Valide_rp (@garant,@idClient,@idPolice,@idCollege,@dateDebut,@dateFin,@seuilPlafond);
 
-- sp_fiche_suivie_DetailMaternite_Valide_rp

-- B --  GESTION DES SINISTRES PROVISOIRES PERIODES

-- PARAMETRES PROVISOIRES
SET @garantp=(NULL),@idClientp=(NULL),@idPolicep=127,@idCollegep=(NULL);
SET @dateDebutp="2023-04-01",@dateFinp="2023-05-31";
SET @XbonPhap=(NULL)
SET @seuilPlafondp=(NULL);
SET @adherent=(NULL);
SET @beneficiaire=(NULL);

-- MONTANT REFERENT PROVIDOIRES  -- 289 278 639
SELECT SUM(IFNULL(prt.fraisReel,0)) AS reclamer,SUM(IFNULL(prt.montantApayer,0)) AS Apayer
FROM p_prestationactes prt 
INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
INNER JOIN adherent a ON (a.id=e.idAdherent)
INNER JOIN  police C ON  (e.idPolice = C.id)
INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
WHERE (LEFT(prt.dateSysteme,10) BETWEEN @dateDebutp AND @dateFinp)
AND (CASE WHEN @garantp IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur=@garantp END)  
AND (CASE WHEN @idPolicep IS NULL THEN e.idPolice IS NOT NULL ELSE e.idPolice=@idPolicep END)  
AND (CASE WHEN @idClientp IS NULL THEN C.idClient IS NOT NULL ELSE C.idClient=@idClientp END)  
AND (CASE WHEN @idCollegep IS NULL THEN e.idCollege IS NOT NULL ELSE e.idCollege=@idCollegep END)
AND G.id!='45' AND C.idClient!='49' 
AND prt.ententePrealable NOT IN ('2','9')  
AND prt.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') ;

-- ('CE1726','CE1725')

-- B.1 -- CUMUL PAR TYPE DE PRESTATAIRE PROVISOIRES
CALL sp_fiche_suivie_type_prest_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp);

-- B.2 -- CUMUL PAR PRESTATAIRE PROVISOIRES
CALL sp_fiche_suivie_type_prest_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp);

-- B.3 -- CUMUL PAR ADHERENT PROVISOIRES
CALL sp_fiche_suivie_adherent_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp);

-- B.4 -- CUMUL PAR BENEFICIAIRE AYANT X BON PHARMACIES PROVISOIRES
CALL sp_fiche_suivie_benefXbon_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp,@XbonPhap);

-- B.5 -- DETAILS DES SINISTRES PROVISOIRES DES PRESTATIONS
CALL sp_fiche_suivie_detSinistre_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp,@adherent,@beneficiaire);

-- B.6 -- DETAILS DES MEDICAMENTEUSES PROVISOIRES DES PRESTATIONS
CALL sp_fiche_suivie_detMedicament_Provis_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@dateDebutp,@dateFinp);



-- C --  GESTION DES ALERTES DES PLAFONDS

-- C.1 -- ALERTE PLAFOND FAMILLE ADHERENT
CALL sp_alerte_depense_plfFamille_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@seuilPlafondp);
-- C.2 -- ALERTE PLAFOND INDIVIDUEL BENEFICIAIRE
CALL  sp_alerte_depense_plfInd_rp (@garantp,@idClientp,@idPolicep,@idCollegep,@seuilPlafondp);
-- C.2 -- ALERTE PLAFOND GARANTIE INDIVIDUEL BENEFICIAIRE
CALL  sp_alerte_depGarantie_plfFamille_rp(@garantp,@idClientp,@idPolicep,@idCollegep,@seuilPlafondp);
	