Jump to content
blueline

Margin Report v2.10

Recommended Posts

Hi,

 

Great job! Loved this contribution!

 

I've translated the report file to PT-BR, would you like to add to the contrib?

 

Regards,

 

Junior.

Share this post


Link to post
Share on other sites

Hi,

 

found some clue for this issue.

 

the query sent for Today is :

 

select orders_id from orders where date_purchased > '2008-07-17' and date_purchased < '2008-07-17' order by orders_id asc

 

and Yesterday :

 

select orders_id from orders where date_purchased > '2008-07-16' and date_purchased < '2008-07-17' order by orders_id asc

 

 

so i am working on.

 

 

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 ?

Share this post


Link to post
Share on other sites

To fix the Daily report returning no orders:

 

In margin_report2.php look for

 

$d = mysql_fetch_array($date, MYSQL_ASSOC);

 

in the 'daily' secion of the switch around line 41.

 

Add after this:

 

$d2 = 'now()';

 

HTH, Scott

Share this post


Link to post
Share on other sites

I have re-installed 2.56 and still get

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #29' at line 1

 

Resource id #29

 

[TEP STOP]

 

when I select a Manufacture and then do a All Catagories by Manufacture. Otherwise the other reports work.

Did anyone actually reply to this at all? I see it posted twice. If so, I cannot find it in the thread. I have installed Margin Report and have exactly the same problem. Weird thing is that if you select a product from the All Categories by Manufacturer drop down menu the export works fine for the product. When you then reselect All Categories by Manufacturer and select export it works! It appears to be only the first time you go there from the main margin reports page that it does not get the sql right and throws the error. Something not being passed somewhere on initial call?

 

Also, I noted refrerence to a fix for the quantity issue that is supposed to be in the thread. Have just gone though and cannot see it. Have I missed it?

 

Thanks

 

Peter

 

If anyone interested...

I also had this problem but only when I wanted to export to Excel when I choosed only the manufacturer and I fixed it like that.

Maybe someone can change this in future versions?

 

/catalog/admin/margin_report.php    (from v 3.00 2008/03/15)

