-- AND a.nomBeneficiaire NOT LIKE "%AVENANT%"

SELECT g.* 
FROM gc_assureur g
JOIN gc_typegarant t ON (t.id=g.typeGarant)
AND g.typeGarant=1

SELECT *

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


SELECT  c.*
FROM emission c
INNER JOIN quittance q ON ((c.idPolice=q.idPolice) AND (c.id=q.idEmission))
INNER JOIN police p ON (p.id=c.idPolice)
WHERE c.codeTypeAvenant='QTM'
AND p.id IN (328,323,339)

SELECT * FROM avenant
WHERE idPolice=229 

-- POUR LES COMPAGNIES

SELECT * FROM police WHERE codeGcAssureur LIKE '%saarpre%'

-- CALL sp_evolution_rsp_rp('ALLIANZCI',NULL,NULL,NULL,"2023-01-01","2023-03-31",30)

-- PROC 0 -- REVOLUTION DES SINISTRES / PRIMES
CALL sp_evolution_depensePrime_rp (NULL,NULL,229,NULL,NULL,"2023-02-01","2023-03-31","QTM",30) -- 0 : 65 115  -- 167 453  -- 252 877  -- 21 332 746

-- PROC 1 -- RECAPTILATIF GLOBAL DES PRIMES PAR GARANT / MOIS 
CALL sp_fiche_recap_emission_global_rp('GNA',NULL,NULL,'2023-01-01','2023-12-31',"QTM",30)  -- 411.075.027 


CALL sp_fiche_recap_emission_global_GARANT_rp('GNA',NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)  -- 411.075.027 

-- PROC 2 -- RECAPTILATIF GLOBAL DES PRIMES PAR GARANT / MOIS 
CALL sp_fiche_recap_emission_detailPolice_rp('GNA',NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)  -- 411.075.027 



CALL sp_fiche_recap_emission_detailcumilsouscripeur_rp ('GNA',NULL,NULL,'2023-01-01','2023-03-31',"QTM",30) -- 411.075.027  / M : 177 272 969  -- A REVOIR AFFICHAGE DOUBLE


CALL sp_fiche_recap_emission_detailcumilapporteur_rp  ('GNA',NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)  -- 411.075.027  / M : 177 272 969  -- A REVOIR AFFICHAGE DOUBLE


CALL sp_fiche_recap_emission_detailcumilpolice_rp (229,'2023-01-01','2023-01-31',"QTM",30)  -- 411.075.027  / M : 177 272 969  -- A REVOIR AFFICHAGE DOUBLE

     

-- PROC 3 -- DETAIL PRIME / MONTANT / RSP / POLICE  / AJUSTEMENT  // RESULTAT TECHNIQUE
CALL sp_s_rapportsp_periodeNews_rp('GNAANS',NULL,NULL,NULL,'2023-01-01','2023-12-31',"QTM",30) -- 411.075.027 / M : 177 272 969


-- CALL sp_s_rapportsp_periode_rp (NULL,NULL,NULL,NULL,'2023-01-01','2023-03-31',"QTM",30) -- 411.075.027 / M : 177 272 969
	

-- PROC 4 -- RESULTAT TECHNIQUECICE COMPAGNIE / COURITER / CATEGORIE / POLICE / RSP /AJUSTEMENT
CALL sp_s_rapportsp_periodeAjustem_rp('GNA',NULL,NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)

-- PROC 5 -- RECAPITUALTIF COMPAGNIE / APPORTEUR / AJUSTEMENT
CALL sp_s_rapportsp_compagnie_rp('GNA',NULL,NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)   -- 411.075.027 / M : 177 272 969

-- PROC 6 -- RAPPORT SINISTRE / COURITER /  POLICE / RSP  
CALL sp_s_rapportsp_periodeCourtier_rp('GNA',NULL,NULL,NULL,'2023-01-01','2023-03-31',"QTM",30) -- 411.075.027 / M : 177 272 969

-- PROC 7 -- RAPPORT SINISTRE / POLICE / RSP  
CALL sp_s_rapportsp_periode_export_rp('GNA',NULL,NULL,NULL,'2023-01-01','2023-03-31',"QTM",30)  -- 411.075.027  / M : 177 272 969



-- POUR LES AUTOFIANCEMENTS
CALL sp_s_rapportsp_periodeAuto_rp (NULL,184,'2023-01-01','2022-08-31',20); -- 406 011 870


CALL `sp_Ft1_elementAutoMutuelle_rp`(NULL,184,'2023-01-01','2022-08-31')


-- EDITION DES CONTRATS
-- 1 -- CALL sp_edition_avenant_primes () -- idAvenant  // CONDITION PRTICUELIERES
--  CALL get_edition_avenant_primes_detail () -- idAvenant  // EDITION DES DETAILS DES QUITTANCES 

