Time Clock - Basic Time Tracking

[QMcKay]

##Time Clock - Basic

Simple Time Clock that allows users (Employee Entities) to Clock In and Clock Out. No custom flow or Navigation mechanisms. Supports Time Tracking, Punch Editing, and Payroll mechanisms (separate Tutorials).

###Setup Quick Links

States for PunchIn and PunchOut
Employee Entities
Time Clock Entity Screen
Automation Commands
Actions
Rules
Rules (extended functionality)
Reports
DB Tools Import Files

[QMcKay]

##States for PunchIn and PunchOut

These States are used to track when an Employee is Clocked In or Clocked Out.

Entity State for Clocked In
Name: PunchIn
State Name: TCStatus
State Type: Entity State

Entity State for Clocked Out
Name: PunchOut
State Name: TCStatus
State Type: Entity State

[QMcKay]

Employee Entities

###Employee Entity Type

V5 Only

Version 5 supports Default States for Entity Types. This sets a “starting State” when a new Entity is created. Set the Default States as follows…

Default States: TCStatus=PunchOut;TCReport=Unloaded

---

###Employee Entity (example)

You can do mass edits to Employee Entities using Batch Entity Editor.

[QMcKay]

##Time Clock Entity Screen

Important Settings

• Entity Screen Name: Time Clock
• Display State: TCStatus
• State Filter: blank
• Select the Employee Entities you want to appear on the screen

---

##Design Mode Elements

We have a single Entity Grid and several Report Viewers (one Report per Employee) on the Time Clock Entity Screen.

Entity Grid Widget

Entity Grid Properties

Name: EmployeeStatus (arbitrary)

Entity Grid Settings

Entity Type: Employees
Display State: TCStatus
Automation Command: TC_LoadEntity
Command Value: {ENTITY NAME}

---

Report Viewer Widgets

Report Viewer Properties

Name: Hours Ovania

Report Viewer Settings

Report Name: Employee Hours
Parameters: Employee,Ovania

The employee name ‘Ovania’ in the Parameters list needs to be set to the proper Entity in each of the Report Viewer Widgets

[QMcKay]

Automation Commands

###Time Clock Button / Navigation Tile

Name: TC_Show Timeclock
Category: Navigation
Header: Time Clock

Navigation Tile Template:

[People:1]
<size 44><bold>Time Clock</bold></size>

@Ovania,Merlin,Jordani,Jenery,Sonia
[='{SETTING:TCStatus $1}'=='Clocked In' ? '$1 ::: ' + '{ENTITY STATE MINUTES BY NAME:Employees.$1:TCStatus}' : '']

[QMcKay]

Actions

Show Time Clock Screen

We use this Action to refresh the Time Clock Screen.

Name: TC_Show Timeclock Screen
Action Type: Navigation Module
Module Name: Entity
Parameter: Time Clock
Hide Header: True

---

###Execute Automation Command

We use this Action to execute other Actions.

Name: TC_ExecAMC
Action Type: Execute Automation Command
Command Value: (blank)
Automation Command Name: [:AMCname]
Background: False
Delay: 0

---

###Show Message

We use this Action to show Messages.

Name: TC_ShowMessage
Action Type: Show Message
Message: [:message]

---

###Ask Question

We use this Action to ask the user to Clock In or Clock Out.

Name: TC_Punch (ask)
Action Type: Ask Question
Question: [:Question]
Buttons: [:Buttons]
Automation Command Name: [:AMCname]
Background Color: [:BGcolor]
Transparent Color: (blank)

---

###Load Entity

We use this Action to load an Employee Entity so we can read and set the Entity Data and State.

Name: TC_LoadEntityBySearch
Action Type: Load Entity
Entity Type Name: [:entityTypeName]
Entity Name: (blank)
Entity Search Value: [:searchValue]

---

###Log Entity State

We use this Action to log the Employee Entity State as PunchIn (Clocked In) or PunchOut (Clocked Out).

