/**
* 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
}
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
}