PDA

View Full Version : Quantity for Product Attributes Mod



michael_s
04-29-2011, 03:00 PM
The code submitted by marcusdesign didn't see to work for me. I've just changed the query that got the info from the products + products_attribute tables. There was an error with how the query was written in marcus' submission (no "on" clause specified with the left join.. not sure why a left was used) and the constant was incorrect too (it's PRODUCTS_TABLE not PRODUCT_TABLE - the S in product :) )

REPLACE IN THE STOCK CHECKOUT_PROCESS.PHP FILE:

// Stock Update - Joao Correia
if (STOCK_LIMITED == 'true') {
if (DOWNLOAD_ENABLED == 'true') {
$stock_query_raw = "SELECT products_quantity, pad.products_attributes_filename
FROM " . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " pa
ON p.products_id=pa.products_id
LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
ON pa.products_attributes_id=pad.products_attributes_ id
WHERE p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'";
// Will work with only one option for downloadable products
// otherwise, we have to build the query dynamically with a loop
$products_attributes = $order->products[$i]['attributes'];
if (is_array($products_attributes)) {
$stock_query_raw .= " AND pa.options_id = '" . $products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . $products_attributes[0]['value_id'] . "'";
}
$stock_query = tep_db_query($stock_query_raw);
} else {
$stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
}
if (tep_db_num_rows($stock_query) > 0) {
$stock_values = tep_db_fetch_array($stock_query);
// do not decrement quantities if products_attributes_filename exists
if ((DOWNLOAD_ENABLED != 'true') || (!$stock_values['products_attributes_filename'])) {
$stock_left = $stock_values['products_quantity'] - $order->products[$i]['qty'];
} else {
$stock_left = $stock_values['products_quantity'];
}
tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
if ( ($stock_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
}
}
}

REPLACE WITH:

// Stock Update - Joao Correia
if (STOCK_LIMITED == 'true') {
if (DOWNLOAD_ENABLED == 'true') {
$stock_query_raw = "SELECT products_quantity, pa.products_attributes_id, pa.options_quantity, pad.products_attributes_filename
FROM " . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " pa
ON p.products_id=pa.products_id
LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
ON pa.products_attributes_id=pad.products_attributes_ id
WHERE p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'";
// Will work with only one option for downloadable products
// otherwise, we have to build the query dynamically with a loop
$products_attributes = $order->products[$i]['attributes'];
if (is_array($products_attributes)) {
$stock_query_raw .= " AND pa.options_id = '" . $products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . $products_attributes[0]['value_id'] . "'";
}
$stock_query = tep_db_query($stock_query_raw);
} else {
// $stock_query = tep_db_query("select p.products_quantity, pa.options_quantity from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
$stock_query = tep_db_query("select p.products_quantity, pa.options_quantity from ". TABLE_PRODUCTS ." p, ". TABLE_PRODUCTS_ATTRIBUTES ." pa where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "' and pa.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
}
if (tep_db_num_rows($stock_query) > 0) {
$stock_values = tep_db_fetch_array($stock_query);
// do not decrement quantities if products_attributes_filename exists
if ((DOWNLOAD_ENABLED != 'true') || (!$stock_values['products_attributes_filename'])) {

$mail = "Looking at product: ". print_r($order->products[$i], true) .".... setting qty to: ";



$stock_left = $stock_values['products_quantity'] - $order->products[$i]['qty'];


$stock_attr_left = $stock_values['options_quantity'] - $order->products[$i]['qty'];

// Debug to check stock reduction is working

} else {
$stock_left = $stock_values['products_quantity'];
$stock_attr_left = $stock_values['options_quantity'];
}
tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
tep_db_query("update " . TABLE_PRODUCTS_ATTRIBUTES . " set options_quantity = '" . $stock_attr_left . "' where products_attributes_id = '" . $stock_values['products_attributes_id'] . "'");
if ( ($stock_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
}
}
}



OR IF YOU'VE APPLIED MARCUS' FIX, CHANGE:

$stock_query = tep_db_query("select p.products_quantity, pa.options_quantity from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCT_ATTRIBUTES . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");

TO:

$stock_query = tep_db_query("select p.products_quantity, pa.options_quantity from ". TABLE_PRODUCTS ." p, ". TABLE_PRODUCTS_ATTRIBUTES ." pa where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "' and pa.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");





More... (http://addons.oscommerce.com/info/3302)