SELECT codeSociete INTO @codeSociete FROM societeuser;
		
	SELECT
	 -- codeGcAssureur,`fn_libelle_garant_rp`(codeGcAssureur) AS garant,-- id,numeroPolice,libellePolice,
	codePrestataire,`fn_libelle_prestataire`(codePrestataire) AS prestataire,
	SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
	,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
	 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
	 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
	+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,
			B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'DECEMBRE'
		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)
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-08-01' AND '2022-11-30')
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete=@codeSociete)
		AND B.valide!=1 AND B.okDecompte!=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codePrestataire,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice
		 
	) depense GROUP BY -- codeGcAssureur,
	codePrestataire-- ,id,numeroPolice,libellePolice
	HAVING TOTAUX>0
	ORDER BY -- codeGcAssureur,
	TOTAUX DESC ;