-------------------------------------------------------------------------------
-- This is the update template file
--
-- For every hotfix/service pack there is a unique update script that is 
-- executed by the installer. 
--
-- The first statement is a logging statement to indicate what db version
-- the database currently has.
--
-- The script ends with an update statement to increment the database version
-- and another log statement to indicate completion of the update script.
-- Wrap all blocks with begin/end keywords, even if there is only one statement.
--
-- If some sql statements only need to be executed against a hosted or licensed -- database, use the dbo.isHosted() function call. This function returns 1 if 
-- the database is a hosted database and 0 if licensed, i.e. 
--	if dbo.isHosted() = 1
--		begin
--			print 'hosted';
--		end
--
--
-- Please use isBigint() function to determine if database uses bigint for IDs:
--	if dbo.isBigint() = 1
--		begin
--			print 'IDs are of BIGINT type';
--		end
-- else 
--		begin
--			print 'IDs are of INTERGER type';
--		end
-------------------------------------------------------------------------------

IF((SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = DB_NAME()) < 110)
BEGIN
	PRINT 'Please alter your database compatibility level'
	PRINT 'Minimal compatibility level is 110 - SQL Server 2012'
	SET NOEXEC ON
END
GO
SET NOCOUNT ON
GO
EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_11_2_1_0.sql#1 $', '$Change: 1420150 $', 'Start';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------
-- Start of updates
--------------------------------------

--------------------------
--BEGIN Jira: CONN-4126402
--------------------------
IF NOT EXISTS (SELECT 1 from sys.columns c where OBJECT_NAME(c.object_id) = 'PPS_TRIAL_ACCOUNT_HISTORY' AND c.name = 'ADOBE_ID')
BEGIN
	ALTER TABLE pps_trial_account_history ADD ADOBE_ID NVARCHAR (64) NULL
END
GO
--------------------------
--END Jira: CONN-4126402
--------------------------

--------------------------
--BEGIN Jira: CONN-4126425
--------------------------
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='PPS_ASSET_HOSTS_HOST')
	DROP INDEX PPS_ASSET_HOSTS_HOST  ON PPS_ASSET_HOSTS
GO

CREATE NONCLUSTERED INDEX PPS_ASSET_HOSTS_HOST ON PPS_ASSET_HOSTS
(
 [HOST_ID] ASC
) INCLUDE ([STATUS]) WITH (PAD_INDEX=ON, SORT_IN_TEMPDB=ON, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, FILLFACTOR=80)
--------------------------
--END Jira: CONN-4126425
--------------------------
GO

