Jump to content
blueline

Margin Report v2.10

Recommended Posts

Sorry if this is cross posted from main category, got confused with new forum layout. Been away a while.

 

Fresh install of 2.2, fresh upload of latest Margin Report. If db managed with phpMyAdmin 2.2.3 no problem. If managed with 2.9.1 following error occurs:

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(*) as total from products p, products_to_categories p2c, categories c, products_description pd left join specials s on p.products_id = s.products_id where pd.language_id = '1' and p.products_id = pd.products_id and p2c.products_id = p.products_id and c.categories_id = p2c.categories_id

 

 

 

Tks

 

Oh. Always was a great contrib. Now may be better.


"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."

B. Franklin

Share this post


Link to post
Share on other sites

Just wanted to say that this contribution is GREAT. It is one of those that should come by default with OSC, and that once you have it installed and start using it, you will wonder how you lived so much time without the information it gives you...

 

I only have one minor issue with it when trying to copy a product from the admin side, I get a SQL error, but that is probably that I made some mistake when integrating it, since I also have SPPC and QPB, I have to revise it.

 

The other thing that bothers me is that when seeing the margin report, the items you have duplicate (same item on more than one category) will also be showing up as duplicates. No big deal for me since I have very few duplicate items, but it can be a headache for those that have many duplicates.


MindTwist of Twisted Reality and Twisted Tienda

Share this post


Link to post
Share on other sites

Hello there

 

Nice system you guys made here.

 

But i was wondering about a little thing.

Is it possible to get this cost price to * with some special currency. The "problem" is that i order items from china and they are in $. But we are using NOK here in norway. Then it had been very easy for people like me that don't only use $'s. to just type in the dollar price and get it over to NOK in cost price.

Since now i have to get it over to NOK manual. Is this possible to do, with out to much work for you guys.

 

Hope someone understand what i mean :)

 

Br

Aukland

Share this post


Link to post
Share on other sites
But i was wondering about a little thing.

Is it possible to get this cost price to * with some special currency. The "problem" is that i order items from china and they are in $. But we are using NOK here in norway. Then it had been very easy for people like me that don't only use $'s. to just type in the dollar price and get it over to NOK in cost price.

Since now i have to get it over to NOK manual. Is this possible to do, with out to much work for you guys.

 

I understand what you mean, but I see no point on entering your costs in US$ (or CNY, or whatever you are peying your pourchases with).

Once you pay your purchases, you know what exchange you have paid for whatever you purchased in China, so you know your cost in NOK. That is the cost you should be using, since you do not want exchange rates to be constantly changing your cost.


MindTwist of Twisted Reality and Twisted Tienda

Share this post


Link to post
Share on other sites

I have successfully installed this mod on my website, and I love it, but I have a couple issues:

 

1) I have updated the cost fields, but when I do a query for say weekly, it shows the net cost of items sold, but it does not add up the cost of items sold?

 

2)when I try to search for last month or last week it give me all of the transactions for last year. Is their a fix that I am missing?

 

Thanks

 

Evan

Edited by dsolutions

Share this post


Link to post
Share on other sites

As I look more into it, it looks like the problem might be with margin_report2.php instead of margin_report.php.

 

When I pull up margin_report.php I see the actual cost but when I go to say, weekly, or any of the others it does not pull in the "cost" column. Also, the math is not functioning for any of the different timelines.

 

I have gone over and over the instructions. Please help me :)

 

Best Regards,

 

Evan

Share this post


Link to post
Share on other sites

I have a similar problem as You dsolutions. Everything works perfectly except from just one thing.

When I select to view a report from "Yesterday" it shows all orders from the very first day of the

shop. Every other report seems to work, but not Yesterday. Anyone have a clue?

 

Br,

 

Clas

Share this post


Link to post
Share on other sites

Hi All, I think this Margin Report is a great module and it's just what I've been looking for to included in my OSC Shop. It sort-of half works. I can view the reports and obtain data as I should but I have a bad error message when I try to edit/update my Catalog/Product in Admin. the message reads; Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.../includes/functions/database.php on line 99. The shop itself still appear to be functioning fine, so I know I'm almost there. If anyone has any ideas as to what this error means and how I fix it, I would appreciate it. My experience in php is limited, so the simpler the explanation the better. Thanks again

Share this post


Link to post
Share on other sites

I've just uploaded new version of this great contrib.

 

I've included the following major functions:

 

- per order percentage margin

- summary percentage margin

- filtering based on order status

 

