SET @charge=30;
DROP TEMPORARY TABLE IF EXISTS temp_beneficiaireprime;	
CREATE TEMPORARY TABLE temp_beneficiaireprime
SELECT c.exercice,c.exercieReference,c.id AS idEntetecontrat,
	       q.idPolice,c.dateEffet AS dateEffetPolice,c.dateFin AS dateFinPolice, 
	       c.duree AS dureePolice,q.primeNette,"0" AS sorti,"0000-00-00" AS dateSortie,"0" AS ristourne ,
	       c.duree AS dureeContrat,c.dateEffet AS dateEffetContrat,
	       CASE WHEN '2022-08-31'>=c.dateFin THEN c.duree ELSE (DATEDIFF('2022-08-31',GREATEST('2022-01-01',c.dateEffet))+1) END AS dureeAnalyse,
	       q.primeNette AS primeContrat, 
	       "1" AS ratioContrat,
	       ROUND((CASE WHEN '2022-08-31'>=c.dateFin THEN c.duree ELSE (DATEDIFF('2022-08-31',GREATEST('2022-01-01',c.dateEffet))+1) END)/c.duree,2) AS ratioAnalyse,    	
		CASE WHEN @charge>0 
		     THEN 
			  ROUND((q.primeNette * (CASE WHEN '2022-08-31'>=c.dateFin THEN c.duree ELSE (DATEDIFF('2022-08-31',GREATEST('2022-01-01',c.dateEffet))+1) END)/c.duree),0)-	
			  ROUND(ROUND((q.primeNette * (CASE WHEN '2022-08-31'>=c.dateFin THEN c.duree ELSE (DATEDIFF('2022-08-31',GREATEST('2022-01-01',c.dateEffet))+1) END)/c.duree),0)*@charge/100,0)	
		     ELSE 
		          ROUND((q.primeNette * (CASE WHEN '2022-08-31'>=c.dateFin THEN c.duree ELSE (DATEDIFF('2022-08-31',GREATEST('2022-01-01',c.dateEffet))+1) END)/c.duree),0) END AS  primeAnalyse	
FROM emission q
LEFT JOIN police p ON (q.idPolice=p.id)
LEFT JOIN gc_assureur g ON (p.codeGcAssureur = g.codeGcAssureur)
LEFT JOIN entetecontrat c ON (p.id=c.idPolice)
WHERE g.id!='45' AND p.idClient!='49'  AND p.codeEtatPolice NOT IN ('RE','EX','AN')
AND (CASE WHEN 168 IS NULL THEN q.idPolice IS NOT NULL ELSE q.idPolice=168 END) 
AND (CASE WHEN 'QTM' IS NULL THEN q.codeTypeAvenant IS NOT NULL ELSE q.codeTypeAvenant='QTM' END) 
AND (LEFT(q.dateEmission,10)  BETWEEN '2022-01-01' AND '2022-08-31' )  
AND c.dateEffet=q.dateEffet
AND (CASE WHEN '2022-08-31' IS NOT NULL THEN c.exercice>=YEAR('2022-08-31') ELSE c.exercice>=YEAR(NOW())  END) ;

-- select * from temp_beneficiaireprime
DROP TEMPORARY TABLE IF EXISTS temp_prime_police_s;
CREATE TEMPORARY TABLE temp_prime_police_s
SELECT
	A.exercice,A.idPolice,-- SUM(A.primeNette) AS primeNetteStat,SUM(A.primeNette) AS primeAnnuelle
	 CASE WHEN @charge>0 THEN ROUND(SUM(A.primeNette)-ROUND(SUM(A.primeNette)*@charge)/100,0) ELSE SUM(A.primeNette) END AS primeNetteStat,
	CASE WHEN @charge>0 THEN ROUND(SUM(A.primeNette)-ROUND(SUM(A.primeNette)*@charge)/100,0) ELSE SUM(A.primeNette) END AS primeAnnuelle
