postgresql7.3
SELECT
ts.task_id,
ts.task_id as item_id,
ts.task_number,
t.task_revision_id,
t.title,
o.package_id as instance_id,
t.parent_id as project_item_id,
to_char(t.earliest_start,'J') as earliest_start_j,
to_char(current_timestamp,'J') as today_j,
to_char(t.latest_start,'J') as latest_start_j,
to_char(t.latest_finish,'J') as latest_finish_j,
to_char(t.latest_start,'YYYY-MM-DD HH24:MI') as latest_start,
to_char(t.latest_finish,'YYYY-MM-DD HH24:MI') as latest_finish,
t.percent_complete,
t.estimated_hours_work,
t.estimated_hours_work_min,
t.estimated_hours_work_max,
case when t.actual_hours_worked is null then 0
else t.actual_hours_worked end as actual_hours_worked,
to_char(t.earliest_start,'YYYY-MM-DD HH24:MI') as earliest_start,
to_char(t.earliest_finish,'YYYY-MM-DD HH24:MI') as earliest_finish,
to_char(t.latest_start,'YYYY-MM-DD HH24:MI') as latest_start,
to_char(t.latest_finish,'YYYY-MM-DD HH24:MI') as latest_finish,
p.first_names || ' ' || p.last_name || ' (' ||
substring(r.one_line from 1 for 1) || ')' as full_name,
p.person_id,
s.status_type as status,
r.is_lead_p,
projectr.title as project_name
FROM
acs_objects o,
pm_tasks_active ts,
pm_task_status s,
cr_items i,
pm_tasks_revisionsx t
LEFT JOIN pm_task_assignment ta
ON t.item_id = ta.task_id
LEFT JOIN persons p
ON ta.party_id = p.person_id
LEFT JOIN pm_roles r
ON ta.role_id = r.role_id,
cr_items projecti,
cr_revisions projectr
WHERE
ts.status = s.status_id and
ts.task_id = t.item_id and
i.item_id = t.item_id and
t.task_revision_id = i.live_revision and
t.end_date >= :first_of_month_date and
t.end_date <= :last_of_month_date and
t.parent_id = projecti.item_id and
o.object_id=t.item_id and
projecti.live_revision = projectr.revision_id
$instance_clause
$hide_closed_clause
$selected_users_clause
ORDER BY
t.latest_start, ts.task_id, r.role_id, p.first_names, p.last_name
SELECT t.item_id as task_id,
t.parent_id as project_item_id,
t.title,
to_char(t.end_date,'YYYY-MM-DD HH24:MI:SS') as day_date,
to_char(t.end_date,'J') as day_date_j,
s.status_type as status,
s.description as status_description,
t.priority,
t.parent_id,
r.is_lead_p,
op.title as project_name,
op.package_id as instance_id,
ta.party_id as person_id
FROM
(select tr.*
from cr_items ci, pm_tasks_revisionsx tr
-- get only live revisions
where ci.live_revision = tr.task_revision_id) t,
pm_tasks_active ti,
pm_task_status s,
pm_task_assignment ta,
pm_roles r,
cr_items cp,
acs_objects op
where t.parent_id = cp.item_id and
t.item_id = ti.task_id and
ti.status = s.status_id
and cp.live_revision = op.object_id
and t.item_id = ta.task_id and ta.role_id = r.role_id and ta.party_id in ([join $selected_users ","])
and s.status_type = 'o'
$hide_closed_clause
$instance_clause
and t.end_date >= :first_of_month_date
and t.end_date <= :last_of_month_date
ORDER BY
t.end_date
SELECT
p.item_id as project_item_id,
p.project_id,
p.status_id,
p.parent_id as folder_id,
p.object_type as content_type,
p.title as project_name,
p.project_code,
f.package_id as instance_id,
to_char(p.planned_start_date, 'MM/DD/YY') as planned_start_date,
to_char(p.planned_end_date, 'MM/DD/YY') as planned_end_date,
p.ongoing_p,
c.category_id,
c.category_name,
to_char(p.planned_end_date,'J') as deadline_j,
p.earliest_finish_date - current_date as days_to_earliest_finish,
p.latest_finish_date - current_date as days_to_latest_finish,
p.actual_hours_completed,
p.estimated_hours_total,
to_char(p.estimated_finish_date, 'MM/DD/YY') as estimated_finish_date,
to_char(p.earliest_finish_date, 'MM/DD/YY') as earliest_finish_date,
to_char(p.latest_finish_date, 'MM/DD/YY') as latest_finish_date,
persons.first_names || ' ' || persons.last_name || ' (' ||
substring(r.one_line from 1 for 1) || ')' as full_name,
persons.person_id,
case when o.name is null then '--no customer--' else o.name
end as customer_name,
o.organization_id as customer_id
FROM pm_projectsx p
LEFT JOIN pm_project_assignment pa
ON p.item_id = pa.project_id
LEFT JOIN persons
ON pa.party_id = persons.person_id
LEFT JOIN pm_roles r
ON pa.role_id = r.role_id
LEFT JOIN organizations o ON p.customer_id =
o.organization_id
LEFT JOIN (
select
om.category_id,
om.object_id,
t.name as category_name
from
category_object_map om,
category_translations t,
categories ctg
where
om.category_id = t.category_id and
ctg.category_id = t.category_id and
ctg.deprecated_p = 'f')
c ON p.item_id = c.object_id,
cr_items i,
cr_folders f
WHERE
p.project_id = i.live_revision
and i.parent_id = f.folder_id
$instance_clause
$selected_users_clause
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = p.project_id
and ppm.privilege = 'read'
and ppm.party_id = :user_id)