Guest Posted August 2, 2004 Share Posted August 2, 2004 sure! I'll look into it. -jared Quote Link to comment Share on other sites More sharing options...
Farrukh Posted August 3, 2004 Share Posted August 3, 2004 sure! I'll look into it. -jared Thanks a lot Quote Link to comment Share on other sites More sharing options...
Guest Posted August 3, 2004 Share Posted August 3, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted August 3, 2004 Share Posted August 3, 2004 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 Quote Link to comment Share on other sites More sharing options...
jello1 Posted August 11, 2004 Author Share Posted August 11, 2004 WOW..... I'm at a loss for words... WOW Quote <span style='font-family:Courier'>If you can't fix it Perl it!!!...</span> ****************************** Link to comment Share on other sites More sharing options...
Guest Posted August 12, 2004 Share Posted August 12, 2004 WOW..... I'm at a loss for words... WOW I hope that means that you like it. :D -jared Quote Link to comment Share on other sites More sharing options...
jello1 Posted August 18, 2004 Author Share Posted August 18, 2004 Indeed!!!....love it..great job Quote <span style='font-family:Courier'>If you can't fix it Perl it!!!...</span> ****************************** Link to comment Share on other sites More sharing options...
Guest Posted August 19, 2004 Share Posted August 19, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted August 20, 2004 Share Posted August 20, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted August 20, 2004 Share Posted August 20, 2004 v2.4 now available at http://www.oscommerce.com/community/contributions,1522 . Let me know what you think! -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted August 20, 2004 Share Posted August 20, 2004 Just realized that I've never put in the currency fix from http://www.oscommerce.com/forums/index.php?sho...ndpost&p=330236 . Can a non-US shop verify that the code in that post works for them? Thanks! -jared Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted August 21, 2004 Share Posted August 21, 2004 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 Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
FlyingMonkey Posted August 21, 2004 Share Posted August 21, 2004 (edited) A nice feature would be to set the variable gross profit rate. Great overall reporting system! :) Edited August 21, 2004 by FlyingMonkey Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Guest Posted August 24, 2004 Share Posted August 24, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted August 24, 2004 Share Posted August 24, 2004 yeah, that would be really cool. :) Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
Guest Posted August 24, 2004 Share Posted August 24, 2004 (edited) 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 August 24, 2004 by jcall Quote Link to comment Share on other sites More sharing options...
Guest Posted August 24, 2004 Share Posted August 24, 2004 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted August 24, 2004 Share Posted August 24, 2004 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 Quote Link to comment Share on other sites More sharing options...
FlyingMonkey Posted August 24, 2004 Share Posted August 24, 2004 very nice works like a charm. lol, on that nickname you gave me. Quote Most likely your question has been answered, please do a search first. Link to comment Share on other sites More sharing options...
kowalle Posted August 25, 2004 Share Posted August 25, 2004 Hi there! Im now to OSC and I have a problem with this contrib. I have no /admin/filename.php - it is at /admin/includes/ So what is wrong with my installation? Regards, Kowalle Quote Link to comment Share on other sites More sharing options...
Guest Posted August 25, 2004 Share Posted August 25, 2004 You are correct - - the file should be /admin/includes/filenames.php. My apologies for the typo. -jared Quote Link to comment Share on other sites More sharing options...
divine_comedy Posted August 29, 2004 Share Posted August 29, 2004 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 !! :) Quote Link to comment Share on other sites More sharing options...
Guest Posted August 31, 2004 Share Posted August 31, 2004 Thanks for catching that, Roger! I'd been wanting to fix that for some time, but never did. -jared Quote Link to comment Share on other sites More sharing options...
Guest Posted September 1, 2004 Share Posted September 1, 2004 (edited) 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 September 1, 2004 by jcall Quote Link to comment Share on other sites More sharing options...
Guest Posted September 1, 2004 Share Posted September 1, 2004 Ok - - I'm sleepier than I thought. That won't work on days other than the first day of the month. Better fix forthcoming. -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.