postgresql7.2
SELECT t.item_id as task_item_id,
t.parent_id as project_item_id,
t.title,
to_char(t.end_date,'YYYY-MM-DD HH24:MI:SS') as end_date,
to_char(t.earliest_start,'YYYY-MM-DD HH24:MI:SS') as earliest_start,
t.earliest_start - current_date as days_to_earliest_start,
to_char(t.earliest_start,'J') as earliest_start_j,
to_char(t.earliest_finish,'YYYY-MM-DD HH24:MI:SS') as earliest_finish,
t.earliest_finish - current_date as days_to_earliest_finish,
to_char(t.latest_start,'YYYY-MM-DD HH24:MI:SS') as latest_start,
t.latest_start - current_date as days_to_latest_start,
to_char(t.latest_start,'J') as latest_start_j,
to_char(current_date,'J') as today_j,
to_char(t.latest_finish,'YYYY-MM-DD HH24:MI:SS') as latest_finish,
t.latest_finish - current_date as days_to_latest_finish,
to_char(t.end_date,'YYYY-MM-DD HH24:MI:SS') as end_date,
t.end_date - current_date as days_to_end_date,
t.percent_complete,
t.estimated_hours_work,
t.estimated_hours_work_min,
t.estimated_hours_work_max,
t.actual_hours_worked,
s.status_type,
s.description as status_description,
r.is_lead_p,
r.is_observer_p,
t.priority,
t.party_id,
t.parent_id,
d.parent_task_id,
o.title as project_name
FROM
(select tr.item_id,
ta.party_id,
ta.role_id,
tr.title,
tr.end_date,
tr.earliest_start,
tr.earliest_finish,
tr.latest_start,
tr.latest_finish,
tr.percent_complete,
tr.estimated_hours_work,
tr.estimated_hours_work_min,
tr.estimated_hours_work_max,
tr.actual_hours_worked,
tr.parent_id,
tr.revision_id,
tr.description,
tr.priority
from cr_items ci,
pm_tasks_revisionsx tr
LEFT JOIN
pm_task_assignment ta ON tr.item_id = ta.task_id
-- get only live revisions
where ci.live_revision = tr.revision_id
[template::list::page_where_clause -and -name "tasks" -key "tr.item_id"]) t
LEFT JOIN
pm_roles r
ON t.role_id = r.role_id
LEFT JOIN
pm_task_dependency d
ON t.item_id = d.task_id,
pm_tasks_active ti,
pm_task_status s,
cr_items cp,
acs_objects o
WHERE
t.parent_id = cp.item_id and
t.item_id = ti.task_id and
ti.status = s.status_id
$observer_clause
and cp.live_revision = o.object_id
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = ti.task_id
and ppm.privilege = 'read'
and ppm.party_id = :user_id)
[template::list::filter_where_clauses -and -name tasks]
[template::list::orderby_clause -name tasks -orderby]
SELECT t.item_id as task_item_id
FROM
(select tr.item_id,
ta.party_id,
ta.role_id,
tr.title,
tr.end_date,
tr.earliest_start,
tr.earliest_finish,
tr.latest_start,
tr.latest_finish,
tr.percent_complete,
tr.estimated_hours_work,
tr.estimated_hours_work_min,
tr.estimated_hours_work_max,
tr.actual_hours_worked,
tr.parent_id,
tr.revision_id,
tr.description,
tr.priority
from cr_items ci, pm_tasks_revisionsx tr
LEFT JOIN
pm_task_assignment ta ON tr.item_id = ta.task_id, pm_roles r
-- get only live revisions
where ci.live_revision = tr.revision_id
and ta.role_id = r.role_id
$observer_pagination_clause
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = tr.item_id
and ppm.privilege = 'read'
and ppm.party_id = :user_id)
) t,
pm_tasks_active ti,
pm_task_status s,
cr_items cp,
acs_objects o
WHERE
t.parent_id = cp.item_id and
t.item_id = ti.task_id and
ti.status = s.status_id
and cp.live_revision = o.object_id
[template::list::filter_where_clauses -and -name tasks]
[template::list::orderby_clause -name tasks -orderby]
select
distinct(first_names || ' ' || last_name) as fullname,
u.person_id
from
persons u,
pm_task_assignment a,
pm_tasks_active ts
where
u.person_id = a.party_id
and ts.task_id = a.task_id
order by
fullname
select
ci.item_id
from
cr_items ci,
pm_projects p,
cr_items pi
where
p.project_id = ci.latest_revision
and ci.tree_sortkey between tree_left(pi.tree_sortkey)
and tree_right(pi.tree_sortkey)
and pi.item_id = :pid_filter
select
rel.object_id_two
from
acs_rels rel,
acs_objects o
where
rel_type ='application_data_link'
and object_id_two = o.object_id
and o.object_type = 'logger_project'
and rel.object_id_one = :parent_id
select
description,
status_id
from
pm_task_status
order by
status_type desc,
description