The uc_reports module has just been uploaded to bazaar (and added as a contrib module for those who want to test the module separately without disturbing their stable ubercart code). The module builds table reports for customers, products, sales for current ubercart orders. While far from the feature complete list we'd like to have, these reports will be good enough for smaller stores that don't have a huge amount of sales data. Later down the road, we'd like to add graphs and other useful report data. As always, bug finding, feature suggestions, comments, and other ideas are welcomed to help improve ubercart modules.
Thu, 11/08/2007 - 16:20
sales report calculations
First wanted to say this is a great module and good functionality.
For the sales report, the revenue totals being listed does not take into account (deduct) any discounts applied (like coupons or role based discounts) such as are reflected in the final order totals. Should it?
Re: sales report calculations
Hmm, revenue totals are based on the order_total row in uc_orders. I'm not familiar with the internals of those discount modules, but if they apply any added discount not reflected in order_total then it would certainly throw off the revenue totals. If that's the case, I would some how need to get information from these discount modules to adjust the revenue totals.
Re: Re: sales report calculations
Yes it is capturing the order total values correctly. There are just a few additional exceptions cases that I need to clean up in my discount module.
Btw, when I click on the "Yesterday" link in the Daily Sales report, it only gives me back a single item from the search even though multiple orders are recorded.
Thankx much!
Re: Re: Re: sales report calculations
Regarding the discrepancy between the Daily Sales reports and the resulting link. The Search Orders page (admin/store/orders/search) doesn't contain an parameter for order status; it returns all current, non-complete orders whereas the Daily Sales reports contains all completed orders. I added those links hoping that Ryan would throw in an additional parameter for order status into the Search Order form later down the road as it would be useful for people.
Nice work, Shawn!
Thanks for
Nice work, Shawn!
Thanks for it. I'm still testing it, and came to this question: when I try to modify the access control for viewing reports, I see no way to do it. I have set some administrative roles at the site, but I would like just some of them to be able to access the reports. Any help would be kindly appreciated.
Thanks =)
Re: uc_reports
Hi, I noticed that you mentioned adding graphs and charts, and just thought I would point out this module I saw on drupal.org the other day:
http://drupal.org/project/open_flash_chart_api
I haven't tried it out yet, but it looks cool...seems to provide the ability to add Google Analytics type charts pretty easily, though I don't know if there's a lot of overhead involved.
In any case, thanks for adding the reports!
xerbutter,
thanks for the
xerbutter,
thanks for the suggestion. Graphs and exporting are two features that I'd like to see in before 1.0. I'll give it a look.
Re: uc_reports
I would like to make a block for "users who bought this product also bought" links to be placed on every product page. I think it could be a good idea to be added to your uc_reports module, maybe if you like the idea I could try to add it to your module, it would be barely a couple of more functions, maybe three (a block_hook, the function for the select and maybe a theme function). What do you think Shawn?
Re: Re: uc_reports
Hmm, obviously is not a feature for every shop, but mainly for big ones, or a least with many, many products.
Re: Re: uc_reports
It sounds like a good idea. It would be better than having a separate module that does this. If you want to code up such a block, you can PM me the code and I'll look at incorporating it into the module.
Ok
I'll have to think how
Ok 
I'll have to think how exactly extract that information, which in fact many shops have but is somewhat tricky...
Re: Ok
I'll have to think how
Cool. To point you in the right direction, you'll want to look at the uc_order_products & uc_orders tables. They contain all the info you'll need to tell which products tend to be grouped together in orders.
Re: Re: Ok
I'll have to think how
Well, I guess that I should read the uc_orders table, look for the node id product (if any) and then read the users, and then read what other users have bought, sum it, order it by the sum and print the ones with a higher sum (higher number of sales). Maybe everything could be read from the same table. Uhmm, I'll try to find a moment to code it
dates not incrmenting properly
I don't think the report length is incrementing correctly. Fro example choosing weekly, starting on 11/1/07 I get the following time periods.
11/1/07-11/8/07
11/8/07-11/14/07
Shouldn't the send line' start date be 11/9/07 not 11/8/07.
I changed the following line
$start=$end+1;to read
$start = mktime(0,0,0,format_date($end,'custom',"n"),format_date($end,'custom',"j")+1,format_date($end,'custom',"Y")); 
Re: dates not incrmenting properly
Shouldn't it be 11/1/07-11/7/07.
my bad
greenSkin is right, my example is incorrect, though the example still stands true
Re: my bad
Yeah, I've introduced a few date arithmetic errors with the switch over to format_date. Look for all of this to be fixed in beta.
Re: Re: my bad
Where are we on the "users who bought this product also bought" module/addition/block whatever? Thx for the update.
Re: Re: Re: my bad
Seems like you are in the wrong thread? Check here: http://www.ubercart.org/forum/ideas_and_suggestions/2376/upsell_check_ou...
Re: Re: Re: Re: my bad
No. Thanks for the link, but this is the correct thread. Look up at the exchange between Shawn and Jorditr. That's what I was trying to follow up on.
Re: Re: Re: Re: Re: my bad
Jorditr never did get back to me on that topic. I'd be willing to put it in if some one submitted a patch, but right now I'm too busy to work on that myself.
Re: Re: Re: Re: Re: Re: my bad
I just started working on getting this module into our site -- we've done thousands of transactions and it looks like the reports module isn't scaling too well. =/
It takes forever for the page to load, and while its trying to display, mysql is running at 40% CPU load. I'm thinking that for reports we're going to need to either optimize the select queries or run a cron job to help cache the changes for the reports.
Re: Re: Re: Re: Re: Re: Re: my bad
I'm having issues too, with running a custom report. Most times the query just times out. I'm doing some database work this week, I'll try to find some ways to optimize it, but I'm not too familiar with uc_reports so I don't know where to start tweaking code.. yet.
It also seems to get worse when there is a month that ends in the middle of the week, and you try to get a report for that week. (If you're able to get to the Custom Reports page).
Re: Re: Re: Re: Re: Re: Re: Re: my bad
Yeah, the problem is that in order to make all those columns sortable I had to do some complex queries (i.e. sub queries inside a SELECT statement). You brought this to my attention a while back Torgos, I just never got around to come up with a more efficient way to query for all this aggregate data. A quick fix would be to ditch sorting for certain columns. I'm still up in the air to do about this, I'd like to revisit this after I take care of a few other issues.
state/zone custom option
For now I figured out a way around my optimization issues.. my client really only needs to see monthly summaries, which works fine.
I also added the ability to select sales by zone (state) for tax purposes. If you want to integrate this, I've attached a patched version of the module. I took the reports module from today at 12:01 1/25/08.
| Attachment | Size |
|---|---|
| uc_reports-1.patch | 3.83 KB |
Re: state/zone custom option
I've also noticed that the timestamps are off when you run the report.
For example if you run a custom report starting today, (or even if you click on "Today's sales") - you get this for a URL:
/admin/store/orders/search/results/0/0/0/0/0/0/1201248000/1201334399
Unfortunately, that first timestamp - 1201248000 - when you run it through a Unix Timestamp conversion page, returns the value "Fri Jan 25 0800 GMT".
When I look up the first order from this report, the times in the Admin Comments are:
01/25/2008
12:27:10 PM
Obviously this is some kind of math calculation error, and the only way I can see to fix it is to remove the GMT calculation from the reports timestamp.
Re: Re: state/zone custom option
To make it friendly to Drupal's format_date all I used gmmktime for all date spans in reports, then adjust the time based on Drupal's date & time settings. If I'm not mistaken Fri Jan 25 0800 GMT should = midnight your time, right? That should be correct for the start of the date, though I am concerned about 1201334399 since it should be 1201334400, I dunno.
Re: Re: Re: state/zone custom option
Yeah, it should be midnight - but the Order that the report brings back is 12:30 PM, not 12:30AM. PM would be "afternoon" and not "early morning"...
Re: Re: Re: Re: state/zone custom option
01/25/2008 12:27:10 PM = 01/25/2008 = Today (or it did last Friday)?
Or am I missing something here?
Re: Re: Re: Re: Re: state/zone custom option
Yes, 12:27:PM on 1/25/2008 is still 1/25/2008 - but it's only HALF of the day. The problem is that if I want "all orders from today" the sales report should start at 12:00:00 AM (technically, midnight, but it's the first hour for "today") and end at 11:59:59 PM - one minute before that day's "midnight".
If I'm trying to get "today's reports" we're not going to see any reports from 1AM through noon because of the way the reports system is calculating "Today." It goes double for us since we're on the left coast, and people in NY and England may already have made their purchases before noon our time.
Re: Re: Re: Re: Re: Re: state/zone custom option
Hmm, if I'm wrong in some assumption point out where I err because I don't see it. The date range in the search URL looks for orders between
1201248000 (Fri, 25 Jan 2008 08:00:00 GMT = Fri, 25 Jan 2008 00:00:00 PST) &
1201334399 (Sat, 26 Jan 2008 07:59:59 GMT = Fri, 25 Jan 2008 23:59:59 PST).
The UNIX timestamp doesn't adjust based on timezone so searching orders with timestamp range should return all orders that happen on 1/25/2008 for PST.
So there are no orders showing up earlier than 12:00 PM your time? I think the search orders form restricts itself to orders with a particular status. Could you plug that date range in a SQL query on your uc_orders table? Otherwise, I don't understand why that timestamp range wouldn't yield all orders created on 1/25/2008 for PST.
Re: Re: Re: Re: Re: Re: Re: state/zone custom option
Okay, we're getting closer. I think you're right, the query only seems to be going for certain payment status. Here's what happened when I ran your query:
324 total results
First order at: 12:01:24 AM
When I look at just today's results, using the correct timestamp in the URL:
11 results
First order at: 12:44:53 AM
However I know it's more than 11 orders here. It looks like they are all "payment received" as well. Hope this helps.
Re: Re: Re: Re: Re: Re: Re: Re: state/zone custom option
To follow up here, I've noticed a few things. First, the grand total sales were incorrect; for some reason I had a "GROUP BY order_status" thrown in the query which was throwing off the numbers. That's fixed now. Second, is dealing with the performance issues with the custom sales reports. Looking over the code again I see that I'm making a lot of separate DB queries in PHP. I need to go back over the code to see if I can condense these queries.
Re: Re: Re: Re: Re: Re: Re: Re: Re: state/zone custom option
Has this been fixed? I'm seeing that on RC2, the custom sales report with a date range doesn't work. Just trying to get 1st quarter sales figures together. When I select 1/1/08 - 3/31/08 -- no results by month, all zeros. When I look at the canned sales report, it has the numbers by month, not zeros. This used to work around the alpha8 bazaar timeframe. Since upgrading to RC2 and Drupal 5.7, the report fails. No errors in log. Thanks.
Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: state/zone custom option
Have you since solved this bug? Was this related to the order statuses thing?
If not, I'm trying to track any remaining issues through this thread.
Re: dates not incrmenting properly
I haven't looked at any of the code, but from experience in similar things I've found php's strtotime() function will perform almost anything I need and replaces lengthy and sometimes confusing mktime calls with short and easy to read code. Like..
$start = strtotime("+1 day", $end);