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

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - Custom Sales Reports by Product (item total and by qty) (https://forum.x-cart.com/showthread.php?t=7270)

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

I have created a custom reports page that shows sales by item total, and sales by quantity for any date range you want. There is also a "Cumulative Report" button that automatically generates a report from the start of your orders in the database. Note, I have only tested this on versions 3.4.8 through 3.4.14, but it should work as long as no major field names have changed in the xcart_orders and orders_details tables.

If you are interested, I can cleanup the code and paste it here, but I won't spend the time on that until I know someone is interested.

This is cool, we have actually been wanting to see that ourselves to be able to see which of our templates are selling. Is it a hard implementation? I'm not so great on the PHP end yet without making my whole shop collapse most times :roll:

It's actually a really EASY implementation due to one simple fact: it doesn't sit within the xcart admin area. you don't login or anything, it's just a separate page that you can put whereever you want on your webserver. All you have to do is change the username and password for your database and bam, you're up and running!

If someone is interested in creating a template for it so that it sits within xcart, let me know and I'd be more than willing to work with you.

Jeremy

Well I would love the code if you don't mind. It would really come in handy until we can have the time to implement (lord only knows when that will be) the Firetank software which I believe can give the same data.

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

}



?>

Very very cool script 8)

Thank You ;)

This could really come in handy... great script! I have a question though... how do you set it up? Just copy it into a file and name it something.php and upload it to your server?

Yep, it's quite simple. I called it "custom_sales_reports.php" and placed it in /xcart/admin/, just for simplicity sake. Change the variables that you see in the top of the code and you are set!

Let me know how it works, since I have never used it outside of my own database.

Thanks Jeremy. I gave it a try, but it didn't work for me. See here: http://www.biz-plates.com/store/report.php (version 3.5.0)

Quote:

Originally Posted by abeight

Thanks Jeremy. I gave it a try, but it didn't work for me. See here: http://www.biz-plates.com/store/report.php (version 3.5.0)

Hmm, odd. Take out lines 2 through 5 (the comments section). Looks like it doesn't like the \ character at the end of line 2.

The only SQL fields that it needs are the following:

Code:

xcart_products.product,

xcart_order_details.productid

xcart_order_details.price

xcart_order_details.amount

xcart_order_details.amount

Try that and let me know.

The link above won't work anymore since I deleted the file and moved it to a password protected area. ;) I took lines 2-5 out, but still get the same error:

Code:

Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /home/bizplate/public_html/admin/report.php on line 2



Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /home/bizplate/public_html/admin/report.php on line 2



Parse error: parse error in /home/bizplate/public_html/admin/report.php on line 2

Try replacing the first line with this (add just php)

Code:

<?php

I think it's a PHP setting with your server. We'll work it out...

Quote:

Originally Posted by abeight

I took lines 2-5 out, but still get the same error

Anyone else want to chime in if you've seen this error before? I can only find these two links on PHP bugs, but can't really think up a solution:

http://bugs.php.net/bug.php?id=5387
http://bugs.php.net/bug.php?id=5448

LOL... well that was a step in the right direction. Now I only get the bottom two lines of the error. Thanks for trying to figure it out. Don't kill youself over it though. ;)

Try doing a search and replace:

Find "<?"
Replace with "<?php"

See if that does anything.

I did some more reading and it may be that 'magic_quotes_gpc' it turned OFF in your PHP settings. Let me know if you need help finding out how to check this.

This picks up failed attempts as well, (which in my case really skews the order qty) is there anyway to have it only accept processed orders?

Thanks,
Eduardo.

This can easily be accomplished by adding a few qualifying statements to the SQL query on Line 110 (or thereabouts): Delete the line where it starts with "$sql = "SELECT tblproducts..." and replace it with the following:

Code:

$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 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY $report_type desc";

Notice that I added two statements:

Code:

AND tblorders.status = 'C' OR tblorders.status = 'P'

This makes sure it only counts orders that are Complete or Processed.

I have tested it on my side and it works fine. Let me know if this helps or not.

I forgot to mention another nice feature of this report. If you noticed, the X-Cart pre-packaged orders reports shows the total gross/paid amounts for the order date range you selected. Unfortunately, it adds shipping costs as well, so you are truly not generating the amount of revenue that x-cart claims you are. This report ONLY takes into account the product prices, not shipping, so you may notice a difference between this report and the reports that x-cart generates (this statement is valid up to version 3.4.14).

Just an FYI.

Quote:

Originally Posted by jeremye

Code:

$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 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY $report_type desc";

Notice that I added two statements:

Code:

AND tblorders.status = 'C' OR tblorders.status = 'P'

This makes sure it only counts orders that are Complete or Processed.

I have tested it on my side and it works fine. Let me know if this helps or not.

I am using 3.3.3 and the second sql didnot work. any help idea?

Quote:

Originally Posted by deniz

