PHP Projects::findBySQL方法代码示例

 /**
  * Return projects by user
  * 
  * $statuses is an array of allowed statuses. If NULL no status filtering 
  * will be done
  * 
  * If $all_for_admins_and_pms is set to true system will return all projects 
  * if user is administrator or project manager
  *
  * @param User $user
  * @param array $statuses
  * @param boolean $all_for_admins_and_pms
  * @return array
  */
 function findByUser($user, $statuses = null, $all_for_admins_and_pms = false)
 {
     $projects_table = TABLE_PREFIX . 'projects';
     $project_users_table = TABLE_PREFIX . 'project_users';
     if ($all_for_admins_and_pms && ($user->isAdministrator() || $user->isProjectManager())) {
         if ($statuses) {
             return Projects::findBySQL("SELECT * FROM {$projects_table} WHERE type = ? AND status IN (?) ORDER BY name", array(PROJECT_TYPE_NORMAL, $statuses));
         } else {
             return Projects::findBySQL("SELECT * FROM {$projects_table} WHERE type = ? ORDER BY name", array(PROJECT_TYPE_NORMAL));
         }
         // if
     } else {
         if ($statuses) {
             return Projects::findBySQL("SELECT {$projects_table}.* FROM {$projects_table}, {$project_users_table} WHERE {$project_users_table}.user_id = ? AND {$project_users_table}.project_id = {$projects_table}.id AND {$projects_table}.type = ? AND {$projects_table}.status IN (?) ORDER BY {$projects_table}.name", array($user->getId(), PROJECT_TYPE_NORMAL, $statuses));
         } else {
             return Projects::findBySQL("SELECT {$projects_table}.* FROM {$projects_table}, {$project_users_table} WHERE {$project_users_table}.user_id = ? AND {$project_users_table}.project_id = {$projects_table}.id AND {$projects_table}.type = ? ORDER BY {$projects_table}.name", array($user->getId(), PROJECT_TYPE_NORMAL));
         }
         // if
     }
     // if
 }

