I have been reading a lot of advice here regarding turning off Advanced Statistics module. I wanted to do so, but I didn't want to have to give up the Bestsellers module in the process. So I modified bestsellers.php to gather bestsellers data based on actual sales data rather than threshold, view stats, sales stats. I've tested it, although I wouldn't say thoroughly. This mod requires changing bestsellers.php only. There's no need to change templates, admin files, etc. The code from bestsellers.php is below and an explanation of how it works follows.
Code:
if (!defined('XCART_SESSION_START')) { header("Location: ../../"); die("Access denied"); }
x_load('product');
if (
!is_numeric($config['Bestsellers']['number_of_bestsellers'])
|| $config['Bestsellers']['number_of_bestsellers'] < 0
) {
$config['Bestsellers']['number_of_bestsellers'] = 0;
}
$bs_prod_ids = array();
/**
* Get products data for current category and store it into $products array
*/
$cat = isset($cat) ? intval($cat) : 0;
$search_query = '';
$bs_now = XC_TIME;
$bs_sale_cutoff = $bs_now - (86400 * 30); //Plan to create and set $config['Bestsellers']['bestsellers_sales_days']
if ($cat) {
$category_data = func_query_first("SELECT categoryid, lpos, rpos FROM $sql_tbl[categories] USE INDEX (PRIMARY) WHERE categoryid = '$cat'");
if ($category_data)
$result = func_query_hash("SELECT categoryid FROM xcart_categories USE INDEX (pa) WHERE lpos BETWEEN $category_data[lpos] AND $category_data[rpos] AND avail = 'Y'", "categoryid", false, true);
else
$result = '';
$cat_ids = array();
if (
is_array($result)
&& !empty($result)
) {
$cat_ids = array_keys($result);
} else {
$cat_ids[] = $cat;
}
$search_query = " AND $sql_tbl[products_categories].categoryid IN ('" . implode("','", $cat_ids) . "')";
unset($result);
}
/**
* Get product sales data from order details and orders tables and store in $products array
*/
$salesprod_data = func_query_hash("SELECT $sql_tbl[order_details].productid, SUM($sql_tbl[order_details].amount) AS number FROM $sql_tbl[products] INNER JOIN ($sql_tbl[orders] INNER JOIN $sql_tbl[order_details] ON $sql_tbl[orders].orderid=$sql_tbl[order_details].orderid) ON $sql_tbl[products].productid=$sql_tbl[order_details].productid WHERE ((($sql_tbl[orders].status) IN ('C', 'P')) AND (($sql_tbl[orders].date)>= '" . $bs_sale_cutoff . "') AND (($sql_tbl[products].forsale)='Y')) GROUP BY $sql_tbl[order_details].productid ORDER BY number DESC, $sql_tbl[orders].date DESC, $sql_tbl[products].add_date DESC LIMIT " . $config['Bestsellers']['number_of_bestsellers']);
$salesprod_ids = array();
if (
is_array($salesprod_data)
&& !empty($salesprod_data)
) {
$salesprod_ids = array_keys($salesprod_data);
}
$search_query .= " AND $sql_tbl[products].productid IN ('" . implode("','", $salesprod_ids) . "')";
unset($salesprod_data);
/**
* Search the bestsellers
*/
$bestsellers = func_search_products(
$search_query,
@$user_account['membershipid'],
"$sql_tbl[products].product"
);
if (is_array($bestsellers) && !empty($bestsellers)) {
foreach ($bestsellers as $k => $v) {
if (!empty($active_modules['Customer_Reviews']) && $config['Customer_Reviews']['customer_voting'] == 'Y') {
$bestsellers[$k]['rating_data'] = func_get_product_rating($bestsellers[$k]['productid']);
}
if (count($bestsellers) >= 6) {
$_limit_width = $config['Appearance']['thumbnail_width'];
$_limit_height = $config['Appearance']['thumbnail_height'];
$bestsellers[$k] = func_get_product_tmbn_dims($bestsellers[$k], $_limit_width, $_limit_height);
}
$bs_prod_ids[] = $bestsellers[$k]['productid'];
}
if (!empty($active_modules['Special_Offers'])) {
func_offers_check_products($logged_userid, $current_area, $bestsellers);
}
}
$smarty->assign_by_ref('bestsellers', $bestsellers);
The key line is the $salesprod_data line that performs the main query. It selects product IDs from the order_details table and sums the amounts for each instance of any given product ID. In addition, it makes sure the product is available for sale, and that the order date is within whatever time frame you choose [for instance, I have chosen the past 30 days; see the line that reads $bs_sale_cutoff = $bs_now - (86400 * 30);]. The results are grouped by productid and sorted by sum(amount) DESC, order date DESC, and product add date DESC. An array is generated consisting of the selected product IDs as keys. The limit is set to the number of bestsellers configured in the admin area. For instance, I have mine set to 10. So my code will choose the top 10 bestselling products ordered in the past 30 days.
By the way, the final result ($bestsellers = func_search_products) is actually sorted alphabetically by product name. I tried to use "SUM($sql_tbl[order_details].amount) DESC" for the orderby, but the func_search_products function (defined in func.product.php) doesn't recognize the order_details table. I may revisit that part to see if I can get it to sort by actual top units sold.
Some other key notes to remember: I placed the limit in the query discussed above and removed it from the actual bestsellers search function (the $bestsellers = func_search_products line). This is important because otherwise the resulting array of product IDs would contain all products sold in the past 30 days, which could result in products that actually sold fewer units being selected as bestsellers.
In addition, my particular file contains a few extras that aren't necessary to the basic bestsellers functionality. For instance, the code below
Code:
if (is_array($bestsellers) && !empty($bestsellers)) {
foreach ($bestsellers as $k => $v) {
if (!empty($active_modules['Customer_Reviews']) && $config['Customer_Reviews']['customer_voting'] == 'Y') {
$bestsellers[$k]['rating_data'] = func_get_product_rating($bestsellers[$k]['productid']);
}
if (count($bestsellers) >= 6) {
$_limit_width = $config['Appearance']['thumbnail_width'];
$_limit_height = $config['Appearance']['thumbnail_height'];
$bestsellers[$k] = func_get_product_tmbn_dims($bestsellers[$k], $_limit_width, $_limit_height);
}
$bs_prod_ids[] = $bestsellers[$k]['productid'];
}
if (!empty($active_modules['Special_Offers'])) {
func_offers_check_products($logged_userid, $current_area, $bestsellers);
}
}
serves to gather a bit more information about the products selected: rating and special offers data. I just like to have the rating stars and special offers thumbnail appear with the bestsellers as they do for featured products. This does require a change to the bestsellers templates so the information gathered is actually displayed. If you don't want this data, you can just remove the above lines of code. If you do like the idea, don't forget to also make the appropriate changes to the template files. I'm not going to go into that now, but I will if anyone asks.
Another portion of the code above allows for larger thumbnails to be used if the number of bestsellers is >= 6. This is for my particular design in which I display my bestsellers in a product slider if there are at least 6. You can remove this or modify as you like.
Also, notice that I build an array of the bestseller product IDs (see the line that reads $bs_prod_ids[] = $bestsellers[$k]['productid']). The $bs_prod_ids array is initiated near the top of the code. Again, this is for my particular design. I use this array to test other listed products (e.g., Featured products) to see whether they are in this array. If so, I have a "Hot Item" thumbnail that appears on the product image (similar to the Special Offers thumbnail). This is something you can delete or keep and make appropriate template modifications. I'll elaborate on this if anyone asks.
Oh, and this Bestsellers mod retains the ability to display bestsellers based on the category being viewed. It also retains the ability to display products and prices based on membership. And it is actually based on sales rather than sales and views. The drawback for new stores will be that there won't be any bestsellers listed until actual orders are received and have a status of Processed or Complete.
I have tested with different scenarios for several hours today and, thus far, everything has worked as intended. Of course, the first thing I did was turn off the Advanced Statistics module. Products ordered before the cutoff date (or associated with orders with status values other than P or C) were not counted in the sum of amounts in order_details. Products that are hidden or disabled were not counted. Bestsellers appeared under appropriate category lists. My limit is 10 and I currently have 12 different products in my order_details table. Appropriately, only the top 10 of these 12 were listed on my store's home page. The one thing I have not tested is the membership-related stuff. So if anyone tries this mod out and finds problems in that area, let me know.
I'm sorry for the loooong post, but I wanted to explain my intentions clearly. Again, this module was made for Gold 4.4.3, which I'm currently customizing and testing. If there are any problems, questions, or suggestions, feel free to reply.
Thanks,
Jill