SambaPOS Forum

English Boards => Support => Topic started by: DQ on March 07, 2013, 09:15:37 am

Title: Employee timecard implementation: Need help for db select query
Post by: DQ on March 07, 2013, 09:15:37 am

Hello,
  I am implementing basic employee time management functionality where I need your help in creating a data base query to
  look up last action performed by the user.  I have never done any SQL programming so your help would be appreciated.
  Once done, will contribute back to community as I did for other features like credit card processing, setting color for modifier buttons etc.


My Table is: TimeCards

Id         int           primarykey,notnull
TimeStamp   datetime      notnull
Action     tinyint       not null,
User_Id    int           FK (User.Id, not null)


I have a class TimeCardEntry as below

class TimeCardEntry : IEntity
{
   enum TimeCardAction
   {
      None = 0,
      ClockIn = 1,
      ClockOut = 2;
   }
   public TimeCardEntry(User user, TimeCardAction action)
   {
        _user = user;
        Action = (int)action;
        TimeStamp = DateTime.Now;
   }
   public int Id { get; set; }
   public int Action { get; set;}
   public DateTime TimeStamp { get; set;}
   public User User { get { return _user; } set { _user = value; }

   private  User _user;
}


   
I need to retrieve a last entry from TimeCards table where

1.    TimeCardEntry.User.Id
2.    TimeCardEntry.TimeStamp > DateTime.Today (Basically last entry today 12:00 AM)

I tried following function but getting exception.

TimeCardEntry GetLastTimeCardEntry(User user)
{

var entry = Dbo.Select<TimeCardEntry, TimeCardEntry>(x=> x, (x=> x.User.Id == user.Id && (DateTime.Compare(x.TimeStamp, DateTime.Today) > 0)).Last()

return entry;
} 

Title: Re: Employee timecard implementation: Need help for db select query
Post by: emre on March 07, 2013, 09:40:43 am

If you register TimeCardEntry into SambaContext and use Dao class for accessing data

var id = user.Id;
Dao.Last<TimeCardEntry>(x=>x.User.Id == id,x=>x.User);

should work.

Edit:Modified to include TimeCardEntry.User to the result

Title: Re: Employee timecard implementation: Need help for db select query
Post by: indypctech on March 07, 2013, 10:00:43 am

DQ let me know if u want me to have this done for u  i have someone on hold for the project and its ready to start but u will need to communicate with him

Title: Re: Employee timecard implementation: Need help for db select query
Post by: DQ on March 07, 2013, 10:32:22 am

Hi Emre,
Thanks for your quick response.  Is it possible to retrieve entry if TimeStamp > DateTime.Today?

The reason I need to do is  I want to check if user has clocked-in today. The concern is what if user clocked-in yesterday and forgot to clock-out.

Title: Re: Employee timecard implementation: Need help for db select query
Post by: emre on March 07, 2013, 11:22:42 am

So you need to return a collection of TimeStamps?

Title: Re: Employee timecard implementation: Need help for db select query
Post by: DQ on March 08, 2013, 05:55:21 am

Thanks Emre.  I am checking date condition in the code and it works fine. Thx for your help. Infact the problem was my Id was not set to auto increment.

How do I add this table creation so it will automatically create if not exist? Should I add into Migration_xxx.cs file?

Title: Re: Employee timecard implementation: Need help for db select query
Post by: emre on March 08, 2013, 07:38:26 am

Yes. First of all you'll increase database version number on LocalSettings class. This version number is useful to know from which database version to which database version we are migrating and which migration classes should run. To be able to upgrade database you need to create a new migration class and attribute it with the new database version number.