Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Adding a Cost Field to categories.php


Cyperis

Recommended Posts

So Ive been messing around late tonight about how to save myself a little time when adding products to a catalog. My current process when having to add an item is to take the cost that I have (Not in osCommerce) and multiply it by the gross that I want in order to get the price point. There are already a few options out there in the addons section but I decided to go another route. Here is what I came up with:

 

Disclaimer: THIS SHOULD NOT BE DONE TO A LIVE SHOP, ONLY IMPLEMENT THIS IF YOU HAVE A TEST SITE - Dont come blaming me if you screw something up :P

 

Added the following with into the database:

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

 

I added this into the databse as well. Please note that I already have a configuration group set up seperate from the main Configuration page that I used in this example:

INSERT INTO `configuration` (`configuration_id`, `configuration_title`, `configuration_key`, `configuration_value`, `configuration_description`, `configuration_group_id`, `sort_order`, `last_modified`, `date_added`, `use_function`, `set_function`) VALUES
(NULL, 'Profit Percentage', 'PRODUCT_PRICE_COST_PERCENTAGE', '1.15', 'How to calculate profit example: 1.xx', 1986, 51, '2012-08-21 12:00:00', '2012-08-21 12:00:00', NULL, NULL),
(NULL, 'Number to use when finding the cost', 'PRODUCT_PRICE_COST_PERCENTAGE_REVERSE', '0.869565217391304', 'Amount to multiply to find the cost', 1986, 52, '2012-08-21 12:00:00', '2012-08-21 12:00:00', NULL, NULL),

In this example imagine you have something that costs $100 and using the profit of 15% you end up with $115. We also need to find the cost if all we have is the retail so I had to figure out how to calculate that. Long story short multiply the retail by 0.869565217391304 and you get $100...ish, it actually equals 99.99999999999996 but that doesn't matter. Moving on...

 

 

I added the second SQL lines into the database so that we can later come back and edit them via the admin section.

 

Now we move to the categories.php file. I did the following:

