X-Cart forums

[jeremye]

Do you have register_globals turned on in your version of PHP? It looks like the variables that are being POSTed are not working, which makes it seem as if register_globals is not allowing the PHP vars to be automatically assigned.

Can you confirm if register_globals is on (check your phpinfo() file)?

[jeremye]

Going on a hunch that your problem is with register_globals, try adding this code to the top of your php file:

/* Assign variables if register_globals = OFF */
$StartMonth = $_POST['StartMonth'];
$StartDay = $_POST['StartDay'];
$StartYear	= $_POST['StartYear'];
$EndMonth	= $_POST['EndMonth'];
$EndDay = $_POST['EndDay'];
$EndYear = $_POST['EndYear'];
$report_type = $_POST['report_type'];

This will assign all the variables from the _POST array. Let me know if this works.

[jeremye]

For those of you using 3.4.14 and below, I added a few snippets of code to keep the page secure, meaning you have to be logged in to the admin area to view the report. My concern was that AS IS, the file has no security measures.

To secure your report, save the file to your [xcart]/admin/ directory.
Next go into your PHP file and add the following lines to the very top:

/* Added require fields so that you must be logged in to view sales reports */
require "../smarty.php";
require "../config.php";
require "./auth.php";
require "../include/security.php";

I know this works for my version of xcart, others may need to just look at another file in your /admin/ directory and see what include files they are requiring at the top of each page.

In order to access this, you must be logged into your admin area. Then you can copy/paste the URL, or create a special link in your admin area.

Hope this helps some of you.

[jeremye]

I know, I know, I wrote this MOD but I am having an issue with the speediness of my SQL query used for gathering product/order data across multiple tables. Here is my SQL query for sales reports between Aug 1 and Aug 3 (real small timespan):

SELECT tblproducts.product, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= 1091332800 AND tblorders.date <= 1091591999 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY total desc

The problem is that if I am running a sales report for a whole month or a few months, it runs SUPER slow. Any ideas on how I can optimize this query to make it run a lot faster? Thanks!

[garryhs]

Hi,

Just thought to let you all know, I created reporting, that sits in the Admin Area, and produces reports based on what you want.

Totals taxes, shipping, you can hide or display any field, use it as a shipping list for completed orders....

It was developed for 3.5.x, but I think it should work for 3.4.x.

Have not updated it or tested with version 4.x yet.

Take a look, I have some sample images at
http://www.x-cart-plus.com/au/xcart/customer/product.php?productid=6&cat=0&page=

[jeremye]

I've had some requests for updates to this script (which hasn't been updated in quite some time). I made modifications to allow for the admin to choose what order status values the report should include. Note the new "Order Status" dropdown at the top. Here's the code, let me know if you have any questions:

<?
/*****************************************************************************\
	Custom Sales Reports for X-Cart (versions 3.4.8+)
	Created 02/23/04 by Jeremy Eastburn
	Updated 08/23/05 by Jeremy Eastburn (added order status dropdown)
\*****************************************************************************/

/* Added require fields so that you must be logged in to view sales reports */
require "../smarty.php";
require "../config.php";
require "./auth.php";
require "../include/security.php";

/* Assign variables if register_globals = OFF */
$StartMonth		= $HTTP_POST_VARS['StartMonth'];
$StartDay		= $HTTP_POST_VARS['StartDay'];
$StartYear		= $HTTP_POST_VARS['StartYear'];
$EndMonth		= $HTTP_POST_VARS['EndMonth'];
$EndDay			= $HTTP_POST_VARS['EndDay'];
$EndYear		= $HTTP_POST_VARS['EndYear'];
$report_type	= $HTTP_POST_VARS['report_type'];

/* Set default and current order status value to be displayed */
$def_status		= "C";
$status			= ($HTTP_POST_VARS['status'] ? $HTTP_POST_VARS['status'] : $def_status);

/* Change database connection settings per your server */
$db_host	= "database_server";
$db_user	= "database_user";
$db_pwd		= "database_password";
$db_db		= "database_name";

/* For debug purposes; shows/hides product ID value in table */
/* Set to true to output product ID */
$show_id	= false;

/* Set variables to the first day your store opened */
$opened_month	= "12";
$opened_day		= "01";
$opened_year	= "2003";

