Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Admin report: Monthly Sales & Tax new version 1.3


zzfritz

Recommended Posts

Well that last code you gave me didn't fix it. It only moved each error up one line... LoL. I guess unless you have any other suggestions I'll just scrap the csv feature. Which is a shame cause that would have been increadibly handy. Thanks for the help.

Link to comment
Share on other sites

  • Replies 50
  • Created
  • Last Reply

Top Posters In This Topic

I believe this contribution should work with all snapshots after 8 Apr 2002, when the new table order_total and modules were added, since it relies on that table's data alone.

 

But you could confirm this and let us know.

Link to comment
Share on other sites

  • 3 weeks later...

I'm getting an SQL error after a fresh install of 1.4a. Any help would be aprreciated.

 

1064 - You have an error in your SQL syntax near '(o.date_purchased), month(o.date_purchased) order by o.date_purchased desc' at line 1

select sum(ot.value) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month, dayofmonth(o.date_purchased) row_day from orders o left join orders_total ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_subtotal' group by year(o.date_purchased), month(o.date_purchased) order by o.date_purchased desc

 

Sincerely;

 

Rich

Link to comment
Share on other sites

  • 5 weeks later...

Nice contrib. Ive installed 1.5.

 

My store is in Australia and all orders so far have been to Australian's which means ive paid GST for all of them. However the report is showing large amounts of Exempt sales when there shouldn't be any at all.

 

What should I be looking at to fix this?

Link to comment
Share on other sites

Sorry, reemo, the logic in version 1.x for determining exempt sales was based on my parochial experience in the US where state sales taxes are not paid on interstate shipments. That column shows the sum of subtotals for all orders shipped outside the store's zone, and the taxable sales column is the sum for orders shipped within the zone.

 

This will be revised in version 2.0, in which the exempt sales column will sum only the orders for which no tax was actually charged, and the taxable sales column will sum the orders for which tax was actually charged.

 

But to do this, I need some help with the sql queries and no one has responded yet to my plea:

http://www.oscommerce.com/forums/viewtopic.php?p=104263

Link to comment
Share on other sites

Sorry, I didn't see that one.

 

I'm not sure I understand the question, though.

 

 

WHERE ot.class="taxes" AND D ot.value="0"

 

