-- script 2

-- temporary table tmp_Part

-- table cf_Part
-- table cf_PartVersion
-- table cf_zz_File

-- parts

delete from [dbo].[cf_Part];
go

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

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG = DB_NAME() and TABLE_NAME = 'cf_Part' and COLUMN_NAME = 'extPartId') begin
	alter table [dbo].[cf_Part] add extPartId int null;
end
go


insert into [dbo].[cf_Part] ([extPartId], [blockedBy], [height], [defaultProcessId], [currentVersion], [sorter], [added], [changed])
select [oldPartId], 0, 0, 0, 0, 0, getdate(), getdate() from [dbo].[tmp_Part];
go

-- versions

delete from [dbo].[cf_PartVersion];
go

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

insert into[dbo].[cf_PartVersion] (
  [partId],
  [preProduction],
  [version],
  [number],
  [name],
  [clientId],
  [weight],
  [alloyTemper],
  [status],
  [localDirectory],
  [image],
  [comments],
  [userId],
  [added],
  [changed]
)
select
  [part].[id] as [partId],
  [tmp].[preProduction],
  (select isnull(max([version]), 0) + 1 from [dbo].[cf_PartVersion] as [pv] where [pv].[partId] = [part].[id]) as [version],
  [tmp].[number],
  [tmp].[name],
  [client].[id] as [clientId],
  [tmp].[weight],
  [tmp].[alloyTemper],
  [tmp].[status],
  '' as [localDirectory],
  (case [tmp].[image] when '' then [tmp].[thumb] else [tmp].[image] end) as [image],
  '' as [comments],
  0 as [userId],
  getdate(),
  getdate()
from [dbo].[tmp_Part] as [tmp]
  join [dbo].[cf_Client] as [client] on [tmp].[clientId] = [client].[number]
  join [dbo].[cf_Part] as [part] on [part].[extPartId] = [tmp].[oldPartId];
go

update [part]
set [part].[currentVersion] = (select max([version].[id]) from [dbo].[cf_PartVersion] as [version] where [version].[partId] = [part].[id])
from [dbo].[cf_Part] as [part];
go

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

-- files

delete from [dbo].[cf_zz_File] where [name] = [nameFs];
go

insert into [dbo].[cf_zz_File] ([name], [nameFs], [type], [size], [added])
select
  case [image] when '' then [thumb] else [image] end,
  case [image] when '' then [thumb] else [image] end,
  case substring(
      (case [image] when '' then [thumb] else [image] end),
      CHARINDEX('.', (case [image] when '' then [thumb] else [image] end)) + 1,
      3
  ) when 'jpg' then 'image/jpeg' else 'image/x-targa' end,
  case [image] when '' then [thumbSize] else [imageSize] end,
  getdate()
from [dbo].[tmp_Part]
where [image] != '' or [thumb] != '';
go

-- script 2 end