if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaRst]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaRst]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAstnciaTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAstnciaTot]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaRst] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT N.IdEmpleado,Apellidos,Nombres,DATEDIFF(YEAR,E.FecNacmto,GETDATE()) AS Edad,E.Sexo,N.NContrato,N.IdNom,TipoNomina,ISNULL(D.HoraInicial,N.FecFinal) AS Fecha,N.IdPeriodo
,D.IdConcepto,CN.Concepto,ISNULL(D.HoraInicial,N.FecFinal) AS Hora_Inicial,ISNULL(D.HoraFinal,N.FecFinal) AS Fec_Final
,D.CantDevg AS CantHoras,D.VrUnitario,D.VrTotDevg AS VrLiquidado,D.VrOrigen AS BaseLiq,D.Tarifa,C.IdCargo,Cargo,D.Detalle,D.ClaseLiq,D.OrigCargue,CN.Unidad
,CN.Tarifa AS ConcTarifa,N.VrSalario,C.IdArea,Area,C.IdDep,Dependencia,C.IdInstala,Instlacion,C.IdCia,Compania,C.IdCCosto,CCosto,C.IdSubCos,SubCosto
FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro AND N.IdEmpleado=D.IdEmpleado
INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
INNER JOIN Instalaciones AS IST ON C.IdInstala=IST.IdInstala INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos 
WHERE (N.IdPeriodo=@pmIdPeriodo OR @pmIdPeriodo IS NULL) AND (N.IdEmpleado=@pmIdEmpleado OR @pmIdEmpleado IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomAstnciaTot] @pmIdEmpleado VARCHAR(16)=Null,@pmFecInicio SMALLDATETIME=Null,@pmFecFinal SMALLDATETIME=Null
,@pmEsDmncal BIT=Null,@pmTipoJrnada VARCHAR(10)=Null
 AS 
 SELECT Count(FechaDia) AS Cant,SUM(HHDiurno) AS STotHHD,SUM(HHNocturno) AS STotHHN,SUM(HEDiurno) AS STotHED ,SUM(HENocturno) AS STotHEN
 ,SUM(CASE WHEN (TipoJrnada='DESCANSO' OR TipoJrnada='DSCSINCOMP') THEN Horas_Dia ELSE 0 END) AS STotDesc
 FROM Trn_NomAstncia
 WHERE FechaDia BETWEEN @pmFecInicio AND @pmFecFinal 
 AND IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (TipoJrnada=@pmTipoJrnada OR @pmTipoJrnada IS NULL)
 AND (EsDmncal=ISNULL(@pmEsDmncal,0) or EsDmncal=ISNULL(@pmEsDmncal,1))
GO