Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Monthly Sales & Tax version 2


zzfritz

Recommended Posts

Keep the feedback coming, it is helpful.

 

I am studying the way order totals are calculated in checkout, so the report can reconstruct the summary in the same way. This requires finding the status (on or off) and the sort order of the configuration variables (MODULE_ORDER_TOTAL_SUBTOTAL_SORT_ORDER, MODULE_ORDER_TOTAL_TAX_SORT_ORDER, etc). The report could adapt (as it already does to the presence or absence of the low order fee and 'extra' orders total class such as gift voucher) to each store's configuration.

 

This still leaves the question of whether taxed vs. nontaxed order subtotals are determined by the presence or absence of tax in the order total, or by the shipping zone. The former (presence or absence of tax) is probably better, although not absolutely perfect. If an order could contain both taxed and nontaxed items, neither method will produce a correct summary. That bothers me a little.

 

Re the popup detail of tax, the method used is simply to group the orders total entries (for the relevant dates and status) by the title field. To use kooyan's example above, "enthaltene (incl). 16% MwSt:", "enthaltene (incl) 7% MwSt:" and "enthaltene (incl) MwSt. 16%:" are found in this field and therefore summarized separately because they are not identical. Can anyone explain why checkout (which creates the orders_total table entries), is storing the text description of the 16% tax in two different ways? If checkout stored the tax_class_id instead of (or in addition to) the tax_class_description, this question would not have to be asked.

Link to comment
Share on other sites

  • Replies 55
  • Created
  • Last Reply

Top Posters In This Topic

Hey Fritz,

 

Here's another possible example of a order total.

 

Item Purchased: 10.00

Discount: 1.00

Tax: 8.25% = 0.83

Subtotal: = 9.83

Shipping: = 5.00

Handling: = 0.00

Gift Voucher: 2.00

Grand Total: 12.83

 

This is just an example.

Link to comment
Share on other sites

Fritz, I am new to this OSC thing, and your module sounds very similar to what I am looking for. One thing missing from your module is the ability to keep track of the cost price of items in the store, so the totals module can total up the profit on those items at the end of the month.

 

Have you thought about adding something like this to your module? I can't find anything like that right now, so I might start working on adding support for this, perhaps as an extension to your module.

 

Thanks!

Link to comment
Share on other sites

Talon's example is a good demonstration of how elaborate a store's order total structure can be, and why it is so difficult to make this report work for everyone. But I do believe it can be done, using the sort order of the order total components, in the next major revision.

 

Kendall, the reporting of store profit is a fine objective but must be the subject of another report. Just reporting the income is complicated enough for this project.

 

I am looking for feedback to confirm the latest v2.1.1 is okay for those who wanted the nontaxed and taxed columns back in simple store configurations.

Link to comment
Share on other sites

just figured i would post and let you guys know and let the creator know that this is adding the subtotal and total together for all orders so your gross earnings are not reported correctly, they are actually doubled, and i have created the products cost/profit report for this but i have to test a little more before sharing the code for it and i am also going to look at the addition of the total and subtotal and cancel that out and i will let you guys know how it comes along.

Link to comment
Share on other sites

Zzfritz,

 

I installed the contrib but I have a couple of problems with it...

 

I am unable to "drill down" on anything. I just get the "Monthly Sales/Tax Summary" listing and nothing is clickable.

 

All sales are listed as "Exempt" even though about half of them are taxable. The "Taxes Paid" column is correct.

 

Thanks,

CatDadRick

Link to comment
Share on other sites

CatDadRick: are you sure you installed v2.1.1 of the report?

 

The column heading "exempt" was changed to "nontaxed" since v2.0.

 

Click the Help button, and scroll to the bottom where it says "v 2.1.1"

 

The month names in the left column should be active links, as should the tax values.

Link to comment
Share on other sites

Zzfritz,

 

Thanks for your reply. I found the problem but could not get into the forums to post.

 

I have two catalog installations, one live and one just for testing. I installed your mod to my test site, but was trying to get it to work on the live site. When I tried the mod on the test site, it worked flawlessly, so I've installed it to my live site. Great work.

 

Thanks,

CatDadRick

Link to comment
Share on other sites

  • 3 weeks later...

Sorry to Fritz, I sent him an email before I found this thread.....

 

Can anyone tell me what the sql would be for getting the report

 

to be able to select a particular manufacturer to report on.

 

That way I can get the monthly sales & tax report per manufacturer so I can see their sales.

 

It would be great if that could be parameterised but just the sql mod would be great - can anyone help please?

 

Joe

Link to comment
Share on other sites