Find:

 if ($action == 'new_product') {
$parameters = array('products_name' => '',
				   'products_description' => '',

Add:

'products_cost' => '',

Below:

'products_price' => '',

 

Next find the $product_query = tep_db_query( located right below the array you just edited. Add p.products_cost, after p.products_price,

 

Now Find:

function updateGross() {
 var taxRate = getTaxRate();
 var grossValue = document.forms["new_product"].products_price.value;
 if (taxRate > 0) {
grossValue = grossValue * ((taxRate / 100) + 1);
 }
 document.forms["new_product"].products_price_gross.value = doRound(grossValue, 4);
}
function updateNet() {
 var taxRate = getTaxRate();
 var netValue = document.forms["new_product"].products_price_gross.value;
 if (taxRate > 0) {
netValue = netValue / ((taxRate / 100) + 1);
 }
 document.forms["new_product"].products_price.value = doRound(netValue, 4);
}

 

Replace with:

function updateGross() {
 var taxRate = getTaxRate();
 var costValue = document.forms["new_product"].products_cost.value;
 var costtaxValue = document.forms["new_product"].products_cost.value;
 if (taxRate > 0) {
costValue = costValue * (<?php echo PRODUCT_PRICE_COST_PERCENTAGE; ?>);
costtaxValue = costValue * ((taxRate / 100) + 1);
 }
 document.forms["new_product"].products_price.value = doRound(costValue, 2);
 document.forms["new_product"].products_price_gross.value = doRound(costtaxValue, 2);

}
function updateCost() {
 var taxRate = getTaxRate();
 var netValue = document.forms["new_product"].products_price.value;
var costtaxValue = document.forms["new_product"].products_price.value;
 if (taxRate > 0) {
netValue = netValue * (<?php echo PRODUCT_PRICE_COST_PERCENTAGE_REVERSE; ?>);
costtaxValue = costtaxValue * ((taxRate / 100) + 1);
 }
 document.forms["new_product"].products_cost.value = doRound(netValue, 2);
 document.forms["new_product"].products_price_gross.value = doRound(costtaxValue, 2);

}

 

And Lastly, Find:

	  <tr bgcolor="#ebebff">
		<td class="main"><?php echo TEXT_PRODUCTS_PRICE_NET; ?></td>
		<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_price', $pInfo->products_price, 'onkeyup="updateGross()"'); ?></td>
	  </tr>
	  <tr bgcolor="#ebebff">
		<td class="main"><?php echo TEXT_PRODUCTS_PRICE_GROSS; ?></td>
		<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_price_gross', $pInfo->products_price, 'onkeyup="updateNet()"'); ?></td>
	  </tr>

 

Replace with:

	  <tr bgcolor="#ebebff">
		<td class="main"><?php echo TEXT_PRODUCTS_PRICE_COST; ?></td>
		<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_cost', $pInfo->products_cost, 'onkeyup="updateGross()"'); ?></td>
	  </tr>
	  <tr bgcolor="#ebebff">
		<td class="main"><?php echo TEXT_PRODUCTS_PRICE_NET; ?></td>
		<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_price', $pInfo->products_price, 'onkeyup="updateCost()"'); ?></td>
	  </tr>
	  <tr bgcolor="#ebebff">
		<td class="main"><?php echo TEXT_PRODUCTS_PRICE_GROSS; ?></td>
		<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' ' . tep_draw_input_field('products_price_gross', $pInfo->products_price); ?></td>
	  </tr>

 

 

 

 

Doing this now allows you to, when creating an item, enter the cost, and the Net and Gross prices are calculated automatically. Remember that the percentage profit is an admin configurable option.

 

A few things I would like to do is:

  • Make the Gross field unable to be edited
  • Edit entire database to update every product to automatically figure the cost for the products that only have a Net price.
  • Somehow make it so when you edit the admin configuration for the profit it will adjust all prices? Dunno if that's even possible?

Any suggestions to this or comments in general are appreciated.

Link to comment
Share on other sites

A few things I would like to do is:

  • Edit entire database to update every product to automatically figure the cost for the products that only have a Net price.
  • Somehow make it so when you edit the admin configuration for the profit it will adjust all prices? Dunno if that's even possible?

 

So Ive managed to be able to do the first part of what I quoted above...I am able to update my database to calculate the cost on items that are already in there using the following mySQL command:

update products set products_cost=(products_price*0.869565217391304) where products_cost=0;

 

However I dont know how to achieve the second part. I have a custom admin page where I can add the configuration value for the multiplier amount. Everything except the query to update I can do. I managed to get the following code together (untested) but where the above mentioned code I could do because all the products_costs's were 0 by default, I dont know how to make this query do every product. Would I have to use a 'while' command in order to loop it or what? Here is what I have sofar:

tep_db_query("update products set products_price = '(products_cost*'" . tep_db_input($configuration_value) . "') where ??????'");

 

Any suggestions?

Link to comment
Share on other sites

So Ive managed to be able to do the first part of what I quoted above...I am able to update my database to calculate the cost on items that are already in there using the following mySQL command:

update products set products_cost=(products_price*0.869565217391304) where products_cost=0;

 

However I dont know how to achieve the second part. I have a custom admin page where I can add the configuration value for the multiplier amount. Everything except the query to update I can do. I managed to get the following code together (untested) but where the above mentioned code I could do because all the products_costs's were 0 by default, I dont know how to make this query do every product. Would I have to use a 'while' command in order to loop it or what? Here is what I have sofar:

tep_db_query("update products set products_price = '(products_cost*'" . tep_db_input($configuration_value) . "') where ??????'");

 

Any suggestions?

 

Could you do something like where products_price != '(products_cost*'" . tep_db_input($configuration_value)?

 

Dan

Link to comment
Share on other sites

@@Dan Cole

 

Thanks, your code worked, had to modify mine a little bit to get it to work:

tep_db_query("update products set products_price=(products_cost*" . tep_db_input($configuration_value) . ") where products_price != (products_cost*" . tep_db_input($configuration_value) . ")");

Tested it and it works, updates all prices based on the amount I set. So now I am able to change how much I want to gross off every item in the store.

 

Just out of curiosity, is there any demand for this to be made into a contribution once everything is finalized?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...