/* Make connection to database */
$connection = mysql_connect($db_host, $db_user, $db_pwd) or die("Could not connect: " . mysql_error());
mysql_select_db($db_db) or die("Could not select the specified mysql database."); 

/* Create start/end dates for SQL queries */
$start_date	= mktime(0,0,0,$StartMonth,$StartDay,$StartYear);
$end_date	= mktime(23,59,59,$EndMonth,$EndDay,$EndYear);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> The Rodale Institute Store: Custom Sales Reports </title>
<META name="ROBOTS" content="NOINDEX,NOFOLLOW"> 
<style type="text/css">
	h3 {font-family: arial}
	td {font-family: arial; font-size: 10 pt;}
	tr {font-family: arial; font-size: 11 pt;}
}
</style>
</head>
<body>

<form action="<?=basename($PHP_SELF)?>" method="POST">
<table>
	<tr>
		<th colspan="2"><u>Custom Sales Report Date Range:</u></th>
	</tr>
	<tr> 
		<td height="10" nowrap width="125">Order Status:</td>
		<td height="10"> 
			<select name="status">
				<option value="A" <?=($status=="A" ? "selected" : "")?>>All</option>
				<option value="I" <?=($status=="I" ? "selected" : "")?>>Not finished</option>
				<option value="Q" <?=($status=="Q" ? "selected" : "")?>>Queued</option>
				<option value="P" <?=($status=="P" ? "selected" : "")?>>Processed</option>
				<option value="B" <?=($status=="B" ? "selected" : "")?>>Backordered</option>
				<option value="D" <?=($status=="D" ? "selected" : "")?>>Declined</option>
				<option value="F" <?=($status=="F" ? "selected" : "")?>>Failed</option>
				<option value="C" <?=($status=="C" ? "selected" : "")?>>Complete</option>
			</select>
		</td>
	</tr>
	<tr> 
		<td height="10" nowrap width="125">Starting Date:</td>
		<td height="10"> 
			<? generateDateSelector("Start"); ?>
		</td>
	</tr>
	<tr> 
		<td height="10" nowrap>Ending Date:</td>
		<td height="10"> 
			<? generateDateSelector("End"); ?>
		</td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=radio name="report_type" value="total" id="1" 
			<? 
				if ($report_type!="qty_purchased") { echo "checked"; }
			?>
		><label for="1">Sales by Item Total</label> • <input type="radio" name="report_type" value="qty_purchased" id="2" 
			<?
				if ($report_type == "qty_purchased") { echo "checked"; }
			?>
		><label for="2">Sales by Quantity</label></td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=submit value="Generate Custom Report">
 • or • 
</td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=button value="Generate Cumulative Report" onClick="document.forms[0].StartMonth.value='<?=$opened_month?>';document.forms[0].StartYear.value='<?=$opened_year?>';document.forms[0].StartDay.value='<?=$opened_day?>';document.forms[0].EndDay.value='01';document.forms[0].submit();"></td>
	</tr>
<table>

</form>

<? 
/* Begin if ** only show report if they posted a new date */
if ($HTTP_SERVER_VARS['REQUEST_METHOD'] == "POST") {

	if ($report_type == "total")
		$report_title = "Online Bookstore: Sales by Item Total";
	else
		$report_title = "Online Bookstore: Sales by Quantity";

?>


<h3><?=$report_title?></h3>
<?
	echo "Report Range: $StartMonth.$StartDay.$StartYear through $EndMonth.$EndDay.$EndYear
";
?>


<table cellpadding="5" cellspacing="2">
	<tr bgcolor="#D3DCE3">
		<th align="left">Product</th>
		<? if ($show_id) echo "<th>Product ID</th>"; ?>
		<th align="left">Item Total</th>
		<th align="right">Quantity</th>
	</tr>
<? 
	/* Main Query */
	$sql = "SELECT tblproducts.product, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date " . ($status != "A" ? "AND tblorders.status = '$status'" : "") . " GROUP BY tbldetails.productid ORDER BY $report_type desc";
	$result = mysql_query($sql) or die("Error in query.". mysql_error()); 

	$cnt		= 1;
	$sum_total	= 0.00;
	$sum_qty	= 0.00;
	while($row	= mysql_fetch_assoc($result))
	{
		$product_name	= $row['product'];
		$product_id		= $row['productid'];
		$total			= $row['total'];
		$qty_purchased	= $row['qty_purchased'];

		$sum_total		+=$total;
		$sum_qty		+=$qty_purchased;

		if ($cnt%2 == 1) $color="#DDDDDD"; else $color="#CCCCCC";
		echo "<tr bgcolor='$color'><td>$product_name</td>";
			if ($show_id) echo "<td>$product_id</td>";
		echo "<td>$ $total</td><td align=\"right\">$qty_purchased</td></tr>\n";
		$cnt++;
	}

if ($show_id)
	$colspan = "2";
else
	$colspan = "1";
?>
	<tr bgcolor="#D3DCE3">
		<td colspan="<?=$colspan?>" align="right">Totals for this date range:</td>
		<th><? echo "$ ".number_format($sum_total, 2, '.', ','); ?></th>
		<th align="right"><?=$sum_qty?></th>
	</tr>
</table>
<?
} /* end if */
?>


