Timeclock Implementation Requirements - Discussion

[Jesse]

Nevermind I understand it now… It is showing all time between the states… I was thinking of it wrong as a time clock… but the system does not know its a time clock its just changing states…I get it now! yes its working beautifully!

I also understand Name… we can log Tickets… TimeClock functions, Tables… and we can name it this way. EntityId is what assigns it to the Entity so we do not need an actual name here.

I am not sure how to use Custom Data yet…Maybe a place to hold accumulated time?

Also what exactly is Async doing?

[QMcKay]

Different crash:

-----------------------------
[General Info]

Application: SambaPOS
Version:     4.1.65
Region:      en
DB:          SQ
Machine:     ACERROCKET
User:        Quentin
Date:        2014-10-05
Time:        16:49:51

User Explanation:

Quentin said ""
-----------------------------

[Exception Info 1]

Top-level Exception
Type:        System.Data.Entity.Infrastructure.DbUpdateException
Message:     An error occurred while updating the entries. See the inner exception for details.
Source:      EntityFramework
Stack Trace: at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at Samba.Infrastructure.Data.SqlData.EFWorkspace.CommitChanges()
   at Samba.Persistance.Implementations.EntityDao.LogEntityState(String name, Int32 entityId, String stateName, String startState, DateTime startStateDate, String endState, DateTime endStateDate, String customData)
   at Samba.Addon.CustomReports.ActionProcessors.LogEntityState.Log(ActionData actionData)
   at Samba.Addon.CustomReports.ActionProcessors.LogEntityState.<>c__DisplayClass1.<Process>b__0()
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
   at System.Windows.Threading.DispatcherOperation.InvokeImpl()
   at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Threading.DispatcherOperation.Invoke()
   at System.Windows.Threading.Dispatcher.ProcessQueue()
   at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
   at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
   at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
   at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
   at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
   at System.Windows.Threading.Dispatcher.Run()
   at System.Windows.Application.RunDispatcher(Object ignore)
   at System.Windows.Application.RunInternal(Window window)
   at System.Windows.Application.Run(Window window)
   at Samba.Presentation.App.Main()

Inner Exception 1
Type:        System.Data.UpdateException
Message:     An error occurred while updating the entries. See the inner exception for details.
Source:      System.Data.Entity
Stack Trace: at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()

Inner Exception 2
Type:        System.Data.SqlClient.SqlException
Message:     The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Source:      .Net SqlClient Data Provider
Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

-----------------------------

[Assembly Info]

Samba.Services, Version=1.0.0.0
mscorlib, Version=4.0.0.0
System, Version=4.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
WindowsBase, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
PresentationFramework, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
FluentValidation, Version=3.4.0.0
Samba.Domain, Version=1.0.0.0
DevExpress.Xpf.Grid.v14.1, Version=14.1.6.0
DevExpress.Xpf.Grid.v14.1.Core, Version=14.1.6.0
PresentationCore, Version=4.0.0.0
Stateless, Version=1.0.0.0
System.Core, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
System.Windows.Forms, Version=4.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
Samba.Localization, Version=1.0.0.0
Microsoft.CSharp, Version=4.0.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
Samba.Persistance, Version=1.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
DevExpress.Xpf.Core.v14.1, Version=14.1.6.0
PropertyTools, Version=2012.4.14.1

-----------------------------

[System Info]

Operating System
-Microsoft Windows 7 Home Premium
--CodeSet = 1252
--CSDVersion = Service Pack 1
--CurrentTimeZone = -360
--FreePhysicalMemory = 13279168
--OSArchitecture = 64-bit
--OSLanguage = 1033
--ServicePackMajorVersion = 1
--ServicePackMinorVersion = 0
--Version = 6.1.7601

Machine
-ACERROCKET
--Manufacturer = Acer
--Model = Aspire E1-531
--TotalPhysicalMemory = 17008279552
--UserName = AcerRocket\Quentin

-----------------------------

Haha… my fault (sort of):

[Jesse]

Get yours working yet? Mine is working fine now. But I did notice some crash behavior if I put wrong thing in Name. Posting Screens of my Setup…

My rule:

Action:

States:

Here is how I am changing States. I am matching Entities with user Logins… and capturing the Login of user to capture the entity state…

When it loads entity at User Login:

I handle Clock Out by a pin entry system from a custom entity screen which will also have the HTML viewer widget to view schedule request time off etc through timetrex… The pin entry is a Task Type and a Task Type Editor Widget… I put command of Time Punch into the Widget… So command Value becomes the Entity that was loaded.

