Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Order Tracker New Contrib....


jello1

Recommended Posts

  • Replies 260
  • Created
  • Last Reply

Top Posters In This Topic

Druide - - I incorporated most of your code into the 2.3 that I'm about to release. The sql query for the order status is wonderful - - I never took the time to figure that out.

 

I didn't include the link color change to blue because it doesn't really match the rest of osC - - links are pretty much the color of the text. In some places, they get underlined (email addresses), in other places, the links auto-highlight (orders and customers). On the left nav of the main admin pages, they don't do anything except change the browser's status bar (assuming that your browser is displaying it) with the link URL.

 

Leaving the links normal text color just seemed consistent with the rest of the admin UI. The auto-underline thing works for much of the rest of the UI, so why not here? :)

 

FWIW, there was something in the code that you added that messed up the who's online display. Not sure what that was, but it's right now.

 

In any case, other fixes include a fix for yesterday's stat's (again!) and code to have yesterday's stats work on the first day of the new year. I hope to have fixed where all orders from today appear to be from repeat customers.

 

Thank you again VERY MUCH for the stats sql queries!

 

-jared

Link to comment
Share on other sites

Looks like the repeat orders problem is more meaty than I thought at first. I'll see if I can get the time to do it later (unless you want to do it, Druide!)

 

Here it is, from the to-do list at the top of admin/orders_tracking.php:

 

7) repeat orders code is not right. Right now it just checks to see if the customer's ID is greater than the order number, which seems like it'd work as long as you don't delete any orders, and each customer only places one order.

The right way to do it seems like we'd have to:

a) find the customer_id for each of today's orders

B) query the orders table for every order that has that customer_id

c) count the results

d) if the number of results is greater than 2 (i.e. this order is not their first order) then this order is from a repeat customer.

 

You are right, there are a lot of things that can be rewritten to avoid hard links and make it smoother. At the time, I had to decide between releasing as-is, or spending the rewrite time. I just didn't have the time, so I just added in features/cleanup instead of rewriting other things that were working in a non-optimized fashion.

 

I'm sure it was a similar issue for Jello.

 

In any case, all input is welcome!

 

-jared

Link to comment
Share on other sites

  • 2 weeks later...

A few days ago, someone gave me a great idea: Wouldn't it be great to see where, geographically, your orders were coming from? I agreed.

 

So, I took the code that Druide's wrote to dynamically get the order status results, and modified it a bit. My hack uses the orders table and zones table to see where your orders come from.

 

I decided to post it in its early stages in the hopes that others can improve it / fix it faster than I can. I'm better at modifying other people's code than coming up with my own.

 

Here's what to do so you can see where we are so far:

 

Find

 
 <TR class="dataTableHeadingRow" bgcolor=silver><td class="dataTableContent" class="dataTableHeadingContent" COLSPAN=4><b><center><br>**Grand Totals</b></center></TD></TR>

 

and add the following right above it:

 

<?php
 $location_query = tep_db_query("select zone_name from " . TABLE_ZONES . " ");    
 while ($customers_location = tep_db_fetch_array($location_query)) {
   $location_pending_query = tep_db_query("select count(*) as count from " . TABLE_ORDERS . " where customers_state = '" . $customers_location['zone_name'] . "'");
   $location_pending = tep_db_fetch_array($location_pending_query);
   $location_contents .= '<tr class="dataTableRow"><td class="dataTableContent">' . $customers_location['zone_name'] . '</font></td><td class="dataTableContent">' . $location_pending['count'] . '</td><td class="dataTableContent" colspan="2" align="right"> </td></tr>';
 }
echo $location_contents;

?>

 

Current problems / limitations / things to do:

1) Since it uses the Zones table, it doesn't report on anything that's not in the Zones table. There are *many* places in the world that aren't in the Zones table.

2) For some reason, the order count seems to be a bit off sometimes. I'm not sure why. When I compare the number output by the code to the number I get searching for the same thing in phpMyAdmin, they are often off by as much as 10-15%. Sometimes they are correct. Not sure why this is happening. Not sure why it works sometimes and not others.

3) It would be nice to NOT display a zone that doesn't have any sales in it.

4) Default display sorts alphabetically by zone name. It'd be very useful to be able to sort by order quantity. Probably have to do this after the while loop, but before the display. Would need to modify the part of the loop where the HTML gets created.

5) Also be handy to be able to click on the zone name and execute a query that returns all the orders from that zone.

6) The queries and calculations can take a short while. We probably ought to put this into its own file. orders_tracking_byzone.php perhaps?

 

There are many contributions that you can use to add your own favorite zones to your database, for the UK, Switzerland, other parts of the world, so I think that the zone-based approach is a sound one, but am open to suggestions.

 

I'll keep working on this, but if anyone else can take care of some of these issues, it'll help us all along.

 

-jared

Link to comment
Share on other sites

I have made some improvements, and so will release an update to the contrib here in a few minutes.

 

2) For some reason, the order count seems to be a bit off sometimes. I'm not sure why. When I compare the number output by the code to the number I get searching for the same thing in phpMyAdmin, they are often off by as much as 10-15%. Sometimes they are correct. Not sure why this is happening. Not sure why it works sometimes and not others.

