 -- FILHET-ALLARD AFRICA
 
SELECT e.codeGcAssureur,e.id AS idPolice,e.numeroPolice,e.libellePolice,e.numeroPoliceGarant,e.dateEffet,e.dateEcheance
FROM police e
INNER JOIN clients c ON (c.id=e.idClient)
WHERE e.libellePolice LIKE '%sith%'


SELECT* FROM emission WHERE idPolice=127
 
 SELECT* FROM primecollege WHERE idPolice=127
 
 
  SELECT* FROM beneficiaire WHERE idPolice=127
 
 
SELECT e.codeGcAssureur,e.id AS idPolice,e.numeroPolice,e.libellePolice,e.numeroPoliceGarant,e.dateEffet,e.dateEcheance
FROM police e
INNER JOIN clients c ON (c.id=e.idClient)
WHERE e.codeApporteur='I000003'




CALL sp_ft2_depense_contrat_rp ('VS',NULL,127,NULL,'2023-01-01','2023-03-31'); -- FT6 :DEPENSE DE TOUTES LES DEPENSES/CONTRAT  -- 30 687 688


CALL `sp_evolution_mensBenefEffetCollegeApporteur_rp`(NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31")


CALL sp_evolution_depenseGaPolice_rp ('VS',NULL,127,NULL,NULL,'2023-01-01',"2023-03-31");


CALL sp_evolution_depensePrime_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31","QTM",30) -- 0 : 65 115  -- 167 453  -- 252 877  -- 21 332 746


CALL sp_auto_evalutionPrimeFacturation_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31") -- PRIME PAR GARANT/SOUCRIPTEUR/POLICE/COLLEGE/APPORTEUR/PERIODE


-- CALL sp_s_rapportsp_periodeAutoGarant_rp (NULL,NULL,'I000003','2023-01-01',"2023-03-31",20); 

		
CALL sp_evolution_depenseGarantiePolice_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31",NULL);


CALL sp_evolution_depenseGarantieActePolice_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31",NULL);


CALL sp_evolution_depenseStatusBenef_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31",NULL);


CALL sp_evolution_depfamilleAss_rp(NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31",0);


CALL sp_evolution_prestataire_Valide_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-05-31",NULL);


CALL sp_fiche_suivie_prestataire_Valide_rp (NULL,NULL,127,NULL,NULL,'2023-01-01',"2023-03-31");


CALL sp_evolution_depenseProvisoirePolice_rp (NULL,NULL,127,NULL,'2023-01-01',"2023-05-31");


CALL sp_repartition_TrancheAge_rp ('FIDRA',NULL,NULL,NULL,'2023-01-01',"2023-03-31");

-- CALL sp_ft1_donnelemn2_rp ('VS',NULL,NULL,NULL,'2023-01-01','2023-01-31',30,'AVANT');   -- FT1B :DONNEES ELEMENTAIRES  -- 30 687 688 / CHARGEMENT* -- 


-- MONTANT ET DECOMPTE DES RD ACCEPTE
	SELECT libelle,idPolice,numeroPolice,libellePolice,SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
	,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
	 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
	 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
	+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT 
			'MONTANT REMBOURSE' AS libelle, e.idPolice,C.numeroPolice,C.libellePolice,		
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COALESCE(SUM(prt.montantApayer),0) ELSE '0' END 'DECEMBRE'
		FROM p_prestationactes prt 
		INNER 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)
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND "2023-03-31")
		-- AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
		AND (CASE WHEN 127 IS NULL  THEN A.idPolice IS NOT NULL ELSE A.idPolice=127 END)  	
		AND G.id!='45' AND C.idClient!='49' AND prt.tiersPayant='0'
		AND B.valide=1 AND B.okDecompte=1  	
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY  e.idPolice,C.numeroPolice,C.libellePolice,B.dateSysteme
	) depense  GROUP BY idPolice,numeroPolice,libellePolice
	UNION ALL 
	SELECT libelle,idPolice,numeroPolice,libellePolice,SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
	,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
	 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
	 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
	+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT 
			 'DECOMPTE' AS libelle,e.idPolice,C.numeroPolice,C.libellePolice,		
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'DECEMBRE'
		FROM p_prestationactes prt 
		INNER 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)
		WHERE (LEFT(B.dateSysteme,10) BETWEEN "2023-01-01" AND "2023-03-31")
		AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
		AND G.id!='45' AND C.idClient!='49' AND prt.tiersPayant='0'
		AND B.valide=1 AND B.okDecompte=1  	
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY  e.idPolice,C.numeroPolice,C.libellePolice,B.dateSysteme
	) depense  GROUP BY idPolice,numeroPolice,libellePolice
	 ORDER BY idPolice,TOTAUX DESC;



	-- MONTANT ET DECOMPTE DES RD REJETE
		SELECT libelle,idPolice,numeroPolice,libellePolice,SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
		,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
		 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
		 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
		(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
		+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
		 FROM (
			SELECT 
				'MONTANT RECLAME' AS libelle, e.idPolice,C.numeroPolice,C.libellePolice,		
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'JANVIER',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'FEVRIER',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'MARS',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'AVRIL',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'MAI',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'JUIN',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'JUILLET',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'AOUT',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'SEPTEMBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'OCTOBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'NOVEMBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COALESCE(SUM(prt.fraisReel),0) ELSE '0' END 'DECEMBRE'
			FROM p_prestationactes prt 
			INNER 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)
			WHERE (LEFT(B.dateSysteme,10) BETWEEN "2023-01-01" AND "2023-03-31")
			AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
			AND G.id!='45' AND C.idClient!='49' AND prt.tiersPayant='0'
			AND B.valide=1 AND B.okDecompte=1 AND prt.montantApayer=0 AND prt.fraisReel>0
			AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
			GROUP BY  e.idPolice,C.numeroPolice,C.libellePolice,B.dateSysteme
		) depense  GROUP BY idPolice,numeroPolice,libellePolice
		UNION ALL 
		SELECT libelle,idPolice,numeroPolice,libellePolice,SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
		,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
		 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
		 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
		(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
		+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
		 FROM (
			SELECT 
				 'DECOMPTE' AS libelle,e.idPolice,C.numeroPolice,C.libellePolice,		
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JANVIER',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'FEVRIER',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'MARS',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'AVRIL',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'MAI',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JUIN',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'JUILLET',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'AOUT',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'SEPTEMBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'OCTOBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'NOVEMBRE',
				CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN IFNULL(COUNT(B.id) ,0) ELSE '0' END 'DECEMBRE'
			FROM p_prestationactes prt 
			INNER 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)
			WHERE (LEFT(B.dateSysteme,10) BETWEEN "2023-01-01" AND "2023-03-31")
			AND (CASE WHEN 'I000003' IS NULL  THEN C.codeApporteur IS NOT NULL ELSE C.codeApporteur='I000003' END)
			AND G.id!='45' AND C.idClient!='49' AND prt.tiersPayant='0'
			AND B.valide=1 AND B.okDecompte=1 AND prt.montantApayer=0 AND prt.fraisReel>0 	
			AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
			GROUP BY  e.idPolice,C.numeroPolice,C.libellePolice,B.dateSysteme
		) depense  GROUP BY idPolice,numeroPolice,libellePolice
		 ORDER BY idPolice,TOTAUX DESC;










