-- 3--
SET @idPolice=200;

SET @user='lionel';

SET 

SELECT 
	dateEffet, 
	dateFin
INTO 
	@dateEffetPolice, 
	@dateFinPolice
FROM police 
WHERE (id=@idPolice) LIMIT 1;
 
DELETE FROM entete_echeancier_temp WHERE (`user`=@user);

SELECT 
MAX(id) INTO @idEmission_temp
FROM emission_temp
WHERE idPolice=200
AND codeTypeAvenant="QTM" AND (`user`=@user);
	 
INSERT INTO entete_echeancier_temp 
(
	idEmission,
	idEmission_temp,
	codeSociete,
	codeTypeEcheancier, 
	primeTtcInitial, 
	tauxInteret, 
	interet, 
	primeTtc, 
	nbEcheance, 
	debutPaiement, 
	`user`
)
SELECT
	0 AS idEmission,
	@idEmission_temp AS idEmission_temp,
	codeSociete,
	"A" codeTypeEcheancier,
	primeTtcInitial,
	@tauxInteret AS tauxInteret,
	interet,
	primeTtc,
	nbEcheance,
	CURDATE() debutPaiement, 
	@user AS `user`
FROM emission_temp 
WHERE (id=@idEmission_temp);
 
  
SELECT 
	codeSociete, 
	nbEcheance 
INTO 
	@codeSociete, 
	@nbEcheance 
FROM entete_echeancier_temp 
WHERE (idEmission_temp=@idEmission_temp) LIMIT 1;
 
SELECT fn_deniernumeroemission(@codeSociete) INTO @numeroEmission;
	 
INSERT INTO emission 
(
	codeSociete, 
	idClient, 
	numeroClient, 
	idPolice, 
	numeroPolice, 
	numeroEmission, 
	codeTypeAvenant, 
	codeTypeOperation, 
	numeroAvenant, 
	dateEffet, 
	dateEcheance, 
	dateEmission, 
	primeNette, 
	commissionGestion, 
	commission, 
	commissionAutre, 
	commissionIntermediaire,
	
	accessoire, 
	accessoireGarant,
	accessoireGestion,
	accessoireIntermediaire,
	
	primeHt, 
	taxe, 
	fraisCarte, 
	primeTtcInitial, 
	tauxInteret, 
	interet, 
	primeTtc, 
	nbEcheance, 
	dateCre, 
	`user`, 
	nbAliment,
	dateEffetPolice
)
SELECT
	A.codeSociete, 
	A.idClient, 
	A.numeroClient, 
	A.idPolice, 
	A.numeroPolice, 
	@numeroEmission, 
	A.codeTypeAvenant, 
	A.codeTypeOperation, 
	A.numeroAvenant, 
	A.dateEffet, 
	A.dateEcheance, 
	A.dateEmission, 
	A.primeNette, 
	
	A.commissionGestion, 
	A.commission, 
	A.commissionAutre,
	A.commissionIntermediaire,
	
	A.accessoire, 
	A.accessoireGarant,
	A.accessoireGestion,
	A.accessoireIntermediaire,
	
	A.primeHt, 
	A.taxe, 
	A.fraisCarte, 
	A.primeTtcInitial, 
	B.tauxInteret, 
	B.interet, 
	B.primeTtc, 
	B.nbEcheance, 
	A.dateCre, 
	A.`user`, 
	A.nbAliment,
	@dateEffetPolice
FROM emission_temp A
LEFT JOIN entete_echeancier_temp B ON (B.idEmission_temp=A.id)
WHERE (B.idEmission_temp=@idEmission_temp);


