CALL sp_count_famille_vitalis_rp(129,'2023-01-26') ;

CALL sp_count_familleCollege_vitalis_rp(129,'2023-01-26','A') ;

CALL `sp_imprimer_assure`(129,'2023-01-26') ;

CALL `sp_imprimer_assure_date`(129,'2023-01-26') ;

CALL `sp_imprimer_assure_date_analyse`(129,'2023-01-26') ;

CALL  `sp_imprimer_assure_historique_rp`(NULL,NULL,129,NULL,NULL,NULL,'2023-01-26') ;

CALL  `sp_imprimer_assure_periode`( 129, '2022-01-01','2023-01-26') ;

CALL  `sp_imprimer_assure_periode_rp`(NULL,NULL,129,NULL, '2022-01-01','2023-01-26') ;


CALL  `sp_imprimer_assure_periode_rp`(NULL,NULL,129,NULL, '2022-01-01','2023-01-26') ;


CALL  `sp_imprimer_assure_retrait`(129,'2022-01-01','2023-01-26',NULL) ;


CALL  sp_getnombrestatutcollegedate ('2023-03-31',156) -- 

CALL  sp_getnombrestatutclientdate ('2023-03-31',156)  -- 

CALL  sp_getnombrestatutpolicedate ('2023-03-31',156)  -- 


CALL  sp_getnombrestatutclientperiode ( '2023-01-03','2023-03-31',156)  -- Souscripteur

CALL  sp_getnombrestatutpoliceperiode ( '2023-01-03','2023-03-31',156)  -- Police

CALL  sp_getnombrestatutcollegeperiode ( '2023-01-03','2023-03-31',156)  -- College


SELECT * FROM police WHERE idClient=207

SELECT
		p.idClient,C.id AS idCollege,
		fn_nb_beneficiaire_client_periode(l.id,'2022-01-02','2023-04-06') AS nbfamille
		/*fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','A') AS nbassure,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','C') AS nbconjoint,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','D') AS nbautreconjoint,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','E') AS nbenfant,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','F') AS nbgeniteur,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','O') AS nbautreparent,
		fn_nb_statut_client_periode(l.id,'2022-01-02','2023-04-06','X') AS nbenfantsup*/
FROM college C
JOIN police p ON (p.id=C.idPolice)
JOIN clients l ON (l.id=p.idClient)	
WHERE C.idPolice=227;




DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;	 
CREATE TEMPORARY TABLE temp_beneficiaire_l	
SELECT  A.id AS idBeneficiaire,A.dateSortie,A.sorti,
CASE WHEN YEAR(A.dateEffet)>YEAR( '2023-01-02') THEN A.dateEntree ELSE A.dateEffet END AS dateEffet
FROM beneficiaire A 
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)
JOIN clients l ON (l.id=C.idClient)	
WHERE (A.idPolice=227) 
AND A.nomBeneficiaire NOT LIKE "%AVENANT%"
AND (A.dateEntree<='2023-04-06');
	
DELETE FROM temp_beneficiaire_l  WHERE CASE WHEN YEAR(dateSortie)=YEAR('2023-01-02') THEN YEAR(dateSortie)<YEAR('2023-01-02')-1
				ELSE YEAR(dateSortie)<YEAR('2023-01-02') END ;

SELECT COUNT(idBeneficiaire) 
-- COUNT(idBeneficiaire) INTO @nb_famille_client 
FROM temp_beneficiaire_l
WHERE dateEffet BETWEEN '2023-01-02' AND '2023-04-06'
AND CASE WHEN sorti = "1" THEN dateSortie >= '2023-04-06' ELSE (dateSortie IS NULL) END;








