-- 0-- AFFICHAGE DETAIL COURRIER PRESTATAIRE / REFERENCE PAIEMENT 
CALL sp_fiche_suivie_prestataireGarant_rp('PR0965','2023-03-01','2023-03-31');

CALL sp_fiche_suivie_prestataireGarant_rp1('PR0965','2023-03-01','2023-03-31',NULL);

CALL sp_p_fiche_suivie_prestataireGarant_rp('PR0965',11304,'202303L001910');  --  NOUVELLE EDITION LETTRE DE CHEQUE MARC



-- 1-- PAIEMENT DES PRESTATAIRES / CHEQUE,VIREMENT,ESPECE,AUTRES
-- CALL sp_cheque_vitalisSante_rp('2022-02-01','2022-02-28','CH','BOA',147896523,'lionel','CE0354')


CALL sp_cheque_vitalisSanteNum_rp('2022-02-01','2022-02-28','BOA',147896523,'lionel',1063)  -- AVEC LE NUMERO DE REGLEMENT
 
-- 2-- AFFICHAGE DETAIL TOUS LES PRESTATAIRES / REFERENCE PAIEMENT 
 CALL sp_perso_regl_RAF_rp('11295','2023-03-01','2023-03-31',1);  -- numeroDemandeReglement

	
	SELECT 
		/*'2023-03-01' AS debut, '2023-03-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.numeroDemandeReglement,
		A.codeBanquePayeur,A.dateReglement,fn_libelle_ordre_reglement_prestataire(A.codePrestataire) AS  OrdrePaiement  */	
		 prt.*	
		SUM(IFNULL(prt.montantApayer,0)) AS Apayer
	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 '2023-03-01' AND '2023-03-31')
	 AND (CASE WHEN 'PR0965' IS NULL THEN B.codePrestataire IS NOT NULL ELSE B.codePrestataire='PR0965' 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 prt.ententePrealable NOT IN ('2','9')
	AND (CASE WHEN 1 IS NULL THEN A.regle IS NOT NULL ELSE A.regle=1 END)  
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	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;
		
		


-- CE0354 FARAH


-- CE0162 PISAM

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

SELECT  SUM(B.montantApayer)
 -- B.* 
FROM p_facture B 
-- LEFT JOIN r_paiementdecompte C ON (C.id=A.idPaiementDecompte)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-02-01' AND '2023-02-28')
AND B.codePrestataire='CE1716'


SELECT   -- A.codePrestataire,R.numeroDemandeReglement,
		CASE A.codeTypeFacture
			WHEN "CSO" THEN F.numeroBonConsultation
			WHEN "PHAR" THEN F.numeroBonOrdonnance
			WHEN "OPT" THEN F.numeroBonOptique
			WHEN "LAB" THEN F.numeroBonExamen
			WHEN "LABCSO" THEN F.numeroBonExamen		
			WHEN "SEA" THEN F.numeroBonSeance
			WHEN "SEACSO" THEN A.numeroBonSeance
		END AS numeroBon,
		A.idBeneficiaire,A.numeroBeneficiaire,CONCAT(i.nomBeneficiaire,' ',i.prenomsBeneficiaire) AS beneficiaire ,
	B.idFacture, B.codeMotifRejet,B.motifManuel,A.fraisReel,A.montantExclues 
	, A.montantRedresse,A.depassement 
	FROM  p_prestationactes prt 	
	INNER JOIN p_facture A ON (A.id=prt.idfacture) 
	INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=A.numeroFeuilleMaladie) 
	-- vw_r_facture A	
	RIGHT JOIN motifdecompte B ON (A.id=B.idFacture)
	-- LEFT JOIN p_reglement R ON (R.numeroDecompte = A.numeroDecompte)
	JOIN beneficiaire i ON (i.id=A.idBeneficiaire)	
	WHERE (A.numeroDecompte=1645)
	AND B.supprime != "1"
	ORDER BY A.dateFacture, A.idFacture;
	
	SELECT * FROM motifdecompte
	
	
	 /*SElect 
	 a.codeSociete AS codeSociete,LEFT(a.dateSysteme,10) AS dateFacture,fn_numordre(DAYOFMONTH(LEFT(a.dateSysteme,10)),2) AS jourFacture,a.id AS idFacture,
	  a.codePrestataire AS codePrestataire,
	 IFNULL(pr.codeTypePrestataire,'') AS codeTypePrestataire,a.codePrestataire AS codePrestataireLivraison,
	 IFNULL(fm.codePrestataire,'') AS codePrestatairePrescription,
	 a.numeroFeuilleMaladie AS numeroFeuilleMaladie,a.codeTypeFacture AS codeTypeFacture,
	 a.numeroDecompte AS numeroDecompte,a.numeroFacture AS numeroFacture,
	 b.idPolice AS idPolice,IFNULL(b.souscripteur,'') AS souscripteur,
	 a.idAdherent AS idAdherent,IFNULL(b.numeroAdherent,'') AS numeroAdherent,IFNULL(b.adherent,'') AS adherent,
	 a.idBeneficiaire AS idBeneficiaire,a.numeroBeneficiaire AS numeroBeneficiaire,IFNULL(b.beneficiaire,'') AS beneficiaire,
	 IFNULL(af.codeTypeAffection,'00') AS codeTypeAffection,af.libelle AS libelleAffection,IFNULL(af.codeAffection,'0000') AS codeAffection,d.libelle AS garantieFacture,
	 a.tiersPayant AS tiersPayant,a.okDecompte AS okDecompte,a.regle AS regle,a.dateReglement AS dateReglement,a.montantFranchise AS montantFranchise,
	 a.dateSysteme AS dateSysteme,a.codeUtilisateur AS codeUtilisateur,a.userControle AS userControle,
	 a.codeExercice AS codeExercice,a.codeMois AS codeMois,a.observations AS observations,a.motifRejet AS motifRejet,
	 a.numeroBonSeance AS numeroBonSeance,a.numeroDemandeDecompte AS numeroDemandeDecompte,a.userDemandeDecompte AS userDemandeDecompte,
	 a.numeroLotDecompte AS numeroLotDecompte,YEAR(a.dateSysteme) AS exercice,a.idFactureSmart AS idFactureSmart,a.gc AS gc,IFNULL(pr.libelle,'') AS prestataire,
	 IFNULL(fm.numeroPrescription,'0') AS numeroPrescription,IFNULL(fm.numeroBonConsultation,'0') AS numeroBonConsultation,
	 IFNULL(fm.numeroBonOrdonnance,'0') AS numeroBonOrdonnance,IFNULL(fm.numeroBonHospitalisation,'0') AS numeroBonHospitalisation,
	 IFNULL(fm.hospitalisation,'0') AS hospitalisation,IFNULL(fm.numeroChambre,'') AS numeroChambre,IFNULL(fm.chirurgie,'0') AS chirurgie,
	 IFNULL(fm.numeroBonOptique,'0') AS numeroBonOptique,IFNULL(fm.numeroOptique,'0') AS numeroOptique,IFNULL(fm.numeroExamen,'0') AS numeroExamen,
	 IFNULL(fm.numeroBonExamen,'0') AS numeroBonExamen,IFNULL(fm.numeroPrescriptionKine,'0') AS numeroPrescriptionKine,IFNULL(fm.numeroBonKine,'0') AS numeroBonKine,
	 a.fraisReel AS fraisReel,a.fraisReelBase AS fraisReelBase,a.prixManuel AS prixManuel,a.prixBase AS prixBase,a.montantTm AS montantTm,
	 a.montantArembourser AS montantArembourser,a.montantRembourse AS montantRembourse,a.fraisExclu AS fraisExclu,a.fraisAssure AS fraisAssure,
	 a.depassement AS depassement,a.fraisRetenu AS fraisRetenu,a.montantForce AS montantForce,a.montantRedresse AS montantRedresse,
	 a.montantApayerCalc AS montantApayerCalc,a.montantExclues AS montantExclues,a.montantApayer AS montantApayer,a.montantPaye AS montantPaye
	 ,B.codeMotifRejet,B.motifManuel	*/
	 
	SELECT 	b.codeGcAssureur,
	CASE a.codeTypeFacture
			WHEN "CSO" THEN fm.numeroBonConsultation
			WHEN "PHAR" THEN fm.numeroBonOrdonnance
			WHEN "OPT" THEN fm.numeroBonOptique
			WHEN "LAB" THEN fm.numeroBonExamen
			WHEN "LABCSO" THEN fm.numeroBonExamen		
			WHEN "SEA" THEN a.numeroBonSeance
			WHEN "SEACSO" THEN a.numeroBonSeance
		END AS numeroBon,
		a.idBeneficiaire,a.numeroBeneficiaire,IFNULL(b.beneficiaire,'') AS beneficiaire,
	a.id AS idFacture, B.codeMotifRejet,B.motifManuel,a.fraisReel,a.montantExclues 
	,a.montantRedresse,a.depassement,a.montantApayer AS montantApayer
	FROM (((((p_facture a 
	LEFT JOIN prestataire pr ON((pr.codePrestataire = CONVERT(a.codePrestataire USING utf8)))) 
	LEFT JOIN vw_infos_contrat_assures b ON((b.idBeneficiaire = a.idBeneficiaire))) 
	LEFT JOIN p_feuillemaladie fm ON((fm.numeroFeuilleMaladie = a.numeroFeuilleMaladie))) 
	LEFT JOIN p_typefacturegarantie d ON((d.codeTypeFactureGarantie = CONCAT(a.codeTypeFacture,IFNULL(fm.hospitalisation,'0'))))) 
	LEFT JOIN p_affection af ON(((af.codeSociete = a.codeSociete) AND (af.codeAffection = fm.codeAffection)))
	RIGHT JOIN motifdecompte B ON (a.id=B.idFacture)
	LEFT JOIN p_reglement r ON (a.numeroDecompte = r.numeroDecompte)) 
	WHERE (a.fraisReel <> 0) AND (r.numeroDecompte=1645)
	
	
SELECT numeroBonSeance,0,0,numeroBonOrdonnance,0 FROM p_facture
SELECT numeroBonConsultation,numeroBonOrdonnance,numeroBonOptique,numeroBonExamen,'' numeroBonSeance,'' numeroBonSeance FROM p_feuillemaladie

SELECT numeroBonSeance FROM vw_r_facture
		
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 ,A.beneficiaireReglement  		
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 1645 IS NULL THEN A.numeroDemandeReglement IS NOT NULL ELSE A.numeroDemandeReglement=1645 END)  
AND (A.tiersPayant="1") AND B.valide=1 AND B.okDecompte=1 AND prt.ententePrealable NOT IN ('2','9')
-- AND (CASE WHEN p_regle IS NULL THEN A.regle IS NOT NULL ELSE A.regle=p_regle END)  
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
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;

	
	