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.
1 (100%)
Not sure, neither helpful or not.
0 (0%)
Not helpful, found problems.
0 (0%)

Total Members Voted: 1

Author Topic: FengOffice 2.x SQL to show time and cost for a given user between dates/time  (Read 5793 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.

This is for FengOffice 2.x, I have this working on 2.2.2 and I am hoping it will work on all of 2.x.

I have added comments which I used to make notes and left them as I think these might be useful.

Here is my code:
Code: [Select]
/* list tasks and show their workspace they belong to */
/* the timeslot id */
SELECT ts.object_id as timeslot_id,
/* this object_id for workspace via member tuple */
mem.object_id as workspace_obj_id,
/* workspace name taken from members table */
mem.name as workspace_name,
/* the depth of a member in the hierachy */
mem.depth as workspace_depth,
/* get the milestone object id */
obj_of_milestone.id as milestone_obj_id,
/* get the milestone name */
obj_of_milestone.name as milestone_name,
/* the tasks object id */
obj_of_task.id as task_obj_id,
/*  the tasks name */
obj_of_task.name as task_name,
/* the timeslot start time */
ts.start_time,
/* the timeslot end time */
ts.end_time,
/* the timeslot differance in time, i.e. Time taken */
TIMEDIFF( ts.end_time, ts.start_time ) time_taken,
/* the cost based on the time taken */
TIME_TO_SEC( TIMEDIFF( ts.end_time, ts.start_time ) ) * ( 40 /60 /60 ) dev_cost
/* We want to start with timeslots as that is what we want to calculate */
FROM fo_timeslots ts
/* Now get the object for that time slot */
LEFT OUTER JOIN fo_objects obj_ts ON ( ts.object_id = obj_ts.id )
/* now get  an object of task (see line 1 of the WHERE clause) */
LEFT OUTER JOIN fo_objects obj_of_task ON (ts.rel_object_id = obj_of_task.id)
/* now get  the task via the object (see line 1 of the WHERE clause) */
LEFT OUTER JOIN fo_project_tasks task ON (obj_of_task.id = task.object_id)
/* using the task get it's milestone */
LEFT OUTER JOIN fo_project_milestones milestone ON (task.milestone_id = milestone.object_id)
/* now get the milestone object */
LEFT OUTER JOIN fo_objects obj_of_milestone ON (milestone.object_id = obj_of_milestone.id)
/* fo_object_member links objects and members; decomposed table to support many to many relationship  */
LEFT OUTER JOIN fo_object_members obj_mem ON ( obj_of_task.id = obj_mem.object_id )
/* fo_members is an type of object but is has it's own id and in addition an object_id, also they have a parent_id to keep a hierachy.
 think of it like; an object of a task can be a member of workspace, a person and/or a company etc., each member is an object   
linked by object_id and parent_id is the fo_members.id of its parent member. Note; fo_members.id is not related to fo.object.id */
LEFT OUTER JOIN fo_members mem ON ( mem.id = obj_mem.member_id )
/* Create link to a second object this time a member object as it's id matches mem.object_id.  Note it might not be a workspace though */
LEFT OUTER JOIN fo_objects obj_of_mem ON ( obj_of_mem.id = mem.object_id )
/* insure this object is a task (type_id 5) */
WHERE obj_of_task.object_type_id = 5
/* insure that the timeslots are for me (contact_id = 2) */
AND ts.contact_id = 2
/* insure this object is a workspace (type_id 1) */
AND obj_of_mem.object_type_id = 1
/* subquery to insure that you use only the row with the Max depth */
AND mem.depth = (
/* SQL to find the max depth for member workspace */
SELECT MAX(mem2.depth)
FROM fo_members mem2
LEFT OUTER JOIN fo_objects obj_of_mem2 ON ( obj_of_mem2.id = mem2.object_id )
/* fo_object_member links objects and members; decomposed table to support many to many relationship  */
LEFT OUTER JOIN fo_object_members obj_mem2 ON (obj_mem2.member_id = mem2.id )
/* links the innerquery to the out query by the members object_id */
WHERE obj_mem2.object_id = obj_of_task.id
/* this must be a workspace */
AND obj_of_mem2.object_type_id =1
)
AND ts.start_time
BETWEEN '2013-01-01 00:00:01'
AND  '2012-02-01 00:00:00'
« Last Edit: March 06, 2013, 12:15:35 pm by EvilLen »

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.