Monthly Sales and Tax Summary Report [support thread]

Having the same issue as mentioned before and am having a hard time getting it solved...

I have 0% - 6% and 19% tax
The non taxed is showign as well as the taxed. However it is combined into 1 tax ( 6+19) and I need it to show them seperate from one another.
Cause now I need to click on each tax amount to see if it is all 19% or if there is a bit of 6% as well. Which takes up a lot of time :(

How and where can this be done???

Thanks
Chrissy

You need to look at orders_total table in your data base to see how each tax % is stored.

Skip

nholovacs:

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!

Hello! I'm having the same issue. Has there been a resolution to this?

Mark

Sorry, more info might help. I'm using zc v1.5.0, clean install via ftp and just installed v1.4 of plugin. This is a new store-first time installing zen-cart. I have multiple add-ons, also. I also see that when you click on the link for taxes collected, it doesn't match the total in the pop-up. If I'm reading this right, it's not looking up the same data? I need it to subtract the gift vouchers before calculating the tax, as 'nholovacs' states above. Thanks for any help someone could give.

tinman4jesus:

Sorry, more info might help. I'm using zc v1.5.0, clean install via ftp and just installed v1.4 of plugin. This is a new store-first time installing zen-cart. I have multiple add-ons, also. I also see that when you click on the link for taxes collected, it doesn't match the total in the pop-up. If I'm reading this right, it's not looking up the same data? I need it to subtract the gift vouchers before calculating the tax, as 'nholovacs' states above. Thanks for any help someone could give.

We are finding the same issue - the taxes collected in this mod are based on the full retail price not the price charged with the discount coupon (etc). As stated it does not match the pop up box.

The product sales (taxed or no taxed) should show the actual sales as shown on the invoices.

This is a great mod but needs a tweek.

thanks in advance if you can help steer us to a solution.

This is a great report that provides the basic information to make State & Local tax authorities happy.

It doesn't play nice with v1.5.3

Below is the log file contents generated when the report is executed but it is greek to me. Is there a simple solution to correct the issues?

[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): Access denied for user 'MyDomain'@'localhost' (using password: NO) in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 314
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): A link to the server could not be established in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 314
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 315
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): Access denied for user 'MyDomain'@'localhost' (using password: NO) in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 329
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): A link to the server could not be established in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 329
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 330
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): Access denied for user 'MyDomain'@'localhost' (using password: NO) in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 389
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_query(): A link to the server could not be established in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 389
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 390
[14-Jul-2014 12:18:57 America/Los_Angeles] PHP Warning:  mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/MyDomain/public_html/ZEN153/MySecretAdmin/stats_monthly_sales.php on line 395

Uh-oh! The plugin is performing database requests by issuing direct mysql_ functions against a mysqli_ database. As I use this plugin on one of my sites, I'll give it a look over the next couple of days and see what I can do to bring it into the ZC v1.5.3 environment.

I've submitted v1.5.0 to the plugins (http://www.zen-cart.com/downloads.php?do=file&id=734) for approval:

• Converted to use $db for SQL actions, as preventative measure for PHP 5.5 deprecation of mysql_* functions
• Converted explicit <input type="hidden" ...> to zen_draw_hidden_field calls
• Converted explicit <form ...> to zen_draw_form calls
• Corrected PHP warning on CSV download (file name issue)
• Converted use of $_SERVER variables to zen-cart functions
• Moved all language phrases to the language file
• Converted to use Zen Cart rounding function
• Added formatting strings for title of popup tax details title
• Added configuration switch to control whether or not S/H charges are added into the store's taxed/untaxed totals
• PHP 5.4-ready (remove/change ereg* calls)
• Use DEFAULT_CURRENCY decimal places for number_format
• Added separate columns for Gift Vouchers and Coupons; non-core Order Totals summed in "Other" column

v1.5.0 is now available for download in the Plugins section.

I noticed the current version group "Shipping& Handling" with Untaxed Total or Taxed Total. The older version had "Shipping& Handling" in a separate column.

How do I create a separate column for "Shipping& Handling" on the current version.

Please advise. I'm currently using zencart v1.5.3 and monthly_sales v1.5.0 2014/07/23

Thanks. Niki

Edit the file /YOUR_ADMIN/includes/languages/english/stats_monthly_sales.php, finding this block:

//-bof-a-lat9
//  Identifies whether the Shipping & Handling values are to be merged into the overall totals, one of:
//  - auto ... (default) S/H charges added to taxed/untaxed totals based on whether shipping is taxed for the store (i.e. sort order of "Tax" order total is greater than that of "Shipping").
//  - on ..... S/H charges added to taxed/untaxed totals *regardless* of store's Order Totals settings
//  - off .... S/H charges kept separate from taxed/untaxed totals *regardless* of store's Order Totals settings
define('STATS_MONTHLY_SALES_COMBINE_SHIPPING', 'auto');

For your store, set the value to 'off'.

Just updated this mod to provide 1.3.9 compatibility.

Thanks, swguy!

Thank YOU for all the great work you did on this mod. It's a very nice addition.

swguy:

Just updated this mod to provide 1.3.9 compatibility.

Ummm - 1.5.3 compatibility ?

frank18:

Ummm - 1.5.3 compatibility ?
Nope, swguy re-added ZC v1.3.9 compatibility, which I'd removed in my update. The plugin is now compatible with ZC 1.3.9, 1.5.0, 1.5.1, 1.5.2 and 1.5.3.

lat9:

Nope, swguy re-added ZC v1.3.9 compatibility, which I'd removed in my update. The plugin is now compatible with ZC 1.3.9, 1.5.0, 1.5.1, 1.5.2 and 1.5.3.

Stupid me! I mis-read that :blush:

Hi, can any help me?
I have twitch base cost plugin installed, how do i display the base cost in the report, i know subtracting from product sales will be complicated so i just want to display it there and subtract it manually myself.....

@Huang, why not post in Twitch's forum for the base cost mod and ask him to add it as an option to one of the sales reports. (It could be this one or another one.)

Environment: A new 1.5.4 site with display logs, encrypted master password, inventory report, and monthly sales installed in the admin.

When I select a state and then click on Filter, everything works well. If I then select a status other than all orders, I get the following debug.> [06-Jan-2015 23:56:45 UTC] #1 trigger_error() called at [/home/enigjohn/public_html/enl154/includes/classes/db/mysql/query_factory.php:155]

#2 queryFactory->show_error() called at [/home/enigjohn/public_html/enl154/includes/classes/db/mysql/query_factory.php:132]
#3 queryFactory->set_error() called at [/home/enigjohn/public_html/enl154/includes/classes/db/mysql/query_factory.php:262]
#4 queryFactory->Execute() called at [/home/enigjohn/public_html/enl154/newenl154/stats_monthly_sales.php:292]

[06-Jan-2015 23:56:45 UTC] PHP Fatal error: 1054:Unknown column 'o.delivery_state' in 'where clause' :: SELECT orders_status_name from orders_status WHERE language_id = '' AND orders_status_id =3 AND (o.delivery_state LIKE 'Texas') ==> (as called by) /home/enigjohn/public_html/enl154/newenl154/stats_monthly_sales.php on line 292 <== in /home/enigjohn/public_html/enl154/includes/classes/db/mysql/query_factory.php on line 155Possibly 1.5.4?