--------------------------
--BEGIN Jira: CONN-4126197 - RTL Step taking all TemDB memory
--------------------------
IF EXISTS(SELECT TOP 1 1 FROM sys.views WHERE name = 'REPORTING_VIEW_CURRICULUM_CLASSROOM_STATUS')
	DROP VIEW REPORTING_VIEW_CURRICULUM_CLASSROOM_STATUS
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- view_curriculum_classroom_status
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE VIEW REPORTING_VIEW_CURRICULUM_CLASSROOM_STATUS
    AS 
	SELECT curriculum_id,
		   dim_sco_details_sco_id,
		   MAX(dim_sco_details_sco_version) AS dim_sco_details_sco_version,
		   version_accessed,
		   certificate_id,
		   dim_users_user_id,
		   current_status,
		   last_activity_date,
		   score,
		   average_score,
		   attempt_number,
		   enrolled_status,
		   date_enrolled,
		   date_unenrolled,
		   account_id
	FROM
	  (SELECT scos.curriculum_id,
			  scos.SCO_ID AS dim_sco_details_sco_id,
			  scos.dim_sco_details_sco_version,
			  transcripts.version_accessed,
			  transcripts.transcript_id AS certificate_id,
			  scos.PRINCIPAL_ID AS dim_users_user_id,
			  CASE transcripts.status
				  WHEN 'P' THEN '{passed}'
				  WHEN 'F' THEN '{failed}'
				  WHEN 'C' THEN '{complete}'
				  WHEN 'I' THEN '{in-progress}'
				  ELSE '{not-taken}'
			  END AS current_status,
			  transcripts.date_last_taken AS last_activity_date,
			  transcripts.SCORE,
			  transcripts.average_score,
			  transcripts.attempt_number,
			  scos.enrolled_status,
			  scos.date_enrolled,
			  scos.date_unenrolled,
			  scos.account_id
	   FROM
		 (SELECT scos1.curriculum_id,
				 scos1.SCO_ID,
				 scos1.dim_sco_details_sco_version,
				 scos1.PRINCIPAL_ID,
				 scos1.account_id,
				 scos1.enrolled_status,
				 scos1.date_unenrolled,
				 CASE
					 WHEN scos1.principal_id = (SELECT TOP 1 owner_principal_id FROM pps_acls WHERE acl_id = scos1.sco_id ORDER BY owner_principal_id DESC) 
						THEN (SELECT TOP 1 date_created FROM pps_scos WHERE sco_id = scos1.sco_id)
					 ELSE activities.date_enrolled
				 END AS date_enrolled
		  FROM
			(SELECT DISTINCT s.curriculum_id,
							 s.SCO_ID,
							 CASE
								 WHEN sa.version IS NULL THEN 1
								 ELSE sa.version
							 END AS dim_sco_details_sco_version,
							 s.PRINCIPAL_ID,
							 s.account_id,
							 '{current}' AS enrolled_status,
							 NULL AS date_unenrolled
			 FROM
			   (SELECT curriculum.SCO_ID AS curriculum_id,
					   classrooms.SCO_ID,
					   g.PRINCIPAL_ID,
					   classrooms.account_id
				FROM PPS_SCOS AS links
				INNER JOIN PPS_SCOS AS classrooms ON links.SOURCE_SCO_ID = classrooms.SCO_ID
				INNER JOIN pps_acl_rollup ar ON ar.acl_id = links.sco_id AND ar.depth > 0
				INNER JOIN PPS_SCOS AS curriculum ON ar.parent_acl_id = curriculum.sco_id AND curriculum.icon = 14 AND ar.depth > 0
				INNER JOIN PPS_ACL_ENTRIES AS a WITH (INDEX (pk2_pps_acl_entries)) ON a.acl_id = curriculum.sco_id
				INNER JOIN PPS_GROUP_MEMBER_ROLLUP AS g ON a.PRINCIPAL_ID = g.GROUP_ID
				INNER JOIN PPS_PRINCIPALS AS p ON g.PRINCIPAL_ID = p.PRINCIPAL_ID
				INNER JOIN PPS_EXT_ENUM_UTYPE AS e ON p.TYPE = e.TYPE
				WHERE (p.HAS_CHILDREN = 0)
				  AND (p.[disabled] IS NULL)
				  AND (p.PRINCIPAL_ID > 0)
				  AND (a.PERMISSION_ID = 2)
				  AND (curriculum.TYPE = 14)
				  AND (classrooms.ICON = 19)
				  AND (links.TYPE = 15)
			) AS s
			 LEFT OUTER JOIN PPS_SCO_ASSETS AS sa ON s.SCO_ID = sa.SCO_ID AND sa.ASSET_TYPE = 0

			 UNION ALL 
			 
			 SELECT curriculum.SCO_ID AS curriculum_id,
					a1.SCO_ID,
					CASE
						WHEN (a1.asset_id = 0) THEN 1
						WHEN (a1.asset_id IS NULL) THEN 1
						ELSE
								(SELECT MAX(sa.version)
								FROM pps_sco_assets sa
								WHERE sa.asset_id = a1.asset_id
								AND sa.sco_id = a1.sco_id
								AND asset_type = 0)
					END AS dim_sco_details_sco_version,
					a2.PRINCIPAL_ID,
					s.account_id,
					'{historical}' AS enrolled_status,
					a1.DATE_CREATED AS date_unenrolled
				FROM PPS_PERMISSION_ACTIVITIES AS a1
				INNER JOIN
					(SELECT DISTINCT MAX(ACTIVITY_ID) AS activity_id, PRINCIPAL_ID,	SCO_ID
						FROM PPS_PERMISSION_ACTIVITIES
						GROUP BY PRINCIPAL_ID, SCO_ID
					) AS a2 ON a1.ACTIVITY_ID = a2.activity_id
				 INNER JOIN PPS_SCOS AS s ON a1.SCO_ID = s.SCO_ID AND a2.SCO_ID = s.SCO_ID
				 INNER JOIN PPS_SCOS AS links ON s.SCO_ID = links.SOURCE_SCO_ID
				 INNER JOIN PPS_ACL_ROLLUP ar ON ar.acl_id = links.sco_id AND ar.depth > 0
				 INNER JOIN PPS_SCOS AS curriculum ON curriculum.sco_id = ar.parent_acl_id
				 WHERE (a1.ACTIVITY_TYPE_ID = 6)
				   AND (a2.PRINCIPAL_ID > 0)
				   AND (s.ICON = 19)
				   AND (links.TYPE = 15)
				   AND (curriculum.TYPE = 14)

			UNION ALL 
			
			SELECT curriculum.SCO_ID AS curriculum_id,
							  a1.SCO_ID,
							  CASE
								  WHEN (a1.asset_id = 0) THEN 1
								  WHEN (a1.asset_id IS NULL) THEN 1
								  ELSE
										 (SELECT MAX(sa.version)
										  FROM pps_sco_assets sa
										  WHERE sa.asset_id = a1.asset_id
											AND sa.sco_id = a1.sco_id
											AND asset_type = 0)
							  END AS dim_sco_details_sco_version,
							  a2.PRINCIPAL_ID,
							  s.account_id,
							  '{historical}' AS enrolled_status,
							  NULL AS date_unenrolled
			 FROM PPS_PERMISSION_ACTIVITIES AS a1
			 INNER JOIN
			   (SELECT DISTINCT MAX(ACTIVITY_ID) AS activity_id, PRINCIPAL_ID, SCO_ID
					FROM PPS_PERMISSION_ACTIVITIES
					GROUP BY PRINCIPAL_ID, SCO_ID
				) AS a2 ON a1.ACTIVITY_ID = a2.activity_id
			 INNER JOIN PPS_SCOS AS s ON a1.SCO_ID = s.SCO_ID AND a2.SCO_ID = s.SCO_ID
			 INNER JOIN PPS_PRINCIPALS AS p ON a1.PRINCIPAL_ID = p.PRINCIPAL_ID
			 INNER JOIN PPS_SCOS AS links ON s.SCO_ID = links.SOURCE_SCO_ID
			 INNER JOIN PPS_ACL_ROLLUP ar ON ar.acl_id = links.sco_id AND ar.depth > 0
			 INNER JOIN PPS_SCOS AS curriculum ON curriculum.sco_id = ar.parent_acl_id
			 WHERE (a1.ACTIVITY_TYPE_ID IN (3, 4))
			   AND (p.PRINCIPAL_ID > 0)
			   AND (p.[disabled] IS NOT NULL)
			   AND (s.ICON = 19)
			   AND (links.TYPE = 15)
			   AND (curriculum.TYPE = 14)
			) AS scos1
		  LEFT OUTER JOIN
			(SELECT a1.SCO_ID,
					CASE
						WHEN (a1.asset_id = 0) THEN 1
						WHEN (a1.asset_id IS NULL) THEN 1
						ELSE
							   (SELECT MAX(sa.version)
								FROM pps_sco_assets sa
								WHERE sa.asset_id = a1.asset_id
								  AND sa.sco_id = a1.sco_id
								  AND asset_type = 0)
					END AS dim_sco_details_sco_version,
					a2.PRINCIPAL_ID,
					s.account_id,
					a1.DATE_CREATED AS date_enrolled
			 FROM PPS_PERMISSION_ACTIVITIES AS a1
			 INNER JOIN
			   (SELECT DISTINCT MAX(ACTIVITY_ID) AS activity_id, PRINCIPAL_ID, SCO_ID
					FROM PPS_PERMISSION_ACTIVITIES
					WHERE (ACTIVITY_TYPE_ID IN (3, 4))
					GROUP BY PRINCIPAL_ID, SCO_ID
				) AS a2 ON a1.ACTIVITY_ID = a2.activity_id
			 INNER JOIN PPS_SCOS AS s ON a1.SCO_ID = s.SCO_ID AND a2.SCO_ID = s.SCO_ID
			 INNER JOIN PPS_PRINCIPALS AS p ON a1.PRINCIPAL_ID = p.PRINCIPAL_ID
			 WHERE (s.TYPE = 14)
			   AND (p.PRINCIPAL_ID > 0)
			   OR (s.TYPE = 0)
			   AND (p.PRINCIPAL_ID > 0)
			   AND (s.ICON = 1)
			   OR (s.TYPE = 3)
			   AND (p.PRINCIPAL_ID > 0)
			   AND (s.ICON = 19)
			 
			 UNION ALL 
			 
			 SELECT links.SCO_ID,
							  CASE
								  WHEN (a1.asset_id = 0) THEN 1
								  WHEN (a1.asset_id IS NULL) THEN 1
								  ELSE
										 (SELECT MAX(sa.version)
										  FROM pps_sco_assets sa
										  WHERE sa.asset_id = a1.asset_id
											AND sa.sco_id = a1.sco_id
											AND asset_type = 0)
							  END AS dim_sco_details_sco_version,
							  a2.PRINCIPAL_ID,
							  s.account_id,
							  a1.DATE_CREATED AS date_enrolled
			 FROM PPS_PERMISSION_ACTIVITIES AS a1
			 INNER JOIN
			   (SELECT DISTINCT MAX(ACTIVITY_ID) AS activity_id, PRINCIPAL_ID, SCO_ID
					FROM PPS_PERMISSION_ACTIVITIES
					WHERE (ACTIVITY_TYPE_ID IN (3, 4))
					GROUP BY PRINCIPAL_ID, SCO_ID
				) AS a2 ON a1.ACTIVITY_ID = a2.activity_id
			 INNER JOIN PPS_SCOS AS s ON a1.SCO_ID = s.SCO_ID AND a2.SCO_ID = s.SCO_ID
			 INNER JOIN PPS_PRINCIPALS AS p ON a1.PRINCIPAL_ID = p.PRINCIPAL_ID AND (p.PRINCIPAL_ID > 0)
			 CROSS JOIN pps_scos links
			 WHERE
				(s.SCO_ID IN
					  (SELECT acl.PARENT_ACL_ID
					   FROM PPS_ACL_ROLLUP AS acl
					   INNER JOIN PPS_SCOS AS wrapper ON acl.PARENT_ACL_ID = wrapper.SCO_ID
					   WHERE (acl.ACL_ID = links.SCO_ID)
						 AND (wrapper.ICON IN (14, 19))
						 AND (acl.DEPTH > 0)))
				AND (links.TYPE = 15)
				AND
				(
					(s.TYPE = 14) 
					OR
					(s.TYPE = 0) 
					OR 
					(s.TYPE = 3) AND (s.ICON = 19)
				)
			) AS activities ON scos1.SCO_ID = activities.SCO_ID
		  AND scos1.dim_sco_details_sco_version = activities.dim_sco_details_sco_version
		  AND scos1.PRINCIPAL_ID = activities.PRINCIPAL_ID
		  AND scos1.account_id = activities.account_id) AS scos
	   LEFT OUTER JOIN
		 (SELECT t3.SCO_ID,
				 t3.version_accessed,
				 t3.transcript_id,
				 t3.PRINCIPAL_ID,
				 t3.STATUS,
				 t3.date_created AS date_last_taken,
				 t3.SCORE,
				 t3.MAX_SCORE,
				 t3.attempt_number,
				 t3.average_score,
				 p.account_id
		  FROM PPS_PRINCIPALS AS p
		  INNER JOIN
			(SELECT			 MAX(t1.TRANSCRIPT_ID) AS transcript_id,
							 t1.PRINCIPAL_ID,
							 t1.STATUS,
							 MAX(t1.DATE_CREATED) AS date_created,
							 t1.SCO_ID,
							 t1.SCORE,
							 t1.MAX_SCORE,
							 MAX(t2.attempts) AS attempt_number,
							 t2.average_score,
							 t2.version_accessed
			 FROM PPS_TRANSCRIPTS AS t1
			 INNER JOIN
			   (SELECT DISTINCT w.SCO_ID,
								w.PRINCIPAL_ID,
								w.date_created,
								w.attempts,
								w.average_score,
								CASE
									WHEN sa.version IS NULL THEN 1
									ELSE sa.version
								END AS version_accessed
				FROM
				  (SELECT	t.PRINCIPAL_ID,
							t.SCO_ID,
							MIN(t.DATE_CREATED) AS date_created,
							COUNT(t.TRANSCRIPT_ID) AS attempts,
							ROUND(AVG(t.SCORE), 2) AS average_score,
							MAX(t.ASSET_ID) AS asset_id
				   FROM PPS_TRANSCRIPTS AS t
						INNER JOIN PPS_SCOS AS s ON t.SCO_ID = s.SCO_ID
				   WHERE (t.STATUS IN ('P', 'F', 'C', 'I'))
						AND (
							(s.TYPE = 14)
							OR
							(s.TYPE = 0) AND (s.ICON = 1)
							OR
							(s.TYPE = 3) AND (s.ICON = 19)
							OR 
							(s.TYPE = 15)
						)
				   GROUP BY t.PRINCIPAL_ID,	t.SCO_ID
				) AS w
				LEFT OUTER JOIN PPS_SCO_ASSETS AS sa ON w.SCO_ID = sa.SCO_ID AND w.asset_id = sa.ASSET_ID
			) AS t2 ON t1.PRINCIPAL_ID = t2.PRINCIPAL_ID AND t1.DATE_CREATED = t2.date_created AND t1.SCO_ID = t2.SCO_ID
			 WHERE (t1.STATUS IN ('P', 'F', 'C', 'I'))
			 GROUP BY t1.SCO_ID,
					  t1.PRINCIPAL_ID,
					  t1.[STATUS],
					  t1.SCORE,
					  t1.MAX_SCORE,
					  t2.average_score,
					  t2.version_accessed
			) AS t3 ON p.principal_id = t3.principal_id
		) AS transcripts ON scos.sco_id = transcripts.sco_id
	   AND scos.principal_id = transcripts.principal_id
	   AND scos.account_id = transcripts.account_id
	) AS status
	GROUP BY curriculum_id,
			 dim_sco_details_sco_id,
			 version_accessed,
			 certificate_id,
			 dim_users_user_id,
			 current_status,
			 last_activity_date,
			 score,
			 average_score,
			 attempt_number,
			 enrolled_status,
			 date_enrolled,
			 date_unenrolled,
			 account_id
