Jump to content
YePix

quantity monitoring

Recommended Posts

Hi folks, I really need a product monitoring function. The point is that individual products are counted with the product inventory, but I need it as a category. If I have 10 different sandwiches in a category and each is specified with 10 pieces of product, it would be 100 pieces in total. But if I only have 20 breads in stock, it won't work. Can someone give me an idea of how I can manage to install a counter in the category where 20 pieces are specified and these are also automatically counted down when purchasing a product from this category. when the level reaches 0, this category and all products should be switched off automatically. I thank you in advance for a little help.

Share this post


Link to post
Share on other sites

The QTPro addon does this for attributes if you can have all of the parts as attributes. If they are all different products, then you would  need to check them on page load. So if there are 100 sandwiches showing but you only have 20 breads, you could have something like 

$db_query = tep_db_query("select count(*) as ttl from breads");
$db = tep_db_fetch_array($db_query);
if ($db['ttl']) < 100) {
  tep_db_query("update sandwiches set quantity = " . $db['ttl']);
} 

But that can cause problems since it might change the amount for another customer that has already entered more than the limit. 

Share this post


Link to post
Share on other sites

I thank you for the approach. I will now try to build it up in more detail.

Share this post


Link to post
Share on other sites

where is the function that is responsible for the product quantity that subtracts the purchased products from the inventory?

Share this post


Link to post
Share on other sites

There isn't a function for it. It is part of the code in checkout_process.php and paypal_standard.php (and maybe other payment modules). Search for stock in those files to find it.

Share this post


Link to post
Share on other sites

