
-- 1--RECHERCHE DES BENEFICIAIRES A ENCAISERR	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;	 
	CREATE TEMPORARY TABLE temp_cumul_collegeEncaisse	
	SELECT 
		fn_exercice_police(A.idPolice) AS  Exercice,
		A.codeSociete,C.codeGcAssureur,fn_libelle_garant_rp(C.codeGcAssureur) AS garant,
		IFNULL(B.libelle, "") AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,
		L.libelle AS lienparente,A.codeLienParente,
		C.codeBaseTarification,CONCAT(YEAR(A.dateEffet),"-",
		CASE WHEN LENGTH(MONTH(A.dateEffet))<2 	THEN CONCAT("0",MONTH(A.dateEffet)) ELSE MONTH(A.dateEffet) END) AS dateEntree,
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
		ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
		/(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,	
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id)
		WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id)
		ELSE 0 END AS nbPersonne,
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,'2022-09-30')+1
		WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateEffet,'2022-09-30')+1
		ELSE 0 END AS MoisUtilise
		,(TIMESTAMPDIFF(MONTH,A.dateEffet,p.dateFin)+1) AS MoisDuree,p.dateEffet,p.dateFin
	FROM beneficiaire A 
	JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
	JOIN police p ON (p.id=A.idPolice)
	JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
	JOIN college C ON (C.id=A.idCollege)
	LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
	LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
	WHERE A.idPolice>0
	AND (CASE WHEN 184 IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=184 END)  
	AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
	AND p.codeEtatPolice NOT IN ('RE','EX','AN')
	AND (A.dateEffet BETWEEN '2022-01-01' AND '2022-09-30')
	GROUP BY A.idPolice,A.codeSociete,C.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
		L.libelle,A.codeLienParente,C.codeBaseTarification,A.dateEffet,
		C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
	ORDER BY idPolice,idPolice,codeLienParente;

-- 2--RECHERCHE DES BENEFICIAIRES A RISTOURNER	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;	 
	CREATE TEMPORARY TABLE temp_cumul_collegeRistourne	
	SELECT 
		fn_exercice_police(A.idPolice) AS  Exercice,
		A.codeSociete,C.codeGcAssureur,fn_libelle_garant_rp(C.codeGcAssureur) AS garant,
		IFNULL(B.libelle, "") AS produit,C.idPolice,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,
		L.libelle AS lienparente,A.codeLienParente,
		C.codeBaseTarification,CONCAT(YEAR(A.dateSortie),"-",
		CASE WHEN LENGTH(MONTH(A.dateSortie))<2 	THEN CONCAT("0",MONTH(A.dateSortie)) ELSE MONTH(A.dateSortie) END) AS dateEntree,
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END AS primeHt,
		ROUND((CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN B.primeFamille ELSE B.primeIndividu END)
		/(TIMESTAMPDIFF(MONTH,p.dateEffet,p.dateFin)+1),0) AS primeIncorpoInd,	
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN COUNT(H.id)
		WHEN (C.codeBaseTarification='IND') THEN COUNT(A.id)
		ELSE 0 END AS nbPersonne,
		CASE WHEN (C.codeBaseTarification='FAM' AND A.codeLienParente='A') THEN TIMESTAMPDIFF(MONTH,A.dateSortie,'2022-09-30')
		WHEN (C.codeBaseTarification='IND') THEN TIMESTAMPDIFF(MONTH,A.dateSortie,'2022-09-30')
		ELSE 0 END AS MoisUtilise
		,(TIMESTAMPDIFF(MONTH,A.dateSortie,p.dateFin)) AS MoisDuree,p.dateEffet,p.dateFin
	FROM beneficiaire A 
	JOIN adherent H ON ((A.codeSociete=H.codeSociete) AND  (H.id=A.idAdherent) AND (H.idPolice=A.idPolice))
	JOIN police p ON (p.id=A.idPolice)
	JOIN gc_assureur g ON (g.codeGcAssureur=p.codeGcAssureur)
	JOIN college C ON (C.id=A.idCollege)
	LEFT JOIN produit B ON ((B.codeSociete=A.codeSociete) AND (B.codeProduit=C.codeProduit)  AND (B.codeBaseTarification=C.codeBaseTarification))
	LEFT JOIN lienparente L ON (L.codeLienParente=A.codeLienParente)
	WHERE A.idPolice>0
	AND (CASE WHEN 184 IS NULL THEN A.idPolice IS NOT NULL ELSE A.idPolice=184 END)  
	AND A.nomBeneficiaire NOT LIKE "%AVENANT%"  AND g.typeGarant!='1' AND g.id!='45' AND p.idClient!='49' 
	AND p.codeEtatPolice NOT IN ('RE','EX','AN') AND A.codeEtatBeneficiaire='R'
	AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= A.dateEffet ELSE A.dateSortie IS NULL END 
	AND (A.dateSortie BETWEEN '2022-01-01' AND '2022-09-30')
	GROUP BY A.idPolice,A.codeSociete,C.codeGcAssureur,B.libelle,C.idPolice,p.numeroPolice,p.libellePolice,
		A.idCollege,C.libelleCollege,C.codeProduit,C.libelleCollege,A.codeLienParente,
		L.libelle,A.codeLienParente,C.codeBaseTarification,A.dateSortie,
		C.codeBaseTarification,B.primeFamille,B.primeIndividu,p.dateEffet,p.dateFin	
	ORDER BY idPolice,idPolice,codeLienParente;	
	
	
	SELECT IFNULL(g.gcTauxTva,0),IFNULL(p.tauxCommission,0),IFNULL(p.tauxCommissionGerant,0)
	INTO 
		@tauxTva,@tauxCourtier,@tauxHonoraire
	FROM gc_assureur g
	INNER JOIN police p ON (p.codeGcAssureur=g.codeGcAssureur)
	WHERE p.id>0
	AND (CASE WHEN 184 IS NULL THEN p.id IS NOT NULL ELSE p.id=184 END)  
	LIMIT 1;
	
