use [site_fuchs]
go

OPEN SYMMETRIC KEY [fuchs_enc_1] DECRYPTION BY PASSWORD = N'*0&PY_6iyjlKyQ';DECLARE @enc_key uniqueidentifier = key_guid('fuchs_enc_1');

DECLARE @utcnow datetime = GETUTCDATE();

/****** Script for SelectTopNRows command from SSMS  ******/
with acc as (
SELECT TOP (1000) [useraccount_id]
      ,'name' = CAST(DECRYPTBYKEY([name_enc]) as nvarchar(100))
	  ,'firstname' = CAST(DECRYPTBYKEY([firstname_enc]) as nvarchar(100))
	  ,'title' = CAST(DECRYPTBYKEY([title_enc]) as varchar(255))
	  ,'gender' = CAST(DECRYPTBYKEY([gender_enc]) as varchar(1))
      ,'email' = CAST(DECRYPTBYKEY([email_enc]) as varchar(255))
      ,'mobile' = CAST(DECRYPTBYKEY([mobile_enc]) as varchar(50))
      ,'password' = CAST(DECRYPTBYKEY([password_enc]) as nvarchar(20))
	  ,[language]
      ,[UserDisabled]
      ,[DateDisabled]
      ,[UserCreated]
      ,[DateCreated]
      ,[UserModified]
      ,[DateModified]
      ,[UserDeleted]
      ,[DateDeleted]
  FROM [site_fuchs].[dbo].[fis_useraccounts] as a 
)
SELECT * from acc order by [name] ;


SELECT 
      ug.usergroup_id
      ,'name' = CAST(DECRYPTBYKEY(ug.[name_enc])	as nvarchar(255))
      ,m.useraccount_id
FROM [site_fuchs].[dbo].[fis_usergroups] as ug
    join [site_fuchs].[dbo].[fis_usergroups_members] as m on m.usergroup_id = ug.usergroup_id
order by m.useraccount_id, ug.usergroup_id;


--UPDate a
--           SET [email_enc] = ENCRYPTBYKEY(@enc_key,					CAST('n.beckmann@sanitaerfuchs.de' as varchar(255)))
--           ,[name_enc] = ENCRYPTBYKEY(@enc_key,					CAST(N'Beckmann' as nvarchar(100)))
--from [site_fuchs].[dbo].[fis_useraccounts] as a 
--WHERE [useraccount_id] = 'O6C2O';


--UPDate a
--	SET [mobile_enc] = ENCRYPTBYKEY(@enc_key, CAST(  '+49 178 3137898' as varchar(50)))
--from [site_fuchs].[dbo].[fis_useraccounts] as a 
--WHERE [useraccount_id] = 'SCHR4';

--UPDate a
--	SET [userDeleted] = 'J5KL8', [dateDeleted] = @utcnow
--from [site_fuchs].[dbo].[fis_useraccounts] as a 
--WHERE [useraccount_id] = 'GRS0Q';



