Partage d'indicateurs

Page recensant de indicateurs utilisés par des établissements pouvant être repris.

Agent·es

Agent·es sans affectation principale

Pour pouvoir calculer les chaînes hiérarchiques depuis les structures il est "nécessaire" que les agent·es aient une affectation principale.

select 
    max(a.c_individu) as c_individu, 
    max(a.prenom) as prenom, 
    max(a.nom_usage) as nom, 
    count(aca.id) as nb
from agent a
left join agent_carriere_affectation aca on a.c_individu = aca.agent_id and aca.t_principale = 'O'
where a.deleted_on IS NULL AND aca.deleted_on IS NULL
and   (aca.date_debut IS NULL OR aca.date_debut < now())
and   (aca.date_fin IS NULL OR aca.date_fin > now())
group by a.c_individu
having count(aca) = 0

Chaînes hiérarchiques

Lister les autorité·s et les agent·es associé·es en cours de validité.

Les autorités sont stockées dans la table agent_hierarchie_autorite. Il est nécessaire de s'assurer que la chaine est active (grâce aux date_debut et date_fin) et de s'assurer que la donnée n'a pas été supprimée ou historisée (via les champs deleted_on et histo_destruction). Il est a noter ici que les matricule SIHAM sont aussi remontés.

select 
       ahd.agent_id AS AGENT_ID, ahd.autorite_id as AUTORITE_ID, 
       TO_CHAR(ahd.date_debut,'dd/mm/yyyy') as DEBUT, TO_CHAR(ahd.date_fin,'dd/mm/yyyy') as FIN,
       (aagent.prenom || ' ' || coalesce(aagent.nom_usage, aagent.nom_famille)) as AGENT, aaref.id_source AS AGENT_MAT,
       (aautorite.prenom || ' ' || coalesce(aautorite.nom_usage, aautorite.nom_famille)) as AUTORITE, asref.id_source AS AUTORITE_MAT
from agent_hierarchie_autorite ahd
join agent aagent on ahd.agent_id = aagent.c_individu
left join agent_ref aaref on aaref.agent_id = aagent.c_individu
join agent aautorite on ahd.autorite_id = aautorite.c_individu
left join agent_ref asref on asref.agent_id = aautorite.c_individu
where ahd.histo_destruction IS NULL and ahd.deleted_on IS NULL
  and (ahd.date_debut IS NULL OR ahd.date_debut <= now())
  and (ahd.date_fin IS NULL OR ahd.date_fin >= now())
  and aagent.deleted_on IS NULL and aautorite.deleted_on IS NULL
  and aaref.deleted_on IS NULL and aaref.source='SIHAM'
  and asref.deleted_on IS NULL and asref.source='SIHAM'

Lister les supérieur·es et les agent·es associé·es en cours de validité.

De même que pour les autorités à la seule différence que les supérieur·es sont stocké·es dans la table agent_hierarchie_superieur.

select 
       shd.agent_id AS AGENT_ID, shd.superieur_id as SUPERIEUR_ID, 
       TO_CHAR(shd.date_debut,'dd/mm/yyyy') as DEBUT, TO_CHAR(shd.date_fin,'dd/mm/yyyy') as FIN,
       (aagent.prenom || ' ' || coalesce(aagent.nom_usage, aagent.nom_famille)) as AGENT, aaref.id_source AS AGENT_MAT,
       (asuperieur.prenom || ' ' || coalesce(asuperieur.nom_usage, asuperieur.nom_famille)) as SUPERIEUR, asref.id_source AS SUPERIEUR_MAT
from agent_hierarchie_superieur shd
join agent aagent on shd.agent_id = aagent.c_individu
left join agent_ref aaref on aaref.agent_id = aagent.c_individu
join agent asuperieur on shd.superieur_id = asuperieur.c_individu
left join agent_ref asref on asref.agent_id = asuperieur.c_individu
where shd.histo_destruction IS NULL and shd.deleted_on IS NULL
  and (shd.date_debut IS NULL OR shd.date_debut <= now())
  and (shd.date_fin IS NULL OR shd.date_fin >= now())
  and aagent.deleted_on IS NULL and asuperieur.deleted_on IS NULL
  and aaref.deleted_on IS NULL and aaref.source='SIHAM'
  and asref.deleted_on IS NULL and asref.source='SIHAM'

Entretien professionnel

Lister les entretiens professionnels d'une campagne et leur état d'avancement

La table entretienprofessionnel contient la liste de tous les entretiens professionnels enregistrés dans l'application. Il est possible d'isoler les entretiens d'une campagne spécifique grâce à la jointure sur la table entretienprofessionnel_campagne.

L'état d'avancement de l'entretien est représenté par une instance d'état qui est listé dans la table de linkage entretienprofessionnel_etat. Cette table pointe vers une instance qui possède un type d'état (En attente de validation de l'agent·e, Convocation ...). Attention, les états précédents sont historisés et seul l'état en cours est non historisé ; ainsi pour connaitre l'état courant il faut considérer celui dans la colonne histo_destruction est vide.

Enfin pour récupérer les informations liés à l'agent·e on peut exploiter la jointure avec la table agent via la colonne agent_id de la table entretienprofessionnel. À partir de cette table on peut alors piocher les informations nécessaire comme par exemple la structure principale d'affectation.

N.B. :

• pour les informations sur le responsable de l'entretien on peut exploiter la colonne responsable_id dans la table entretienprofessionnel
• attention les données synchronisées et retirées auront une valeur dans la colonne deleted_on (à savoir la date de retrait)
• attention les données maîtrisées par EMC2 et supprimées auront un valeur dans la colonne histo_destruction (à savoir la date de suppression)

