#Timeclock & Timeclock Policies
WARNING: The Reports (@@SQL handlers) for Timeclock use JSON functions available only in SQL 2016, so you will need to upgrade to SQL Express 2016 to use the Reporting features.
##Task Types
##TC Punch Task (Task Type)##
Custom Fields
(none)
##TC Punch Control Task (Task Type)##
| Name: | TC Punch Control Task |
Custom Fields
(none)
##TC Policy Task (Task Type)##
Custom Fields
| Field Name | Field Type | Editing Format | Display Format |
active | Number | | |
type | String | | |
name | String | | |
dateStart | Date | | |
dateEnd | Date | | |
value | String | | |
##Scripts (SQL)
##TC_EmployeeHours [@@TC_EmployeeHours] (Script)##
| Script Name: | TC_EmployeeHours |
| Script Handler: | @@TC_EmployeeHours |
Script:
-- Hours 15 and 30
-- PARM for Employee Entity Type
declare @entityType varchar(20) = '@1'
-- PARM for Employee Name
declare @entityName 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 'Employees'
IF (@entityType = '') OR (@entityType is null) OR (@entityType = '$1') SET @entityType = 'Employees'
-- 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)+'-01'
-- 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 names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- NOTHING TO SET BEYOND HERE
DECLARE @RATE1 money = 0
DECLARE @RATE2 money = 0
DECLARE @RATE3 money = 0
-- get RATES
SELECT
--[Name] as [EntityName]
@RATE1 = SUM([R1])
, @RATE2 = SUM([R2])
, @RATE3 = SUM([R3])
FROM(
SELECT
e.[Id]
,e.[Name]
--,e.[CustomData]
,cdName
,CASE cdName WHEN @RateField1 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R1]
,CASE cdName WHEN @RateField2 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R2]
,CASE cdName WHEN @RateField3 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R3]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
-- here we "join" the [Entities] table to itself and use the OPENJSON function
-- on the [CustomData] column
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (
cdName varchar(50) '$.Name'
,cdValue varchar(50) '$.Value'
) jsonData
WHERE et.[Name] = @entityType
AND e.[Name] = @entityName
AND cdName IN (@RateField1,@RateField2,@RateField3)
) rates
--GROUP BY [Name]
-- get Policies
DECLARE @policies table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[taskId] int
,[taskName] varchar(255)
,[taskContent] varchar(255)
)
INSERT INTO @policies
SELECT
t.[Id]
,t.[Name]
,t.[Content]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]
WHERE 1=1
AND tt.[Name] = 'TC Policy Task'
AND [Completed] = 0
-- Calculate
SELECT
-- [EmployeeName]
-- [StartDate]
--,[EndDate]
left(datename(YEAR,[StartDate]),4)+' '+left(datename(MONTH,[StartDate]),3)+' '+substring(convert(varchar,[StartDate],126),9,2) as [DT]
,left(DATENAME(weekday,[StartDate]),3) as [DT]
--,[H]
,[Hours]
,[REG]
,[OT]
,[HOL]
--,FORMAT(SUM([REG]*@RATE1 + [OT]*@RATE2 + [HOL]*@RATE3) ,'0000.00') as [EARNED]
FROM (
SELECT
t.[Id]
,tt.[Name] as [TaskType]
,t.[Name] as [EmployeeName]
--,[Identifier]
--,[Completed]
--,[State]
--,substring(convert(varchar(10),[StartDate],126),6,5)
,[StartDate]
,[EndDate]
--,datediff(SECOND,t.[StartDate],t.[EndDate]) as [DUR_s]
--,datediff(SECOND,t.[StartDate],t.[EndDate])/60.0 as [DUR_m]
--,datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 as [DUR_h]
,FORMAT(
datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0
,'0.00')
as [Hours]
,CASE ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) WHEN '0' THEN '0' ELSE '1' END as [H]
-- HOLIDAY
,FORMAT(
CASE WHEN ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) != '0'
THEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0
ELSE 0
END ,'0.00') as [HOL]
-- OVERTIME
,FORMAT(
CASE WHEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 > ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999) AND (ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) = '0')
THEN (datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0) - ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999)
ELSE 0
END ,'0.00') as [OT]
-- REGULAR
,FORMAT(
CASE WHEN (ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) != '0')
THEN 0
ELSE CASE WHEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 > ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999)
THEN 8
ELSE (datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0)
END
END ,'0.00') as [REG]
--,[LastUpdateTime]
--,[Content]
--,[CustomData]
--,[StateLog]
--,[UserName]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]
WHERE 1=1
AND t.[Completed] = 1
AND t.[Name] = @entityName
AND t.[StartDate] >= @StartDate
AND t.[StartDate] < @EndDate
AND tt.[Name] = 'TC Punch Control Task'
--AND t.[State] = 'Punch Cycle Complete'
----ORDER BY t.[Name], t.[StartDate],t.[EndDate]
) HRS
GROUP BY
-- [EmployeeName]
[StartDate]
--,[EndDate]
,left(datename(YEAR,[StartDate]),4)+' '+left(datename(MONTH,[StartDate]),3)+' '+substring(convert(varchar,[StartDate],126),9,2)
,left(DATENAME(weekday,[StartDate]),3)
--,[H]
,[Hours]
,[HOL]
,[OT]
,[REG]
ORDER BY 1,2,3
##TC_EmployeeHoursTTL [@@TC_EmployeeHoursTTL] (Script)##
| Script Name: | TC_EmployeeHoursTTL |
| Script Handler: | @@TC_EmployeeHoursTTL |
Script:
-- Hours 15 and 30
-- PARM for Employee Entity Type
declare @entityType varchar(20) = '@1'
-- PARM for Employee Name
declare @entityName 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 'Employees'
IF (@entityType = '') OR (@entityType is null) OR (@entityType = '$1') SET @entityType = 'Employees'
-- 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)+'-01'
-- 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 names of Employee Custom Data Rate Fields
declare @RateField1 varchar(20) = 'Rate1'
declare @RateField2 varchar(20) = 'Rate2'
declare @RateField3 varchar(20) = 'Rate3'
-- NOTHING TO SET BEYOND HERE
DECLARE @RATE1 money = 0
DECLARE @RATE2 money = 0
DECLARE @RATE3 money = 0
-- get RATES
SELECT
--[Name] as [EntityName]
@RATE1 = SUM([R1])
, @RATE2 = SUM([R2])
, @RATE3 = SUM([R3])
FROM(
SELECT
e.[Id]
,e.[Name]
--,e.[CustomData]
,cdName
,CASE cdName WHEN @RateField1 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R1]
,CASE cdName WHEN @RateField2 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R2]
,CASE cdName WHEN @RateField3 THEN convert(decimal(10,2),cdValue) ELSE 0.00 END as [R3]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
-- here we "join" the [Entities] table to itself and use the OPENJSON function
-- on the [CustomData] column
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (
cdName varchar(50) '$.Name'
,cdValue varchar(50) '$.Value'
) jsonData
WHERE et.[Name] = @entityType
AND e.[Name] = @entityName
AND cdName IN (@RateField1,@RateField2,@RateField3)
) rates
--GROUP BY [Name]
-- get Policies
DECLARE @policies table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[taskId] int
,[taskName] varchar(255)
,[taskContent] varchar(255)
)
INSERT INTO @policies
SELECT
t.[Id]
,t.[Name]
,t.[Content]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]
WHERE 1=1
AND tt.[Name] = 'TC Policy Task'
AND [Completed] = 0
-- Calculate
SELECT
-- [EmployeeName]
-- [StartDate]
--,[EndDate]
left(datename(YEAR,[StartDate]),4)+' '+left(datename(MONTH,[StartDate]),3)+' TTL' as [DT]
,'TOTALS'
--,left(DATENAME(weekday,[StartDate]),3) as [DT]
--,[H]
--,[Hours]
,FORMAT(sum([Hours]) ,'0.00') as [Hours]
,FORMAT(sum([REG]) ,'0.00') as [REG]
,FORMAT(sum([OT]) ,'0.00') as [OT]
,FORMAT(sum([HOL]) ,'0.00') as [HOL]
--,FORMAT(SUM([REG]*@RATE1 + [OT]*@RATE2 + [HOL]*@RATE3) ,'0000.00') as [EARNED]
FROM (
SELECT
t.[Id]
,tt.[Name] as [TaskType]
,t.[Name] as [EmployeeName]
--,[Identifier]
--,[Completed]
--,[State]
--,substring(convert(varchar(10),[StartDate],126),6,5)
,[StartDate]
,[EndDate]
--,datediff(SECOND,t.[StartDate],t.[EndDate]) as [DUR_s]
--,datediff(SECOND,t.[StartDate],t.[EndDate])/60.0 as [DUR_m]
--,datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 as [DUR_h]
,--FORMAT(
datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0
--,'000.00')
as [Hours]
,CASE ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) WHEN '0' THEN '0' ELSE '1' END as [H]
-- HOLIDAY
,--FORMAT(
CASE WHEN ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) != '0'
THEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0
ELSE 0
END-- ,'000.00')
as [HOL]
-- OVERTIME
,--FORMAT(
CASE WHEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 > ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999) AND (ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) = '0')
THEN (datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0) - ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999)
ELSE 0
END-- ,'000.00')
as [OT]
-- REGULAR
,--FORMAT(
CASE WHEN (ISNULL(( SELECT [taskContent] FROM @policies WHERE [taskName] like '%Holiday%' AND [taskContent]=substring(convert(varchar(10),[StartDate],126),6,5) ),0) != '0')
THEN 0
ELSE CASE WHEN datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0 > ISNULL((SELECT [taskContent] FROM @policies WHERE [taskName] like '%OT Hour Limit%'),9999)
THEN 8
ELSE (datediff(SECOND,t.[StartDate],t.[EndDate])/60.0/60.0)
END
END-- ,'000.00')
as [REG]
--,[LastUpdateTime]
--,[Content]
--,[CustomData]
--,[StateLog]
--,[UserName]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]
WHERE 1=1
AND t.[Completed] = 1
AND t.[Name] = @entityName
AND t.[StartDate] >= @StartDate
AND t.[StartDate] < @EndDate
AND tt.[Name] = 'TC Punch Control Task'
--AND t.[State] = 'Punch Cycle Complete'
----ORDER BY t.[Name], t.[StartDate],t.[EndDate]
) HRS
GROUP BY
-- [EmployeeName]
-- [StartDate]
--,[EndDate]
left(datename(YEAR,[StartDate]),4)+' '+left(datename(MONTH,[StartDate]),3)+' TTL'
--,[H]
--,[Hours]
--,[HOL]
--,[OT]
--,[REG]
ORDER BY 1,2,3
##Reports
##TC Employee Hours [0] (Report)##
| Report Name: | TC Employee Hours |
| Page Size: | 15cm |
| Display in Report Explorer: | checked |
| Visual Printing: | unchecked |
Template:
[Current Hours $2:1,1, 1, 1, 1, 1]
>Date|Day|Hours|REG|OT|Start
{REPORT TASK DETAILS:
T.StartDate,T.StartTime
,=F([T.Duration]/60)
,=if([T.Duration]/60 > 8, 8, [T.Duration]/60)
,=if([T.Duration]/60 > 8, [T.Duration]/60 - 8, 0):
(TST=TC Punch Task) AND T.Completed=False AND T.State="Punched In" AND T.Name="$2":
[=FD('{0}','yyyy-MMM-dd')]|[=FD('{0}','ddd')]|[=F('{2}')]|[=F('{3}')]|[=F('{4}')]|{1}
}
[Previous Hours $2:2,1, 1, 1, 1, 1]
>Date|Day|Hours|REG|OT|HOL
@@TC_EmployeeHours:$1,$2,$3,$4,$5
>>Month| |Hours|REG|OT|HOL
>@@TC_EmployeeHoursTTL:$1,$2,$3,$4,$5
NOTE: The following documents the Actions/Rules that are required for the Javascript Kitchen Display Module. Actions required by a native Entity Screen are not shown, since the JS Module method supersedes the “old” method.
This technique can be used to embed ANY of the modules into SambaPOS. For example, I embedded Kitchen Display, Timeclock, and Reports in this way.
)