Find:
if (isset($_GET['manufacturers_id']) && ($_GET['manufacturers_id'] != '') && (!isset($_GET['filter_id']))) {
  $manufacturer_query = tep_db_query("select manufacturers_name as name from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . $_GET['manufacturers_id'] . "'");
  $manufacturer = [color="#FF0000"]tep_db_query[/color]($manufacturer_query);

Replace with:
if (isset($_GET['manufacturers_id']) && ($_GET['manufacturers_id'] != '') && (!isset($_GET['filter_id']))) {
  $manufacturer_query = tep_db_query("select manufacturers_name as name from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . $_GET['manufacturers_id'] . "'");
  $manufacturer = [color="#2E8B57"]tep_db_fetch_array[/color]($manufacturer_query);

Share this post


Link to post
Share on other sites

I added this contrib no problem and I though all was well but now when I go to make a copy of a product I get this error:

 

1136 - Column count doesn't match value count at row 1

 

insert into products (products_quantity, products_model,products_image, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('2', 'Brabus#3', 'brabus3-2.jpg', 'brabus3-1.jpg', 'brabusgroupshot2.jpg', '', '', '', '', '5180.0000', now(), null, '150', '0', '0', '43')

 

[TEP STOP]

 

I'm 99% sure it's from this contrib because it was the last one I did in the last month.... I went over most of this contrib and I can't see what I did wrong. Does anyone have any ideas how I can fix this so I can create new products again by just clicking copy....

 

Thanks,

 

FK

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

 

Did you ever find a solution to this? I'm getting the same error.

Share this post


Link to post
Share on other sites

this report did not calculate the sales amount order correctly. Whenever there is an extra charges such as $2 for XXL, (SPPC installed) and item sales $20 + $2 = $22... but it only show out as $20. Any solution for this ??? :blush:

Share this post


Link to post
Share on other sites

can someone tell me how to change the font color on the margin_report.php. I want the header font to be white.

 

 

Thanks

Share this post


Link to post
Share on other sites

Hi,

 

Just installed the Margin Report 3.00a and it's really great piece of software. I like it. But I have some ideas to ekstra functions that would make it even better. But I'm not PHP programmer - but mayby someone can help!

 

1. Would be nice if "amout of product" would be exported til Excel as well - then it could be used to calculate complete stockvalue of all the parts in stock

2. To make an import bottom. The exported Excel file could be edited in excel with correct prices and how many in stock of each part - and after the update the excel file could be imported back to Oscommerce.

 

Could anyone do this add?? What would be the cost to make this update for the Margin Report 3.00a??

 

Regards

Lars

Edited by dinmc

Share this post


Link to post
Share on other sites

Is anyone doing any updates or adding features? I would but i not a PHP programmer to figure out all the database calls.

 

I would like to see added

Shipping costs ( entered per order after enter true shipping costs)

Total shipping

Shipping Margin

 

 

Also add options to get 1st, 2nd, 3rd, 4th quarter results.

 

Also output date of the order

 

 

I have looked into what variables are where, i just cant figure out how to program it in.

 

 

Bruce

Share this post


Link to post
Share on other sites
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?

 

I have the same issue. Has anybody a solution for this?

Share this post


Link to post
Share on other sites
I have the same issue. Has anybody a solution for this?

 

I found the solution for the incorrect margin with more products.

 

In the margin_report2.php change line 221 from $margin = tep_round((((($prods['products_price'])-($prods['products_cost']))/($prods['products_price']))*100), 0

 

to: $margin = tep_round(($price-$cost)/$price*100,2);

Share this post


Link to post
Share on other sites

this report did not calculate the sales amount order correctly. Whenever there is an extra charges such as $2 for XXL, (SPPC installed) and item sales $20 + $2 = $22... but it only show out as $20. Any solution for this ??? :blush:

 

 

Any solution found for this? Have the same problem!

 

Great add on by the way! Love it, does lots of things I needed but still some things which not work very well. So anyone still updating or is there an alternative for these functions?

Share this post


Link to post
Share on other sites

Is anyone doing any updates or adding features? I would but i not a PHP programmer to figure out all the database calls.

 

I would like to see added

Shipping costs ( entered per order after enter true shipping costs)

Total shipping

Shipping Margin

 

 

Also add options to get 1st, 2nd, 3rd, 4th quarter results.

 

Also output date of the order

 

 

I have looked into what variables are where, i just cant figure out how to program it in.

 

 

Bruce

 

 

No. It looks like there is no suppot for this nice contribution. I made proposal more than one year ago but newer got any reply.

Regards

Lars

Share this post


Link to post
Share on other sites

I get lots of:

 

Warning: Division by zero in /var/www/xxx.xx.xx/catalog/admin/margin_report2.php on line 222

 

Any ideas why??

 

Regards

Lars

Share this post


Link to post
Share on other sites

I found the solution for the incorrect margin with more products.

 

In the margin_report2.php change line 221 from $margin = tep_round((((($prods['products_price'])-($prods['products_cost']))/($prods['products_price']))*100), 0

 

to: $margin = tep_round(($price-$cost)/$price*100,2);

 

sjnelle is right. The way the code was written on a multi-item order the margin percentage reported for the whole order was actually the margin percentage for the last product on the order. To make things worse the old formula rounded off the last two digits of the calculated margin percentage. That caused a 47.51% margin to be reported as 48% and a 32.49% margin to be reported as 32%.

 

sjnelle's fix fixes the problem.

Share this post


Link to post
Share on other sites

Hurray! :D After days of carefully installing new versions of margin report and testing and finding bugs and fixing them and retesting until I got the code for each version working right, and then starting over again and applying the next set of updates, I've finally managed to get Margin Report working 100% right all the way through v3.0b as far as I can tell. It even records product cost into the order-products table when the sale takes place (like it was designed to do). It also calculates the correct margin percentage for each order, and accurately reports sales for today, yesterday, this week, last week, this month, last month, this quarter, this half year, year-to-date and "forever-to-date" (i.e. all orders) too. It does NOT however, attempt to handle price adjustments based on product attributes (size, color, etc.)

 

Not even Abysynth's v3.0 patches to the original v1.0 code were getting product costs recorded into the order-products (a.k.a. order-items) table correctly; but my cart does that too now. At this point, I think I could even add daily, weekly and monthly sales reports, cash sales reports, credit card sales reports, weekly, monthly, quarterly, semi-annual and annual sales AND gross profit reports and Cost of Goods Sold reports. Reports of sales, costs and margins by manufacturer / supplier / vendor and other reports are possible as well.

 

The fact is I found most of the answers to the bugs I identified right here in this forum thread. So, if I can do it at age 60, you can too, folks. Furthermore it's a worthwhile project to undertake; because in the process I've learned a lot about where the data resides and how the tables are structured and how the mysql queries work with PHP to make all that stuff work together.

 

So far, I've successfully installed the OSC product, designed, installed and tested my own custom OSC template that's used throughout the new site I'm building, installed, tested and successfully implemented Dynamenu, installed and tested Simple Category Descriptions, Enable/Disable Categories, Easy Populate and Margin Report.

 

At this moment I feel like I own the world. B) But tomorrow morning, there'll be a new mountain to climb. My next project is Orders Tracking. Then its Quick Books Interface, OllaCart and Ultimate SEO. Other than that, I've got basically nothing to do here... :'(

 

Wish me luck! :-"

Edited by websissy

Share this post


Link to post
Share on other sites

I get lots of:

 

Warning: Division by zero in /var/www/xxx.xx.xx/catalog/admin/margin_report2.php on line 222

 

Any ideas why??

 

Regards

Lars

This is a typical error that comes out when you try to divide a number by zero. I got rid of it by adding @ in front of the formula which makes php not report the warning:

 

Find

 

$margin = tep_round((((($prods['products_price'])-($prods['products_cost']))/($prods['products_price']))*100), 0);

 

replace with

 

$margin = @tep_round(($price-$cost)/$price*100,2);

 

That integrates the fix from the forum just above by sjnelle


You can kill the King, but you can't kill the King Riddim...

Share this post


Link to post
Share on other sites

To fix the Daily report returning no orders:

 

In margin_report2.php look for

 

$d = mysql_fetch_array($date, MYSQL_ASSOC);

 

in the 'daily' secion of the switch around line 41.

 

Add after this:

 

$d2 = 'now()';

 

HTH, Scott

 

I tried that but it didn't work for me and the daily reports were still not showing any results. After much head scratching I came up with this:

 

On line 42 of margin_report2.php

 

Find...

 

$d = mysql_fetch_array($date, MYSQL_ASSOC);

 

Add after...

 

$d2 = mysql_fetch_array('now()', MYSQL_ASSOC);

 

Now the daily reports seem to return a result.

Give it a try and see if it gets a result for you!


You can kill the King, but you can't kill the King Riddim...

Share this post


Link to post
Share on other sites

I tried that but it didn't work for me and the daily reports were still not showing any results. After much head scratching I came up with this:

 

On line 42 of margin_report2.php

 

Find...

 

$d = mysql_fetch_array($date, MYSQL_ASSOC);

 

Add after...

 

$d2 = mysql_fetch_array('now()', MYSQL_ASSOC);

 

Now the daily reports seem to return a result.

Give it a try and see if it gets a result for you!

 

Actually scrub that it returns errors (PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource)

 

Try this instead:

 

On line 42 of margin_report2.php

 

Find...

 

$d = mysql_fetch_array($date, MYSQL_ASSOC);

 

Add after...

 

$mydate = mysql_query("SELECT NOW() as time");
$d2 = mysql_fetch_array($mydate, MYSQL_ASSOC);

 

Now the daily reports seem to return a result.

Give it a try and see if it gets a result for you!


You can kill the King, but you can't kill the King Riddim...

Share this post


Link to post
Share on other sites

Hi, trying to install this, but cant get the most important part for me to work. In the table orders_products the products_cost is always 0.000.

I need this to get updated.

 

Installed Margin Report 3.00b but with SPPC installed i had to do something diferent.

The only big problem i had is this

ind:
    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 . "'");                

 

Cant find it, but this seems to be where it was before:

   $products_array = array();
     reset($this->contents);
     while (list($products_id, ) = each($this->contents)) {
// BOF QPBPP for SPPC
     $pf->loadProduct($products_id, $languages_id); // does query if necessary and adds to 
     // PriceFormatterStore or gets info from it next
  if ($products = $pfs->getPriceFormatterData($products_id)) {
      if (tep_not_null($this->contents[$products_id]['discount_categories_id'])) {
         $nof_items_in_cart_same_cat =  $discount_category_quantity[$this->contents[$products_id]['discount_categories_id']];
         $nof_other_items_in_cart_same_cat = $nof_items_in_cart_same_cat - $this->contents[$products_id]['qty'];
       } else {
         $nof_other_items_in_cart_same_cat = 0;
       }
         $products_price = $pf->computePrice($this->contents[$products_id]['qty'], $nof_other_items_in_cart_same_cat);
// EOF QPBPP for SPPC

 

Dos this have anything about the update to orders_products products_cost?

 

Can anyone point me in the right direction to get the product_cost to update in orders_products when someone make a order?

Share this post


Link to post
Share on other sites

Have read this post three times now, and was only when i was thinking about the one page checkout i got the solution.

 

Had to fix the missing p.products_cost, in priceformatter.php

 

 $sql = "select pd.products_name, p.products_model, p.products_image, p.products_id," .
  " p.manufacturers_id, p.products_price, p.products_cost, p.products_weight, p.products_quantity," .
  " p.products_qty_blocks as qtyBlocks, p.products_min_order_qty, p.products_tax_class_id," .
  " NULL as specials_new_products_price," .

 

But also had to add 'products_cost' => $order->products[$i]['cost'], to the checkout_process.php.

 

$sql_data_array = array('orders_id' => $insert_id, 
                           'products_id' => tep_get_prid($order->products[$i]['id']), 
                           'products_model' => $order->products[$i]['model'], 
                           'products_name' => $order->products[$i]['name'], 
                           'products_price' => $order->products[$i]['price'],
                        'products_cost' => $order->products[$i]['cost'], 							
                           'final_price' => $order->products[$i]['final_price'], 
                           'products_tax' => $order->products[$i]['tax'], 
                           'products_quantity' => $order->products[$i]['qty']);
   tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);
   $order_products_id = tep_db_insert_id();

Share this post


Link to post
Share on other sites

Hi!

I have a problem copy a product in the admin section after I installed Margin Report contribution. :'( I got this failure notice:

 

1136 - Column count doesn't match value count at row 1

 

insert into products (products_quantity, products_model,products_image, products_msrp, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('3', 'Rek. 189kr', 'Fudge daily mint balsam.jpg', '87.2000', '0.0000', now(), '', '23.00', '0', '1', '8')

 

[TEP STOP]

 

It seems to be products_msrp that is not shown as a value. When I take out the same info from the database, it looks like this:

 

/*!40000 ALTER TABLE `products` DISABLE KEYS */;
INSERT INTO `products` (`products_id`,`products_quantity`,`products_model`,`products_image`,`products_msrp`,`products_price`,`products_cost`,`products_date_added`,`products_last_modified`,`products_date_available`,`products_weight`,`products_status`,`products_tax_class_id`,`manufacturers_id`,`products_ordered`) 

 

(147,3,'Rek. 189kr','Fudge daily mint balsam.jpg','151.2000','87.2000','0.0000','2010-01-02 11:25:19','2011-01-16 16:43:40',NULL,'23.00',1,1,8,0)

 

Here you see that I have the correct values. Does anyone knows why this happen when I try to copy an existing product in the admin section? It does not hapen when I create a new one.

 

Please help, my budy that runs the economics reports really need this one :)

 

BR

Andreas

Edited by addeaz

Share this post


Link to post
Share on other sites

Hi!

I have a problem copy a product in the admin section after I installed Margin Report contribution. :'( I got this failure notice:

 

1136 - Column count doesn't match value count at row 1

 

insert into products (products_quantity, products_model,products_image, products_msrp, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('3', 'Rek. 189kr', 'Fudge daily mint balsam.jpg', '87.2000', '0.0000', now(), '', '23.00', '0', '1', '8')

 

[TEP STOP]

 

It seems to be products_msrp that is not shown as a value. When I take out the same info from the database, it looks like this:

 

/*!40000 ALTER TABLE `products` DISABLE KEYS */;
INSERT INTO `products` (`products_id`,`products_quantity`,`products_model`,`products_image`,`products_msrp`,`products_price`,`products_cost`,`products_date_added`,`products_last_modified`,`products_date_available`,`products_weight`,`products_status`,`products_tax_class_id`,`manufacturers_id`,`products_ordered`) 

 

(147,3,'Rek. 189kr','Fudge daily mint balsam.jpg','151.2000','87.2000','0.0000','2010-01-02 11:25:19','2011-01-16 16:43:40',NULL,'23.00',1,1,8,0)

 

Here you see that I have the correct values. Does anyone knows why this happen when I try to copy an existing product in the admin section? It does not hapen when I create a new one.

 

Please help, my budy that runs the economics reports really need this one :)

 

BR

Andreas

 

 

Hi, I solved the problem. Here is the code I changed in admin/categories.php:

   tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model, products_image, products_msrp, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "', '" . tep_db_input($product['products_cost']) . "', now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");
   $dup_products_id = tep_db_insert_id();

 

Changed it too

    tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model, products_image, products_msrp, products_price, products_cost, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_msrp']) . "', '" . tep_db_input($product['products_price']) . "', '" . tep_db_input($product['products_cost']) . "', now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");
   $dup_products_id = tep_db_insert_id();

 

BR Andreas

Edited by addeaz

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

×