

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/AppSetEDiscoveryForCompany.sp] ---------- 

--  +==============================================================================+
--  |		 		 Name:  AppSetEDiscoveryForCompany()
--  | Description:
--  +==============================================================================+
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppSetEDiscoveryForCompany.sp,v $ $Id: AppSetEDiscoveryForCompany.sp,v 1.1.2.1.14.1 2021/07/08 03:59:05 alakra Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='AppSetEDiscoveryForCompany')
BEGIN
	print '>>> Drop Stored Procedure: AppSetEDiscoveryForCompany <<<'
	drop procedure AppSetEDiscoveryForCompany
END
IF EXISTS (select * from GxQscripts where name='AppSetEDiscoveryForCompany')
	delete from GxQscripts where name = 'AppSetEDiscoveryForCompany'
GO

IF EXISTS (select * from GXDBVersions where aliasname='AppSetEDiscoveryForCompany')
	delete from GXDBVersions where aliasname = 'AppSetEDiscoveryForCompany'
GO
print '... Creating Procedure: AppSetEDiscoveryForCompany'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppSetEDiscoveryForCompany
  @companyId INT,
  @clientIds NVARCHAR(MAX),
  @xmlData XML,
  @o_errorCode INT OUTPUT,
  @o_errorString NVARCHAR(MAX) OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @companySCG INT
DECLARE @enableEDiscoveryForO365 INT
DECLARE @enableEDiscoveryForLaptop INT
DECLARE @enableEDiscoveryForO365Request INT
DECLARE @enableEDiscoveryForLaptopRequest INT
DECLARE @nowTime INTEGER = dbo.GetUnixTime (GetUTCdate())
DECLARE @companyDefaultValue INT = -1
SET @o_errorCode = 0
SET @o_errorString = ''
SET @enableEDiscoveryForO365Request = ISNULL(@xmlData.value('(//./organizationInfo/organizationProperties/@enableEDiscoveryForO365)[1]', 'INT'), -1)
SET @enableEDiscoveryForLaptopRequest = ISNULL(@xmlData.value('(//./organizationInfo/organizationProperties/@enableEDiscoveryForLaptop)[1]', 'INT'), -1)
-- First condition
-- IF request has XML data but none of these values set in XML, don't do any thing
-- If we have empty request, then we need to read value from DB for that company
IF ((@xmlData IS NOT NULL) AND (@xmlData.exist('*') <> 0) AND (@enableEDiscoveryForO365Request=-1 AND @enableEDiscoveryForLaptopRequest=-1))
BEGIN
	-- We don't have anything applicable. Just exit this script wihtout any error
	GOTO SCRIPT_EXIT
END
IF OBJECT_ID('tempdb.dbo.#CompanyClientsTable') IS NOT NULL DROP TABLE #CompanyClientsTable
IF OBJECT_ID('tempdb.dbo.#InputClientsTable') IS NOT NULL DROP TABLE #InputClientsTable
CREATE TABLE #CompanyClientsTable
(
	clientId  INT PRIMARY KEY,
	isEnable INT
)
CREATE TABLE #InputClientsTable
(
	clientId  INT PRIMARY KEY
)
IF ((@companyId=-1) AND ((@clientIds IS NOT NULL) AND (@clientIds <> '')))
BEGIN
	-- For new client, we will have one client id
	-- For Change company, we might have more than 1 client which will go into same company
	--	-- For getting company Id from client, we will only fetch from top 1 . Assumption is that this code is not passed client ids with different company Ids
	INSERT INTO #InputClientsTable
	SELECT DISTINCT _ID FROM dbo.SplitIDs(@clientIds)
	-- In case. we recieve multiple client ids which have different company ids, this code will throw exception
	-- Which should be caught by caller.
	-- In this case, read company Id from App_clientProp (installation Company Id)
	SET @companyId = ISNULL((SELECT TOP 1 ACP.attrval FROM APP_ClientProp ACP
								INNER JOIN #InputClientsTable ICT
									ON ICT.clientId=ACP.componentNameId
WHERE ACP.attrname='Installation Company ID' AND modified=0
								GROUP BY ACP.attrval), 0)
	-- Also check client current state(if 'Content Indexing Enabled') is enabled or not (even if that mean we have only 1 client with this property enabled)
	-- This is important in case of Change Company entry point
	-- when our API will be callled, we have no ideal about last company and our default in case of missing setting is to not do anything
	-- Hence, if any client has this property enabled, then our default for Company Ediscovery property needs to be 0 (that will help us disable on client in case company is missing this property)
	-- IF EXISTS (SELECT 1 FROM APP_ClientProp ACP
	-- 							INNER JOIN #InputClientsTable ICT
	-- 								ON ICT.clientId=ACP.componentNameId
	-- 							WHERE ACP.modified=0 AND ACP.attrName='Content Indexing Enabled' AND attrVal='1')
	-- BEGIN
	-- 	SET @companyDefaultValue = 0
	-- END
	-- Above section is wrong. If any client has this feature enabled-CI and next company don't have this setting, then it should be left as enabled.
	-- We should not enforce and disable it if next company doesn't have this setting. In case, metallic want this feature to be disabled when client moves, they need to set this at company level.
