if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAstncia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAstncia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAstncia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAstncia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAstnciaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAstnciaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAstnciaTip]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAstnciaTip]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomProvAjuFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomProvAjuFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomProvAjuLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomProvAjuLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAstncia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAstncia]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomProvAjuFmt] @pmTipDoc VARCHAR(3),@pmNumAjusteIni INT,@pmNumAjusteFin INT,@pmIdCia CHAR(2)
 AS 
SELECT A.TipDoc,A.NumAjuste,A.IdCia,Compania,A.Fecha,A.TipoLiquida,A.FecLiquida,A.LiqParcial,A.IdPeriodo,A.IdNom,TipoNomina,A.VrAjusteProv,A.VrAjusteLiq,A.TipCom,TipoCom,A.Comprobante,A.IdCiaCom
,A.Observacion,A.Anulado,A.FechaCrea,A.IdCiaCrea,A.IdUsuario,Usuario
--NE_Estado, NE_Prefijo, NE_Numero, NE_FecEnvio, NE_CUNE, NE_QR, 
,D.Item,D.IdConcepto,Concepto,D.Descripcion,D.VrDebito,D.VrCredito,D.IdEmpleado,E.Apellidos,E.Nombres,D.NContrato
,D.CdCuentaDb,P.NomCuenta AS NomCuentaDb,D.CdCuentaCr,PC.NomCuenta AS NomCuentaCr,D.IdCCosto,CCosto,D.IdSubCos,SubCosto,D.CdDepend,Dependencia
FROM Trn_NomProvAju AS A INNER JOIN Trn_NomProvAjuDet AS D ON A.TipDoc=D.TipDoc AND A.NumAjuste=D.NumAjuste AND A.IdCia=D.IdCia
INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON A.IdNom=TN.IdNom INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
LEFT JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto LEFT JOIN TiposCom AS TC ON A.TipCom=TC.IdCom
LEFT JOIN Puc AS P ON D.CdCuentaDb=P.IdCuenta LEFT JOIN Puc AS PC ON D.CdCuentaCr=PC.IdCuenta
LEFT JOIN Dependencias AS DP ON D.CdDepend=DP.IdDep
WHERE A.TipDoc=@pmTipDoc AND A.NumAjuste BETWEEN @pmNumAjusteIni AND @pmNumAjusteFin AND A.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomProvAjuLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT A.TipDoc,A.NumAjuste,A.IdCia,Compania,A.Fecha,A.TipoLiquida,A.FecLiquida,A.LiqParcial,A.IdPeriodo,A.IdNom,TipoNomina,A.VrAjusteProv,A.VrAjusteLiq,A.TipCom,TipoCom,A.Comprobante,A.IdCiaCom
,A.Observacion,A.Anulado,A.FechaCrea,A.IdCiaCrea,A.IdUsuario,Usuario--NE_Estado, NE_Prefijo, NE_Numero, NE_FecEnvio, NE_CUNE, NE_QR, 
,D.Item,D.IdConcepto,Concepto,D.Descripcion,D.VrDebito,D.VrCredito,D.IdEmpleado,E.Apellidos,E.Nombres,D.NContrato
,D.CdCuentaDb,P.NomCuenta AS NomCuentaDb,D.CdCuentaCr,PC.NomCuenta AS NomCuentaCr,D.IdCCosto,CCosto,D.IdSubCos,SubCosto,D.CdDepend,Dependencia
FROM Trn_NomProvAju AS A INNER JOIN Trn_NomProvAjuDet AS D ON A.TipDoc=D.TipDoc AND A.NumAjuste=D.NumAjuste AND A.IdCia=D.IdCia
INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON A.IdNom=TN.IdNom INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
LEFT JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto LEFT JOIN TiposCom AS TC ON A.TipCom=TC.IdCom
LEFT JOIN Puc AS P ON D.CdCuentaDb=P.IdCuenta LEFT JOIN Puc AS PC ON D.CdCuentaCr=PC.IdCuenta
LEFT JOIN Dependencias AS DP ON D.CdDepend=DP.IdDep
WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomAstnciaTip] @pmTipo CHAR(1),@pmIdEmpleado VARCHAR(16),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME
 AS 
 IF @pmTipo='D' --descansos
  SELECT Count(FechaDia) AS Cant,SUM(HHDiurno) AS STotHHD,SUM(HHNocturno) AS STotHHN,SUM(HEDiurno) AS STotHED ,SUM(HENocturno) AS STotHEN
  ,SUM(Horas_Dia) AS CantHorasDia
  FROM Trn_NomAstncia
  WHERE IdEmpleado=@pmIdEmpleado AND  FechaDia BETWEEN @pmFecInicio AND @pmFecFinal 
   AND TipoJrnada IN ('DESCANSO','DSCSINCOMP')
 ELSE --SIN COMPENSATORIOS
  SELECT Count(FechaDia) AS Cant,SUM(HHDiurno) AS STotHHD,SUM(HHNocturno) AS STotHHN,SUM(HEDiurno) AS STotHED ,SUM(HENocturno) AS STotHEN
  ,SUM(Horas_Dia) AS CantHorasDia
  FROM Trn_NomAstncia
  WHERE IdEmpleado=@pmIdEmpleado AND  FechaDia BETWEEN @pmFecInicio AND @pmFecFinal 
   AND TipoJrnada IN ('SINCOMP','DSCSINCOMP')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNomAstncia] @pmIdEmpleado VARCHAR(16),@pmFechaDia SMALLDATETIME,@pmHHDiurno DECIMAL(10,4),@pmHHNocturno DECIMAL(10,4),@pmHEDiurno DECIMAL(10,4),@pmHENocturno DECIMAL(10,4)