FROM temp_beneficiaireprime A 	
WHERE ABS(A.primeNette)!=0
GROUP BY A.exercice,A.idPolice;



	
	
DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod;	 
CREATE TEMPORARY TABLE temp_rapportsp_prod
SELECT
	A.exercice,A.idPolice,A.dateEffetPolice AS dateEffet,A.dateFinPolice AS dateFin,
	TIMESTAMPDIFF(MONTH,'2022-08-31',A.dateFinPolice) AS MoisRestant,
	TIMESTAMPDIFF(MONTH,A.dateEffetPolice,'2022-08-31')+1 AS MoisAcquis,	
	SUM(A.primeAnalyse) AS primeAnalyse
FROM temp_beneficiaireprime A
GROUP BY A.exercice,A.idPolice,A.dateEffetPolice,A.dateFinPolice;

-- SELECT TIMESTAMPDIFF(MONTH,'2022-08-01','2022-08-31') INTO @MoisRestant;

-- SELECT TIMESTAMPDIFF(MONTH,'2022-01-01','2022-08-31')+1 INTO @acquis;

DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_sin_cumul;
CREATE TEMPORARY TABLE temp_rapportsp_sin_cumul
SELECT 
	B.idPolice,T.dateEffet,T.dateFin,T.MoisAcquis,T.MoisRestant,SUM(IFNULL(prt.fraisReel,0)) AS reclamer,SUM(IFNULL(prt.montantApayer,0)) AS montantApayer
	,(ROUND(SUM(IFNULL(prt.montantApayer,0))/MoisAcquis,0)*MoisRestant) AS estiMensuelle
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)
INNER JOIN temp_rapportsp_prod T ON (T.idPolice=B.idPolice)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-08-31' )
-- AND (CASE WHEN 121 IS NULL THEN C.id IS NOT NULL ELSE C.id=121 END)  
AND G.id!='45' AND C.idClient!='49' AND B.valide=1 AND B.okDecompte=1
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
GROUP BY B.idPolice,T.dateEffet,T.dateFin,T.MoisAcquis,T.MoisRestant;

SET  @primeAnnuelle=0,@primeAnalyseTotal=0,@montantApayerTotal=0,@rapportSpTotal=0,@rapportSpAnTotal=0,@estiMensuelleTotal=0;


SELECT SUM(IFNULL(primeAnnuelle, 0)) INTO @primeAnnuelle FROM temp_prime_police_s;
 
SELECT SUM(IFNULL(primeAnalyse, 0)) INTO @primeAnalyseTotal FROM temp_rapportsp_prod;
 
SELECT SUM(IFNULL(montantApayer, 0)) INTO @montantApayerTotal FROM temp_rapportsp_sin_cumul;
 
SELECT ROUND(100*IFNULL((@montantApayerTotal/@primeAnalyseTotal), 0),2) INTO @rapportSpTotal;
 
SELECT ROUND(100*IFNULL((@montantApayerTotal/@primeAnnuelle), 0),2) INTO @rapportSpAnTotal;

SELECT SUM(IFNULL(estiMensuelle, 0)) INTO @estiMensuelleTotal FROM temp_rapportsp_sin_cumul;

