-- script 3

-- temporary table tmp_Process
-- temporary table tmp_ProcessElements

-- table cf_Department
-- table cf_Process
-- table cf_DepartmentAndProcessSorter
-- table cf_FormElement
-- table cf_FormElementPropertyValue

-- departments

delete from [dbo].[cf_Department];
go

dbcc checkident ('dbo.cf_Department', reseed, 0);
go

insert into [dbo].[cf_Department] ([parentId], [name], [added], [changed])
select distinct 0, [departmentName], getdate(), getdate()
from [dbo].[tmp_Process]
where [departmentName] not in ('', 'none');
go

insert into [dbo].[cf_Department] ([parentId], [name], [added], [changed])
select distinct [d].[id], [t].[subDepartmentName], getdate(), getdate()
from [dbo].[cf_Department] as [d]
join [dbo].[tmp_Process] as [t] on [t].[departmentName] = [d].[name]
where [t].[subDepartmentName] not in ('', 'none');
go

update [dbo].[cf_Department] set [sorter] = [id];
go

-- processes

delete from [dbo].[cf_Process];
go

dbcc checkident ('dbo.cf_Process', reseed, 0);
go

insert into [dbo].[cf_Process] (
  [departmentId],
  [name],
  [isMedia],
  [height],
  [new],
  [added],
  [changed]
)
select
  [d].[id],
  [t].[processName],
  [t].[isMedia],
  max([t].[height]),
  'n',
  getdate(),
  getdate()
from [dbo].[tmp_Process] as [t]
join [dbo].[cf_Department] as [d]
  on [d].[name] = (case [t].[subDepartmentName] when 'none' then [t].[departmentName] else [t].[subDepartmentName] end)
where [t].[processName] not in ('', 'none')
group by [d].[id], [t].[processName], [t].[isMedia];
go

delete from [dbo].[cf_DepartmentAndProcessSorter];
go

dbcc checkident ('dbo.cf_DepartmentAndProcessSorter', reseed, 0);
go