Name: TC_LogEntityState
Action Type: Log Entity State
Name: [:logName]
Entity Id: [:entityId]
State Name: [:SGroupName]
Start State: [:StateBeg]
Start State Date: [:SDateBeg]
End State: [:StateEnd]
End State Date: [:SDateEnd]
Custom Data: [:SCustomData]
Async: [:Async]

---

###Update Entity State

We use this Action to set the Employee Entity State to PunchIn or PunchOut.

Name: TC_UpdateEntityState
Action Type: Update Entity State
Entity Type Name: Employees
Entity State Name: TCStatus
Entity State: [:newState]

---

###Update Program Setting

We use this Action to update various Program Settings used to track certain parameters.

Name: TC_Update Program Setting
Action Type: Update Program Setting
Setting Name: [:SettingName]
Setting Value: [:SettingValue]
Update Type: Update
Is Local: False

[QMcKay]

Rules

###Show Time Clock Screen

Name: TC_Show TimeClock Screen
Event Name: Automation Command Executed
Constraints:
Execute Rule if: Matches
Automation Command Name Equals TC_Show Timeclock

TC_Show Timeclock Screen
Constraint: (none)

---

###Load Entity

Name: TC_Load Employee Entity
Event Name: Automation Command Executed
Constraints:
Execute Rule if: Matches
Automation Command Name Equals TC_LoadEntity

TC_LoadEntityBySearch
Constraint: (none)
entityTypeName: Employees
searchValue: [:CommandValue]

TC_ExecAMC
Constraint: (none)
AMCname: TC_Punch

---

###Time Clock Punch (ask)

Name: TC_Punch (ask)
Event Name: Automation Command Executed
Constraints:
Execute Rule if: Matches
Automation Command Name Equals TC_Punch

TC_Punch (ask)
Constraint: (none)
Question: Select a TimeClock Action for {ENTITY NAME}:
Buttons: Clock In=PunchIn:Orange;Gray,Clock Out=PunchOut:White;Gray,Cancel=Cancel:Red;Gray
AMCname: TC_Update Entity State
BGcolor: DarkBlue

---

###Update Entity State

Name: TC_Update Entity State
Event Name: Automation Command Executed
Constraints:
Execute Rule if: Matches All
Automation Command Name Equals TC_Update Entity State
Command Value Not Equals Cancel
Command Value Not Equals {ENTITY STATE:TCStatus}

TC_UpdateEntityState
Constraint: (none)
newState: [:CommandValue]

TC_Update Program Setting
Constraint: (none)
SettingName: TCStatus {ENTITY NAME}
SettingValue: [=('[:CommandValue]'=='PunchIn' ? 'Clocked In' : 'Clocked Out')]

TC_ExecAMC
Constraint: (none)
AMCname: TC_Show Timeclock

---

###Log Entity State

Name: TC_LogEntityState
Event Name: Entity State Updated
Constraints:
Execute Rule if: Matches
Entity Type Name Equals Employees
State Name Equals TCStatus
State Not Equals Cancel

TC_LogEntityState
Constraint: (none)
logName: TimeClock
entityId: [:EntityId]
SGroupName: [:StateName]
StateBeg: [:StartState]
SDateBeg: [:StartStateDate]
StateEnd: [:EndState]
SDateEnd: [:EndStateDate]
SCustomData: ManualPunch~{SETTING:HolidayFlag}
Async: False

[QMcKay]

Rules (extended functionality)

###Set Report Date Filter on Application Started

This Rule is for supporting the Payroll setup and Punch Editor setup, which are separate Tutorials.

Name: TC_Set Report Date Filter on App Started
Event Name: Application Started
Constraints: (none)
Execute Rule if: Matches

TC_UpdateProgramSetting
Constraint:
SettingName: TCDateFilter
SettingValue: This Month

---

###Set Holiday Flag to TRUE

This Rule is for supporting the Payroll setup, which is a separate Tutorial.

Name: TC_SetHolidayFlag TRUE
Event Name: Application Started
Constraints:
Execute Rule if: Matches Any
[=FD('{DATE}','MM-dd')] Matches 01-01
[=FD('{DATE}','MM-dd')] Matches 12-25