Fixed.

 

3) It would be nice to NOT display a zone that doesn't have any sales in it.

Done.

 

6) The queries and calculations can take a short while. We probably ought to put this into its own file. orders_tracking_byzone.php perhaps?

Done.

 

That leaves us with (as far as this piece is concerned):

1) Since it uses the Zones table, it doesn't report on anything that's not in the Zones table. There are *many* places in the world that aren't in the Zones table.

2) Default display sorts alphabetically by zone name. It'd be very useful to be able to sort by order quantity. Probably have to do this after the while loop, but before the display. Would need to modify the part of the loop where the HTML gets created.

3) Also be handy to be able to click on the zone name and execute a query that returns all the orders from that zone.

 

-jared

Link to comment
Share on other sites

Minor Bug Fix:

 

Update Install to:

 

define('BOX_REPORTS_ORDERS_TRACKING_Zones', 'Orders_Tracking_Zones');

 

define('BOX_REPORTS_ORDERS_TRACKING_ZONES', 'Orders_Tracking_Zones');

 

The names are case sensitive (I did it too during a beta build of the Feeder System) It's just so unatural to type like that. :P

Most likely your question has been answered, please do a search first.

Link to comment
Share on other sites

Calvin - - Thanks for the catch! I've fixed it here, and will have it fixed, along with the currency fix, for the next rev.

 

Would you like to be able to set the profit rate like you can presently set the year? Just a little box to the right of it sound ok?

 

-jared

Link to comment
Share on other sites

ask and ye shall receive! v2.5 released with configurable gross profit rate added. I imagine, though, that most people don't know their entire store's gross profit rate. I think that that would be fairly complicated to sort out, unless you have had a good accountant completely engaged for several months.

 

But, what the hey. Why not? Done.

 

http://www.oscommerce.com/community/contributions,1522

 

I even included a special reference to our local Froogle coder. <grin>

 

-jared

Edited by jcall
Link to comment
Share on other sites

The following files were updated:

1) /admin/orders_tracking.php

2) /admin/includes/language/english/orders_tracking.php

- - Feel free, anyone, to add in additional language translations and stuff. I'll add Spanish if I remember anytime soon.

3) install.txt (fixed typo)

 

The changes were actually pretty minor, with only one line added near the top of /admin/includes/language/english/orders_tracking.php, and the profit margin stuff added to /admin/orders_tracking.php.

 

-jared

Link to comment
Share on other sites

The following files were updated:

1) /admin/orders_tracking.php

2) /admin/includes/language/english/orders_tracking.php

- - Feel free, anyone, to add in additional language translations and stuff. I'll add Spanish if I remember anytime soon.

3) install.txt (fixed typo)

 

The changes were actually pretty minor, with only one line added near the top of /admin/includes/language/english/orders_tracking.php, and the profit margin stuff added to /admin/orders_tracking.php.

 

-jared

Link to comment
Share on other sites

Great Contribution, but there's a small bug in the get_order_totals function:

 

if ( $i == 0 ) {

$first=$col_value;

$last=$col_value; ==> Added by me, otherwise with only one order in a store, the order_total for a given month will appear as zero.

$i++;

} else {

 

The bug occurs when there's only one order for a given month ( I know this is not noticable when you have a live store), but I am including it so the function is functionally correct, and i notice this because my store is not live yet !! :)

Link to comment
Share on other sites

Another "yesterday" problem.

 

When it rolled over to 09-01-2004, my stats didn't show any orders at all for yesterday.

 

Look for

// next line to normalize $yesterday format to 2 digits
   if ($yesterday <10) {$yesterday = "0$yesterday";}
//    if ($yesterday_month <10) {$yesterday_month = "0$yesterday_month";}
//    if ($first_day_of_month == 1)  // if today is the first day of the month, then run yesterday stats for last_month,day instead of this_month,day
   $query = "SELECT * FROM orders WHERE date_purchased LIKE \"$yesterday_year-$yesterday_month-$yesterday%\"";
   $result = mysql_query($query) or die("Query failed : " . mysql_error());
   $yesterday_order_count=0;
   while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $yesterday_order_count++;
   }
   mysql_free_result($result);

and change it to

 

// next line to normalize $yesterday format to 2 digits
   if ($yesterday <10) {$yesterday = "0$yesterday";}
//    if ($yesterday_month <10) {$yesterday_month = "0$yesterday_month";}
//    if ($first_day_of_month == 1)  // if today is the first day of the month, then run yesterday stats for last_month,day instead of this_month,day
   $query = "SELECT * FROM orders WHERE date_purchased >= \"$yesterday_year-$yesterday_month-$yesterday%\"";
   $result = mysql_query($query) or die("Query failed : " . mysql_error());
   $yesterday_order_count=0;
   while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $yesterday_order_count++;
   }
   mysql_free_result($result);

 

We're just changing the query line from

  $query = "SELECT * FROM orders WHERE date_purchased LIKE \"$yesterday_year-$yesterday_month-$yesterday%\"";

to

  $query = "SELECT * FROM orders WHERE date_purchased >= \"$yesterday_year-$yesterday_month-$yesterday%\"";

 

That fixed it for me.

 

 

 

-jared

Edited by jcall
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...