GO
--------------------------
--END Jira: CONN-4126197
--------------------------

--------------------------
--BEGIN Jira: CONN-4126651
--------------------------
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.views WHERE name = 'REPORTING_VIEW_MEETING_MINUTES')
	DROP VIEW REPORTING_VIEW_MEETING_MINUTES
GO

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- view_system_meeting_minutes
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE VIEW REPORTING_VIEW_MEETING_MINUTES 
    AS 
    SELECT 
            t.principal_id AS dim_users_user_id,
            t.date_created,
            ISNULL(t.date_closed, DATEADD(minute, 30, t.date_created)) AS date_closed,
            t.data_field_id AS dim_cost_center_id,
            s.sco_id,              
            s.account_id,
            DATEDIFF(n, t.date_created, t.date_closed) AS meeting_attendance_minutes            
        FROM
			pps_accounts a
				JOIN pps_scos s ON s.account_id = a.account_id
				JOIN pps_transcripts t ON s.sco_id = t.sco_id
        WHERE
			a.[disabled] IS NULL AND
            s.type = 3 AND
            t.status NOT IN ('X', 'N', 'Q', 'R')
GO
--------------------------
--END Jira: CONN-4126651
--------------------------

--------------------------
--BEGIN Jira: CONN-4126422
--------------------------
IF EXISTS (SELECT * FROM sys.indexes WHERE name='PPS_SCO_ACCOUNT_ID')
	DROP INDEX PPS_SCO_ACCOUNT_ID  ON PPS_SCOS
