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

DROP FUNCTION IF EXISTS public.getdisciplinesforexport(pgroupid integer, psemesterid integer);
CREATE OR REPLACE FUNCTION public.getdisciplinesforexport(pgroupid integer, psemesterid integer)
    RETURNS TABLE("ID" integer, "SubjectName" character varying, "DisciplineExternalID" character varying, "PlanExternalID" integer)
    LANGUAGE plpgsql
AS $function$
BEGIN
    return query
        select D."ID" as "ID",
               D."SubjectName" as "SubjectName",
               subjects.externalid as "DisciplineExternalID",
               study_plans.externalid as "PlanExternalID"
        from public.getdisciplinesforgroup(pgroupid, psemesterid, True, True) as D
                 join disciplines on disciplines.id = D."ID"
                 join subjects on disciplines.subjectid = subjects.id
                 join disciplines_study_plans on disciplines_study_plans.disciplineid = D."ID"
                 join students_groups on public.students_groups.groupid = pgroupid
                 join record_books_plans on record_books_plans.recordbookid = public.students_groups.recordbookid
                 join study_plans on disciplines_study_plans.studyplanid = study_plans.id and record_books_plans.studyplanid = study_plans.id;

end;
$function$;