Magento 2 — Cron Job to Update Inventory from Another Database (Simple Example for Smaller Store)

In Magento 2, anytime you want to write custom PHP code, you must first create a “module” — which is where your PHP code will live.

For this example, our module shall be named “MyCompany\MyStockModule” and our PHP code will be written in the UpdateInventory.php file.

Create this folder structure in app/code:

Copy/paste these file contents (from top to bottom):

UpdateInventory.php

<?php

namespace MyCompany\MyStockModule\Cron;

use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
use Magento\InventoryApi\Api\SourceItemRepositoryInterface;
use Magento\InventoryApi\Api\Data\SourceItemInterface;
use Magento\InventoryApi\Api\SourceItemsSaveInterface;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Framework\Api\SearchCriteriaBuilder;
use Exception;
use PDO;

class UpdateInventory
{
    /**
     * @param SourceItemRepositoryInterface $sourceItemRepository
     * @param SourceItemsSaveInterface $sourceItemsSaveInterface
     * @param SearchCriteriaBuilder $searchCriteriaBuilder
     * @param SourceItemInterface $sourceItemInterface
     * @param LoggerInterface $logger

     */
    public function __construct(
        protected SourceItemRepositoryInterface $sourceItemRepository,
        protected SourceItemsSaveInterface $sourceItemsSaveInterface,
        protected SearchCriteriaBuilder $searchCriteriaBuilder,
        protected SourceItemInterface $sourceItemInterface
    )
    {}

    /**
     * @return void
     * @throws Exception
     */
    public function execute()
    {
        /**
         * Get a database connection to your non-Magento database (your "outside" database)
         */
        $pdo = new PDO(
            // provide your database connection details ...
        );

        /**
         * Get your "outside" (non-Magento) inventory quantities.
         * Only SKU and quantity are needed.
         */
        $query = <<<SQL
            SELECT sku, quantity
            FROM my_outside_database.my_outside_inventory_table
SQL;
        $statement = $pdo->prepare($query);
        $statement->execute();

        /**
         * Store your outside quantities in an array, keyed by the SKU number.
         */
        $outsideInventory = $statement->fetchAll(PDO::FETCH_KEY_PAIR);

        /**
         * Get Magento SourceItems. For basic stores, you will use 
         * the "default" source.
         */
        $sourceItems = $this->sourceItemRepository
            ->getList(
                $this->searchCriteriaBuilder
                    ->addFilter(SourceItemInterface::SOURCE_CODE, 'default')
                    ->create()
            )
            ->getItems();

        /**
         * Compare each Magento SourceItem to KBS Inventory quantity.
         */
        $sourceItemsToUpdate = [];
        foreach($sourceItems as $sourceItem) {

            $sku = $sourceItem->getSku();
            $magentoQty = $sourceItem->getQuantity();
            $outsideQty = $outsideInventory[$sku];

            if ($magentoQty !== $outsideQty) {
                $sourceItem->setQuantity($outsideQty);
                $sourceItemsToUpdate[] = $sourceItem;
            }
        }

        /**
         * Persist quantity changes.
         */
        if (count($sourceItemsToUpdate) > 0) {
            $this->sourceItemsSaveInterface->execute($sourceItemsToUpdate);
        }
    }
}

crontab.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Cron:etc/crontab.xsd">
    <group id="default">
        <job instance="MyCompany\MyStockModule\Cron\UpdateInventory" method="execute" name="mycompany_mymodule_updateinventory">
            <schedule>* * * * *</schedule>
        </job>
    </group>
</config>

module.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="MyCompany_MyStockModule" setup_version="1.0.0"/>
</config>

registration.php

<?php

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'MyCompany_MyStockModule',
    __DIR__
);

Run this command to install your module (only needs to be done once):

php bin/magento setup:upgrade

And that’s it! This will keep your Magento quantities in-sync with an outside database.

What Are “SourceItems”?

Magento’s “SourceItems” can be thought of as “Quantity of product at a specific warehouse”.

If you are a smaller company with only 1 warehouse, you can just use the “default” source for everything.

Larger companies, however, may have multiple different “sources” (physical warehouses/locations where product can ship from).

For example, your Magento site may tell the customer you have x10 widgets available to buy. Behind-the-scenes, your Warehouse A “source” might have x4 of those widgets, while Warehouse B “source” has x6 of those widgets…for that total of x10 you are displaying in the store.

So long story short — it’s a small bit of complexity that was added to accommodate stores with multiple warehouses. If you don’t have multiple warehouses, just stick with “default”.

If you want to learn more about Magento Multi-Source Inventory:

https://www.magestore.com/blog/complete-guide-for-magento-inventory-msi/