Help creating "Who's In" pop up for Sales group

Discussion for TimeTrex open source community developers.
Locked
jaustill
Posts: 34
Joined: Fri Sep 19, 2008 8:36 am

Help creating "Who's In" pop up for Sales group

Post by jaustill »

I am attempting to build a query that gives me the last punch in or out of all the employees in one department (sales).

I will use that query to add a clickable link on my company intranet that will pop up a window that shows which sales people are available.(punched in or out)

I need help to know if I am looking at the correct group of tables and also which field in which table would be the "last" date that my links would start from.

My report would look similar to this:
Employee | last punch | in or out | Lunch or normal etc. | notes |

Adrian | Fri 09/17/2008 8:05 am | IN |Normal | |
Bill | Fri 09/17/2008 8:00 am | IN |Normal | |
Eve | Fri 09/17/2008 1:05 am |OUT | Lunch | |
Joe | Fri 09/17/2008 11:30 am |OUT | Lunch | with client |
Tom | Thur 09/16/2008 11:30 am |OUT| Normal | out Friday |

I believe I need to start by grouping the records with the last 'updated_date' from the table "punch_control" for my group of users
and then using these tables:

punch
punch_control
Users
user_date

and linking the following:
the column 'punch_control_id' in the table "punch" links the table "punch" to the column 'id' in the table "punch_control";
the column 'user_date_id' in the table "punch_control" links the table "punch_control" to the column 'id' in the table "user_date";
the column 'user_id' in the table "user_date" links the table "user_date" to the column 'id' in the table "users"

Am I on the right track? Am I using the correct tables and columns?
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw »

Your best bet is to look at the Who's In Summary report. It has a function that should get you all the data you need.

Or if you enable debug verbosity 11, it will show you the exact SQL queries that it uses, so you could just bring up the report you want, then enable debugging to copy the exact query.

Of course even better would be to use the same function that it uses to get the data:

Code: Select all

$plf->getLastPunchByCompanyIdAndArrayCriteria( $current_company->getId(), $filter_data );
That way if the tables ever change your query won't break.
jaustill
Posts: 34
Joined: Fri Sep 19, 2008 8:36 am

Post by jaustill »

Thanks, your suggestion to run debug to get the query data was just what I needed. Now I have a clikable link on the companies intranet that pops a small screen to show them which sales people are available. I was also able to use one of your built in "extra" fields to just show their nicknames.
Locked