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

bigmaggot · #61 06-26-2008, 04:04 AM

You can search by the SKU:

http://forum.x-cart.com/showthread.php?p=93860#post93860

Keys Care · #62 06-26-2008, 04:32 AM

Hi, Actually this code on lists the sku. I have this implemented and it helps for sure. What I want to be able to do is to sort by sku so that my reports are always in the same order versus ranking by revenue or units.

Regards,

Bob

bigmaggot · #63 06-26-2008, 05:52 AM

Code:

<?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 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     = true;
$show_sku    = true;

/* Set variables to the first day your store opened */
$opened_month = "03";
$opened_day   = "04";
$opened_year  = "2006";

/* 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>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>
                <option value="S" <?=($status=="S" ? "selected" : "")?>>Shipping</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> ∙ <input type="radio" name="report_type" value="productcode" id="3" 
			<?
				if ($report_type == "productcode") { echo "checked"; }
			?>
		><label for="3">Sales by SKU</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 = "Sales by Item Total";
	elseif ($report_type == "qty_purchased")
		$report_title = "Sales by Quantity";
	elseif ($report_type == "productcode")
		$report_title = "Sales by SKU";

?>


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


<table cellpadding="5" cellspacing="2">
	<tr bgcolor="#D3DCE3">
		<? if ($show_sku) echo "<th>Man Part#</th>"; ?>
		<th align="right">Quantity</th>
		<? if ($show_id) echo "<th>Product ID</th>"; ?>
		<th align="left">Product</th>
		<th align="left">Item Total</th>

	</tr>
<? 
	/* Main Query */

	if ($report_type == "total")
	$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";

	elseif ($report_type == "qty_purchased")
	$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";

	elseif ($report_type == "productcode")
	$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 asc";


	$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'];
		$qty_purchased	= $row['qty_purchased'];
		$product_id		= $row['productid'];
		$product_sku	= $row['productcode'];
		$total			= $row['total'];



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

		if ($cnt%2 == 1) $color="#DDDDDD"; else $color="#CCCCCC";
		echo "<tr bgcolor='$color'>";

if ($show_sku) echo "<td>$product_sku</td>";
echo "<td align=\"right\">$qty_purchased</td>";
if ($show_id) echo "<td>$product_id</td>";
echo "<td>$product_name</td>";
echo "<td>ё $total</td>";
echo "</tr>\n";
		$cnt++;
	}

if ($show_id && $show_sku)
	$colspan = "2";
elseif ($show_id || $show_sku)
	$colspan = "3";
else
	$colspan = "1";
?>
	<tr bgcolor="#D3DCE3">

<td colspan="<?=$colspan?>"align="right"><?=$sum_qty?></td>
<th align="right"> </th>
<th align="right">Totals for this date range:</th>
<th><? echo "ё ".number_format($sum_total, 2, '.', ','); ?></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=2005; $x<=2009; $x++)
		{
		$str = "<option value=$x";
			if ($x == $currYear)
			{
			$str .= " selected";
			}
		$str .= ">" . sprintf("%04d", $x) . "</option>\n";
		echo $str;
		}
	echo "</select> \n";
}
?>

bigmaggot · #64 06-26-2008, 05:53 AM

Added above to search by SKU, don't forget to add database access. Apologies for removing Created by Jeremy Eastburn etc, had to remove as forum limited by 10,000 characters

cflsystems · #65 06-26-2008, 06:27 AM

Using 4.1.10 the code above works after deleting the "require" lines. One bug though - if your date range is bigger than the days in month it shows sales for next month. Example - date range from April 1 to April 31 (April has 30 days not 31) it will show wrong output as it takes April 31 as May 1 I assume. Not a big deal just wanted to point out be careful when selecting date range.

Keys Care · #66 06-27-2008, 06:59 AM

I have the reports running, but it does not match my x-cart main page or my orders page. I ran the orders for this month that are complete and the report is much higher than what x-cart says by a few thousand $. When I run the orders in process, it matches perfectly.

As a fyi, we do use discount coupons and have used the "other discount" AOM feature. It seems like it has something to do with reporting on a pre-discounted sub-total.

Keys Care · #67 06-27-2008, 12:36 PM

Quote:

Originally Posted by bigmaggot

You can search by the SKU:

http://forum.x-cart.com/showthread.php?p=93860#post93860

Thanks for this code. Sorry when I responded, it looked like the old code. Your addition works great except per my other post, that the report does not take into account any discounts. It appears to be a price * amount report. I need to report actual revenue minus shipping and discounts.

Unfortunately, these equations are not my forte.

This should be a caution to all that use the reports and use discounts and coupons. Your reports are probably incorrect.

Regards,

Bob

ScrapOrchard · #68 01-16-2009, 06:22 AM

Is there anyway to modify the code to show either how much the discounts/coupons for that product were or to show what was actually paid for the product?

jkwalz108 · #69 07-14-2009, 09:35 PM

Wow- what a great tool.

Thanks!

Jason

delphighse · #70 08-17-2009, 01:58 PM

Has anyone been able to get this to work for v 4.2.2?