Poll

Do you think this will be help or have you used this to help with your billing

Helpful, I used it or will use it.
0 (0%)
Not sure, neither helpful or not.
0 (0%)
Not helpful, found problems.
0 (0%)

Total Members Voted: 0

Author Topic: FengOffice 1.x SQL to show time and cost for a given user between dates/time  (Read 3785 times)

EvilLen

  • Newbie
  • *
  • Posts: 7
    • View Profile
    • Email
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

  • Newbie
  • *
  • Posts: 7
    • View Profile
    • Email
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.

 

anything