[Jon]

I've never much cared for the Best Sellers list of X-Cart.

I wrote up this query to grab the best sellers for a category on a monthly basis.

<?
#-----------------------------------------
# Monthly Best Sellers
# http://www.websitecm.com/
#-----------------------------------------

$start_date = strtotime(date("Y-m-01"));
$end_date = strtotime(date("Y-m-t"));

// The category to pull products from
$month_cat = '20';

// The number of products to show
$month_limit = '4';

$query = "SELECT $sql_tbl[products].product,$sql_tbl[products].distribution, $sql_tbl[products].list_price, $sql_tbl[products].vat, $sql_tbl[products].product_type,$sql_tbl[products].min_amount,$sql_tbl[products].productid,$sql_tbl[products].avail,$sql_tbl[products].categoryid,$sql_tbl[products].forsale,$sql_tbl[pricing].*,$sql_tbl[order_details].productid,$sql_tbl[order_details].orderid,$sql_tbl[orders].orderid,$sql_tbl[orders].date, count($sql_tbl[order_details].productid) as sold FROM $sql_tbl[pricing],$sql_tbl[products],$sql_tbl[orders],$sql_tbl[order_details] WHERE $sql_tbl[orders].date > '$start_date' AND $sql_tbl[orders].date < '$end_date' AND $sql_tbl[order_details].orderid=$sql_tbl[orders].orderid AND $sql_tbl[products].productid=$sql_tbl[order_details].productid AND $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[products].categoryid='$month_cat' AND $sql_tbl[products].forsale='Y' AND $sql_tbl[products].avail>0 GROUP BY $sql_tbl[order_details].productid ORDER BY sold DESC LIMIT $month_limit";

$month_sellers = func_query($query);

$smarty->assign("month_sellers",$month_sellers);

?>

You can then use it on the template using something like:

{if $month_sellers ne ""}


{capture name=dialog}
{include file="customer/main/products.tpl" products=$month_sellers featured="Y"}
{/capture}
{include file="dialog.tpl" title="This Months Hottest Sellers!" content=$smarty.capture.dialog extra="width=100%"}
{/if}