Monthly Sales and Tax Summary Report [support thread]

Renz:

Hi,

I have duplicated my core files into another site and this mod is amongst them, I would like to know If I can zero the figures for the new business.

Can it be done in anyway or is it an overwrite of the mod with.

Cheers

RR

The report data comes from the orders and orders_total tables of your database. So if your new business site started with a clean database there should be no values.

Skip

Hello,
I have this mod installed that figures out profit:
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=278

Any way you can take a look and see if it is possilble to add a profit column to your mod? It'd be great to show Tax collected, Gross Sales, and Gross Profit all on the same page, separated by Month and Year like you have.

Great mod, btw!

JoeZ:

Hello,
I have this mod installed that figures out profit:
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=278

Any way you can take a look and see if it is possilble to add a profit column to your mod? It'd be great to show Tax collected, Gross Sales, and Gross Profit all on the same page, separated by Month and Year like you have.

Great mod, btw!

With ZC 2.0 coming out and the profit mod over writing core files. Adding this option is not something that I want to do.
Can it be done YES.

Skip

Does anyone know if this works with 1.3.9 and PHP 5.3?

anita:

Does anyone know if this works with 1.3.9 and PHP 5.3?

Yes all 1.3.x and php 5.3

Skip

I'm sure I'm missing something really obvious as I'm relatively new to Zen Cart. I've installed the files for this addon but their not showing up under the reports menu.

I have successfully installed a couple of shipping modules, COWOA so I'm not quite that green... but green enough apparently.

I did put them in their respective folders in the admin file on my server.

I'm running 1.3.9h

I've spent more than a couple of hours looking for a thread. Thanks for any help,
David

dkeil:

I'm sure I'm missing something really obvious as I'm relatively new to Zen Cart. I've installed the files for this addon but their not showing up under the reports menu.

I have successfully installed a couple of shipping modules, COWOA so I'm not quite that green... but green enough apparently.

I did put them in their respective folders in the admin file on my server.

I'm running 1.3.9h

I've spent more than a couple of hours looking for a thread. Thanks for any help,
David

It sounds like you have not renamed the mods admin folder to match the admin folder that you are using on your site.

Double check that the mods files where uploaded to there correct folders.

Skip

Thank you Skip,

I'm still wrapping my head around the file structure. I haven't renamed my folders, except for my template. So I assumed that the files would go in the admin folder and structure on the server. But because of your post I took a closer look at the actual address of my admin section and realized I wasn't looking at my admin directory but instead my manage directory. Moved the files and of course, they showed up in my admin section.

Thanks,
David

Hi, like the mod so far, however, I have two tax classes for the netherlands, one at 6% and the other at 19%. Both tax classes are installed properly (they work fine when items are sold) however when using your mod I only see a total tax collected. When I click on the "tax" number to get the details I get something like this:

2010/12

: 1,658.50

Only showing the amount but not listing the tax classes. Also, this number is considerable lower than the actual taxes collected (the number I clicked on). I really would love this to work so I can stop paying the tax office 19% on things I charged only 6% for! Can you direct me on where to look for the error? Does this have something to do with the mod? I imagine I have overlooked something...

I am using 1.3.9f

Any help would be greatly appriciated.

Cheers,
jeff

jcun45:

Hi, like the mod so far, however, I have two tax classes for the netherlands, one at 6% and the other at 19%. Both tax classes are installed properly (they work fine when items are sold) however when using your mod I only see a total tax collected. When I click on the "tax" number to get the details I get something like this:

2010/12 : 1,658.50

Only showing the amount but not listing the tax classes. Also, this number is considerable lower than the actual taxes collected (the number I clicked on). I really would love this to work so I can stop paying the tax office 19% on things I charged only 6% for! Can you direct me on where to look for the error? Does this have something to do with the mod? I imagine I have overlooked something...

I am using 1.3.9f

Any help would be greatly appriciated.

Cheers,
jeff

Look at your
table orders_total

orders_total_id ... orders_id .. title .................... text .......... value ... class ....... sort_order
67  ............... 20050 ...... Sub-Total: ............... $30.00 ........ 30.0000 . ot_subtotal . 1
68  ............... 20050 ...... UPS (1 x 26.4lbs) (Ground) $13.01 ........ 13.0100 . ot_shipping . 2
69  ............... 20050 ...... TAX 4.5%: ...............  $1.35 .........  1.3500 . ot_tax ...... 3
70  ............... 20050 ...... Total: ................... <b>$44.36</b> . 44.3600 . ot_total .... 4

How are you storing the %6 tax and the 19% tax is there another class? And if so what is its name.

If you look in admin/stats_monthly_sales.php you will find queries getting the class info and you most likely have a class name unknown to this mod.

Skip

