|
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";
|