Punch Editor for Timeclock

V5 Tutorial
1

Sometimes it is necessary to edit Timeclock Punch Data when we need to change Punch In (Clock In) or Punch Out (Clock Out) dates and/or times. This Tutorial demonstrates a setup for a Punch Editor for that purpose.

Pasted image1184×589 59 KB

Pasted image770×795 41.8 KB

##Quick Links

States
Entity Screen
Scripts
Reports
Custom Entity Screen Widgets
Automation Commands
Actions
Rules

2 Likes
3

#States

These Entity States are used to change the color of the active Entity that we are editing.

##Unloaded

Pasted image727×626 27.3 KB

##[TCReport] Unloaded [Entity State] (State)##

Name (State): Unloaded
State Name (State Group): TCReport
State Type: Entity State
Color: #FFFFFFFF
Display Format: 
Quantity Format:  
Price Format:  
Show on End of Day Report: unchecked
Show on Product Report: unchecked
Show on Ticket: unchecked




##Loaded


Pasted image727×626 27.9 KB




##[TCReport] Loaded [Entity State] (State)##




Name (State): Loaded
State Name (State Group): TCReport
State Type: Entity State
Color: #FFFFA500
Display Format:  
Quantity Format:  
Price Format:  
Show on End of Day Report: unchecked
Show on Product Report: unchecked
Show on Ticket: unchecked



            


            

              
              
              
            


          

          

            

              


                

              
                  
                  
              4
              
            

            

              
#Entity Screen - Punch Editor


We will be using a Custom Entity Screen to perform Punch Edits.  Be sure to select the Employee Entities that you want to see listed on the Punch Editor Entity Screen.


Pasted image727×1394 57.3 KB




##Punch Editor [Employees] (Entity Screen)##




Name: Punch Editor
Ticket Type: Ticket
View Mode: Custom
Search Value Replace Pattern: 



Appearance


Background Image: 
Background Color: #00FFFFFF
Use State Display Format: unchecked
Column Count: 0
Row Count: 0
Button Height: 0
Page Count: 1
Font Size: 50
Header Button Font Size: 0





Entity List (4)


Entity Type: Employees
Display State: TCReport
State Filter: 

Entities:

Ovania
Jenery
Courtney
Nikendra






Details Template (none)(none)



Mappings (1)
Terminal User Role Department Ticket Type Visibility 
*Admin**All



            


            

              
              
              
            


          

          

            

              


                

              
                  
                  
              5
              
            

            

              
#Scripts


We will use a few JScript Functions and SQL Scripts to efficiently handle selecting and editing Entity Punch Data.


##datefunc.datediff()


Calculates time difference between 2 times.


Pasted image727×500 29.6 KB




##DateFunctions [datefunc] (Script)##




Script Name: DateFunctions
Script Handler: datefunc


Script:


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;
}





##ent.UpdateEntityState()


This script will handle batch State changes for Entities.  It can change the State of all Entities at once, or change the State of a single Entity.


Pasted image727×500 29.3 KB




##EntityFunctions [ent] (Script)##




Script Name: EntityFunctions
Script Handler: ent


Script:


function UpdateEntityState(eType,eName,sName,s) {
  var entitylist='';
  var qry="";
  var eTypeId=-1;
  var eCount=0;
  
  qry = "SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+eType+"'";
  eTypeId = sql.Query(qry).First;
  qry = "SELECT COUNT([Id]) FROM [Entities] WHERE [EntityTypeId]="+eTypeId;
  eCount = sql.Query(qry).First;

  qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]="+eTypeId+" ORDER BY [Name]";
  var entities = sql.Query(qry).Delimit(',').All;

  //for (var e=0; e<eCount; e++) {
  //  entitylist += entities[e] + "\r";
  //}
  //return entitylist;

  if (typeof(eName)=="undefined" || eName=='') {
    //update State for all Entities
    for (var e=0; e<eCount; e++) {
      api.Entity(entities[e]).State(sName).Update(s);
    }
    return "All Entities ["+eType+"] updated ["+sName+"] "+s;
  } else {
    //update State for specified Entity
    api.Entity(eName).State(sName).Update(s);
    return "["+eType+"] "+eName+" updated ["+sName+"] "+s;
  }
  
  return 0;
}





