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,fn_libelle_acte_rp(prt.codeActe) AS acte,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-10-31')
AND prt.codePrestataire IN ('CE1629') -- sisangue(CE1629),bethani(CE1258),miennoh(CE1257),TSP TONGON(CE1376),MBENGUE(CE1382)   --- ,'CE1382'
-- AND prt.codeGarantie IN ('HOS','MAT')  -- 
-- AND prt.numeroBeneficiaire IN ('0156182701')
-- AND a.numeroAdherent IN ('01561827')
-- AND B.idPolice IN (208,148,167)
AND G.id!='45'  AND (B.codeSociete="vitalis")   -- AND prt.codeActe IN ('C')
 -- AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
-- AND B.codePrestataire IN ('CE1629') 
ORDER BY   DatePaiement,B.idPolice, a.idCollege,a.numeroAdherent,B.numeroBeneficiaire,datedeSoins;





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


-- conso groupée

	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-08-31')
	AND Bp.codePrestataire IN ('CE1629')
	AND A.montantApayer>0  
	-- AND A.codeActe='C'
	-- AND A.numeroAdherent IN ('01561827')
	-- AND B.idPolice IN (82)
	GROUP BY  B.idPolice -- ,a.id,a.numeroAdherent, a.nom, a.prenoms
	-- ,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
	-- ORDER BY  B.idPolice,idAdherent, -- ,idbenef
	ORDER BY montantRembourse DESC  ;
	
	
	
	SELECT 
	B.idPolice,fn_libelle_policeID(B.idPolice) AS Police,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	SUM(IFNULL(prt.fraisReel,0)) AS montantReclame,SUM(IFNULL(prt.montantApayer,0)) AS montantRembourse
	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))
	JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
	JOIN typeprestataire t ON (t.codeTypePrestataire=pr.codeTypePrestataire)	
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-05-31')
		AND B.idPolice IN (229)
	AND G.id!='45' AND C.idClient!='49'    AND (B.codeSociete="vitalis")
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175','CE1276')
	GROUP BY  B.idPolice,a.id,a.numeroAdherent, a.nom, a.prenoms
	ORDER BY montantRembourse DESC  ;
	
	
	SELECT * FROM police WHERE id=229

	

