Пример #1
0
 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;
 }