--Hash:AC858057441FE373EB8705B15FB6BD90

-- Bloqueo exclusivo por base de datos
--//
SET NOCOUNT ON;

DECLARE @__ZiurDbScriptLockResource NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
DECLARE @__ZiurDbScriptLockResult INT;
EXEC @__ZiurDbScriptLockResult = sp_getapplock
    @Resource = @__ZiurDbScriptLockResource,
    @LockMode = N'Exclusive',
    @LockOwner = N'Session',
    @LockTimeout = 600000; -- 10 min

IF (@__ZiurDbScriptLockResult < 0)
BEGIN
    RAISERROR(N'No se pudo obtener el bloqueo exclusivo de actualizacion para esta base de datos.', 16, 1);
    SET NOEXEC ON;
END
--//

/* Idempotencia a nivel de script: si la version ya existe, no ejecutar nada. */
IF EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 988)
BEGIN
    PRINT N'Scripts_988.sql: la version 988 ya esta aplicada. Se omite ejecucion.';
    EXEC sp_releaseapplock @Resource = @__ZiurDbScriptLockResource, @LockOwner = N'Session';
    SET NOEXEC ON;
END

SET XACT_ABORT ON

GO

SET DATEFORMAT mdy;

--Validar que no existan scripts que no se hayan ejecutado
--//
IF EXISTS
(
	SELECT a.NumVersion + 1 AS HuecoInicio
	FROM ZiurVersiones a
	LEFT JOIN ZiurVersiones b ON a.NumVersion + 1 = b.NumVersion
	WHERE a.NumVersion>830 AND b.NumVersion IS NULL
	AND a.NumVersion + 1 < 988
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

--Colocar contenido de script diferencial aqui (Ziur.Database.publish.sql)

GO
/*
 Plantilla de script anterior a la implementación							
--------------------------------------------------------------------------------------
 Este archivo contiene instrucciones de SQL que se ejecutarán antes del script de compilación	
 Use la sintaxis de SQLCMD para incluir un archivo en el script anterior a la implementación			
 Ejemplo:      :r .\miArchivo.sql								
 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script anterior a la implementación		
 Ejemplo:      :setvar TableName miTabla							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/


GO

GO
PRINT N'Altering Procedure [dbo].[LiqPrestacionesNomina]...';

IF OBJECT_ID(N'dbo.LiqPrestacionesNomina', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE dbo.LiqPrestacionesNomina AS BEGIN SET NOCOUNT ON; RETURN 0; END');


GO
ALTER PROCEDURE LiqPrestacionesNomina

@FechaIni datetime='01/01/2013',
@FechaCorte datetime='12/31/2013',
@IdContrato NVARCHAR(1000)='0',
@IdItem INT=120

AS
BEGIN

declare @factor numeric(28,4)=1

if  @IdItem=121
set @factor=30

--Crear Tabla temporal de Contratos
--//
CREATE TABLE #Contratos (IdContrato int NOT NULL PRIMARY KEY);
INSERT INTO #Contratos(IdContrato)
SELECT number
FROM iter$simple_intlist_to_tbl(@IdContrato);
--//

--Crear tabla temporal de Hijos
--//
CREATE TABLE #HijosItems
(
    IdEncab           int          NOT NULL,
    IdCliente         int          NOT NULL,
    IdContrato        int          NOT NULL,
    IdItem            int          NOT NULL,
    Items             int          NOT NULL,
    ParteSuperior     numeric(28,6) NULL,
    ParteInferior     numeric(28,6) NULL,
    ValorUnit         numeric(28,6) NULL,
    ValorUnitInferior numeric(28,6) NULL,
    Cantidad          numeric(28,6) NULL
);

INSERT INTO #HijosItems (IdEncab, IdCliente, IdContrato, IdItem, Items, ParteSuperior, ParteInferior, ValorUnit, ValorUnitInferior, Cantidad)
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_01 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = @IdItem
UNION ALL
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_02 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = @IdItem
UNION ALL
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_03 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = @IdItem;

CREATE CLUSTERED INDEX IXH_01 ON #HijosItems (IdContrato, IdCliente, IdItem, Items);
CREATE NONCLUSTERED INDEX IXH_02 ON #HijosItems (IdEncab);
--//


select 
IdContrato,IdCliente,IdItem,Items,
SUM(ValorUnit*Cantidad)/MAX(ValorUnitInferior)* MAX(FactorGeneral) valorPromedio,
case when IdClasif in(1,3) then sum(Cantidad) else 1 end as Cantidad,
MAX(ParteSuperior)ParteSuperior,
MAX(ParteInferior)* @factor ParteInferior ,
MAX(FechaFin)FechaFin,
NomItem,IdClasif,CAST(0 as bit) as Sel
from
(
	select
	CASE WHEN q.ParteSuperior=0 THEN nomItem1.DivParteSuperior ELSE q.ParteSuperior END AS ParteSuperior,
	CASE WHEN q.ParteSuperior=0 THEN nomItem1.DivParteInferior ELSE Coalesce(q.ParteInferior,1) END AS ParteInferior,
	q.IdCliente,q.Items,ValorUnit,ValorUnitInferior,Cantidad,cliEmple.IdCentroCosto as CentroCosto,q.IdEncab,
	q.IdItem,q.IdContrato,enc.FechaFin,nomItem2.NomItem,nomItem2.IdClasif,nomItem2.FactorGeneral
 	from #HijosItems q
	INNER JOIN dbo.Nomina_Items as nomItem1 ON q.Items=nomItem1.IdItem
	INNER JOIN dbo.Clientes_Empleados as cliEmple ON cliEmple.IdCliente=q.IdCliente
	INNER JOIN Encabezados enc on q.IdEncab=enc.IdEncab
	INNER JOIN dbo.Nomina_Items as nomItem2 ON q.IdItem=nomItem2.IdItem 
	where 
	enc.EsAnulado=0 and 
	enc.FechaIni>=@FechaIni and enc.FechaFin<=@FechaCorte
) as q2
group by IdContrato,IdCliente,IdItem,Items,NomItem,IdClasif
order by IdContrato,IdItem
       
END
GO
PRINT N'Altering Procedure [dbo].[LiquidarVacacionesNomina]...';

IF OBJECT_ID(N'dbo.LiquidarVacacionesNomina', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE dbo.LiquidarVacacionesNomina AS BEGIN SET NOCOUNT ON; RETURN 0; END');


GO
ALTER PROCEDURE [dbo].[LiquidarVacacionesNomina]

@FechaCorte NVARCHAR(10)='',
@IdContrato NVARCHAR(1000)=''

AS
BEGIN

declare @factor numeric(28,4)=1,
@diasVacaciones int

select @diasVacaciones=max(Valor)from [dbo].[Nomina_Valores] where IdTipo=5 and EsActivo=1;

--Crear Tabla temporal de Contratos
--//
CREATE TABLE #Contratos (IdContrato int NOT NULL PRIMARY KEY);
INSERT INTO #Contratos(IdContrato)
SELECT number
FROM iter$simple_intlist_to_tbl(@IdContrato);
--//

--Crear tabla temporal de Hijos
--//
CREATE TABLE #HijosItems
(
    IdEncab           int          NOT NULL,
    IdCliente         int          NOT NULL,
    IdContrato        int          NOT NULL,
    IdItem            int          NOT NULL,
    Items             int          NOT NULL,
    ParteSuperior     numeric(28,6) NULL,
    ParteInferior     numeric(28,6) NULL,
    ValorUnit         numeric(28,6) NULL,
    ValorUnitInferior numeric(28,6) NULL,
    Cantidad          numeric(28,6) NULL
);

INSERT INTO #HijosItems (IdEncab, IdCliente, IdContrato, IdItem, Items, ParteSuperior, ParteInferior, ValorUnit, ValorUnitInferior, Cantidad)
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_01 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = 123
UNION ALL
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_02 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = 123
UNION ALL
SELECT h.IdEncab, h.IdCliente, h.IdContrato, h.IdItem, h.Items, h.ParteSuperior, h.ParteInferior, h.ValorUnit, h.ValorUnitInferior, h.Cantidad
FROM dbo.VNominaHijos_03 h INNER JOIN #Contratos c ON c.IdContrato = h.IdContrato
WHERE h.EsVisible = 1 AND h.Items = 123;

CREATE CLUSTERED INDEX IXH_01 ON #HijosItems (IdContrato, IdCliente, IdItem, Items);
CREATE NONCLUSTERED INDEX IXH_02 ON #HijosItems (IdEncab);
--//

select q4.*
from
(
select IdContrato,IdCliente,IdItem,Items,Rango,valorPromedio,Cantidad,ParteSuperior,ParteInferior,FechaIni,FechaFin,CantidadVacaciones,
case when Rows=1 then CantidadTotal-CantidadVacaciones else  CantidadTotal end CantidadFaltante,
CantidadTotal,NomItem,IdClasif,Sel,
case when Rows=1 then valorPromedio * ParteSuperior * (CantidadTotal-CantidadVacaciones)/(ParteInferior*CantidadTotal) 
else valorPromedio * ParteSuperior / ParteInferior  end as ValorVacaciones
from
(
select  
IdContrato,IdCliente,IdItem,Items,max(Rango)Rango,
case when IdClasif in(1,3) then sum(Cantidad)*MAX(ValorUnit)/MAX(ValorUnitInferior)* MAX(FactorGeneral)  else SUM(ValorUnit*Cantidad)/MAX(ValorUnitInferior)* MAX(FactorGeneral) end as valorPromedio,
case when IdClasif in(1,3) then sum(Cantidad) else 1 end as Cantidad,
MAX(ParteSuperior)ParteSuperior,
MAX(ParteInferior)* 1 ParteInferior ,
FechaIni,
FechaFin,
max(CantidadVacaciones)CantidadVacaciones,
sum(Cantidad)*@diasVacaciones/360 CantidadTotal,
NomItem,IdClasif,CAST(0 as bit) as Sel,
ROW_NUMBER() OVER(PARTITION BY max(Rango) ORDER BY sum(Cantidad)*@diasVacaciones/360 desc) AS Rows
from
(	select 
	CASE WHEN ParteSuperior=0 THEN nomItem1.DivParteSuperior ELSE ParteSuperior END AS ParteSuperior,
	CASE WHEN ParteSuperior=0 THEN nomItem1.DivParteInferior ELSE Coalesce(ParteInferior,1) END AS ParteInferior,
	q.IdCliente,Items,ValorUnit,ValorUnitInferior,q.Cantidad,cliEmple.IdCentroCosto as CentroCosto,q.IdEncab,
	q.IdItem,q.IdContrato,nomItem2.NomItem,nomItem2.IdClasif,nomItem2.FactorGeneral,
	unfVaca.FechaIni,unfVaca.Fechafin,unfVaca.Cantidad as CantidadVacaciones,
	'Periodo: '+CONVERT(VARCHAR(10),unfVaca.FechaIni,105) +' - '+ CONVERT(VARCHAR(10),unfVaca.FechaFin,105) Rango
	from #HijosItems q
	INNER JOIN dbo.Nomina_Items as nomItem1 ON q.Items=nomItem1.IdItem 
	INNER JOIN dbo.Clientes_Empleados as cliEmple ON cliEmple.IdCliente=q.IdCliente
	INNER JOIN Encabezados enc on q.IdEncab=enc.IdEncab
	INNER JOIN dbo.Nomina_Items as nomItem2 ON q.IdItem=nomItem2.IdItem
	INNER JOIN [dbo].[ufnVacacionesDisponibles](@FechaCorte,@IdContrato) AS unfVaca
	on q.IdContrato=unfVaca.IdContrato and enc.FechaFin between unfVaca.FechaIni and unfVaca.FechaFin
	where 
	enc.EsAnulado=0
)q2
group by IdContrato,FechaIni,Fechafin,IdCliente,IdItem,Items,NomItem,IdClasif
)as q3
)as q4
where CantidadFaltante>0
order by IdContrato,FechaIni

END
GO
	/*
	Plantilla de script posterior a la implementación							
	--------------------------------------------------------------------------------------
	 Este archivo contiene instrucciones de SQL que se anexarán al script de compilación.		
	 Use la sintaxis de SQLCMD para incluir un archivo en el script posterior a la implementación.			
	 Ejemplo:      :r .\miArchivo.sql								
	 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script posterior a la implementación.		
	 Ejemplo:      :setvar TableName miTabla							
				   SELECT * FROM [$(TableName)]					
	--------------------------------------------------------------------------------------
	*/
	
GO

GO
PRINT N'Update complete.';


GO


--Actualizar datos de la version

IF NOT EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 988)
BEGIN
    INSERT INTO dbo.ZiurVersiones (IdVersion, NumVersion, Descripcion)
    VALUES (398, 988, 'Mejoras Nomina y Vacaciones');
END
GO


/* Liberar bloqueo exclusivo por base de datos */
DECLARE @__ZiurDbScriptLockResourceEnd NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
EXEC sp_releaseapplock
    @Resource = @__ZiurDbScriptLockResourceEnd,
    @LockOwner = N'Session';
GO