DROP TEMPORARY TABLE IF EXISTS temp_rapportsp;
CREATE TEMPORARY TABLE temp_rapportsp
SELECT 	
	DISTINCT A.exercice,Z.codeGcAssureur,
	A.idPolice,Z.numeroPolice,Z.libellePolice,A.dateEffet,A.dateFin,		
	(DATEDIFF(LEAST('2022-08-31', A.dateFin), GREATEST(A.dateEffet,'2022-01-01'))+1) AS dureeAcq,
	ROUND(IFNULL(F.primeAnnuelle, 0),0) 	AS primeAnnuelle,
	ROUND(IFNULL(A.primeAnalyse, 0),0) 	AS primeAnalyse,
	IFNULL(B.montantApayer, 0) 		AS montantApayer,
	IFNULL(B.estiMensuelle, 0) 		AS montantApEstimer,
	ROUND(100*IFNULL((B.montantApayer/A.primeAnalyse), 0),2) AS rapportSp,	
	-- ROUND(100*IFNULL((B.montantApayer/F.primeAnnuelle), 0),2) AS rapportSpAn,
	ROUND(IFNULL(@primeAnnuelle, 0),0)  AS primeAnnuelleTotal,
	ROUND(IFNULL(@estiMensuelleTotal, 0),0)  AS SinistresEstimeTotal,
	ROUND(IFNULL(@primeAnalyseTotal, 0),0)  AS primeAnalyseTotal,
	IFNULL(@montantApayerTotal, 0)  AS montantApayerTotal,
	IFNULL(@rapportSpTotal, 0) AS rapportSpTotal,
	IFNULL(@rapportSpAnTotal, 0) AS rapportSpTotalAn		
FROM temp_rapportsp_prod A
LEFT JOIN temp_prime_police_s F ON (F.idPolice=A.idPolice)
LEFT JOIN temp_rapportsp_sin_cumul B ON (A.idPolice=B.idPolice)
LEFT JOIN vw_infos_polices Z ON (Z.idPolice = A.idPolice)
WHERE Z.idClient !="49"
ORDER BY codeGcAssureur,idPolice,primeAnnuelle DESC;

DROP TEMPORARY TABLE IF EXISTS temp_rapportspNEWS;
CREATE TEMPORARY TABLE temp_rapportspNEWS
 SELECT DISTINCT  t.exercice,t.codeGcAssureur,fn_libelle_garant_rp(t.codeGcAssureur) AS Garant,
 t.idPolice,t.numeroPolice,t.libellePolice,	
	t.montantApayer AS sinitres,t.montantApEstimer AS sinitreRestant,t.primeAnalyse,t.rapportSp 
	,t.primeAnnuelle,(t.montantApayer+montantApEstimer) AS sinitresAnnuelle,
	ROUND(100*ROUND((t.montantApayer+montantApEstimer)/t.primeAnnuelle,2),2) AS rapportSpAn	
FROM temp_rapportsp t;



 SELECT DISTINCT 
         t.exercice,t.codeGcAssureur,fn_libelle_garant_rp(t.codeGcAssureur) AS Garant,
         t.idPolice,t.numeroPolice,t.libellePolice,
	 sinitres AS "sinitres janvier 2022 à Août 2022",
	 sinitreRestant AS "EstimationSinistre (Septembre 2022 à décembre 2022)",
	 t.primeAnalyse AS "primeAnalyse sur la période",
	 t.rapportSp AS "rapportSp sur la période en % ", 
	 A.tauxAjustement AS "ajustementSp sur la période",
	 t.primeAnnuelle,
	  sinitresAnnuelle AS "Estimation SinistreAnnuel 2022",
	 rapportSpAn AS "rapportSpAn en %",
	 B.tauxAjustement AS ajustementSpAn		
FROM temp_rapportspNEWS t 
LEFT JOIN tableauajustement A ON (t.codeGcAssureur = A.codeGcAssureur)
LEFT JOIN tableauajustement B ON (t.codeGcAssureur = B.codeGcAssureur)
WHERE t.rapportSp BETWEEN A.spMin AND A.spMax   -- and t.idPolice=200  -- 21 806 806
AND t.rapportSpAn BETWEEN B.spMin AND B.spMax;


DROP TEMPORARY TABLE IF EXISTS temp_beneficiaireprime;
DROP TEMPORARY TABLE IF EXISTS temp_prime_police_s;	
DROP TEMPORARY TABLE IF EXISTS temp_rapportspNEWS;
DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_prod;
DROP TEMPORARY TABLE IF EXISTS temp_rapportsp_sin_cumul;
DROP TEMPORARY TABLE IF EXISTS temp_rapportsp;
DROP TEMPORARY TABLE IF EXISTS temp_rapportspNEWS;