##db.getSettingbydb() and db.updaterow()


db.getSettingbydb() retrieves a value from the [ProgramSettingsValues] table, which is where we will be storing SQL UPDATE statements for changing Punch Data.


db.updaterow() executes a stored SQL statement which is obtained via the above function.  It effectively executes the stored statement, which alters Punch Data stored in the [EntityStateLogs] table.


Pasted image727×500 27.7 KB




##DBQuery [db] (Script)##




Script Name: DBQuery
Script Handler: db


Script:


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






##@@EmployeeHoursDailyPE


This is a SQL script to retrieve Entity State Log Data from the [EntityStateLogs] table in the DB, which is where Timeclock data is stored.


Pasted image727×500 43.2 KB




##EmployeeHoursDailyPE [@@EmployeeHoursDailyPE] (Script)##




Script Name: EmployeeHoursDailyPE
Script Handler: @@EmployeeHoursDailyPE


Script:


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


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





            


            

              
              
              
            


                

                      

                        
                        
                      

                

          

          

            

              


                

              
                  
                  
              6
              
            

            

              
#Reports


##PE Punch Editor


This Report will show Entity State Log (Punch) Data for a selected Entity.  It simply calls upon the SQL script @@EmployeeHoursDailyPE to retrieve data, based on the supplied $parameters.


$1 : Entity Type (Employees)
$2 : Entity Name (name of Employee)
$3 : Date Filter Start (shows punch data after this date)
$4 : Date Filter End (shows punch data before this date)
$5 : unused



Pasted image727×500 19 KB




##PE Punch Editor (Report)##




Report Name: PE Punch Editor
Page Size: 15cm
Display in Report Explorer: unchecked
Visual Printing: unchecked


Template:


