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