##Reports
###Hourly Report
The $variables in the Report are passed from the Report Viewer Widget Parameter list. The Parameters are assigned as follows:
$1 : Entity Type (Employee)
$2 : Employee Name (i.e. Ovania or Jenery or Courtney or ...)
$3 : Date Filter Start (optional)
$4 : Date Filter End (optional)
$5 : unused
[Current Hours $2:21,10, 10, 10, 18, 15, 6]
@@EmployeePunchInLatest:$1,$2,$3,$4,$5
[Previous Hours $2:21,10, 10, 10, 18, 15, 6]
>Date|Day|Start|End|Hours|OT|H
@@EmployeeHoursDaily:$1,$2,$3,$4,$5
>@@EmployeeHoursTTL:$1,$2,$3,$4,$5
###SQL Scripts
The SQL Scripts are called from within the Report shown above. These scripts need to be set up in
Manage > Automation > Scripts
EmployeeHoursDaily
Handler: @@EmployeeHoursDaily
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'
-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'
-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'
-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)
-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)
-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00
-- NOTHING TO SET BEYOND HERE)
SELECT
left(convert(varchar(19),sl.[StartStateDate],120),4)
+' '+
left(convert(varchar(19),sl.[StartStateDate],107),3)
+' '+
substring(convert(varchar(19),sl.[StartStateDate],120),9,2) as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[StartState] = @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]
EmployeePunchInLatest
Handler: @@EmployeePunchInLatest
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'
-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'
-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'
-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)
-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)
-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00
-- NOTHING TO SET BEYOND HERE
declare @Now datetime = CONVERT(VARCHAR(25), GETDATE(), 126)
SELECT
left(convert(varchar(19),sl.[EndStateDate],126),4)
+' '+
left(convert(varchar(19),sl.[EndStateDate],107),3)
+' '+
substring(convert(varchar(19),sl.[EndStateDate],120),9,2) as [Date]
,Left(format(sl.[EndStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),@Now,120),12,5) as [End]
,format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[Id]=(select max([Id]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [EndState]=@Clockin)
and [EndStateDate]>(select max([StartStateDate]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [StartState]=@Clockin)
and sl.[StartStateDate] >= @StartDate -- something wrong here
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[EndState] = @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]
EmployeeHoursTTL
Handler: @@EmployeeHoursTTL
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'
-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'
-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'
-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)
-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)
-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00
-- NOTHING TO SET BEYOND HERE
SELECT
left(convert(date,[Date],120),7)
,left(convert(datetime,[Date],107),3)
,'',''
, format(sum([Hours]),'###.00') as [Hours]
, format(sum([OTH]),'###.00') as [OTH]
, sum([H]) as [H]
FROM (
SELECT
sl.[StartStateDate] as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,(DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[StartState] = @Clockin
and e.[Name]=@Employee
) Daily
WHERE 1=1
GROUP BY left(convert(date,[Date],120),7), left(convert(datetime,[Date],107),3)
ORDER BY 1