X-Cart forums - Custom Sales Reports by Product (item total and by qty)

I've had some requests for updates to this script (which hasn't been updated in quite some time). I made modifications to allow for the admin to choose what order status values the report should include. Note the new "Order Status" dropdown at the top. Here's the code, let me know if you have any questions:

Code:

<?

/*****************************************************************************\

        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)

\*****************************************************************************/



/* 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        = "database_server";

$db_user        = "database_user";

$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> 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>"; ?>

                <th align="left">Item Total</th>

                <th align="right">Quantity</th>

        </tr>

<? 

        /* Main Query */

        $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 " . ($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'];

                $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 */

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";

}



?>

The new code below adds the ability to show the Product Code (SKU #) as an option. It is disabled by default; to enable it find where $show_sku = false; and change to true.

Code:

<?

/*****************************************************************************\

        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";

}



?>