delete from DDTests where Tablename = 'CSEasyVerifyVerifications' and FieldName = 'Action'
go

if not exists(select * from DDTests where Tablename = 'CSEasyVerifyVerifications' and FieldName = 'Action')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, options, SeqNr) values('M', 'CSEasyVerifyVerifications', 'Action', 'M', 'Message', 1778, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, options, SeqNr) values('O', 'CSEasyVerifyVerifications', 'Action', 'O', 'BPA Platform (Orbis)', 0, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, options, SeqNr) values('S', 'CSEasyVerifyVerifications', 'Action', 'S', 'Stored Procedure', 0, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, options, SeqNr) values('T', 'CSEasyVerifyVerifications', 'Action', 'T', 'Tabel uitvoeren', 0, '', 3)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, options, SeqNr) values('A', 'CSEasyVerifyVerifications', 'Action', 'A', 'Access denied.', 10008, '', 4)
end
go

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'CSEasyVerifyVerifications' and COLUMN_NAME = 'ExecuteTable')
begin
	alter table CSEasyVerifyVerifications
		add [ExecuteTable] [nvarchar](128) NULL
end
go


if object_id('spCSEasyVerify_CreateTable') is not null
	drop procedure dbo.spCSEasyVerify_CreateTable
go

create procedure dbo.spCSEasyVerify_CreateTable(@ComponentName nvarchar(30))
as
begin

if @ComponentName is null or @ComponentName = ''
	return

declare @CompID uniqueidentifier, @MainDataSource uniqueidentifier
set @CompID = (select CompID from EbcComponents where ComponentName = @ComponentName and ComponentTypeId = 0) -- Business Components

if @CompID is null
	return

set @MainDataSource = (select CompRelationId from EbcComponentRelations where CompId1 = @CompID and CompRelationTypeId = 0) -- Main datasource
if @MainDataSource is null
	return

declare @TableName nvarchar(128)
set @TableName = '_CSEasy_' + ltrim(rtrim(@ComponentName))

declare @sql nvarchar(max), @sqlcolumn nvarchar(128)
set @sql = N'if exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = ''{{TABLE_NAME}}'')
	drop table {{TABLE_NAME}};


