Newer
Older
DROP FUNCTION IF EXISTS public.getdisciplinesforgroup(pgroupid integer, psemesterid integer, pshowhidden boolean);
DROP FUNCTION IF EXISTS public.getdisciplinesforgroup(pgroupid integer, psemesterid integer, pshowglobal boolean, pshowhidden boolean);
CREATE OR REPLACE FUNCTION public.getdisciplinesforgroup(pgroupid integer, psemesterid integer, pshowglobal boolean DEFAULT false, pshowhidden boolean DEFAULT false)
RETURNS TABLE("ID" integer, "SubjectID" integer, "SubjectName" character varying, "Subtype" scientific_disciplinary_coursework, "Type" exam_credit_grading_credit, "CompoundDiscID" integer, "CompoundDiscName" character varying, "CurRate" integer, "MaxRate" integer, "IsGlobal" boolean)
LANGUAGE plpgsql
AS $function$
BEGIN
return query
SELECT DISTINCT view_disciplines.DisciplineID AS "ID",
view_disciplines.subjectid as "SubjectID",
view_disciplines.SubjectName as "SubjectName",
view_disciplines.Subtype as "Subtype",
view_disciplines.ExamType AS "Type",
view_disciplines.CompoundDiscID as "CompoundDiscID",
view_disciplines.CompoundDiscName as "CompoundDiscName",
view_disciplines.CurRate as "CurRate",
view_disciplines.MaxRate as "MaxRate",
view_disciplines.isGlobal as "IsGlobal"
FROM students_groups
LEFT JOIN view_disciplines_recordbooks ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID
INNER JOIN view_disciplines ON view_disciplines_recordbooks.DisciplineID = view_disciplines.DisciplineID
WHERE students_groups.GroupID = pGroupID AND
students_groups.SemesterID = pSemesterID AND
view_disciplines.SemesterID = pSemesterID AND
(NOT (view_disciplines.IsInactive) OR pshowhidden) AND
(NOT (view_disciplines.IsGlobal) OR pshowglobal) AND
students_groups.State <= 'outlet' AND
-- view_disciplines_recordbooks.Type may be attach or NULL
COALESCE(view_disciplines_recordbooks.Type, 'attach') = 'attach'
ORDER BY view_disciplines.ExamType DESC,
view_disciplines.SubjectName ASC;
end;
$function$;