SET ANSI_NULLS ON
SET ANSI_PADDING ON;
SET QUOTED_IDENTIFIER ON
go


--Part 1: tables
IF OBJECT_ID('FAF.FA_MROS_VERSION_SUPPORT_DEFAULT', 'U') IS NULL
begin
	create table FAF.FA_MROS_VERSION_SUPPORT_DEFAULT (
		[userbk_nr] numeric(4,0) NOT NULL,
		[name] varchar(50) NOT NULL,
		[value] varchar(100) NULL,
		[length] int NULL,
		CONSTRAINT PK_VERSION_SUPPORT_DEFAULT PRIMARY KEY ([userbk_nr],[name]));
end;
go

IF OBJECT_ID('FAF.FA_MROS_VERSION_SUPPORT_SPECIAL', 'U') IS NULL
begin
	create table FAF.FA_MROS_VERSION_SUPPORT_SPECIAL (
		[userbk_nr] numeric(4,0) NOT NULL,
		[ID] bigint NOT NULL,
		[value1] varchar(100) NULL,
		[value2] varchar(100) NULL,
		[comments] varchar(4000) NULL,
		CONSTRAINT PK_VERSION_SUPPORT_SPECIAL PRIMARY KEY ([userbk_nr],[ID]));
end;
go

--remove columns
if exists (select * from sys.columns where [name]='scheme' and object_id = object_id('[FAF].[FA_MROS_VERSION_SUPPORT_DEFAULT]'))
	alter table [FAF].[FA_MROS_VERSION_SUPPORT_DEFAULT] drop column [scheme];
if exists (select * from sys.columns where [name]='version' and object_id = object_id('[FAF].[FA_MROS_VERSION_SUPPORT_DEFAULT]'))
	alter table [FAF].[FA_MROS_VERSION_SUPPORT_DEFAULT] drop column [version];
if exists (select * from sys.columns where [name]='scheme' and object_id = object_id('[FAF].[FA_MROS_VERSION_SUPPORT_SPECIAL]'))
	alter table [FAF].[FA_MROS_VERSION_SUPPORT_SPECIAL] drop column [scheme];
if exists (select * from sys.columns where [name]='version' and object_id = object_id('[FAF].[FA_MROS_VERSION_SUPPORT_SPECIAL]'))
	alter table [FAF].[FA_MROS_VERSION_SUPPORT_SPECIAL] drop column [version];


--Part 2: functions
--technical function to process varchar variables
IF OBJECT_ID('[FAF].[faf_MROS_char_process]', 'FN') IS NOT NULL
	DROP function [FAF].[faf_MROS_char_process]; 
go
IF OBJECT_ID('[FAF].[GoAML_char_process]', 'FN') IS NOT NULL
	DROP function [FAF].[GoAML_char_process]; 
go
create function FAF.GoAML_char_process (@txt varchar(max),@mv varchar(max),@size int)
returns varchar(max)
as
begin
	declare @s varchar(max);
	set @s = replace(rtrim(ltrim(replace(replace(replace(@txt,char(10),' '),char(9),' '),char(13),' '))),'  ',' ');
	if @s is null or @s='' 
		set @s = @mv;
	if @s is not null and @size>0
		set @s = left(@s,@size);

	return(@s)
end;
go

--version  of technical function without replacement
IF OBJECT_ID('[FAF].[faf_MROS_char_process_]', 'FN') IS NOT NULL
	DROP function [FAF].[faf_MROS_char_process_]; 
go
IF OBJECT_ID('[FAF].[GoAML_char_process_WR]', 'FN') IS NOT NULL
	DROP function [FAF].[GoAML_char_process_WR]; 
go
create function FAF.GoAML_char_process_WR (@txt varchar(max),@mv varchar(max),@size int)
returns varchar(max)								 
as
begin
	declare @s varchar(max);					   
	set @s = rtrim(ltrim(@txt));
	if @s is null or @s='' 
		set @s = @mv;
	if @s is not null and @size>0
		set @s = left(@s,@size);

	return(@s)
end;
go						 

--Provides default values from the table VERSION_SUPPORT_DEFAULT
IF OBJECT_ID('[FAF].[faf_MROS_GetMROSParamV]', 'FN') IS NOT NULL
	DROP function [FAF].[faf_MROS_GetMROSParamV]; 
go
IF OBJECT_ID('[FAF].[GoAML_get_paramV]', 'FN') IS NOT NULL
	DROP function [FAF].[GoAML_get_paramV]; 
