Custom Sales Reports by Product (item total and by qty)

jeremye · #1 04-22-2004, 12:47 PM

I have created a custom reports page that shows sales by item total, and sales by quantity for any date range you want. There is also a "Cumulative Report" button that automatically generates a report from the start of your orders in the database. Note, I have only tested this on versions 3.4.8 through 3.4.14, but it should work as long as no major field names have changed in the xcart_orders and orders_details tables.

If you are interested, I can cleanup the code and paste it here, but I won't spend the time on that until I know someone is interested.

lyncca · #2 04-22-2004, 01:28 PM

This is cool, we have actually been wanting to see that ourselves to be able to see which of our templates are selling. Is it a hard implementation? I'm not so great on the PHP end yet without making my whole shop collapse most times

jeremye · #3 04-22-2004, 02:17 PM

It's actually a really EASY implementation due to one simple fact: it doesn't sit within the xcart admin area. you don't login or anything, it's just a separate page that you can put whereever you want on your webserver. All you have to do is change the username and password for your database and bam, you're up and running!

If someone is interested in creating a template for it so that it sits within xcart, let me know and I'd be more than willing to work with you.

Jeremy

lyncca · #4 04-22-2004, 02:28 PM

Well I would love the code if you don't mind. It would really come in handy until we can have the time to implement (lord only knows when that will be) the Firetank software which I believe can give the same data.

jeremye · #5 04-22-2004, 03:04 PM

Ok, I cleaned it up a bit. Let me know if I can help on improving it at all. I'd love to see it become a module for x-cart, where it sits within the admin area and you have to login to view it, etc.

Enjoy it, let me know if anyone wants me to email it to them as well.

I'd love to hear anyone's comments, thanks.

Code:

<?
/*****************************************************************************\
	Custom Sales Reports for X-Cart (versions 3.4.8+)
	Created 02/23/04 by Jeremy Eastburn
\*****************************************************************************/

/* Change database connection settings per your server */
$db_host	= "localhost";
$db_user	= "database_username";
$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> X-Cart: 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="<?=$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">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].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 = "X-Cart: Sales by Item Total";
	else
		$report_title = "X-Cart: 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>
<? 
	/* Sales by $$ */
	$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 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 */
/* generateDateSelector functions modified from http://www.devshed.com/c/a/PHP/Time-is-Money-part-1/6/ */
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=($currYear-1); $x<=($currYear+1); $x++)
		{
		$str = "<option value=$x";
			if ($x == $currYear)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%04d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";
}

?>

Emerson · #6 04-22-2004, 08:17 PM

Very very cool script

Thank You

abeight · #7 04-23-2004, 05:42 AM

This could really come in handy... great script! I have a question though... how do you set it up? Just copy it into a file and name it something.php and upload it to your server?

jeremye · #8 04-23-2004, 05:45 AM

Yep, it's quite simple. I called it "custom_sales_reports.php" and placed it in /xcart/admin/, just for simplicity sake. Change the variables that you see in the top of the code and you are set!

Let me know how it works, since I have never used it outside of my own database.

abeight · #9 04-23-2004, 05:59 AM

Thanks Jeremy. I gave it a try, but it didn't work for me. See here: http://www.biz-plates.com/store/report.php (version 3.5.0)

jeremye · #10 04-23-2004, 07:28 AM

Quote:

Originally Posted by abeight

Thanks Jeremy. I gave it a try, but it didn't work for me. See here: http://www.biz-plates.com/store/report.php (version 3.5.0)

Hmm, odd. Take out lines 2 through 5 (the comments section). Looks like it doesn't like the \ character at the end of line 2.

The only SQL fields that it needs are the following:

Code:

xcart_products.product,
xcart_order_details.productid
xcart_order_details.price
xcart_order_details.amount
xcart_order_details.amount

Try that and let me know.