Results 1 to 7 of 7

Thread: Check for logins for all previous days.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Check for logins for all previous days.

    Hi,

    I am working on an application where I would need a little help.

    I have a button which the user clicks to record a login into the system and also another button which records logout.

    I have to insert a code to the login button which will check the login/logout records in the database for all the days prior to today.
    If there is no data present for yesterday, the click on the login button should point the user to first update the data for yesterday and then proceed with recording the login for today.

    Example, our users record logins and logouts on the system from monday - friday. Now, when a user tries to login on monday again, he/she should be prompted to first update the data for Sat and Sun before proceeding with login for monday.

    I want this feature to be implemented as we need the database to be fully populated records for all the days in the month without missing any.

    Any help on this will be greatly appreciated.

  2. #2
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    673

    Re: Check for logins for all previous days.

    > "when a user tries to login on monday again, he/she should be prompted to first update the data for Sat and Sun"
    Why?
    Do these people normally work weekends?
    What if they're off sick for a week? Do they have to "account" for [each of] the "missing" five days?
    (Oops; should have read on...)

    > "I want this feature ... we need the database to be fully populated records for all the days in the month without missing any"
    IMHO, this is unrealistic, inconvenient for users (Bad Idea) and I can see no practical purpose to it.
    What if someone goes off sick in the middle of the month and doesn't return until the middle of the next month (if at all)? When you run your "end of month" reconciliation, you'll still have "holes" in the data, because that person hasn;t logged on again yet.

    Building your application to deal with these "holes" in the time line is probably a better, long-term option. YMMV.
    A simple "calendar" table, one row per [working] day, is your friend in this.

    Regards, Phill W.

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,346

    Re: Check for logins for all previous days.

    Doesn't make much sense but

    Seems like the only thing you would need to lookup in the database is the most recent login and then compare that to today.
    You could then add the one or 2 records needed based on the datediff() between the most recent login and today.

  4. #4
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Check for logins for all previous days.

    I must admit that I don't really understand the requirement unless the 'admin system' demands that the user positively states that they did not log in or out, on the 'missing' days in the Database, by entering something.

    @prmaodsd: What do you expect to be in the Database Table for those days that the user did not log in ? i.e. when you prompt the user to enter the details, what are they expected to enter if they didn't log in on that day ?

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Check for logins for all previous days.

    Thank you all for your comments.

    DataMiser,
    I believe that would work. Will try that out.
    Could you please provide me a snippet of the code you would use to achieve it.

    Doogle,
    For the days the user has not logged in, I have a frame that helps him/her select from 3 options viz., 1. Weekly-Off/Public Holiday, 2. Annual Leave/Sick Leave, 3. Comp-off.
    The reason I want all these leaves/offs to be populated in the database for each day is because, when we generate a report in excel by the end of the month, we need to see the data for each day.

    Thanks.

  6. #6
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    673

    Re: Check for logins for all previous days.

    > "when we generate a report in excel by the end of the month, we need to see the data for each day"
    ... which can still be done perfectly well without having a record for every user on every day:

    Code:
    create table kalendar 
    ( id integer not null auto_increment 
    , kalendar_date date not null 
    , primary key kalendar_date 
    ) 
    
    create table users
    ( id integer not null auto_increment 
    , name varchar( 60 ) 
    , primary key ( id ) 
    ) 
    
    create table user_log 
    ( user_id integer not null 
    , kalendar_id  integer not null 
    , primary key ( user_id, kalendar_id ) 
    ) 
    
    
    insert into kalendar ( kalendar_date ) values 
      ( '2013-09-02' ) 
    , ( '2013-09-03' ) 
    , ( '2013-09-04' ) 
    , ( '2013-09-05' ) 
    , ( '2013-09-06' ) 
    ; 
    
    insert into users ( name ) values ( 'fred' ), ( 'bob' ) ; 
    
    insert into user_log values ( 1, 1 ), ( 1, 2 ), ( 1, 3 ) ; 
    insert into user_log values ( 2, 3 ), ( 2, 4 ), ( 2, 5 ) ; 
    
    select k.kalendar_date 
    , k.id as kalendar_id 
    , u.id as user_id 
    , u.name 
    from kalendar k 
    left join user_log ul 
    on k.id = ul.kalendar_id 
    left join users u 
    on ul.user_id = u.id 
    order by k.kalendar_date, u.name ; 
    
    +---------------+-------------+---------+------+ 
    | kalendar_date | kalendar_id | user_id | name | 
    +---------------+-------------+---------+------+ 
    | 2013-09-02    |           1 |       1 | fred | 
    | 2013-09-03    |           1 |       2 | fred | 
    | 2013-09-04    |           1 |       3 | fred | 
    | 2013-09-04    |           2 |       3 | bob  | 
    | 2013-09-05    |           2 |       4 | bob  | 
    | 2013-09-06    |           2 |       5 | bob  | 
    +---------------+-------------+---------+------+
    Rearranging this raw data into a report format should be done in your application layer; it will do a far better job than your database.

    Regards, Phill W.

  7. #7
    Member
    Join Date
    Mar 2013
    Posts
    35

    Re: Check for logins for all previous days.

    @Phil W: I don't agree. This is obviously about user accountability. Holes mean issues (user timekeeping issues that is). I would say that's the point. This is a Positive-Action system. What prmaodsd is saying is “if there isn’t any data for a User on a particular date, why?”. If you build the idea that data can be missing into your system, you cannot easily ask that question.

    Moreover, requiring data for every user for every date means it's far easier to maintain and manipulate the database. Why make it difficult? Why have a Calendar and populate a related table with the User log ins? What does it get you? As every date should be present the Calendar table just gives you a list of every date. That’s a pointless table with an unnecessary relationship.

    All prmaodsd is asking about is an efficient way to fill in the empty records. It looks like the UI is almost sorted, ie a way to easily select what to populate the missing dates with. DataMiser’s DateDiff idea is the way to go. And then tweak the UI so start and end dates can be selected.

    @prmaodsd: You need to figure out how to select the latest date a user logged in, then DateDiff it with now, loop through the dates and present a UI that allows the user to select multiple dates and assign an option to explain the absence. Then just UPDATE the table with the reasons given.

    If the users are shift workers you may need to consider the scenario where they log in and out on different days, so the time will be important. For example they log in at 8pm and then log out at 4am the next morning. That may mean having a shift pattern stored against the user.

    Selecting the last date for a user is quite easy. Something like:

    Code:
    SELECT TOP 1 [UserLog].[LogIn]
    FROM [UserLog]
    WHERE [UseLog].[UserID]=’User ID’
    ORDER BY [UserLog].[LogIn] DESC;
    It's TOP 1 and ORDER BY [UserLog].[LogIn] DESC that make it work.

    Hope this helps

    Cheers

    Yowser

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width