TC_UpdateProgramSetting
Constraint:
SettingName: HolidayFlag
SettingValue: Holiday

TC_ShowMessage
Constraint:
Message: Today ([=FD('{DATE}','yyyy')]-[=FD('{DATE}','MM-dd')]) is a Holiday! Employees receive Double Pay!

---

###Set Holiday Flag to FALSE

This Rule is for supporting the Payroll setup, which is a separate Tutorial.

This Rule is identical to the previous Rule except for the Constraint. So you can Clone the above Rule, and simply change the Constraint in this Clone.

The previous Rule has constraint: Matches Any
This Rule has constraint: Not Matches All

Name: TC_SetHolidayFlag FALSE
Event Name: Application Started
Constraints:
Execute Rule if: Not Matches All
[=FD('{DATE}','MM-dd')] Matches 01-01
[=FD('{DATE}','MM-dd')] Matches 12-25

TC_UpdateProgramSetting
Constraint:
SettingName: HolidayFlag
SettingValue: (blank)

[QMcKay]

##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

[QMcKay]

##DB Tools Import Files

Automation (Rules/Actions): !TimeClock_Automation.zip (2.5 KB)

[QMcKay]

… reserved for updates …

[devloic]

@QMcKay

Rules

###Update Entity State

Name: TC_Update Entity State
Event Name: Automation Command Executed
Constraints:
Execute Rule if: Matches
Automation Command Name Equals TC_Update Entity State
Command Value Not Equals Cancel
Command Value Not Equals {ENTITY STATE:TCStatus}

=> Screen capture says Matches All while description says Matches

@QMcKay

Time Clock Punch (ask)

Pasted image927x543 21.6 KB

Name: TC_Punch (ask)Event Name: Automation Command ExecutedConstraints:Execute Rule if: Matches Automation Command Name Equals TC_Punch

TC_Punch (ask)Constraint: (none)Question: Clock In=PunchIn:Orange;Gray,Clock Out=PunchOut:White;Gray,Cancel=Cancel:Red;GrayButtons: [:CommandValue]AMCname: TC_Update Entity StateBGcolor: DarkBlue

=> Description is not correct.

[devloic]

@QMcKay

Automation Command: TC_LoadEntity

@QMcKay
I haven’t setup the Automation Command TC_LoadEntity because it is not defined in this tutorial. Could that be the reason why the reports don’t show up in my Time Clock Screen?

[QMcKay]

@devloic, thanks for catching the errors… corrections have been made.

@devloic

I haven’t setup the Automation Command TC_LoadEntity because it is not defined in this tutorial.

It is not required.

@devloic

Could that be the reason why the reports don’t show up in my Time Clock Screen?

It should have no effect on reporting.

[gokulhirani]

If I want User List to employee entity

Then Is there any way to link both thing

[QMcKay]

Yes, you can link Users to Entities, but that is not what this Tutorial is about. The method you are looking for is in another V4 Topic.

Time Clock: Linked to SambaPOS Users with Custom Reports

It is wise to backup of your database and test this first before putting into production Download Link: Operational Flow: Clock In: Employee enters pin or scans badge to log in to SambaPOS4 Time clock greets employee by name and gives option to Clock In or Cancel If employee chooses Clock In it records a punch and logs them into SambaPOS If employee chooses Cancel it takes them back to the User Log In screen of SambaPOS4 Clock Out: Employee navigates to the Time Clock e…

[rightguys]

Sorry but I think I am having trouble with setting up the Time Clock.

I go to the Navigation screen, I press Time Clock,

and I am immediately led to this screen:

The report does NOT display and when I click on the name, I get sent directly to the Order screen.

I do NOT see this message:

I will show screenshots of my progress (rules, actions, etc.) if you’d like me to.
Thank you.

[QMcKay]

Show your Rules (with all Actions fully expanded) for:

TC_Load Employee Entity
TC_Punch (ask)