-------------------------------------------------------------------------------
-- 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()) < 100)
BEGIN
	PRINT 'Please alter your database compatibility level'
	PRINT 'Minimal compatibility level is 100 - SQL Server 2008R2'
	SET NOEXEC ON
END
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_10_5_1_0.sql#3 $', '$Change: 1392689 $', 'Start';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--------------------------------------
-- Start of updates
--------------------------------------
------------------------------------------------------------------------------------------------------
--BEGIN CONN-4113745: Label to show linked Captivate Account
------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 701)
    INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME) VALUES (701, 'linked-account-url');
GO
IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 702)
    INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME) VALUES (702, 'linked-account-label');
GO
------------------------------------------------------------------------------------------------------
--END CONN-4113745
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
--BEGIN CONN-4114404: DW jobs on EMEA2 and NA7 failing in some steps
------------------------------------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM sys.views WHERE name = 'REPORTING_VIEW_TRAINING_LINK_STATUS')
	DROP VIEW REPORTING_VIEW_TRAINING_LINK_STATUS
GO

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- view_training_link_status
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE VIEW REPORTING_VIEW_TRAINING_LINK_STATUS 
    AS 
        SELECT
            status.dim_sco_details_sco_id, 
            MAX(status.dim_sco_details_sco_version) AS dim_sco_details_sco_version,
            status.parent_sco_id,
            status.transcript_sco_id,
            status.version_accessed,
            status.certificate_id,
            status.dim_users_user_id,
            status.current_status,
            status.last_activity_date,
            status.score,
            status.average_score,
            status.attempt_number,
            status.enrolled_status,
            status.date_enrolled,
            status.date_unenrolled,
            status.account_id
        FROM
        (
            SELECT 
                scos.sco_id AS dim_sco_details_sco_id, 
                scos.dim_sco_details_sco_version,
                scos.parent_sco_id,
                scos.transcript_sco_id,
                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,
                CASE 
                    WHEN transcripts.date_last_taken IS NOT NULL THEN transcripts.date_last_taken
                    WHEN scos.date_unenrolled IS NOT NULL THEN scos.date_unenrolled
                    ELSE scos.date_enrolled
                END 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.sco_id,
                        scos1.dim_sco_details_sco_version,
                        scos1.parent_sco_id,
                        scos1.principal_id,
                        scos1.transcript_sco_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 date_created FROM pps_scos WHERE sco_id = scos1.sco_id)
                            ELSE activities.date_enrolled
                        END AS date_enrolled
                    FROM (

                  
                        SELECT
                            base_sco.sco_id, 
                            1 AS dim_sco_details_sco_version,
                            base_sco.parent_sco_id,
                            base_sco.principal_id, 
                            CASE 
                               WHEN (base_sco.icon = 3) THEN base_sco.source_sco_id
                               WHEN (base_sco.icon = 19) THEN base_sco.source_sco_id
                               WHEN (base_sco.icon = 1 AND base_sco.max_transcript_id IS NOT NULL) THEN (SELECT sco_id FROM pps_transcripts WHERE transcript_id = base_sco.max_transcript_id)
                               ELSE base_sco.sco_id
                            END AS transcript_sco_id,
                            base_sco.account_id,
                            '{current}' AS enrolled_status, NULL AS date_unenrolled

                        FROM
                            (
                                SELECT 
                                     links.sco_id,
                                     links.source_sco_id,
                                     s.sco_id AS parent_sco_id,
                                     g.principal_id, 
                                     links.icon,
                                     (SELECT MAX(tr.transcript_id) FROM pps_transcripts tr WHERE tr.sco_id IN (links.sco_id, links.source_sco_id) and tr.principal_id = g.principal_id AND tr.status NOT IN ('N', 'Q', 'R', 'X', 'D')) AS max_transcript_id,
                                     s.account_id
                                FROM 
                                    pps_acl_entries as a  
                                WITH (INDEX(pk2_pps_acl_entries)),    
                                    pps_group_member_rollup g, pps_principals p, pps_ext_enum_utype e, pps_scos s, pps_scos links
                                WHERE 
                                    a.acl_id = s.sco_id AND    
                                    a.principal_id = g.group_id AND 
                                    g.principal_id = p.principal_id AND
                                    p.has_children = 0 AND
                                    p.type = e.type AND   
                                    p.disabled IS NULL AND 
                                    p.principal_id > 0 AND
                                    a.permission_id IN (2, 10, 22) AND
                                    (s.type = 14 OR (s.type = 0 AND s.icon = 1) OR (s.type = 3 AND s.icon = 19)) AND
                                    links.type = 15 AND
                                    s.sco_id IN (
                                        SELECT
                                            acl.parent_acl_id 
                                        FROM 
                                            pps_acl_rollup acl, pps_scos wrapper
                                        WHERE
                                            acl.acl_id = links.sco_id AND
                                            acl.parent_acl_id = wrapper.sco_id AND
                                            wrapper.icon IN (14, 19) AND
                                            depth > 0)
                            ) base_sco

                        ) scos1

                    LEFT OUTER JOIN
                        (

                        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_scos links 
						JOIN pps_acl_rollup ar ON links.sco_id = ar.acl_id AND ar.depth > 0
						JOIN pps_scos s ON ar.parent_acl_id = s.sco_id
						JOIN pps_permission_activities a1 ON a1.sco_id = s.sco_id
						JOIN pps_principals p ON p.principal_id = a1.principal_id AND p.principal_id > 0 
                        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
	                        ) a2 ON a2.sco_id = s.sco_id
                    WHERE
                        a1.activity_id = a2.activity_id 
                        AND (s.type = 14 OR (s.type = 0 AND s.icon = 1) OR (s.type = 3 AND s.icon = 19))
						AND links.type = 15 AND s.icon IN (14, 19)
                        ) 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
                ) 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 p,  
                         (SELECT 
                             DISTINCT 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 t1,        
                             (
                                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 
                                        DISTINCT t.principal_id, t.sco_id, MAX(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 t, 
                                        pps_scos s 
                                    WHERE 
                                        t.status IN ('P', 'F', 'C', 'I') AND 
                                        t.sco_id = s.sco_id 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
                                ) w
                                LEFT OUTER JOIN
                                    pps_sco_assets sa
                                ON
                                    w.sco_id = sa.sco_id AND
                                    w.asset_id = sa.asset_id AND
                                    sa.asset_type = 0) t2  
                          WHERE  
                              t1.principal_id = t2.principal_id AND  
                              t1.date_created = t2.date_created AND  
                              t1.sco_id = t2.sco_id AND
                              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) t3   
                    WHERE   
                        p.principal_id = t3.principal_id
                ) transcripts
            ON
            scos.transcript_sco_id = transcripts.sco_id AND
            scos.principal_id = transcripts.principal_id AND
            scos.account_id = transcripts.account_id
        ) status
        
        GROUP BY status.dim_sco_details_sco_id, status.version_accessed, status.parent_sco_id, 
            status.transcript_sco_id, status.certificate_id, status.dim_users_user_id,
            status.current_status, status.last_activity_date, status.score,
            status.average_score, status.attempt_number, status.enrolled_status,
            status.date_enrolled, status.date_unenrolled, status.account_id
GO
------------------------------------------------------------------------------------------------------
--END of CONN-4114404
------------------------------------------------------------------------------------------------------

--------------------------------------
-- 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

UPDATE PPS_DATABASE_VERSION 
	SET MAJOR_RELEASE = 10, 
	MINOR_RELEASE = 5, 
	[UPDATE] = 1, 
	REVISION = 0
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_10_5_1_0.sql#3 $', '$Change: 1392689 $', 'Finish';
GO

