234 replies to this topic

[blueline]

CatDadRick-

Well? How do you like it?

[CatDadRick]

BlueLine,

For some reason I'm not getting proper notification of post here so I've missed most of them until just now.

Everything you've done sounds wonderful. I'll download the new version today and give it a run.

Thanks,
CatDad

[CatDadRick]

BlueLine,

Great contrib. I love it. And I can't think of anything else to add.

Thanks again,
CatDad

P.S. Don't go away, I think you have a lot to contribute to osCommerce.

[blueline]

Why would I go away?

[chaicka]

blueline,

No offense, great contribution but your installation instructions are having a few errors which I have fixed in a previous upload.

Here are the errors based on the new v2.51 file:

Page 2 of 7 - Wrongly inputting the products_cost value to the wrong value in mySQL table as the statement is wrong.

Error Is
<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '&nbsp;' . tep_draw_input_field('products_price', $pInfo->products_cost, ''); ?></td>

Should Be
<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . '&nbsp;' . tep_draw_input_field('products_cost', $pInfo->products_cost, ''); ?></td>


Page 6 of 7 - In your files, there is reference to TEXT_PRODUCTS_COST_INFO but your installation instructions never add that in. Furthermore, the definition of TEXT_PRODUCTS_PRICE_COST should be in line with the rest of osC 2.2 MS2 definitions.

Error Is
/includes/languages/english/categories.php
Add:
define('TEXT_PRODUCTS_PRICE_COST', 'Cost: ');
define('TEXT_PRODUCTS_PROFIT_INFO', 'Profit:');

Should Be
Add:
define('TEXT_PRODUCTS_COST_INFO', 'Cost: ');
define('TEXT_PRODUCTS_PROFIT_INFO', 'Profit:');

define('TEXT_PRODUCTS_PRICE_COST', 'Products Price (Cost):');

[blueline]

Thanks. Fixed and uploaded changes.

[chaicka]

Error in Margin_Report2.php whereby the export button after the date selection is not showing up in browser.

Removed the DIR_FS_Admin statement to make it the same as the <Back><Export> statement and it works fine now.

[mijman2]

It would be wonderful if you could release a version of this with all of the files edited from a clean version of oscommerce... It would be much easier for... New people (clean start) who want to use this, and for using comparing progams...

[dcsteve72]

Got 2.55 installed.  Tried executing an export and was given file download errors.

Put in the filename, click export.  Dialog box appears with no filename or type, but does have the domain name.  Choosing save (or open for that matter), generates
"Cannot open file from site blah.com.  Internet Explorer is unable to open this internet site.  The requested site is unavailable or cannot be found."

Any thoughts?

[vaughnn]

Folks,

This contribution is fantastic, but has one flaw for historical info which needs some time investigating.
The cost of items is extracted from the products table when doing historical reports.  If like me your items change in price regularly, or you buy the same item from multiple suppliers, the history reports over time become les and less accurate.

I know that to fix this will need some changes to the orders_products table, to the checkout process, so the item cost is saved into the OP table on checkout.
I can look at this, but make no promises, as have many other things on the go.

Cheers,

VB

[vaughnn]

OK, had a quick look and think the following is all that is required to make it work.
You will need to make some changes to the margin report files, but these are changes to the standard files to allow saving of costs for each order.  I hope these changes are ALL required (they at least worked for me).

make the following database change;

ALTER TABLE `orders_products` ADD `products_cost` DECIMAL( 15, 4 ) DEFAULT '0.0000' NOT NULL AFTER `products_price`;

make the following changes to code;

includes/classes/order.php

