DROP MATERIALIZED VIEW public."INTER_CALENDRIER_VUE";

CREATE MATERIALIZED VIEW public."INTER_CALENDRIER_VUE"
AS SELECT row_number() OVER () AS id,
    tab."idSeanceSonate",
    tab."idEvenementSonate",
    tab."idUtilisateurSonate",
    string_agg(DISTINCT tab."idSalle"::text, ', '::text) AS "idSalle",
    tab."idModule",
    tab."NomModule",
    tab."NomSeance",
    tab."DateDebut",
    tab."DateFin",
    string_agg(DISTINCT tab."NomProfesseur", ', '::text) AS "NomProfesseur",
    string_agg(DISTINCT tab."Salle"::text, ', '::text) AS "Salle",
    string_agg(DISTINCT tab."CodeSalle"::text, ', '::text) AS "CodeSalle",
    tab."Type",
    tab.nom,
    tab.prenom,
    tab.pseudocentrale,
    string_agg(DISTINCT tab."GroupeTD"::text, '|'::text) AS "GroupeTD",
    tab.mailcentrale
   FROM ( SELECT DISTINCT seance.id_seance AS "idSeanceSonate",
            0 AS "idEvenementSonate",
                CASE
                    WHEN usrprof.id_user IS NULL THEN 0::bigint
                    ELSE usrprof.id_user
                END AS "idUtilisateurSonate",
            salle.id_salle AS "idSalle",
            mo.id_module AS "idModule",
            mo.nom AS "NomModule",
            seance.nom AS "NomSeance",
            ((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "DateDebut",
            ((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "DateFin",
            (usrprof.nom::text || ' '::text) || initcap(usrprof.prenom::text) AS "NomProfesseur",
            salle.nom AS "Salle",
            salle.code AS "CodeSalle",
            typseance.nom AS "Type",
            usrprof.nom,
            initcap(usrprof.prenom::text) AS prenom,
            usrprof.logindomaine AS pseudocentrale,
            usrprof.mailgemgrh AS mailcentrale,
            gpe_td.nom AS "GroupeTD"
           FROM "ERP_Seance" seance
             LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
             LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
             LEFT JOIN "ERP_InstanceModule" imo ON seance_relle.id_instancemodule = imo.id_instancemodule
             JOIN "ERP_AnneeScolaire" ann_scol ON imo.id_debutanneescolaire = ann_scol.id_anneedebut AND ann_scol.encours = true
             LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
             LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
             LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
             LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
             LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
             LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
             LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
             LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
             LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
             LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
             LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
             LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
        UNION
         SELECT DISTINCT seance.id_seance AS "idSeanceSonate",
            0 AS "idEvenementSonate",
                CASE
                    WHEN usr.id_user IS NULL THEN 0::bigint
                    ELSE usr.id_user
                END AS "idUtilisateurSonate",
            salle.id_salle AS "idSalle",
            mo.id_module AS "idModule",
            mo.nom AS "NomModule",
            seance.nom AS "NomSeance",
            ((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "DateDebut",
            ((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "DateFin",
            (usrprof.nom::text || ' '::text) || initcap(usrprof.prenom::text) AS "NomProfesseur",
            salle.nom AS "Salle",
            salle.code AS "CodeSalle",
            typseance.nom AS "Type",
            usr.nom,
            initcap(usr.prenom::text) AS prenom,
            usr.logindomaine AS pseudocentrale,
            usr.mailgemgrh AS mailcentrale,
            gpe_td.nom AS "GroupeTD"
           FROM "ERP_Seance" seance
             LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
             LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
             LEFT JOIN "ERP_InstanceModule" imo ON iim.id_instancemodule = imo.id_instancemodule
             JOIN "ERP_AnneeScolaire" ann_scol ON imo.id_debutanneescolaire = ann_scol.id_anneedebut AND ann_scol.encours = true
             LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
             LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
             LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
             LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
             LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
             LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
             LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
             LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
             LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
             LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
             LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
             LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
          WHERE igpe_td.id_instancegroupetd = seance_relle.id_instancegroupetd
        UNION
         SELECT DISTINCT seance.id_seance AS "idSeanceSonate",
            0 AS "idEvenementSonate",
                CASE
                    WHEN usr.id_user IS NULL THEN 0::bigint
                    ELSE usr.id_user
                END AS "idUtilisateurSonate",
            salle.id_salle AS "idSalle",
            mo.id_module AS "idModule",
            mo.nom AS "NomModule",
            seance.nom AS "NomSeance",
            ((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "DateDebut",
            ((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "DateFin",
            (usrprof.nom::text || ' '::text) || initcap(usrprof.prenom::text) AS "NomProfesseur",
            salle.nom AS "Salle",
            salle.code AS "CodeSalle",
            typseance.nom AS "Type",
            usr.nom,
            initcap(usr.prenom::text) AS prenom,
            usr.logindomaine AS pseudocentrale,
            usr.mailgemgrh AS mailcentrale,
            gpe_td.nom AS "GroupeTD"
           FROM "ERP_Seance" seance
             LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
             LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
             JOIN "ERP_GPCOM_IIM" gp_comp ON iim.id_inscriptioninstancemodule = gp_comp.id_inscriptioninstancemodule
             LEFT JOIN "ERP_InstanceModule" imo ON iim.id_instancemodule = imo.id_instancemodule
             JOIN "ERP_AnneeScolaire" ann_scol ON imo.id_debutanneescolaire = ann_scol.id_anneedebut AND ann_scol.encours = true
             LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
             LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
             LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
             LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
             LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
             LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
             LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
             LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
             LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
             LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND gp_comp.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
             LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
             LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
          WHERE igpe_td.id_instancegroupetd = seance_relle.id_instancegroupetd
        UNION
         SELECT DISTINCT seance.id_seanceevaluation AS "idSeanceSonate",
            0 AS "idEvenementSonate",
                CASE
                    WHEN usr.id_user IS NULL THEN 0::bigint
                    ELSE usr.id_user
                END AS "idUtilisateurSonate",
            0 AS "idSalle",
            mo.id_module AS "idModule",
            mo.nom AS "NomModule",
            seance.nom AS "NomSeance",
            ((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "DateDebut",
            ((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "DateFin",
            ''::text AS "NomProfesseur",
            salle.salles AS "Salle",
            ''::text AS "CodeSalle",
            typseance.nom AS "Type",
            usr.nom,
            initcap(usr.prenom::text) AS prenom,
            usr.logindomaine AS pseudocentrale,
            usr.mailgemgrh AS mailcentrale,
            gpe_td.nom AS "GroupeTD"
           FROM "ERP_SeanceEvaluation" seance
             LEFT JOIN "ERP_SeanceEvaluationConcat" salle ON salle.id_seanceevaluation = seance.id_seanceevaluation
             LEFT JOIN "ERP_GroupeSeanceEvaluationReelle" s_grpe_eval_re ON seance.id_seanceevaluation = s_grpe_eval_re.id_seanceevaluation
             LEFT JOIN "ERP_InstanceModule" imo ON imo.id_instancemodule = s_grpe_eval_re.id_instancemodule
             JOIN "ERP_AnneeScolaire" ann_scol ON imo.id_debutanneescolaire = ann_scol.id_anneedebut AND ann_scol.encours = true
             LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
             LEFT JOIN "ERP_InscriptionInstanceModule" iim ON imo.id_instancemodule = iim.id_instancemodule
             LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
             LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
             LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
             LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
             LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
             LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
          WHERE s_grpe_eval_re.id_instancegroupetd = iim.id_instancegroupetd
        UNION
         SELECT DISTINCT 0 AS "idSeanceSonate",
            evtsalle.id_evenement AS "idEvenementSonate",
                CASE
                    WHEN usr_inv.id_user IS NOT NULL THEN usr_inv.id_user
                    ELSE 0::bigint
                END AS "idUtilisateurSonate",
            salle.id_salle AS "idSalle",
            0 AS "idModule",
            evt.nom AS "NomModule",
            evt.nom AS "NomSeance",
            ((evt.datedebutevenement || ' '::text) || evt.heuredebut::text) || ':00'::text AS "DateDebut",
            ((evt.datefinevenement || ' '::text) || evt.heurefin::text) || ':00'::text AS "DateFin",
            ''::text AS "NomProfesseur",
            salle.nom AS "Salle",
            salle.code AS "CodeSalle",
            evttype.nom AS "Type",
                CASE
                    WHEN usr_inv.id_user IS NOT NULL THEN usr_inv.nom
                    ELSE ''::character varying
                END AS nom,
                CASE
                    WHEN usr_inv.id_user IS NOT NULL THEN initcap(usr_inv.prenom::text)::character varying
                    ELSE ''::character varying
                END AS prenom,
            usr_inv.logindomaine::text AS pseudocentrale,
            usr_inv.mailgemgrh AS mailcentrale,
            ''::text AS "GroupeTD"
           FROM "ERP_Evenement" evt
             LEFT JOIN "ERP_SalleEvenement" evtsalle ON evtsalle.id_evenement = evt.id_evenement
             LEFT JOIN "ERP_TypeEvenement" evttype ON evttype.id_typeevenement = evt.id_typeevenement
             LEFT JOIN "ERP_Salle" salle ON evtsalle.id_salle = salle.id_salle
             LEFT JOIN "ERP_InviteEvenement" inv ON inv.id_evenement = evt.id_evenement
             LEFT JOIN "ERP_User" usr_inv ON inv.id_user = usr_inv.id_user
          WHERE (( SELECT "ERP_AnneeScolaire".datedebut
                   FROM "ERP_AnneeScolaire"
                  WHERE "ERP_AnneeScolaire".encours = true)) <= evt.datedebutevenement AND (( SELECT "ERP_AnneeScolaire".datefin
                   FROM "ERP_AnneeScolaire"
                  WHERE "ERP_AnneeScolaire".encours = true)) >= evt.datedebutevenement) tab
  GROUP BY tab."idSeanceSonate", tab."idEvenementSonate", tab."idUtilisateurSonate", tab."NomSeance", tab."idModule", tab."NomModule", tab.pseudocentrale, tab."DateDebut", tab."DateFin", tab."Type", tab.nom, tab.prenom, tab.mailcentrale
;
  ALTER TABLE "INTER_CALENDRIER_VUE"
    OWNER TO cri;

  GRANT SELECT ON "INTER_CALENDRIER_VUE" TO edt;

REFRESH MATERIALIZED VIEW "INTER_CALENDRIER_VUE";