And finally just for completeness here is the Button that I have mapped to Ticket to switch to the TIme Clock entity screen.

Right now break and lunch I have not defined yet. I just wanted to test the simple stuff first. I also have not started the TimeTrex integration although I think I know where to start now.

What is all of this doing for me? When a user logs in it greets them by name with a question asking if they wish to clock in or not… if they choose not it will log out and not let them access to terminal. IF they choose yes… it changes their state to clocked in and Blue…

When they decide to logout of SambaPOS it will not clock them out… this lets other employees use it or clock in… They would clock out by pressing Time clock button and entering their pin then be presented by name with a question asking if they want to clock out, go to break, go to lunch.

I set the State logging to track this… and so far it looks like its working great! next is building the report.

[Jesse]

If anyone wants to test this… I have created it with unique naming so I can export it with Database Tools let you try it.

I will clean it up and finalize it with reports and package it as a Prebuilt Database Tools download from the Prebuilt Database forum catagory. Hopefully I can finish this today. This would be a working basic timeclock solution for testing

I will wait until this release of Samba is official however… so the first release will just be inside this thread… I dont want to release any unnoficial into Prebuilt Database catagory yet.

[Jesse]

Oh almost forgot… reason for state colors is for future use with managment screen to view employee status… in or out. And now I can put the report with it to view Time on break, time on lunch, time clocked in etc

For me this will all just be visual reference… my actual real payroll time keeping will be done with TimeTrex. Eventually someone might be able to use this for TimeKeeping… its certainly possible… I however feel it could be prone to accidental tampering and error so I will wait for a more mature solution before I trust it with my complete time keeping. However this is great function and I will definitely use it as described and incorporate it with other things as well…

[emre]

It is holiday here. You won’t believe how I’m implementing these on a small town cafe, behind a Internet connection that frequently drops Sorry for just adding features with very few introduction.

I’m uploading a new version (if I can) with some improvements. If you can add some requests about custom reporting I can prepare it and post template here.

Home - SambaPOS | Restaurant POS Software

PS: If table structure seems fine I can release it.

[Jesse]

@emre

If you can add some requests about custom reporting I can prepare it and post template here.

Just now diving into the reports… took me a bit to get the state logging going About 50 crashes and some other mistakes I will not mention haha

PS: What would a use for CustomData be? I thought it could store accumulated time in my case… I cannot seem to get it to store anything but 0

[emre]

Don’t care about Custom Data for now. We’ll use it later.

[Jesse]

@emre

Sorry for just adding features with very few introduction.

To be honest this kind of makes it fun lol

[QMcKay]

Here’s a report, even calculating Estimated Pay based on Entity Custom Data Field called Salary. Also showing a possible use for [EntityStateLogs].[CustomData] when using the log for a Timeclock.

You will need to alter the WHERE Criteria to match the Entity Type Name [EntityName] (Employee) and (optionally) the EntityStateLog [StartState] (PunchIn) and (optionally) the EntityStateLog [Name] (TimeClock) and (optionally) the State [GroupName] (Epunch) to apply to your setup.

Also, change the SUBSTRING function to properly locate the Salary Value in your [Entities] table (the lines are commented out now so the Query won’t crash when you use it).

use [SambaPOS4]
SELECT
 sl.[Id] as [SLid]
,e.[Id] as [EntId]
,e.[Name] as [EntName]
,sb.[GroupName] as [SGroup]
,sl.[StartState] as [StateBegin]
,sl.[EndState] as [StateEnd]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])/3600),2)+':'+right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%3600/60),2)+':'+right('00'+convert(varchar(5),(DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%60)),2) as [DurationHMS]
,right('00000'+convert(varchar(5),convert(decimal(5,2),convert(decimal(5,2),DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60)/60)),5) as [DurationDEC]
--,replace(SUBSTRING(e.[CustomData],64,3),'"','') as [Rate]
--,convert(decimal(5,2),convert(decimal(5,2),DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60)/60) * convert(decimal(5,2),replace(SUBSTRING(e.[CustomData],64,3),'"','')) as [PayEst]
,sl.[Name] as [SLName]
,sl.[CustomData] as [SCustomData]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
left join [States] sb on sb.[Name]=sl.[StartState]
left join [States] se on se.[Name]=sl.[EndState]
WHERE 1=1
and et.[EntityName]='Employee'
--and sl.[Name]='TimeClock'
--and sb.[GroupName]='Epunch'
and sl.[StartState]='PunchIn'
and sl.[StartState]<>sl.[EndState] 
ORDER BY e.[Name], sl.[StartStateDate], sl.[EndStateDate]

[emre]