B) For you people that couldn't get this thing to work right the first time because you can not find this .php file as instructed in step 2 of the installation guide:

 

admin/includes/filenames.php

 

try modifing this file instead :

 

admin/includes/application_top.php

 

took me a while tring to figure out what's going on, and finally found out that my version of osC ( I just installed today 05/05/04 ) have a different data structure (not only this file).

 

Peace Out!

 

By the way, GREAT WORK, zzfritz , keep it up!

Link to comment
Share on other sites

  • 2 weeks later...
One thing missing from your module is the ability to keep track of the cost price of items in the store, so the totals module can total up the profit on those items at the end of the month.

I have been thinking about the same thing but as fritz said, it's a totally different animal. In concept though, it should be fairly easy for someone with decent PHP/SQL knowledge to put it together. You would need a new field in admin during product entry for the price you paid per item and then link to that to the product sales. Would display a total sales and total you paid, subtract one from the other for your profit.

 

 

Anyway, I was just about to add fritz's mod so I have no comments yet other than to say THANKS! :) This mod is perfect for my site as I only sell taxable items and atm at least, I don't use any sort of coupon mod. And I file my own taxes (and dread doing so) so anything that makes it a little easier is a welcome addition.

Link to comment
Share on other sites

  • 2 months later...

Hello everybody,

 

I was unable to find the solution for my problem, so I have to post it:

 

The tax for our store, located in Germany, is counted double. Does anybody know how to change this?

 

Thx, Michael

Not because it is difficult we don't dare to do it, but because we don't dare to do it, it is difficult. (Seneca)

Link to comment
Share on other sites

  • 1 month later...

This program is exactly what we needed. We have installed your 2.1.1 and looking at the products purchased - it is not acurately reporting what I have actually sold in the past months.

 

I read all through the forum and may be misunderstanding what this script is exactly supposed to be doing in this field. I'm assuming it is supposed to tell me the total number of a particular product sold in this field for all months. If so, mine is way under the total it should be and hopefully you could shed some light for me please?

 

Thank you for a wonderful script.

Link to comment
Share on other sites

  • 2 months later...

For my heavily modified MS2 code, the Monthly Sales & Tax Report 2.1.1 did not correctly calculate the taxed/non-taxed detail by day (i.e. report after clicking the month). The reason is that:

 

	if ($sel_month<>0) {
 $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by o.date_purchased";
} else {
 $taxed_query_raw .= " group by month(o.date_purchased)";
};

 

needs to be:

 

	if ($sel_month<>0) {
 $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by dayofmonth(o.date_purchased)";
} else {
 $taxed_query_raw .= " group by month(o.date_purchased)";
};

 

The reason is the sql query would not sum if the date_purchased was in datetime format, because the times of the purchases would differ would differ.

 

I am not sure how this ever worked for anyone else but as I said, my code is heavily modified and perhaps entries in my database differ.

 

Sorry if this has been mentioned before. I did read through the thread and did not find any mention.

Link to comment
Share on other sites

  • 1 month later...
For my heavily modified MS2 code, the Monthly Sales & Tax Report 2.1.1 did not correctly calculate the taxed/non-taxed detail by day (i.e. report after clicking the month).  The reason is that:

 

	if ($sel_month<>0) {
 $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by o.date_purchased";
} else {
 $taxed_query_raw .= " group by month(o.date_purchased)";
};

 

needs to be:

 

	if ($sel_month<>0) {
 $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by dayofmonth(o.date_purchased)";
} else {
 $taxed_query_raw .= " group by month(o.date_purchased)";
};

 

The reason is the sql query would not sum if the date_purchased was in datetime format, because the times of the purchases would differ would differ.

 

I am not sure how this ever worked for anyone else but as I said, my code is heavily modified and perhaps entries in my database differ.

 

Sorry if this has been mentioned before.  I did read through the thread and did not find any mention.

 

 

Thank you for your post.

I had the same problem with 3 orders made on the same day with the same product in 3 different currencies. The year calculation was correct, but for one month not.

For year:

Gross income: 2,530.50

Product sales: 2,499.00

Exempt sales: 0.00

Taxable sales: 2,499.00

Tax paid: 399.00

Shpg & Hndlg: 32.50

 

For month:

Gross income: 2,530.50

Product sales: 2,499.00

Exempt sales: 1,666.00

Taxable sales: 833.00

Tax paid: 399.00

Shpg & Hndlg: 32.50

 

After correcting the code, report is working O.K. for the TAX, but there is still a little flaw:

The last column collects all classes other then ot_subtotal, ot_tax, ot_shipping, ot_loworderfee and ot_total. If somebody is using extra classes with mixed values (e.g. discounts and surcharges) in the price calculation, then all of them will be added together, which is not correct. Maybe another challenge for the author - Fritz Clapp.

Many complements for his great work!!!!

Your repair should be incorporated into the source of this contribution.

 

Many thanks,

 

Eryk

Link to comment
Share on other sites

  • 3 months later...
The last column collects all classes other then ot_subtotal, ot_tax, ot_shipping, ot_loworderfee and ot_total. If somebody is using extra classes with mixed values (e.g. discounts and surcharges) in the price calculation, then all of them will be added together, which is not correct. Maybe another challenge for the author -

 

I agree this is a flaw, but should be easily fixed. Luckily in my installation I only have one extra class for now so it works for me.

 

There is yet another flaw in the report. The "Taxed Sales" is a total of subtotals that have tax. When a single order contains both non-taxed and taxed items, "Taxed Sales" will include the non-taxed items, because the subtotal includes the non-taxed items.

 

Unfortunately, there are not separate totals for taxed and non-taxed items in the orders_totals table.

 

For my installation, I changed the code to resum the orders rather than use the subtotals in orders_totals. This works for my installation but may not work in other installations where other manipulations of subtotals occur during order processing (i.e. discounting, surcharges, etc).

 

Here is the code:

 

// changed code to sum order for tax rather than use orders_totals
// necessary when order contains both taxed and non-taxed items
//
// sum of order products taxed
$taxed_query_raw = "select sum(round(op.final_price*op.products_quantity,2)) taxed_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " .  TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id)";
$taxed_query_raw .= " where op.products_tax!=0 and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
if ($status<>'') $taxed_query_raw .= " and o.orders_status ='" . $status . "'";
if ($sel_month<>0) {
 $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by dayofmonth(o.date_purchased)";
} else {
 $taxed_query_raw .= " group by month(o.date_purchased)";
};
$taxed_query = tep_db_query($taxed_query_raw);
$taxed_this_row = tep_db_fetch_array($taxed_query);
//
// subtract "other" row from taxed sales
// "other" is a global order discount, but to minimize taxes
//      we assume that the discount comes from taxable sales first.
$taxed_this_row['taxed_sales'] += $other_this_row['other'];

Link to comment
Share on other sites

  • 8 months later...

Is still thread still alive?

For me this cont. is calculating taxes wrong. I have 22% sales tax and in shop i have option on that prices are shown with taxes.

Summary lines says: gross sales 136,42 this is ok but it says taxes are 22,18 as it should be 24,59 (136,42*0,1803) As iam new in php i cant figure out where are these calculation made. CAn you help me out?

i have latest 2.2 version

Link to comment
Share on other sites

  • 6 months later...

I think I've got the CSV working over SSL -

 

At the very top of the report (has to go before session_start, so it has to go even before the include of application_top):

ini_set ('session.cache_limiter', 'private, must-revalidate');

 

And in the following section:

 

  if (strlen($csv_string)>0){
 header("Expires: Mon, 26 Nov 1962 00:00:00 GMT");
 header("Last-Modified: " . gmdate('D,d M Y H:i:s') . ' GMT');
 header("Cache-Control: no-cache, must-revalidate");
 header("Pragma: no-cache");
 header("Content-Type: Application/octet-stream");
 header("Content-Disposition: attachment; filename=$savename");
 echo $csv_string;
 }

 

Delete the two lines:

 

  header("Cache-Control: no-cache, must-revalidate");
 header("Pragma: no-cache");

 

The php line takes care of this for you, and if they're both there it causes a conflict.

Link to comment
Share on other sites

Oh, I should add, for the sake of Google, that the above code changes will fix it when, in IE (over an https connection), you try to download the CSV file and get the following error:

 

"Internet Explorer cannot download stats_monthly_sales.php from <your site here>

 

Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot e found. Please try again later."

Link to comment
Share on other sites

I'm curious about how the calculations are done. In my shop we sell items in USD and CAD - are the totals being calculated by just adding up total sales regardless of the currency, or are they converted into the store's default currency when/before they get tallied?

 

Thanks.

Link to comment
Share on other sites

  • 2 weeks later...
  • 6 months later...

First of all, thanks for a great contribution.

 

Second, I got some problem :)

I use the COD module(Cash on Delivery) which charges every COD order with $5.

I also have Easy Discount which gives 10% discount on all orders.

 

The problem is that COD shows as a discount in Gift Vouchers in Monthly sales & Tax, but the correct place would be in Tax and Handling charges.

 

Anyone having the same problems or a solution to it?

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