SELECT  p.codeGcAssureur,c.id,c.idPolice,p.libellePolice,c.primeNette AS primeNette,0 AS primeSave,(0-c.primeNette) AS ecart
FROM emission c
INNER JOIN police p ON (p.id=c.idPolice)
WHERE c.codeTypeAvenant='QTM'
AND YEAR(c.dateEffet)=2023
AND c.idPolice=325
-- AND p.libellePolice LIKE '%COM%'
-- AND (c.primeNette-c.primeNette)!=0


-- 1 -- RECHERCHE ECART EMISSION   -- SOCOPAHLD COOP CA  / FAMILLE ADI RAJESH BABU 

SELECT  p.codeGcAssureur,c.id,c.idPolice,p.libellePolice,c.primeNette AS primeNette,s.primeNette AS primeSave,(s.primeNette-c.primeNette) AS ecart
FROM emission c
INNER JOIN emission_save s ON (s.id=c.id)
INNER JOIN police p ON (p.id=s.idPolice)
WHERE c.codeTypeAvenant='QTM'
-- AND p.libellePolice LIKE '%COM%'
AND (s.primeNette-c.primeNette)!=0
-- AND p.id IN (201);

SELECT * FROM emission
WHERE idPolice=325


SELECT * FROM avenant
WHERE idPolice=325

-- 2 CORRECTION EMISSION

UPDATE emission_save AS e
INNER JOIN emission AS s ON (s.id=e.id)
INNER JOIN police p ON (p.id=s.idPolice)
		SET 
		   e.dateEffet=s.dateEffet,e.dateEffetPolice=s.dateEffetPolice,e.dateEcheance=s.dateEcheance,e.dateEmission=s.dateEmission
		   ,e.primeNette=s.primeNette,e.commissionGestion=s.commissionGestion,e.commission=s.commission,e.commissionAutre=s.commissionAutre
		   ,e.primeNetteStat=s.primeNetteStat,e.accessoire=s.accessoire,e.accessoireGarant=s.accessoireGarant,e.primeHt=s.primeHt
		   ,e.primeNetteCie=s.primeNetteCie,e.accessoireCie=s.accessoireCie,e.assesoireApporteur=s.assesoireApporteur
		   ,e.chiffreAffaire=s.chiffreAffaire,e.taxe=s.taxe,e.fraisCarte=s.fraisCarte,e.primeTtcInitial=s.primeTtcInitial
		   ,e.tauxInteret=s.tauxInteret,e.interet=s.interet,e.primeTtc=s.primeTtc,e.nbEcheance=s.nbEcheance,e.dateCre=s.dateCre,e.`user`=s.`user`
		   ,e.encaisse=s.encaisse,e.regle=s.regle,e.nbAliment=s.nbAliment,e.dateSysteme=s.dateSysteme,e.quittanceNova=s.quittanceNova
		   ,e.libelleEmission=s.libelleEmission,e.accessoireIntermediaire=s.accessoireIntermediaire
		   ,e.accessoireGestion=s.accessoireGestion,e.commissionIntermediaire=s.commissionIntermediaire
WHERE e.codeTypeAvenant='QTM'
AND p.codeApporteur='I000003'
AND (e.primeNette-s.primeNette)!=0;


-- 1 -- RECHERCHE ECART QUITTANCE   -- SOCOPAHLD COOP CA  / FAMILLE ADI RAJESH BABU 

SELECT s.id,s.idPolice,p.libellePolice,c.primeNette AS primeNette,s.primeNette AS primeSave,(s.primeNette-c.primeNette) AS ecart
FROM quittance c
INNER JOIN emission AS q ON (q.id=c.idEmission)
INNER JOIN quittance_save s ON (s.id=c.id)
INNER JOIN police p ON (p.id=s.idPolice)
WHERE c.codeTypeAvenant='QTM'
-- AND p.codeApporteur='I000003'
AND c.idPolice=82
AND (s.primeNette-c.primeNette)!=0
-- AND p.id IN (202,196);


-- DELETE FROM avenant WHERE idPolice=82 AND id=1739

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

SELECT * FROM `typecontrat` WHERE codeTypeContrat='G'


UPDATE `typecontrat` SET tauxTaxe=3 WHERE codeTypeContrat='G';


SELECT * FROM police WHERE id=305 

SELECT * FROM  beneficiaire  WHERE idPolice=305 AND nomBeneficiaire LIKE '%AVENANT%'

SELECT * FROM  beneficiaireprime WHERE idPolice=305 AND idBeneficiaire=45814 AND idAvenant=2305 ORDER BY id DESC

SELECT * FROM  avenant WHERE idPolice=305  AND codeTypeAvenant='QTM' AND id=2305

SELECT * FROM emission WHERE idPolice=305 AND codeTypeAvenant='QTM' AND id=787

SELECT * FROM quittance WHERE idPolice=305 AND codeTypeAvenant='QTM' AND idEmission=787


-- CORRECTION QUITTANCE

