

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

--  +========================================================================+
--  | Name:  AppCreateCustomDomainUsers
--  | Description: Takes an XML with user info and domain info
--  | creates user if the user does not exist in DB
--  +========================================================================+
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppCreateCustomDomainUsers')
	delete from GXDBVersions where aliasname = 'AppCreateCustomDomainUsers'
GO
print '... Creating Procedure: AppCreateCustomDomainUsers'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppCreateCustomDomainUsers
  @inXML XML OUTPUT
AS
BEGIN TRY
DECLARE @userId INT = 0
DECLARE @errorCode   integer=0
DECLARE @errorStr   nvarchar(MAX)
DECLARE @owner nvarchar(256)
DECLARE @domainname nvarchar(256)
DECLARE @loginname nvarchar(256)
DECLARE @extAuthServer NVARCHAR(1024) = N''
DECLARE @defaultUserGroupName nvarchar(256)
DECLARE @customDomainUserGroupId INT=0
DECLARE @pos INT =0
DECLARE @providerId INT = 0
DECLARE @userGUID nvarchar(255)= N''
DECLARE @userSID nvarchar(max)= N''
DECLARE @email nvarchar(256)=N''
DECLARE @userUPN NVARCHAR(256)=N''
DECLARE @userType INT = 0
DECLARE @fullName nvarchar(256)=N''
DECLARE @description nvarchar(max)=N''
DECLARE @ownerCompany INT = 0   -- Default Owner Company is 0
DECLARE @ownerUserId INT = 0   -- Default userId is 1
DECLARE @createOnlyDomainAndUserGroups INT = 0
DECLARE @customDomainName nvarchar(255) = NULL
DECLARE @adGroupsTbl Table(id int identity(1,1) PRIMARY KEY, groupName NVARCHAR(MAX), groupId INT)
DECLARE @useUPNForEmail INT = 0
DECLARE @clientId INT  = 0
SET @userType = ISNULL((SELECT  ref.value('@userType', 'integer')
					FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref )),0)
