Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

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

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #1  
Old 04-22-2004, 12:47 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default 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.
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #2  
Old 04-22-2004, 01:28 PM
  lyncca's Avatar 
lyncca lyncca is offline
 

X-Adept
  
Join Date: Nov 2003
Location: Fort Worth, Texas
Posts: 455
 

Default

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
Reply With Quote
  #3  
Old 04-22-2004, 02:17 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #4  
Old 04-22-2004, 02:28 PM
  lyncca's Avatar 
lyncca lyncca is offline
 

X-Adept
  
Join Date: Nov 2003
Location: Fort Worth, Texas
Posts: 455
 

Default

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.
Reply With Quote
  #5  
Old 04-22-2004, 03:04 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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"; } ?>
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #6  
Old 04-22-2004, 08:17 PM
 
Emerson Emerson is offline
 

X-Man
  
Join Date: Mar 2004
Location: Atlanta, GA
Posts: 2,209
 

Default

Very very cool script

Thank You
__________________
Emerson
Total Server Solutions LLC- Quality X-Cart Hosting
Recommended X-Cart Hosting Provider - US and UK servers
Does your host backup your site? We do EVERY HOUR!!!
Shared Hosting | Managed Cloud | Dedicated Servers
Reply With Quote
  #7  
Old 04-23-2004, 05:42 AM
  abeight's Avatar 
abeight abeight is offline
 

X-Adept
  
Join Date: Nov 2003
Location: Cleveland, OH
Posts: 479
 

Default

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?
__________________
~ Andrea Beight
Reply With Quote
  #8  
Old 04-23-2004, 05:45 AM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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.
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #9  
Old 04-23-2004, 05:59 AM
  abeight's Avatar 
abeight abeight is offline
 

X-Adept
  
Join Date: Nov 2003
Location: Cleveland, OH
Posts: 479
 

Default

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)
__________________
~ Andrea Beight
Reply With Quote
  #10  
Old 04-23-2004, 07:28 AM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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.
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
Reply
   X-Cart forums > X-Cart 4 > Dev Questions


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 11:49 AM.

   

 
X-Cart forums © 2001-2020