public function get_search_request($args)
{
$now = new Date();
$authorized_categories = ArticlesService::get_authorized_categories(Category::ROOT_CATEGORY);
$weight = isset($args['weight']) && is_numeric($args['weight']) ? $args['weight'] : 1;
return "SELECT " . $args['id_search'] . " AS id_search,\n\t\t\tarticles.id AS id_content,\n\t\t\tarticles.title AS title,\n\t\t\t(2 * FT_SEARCH_RELEVANCE(articles.title, '" . $args['search'] . "') + (FT_SEARCH_RELEVANCE(articles.contents, '" . $args['search'] . "') +\n\t\t\tFT_SEARCH_RELEVANCE(articles.description, '" . $args['search'] . "')) / 2 ) / 3 * " . $weight . " AS relevance,\n\t\t\tCONCAT('" . PATH_TO_ROOT . "/articles/index.php?url=/', id_category, '-', IF(id_category != 0, cat.rewrited_name, 'root'), '/', articles.id, '-', articles.rewrited_title) AS link\n\t\t\tFROM " . ArticlesSetup::$articles_table . " articles\n\t\t\tLEFT JOIN " . ArticlesSetup::$articles_cats_table . " cat ON cat.id = articles.id_category\n\t\t\tWHERE ( FT_SEARCH(articles.title, '" . $args['search'] . "') OR FT_SEARCH(articles.contents, '" . $args['search'] . "') OR FT_SEARCH_RELEVANCE(articles.description, '" . $args['search'] . "') )\n\t\t\tAND id_category IN(" . implode(", ", $authorized_categories) . ")\n\t\t\tAND (published = 1 OR (published = 2 AND publishing_start_date < '" . $now->get_timestamp() . "' AND (publishing_end_date > '" . $now->get_timestamp() . "' OR publishing_end_date = 0)))\n\t\t\tORDER BY relevance DESC\n\t\t\tLIMIT 100 OFFSET 0";
}
private function build_view($request)
{
$now = new Date();
$authorized_categories = ArticlesService::get_authorized_categories(Category::ROOT_CATEGORY);
$config = ArticlesConfig::load();
$mode = $request->get_getstring('sort', 'desc');
$field = $request->get_getstring('field', 'date');
$sort_mode = $mode == 'asc' ? 'ASC' : 'DESC';
switch ($field) {
case 'title':
$sort_field = 'title';
break;
case 'view':
$sort_field = 'number_view';
break;
case 'com':
$sort_field = 'number_comments';
break;
case 'note':
$sort_field = 'average_notes';
break;
case 'author':
$sort_field = 'display_name';
break;
default:
$sort_field = 'date_created';
break;
}
$condition = 'WHERE id_category IN :authorized_categories
' . (!ArticlesAuthorizationsService::check_authorizations()->moderation() ? ' AND author_user_id = :user_id' : '') . '
AND (published = 0 OR (published = 2 AND (publishing_start_date > :timestamp_now OR (publishing_end_date != 0 AND publishing_end_date < :timestamp_now))))';
$parameters = array('authorized_categories' => $authorized_categories, 'user_id' => AppContext::get_current_user()->get_id(), 'timestamp_now' => $now->get_timestamp());
$page = AppContext::get_request()->get_getint('page', 1);
$pagination = $this->get_pagination($condition, $parameters, $field, $mode, $page);
$result = PersistenceContext::get_querier()->select('SELECT articles.*, member.*, com.number_comments, notes.number_notes, notes.average_notes, note.note
FROM ' . ArticlesSetup::$articles_table . ' articles
LEFT JOIN ' . DB_TABLE_MEMBER . ' member ON member.user_id = articles.author_user_id
LEFT JOIN ' . DB_TABLE_COMMENTS_TOPIC . ' com ON com.id_in_module = articles.id AND com.module_id = "articles"
LEFT JOIN ' . DB_TABLE_AVERAGE_NOTES . ' notes ON notes.id_in_module = articles.id AND notes.module_name = "articles"
LEFT JOIN ' . DB_TABLE_NOTE . ' note ON note.id_in_module = articles.id AND note.module_name = "articles" AND note.user_id = :user_id
' . $condition . '
ORDER BY ' . $sort_field . ' ' . $sort_mode . '
LIMIT :number_items_per_page OFFSET :display_from', array_merge($parameters, array('number_items_per_page' => $pagination->get_number_items_per_page(), 'display_from' => $pagination->get_display_from())));
$nbr_articles_pending = $result->get_rows_count();
$this->build_form($field, $mode);
$this->view->put_all(array('C_PENDING' => true, 'C_MOSAIC' => $config->get_display_type() == ArticlesConfig::DISPLAY_MOSAIC, 'C_NO_ARTICLE_AVAILABLE' => $nbr_articles_pending == 0));
if ($nbr_articles_pending > 0) {
$this->view->put_all(array('C_ARTICLES_FILTERS' => true, 'C_COMMENTS_ENABLED' => $config->are_comments_enabled(), 'C_NOTATION_ENABLED' => $config->is_notation_enabled(), 'C_PAGINATION' => $pagination->has_several_pages(), 'PAGINATION' => $pagination->display()));
while ($row = $result->fetch()) {
$article = new Article();
$article->set_properties($row);
$this->build_keywords_view($article);
$this->view->assign_block_vars('articles', $article->get_tpl_vars());
$this->build_sources_view($article);
}
}
$result->dispose();
$this->view->put('FORM', $this->form->display());
}
private function build_categories_listing_view(Date $now, $field, $mode, $page, $subcategories_page)
{
$authorized_categories = ArticlesService::get_authorized_categories($this->get_category()->get_id());
$subcategories_number = count(ArticlesService::get_categories_manager()->get_categories_cache()->get_childrens($this->category->get_id()));
$pagination = $this->get_subcategories_pagination($subcategories_number, $this->config->get_number_categories_per_page(), $field, $mode, $page, $subcategories_page);
$result = PersistenceContext::get_querier()->select('SELECT @id_cat:= id, articles_cats.*,
(SELECT COUNT(*) FROM ' . ArticlesSetup::$articles_table . '
WHERE id_category IN (
@id_cat,
(SELECT GROUP_CONCAT(id SEPARATOR \',\') FROM ' . ArticlesSetup::$articles_cats_table . ' WHERE id_parent = @id_cat),
(SELECT GROUP_CONCAT(childs.id SEPARATOR \',\') FROM ' . ArticlesSetup::$articles_cats_table . ' parents
INNER JOIN ' . ArticlesSetup::$articles_cats_table . ' childs ON parents.id = childs.id_parent
WHERE parents.id_parent = @id_cat)
)
AND (published = 1 OR (published = 2 AND publishing_start_date < :timestamp_now AND (publishing_end_date > :timestamp_now OR publishing_end_date = 0)))
) AS articles_number
FROM ' . ArticlesSetup::$articles_cats_table . ' articles_cats
WHERE id_parent = :id_category AND id IN :authorized_categories
ORDER BY id_parent, c_order
LIMIT :number_items_per_page OFFSET :display_from', array('timestamp_now' => $now->get_timestamp(), 'id_category' => $this->category->get_id(), 'authorized_categories' => $authorized_categories, 'number_items_per_page' => $pagination->get_number_items_per_page(), 'display_from' => $pagination->get_display_from()));
$nbr_cat_displayed = 0;
while ($row = $result->fetch()) {
$category_image = new Url($row['image']);
$this->view->assign_block_vars('sub_categories_list', array('C_CATEGORY_IMAGE' => !empty($row['image']), 'C_MORE_THAN_ONE_ARTICLE' => $row['articles_number'] > 1, 'CATEGORY_NAME' => $row['name'], 'CATEGORY_IMAGE' => $category_image->rel(), 'ARTICLES_NUMBER' => $row['articles_number'], 'U_CATEGORY' => ArticlesUrlBuilder::display_category($row['id'], $row['rewrited_name'])->rel()));
if (!empty($row['id'])) {
$nbr_cat_displayed++;
}
}
$result->dispose();
$nbr_column_cats = $nbr_cat_displayed > $this->config->get_number_cols_display_cats() ? $this->config->get_number_cols_display_cats() : $nbr_cat_displayed;
$nbr_column_cats = !empty($nbr_column_cats) ? $nbr_column_cats : 1;
$cats_columns_width = floor(100 / $nbr_column_cats);
$category_description = FormatingHelper::second_parse($this->get_category()->get_description());
$this->view->put_all(array('C_CATEGORY' => true, 'C_ROOT_CATEGORY' => $this->get_category()->get_id() == Category::ROOT_CATEGORY, 'C_HIDE_NO_ITEM_MESSAGE' => $this->get_category()->get_id() == Category::ROOT_CATEGORY && ($nbr_cat_displayed != 0 || !empty($category_description)), 'C_CATEGORY_DESCRIPTION' => !empty($category_description), 'C_SUB_CATEGORIES' => $nbr_cat_displayed > 0, 'C_SUBCATEGORIES_PAGINATION' => $pagination->has_several_pages(), 'CATEGORY_NAME' => $this->get_category()->get_name(), 'CATEGORY_IMAGE' => $this->get_category()->get_image()->rel(), 'CATEGORY_DESCRIPTION' => $category_description, 'SUBCATEGORIES_PAGINATION' => $pagination->display(), 'CATS_COLUMNS_WIDTH' => $cats_columns_width));
}