is a perfectly valid where clause in MySQL. However, if the 'value' field is of type 'decimal', as I suspect it is (I havn't looked), then there is no need for the quotes.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

  • 2 weeks later...

Do you know why there would be only the TABLE_FOOTER_YEAR for 2001 and 2002 but not 2003? So it looks like:

 

Month Year

---------------------------

Feb 2003 etc.

Jan 2003 etc.

Dec 2002 etc.

Oct 2002 etc.

May 2002 etc.

Apr 2002 etc.

Jan 2002 etc.

---------------------------

YEAR 2002 etc.

---------------------------

Dec 2001 etc.

Nov 2001 etc.

Oct 2001 etc.

---------------------------

YEAR 2001 etc.

---------------------------

 

Instead of:

 

 

 

Month Year

---------------------------

Feb 2003 etc.

Jan 2003 etc.

---------------------------

YEAR 2003 etc. // the footer part for the current year is missing.

---------------------------

Dec 2002 etc.

Oct 2002 etc.

May 2002 etc.

Apr 2002 etc.

Jan 2002 etc.

---------------------------

YEAR 2002 etc.

---------------------------

Dec 2001 etc.

Nov 2001 etc.

Oct 2001 etc.

---------------------------

YEAR 2001 etc.

---------------------------

 

Regards,

Michelle

Link to comment
Share on other sites

I have installed the contribution

 

but I am getting this in

 

Admin => Reports =>Monthly Sales/Tax

 

 

HEADING_TITLE

HEADING_TITLE_STATUS: 





TEXT_BUTTON_REPORT_PRINT 

TEXT_BUTTON_REPORT_FILE 

TEXT_BUTTON_REPORT_HELP 



TABLE_HEADING_MONTH

TABLE_HEADING_YEAR

TABLE_HEADING_INCOME

TABLE_HEADING_SALES

TABLE_HEADING_NONTAXED

TABLE_HEADING_TAXED

TABLE_HEADING_TAX_COLL

TABLE_HEADING_SHIPHNDL

 

Is this the way its meant to show.

Link to comment
Share on other sites

Is this the way its meant to show.

 

No, recheck your installation steps.

 

ZZ, I'd love to see a couple of small details added to this report, if you ever get the chance. Number of orders, and a weekly breakdown would be greatly helpful.

 

Love the contribution. OSC needs more reports.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

I don't thinkn that I'm willing to disclose that kind of information outright, however, I can tell you that with what you have described above, you do not have it installed correctly.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Michelle -- we determined that this behavior results from your having no sales in January, which is the month that triggers output of the yearly footer. The logic will be improved in the next interim release, a few days hence.

 

Farrukh -- I believe you have misplaced the two scripts which comprise this report contribution. The larger one goes in the admin/ folder, and the smaller one with the define statements goes in the admin/includes/languages/english/ folder.

 

Chris -- The next major revision of this contribution (maybe next month) will provide the capability to expand each month's summary line to show the daily totals for that month. But I resist adding things, such as number of orders, which are measures of marketing performance rather than accounting data. There is not room on the screen to include enough to satisfy both objectives, so these should be in separate reports. Other contributors are making efforts in that direction as well.

Link to comment
Share on other sites

Firstly thanks for the great contribution Fritz, but is there any way that it can list sales from tax exempt products seperately from taxable products?

 

We are really looking forward to the next revision too as we want to use this mod to enter our online sales data into our accounting package and we really need to do this at least weekly if not daily.

Best wishes

Steve

Link to comment
Share on other sites

is there any way that it can list sales from tax exempt products seperately from taxable products?

Not quite sure I understand precisely what you are asking, and tax-related distinctions are quite tricky to implement since the world of tax has no limit to its complexity. It's a wonder the osC developers have been able to provide what is necessary for us to tailor the variety of taxing strategies which are out there, but apparently they have.

 

Having the capability to calculate the imposed tax on an order is one thing, but to summarize the resulting taxes (or the sales from which they were derived) in a report is something else. The logic of doing it is backwards, so to speak, making inferences instead of imposing methods.

 

I began with a very parochial (US) view of sales tax, a simple percentage state tax on selected merchandise with an occasional hike for local authorities. But now I realize that many of the other taxing systems are quite different, and perhaps beyond my ability to synthesize.

 

The incremental improvement I will next provide for this report is to change the logic for distinguishing between what I characterize as 'taxed sales' and 'exempt sales'. Through version 1.5, those columns show the subtotals from sales shipped within vs. outside the store zone. That is not sophisticated enough in stores that charge sales tax for multiple jurisdictions due to having a business presence in them (which requires the imposition of the tax in the destination state), other than the store zone itself. So, in version 1.6, the taxed column will be the subtotal of all orders for which any tax was added, and the exempt column will be the subtotal of all orders for which no tax was added.

 

A further planned enhancement will be to make the figures in the tax amount column be live links, which if pressed, reveal the breakdown of the figure by tax zone or tax type -- not interesting if you only have one tax zone or tax type, but essential information if you have more than one. The main purpose of this report is to extract the data necessary for preparing sales tax filings to the taxing authorities.

 

So, to get back to the question, it might be yet another thing to look for sales based on whether the products themselves were exempt. That could be the same as what I will do in version 1.6, but maybe it is not.

Link to comment
Share on other sites

So, in version 1.6, the taxed column will be the subtotal of all orders for which any tax was added, and the exempt column will be the subtotal of all orders for which no tax was added.

That would difinately be a help but could that distinguish seperately any orders that contain some taxable items and some non taxable items?

 

So, to get back to the question, it might be yet another thing to look for sales based on whether the products themselves were exempt. That could be the same as what I will do in version 1.6, but maybe it is not.n

Thanks any way for all the brilliant work you are doing so far :D

Best wishes

Steve

Link to comment
Share on other sites

but could that distinguish seperately any orders that contain some taxable items and some non taxable items?

No, it wouldn't. As I mentioned above, the possible variations for summarizing taxable vs. nontaxable orders/items are endless. So we need to focus on what summaries have practical value.

 

The elements of the report that are universally worthwhile are the columns that summarize the gross totals, product subtotals, tax, shipping, and the optional loworder fee and credit class amounts. The two extra columns were my attempt to enhance the report for the simple tax reporting obligation in most US states, where interstate sales are exempt. If there are a few other simple analogues useful in other jurisdictions, I will try to implement them for the good of the community. Suggestions are welcome if they are accompanied by an explanation of their pragmatic goal.

Link to comment
Share on other sites

Sorry I probably seem like I am babbling and asking for daft things :oops:

What I need (and I am only politely asking if this is possible, I am not expecting you to do it) is this:

a. Total sales (including delivery costs)

b. Total sales (including delivery costs) on which tax was charged

c. Total sales (including delivery costs) on which tax was NOT charged (either because it was a zero rated item or because it was supplied to a customer outside our tax region)

d. Total tax collected

e. Total of any discounts or gift vouchers used.

Then b. + c. + d. + e. should equal a.

My need for this is to enter the information from my on-line sales back into my accounting package (Sage Instant Accounting). The idea being that each month (or hopefully each week) I would generate an invoice in Sage with the a fictional customer called internet sales, the first item on the invoice would be taxable goods (b.) sage would then calculate the tax which should be roughly the same as (d.) , the second item on the invoice would be non taxable goods (c.) the third item would be the discounts (e).

I would be quite happy to make a small donation to someone if your contribution could be modified to produce the above data.

Best wishes

Steve

Link to comment
Share on other sites

What livefooduk asks for is exactly what the report was designed to provide, an accounting summary from which to prepare tax filings. It works that way for me, only because I am in a US state where sales tax is charged for sales shipped within the state and my store has no other tax obligation. The leftmost column (1) is gross income, and indeed is the sum of columns 2, 5, 6, and optional 7 & 8. The troublesome columns, as I pointed out above in this thread, are 3 and 4 because making the distinction for taxed/exempt sales is not a simple matter.

 

Up through version 1.5, the "exempt sales" column is the sum of the subtotals of orders shipped outside my store's zone, and the "taxable" column is the sum of order subtotals within the store's zone. That works for my simple situation, but not for others.

 

The next revision, which is nearly ready, may be (almost) what you are asking for because it will distinguish taxable/exempt based on whether tax for the orders was zero. These columns will be the sum of order subtotals, which includes shipping only if you have set up the Order Total module that way.

Link to comment
Share on other sites

The next revision, which is nearly ready, may be (almost) what you are asking for because it will distinguish taxable/exempt based on whether tax for the orders was zero.

Presumably though that will not distinguish those orders that have some taxable goods and some untaxable goods. :cry:

Best wishes

Steve

Link to comment
Share on other sites

not distinguish those orders that have some  taxable goods and some untaxable goods

To summarize the taxable and nontaxable goods, the report would have to review the order details that were processed when the order was made (the individual products, their tax classes and the associated calculations). I had intended to rely only on what is available in the orders_total table for the data summarized by this report (with the limited exception that the date the order was made is taken from the orders table).

 

This illustrates the principle that no report satisfies everyone's expectations for it. Sorry about that.

Link to comment
Share on other sites

  • 3 weeks later...

I just upgraded to the latest release. For some reason, my totals for the month of March are all out of wack.

 

How can I fix this?

 

Thanks

Link to comment
Share on other sites

my totals for the month of March are all out of wack

 

That's not a sufficiently precise description to work from. The report just summarizes what is in the database orders_total table, so maybe that's where the problem is.

Link to comment
Share on other sites

You are right. I am using the order update tool. Once I updated this order, the totals for the month dont reflect the order that was modified.

 

Thanks

 

This is a really a great contibution.

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