Time Clock - Punch Editor (for changing Clock In/Out Date and Time)

A fully working Punch Editor setup …


Used to indicate which Entity we are viewing/Editing.

##Entity Screen#


Entity Grid

Custom Report Viewer

Name: PE Hours
Report Name: PE Myrna
Setting Mappings:

PErowID=Punch Editor.6
PEstart=Punch Editor.4
PEend=Punch Editor.5

Edit Punch Automation Command Button

Command name value: PE Edit
Value: StartStateDate
Caption: Edit Punch for<linebreak/><bold>{SETTING:PEEntity}</bold>

This Month / Past Month Automation Command Buttons

This Month

Command name value: PE Set Punch Date Filter
Value: This Month
Caption: [=('{SETTING:PEDateFilter}'=='This Month' ? '<bold><color Orange>' : '')]This\rMonth[=('{SETTING:PEDateFilter}'=='This Month' ? '</color></bold>' : '')]

Past Month

Command name value: PE Set Punch Date Filter
Value: Past Month
Caption: [=('{SETTING:PEDateFilter}'=='Past Month' ? '<bold><color Orange>' : '')]Past\rMonth[=('{SETTING:PEDateFilter}'=='Past Month' ? '</color></bold>' : '')]

##SQL Script##
Pulls Punch Data from [EntityStateLogs] for an Employee based on a Date Range

Name: EmployeeHoursDailyPE
Handler: @@EmployeeHoursDailyPE

declare @StartDate datetime = convert(varchar(25),'{Start}',126)
declare @EndDate datetime = convert(varchar(25),'{End}',126)
set @EndDate  = dateadd(Hour,1,@EndDate)

-- if {Start} and {End} are less than 2 days apart, set the date range to 1 Month
IF DateDiff(DAY, @StartDate, @EndDate) < 2
set @StartDate= left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'
set @EndDate  = dateadd(Month,1,@StartDate)

-- set Employee Name from Report Parameter
declare @Employee varchar(20) = '@2'
-- set Entity Type from Report Parameter, i.e. Employee
declare @EntityType varchar(20) = '@1'
declare @Clockin varchar(20) = 'PunchIn'


 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]
, convert(varchar(19),sl.[StartStateDate],126)
, convert(varchar(19),sl.[EndStateDate],126)
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
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]

Displays Punch Data from SQL Script.

:exclamation: We need 1 Report per Employee.

Name: PE Myrna, PE Julia, PE Emre, etc.
Page Size: 15cm

[#Punch Editor:1,1, 1,48,48, 1]

Pulls the UPDATE Query from [ProgramSettingValues] and Executes the Query to alter Punch Data in [EntityStateLogs]

Name: DBQuery
Handler: db

function getSettingbydb(settingname) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+settingname+"'";
  var r = sql.Query(qry).First;
  return r;

function updaterow(stmt) {
  var qry = getSettingbydb(stmt);

Calculates the time difference in Hours between 2 dates

Name: DateFunctions
Handler: datefunc

function datediff(d1,d2,x) {
  var x='s';
  var diff=0;
  // 2015-03-01T23:17:45.000
  // 01234567890123456789012
  var t1 = new Date(d1.substr(0,4), d1.substr(5,2), d1.substr(8,2), d1.substr(11,2), d1.substr(14,2), d1.substr(17,2), 0);
  var t2 = new Date(d2.substr(0,4), d2.substr(5,2), d2.substr(8,2), d2.substr(11,2), d2.substr(14,2), d2.substr(17,2), 0);
  diff = t2.getTime() - t1.getTime();
  diff = diff/1000 /60/60;
  return diff;


Automation Command


Ask Question

Execute Automation Command

Execute Script

Set Widget Value

Update Entity State

Update Program Setting


NV Show Punch Editor Screen

PE Unload Entities and Load Selected Entity

PE Set Punch Entity

PE Set Report Date Filter

PE Edit

SettingName: PErowID
SettingValue: {:PErowID}

SettingName: PEnewStart
SettingValue: ``

SettingName: PEnewEnd
SettingValue: ``

SettingName: PEnewStart
SettingValue: [?Enter new Start;;{SETTING:PEstart}]

SettingName: PEnewEnd
SettingValue: [?Enter new End;;{SETTING:PEend}]

SettingName: PEduration
SettingValue: {CALL:datefunc.datediff('{SETTING:PEnewStart}','{SETTING:PEnewEnd}','s')}

Question: Editing Time Clock Punch Data for {SETTING:PEEntity} ...<linebreak/><linebreak/><font Consolas>ID : {SETTING:PErowID}<linebreak/>Start : {SETTING:PEnewStart}<linebreak/>End : {SETTING:PEnewEnd}<linebreak/>Hours : {SETTING:PEduration}</font><linebreak/><linebreak/>Do you want to Commit these changes?
buttons: Yes=y:Green;Gray,No=n:Red;Gray
AMCname: PE Edit Commit
Color: Black

PE Edit Commit

SettingName: PEQuery
SettingValue: UPDATE [EntityStateLogs] SET [StartStateDate]='{SETTING:PEnewStart}' WHERE [Id]={SETTING:PErowID}

Constraint: '{SETTING:PEnewStart}'!=''
function: db.updaterow('PEQuery')

SettingName: PEQuery
SettingValue: UPDATE [EntityStateLogs] SET [EndStateDate]='{SETTING:PEnewEnd}' WHERE [Id]={SETTING:PErowID}

Constraint: '{SETTING:PEnewEnd}'!=''
function: db.updaterow('PEQuery')

##DB Tools Import Files##


Entity Screen

PunchEditor.zip (7.0 KB)

Opening this up again it has some great examples in it.

1 Like