Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - EvilLen

Pages: [1]
1
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.

2
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.

3
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'

4
I have worked on it and I have the solution:

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 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
)

5
Oh, I think I might have just found it,

Is it linked via the fo_object_members which is then linked to fo_members table?

I will check this out for myself later, will create a update post if it works.

Fingers crossed?

6
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.

7
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!

Pages: [1]