What is PunchIn - PunchIn ? I mean rows that start state equals end state.

PS: Uploaded an update

Home - SambaPOS | Restaurant POS Software

[QMcKay]

That is from an Error in my implementation… I’m make no claims the log data is correct yet

[Jesse]

What is changed in this update? Just some reporting stuff? The action and rule are the same and its functioning the same.

[Jesse]

@QMcKay why is the report coming up blank for me… I changed it accordingly… Its not finding any valid rows…I know I am missing something. There is no From EntityStateLogs in that… where would I insert this? Nevermind I found it… still cant figure out why mine is coming up blank

OK just add that to one of my “Other Mistakes” /slap forehead I had Clock In and Clock Out it was Clocked In and Clocked Out

And BAM the beautiful report… Thank you @QMcKay Now to test salary like you suggested…

I think I will change mine to PunchIn and PunchOut it sounds more fitting.

[Jesse]

Beginning the building of the report. This is just a complete copy of above… I will start formatting and editing it now as I do not need to see all of the fields etc. Thank you @QMcKay for getting me started with the report.

@emre I will suggest some stuff for reports once I build something with sql to output a design I like. I am not sure what all I want in it yet.

I know a few things I want I just dont know where yet.

1. Duration HMS and Duration DEC for sure… (May not be named this)
2. Entity Name
3. Current State…Starting State, Ending State,
   These are stuff I know I will need. I will know more specifics after I play with it. I am going to compare to reports in TimeTrex as those are very nice reports.

[QMcKay]

@Jesse

still cant figure out why mine is coming up blank

I just made a whole pile of edits to the SQL, so you might want to look at the new version which uses [EntityStateLogs] as the Primary Table instead of [Entities] (much, much better, the way it should be)…

use [SambaPOS4]
SELECT
 sl.[Id] as [SLid]
,e.[Id] as [EntId]
,e.[Name] as [EntName]
,sb.[GroupName] as [SGroup]
,sl.[StartState] as [StateBegin]
,sl.[EndState] as [StateEnd]
,sl.[StartStateDate] as [SDateBegin]
,sl.[EndStateDate] as [SDateEnd]
,right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])/3600),2)+':'+right('00'+convert(varchar(5),DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%3600/60),2)+':'+right('00'+convert(varchar(5),(DateDiff(s, sl.[StartStateDate], sl.[EndStateDate])%60)),2) as [DurationHMS]
,right('00000'+convert(varchar(5),convert(decimal(5,2),convert(decimal(5,2),DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60)/60)),5) as [DurationDEC]
--,replace(SUBSTRING(e.[CustomData],64,3),'"','') as [Rate]
--,convert(decimal(5,2),convert(decimal(5,2),DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60)/60) * convert(decimal(5,2),replace(SUBSTRING(e.[CustomData],64,3),'"','')) as [PayEst]
,sl.[Name] as [SLName]
,sl.[CustomData] as [SCustomData]
FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
left join [States] sb on sb.[Name]=sl.[StartState]
left join [States] se on se.[Name]=sl.[EndState]
WHERE 1=1
and et.[EntityName]='Employee'
--and sl.[Name]='TimeClock'
--and sb.[GroupName]='Epunch'
and sl.[StartState]='PunchIn'
and sl.[StartState]<>sl.[EndState] 
ORDER BY e.[Name], sl.[StartStateDate], sl.[EndStateDate]

Now I just need to follow the implementation by @Jesse to get my data corrected.

[Jesse]

This works… the only thing I would change when building it as a report to view in Samba is how it reports the duration… it should show a duration for Clocked In and a duration for Clocked Out…

IE: this report shows the duration after clockout until clock in… and it shows the duration from clock in to clock out… but it does not distinguish which is which… For a Time Clock implementation of this state logging we would need that distinction for the actual report. So in a way the first report was better specifically for a Time Clock. Maybe we can keep tweaking it

[QMcKay]

Add this to the criteria:

and sl.[StartState]='PunchIn'

I had removed that from the Criteria because I don’t yet understand the data I’m looking at (and it could be useful for something).

[Jesse]

Worked! I am learning a ton about sql just from this. Your teaching me the way @QMcKay

Basically what I did was Created 2 states of the GroupCode TCStatus… Clocked In and Clocked out… so its recording the duration between each change… this means it also records the duration between Clocked out and Clocked in… IE: the non company time. We just filter that time. But this means we could technically introduce a way to modify it… if someone makes a punch error… I am not sure how to go about it yet… but it should be possible

[QMcKay]

Did we find out the nature of Async yet? I assume it means asynchronous, but what effect does it have on the log entries?