</body>
</html>
<?
/* Generate three list boxes for d-m-y selection */
function generateDateSelector($prefix="")
{
	global $HTTP_SERVER_VARS;
	global $StartYear, $StartMonth, $StartDay, $EndYear, $EndMonth, $EndDay;

	// month array
	$monthArray = array("", "January", "February", "March", "April", "May",
	"June", "July", "August", "September", "October", "November", "December");

	// get current year, month and date
	// set curr* variables to use preset start/end values if so needed
	if ($HTTP_SERVER_VARS['REQUEST_METHOD'] == "POST") {
		if ($prefix == "Start") {
			$currYear = $StartYear;
			$currMonth = $StartMonth;
			$currDay = $StartDay;
		}
		elseif ($prefix == "End") {
			$currYear = $EndYear;
			$currMonth = $EndMonth;
			$currDay = $EndDay;
		}
	}
	else {
		$arr = getdate(mktime());
		$currYear = $arr["year"];
		$currMonth = $arr["mon"];
		
		// If the user hasn't chosen a date, set the beginning day at the first of the month
		if ($prefix == "Start")
			$currDay = 01;
		else
			$currDay = $arr["mday"];
	}

	// generate month drop-down
	echo "<select name=" . $prefix . "Month>\n";
		for ($x=1; $x<=12; $x++)
		{
		$str = "<option value=" . sprintf("%02d", $x) . "";
			if ($x == $currMonth)
			{
			$str .= " selected";
			}
		$str .= ">" . $monthArray[$x] . "</option>\n";
		echo $str;
		}
	echo "</select> \n";

	// generate date drop-down
	echo "<select name=" . $prefix . "Day>\n";
		for ($x=1; $x<=31; $x++)
		{
		$str = "<option value=" . sprintf("%02d", $x) . "";
			if ($x == $currDay)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%02d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";

	// generate year drop-down
	echo "<select name=" . $prefix . "Year>\n";
		for ($x=2003; $x<=2006; $x++)
		{
		$str = "<option value=$x";
			if ($x == $currYear)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%04d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";
}

?>

[jeremye]

The new code below adds the ability to show the Product Code (SKU #) as an option. It is disabled by default; to enable it find where $show_sku = false; and change to true.

<?
/*****************************************************************************\
	Custom Sales Reports for X-Cart (versions 3.4.8+)
	Created 02/23/04 by Jeremy Eastburn
	Updated 08/23/05 by Jeremy Eastburn (added order status dropdown)
	Updated 08/23/05 by Jeremy Eastburn (added 'show sku' option)
\*****************************************************************************/

/* Added require fields so that you must be logged in to view sales reports */
require "../smarty.php";
require "../config.php";
require "./auth.php";
require "../include/security.php";

/* Assign variables if register_globals = OFF */
$StartMonth	 = $HTTP_POST_VARS['StartMonth'];
$StartDay    = $HTTP_POST_VARS['StartDay'];
$StartYear   = $HTTP_POST_VARS['StartYear'];
$EndMonth    = $HTTP_POST_VARS['EndMonth'];
$EndDay      = $HTTP_POST_VARS['EndDay'];
$EndYear     = $HTTP_POST_VARS['EndYear'];
$report_type = $HTTP_POST_VARS['report_type'];

/* Set default and current order status value to be displayed */
$def_status  = "C";
$status      = ($HTTP_POST_VARS['status'] ? $HTTP_POST_VARS['status'] : $def_status);

/* Change database connection settings per your server */
$db_host     = "localhost";
$db_user     = "";
$db_pwd      = "";
$db_db       = "";

/* For debug purposes; shows/hides product ID and SKU value in table */
/* Set to true to output product ID or SKU */
$show_id     = false;
$show_sku    = false;

/* Set variables to the first day your store opened */
$opened_month = "12";
$opened_day   = "01";
$opened_year  = "2003";

/* Make connection to database */
$connection = mysql_connect($db_host, $db_user, $db_pwd) or die("Could not connect: " . mysql_error());
mysql_select_db($db_db) or die("Could not select the specified mysql database."); 

/* Create start/end dates for SQL queries */
$start_date  = mktime(0,0,0,$StartMonth,$StartDay,$StartYear);
$end_date    = mktime(23,59,59,$EndMonth,$EndDay,$EndYear);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> The Rodale Institute Store: Custom Sales Reports </title>
<META name="ROBOTS" content="NOINDEX,NOFOLLOW"> 
<style type="text/css">
	h3 {font-family: arial}
	td {font-family: arial; font-size: 10 pt;}
	tr {font-family: arial; font-size: 11 pt;}
}
</style>
</head>
<body>

<form action="<?=basename($PHP_SELF)?>" method="POST">
<table>
	<tr>
		<th colspan="2"><u>Custom Sales Report Date Range:</u></th>
	</tr>
	<tr> 
		<td height="10" nowrap width="125">Order Status:</td>
		<td height="10"> 
			<select name="status">
				<option value="A" <?=($status=="A" ? "selected" : "")?>>All</option>
				<option value="I" <?=($status=="I" ? "selected" : "")?>>Not finished</option>
				<option value="Q" <?=($status=="Q" ? "selected" : "")?>>Queued</option>
				<option value="P" <?=($status=="P" ? "selected" : "")?>>Processed</option>
				<option value="B" <?=($status=="B" ? "selected" : "")?>>Backordered</option>
				<option value="D" <?=($status=="D" ? "selected" : "")?>>Declined</option>
				<option value="F" <?=($status=="F" ? "selected" : "")?>>Failed</option>
				<option value="C" <?=($status=="C" ? "selected" : "")?>>Complete</option>
			</select>
		</td>
	</tr>
	<tr> 
		<td height="10" nowrap width="125">Starting Date:</td>
		<td height="10"> 
			<? generateDateSelector("Start"); ?>
		</td>
	</tr>
	<tr> 
		<td height="10" nowrap>Ending Date:</td>
		<td height="10"> 
			<? generateDateSelector("End"); ?>
		</td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=radio name="report_type" value="total" id="1" 
			<? 
				if ($report_type!="qty_purchased") { echo "checked"; }
			?>
		><label for="1">Sales by Item Total</label> • <input type="radio" name="report_type" value="qty_purchased" id="2" 
			<?
				if ($report_type == "qty_purchased") { echo "checked"; }
			?>
		><label for="2">Sales by Quantity</label></td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=submit value="Generate Custom Report">
 • or • 
</td>
	</tr>
	<tr> 
		<td height="10" colspan="2" align="center"><input type=button value="Generate Cumulative Report" onClick="document.forms[0].StartMonth.value='<?=$opened_month?>';document.forms[0].StartYear.value='<?=$opened_year?>';document.forms[0].StartDay.value='<?=$opened_day?>';document.forms[0].EndDay.value='01';document.forms[0].submit();"></td>
	</tr>
<table>

</form>

<? 
/* Begin if ** only show report if they posted a new date */
if ($HTTP_SERVER_VARS['REQUEST_METHOD'] == "POST") {

	if ($report_type == "total")
		$report_title = "Online Bookstore: Sales by Item Total";
	else
		$report_title = "Online Bookstore: Sales by Quantity";

?>


<h3><?=$report_title?></h3>
<?
	echo "Report Range: $StartMonth.$StartDay.$StartYear through $EndMonth.$EndDay.$EndYear
";
?>


<table cellpadding="5" cellspacing="2">
	<tr bgcolor="#D3DCE3">
		<th align="left">Product</th>
		<? if ($show_id) echo "<th>Product ID</th>"; ?>
		<? if ($show_sku) echo "<th>SKU #</th>"; ?>
		<th align="left">Item Total</th>
		<th align="right">Quantity</th>
	</tr>
<? 
	/* Main Query */
	$sql = "SELECT tblproducts.product, tblproducts.productcode, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date " . ($status != "A" ? "AND tblorders.status = '$status'" : "") . " GROUP BY tbldetails.productid ORDER BY $report_type desc";
	$result = mysql_query($sql) or die("Error in query.". mysql_error()); 

	$cnt		= 1;
	$sum_total	= 0.00;
	$sum_qty	= 0.00;
	while($row	= mysql_fetch_assoc($result))
	{
		$product_name	= $row['product'];
		$product_id		= $row['productid'];
		$product_sku	= $row['productcode'];
		$total			= $row['total'];
		$qty_purchased	= $row['qty_purchased'];


		$sum_total		+=$total;
		$sum_qty		+=$qty_purchased;

		if ($cnt%2 == 1) $color="#DDDDDD"; else $color="#CCCCCC";
		echo "<tr bgcolor='$color'><td>$product_name</td>";
			if ($show_id) echo "<td>$product_id</td>";
			if ($show_sku) echo "<td>$product_sku</td>";
		echo "<td>$ $total</td><td align=\"right\">$qty_purchased</td></tr>\n";
		$cnt++;
	}

if ($show_id && $show_sku)
	$colspan = "3";
elseif ($show_id || $show_sku)
	$colspan = "2";
else
	$colspan = "1";
?>
	<tr bgcolor="#D3DCE3">
		<td colspan="<?=$colspan?>" align="right">Totals for this date range:</td>
		<th><? echo "$ ".number_format($sum_total, 2, '.', ','); ?></th>
		<th align="right"><?=$sum_qty?></th>
	</tr>
</table>
<?
} /* end if */
?>


</body>
</html>
<?
/* Generate three list boxes for d-m-y selection */
function generateDateSelector($prefix="")
{
	global $HTTP_SERVER_VARS;
	global $StartYear, $StartMonth, $StartDay, $EndYear, $EndMonth, $EndDay;

	// month array
	$monthArray = array("", "January", "February", "March", "April", "May",
	"June", "July", "August", "September", "October", "November", "December");

	// get current year, month and date
	// set curr* variables to use preset start/end values if so needed
	if ($HTTP_SERVER_VARS['REQUEST_METHOD'] == "POST") {
		if ($prefix == "Start") {
			$currYear = $StartYear;
			$currMonth = $StartMonth;
			$currDay = $StartDay;
		}
		elseif ($prefix == "End") {
			$currYear = $EndYear;
			$currMonth = $EndMonth;
			$currDay = $EndDay;
		}
	}
	else {
		$arr = getdate(mktime());
		$currYear = $arr["year"];
		$currMonth = $arr["mon"];
		
		// If the user hasn't chosen a date, set the beginning day at the first of the month
		if ($prefix == "Start")
			$currDay = 01;
		else
			$currDay = $arr["mday"];
	}

	// generate month drop-down
	echo "<select name=" . $prefix . "Month>\n";
		for ($x=1; $x<=12; $x++)
		{
		$str = "<option value=" . sprintf("%02d", $x) . "";
			if ($x == $currMonth)
			{
			$str .= " selected";
			}
		$str .= ">" . $monthArray[$x] . "</option>\n";
		echo $str;
		}
	echo "</select> \n";

	// generate date drop-down
	echo "<select name=" . $prefix . "Day>\n";
		for ($x=1; $x<=31; $x++)
		{
		$str = "<option value=" . sprintf("%02d", $x) . "";
			if ($x == $currDay)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%02d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";

	// generate year drop-down
	echo "<select name=" . $prefix . "Year>\n";
		for ($x=2003; $x<=2006; $x++)
		{
		$str = "<option value=$x";
			if ($x == $currYear)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%04d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";
}

?>

[whsu716]

This looks like a great mod, just a quick question

1) which folder would I put this into?

I have currently put it in the admin folder, but it always redirects me to the admin home page ?

Thanks

[jeremye]

You need to put it in the [xcart]/admin/ directory. Make sure you put the following code at the top:

require "../smarty.php"; 
require "../config.php"; 
require "./auth.php"; 
require "../include/security.php"; 

The reason it redirects you to the admin login page is because you don't have admin authentication, most likely. Let me know if that works.

[whsu716]

wow thanks for the fast reply...

unfortunately - i'm still getting that error even when I put it in the admin folder...

It just redirects me to the home page of the admin site (not the login page, but the page after I log in)

any ideas?