END
ELSE
BEGIN
	-- If we are passed company Id, then only read company SCG
	-- This will imply that we have to process all clients of that company
	-- Once company id is provided, all clients ot hat company will be processed irrespective of the input for client id
SELECT @companySCG = attrValInt FROM App_CompanyProp WHERE componentNameId=@companyId AND attrname='Associated Smart Client Group' AND modified=0 AND cs_attrName = CHECKSUM('Associated Smart Client Group')
END
SET @enableEDiscoveryForLaptop  = ISNULL((SELECT attrValInt from App_CompanyProp where attrName = 'Enable eDiscovery for Laptop' and componentNameId = @companyId AND cs_attrName = CHECKSUM('Enable eDiscovery for Laptop') AND modified = 0), @companyDefaultValue)
SET @enableEDiscoveryForO365   = ISNULL((SELECT attrValInt from App_CompanyProp where attrName = 'Enable eDiscovery for O365' and componentNameId = @companyId AND cs_attrName = CHECKSUM('Enable eDiscovery for O365') AND modified = 0), @companyDefaultValue)
IF ((@xmlData IS NOT NULL) AND (@xmlData.exist('*') <> 0))
BEGIN
	-- We have valid XML data. Then if something is not passed in request, then there default value should be the one in DB for that company
	-- Else, we will always end up changing properties for both cases even when user is trying to change anyone only
	IF (@enableEDiscoveryForLaptopRequest = -1)
	BEGIN
		SET @enableEDiscoveryForLaptopRequest = @enableEDiscoveryForLaptop
	END
	IF (@enableEDiscoveryForO365Request = -1)
	BEGIN
		SET @enableEDiscoveryForO365Request = @enableEDiscoveryForO365
	END
END
-- We can have following cases -
--	1. Company Request
--	--	--	We have some value in request XML and it is different from one present in DB
--	2. New client installation call
--	--	--	In this case, request XML would be empty, so @enableEDiscoveryForLaptopRequest is -1. We read value from DB and apply to client accordingly.
--	3. Company Change for Client
--	--	--	In this case, request XML would be empty, so @enableEDiscoveryForLaptopRequest is -1. We read value from DB and apply to client accordingly.
IF (@enableEDiscoveryForLaptop<>@enableEDiscoveryForLaptopRequest)
BEGIN
	-- If request was empty and request tag was not present, then we have to honor value present in DB.
	-- Hence, below change is needed
	IF (@enableEDiscoveryForLaptopRequest = -1)
	BEGIN
		SET @enableEDiscoveryForLaptopRequest = @enableEDiscoveryForLaptop
	END
	-- One query for clientid and other for getting details from company SCG
	-- This effectively covers all entry point (either we have valid client which is laptop or all clients withing that company scg)
	INSERT INTO #CompanyClientsTable
	SELECT DISTINCT AC.id, @enableEDiscoveryForLaptopRequest
		FROM APP_Client AC
	INNER JOIN APP_ClientGroupAssoc AAG
		ON AAG.clientId=AC.id AND AAG.clientGroupId=@companySCG
WHERE AC.status&0x1000=0x1000
	UNION
	SELECT DISTINCT AC.id, @enableEDiscoveryForLaptopRequest
		FROM APP_Client AC
	INNER JOIN  #InputClientsTable AAG
		ON AAG.clientId=AC.id
