-- 1 Analyse fraude
SELECT * FROM p_prestationactes
WHERE codeUtilisateur='pisam1872'
AND idBeneficiaire=5214
ORDER BY datePrestation


SELECT DISTINCT idBeneficiaire FROM p_prestationactes
WHERE codeUtilisateur='pisam1872'
ORDER BY datePrestation


-- 2 Recherche des bénéficiaires
SELECT b.idPolice,fn_libelle_policeID(b.idPolice) Police,b.idCollege,b.id,b.numeroBeneficiaire,b.nomBeneficiaire ,b.prenomsBeneficiaire 
FROM beneficiaire b
WHERE b.id IN ( 1690,432,1461,1784,5590,21228,23968,4808,6820,4810,12459,24475,15006,20253,24407,
21465,5589,23182,22120,1941,15861,12932,22123,23745,4773,6827,15004,27464,23967	)


-- 3 --recherche global des sinistres
SELECT B.idPolice,fn_libelle_policeID(B.idPolice) AS Police,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	-- B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
INNER JOIN p_facture Bp ON (Bp.id=A.idfacture)
INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
INNER JOIN police Po ON (Po.id=B.idPolice)
INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=Bp.numeroFeuilleMaladie)
INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
WHERE (LEFT(Bp.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31')
AND Bp.codePrestataire  IN ('CE0162') AND A.codeUtilisateur='pisam1872'
AND A.montantApayer>0  
AND  B.id IN ( 1690,432,1461,1784,5590,21228,23968,4808,6820,4810,12459,24475,15006,20253,24407,
21465,5589,23182,22120,1941,15861,12932,22123,23745,4773,6827,15004,27464,23967	)
GROUP BY  B.idPolice,a.id,a.numeroAdherent, a.nom, a.prenoms
ORDER BY B.idPolice,montantRembourse DESC  ;


-- 4 --recherche detaillé des sinistres
SELECT  CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DatePaiement, 
G.codeGcAssureur AS codeGarant,G.libelle AS Garant,CONCAT(`cl`.`nom`,' ',`cl`.`prenoms`) AS Souscripteur,
B.idPolice AS NumInternePolice,P.numeroPolice AS NContrat,P.libellePolice AS Police,a.idCollege AS CodeCollege,a.codeProduit  AS College,
fn_codeNormalise_prestataire_rp(prt.codePrestataire) AS CodePrestataire,fn_libelle_prestataire(prt.codePrestataire) AS Prestataire,
fn_libelle_prestataire(prt.codePrestataire)  AS codePrestatairePrescripteur,a.numeroAdherent AS MatriculeAssurePrincipal,
CONCAT(a.nom,' ',a.prenoms) AS  NomAssurePrincipal,B.numeroBeneficiaire AS MatriculeAssureBeneficiaire,
fn_rech_beneficiaire_numero_sp(B.numeroBeneficiaire) AS NomBeneficiairePrincipal,fn_code_lienparent_rp(B.numeroBeneficiaire) AS LienParente, 	
prt.id AS NPrestation,prt.datePrestation AS DatedeSoins,CASE WHEN prt.tiersPayant='1' THEN 'TP' ELSE 'RD' END AS TypePrestation,
B.numeroDecompte AS NReglement,prt.idfacture AS NRemboursement,F.codeAffection AS Affection, 
CASE WHEN prt.codeActe='CSM' THEN 'HOSPITALISATION' ELSE  fn_libelle_familleacte(prt.codeActe) END AS FamillePrestation,
prt.codeActe AS CodeActe,prt.quantite AS Quantité
,IFNULL(prt.fraisReel,0) AS MontantReclame,(IFNULL(prt.fraisExclu,0)+IFNULL(prt.montantRedresse,0)) AS MontantExclus
,IFNULL(prt.fraisRetenu,0) AS BaseRemboursement,IFNULL(prt.tm,0) AS TM
,IFNULL(prt.montantTM,0) AS MontantTM,IFNULL(prt.montantApayer,0) AS MontantPaye
FROM p_prestationactes prt INNER JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN beneficiaire f ON (f.id=prt.idBeneficiaire )	
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31')
AND  B.codePrestataire  IN ('CE0162') AND prt.codeUtilisateur='pisam1872'
AND  f.id IN ( 1690,432,1461,1784,5590,21228,23968,4808,6820,4810,12459,24475,15006,20253,24407,
21465,5589,23182,22120,1941,15861,12932,22123,23745,4773,6827,15004,27464,23967	)
ORDER BY   DatePaiement,B.idPolice, a.idCollege,a.numeroAdherent,B.numeroBeneficiaire,datedeSoins;


SELECT * FROM police WHERE id=229



