Content
- General Information
- Tables and views used
- Time sheet Portlets
- Sample Queries
General information
When time sheet form (/intranet-timesheet2/hours/new) is send to the server,
- … ]po[ deletes affected time sheet entries for affected day and user and creates new ones
- ... ]po[ updates im_projects::reported_hours_cache
Please note that
- ... Paramaters have in impact on values shown in time sheet plugins. In case there's mismath regarding hours reported, check parameter SyncHoursInterval and SyncHoursP
- If hours don't sum up correctly on the time sheet component in a sub project, there might be hours logged on the main project that are not considered.
fullquery name: update_timesheet_task
update im_projects
set reported_hours_cache = (
select sum(h.hours)
from im_hours h
where h.project_id = :project_id
)
where project_id = :project_id
fullquery name: delete_timesheet_costs
DECLARE
row RECORD;
BEGIN
for row in
select cost_id
from im_costs
where cost_type_id = [im_cost_type_timesheet]
and project_id = :project_id
and cause_object_id = :user_id_from_search
and effective_date = to_date(:julian_date, ‘J’)
loop
PERFORM im_cost__delete(row.cost_id);
end loop;
return 0;
END;
Tables used
- im_timesheet_tasks
- im_hours
- im_costs
im_timesheet_tasks
- Contains all timesheet tasks
projop=# \d im_timesheet_tasks
Table "public.im_timesheet_tasks"
Column | Type | Modifiers
----------------+------------------+-----------
task_id | integer | not null
material_id | integer | not null
uom_id | integer | not null
planned_units | double precision |
billable_units | double precision |
cost_center_id | integer |
invoice_id | integer |
priority | integer |
sort_order | integer |
bt_bug_id | integer |
Indexes:
"im_timesheet_tasks_pk" PRIMARY KEY, btree (task_id)
Foreign-key constraints:
"im_times_tasks_bt_bug_fk" FOREIGN KEY (bt_bug_id) REFERENCES bt_bugs(bug_id)
"im_timesheet_task_fk" FOREIGN KEY (task_id) REFERENCES im_projects(project_id)
"im_timesheet_tasks_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
"im_timesheet_tasks_invoice_fk" FOREIGN KEY (invoice_id) REFERENCES im_invoices(invoice_id)
"im_timesheet_tasks_material_fk" FOREIGN KEY (material_id) REFERENCES im_materials(material_id)
"im_timesheet_tasks_uom_fk" FOREIGN KEY (uom_id) REFERENCES im_categories(category_id)
im_costs
A cost item will be created for each time sheet entry
Table "public.im_costs"
Column | Type | Modifiers
------------------------+--------------------------+-----------
cost_id | integer | not null
cost_name | character varying(400) | not null
cost_nr | character varying(400) | not null
project_id | integer |
customer_id | integer | not null
cost_center_id | integer |
provider_id | integer | not null
investment_id | integer |
cost_status_id | integer | not null
cost_type_id | integer | not null
cause_object_id | integer |
template_id | integer |
effective_date | timestamp with time zone |
start_block | timestamp with time zone |
payment_days | integer |
amount | numeric(12,3) |
currency | character(3) |
paid_amount | numeric(12,3) |
paid_currency | character(3) |
vat | numeric(12,5) |
tax | numeric(12,5) |
variable_cost_p | character(1) |
needs_redistribution_p | character(1) |
parent_id | integer |
redistributed_p | character(1) |
planning_p | character(1) |
planning_type_id | integer |
description | character varying(4000) |
note | character varying(4000) |
last_modified | timestamp with time zone |
last_modifying_user | integer |
last_modifying_ip | character varying(20) |
Indexes:
"im_costs_pk" PRIMARY KEY, btree (cost_id)
"im_costs_cause_object_idx" btree (cause_object_id)
"im_costs_start_block_idx" btree (start_block)
Check constraints:
"im_costs_needs_redist_ck" CHECK (needs_redistribution_p = 't'::bpchar OR needs_redistribution_p = 'f'::bpchar)
"im_costs_planning_ck" CHECK (planning_p = 't'::bpchar OR planning_p = 'f'::bpchar)
"im_costs_redist_ck" CHECK (redistributed_p = 't'::bpchar OR redistributed_p = 'f'::bpchar)
"im_costs_var_ck" CHECK (variable_cost_p = 't'::bpchar OR variable_cost_p = 'f'::bpchar)
Foreign-key constraints:
"im_cost_template_fk" FOREIGN KEY (template_id) REFERENCES im_categories(category_id)
"im_costs_cause_fk" FOREIGN KEY (cause_object_id) REFERENCES acs_objects(object_id)
"im_costs_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
"im_costs_cost_fk" FOREIGN KEY (cost_id) REFERENCES acs_objects(object_id)
"im_costs_currency_fk" FOREIGN KEY (currency) REFERENCES currency_codes(iso)
"im_costs_customer_fk" FOREIGN KEY (customer_id) REFERENCES acs_objects(object_id)
"im_costs_inv_fk" FOREIGN KEY (investment_id) REFERENCES acs_objects(object_id)
"im_costs_last_mod_user" FOREIGN KEY (last_modifying_user) REFERENCES users(user_id)
"im_costs_paid_currency_fk" FOREIGN KEY (paid_currency) REFERENCES currency_codes(iso)
"im_costs_parent_fk" FOREIGN KEY (parent_id) REFERENCES im_costs(cost_id)
"im_costs_planning_type_fk" FOREIGN KEY (planning_type_id) REFERENCES im_categories(category_id)
"im_costs_provider_fk" FOREIGN KEY (provider_id) REFERENCES acs_objects(object_id)
"im_costs_status_fk" FOREIGN KEY (cost_status_id) REFERENCES im_categories(category_id)
"im_costs_type_fk" FOREIGN KEY (cost_type_id) REFERENCES im_categories(category_id)
Triggers:
im_costs_project_cache_del_tr AFTER DELETE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_del_tr()
im_costs_project_cache_ins_tr AFTER INSERT ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_ins_tr()
im_costs_project_cache_up_tr AFTER UPDATE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_up_tr()
Views used by ]po[ Time Sheet Management
- im_timesheet_tasks_view
projop=# \d im_timesheet_tasks_view
View "public.im_timesheet_tasks_view"
Column | Type | Modifiers
----------------------+--------------------------+-----------
task_id | integer |
material_id | integer |
uom_id | integer |
planned_units | double precision |
billable_units | double precision |
cost_center_id | integer |
invoice_id | integer |
priority | integer |
sort_order | integer |
project_id | integer |
task_name | character varying(1000) |
task_nr | character varying(100) |
percent_completed | double precision |
task_type_id | integer |
task_status_id | integer |
start_date | timestamp with time zone |
end_date | timestamp with time zone |
reported_hours_cache | double precision |
reported_units_cache | double precision |
View definition:
SELECT t.task_id, t.material_id, t.uom_id, t.planned_units, t.billable_units, t.cost_center_id, t.invoice_id, t.priority, t.sort_order, p.parent_id AS project_id, p.project_name AS task_name, p.project_nr AS task_nr, p.percent_completed, p.project_type_id AS task_type_id, p.project_status_id AS task_status_id, p.start_date, p.end_date, p.reported_hours_cache, p.reported_hours_cache AS reported_units_cache
FROM im_projects p, im_timesheet_tasks t
WHERE t.task_id = p.project_id;
Time sheet Portlets
Timesheet Tasks
| Location: | /intranet/projects/view?project_id=... |
Facts about the Time sheet Component:
- In order to speed up page creation, hours are taken from im_projects::reported_hours_cache
0 Comments.