Topics

1

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

« on: March 05, 2013, 04:40:34 pm »

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'


2

How To's / Problems in FengOffice 2.x SQL to show time and cost for a user between dates

« on: February 11, 2013, 12:43:45 pm »

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

Please see the forum article:
link: http[://]forums[.]fengoffice[.]com[/]index.php[?]topic=15924.0
I get the error: Sorry, you are not allowed to post links.
Remove [ and ] and copy the url in to the browser to see the working SQL for version 1.x.

As FengOffice 2.x has had major changes in the database and I have found a few problems running my old SQL on the new database.  I have found solutions for many of the problems but I am stuck with one problem.

I have spent hours looking at the database and the classes and I am thinking I might find the answer quicker if I post a question here.

The new SQL I have is:

Code: [Select]

SELECT workspace.object_id, obj_ws.name, pr_ms.object_id, obj_pr_ms.name, pr_task.object_id, obj_pr_task.name, 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 fo_timeslots ts
LEFT OUTER JOIN fo_objects obj_ts ON ( ts.object_id = obj_ts.id )
LEFT OUTER JOIN fo_project_tasks pr_task ON ( ts.rel_object_id = pr_task.object_id )
LEFT OUTER JOIN fo_objects obj_pr_task ON ( pr_task.object_id = obj_pr_task.id )
LEFT OUTER JOIN fo_workspaces workspace ON ( pr_task.object_id  = workspace.object_id)
LEFT OUTER JOIN fo_objects obj_ws ON ( workspace.object_id = obj_ws.id )
LEFT OUTER JOIN fo_project_milestones pr_ms ON ( pr_task.milestone_id = pr_ms.object_id )
LEFT OUTER JOIN fo_objects obj_pr_ms ON ( pr_ms.object_id = obj_pr_ms.id )
WHERE obj_ts.created_by_id  = 2
AND ts.start_time
BETWEEN '2013-01-01 00:00:00'
AND  '2013-01-31 23:59:59'
The problem I have is linking fo_project_tasks to fo_workspaces, this really helps to see which workspace a task belongs to.

At the moment the fields workspace.object_id and obj_ws.name in the SELECT statement just come back as NULL.

I am pretty sure the problem is in:

Code: [Select]

LEFT OUTER JOIN fo_workspaces workspace ON ( pr_task.object_id  = workspace.object_id)
LEFT OUTER JOIN fo_objects obj_ws ON ( workspace.object_id = obj_ws.id )
Does any one know how these tables are linked up?

Your help will be much appreciated.  Thank you in advance.

3

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

« on: February 11, 2013, 12:18:56 pm »

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!