USE [site_fuchs]
GO
/****** Object:  UserDefinedFunction [dbo].[ocms_fn_generatePassword]    Script Date: 02.12.2020 21:05:05 ******/
DROP FUNCTION [dbo].[ocms_fn_generatePassword]
GO
/****** Object:  UserDefinedFunction [dbo].[ocms_fn_generatePassword]    Script Date: 02.12.2020 21:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[ocms_fn_generatePassword] (
	@length int
  , @specialChars bit = 1
  , @nums bit = 1
  , @lowerCase bit = 1
)
RETURNS varchar(255)
AS
BEGIN
  DECLARE @randInt int;
  DECLARE @NewCharacter varchar(1); 
  DECLARE @NewPassword TABLE([typ] char(1), [char] char(1), [order] int); 

  SET @length = CASE WHEN @length > 255 THEN 255 ELSE @length END;
  
  DECLARE @cnt int = 0, @position as int;
  --random characters
  WHILE (@cnt < @length)
  BEGIN
    SELECT @randInt = [dbo].[ocms_fn_randbetween](33,122)
	
    IF (CASE WHEN (@randInt>=33 AND @randInt<=47) AND @specialChars = 1 THEN 1
					 WHEN (@randInt>=48 AND @randInt<=57) AND @nums = 1 THEN 1
					 WHEN (@randInt>=58 AND @randInt<=64) AND @specialChars = 1 THEN 1
					 WHEN (@randInt>=65 AND @randInt<=90) THEN 1
					 WHEN (@randInt>=91 AND @randInt<=93) AND @specialChars = 1 THEN 1
					 WHEN (@randInt>=97 AND @randInt<=122) AND @lowerCase = 1 THEN 1
					 ELSE 0
				END) = 1
    BEGIN
		SET @cnt = @cnt + 1;

        INSERT INTO @NewPassword
		  VALUES (CASE WHEN (@randInt>=33 AND @randInt<=47) THEN 's'
					 WHEN (@randInt>=48 AND @randInt<=57) THEN 'n'
					 WHEN (@randInt>=58 AND @randInt<=64) THEN 's'
					 WHEN (@randInt>=65 AND @randInt<=90) THEN 'u'
					 WHEN (@randInt>=91 AND @randInt<=93) THEN 's'
					 WHEN (@randInt>=97 AND @randInt<=122) THEN 'l'
				END,
				CHAR(@randInt), @cnt);
    END
  END

  DECLARE @min_n int = CASE WHEN @length > 4 AND @length < 11 THEN 1 ELSE FLOOR(@length * 0.1) END;
  DECLARE @min_s int = FLOOR(@length * 0.1);
  DECLARE @min_l int = FLOOR(@length * 0.4);
  DECLARE @min_u int = FLOOR(@length * 0.4);

  WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'n') < @min_n AND @length > 0 AND @nums = 1
	BEGIN
		SET @position = [dbo].[ocms_fn_randbetween](1,@length);
		UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](48,57)), [typ] = 'n' WHERE [order] = @position;
	END

  WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'l') < @min_l AND @length > 0 AND @lowerCase = 1
	BEGIN
		SET @position = [dbo].[ocms_fn_randbetween](1,@length);
		UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](97,122)), [typ] = 'l' WHERE [order] = @position;
	END
	
  WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'u') < @min_n AND @length > 0
	BEGIN
		SET @position = [dbo].[ocms_fn_randbetween](1,@length);
		UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](65,90)), [typ] = 'u' WHERE [order] = @position;
	END
  
  WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 's') < @min_s AND @length > 0 AND @specialChars = 1
	BEGIN
		SET @position = [dbo].[ocms_fn_randbetween](1,@length);
		DECLARE @nc char(1), @nt char(1);
		select @randInt=[dbo].[ocms_fn_randbetween](33,122)
			
		UPDATE @NewPassword SET [char] = CHAR(@randint), [typ] = 's' WHERE [order] = @position AND (CASE WHEN (@randInt>=33 AND @randInt<=47) THEN 's'
																										 WHEN (@randInt>=48 AND @randInt<=57) THEN 'n'
																										 WHEN (@randInt>=58 AND @randInt<=64) THEN 's'
																										 WHEN (@randInt>=65 AND @randInt<=90) THEN 'u'
																										 WHEN (@randInt>=91 AND @randInt<=93) THEN 's'
																										 WHEN (@randInt>=97 AND @randInt<=122) THEN 'l'
																									END) = 's';
	END



  RETURN  (Cast((SELECT [CHAR] as [text()] FROM @NewPassword FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') as varchar(255)));
END;
GO
ALTER AUTHORIZATION ON [dbo].[ocms_fn_generatePassword] TO  SCHEMA OWNER 
GO