[#Punch Editor:1,1, 1,48,48, 1]
>Date|Day|Hours|START|END|ID
@@EmployeeHoursDailyPE:$1,$2,$3,$4,$5





            


            

              
              
              
            


                

                      

                        
                        
                      

                

          

          

            

              


                

              
                  
                  
              7
              
            

            

              
#Entity Screen Widgets


Our Custom Entity Screen contains a handful of Widgets for editing Timeclock Punch Data.


Pasted image727×981 52.6 KB




##PE EmployeeStatus [Entity Grid] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Entity Grid
Name: PE EmployeeStatus
X: 65
Y: 2
Height: 705
Width: 200
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (11):###


Automation Command Name: PE Set Punch Entity
Columns: 1
Command Value: {ENTITY NAME}
Disallow Change Entity: unchecked
Display State: TCReport
Entity Type Name: Employees
Font Size: 0
Page Count: 0
Rows: 0
State Filter Name: 
Validation Expression: 




Pasted image727×894 45.5 KB




##PE Hours [Custom Report Viewer] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Custom Report Viewer
Name: PE Hours
X: 275
Y: 0
Height: 700
Width: 655
Zindex: 3
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (5):###


Border Color: White
Parameters: Employee,Ovania (change 'Ovania' to the name of one of your Employee Entities)
Report Name: PE Punch Editor
Setting Mappings: PErowID=Punch Editor.6
PEstart=Punch Editor.4
PEend=Punch Editor.5
Zoom: 115




Pasted image727×954 49.6 KB




##PE Edit Button [Automation Button] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Automation Button
Name: PE Edit Button
X: 955
Y: 0
Height: 170
Width: 500
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (7):###


Button Color: #FF3F3F3F
Caption:   
?Edit Punch for{SETTING:PEEntity}
 
Command Name: PE Edit
Font Size: 32
Image Path: 
Validation: 
Value: 




Pasted image727×840 44.2 KB




##PE Date Filter BEG [Editor Widget] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Editor Widget
Name: PE Date Filter BEG
X: 956
Y: 210
Height: 30
Width: 110
Zindex: 5
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (6):###


Alignment: 
Command Name: 
Edit Mask: 20[0-9][0-9]-[0-9][0-9]-[0-9][0-9]
Font Size: 16
Local Setting Name: PEDateFilterBEG
Refreshing Widgets: PEDateFilterBEG Label




Pasted image727×840 44 KB




##PE Date Filter END [Editor Widget] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Editor Widget
Name: PE Date Filter END
X: 1090
Y: 210
Height: 30
Width: 110
Zindex: 5
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (6):###


Alignment: 
Command Name: 
Edit Mask: 20[0-9][0-9]-[0-9][0-9]-[0-9][0-9]
Font Size: 16
Local Setting Name: PEDateFilterEND
Refreshing Widgets: PEDateFilterEND Label




Pasted image727×954 48.3 KB




##PE Date Filter Button [Automation Button] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Automation Button
Name: PE Date Filter Button
X: 1221
Y: 209
Height: 55
Width: 235
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (7):###


Button Color: #FFE36C09
Caption:  
Apply
Date Filter
Command Name: PE Set Punch Entity
Font Size: 18
Image Path: 
Validation: 
Value: {SETTING:PEEntity}




Pasted image727×1044 58.2 KB




##PEDateFilterBEG Label [Label Widget] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Label Widget
Name: PEDateFilterBEG Label
X: 955
Y: 209
Height: 55
Width: 112
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (13):###


Alignment: Left
Background Color: Transparent
Border Color: #FFF79646
Border Thickness: 1
Font Name: Segoe UI
Font Size: 16
Font Weight: Normal
Foreground Color: #FFF79646
Text: {SETTING:PEDateFilterBEG}
Vertical Alignment: Bottom




Pasted image727×1044 58.1 KB




##PEDateFilterEND Label [Label Widget] (Widget)##

Entity Screen: Punch Editor




###Properties (13):###


Widget Type: Label Widget
Name: PEDateFilterEND Label
X: 1089
Y: 209
Height: 55
Width: 112
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin: 


###Settings (13):###


Alignment: Left
Background Color: Transparent
Border Color: #FFF79646
Border Thickness: 1
Font Name: Segoe UI
Font Size: 16
Font Weight: Normal
Foreground Color: #FFF79646
Text: {SETTING:PEDateFilterEND}
Vertical Alignment: Bottom



            


            

              
              
              
            


          

          

            

              


                

              
                  
                  
              8
              
            

            

              
#Automation Commands


Pasted image727×1288 52.2 KB




##NV PunchEditor [Navigation] (Automation Command)##




Name: NV PunchEditor
Category: Navigation
Button Header: Punch Editor
Color: #FF000000
Font Size: 40
Confirmation: None

Values (0): (none)


Navigation Settings
Symbol: 
Image:  
Auto Refresh: 0
Tile Cache: 0
Navigation Module:  
Nav Module Parameter:  

Template:
```


</details>


<details>
<summary><b><u>Mappings</u></b></summary><table><tr><td><b>Terminal</b> </td><td><b>User Role</b> </td><td><b>Department</b> </td><td><b>Ticket Type</b> </td><td><b>Enabled States</b> </td><td><b>Visible States</b> </td><td><b>Visibility</b> </td></tr><tr><td><code>Server</code></td><td><code>Admin</code></td><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>Display on Navigation</code></td></tr></table></details>


----------

            


            

              
              
              1 Like
            


          

          

            

              


                

              
                  
                  
              9
              
            

            

              
#Actions


Pasted image727×365 20.8 KB




##NV Show Punch Editor Screen [Navigate Module] (Action)##




Action Name: NV Show Punch Editor Screen
Action Type: Navigate Module

###Parameters:###

Module Name: Entity
Parameter: Punch Editor




Pasted image727×425 22.6 KB




##PE Ask Question [Ask Question] (Action)##




Action Name: PE Ask Question
Action Type: Ask Question

###Parameters:###

Question: [:question]
Buttons: [:buttons]
Automation Command Name: [:AMCname]
Background Color: [:color]




Pasted image727×395 19.3 KB




##PE Exec Script [Execute Script] (Action)##




Action Name: PE Exec Script
Action Type: Execute Script

###Parameters:###

Function: [:function]
Command: [:command]
Run In Background: [:runinbackground]




Pasted image727×395 22.5 KB




##PE ExecAMC [Execute Automation Command] (Action)##




Action Name: PE ExecAMC
Action Type: Execute Automation Command

###Parameters:###

Automation Command Name: [:AMCname]
Command Value: [:AMCvalue]
Delay: [:AMCdelay]




Pasted image727×425 26.4 KB




##PE Update Report Widget [Refresh Custom Report Widget] (Action)##




Action Name: PE Update Report Widget
Action Type: Refresh Custom Report Widget

###Parameters:###

Widget Name: PE Hours
Report Name: PE Punch Editor
Report Parameters: [:parms]
Date Filter: [:dateFilter]




Pasted image727×425 25.8 KB




##PE Update Setting Global [Update Program Setting] (Action)##




Action Name: PE Update Setting Global
Action Type: Update Program Setting

###Parameters:###

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




Pasted image727×425 25.3 KB




##PE Update Setting Local [Update Program Setting] (Action)##




Action Name: PE Update Setting Local
Action Type: Update Program Setting

###Parameters:###

Setting Name: [:SettingName]
Setting Value: [:SettingValue]
Update Type: Update
Is Local: True



            


            

              
              
              
            


          

          

            

              


                

              
                  
                  
              10
              
            

            

              
#Rules


Pasted image727×1754 105 KB




##NV Show Punch Editor Screen [Automation Command Executed] (Rule)##




Rule Name: NV Show Punch Editor Screen
Event Name: Automation Command Executed
Rule Tags: punchEditor

Custom Constraint List (1):

Execute Rule if: Matches


Automation Command NameEqualsNV PunchEditor


##Actions (8):##



PE Update Setting Global

Constraint: (none)


SettingName: PEEntity
SettingValue: {REPORT SQL DETAILS:SELECT TOP 1 esi.[Name] as [EntityName] FROM [dbo].[EntityScreenItems] esi JOIN [EntityScreens] es on es.[Id]=esi.[EntityScreenId] JOIN [Entities] e on e.[Id]=esi.[EntityId] JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId] WHERE et.[Name]='Employees' AND es.[Name]='Punch Editor' ORDER BY esi.[SortOrder]:F.EntityName}



PE Update Setting Global

Constraint: ‘[:CommandValue]’ != ‘’


SettingName: PEEntity
SettingValue: [:CommandValue]



PE Update Setting Local

Constraint: (none)


SettingName: PEDateFilterBEG
SettingValue: [='{DATE:yyyy-MM}'+'-01']



PE Update Setting Local

Constraint: (none)


SettingName: PEDateFilterEND
SettingValue: [=ADM('{DATE:yyyy-MM}'+'-01',1)]



PE Update Setting Local

Constraint: (none)


SettingName: PEDateFilterEND
SettingValue: [='{:PEDateFilterEND}'.substr(0,10)]



PE ExecAMC

Constraint: (none)


AMCname: PE Unload Entities and Load Selected Entity
AMCvalue:  
AMCdelay:  



NV Show Punch Editor Screen

Constraint: (none)





PE Update Report Widget

Constraint: (none)


parms: Employee,{SETTING:PEEntity},{SETTING:PEDateFilterBEG},{SETTING:PEDateFilterEND}
dateFilter:  


##Mappings##



Mappings
Terminal User Role Department Ticket Type 
*Admin**




Pasted image727×1182 61.9 KB




##PE Set Punch Entity [Automation Command Executed] (Rule)##




Rule Name: PE Set Punch Entity
Event Name: Automation Command Executed
Rule Tags: punchEditor

Custom Constraint List (1):

Execute Rule if: Matches


Automation Command NameEqualsPE Set Punch Entity


##Actions (4):##



PE Update Setting Global

Constraint: (none)


SettingName: PEEntity
SettingValue: [:CommandValue]



PE ExecAMC

Constraint: (none)


AMCname: PE Unload Entities and Load Selected Entity
AMCvalue:  
AMCdelay:  



NV Show Punch Editor Screen

Constraint: (none)





PE Update Report Widget

Constraint: (none)


parms: Employee,{SETTING:PEEntity},{SETTING:PEDateFilterBEG},{SETTING:PEDateFilterEND}
dateFilter:  


##Mappings##



Mappings
Terminal User Role Department Ticket Type 
****




Pasted image726×1012 54 KB




##PE Unload Entities and Load Selected Entity [Automation Command Executed] (Rule)##




Rule Name: PE Unload Entities and Load Selected Entity
Event Name: Automation Command Executed
Rule Tags: punchEditor

Custom Constraint List (1):

Execute Rule if: Matches


Automation Command NameEqualsPE Unload Entities and Load Selected Entity


##Actions (2):##



PE Exec Script

Constraint: (none)


function: ent.UpdateEntityState('Employees','','TCReport','Unloaded')
command:  
runinbackground: False



PE Exec Script

Constraint: (none)


function: ent.UpdateEntityState('Employees','{SETTING:PEEntity}','TCReport','Loaded')
command:  
runinbackground: False


##Mappings##



Mappings
Terminal User Role Department Ticket Type 
*Admin**




Pasted image727×1784 99.3 KB




##PE Edit [Automation Command Executed] (Rule)##




Rule Name: PE Edit
Event Name: Automation Command Executed
Rule Tags: punchEditor

Custom Constraint List (1):

Execute Rule if: Matches


Automation Command NameEqualsPE Edit


##Actions (8):##



PE Update Setting Global

Constraint: (none)


SettingName: PErowID
SettingValue: {:PErowID}



PE Update Setting Global

Constraint: (none)


SettingName: PEnewStart
SettingValue:  



PE Update Setting Global

Constraint: (none)


SettingName: PEnewEnd
SettingValue:  



PE Update Setting Global

Constraint: (none)


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



PE Update Setting Global

Constraint: (none)


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



PE Update Setting Global

Constraint: (none)


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



PE Ask Question

Constraint: (none)


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



NV Show Punch Editor Screen

Constraint: (none)




##Mappings##



Mappings
Terminal User Role Department Ticket Type 
*Admin**




Pasted image727×1306 74 KB




##PE Edit Commit [Automation Command Executed] (Rule)##




Rule Name: PE Edit Commit
Event Name: Automation Command Executed
Rule Tags: punchEditor

Custom Constraint List (2):

Execute Rule if: Matches


Automation Command NameEqualsPE Edit Commit
Command ValueEqualsy


##Actions (4):##



PE Update Setting Global

Constraint: (none)


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



PE Exec Script

Constraint: ‘{SETTING:PEnewStart}’!=‘’


function: db.updaterow('PEQuery')
command:  
runinbackground:  



PE Update Setting Global

Constraint: (none)


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



PE Exec Script

Constraint: ‘{SETTING:PEnewEnd}’!=‘’


function: db.updaterow('PEQuery')
command:  
runinbackground:  


##Mappings##



Mappings
Terminal User Role Department Ticket Type 
*Admin**



            


            

              
              
              
            


                

                      

                        
                        
                      

                

          

          

            

              



              
                  
                  
              11
              
            

            

              
… reserved for updates …

            


            

              
              
              
            


          

          

            

              



              
                  
                  
              12
              
            

            

              
… reserved for updates …

            


            

              
              
              
            


          

          

            

              


                

              
                  
                  
              14
              
            

            

              
Hey @QMcKay

Ok I have been back and forward all day in the amazing Punch Editor Tutorial.


There is only little thing that puzzles me:

What is the significance of the Parameter Value in the Properties of the Report Viewer Widget below?



Qparamters.JPG784×382 47.7 KB



I see there is a RULE to Refresh this Widget and the Rule posts the 5 parameters required.  So does this Rule “post” just override the Values shown above?


This Rule calls & refreshes this Widget with the following Parameters WHICH are sent through to the Script:


PE Update Report Widget


Constraint: (none)
parms:  Employee,{SETTING:PEEntity},{SETTING:PEDateFilterBEG},{SETTING:PEDateFilterEND} 



Correct? Thanks…

            


            

              
              
              
            


          

          

            

              



              
                  
                  
              15
              
            

            

              
Sorry let me rephrase you said rule but you posted an action so I was mistaken with my explanation let me go again.  One moment.,…



He is updating the report widget so it shows data for specific employee with specific date filter for start and end date.


The parameters you posted in that action are not sent through a script they simply update the widget to show the report for those parameters. In fact you could just leave those parameters blank it would work fine. The data he is passing to the script is captured from the Entity Button when pressed.   PS: Correct me if I am wrong on this @QMcKay




The parameter Mappings is set so when you click on a specific cell it will pass the information from that cell into the Setting he defined. When you click edit punch it will pull info from those settings… it displays it in the ask question action.


Reports by default are just reports they are not tables with cells, but by adding # to the report template like [#report:1,1] turns it into a visual table with cells and rows that can be interacted with.  You use Setting mappings to map a program setting to a specific row and in turn when you click on a cell in that row it will map that data into the Program Setting. You can then use events and actions to interact with that data.

            


            

              
              
              
            


          

          

            

              



              
                  
                  
              16
              
            

            

              

Correct.


The Parameters in the Report Widget Settings are simply to set a starting value.  The Refresh Action, when fired, will override these values.


The SQL script accepts 4 parameters, but only 2 are required, while the others are optional and will receive “default” values if left undefined.  This is handled in the SQL Script itself.





Well, yes and no.  If we fired the Action without the Parameters, the Widget would refresh, but nothing would change.  The idea is to update the Parameters and override the static parameter assignment in the Widget Settings.


This is done to update/change the Entity and the Start and End Date filters, which in turn changes the $1,$2,$3,$4 values in the Report, which in turn changes the @1,@2,@3,@4 values in the @@SQL Script.  New data is pulled via SQL, and returned to the Report.

            


            

              
              
              2 Likes
            


          

          

            

              



              
                  
                  
              17
              
            

            

              
This was probably one of the best examples of customizing SambaPOS we have.  Shows some very advanced techniques.  Its really good practice for anyone wanting to learn how scripting can improve your configuration.

            


            

              
              
              1 Like
            


          

          

            

              



              
                  
                  
              18
              
            

            

              

Perhaps I explained it wrong and I tried to correct it just now.  What I meant is the parameters you see at the moment are not read and passed through the script.  They are there because of the script.   I mean your not reading those values from that part of the widgets settings. I meant you could leave them blank in the widget settings when creating the widget.

            


            

              
              
              
            


          

          

            

              



              
                  
                  
              19
              
            

            

              
Is it okay to have both kendash’s and yours installed simultaneously?

            


            

              
              
              
            


          

          

            

              



              
                  
                  
              20
              
            

            

              

Yes, that is correct.


When any of the buttons on the Screen are pressed (other than the “Edit Punch” button), it calls Rules that read the Entity Grid selected Entity, and the Start/End Date filter (Editor Widget) values.  Then it calls the Refresh Custom Report Viewer Action and feeds all those values as Parameters back into the Report, and refreshes the data returned by the SQL Script.


When the “Edit Punch” button is pressed, a Rule reads the Setting Mappings values (i.e. {SETTING:PErowID}, etc.) for the highlighted Row, as well as the selected Entity.  Then it prompts for new Punch data, runs a SQL UPDATE statement, then calls the Refresh Custom Report Viewer Action using the Parameters for the selected Entity and the Start/End Date filters (Editor Widgets).  The SQL SELECT Script is then invoked, which in turn passes it’s return data back to the Report.


We are not editing data directly in a table.  Instead, we do this:


    

  • 

    SELECT data (based on Entity, and Date Filters).  This returns rows to the #Report for viewing only.
    

    • 

  • 

    UPDATE data (based on Setting Mappings for the selected row such as {:PErowID}).  This directly alters a single row of data in the database, and more specifically, changes only 2 fields of data for that row.  This is done via a separate SQL Statement that is not part of the same SQL Script that reads the data.  The Update statement is fired using an Execute Script Action.
    

    • 

  • 

    SELECT data again (based on Entity, and Date Filters), which is invoked by the Refresh Custom Report Viewer Action.  This returns rows to the #Report for viewing only, but since the update statement occurred just prior to this, the data returned is different.
    

    •