SELECT
	 CONCAT ('0',MONTH(LEFT(B.dateSysteme,10)),'/',YEAR(LEFT(B.dateSysteme,10))) AS DateCloture, 
	G.id AS Numero,G.codeGcAssureur AS CodGar,G.libelle AS Garant,
	cl.id AS CodeSouscr, 
	CONCAT(`cl`.`nom`,' ',`cl`.`prenoms`) AS Souscripteur,
	B.idPolice AS N,C.libellePolice AS Police,a.idCollege AS codeCollege,fn_libelle_collegeID(a.idCollege) AS College,
	-- e.id AS idPatient,
	e.numeroBeneficiaire AS MatriculeP,'' MatricSouscP,
	CASE WHEN (e.numeroSociete IS NULL OR e.numeroSociete=(NULL)) AND e.numeroMatricule<>'' THEN IFNULL(e.numeroMatricule,'') ELSE IFNULL(e.numeroMatricule,'')END AS MatricSocieP,
	CONCAT(e.nomBeneficiaire,'',e.prenomsBeneficiaire) Patient,
	e.codeLienParente AS Statut,e.dateNaissance, 
	-- a.id AS idAdherent,
	a.numeroAdherent AS MatriculeA,'' MatricSouscA,
	CASE WHEN (a.numeroSociete IS NULL OR a.numeroSociete=(NULL)) AND a.numeroMatricule<>'' THEN IFNULL(a.numeroMatricule,'') ELSE IFNULL(a.numeroMatricule,'') END AS MatricSocieA,
	CONCAT(a.nom,'',a.prenoms)Assure,		
	fn_libelle_prestataire(prt.codePrestataire) AS centreExecutant,
	CASE WHEN prt.codeActe='PH' THEN fn_libelle_prestataire(f.codePrestataire) ELSE  fn_libelle_prestataire(prt.codePrestataire) END AS centrePrescripteur, 
	fn_libelle_garantie(prt.codeGarantie) AS Prestations,
	prt.codeActe AS codeActe,
	-- fn_libelle_acte_rp(prt.codeActe) AS Actes,
	`fn_libelle_medecin_rp`(f.codeMedecin) AS Medecin,prt.datePrestation AS DatedeSoins,
	ta.CodeTypeAffection,ta.libelle AS TypeAffection,f.codeAffection,	
	CASE WHEN prt.tiersPayant='1' THEN 'TP' ELSE 'RD' END AS TypePrestation,
	IFNULL(prt.fraisReel,0) AS Reclamé,IFNULL(prt.baseRemboursement,0) AS BaseDecomptée,IFNULL(prt.tm,0) AS tm,
	(IFNULL(prt.fraisExclu,0)+IFNULL(prt.montantRedresse,0)) AS Exclusion,
	IFNULL(prt.depassement,0) AS Depassement,IFNULL(prt.montantApayer,0) AS Remboursé,IFNULL(prt.montantTM,0) AS Part_Patient
FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police C ON  (B.idPolice = C.id)
INNER JOIN clients cl ON (cl.id=C.idClient) AND cl.id<>'49'
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)
INNER JOIN p_affection af ON (f.codeAffection=af.codeAffection)
INNER JOIN p_typeaffection ta ON (ta.CodeTypeAffection=af.CodeTypeAffection)	
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-01-31')
AND (CASE WHEN 'GNA' IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur='GNA' END)  
AND G.id!='45' AND C.idClient!='49' 
AND B.valide=1 AND B.okDecompte=1 AND prt.ententePrealable NOT IN ('2','9')
ORDER BY DateCloture,G.id,cl.id,B.idPolice,e.id,DatedeSoins