SELECT codeApporteur,libellePolice,codeGcAssureur FROM police  WHERE codeApporteur  NOT IN ( SELECT codeApporteur FROM apporteur )

SELECT * FROM apporteur  WHERE codeApporteur="PHAMAM BROKER"

UPDATE police SET  codeApporteur="I000010"   WHERE codeApporteur='UNAP'

SELECT * FROM  




UPDATE police p
INNER JOIN apporteur a ON (p.codeApporteur=a.codeApporteur)
SET p.codeApporteur=a.codeApporteur



CALL sp_edition_avenant_primes (772)

-- SURVEILLANCE DE LA FACTURATION
-- 1-- DECOMPTE GARANTS / POLICES ACTIVES  

SELECT g.codeGcAssureur,COUNT(p.id) nbrePolice
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
GROUP BY g.codeGcAssureur
ORDER BY nbrePolice DESC


SELECT g.codeGcAssureur,p.codeApporteur,p.id,p.libellePolice,p.codeEtatPolice
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
ORDER BY g.codeGcAssureur,p.codeApporteur DESC


SELECT g.codeGcAssureur,p.codeApporteur,a.`libelle` AS apporteur,p.id,p.numeroPolice,p.libellePolice,e.dateEffet,e.dateEcheance,e.dateEmission,e.primeNette
,p.tauxCommissionGerant
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
INNER JOIN `apporteur` a ON (a.codeApporteur=p.codeApporteur)
INNER JOIN emission e ON (e.idPolice=p.id)
INNER JOIN entetecontrat c ON (p.id=c.idPolice)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
AND e.codeTypeAvenant="QTM"
AND p.CodeGcAssureur='SAAR'
AND c.`exercice`=2023 AND c.enCours=1 AND c.dateEffet=e.dateEffet
AND c.id=fn_id_entetecontrat_police_en_cours(c.idPolice)
ORDER BY g.codeGcAssureur,p.codeApporteur DESC

-- 2--DECOMPTE GARANTS / POLICES ACTIVES / EMISSIONS DE QUITTANCE DE FACTURATION

SELECT g.codeGcAssureur,COUNT(DISTINCT p.id) nbrePolice
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
INNER JOIN emission e ON (e.idPolice=p.id)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
AND e.codeTypeAvenant="QTM"
GROUP BY g.codeGcAssureur
ORDER BY nbrePolice DESC



SELECT * FROM entetecontrat
WHERE idPolice=229 AND `exercice`=2023

SELECT fn_id_entetecontrat_police_en_cours(229)

-- 3-- DECOMPTE GARANTS / POLICES ACTIVES / PAS EMISSIONS DE QUITTANCE DE FACTURATION

SELECT g.codeGcAssureur,COUNT(DISTINCT p.id) nbrePolice
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
AND p.id NOT IN ( SELECT e.idPolice FROM emission e WHERE  e.codeTypeAvenant="QTM")
GROUP BY g.codeGcAssureur
ORDER BY nbrePolice DESC


SELECT g.codeGcAssureur,p.codeApporteur,a.`libelle` AS apporteur,p.id,p.numeroPolice,p.libellePolice,c.dateEffet,c.dateFin AS dateEcheance,' ' dateEmission,0 primeNette
,p.tauxCommissionGerant
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
INNER JOIN `apporteur` a ON (a.codeApporteur=p.codeApporteur)
INNER JOIN entetecontrat c ON (p.id=c.idPolice)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
AND c.`exercice`=2023 AND c.enCours=1  
AND c.id=fn_id_entetecontrat_police_en_cours(c.idPolice)
AND p.id NOT IN ( SELECT e.idPolice 
		  FROM emission e 
		  INNER JOIN entetecontrat c ON (c.idPolice=e.idPolice)
		  WHERE  e.codeTypeAvenant="QTM"
		  AND c.`exercice`=2023 AND c.enCours=1 AND c.dateEffet=e.dateEffet
		  AND c.id=fn_id_entetecontrat_police_en_cours(c.idPolice)
		  )
ORDER BY g.codeGcAssureur,p.codeApporteur DESC


 
SELECT * FROM emission WHERE codeTypeAvenant="QTM" AND idPolice=179;

SELECT * FROM quittance WHERE codeTypeAvenant="QTM" AND idPolice=179;

SELECT b.* 
FROM  beneficiaireprime b
INNER JOIN beneficiaire r ON ((r.idPolice=b.idPolice) AND (r.id=b.id))
INNER JOIN avenant a ON (a.idPolice=b.idPolice)
-- INNER JOIN emission e ON (a.idPolice=e.idPolice)
-- INNER JOIN quittance q ON (a.idPolice=q.idPolice)
WHERE a.codeTypeAvenant IN ("QTM") AND r.nombeneficiaire LIKE "%AVENANT%";
