function fn_get_discussion_posts($params, $items_per_page = 0)
{
// Set default values to input params
$default_params = array('page' => 1, 'thread_id' => 0, 'avail_only' => false, 'random' => false, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$thread_data = db_get_row("SELECT thread_id, type, object_type, object_id FROM ?:discussion WHERE thread_id = ?i ?p", $params['thread_id'], fn_get_discussion_company_condition('?:discussion.company_id'));
if ($thread_data['type'] == 'D') {
return array(array(), $params);
}
$thread_condition = fn_generate_thread_condition($thread_data);
$join = $fields = '';
if ($thread_data['type'] == 'C' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_messages.message";
if ($thread_data['type'] == 'C') {
$thread_condition .= " AND ?:discussion_messages.message <> ''";
}
}
if ($thread_data['type'] == 'R' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_rating.rating_value";
if ($thread_data['type'] == 'R') {
$thread_condition .= " AND ?:discussion_rating.rating_value > 0";
}
}
if ($params['avail_only'] == true) {
$thread_condition .= " AND ?:discussion_posts.status = 'A'";
}
$limit = '';
if (!empty($params['limit'])) {
$limit = db_quote("LIMIT ?i", $params['limit']);
} elseif (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE {$thread_condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$order_by = isset($params['random']) && $params['random'] == 'Y' ? 'RAND()' : '?:discussion_posts.timestamp DESC';
$posts = db_get_array("SELECT ?:discussion_posts.* {$fields} FROM ?:discussion_posts {$join} " . "WHERE {$thread_condition} ORDER BY ?p {$limit}", $order_by);
foreach ($posts as $k => $post) {
if (isset($post['ip_address'])) {
$posts[$k]['ip_address'] = fn_ip_from_db($post['ip_address']);
}
}
return array($posts, $params);
}
/**
* This function extends fn_get_discussion_posts
*/
function fn_get_review_posts($thread_id = 0, $page = 1, $limit = '', $random = false, $items_per_page = 0)
{
var_dump("test");
// Set default values to input params
$default_params = array('page' => 1, 'thread_id' => 0, 'avail_only' => false, 'random' => false, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, array('thread_id' => $thread_id, 'page' => $page, 'limit' => $limit, 'random' => $random));
$thread_data = db_get_row("SELECT thread_id, type, object_type, object_id FROM ?:discussion WHERE thread_id = ?i ?p", $params['thread_id'], fn_get_discussion_company_condition('?:discussion.company_id'));
if (AREA == 'C') {
$params['avail_only'] = true;
if (!$items_per_page) {
$discussion_object_types = array('P' => 'product', 'C' => 'category', 'A' => 'page', 'O' => 'order', 'E' => 'home_page');
//get the values from the settings
$getDiscutionsPostPerPageSettings = db_get_field("\n SELECT ?:settings_objects.value\n FROM ?:settings_objects\n JOIN ?:settings_sections ON ?:settings_sections.section_id = ?:settings_objects.section_id\n WHERE \n ?:settings_sections.name = 'discussion'\n AND ?:settings_objects.name=?s\n ", $discussion_object_types[$thread_data["object_type"]] . "_posts_per_page");
if ($getDiscutionsPostPerPageSettings) {
$params['items_per_page'] = intval($getDiscutionsPostPerPageSettings);
}
}
}
if ($thread_data['type'] == 'D') {
return array(array(), $params);
}
$join = $fields = '';
if ($thread_data['type'] == 'C' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_messages.message";
$fields .= ", ?:discussion_messages.user_age";
$fields .= ", ?:discussion_messages.image1";
$fields .= ", ?:discussion_messages.image2";
$fields .= ", ?:discussion_messages.user_product_ordered";
}
if ($thread_data['type'] == 'R' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_rating.rating_value";
}
$thread_condition = fn_generate_thread_condition($thread_data);
if ($params['avail_only'] == true) {
$thread_condition .= " AND ?:discussion_posts.status = 'A'";
}
$limit = '';
if (!empty($params['limit'])) {
$limit = db_quote("LIMIT ?i", $params['limit']);
} elseif (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts WHERE {$thread_condition}", $params['thread_id']);
if ($_REQUEST['page']) {
$params['page'] = $_REQUEST['page'];
}
$limit = db_paginate($params['page'], $params['items_per_page']);
}
$sort = isset($_REQUEST['sort_review']) && $_REQUEST['sort_review'] != 'DF' ? $_REQUEST['sort_review'] : '';
// if discussion type is C do not sort by rating
if ($thread_data['type'] == 'C' && ($sort == 'HR' || $sort == 'LR')) {
$sort = '';
}
switch ($sort) {
case 'MH':
$fields .= ', SUM(?:review_likes.is_like) as is_l';
$order_by = 'is_l DESC';
$join .= ' LEFT JOIN ?:review_likes ON ?:discussion_posts.post_id = ?:review_likes.post_id ';
$thread_condition .= ' GROUP BY ?:discussion_posts.post_id ';
break;
case 'HR':
$fields .= ', AVG(?:review_rating.rating) as rating';
$order_by = 'rating DESC';
$join .= ' JOIN ?:review_rating ON ?:discussion_posts.post_id = ?:review_rating.post_id ';
$thread_condition .= ' GROUP BY ?:discussion_posts.post_id ';
break;
case 'LR':
$fields .= ', AVG(?:review_rating.rating) as rating';
$order_by = 'rating ASC';
$join .= ' JOIN ?:review_rating ON ?:discussion_posts.post_id = ?:review_rating.post_id ';
$thread_condition .= ' GROUP BY ?:discussion_posts.post_id ';
break;
case 'OD':
$order_by = '?:discussion_posts.timestamp ASC';
break;
default:
$fields .= ', SUM(?:review_likes.is_like) as is_l';
$order_by = 'is_l DESC';
$join .= ' LEFT JOIN ?:review_likes ON ?:discussion_posts.post_id = ?:review_likes.post_id ';
$thread_condition .= ' GROUP BY ?:discussion_posts.post_id ';
}
$posts = db_get_array("SELECT ?:discussion_posts.* {$fields} FROM ?:discussion_posts {$join} " . "WHERE {$thread_condition} ORDER BY ?p {$limit}", $order_by);
if (!empty($posts) && is_array($posts)) {
foreach ($posts as $k => $post) {
$is_like = db_get_field("SELECT COUNT(is_like) FROM ?:review_likes WHERE post_id = ?i AND is_like = '1'", $post['post_id']);
$votes = db_get_field('SELECT COUNT(is_like) FROM ?:review_likes WHERE post_id = ?i', $post['post_id']);
$posts[$k]['likes']['yes'] = $is_like ? $is_like : 0;
$posts[$k]['likes']['votes'] = $votes ? $votes : 0;
}
}
return $posts;
}
/**
* Gets discussion posts
*
* @param array $params Request parameters
* @param int $items_per_page Amount of posts per page
* @return array Array with posts and query parameters
*/
function fn_get_discussion_posts($params, $items_per_page = 0)
{
/**
* Modifies request parameters for fetching discussion posts
* @param array $params Query parameters
* @param int $items_per_page Amount of posts per page
*/
fn_set_hook('get_discussion_posts_pre', $params, $items_per_page);
// Set default values to input params
$default_params = array('page' => 1, 'thread_id' => 0, 'avail_only' => false, 'random' => false, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
$thread_data = db_get_row("SELECT thread_id, type, object_type, object_id FROM ?:discussion WHERE thread_id = ?i ?p", $params['thread_id'], fn_get_discussion_company_condition('?:discussion.company_id'));
if ($thread_data['type'] == 'D') {
return array(array(), $params);
}
$condition = fn_generate_thread_condition($thread_data);
$join = $fields = '';
if ($thread_data['type'] == 'C' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_messages.message";
if ($thread_data['type'] == 'C') {
$condition .= " AND ?:discussion_messages.message <> ''";
}
}
if ($thread_data['type'] == 'R' || $thread_data['type'] == 'B') {
$join .= " LEFT JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id ";
$fields .= ", ?:discussion_rating.rating_value";
if ($thread_data['type'] == 'R') {
$condition .= " AND ?:discussion_rating.rating_value > 0";
}
}
if ($params['avail_only'] == true) {
$condition .= " AND ?:discussion_posts.status = 'A'";
}
$limit = '';
if (!empty($params['limit'])) {
$limit = db_quote("LIMIT ?i", $params['limit']);
} elseif (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$order_by = isset($params['random']) && $params['random'] == 'Y' ? 'RAND()' : '?:discussion_posts.timestamp DESC';
/**
* Modifies discuission posts query
*
* @param array $params Query parameters
* @param int $items_per_page Amount of posts per page
* @param string $fields String of comma-separated SQL fields to be selected in an SQL-query
* @param string $join String with the complete JOIN information (JOIN type, tables and fields) for an SQL-query
* @param string $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR)
* @param string $order_by String containing SQL-query ordering conditions
* @param string $limit String containing SQL-query limit conditions
*/
fn_set_hook('get_discussion_posts', $params, $items_per_page, $fields, $join, $condition, $order_by, $limit);
$posts = db_get_array("SELECT ?:discussion_posts.* {$fields} FROM ?:discussion_posts {$join} " . "WHERE {$condition} ORDER BY ?p {$limit}", $order_by);
foreach ($posts as $k => $post) {
if (isset($post['ip_address'])) {
$posts[$k]['ip_address'] = fn_ip_from_db($post['ip_address']);
}
}
/**
* Modifies discussion posts and request parameters
*
* @param array $params Query parameters
* @param int $items_per_page Amount of posts per page
* @param array $posts Discussion posts
*/
fn_set_hook('get_discussion_posts_post', $params, $items_per_page, $posts);
return array($posts, $params);
}
