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


#1

A fully working Punch Editor setup …

##FLOW


##States##
Used to indicate which Entity we are viewing/Editing.


##Entity Screen#


##Widgets##

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
Code:

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
BEGIN
set @StartDate= left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'
set @EndDate  = dateadd(Month,1,@StartDate)
END

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

-- NOTHING TO SET BEYOND HERE

SELECT
 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)
,sl.[Id]
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]

##Report##
Displays Punch Data from SQL Script.

:exclamation: We need 1 Report per Employee.

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

[#Punch Editor:1,1, 1,48,48, 1]
>Date|Day|Hours|START|END|ID
@@EmployeeHoursDailyPE:Employee,Myrna

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

Name: DBQuery
Handler: db
Code:

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);
  ql.ExecSql(qry);
}

##JScript##
Calculates the time difference in Hours between 2 dates

Name: DateFunctions
Handler: datefunc
Code:

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

Automation Command


##Actions##

Ask Question

Execute Automation Command

Execute Script

Set Widget Value

Update Entity State

Update Program Setting


##Rules##

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

Contains:

Scripts
Reports
States
Entity Screen
Automation

PunchEditor.zip (7.0 KB)


unlisted #2

listed #3

#4

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