-
Course: ~[gpv:coursetitle]
- Late
- Missing
- Exempt
- Collected
- Excluded
~[tlist_sql;
SELECT
pga.name assignment,
(case when NVL(psmas.TURNEDIN,0) = 1 then 'inline' ELSE 'none' END) turnedin,
(case when NVL(psmas.TURNEDINLATE,0) = 1 then 'inline' ELSE 'none' END) late,
(case when NVL(psmas.ISMISSING,0) = 1 then 'inline' ELSE 'none' END) missing,
(case when NVL(psmas.EXEMPT,0) = 1 then 'inline' ELSE 'none' END) exempt,
(case when PGA.INCLUDEINFINALGRADES = 0 then 'inline' ELSE 'none' END) notclac,
CASE WHEN pga.PUBLISHSCORES != 0 AND ~[if.~[dbval;table=termbins;field=suppressltrgrd;*storecode=~[gpv:fg];*termid=~(termid);*schoolid=~(schoolid)]#true]1[else]2[/if] = 1 THEN ssa.grade ELSE '' END ltrgrade,
CASE WHEN pga.PUBLISHSCORES != 0 AND ~[dbval;table=termbins;field=SuppressPercentScr;*storecode=~[gpv:fg];*termid=~(termid);*schoolid=~(schoolid)] = 0 THEN chr(40)||to_char(ssa.percent)||chr(37)||chr(41) ELSE '' END percent,
CASE WHEN pga.PUBLISHSCORES != 0 THEN to_char(ssa.SCORE) ||'/'|| to_char(pga.POINTSPOSSIBLE) ELSE 'Score Not Published' END earnposs,
pgc.name category,
pga.datedue
FROM CC
INNER JOIN PGAssignments PGA on cc.sectionid = pga.sectionid and pga.datedue >= (SELECT min(date1) from termbins where schoolid=~(curschoolid) and yearid = ~(curyearid) and upper(storecode) = upper('~(current.final.grade.name)')) and pga.datedue <= (SELECT max(date2) from termbins where schoolid=~(curschoolid) and yearid = ~(curyearid) and upper(storecode) = upper('~(current.final.grade.name)'))
INNER JOIN SYNC_SECTIONENROLLMENTMAP sync_cc on cc.dcid = sync_cc.ccdcid and sync_cc.sectionenrollmentid = ~[gpv:psmseid] and CC.studentid = ~(curstudid) AND CC.dateenrolled <= trunc(sysdate) and cc.dateleft >= trunc(sysdate)
INNER JOIN SECTIONS sec on CC.sectionid = sec.id
INNER JOIN COURSES c on sec.course_number = c.course_number
INNER JOIN PGCategories PGC on pga.pgcategoriesid = pgc.id
LEFT OUTER JOIN SYNC_PGASSIGNMENTSMAP sync_pga on pga.dcid = sync_pga.pgassignmentsdcid
LEFT OUTER JOIN PSM_ASSIGNMENTSCORE PSMAS ON sync_pga.sectionassignmentid = psmas.sectionassignmentid and sync_cc.sectionenrollmentid = psmas.sectionenrollmentid
LEFT OUTER JOIN SECTIONSCORESID SSI ON sec.id = ssi.sectionid and cc.studentid = ssi.studentid
LEFT OUTER JOIN SECTIONSCORESASSIGNMENTS SSA ON pga.id = ssa.assignment and ssi.dcid = ssa.fdcid
WHERE
CASE WHEN PSMAS.EXEMPT != 1 THEN 1
WHEN PSMAS.EXEMPT = 1 and (PSMAS.SCORE is null or PSMAS.ACTUALSCOREENTERED is null) THEN 2 ELSE 1 END = 1
AND
CASE
WHEN PGA.PUBLISHSTATE = 1 THEN PGA.PUBLISHONSPECIFICDATE
WHEN PGA.PUBLISHSTATE = 2 THEN (PGA.DATEDUE - PGA.PUBLISHDAYSBEFOREDUE)
WHEN PGA.PUBLISHSTATE = 3 THEN PGA.DATEDUE
ELSE PGA.DATEDUE END <= SYSDATE
order by datedue desc, pga.name]
-
~(assignment;t)
~(ltrgrade;t) ~(percent;t) Points: ~(earnposs;t)
Cat: ~(category;t) Due: ~(duedate;d)
[/tlist_sql]