Punch Editor for Timeclock

[QMcKay]

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

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

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

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

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

---