

CALL sp_changement_effet_police_rp(197,'2023-01-19','2023-12-31','lionel') ;








SELECT * FROM adherent  WHERE idPolice='212'

SELECT * FROM police WHERE libellePolice LIKE '%ACML%'

SELECT * FROM beneficiaire  WHERE idPolice='197'


UPDATE adherent SET dateSortie=dateEffet  WHERE (idPolice=257) AND dateSortie IS NOT NULL;

UPDATE beneficiaire SET dateSortie=dateEffet WHERE (idPolice=257) AND dateSortie IS NOT NULL;



 
UPDATE adherent SET dateEffet='2023-01-01',dateEntree='2023-01-01' WHERE (idPolice=257) AND dateEntree<'2023-01-01'

UPDATE beneficiaire SET dateEffet='2023-01-01',dateEntree='2023-01-01' WHERE (idPolice=257) AND dateEntree<'2023-01-01'

UPDATE beneficiaireprime SET dateEffet='2023-01-01'  WHERE (idPolice=257) AND dateEffet='2022-10-13';


UPDATE beneficiaireavenant b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344 ,b.dateEffet=a.dateEffet
WHERE b.idPolice='257' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;


UPDATE adherent SET dateSortie='2022-12-01',sorti='1',codeEtatAdherent='R',codeMotifSortie='T'  WHERE numeroAdherent='00660147';

UPDATE beneficiaire SET dateSortie='2022-12-01',sorti='1',codeEtatBeneficiaire='R',codeMotifSortie='T' WHERE numeroAdherent='00660147';


-- 1-- INFOS POLICE 
/*SELECT 
	dateEffet,dateFin,dateEcheance,DATEDIFF('"2022-08-01"',dateEffet) as numeroAvenant,
	YEAR('"2022-08-01"') anneAvenant,fn_id_entetecontrat_police(id, dateEffet) AS entete,DATE_ADD(@olddateEcheance, INTERVAL @decallage DAY) AS newDateEcheance 
-- INTO @olddateEffet,@olddateFin,@olddateEcheance,@decallage,@numeroAvenant,@exercieReferenceNew,@idEntetecontratOld,@newDateEcheance
FROM police A 
WHERE (id=204)
LIMIT 1;
*/

-- NEWS 
-- 1--
UPDATE beneficiaire b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344,b.dateEntree=a.dateEffet,b.dateEffet=a.dateEffet
WHERE b.idPolice='227' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 2--
UPDATE adherent b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344 ,b.dateEntree=a.dateEffet,b.dateEffet=a.dateEffet
WHERE b.idPolice='227' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 3---
UPDATE beneficiaireprime b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344 ,b.dateEffetAdherent=a.dateEffet,b.dateEffetBeneficiaire=a.dateEffet
WHERE b.idPolice='227' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 4 --
UPDATE hbeneficiaireprime b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344,b.dateEffet=a.dateEffet
WHERE b.idPolice='227' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 5 --
UPDATE adherentsupprime b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344,b.dateEntree=a.dateEffet 
WHERE b.idPolice='227' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 6 --
UPDATE beneficiairesupprime b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344 ,b.dateEntree=a.dateEffet,b.dateEffet=a.dateEffet
WHERE b.idPolice='185' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;

-- 7 --
UPDATE beneficiaireavenant b
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
SET b.idAvenant=344 ,b.dateEffet=a.dateEffet
WHERE b.idPolice='185' AND a.codeTypeAvenant NOT IN ("AFN","CHC") ;


-- 8 -- SUPRESSION DE L'AVENANT
DELETE FROM avenant
WHERE idPolice=185
AND codeTypeAvenant NOT IN ("AFN","CHC");


SELECT * FROM avenant
WHERE idPolice=185;


SELECT * FROM avenant WHERE idPolice= 227 AND dateEffet="2022-04-01"

SELECT * FROM entetecontrat WHERE idPolice= 227

SELECT * FROM contrat WHERE idPolice= 227

SELECT dateEntree,DateEffet  FROM beneficiaire WHERE idPolice= 227 AND dateEffet="2022-04-01"

SELECT * FROM police WHERE id= 227

SELECT * FROM police WHERE libellePolice LIKE "%IBSA%"


SELECT * FROM police WHERE id LIKE "%BETHA%"

-- 2 -- MAJ INFOS POLICE
UPDATE police 
SET dateEffet= '2022-08-01',dateEffetInitial= '2022-08-01'-- ,dateEcheance="2022-12-31",dateFin="2022-12-31" -- ,dateAvenant='"2022-08-01"',dateEcheance=@newDateEcheance,dateFin= @newDateEcheance
WHERE (id=242);

-- 3 -- MAJ INFOS POLICE
UPDATE  entetecontrat 
SET dateEffet= '2022-08-01',dateFin="2022-12-31",duree=DATEDIFF("2022-12-31",'2022-08-01')+1
WHERE (idPolice=242) AND exercice=2022;


SELECT * FROM police WHERE id=180

SELECT * FROM entetecontrat WHERE idPolice=180

UPDATE contrat 
SET dateEffet= '"2022-08-01"',dateFin="2022-12-31",duree=DATEDIFF("2022-12-31",'"2022-08-01"')+1
-- WHERE (id=204) AND exercice=2022;
WHERE (idPolice=204) AND exercice=2022;

-- 4-- MAJ BENEF
UPDATE adherent SET dateEffet='2022-01-01',dateEntree='2022-01-01' WHERE idPolice= 227 AND dateEffet="2022-04-01";


UPDATE beneficiaire SET dateEffet='2022-01-01',dateEntree='2022-01-01' WHERE idPolice= 227 AND dateEffet="2022-04-01";


UPDATE beneficiaireprime SET DateEffet='2022-01-01'  WHERE idPolice= 227 AND dateEffet="2022-04-01";

UPDATE
FROM beneficiaireprime p
INNER JOIN adherent a ON (a.id=p.idAdherent)
SET p.dateEffet='2022-09-01' 
WHERE a.numeroAdherent IN ("01890015","01890016","01890017");




UPDATE avenant SET
	dateEffet = "2022-02-01"
WHERE idPolice="111" AND numeroAvenant="1";

UPDATE police SET 
	dateEffet = "2022-02-01",
	dateEcheance = "2023-01-31",
	dateFin = "2023-01-31",
	numeroAvenant = "3",
	codeTypeAvenant = "QTM",
	codeTypeAvenantCpt = "QTM",
	codeEtatPolice = "EC"
WHERE id="111";


UPDATE contrat SET
	dateEffet = "2022-02-01",
	dateFin = "2023-01-31"
WHERE idPolice="111" AND exercice="2022";

UPDATE adherent SET
	dateSortie=NULL,
	codeMotifSortie = "",
	codeEtatAdherent = "V",
	dateEffet = "2022-02-01"
WHERE id="781";

UPDATE beneficiaire SET
	dateSortie=NULL,
	codeMotifSortie = "",
	codeEtatBeneficiaire = "V",
	dateEffet = "2022-02-01",
	sorti = "0"
WHERE idAdherent="781";


SELECT * FROM avenant 
WHERE idPolice="111" AND numeroAvenant="1";


-- select * from garantieadherent where idPolice=242
-- select * from garantiebeneficiaire where idPolice=242
-- select * from limiteadherent where idPolice=242
-- select * from limitebeneficiaire where idPolice=242