CREATE TABLE [dbo].[{{TABLE_NAME}}](
	[_ID] [int] IDENTITY(1,1) NOT NULL,
	[_BCAction] [int] NOT NULL default(0),
	[_BCComponent] [varchar](128) NOT NULL default(''{{COMPONENT_NAME}}''),
	[_Status] [int] NOT NULL default(0),
	[_AddNewProps] [varchar](max) NULL,
	[_SkipNulls] [varchar](max) NULL,
	[_SkipAllNulls] [tinyint] NOT NULL default(1),
	[_UpdateColumns] [varchar](max) NULL,
	[_SortOrder] [int] NOT NULL default(0),
	[_SysCreated] [datetime] NOT NULL default(getdate()),
	[_SysModified] [datetime] NOT NULL default(getdate()),
	[_Session] [uniqueidentifier] NOT NULL,
	[_ErrorMessage] [nvarchar](max) NULL,
'

declare @PropName nvarchar(40), @BasicTypeId int, @Precision int, @Scale int, @ColumnCounter int
set @ColumnCounter = 11

declare curProps cursor for
	select distinct rtrim(p.PropName), bt.BasicTypeId, pt.PropPrecision, pt.PropScale
	from EbcPropRelations pr
		inner join EbcProps p on p.PropId = pr.PropId
		inner join EbcPropTypes pt on pt.PropTypeId = p.PropTypeId
		inner join EbcBasicTypes bt on bt.BasicTypeId = pt.BasicTypeId
	where 
		pr.CompRelationId = @MainDataSource
		and pr.PropRelationTypeId = 0 -- Source
	order by 1

open curProps
fetch next from curProps into @PropName, @BasicTypeId, @Precision, @Scale
while @@FETCH_STATUS = 0
begin
	if @ColumnCounter < 1024
	begin
		set @sqlcolumn = N'[' + @PropName + N'] '

		if @BasicTypeId = 0 -- String
			set @sqlcolumn = @sqlcolumn + '[nvarchar](' + case when @Precision is null or @Precision = 0 then 'max' else cast(@Precision as nvarchar) end + ')'
		else if @BasicTypeId = 1 -- number
			set @sqlcolumn = @sqlcolumn + case when @Scale = 0 then 'int' else 'float' end
		else if @BasicTypeId = 2 -- datetime
			set @sqlcolumn = @sqlcolumn + '[datetime]'
		else if @BasicTypeId = 3 -- binary
			set @sqlcolumn = @sqlcolumn + '[varbinary](max)'
		else if @BasicTypeId = 4 -- guid
			set @sqlcolumn = @sqlcolumn + '[uniqueidentifier]'
		else if @BasicTypeId = 5 -- percentage
			set @sqlcolumn = @sqlcolumn + '[float]'
		else if @BasicTypeId = 6 -- boolean
			set @sqlcolumn = @sqlcolumn + '[tinyint]'
		else if @BasicTypeId = 7 -- seconds
			set @sqlcolumn = @sqlcolumn + '[int]' -- wordt nergens gebruikt door Exact...
		else if @BasicTypeId = 8 -- picture
			set @sqlcolumn = @sqlcolumn + '[varbinary](max)'
		else if @BasicTypeId = 9 -- human id
			set @sqlcolumn = @sqlcolumn + '[int]'

		set @sqlcolumn = @sqlcolumn + ' NULL,'

		set @sql = @sql + char(13) + char(10) + N'    ' + @sqlcolumn

		set @ColumnCounter = @ColumnCounter + 1
	end

	fetch next from curProps into @PropName, @BasicTypeId, @Precision, @Scale
end
close curProps
deallocate curProps

set @sql = @sql + N'
	CONSTRAINT [PK_{{TABLE_NAME}}] PRIMARY KEY CLUSTERED 
	(
		[_ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
;

CREATE NONCLUSTERED INDEX [IX_{{TABLE_NAME}}] ON [dbo].[{{TABLE_NAME}}]
(
	[_Session] ASC,
	[_Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
;
'


set @sql = replace(@sql, '{{TABLE_NAME}}', @TableName)
set @sql = replace(@sql, '{{COMPONENT_NAME}}', @ComponentName)

exec sp_executesql @sql

select @TableName as TableName, @ComponentName as ComponentName

end
go


if not exists(select * from CSEasyVerifyPages where BusinessComponent = 'DivisionDebtor')
begin
	insert into [dbo].[CSEasyVerifyPages]([ID], [Caption], [CaptionID], [ASPXPage], [IsMaintScreen], [TableName], [KeyColumn], [KeyInputField], [BusinessComponent], [BCDataSource], [BCErrorMessages], [CardID], [SysCreated])
		select '2C060317-6B87-4412-89A0-92AA1BFF2207' as [ID], N'Debtor Code' as [Caption], 1868 as [CaptionID], N'CRMDivDebtor.aspx' as [ASPXPage], 0 as [IsMaintScreen], N'DivisionDebtors' as [TableName], N'DivisionDebtorID' as [KeyColumn], N'DivisionDebtorID' as [KeyInputField], N'DivisionDebtor' as [BusinessComponent], NULL as [BCDataSource], N'ErrorMsg' as [BCErrorMessages], N'Card' as [CardID], getdate() as [SysCreated]
		union
		select '3C060317-6B87-4412-89A0-92AA1BFF2207' as [ID], N'Debtor Code' as [Caption], 1868 as [CaptionID], N'CRMDivDebtorMaint.aspx' as [ASPXPage], 1 as [IsMaintScreen], N'DivisionDebtors' as [TableName], N'DivisionDebtorID' as [KeyColumn], N'DivisionDebtorID' as [KeyInputField], N'DivisionDebtor' as [BusinessComponent], N'bc' as [BCDataSource], N'ErrorMsg' as [BCErrorMessages], N'Card' as [CardID], getdate() as [SysCreated]
end
go


if not exists(select * from CSEasyVerifyPropertiesChanged where BusinessComponent = 'DivisionDebtor')
begin
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('4C060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionDebtor', 'Division', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('5C060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionDebtor', 'DebCode', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('6C060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionDebtor', 'MainDebtor', getdate())
end
go


if not exists(select * from CSEasyVerifyPages where BusinessComponent = 'DivisionCreditor')
begin
	insert into [dbo].[CSEasyVerifyPages]([ID], [Caption], [CaptionID], [ASPXPage], [IsMaintScreen], [TableName], [KeyColumn], [KeyInputField], [BusinessComponent], [BCDataSource], [BCErrorMessages], [CardID], [SysCreated])
		select '2D060317-6B87-4412-89A0-92AA1BFF2207' as [ID], N'Creditor code' as [Caption], 1869 as [CaptionID], N'CRMDivCreditor.aspx' as [ASPXPage], 0 as [IsMaintScreen], N'DivisionCreditors' as [TableName], N'DivisionCreditorID' as [KeyColumn], N'DivisionCreditorID' as [KeyInputField], N'DivisionCreditor' as [BusinessComponent], NULL as [BCDataSource], N'ErrorMsg' as [BCErrorMessages], N'Card' as [CardID], getdate() as [SysCreated]
		union
		select '3D060317-6B87-4412-89A0-92AA1BFF2207' as [ID], N'Creditor code' as [Caption], 1869 as [CaptionID], N'CRMDivCreditorMaint.aspx' as [ASPXPage], 1 as [IsMaintScreen], N'DivisionCreditors' as [TableName], N'DivisionCreditorID' as [KeyColumn], N'DivisionCreditorID' as [KeyInputField], N'DivisionCreditor' as [BusinessComponent], N'bc' as [BCDataSource], N'ErrorMsg' as [BCErrorMessages], N'Card' as [CardID], getdate() as [SysCreated]
end
go


if not exists(select * from CSEasyVerifyPropertiesChanged where BusinessComponent = 'DivisionCreditor')
begin
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('4D060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionCreditor', 'Division', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('5D060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionCreditor', 'CrdCode', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('6D060317-6B87-4412-89A0-92AA1BFF2207', 'DivisionCreditor', 'MainCreditor', getdate())
end
go
