1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
|
select distinct concat( "
") as Qui
, if(cat32.STATUT is null," ", cat32.STATUT) as ""
from
(SELECT distinct cybele_secteur_agent.CODE_AGENT, members.nom , members.prenom FROM cybele_secteur_agent
inner join members on members.id=cybele_secteur_agent.code_agent WHERE cybele_secteur_agent.CODE_SECTEUR and cybele_secteur_agent.CODE_AGENT in (0,138,139,150,151,154,115))
as agent
left join
(select cybele_hab_agent.CODE_AGENT, cybele_hab_agent.CODE_CAT_HAB, cybele_hab_agent.DATE_FORM, cybele_hab_agent.DATE_HAB, cybele_hab_agent.DATE_DESD_HAB_AGENT
, if(cybele_hab_agent.DATE_DESD_HAB_AGENT is not null
,concat("+")
, if( (susp.DATE_RETOUR_AGENT is null and datediff(now() ,susp.mDATE_SUSP_AGENT) > cybele_cat_hab.DELAIS_REHAB) or (susp.DATE_RETOUR_AGENT is not null and datediff(susp.DATE_RETOUR_AGENT ,susp.mDATE_SUSP_AGENT) > cybele_cat_hab.DELAIS_REHAB) , if(maint.mDATE_MAINT is not null , concat("a Rehabiliter") , concat("Non Habilite")) , if(maint.mDATE_MAINT is not null and datediff(now(),maint.mDATE_MAINT) < DELAIS-AJUSTEMENT , if(maint.PARTIEL=1 , concat("Habilite Partiellement") , concat("Habilite")) , if(maint.mDATE_MAINT is not null and datediff(now() ,maint.mDATE_MAINT) between DELAIS-AJUSTEMENT and DELAIS+AJUSTEMENT , concat("a Maintenir") , if(maint.mDATE_MAINT is not null and datediff(now()
,maint.mDATE_MAINT) > DELAIS+AJUSTEMENT , concat("Depasse") , if(maint.mDATE_MAINT is null and (cybele_hab_agent.DATE_FORM is not null or cybele_hab_agent.VAE=1) , concat("Formation") , concat("Non Habilite")))))) ) as STATUT
from cybele_hab_agent
inner join cybele_cat_hab on cybele_cat_hab.code_cat_hab=cybele_hab_agent.CODE_CAT_HAB
left JOIN
(select cybele_agent_susp.CODE_MEMBERS, cybele_agent_susp.DATE_SUSP_AGENT as mDATE_SUSP_AGENT, cybele_agent_susp.DATE_RETOUR_AGENT
from cybele_agent_susp
inner join
(SELECT CODE_MEMBERS, max(DATE_SUSP_AGENT) as MaxD
FROM cybele_agent_susp
group by CODE_MEMBERS)
as MaxSusp on cybele_agent_susp.CODE_MEMBERS=MaxSusp.CODE_MEMBERS
and cybele_agent_susp.DATE_SUSP_AGENT=MaxSusp.MaxD)
as susp on cybele_hab_agent.CODE_AGENT=susp.CODE_MEMBERS
left JOIN
(select cybele_maint_agent.CODE_AGENT
, cybele_maint_agent.CODE_CAT_HAB
, cybele_maint_agent.DATE_MAINT as mDATE_MAINT
, cybele_maint_agent.COMMENTAIRE_MAINT
, cybele_maint_agent.PARTIEL from cybele_maint_agent
inner join
(SELECT CODE_AGENT, CODE_CAT_HAB, max(DATE_MAINT) as MaxD
FROM cybele_maint_agent
group by CODE_AGENT, CODE_CAT_HAB)
as MaxMaint on cybele_maint_agent.CODE_AGENT=MaxMaint.CODE_AGENT
and cybele_maint_agent.CODE_CAT_HAB=MaxMaint.CODE_CAT_HAB
and cybele_maint_agent.DATE_MAINT=MaxMaint.MaxD)
as maint on cybele_hab_agent.CODE_AGENT=maint.CODE_AGENT
and cybele_cat_hab.CODE_CAT_HAB=maint.CODE_CAT_HAB
where cybele_hab_agent.CODE_CAT_HAB=32)
as cat32 on agent.code_agent=cat32.code_agent |
Partager