-- ANALYSE 
SELECT e.* 
FROM beneficiaire e
INNER JOIN police p ON (e.idPolice=p.id)
WHERE p.codeGcAssureur='GNA'
AND nomBeneficiaire LIKE '%AVENA%'
AND idPolice='82'


SELECT numeroAvenant,codeTypeAvenant,dateAvenant FROM police
WHERE id=82


SELECT * 
FROM beneficaireprime = 26854


SELECT 
	dateEffet,dateFin,YEAR(dateEffet),fn_id_entetecontrat_police_en_cours(id),libellePolice
INTO 
	@dateEffetPolice,@dateFinPolice,@exercieReference,@idEntetecontrat,@libellePolice
FROM police 
WHERE (id=82);

SELECT MAX(id)  INTO @idBeneficiaire
FROM beneficiaire
WHERE CONCAT(nomBeneficiaire, " ", prenomsBeneficiaire) LIKE '%FACTURATION%'
AND nomBeneficiaire="AVENANT" AND idPolice=82;


IF (1312>"0") THEN
	SELECT codeSociete, dateEffet, motifavenant, codeTypeAvenant, numeroAvenant, facture
	-- INTO @codeSociete, @dateAvenant, @motifavenant, @codeTypeAvenant, @numeroAvenant, @facture
	FROM avenant WHERE id = 1312;
	
ELSE
	SELECT codeSociete, dateEffet, motifavenant, "QTM"
	-- INTO @codeSociete, @dateAvenant, @motifavenant, @codeTypeAvenant
	FROM avenant_temp WHERE (`user`='');
	
	SELECT * FROM avenant_temp
 
	UPDATE police SET 
	numeroAvenant = numeroAvenant+1, 
	codeTypeAvenant = "QTM",
	dateAvenant = @dateAvenant
	WHERE (id=p_idPolice);
 
	SELECT numeroAvenant INTO @numeroAvenant FROM police WHERE (id=p_idPolice);
END IF;

SELECT 
	accessoire,accessoireGarant,accessoireGestion,accessoireIntermediaire,		
	commissionGestion,commission,commissionAutre,commissionIntermediaire,
	primeNette,primeNetteStat,primeHt,taxe,fraisCarte,primeTtcInitial,primeTtc
 /*INTO 
	@accessoire,@accessoireGarant,@accessoireGestion,accessoireIntermediaire,		
	@commissionGestion,@commission,@commissionAutre,@commissionIntermediaire,		
	@primeNette,@primeNetteStat,@primeHt,@taxe,@fraisCarte,@primeTtcInitial,@primeTtc*/
 FROM avenant_temp A WHERE (A.user='lionel');
 
 
 -- 2




SELECT * FROM beneficiaireprime_temp
WHERE idPolice=82


SELECT a.*
FROM  (((`beneficiaireprime_temp` `a`
     JOIN `beneficiaire` `bn`
       ON ((`bn`.`id` = `a`.`idBeneficiaire`)))
    JOIN `adherent` `b`
      ON ((`b`.`id` = `a`.`idAdherent`)))
   LEFT JOIN `college` `c`
     ON ((`c`.`id` = `a`.`idCollege`)))
WHERE a.idPolice=82


--  AFFICHER LA QUITTANCE MANUELLE POUR FACTURATURATION
/*sp_afficher_facturation_police`(
	p_idPolice	BIGINT,
	p_user 		VARCHAR(50),
	p_idSaisie 	VARCHAR(50),
	p_debut 	DATE,	
	p_fin 		DATE,
	p_idAvenant     BIGINT
)*/

SELECT B.garantieArchive,A.idEmission,A.*
FROM beneficiaireprime A
JOIN beneficiaire BN ON (BN.id=A.idBeneficiaire)
JOIN beneficiaireprime_temp n ON (n.id=A.idBeneficiaire)
JOIN college B ON (B.id=A.idCollege)
WHERE (A.idPolice=82)  AND A.idBeneficiaire=26854
AND (A.codeEtatFacturation != "1")
AND (A.primeTtc!=0) 
AND (B.garantieArchive="1")
AND (A.supprime!="1") 
AND (A.idEmission=0)


SELECT  garantieArchive 
FROM  college B WHERE id=114

UPDATE college SET   garantieArchive=1  WHERE id=114
