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