SET @owner = ISNULL((SELECT  ref.value('@userName', 'nvarchar(256)')
					FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/user') R ( ref )),N'')
SET @ownerCompany = ISNULL((SELECT  ref.value('@ownerCompany', 'INT')
				    FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref )),0)
SET @clientId = ISNULL((SELECT  ref.value('@clientId', 'INT')
				    FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/physicalClient') R ( ref )),0)
IF @userType = 3
BEGIN
    SET @email = ISNULL((SELECT  ref.value('@email', 'nvarchar(256)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
	SET @userUPN = ISNULL((SELECT  ref.value('@userPrincipalName', 'nvarchar(256)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
    SET @fullName = ISNULL((SELECT  ref.value('@fullname', 'nvarchar(256)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
    SET @description = ISNULL((SELECT  ref.value('@description', 'nvarchar(max)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
    SET @domainname = ISNULL((SELECT  ref.value('@userDomain', 'nvarchar(256)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
    SET @userGUID = ISNULL((SELECT  ref.value('@uGUID', 'nvarchar(256)')
				    FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),N'')
    SET @userSID =ISNULL(REPLACE(CAST((SELECT  ref.query('sidInfo')
                FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref ) ) as nvarchar(max)),'sidInfo','App_ADSidInfo'),NULL)
	-- For backward compatibility, if we get SID info from older xml request, then read that
	-- In both cases, generate XML right over here
	IF @userSID IS NULL
	BEGIN
		SET @userSID = ISNULL((SELECT  ref.value('@uSID', 'nvarchar(1024)')
					FROM @inXML.nodes('/App_CustomDomainUserCreationRequest/adUserInfo') R ( ref )),NULL)
		IF @userSID IS NOT NULL
		BEGIN
			SET @userSID=CAST((SELECT @userSID as '@uSID'
									FOR XML PATH('App_ADSIDInfo'),TYPE) AS NVARCHAR(MAX))
		END
	END
	-- If the given user is AD user and on that user owner company if property "Use UPN For Email" is set, then we have to override email with that value.
	-- Form 118 in SP12 allows us to set UPN at commcell level in gxglobalParam table.
	-- Form 2549 in SP11 allows us to set UPN at company level in App_companyProp table.
	-- To handle both scenarios we look into the gxglobalparam table for the UPN property only when companyId = 0 and we dont find UPN property set at company level.
SET @useUPNForEmail = ISNULL((SELECT attrValInt FROM APP_CompanyProp where componentNameID=@ownerCompany AND attrname='Use UPN For Email' AND modified=0 AND cs_attrName=checksum('Use UPN For Email')),
							CASE WHEN @ownerCompany = 0 THEN
ISNULL((SELECT CAST(value AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'Use UPN For Email' AND modified = 0),0)
							ELSE 0 END)
END
IF @description=''
BEGIN
	SET @description = 'Entity created by system at ' + CAST(GETUTCDATE() AS NVARCHAR(64))
END
SET @ownerUserId = ISNULL((SELECT  ref.value('@ownerID', 'INT')
				    FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref )),0)
SET @defaultUserGroupName = ISNULL((SELECT  ref.value('@defaultUserGroupName', 'nvarchar(256)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref )),N'')
SET @customDomainName = (SELECT  ref.value('@domainName', 'nvarchar(255)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref ))
SET @loginname = @owner
-- find if the domain\login format OR only login name given
SET @pos=CHARINDEX('\',@owner,1)
IF @pos <> 0
BEGIN
	SET @domainname =LTRIM(RTRIM(LEFT(@owner,@pos-1)))
	SET @loginname= RIGHT(@owner,LEN(@owner)-@pos)
	IF @domainName = 'Commcell.Local'
	BEGIN
		SET @domainname = N''
	END
END
SET @providerId = ISNULL((SELECT id FROM UMDSProviders WITH (READUNCOMMITTED) WHERE
				domainName = @domainName),0)
DECLARE @compId INT = dbo.AppGetCompanyForProvider(@providerId)
If(@compId <> 0 AND @compId <> (ISNULL((SELECT CONVERT(INT,attrVal) FROM App_ClientProp WITH(NOLOCK) WHERE componentNameId = @clientId AND attrName = 'Installation Company ID' AND modified = 0),0)))
BEGIN
SET @errorCode = (4604 | (CAST(POWER(2, 24) AS BIGINT) * 35))
SET @errorStr = (SELECT message FROM EvLocaleMsgs WHERE messageId = (4604 | (CAST(POWER(2, 24) AS BIGINT) * 35)) AND localeId = 0)
	SET @errorStr = REPLACE(@errorStr, '^1%s',(SELECT name FROM App_client WITH (NOLOCK) WHERE id = @clientId))
	GOTO PROC_EXIT
END
IF @domainName = 'EXT_AUTH_USER'
BEGIN
	SET @extAuthServer = ISNULL((SELECT  ref.value('@externalAuthServer', 'nvarchar(max)')
				FROM @inXML.nodes('/App_CustomDomainUser') R ( ref )),N'')
	IF  @extAuthServer <> ''
    BEGIN
			SET @customDomainUserGroupId = (select top 1 componentnameid from UMGroupsProp where attrName = 'External Authentication URL'
			and attrVal like '%'+ @extAuthServer +'%' and modified = 0)
    END
END
IF @customDomainName IS NOT NULL
BEGIN
	SET @domainname=ISNULL((SELECT  ref.value('@domainName', 'nvarchar(max)')
				FROM @inXML.nodes('/App_CustomDomainUserCreationRequest') R ( ref )),N'')
	SET @createOnlyDomainAndUserGroups = 1
END
IF LEN(@domainName) > 0
BEGIN
	-------------------------------------------------------------------------------------------------------------------------------------------
--- CODE BELOW HERE IS FROM AppCreateCustomDomain.spb  ----------
-- give few parameters (customDomain,ownerCompany,ownerUserId,userGroupName), this code can create domain
-- identify if the owner is in hostname\username format, and convert to domainName\username
SET @providerId = ISNULL((SELECT id FROM UMDSProviders WITH (READUNCOMMITTED) WHERE domainName = @domainName ),0)
IF @providerId = 0
BEGIN
	-- Create a dummy domain
	DECLARE @desc NVARCHAR(128) = 'Entity created by system at ' + CAST(GETUTCDATE() AS NVARCHAR(64))
	INSERT UMDSProviders (domainName, hostName, description, login, password, trustedHostUser, trustedHostPW, flags, enabled, serviceType,
							modified, dnsRoot, dnsRootStatus, useSecureLdap, origCCId, GUID, port, resourceId, checkTime,ownerCompany,ownerId )
VALUES(@domainName, @domainName, @desc,'', '', '', '', CAST(0x0004 AS INT), 1, 12,
							0,'','',0, default, default, 0, 0,86400,@ownerCompany,@ownerUserId)
	SET @providerId = ISNULL((SELECT id FROM UMDSProviders WITH (READUNCOMMITTED) WHERE hostname = @domainName ),0)
	DECLARE @errString			nvarchar(1024)
	DECLARE @errCode INT
DECLARE @TenantAdminGroupId int = ISNULL((SELECT id FROM umgroups WITH (NOLOCK) WHERE umdsProviderId=@ownerCompany AND groupFlags &  0x10000 = 0x10000),0)
DECLARE @userManagementRoleId int = ISNULL((SELECT id FROM UMRoles WITH (NOLOCK) WHERE (name = 'UserManagement_Owner') AND (flags & 128 <> 0)), 1)
EXEC sec_setCreatorForEntity @ownerUserId, @userManagementRoleId, '', @errCode OUTPUT, @errString OUTPUT, 61, @providerId, 0, 0, 0, 0, 0, 0, 0, 0, @TenantAdminGroupId
	-- Logic
	-- If you have valid company id - where you have actual parent company, then keep working the same logic
	-- If user have specified some different user group name, then we will also create that one.
	-- Newly created user will be added to that group
END
--- CODE Till HERE IS FROM AppCreateCustomDomain.spb  ----------
	-------------------------------------------------------------------------------------------------------------------------------------------
	IF @providerId <> 0
	BEGIN
		SET @owner = (SELECT domainName FROM UMDSProviders WITH (READUNCOMMITTED) where id = @providerId ) + '\' + @loginname
	END
	IF(@createOnlyDomainAndUserGroups = 1)
	GOTO PROC_EXIT
END
IF @fullname = N''
BEGIN
    SET @fullname = @loginname
END
-- No association for custom domain user group (Tenant Admin or Tenant Users)
--IF @customDomainUserGroupId = 0
--BEGIN
--	IF @defaultUserGroupName = '' and @providerId <> 0
--	BEGIN
--		SET @defaultUserGroupName =  'Tenant Users'
--	END
--	SET @customDomainUserGroupId = ISNULL( (SELECT id from UMGROUPS WITH (NOLOCK) where name = @defaultUserGroupName and umdsproviderId = @providerId),0)
--END
-- Sequence is as per Mr  - https:--engweb.commvault.com/engtools/defect/207409
-- Below table to keep same SPB
DECLARE  @adUserInfoTbl TABLE(userId INT DEFAULT (0), reqLoginAlias NVARCHAR(1024), loginAlias NVARCHAR(1024), upn NVARCHAR(1024) DEFAULT '', email NVARCHAR(1024) DEFAULT '', uguid nvarchar(64), uSID nvarchar(128), useUPN INT DEFAULT(0))
INSERT INTO @adUserInfoTbl (reqLoginAlias , loginAlias , upn , email , uguid , useUPN)
SELECT @owner, @owner, @userUPN, @email, @userGUID, @useUPNForEmail
------------------------------------------------------------------------------------------------------
--- CODE BELOW HERE IS FROM AppFindDomainUser.spb  ----------
-- Sequence is as per Mr  - https:--engweb.commvault.com/engtools/defect/207409
	-- first check on GUID
	UPDATE ADT
		SET ADT.userId=UU.id, ADT.loginAlias=UU.login
	FROM @adUserInfoTbl ADT
	INNER JOIN UMUsers UU
		ON ((UU.userGuid=ADT.uguid) AND (ADT.uguid <> '') AND (UU.userGuid IS NOT NULL))
	-- Second check on Email
	IF (@useUPNForEmail=0)
	BEGIN
		UPDATE ADT
			SET ADT.userId=UU.id, ADT.loginAlias=UU.login
		FROM @adUserInfoTbl ADT
		INNER JOIN UMUsers UU
			ON ((ADT.email = UU.email) AND (ADT.email <> ''))
		WHERE ADT.userId=0
	END
	-- Third check on UPN
	UPDATE ADT
		SET ADT.userId=UU.id, ADT.loginAlias=UU.login
	FROM @adUserInfoTbl ADT
	INNER JOIN UMUsers UU
		ON (((ADT.upn=UU.email) AND (ADT.upn <> '')))
	WHERE ADT.userId=0
	-- Fourth Check for login directly
	UPDATE ADT
		SET ADT.userId=UU.id
	FROM @adUserInfoTbl ADT
	INNER JOIN UMUsers UU
		ON (ADT.loginAlias=UU.login)
	WHERE ADT.userId=0
--- CODE Till HERE IS FROM AppFindDomainUser.spb  ----------
------------------------------------------------------------------------------------------------------
-- Since we have only 1 user here, we can directly refer userid
SET @userId = ISNULL((SELECT userId FROM @adUserInfoTbl),0)
-- If for this AD, we have setting at company level to use email as primary id, then we have to update our derived value as UPN instead of email.
-- since useUPNForEmail will be 1 only for AD users (see code for reading value in this variable), we should be good with below checks
IF (@useUPNForEmail=1) AND (@userUPN<>'')
BEGIN
	SET @email=@userUPN
END
IF @providerId = 0
BEGIN
	SET @errorCode	 = 1
	SET @errorStr = 'Unknown provider.Skipping creation of user.'
SET @userType = 1
END
ELSE IF @userId = 0 --user does not exists already
BEGIN
IF ((@email IS NULL) OR (LEN(@email)=0)) AND (@userType <> 3)
    BEGIN
		IF (@ownerCompany<>0)
		BEGIN
	        SET @email = (SELECT TOP 1 @loginname + '@' +  UMP.attrVal FROM UMDSProviderProp UMP
	                            INNER JOIN UMDSProviders UM ON UMP.componentNameId =
	                            CASE
WHEN UM.serviceType = 12 THEN @ownerCompany
		                            ELSE UM.id
	                            END
	                            WHERE UM.id=@providerID AND UMP.attrName='Email Domain' AND UMP.modified=0)
		END
		ELSE
		BEGIN
			-- For owner company 0, look into gxglobalparam
SET @email = (SELECT TOP 1  @loginname + '@' + value FROM GxGlobalParam WHERE name = 'sCustomEmailDomain')
			IF @email IS NULL
			BEGIN
				SET @email=''
			END
		END
    END
	IF (LEN(@domainName) > 0)
	BEGIN
		IF EXISTS(SELECT 1 FROM UMUsers WHERE login like @owner + '(Deleted,%' )
		BEGIN
			SET @userid = (SELECT TOP(1) id  FROM UMUsers WHERE login like @owner + '(Deleted,%' ORDER BY id DESC)
			UPDATE UMUsers
			SET login = @owner,
				flags = 1,
				enabled = 1,
				--password = '2ALXqLLXNLLX0LLXcLLXxLL',  -- pwd='admin'
				umDSproviderId=@providerId
			WHERE id = @userid
		END
		ELSE
		BEGIN
			INSERT INTO UMUsers([name]
			,[description]
			,[login]
			,[password]
			,[email]
			,[datePasswordSet]
			,[dateExpires]
			,[policy]
			,[enabled]
			,[flags]
			,[modified]
			,[pVer]
			,[Pager]
			,[lastLogInTime]
			,[credSetTime]
			,[umDSproviderId]
			,[userGuid]
			,[origUserGuid])
			VALUES(@fullName, --name
			@description,
			@owner, -- login
			'2ALXqLLXNLLX0LLXcLLXxLL',-- password
			@email,--email
			0,--datePWDset
			0,--dateExpires
			0,--policy
			1,--enabled
			1,--flags
			0,--modified
			0,--pVer
			0,--Pager
			0,--lastLogInTime
			0,--credSetTime
			@providerId,--umDSproviderId
			@userGUID,--userGUID
			''--origUserGuid
			)
			SET @userid = SCOPE_IDENTITY()
		END
		IF @customDomainUserGroupId <> 0
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM UMUserGroup where userId = @userid AND groupId = @customDomainUserGroupId)
				INSERT INTO UMUserGroup (userId, groupId, flag) VALUES(@userid, @customDomainUserGroupId, 0)
		END
        IF @userSID IS NOT NULL
        BEGIN
            INSERT INTO UMUsersProp(componentNameId, attrName, attrType, attrVal, created, modified)
VALUES (@userid, 'User SID', 1, CAST(@userSID as nvarchar(max)), dbo.GetUnixTime(GETUTCDATE()), 0)
        END
	END
END  -- IF user exist already
ELSE
BEGIN
	DECLARE @userLoginName NVARCHAR(128)=N''
	IF ((@email IS NULL) OR (LEN(@email)=0))
	BEGIN
		SELECT @email=email,@userLoginName=UU.login, @providerId=umDSproviderId FROM UMUsers UU WITH (NOLOCK)
							INNER JOIN UMDSProviders UP with(nolock)
on UP.id=UU.umDSproviderId AND UP.serviceType IN (1,5,11,12)
						WHERE UU.id=@userId
	END
	IF ((@email IS NULL) OR (LEN(@email)=0))
	BEGIN
		SET @pos=CHARINDEX('\',@userLoginName,1)
		IF @pos <> 0
		BEGIN
			SET @loginname= RIGHT(@userLoginName,LEN(@userLoginName)-@pos)
		END
		SET @ownerCompany = dbo.AppGetCompanyForProvider(@providerID)
		SET @email = (SELECT TOP 1 @loginname + '@' +  UMP.attrVal FROM UMDSProviderProp UMP WITH (NOLOCK)
							WHERE UMP.componentNameId=@ownerCompany AND UMP.attrName='Email Domain' AND UMP.modified=0)
	END
	-- MR - https:--engweb.commvault.com/engtools/defect/217113
	-- Don't update any details if user is 1st admin user
IF EXISTS (SELECT 1 FROM UMUsers WITH (NOLOCK) WHERE id=@userId AND flags & 0x040 = 0)
	BEGIN
		-- Enable user in case it is scenario where we overtook deleted user
		UPDATE UMUsers SET enabled=1 where id=@userId and enabled<>1
		-- If user is not marked as valid user, then mark this user as valid
		UPDATE UMUsers SET flags=1 where id=@userId and flags=0
		-- Update Email/UPN if not same
		UPDATE UMUsers SET email=@email where id=@userId and LEN(@email) > 0 AND email<>@email
		-- Update Login if not matching
UPDATE UMUsers SET login=@owner where id=@userId and LEN(@owner) > 0 AND login<>@owner AND (@userType = 3)
		-- Update GUID if not matching
UPDATE UMUsers SET userGuid=@userGUID where id=@userId and LEN(@userGUID) > 0 AND userGuid<>@userGUID AND (@userType = 3)
	END
	SET @errorCode	 =  0
	SET @errorStr = 'User already exists. Nothing to be done.'
END
-- If we have userid and this user is part of some sort of AD and if we have those user group already in CS
-- Then simply update the mapping of those user to existing user group
IF (@userId <> 0)
BEGIN
IF (@userType = 3)
	BEGIN
		INSERT INTO @adGroupsTbl
		SELECT ref.value('@val', 'NVARCHAR(MAX)'), UG.id
			FROM @inXML.nodes('App_CustomDomainUserCreationRequest/adUserInfo/groups') R ( ref )
				INNER JOIN UMGroups UG
					ON UG.name=ref.value('@val', 'NVARCHAR(MAX)')
				INNER JOIN UMUsers UU
					ON UU.id=@userId AND UG.umdsProviderId=UU.umDSproviderId
		INSERT INTO UMUserGroup
		SELECT @userId,GT.groupId,0 FROM @adGroupsTbl GT
		LEFT OUTER JOIN UMUserGroup UUG
			ON GT.groupId=UUG.groupid AND UUG.userid=@userId
		WHERE UUG.groupid IS NULL
	END
	SET @loginname = (SELECT login FROM UMUsers WITH (NOLOCK) where id=@userId)
END
END TRY
BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
	SET @errorCode	 = ERROR_NUMBER()
	SET @errorStr  = 'Procedure [' + ERROR_PROCEDURE() + '] Error Line [' +Convert(varchar(5), ERROR_LINE()) +']. ' +ERROR_MESSAGE()
END CATCH
PROC_EXIT:
IF (@errorStr = '' OR @errorStr IS NULL) AND @errorCode = 0
	SET @errorStr = 'Success'
SET @inXML = ( SELECT @errorCode '@errorCode', @errorStr '@errorString',
@userType as '@userType',
(SELECT @userId as 'userId', @loginname as 'userName' FOR XML PATH ('user'),TYPE)
FOR XML PATH('App_CustomDomainUserResp'),TYPE)
SELECT @inXML
GO

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

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

insert into GXDBVersions values(2, 'AppCreateCustomDomainUsers',  '00000000000000000000', 'AppCreateCustomDomainUsers', '00000000000000000000')
GO

