INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (61, 'Logo manquant (UR)',
        'Liste des unités de recherche ayant au moins une thèse en cours dont le logo est manquant', 'SELECT *
FROM STRUCTURE
WHERE ID IN (
    SELECT UNITE_RECH.STRUCTURE_ID
    FROM THESE
             LEFT JOIN UNITE_RECH ON THESE.UNITE_RECH_ID = UNITE_RECH.ID
    WHERE THESE.ETAT_THESE=''E''
      AND UNITE_RECH.STRUCTURE_ID IS NOT NULL
    GROUP BY UNITE_RECH.STRUCTURE_ID)
  AND CHEMIN_LOGO IS NULL;', 1, 'STRUCTURE', 'warning');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (62, 'Logo manquant (ED)',
        'Liste des écoles doctorales ayant au moins une thèse en cours dont le logo est absent', 'SELECT *
FROM STRUCTURE
WHERE ID IN (
    SELECT ECOLE_DOCT.STRUCTURE_ID
    FROM THESE
             LEFT JOIN ECOLE_DOCT ON THESE.ECOLE_DOCT_ID = ECOLE_DOCT.ID
    WHERE THESE.ETAT_THESE=''E''
      AND ECOLE_DOCT.STRUCTURE_ID IS NOT NULL
    GROUP BY ECOLE_DOCT.STRUCTURE_ID)
  AND CHEMIN_LOGO IS NULL;', 1, 'STRUCTURE', 'warning');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (1, 'Nombre de thèse dans SyGAL', 'Dénombrement de toutes les thèses peu importe leur états', 'SELECT THESE.ID ID,THESE.ETABLISSEMENT_ID ETABLISSEMENT_ID,THESE.DOCTORANT_ID DOCTORANT_ID,THESE.ECOLE_DOCT_ID ECOLE_DOCT_ID,
       THESE.UNITE_RECH_ID UNITE_RECH_ID,THESE.BESOIN_EXPURGE BESOIN_EXPURGE,THESE.COD_UNIT_RECH COD_UNIT_RECH,
       THESE.CORREC_AUTORISEE CORREC_AUTORISEE,THESE.DATE_AUTORIS_SOUTENANCE DATE_AUTORIS_SOUTENANCE,
       THESE.DATE_FIN_CONFID DATE_FIN_CONFID,THESE.DATE_PREM_INSC DATE_PREM_INSC,THESE.DATE_PREV_SOUTENANCE DATE_PREV_SOUTENANCE,
       THESE.DATE_SOUTENANCE DATE_SOUTENANCE,THESE.ETAT_THESE ETAT_THESE,THESE.LIB_DISC LIB_DISC,THESE.LIB_ETAB_COTUT LIB_ETAB_COTUT,
       THESE.LIB_PAYS_COTUT LIB_PAYS_COTUT,THESE.LIB_UNIT_RECH LIB_UNIT_RECH,THESE.RESULTAT RESULTAT,
       THESE.SOUTENANCE_AUTORIS SOUTENANCE_AUTORIS,THESE.TEM_AVENANT_COTUT TEM_AVENANT_COTUT,
       THESE.TITRE TITRE,THESE.SOURCE_CODE SOURCE_CODE,THESE.SOURCE_ID SOURCE_ID,
       THESE.HISTO_CREATEUR_ID HISTO_CREATEUR_ID,THESE.HISTO_CREATION HISTO_CREATION,
       THESE.HISTO_MODIFICATEUR_ID HISTO_MODIFICATEUR_ID,THESE.HISTO_MODIFICATION HISTO_MODIFICATION,
       THESE.HISTO_DESTRUCTEUR_ID HISTO_DESTRUCTEUR_ID,THESE.HISTO_DESTRUCTION HISTO_DESTRUCTION
FROM THESE THESE;', 1, 'THESE', 'success');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (2, 'Pas de PDC à 2 mois',
        'Pas de page de couverture de valider alors que la soutenance de thèse est dans moins de 2 mois', 'SELECT t.*
FROM THESE T
         LEFT JOIN VALIDATION V ON T.ID = V.THESE_ID
         LEFT JOIN TYPE_VALIDATION N on V.TYPE_VALIDATION_ID = N.ID
WHERE T.DATE_SOUTENANCE > current_date - interval ''2 month''
  AND T.ETAT_THESE = ''E''
  AND N.CODE = ''PAGE_DE_COUVERTURE''
  AND V.ID IS NULL;', 1, 'THESE', 'danger');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (7, 'Doctorant sans email', 'Doctorant ayant une thèse en cours et ne possèdant pas d''email', 'SELECT i.*
FROM THESE t
         JOIN DOCTORANT d ON d.ID = t.DOCTORANT_ID
         JOIN INDIVIDU I on d.INDIVIDU_ID = I.ID
WHERE i.TYPE=''doctorant''
  AND t.ETAT_THESE = ''E''
  AND i.EMAIL is NULL;', 1, 'INDIVIDU', 'danger');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (101, 'Possible abandon', 'Doctorant ayant une thèse en cours dont la date d''inscription remonte à 5 ans et dont la dernière inscription à plus de 12 mois  (date actuelle - 12 moins et année seulement)
', 'select i.*
FROM DOCTORANT d
         join INDIVIDU i ON d.INDIVIDU_ID = i.ID
         left join THESE t ON d.ID = t.DOCTORANT_ID
where t.ETAT_THESE = ''E''
  and t.DATE_PREM_INSC < current_date - interval ''60 month''
  and t.ID IN (
    SELECT THESE_ID
    FROM THESE_ANNEE_UNIV
             JOIN THESE T on THESE_ANNEE_UNIV.THESE_ID = T.ID
    WHERE T.ETAT_THESE = ''E''
    GROUP BY(THESE_ID)
    HAVING MAX(ANNEE_UNIV) < TO_CHAR(current_date - interval ''12 month'', ''yyyy'')::numeric
);', 1, 'INDIVIDU', 'warning');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (3, 'Pas de dépôt à 1 mois', 'Pas de dépôt de réalisé dans SyGAL alors que la soutenance est à moins d''un mois', 'SELECT t.*
FROM THESE T
         LEFT JOIN fichier_these F on T.ID = F.THESE_ID
         LEFT JOIN fichier Fi on Fi.ID = F.fichier_id
         LEFT JOIN NATURE_FICHIER N on Fi.NATURE_ID = N.ID
WHERE T.DATE_SOUTENANCE > LOCALTIMESTAMP - interval ''1 month''
  AND T.ETAT_THESE = ''E''
  AND N.CODE = ''THESE_PDF''
  AND F.ID IS NULL;', 1, 'THESE', 'danger');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (4, 'Thèse en cours avec soutenance dépassée', 'Thèse en cours avec soutenance dépassée', 'SELECT *
FROM THESE t
WHERE t.ETAT_THESE = ''E''
  AND t.DATE_SOUTENANCE < current_date;', 1, 'THESE', 'warning');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (5, 'Thèse avec soutenance à venir', 'Thèse avec soutenance à venir', 'SELECT *
FROM THESE t
WHERE t.ETAT_THESE = ''E''
  AND t.DATE_SOUTENANCE > current_date;', 1, 'THESE', 'info');
INSERT INTO public.indicateur (id, libelle, description, requete, actif, display_as, class)
VALUES (6, 'Thèses hors d''âge', 'Les thèses ayant une durée supérieure à 6 ans posent des soucis ', 'SELECT *
FROM THESE t
WHERE t.ETAT_THESE = ''E''
  AND t.DATE_PREM_INSC < current_date - interval ''72 month'';', 1, 'THESE', 'warning');


CREATE MATERIALIZED VIEW mv_indicateur_61 as SELECT *
FROM STRUCTURE
WHERE ID IN (
    SELECT UNITE_RECH.STRUCTURE_ID
    FROM THESE
             LEFT JOIN UNITE_RECH ON THESE.UNITE_RECH_ID = UNITE_RECH.ID
    WHERE THESE.ETAT_THESE='E'
      AND UNITE_RECH.STRUCTURE_ID IS NOT NULL
    GROUP BY UNITE_RECH.STRUCTURE_ID)
  AND CHEMIN_LOGO IS NULL;
CREATE MATERIALIZED VIEW mv_indicateur_62 as SELECT *
FROM STRUCTURE
WHERE ID IN (
    SELECT ECOLE_DOCT.STRUCTURE_ID
    FROM THESE
             LEFT JOIN ECOLE_DOCT ON THESE.ECOLE_DOCT_ID = ECOLE_DOCT.ID
    WHERE THESE.ETAT_THESE='E'
      AND ECOLE_DOCT.STRUCTURE_ID IS NOT NULL
    GROUP BY ECOLE_DOCT.STRUCTURE_ID)
  AND CHEMIN_LOGO IS NULL;
CREATE MATERIALIZED VIEW mv_indicateur_1 as SELECT THESE.ID ID,THESE.ETABLISSEMENT_ID ETABLISSEMENT_ID,THESE.DOCTORANT_ID DOCTORANT_ID,THESE.ECOLE_DOCT_ID ECOLE_DOCT_ID,
       THESE.UNITE_RECH_ID UNITE_RECH_ID,THESE.BESOIN_EXPURGE BESOIN_EXPURGE,THESE.COD_UNIT_RECH COD_UNIT_RECH,
       THESE.CORREC_AUTORISEE CORREC_AUTORISEE,THESE.DATE_AUTORIS_SOUTENANCE DATE_AUTORIS_SOUTENANCE,
       THESE.DATE_FIN_CONFID DATE_FIN_CONFID,THESE.DATE_PREM_INSC DATE_PREM_INSC,THESE.DATE_PREV_SOUTENANCE DATE_PREV_SOUTENANCE,
       THESE.DATE_SOUTENANCE DATE_SOUTENANCE,THESE.ETAT_THESE ETAT_THESE,THESE.LIB_DISC LIB_DISC,THESE.LIB_ETAB_COTUT LIB_ETAB_COTUT,
       THESE.LIB_PAYS_COTUT LIB_PAYS_COTUT,THESE.LIB_UNIT_RECH LIB_UNIT_RECH,THESE.RESULTAT RESULTAT,
       THESE.SOUTENANCE_AUTORIS SOUTENANCE_AUTORIS,THESE.TEM_AVENANT_COTUT TEM_AVENANT_COTUT,
       THESE.TITRE TITRE,THESE.SOURCE_CODE SOURCE_CODE,THESE.SOURCE_ID SOURCE_ID,
       THESE.HISTO_CREATEUR_ID HISTO_CREATEUR_ID,THESE.HISTO_CREATION HISTO_CREATION,
       THESE.HISTO_MODIFICATEUR_ID HISTO_MODIFICATEUR_ID,THESE.HISTO_MODIFICATION HISTO_MODIFICATION,
       THESE.HISTO_DESTRUCTEUR_ID HISTO_DESTRUCTEUR_ID,THESE.HISTO_DESTRUCTION HISTO_DESTRUCTION
FROM THESE THESE;
CREATE MATERIALIZED VIEW mv_indicateur_2 as SELECT t.*
FROM THESE T
         LEFT JOIN VALIDATION V ON T.ID = V.THESE_ID
         LEFT JOIN TYPE_VALIDATION N on V.TYPE_VALIDATION_ID = N.ID
WHERE T.DATE_SOUTENANCE > current_date - interval '2 month'
  AND T.ETAT_THESE = 'E'
  AND N.CODE = 'PAGE_DE_COUVERTURE'
  AND V.ID IS NULL;
CREATE MATERIALIZED VIEW mv_indicateur_7 as SELECT i.*
FROM THESE t
         JOIN DOCTORANT d ON d.ID = t.DOCTORANT_ID
         JOIN INDIVIDU I on d.INDIVIDU_ID = I.ID
WHERE i.TYPE='doctorant'
  AND t.ETAT_THESE = 'E'
  AND i.EMAIL is NULL;
CREATE MATERIALIZED VIEW mv_indicateur_101 as select i.*
FROM DOCTORANT d
         join INDIVIDU i ON d.INDIVIDU_ID = i.ID
         left join THESE t ON d.ID = t.DOCTORANT_ID
where t.ETAT_THESE = 'E'
  and t.DATE_PREM_INSC < current_date - interval '60 month'
  and t.ID IN (
    SELECT THESE_ID
    FROM THESE_ANNEE_UNIV
             JOIN THESE T on THESE_ANNEE_UNIV.THESE_ID = T.ID
    WHERE T.ETAT_THESE = 'E'
    GROUP BY(THESE_ID)
    HAVING MAX(ANNEE_UNIV) < TO_CHAR(current_date - interval '12 month', 'yyyy')::numeric
);
CREATE MATERIALIZED VIEW mv_indicateur_3 as SELECT t.*
FROM THESE T
         LEFT JOIN fichier_these F on T.ID = F.THESE_ID
         LEFT JOIN fichier Fi on Fi.ID = F.fichier_id
         LEFT JOIN NATURE_FICHIER N on Fi.NATURE_ID = N.ID
WHERE T.DATE_SOUTENANCE > LOCALTIMESTAMP - interval '1 month'
  AND T.ETAT_THESE = 'E'
  AND N.CODE = 'THESE_PDF'
  AND F.ID IS NULL;
CREATE MATERIALIZED VIEW mv_indicateur_4 as SELECT *
FROM THESE t
WHERE t.ETAT_THESE = 'E'
  AND t.DATE_SOUTENANCE < current_date;
CREATE MATERIALIZED VIEW mv_indicateur_5 as SELECT *
FROM THESE t
WHERE t.ETAT_THESE = 'E'
  AND t.DATE_SOUTENANCE > current_date;
CREATE MATERIALIZED VIEW mv_indicateur_6 as SELECT *
FROM THESE t
WHERE t.ETAT_THESE = 'E'
  AND t.DATE_PREM_INSC < current_date - interval '72 month';