Update Ps_Product Quantity According To Combination Quantity

[Rissinko]

Hi I have question about updating tables in database ... I have script which change quantity in database. But there is problem ... if my product has combinations it update all quantity in combinations but quantity in ps_product is not change.. Is there in presta some function which can update this number ? or any sql trigger ? or something like this ?

 

In attachment is picture of quatities that i  need to update.

 

 

Here is the code what Iam using to update my quantities from file :

try {
      $res4 = $db->prepare("SELECT id_product, id_product_attribute from "._DB_PREFIX_."product_attribute WHERE reference = :reference");
      $res4->execute(array(':reference'=>$reference));
 
      if ($res4->rowCount() > 0) {
 
                
        $row4 = $res4->fetch();
    
        $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = :q where id_product_attribute = :id_product_attribute");
        $res->execute(array(':q'=>$quantity, ':id_product_attribute'=>$row4['id_product_attribute']));
            
        $res = $db->prepare("update "._DB_PREFIX_."product_attribute set quantity = :q where id_product_attribute = :id_product_attribute");
        $res->execute(array(':q'=>$quantity, ':id_product_attribute'=>$row4['id_product_attribute']));
 
        $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = quantity + :q where id_product = :id_product and id_product_attribute = 0");
        $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
        
        $res = $db->prepare("update "._DB_PREFIX_."product set quantity = quantity + :q where id_product = :id_product");
        $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
 
      } else {
 
        // IT'S A SIMPLE PRODUCT
        
        $res4 = $db->prepare("SELECT id_product from "._DB_PREFIX_."product WHERE reference = :reference");
        $res4->execute(array(':reference'=>$reference));
        if ($res4->rowCount() > 0) {
          $row4 = $res4->fetch();
    
          $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0");
          $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
 
          $res = $db->prepare("update "._DB_PREFIX_."product set quantity = :q where id_product = :id_product");
          $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));
        }
 
      }

[Rissinko]

up

[prestatent]

Hi

 

If I understand you correctly you want to see the total for the product for all combinations. If so you need to add another record into the ps_stock_available table with :

 

- id_product_attribute = 0

 

See attached screenshot for details. This should give you what you are looking for.

 

Cheers

[Rissinko]

Hi prestatent thank you for you answer..

 

But can you describe what do this id_product_attribute = 0 ?? because I think it is id of my attributes (sizes)

 

I need to refresh the quantity numbers in product list (in back end) after using my script in first post... this script i am using for update quantity  and it works like this : First of all it open csv where is reference numbers and quantities... it set up all quantities in database to 0 for reference numbers in this csv and then update all quanties to numbers in csv.

[prestatent]

Hi

 

For each product a "id_product_attribute" = 0 must exist for you to see the total in the product-list in the backend.

 

If you have access to PHPMyAdmin, you should be able to see if a record exists or not. If not, you can manually add a record into the ps_stock_available table. The quantity should be the total of all quantities for all the attributes for the product. In my example the quantities for id_product_attributes 594 and 595 is 15 + 12 = 27.

[Rissinko]

Thanks for description I understand to you now...

 

 

So now i need to find solution which can work with this id_product_attribute ... because i need count combinations quantity together and update this id_product_attribute = 0 row for all products....

[prestatent]

Hi

 

You already have the code in your script above to do this:

$res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = quantity + :q where id_product = :id_product and id_product_attribute = 0");
        $res->execute(array(':q'=>$quantity, ':id_product'=>$row4['id_product']));

So providing you already have a record for the product with id_product_attribute = 0, the above code should work.

[Totti]

why not use class stockavaiable ?