Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Admin report: Monthly Sales & Tax


Recommended Posts

The first release version of this administration tool report has been contributed here:



This basic report provides a financial summary of the store's history, showing for each month the gross income, sales income (broken down into taxable and exempt), tax collected, shipping & handling charges, and low order fees. These amounts are summed from orders with status = Shipped, as of the date the order status last changed.


One of the common signals for the failure of a small business is inattention to monthly sales tax obligations. An owner is responsibile to account for sales, and to distinguish which are exempt and which require taxes be paid. osC is clever enough to mechanize the inclusion of sales tax in orders, and this report helps the owner retrieve the summary data needed for tax filing and payment.

Link to comment
Share on other sites

  • Replies 81
  • Created
  • Last Reply

Top Posters In This Topic

Tested it and gives good info.


I Think a status page would be beneficial to all store owners to start their day with actual store status:

Started actually with pending orders, shipped orders on index page.... but could add daily snapshot in terms of other quality indicators:


->average nbr of days to ship order. (alert for all orders not shipped after so many days)

->monthly sales.

->average daily sales

-> nbr of customers

->nbr of newsletter subscribers....



On the same line, are preparing a report which gives average daily sales with same breakdown and gives projection to end of month. (current average daily sale * 30)


Of course far from perfect in terms of forecast but is a basis for establishing potential end of month sales.

Link to comment
Share on other sites

Sounds like a good idea, although when I tried it out and it gave me a cgi error:


The specified CGI application misbehaved by not returning a complete set of HTTP headers. The headers it did return are:


Got me stuffed why it does'nt work on my install. :cry:

Link to comment
Share on other sites

For clarification, when I have referred in this contribution and its documentation to the order status "Shipped" this value (='3') is in the default installation as "Delivered" in English, "Versendet" (sent) in German, and "Entregado" (given out) in Spanish.


On my site, I changed the name for this status to "Shipped" because we don't really know when it is delivered (well, we could, but that is a research project). This is the status that means the order is fulfilled, and has been tendered to the shipper. Out of our hands, and on its way.


There is no management guide for using osCommerce (yet), but if there were, it would suggest some rules or conventions for how the order status values are used. For instance, my store personnel are instructed to change the status from Pending to Processing when the order has been reviewed and determined that there is some cause for delay in fulfilling it -- maybe the customer has to be asked for permission to substitute a size or color, or restocking is expected soon. We also use the order comment field to state what is going on, so we and the customer (by the email confirmation) have a record of that.

Link to comment
Share on other sites

Hello Fritz,


Thanks for the contribution, but I have a slight problem with it.


For me, the report only shows the header description (i.e Month, Gross Income, etc), with no value at all. Since I do not charge sales tax, could it be the source of the problem?




Please give your rating on OSCommerce at FreebiesCafe.com, a search directory for free scripts, software, services and resources.

Link to comment
Share on other sites

Jalil, the failure to get any report lines is more likely a result of what order status codes your site has defined. As stated in the install.txt, the report assumes completed orders have orders_status=3. The report is intended to summarize only the orders actually processed and shipped out.


If you have changed the list in Administration Tool -> Localization -> Orders Status, or do not update your orders to what is the default third choice ("Delivered"), the report will not find any orders with the status it looks for.


But the report can easily be revised to look for a different orders status value, whichever one you want, by editing this in six places where it occurs in stats_monthly_sales.php:

o.orders_status = 3

Link to comment
Share on other sites

I have revised this report contribution so it works with any site's order status list.


Now it will generate a summary for all orders or for whichever status is selected from the dropdown list (just like the Orders review and edit page).


Willing testers of this revision should send me an email direct and I will reply with the file attached. I want to avoid making it a contribution until reasonably satisfied it works correctly.

Link to comment
Share on other sites

This is the error message i get after installing this mod:


1064 - You have an error in your SQL syntax near 'desc order by o.last_modified desc' at line 1


select truncate(sum(ot.value),2) gross_sales, monthname(o.last_modified) row_month, year(o.last_modified) row_year, month(o.last_modified) i_month from orders o left join orders_total ot on (o.orders_id = ot.orders_id) where o.orders_status = 3 and ot.class = 'ot_subtotal' group by year(o.last_modified) desc, month(o.last_modified) desc order by o.last_modified desc



any ideas on how to fix this?





Link to comment
Share on other sites

Perhaps the SQL syntax is fuzzy and does not work with some versions, since this query does okay with all testers but you (so far).


I suspect the issue is with two columns used in the GROUP BY clause, each with descend modifiers and a comma separating them. Perhaps this is a sytax error tolerated by some versions and not by others. I am curious what version your host is using, to assist further investigation. (Use the admin Tools -> Server Info to see this, mine says "MySQL 3.23.45")


Meanwhile there is a revised beta which I am emailing you, but its improvements do not affect this query problem.

Link to comment
Share on other sites

Vincent, never mind checking your version of SQL. I have removed both DESC modifiers from the GROUP BY clause since they don't make any difference (the ORDER BY clause is the only place you need to control the order). Apparently my host version is more forgiving than yours.


Thanks for the tip, that's what independent testing is for. The revision should be public within a few hours.

Link to comment
Share on other sites

The new and improved version of this contribution is posted:




The report will now summarize the totals for any choice of order type (or for all orders), and has calendar year footers.


Thanks to the testers for shaking this down. The only identified bug is that the amounts of Gift Vouchers (for those sites that are using this contribution from Ian) are not included, so the totals appear off by that amount.

Link to comment
Share on other sites

riff: thats usually code for "page not found" :)


so just check your includes


Yeah, that's what I thought, So I checked the file name and I'm using the right page name in the URL and I think all the includes are there. I followed all the install instructions too. My admin has be modified quite a bit so it could be that. I'll have to go over it again.


Just my luck. Everytime I find a good contrib, it either doesn't work for me or it's works only with older vers of the site. :roll:

Link to comment
Share on other sites

your the man......


works great on my site. Now I know exactly how much not to pay Uncle Sam ...




Thanks for the great contibution ....


LMAO!!! 8)

Link to comment
Share on other sites

I have made an improvement that is available for beta testing:

compatibility for those using the Gift Voucher contribution (or any other mod that uses special class values in the orders_total table) by adding another column for these values (but only if they are present), so the whole row adds up correctly.


If you use Gift Voucher, and want to try this, send me an email (or personal message including your email address) so I can reply with the attachment. When I am sure it does more good than harm, it will be publicly contributed.

Link to comment
Share on other sites

  • 5 months later...
  • 3 weeks later...

Ive just installed this mod on todays snapshot and am gettign the following errors:


Warning: Missing argument 2 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


Warning: Missing argument 3 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


Warning: Missing argument 2 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


Warning: Missing argument 3 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


anyone have any ideas on what the cause of this might be?

Link to comment
Share on other sites



Ive loaded the new version and i am still getting errors.


Ive set the csv to false. but im still getting :


Warning: fopen(temp.csv) [function.fopen]: failed to create stream: Permission denied in /home/stadmin/public_html/demostore/admin/stats_monthly_sales.php on line 46


Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/stadmin/public_html/demostore/admin/stats_monthly_sales.php:46) in /home/stadmin/public_html/demostore/admin/includes/functions/sessions.php on line 67




Warning: Missing argument 3 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


Warning: Missing argument 3 for tep_get_zone_name() in /home/stadmin/public_html/demostore/admin/includes/functions/general.php on line 259


Im using the current 2.2CVS version


any ideas?

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.

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...