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