-- script 4

-- temporary table tmp_ProcessParts
-- temporary table tmp_ProcessData

-- table cf_Part2Process
-- table cf_Process
-- table cf_Media
-- table cf_Part2ProcessData
-- table cf_FormSubmissionValue
-- table cf_zz_File
-- table cf_FormElementPropertyValue

delete from [dbo].[cf_Part2Process];
go

insert into [dbo].[cf_Part2Process] ([partId], [processId])
select [part].[id], [proc].[id]
from [dbo].[tmp_ProcessParts] as [parts]
join [dbo].[cf_Process] as [proc] on [proc].[name] = [parts].[processName]
join [dbo].[cf_Department] as [dept] on [proc].[departmentId] = [dept].[id]
  and [dept].[name] = case [parts].[subDepartmentName] when 'none' then [parts].[departmentName] else [parts].[subDepartmentName] end
join [dbo].[cf_Part] as [part] on [part].[extPartId] = [parts].[partId]
order by [part].[id], [dept].[id], [proc].id;
go

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

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

insert into [#tmpMediaProcesses]
select
  [p].[id],
  [p].[name],
  max(case when [tpd].[resultValue] like '%.jpg%' or [tpd].[resultValue] = '' then 0 else 1 end) as [pregJpg]
from [dbo].[cf_Process] as [p]
join [dbo].[tmp_ProcessData] as [tpd] on [p].[name] = [tpd].[processName]
group by [p].[id], [p].[name]
having max(case when [tpd].[resultValue] like '%.jpg%' or [tpd].[resultValue] = '' then 0 else 1 end) = 0;
go

update [p]
set [p].[isMedia] = 'y'
from  [dbo].[cf_Process] as [p]
join [#tmpMediaProcesses] as [tmp] on [p].[id] = [tmp].[id];
go

delete from [dbo].[cf_Media];
go

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

insert into [dbo].[cf_Media] (
  [processId],
  [partId],
  [revisionStart],
  [revisionEnd],
  [title],
  [description],
  [type],
  [image],
  [video],
  [sorter],
  [added],
  [changed]
)
select
  [proc].[id],
  [part].[id],
  0,
  NULL,
  '',
  '',
  'image',
  [procdata].[resultValue],
  '',
  [procdata].[pageNum],
  getdate(),
  getdate()
from [dbo].[tmp_ProcessData] as [procdata]
join [dbo].[cf_Process] as [proc] on [proc].[name] = [procdata].[processName] and [proc].[isMedia] = 'y'
join [dbo].[cf_Department] as [dept] on [proc].[departmentId] = [dept].[id]
  and [dept].[name] = case [procdata].[subDepartmentName] when 'none' then [procdata].[departmentName] else [procdata].[subDepartmentName] end
join [dbo].[cf_Part] as [part] on [part].[extPartId] = [procdata].[partId]
join [dbo].[cf_PartVersion] as [partver] on [partver].[id] = [part].[currentVersion]
where [procdata].[resultValue] != ''
and [procdata].[fileExists] = 1; -- додав цю перевірку, бо в старому запиті в вибірку попадали строки які не містять файлів
go

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

create table [#tmpMultyProcessTable] (
  [processName] varchar (255) not null,
  [partId] int not null,
  [totalSets] int  not null
);
go

insert into [#tmpMultyProcessTable]
select [processName], [partId], count([pageNum]) as [totalSets]
from [dbo].[tmp_ProcessData]
where [pageNum] != -1
group by [processName], [partId];
go

update [p]
set [p].[isMultiply] = 'y'
from [dbo].[cf_Process] as [p]
join [#tmpMultyProcessTable] as [t] on [p].[name] = [t].[processName]
where [t].[totalSets] > 1;
go

delete from [dbo].[cf_Part2ProcessData];
go

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

insert into [dbo].[cf_Part2ProcessData] ([processId], [versionId], [setId], [added], [changed])
select distinct
  [proc].[id],
  [partver].[id],
  case [proc].[isMultiply] when 'y' then (case [procdata].[pageNum] when -1 then null else [procdata].[pageNum] end) else null end,
  getdate(),
  getdate()
from [dbo].[tmp_ProcessData] as [procdata]
join [dbo].[cf_Process] as [proc] on [proc].[name] = [procdata].[processName] and [proc].[isMedia] = 'n'
join [dbo].[cf_Department] as [dept] on [proc].[departmentId] = [dept].[id]
  and [dept].[name] = case [procdata].[subDepartmentName] when 'none' then [procdata].[departmentName] else [procdata].[subDepartmentName] end
join [dbo].[cf_Part] as [part] on [part].[extPartId] = [procdata].[partId]
join [dbo].[cf_PartVersion] [partver] on [partver].[id] = [part].[currentVersion];
go

delete from [dbo].[cf_FormSubmissionValue];
go

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

insert into [dbo].[cf_FormSubmissionValue] ([submissionId], [elementId], [value])
select [part2procdata].[id], [formel].[id], '"' + replace([procdata].[resultValue], '"', '\\\"') + '"'
from [dbo].[tmp_ProcessData] as [procdata]
join [dbo].[cf_Process] as [proc] on [proc].[name] = [procdata].[processName]
join [dbo].[cf_Department] as [dept] on [proc].[departmentId] = [dept].[id]
  and [dept].[name] = case [procdata].[subDepartmentName] when 'none' then [procdata].[departmentName] else [procdata].[subDepartmentName] end
join [dbo].[cf_Part] as [part] on [part].[extPartId] = [procdata].[partId]
join [dbo].[cf_PartVersion] as [partver] on [partver].[id] = [part].[currentVersion]
join [dbo].[cf_Part2ProcessData] as [part2procdata] on [part2procdata].[processId] = [proc].[id]
  and [part2procdata].[versionId] = [partver].[id]
  and ([part2procdata].[setId] = [procdata].[pageNum] or ([procdata].[pageNum] in (-1, 1) and [part2procdata].[setId] is null))
join [dbo].[cf_FormElement] as [formel] on [formel].[formId] = [proc].[id]
  and [formel].[controlId] = [procdata].[controlId];
go

insert into [dbo].[cf_zz_File] ([name], [nameFs], [type], [size], [added])
select distinct [resultValue], [resultValue], 'image/jpeg', [fileSize], getdate()
from [dbo].[tmp_ProcessData]
left join [dbo].[cf_zz_File] exifile on [dbo].[tmp_ProcessData].resultValue = exifile.nameFs
where [dbo].[tmp_ProcessData].[fileSize] > 0
	and exifile.id is null;
go

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

create table [#tmpElementsName] (
  [elementId] int not null,
  [name] varchar(255) not null
);
go

insert into [#tmpElementsName]
select distinct [formel].[id], [procdata].[fieldName]
from [dbo].[tmp_ProcessData] as [procdata]
join [dbo].[cf_Process] as [proc] on [proc].[name] = [procdata].[processName]
join [dbo].[cf_Department] as [dept] on [proc].[departmentId] = [dept].[id]
  and [dept].[name] = case [procdata].[subDepartmentName] when 'none' then [procdata].[departmentName] else [procdata].[subDepartmentName] end
join [dbo].[cf_FormElement] as [formel] on [formel].[formId] = [proc].[id]
  and [formel].[controlId] = [procdata].[controlId]
join [dbo].[cf_FormElementPropertyValue] as [formelpropval] on [formelpropval].[elementId] = [formel].[id]
  and [formelpropval].[propertyId] = 8 -- WTF!?
where [procdata].[fieldName] != '';
go

update [fepv]
set [fepv].[value] = [ten].[name]
from [#tmpElementsName] as [ten]
join [dbo].[cf_FormElementPropertyValue] as [fepv] on [fepv].[elementId] = [ten].[elementId]
where [fepv].[propertyId] = 8; -- WTF!?
go

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

create table [#tmpMultyProcess] (
  [processId] int not null,
  [versionId] int not null,
  [totalSets] int not null
);
go

-- нічого не вибралось і не вставилось
insert into [#tmpMultyProcess]
select [processId], [versionId], count([setId]) [totalSets]
from [dbo].[cf_Part2ProcessData]
where [setId] is not null
group by [processId], [versionId]
having count([setId]) > 1;
go

update [p]
set [isMultiply] = 'y'
from [dbo].[cf_Process] as [p]
join [#tmpMultyProcess] as [mp] on [p].[id] = [mp].[processId];
go

update [p2dd]
set [p2dd].[setId] = null
from [dbo].[cf_Part2ProcessData] as [p2dd]
join [dbo].[cf_Process] as [p] on [p].[id] = [p2dd].[processId]
where [p].[isMultiply] = 'n';
go

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

create table [#tmpP2dd] (
  [id] int null,
  [processId] int not null,
  [versionId] int not null,
  [setId] int null
);
go

insert into [#tmpP2dd]
select [p2dd].[id], [p2dd].[processId], [p2dd].[versionId], [p2dd].[setId]
from [dbo].[cf_Part2ProcessData] as [p2dd]
join [dbo].[cf_Part2ProcessData] as [p2dd2] on [p2dd2].[processId] = [p2dd].[processId]
  and [p2dd2].[versionId] = [p2dd].[versionId];
go

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

create table [#tmpNewSet] (
  [id] int not null,
  [versionId] int not null,
  [newSubmissionId] int not null
);
go

insert into [#tmpNewSet]
select [p].[id], [p2dd].[versionId], [p2dd].[id]
from [dbo].[cf_Part2ProcessData] as [p2dd]
join [dbo].[cf_Process] as [p] on [p].[id] = [p2dd].[processId]
join [#tmpP2dd] as [p2dd2] on [p2dd2].[id] = [p2dd].[id]
where [p].[isMultiply] = 'y'
and [p2dd].[setId] is null;
go

insert into [dbo].[cf_FormSubmissionValue] ([submissionId], [elementId], [value])
select distinct [p2pd].[id] as [submissionId], [fsv].[elementId], [fsv].[value]
from [#tmpNewSet] as [tns]
join [dbo].[cf_Part2ProcessData] as [p2pd] on [p2pd].[processId] = [tns].[id]
  and [p2pd].[versionId] = [tns].[versionId]
join [dbo].[cf_FormSubmissionValue] as [fsv] on [tns].[newSubmissionId] = [fsv].[submissionId]
where [tns].[newSubmissionId] != [p2pd].[id];
go

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

create table [#tmpDoubleValue] (
  id int not null,
  newSubmissionId int not null
);
go

insert into [#tmpDoubleValue]
select distinct [p2pd].[id], [p2pdd].[id] as [newSubmissionId]
from [dbo].[cf_Part2ProcessData] as [p2pd]
join [dbo].[cf_Part2ProcessData] as [p2pdd] on [p2pd].[processId] = [p2pdd].[processId]
  and [p2pd].[versionId] = [p2pdd].[versionId]
where [p2pd].[processId] = 59 -- WTF!?
and [p2pd].[versionId] = 1874 -- WTF!?
and [p2pd].[setId] is null
and [p2pdd].[setId] is null;
go

insert into [dbo].[cf_FormSubmissionValue] ([submissionId], [elementId], [value])
select [tdv].[newSubmissionId] as [submissionId], [fesv].[elementId], [fesv].[value]
from [#tmpDoubleValue] as [tdv]
join [dbo].[cf_FormSubmissionValue] as [fesv] on [fesv].[submissionId] = [tdv].[id]
where [tdv].[id] != [tdv].[newSubmissionId];
go

update [fsv]
set [fsv].[value] = case [fsv].[value] when '"1"' then '"y"' else '"n"' end
from [dbo].[cf_FormSubmissionValue] as [fsv]
join [dbo].[cf_FormElement] as [fe] on [fe].[id] = [fsv].[elementId]
where [fe].[typeId] = 5;
go

update [dbo].[cf_FormSubmissionValue] set [value] = replace([value], '[\r]', '') where [value] like '%[\r]%';
go

update [dbo].[cf_FormSubmissionValue] set [value] = replace([value], '[\n]', char(10)) where [value] like '%[\n]%';
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [fe].[id], 2, (isnull(len([fepv].[value]), 0) * 7)
from [dbo].[cf_FormElement] as [fe]
left join [dbo].[cf_FormElementPropertyValue] as [fepv] on [fepv].[elementId] = [fe].[id]
  and [fepv].[propertyId] = 1
left join [dbo].[cf_FormElementPropertyValue] as [fepvw] on [fepvw].[elementId] = [fe].[id]
  and [fepvw].[propertyId] = 2
where [fe].[typeId] = 5
and [fepvw].[value] is null;
go

insert into [dbo].[cf_FormElementPropertyValue] ([elementId], [propertyId], [value])
select [fe].[id], 11, 'right'
from [dbo].[cf_FormElement] as [fe]
where [fe].[typeId] = 5;
go

update [fepvw]
set [fepvw].[value] = [fepvw].[value] - 6
from [dbo].[cf_FormElement] as [fe]
left join [dbo].[cf_FormElementPropertyValue] as [fepvw] on [fepvw].[elementId] = [fe].[id]
  and [fepvw].[propertyId] = 12
where [fe].[typeId] = 1;
go

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

create table [#tmpNotNullSet] (
  id int not null
);
go

insert into [#tmpNotNullSet]
select min([p2pd].[id])
from [dbo].[cf_Part2ProcessData] as [p2pd]
  join [dbo].[cf_Process] as [p] on [p2pd].[processId] = [p].[id]
where [p].[isMultiply] = 'y'
      and [p2pd].[setId] is null
group by [p2pd].[processId], [p2pd].[versionId]
having count(*) = 1;
go

update [p2pd]
set [p2pd].[setId] = 1
from [dbo].[cf_Part2ProcessData] as [p2pd]
join [#tmpNotNullSet] as [nns] on [p2pd].[id] = [nns].[id];
go

update [dbo].[cf_FormSubmissionValue] set [value] = replace([value], '	', '    ') ;
go

update [dbo].[cf_Process] set [height] = [height] + 10;
go

update [dbo].[cf_FormElementPropertyValue] set [value] = [value] - 1 where [propertyId] = 7;
go

-- drop tmp fileds/indexes

drop index [tmpFormId] on [dbo].[cf_FormElement];
go

drop index [tmpName] on [dbo].[cf_Process];
go

alter table [dbo].[cf_FormElement] drop column [controlId];
go

alter table [dbo].[tmp_ProcessElements] drop column [id];
go

-- script 4 end