/*
DELETE FROM  beneficiaireprime WHERE idPolice=305 AND idBeneficiaire=45850 and idAvenant=2305 ;

DELETE FROM  quittance WHERE idPolice=305 AND codeTypeAvenant='QTM'  AND idEmission=787;

DELETE FROM  emission WHERE idPolice=305 AND codeTypeAvenant='QTM'  AND id=787;

DELETE FROM  avenant WHERE idPolice=305  AND codeTypeAvenant='QTM' AND id=2305;

UPDATE police set numeroAvenant=3 WHERE id=305 ;
*/


UPDATE quittance_save AS e
INNER JOIN quittance AS s ON ((s.id=e.id) AND (s.idEmission=e.idEmission))
INNER JOIN police p ON (p.id=s.idPolice)
INNER JOIN emission AS q ON (q.id=s.idEmission)
	SET 
	    e.dateEffet=s.dateEffet,e.dateEffetPolice=s.dateEffetPolice,e.dateEcheance=s.dateEcheance,e.dateEmission=s.dateEmission
	    ,e.primeTtcInitialEmission=s.primeTtcInitialEmission,e.primeTtcEmission=s.primeTtcEmission,e.ordreEcheance=s.ordreEcheance
	    ,e.libelleQuittance=s.libelleQuittance,e.tauxEcheance=s.tauxEcheance,e.datePaiement=s.datePaiement
	    ,e.primeNette=s.primeNette,e.commissionGestion=s.commissionGestion,e.commission=s.commission,e.commissionAutre=s.commissionAutre
	    ,e.primeNetteStat=s.primeNetteStat,e.accessoire=s.accessoire,e.accessoireGarant=s.accessoireGarant,e.primeHt=s.primeHt
	    ,e.primeNetteCie=s.primeNetteCie,e.accessoireCie=s.accessoireCie,e.assesoireApporteur=s.assesoireApporteur,e.chiffreAffaire=s.chiffreAffaire
	    ,e.taxe=s.taxe,e.fraisCarte=s.fraisCarte,e.primeTtcInitial=s.primeTtcInitial,e.tauxInteret=s.tauxInteret,e.interet=s.interet
	    ,e.primeTtc=s.primeTtc,e.dateCre=s.dateCre,e.`user`=s.`user`,e.encaisse=s.encaisse,e.regle=s.regle,e.nbAliment=s.nbAliment,e.dateSysteme=s.dateSysteme
	    ,e.deduireComm=s.deduireComm,e.quittanceNova=s.quittanceNova,e.primeadditionnelle=s.primeadditionnelle,e.accessoireIntermediaire=s.accessoireIntermediaire
	    ,e.accessoireGestion=s.accessoireGestion,e.commissionIntermediaire=s.commissionIntermediaire
WHERE q.codeTypeAvenant='QTM'
AND p.codeApporteur='I000003'
AND (e.primeNette-s.primeNette)!=0; 
-- AND p.id IN (202,196);


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

SELECT * FROM police WHERE id=286

SELECT * FROM avenant WHERE idPolice=371

SELECT * FROM beneficiairequittance WHERE idPolice=286 ORDER BY id DESC

SELECT * FROM emission WHERE idPolice=346  AND codeTypeAvenant='QTM'

/*DELETE FROM emission WHERE idPolice=257

DELETE FROM quittance WHERE idPolice=257

DELETE FROM avenant WHERE codeTypeAvenant='QTM' and idPolice=257

DELETE  FROM beneficiairequittance WHERE idPolice=257 ORDER BY id DESC*/

SELECT * FROM police WHERE id=257




-- NEWS 
UPDATE (
	SELECT 
		p.id AS idPolice,p.libellePolice,a.id AS idAvenant,
		IFNULL(a.accessoire,0) accessoire,IFNULL(a.accessoireGarant,0) accessoireGarant,IFNULL(a.accessoireGestion,0) accessoireGestion,
		IFNULL(a.accessoireIntermediaire,0) accessoireIntermediaire,IFNULL(a.commissionGestion,0) commissionGestion,IFNULL(a.commission,0) commission,		
		IFNULL(a.commissionAutre,0) commissionAutre,IFNULL(a.commissionIntermediaire,0) commissionIntermediaire,				
		IFNULL(a.primeNette,0) primeNette,IFNULL(a.primeNetteStat,0) primeNetteStat,IFNULL(a.primeHt,0) primeHt, IFNULL(a.taxe,0) taxe,		
		IFNULL(a.fraisCarte,0) fraisCarte,IFNULL(a.primeTtcInitial,0) primeTtcInitial,IFNULL(a.primeTtc,0) primeTtc
	FROM avenant AS a 
	INNER JOIN police AS p ON (a.idPolice=p.id)
	WHERE a.codeTypeAvenant="QTM" 
	-- AND p.codeApporteur='I000003'
	AND p.id=286
)  B
INNER JOIN emission A ON ( (A.idPolice=B.idPolice) )
SET 
A.primeNette = B.primeNette,
A.commissionGestion= B.commissionGestion,
A.commission = B.commission, 
A.commissionAutre= B.commissionAutre, 
A.commissionIntermediaire = B.commissionIntermediaire, 
A.primeNetteStat = B.primeNetteStat,
A.accessoire = B.accessoire,
A.accessoireGarant = B.accessoireGarant,
A.accessoireGestion = B.accessoireGestion,
A.accessoireIntermediaire = B.accessoireIntermediaire,
A.primeHt = B.primeHt, 
A.taxe = B.taxe, 
A.primeTtc = B.primeTtc;