--DECLARE @RC int
--DECLARE @name nvarchar(100) = 'Winkels'
--DECLARE @firstname nvarchar(100) = 'André'
--DECLARE @title varchar(50) = ''
--DECLARE @gender varchar(1) = 'm'
--DECLARE @email varchar(255) = 'a.winkels@sanitaerfuchs.de'
--DECLARE @mobile varchar(50) = ''
--DECLARE @password nvarchar(20) = 'En2dfB4R'--[dbo].[fn_generatePassword(8,1,0,1)

---- TODO: Set parameter values here.

--EXECUTE @RC = [dbo].[fis_admin_createUserAccount] 
--   @name
--  ,@firstname
--  ,@title
--  ,@gender
--  ,@email
--  ,@mobile
--  ,@password
--  ,@enc_key






 -- INSERT INTO [dbo].[dda_usergroups_members]
 --          ([usergroup_id]
 --          ,[useraccount_id]
 --          ,[UserCreated]
 --          ,[DateCreated]
 --          ,[UserDeleted]
 --          ,[DateDeleted])
 --    SELECT
 --          'ILW0I'
 --          ,[useraccount_id]
 --          ,'GFLU7'
 --          ,@utcnow
 --          ,null
 --          ,null
	--FROM [site_fuchs].[dbo].[fis_useraccounts] as a 
	--WHERE NOT EXISTS(select * from [dbo].[dda_usergroups_members] as m1 where m1.useraccount_id = a.[useraccount_id])
	--	and not useraccount_id in ('GFLU7','4U8LM','8FPIO')


--  SELECT * FROM [site_fuchs].dbo.[fis_admin_getUserAccount]('4U8LM');
--  SELECT * from [dbo].[fis_admin_getUserAccountList]
--(
--	'Y1LU5'
--	, 'Y1LU5'
--	, NULL --@usergroup_id varchar(5)
--	, 1
--);



--INSERT INTO [dbo].[fis_useraccounts]
--           ([useraccount_id]
--           ,[name_enc]
--           ,[firstname_enc]
--           ,[title_enc]
--           ,[gender_enc]
--           ,[email_enc]
--           ,[mobile_enc]
--           ,[password_enc]
--           ,[language]
--           ,[UserDisabled]
--           ,[DateDisabled]
--           ,[UserCreated]
--           ,[DateCreated]
--           ,[UserModified]
--           ,[DateModified]
--           ,[UserDeleted]
--           ,[DateDeleted])
--     SELECT
--           [dbo].[fis_fn_useraccount_id]()
--           ,ENCRYPTBYKEY(@enc_key,					CAST([Name] as nvarchar(100)))
--           ,ENCRYPTBYKEY(@enc_key,					CAST([Vorname] as nvarchar(100)))
--           ,ENCRYPTBYKEY(@enc_key,					CAST(CASE WHEN LOWER([T]) = 'dr.' THEN 'Dr.' ELSE NULL END as varchar(50)))
--           ,ENCRYPTBYKEY(@enc_key,					CAST(CASE WHEN LOWER([A]) ='herr' THEN 'm' ELSE 'f' END as varchar(1)))
--           ,ENCRYPTBYKEY(@enc_key,					CAST([Email] as varchar(255)))
--           ,NULL
--           ,ENCRYPTBYKEY(@enc_key,					CAST([dbo].[ocms_fn_generatePassword] (8, 0, 1, 1)  as nvarchar(20)))
--           ,'de'
--           ,NULL --<UserDisabled, varchar(36),>
--           ,NULL --<DateDisabled, datetime,>
--           ,'GFLU7'
--           ,@utcnow
--           ,'GFLU7'
--           ,@utcnow
--           ,NULL --<UserDeleted, varchar(36),>
--           ,NULL --<DateDeleted, datetime,>)
--  FROM [master].[dbo].[20200430__Y2020_TN_Email-Aschriften_2];





--		EXECUTE  [dbo].[fis_admin_setUserGroup] 
--		   'GFLU7'
--		  ,NULL
--		  ,'Studienjahr 2020-2022'
--		  ,@enc_key;


--UPDATE dbo.fis_useraccounts SET email_enc = ENCRYPTBYKEY(@enc_key,					CAST('webmaster@duesseldorf-dental-academy.de' as varchar(255))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET name_enc = ENCRYPTBYKEY(@enc_key,					CAST('Ott' as nvarchar(100))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET firstname_enc = ENCRYPTBYKEY(@enc_key,					CAST('Stefan' as nvarchar(100))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET title_enc = ENCRYPTBYKEY(@enc_key,					CAST('Dr.' as varchar(50))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET gender_enc = ENCRYPTBYKEY(@enc_key,					CAST('m' as varchar(1))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET mobile_enc = ENCRYPTBYKEY(@enc_key,					CAST('+49 123 45678901' as varchar(50))) WHERE useraccount_id = '2OGN4'
--UPDATE dbo.fis_useraccounts SET password_enc = ENCRYPTBYKEY(@enc_key,					CAST('+49 123 45678901' as nvarchar(20))) WHERE useraccount_id = 'J5KL8'

--SELECT * FROM [dbo].[fis_admin_getUserAccount_byemail] (
--   'davidsonntag@hush.com', 1)



CLOSE SYMMETRIC KEY [fuchs_enc_1]