ALTER PROCEDURE  [dbo].[VersionBD] @Version Varchar(25)  = '' Output
As

Set @Version='3.46'
Select @Version as Version
Go


CREATE TABLE [dbo].[ImpresionCaratula](
	[generadorid] [smallint] IDENTITY(1,1) NOT NULL,
	[NombreCampo] [nvarchar](50) NULL,
	[NombreColumna] [nvarchar](50) NULL,
	[PosicionTop] [numeric](18, 0) NULL,
	[PosicionLeft] [numeric](18, 0) NULL,
	[LetraSize] [numeric](18, 0) NULL,
	[LetraNegrita] [numeric](18, 0) NULL
) ON [PRIMARY]
GO


Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text1','COD_ADUANA','100','2500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text2','DESPACHO','100','7000','12','1')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text3','CANAL','100','10000','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text4','NOMBRE_IMPO_EXPO','600','2500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text5','CUIT_IMPO_EXPO','600','10000','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text6','DOMICILIO','1000','500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text7','NOMBRE_DESPACHANTE','1400','2500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text8','CUIT_DESPACHANTE','1400','10000','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text9','MATRICULA_MEDIO_TRANSPORTE','1800','2500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text10','FECHA_LLEGADA_TRANSPORTE','1800','10000','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text11','DECLARACION_SUMARIA','2200','500','12','0')
Go

Insert Into [ImpresionCaratula] ([NombreCampo] ,[NombreColumna] ,[PosicionTop] ,[PosicionLeft] ,[LetraSize] ,[LetraNegrita])
Values ('Text12','CODIGO_DEPOSITO','2200','8000','12','0')
Go

--exec [sp_RepImpresionCaratula] '21073IC24000540K'
CREATE Procedure [dbo].[sp_RepImpresionCaratula]
(@Despacho varchar(25) )
as 
Begin

set nocount on 
--select top 1 @Despacho=despacho from caratula_caratula order by despacho desc

 DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'Caratula_Caratula' 
			   and c.COLUMN_NAME not like 'Xml_%'
			   and c.CHARACTER_MAXIMUM_LENGTH > 100
			   and c.DATA_TYPE = 'nvarchar'
           for xml path('')), 1, 1, '')

set @query 
  = 'select * From (
     select ' + @colsunpivot + '
     from Caratula_Caratula
	 Where Despacho = ''' + @Despacho + ''' ) p
     unpivot
     (
        ValorCampo
        for NombreCampo in (' + @colsunpivot + ')  
     ) as u  '

   print @query

--declare @TempRep table (Valor  nvarchar(400), Campo  nvarchar(400))
Select top 0 Valor=Cast(Null as  nvarchar(400)), Campo=Cast(Null as  nvarchar(400)) into #TempRep

insert into #TempRep
exec sp_executesql @query;
 
--*************************************************************** 
UPDATE  #TempRep 
	SET Valor=Valor + '-' +  Depositos.DESCRIPCION
From Depositos
Where #TempRep.Campo = 'CODIGO_DEPOSITO'
and Depositos.CODIGO=#TempRep.Valor
and Depositos.FECHA_FIN > GETDATE()
--AND Depositos.ADUANA = (Select Top 1 Valor From #TempRep Where Campo = 'COD_ADUANA')
--la tabla depositos no coincide o esta desactualizada
--****************************************************************** 

Insert  #TempRep 
Select Valor=(select top 1 Canal from ALPHASEGUIMIENTO..SeguimientoEstado4 where NumeroOficializacion = @Despacho),
 Campo='CANAL'

Update #TempRep
set Valor = Case Valor
when 'N' then 'Naraja'
when 'A' then 'Amarillo'
when 'R' then 'Rojo'
when 'M' then 'Morado'
when 'V' then 'Verde'
ELSE '-'
end 
Where Campo = 'CANAL'

--****************************************************************** 

UPDATE  #TempRep 
	SET Valor=Valor + '-' +  aduana.descripcion
From aduana
Where #TempRep.Campo = 'COD_ADUANA'
and aduana.id=#TempRep.Valor
--******************************************************************
Insert  #TempRep 
Select Valor=CARATULA_INFORMACION_COMPLEMENTARIAS.Valor, Campo='DOMICILIO'
From CARATULA_INFORMACION_COMPLEMENTARIAS
Where  CARATULA_INFORMACION_COMPLEMENTARIAS.IDN_DESPACHO=@Despacho
AND CARATULA_INFORMACION_COMPLEMENTARIAS.CODIGO = 'DOMICIL.ESTABLEC'
--******************************************************************
Insert  #TempRep 
Select Valor=despadu.Nombre, Campo='NOMBRE_DESPACHANTE'
From ALPHA2000..despadu
Where  despadu.cuit = (Select Top 1 Valor From #TempRep Where Campo = 'CUIT_DESPACHANTE')
--****************************************************************** 



--select * from #TempRep 
 

select  
	generadorid,
	NombreCampo,
	NombreColumna,
	PosicionTop,
	PosicionLeft,
	LetraSize,
	LetraNegrita,
	Valor=Isnull(Valor,'')
from  ImpresionCaratula I 
Left Join  #TempRep Temp on I.NombreColumna = Temp.Campo


Drop table #TempRep

End 


Go