SELECT LAST_INSERT_ID() INTO @idEmission;
	  
 UPDATE emission SET dateEmission = "2022-01-01" WHERE id = @idEmission;

 
 -- INSETYION DANS TABLE ECHEANCIER TEMP
 INSERT INTO echeancier_temp 
	(
		idEmission,
		idEmission_temp,
		codeSociete,
		idEntete, 
		ordreEcheance, 
		datePaiement, 
		libelleEcheance, 
		primeTtcInitialEmission, 
		tauxInteret, 
		primeTtcEmission, 
		tauxEcheance, 
		primeTtcInitial, 
		interet, 
		primeTtc, 
		`user`,		
		primeNette,
		
		commissionGestion,
		commission,
		commissionAutre,
		commissionIntermediaire,
		
		
		accessoire,
		accessoireGarant,
		accessoireGestion,
		accessoireIntermediaire,
		
		primeHt,
		taxe,
		fraisCarte	
	)
	SELECT
		0 AS idEmission,
		@idEmission_temp AS idEmission_temp,
		A.codeSociete,
		@idEntete AS idEntete, 
		"1" AS ordreEcheance,
		A.dateEmission AS datePaiement, 
		CASE WHEN "FRF"="en_US" THEN IFNULL(B.libelleEng, CONCAT('Premium issue of ', CURDATE())) ELSE IFNULL(B.libelle,CONCAT('Emission du ', CURDATE())) END AS libelleEcheance, 
		
		primeTtcInitial AS primeTtcInitialEmission, 
		@tauxInteret AS tauxInteret, 
		primeTtc AS primeTtcEmission, 
		'100' AS tauxEcheance, 
		primeTtcInitial, 
		interet, 
		primeTtc, 
		`user`,
		
		primeNette,
		
		commissionGestion,
		commission,
		commissionAutre,
		commissionIntermediaire,
		
		accessoire,
		accessoireGarant,
		accessoireGestion,
		accessoireIntermediaire,
		
		primeHt,
		taxe,
		fraisCarte	
	FROM emission_temp A
	LEFT JOIN typeavenant B ON  (B.codeTypeAvenant=A.codeTypeAvenant)
	WHERE (A.id=@idEmission_temp);
 
 -- FIN INSERTION
 
INSERT INTO quittance 
(
	idEmission, 
	numeroEmission,
	codeSociete, 
	idClient, 
	numeroClient, 
	idPolice, 
	numeroPolice, 
	numeroQuittance, 
	codeTypeAvenant, 
	codeTypeOperation, 
	numeroAvenant, 
	dateEffet, 
	dateEcheance, 
	dateEmission, 	
	primeTtcInitialEmission, 
	primeTtcEmission, 
	ordreEcheance, 	
	libelleQuittance, 
	tauxEcheance, 
	datePaiement, 
	primeNette, 
	
	commissionGestion,
	commission, 
	commissionAutre,
	commissionIntermediaire,
	
	accessoire, 
	accessoireGarant,
	accessoireGestion,
	accessoireIntermediaire,
	
	primeHt, 
	taxe, 
	fraisCarte, 
	primeTtcInitial, 
	tauxInteret, 
	interet, 
	primeTtc, 
	dateCre, 
	`user`, 
	encaisse, 
	regle, 
	nbAliment,
	dateEffetPolice
)	
SELECT
	@idEmission, 
	@numeroEmission, 
	A.codeSociete, 
	A.idClient, 
	A.numeroClient, 
	A.idPolice, 
	A.numeroPolice, 
	fn_deniernumeroquittance(@codeSociete) AS numeroQuittance, 
	A.codeTypeAvenant, 
	A.codeTypeOperation, 
	A.numeroAvenant, 
	B.datePaiement AS dateEffet,
	A.dateEcheance, 
	B.datePaiement AS dateEmission,
	A.primeTtcInitial, 
	B.primeTtcEmission, 
	B.ordreEcheance, 	
	libelleEcheance AS  libelleQuittance, 
	B.tauxEcheance, 
	B.datePaiement, 
	B.primeNette, 
	
	B.commissionGestion,
	B.commission, 
	B.commissionAutre,
	B.commissionIntermediaire,
	
	
	B.accessoire, 
	B.accessoireGarant,
	B.accessoireGestion,
	B.accessoireIntermediaire,
	
	B.primeHt, 
	B.taxe, 
	B.fraisCarte, 
	B.primeTtcInitial, 
	B.tauxInteret,
	B.interet,
	B.primeTtc, 
	A.dateCre, 
	A.`user`,
	A.encaisse, 
	A.regle, 
	A.nbAliment,
	@dateEffetPolice
FROM emission_temp A
LEFT JOIN echeancier_temp B ON (B.idEmission_temp=A.id)
WHERE (B.idEmission_temp=@idEmission_temp)
ORDER BY B.ordreEcheance;

SELECT * FROM emission_temp A  -- A.id : 1277

SELECT * FROM echeancier_temp B  -- B.idEmission_temp : 1277

