Magento – Massedit stock status in database

magento-1.9sql

I realized I have a lot of products that have quantity above 0 but are not in stock. So I would like to do a search in my database for products that have quantiy > 0 and stock_status 0, this is the code I am using:

UPDATE `cataloginventory_stock_status` SET `stock_status` = replace(stock_status, '0', '1') WHERE qty > '0.0000' AND stock_status = '0'

While this will find all products and change stock_status in the database, the products stock status will not change. I also updated cataloginventory_stock_status_idx but to no avail. Are there other tables I need to set stock status?

Best Answer

You can do this using Magento code also.

First find all products, which have qty>0 and stock_status=0 and set stock status using code below:

<?php
require_once('app/Mage.php');
umask(0);
Mage::app('admin');
set_time_limit(0);

$productCollection = Mage::getModel('catalog/product')
     ->getCollection()
     ->addAttributeToSelect('*')
     ->joinField('qty',
                 'cataloginventory/stock_item',
                 'qty',
                 'product_id=entity_id',
                 '{{table}}.is_in_stock=0',
                 'left')
     ->addAttributeToFilter('qty', array("gt" => 0));

foreach($productCollection as $product) { //print_r($product->getData());exit;
    $_product = Mage::getModel('catalog/product')->load($product->getId());
    $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product);
    $stockItem->setData('is_in_stock', 1); // is 0 or 1
    $stock_item->setData('manage_stock', 1);
    try {
        $stock_item->save();
    } catch (Exception $e) {
        echo "{$e}";
    }
}
echo 'Done';
?>

Please let me know if you find any problem.

Related Solutions

Magento – How to get stock quantity of child product from configurable product in Magento 1.9

       -----------Create controller-------------

         <?php
class Magento_Guys_IndexController extends Mage_Core_Controller_Front_Action
{
    public function indexAction()
    {
        echo "Thank you !";
    }
     public function genCartAction()
    {
        $id = $this->getRequest()->getParam('pid');
        $_product = Mage::getModel('catalog/product')->load($id);
        $buy = Mage::helper('checkout/cart')->getAddUrl($_product);
        echo $qty = (int) Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product)->getQty();

        //echo $id;
        //echo $this->getRequest()->getParam('id');;
    }
}
?>

    ----------------Change Add to cart code-------------------

    <?php if ($_product->isAvailable()): ?>
              <b class="available_quanity" style="display: none">Available Quantity :</b> <span id="simplestock" class="simplestock">Please select a color to view the quantity</span>
            <?php endif; ?>
            </div>
            <?php if(!$_product->isGrouped()): ?>
            <div class="qty">
                <label for="qty"><?php echo $this->__('Qty:') ?></label>
                  <?php $i = 0; ?>
                  <select id="qty" class="input-text" name="qty" style="width:50px;">
                    <?php while($i<=(int) Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product)->getQty()):?>
                      <option value="<?php echo $i; ?>"><?php echo $i++; ?></option>
                    <?php endwhile; ?>
                  </select>
            </div>
            <?php endif; ?>

    ----------------Add Ajax for get Quantity---------------

    <script>
    jQuery(document).ready(function() {
        jQuery(".product-options select[id^='attribute']").on('change', function() {
            var id = getSimpleProductId();
            var qty = "";
            jQuery('.available_quanity').show();
            jQuery("#fancybox-loading").show();
            jQuery.ajax({      
                type: "POST",
                data: 'pid=' + id,
                url:'https://www.thewirelesscircle.com/guys/index/genCart',
                success:function(response){ 
                    if (response) {
                        qty = response;
                        var x = document.getElementById("qty");
                        var i;
                        removeOptions(x);
                        for(i=1;i<=qty;i++) {
                            var option = document.createElement("option");
                            option.text = i;
                            option.value = i;
                            x.add(option);
                        }
                    }
                    jQuery("#fancybox-loading").hide();
                }
            });

        });
    });

    </script>

    ---------------Get Selected Option Id in Magento-------------------

    <script type="text/javascript">
        function removeOptions(selectbox)
        {
            var i;
            for(i = selectbox.options.length - 1 ; i >= 0 ; i--)
            {
                selectbox.remove(i);
            }
        }
        function getSimpleProductId() {
            var productCandidates = [];
            jQuery.each(spConfig.settings, function (selectIndex, select) {
                var attributeId = select.id.replace('attribute', '');
                var selectedValue = select.options[select.selectedIndex].value;

                jQuery.each(spConfig.config.attributes[attributeId].options, function(optionIndex, option) {

                    if (option.id == selectedValue) {
                        var optionProducts = option.products;

                        if (productCandidates.length == 0) {
                            productCandidates = optionProducts;
                        } else {
                            var productIntersection = [];
                            jQuery.each(optionProducts, function (productIndex, productId) {
                                if (productCandidates.indexOf(productId) > -1) {
                                    productIntersection.push(productId);
                                }
                            });
                            productCandidates = productIntersection;
                        }
                    }
                });
            });
            return (productCandidates.length == 1) ? productCandidates[0] : null;
        }
    </script>

Magento 1.9 Event Observer – Product Quantity and Status Change Issue

Try the following.

       // Check if there is a stock item object
    $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product->getId());
    $stockItemData = $stockItem->getData();
    if (empty($stockItemData)) {
    // Create the initial stock item object
    $stockItem->setData('manage_stock',1);
    $stockItem->setData('is_in_stock',$qty ? 1 : 0);
    $stockItem->setData('use_config_manage_stock', 0);
    $stockItem->setData('stock_id',1);
    $stockItem->setData('product_id',$product->getId());
    $stockItem->setData('qty',0);
    $stockItem->save();

    // Init the object again after it has been saved so we get the full object
    $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product->getId());
}

// Set the quantity
$stockItem->setData('qty',$qty);
$stockItem->save();
$product->save();

And the just save the product. Also you can refer few links here,
How can i quick update of qty and stock and all inventory fields in Magento
How to set a product to be in/out of stock programatically

Best Answer

Related Solutions

Magento – How to get stock quantity of child product from configurable product in Magento 1.9

Magento 1.9 Event Observer – Product Quantity and Status Change Issue

Related Topic