function fn_get_discussions($params, $items_per_page)
{
// Init filter
$params = LastView::instance()->update('discussion', $params);
// Set default values to input params
$default_params = array('page' => 1, 'items_per_page' => $items_per_page);
$params = array_merge($default_params, $params);
// Define fields that should be retrieved
$fields = array('?:discussion_posts.*', '?:discussion_messages.message', '?:discussion_rating.rating_value', '?:discussion.*');
// Define sort fields
$sortings = array('object' => "?:discussion.object_type", 'name' => "?:discussion_posts.name", 'ip_address' => "?:discussion_posts.ip_address", 'timestamp' => "?:discussion_posts.timestamp", 'status' => "?:discussion_posts.status", 'date' => "?:orders.timestamp", 'total' => "?:orders.total");
$sorting = db_sort($params, $sortings, 'timestamp', 'desc');
$condition = $join = '';
if (isset($params['name']) && fn_string_not_empty($params['name'])) {
$condition .= db_quote(" AND ?:discussion_posts.name LIKE ?l", "%" . trim($params['name']) . "%");
}
if (isset($params['message']) && fn_string_not_empty($params['message'])) {
$condition .= db_quote(" AND ?:discussion_messages.message LIKE ?l", "%" . trim($params['message']) . "%");
}
if (!empty($params['type'])) {
$condition .= db_quote(" AND ?:discussion.type = ?s", $params['type']);
}
if (!empty($params['status'])) {
$condition .= db_quote(" AND ?:discussion_posts.status = ?s", $params['status']);
}
if (!empty($params['post_id'])) {
$condition .= db_quote(" AND ?:discussion_posts.post_id = ?i", $params['post_id']);
}
if (isset($params['ip_address']) && fn_string_not_empty($params['ip_address'])) {
$condition .= db_quote(" AND ?:discussion_posts.ip_address = ?s", fn_ip_to_db(trim($params['ip_address'])));
}
if (!empty($params['rating_value'])) {
$condition .= db_quote(" AND ?:discussion_rating.rating_value = ?i", $params['rating_value']);
}
if (!empty($params['object_type'])) {
$condition .= db_quote(" AND ?:discussion.object_type = ?s", $params['object_type']);
}
$condition .= fn_get_discussion_company_condition('?:discussion.company_id');
if (!empty($params['period']) && $params['period'] != 'A') {
list($params['time_from'], $params['time_to']) = fn_create_periods($params);
$condition .= db_quote(" AND (?:discussion_posts.timestamp >= ?i AND ?:discussion_posts.timestamp <= ?i)", $params['time_from'], $params['time_to']);
}
$join .= " INNER JOIN ?:discussion ON ?:discussion.thread_id = ?:discussion_posts.thread_id";
$join .= " INNER JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id";
$join .= " INNER JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id";
$limit = '';
if (!empty($params['items_per_page'])) {
$params['total_items'] = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE 1 {$condition}");
$limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']);
}
$posts = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:discussion_posts {$join} WHERE 1 {$condition} {$sorting} {$limit}");
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);
}
exit;
}
return array(CONTROLLER_STATUS_OK, fn_url());
}
if ($mode == 'delete_post' && defined('AJAX_REQUEST')) {
db_query("DELETE FROM ?:discussion_messages WHERE post_id = ?i", $_REQUEST['post_id']);
db_query("DELETE FROM ?:discussion_rating WHERE post_id = ?i", $_REQUEST['post_id']);
db_query("DELETE FROM ?:discussion_posts WHERE post_id = ?i", $_REQUEST['post_id']);
return array(CONTROLLER_STATUS_OK, fn_url());
}
return;
}
// No action for vendor at the index
if (Registry::get('runtime.company_id') && fn_allowed_for('MULTIVENDOR')) {
return;
}
$latest_posts = db_get_array("SELECT a.post_id, a.ip_address, a.status, a.timestamp, b.object_id, b.object_type as object_type, b.type as type, a.name, c.message, d.rating_value " . "FROM ?:discussion_posts as a INNER JOIN ?:discussion as b ON a.thread_id = b.thread_id ?p " . "LEFT JOIN ?:discussion_messages as c ON a.post_id = c.post_id LEFT JOIN ?:discussion_rating as d ON a.post_id = d.post_id " . "ORDER BY a.timestamp DESC LIMIT 5", fn_get_discussion_company_condition('b.company_id'));
if (!empty($latest_posts)) {
foreach ($latest_posts as $k => $v) {
$latest_posts[$k]['ip_address'] = fn_ip_from_db($v['ip_address']);
$latest_posts[$k]['object_data'] = fn_get_discussion_object_data($v['object_id'], $v['object_type'], DESCR_SL);
$latest_posts[$k]['rating'] = fn_get_discussion_rating($v['rating_value']);
}
}
Registry::get('view')->assign('discussion_objects', fn_get_discussion_objects());
Registry::get('view')->assign('latest_posts', $latest_posts);
if ($mode == 'delete_post' && defined('AJAX_REQUEST')) {
// FIXME - bad style
Registry::get('view')->display('addons/discussion/views/index/components/dashboard.tpl');
exit;
}
function fn_get_rating_list($object_type, $parent_object_id = '')
{
$object2parent_links = array('P' => array('table' => '?:categories', 'field' => 'category_id', 'join' => array('?:products_categories' => "?:discussion.object_id=?:products_categories.product_id AND ?:products_categories.link_type='M'", '?:categories' => "?:products_categories.category_id=?:categories.category_id")));
$query = db_quote(" object_type = ?s AND ?:discussion.type IN ('R', 'B') AND !(?:discussion_rating.rating_value IS NULL) ", $object_type);
$join = array();
if (isset($object2parent_links[$object_type]) && !empty($parent_object_id)) {
$path = db_get_field("SELECT id_path FROM {$object2parent_links[$object_type]['table']} WHERE {$object2parent_links[$object_type]['field']} = ?i", $parent_object_id);
$parent_object_ids = db_get_fields("SELECT {$object2parent_links[$object_type]['field']} FROM {$object2parent_links[$object_type]['table']} WHERE id_path LIKE ?l", "{$path}/%");
$parent_object_ids[] = $parent_object_id;
$query .= " AND {$object2parent_links[$object_type]['table']}.{$object2parent_links[$object_type]['field']} IN ('" . implode("','", $parent_object_ids) . "') AND {$object2parent_links[$object_type]['table']}.status='A'";
$join = $object2parent_links[$object_type]['join'];
}
if ($object_type == 'P') {
// Adding condition for the "Show out of stock products" setting
if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') {
$join["?:product_options_inventory AS inventory"] = "inventory.product_id=?:discussion.object_id";
$join['?:products'] = "?:products.product_id=?:discussion.object_id";
$query .= " AND IF(?:products.tracking='O', inventory.amount>0, ?:products.amount>0)";
}
}
$join_conditions = '';
foreach ($join as $table => $j_cond) {
$join_conditions .= " LEFT JOIN {$table} ON {$j_cond} ";
}
return db_get_hash_array("SELECT object_id, avg(rating_value) AS rating FROM ?:discussion " . "LEFT JOIN ?:discussion_rating ON ?:discussion.thread_id=?:discussion_rating.thread_id {$join_conditions} " . "WHERE ?p GROUP BY ?:discussion.thread_id ORDER BY rating DESC", 'object_id', $query . fn_get_discussion_company_condition('?:discussion.company_id'));
}
/**
* 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;
}