,@pmTipoJrnada VARCHAR(10),@pmEsDmncal BIT,@pmHorario VARCHAR(30),@pmCodTurno VARCHAR(4),@pmHoras_Dia DECIMAL(10,4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomAstncia SET HHDiurno=@pmHHDiurno,HHNocturno=@pmHHNocturno,HEDiurno=@pmHEDiurno,HENocturno=@pmHENocturno,TipoJrnada=@pmTipoJrnada,EsDmncal=@pmEsDmncal,Horario=@pmHorario
,CodTurno=@pmCodTurno,Horas_Dia=@pmHoras_Dia,FecUpdate=@pmFecUpdate
 WHERE IdEmpleado=@pmIdEmpleado AND FechaDia=@pmFechaDia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAstncia] @pmIdEmpleado VARCHAR(16),@pmFechaDia SMALLDATETIME
 AS 
 SELECT IdEmpleado,FechaDia,HHDiurno,HHNocturno,HEDiurno,HENocturno,TipoJrnada,EsDmncal,Horario,CodTurno,Horas_Dia,TimeSys,FecUpdate,IdUsuario
 FROM Trn_NomAstncia
 WHERE IdEmpleado=@pmIdEmpleado AND FechaDia=@pmFechaDia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomAstnciaLta] @pmIdEmpleado VARCHAR(16)=Null,@pmFecInicio SMALLDATETIME=Null,@pmFecFinal SMALLDATETIME=Null
,@pmEsDmncal BIT=Null,@pmTipoJrnada VARCHAR(10)=Null
 AS 
 SELECT A.IdEmpleado AS IdEmplado,Apellidos,Nombres,FechaDia,HHDiurno,HHNocturno,HEDiurno,HENocturno,TipoJrnada,EsDmncal,Horario,TimeSys,A.FecUpdate AS FecUpd
,A.IdUsuario AS IdUsuari,Usuario,E.NContrato AS NumContrato,FecIngreso,FecRetiro,VrSalario,VrAuxTrans,C.IdNom AS CodNom,TipoNomina,pVehiculo,CodTurno,Horas_Dia
 FROM Trn_NomAstncia AS A INNER JOIN Empleados AS E ON A.IdEmpleado=E.IdEmpleado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 LEFT JOIN Emp_Contrato AS C ON E.NContrato=C.NContrato LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE  FechaDia BETWEEN @pmFecInicio AND @pmFecFinal AND A.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
  AND TipoJrnada LIKE ISNULL(@pmTipoJrnada,'%')
 AND (EsDmncal=ISNULL(@pmEsDmncal,0) or EsDmncal=ISNULL(@pmEsDmncal,1))
 ORDER BY Apellidos,Nombres,FechaDia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomAstncia] @pmIdEmpleado VARCHAR(16),@pmFechaDia SMALLDATETIME,@pmHHDiurno DECIMAL(10,4),@pmHHNocturno DECIMAL(10,4),@pmHEDiurno DECIMAL(10,4),@pmHENocturno DECIMAL(10,4),@pmTipoJrnada VARCHAR(10)
,@pmEsDmncal BIT,@pmHorario VARCHAR(30),@pmCodTurno VARCHAR(4),@pmHoras_Dia DECIMAL(10,4),@pmTimeSys SMALLDATETIME, @pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomAstncia (IdEmpleado,FechaDia,HHDiurno,HHNocturno,HEDiurno,HENocturno,TipoJrnada,EsDmncal,Horario,TimeSys,IdUsuario,CodTurno,Horas_Dia)
 VALUES (@pmIdEmpleado,@pmFechaDia,@pmHHDiurno,@pmHHNocturno,@pmHEDiurno,@pmHENocturno,@pmTipoJrnada,@pmEsDmncal,@pmHorario,@pmTimeSys,@pmIdUsuario,@pmCodTurno,@pmHoras_Dia)
GO