CALL sp_s_rapportsp_periode('2022-01-01','2022-05-30')


-- PRODUCTION PAR MOIS
CALL sp_pr_production_globale_par_mois('2022-01-01','2022-05-30','lionel')

-- 1-- recherche beneficiaires primes
SELECT
	A.idPolice,B.codeEtape,A.exercice,A.dateEffet,fn_numordre(MONTH(A.dateEffet),2) AS codeMois,
	A.primeNette,A.commission,A.commissionAutre,A.primeNetteStat,A.accessoire, 
	A.primeHt,A.taxe,A.fraisCarte,A.primeTtc
FROM beneficiaireprime A 
JOIN police B ON (B.id=A.idPolice)
WHERE (A.dateEffet BETWEEN '2022-01-01' AND '2022-05-31')
AND (A.supprime!="1")
AND (B.exclureSp!="1") ;

	-- MISE A JOUR DES PRIMES DES BENEFICIAIRES
	UPDATE beneficiaireprime A
	JOIN beneficiaire B ON (B.id = A.idBeneficiaire)
	JOIN  avenant v ON ((v.id=B.idAvenant) AND (v.idPolice=B.idPolice))
	SET 
		A.primeNette= B.primeNette,A.primeCplFuneraire= B.primeCplFuneraire,
		A.commissionGestion= B.commissionGestion,A.commission= B.commission, 
		A.commissionAutre=B.commissionAutre,A.primeNetteStat=B.primeNetteStat, 
		A.accessoire=B.accessoire,A.accessoireGarant=B.accessoireGarant,
		A.primeHt=B.primeHt,A.taxe=B.taxe,A.fraisCarte=B.fraisCarte, 
		A.primeTtc= B.primeTtc,A.dateSysteme= NOW(),A.codeUtilisateur= p_user, -- USERS
		A.idAdherent=B.idAdherent,A.codeProduit= B.codeProduit,
		A.codeLienParente = B.codeLienParente,A.idCollege= B.idCollege,
		A.codeEtatFacturation= B.codeEtatFacturation,
		A.idEntetecontrat=fn_id_entetecontrat_police(B.idPolice, B.dateEffet)
	WHERE (A.idPolice=B.idPolice ) AND (A.idAvenant =B.idAvenant)
	-- AND fn_id_entetecontrat_police_en_cours(B.idPolice)
	
	-- A : INSERTIO NBENEFICIAIRE PRIME
	INSERT INTO beneficiaireprime (codeSociete,idPolice,idAvenant,dateEffet,enVigueur, 
	IdBeneficiaire,numeroBeneficiaire,primeNette,primeCplFuneraire,commission,commissionAutre, 
	primeNetteStat,accessoire,primeHt,taxe,fraisCarte,primeTtc,dateSysteme,codeUtilisateur,
	idEmission,numeroEmission,idAdherent,exercice,exercieReference,dateEffetPolice,dateFinPolice, 
	dateEffetAdherent,dateEffetBeneficiaire,codeProduit?codeLienParente,idCollege,idEntetecontrat)
	
	SELECT A.codeSociete,A.idPolice,A.idAvenant,A.dateEffet,A.enVigueur,A.id AS idBeneficiaire, 
	A.numeroBeneficiaire,A.primeNette,A.primeCplFuneraire,A.commission,A.commissionAutre,A.primeNetteStat, 
	A.accessoire,A.primeHt,A.taxe,A.fraisCarte,A.primeTtc,NOW() AS dateSysteme,A.`user` AS codeUtilisateur,
	"0" idEmission,"0" numeroEmission,A.idAdherent,YEAR(A.dateEffet) AS exercice, YEAR(A.dateEffet) AS  exercieReference, 
	B.dateEffet AS dateEffetPolice,B.dateFin AS dateFinPolice,Ad.dateEffet AS dateEffetAdherent, 
	A.dateEffet AS dateEffetBeneficiaire,A.codeProduit,A.codeLienParente,A.idCollege,
	fn_id_entetecontrat_police(A.idPolice, A.dateEffet) AS idEntetecontrat 
	FROM beneficiaire A
	JOIN adherent Ad ON (Ad.id=A.idAdherent)
	JOIN police B ON (B.id=A.idPolice)
	WHERE (0);


CALL `rechercheProc`('UPDATE beneficiaireprime')