FIND
      $orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'");
      while ($orders_products = tep_db_fetch_array($orders_products_query)) {
        $this->products[$index] = array('qty' => $orders_products['products_quantity'],
                                 'id' => $orders_products['products_id'],
                                        'name' => $orders_products['products_name'],
                                        'model' => $orders_products['products_model'],
                                        'tax' => $orders_products['products_tax'],
                                        'price' => $orders_products['products_price'],
                                        'final_price' => $orders_products['final_price'],

REPLACE WITH
      $orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'");
      while ($orders_products = tep_db_fetch_array($orders_products_query)) {
        $this->products[$index] = array('qty' => $orders_products['products_quantity'],
                                 'id' => $orders_products['products_id'],
                                        'name' => $orders_products['products_name'],
                                        'model' => $orders_products['products_model'],
                                        'tax' => $orders_products['products_tax'],
                                        'price' => $orders_products['products_price'],
                                        'cost' => $orders_products['products_cost'],
                                        'final_price' => $orders_products['final_price'],
FIND
// products price
        $product_query = tep_db_query("select products_id, products_price, products_tax_class_id, products_weight from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");
        if ($product = tep_db_fetch_array($product_query)) {
          $prid = $product['products_id'];
          $products_tax = tep_get_tax_rate($product['products_tax_class_id']);
          $products_price = $product['products_price'];
          $products_weight = $product['products_weight'];

REPLACE WITH
// products price
        $product_query = tep_db_query("select products_id, products_price, products_tax_class_id, products_weight from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");
        if ($product = tep_db_fetch_array($product_query)) {
          $prid = $product['products_id'];
          $products_tax = tep_get_tax_rate($product['products_tax_class_id']);
          $products_price = $product['products_price'];
          $products_cost = $product['products_cost'];
          $products_weight = $product['products_weight'];




FIND
                                        'price' => $products[$i]['price'],
                                        'final_price' => $products[$i]['price'] + $cart->attributes_price($products[$i]['id']),

REPLACE WITH
                                        'price' => $products[$i]['price'],
                                        'cost' => $products[$i]['cost'],
                                        'final_price' => $products[$i]['price'] + $cart->attributes_price($products[$i]['id']),


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


FIND
          $products_array[] = array('id' => $products_id,
                                    'name' => $products['products_name'],
                                    'model' => $products['products_model'],
                                    'image' => $products['products_image'],
                                    'price' => $products_price,

REPLACE WITH
          $products_array[] = array('id' => $products_id,
                                    'name' => $products['products_name'],
                                    'model' => $products['products_model'],
                                    'image' => $products['products_image'],
                                    'price' => $products_price,
                                    'cost' => $products['products_cost'],



checkout_process.php

FIND
    $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'], 
                            'final_price' => $order->products[$i]['final_price'], 
                            'products_tax' => $order->products[$i]['tax'], 
                            'products_quantity' => $order->products[$i]['qty']);

REPLACE WITH
    $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']);

Cheers,

VB

[urbach]

Ive installed the version 2.25 and all is well. For some reason the cost prices are not showing up in the margin report. They have all been enter in via MySql database. The Margin (%) is showing up. Any suggestion for this problem?

Thanks
Steven

[chaicka]

v2.55a is great....but like vaughnn has mentioned, it has some bugs in it on how it lookup the product price.

Furthermore, I have just discovered that my latest recent order is not being reported correctly too.

Example:

Order #1002

Item A has order quantity of 90, but the margin report is only reporting order quantity of 1 which gave the wrong report on sales total and gross profit as a result.

Any guru to help fix this and release a new version?

[chaicka]

Anyone interested to team up and work on resolving existing issues with this contribution? I am not very good with programming, thus I need help.

Can someone help me with the following questions?

Based on v2.55a:

1. Is it looking up the table 'products'? If so, what are the values that is looking up for, products_price, products_cost, etc?

2. Majority of the problem is with margin_report2.php right?

3. Command used in margin_report2.php is not osC MS2-2.2 standard commands. Is there anyway to convert to using osC standard commands, so as to make it more compatible with the osC itself as well as easier to comprehend? Eg. mysql_query can be replaced with tep_db_query?

Let's identify all existing problem first, then we sort out solutions for it.

[urbach]

I have some one I use in India which does great programming using OSC. He dosent charge much at all. Heres his email if you want to contact him in doing this job. kalyanjit@hotmail.com

Steven

[blueline]

I am the author of this contribution. I can fix the issues at hand if all are identified.

-Chris

[urbach]

Hi Chris

What do u mean "I can fix the issues at hand if all are identified" ? Do u need access to our admin and MySql?

Steven

[vaughnn]

Well my changes above should fix the matter of recording the cost price at sales time, and all that is left to fix after that should be a couple of minor changes to margin_report2 to give the correct information.

Unfortunately if you have not got the cost history, margin_report2 will need to extract the info from current pricing costs, but these are relatively minor changes in margin_report2.

Chris, I also had the issue of the qty not being used for calculating the total cost, and made some changes to the module.  I have included an extract from my margin_report2 code, which fixed my qty issue.  (Unfortunately I use a combination of oscommerce and phpnuke on my website [oscNuke], so when I add contributions I have to amend things to get them to work).

My changes are as follows;

for($i=0;$i<sizeof($o);$i++) {
$price = 0;
$cost = 0;
$items_sold = 0;
$prods_query = mysql_query("select products_id, products_price, products_cost, products_quantity from orders_products where orders_id = '" . $o[$i] . "'");
while ($prods = mysql_fetch_array($prods_query, MYSQL_ASSOC)) {
$p[] = array($prods['products_id'], $prods['products_price'], $prods['products_cost'], $prods['products_quantity']);
$price = $price + ($prods['products_price'] * $prods['products_quantity']);
$cost = $cost + ($prods['products_cost'] * $prods['products_quantity']);
$items_sold = $items_sold + $prods['products_quantity'];

$total_price = $total_price + ($prods['products_price'] * $prods['products_quantity']);
$total_cost = $total_cost + ($prods['products_cost'] * $prods['products_quantity']);
$total_items_sold = $total_items_sold + $prods['products_quantity'];
}

To fix the issue with the product cost issue on item sales where you did not save the cost price to the order_products table, I would do something like the following;
REPLACE
$cost = $cost + ($prods['products_cost'] * $prods['products_quantity']);
WITH

if (!$prods['products_cost'] > 0) {
  $cost_query = mysql_query("select products_cost from products where products_id = '" . $prods['products_id'] . "'");
  $cost_result = mysql_fetch_array($cost_query, MYSQL_ASSOC);
  $cost = $cost + ($cost['products_cost'] * $prods['products_quantity']);
} else {
  $cost = $cost + ($prods['products_cost'] * $prods['products_quantity']);
}

I am a bit of a hack (and haven't tested this last change), but most things I have done work OK.

Cheers folks,

Vaughnn
www.theITcrew.com.au

[tlelliott77]

Just looking at the install instructions I think there may be a line missing on page 4 where it says:  

Replace With: 
         } elseif ($HTTP_POST_VARS['copy_as'] == 'duplicate') {
 $product_query = tep_db_query("select products_quantity, products_model, products_image, products_price, products_cost, products_date_available, products_weight, products_tax_class_id, manufacturers_id from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'"); $product = tep_db_fetch_array($product_query);
tep_db_query("insert into " . TABLE_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 ('" . 
tep_db_input($product['products_quantity']) . "', '" . 
tep_db_input($product['products_model']) . "', '" . 
tep_db_input($product['products_image']) . "', '" . 
tep_db_input($product['products_price']) . "', 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();

I think the line:

tep_db_input($product['products_price']) . "', now(), '" .

Should change to:

tep_db_input($product['products_price']) . "', '" . 
tep_db_input($product['products_cost']) . "', now(), '" .

Please someone correct me if I'm wrong.

Great contribution by the way.  My only minor gripe is having the instructions in PDF.  Makes it a pain in the ...... to copy into text files.  Line breaks seem to get all screwed up.

Cheers
Tim

[tlelliott77]

Oh, I see later on in the instructions you have the same editing but with the extra change I mentioned in my last post.  Might be an idea to clean that up on the next release.  As far as I can see there are 3 duplicate instructions for categories.php.

Cheers
Tim