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 = 13

if @ComponentName = 'Request'
begin
	set @sql = @sql + N'	[_RebuildWorkflow] [tinyint] NOT NULL default(0),
'
	set @ColumnCounter = @ColumnCounter + 1
end


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 ASPXPage = 'CRMOpportunityCard.aspx')
begin
	insert into CSEasyVerifyPages(ID, Caption, CaptionID, ASPXPage, IsMaintScreen, TableName, KeyColumn, KeyInputField, BusinessComponent, BCDataSource, BCErrorMessages, CardID, SysCreated)
	values('C12C2FAB-5071-4703-971A-BEB5513035DD', 'Opportunity', 34831, 'CRMOpportunityCard.aspx', 0, 'Opportunities', 'ID', 'ID', 'Opportunities', null, 'ErrMandatory', 'Card', getdate())
end
go

if not exists(select * from CSEasyVerifyPages where ASPXPage = 'CRMOpportunityMaint.aspx')
begin
	insert into CSEasyVerifyPages(ID, Caption, CaptionID, ASPXPage, IsMaintScreen, TableName, KeyColumn, KeyInputField, BusinessComponent, BCDataSource, BCErrorMessages, CardID, SysCreated)
	values('C12C2FAB-5071-4703-971A-BEB5513035DE', 'Opportunity', 34831, 'CRMOpportunityMaint.aspx', 1, 'Opportunities', 'ID', 'ID', 'Opportunities', 'bc', null, 'Card', getdate())
end
go

if not exists(select * from CSEasyVerifyPages where ASPXPage = 'LogItemNumber.aspx')
begin
	insert into CSEasyVerifyPages(ID, Caption, CaptionID, ASPXPage, IsMaintScreen, TableName, KeyColumn, KeyInputField, BusinessComponent, BCDataSource, BCErrorMessages, CardID, SysCreated)
	values('E12E2FAB-5071-4703-971A-BEB5513035EE', 'Serial number', 6057, 'LogItemNumber.aspx', 1, 'ItemNumbers', 'ID', 'ID', 'ItemNumber', 'bc', null, 'Card', getdate())
end
go

if not exists(select * from CSEasyVerifyPropertiesChanged where BusinessComponent = 'Opportunities')
begin
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('55EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'Description', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('56EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'Owner', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('57EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'Probability', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('58EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'Project', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('59EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'PlannedAmountFC', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('60EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'FollowUpDate', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('61EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'ContactID', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('62EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'DealStatus', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('63EE62F1-88FF-4790-8225-2F29D5C4DB39', 'Opportunities', 'AccountID', getdate())
end
go

if not exists(select * from CSEasyVerifyPropertiesChanged where BusinessComponent = 'ItemNumbers')
begin
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('55FF62F1-88FF-4791-8225-2F29D5C4DB39', 'ItemNumbers', 'NumberInBatch', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('56FF62F1-88FF-5791-8225-2F29D5C4DB39', 'ItemNumbers', 'Description', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('57FF62F1-88FF-6791-8225-2F29D5C4DB39', 'ItemNumbers', 'DateStart', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('58FF62F1-88FF-7791-8225-2F29D5C4DB39', 'ItemNumbers', 'DateEnd', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('59FF62F1-88FF-8791-8225-2F29D5C4DB39', 'ItemNumbers', 'AccountCode', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('60FF62F1-88FF-9791-8225-2F29D5C4DB39', 'ItemNumbers', 'Blocked', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('61FF62F1-88FF-0791-8225-2F29D5C4DB39', 'ItemNumbers', 'CountryCode', getdate())
	insert into CSEasyVerifyPropertiesChanged(ID, BusinessComponent, PropName, SysCreated) values('62FF62F1-88FF-1791-8225-2F29D5C4DB39', 'ItemNumbers', 'OwnerCompany', getdate())
end
go


if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'CSEasyVerifyVerifications' and COLUMN_NAME = 'Connection')
begin
	alter table [dbo].[CSEasyVerifyVerifications]
		add [Connection] nvarchar(30) NOT NULL default('Synergy')
end
go
