Newer
Older
ALTER TABLE public.lessons ADD COLUMN subgroup_id integer;
DROP FUNCTION IF EXISTS public.create_lesson(pdisciplineid integer, pdate date, plessontype integer);
CREATE OR REPLACE FUNCTION public.create_lesson(pdisciplineid integer, pdate date, plessontype integer, psubgroupid integer default NULL)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
vRowUpd int;
begin
INSERT INTO lessons (discipline_id, lesson_date, lesson_type, subgroup_id)
VALUES (pdisciplineid, pdate, plessontype, psubgroupid) returning id into vID;
RETURN vID;
end
$function$;
DROP FUNCTION IF EXISTS public.get_lessons_for_discipline(pdisciplineid integer);
CREATE OR REPLACE FUNCTION public.get_lessons_for_discipline(pdisciplineid integer)
RETURNS TABLE("ID" integer, "LessonDate" date, "LessonType" integer, "SubgroupID" integer)
LANGUAGE sql
AS $function$
SELECT lessons.id AS "ID",
lessons.lesson_date AS "LessonDate",
lessons.lesson_type AS "LessonType",
lessons.subgroup_id AS "SubgroupID"
FROM lessons
WHERE lessons.discipline_id = pdisciplineid
AND lessons.lesson_deleted = 0;
$function$;
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
DROP FUNCTION IF EXISTS public.getstudentsforrating(pdisciplineid integer);
CREATE OR REPLACE FUNCTION public.getstudentsforrating(pdisciplineid integer)
RETURNS TABLE("ID" integer, "RecordBookID" integer, "LastName" character varying, "FirstName" character varying, "SecondName" character varying, "GradeID" integer, "GradeNum" integer, "Degree" bachelor_master_specialist, "GroupID" integer, "GroupNum" integer, "IsAttached" boolean, "SubgroupID" integer)
LANGUAGE plpgsql
AS $function$
DECLARE vSemesterID INT DEFAULT -1; vRes INT DEFAULT -1;
BEGIN
vSemesterID := GetDisciplineProperty(pDisciplineID, 'semester');
DROP TABLE IF EXISTS tDisciplineStudents;
vRes := Discipline_GetStudentsTemp(pDisciplineID);
return query
SELECT view_students.StudentID AS "ID",
view_students.RecordBookID as "RecordBookID",
view_students.LastName as "LastName",
view_students.FirstName as "FirstName",
view_students.SecondName as "SecondName",
view_students.GradeID as "GradeID",
view_students.GradeNum as "GradeNum",
view_students."degree" as "Degree",
view_students.GroupID as "GroupID",
view_students.GroupNum as "GroupNum",
( tDisciplineStudents."Type" IS NOT NULL ) AS "IsAttached",
students_subgroups.subgroup_id as "SubgroupID"
FROM tDisciplineStudents
INNER JOIN view_students ON view_students.StudentID = tDisciplineStudents.StudentID AND
view_students.SemesterID = vSemesterID
LEFT JOIN students_subgroups ON view_students.RecordBookID = students_subgroups.recordbook_id AND
students_subgroups.subgroup_id IN (SELECT subgroups.id FROM subgroups WHERE subgroups.discipline_id = pdisciplineid)
WHERE tDisciplineStudents."Type" is null or ( tDisciplineStudents."Type" !='detach')
ORDER BY view_students.GradeID ASC,
view_students.GroupNum ASC,
view_students.LastName ASC,
view_students.FirstName ASC;
END;
$function$;