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.
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";
}
?>