/** * Paginate attachments by project * * @param Project $project * @param User $user * @param integer $page * @param integer $per_page * @param integer $min_state * @return array */ function paginateByProject($project, $user, $page = 1, $per_page = 30, $min_state = STATE_VISIBLE) { $attachments_table = TABLE_PREFIX . 'attachments'; $project_objects_table = TABLE_PREFIX . 'project_objects'; $type_filter = ProjectUsers::getVisibleTypesFilterByProject($user, $project); if ($type_filter) { $total = array_var(db_execute_one("SELECT COUNT({$attachments_table}.id) AS 'row_count' FROM {$attachments_table}, {$project_objects_table} WHERE {$attachments_table}.attachment_type = ? AND {$attachments_table}.parent_id = {$project_objects_table}.id AND {$type_filter} AND {$project_objects_table}.state >= ? AND {$project_objects_table}.visibility >= ?", ATTACHMENT_TYPE_ATTACHMENT, $min_state, $user->getVisibility()), 'row_count'); if ($total) { $offset = ($page - 1) * $per_page; $attachments = Attachments::findBySQL("SELECT {$attachments_table}.* FROM {$attachments_table}, {$project_objects_table} WHERE {$attachments_table}.attachment_type = ? AND {$attachments_table}.parent_id = {$project_objects_table}.id AND {$type_filter} AND {$project_objects_table}.state >= ? AND {$project_objects_table}.visibility >= ? ORDER BY `created_on` DESC LIMIT {$offset}, {$per_page}", array(ATTACHMENT_TYPE_ATTACHMENT, $min_state, $user->getVisibility())); if ($attachments) { return array($attachments, new Pager($page, $total, $per_page)); } // if } // if } // if return array(null, new Pager(1, 0, $per_page)); }
function search_attachments($search_for, $user, $page, $per_page, $search_object_type, $search_under_project_id, $datesort) { $page = (int) $page; $per_page = (int) $per_page; $offset = ($page - 1) * $per_page; $link = mysql_connect(DB_HOST, DB_USER, DB_PASS); mysql_select_db(DB_NAME); $sql = "select ::PLACE_HOLDER:: from healingcrystals_attachments a inner join healingcrystals_project_objects b on (a.parent_id=b.id" . (!empty($search_under_project_id) ? " and b.project_id='" . $search_under_project_id . "'" : "") . ") where a.name like '%" . $search_for . "%' "; $count_sql = str_replace('::PLACE_HOLDER::', "count(*) as row_count", $sql); $result = mysql_query($count_sql, $link); $info = mysql_fetch_assoc($result); $total_items = $info['row_count']; if ($total_items) { if (empty($datesort)) { $sql = str_replace('::PLACE_HOLDER::', 'a.id', $sql) . " order by a.name LIMIT " . $offset . " ," . $per_page; } elseif ($datesort == 'a') { $sql = str_replace('::PLACE_HOLDER::', 'a.id', $sql) . " order by a.created_on, a.name LIMIT " . $offset . " ," . $per_page; } elseif ($datesort == 'd') { $sql = str_replace('::PLACE_HOLDER::', 'a.id', $sql) . " order by a.created_on desc, a.name LIMIT " . $offset . " ," . $per_page; } $items = Attachments::findBySQL($sql); } else { $items = null; } mysql_close($link); return array($items, new Pager($page, $total_items, $per_page), $total_items); }