I'm not quite sure, but could someone see if it was right? If a product is purchased from a category, that the amount is automatically deducted from the amount from the category counter and, if the product is 0, the category and all the products it contains are set to status 0?

  $products_ordered = '';

  for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {
// Stock Update - Joao Correia
    if (STOCK_LIMITED == 'true') {
      if (DOWNLOAD_ENABLED == 'true') {
        $stock_query_raw = "SELECT products_quantity, pad.products_attributes_filename, ptc.categories_product_qty 
                            FROM " . TABLE_PRODUCTS . " p
                            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc
                             ON p.products_id = ptc.products_id 
                            LEFT JOIN " . TABLE_CATEGORIES . " c
                             ON c.categories_id = ptc.categories_id 
                            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 = (isset($order->products[$i]['attributes'])) ? $order->products[$i]['attributes'] : '';
        if (is_array($products_attributes)) {
          $stock_query_raw .= " AND pa.options_id = '" . (int)$products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . (int)$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 {
        if (tep_not_null($stock_values['categories_product_qty'])){
          $stock_left = $stock_values['categories_product_qty'] - $order->products[$i]['qty'];
        }else{
          $stock_left = $stock_values['products_quantity'];
        }
        }
        tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . (int)$stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
        tep_db_query("update " . TABLE_PRODUCTS_TO_CATEGORIES . " set categories_product_qty = '" . (int)$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']) . "'");
        if ( ($stock_values['categories_product_qty'] < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
          tep_db_query("update  " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c set p.products_status = '0' where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "' and c.categories_status = '0' where c.categories_id = '" . (int)$stock_query['ptc.categories_id'] . "' and products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
}
        }
      }
    }

 

Share this post


Link to post
Share on other sites

Assuming you have standard tables,. there isn't a categories_products_qty field. I assume by "category counter" you are referring to the product count that is shown in the categories box. If so, that number is calculated each time the page loads, which is why it is not recommended to be used. There also isn't a field to show or hide a category. You would need to edit the category_tree.php class file to check for products in the category to show or hide it. You should also install one of the call for prices addons that will mark products with a price of 0 as not available. 

 

Share this post


Link to post
Share on other sites

Yes, each product has to be monitored in a category.

Exact example.

Category A

Product 1 Quantity 10
Product B quantity 10
Product C quantity 10

Category A Available quantity 3

If product A is sold twice and product B is sold once, the amount available in category A must also be deducted automatically.

If category A available quantity is set to 0, this category A and all of its products must be set to status 0.

Share this post


Link to post
Share on other sites

What you are trying to do is not a small job and is beyond the scope of support threads, in my opinion, but here is a way to do it.

1 - Get is a comma separate list of all of the product ID's in a category to give something like this. You can get the list from the products_to_categories table using the category ID to check.

$product_ids = '1,2'3';

2 - In the categories box,  use code like this to get the minimum quantity of all of the products:

SELECT MIN(products_quantity) FROM products where products_id in (' . $product_list . ');

3 - Change the category tree file so check the above result and if it is 0, returns 0, then don't add that category to the listing.

4 - Cycle through the products in the above list and set the status to 0. Note that if you do this, it will remove them from the site and that will affect SEO, if that matters. Otherwise, set their quantities to 0 and change the product page to not allow adding to cart when the quantity is 0. 

Share this post


Link to post
Share on other sites

I already realize that it is a bit more complex. But I don't have the time to sit down and click around on the categories and products. Everything has to run automatically for me because my products are prepared in real time and if something is no longer available it has to be switched off immediately. I thank you anyway for the suggestion.

Share this post


Link to post
Share on other sites
Posted (edited)

I have the problem that I cannot test it because I only have 1 database and for this reason my system must not fail. I think, however, that it is not too much effort to calculate the quantities from the categories to products as described above and to subtract the purchased products. this instruction and query in the checkout_process.php should solve it. I just need someone who takes a quick look at it and tells me whether it can be implemented this way.

 

  for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {
// Stock Update - Joao Correia
    if (STOCK_LIMITED == 'true') {
      $stock_categories_query = tep_db_query("select p.products_id, p.products_quantity, ptc.categories_product_qty 
                            FROM " . TABLE_PRODUCTS . " p
                            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc
                             ON p.products_id = ptc.products_id 
                            LEFT JOIN " . TABLE_CATEGORIES . " c
                             ON c.categories_id = ptc.categories_id
        where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
      }
      if (tep_db_num_rows($stock_categories_query) > 0) {
        $st_cat_values = tep_db_fetch_array($stock_categories_query);
        $stock_cat_left = $st_cat_values['categories_product_qty'];
       
        tep_db_query("update " . TABLE_PRODUCTS_TO_CATEGORIES . " set categories_product_qty = '" . (int)$stock_cat_left . "' where products_id = '" . (int)$st_cat_values['products_id'] . "' and products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
        if ( ($stock_cat_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
          tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . (int)$st_cat_values['products_id'] . "' and products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
        }
      }
    }

 

Edited by YePix

Share this post


Link to post
Share on other sites

This should always reduce the counter of the purchased quantity of products from the assigned category by the purchased number. Set up like the download counter. Could someone take a look at whether this code can be built into the checkout_process.php?

  for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {
      $stock_categories_query = tep_db_query("select c.categories_product_qty 
                            FROM " . TABLE_PRODUCTS . " p
                            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc
                             ON p.products_id = ptc.products_id 
                            LEFT JOIN " . TABLE_CATEGORIES . " c
                             ON c.categories_id = ptc.categories_id
        where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
      if (tep_db_num_rows($stock_categories_query) > 0) {
        $st_cat_values = tep_db_fetch_array($stock_categories_query);
        $stock_cat_left = $st_cat_values['categories_product_qty'];
       
        tep_db_query("update " . TABLE_CATEGORIES . " set categories_product_qty = '" . (int)$stock_cat_left . "' - " . sprintf('%d', $order->products[$i]['qty']) . " where products_id = '" . (int)$st_cat_values['products_id'] . "'");
        if ( ($stock_cat_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
          tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . (int)$st_cat_values['products_id'] . "' and products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
          tep_db_query("update " . TABLE_CATEGORIES . " set categories_status = '0' where categories_id = '" . (int)$st_cat_values['categories_id'] . "'");
        }
      }
    }

 

Share this post


Link to post
Share on other sites

Hi people, now my project works so far.

I still have a problem though How do I set all products in the same category to status 0 ?

This script sets the category to status 0 and only the purchased product to 0 Does anyone here have any food for thought for me?

// BOF Update products_category_count
      $stock_categories_query = tep_db_query("select p.products_id, c.categories_id, c.categories_product_qty 
                            FROM " . TABLE_PRODUCTS . " p
                            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc
                             ON p.products_id = ptc.products_id 
                            LEFT JOIN " . TABLE_CATEGORIES . " c
                             ON c.categories_id = ptc.categories_id
        where p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "' and c.categories_id");

        $st_cat_values = tep_db_fetch_array($stock_categories_query);
        $stock_cat_left = $st_cat_values['categories_product_qty'];
        if (tep_not_null($stock_cat_left)) {
        tep_db_query("update " . TABLE_CATEGORIES . " set categories_product_qty = '" . (int)$stock_cat_left . "' - " . sprintf('%d', $order->products[$i]['qty']) . " where categories_id = '" . (int)$st_cat_values['categories_id'] . "'");

        $cat_stock = tep_db_query("select categories_id, categories_product_qty from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$st_cat_values['categories_id'] . "'");
        $cs = tep_db_fetch_array($cat_stock);
        if ($cs['categories_product_qty'] < 1) {
          tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
          tep_db_query("update " . TABLE_CATEGORIES . " set categories_status = '0' where categories_id = '" . (int)$cs['categories_id'] . "'");
        }
      }
// EOF Update products_category_count
// **********************************

 

Share this post


Link to post
Share on other sites

Retrieve all of the product ID's from the products_to_categories table for the given categories ID. Then loop through the returned value and change their status.

Share this post


Link to post
Share on other sites

I thank you. that is exactly.

Now I need to expand the query for $ stock_check in shopping_cart.php so that not only the product quantity but also the inventory of the quantities in the categories is monitored and taken into account

Share this post


Link to post
Share on other sites

so slowly I despair people.

How do I get the function to check my inventory in the categories of the quantity of the product in the shopping cart?
It should be a minor matter for the professionals here. I thank you for any help.

 

// Return a categories's stock
// TABLES: categories
  function tep_get_categories_stock($categories_id) {
    $stock_categories_query = tep_db_query("select c.categories_product_qty from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc,  " . TABLE_CATEGORIES . " c where p.products_id = ptc.products_id and ptc.products_id = '" . (int)$products_id . "' and ptc.categories_id = c.categories_id and c.categories_id = '" . (int)$categories_id . "'");
    $stock_categories_values = tep_db_fetch_array($stock_categories_query);

    return $stock_categories_values['categories_product_qty'];
  }

// Check if the required categories's stock is available
// If insufficent categories's stock is available return an out of categories's stock message
  function tep_check_categories_stock($categories_stock, $products_quantity) {
    $stock_categories_left = tep_get_categories_stock($categories_stock) - $products_quantity;
    $out_of_categories_stock = '';

    if ($stock_categories_left < 0) {
      $out_of_categories_stock = '<span class="text-danger"><b>' . STOCK_MARK_PRODUCT_OUT_OF_STOCK . '</b></span>';
    }

    return $out_of_categories_stock;
  }

        $stock_categories_check = tep_check_categories_stock($stock_categories_values['categories_product_qty'], $products[$i]['quantity']);
        if (tep_not_null($stock_categories_check)) {
          $any_out_of_categories_stock = 1;

          $products_name .= $stock_categories_check;
        }

 

Share this post


Link to post
Share on other sites

There are a number of ways to do it but I would use the shopping_cart class. See includes/classes/shopping_cart.php for the available functions. You probably want to use get_product_id_list. So it would be

$id_list = $cart->get_product_id_list();

Then explode the list and go through the array to get each ID. Then call 

$qty = $cart->get_quantity($products_id);

 

Share this post


Link to post
Share on other sites

Hello Jack, first of all thanks for the tips.

But now I'm completely on the hose. I absolutely don't know how to set it up or how to put the corresponding function together in order to link the category quantity with the product in the shopping cart.
If an idea seems obvious to you, it would be great to post here. Thanks.

Share this post


Link to post
Share on other sites
6 hours ago, YePix said:

link the category quantity with the product in the shopping cart

I don't understand what you are trying to do. Can you explain it in more detail? Or are you saying you don't know how to implement the code I mentioned?

Share this post


Link to post
Share on other sites

I have a counter in category A.
Counter stood 10 pieces.

In category A there are 5 products.
Each product is available 20 times.

When purchasing products in category A, it must be checked in the shopping cart that the customer cannot purchase more than 10 products from this category.
Only 10 products from category A may be purchased that are specified in the counter.

I therefore need a review of the products that are assigned to their categories.

Share this post


Link to post
Share on other sites
Posted (edited)

Is that how you meant it?

 

    function get_products_to_categories() {
      global $categories_id;

      if (!is_array($this->contents)) return false;

      $products_to_categories_array = array();
      foreach(array_keys($this->contents) as $products_id) {

      $products_to_categories_query = tep_db_query("select p.products_id 
                                        from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc 
                                        where p.products_id = '" . (int)$products_id . "' and
                                        ptc.products_id = p.products_id");



        if ($products_to_categories = tep_db_fetch_array($products_to_categories_query)) {
          $pr_to_cid = $products_to_categories['products_id'];

       $stock_categories_query = tep_db_query("select c.categories_product_qty 
                                        from " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc 
                                        where c.categories_id = ptc.categories_id and
                                        ptc.products_id = '" . (int)$products_to_categories['products_id'] . "'");
          if (tep_db_num_rows($stock_categories_query)) {
            $stock_categories = tep_db_fetch_array($stock_categories_query);
            $sptc_value = $stock_categories['categories_product_qty'];
          }

          $products_to_categories_array[] = array('ptcid' => $sptc_value);
        }
      }

      return $products_to_categories_array;
    }

 

Edited by YePix

Share this post


Link to post
Share on other sites

In shopping_cart.php, add something like this. Add what you want to do with the quantities at the echo statement.

$id_list = $cart->get_product_id_list();
$ids = explode(',', $id_list;

foreach ($ids as $id) {
   $qty = $cart->get_quantity($products_id);
   
   echo 'There are ' . $qty . ' products in this category<br>';
}

 

Share this post


Link to post
Share on other sites

Hi Jack, I don't need a list of products that are in the shopping cart.

I need something like this code below, but instead of monitoring the inventory of the products here, it must be monitored with the inventory in the category counter.
I just can't manage that products are assigned to the category and that these are connected to category counters.

 

// Return a product's stock
// TABLES: products
  function tep_get_products_stock($products_id) {
    $products_id = tep_get_prid($products_id);
    $stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");
    $stock_values = tep_db_fetch_array($stock_query);

    return $stock_values['products_quantity'];
  }

////
// Check if the required stock is available
// If insufficent stock is available return an out of stock message
  function tep_check_stock($products_id, $products_quantity) {
    $stock_left = tep_get_products_stock($products_id) - $products_quantity;
    $out_of_stock = '';

    if ($stock_left < 0) {
      $out_of_stock = '<span class="text-danger"><b>' . STOCK_MARK_PRODUCT_OUT_OF_STOCK . '</b></span>';
    }

    return $out_of_stock;
  }

 

Share this post


Link to post
Share on other sites

The shopping cart page was just an example. The $cart variable is available throughout the shop, though you may need to use global $cart; in some places to make it usable. So anywhere you need to know the quantities in the shop, just use the code I posted and you will know. What you do with the results is up to you.

Share this post


Link to post
Share on other sites

@YePix Let me see if I understand your problem correctly ...

You say have enough meat to make 100 sandwiches, but only enough bread to make 10. This sounds like you need an add-on like Bundled Products

https://apps.oscommerce.com/BZ2FO&amp;bundled-products

This way, your customers buy a sandwich, and the add-on automatically subtracts the inventory for both the meat and bread. When there is not enough bread to make any more sandwiches, the add-on states that there is not enough stock to assemble the requested sandwich.

This add-on will most likely need to be updated to work on a newer version of osC (either stock or community edition). Also, there is another add-on available specifically for the Phoenix/community edition version, but we can't discuss that here.

Excuse me if I misunderstood your problem ...

Malcolm

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

×