I am using 3.3.3 and the second sql didnot work. any help idea?

Exactly what SQL query are you using, and what error is it giving you?

Code:

$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 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY $report_type desc";

I am using this sql query , the page not finishing when I run this.

But in the first example the page works fine , I only need to eliminate status C and P position in sql query.

You are ok, it's no biggie. I think the reason is because the database structure is different in your version. If you want, look in your orders table and find the column that lists C, P, Q, etc. It may be named different, but I don't have your version so I can't check.

Let me know if you need more assistance.

its writen same 'status'

Quote:

Originally Posted by deniz

its writen same 'status'

You can just ignore the status == C and status == P part of the query. This was not in the original code, someone else suggested this, so you will be fine without it in there.

Jeremy

But I need to use P and C filter

Can anyone using 3.3.3 check the database structure for the status of orders? Is the table/field name xcart_orders.status? Have the abbreviations changed (are they different from C,P,Q,D,F,etc)?

As I do not have 3.3.3 I am stuck here. Any help?

Jeremy

Quote:

Notice that I added two statements:
Code:
AND tblorders.status = 'C' OR tblorders.status = 'P'

Hi,
People seem to be having trouble implementing this, I just wanted to let you know that it works perfect for me so you wouldn't feel that the script only worked on your server. Couple of comments though, the above code I mentioned above does not work, it will cause the script to only list one product and credit it with insane sales of 99 in qty and 300,000 in total. The right code is:

Code:

AND tblorders.status = 'C' OR 'P'

I don't think it would be that difficult to implement this into xcart's admin area, you just have to take out the non php related material and save that as the tpl file, create a link in the menu, and link it in the home.tpl area. I'm pretty sure someone will crack it.

Thanks for the mod, its excellent!

Thanks for the SQL update. I appreciate it. Glad it's working on your site...

When i try to run it i get this error message:

Report Range: .. through ..
Product Item Total Quantity
Error in query.You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1

The year are falling back to 0000 and there's no other year to choose.

Do you have register_globals turned on in your version of PHP? It looks like the variables that are being POSTed are not working, which makes it seem as if register_globals is not allowing the PHP vars to be automatically assigned.

Can you confirm if register_globals is on (check your phpinfo() file)?

Going on a hunch that your problem is with register_globals, try adding this code to the top of your php file:

Code:

/* Assign variables if register_globals = OFF */

$StartMonth = $_POST['StartMonth'];

$StartDay = $_POST['StartDay'];

$StartYear        = $_POST['StartYear'];

$EndMonth        = $_POST['EndMonth'];

$EndDay = $_POST['EndDay'];

$EndYear = $_POST['EndYear'];

$report_type = $_POST['report_type'];

This will assign all the variables from the _POST array. Let me know if this works.

For those of you using 3.4.14 and below, I added a few snippets of code to keep the page secure, meaning you have to be logged in to the admin area to view the report. My concern was that AS IS, the file has no security measures.

To secure your report, save the file to your [xcart]/admin/ directory.
Next go into your PHP file and add the following lines to the very top:

Code:

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

I know this works for my version of xcart, others may need to just look at another file in your /admin/ directory and see what include files they are requiring at the top of each page.

In order to access this, you must be logged into your admin area. Then you can copy/paste the URL, or create a special link in your admin area.

Hope this helps some of you.

I know, I know, I wrote this MOD but I am having an issue with the speediness of my SQL query used for gathering product/order data across multiple tables. Here is my SQL query for sales reports between Aug 1 and Aug 3 (real small timespan):

Code:

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 >= 1091332800 AND tblorders.date <= 1091591999 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY total desc

The problem is that if I am running a sales report for a whole month or a few months, it runs SUPER slow. Any ideas on how I can optimize this query to make it run a lot faster? Thanks!

Hi,

Just thought to let you all know, I created reporting, that sits in the Admin Area, and produces reports based on what you want.

Totals taxes, shipping, you can hide or display any field, use it as a shipping list for completed orders....

It was developed for 3.5.x, but I think it should work for 3.4.x.

Have not updated it or tested with version 4.x yet.

Take a look, I have some sample images at
http://www.x-cart-plus.com/au/xcart/customer/product.php?productid=6&cat=0&page=

:)

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

}



?>

This looks like a great mod, just a quick question

1) which folder would I put this into?

I have currently put it in the admin folder, but it always redirects me to the admin home page ?

Thanks

You need to put it in the [xcart]/admin/ directory. Make sure you put the following code at the top:

Code:

require "../smarty.php"; 

require "../config.php"; 

require "./auth.php"; 

require "../include/security.php";

The reason it redirects you to the admin login page is because you don't have admin authentication, most likely. Let me know if that works.

wow thanks for the fast reply...

unfortunately - i'm still getting that error even when I put it in the admin folder...

It just redirects me to the home page of the admin site (not the login page, but the page after I log in)

any ideas?