SELECT LAST_INSERT_ID() INTO @idQuittance;

UPDATE quittance SET dateEmission = '2022-01-01' WHERE id = @idQuittance;

 
INSERT INTO beneficiairequittance 
(
	codeSociete, 
	idEmission, 
	numeroEmission, 
	idBeneficiaire, 
	numeroBeneficiaire, 
	primeNette,
	
	commissionGestion,
	commission, 
	commissionAutre,
	commissionIntermediaire,
	
	accessoire, 
	accessoireGarant,
	accessoireGestion,
	accessoireIntermediaire,
	
	primeHt, 
	taxe, 
	fraisCarte, 
	primeTtc, 
	idPolice
)
SELECT 
	A.codeSociete, 
	@idEmission, 
	@numeroEmission, 
	A.idBeneficiaire, 
	A.numeroBeneficiaire, 
	A.primeNette, 
	
	A.commissionGestion,
	A.commission, 
	A.commissionAutre,
	A.commissionIntermediaire,
	
	A.accessoire, 
	A.accessoireGarant,
	A.accessoireGestion,
	A.accessoireIntermediaire,
	
	A.primeHt, 
	A.taxe, 
	A.fraisCarte, 
	A.primeTtc, 
	A.idPolice
FROM beneficiaireprime_temp A
WHERE (idPolice=@idPolice)
AND (codeUtilisateur=@user)
AND (idSaisie=CURDATE())	
AND (dateEffet BETWEEN '2022-01-01' AND '2022-01-01')
AND (choix="1");
 
UPDATE beneficiaireprime A
JOIN beneficiaireprime_temp B ON (B.idBeneficiairePrime=A.id)
SET 
	A.codeEtatFacturation 	= "1" ,
	A.idEmission 		= @idEmission,
	A.numeroEmission 	= @numeroEmission,
	A.userFacturation 	= @user,
	A.dateFacturation	= NOW(),
	A.modifieAlaFacturation = B.modifieAlaFacturation
WHERE (B.idPolice=@idPolice)
AND (B.codeUtilisateur=@user)
AND (B.idSaisie=p_idSaisie)	
AND (B.dateEffet BETWEEN p_debut AND p_fin)
AND (B.choix="1");
 
  
UPDATE beneficiaireprime A
JOIN beneficiaireprime_temp B ON (B.idBeneficiairePrime=A.id)
SET 
	A.primeFamilleOld 	= A.primeNette,
	A.fraisCarteOld 	= A.fraisCarte
WHERE (B.idPolice=@idPolice)
AND (B.codeUtilisateur=@user)
AND (B.idSaisie=p_idSaisie)	
AND (B.dateEffet BETWEEN p_debut AND p_fin)
AND (B.choix="1")
AND (B.modifieAlaFacturation="1");
  
UPDATE beneficiairecarte A
JOIN beneficiaireprime_temp B ON (B.idBeneficiaire=A.idBeneficiaire) AND (B.idBeneficiaireCarte=A.id)
SET 
	A.facture 	= "1", 
	A.idEmission 	= @idEmission, 
	A.numeroEmission = @numeroEmission
WHERE (B.idPolice=@idPolice)
AND (B.codeUtilisateur=@user)
AND (B.idSaisie=p_idSaisie)	
AND (B.dateEffet BETWEEN p_debut AND p_fin)
AND (B.choix="1");
 
UPDATE beneficiaire A
JOIN 
(
	SELECT DISTINCT idBeneficiaire FROM beneficiaireprime_temp
	WHERE (idPolice=@idPolice)
	AND (codeUtilisateur=@user)
	AND (idSaisie=p_idSaisie)	
	AND (dateEffet BETWEEN p_debut AND p_fin)
	AND (choix="1")
	
) B ON (B.idBeneficiaire=A.id)
SET A.codeEtatFacturation = "1";
 

CALL sp_maj_beneficaiaireprime_facturation
(
	@idPolice,
	@user,
	p_idSaisie,
	p_debut,
	p_fin
);
	 
DELETE FROM emission_temp 		WHERE (`user`=@user);
DELETE FROM entete_echeancier_temp 	WHERE (`user`=@user);
DELETE FROM echeancier_temp 		WHERE (`user`=@user);
DELETE FROM beneficiaireprime_temp 	WHERE (codeUtilisateur=@user);