05 ‐ Partage d'indicateurs

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

Formations

Action de formations ayant des abonnements

L'idée est de pouvoir repérer les actions de formation pour lesquels ils seraient utile de proposer une nouvelle session de formation.

select 
    max(f.id) as id, 
    max(f.libelle) as libelle, 
    count(*) as nb
from formation_formation_abonnement a
join formation f on a.formation_id = f.id
group by formation_id

Il est possible de compléter cela en indiquant un seuil minimal d'abonnement (à UCN on envisage un minimum de six abonnements).

Absences non justifiées

Le but est d'identifier des agents "souvent" absents à des formations sans raison.

select
    concat (coalesce(max(a.nom_usage), max(s.nom)), ' ', coalesce(max(a.prenom), max(s.prenom))) as Denimination,
    max(f.libelle) as Formation,
    array_agg(seance.jour) as seance
from formation_presence fp
join formation_inscription fi on fp.inscription_id = fi.id
join formation_instance session on fi.session_id = session.id
join formation f on session.formation_id = f.id
left join agent a on fi.agent_id = a.c_individu
left join formation_stagiaire_externe s on fi.stagiaire_id = s.id
left join formation_seance seance on session.id = seance.instance_id
where 
    statut = 'ABSENCE_NON_JUSTIFIEE'
and seance.jour > CURRENT_DATE - INTERVAL '1 year'
group by inscription_id

Ici la fenêtre glissante est de 1 an, libre aux établissements de modifier cette fenêtre glissante.

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

Bilbiothèque UniCaen

Macros et usages de celles-ci

SELECT
    count(urt.code) as nb_usage,
    urm.code as macro_code,
    array_agg(urt.code) as templates
FROM  unicaen_renderer_macro urm
left JOIN unicaen_renderer_template urt
                 ON (urt.document_corps LIKE concat('%',urm.code,'%') OR urt.document_sujet LIKE concat('%',urm.code,'%'))
group by urm.code