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.
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)