go
create function FAF.GoAML_get_paramV (@F_BANK_ID int,@name varchar(50))
returns varchar(100)								 
as
begin
	declare @s varchar(100);					   
	set @s = (select top 1 [value] from FAF.FA_MROS_VERSION_SUPPORT_DEFAULT where [userbk_nr]=@F_BANK_ID and [name] = @name);

	return(@s)
end;
go	

--Provides fields length from the table VERSION_SUPPORT_DEFAULT
IF OBJECT_ID('[FAF].[faf_MROS_GetMROSParamL]', 'FN') IS NOT NULL
	DROP function [FAF].[faf_MROS_GetMROSParamL]; 
go
IF OBJECT_ID('[FAF].[GoAML_get_paramL]', 'FN') IS NOT NULL
	DROP function [FAF].[GoAML_get_paramL]; 
go
create function FAF.GoAML_get_paramL (@F_BANK_ID int,@name varchar(50))
returns int								 
as
begin
	declare @s int;
	set @s = (select top 1 [length] from FAF.FA_MROS_VERSION_SUPPORT_DEFAULT where [userbk_nr]=@F_BANK_ID and [name] = @name);
	if @s is null 
		set @s = 0;

	return(@s)
end;
go	

--Works with special cases from the table VERSION_SUPPORT_SPECIAL
IF OBJECT_ID('[FAF].[faf_MROS_GetMROSParamS]', 'FN') IS NOT NULL
	DROP function [FAF].[faf_MROS_GetMROSParamS]; 
go
IF OBJECT_ID('[FAF].[GoAML_get_paramS]', 'FN') IS NOT NULL
	DROP function [FAF].[GoAML_get_paramS]; 
go
create function FAF.GoAML_get_paramS (@F_BANK_ID int,@ID bigint,@pos int)
returns varchar(100)
as
begin
	declare @s varchar(100);
	set @s = 
		(select top 1
			case @pos
				when 1 then [value1]  
				when 2 then [value2] 
				else NULL
			end
		from FAF.FA_MROS_VERSION_SUPPORT_SPECIAL where [userbk_nr]=@F_BANK_ID and [ID] = @ID);

	return(@s)
end;
go

--country from bank_id
if object_id('[FAF].[GoAML_get_country]', 'FN') is not NULL
	drop function [FAF].[GoAML_get_country]; 
go
create function FAF.GoAML_get_country (@F_BANK_ID int)
returns varchar(10)
as
begin
	declare @cntr varchar(10);
	set @cntr = (SELECT TOP 1 [value1] FROM FAF.FA_MROS_VERSION_SUPPORT_SPECIAL where [userbk_nr]=@F_BANK_ID and [ID] = 0);

	return(@cntr)
end;
go

--version of xsd
if object_id('[FAF].[GetMROSVersion]', 'FN') is not NULL
	drop function [FAF].[GetMROSVersion]; 
go
create function FAF.GetMROSVersion ()
returns varchar(4000)
as
begin
	declare @s varchar(4000);
	set @s = 'This function is obsolete. Use GoAML_get_schema_version function';

	return(@s)
end;
go
if object_id('[FAF].[GoAML_get_schema_version]', 'FN') is not NULL
	drop function [FAF].[GoAML_get_schema_version]; 
go
create function FAF.GoAML_get_schema_version (@F_BANK_ID int)
returns varchar(4000)
as
begin
	declare @s varchar(4000);
	set @s = (SELECT TOP 1 [value2] FROM FAF.FA_MROS_VERSION_SUPPORT_SPECIAL where [userbk_nr]=@F_BANK_ID and [ID] = 0);

	return(@s)
end;
go


--version
if object_id('[FAF].[get_version_MROS]', 'FN') is not NULL
	drop function [FAF].[get_version_MROS]; 
go
create function FAF.get_version_MROS ()
returns varchar(4000)
as
begin
	declare @s varchar(4000);
	set @s = 'This function is obsolete. Use GoAML_get_package_version function';

	return(@s)
end;
go


if object_id('[FAF].[GoAML_get_package_version]', 'FN') is not NULL
	drop function [FAF].[GoAML_get_package_version]; 
go
create function FAF.GoAML_get_package_version ()
returns varchar(4000)
as
begin
	declare @s varchar(4000);
	set @s = 'Version 152 on 17.03.2025';

	return(@s)
end;
go

