|
-- creation de la vue matérialisée des salles
|
|
-- DROP MATERIALIZED VIEW public."tr_serenade_salle";
|
|
|
|
CREATE MATERIALIZED VIEW public."tr_serenade_salle" AS
|
|
SELECT id_salle AS id, nom, code FROM "ERP_Salle";
|
|
|
|
ALTER TABLE "tr_serenade_salle" OWNER TO cri;
|
|
GRANT SELECT ON "tr_serenade_salle" TO edt;
|
|
REFRESH MATERIALIZED VIEW "tr_serenade_salle";
|
|
|
|
|
|
-- creation de la vue matérialisée des utilisateurs
|
|
-- DROP MATERIALIZED VIEW public."tr_serenade_salle";
|
|
|
|
CREATE MATERIALIZED VIEW public."t_serenade_user" AS
|
|
SELECT
|
|
id_user AS id ,
|
|
CASE
|
|
WHEN prenomusuel IS NOT NULL AND length(prenomusuel::text) > 0 THEN initcap(prenomusuel::text)
|
|
ELSE initcap(prenom::text)
|
|
END AS prenom,
|
|
CASE
|
|
WHEN nomusuel IS NOT NULL AND length(nomusuel::text) > 0 THEN initcap(nomusuel::text)
|
|
ELSE initcap(nom::text)
|
|
END AS nom,
|
|
logindomaine AS pseudo,
|
|
mailgemgrh AS mail
|
|
FROM "ERP_User"
|
|
ORDER BY id_user ;
|
|
|
|
ALTER TABLE "tr_serenade_salle" OWNER TO cri;
|
|
GRANT SELECT ON "tr_serenade_salle" TO edt;
|
|
REFRESH MATERIALIZED VIEW "tr_serenade_salle";
|
|
|
|
|
|
-- creation de la vue matérialisée des type de séance (enseignement)
|
|
-- DROP MATERIALIZED VIEW public."tr_serenade_type_seance";
|
|
|
|
CREATE MATERIALIZED VIEW public."tr_serenade_type_seance" AS
|
|
SELECT id_typeseance AS id, nom, code FROM "ERP_TypeSeance" ORDER BY id_typeseance ;
|
|
|
|
ALTER TABLE "tr_serenade_type_seance" OWNER TO cri;
|
|
GRANT SELECT ON "tr_serenade_type_seance" TO edt;
|
|
REFRESH MATERIALIZED VIEW "tr_serenade_type_seance";
|
|
|
|
|
|
-- creation de la vue matérialisée des type d'évènements
|
|
-- DROP MATERIALIZED VIEW public."tr_serenade_type_evenement";
|
|
|
|
CREATE MATERIALIZED VIEW public."tr_serenade_type_evenement" AS
|
|
SELECT id_typeevenement AS id, nom, code FROM "ERP_TypeEvenement" ORDER BY id_typeevenement ;
|
|
|
|
ALTER TABLE "tr_serenade_type_evenement" OWNER TO cri;
|
|
GRANT SELECT ON "tr_serenade_type_evenement" TO edt;
|
|
REFRESH MATERIALIZED VIEW "tr_serenade_type_evenement";
|
|
|
|
|
|
-- creation de la vue matérialisée des type d'évènements
|
|
-- DROP MATERIALIZED VIEW public."tr_serenade_semestre";
|
|
|
|
CREATE MATERIALIZED VIEW public."tr_serenade_semestre" AS
|
|
SELECT id_semestre AS id, nom , '' AS code FROM "ERP_Semestre" ORDER BY id_semestre ;
|
|
|
|
ALTER TABLE "tr_serenade_semestre" OWNER TO cri;
|
|
GRANT SELECT ON "tr_serenade_semestre" TO edt;
|
|
REFRESH MATERIALIZED VIEW "tr_serenade_semestre";
|
|
|
|
|
|
|
|
-- creation de la vue matérialisée des type d'évènements
|
|
-- DROP MATERIALIZED VIEW public."t_serenade_module";
|
|
|
|
CREATE MATERIALIZED VIEW public."t_serenade_module" AS
|
|
SELECT id_module AS id, nom, code FROM "ERP_Module" ORDER BY id_module;
|
|
|
|
ALTER TABLE "t_serenade_module" OWNER TO cri;
|
|
GRANT SELECT ON "t_serenade_module" TO edt;
|
|
REFRESH MATERIALIZED VIEW "t_serenade_module";
|
|
|
|
-- creation de la vue matérialisée des séances
|
|
-- séances avec les enseignants comme utilisateurs
|
|
-- DROP MATERIALIZED VIEW public."t_serenade_seance";
|
|
|
|
CREATE MATERIALIZED VIEW public."t_serenade_seance" AS
|
|
SELECT
|
|
row_number() OVER () AS id,
|
|
tab.utilisateur_id,
|
|
tab.module_id,
|
|
tab.semestre_id,
|
|
tab.salle_id,
|
|
tab.debut,
|
|
tab.fin,
|
|
tab.type_id,
|
|
tab.number_type,
|
|
tab.groupe_td,
|
|
tab.enseignant
|
|
FROM (
|
|
SELECT DISTINCT
|
|
seance.id_seance AS "id",
|
|
CASE
|
|
WHEN usrprof.id_user IS NULL THEN 0::bigint
|
|
ELSE usrprof.id_user
|
|
END AS "utilisateur_id",
|
|
salle.id_salle::text AS "salle_id",
|
|
mo.id_module AS "module_id",
|
|
es.id_semestre AS "semestre_id",
|
|
((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
|
|
((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
|
|
CASE
|
|
WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN usrprof.nomusuel::text
|
|
ELSE usrprof.nom::text || ' '::text
|
|
END ||
|
|
CASE
|
|
WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
|
|
ELSE initcap(usrprof.prenom::text)
|
|
END AS "enseignant",
|
|
typseance.id_typeseance AS "type_id",
|
|
0 AS number_type,
|
|
STRING_AGG (gpe_td.nom, '|') AS "groupe_td"
|
|
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
|
|
LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
|
|
LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
|
|
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 seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
|
|
GROUP BY seance.id_seance, usrprof.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, usrprof.nomusuel, usrprof.nom, usrprof.prenomusuel, usrprof.prenom, typseance.id_typeseance
|
|
--;
|
|
UNION ALL
|
|
-- séances avec les étudiants comme utilisateurs
|
|
SELECT DISTINCT
|
|
seance.id_seance AS "id",
|
|
CASE
|
|
WHEN usr.id_user IS NULL THEN 0::bigint
|
|
ELSE usr.id_user
|
|
END AS "utilisateur_id",
|
|
salle.id_salle::text AS "salle_id",
|
|
mo.id_module AS "module_id",
|
|
es.id_semestre AS "semestre_id",
|
|
((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
|
|
((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
|
|
STRING_AGG (CASE
|
|
WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN initcap(usrprof.nomusuel::text)
|
|
ELSE initcap(usrprof.nom::text) || ' '::text
|
|
END ||
|
|
CASE
|
|
WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
|
|
ELSE initcap(usrprof.prenom::text)
|
|
END, '|') AS "enseignant",
|
|
typseance.id_typeseance AS "type_id",
|
|
0 AS number_type,
|
|
gpe_td.nom AS "groupe_td"
|
|
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
|
|
LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
|
|
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 AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
|
|
GROUP BY seance.id_seance, usr.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, typseance.id_typeseance, gpe_td.nom
|
|
--;
|
|
UNION ALL
|
|
-- séances avec les étudiants comme utilisateurs
|
|
-- table des modules bizarres
|
|
SELECT DISTINCT
|
|
seance.id_seance AS "id",
|
|
CASE
|
|
WHEN usr.id_user IS NULL THEN 0::bigint
|
|
ELSE usr.id_user
|
|
END AS "utilisateur_id",
|
|
salle.id_salle::text AS "salle_id",
|
|
mo.id_module AS "module_id",
|
|
es.id_semestre AS "semestre_id",
|
|
((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
|
|
((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
|
|
STRING_AGG (CASE
|
|
WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN initcap(usrprof.nomusuel::text)
|
|
ELSE initcap(usrprof.nom::text) || ' '::text
|
|
END ||
|
|
CASE
|
|
WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
|
|
ELSE initcap(usrprof.prenom::text)
|
|
END, '|') AS "enseignant",
|
|
typseance.id_typeseance AS "type_id",
|
|
0 AS number_type,
|
|
gpe_td.nom AS "groupe_td"
|
|
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
|
|
LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
|
|
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 AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
|
|
GROUP BY seance.id_seance, usr.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, typseance.id_typeseance, gpe_td.nom
|
|
--;
|
|
UNION ALL
|
|
-- seance de type evaluations
|
|
SELECT DISTINCT
|
|
seance.id_seanceevaluation AS "id",
|
|
CASE
|
|
WHEN usr.id_user IS NULL THEN 0::bigint
|
|
ELSE usr.id_user
|
|
END AS "utilisateur_id",
|
|
salle.id_salle::text AS "salle_id",
|
|
mo.id_module AS "module_id",
|
|
es.id_semestre AS "semestre_id",
|
|
((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
|
|
((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
|
|
''::text AS "enseignant",
|
|
typseance.id_typeseance AS "type_id",
|
|
0 AS number_type,
|
|
gpe_td.nom AS "groupe_td"
|
|
FROM "ERP_SeanceEvaluation" seance
|
|
LEFT JOIN "ERP_SalleSeanceEvaluation" evasalle ON evasalle.id_seanceevaluation = seance.id_seanceevaluation
|
|
LEFT JOIN "ERP_Salle" salle ON salle.id_salle = evasalle.id_salle
|
|
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
|
|
LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
|
|
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 AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
|
|
--;
|
|
UNION ALL
|
|
-- séance de type evenements
|
|
SELECT DISTINCT
|
|
evtsalle.id_evenement AS "id",
|
|
CASE
|
|
WHEN usr_inv.id_user IS NOT NULL THEN usr_inv.id_user
|
|
ELSE 0::bigint
|
|
END AS "utilisateur_id",
|
|
STRING_AGG (salle.id_salle::text, '|') AS "salle_id",
|
|
0 AS "module_id",
|
|
0 AS "semestre_id",
|
|
((evt.datedebutevenement || ' '::text) || evt.heuredebut::text) || ':00'::text AS "debut",
|
|
((evt.datefinevenement || ' '::text) || evt.heurefin::text) || ':00'::text AS "fin",
|
|
''::text AS "enseignant",
|
|
1 AS number_type,
|
|
evttype.id_typeevenement AS "type_id",
|
|
''::text AS "groupe_td"
|
|
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 evt.datedebutevenement <= (now() + '6 mons'::interval) AND evt.datedebutevenement >= (now() - '6 mons'::interval)
|
|
GROUP BY evtsalle.id_evenement, usr_inv.id_user, evt.datedebutevenement, evt.heuredebut, evt.datefinevenement, evt.heurefin, evttype.id_typeevenement
|
|
) AS tab;
|
|
|
|
ALTER TABLE "t_serenade_seance" OWNER TO cri;
|
|
GRANT SELECT ON "t_serenade_seance" TO edt;
|
|
REFRESH MATERIALIZED VIEW "t_serenade_seance";
|