Something went wrong on our end
Select Git revision
temp_tables.sql
Forked from
it-lab / grade
Source project has a limited visibility.
-
Ekaterina Khaustova authoredEkaterina Khaustova authored
temp_tables.sql 15.10 KiB
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$