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; }