/** * Return all groups ordered by name * * This function will return only groups visible to given user * * If $return_all is set to true all groups will be loaded and returned. * This is used in situations where we need all of them regardels of user * previous assignments (like select project group helper) * * @param User $user * @param boolean $return_all * @return array */ function findAll($user, $return_all = false) { if ($return_all || $user->isAdministrator() || $user->isProjectManager()) { return ProjectGroups::find(array('order' => 'name')); } // if $project_ids = Projects::findProjectIdsByUser($user); if (is_foreachable($project_ids)) { $projects_table = TABLE_PREFIX . 'projects'; $project_groups_table = TABLE_PREFIX . 'project_groups'; return ProjectGroups::findBySQL("SELECT DISTINCT {$project_groups_table}.* FROM {$projects_table}, {$project_groups_table} WHERE {$project_groups_table}.id = {$projects_table}.group_id AND {$projects_table}.id IN (?) ORDER BY {$project_groups_table}.name", array($project_ids)); } else { return null; } // if }
function search($search_for, $type, $user, $page = 1, $per_page = 30, $search_object_type = '', $search_under_project_id = '', $datesort = '') { //EOF:mod 20120711 $page = (int) $page; $per_page = (int) $per_page; $search_index_table = TABLE_PREFIX . 'search_index'; $offset = ($page - 1) * $per_page; // Search in projects if ($type == 'ProjectObject') { $type_filter = ProjectUsers::getVisibleTypesFilter($user, array(PROJECT_STATUS_ACTIVE, PROJECT_STATUS_PAUSED, PROJECT_STATUS_COMPLETED, PROJECT_STATUS_CANCELED)); if (empty($type_filter)) { return array(null, new Pager(1, 0, $per_page)); } // if if (strlen($search_for) <= 2) { return array(null, new Pager(1, 0, $per_page)); } //BOF:mod 20111102 // $search_for = str_replace(' ', '% %', $search_for); //EOF:mod 20111102 $project_objects_table = TABLE_PREFIX . 'project_objects'; //$total_items = (integer) array_var(db_execute_one("SELECT COUNT($project_objects_table.id) AS 'row_count' 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 >= ?", $search_for, $type, STATE_VISIBLE, $user->getVisibility()), 'row_count'); /*if (empty($search_object_type)){ $total_items = (integer) array_var(db_execute_one("SELECT COUNT($project_objects_table.id) AS 'row_count' 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 >= ?", $search_for, $type, STATE_VISIBLE, $user->getVisibility()), 'row_count'); } else { $total_items = (integer) array_var(db_execute_one("SELECT COUNT($project_objects_table.id) AS 'row_count' 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 = ?", $search_for, $type, STATE_VISIBLE, $user->getVisibility(), $search_object_type), 'row_count'); }*/ $complete_str = ''; if ($_GET['complete'] != '1') { $complete_str = " and healingcrystals_project_objects.completed_on is null and (healingcrystals_project_objects.completed_by_id is null or healingcrystals_project_objects.completed_by_id='0') and healingcrystals_project_objects.boolean_field_1 is null "; } $link = mysql_connect(DB_HOST, DB_USER, DB_PASS); mysql_select_db(DB_NAME); $query_main = "(select healingcrystals_sort_order_for_search.sort_order, if(healingcrystals_project_objects.completed_on is null, '0', '1'), '0' as new_order, healingcrystals_project_objects.* \n\t\t \t\t from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " \n\t\t\t\t and (healingcrystals_project_objects.name is not null) \n\t\t\t\t and (healingcrystals_project_objects.name like '%" . addslashes($search_for) . "%') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )\n\t\t\t\t UNION \n\t\t\t\t (select healingcrystals_sort_order_for_search.sort_order, if(healingcrystals_project_objects.completed_on is null, '0', '1'), '0' as new_order, healingcrystals_project_objects.* \n\t\t \t\t from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " \n\t\t\t\t and (healingcrystals_project_objects.body like '%" . addslashes($search_for) . "%') and \n\t\t\t\t (healingcrystals_project_objects.name is null or healingcrystals_project_objects.name='') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " ) "; $count_query_main = "(select * \n\t\t \t\t from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " and (healingcrystals_project_objects.name is not null) \n\t\t\t\t and (healingcrystals_project_objects.name like '%" . addslashes($search_for) . "%') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )\n\t\t\t\t UNION \n\t\t\t\t (select * from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " \n\t\t\t\t and (healingcrystals_project_objects.body like '%" . addslashes($search_for) . "%') and \n\t\t\t\t (healingcrystals_project_objects.name is null or healingcrystals_project_objects.name='') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " ) "; //BOF-20120216 if (strpos($search_for, ' ') !== false) { $all_parts_in_name_string = ''; $all_parts_in_body_string = ''; $few_parts_in_name_string = ''; $string_parts = explode(' ', $search_for); $query_parts_name = array(); $query_parts_description = array(); foreach ($string_parts as $part) { $query_parts_name[] = "healingcrystals_project_objects.name like '%" . addslashes($part) . "%'"; $query_parts_description[] = "healingcrystals_project_objects.body like '%" . addslashes($part) . "%'"; } $all_parts_in_name_string = implode(" and ", $query_parts_name); $all_parts_in_body_string = implode(" and ", $query_parts_description); $few_parts_in_name_string = implode(" or ", $query_parts_name); $few_parts_in_body_string = implode(" or ", $query_parts_description); $query_main = "(select distinct(healingcrystals_project_objects.id), healingcrystals_sort_order_for_search.sort_order, if(healingcrystals_project_objects.completed_on is null, '0', '1'), if(healingcrystals_project_objects.name like '%" . addslashes($search_for) . "%','1', if(" . $all_parts_in_name_string . ",'3',if(" . $few_parts_in_name_string . ",'5','99'))) as new_order, healingcrystals_project_objects.* \n\t\t \t\t from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " \n\t\t\t\t and (healingcrystals_project_objects.name is not null) \n\t\t\t\t and ( (healingcrystals_project_objects.name like '%" . addslashes($search_for) . "%') or ( " . $all_parts_in_name_string . " ) or ( " . $few_parts_in_name_string . " ) ) \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )\n\t\t\t\t UNION \n\t\t\t\t (select distinct(healingcrystals_project_objects.id), healingcrystals_sort_order_for_search.sort_order, if(healingcrystals_project_objects.completed_on is null, '0', '1'), if(healingcrystals_project_objects.body like '%" . addslashes($search_for) . "%','2', if(" . $all_parts_in_body_string . ",'4',if(" . $few_parts_in_body_string . ",'6','99'))) as new_order, healingcrystals_project_objects.* \n\t\t \t\t from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " \n\t\t\t\t and ( (healingcrystals_project_objects.body like '%" . addslashes($search_for) . "%') or ( " . $all_parts_in_body_string . " ) or ( " . $few_parts_in_body_string . " ) ) \n\t\t\t\t and (healingcrystals_project_objects.name is null or healingcrystals_project_objects.name='') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )"; $count_query_main = "(select * from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . " and (healingcrystals_project_objects.name is not null) \n\t\t\t\t and ( (healingcrystals_project_objects.name like '%" . addslashes($search_for) . "%' ) or ( " . $all_parts_in_name_string . " ) or ( " . $few_parts_in_name_string . " ) ) \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )\n\t\t\t\t UNION \n\t\t\t\t (select * from healingcrystals_project_objects " . (!empty($search_under_project_id) ? " inner join healingcrystals_projects on (healingcrystals_project_objects.project_id=healingcrystals_projects.id and healingcrystals_project_objects.project_id='" . (int) $search_under_project_id . "') " : " ") . " inner join healingcrystals_search_index on healingcrystals_project_objects.id=healingcrystals_search_index.object_id \n\t\t \t\t left join healingcrystals_sort_order_for_search on healingcrystals_sort_order_for_search.type=healingcrystals_project_objects.type \n\t\t\t\t where " . $type_filter . $complete_str . "\n\t\t\t\t and ( (healingcrystals_project_objects.body like '%" . addslashes($search_for) . "%' ) or ( " . $all_parts_in_body_string . " ) or ( " . $few_parts_in_body_string . " ) ) \n\t\t\t\t and (healingcrystals_project_objects.name is null or healingcrystals_project_objects.name='') \n\t\t\t\t and healingcrystals_search_index.type='" . $type . "' \n\t\t\t\t and healingcrystals_project_objects.state='" . STATE_VISIBLE . "' " . (empty($search_object_type) ? "" : " and healingcrystals_project_objects.type='" . $search_object_type . "' ") . " )"; } $result = mysql_query($query_main, $link); $count_result = mysql_query($count_query_main, $link); $count = mysql_num_rows($count_result); $total_items = mysql_num_rows($result); //mysql_query("insert into testing (content, date_added) values ('" . mysql_real_escape_string($query_main) . "', now())"); if ($total_items) { $rows = array(); $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 }
/** * Show and process add to projects page * * @param void * @return null */ function add_to_projects() { if ($this->active_user->isNew()) { $this->httpError(HTTP_ERR_NOT_FOUND); } // if if (!$this->logged_user->isProjectManager()) { $this->httpError(HTTP_ERR_FORBIDDEN); } // if $add_to_projects_data = $this->request->post('add_to_projects'); $this->smarty->assign(array('add_to_projects_data' => $add_to_projects_data, 'exclude_project_ids' => Projects::findProjectIdsByUser($this->active_user))); if ($this->request->isSubmitted()) { $errors = new ValidationErrors(); $projects = null; if (is_foreachable($add_to_projects_data['projects'])) { $projects = Projects::findByIds($add_to_projects_data['projects']); } // if if (!is_foreachable($projects)) { $errors->addError(lang('Please select projects'), 'projects'); } // if if ($add_to_projects_data['role_id']) { $role = Roles::findById($add_to_projects_data['role_id']); $permissions = null; if (!instance_of($role, 'Role') || !($role->getType() == ROLE_TYPE_PROJECT)) { $errors->addError(lang('Invalid project role'), 'project_permissions'); } // if } else { $role = null; $permissions = array_var($add_to_projects_data, 'permissions'); } // if if ($errors->hasErrors()) { $this->smarty->assign('errors', $errors); } else { $added = 0; foreach ($projects as $project) { $add = $project->addUser($this->active_user, $role, $permissions); if ($add && !is_error($add)) { $added++; } // if } // foreach if ($added == 1) { flash_success(':name has been added to 1 project', array('name' => $this->active_user->getDisplayName())); } else { flash_success(':name has been added to :count projects', array('name' => $this->active_user->getDisplayName(), 'count' => $added)); } // if $this->redirectToUrl($this->active_user->getViewUrl()); } // if } // if }