-- 3- Concilliation des encaissement et des ristournes
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTEncaisse;
	CREATE TEMPORARY TABLE temp_cumul_colHTEncaisse
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,
		t.codeBaseTarification,t.codeLienParente,
		t.primeHt,t.nbPersonne,t.MoisDuree,t.MoisUtilise,
		SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotaleEncaisse,
		SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtPeriodeEncaisse,
		ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxCourtier)/100,0) AS honoCourtierEncaisse,
		ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxHonoraire)/100,0) AS honoGestionnaireEncaisse,
		ROUND((ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxCourtier)/100,0)*@tauxTva)/100,0) AS  montantTVAEncaisse
	FROM    temp_cumul_collegeEncaisse t
	JOIN entetecontrat e ON (e.idPolice=t.idPolice)
	WHERE t.primeIncorpoInd>0
	AND e.id=fn_id_entetecontrat_police_courant(e.idPolice)
	GROUP BY t.Exercice,t.codeGcAssureur,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,	
	t.primeHt,t.nbPersonne,t.MoisDuree,t.MoisUtilise,t.codeLienParente;
	

	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTRistourne;
	CREATE TEMPORARY TABLE temp_cumul_colHTRistourne
	SELECT 	 	
		t.Exercice,
		t.codeGcAssureur,  
		t.idPolice,t.numeroPolice,t.libellePolice,
		t.idCollege,
		t.codeBaseTarification,t.codeLienParente,
		t.primeHt,t.nbPersonne,t.MoisDuree,t.MoisUtilise,
		- SUM(t.primeIncorpoInd*t.nbPersonne*MoisDuree) AS primeHtTotaleRistourne,
		- SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise) AS primeHtPeriodeRistourne,
		- ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxCourtier)/100,0) AS honoCourtierRistourne,
		- ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxHonoraire)/100,0) AS honoGestionnaireRistourne,
		- ROUND((ROUND((SUM(t.primeIncorpoInd*t.nbPersonne*t.MoisUtilise)*@tauxCourtier)/100,0)*@tauxTva)/100,0) AS  montantTVARistourne
	FROM  temp_cumul_collegeRistourne t
	JOIN entetecontrat e ON (e.idPolice=t.idPolice)
	WHERE t.primeIncorpoInd>0
	AND e.id=fn_id_entetecontrat_police_courant(e.idPolice)
	GROUP BY t.Exercice,t.codeGcAssureur,t.idPolice,t.numeroPolice,t.libellePolice,t.idCollege,t.codeBaseTarification,	
	t.primeHt,t.nbPersonne,t.MoisDuree,t.MoisUtilise,t.codeLienParente;
	
	SELECT 'ENCAISSE' AS LIBELLE,t.* FROM temp_cumul_colHTEncaisse t
	UNION ALL	
	SELECT 'RISTOURNE' AS LIBELLE,e.* FROM temp_cumul_colHTRistourne e;
	
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeEncaisse;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_collegeRistourne;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTEncaisse;
	DROP TEMPORARY TABLE IF EXISTS temp_cumul_colHTRistourne;