-----------------------------------------------------------------
----- SCRIPT PARA MIGRAÇÃO DOS CDUs DOS DOCUMENTOS DE STOCK -----
-----------------------------------------------------------------
-- Esta script cria os CDUs caso não existam e copia os dados 
 
/***************/
/** ATUALIZAR **/
/***************/
-- Lista de campos e tipo de movimento a migrar os CDU
declare @lstCamposCabecSTK	as nvarchar(max)	= N'CDU_Campo1,CDU_Campo2' -- Colocar a lista de CDUs do cabeçalho separados por vírgulas
declare @lstCamposLinhasSTK	as nvarchar(max)	= N'CDU_Campo1,CDU_Campo2' -- Colocar a lista de CDUs das linhas separados por vírgulas	
declare @tipoMovimento		as smallint			= 0	-- Colocar: 0 = Documentos Internos; 1 = Transferências; 2 = Composições
 
 
/*************************************************/
/** Não efetuar alterações a partir deste ponto **/
/*************************************************/
 
/*** Variáveis internas ***/
declare @sql				as nvarchar(max)
declare @text                     	as nvarchar(max)
declare @CountFieldsLinhasSTK	as int
declare @CountFieldsCabecSTK	as int
declare @ActualFieldsLinhasSTK	as int
declare @ActualFieldsCabecSTK	as int
declare @TabDestinoLinhasSTK    	as nvarchar(50) = ''
declare @TabDestinoCabecSTK       	as nvarchar(50) = ''
declare @Campo			as nvarchar(50)
declare @Tipo                     	as nvarchar(50)
declare @Nulo                     	as nvarchar(10)
declare @Omissao                  	as nvarchar(max)
 
 
/*** Tabelas temporárias ***/
IF OBJECT_ID('tempdb..#CamposCabecSTK') IS NOT NULL DROP TABLE #CamposCabecSTK
IF OBJECT_ID('tempdb..#CamposLinhasSTK') IS NOT NULL DROP TABLE #CamposLinhasSTK
 
 
/*** Tipos de movimento ***/
set @TabDestinoCabecSTK = case @tipoMovimento when 0 then 'CabecInternos' when 1 then 'INV_CabecTransferencias' when 2 then 'INV_CabecComposicoes' else '' end
set @TabDestinoLinhasSTK = case @tipoMovimento when 0 then 'LinhasInternos' when 1 then 'INV_LinhasTransferencias' when 2 then 'INV_LinhasComposicoes' else '' end
 
 
/*** VALIDAÇÃO: CDUs antigos existem na BD migrada (dá erro caso não existam) ***/
select Campo into #CamposCabecSTK from [dbo].[STD_ParteString](@lstCamposCabecSTK,',')
select @CountFieldsCabecSTK = count(*) from #CamposCabecSTK
select Campo into #CamposLinhasSTK from [dbo].[STD_ParteString](@lstCamposLinhasSTK,',')
select @CountFieldsLinhasSTK = count(*) from #CamposLinhasSTK
 
set @sql = 'select @ActualFieldsCabecSTK = count(*) from StdCamposVar where Tabela = ''CabecStk'' and Campo in (select campo from #CamposCabecSTK)'
EXEC sp_executesql @sql,
  N'@ActualFieldsCabecSTK int OUTPUT',
  @ActualFieldsCabecSTK OUTPUT;
if (@ActualFieldsCabecSTK <> @CountFieldsCabecSTK)
THROW 90001, 'Os CDUs indicados não existem na tabela [BACKUP_UPG_PRI_V9_V10_CabecStk].', 1;
 
set @sql = 'select @ActualFieldsLinhasSTK = count(*) from StdCamposVar where Tabela = ''LinhasStk'' and Campo in (select campo from #CamposLinhasSTK)'
EXEC sp_executesql @sql,
  N'@ActualFieldsLinhasSTK int OUTPUT',
  @ActualFieldsLinhasSTK OUTPUT;
if (@ActualFieldsLinhasSTK <> @CountFieldsLinhasSTK)
THROW 90001, 'Os CDUs indicados não existem na tabela [BACKUP_UPG_PRI_V9_V10_LinhasStk].', 1;
 
/*** CRIA OS CDU (CabecSTK): os CDUs são criados automaticamente na tabela destino caso não existam ***/
DECLARE CAMPOS_CURSOR CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
select
SysColumn, SysType, SysNull, SysDefault 
from 
#CamposCabecSTK cs
inner join 
(	
select 
SysColumn=COLUMN_NAME
,SysNull = case when IS_NULLABLE = 'YES' then 'NULL' else 'NOT NULL' end
,SysDefault = case when COLUMN_DEFAULT is null then '' else 'DEFAULT ' + COLUMN_DEFAULT end
,SysType = DATA_TYPE + 
case DATA_TYPE 
when 'VARCHAR'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
when 'nVARCHAR'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
when 'decimal'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
else ''
end
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='BACKUP_UPG_PRI_V9_V10_CabecStk'
) SystemColumns on SystemColumns.SysColumn = cs.Campo
 
OPEN CAMPOS_CURSOR
FETCH NEXT FROM CAMPOS_CURSOR INTO @Campo, @Tipo, @Nulo, @Omissao
WHILE @@FETCH_STATUS = 0
BEGIN 
--Cria CDU
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name = @Campo AND Object_ID = Object_ID(@TabDestinoCabecSTK)) 
BEGIN
set @sql = 'ALTER TABLE ' + @TabDestinoCabecSTK + ' ADD ' + @Campo + ' ' + @Tipo + ' ' + @Nulo + ' ' + @Omissao
EXEC sp_executesql @sql
END
 
