Jump to content
jello1

Order Tracker New Contrib....

Recommended Posts

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 by jcall

Share this post


Link to post
Share on other sites

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


Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 $

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Another suggestion is in the By Countries section, it shows the states or provinces, how about showing hte country too?


Remember what the Bible says: He who is without sin, cast the first rock. And I shall smoketh it.

Share this post


Link to post
Share on other sites
Anyone having problems today with orders_tracking saying "Yesterday 09-001" and there not being any match?

 

The current version should resolve this problem.

 

-jared

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

That is correct. They are not absolutely necessary, but exist to help speed up the report.

 

-jared

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×