X-Cart forums

[jillsybte]

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.

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

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

[IPR]

Jillsbyte, thank you for a great and simple alteration to the module. Would it be possible to make it show up in two seperate time frames? For example, if I wanted one block that shows "What's hot!!" that shows the sales for the last 10 days, and another block that is "All Time Favorites" that show the top ten products for the last two years?

[jillsybte]

Originally Posted by IPR

Jillsbyte, thank you for a great and simple alteration to the module. Would it be possible to make it show up in two seperate time frames? For example, if I wanted one block that shows "What's hot!!" that shows the sales for the last 10 days, and another block that is "All Time Favorites" that show the top ten products for the last two years?

OK, this is off the top of my head and I have NOT tested it, but here goes:

You would need two more variables in the top section. All order dates should be greater than 0 since they're Unix timestamps:

$search_query_all = '';

$bs_sale_cutoff_all = 0;

After this line:

$search_query = " AND $sql_tbl[products_categories].categoryid IN ('" . implode("','", $cat_ids) . "')";

add:

$search_query_all = $search_query;

This will allow your all-time search query to also capture category-specific products.

Then you would need to run a different query and search of the bestsellers to capture the all-time bestsellers (using the $bs_sale_cutoff_all rather than $bs_sale_cutoff that is used in the date-limited query):

$salesprod_data_all = 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_all . "') 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_all_ids = array();
if (
    is_array($salesprod_data_all)
    && !empty($salesprod_data_all)
) {
    $salesprod_all_ids = array_keys($salesprod_data_all);
}
$search_query_all .= " AND $sql_tbl[products].productid IN ('" . implode("','", $salesprod_all_ids) . "')";
unset($salesprod_data_all);
 
$bestsellers_all = func_search_products(
    $search_query_all,
    @$user_account['membershipid'],
    "$sql_tbl[products].product"
);
$smarty->assign_by_ref('bestsellers_all', $bestsellers_all);

You will also need to duplicate the code in bestsellers.tpl, substituting $bestsellers_all for $bestsellers. This additional code assumes you will use the same number of all-time bestsellers as for date-limited bestsellers.

Again, I haven't tested this so I'm not making any promises.

[mltriebe]

Seems to work very well for version 4.3.2 as well.

Thanks, Mike

[jillsybte]

Originally Posted by mltriebe

Seems to work very well for version 4.3.2 as well.

Thanks, Mike

That's great. I haven't had a chance to try it yet, but it should work in 4.4.4 as well as 4.4.3. I don't believe any of the Bestsellers files changed between the two versions.

[mltriebe]

Actually I do seem to be having issues with these 2 lines:

$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);

Specifically lpos and rpos do not exist in 4.3.2.

I will keep working at it but any help would be great.

Mike

[jillsybte]

Originally Posted by mltriebe

Actually I do seem to be having issues with these 2 lines:

$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);

Specifically lpos and rpos do not exist in 4.3.2.

I will keep working at it but any help would be great.

Mike

Older versions of X-Cart used the field, categorid_path, in the categories table rather than lpos and rpos. I'm not sure when the change occurred, but I think it was with the 4.4.x branch. So your categories table in 4.3.2 should be similar to the categories table in my current live 4.1.8 store and the 4.2.3 version I tested some time ago. I looked at bestsellers.php for 4.2.3 and, based on that, modified the category IDs section to this:

if ($cat) {
 $category_data = func_query_first("SELECT categoryid_path FROM $sql_tbl[categories] USE INDEX (PRIMARY) WHERE categoryid = '$cat'");
 $result = func_query_hash("SELECT categoryid FROM $sql_tbl[categories] USE INDEX (pa) WHERE categoryid_path LIKE '$category_data[categoryid_path]/%' AND avail = 'Y'", "categoryid", false, true);
 $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);
}

I don't have any way of testing this code, but it should work in your version unless major changes occurred between 4.2.3 and 4.3.2. Hope that helps.

[mltriebe]

Finally got it to work for the most part. I had to remove the /% from this:

$category_data[categoryid_path]/%' AND avail = 'Y'

Now how do I change it to 60 days? I changed this to 60 but it didn't work (86400 * 30)?

Mike

[jillsybte]

Originally Posted by mltriebe

Finally got it to work for the most part. I had to remove the /% from this:

$category_data[categoryid_path]/%' AND avail = 'Y'

Now how do I change it to 60 days? I changed this to 60 but it didn't work (86400 * 30)?

Mike

86400 is the number of seconds in a day (60x60x24). So leave the 86400* and just change the 30 to whatever number of days you prefer; the result will be the number of seconds in that many days. If you simply replaced (86400 * 30) with 60, you would be basing your bestsellers on orders from the last minute only.

[mltriebe]

Originally Posted by jillsybte

86400 is the number of seconds in a day (60x60x24). So leave the 86400* and just change the 30 to whatever number of days you prefer; the result will be the number of seconds in that many days. If you simply replaced (86400 * 30) with 60, you would be basing your bestsellers on orders from the last minute only.

Sorry, I should have been more clear. I changed the 30 to 60 and 90 but the results never changed. I also have changed XC_TIME to time() with no luck as well.

I have also just noticed it is not getting the items correctly either. I know I sold 50 pieces of 3mm carbon in one order and 24 pieces of 1mm carbon but 1mm is listed first and 3mm 4th on the list. and some items don't show at all that should be.

It is getting late and tomorrow is another day.

Thanks, Mike