issue595_discipline_info.sql

ALTER TABLE disciplines
    ADD COLUMN formid integer DEFAULT NULL;

--ALTER TABLE ONLY disciplines
--    ADD CONSTRAINT disciplines_ibfk_7 FOREIGN KEY (formid) REFERENCES study_form(id);

drop view if exists public.view_disciplines_stat;

DROP FUNCTION IF EXISTS public.disciplinecollection_get(psemesterid integer, pfacultyid integer, pgradenum integer, pdegree bachelor_master_specialist);
CREATE OR REPLACE FUNCTION public.disciplinecollection_get(psemesterid integer, pfacultyid integer, pgradenum integer DEFAULT NULL::integer, pdegree bachelor_master_specialist DEFAULT NULL::bachelor_master_specialist)
    RETURNS TABLE("ID" integer, "SubjectName" character varying, "AuthorID" integer, "ExamType" exam_credit_grading_credit, "StudyForm" integer, "State" text, "IsInactive" boolean, "IsGlobalStub" boolean, "PlanExternalID" integer, "GlobalName" character varying)
    LANGUAGE plpgsql
AS $function$
    -- select public.discipline_getwithfullness(
--	:psemesterid,	-- put the psemesterid parameter value instead of 'psemesterid' (int4)
--	:pfacultyid 	-- put the pfacultyid parameter value instead of 'pfacultyid' (int4)
-- );
BEGIN
    return query
        SELECT view_disciplines.DisciplineID AS ID,
               view_disciplines.SubjectName As SubjectName,
               view_disciplines.AuthorID As AuthorID,
               view_disciplines.ExamType As ExamType,
               disciplines.FormID AS StudyForm,
               CASE
                   WHEN (view_disciplines.MaxRate < 100 AND view_disciplines.IsLocked = 0) THEN 'no_map'
                   WHEN (view_disciplines.MaxRate = 100 AND view_disciplines.IsLocked = 0) THEN 'no_rates'
                   WHEN (view_disciplines.MaxRate = 100 AND view_disciplines.IsLocked = 1) THEN 'in_progress'
                   ELSE 'other'
                   END AS State,
               disciplines.IsInactive as IsInactive,
               disciplines.IsGlobalStub as IsGlobalStub,
               view_disciplines.planexternalid,
               view_disciplines.globalname as GlobalName
        FROM view_disciplines
                 JOIN disciplines ON view_disciplines.disciplineid = disciplines.id
        WHERE view_disciplines.SemesterID = pSemesterID AND
                view_disciplines.FacultyID = pFacultyID AND
            (view_disciplines.GradeNum = pgradenum or ((public.view_disciplines.GradeNum = pgradenum)is unknown)) AND
            (view_disciplines.Degree = pdegree or ((public.view_disciplines.Degree = pdegree)is unknown));
END
$function$;

CREATE OR REPLACE VIEW public.view_disciplines_stat
AS SELECT ff.name,
          ( SELECT count(*) AS count
            FROM disciplinecollection_get(11, ff.id) disciplinecollection_get("ID", "SubjectName", "AuthorID", "ExamType", "StudyForm", "State", "IsInactive", "PlanExternalID", "GlobalName", "GlobalName_1")) AS "Всего дисциплин",
          ( SELECT count(*) AS count
            FROM disciplinecollection_get(11, ff.id) t("ID", "SubjectName", "AuthorID", "ExamType", "StudyForm", "State", "IsInactive", "PlanExternalID", "GlobalName", "GlobalName_1")
            WHERE t."State" = 'no_map'::text AND t."IsInactive" = false) AS "Без УКД"
   FROM faculties ff;