View source for Rollback HandPunch Templates

[[image:HandPunch.png|right]]

{{Advanced}}

When an existing HandPunch is replaced with a new HandPunch, there can be a difference in the calibration from the existing to the new clock. A hand template that previously worked can now have a problem being used at the new clock. It is not the clock that is causing the issue, but the hand template. This occurs because the hand template adjusts to the clocks' gradual change in calibration every time an employee punches. If a small number of employees are affected, it is easiest to remove and [[HandPunch Enrollment|re-enroll]] the employees. If a large number of employees are affected then the templates can be rolled back to an older template. This is possible because the hand template is retrieved from the HandPunch for every punch that is made at the clock. The rollback procedure needs to be performed in [[SQL Management Studio]].

It is best to rollback templates only for employees that were having biometric issues. An example of a query that selects TRY AGAIN or ID REFUSED clock punches is:

 <nowiki>
SELECT 
	*
FROM
	RawPunch 
WHERE 
	RawPunchType = 3 OR
	RawPunchType = 6</nowiki>

This is a more advanced query that limits the results to a date range and to a single time clock:

 <nowiki>
SELECT 
	*
FROM
	RawPunch 
WHERE 
	(RawPunchType = 3 OR
	RawPunchType = 6) AND
	RawPunchDateTime > '05/05/2015 22:00' AND
	RawPunchDateTime < '05/06/2015 09:00' AND
	FkTimeClockID = 876861399</nowiki>

Once the subset of employees have been identified then the full query needs to update the Employee Clock ID (EmployeeClockID) table with the template information from the Raw Punch table (RawPunch), joining on the employee key (FkEmployeeID), and ensuring that we use only successful punches (RawPunchType = 7) from a given date range (RawPunchDateTime), and finally, filtering the scope of this query to the employees returned in the above query.

 <nowiki>
UPDATE
	EmployeeClockID
SET
	HandPunchTemplate0 = RawPunch.HandPunchTemplate0,
	HandPunchTemplate1 = RawPunch.HandPunchTemplate1,
	HandPunchTemplate2 = RawPunch.HandPunchTemplate2,
	HandPunchTemplate3 = RawPunch.HandPunchTemplate3,
	HandPunchTemplate4 = RawPunch.HandPunchTemplate4,
	HandPunchTemplate5 = RawPunch.HandPunchTemplate5,
	HandPunchTemplate6 = RawPunch.HandPunchTemplate6,
	HandPunchTemplate7 = RawPunch.HandPunchTemplate7,
	HandPunchTemplate8 = RawPunch.HandPunchTemplate8
FROM
	EmployeeClockID
INNER JOIN
	RawPunch
ON
	EmployeeClockID.FkEmployeeID = RawPunch.FkEmployeeID
WHERE
	RawPunch.RawPunchType = 7 AND
	RawPunch.RawPunchDateTime > '02/01/2015' AND
	RawPunch.RawPunchDateTime < '03/01/2015' AND
	RawPunch.HandPunchTemplate0 <> 0 AND
	RawPunch.HandPunchTemplate0 <> 255 AND
	EmployeeClockID.FkEmployeeID IN

(SELECT 
	FkEmployeeID
FROM
	RawPunch 
WHERE 
	(RawPunchType = 3 OR
	RawPunchType = 6) AND
	RawPunchDateTime > '05/05/2015 22:00' AND
	RawPunchDateTime < '05/06/2015 09:00' AND
	FkTimeClockID = 876861399)</nowiki>

Note that we are using the templates from successful punches made on any time clock. The rollback procedure is best done for a range of one month, moving back a month at a time.

See also

* [[HandPunch]]