SELECT * FROM beneficiaire WHERE numeroAdherent='01450019'


SELECT a.* FROM  beneficiaire a
JOIN police b ON (b.id=a.idPolice)
-- SET a.dateEffet=b.dateEffet,a.enVigueur = "1",a.dateSortie=(NULL),a.sorti=0,a.codeEtatBeneficiaire="V",a.dateEtat=NOW(),codeMotifSortie=''
WHERE a.idPolice=106
AND YEAR(a.dateSortie)<YEAR('2023-01-01')  AND YEAR(a.dateEffet)>=YEAR('2023-01-01')-1;



SELECT * FROM police WHERE codeTypeAvenant='RES'

-- 1-- ACTIVATION DE LA POLICE
SELECT * FROM police WHERE id=106

-- sp_maj_assures_retire_rp a creer
-- 1--  
-- CALL sp_init_reactivation_rp(106,'2023-01-01','lionel',"Demande de remise en vigueur de la direction générale");

-- 2 
CALL sp_init_renouvellement_police_resiliee_rp (106,'2023-01-01','lionel','2023-12-31');

-- 3--
CALL sp_avenant_renouvellement_rp(106,'lionel')

-- 4 -- rechargement des garanties au cas ou la garantie ne charge pas
CALL sp_maj_garanties_rp(106,NULL)


SELECT * FROM p_feuillemaladie ORDER BY id DESC

SELECT b.* FROM beneficiaire b -- WHERE b.numeroBeneficiaire='0168011704';
WHERE dateSortie='31/01/2023' AND  idPolice=168



SELECT * FROM police WHERE id=145

SELECT * FROM avenant WHERE idPolice=168

UPDATE adherent a
JOIN police b ON (b.id=a.idPolice)
SET a.dateEffet=b.dateEffet,a.enVigueur = "1",a.dateSortie=(NULL),a.sorti=0,
a.codeEtatAdherent="V",a.dateEtat=NOW(),codeMotifSortie=''
WHERE a.numeroAdherent='01450019';

SELECT e.* FROM etatadherent e JOIN adherent a ON (e.idAdherent=a.id) WHERE a.numeroAdherent='01450019';

DELETE  e FROM etatadherent e JOIN adherent a ON (e.idAdherent=a.id) WHERE a.numeroAdherent='01450019';

UPDATE beneficiaire a
JOIN police b ON (b.id=a.idPolice)
SET a.dateEffet=b.dateEffet,a.enVigueur = "1",a.dateSortie=(NULL),a.sorti=0,
a.codeEtatBeneficiaire="V",a.dateEtat=NOW() ,codeMotifSortie=''
-- WHERE a.numeroAdherent='01450019';
WHERE a.numeroBeneficiaire='0168011704';

SELECT e.* FROM etatbeneficiaire e JOIN beneficiaire b ON (e.idBeneficiaire=b.id) JOIN adherent a ON (b.idAdherent=a.id)  WHERE b.numeroBeneficiaire='0168011704';

DELETE e FROM etatbeneficiaire e JOIN beneficiaire b ON (e.idBeneficiaire=b.id) JOIN adherent a ON (b.idAdherent=a.id)  WHERE b.numeroBeneficiaire='0168011704';


SELECT e.* FROM etatbeneficiaire e JOIN beneficiaire b ON (e.idBeneficiaire=b.id) JOIN adherent a ON (b.idAdherent=a.id)  WHERE a.numeroAdherent='01450019';

DELETE  e FROM etatbeneficiaire e JOIN beneficiaire b ON (e.idBeneficiaire=b.id) JOIN adherent a ON (b.idAdherent=a.id)  WHERE a.numeroAdherent='01450019';