I think that it is real important to see margin per order as there might be a combination of products or conditions (vouchers, delivery charges) that can cause you loss. Also, the product status filter - if we run reports, we are not interested in canceled or test orders are we? Thus the filter.

 

If you decide to improve or amend anything, please use this latest version to make sure that you work with the latest, fixed report files. I hope that I've not missed anything, if I did, I'm sorry but I have very limited time to do any coding or contribution improvements.

 

If you're upgrading, you should be OK with replacing files, but as always backup and compare.

 

Download: Margin Report v_3.00

 

Enjoy! :thumbsup:

Edited by mr_absinthe

Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites
I've just uploaded new version of this great contrib.

 

I've included the following major functions:

 

- per order percentage margin

- summary percentage margin

- filtering based on order status

 

I think that it is real important to see margin per order as there might be a combination of products or conditions (vouchers, delivery charges) that can cause you loss. Also, the product status filter - if we run reports, we are not interested in canceled or test orders are we? Thus the filter.

 

If you decide to improve or amend anything, please use this latest version to make sure that you work with the latest, fixed report files. I hope that I've not missed anything, if I did, I'm sorry but I have very limited time to do any coding or contribution improvements.

 

If you're upgrading, you should be OK with replacing files, but as always backup and compare.

 

Download: Margin Report v_3.00

 

Enjoy! :thumbsup:

 

It is possible to make the order number clickable to link to the order details directly?


Best regards,

Koh Kho King

Share this post


Link to post
Share on other sites

By the way, I think margin% should be total profit divided by total cost, not total price.

 

Thanks.


Best regards,

Koh Kho King

Share this post


Link to post
Share on other sites

It depends if you want to calculate your margin from the top or from the bottom, since it is common business practice in the US and Europe to do it from the top, since it was done in this contribution originally in margin_report.php and last but not least since it corresponds with totals - thus the formula the way it is. Yes, it is also possible to make the order number clickable but as I've already mentioned - no time, so its up to you to. ;)

I would personally think of other options such us a bubble icon that would display the products of that particular order on mouseover. :-"


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

Dear mr_absinthe,

 

Thank you very much for your update of the contribution! Really appreciate it!

 

My knowledge in PHP is near to ZERO, I have spent a lot of time in trying to modify the codes without success. Your 5 mins work can be my forever but still unsuccess work.

 

Would you be kind enough to add the "bubble icon" mouse over to show order details to the order number? I have this in my Customer Order, would really be helpful if you can add it to the margin report.

 

Many thanks!


Best regards,

Koh Kho King

Share this post


Link to post
Share on other sites

Hi

 

I have just installed this Margin_report_v3.00

 

and have 2 Q:

 

1) I have Quantity Price Break, price break per discount category installed and in Margin_report_v3.00 Install.txt it has this:

 

/catalog/includes/classes/shopping_cart.php

 

