issue547_global_bill.sql

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, "GlobalName" character varying)
    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",
                        global_disciplines.name as "GlobalName"
        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
                 LEFT JOIN global_disciplines ON view_disciplines.DisciplineID = global_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$;