-- obtener configuracion parametros
-- -------------------------------------------------
CREATE TABLE [dbo].[ConfiguracionParametros](
[App] [varchar](23) NOT NULL,
[PermitirCapturaManual] [int] NOT NULL,
[PermitirCapturaEscaner] [int] NOT NULL,
[HorasMaximasCaptura] [int] NOT NULL,
[PorcentajeMaximoCaptura] [int] NOT NULL,
[NumeroMaximoLineosTrabajadosCaptura] [int] NOT NULL,
[EscanearCodigosActividadCosecha] [varchar](3) NOT NULL,
[OmitirDepartamentos] [varchar](3) NOT NULL,
[ForzarRangoFechas] [int] NOT NULL,
[FechaMinima] [datetime] NOT NULL,
[FechaMaxima] [datetime] NOT NULL,
[ForzarDepuracion] [int] NOT NULL,
[DiasAnterioresADepurar] [int] NOT NULL,
[PermitirCapturaEscanerTunel] [int] NOT NULL,
[PermitirCapturaEscanerRejas] [int] NOT NULL,
[NumeroMaximoLineosDescontados] [int] NOT NULL,
[ForzarCapturarChecadaPorActividad] [int] NOT NULL,
[ConsultarCalculoTotalDeLineos] [int] NOT NULL,
[ForzarCapturarTubo] [int] NOT NULL,
[EmpresaId] [int] NOT NULL,
[strLineos] [varchar](6) NOT NULL,
[strBancales] [varchar](8) NOT NULL,
[ForzarCapturaEmpleado] [int] NOT NULL,
[ForzarCapturarTuboPlanDeTrabajo] [int] NOT NULL,
[ForzarCapturaEmpleadoPlanDeTrabajo] [int] NOT NULL,
[MostrarPanelEscanerTunel] [int] NOT NULL,
[MostrarTunelesCompletosPorActividad] [int] NOT NULL,
[ForzarEnvioDBPorCorreoEnError] [int] NOT NULL,
[ForzarEnvioLogPorCorreoEnError] [int] NOT NULL,
[MostraSoloEmpleadosEnNave] [int] NOT NULL,
[ForzarRecepcionEmpleados] [int] NOT NULL,
[OmitirRecepcion_Si_Wifi_Lento] [int] NOT NULL,
[ApiTimeOutSegundos] [int] NOT NULL,
[ForzarSincronizarAlFinalizar] [int] NOT NULL,
[ForzarSincronizarAlFinalizarPlanDeTrabajo] [int] NOT NULL
)
-- -------------------------------------------------
CREATE PROCEDURE ObtenerConfiguracionPrametros
as begin
DECLARE @numero_parametros AS INT
;
SELECT @numero_parametros = COUNT(*)
FROM information_schema.columns
WHERE table_name = 'ObtenerConfiguracionPrametros'
;
CREATE TABLE #tpm_ConfiguracionParametros
(
Id varchar(30)
,Valor varchar(30)
)
;
WHILE(@numero_parametros IS NOT NULL and @numero_parametros >0)
BEGIN
DECLARE @column nvarchar(100), @columnvalue nvarchar(100), @sql nvarchar(500)
;
SELECT @columnvalue = COLUMN_NAME , @column = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ObtenerConfiguracionPrametros' AND ORDINAL_POSITION = @numero_parametros
;
SET @sql = ' SELECT ' + CAST(@numero_parametros as varchar(2)) + ','+ @columnvalue + ' FROM ObtenerConfiguracionPrametros '
;
INSERT INTO #tpm_ConfiguracionParametros
EXEC( @sql)
;
SELECT @numero_parametros = @numero_parametros -1
;
END;
SELECT *
INTO #tmp_t1
FROM #tpm_ConfiguracionParametros ORDER BY CAST(Id as int) asc
;
select
Id = ORDINAL_POSITION,
Descripcion= COLUMN_NAME ,
Tipo = DATA_TYPE
into #tmp_t2
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='cpm_ConfiguracionParametros'
;
select
t1.Id, t2.Descripcion, t1.Valor , t2.Tipo
into #tmp_resultados
from #tmp_t2 t2
inner join #tmp_t1 t1
on t2.Id= t1.Id
;
select Id, Descripcion, Valor, Tipo from #tmp_resultados
end
;
-- -------------------------------------------------
CREATE PROCEDURE [dbo].[modificarConfiguracionGlobal]
@id as int
,@Valor as varchar(23)
as BEGIN
DECLARE @column nvarchar(100), @columnvalue nvarchar(100), @sql nvarchar(500)
;
SELECT @columnvalue = COLUMN_NAME , @column = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ConfiguracionParametros' AND ORDINAL_POSITION = @id
;
SET @sql = ' update top(1) t set ' + @column + ' = ' + @Valor + ' FROM ConfiguracionParametros t '
;
EXEC(@sql)
;
END
