Newer
Older
CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryearnum(pyear integer, pnum integer)
RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
"SubjectExternalID" character varying, "RecordBookExternalID" character varying,
"SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
"ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
return query
SELECT *
FROM view_final_report
WHERE view_final_report.SemesterYear = pyear and
view_final_report.SemesterNum = pnum;
END
$function$;
DROP FUNCTION IF EXISTS public.finalreport_getbysemesteryearplan(pyear integer, pnum integer, pplanexternalid integer);
CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryearplan(pyear integer, pnum integer, pplanexternalid integer)
RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
"SubjectExternalID" character varying, "RecordBookExternalID" character varying, "ExamType" exam_credit_grading_credit,
"SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
"ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
return query
SELECT *
FROM view_final_report
WHERE view_final_report.SemesterYear = pyear and
view_final_report.SemesterNum = pnum and
view_final_report.PlanExternalID = pplanexternalid;
END
$function$;
DROP FUNCTION IF EXISTS public.finalreport_getbysemesteryeardiscipline(pyear integer, pnum integer, pdisciplineexternalid character varying);
CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryeardiscipline(pyear integer, pnum integer, pdisciplineexternalid character varying)
RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
"SubjectExternalID" character varying, "RecordBookExternalID" character varying, "ExamType" exam_credit_grading_credit,
"SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
"ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
return query
SELECT *
FROM view_final_report
WHERE view_final_report.SemesterYear = pyear and
view_final_report.SemesterNum = pnum and
view_final_report.SubjectExternalID = pdisciplineexternalid;
END
$function$;
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
drop view if exists public.view_final_report;
CREATE OR REPLACE VIEW public.view_final_report
AS SELECT DISTINCT semesters.year AS semesteryear,
semesters.num AS semesternum,
study_plans.externalid AS planexternalid,
subjects.externalid AS subjectexternalid,
record_books.externalid AS recordbookexternalid,
disciplines.examtype as examtype,
( SELECT sum(rating_table.rate) AS sum
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'regular'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
GROUP BY modules.disciplineid, rating_table.recordbookid) AS semesterrate,
( SELECT rating_table.rate
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
LIMIT 1) AS examrate,
( SELECT rating_table.rate
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
OFFSET 1
LIMIT 1) AS exam2rate,
( SELECT rating_table.rate
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
OFFSET 2
LIMIT 1) AS exam3rate,
( SELECT rating_table.rate
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'extra'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
LIMIT 1) AS extrarate,
( SELECT rating_table.rate
FROM rating_table
JOIN submodules ON rating_table.submoduleid = submodules.id
JOIN modules ON submodules.moduleid = modules.id
WHERE modules.disciplineid = disciplines.id AND modules.type = 'extra'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
OFFSET 1
LIMIT 1) AS extra2rate
FROM semesters
JOIN disciplines ON disciplines.semesterid = semesters.id
JOIN disciplines_study_plans ON disciplines_study_plans.disciplineid = disciplines.id
JOIN study_plans ON study_plans.id = disciplines_study_plans.studyplanid
JOIN subjects ON subjects.id = disciplines.subjectid
JOIN view_disciplines_recordbooks ON view_disciplines_recordbooks.disciplineid = disciplines.id AND 'attach'::attach_detach = COALESCE(view_disciplines_recordbooks.type, 'attach'::attach_detach)
JOIN record_books ON record_books.id = view_disciplines_recordbooks.recordbookid;
-- Permissions
ALTER TABLE public.view_final_report OWNER TO postgres;
GRANT ALL ON TABLE public.view_final_report TO postgres;