USE [Nabeghlavi13.12.2018]
GO
/****** Object:  StoredProcedure [dbo].[VisitsSelect]    Script Date: 12/14/2018 4:57:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[VisitsSelect]
(
    @TimeGroupID int
      ,@StuffID int
      ,@DateStart date
      ,@DateEnd datetime
      ,@DepartmentName nvarchar(500)
      ,@StuffGroupID INT
)
AS
BEGIN

SET @DateEnd=DATEADD(MINUTE,60*24-1,@DateEnd)

    select
        v.ID
        ,v.InCount
        ,1 as AllowedAccessNumbers
        ,v.IndeviceID
        ,v.RecordTime
        ,v.StuffID
        ,v.TimeGroupID
        ,s.Name as StuffFullName
        ,s.IdNumber as StuffIDNumber
        ,s.Department as DepartmentName
        ,t.Name as TimeGroupName
        ,v.AlarmType
        ,t.TimeStart as GstartTime
        ,t.TimeEnd as GendTime
    from Visits as v
    left join [Stuff] as s
    on s.ID = v.StuffID
    left join TimeGroups as t
    on v.TimeGroupID = t.ID
    where
        (@StuffID is null OR v.StuffID = @StuffID)
        and
        (@DateStart is null OR v.RecordTime >= @DateStart)
        and
        (@DateEnd is null OR v.RecordTime <= @DateEnd)
        and
        (len(@DepartmentName) < 3 OR s.Department like '%'+@DepartmentName+'%')
        AND
        (@TimeGroupID IS NULL OR v.TimeGroupID=@TimeGroupID)
        AND
        (@StuffGroupID IS NULL OR s.StufGroupID=@StuffGroupID)
    order by v.RecordTime asc

END