Stefan
10ecdfa2e4
Adds the SQL Server Data Tools project for the fuchs_fds database — tables, table types, functions and stored procedures that the backend calls (e.g. fds__getInvoice, fds__merge_bankingtransactions, fds__tt__bankingtransactions, fds__admin_getReportCatalog, fis_* auth). Build/model caches (bin, obj, *.dbmdl, *.jfm, *.user) are git-ignored. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
106 lines
3.3 KiB
Transact-SQL
106 lines
3.3 KiB
Transact-SQL
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date, ,>
|
|
-- Description: <Description, ,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fis_admin_setUserAccount]
|
|
@authaccount varchar(5)
|
|
,@useraccount_id varchar(5)
|
|
,@name nvarchar(100)
|
|
,@firstname nvarchar(100)
|
|
,@title varchar(50)
|
|
,@gender varchar(1)
|
|
,@email varchar(255)
|
|
,@mobile varchar(50)
|
|
,@password nvarchar(20)
|
|
,@enc_key uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @utcnow datetime = GETUTCDATE();
|
|
DECLARE @auth smallint = [dbo].[fis_admin_getUserAuth](@authaccount);
|
|
|
|
DECLARE @createdID as TABLE ([id] varchar(5));
|
|
|
|
IF @auth > 1
|
|
BEGIN
|
|
|
|
IF EXISTS (SELECT * FROM [dbo].[fis_useraccounts] WHERE [DateDeleted] is null AND [useraccount_id] <> ISNULL(@useraccount_id, '') AND ISNULL(@email,'') <> '' AND LTRIM(RTRIM(DECRYPTBYKEY([email_enc]))) like LTRIM(RTRIM(@email)))
|
|
THROW 60001, 'email is already assigned to another account', 1;
|
|
|
|
|
|
MERGE [dbo].[fis_useraccounts] as TARGET
|
|
USING (VALUES( ISNULL(@useraccount_id, [dbo].[fis_fn_useraccount_id] ())
|
|
,ENCRYPTBYKEY(@enc_key, LTRIM(RTRIM(@name)))
|
|
,ENCRYPTBYKEY(@enc_key, LTRIM(RTRIM(@firstname)))
|
|
,ENCRYPTBYKEY(@enc_key, LTRIM(RTRIM(@title)))
|
|
,ENCRYPTBYKEY(@enc_key, LOWER(@gender))
|
|
,ENCRYPTBYKEY(@enc_key, LTRIM(RTRIM(LOWER(@email))))
|
|
,ENCRYPTBYKEY(@enc_key, LTRIM(RTRIM(@mobile)))
|
|
)) as SOURCE([useraccount_id]
|
|
,[name_enc]
|
|
,[firstname_enc]
|
|
,[title_enc]
|
|
,[gender_enc]
|
|
,[email_enc]
|
|
,[mobile_enc])
|
|
ON TARGET.[useraccount_id] = SOURCE.[useraccount_id]
|
|
WHEN MATCHED THEN
|
|
UPDATE SET
|
|
TARGET.[name_enc] = ISNULL(SOURCE.[name_enc],TARGET.[name_enc])
|
|
,TARGET.[firstname_enc] = ISNULL(SOURCE.[firstname_enc],TARGET.[firstname_enc])
|
|
,TARGET.[title_enc] = SOURCE.[title_enc]
|
|
,TARGET.[gender_enc] = SOURCE.[gender_enc]
|
|
,TARGET.[email_enc] = ISNULL(SOURCE.[email_enc],TARGET.[email_enc])
|
|
,TARGET.[mobile_enc] = SOURCE.[mobile_enc]
|
|
,TARGET.[UserModified] = @authaccount
|
|
,TARGET.[DateModified] = @utcnow
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT
|
|
([useraccount_id]
|
|
,[name_enc]
|
|
,[firstname_enc]
|
|
,[title_enc]
|
|
,[gender_enc]
|
|
,[email_enc]
|
|
,[mobile_enc]
|
|
,[password_enc]
|
|
,[UserDisabled]
|
|
,[DateDisabled]
|
|
,[UserCreated]
|
|
,[DateCreated]
|
|
,[UserModified]
|
|
,[DateModified]
|
|
,[UserDeleted]
|
|
,[DateDeleted])
|
|
VALUES
|
|
(SOURCE.[useraccount_id]
|
|
,SOURCE.[name_enc]
|
|
,SOURCE.[firstname_enc]
|
|
,SOURCE.[title_enc]
|
|
,SOURCE.[gender_enc]
|
|
,SOURCE.[email_enc]
|
|
,SOURCE.[mobile_enc]
|
|
,CASE WHEN @password is null then ENCRYPTBYKEY(@enc_key, CAST([dbo].[ocms_fn_generatePassword] (8, 0, 1, 1) AS nvarchar(20))) ELSE ENCRYPTBYKEY(@enc_key, @password) END
|
|
,NULL --[UserDisabled]
|
|
,NULL --[DateDisabled]
|
|
,@authaccount
|
|
,@utcnow
|
|
,@authaccount
|
|
,@utcnow
|
|
,NULL
|
|
,NULL)
|
|
OUTPUT inserted.[useraccount_id]
|
|
INTO @createdID;
|
|
|
|
|
|
SELECT TOP(1) * FROM [dbo].[fis_admin_getUserAccountList](@authaccount, @useraccount_id, null, null) ual
|
|
JOIN @createdID as id on ual.[useraccount_id] = id.[id];
|
|
|
|
END
|
|
|
|
END |