//.........这里部分代码省略.........
             $items = array();
             //$items = ProjectObjects::findBySQL("SELECT $project_objects_table.* FROM $project_objects_table, $search_index_table WHERE $type_filter AND MATCH ($search_index_table.content) AGAINST (? IN BOOLEAN MODE) AND $project_objects_table.id = $search_index_table.object_id AND $search_index_table.type = ? AND state >= ? AND visibility >= ? LIMIT $offset, $per_page", array($search_for, $type, STATE_VISIBLE, $user->getVisibility()));
             $ids = array();
             //BOF:mod 20110706 ticketid222
             /*
             //EOF:mod 20110706 ticketid222
             $query = $query_main . " order by 2, 1 LIMIT " . $offset . " ," . $per_page;
             //BOF:mod 20110706 ticketid222
             */
             //BOF:mod 20120711
             if (empty($datesort)) {
                 //EOF:mod 20120711
                 $query = $query_main . " order by new_order ASC, 2, 1, created_on desc LIMIT " . $offset . " ," . $per_page;
                 //BOF:mod 20120711
             } elseif ($datesort == 'a') {
                 $query = $query_main . " order by created_on, new_order ASC, 2, 1 LIMIT " . $offset . " ," . $per_page;
             } elseif ($datesort == 'd') {
                 $query = $query_main . " order by created_on desc, new_order ASC, 2, 1 LIMIT " . $offset . " ," . $per_page;
             }
             //EOF:mod 20120711
             //EOF:mod 20110706 ticketid222
             $result = mysql_query($query, $link);
             while ($info = mysql_fetch_assoc($result)) {
                 $ids[] = $info['id'];
                 $rows[] = $info;
             }
             foreach ($rows as $row) {
                 $item_class = array_var($row, 'type');
                 $item = new $item_class();
                 $item->loadFromRow($row);
                 $add_item = true;
                 if ($_GET['complete'] != '1') {
                     if ($item->getParentType() == 'Page') {
                         $temp_page = new Page($item->getParentId());
                         $is_archived = $temp_page->getIsArchived();
                         if ($is_archived) {
                             $add_item = false;
                         }
                     }
                     if ($add_item) {
                         $temp_obj = new ProjectObject($item->getParentId());
                         if ($temp_obj->isCompleted()) {
                             $add_item = false;
                         }
                     }
                 }
                 if ($add_item) {
                     $items[] = $item;
                 }
             }
             if (empty($search_object_type)) {
                 //$items = ProjectObjects::findBySQL("SELECT $project_objects_table.* FROM $project_objects_table, $search_index_table WHERE $type_filter AND MATCH ($search_index_table.content) AGAINST (? IN BOOLEAN MODE) AND $project_objects_table.id = $search_index_table.object_id AND $search_index_table.type = ? AND state >= ? AND visibility >= ? LIMIT $offset, $per_page", array($search_for, $type, STATE_VISIBLE, $user->getVisibility()));
             } else {
                 //$items = ProjectObjects::findBySQL("SELECT $project_objects_table.* FROM $project_objects_table, $search_index_table WHERE $type_filter AND MATCH ($search_index_table.content) AGAINST (? IN BOOLEAN MODE) AND $project_objects_table.id = $search_index_table.object_id AND $search_index_table.type = ? AND state >= ? AND visibility >= ? AND $project_objects_table.type = ? LIMIT $offset, $per_page", array($search_for, $type, STATE_VISIBLE, $user->getVisibility(), $search_object_type));
             }
             //mysql_query("insert into healingcrystals_testing (query, fired_at) values ('" . mysql_real_escape_string($query) . "', now())", $link);
         } else {
             $items = null;
         }
         // if
         mysql_close($link);
         return array($items, new Pager($page, $total_items, $per_page), $count);
         // Search for projects
     } elseif ($type == 'Project') {
         $project_ids = Projects::findProjectIdsByUser($user, null, true);
         if (!is_foreachable($project_ids)) {
             return array(null, new Pager(1, 0, $per_page));
         }
         // if
         $projects_table = TABLE_PREFIX . 'projects';
         $total_items = (int) array_var(db_execute_one("SELECT COUNT({$projects_table}.id) AS 'row_count' FROM {$projects_table}, {$search_index_table} WHERE {$projects_table}.id IN (?) AND MATCH ({$search_index_table}.content) AGAINST (? IN BOOLEAN MODE) AND {$projects_table}.id = {$search_index_table}.object_id AND {$search_index_table}.type = ?", $project_ids, $search_for, 'Project'), 'row_count');
         if ($total_items) {
             $items = Projects::findBySQL("SELECT * FROM {$projects_table}, {$search_index_table} WHERE {$projects_table}.id IN (?) AND MATCH ({$search_index_table}.content) AGAINST (? IN BOOLEAN MODE) AND {$projects_table}.id = {$search_index_table}.object_id AND {$search_index_table}.type = ? LIMIT {$offset}, {$per_page}", array($project_ids, $search_for, 'Project'));
         } else {
             $items = null;
         }
         // if
         return array($items, new Pager($page, $total_items, $per_page));
         // Search for users
     } elseif ($type == 'User') {
         $user_ids = $user->visibleUserIds();
         if (!is_foreachable($user_ids)) {
             return array(null, new Pager(1, 0, $per_page));
         }
         // if
         $users_table = TABLE_PREFIX . 'users';
         $total_items = (int) array_var(db_execute_one("SELECT COUNT({$users_table}.id) AS 'row_count' FROM {$users_table}, {$search_index_table} WHERE {$users_table}.id IN (?) AND MATCH ({$search_index_table}.content) AGAINST (? IN BOOLEAN MODE) AND {$users_table}.id = {$search_index_table}.object_id AND {$search_index_table}.type = ?", $user_ids, $search_for, 'User'), 'row_count');
         if ($total_items) {
             $items = Users::findBySQL("SELECT * FROM {$users_table}, {$search_index_table} WHERE {$users_table}.id IN (?) AND MATCH ({$search_index_table}.content) AGAINST (? IN BOOLEAN MODE) AND {$users_table}.id = {$search_index_table}.object_id AND {$search_index_table}.type = ? LIMIT {$offset}, {$per_page}", array($user_ids, $search_for, 'User'));
         } else {
             $items = null;
         }
         // if
         return array($items, new Pager($page, $total_items, $per_page));
         // Unknown search type
     } else {
         return array(null, new Pager(1, 0, $per_page));
     }
     // if
 }