FengOffice 1.x SQL to show time and cost for a given user between dates/time

[EvilLen]

I have created the below SQL that show time and cost for a given user between dates/time.

I you this once a month to find out who I need to send bills to and how much.

It has worked well for me for many years 2+ and I am not fully sure which versions it works for, I think 1.5.x to 1.7.x and could be for all of 1.x, please comment if you know and any modifications for older versions.

Here is my code:

Code: [Select]

SELECT workspace.workspace_id, pr.name, pr_ms.id, pr_ms.name, pr_task.id, pr_task.title, ts.start_time, ts.end_time, TIMEDIFF( ts.end_time, ts.start_time ) time_taken, TIME_TO_SEC( TIMEDIFF( ts.end_time, ts.start_time ) ) * ( 40 /60 /60 ) dev_cost
FROM og_timeslots ts
LEFT OUTER JOIN og_project_tasks pr_task ON ( ts.object_id = pr_task.id )
LEFT OUTER JOIN og_workspace_objects workspace ON ( pr_task.id  = workspace.object_id AND workspace.object_manager = 'ProjectTasks')
LEFT OUTER JOIN og_projects pr ON ( workspace.workspace_id = pr.id )
LEFT OUTER JOIN og_project_milestones pr_ms ON ( pr_task.milestone_id = pr_ms.id )
WHERE ts.user_id =5
AND ts.object_manager = 'ProjectTasks'
AND ts.start_time
BETWEEN '2012-12-01 00:00:00'
AND  '2012-12-31 23:59:59'
I hope this is of help to you!

[EvilLen]

NOTE: to change the cost rate you change the line:

Code: [Select]

TIME_TO_SEC( TIMEDIFF( ts.end_time, ts.start_time ) ) * ( 40 /60 /60 ) dev_cost
The 40 is the rate in your currency, change this to the value you want.