Find:

	 while (list($products_id, ) = each($this->contents)) {
	$products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

Replace With:

while (list($products_id, ) = each($this->contents)) {
   $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_cost, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

but Quantity Price Break, price break per discount category has:

 

Find (around line 325-334):

$products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
	 if ($products = tep_db_fetch_array($products_query)) {
	   $prid = $products['products_id'];
	   $products_price = $products['products_price'];

	   $specials_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = '" . (int)$prid . "' and status = '1'");
	   if (tep_db_num_rows($specials_query)) {
		 $specials = tep_db_fetch_array($specials_query);
		 $products_price = $specials['specials_new_products_price'];
	   }

 

Replace with:

if ($products = $pf->loadProduct($products_id, $languages_id)) {
	   $products_price = $pf->computePrice($this->contents[$products_id]['qty']);

 

so i can not do this should this be ok? as I can not see as 2) below.

 

 

2) I am getting this erorr when I go to Admin > reports > Margin Report

 

Fatal error: Cannot redeclare class productListingBox in /catalog/admin/includes/classes/box.php on line 227

 

but there is not a line 227 ?


Thanks Simon

 

osCommerce Version: Online Merchant v2.2 RC 2a - Test Site with: oscommerce-2.2ms2-060817

cPanel Version: 11.23.4-RELEASE - Apache version: 2.0.63 - PHP version: 5.2.5 - MySQL version: 5.0.51a-community - Operating system: Linux

========================================================================

Share this post


Link to post
Share on other sites
Hi

 

I have just installed this Margin_report_v3.00

 

and have 2 Q:

 

1) I have Quantity Price Break, price break per discount category installed and in Margin_report_v3.00 Install.txt it has this:

 

/catalog/includes/classes/shopping_cart.php

 

Find:

	 while (list($products_id, ) = each($this->contents)) {
	 $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

Replace With:

while (list($products_id, ) = each($this->contents)) {
	$products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_cost, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

but Quantity Price Break, price break per discount category has:

 

Find (around line 325-334):

$products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
	  if ($products = tep_db_fetch_array($products_query)) {
		$prid = $products['products_id'];
		$products_price = $products['products_price'];

		$specials_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = '" . (int)$prid . "' and status = '1'");
		if (tep_db_num_rows($specials_query)) {
		  $specials = tep_db_fetch_array($specials_query);
		  $products_price = $specials['specials_new_products_price'];
		}

 

Replace with:

if ($products = $pf->loadProduct($products_id, $languages_id)) {
		$products_price = $pf->computePrice($this->contents[$products_id]['qty']);

 

so i can not do this should this be ok? as I can not see as 2) below.

 

 

2) I am getting this erorr when I go to Admin > reports > Margin Report

 

Fatal error: Cannot redeclare class productListingBox in /catalog/admin/includes/classes/box.php on line 227

 

but there is not a line 227 ?

 

 

Hi

 

odd if I change margin_report.php to margin_report2.php all works?


Thanks Simon

 

osCommerce Version: Online Merchant v2.2 RC 2a - Test Site with: oscommerce-2.2ms2-060817

cPanel Version: 11.23.4-RELEASE - Apache version: 2.0.63 - PHP version: 5.2.5 - MySQL version: 5.0.51a-community - Operating system: Linux

========================================================================

Share this post


Link to post
Share on other sites

Hello I have been using this report also.

 

I also added last year to the report (If anyone also wants that, let me know), but I've got a an issue.

 

When I check the margin, it isn't always correct.

 

It looks like when 2 items are in an order this happends, I've tested this on a virgin installed Oscommerce with Margin report.

 

Added the DvD there is something about Mary (10 euro costst) and You've got mail (12 euro costs).

 

Here is the result:

Order ID 1

Items Sold 2

Sales Amount 84.98EUR

Cost Gross 22.00EUR

profit 62.98EUR

Margin (%) 66%

 

When you calculate the margin manually: (Sales Amount-Cost Gross)/Sales Amount it comes to 74% and not 66%

 

Anybody any idea?

Share this post


Link to post
Share on other sites

Damn found another one!

 

When you choose today, no data shows! Even though somebody bought something today.

 

Checked the query and it gives the correct date: SELECT curdate() as time

 

When you look at the yesterday query it gives SELECT DATE_SUB(curdate(), INTERVAL 1 DAY) as time which also gives the correct date.

 

Anybody any idea?

 

Greets

Share this post


Link to post
Share on other sites

Hi mr_absinthe,

 

Thank you for this contribe it's that I need but mine doesn't work !!!

 

I installed the release v3.00a and I have problem with margin_report2.php.

 

 

When I select a report (exemple: yesterday) I have this message:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 192

 

 

Here are the lines 191-193 of the file margin_report2.php:

 

$o = array();

while ($order = mysql_fetch_array($order_query, MYSQL_ASSOC)) {

$o[] = $order['orders_id'];

 

 

And when I click on the button "export", the excel table is empty and I have this error message:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 192

 

Warning: Cannot modify header information - headers already sent by (output started at /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php:192) in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 285

 

Warning: Cannot modify header information - headers already sent by (output started at /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php:192) in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 286

 

Warning: Cannot modify header information - headers already sent by (output started at /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php:192) in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 287

 

Warning: Cannot modify header information - headers already sent by (output started at /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php:192) in /home.10.9/infinicl/www/clients/sygma/matteo/margin_report2.php on line 288

 

 

I hope somebody could help me.

Thank you.

 

Guillaume.

Share this post


Link to post
Share on other sites

I seemed to have installed Margin Report v3.00a correctly since I am not getting any errors.

 

When the try the following...

 

(Admin->Reports->Margin Report->Show)

 

I am only seeing data for the weekly option. The rest of the options do not display any data.

 

Any ideas on where I went wrong?

 

Margin Report Home

marginreporthomekv6.jpg

 

Margin Report Daily (Everything other than Weekly shows something like this.)

marginreportdailyzh1.jpg

 

Margin Report Weekly

marginreportweeklysn7.jpg

Share this post


Link to post
Share on other sites

Hi,

 

I just installed Margin Report 3.0a

and i got exactly the same problem as listed before

 

When i select the option "Today", "Yesterday", the report empty.

I allready worked around with the mysql query.. but i didn't find any probing clue

 

anybody ?


Less is more...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×