<?
/*****************************************************************************\
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";
}
?>