Guest Posted July 9, 2005 Share Posted July 9, 2005 (edited) Still more work to be done, but 2.6 has just been released at http://www.oscommerce.com/community/contributions,1522 . Changees in v2.6 since v2.5 - Added tracking by current order status (Admin >> Reports >> Status Tracking). -- This shows current database $$ totals (not just for this year) for orders in each order status. For example, if you currently have 20 orders in Pending status, for $20 each, status_tracking.php will show Pending :: 20 :: $400. - Added MySQL indexes for better performance for tracking by zones and status - Apostrophes are now stripped in Zone names. This means that orders_tracking_zones.php now works for countries like M'Sila, C?te d'Ivoire, etc. -jared Edited July 9, 2005 by jcall Quote Link to comment Share on other sites More sharing options...
AWWWW.WAHWAH Posted July 9, 2005 Share Posted July 9, 2005 Are the MySQL command correctly syntaxed in your instructions? Still more work to be done, but 2.6 has just been released at http://www.oscommerce.com/community/contributions,1522 . Changees in v2.6 since v2.5 - Added tracking by current order status (Admin >> Reports >> Status Tracking). -- This shows current database $$ totals (not just for this year) for orders in each order status. For example, if you currently have 20 orders in Pending status, for $20 each, status_tracking.php will show Pending :: 20 :: $400. - Added MySQL indexes for better performance for tracking by zones and status - Apostrophes are now stripped in Zone names. This means that orders_tracking_zones.php now works for countries like M'Sila, C?te d'Ivoire, etc. -jared <{POST_SNAPBACK}> Quote Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it. Link to comment Share on other sites More sharing options...
Guest Posted July 9, 2005 Share Posted July 9, 2005 Whoops. No, they're not. I don't remember the right syntax off the top of my head, but I'll go figure it out. -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted July 9, 2005 Share Posted July 9, 2005 correct syntax is: ALTER TABLE orders ADD INDEX (customers_state), ADD INDEX (orders_status); Sorry 'bout that. I'll post a 2.6a with the corrected readme. -jared Quote Link to comment Share on other sites More sharing options...
AWWWW.WAHWAH Posted July 12, 2005 Share Posted July 12, 2005 Request: In the Orders By Tracking Zones part, can we make it to where the city, state and country show instead of just the state? we have non-usa customers. Quote Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it. Link to comment Share on other sites More sharing options...
Guest Posted July 13, 2005 Share Posted July 13, 2005 I'll see what I can do. I already want to do it by month and year, not just year, just haven't gotten to it yet. Presently, it doesn't simply show the state, it shows entries from the zones table. In other words, if you get orders from both Albania and Alabama, they'll both show up in that list. -jared Quote Link to comment Share on other sites More sharing options...
AWWWW.WAHWAH Posted July 14, 2005 Share Posted July 14, 2005 Ok, this is still missing from the latest update. if ( $i == 0 ) { $first=$col_value; $last=$col_value; $i++; } else { "$last=$col_value;" must be added to the orders_tracking.php because if not ... if you have only one order for that month ... it won't show that sales figure. Quote Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it. Link to comment Share on other sites More sharing options...
Guest Posted July 14, 2005 Share Posted July 14, 2005 Thanks, Young! I'd forgotten about that. 2.6c now uploaded to http://www.oscommerce.com/community/contributions,1522 . v2.6c - added Spanish translation - added fix from forum (Thanks, Young!) where stats would not show up for months with only one order v2.6c includes fixes in 2.6b (Thanks, Kornie!) -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted August 2, 2005 Share Posted August 2, 2005 2.7 released, but needs to have this line commented: if ($yesterday <10) {$yesterday = "0$yesterday";} I'm waiting to make sure that it works in another 24 hours before posting 2.7a. -jared Quote Link to comment Share on other sites More sharing options...
nesincg Posted August 14, 2005 Share Posted August 14, 2005 I'm having a problem for July reports where the sales amout is climbing higher and higher, even though we are in August. Anyone else have a similar problem? Just downloaded 2.7 and that didn't solve the problem. Just started happening lately. So either my database is screwing up or this report SQL is off a little. All other sale reports show correctly. Any Ideas? Quote Link to comment Share on other sites More sharing options...
Guest Posted August 16, 2005 Share Posted August 16, 2005 Chris, the orders_tracking report looks specifically at the date_purchased field in the orders table. If this has been modified in some way, then that's what's confusing orders_tracking. HTH -jared Quote Link to comment Share on other sites More sharing options...
nesincg Posted August 19, 2005 Share Posted August 19, 2005 The report function get_order_total() is written somewhat incorrecly. It searches for the first order number of the month, then the last order number of the month and then gives you the total of all the orders in between. If you modify the date purchased of an order made in a later month, it then gets all the order totals up to that date. Here is the new function. This should make it a bit faster too. function get_order_total($mo, $yr) { $totals_query = tep_db_query(" SELECT sum( ot.value ) as totals FROM orders o, orders_total ot WHERE o.date_purchased LIKE \"".$yr."-".$mo."%\" AND ot.class = 'ot_total' AND o.orders_id = ot.orders_id"); $totals_row = tep_db_fetch_array($totals_query); return $totals_row['totals']; } Thanks for this report. It has been great, Chris Chris, the orders_tracking report looks specifically at the date_purchased field in the orders table. If this has been modified in some way, then that's what's confusing orders_tracking. HTH -jared <{POST_SNAPBACK}> Quote Link to comment Share on other sites More sharing options...
Guest Posted August 20, 2005 Share Posted August 20, 2005 Thanks, Chris! That works much simpler. -jared Quote Link to comment Share on other sites More sharing options...
♥radders Posted August 20, 2005 Share Posted August 20, 2005 I look forward to trying the new version. Somehow an old bug seems to have been reintroduced into this contribution. It seems to be ignoring the currency of the shop and treating everthing as $ Quote Link to comment Share on other sites More sharing options...
♥radders Posted August 20, 2005 Share Posted August 20, 2005 What I have done is to play around with the Orders by Zones report and set it up so that the zones are countries rather than regions as it seemed more useful that way for international stores. I haven't done anything clever with it lke currency conversions or putting the text in language files but it would proably be a good idea to include a report of this nature in a future version of the package. Quote Link to comment Share on other sites More sharing options...
♥radders Posted August 21, 2005 Share Posted August 21, 2005 I've updated the orders tracking file with Chris's excellent suggestion above and set the store currency to be the default currency rather than the first curency numerically. I also uploaded a new package incorporating the Country report. Works for me but YMMV. One caveat - all your orders must be in the same currency (the default or store currency) for this report to give meaningful results. All the text is now in the language files (not translated). David Quote Link to comment Share on other sites More sharing options...
Guest Posted August 24, 2005 Share Posted August 24, 2005 Thanks, David! -jared Quote Link to comment Share on other sites More sharing options...
AWWWW.WAHWAH Posted August 26, 2005 Share Posted August 26, 2005 Another suggestion is in the By Countries section, it shows the states or provinces, how about showing hte country too? Quote Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it. Link to comment Share on other sites More sharing options...
nesincg Posted September 3, 2005 Share Posted September 3, 2005 Anyone having problems today with orders_tracking saying "Yesterday 09-001" and there not being any match? Quote Link to comment Share on other sites More sharing options...
Guest Posted September 3, 2005 Share Posted September 3, 2005 Anyone having problems today with orders_tracking saying "Yesterday 09-001" and there not being any match? <{POST_SNAPBACK}> The current version should resolve this problem. -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted December 5, 2005 Share Posted December 5, 2005 1064 - You have an error in your SQL syntax near 'rib ' and date_purchased >= '2005-1-01 00:00:00' and date_purchased <= '2005-12-' at line 1 select count(*) as count from orders where customers_state = 'Ma'rib ' and date_purchased >= '2005-1-01 00:00:00' and date_purchased <= '2005-12-31 11:59:59' I got this message from Order Tracking by Zone. What I did wrong on my installation Quote Link to comment Share on other sites More sharing options...
Guest Posted December 5, 2005 Share Posted December 5, 2005 You didn't do anything wrong, it's just that the code you're running doesn't account for the apostrophe in the zone name. Which version are you running? The current version should not have that problem. -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted December 5, 2005 Share Posted December 5, 2005 Thanks! re-upload the World Zones SQL. It works! Quote Link to comment Share on other sites More sharing options...
barryjacq2 Posted December 25, 2005 Share Posted December 25, 2005 In orders tracking version 2_8b, there are two indexes created for customers_postcode. Is this correct? ALTER TABLE orders ADD INDEX (customers_state), ADD INDEX (orders_status); ALTER TABLE orders ADD INDEX (customers_postcode), ADD INDEX (customers_postcode); Quote Link to comment Share on other sites More sharing options...
Guest Posted January 4, 2006 Share Posted January 4, 2006 That is correct. They are not absolutely necessary, but exist to help speed up the report. -jared Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.