
-- procédure decompte INTERSANTE
CALL  `sp_perso_decompte_periode_tp`('2022','09')


-- Procedure decompte par prestataire
CALL sp_r_get_reglement (1893)

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

-- PROCEDURE RAF PRESTATAIRE  VISALISATION DES INFOSMATIONS DE PAIEMENT

CALL  `sp_perso_regl_RAF_rp`(NULL,'2022-12-01','2022-12-31',1)   / -- 1er Param :  Le code du prestataire  et en Dernier  1 pour Reglé et 0 non reglé

-- (SUM(B.montantArembourser)-SUM(B.montantRedresse)) AS montantApayerTotal,



	SELECT 
		'2022-12-01' AS debut,'2022-12-31' AS Fin,A.codePrestataire,fn_libelle_prestataire(A.codePrestataire) AS prestataire,
		SUM(IFNULL(prt.montantApayer,0)) AS Apayer,
		A.numeroReglement,A.userPaiement,M.libelle,A.referencePaiement,
		A.codeBanquePayeur,A.dateReglement,fn_libelle_ordre_reglement_prestataire(A.codePrestataire) AS  OrdrePaiement  		
	FROM  p_prestationactes prt 	
	INNER JOIN p_facture B ON (B.id=prt.idfacture) 
	LEFT JOIN p_reglement A ON (B.numeroDecompte = A.numeroDecompte)
	LEFT JOIN modepaiement M ON (M.codeModePaiement = A.codeModePaiement)	
	WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-12-01'  AND '2022-12-31')
	-- AND (CASE WHEN p_codePrestat IS NULL THEN B.codePrestataire IS NOT NULL ELSE B.codePrestataire=p_codePrestat END)  
	-- AND (CASE WHEN p_numero IS NULL THEN A.numeroDemandeReglement IS NOT NULL ELSE A.numeroDemandeReglement=p_numero END)  
	AND (A.tiersPayant="1") AND B.valide=1 AND B.okDecompte=1 
	-- AND (CASE WHEN 1 IS NULL THEN A.regle IS NOT NULL ELSE A.regle=1 END)  
	AND B.codePrestataire IN ('CE0162')
	GROUP BY  A.codePrestataire,A.numeroReglement,A.userPaiement,M.libelle,
		  A.referencePaiement,A.codeBanquePayeur,A.dateReglement 	
	HAVING SUM(IFNULL(prt.montantApayer,0))!=0
	ORDER BY Apayer DESC;


-- detail prestataire / chèque
SELECT  CONCAT(A.codeExercice,"-",A.codeMois,"-",(CASE 
	WHEN A.codeMois IN ("01","03","05","07","08","10","12") THEN "31"
	WHEN A.codeMois IN ("04","06","09","11") THEN "30"
	WHEN A.codeMois IN ("02") THEN 
	(CASE WHEN (A.codeExercice%4=0 AND A.codeExercice%100!=0 OR A.codeExercice%400=0)
	THEN "29"ELSE "28"END )END)) AS dateComptable,
	A.codeMois AS Mois,A.codeExercice,
	A.numeroDecompte,A.numeroReglement,
	A.codePrestataire,fn_libelle_prestataire(A.codePrestataire) AS prestataire,
	A.montantAPayer AS MontantCheque,	
	C.montantPaye AS MontantTotReglement,
	C.codeModePaiement,C.codeBanquePayeur, 
	C.referencePaiement,C.dateReglement,C.heureReglement,C.valide,
	A.regle,A.userPaiement	
FROM p_reglement A
JOIN p_facture B ON (B.`numeroDecompte` = A.numeroDecompte)
LEFT JOIN r_paiementdecompte C ON (C.id=A.idPaiementDecompte)
JOIN p_feuillemaladie F ON ((F.codeSociete=A.codeSociete) AND (B.numeroFeuilleMaladie=F.numeroFeuilleMaladie)
AND (B.codePrestataire=F.codePrestataire) AND (B.id=F.idFactureFeuille))
WHERE A.`codeExercice` = 2022
-- AND A.`codeMois` = 02
-- AND (CASE WHEN "CE0396" IS NULL THEN A.codePrestataire IS NOT NULL ELSE A.codePrestataire = "CE0396" END)  
AND (A.tiersPayant="1") AND (A.fraisRetenu>"0") AND C.montantPaye>0
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY A.codePrestataire,A.codeMois,A.codeExercice
-- HAVING (SUM(B.montantArembourser)-SUM(B.montantRedresse))=690254
ORDER BY MontantCheque DESC;

