public static function GetAllProjects($filter, $forMyProject = false, $order_column = 'project_id', $order_direction = 1)
{
$db = Zend_Db_Table::getDefaultAdapter();
$projects = array();
$sql = 'SELECT
p.*,
c.name AS corporation_name,
w.name AS workgroup_name, w.access_pct_completed,
l.name AS location_name,
s.title AS stage_title,
b.name AS trim_business_unit,
lc.name AS lc_name,
sp.project_percentage_id, sp.value AS status_progress_value, sp.display_static AS status_value_display,
up.firstname pcon_firstname, up.lastname pcon_lastname,
CASE WHEN pf.project_id IS NULL THEN 0 ELSE 1 END `on_project_team`,
IFNULL(pf.favorite, 0) favorite
FROM projects p
INNER JOIN corporations c ON p.done_for_corporation=c.corporation_id
INNER JOIN locations l ON p.done_at_location = l.location_id
LEFT JOIN workgroups w ON p.done_for_workgroup=w.workgroup_id
INNER JOIN project_stages s ON p.stage_id=s.stage_id
INNER JOIN workgroups b ON p.done_by_workgroup=b.workgroup_id
INNER JOIN corporations lc ON l.corporation_id=lc.corporation_id
LEFT JOIN users up ON p.point_of_contact=up.user_id
LEFT JOIN project_users pf ON p.project_id = pf.project_id AND pf.user_id = ?
-- Project Tasks and the last history entry for the overall
LEFT JOIN project_tasks ptasks ON ptasks.project_id = p.project_id AND ptasks.is_overall = 1
LEFT JOIN (select MAX(project_task_history_id) project_task_history_id, project_task_id FROM project_task_history GROUP BY project_task_id) ptaskhist_max ON ptasks.project_task_id = ptaskhist_max.project_task_id
LEFT JOIN project_task_history ptaskhist ON ptaskhist_max.project_task_history_id = ptaskhist.project_task_history_id
LEFT JOIN project_percentages_task sp ON sp.project_percentage_id = IFNULL(ptaskhist.project_percentage_id, 14)
-- End Project Tasks
%s';
if ($order_direction == 0) {
$order_direction = ' desc';
} else {
$order_direction = ' asc';
}
$order_str = '';
switch ($order_column) {
case 'project':
$order_str .= ' ORDER BY p.stage_id ASC, p.project_id ' . $order_direction;
break;
case 'job_no':
$order_str .= ' ORDER BY p.stage_id ASC, p.job_no ' . $order_direction;
break;
case 'cust_ref_no':
$order_str .= ' ORDER BY p.stage_id ASC, p.ref_no ' . $order_direction;
break;
case 'location':
$order_str .= " ORDER BY p.stage_id ASC, lc.name {$order_direction}, l.name {$order_direction}, p.title {$order_direction}";
break;
default:
$order_str .= ' ORDER BY p.stage_id ASC, p.project_id ASC';
break;
}
$sql .= $order_str;
$sql = sprintf($sql, Application_Model_Projects::getSQLFromFilter($filter, $forMyProject));
$rows = $db->query($sql, ProNav_Auth::getUserID())->fetchAll();
foreach ($rows as $row) {
$project = Application_Model_Projects::constructProjectFromData($row);
$projects[$project->project_id] = $project;
}
return $projects;
}