-------------------------------------------------------------------------------
-- 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/branches/AdobeConnect/11.0.6dev/components/dbscripts/updates/update_11_0_5_0.sql#1 $', '$Change: 1416576 $', 'Start';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------
-- Start of updates
--------------------------------------

UPDATE PPS_ENUM_ZONES SET ZONE_DESCRIPTION = 'EMEA Production 8' WHERE ZONE_ID=48
GO

UPDATE PPS_ENUM_ZONES SET ZONE_DESCRIPTION = 'EMEA Production 9' WHERE ZONE_ID=49
GO

--------------------------
--BEGIN Jira: CONN-4117548
--------------------------
IF EXISTS(SELECT TOP 1 1 FROM sys.views WHERE [name] = 'REPORTING_VIEW_MEETING_USER_SESSIONS')
	DROP VIEW REPORTING_VIEW_MEETING_USER_SESSIONS
GO
CREATE VIEW REPORTING_VIEW_MEETING_USER_SESSIONS
  AS
	WITH sa (account_id, domain_name, sco_id, meeting_name, url_path, type, icon, asset_id, session_start_time, session_end_time, total_users, user_minutes) AS (
		SELECT s.account_id, a.domain_name, s.sco_id, s.name, s.url_path, s.type, s.icon, t.asset_id,
			MIN(t.date_created) AS session_start_time, 
			MAX(t.date_closed) As session_end_time, 
			COUNT(DISTINCT ISNULL(t.principal_id, -t.session_id)) AS distinct_tickets,
			SUM(DATEDIFF(MINUTE, t.date_created, ISNULL(t.date_closed, t.date_created))) AS user_minutes
			FROM pps_scos s 
			join pps_transcripts t ON s.sco_id = t.sco_id
			JOIN pps_accounts a on a.account_id = s.account_id
			WHERE (s.type = 3) AND (t.status  IN ('P', 'F', 'C')) AND a.disabled is null
			GROUP BY s.account_id, a.domain_name, s.sco_id, s.name, s.url_path, s.type, s.icon, t.asset_id
	)
	SELECT 
	  sa.sco_id AS sco_id, 
	  sa.meeting_name,
	  sa.account_id AS account_id, 
	  sa.asset_id AS session_id, 
	  session_start_time,
	  session_end_time,
	  total_users,
	  user_minutes,
	  ISNULL(t_peak_users.peak_session_users, 0) AS peak_session_users,
	  'http://' + sa.domain_name + sa.url_path AS meeting_url,
	  p.name AS meeting_host, 
	  CASE 
		WHEN sa.icon = 19 THEN '{classroom}' 
		WHEN sa.icon = 3 AND sa.type = 3 
			AND EXISTS (SELECT ss.sco_id FROM pps_scos ss JOIN pps_acl_rollup ar ON ar.acl_id = sa.sco_id
							WHERE ar.parent_acl_id = ss.sco_id AND ss.name = '{tree-type-seminars}')  
		  THEN '{seminar}'
		ELSE '{meeting}'
	  END AS meeting_type
	FROM sa 
		INNER JOIN PPS_ACLS acl on sa.SCO_ID = acl.ACL_ID
		INNER JOIN pps_principals AS p ON p.principal_id = acl.OWNER_PRINCIPAL_ID
		OUTER APPLY (SELECT MAX(tc.transcript_count) AS peak_session_users
				FROM pps_transcript_counts tc
					join pps_assets a on a.asset_id=sa.asset_id
					WHERE tc.acl_id = sa.sco_id
						AND (tc.date_created BETWEEN a.date_created AND a.date_end)
				) t_peak_users
GO
--------------------------
--END Jira: CONN-4117548
--------------------------

--------------------------
--BEGIN Jira: CONN-4124549
--------------------------
IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 752)
	INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME) VALUES (752, 'account-level-preload-hybrid-ppt-conversion');
GO
--------------------------
--END Jira: CONN-4124549
--------------------------



--------------------------------------------------
-- End of updates - !!! DO NOT ADD LINES BELOW !!!
--------------------------------------------------

--------------------------------------------------------------------
-- Various data maintenance points
--------------------------------------------------------------------

--------------------------------------------------------------------

--------------------------------------------------------------------
-- 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 = 0, 
	[UPDATE] = 6, 
	REVISION = 0
GO

EXECUTE updateLogger '$Id: //depot/branches/AdobeConnect/11.0.6dev/components/dbscripts/updates/update_11_0_5_0.sql#1 $', '$Change: 1416576 $', 'Finish';
GO
SET NOCOUNT OFF
SET NOEXEC OFF
GO