Thanks for the response, I have sorted it out actually. For my international customers I simply never installed the order-total tax module as I had the prices displayed in the e-shop to include the tax to keep things simple and to keep checkout a bit cleaner...who wants to know they're paying tax right? However, their final invoice did include the tax info regardless.

So, for anyone with a similar problem in the future, I had to first install the tax module in the admin, simple enough, and then go to the admin/configuration/my store and make sure split tax lines was set to "true" as I am using multiple tax rates. Pretty basic I know but thanks for the Mod, this is really helpful!

Cheers,
jeff

Oh, and by the way, i would not have figured that out if you hadn't told me to look at my tables and see what was going on there! So thanks for that!

jeff

jcun45:

Oh, and by the way, i would not have figured that out if you hadn't told me to look at my tables and see what was going on there! So thanks for that!

jeff

Glad to help

Skip

Hello, I do have one more question about this Mod. I am using Ollacart Point of Sale in my shop. Your mod works great with e-shop orders but with Ollacart I only get one tax amount. Looking at the the database tables in "order total" I only see ":" then one amount for sales tax. This is also the only thing I see in the tax area when I look at the actual order invoice in Zencart. However, the in-store recepts have "Sales Tax" as the field, but the two seperate tax classes are simply added and the total is given. Your mod would obviously be a lot more useful if the invoices in Zencart from the in-store orders (using Ollacart) would list the 6% and 19% seperately like my e-shop orders do.

I understand that this is not your problem as it is apparently an Ollacart issue but it is a big problem for me as I am paying the tax man more than I need to.

Since Ollacart does read the different tax rates with no problem and calculates them during checkout, can you think of an easy way to get this to work with the invoices in Zencart? Is this something you could handle for a fee?

I understand if you don't feel like dealing with this, just thought I would ask.

Cheers,
jeff

Jeff

I understand your issue, with this report mod it can only read what is in the database.
I have not done any updates to this mod because of 2.0 coming out (within the next year or two) and I think that the ZC core code will take into account VAT tax because the USA is going to have it. So there will be multiple tax classes for each item.

Skip

Hi everybody.

I wanted to get the paypal fee calculated in this amazing mod. So instead of waiting for the 2.0, you can replace this line (line 378):

$sales_query_raw = "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 " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) WHERE ";

by this one:

$sales_query_raw = "SELECT sum(p.mc_gross - p.mc_fee) 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 " . TABLE_ORDERS . " o INNER JOIN paypal p ON (o.orders_id = p.order_id) left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) WHERE ";

It will change yout gross income row and include the paypal fees. And it takes care of the paypal currency exchange rates as all of the values are taken from the paypal table.

I know it's not the best way to do it for most of you as you may still want to get the gross value, but as i only use paypal, this is perfect for me.

Sorry... forget that. There is a problem with the currencies and even the dates.
The paypal table stores the gross amount in the original currency. So the calculation i made is mixing currencies. You could use the settle amount, but it is at 0 if the currency is the default one. The mod is a little more complicated than just replacing this line.

Sorry...

First of all, I wish to thank everybody who has posted here and other places in the Zen Cart support threads. Most of my issues were resolved simply by reading the threads and solutions to common problems. I now believe I have a not-so-common issue.

Basically, it seems that Gross sales and tax are incorrect when a coupon is involved. I picked a day I only did a single sale and a coupon was used. in the report it looks like this:
http://www.vacstechservices.com/issue/report.png

But the invoice looks like this:
http://www.vacstechservices.com/issue/invoice.png

You may notice that the product sales total in the report ($109.98) does not actually equal the product sales (subtotal - coupons & discounts [$98.98]). My logic is that total product sales should be total price associated with product sales, not gross price before discount. I'm not sure if this is a bug or the intended behavior of the mod but when reporting to state governments for sales tax they only care about your actual product sales, not what you would have sold had the coupon been omitted so this is why I think it is a bug.

The more important thing is the tax calculation which I think is an obvious bug. When a coupon is used, the report shows a higher tax burden then what is actually collected. For example, the report shows the tax burden on this sale to be $8.80, when in fact the tax collected on the invoice is $7.92. When I do the math I can determine that subtotal [$109.98] - coupon [$11.00] * tax percentage [$0.08] = $7.92, so the invoice is accurate. Essentially, the tax collected field is certainly wrong.

As it turns out, I have been reporting a higher income and paying a higher tax burden for no reason... As I don't see anyone else with this issue (I have been searching for quite some time, so I am very sorry if I missed something) I think maybe I have configured something incorrectly... Perhaps someone can shed some light into my Dilemma and help me resolve this?

I appreciate anything that will help!

oh, and I am using v1.3.9c

hello, I am using v1.3.9h and uploaded the files to there
respective directories but "Admin->Reports>Monthly Sales" menu choice isn't there !

I have noticed you have being working on it the last days! so is there something what wents wrong ?

regards, Cees