-- 0--
SELECT * FROM prestataire WHERE libelle LIKE '%Mienmoh%'

-- 1-- SINITRES 
DROP TEMPORARY TABLE IF EXISTS temp_tauxGarantie;	 
CREATE TEMPORARY TABLE temp_tauxGarantie
SELECT  
	LAST_DAY (LEFT(B1.dateSysteme,10)) AS dateComptable,
	C1.id idPolice,-- prt1.codeGarantie AS codeGarantie,
	SUM(IFNULL(prt1.montantApayer,0)) montantGarantie   
FROM p_prestationactes prt1 JOIN p_facture B1 ON (B1.id=prt1.idfacture)
	INNER JOIN  police C1 ON  (B1.idPolice = C1.id)
	INNER JOIN  gc_assureur G1 ON  (C1.codeGcAssureur = G1.codeGcAssureur)
	INNER JOIN adherent a1 ON ((C1.id=a1.idPolice) AND (C1.codeSociete=a1.codeSociete) AND ( a1.id=B1.idAdherent))
	WHERE (LEFT(B1.dateSysteme,10)  BETWEEN '2022-05-01' AND '2022-08-31' )
	AND B1.codePrestataire IN ('CE1257')
	AND C1.id IN (168,156)
	AND G1.id!='45' AND C1.idClient!='49' AND (B1.codeSociete="vitalis")
	AND B1.valide=1 AND B1.okDecompte=1 AND prt1.valide=1
	AND B1.codePrestataire IN ('CE1257')
GROUP BY LAST_DAY (LEFT(B1.dateSysteme,10))-- ,prt1.codeGarantie 

SELECT LAST_DAY (LEFT(B.dateSysteme,10)) AS dateComptable,C.id N,fn_libelle_policeID(C.id) AS police,
	prt.codeGarantie AS codeGarantie,fn_libelle_garantie(prt.codeGarantie) AS garantie,  
	COUNT(DISTINCT prt.idBeneficiaire) AS Quantite, COALESCE(SUM(prt.montantApayer),0) AS MontantRembourse
	,ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(DISTINCT prt.idBeneficiaire)),0) AS Coutmoyen 
	,ROUND(SUM(IFNULL(prt.montantApayer,0))*100/t.montantGarantie,2) AS Taux 
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 temp_tauxGarantie t ON ((C.id=t.idPolice)  )  AND (t.dateComptable=LAST_DAY(LEFT(B.dateSysteme,10)) )
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-05-01' AND '2022-08-31' )
AND C.id IN (168,156)
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
AND B.valide=1 AND B.okDecompte=1 AND prt.valide=1
AND B.codePrestataire IN ('CE1257')
GROUP BY LAST_DAY (LEFT(B.dateSysteme,10)),prt.codeGarantie 
HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY  dateComptable,MontantRembourse DESC ;
DROP TEMPORARY TABLE IF EXISTS temp_tauxGarantie;	

-- 2 -- CONSULTATION

DROP TEMPORARY TABLE IF EXISTS temp_tauxActes;	 
CREATE TEMPORARY TABLE temp_tauxActes
SELECT  
	LAST_DAY (LEFT(B1.dateSysteme,10)) AS dateComptable,
	C1.id idPolice, 
	SUM(IFNULL(prt1.montantApayer,0)) montantActe  
FROM p_prestationactes prt1 JOIN p_facture B1 ON (B1.id=prt1.idfacture)
	INNER JOIN  police C1 ON  (B1.idPolice = C1.id)
	INNER JOIN  gc_assureur G1 ON  (C1.codeGcAssureur = G1.codeGcAssureur)
	INNER JOIN adherent a1 ON ((C1.id=a1.idPolice) AND (C1.codeSociete=a1.codeSociete) AND ( a1.id=B1.idAdherent))
	WHERE (LEFT(B1.dateSysteme,10)  BETWEEN '2022-05-01' AND '2022-08-31' )
	AND B1.codePrestataire IN ('CE1257')
	AND C1.id IN (168,156)
	AND prt1.codeActe IN ('C','CS')
	AND G1.id!='45' AND C1.idClient!='49' AND (B1.codeSociete="vitalis")
	AND B1.valide=1 AND B1.okDecompte=1 AND prt1.valide=1
	AND B1.codePrestataire IN ('CE1257')
GROUP BY LAST_DAY (LEFT(B1.dateSysteme,10)) 



SELECT LAST_DAY (LEFT(B.dateSysteme,10)) AS dateComptable,C.id N,fn_libelle_policeID(C.id) AS police,
	prt.codeActe AS codeActe,fn_libelle_acte_rp(prt.codeActe) AS Acte,  
	COUNT(DISTINCT prt.idBeneficiaire) AS Quantite, COALESCE(SUM(prt.montantApayer),0) AS MontantRembourse
	,ROUND((SUM(IFNULL(prt.montantApayer,0))/COUNT(DISTINCT prt.idBeneficiaire)),0) AS Coutmoyen 
	,ROUND(SUM(IFNULL(prt.montantApayer,0))*100/t.montantActe,2) AS Taux 
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 temp_tauxActes t ON ((C.id=t.idPolice)  )  AND (t.dateComptable=LAST_DAY(LEFT(B.dateSysteme,10)) )
WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-05-01' AND '2022-08-31' )
AND C.id IN (168,156)
AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete="vitalis")
AND B.valide=1 AND B.okDecompte=1 -- AND prt.valide=1
AND B.codePrestataire IN ('CE1257')
AND prt.codeActe IN ('C','CS')
GROUP BY LAST_DAY (LEFT(B.dateSysteme,10)),prt.codeActe 
HAVING SUM(IFNULL(B.montantApayer,0))!='0' 
ORDER BY  dateComptable,MontantRembourse DESC ; 
DROP TEMPORARY TABLE IF EXISTS temp_tauxActes;	 