--Adiciona na StdCamposVar
IF NOT EXISTS (SELECT * FROM StdCamposVar WHERE Tabela = @TabDestinoCabecSTK and Campo = @Campo) 
BEGIN
INSERT 
INTO StdCamposVar ([Tabela], [Campo], [Descricao], [Texto], [Visivel], [Ordem], [Pagina], [ValorDefeito], [Query], [ExportarTTE], [DadosSensiveis]) 
SELECT @TabDestinoCabecSTK, [Campo], [Descricao], [Texto], [Visivel], (SELECT count(*) + 1 FROM StdCamposVar WHERE Tabela = @TabDestinoCabecSTK), [Pagina], [ValorDefeito], [Query], [ExportarTTE], [DadosSensiveis]
FROM StdCamposVar
WHERE Tabela = 'CabecStk' AND Campo = @Campo
END
 
FETCH NEXT FROM CAMPOS_CURSOR INTO @Campo, @Tipo, @Nulo, @Omissao
END
CLOSE CAMPOS_CURSOR
DEALLOCATE CAMPOS_CURSOR
 
 
/*** Copia dados dos cabeçalhos ***/
IF EXISTS (select * from #CamposCabecSTK)
BEGIN
set @sql = '
update DestTbl
set @@Campos@@
from @@DestTbl@@ DestTbl
inner join @@OrigTbl@@ OrigTbl on OrigTbl.ID = DestTbl.Id'
set @text = null
select @text = coalesce (@text + ', ', '') + 'DestTbl.' + Campo + ' = OrigTbl.' + Campo  from #CamposCabecSTK
set @sql = replace(@sql, '@@Campos@@', @text)	
set @sql = replace(@sql, '@@DestTbl@@', @TabDestinoCabecSTK)
set @sql = replace(@sql, '@@OrigTbl@@', 'BACKUP_UPG_PRI_V9_V10_CabecStk')
 
EXEC sp_executesql @sql
END
 
 
/*** CRIA OS CDU (LinhasSTK): os CDUs são criados automaticamente na tabela destino caso não existam ***/
DECLARE CAMPOS_CURSOR CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
select
SysColumn, SysType, SysNull, SysDefault 
from 
#CamposLinhasSTK cs
inner join 
(	
select 
SysColumn=COLUMN_NAME
,SysNull = case when IS_NULLABLE = 'YES' then 'NULL' else 'NOT NULL' end
,SysDefault = case when COLUMN_DEFAULT is null then '' else 'DEFAULT ' + COLUMN_DEFAULT end
,SysType = DATA_TYPE + 
case DATA_TYPE 
when 'VARCHAR'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
when 'nVARCHAR'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
when 'decimal'  then
ISNULL('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+') ','')
+ ISNULL('('+CAST(NUMERIC_PRECISION AS VARCHAR),'')
+ ISNULL(','+CAST(NUMERIC_SCALE AS VARCHAR)+')','')
else ''
end
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='BACKUP_UPG_PRI_V9_V10_LinhasStk'
) SystemColumns on SystemColumns.SysColumn = cs.Campo
 
OPEN CAMPOS_CURSOR
FETCH NEXT FROM CAMPOS_CURSOR INTO @Campo, @Tipo, @Nulo, @Omissao
WHILE @@FETCH_STATUS = 0
BEGIN 
--Cria CDU
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name = @Campo AND Object_ID = Object_ID(@TabDestinoLinhasSTK)) 
BEGIN
set @sql = 'ALTER TABLE ' + @TabDestinoLinhasSTK + ' ADD ' + @Campo + ' ' + @Tipo + ' ' + @Nulo + ' ' + @Omissao
EXEC sp_executesql @sql
END
 
--Adiciona na StdCamposVar
IF NOT EXISTS (SELECT * FROM StdCamposVar WHERE Tabela = @TabDestinoLinhasSTK and Campo = @Campo) 
BEGIN
INSERT 
INTO StdCamposVar ([Tabela], [Campo], [Descricao], [Texto], [Visivel], [Ordem], [Pagina], [ValorDefeito], [Query], [ExportarTTE], [DadosSensiveis]) 
SELECT @TabDestinoLinhasSTK, [Campo], [Descricao], [Texto], [Visivel], (SELECT count(*) + 1 FROM StdCamposVar WHERE Tabela = @TabDestinoLinhasSTK), [Pagina], [ValorDefeito], [Query], [ExportarTTE], [DadosSensiveis]
FROM StdCamposVar
WHERE Tabela = 'LinhasStk' AND Campo = @Campo
END
 
FETCH NEXT FROM CAMPOS_CURSOR INTO @Campo, @Tipo, @Nulo, @Omissao
END
CLOSE CAMPOS_CURSOR
DEALLOCATE CAMPOS_CURSOR
 
 
/*** Copia dados das linhas ***/
IF EXISTS (select * from #CamposLinhasSTK)
BEGIN
set @sql = '
update DestTbl
set @@Campos@@
from @@DestTbl@@ DestTbl
inner join @@OrigTbl@@ OrigTbl on OrigTbl.ID = DestTbl.Id'
set @text = null
select @text =  coalesce (@text + ', ', '') + 'DestTbl.' + Campo + ' = OrigTbl.' + Campo  from #CamposLinhasSTK
set @sql = replace(@sql, '@@Campos@@', @text)	
set @sql = replace(@sql, '@@DestTbl@@', @TabDestinoLinhasSTK)
set @sql = replace(@sql, '@@OrigTbl@@', 'BACKUP_UPG_PRI_V9_V10_LinhasStk')
 
EXEC sp_executesql @sql
END