"git@gitlab.mmcs.sfedu.ru:Mr.Tiger/grade.git" did not exist on "c0959e3196ee635c654feaa6f5ae5fac018fa20c"
Newer
Older
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;