if object_id('tempdb.dbo.#tmpDepartmentProcessSorter','U') is not null
  drop table [#tmpDepartmentProcessSorter];
go

create table [#tmpDepartmentProcessSorter] (
  [ident] int identity,
  [depId] int not null,
  [subDepId] int null,
  [procId] int null,
  [depSorter] int not null,
  [subDepSorter] int not null,
  [procSorter] int not null
);
go

insert into [#tmpDepartmentProcessSorter] (
  [depId],
  [subDepId],
  [procId],
  [depSorter],
  [subDepSorter],
  [procSorter]
)
select
  isnull([d].[id], [sd].[id]) as [depId],
  [sd].[id] as [subDepId],
  [p].[id] as [procId],
  0,
  0,
  0
from [dbo].[cf_Process] as [p]
join [dbo].[cf_Department] as [sd] on [p].[departmentId] = [sd].[id]
left join [dbo].[cf_Department] as [d] on [sd].[parentId] = [d].[id];
go

update [#tmpDepartmentProcessSorter] set [depSorter] = ([ident] - 1) * 3 + 1;
go

update [#tmpDepartmentProcessSorter] set [subDepSorter] = [depSorter] + 1;
go

update [#tmpDepartmentProcessSorter] set [procSorter] = [depSorter] + 2;
go

insert into [dbo].[cf_DepartmentAndProcessSorter] ([departmentId], [processId], [sorter])
  select distinct [depId], null, min([depSorter]) as [sorter]
  from [#tmpDepartmentProcessSorter]
  where [depId] = [subDepId]
  group by [depId]
union all
  select distinct [subDepId], null, min([subDepSorter]) as [sorter]
  from [#tmpDepartmentProcessSorter]
  where [depId] != [subDepId]
  group by [subDepId]
union all
  select null, [procId], min([procSorter]) as [sorter]
  from [#tmpDepartmentProcessSorter]
  group by [procId]
order by [sorter] asc;
go

update [dbo].[cf_DepartmentAndProcessSorter] set [sorter] = [id];
go

-- elements

update [proc]
set [proc].[sidebar] = [el].[defaultValue] + '.jpg'
from [dbo].[cf_Process] as [proc]
join [dbo].[tmp_ProcessElements] as [el] on [el].[processName] = [proc].[name]
where [el].[typeId] = 7
and [el].[defaultValue] like '%.bmp';
go

delete from [dbo].[tmp_ProcessElements] where [typeId] = 0 or [processName] in ('', 'none');
go

alter table [dbo].[tmp_ProcessElements] add [id] int identity(1,1);
go

alter table [dbo].[cf_FormElement] add [controlId] int null;
go

create index [tmpFormId] on [dbo].[cf_FormElement] ([formId], [controlId]);
go

create index [tmpName] on [dbo].[cf_Process]([name]);
go

delete from [dbo].[cf_FormElement];
go

dbcc checkident ('dbo.cf_FormElement', reseed, 0);
go

delete from [dbo].[cf_FormElementPropertyValue];
go

set identity_insert [dbo].[cf_FormElement] on;
go

insert into [dbo].[cf_FormElement] ([id], [formId], [typeId], [left], [top], [zIndex], [added], [changed], [controlId])
select [pe].[id], [p].[id], [pe].[typeId], [pe].[left], [pe].[top], [pe].[zIndex], getdate(), getdate(), [pe].[controlId]
from [dbo].[tmp_ProcessElements] as [pe]
join [dbo].[cf_Process] as [p] on [p].[name] = [pe].[processName]
join [dbo].[cf_Department] as [d] on [p].[departmentId] = [d].[id]
  and [d].[name] = (case [pe].[subDepartmentName] when 'none' then [pe].[departmentName] else [pe].[subDepartmentName] end)
where [pe].[typeId] > 0 and [pe].[processName] not in ('', 'none');
go

set identity_insert [dbo].[cf_FormElement] off;
go

if object_id('tempdb.dbo.#tmpProcessElementsWidthHeight','U') is not null
  drop table [#tmpProcessElementsWidthHeight];
go

create table [#tmpProcessElementsWidthHeight] (
  [typeId] int not null primary key,
  [widthPropertyId] int null,
  [heightPropertyId] int null,
  [name] varchar (255) null
);
go

insert into [#tmpProcessElementsWidthHeight] values
  (1, 12, null, 'input'),
  (2, 13, 14, 'textarea'),
  (4, 15, null, 'label'),
  (5, null, null, 'checkbox'),
  (6, 24, null, 'select'),
  (7, 25, 26, 'file'),
  (8, 27, null, null),
  (9, 38, null, null);
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], [pewh].[widthPropertyId], [pe].[width]
from [dbo].[tmp_ProcessElements] as [pe]
join [#tmpProcessElementsWidthHeight] as [pewh] on [pe].[typeId] = [pewh].[typeId]
where [pewh].[widthPropertyId] is not null;
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], [pewh].[heightPropertyId], [pe].[height]
FROM [dbo].[tmp_ProcessElements] as [pe]
join [#tmpProcessElementsWidthHeight] as [pewh] on [pe].[typeId] = [pewh].[typeId]
where [pewh].[heightPropertyId] is not null;
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], 8, [pewh].[name] + cast([pe].[id] as varchar)
from [dbo].[tmp_ProcessElements] as [pe]
join [#tmpProcessElementsWidthHeight] as [pewh] on [pe].[typeId] = [pewh].[typeId]
where [pewh].[name] is not null;
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], 1, [pe].[title]
from [dbo].[tmp_ProcessElements] as [pe]
where [pe].[title] != '';
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], 4, [pe].[fontName]
from [dbo].[tmp_ProcessElements] as [pe]
where [pe].[fontName] != '';
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [pe].[id], 7, cast(round(([pe].[fontSize] * 1.4), 0) as int)
from [dbo].[tmp_ProcessElements] as [pe]
where [pe].[fontSize] != '';
go

update [fepvh]
set [fepvh].[value] = case when [p].[height] != 0 then [p].[height] else 300 end
from [dbo].[cf_FormElementPropertyValue] as [fepv]
join [dbo].[cf_FormElementPropertyValue] as [fepvh] on [fepvh].[elementId] = [fepv].[elementId]
  and [fepvh].[propertyId] = 14
  and [fepvh].[value] = -1
join [dbo].[cf_FormElement] as [fe] on [fepv].[elementId] = [fe].[id]
join [dbo].[cf_Process] as [p] on [fe].[formId] = [p].[id]
where [fepv].[propertyId] = 13
and [fepv].[value] = -1 ;
go

update [p]
set [p].[height] = case when [p].[height] != 0 then [p].[height] else 300 end
from [dbo].[cf_FormElementPropertyValue] as [fepv]
join [dbo].[cf_FormElementPropertyValue] as [fepvh] on [fepvh].[elementId] = [fepv].[elementId]
  and [fepvh].[propertyId] = 14
  and [fepvh].[value] = -1
join [dbo].[cf_FormElement] as [fe] on [fepv].[elementId] = [fe].[id]
join [dbo].[cf_Process] as [p] on [fe].[formId] = [p].[id]
where [fepv].[propertyId] = 13
and [fepv].[value] = -1 ;
go

update [fepv]
set [fepv].[value] = 600
from [dbo].[cf_FormElementPropertyValue] as [fepv]
join [dbo].[cf_FormElementPropertyValue] as [fepvh] on [fepvh].[elementId] = [fepv].[elementId]
  and [fepvh].[propertyId] = 14
  and [fepvh].[value] = -1
join [dbo].[cf_FormElement] as [fe] on [fepv].[elementId] = [fe].[id]
join [dbo].[cf_Process] as [p] on [fe].[formId] = [p].[id]
where [fepv].[propertyId] = 13
and [fepv].[value] = -1 ;
go

if object_id('tempdb.dbo.#tmpElementNewName','U') is not null
  drop table [#tmpElementNewName];
go

create table [#tmpElementNewName] (
  [id] int null,
  [name] varchar (255) null
);
go

insert into [#tmpElementNewName]
select distinct [pe].[id], replace([pe].[description], ' ', '_') as [name]
from [dbo].[tmp_ProcessElements] as [pe]
join [#tmpProcessElementsWidthHeight] as [pewh] on [pe].[typeId] = [pewh].[typeId]
join [dbo].[cf_FormElementPropertyValue] as [fepv] on [pe].[id] = [fepv].[elementId]
join [dbo].[cf_Process] as [p] on [p].[name] = [pe].[processName]
where [pewh].[name] is not null
and [pe].[description] != ''
and [fepv].[propertyId] = 8;
go

update [fepv]
set [fepv].[value] = [enn].[name]
from [#tmpElementNewName] as [enn]
join [dbo].[cf_FormElementPropertyValue] as [fepv] on [enn].[id] = [fepv].[elementId]
where [fepv].[propertyId] = 8;
go

-- script 3 end