select
    a.c_individu as AGENT_ID, concat(a.prenom, ' ', coalesce(a.nom_usage, a.nom_famille)) as AGENT_DENOMINATION,
    s.id as STRUCTURE_ID, s.libelle_court as STRUCTURE_LIBELLE,
    uet.libelle as etat
from entretienprofessionnel e
join entretienprofessionnel_campagne c on e.campagne_id = c.id
left join entretienprofessionnel_etat ee on e.id = ee.entretien_id
left join unicaen_etat_instance uei on ee.etat_id = uei.id
left join unicaen_etat_type uet on uei.type_id = uet.id
join agent a on e.agent = a.c_individu
join agent_carriere_affectation aca on a.c_individu = aca.agent_id
join structure s on aca.structure_id = s.id
where
    c.annee='2023/2024'
and e.histo_destruction IS NULL
and a.deleted_on IS NULL
and aca.date_debut < e.date_entretien AND (aca.date_fin IS NULL OR aca.date_fin > e.date_entretien)
and aca.t_principale = 'O'
and aca.deleted_on IS NULL
and uei.histo_destruction IS NULL

Lister le personnel BIATSS à une date donnée (pendant la campagne)

Comme précédemment on va utiliser la table agent et les tables agent_carriere_affectation et agent_carriere_statut.

WITH date_reference AS (
    SELECT c.date_debut AS date_ref from entretienprofessionnel_campagne c where c.annee='2023/2024'
)
SELECT
    a.c_individu AS AGENT_ID,
    CONCAT(a.prenom, ' ', COALESCE(a.nom_usage, a.nom_famille)) AS AGENT_DENOMINATION,
    s.id AS STRUCTURE_ID,
    s.libelle_court AS STRUCTURE_LIBELLE
FROM agent a
         JOIN agent_carriere_affectation aca ON a.c_individu = aca.agent_id
         JOIN agent_carriere_statut acs ON a.c_individu = acs.agent_id
         JOIN structure s ON aca.structure_id = s.id
         JOIN date_reference dr ON 1 = 1
WHERE
    a.deleted_on IS NULL
  AND aca.deleted_on IS NULL
  AND aca.date_debut < dr.date_ref AND (aca.date_fin IS NULL OR aca.date_fin > dr.date_ref)
  AND aca.t_principale = 'O'
  AND acs.deleted_on IS NULL
  AND acs.d_debut < dr.date_ref AND (acs.d_fin IS NULL OR acs.d_fin > dr.date_ref)
  AND acs.t_administratif = 'O';

Lister les agents n'ayant pas d'entretien professionnel

On peut maintenant utiliser les deux requêtes précédentes.

N.B.: La page de l'indicateur permet ensuite de faire les filtrages nécessaires par structure ou par agent.

WITH date_reference AS (
    SELECT c.date_debut AS date_ref from entretienprofessionnel_campagne c where c.annee='2023/2024'
)
SELECT
    a.c_individu AS AGENT_ID,
    CONCAT(a.prenom, ' ', COALESCE(a.nom_usage, a.nom_famille)) AS AGENT_DENOMINATION,
    s.id AS STRUCTURE_ID,
    s.libelle_court AS STRUCTURE_LIBELLE
FROM agent a
         JOIN agent_carriere_affectation aca ON a.c_individu = aca.agent_id
         JOIN agent_carriere_statut acs ON a.c_individu = acs.agent_id
         JOIN structure s ON aca.structure_id = s.id
         JOIN date_reference dr ON 1 = 1
WHERE
    a.deleted_on IS NULL
  AND aca.deleted_on IS NULL
  AND aca.date_debut < dr.date_ref AND (aca.date_fin IS NULL OR aca.date_fin > dr.date_ref)
  AND aca.t_principale = 'O'
  AND acs.deleted_on IS NULL
  AND acs.d_debut < dr.date_ref AND (acs.d_fin IS NULL OR acs.d_fin > dr.date_ref)
  AND acs.t_administratif = 'O'
  AND a.c_individu not in (
    select
        a.c_individu
    from entretienprofessionnel e
             join entretienprofessionnel_campagne c on e.campagne_id = c.id
             left join entretienprofessionnel_etat ee on e.id = ee.entretien_id
             left join unicaen_etat_instance uei on ee.etat_id = uei.id
             left join unicaen_etat_type uet on uei.type_id = uet.id
             join agent a on e.agent = a.c_individu
             join agent_carriere_affectation aca on a.c_individu = aca.agent_id
             join structure s on aca.structure_id = s.id

    where
        c.annee='2023/2024'
      and e.histo_destruction IS NULL
      and aca.date_debut < e.date_entretien AND (aca.date_fin IS NULL OR aca.date_fin > e.date_entretien)
      and aca.t_principale = 'O'
      and aca.deleted_on IS NULL
      and a.deleted_on IS NULL
      and uei.histo_destruction IS NULL
)

Pour aller plus loin, la sélection des agents ci-dessus est très/trop rapide. Il faudrait considérer les critères d' "exclusion" de la campagne de votre établissement :

• le témoin contrat long (indiquant qu'un agent est en contrat pour au moins 12 moins) : porté par la colonne t_contrat_long de la table agent.
• des statuts particuliers qui pourrait être exclus (par exemple détachement sortant ou entrant) : voir la liste des témoins disponible dans la table agent_carriere_statut.
• un critère d' "ancienneté" sur le poste (par exemple au moins 3 mois en poste pour que l'entretien soit pertinent) : soit via l'affectation agent_carriere_affectation ou le grade agent_carriere_grade.
• considérer les situations de blocage ou de forçage : situation noté dans la table entretienprofessionnel_agent_force