WHERE AC.status&0x1000=0x1000
END
-- We can following cases -
--	1. Company Request
--	--	--	We have some value in request XML and it is different from one present in DB
--	2. New client installation call
--	--	--	In this case, request XML would be empty, so @enableEDiscoveryForO365Request is -1. We read value from DB and apply to client accordingly.
--	3. Company Change for Client
--	--	--	In this case, request XML would be empty, so @enableEDiscoveryForO365Request is -1. We read value from DB and apply to client accordingly.
IF (@enableEDiscoveryForO365<>@enableEDiscoveryForO365Request)
BEGIN
	-- If request was empty and request tag was not present, then we have to honor value present in DB.
	-- Hence, below change is needed
	IF (@enableEDiscoveryForO365Request = -1)
	BEGIN
		SET @enableEDiscoveryForO365Request = @enableEDiscoveryForO365
	END
	-- One query for clientid and other for getting details from company SCG
	-- This effectively covers all entry point
	INSERT INTO #CompanyClientsTable
	SELECT DISTINCT AC.id, @enableEDiscoveryForO365Request
		FROM APP_Client AC
		INNER JOIN APP_ClientGroupAssoc AAG
				ON AAG.clientId=AC.id AND AAG.clientGroupId=@companySCG
		INNER JOIN APP_Application AAP
ON AAP.clientId=AC.id AND AAP.appTypeId IN (78,134)
		INNER JOIN APP_InstanceProp AIN
			ON AIN.componentNameId=AAP.instance AND ((AAP.appTypeId=78) OR (AAP.appTypeId=134 AND AIN.attrName = 'Cloud Apps Instance Type'  AND AIN.attrval = '7' AND AIN.modified = 0))
		LEFT OUTER JOIN APP_ClientProp ACP_V1
			ON ACP_V1.componentNameId=AC.id AND ACP_V1.attrName='Office 365 Pseudo Client' AND ACP_V1.attrVal='1' AND ACP_V1.modified=0
		LEFT OUTER JOIN #CompanyClientsTable CT
			ON CT.clientId=AC.id
		WHERE ACP_V1.id IS NULL AND CT.clientId IS NULL
	UNION
	SELECT DISTINCT AC.id, @enableEDiscoveryForO365Request
		FROM APP_Client AC
		INNER JOIN #InputClientsTable AAG
				ON AAG.clientId=AC.id
		INNER JOIN APP_Application AAP
ON AAP.clientId=AC.id AND AAP.appTypeId IN (78,134)
		INNER JOIN APP_InstanceProp AIN
			ON AIN.componentNameId=AAP.instance AND ((AAP.appTypeId=78) OR (AAP.appTypeId=134 AND AIN.attrName = 'Cloud Apps Instance Type'  AND AIN.attrval = '7' AND AIN.modified = 0))
		LEFT OUTER JOIN APP_ClientProp ACP_V1
			ON ACP_V1.componentNameId=AC.id AND ACP_V1.attrName='Office 365 Pseudo Client' AND ACP_V1.attrVal='1' AND ACP_V1.modified=0
		LEFT OUTER JOIN #CompanyClientsTable CT
			ON CT.clientId=AC.id
		WHERE ACP_V1.id IS NULL AND CT.clientId IS NULL
END
-- We have all applicable clients
-- Just update them
--Set Property on the client level
MERGE APP_ClientProp AS T1
    USING #CompanyClientsTable T2
    ON T1.componentNameId = T2.clientId  AND T1.attrName='Content Indexing Enabled' and T1.modified=0
    WHEN MATCHED and T1.attrVal <> T2.isEnable THEN
        UPDATE SET T1.attrVal=T2.isEnable
    WHEN NOT MATCHED THEN
        INSERT(componentNameId, attrName, attrType, attrVal, created, modified)
        VALUES(T2.clientId,'Content Indexing Enabled',2,CAST(T2.isEnable AS NVARCHAR(32)), @nowTime ,0);
SCRIPT_EXIT:
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'AppSetEDiscoveryForCompany')
	delete from GxQscripts where name = 'AppSetEDiscoveryForCompany'
GO

IF EXISTS (select * from GXDBVersions where aliasname='AppSetEDiscoveryForCompany')
	delete from GXDBVersions where aliasname = 'AppSetEDiscoveryForCompany'
GO

insert into GXDBVersions values(2, 'AppSetEDiscoveryForCompany',  'v1.1.2.1.14.1', 'AppSetEDiscoveryForCompany', 'v1.1.2.1.14.1')
GO

