RE: Specify product quantity for an attribute

This message was sent from: Features
http://forums.oscommerce.com/viewtopic.php?p=190030#190030
----------------------------------------------------------------

radders,
i checked my site and checkout button seems OK to me. Please check for me again.

Anyway, to all that interested in attributes stock tracking. Qtpro doesn't work with multiple attributes. For e.g. A shirt with color and size options. It cant match the product_stock_attributes during checkout_process. But it can check the stock. Funny.... here is what i did to the checkout process:

// Stock Update - Joao Correia
    if (STOCK_LIMITED == 'true') {
     $products_attributes = $order->products[$i]['attributes'];
      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
        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 {
        if (is_array($products_attributes)) {
	        $products_stock_attributes_array = array();
	        For($k=0, $n3=sizeof($products_attributes); $k<$n3; $k++){
	         if ($products_attributes[$k]['special'] == 0) {
	         $products_stock_attributes_array[] = $products_attributes[$k]['option_id']."-".$products_attributes[$k]['value_id'];
	         }
	        }
         $products_stock_attributes=implode(",",$products_stock_attributes_array);
         $attributes_stock_query = tep_db_query("select products_stock_quantity from " . TABLE_PRODUCTS_STOCK . " where products_stock_attributes = '$products_stock_attributes' AND products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
	if (tep_db_num_rows($attributes_stock_query) > 0) {
         $attributes_stock_values = tep_db_fetch_array($attributes_stock_query);
         $attributes_stock_left = $attributes_stock_values['products_stock_quantity'] - $order->products[$i]['qty'];
         if ($attributes_stock_left < 1) {
           tep_db_query("update " . TABLE_PRODUCTS_STOCK . " set products_stock_quantity = '0' where products_stock_attributes = '$products_stock_attributes' AND products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
           $actual_stock_bought = $attributes_stock_values['products_stock_quantity'];
	         }else{
           tep_db_query("update " . TABLE_PRODUCTS_STOCK . " set products_stock_quantity = '" . $attributes_stock_left . "' where products_stock_attributes = '$products_stock_attributes' AND products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
           $actual_stock_bought = $order->products[$i]['qty'];
		        }
	      }else{
		$actual_stock_bought = $order->products[$i]['qty'];
		}
	      }else{
		$actual_stock_bought = $order->products[$i]['qty'];
		}
        $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'] - $actual_stock_bought;
        } 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']) . "'");
      }
    }

I didnt want to set the product status to 0 when out of stock even though i dont allow out of stock checkout cos i want my customer to see what i sell. So, i remove the set status part .....

Request help from expert programmer, how do i query multiple attributes so that it can match and deduct stock from products_stock table? The above only works for a single attribute product.

thanks in advance