GO

CREATE NONCLUSTERED INDEX PPS_SCO_ACCOUNT_ID ON PPS_SCOS 
(
 ACCOUNT_ID ASC
) INCLUDE (DISABLED, TYPE, ICON) 
WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, FILLFACTOR=80)
GO
--------------------------
--END Jira: CONN-4126422
--------------------------

--------------------------------------
-- End of updates
--------------------------------------

--------------------------------------------------------------------
-- BEGIN REFRESH block
--
-- Recompile SPs, views and other objects that need to be recompiled
-- Please DO NOT change following lines
--------------------------------------------------------------------
GO
IF CURSOR_STATUS('global', 'ObjectCursor') >= -1
BEGIN
	DEALLOCATE ObjectCursor;
END
GO

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
	SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'
	FROM sys.objects o 
	WHERE type_desc IN ('SQL_STORED_PROCEDURE', 'SQL_TRIGGER', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',
								'SQL_INLINE_TABLE_VALUED_FUNCTION',	'VIEW')
	AND ISNULL(OBJECTPROPERTY(o.object_id,'IsSchemaBound'), 0) = 0	 -- don't check schemabinding they don't like it

OPEN ObjectCursor;
FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
   PRINT @Sql;

   BEGIN TRY
		EXEC @Result = sp_executesql @Sql;
		IF @Result <> 0 RAISERROR('Failed', 16, 1);
   END TRY
   BEGIN CATCH
		-- PRINT @Sql
      PRINT 'The module ''' + @Name + ''' does not compile.';
   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;
GO

----------------------------------------------------------------------------------------------------------
-- Change the version number to match the file build number below
-- MINOR_RELEASE is tantamount to Service Pack
-- The REVISION field should never really be set to anything other than 0 in the DB update scripts context
----------------------------------------------------------------------------------------------------------
UPDATE PPS_DATABASE_VERSION 
	SET MAJOR_RELEASE = 11, 
	MINOR_RELEASE = 2, 
	[UPDATE] = 1, 
	REVISION = 0
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_11_2_1_0.sql#1 $', '$Change: 1420150 $', 'Finish';
GO
SET NOEXEC OFF
SET NOCOUNT OFF
GO