UPDATE  quittance AS e 
INNER JOIN police p ON (p.id=e.idPolice)
INNER JOIN emission AS s ON (s.id=e.idEmission)
	SET 
	    e.dateEffet=s.dateEffet,e.dateEffetPolice=s.dateEffetPolice,e.dateEcheance=s.dateEcheance,e.dateEmission=s.dateEmission,
--	    ,e.primeTtcInitialEmission=s.primeTtcInitialEmission,
--	    e.primeTtcEmission=s.primeTtcEmission,
	    -- e.ordreEcheance=s.ordreEcheance
	    ,e.libelleQuittance=s.libelleQuittance,e.tauxEcheance=s.tauxEcheance,e.datePaiement=s.datePaiement
	    ,e.primeNette=s.primeNette,e.commissionGestion=s.commissionGestion,e.commission=s.commission,e.commissionAutre=s.commissionAutre
	    ,e.primeNetteStat=s.primeNetteStat,e.accessoire=s.accessoire,e.accessoireGarant=s.accessoireGarant,e.primeHt=s.primeHt
	    ,e.primeNetteCie=s.primeNetteCie,e.accessoireCie=s.accessoireCie,e.assesoireApporteur=s.assesoireApporteur,e.chiffreAffaire=s.chiffreAffaire
	    ,e.taxe=s.taxe,e.fraisCarte=s.fraisCarte,e.primeTtcInitial=s.primeTtcInitial,e.tauxInteret=s.tauxInteret,e.interet=s.interet
	    ,e.primeTtc=s.primeTtc,e.dateCre=s.dateCre,e.`user`=s.`user`,e.encaisse=s.encaisse,e.regle=s.regle,e.nbAliment=s.nbAliment,e.dateSysteme=s.dateSysteme
	    ,e.deduireComm=s.deduireComm,e.quittanceNova=s.quittanceNova,e.primeadditionnelle=s.primeadditionnelle,e.accessoireIntermediaire=s.accessoireIntermediaire
	    ,e.accessoireGestion=s.accessoireGestion,e.commissionIntermediaire=s.commissionIntermediaire
WHERE s.codeTypeAvenant='QTM'
-- AND p.codeApporteur='I000003'
AND p.id=286
AND (e.primeNette-s.primeNette)!=0; 




SELECT * FROM avenant WHERE codeTypeAvenant='QTM' ORDER BY id DESC
WHERE idPolice=325


SELECT * FROM beneficiaire
WHERE nomBeneficiaire LIKE '%AVENANT%'
AND idPolice=325

SELECT * FROM police
WHERE id=325



-- 2 --NEWS 
UPDATE (
	SELECT 
		p.id AS idPolice,p.libellePolice,a.id AS idAvenant,
		IFNULL(a.accessoire,0) accessoire,IFNULL(a.accessoireGarant,0) accessoireGarant,IFNULL(a.accessoireGestion,0) accessoireGestion,
		IFNULL(a.accessoireIntermediaire,0) accessoireIntermediaire,IFNULL(a.commissionGestion,0) commissionGestion,IFNULL(a.commission,0) commission,		
		IFNULL(a.commissionAutre,0) commissionAutre,IFNULL(a.commissionIntermediaire,0) commissionIntermediaire,				
		IFNULL(a.primeNette,0) primeNette,IFNULL(a.primeNetteStat,0) primeNetteStat,IFNULL(a.primeHt,0) primeHt, IFNULL(a.taxe,0) taxe,		
		IFNULL(a.fraisCarte,0) fraisCarte,IFNULL(a.primeTtc,0) primeTtcInitial,
		IFNULL(a.primeTtc,0) primeTtc
	FROM beneficiaireprime AS a 
	INNER JOIN police AS p ON (a.idPolice=p.id)
	WHERE a.idBeneficiaire=48462
	AND p.id=325
)  B
INNER JOIN avenant A ON ( (A.idPolice=B.idPolice) AND  (A.id=B.idAvenant) )
SET 
A.primeNette = B.primeNette,
A.commissionGestion= B.commissionGestion,
A.commission = B.commission, 
A.commissionAutre= B.commissionAutre, 
A.commissionIntermediaire = B.commissionIntermediaire, 
A.primeNetteStat = B.primeNetteStat,
A.accessoire = B.accessoire,
A.accessoireGarant = B.accessoireGarant,
A.accessoireGestion = B.accessoireGestion,
A.accessoireIntermediaire = B.accessoireIntermediaire,
A.primeHt = B.primeHt, 
A.taxe = B.taxe, 
A.primeTtc = B.primeTtc
WHERE A.codeTypeAvenant='QTM';


 