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; }