Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
CREATE OR REPLACE FUNCTION public.getgroupsfordisciplineall(pdisciplineid integer)
RETURNS TABLE("ID" integer, "GroupNum" integer, "GradeID" integer, "GradeNum" integer, "Degree" bachelor_master_specialist, "SpecID" integer, "SpecName" character varying, "SpecAbbr" character varying)
LANGUAGE plpgsql
AS $function$
DECLARE vSemesterID INT DEFAULT -1; vYear INT DEFAULT -1;
BEGIN
vSemesterID := GetDisciplineProperty(pDisciplineID, 'semester');
SELECT "year" into vYear from semesters where semesters.ID = vSemesterID;
-- general + attached
/* drop table if exists tGroup; -- этого не было
CREATE TEMPORARY TABLE IF NOT EXISTS tGroup AS (
SELECT students_groups.GroupID
FROM view_disciplines_recordbooks
INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet'
WHERE view_disciplines_recordbooks.DisciplineID = pDisciplineID AND
'attach' = COALESCE(view_disciplines_recordbooks.Type, 'attach')
GROUP BY students_groups.GroupID
);
*/
return query
SELECT view_groups.GroupID AS "ID",
view_groups.GroupNum as "GroupNum",
view_groups.GradeID as "GradeID",
view_groups.GradeNum as "GradeNum",
view_groups."degree" as "Degree",
view_groups.SpecID as "SpecID",
view_groups.SpecName as "SpecName",
view_groups.SpecAbbr as "SpecAbbr"
FROM (SELECT students_groups.GroupID
FROM view_disciplines_recordbooks
INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet'
WHERE view_disciplines_recordbooks.DisciplineID = pDisciplineID AND
'attach' = COALESCE(view_disciplines_recordbooks.Type, 'attach')
GROUP BY students_groups.GroupID) tGroup
INNER JOIN view_groups ON tGroup.GroupID = view_groups.GroupID AND view_groups.Year = vYear
ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END;
$function$;
CREATE OR REPLACE FUNCTION public.getratesforgroup(pdisciplineid integer, pgroupid integer)
RETURNS TABLE("ID" integer, "LastName" character varying, "FirstName" character varying, "SecondName" character varying, intermediate integer, bonus integer, exam integer)
LANGUAGE plpgsql
AS $function$
-- DECLARE vInGeneralGroup BOOLEAN DEFAULT FALSE;
declare vSemesterID INT DEFAULT -1;
BEGIN
vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
/*
vInGeneralGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT students_groups.RecordBookID
FROM students_groups
LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.RecordBookID = students_groups.RecordBookID
WHERE students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet' AND -- actual students
students_groups.GroupID = pGroupID AND
--CASE WHEN vInGeneralGroup THEN
-- NOT disciplines_students.Type <=> 'detach' -- not detached
--ELSE
-- COALESCE(disciplines_students.Type, 'attach') = 'attach'-- is attached
--END
(disciplines_students."type" is null or disciplines_students."type" ='attach')
);
DROP TABLE IF EXISTS vRoadMap;
CREATE TEMPORARY TABLE vRoadMap AS (
SELECT view_roadmap.SubmoduleID,
view_roadmap.ModuleType AS Type
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID
);
*/
return query
SELECT students.ID as "ID",
accounts.LastName as "LastName",
accounts.FirstName as "FirstName",
accounts.SecondName as "SecondName",
vRates.RateRegular AS intermediate,
vRates.RateBonus AS bonus,
vRates.RateExam AS exam
FROM (
--SELECT tStudents.RecordBookID,
select students_groups.recordbookid,
cast(SUM(CASE WHEN vRoadMap.Type = 'regular' THEN rt.Rate ELSE 0 end) as integer) AS RateRegular,
cast(SUM(CASE WHEN vRoadMap.Type = 'extra' THEN rt.Rate ELSE 0 END) as integer) AS RateExtra,
cast(SUM(CASE WHEN vRoadMap.Type = 'bonus' THEN rt.Rate ELSE 0 END) as integer) AS RateBonus,
MAX(CASE WHEN vRoadMap.Type = 'exam' THEN rt.Rate ELSE 0 END) AS RateExam
FROM students_groups /*(SELECT students_groups.RecordBookID
FROM students_groups LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.RecordBookID = students_groups.RecordBookID
WHERE students_groups.SemesterID = vSemesterID AND
--students_groups.State <= 'outlet' AND -- actual students
students_groups.GroupID = pGroupID AND
(disciplines_students."type" is null or disciplines_students."type" ='attach')
) tStudents*/
CROSS JOIN (SELECT view_roadmap.SubmoduleID, view_roadmap.ModuleType AS Type
FROM view_roadmap
WHERE view_roadmap.DisciplineID = pDisciplineID
) vRoadMap
LEFT JOIN rating_table as rt ON rt.RecordBookID=students_groups.recordbookid and-- rt.RecordBookID = tStudents.RecordBookID AND
rt.SubmoduleID = vRoadMap.SubmoduleID
where students_groups.SemesterID = vSemesterID and students_groups.GroupID = pGroupID and students_groups.State <= 'outlet'
--GROUP BY tStudents.RecordBookID
group by students_groups.recordbookid
) vRates
inner join record_books on record_books.id=vRates.recordbookid
INNER JOIN students ON students.ID = record_books.studentid
LEFT JOIN accounts ON students.AccountID = accounts.ID
ORDER BY CONCAT(accounts.LastName, accounts.FirstName, coalesce (accounts.SecondName,'')) ASC,
students.ID ASC;
END
$function$;
CREATE OR REPLACE FUNCTION public.getratesforgroupall(pgroupid integer, psemesterid integer)
RETURNS TABLE("StudentID" integer, "DisciplineID" integer, "CompoundDiscID" integer, intermediate integer, bonus integer, extra integer, exam integer, examcnt integer)
LANGUAGE plpgsql
AS $function$
BEGIN
/* DROP TABLE IF EXISTS tDisc;
CREATE TEMPORARY TABLE tDisc AS (
SELECT view_disciplines_recordbooks.DisciplineID
FROM view_disciplines_recordbooks
INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
students_groups.SemesterID = view_disciplines_recordbooks.semesterid --AND
--students_groups.State <= 'outlet'
WHERE students_groups.GroupID = pGroupID and students_groups.semesterid = pSemesterID --and
--'attach' = COALESCE(view_disciplines_recordbooks."type" , 'attach')
GROUP BY view_disciplines_recordbooks.DisciplineID
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT students_groups.RecordBookID,
record_books.StudentID
FROM students_groups
INNER JOIN record_books ON record_books.ID = students_groups.RecordBookID
WHERE students_groups.GroupID = pGroupID
and students_groups.SemesterID = pSemesterID
);*/
DROP TABLE IF EXISTS vRoadMap;
CREATE TEMPORARY TABLE vRoadMap AS (
SELECT --tDisc.DisciplineID as DisciplineID,
view_roadmap.disciplineid,
view_roadmap.ModuleType,-- as ModuleType,
view_roadmap.SubmoduleID
--FROM tDisc
--LEFT JOIN view_roadmap ON view_roadmap.DisciplineID = tDisc.DisciplineID
from view_roadmap where view_roadmap.disciplineid in (SELECT view_disciplines_recordbooks.DisciplineID
FROM view_disciplines_recordbooks
INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
students_groups.SemesterID = view_disciplines_recordbooks.semesterid and
students_groups.state <= 'outlet'
WHERE students_groups.GroupID = pGroupID and students_groups.semesterid = pSemesterID
GROUP BY view_disciplines_recordbooks.DisciplineID)
);
return query
SELECT vRates.StudentID as "StudentID",
vRates.DisciplineID as "DisciplineID",
disciplines.CompoundDiscID as "CompoundDiscID",
vRates.RateRegular AS intermediate,
vRates.RateBonus AS bonus,
vRates.RateExtra AS extra,
vRates.RateExam AS exam,
vRates.CntExam AS examCnt
FROM (
SELECT tStudents.StudentID,
vRoadMap.DisciplineID,
cast(SUM(CASE WHEN vRoadMap.ModuleType = 'regular' THEN rt.Rate ELSE 0 END) as integer)AS RateRegular,
cast(SUM(CASE WHEN vRoadMap.ModuleType = 'extra' THEN rt.Rate ELSE 0 END)as integer) AS RateExtra,
cast(SUM(CASE WHEN vRoadMap.ModuleType = 'bonus' THEN rt.Rate ELSE 0 END)as integer) AS RateBonus,
MAX(CASE WHEN vRoadMap.ModuleType = 'exam' THEN rt.Rate ELSE 0 END) AS RateExam,
cast( SUM(CASE WHEN vRoadMap.ModuleType = 'exam' THEN 1 ELSE 0 END)as integer) AS CntExam
FROM (select vs.recordbookid, vs.studentid from view_students as vs where vs.groupid=pgroupid and vs.semesterid=psemesterid) tStudents
CROSS join vRoadMap
LEFT JOIN rating_table as rt ON rt.RecordBookID = tStudents.RecordBookID AND
rt.SubmoduleID = vRoadMap.SubmoduleID
WHERE rt.Rate IS NOT NULL
GROUP BY tStudents.StudentID, vRoadMap.DisciplineID
) vRates
INNER JOIN students ON students.ID = vRates.StudentID
INNER JOIN accounts ON students.AccountID = accounts.ID
INNER JOIN disciplines ON disciplines.id = vRates.DisciplineID
ORDER BY CONCAT(accounts.LastName, accounts.FirstName, coalesce(accounts.SecondName,'')) ASC,
vRates.DisciplineID ASC;
END
$function$;
CREATE OR REPLACE FUNCTION public.getratesforgroupbystage(pdisciplineid integer, pgroupid integer, pmilestone integer)
RETURNS TABLE("StudentID" integer, "Semester" integer, "Bonus" integer, "Extra" integer, "PreviousExtra" integer, "Exam" integer, "PreviousExam" integer, "Option" absence_pass, "AutoPassed" integer, "LastName" character varying, "FirstName" character varying, "SecondName" character varying)
LANGUAGE plpgsql
AS $function$
DECLARE vSemesterID INT DEFAULT -1; vGroupID INT DEFAULT -1;
--vInGeneralGroup BOOL DEFAULT FALSE;
BEGIN
vSemesterID := GetDisciplineProperty(pDisciplineID, 'semester');
-- check that group attached to discipline. Otherwise vGroupID = -1;
/* vInGeneralGroup = EXISTS(
SELECT * FROM disciplines_groups
WHERE disciplines_groups.DisciplineID = pDisciplineID AND
disciplines_groups.GroupID = pGroupID
LIMIT 1
);
DROP TABLE IF EXISTS tStudents;
CREATE TEMPORARY TABLE tStudents AS (
SELECT record_books.ID, record_books.StudentID
FROM students_groups
LEFT JOIN record_books ON record_books.ID = students_groups.RecordBookID
LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
disciplines_students.RecordBookID = record_books.ID
WHERE students_groups.SemesterID = vSemesterID AND
students_groups.State <= 'outlet' AND --actual students
students_groups.GroupID = pGroupID and
COALESCE(disciplines_students.Type, 'attach') = 'attach'
);*/
return query
SELECT tRes.*,
accounts.LastName as "LastName",
accounts.FirstName as "FirstName",
accounts.SecondName as "SecondName"
FROM (
SELECT tStudents.StudentID as "StudentID",
cast (SUM(tRate.Rate*iif_sql(tMap.ModuleType = 'regular',1,0)) as integer) AS "Semester",
cast (SUM(tRate.Rate*iif_sql(tMap.ModuleType = 'bonus',1,0)) as integer) AS "Bonus",
cast (SUM(tRate.Rate*iif_sql(tMap.ModuleType = 'extra',1,0)*iif_sql(tMap.SubmoduleOrderNum < pMilestone,1,0)) as integer) AS "Extra",
cast (SUM(tRate.Rate*iif_sql(tMap.ModuleType = 'extra',1,0)*iif_sql(tMap.SubmoduleOrderNum < pMilestone - 1,1,0)) as integer) AS "PreviousExtra",
cast (SUM(tRate.Rate*iif_sql(tMap.ModuleType = 'exam',1,0)*iif_sql(tMap.SubmoduleOrderNum = pMilestone,1,0)) as integer) AS "Exam",
MAX(tRate.Rate*iif_sql(tMap.ModuleType = 'exam',1,0)*iif_sql(tMap.SubmoduleOrderNum < pMilestone,1,0)) AS "PreviousExam",
MAX(CASE WHEN tMap.SubmoduleOrderNum = pMilestone THEN exam_period_options."type" ELSE NULL END) As "Option",
MAX(CASE WHEN exam_period_options."type" = 'pass' THEN 1 ELSE 0 END) As "AutoPassed"
FROM (select record_books.id, record_books.studentid
from students_groups left join record_books on record_books.id=students_groups.recordbookid
where students_groups.groupid=pgroupid and students_groups.semesterid=vsemesterid and students_groups.state <= 'outlet') tStudents
LEFT JOIN view_roadmap AS tMap ON tMap.DisciplineID = pDisciplineID
LEFT JOIN rating_table AS tRate ON tRate.RecordbookID = tStudents.ID AND
tRate.SubmoduleID = tMap.SubmoduleID
LEFT JOIN exam_period_options ON exam_period_options.submoduleID = tMap.SubmoduleID AND
exam_period_options.RecordbookID = tStudents.ID
GROUP BY tStudents.StudentID
) tRes
INNER JOIN students ON students.ID = tRes."StudentID"
INNER JOIN accounts ON students.AccountID = accounts.ID
ORDER BY accounts.LastName ASC,
accounts.FirstName ASC,
accounts.SecondName ASC;
END
$function$