[Ils-source] r1495 - trunk/db/mssql

[scribe at crosswire.org]

Author: scribe
Date: 2016-10-27 14:13:14 -0700 (Thu, 27 Oct 2016)
New Revision: 1495

Modified:
   trunk/db/mssql/ilsdb.sql
Log:
added comments and new FROMCURRICULUM to usercourseassignmentswithgroups view


Modified: trunk/db/mssql/ilsdb.sql
===================================================================
--- trunk/db/mssql/ilsdb.sql	2016-10-26 20:31:45 UTC (rev 1494)
+++ trunk/db/mssql/ilsdb.sql	2016-10-27 21:13:14 UTC (rev 1495)
@@ -42,7 +42,25 @@
 
 CREATE VIEW USERCOURSEASSIGNMENTS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID FROM   dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
 
-CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID FROM   dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID LEFT OUTER JOIN dbo.ILSGROUP G ON G.ILSGROUPID=T2.ILSGROUPID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
+CREATE VIEW USERCOURSEASSIGNMENTSWITHGROUPS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID, G.ILSGROUPID, G.GROUPTYPEID, CASE WHEN (T9.COURSID IS NULL) THEN NULL ELSE T1.COURSID END as FROMCURRICULUM
+FROM   dbo.USERGROUP AS T2
+-- which courses and curriculum are assigned by user group
+INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID
+-- course details for courses curriculum assigned by user group
+INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID
+-- for each curriculum, pull in all courses. T5.COURSID is a course assigned via a curriculum
+LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T'
+-- course details for courses assigned via a curriculum
+LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID
+-- group details
+LEFT OUTER JOIN dbo.ILSGROUP G ON G.ILSGROUPID=T2.ILSGROUPID
+--	
+WHERE	(T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T')
+OR	(T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T')
+OR	(T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T')
+OR	(T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T')
+OR	(T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID)
+OR	(T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
 
 CREATE VIEW COURSEATTEMPTSTATUS WITH SCHEMABINDING AS SELECT CA.COMPANYID, CA.USERPRID, CA.COURSID, CA.COURSATTID, CA.CACOMPLETEDATE, CA.CAEXPIREDON,  COALESCE(CA.CASTATUSID, 0) AS CASTATUSID, CASE COALESCE(CA.CASTATUSID, 0) WHEN 3 THEN 1 WHEN 0 THEN 1 ELSE 0 END AS CRS_OPEN, CASE COALESCE(CA.CASTATUSID, 0) WHEN 1 THEN 1 ELSE 0 END               AS CRS_COMPLETED, CASE COALESCE(CA.CASTATUSID, 0) WHEN 2 THEN 1 ELSE 0 END               AS CRS_FAILED FROM DBO.COURSEATTEMPT CA;