Update Amazon with Magento stock quantities

This is doubtless going to be one of our more obscure posts, but it's helpful to a client, so chances are that it may be helpful to someone else!

Background

In this instance, the client was selling on Amazon, and wanted a way to sync stock. They integrated Magento and Amazon through me2pro's extension, which did a great job of pulling down orders, sorting out listings and the like, but fell down when it came to bundled products - some of the listings on Amazon were for one product (from Amazon's point of view) but, because of stock levels, had to be dealt with as bundled products (from Magento's point of view). The problem was that they needed to be able to tell Amazon how many of those items they had in stock, but there was no way of doing so through me2pro. Could we help...?

Of course - with a little export script. We created a script that picked up all the bundled products that they sold on Amazon, ran through each of them, and calculated, by looking at the stock levels of their component parts, what the maximum number of each bundle that they could sell was. This then outputted that to a tab delimited text file in the format that Amazon wanted so that they could just upload it to Amazon to update the stock levels of their bundle products.

Tab separated text files

A quick aside - Amazon has a quick import function for prices and stock quantities, but the file needs to be a tab-separated text file. In the example below we're using fputcsv to write the CSV - as is standard we pass two arguments to this function, the value we want written, and the separator. Since it's a tab separated text, normally we would look to write '\t' as the separator, but fputcsv only takes one character. We therefore pass the number 9 (which is the ASCII code for a tab symbol) to the chr() function, and put that in as our separator instead.

Now...back to the code

Let's jump straight into the code - as usual, we'll explain as we go, and as usual there's a link to download the whole file at the end of this post.

This is a standalone script file, so it needs to hook into Magento at the very beginning in the usual way :

<?php
require_once 'app/Mage.php';
Mage::app();
Mage::app()->getStore()->setId(Mage_Core_Model_App::ADMIN_STORE_ID);

Now we need to get the products. In this instance, not all bundled products were being sold on Amazon, so we'd created a simple Yes / No attribute called 'amazon_bundle' - if yes, then the bundle was being sold on Amazon. So let's get all those products into $products :

$products = Mage::getModel('catalog/product')->getCollection();
$products->addFieldToFilter(array(
	array('attribute'=>'amazon_bundle','eq'=>'1'),
));

As we mentioned above, Amazon wants a tab delimited text file for the stock and price upload file, with a specific set of headers, so let's create the file (this will be called 'amazon.txt' and will be outputted to the web root of the site) and write the header line that Amazon specifies :

$fp = fopen('amazon.txt', 'w');
$headerRow = array("sku","price","minimum-seller-allowed-price","maximum-seller-allowed-price","quantity","leadtime-to-ship");
fputcsv($fp, $headerRow, chr(9));

Now we need to start looping through our bundled products. For each product we need to work through it in order - first we get its SKU, then we load all the information about that product :

foreach ($products as $product) { 
	$productSKU = $product->getSku();
	$currentProduct = Mage::getModel('catalog/product')->loadByAttribute('sku',$productSKU);

Now we need to look at the simple products that make up that bundled product - there may be one or more of these, so first up let's load them into $selectionCollection :

$selectionCollection = $currentProduct->getTypeInstance(true)->getSelectionsCollection(
        $currentProduct->getTypeInstance(true)->getOptionsIds($currentProduct), $currentProduct);

And for each of the simple products in the bundle, we're going to need its SKU, the quantity that we have in stock, and (and this is <strong>important</strong>) the quantity of that item that are needed for the bundle. We're going to store those in three arrays :

$skus = array();
	$quantityinstock = array();
	$quantityrequired = array();

Next we loop through each of the individual simple items that make up the bundle and get its product ID, then use that to load all its information :

foreach($selectionCollection as $option) {
		$productID = $option[product_id];
		$componentSku = Mage::getModel('catalog/product')->load($productID)->getSku();

Now we start writing to our arrays (i) the SKU of the individual item, (ii) the quantity we have in stock, and (iii) the quantity that we need of that item for the bundle :

$skus[] = $componentSku;
	$quantityinstock[] = (int)Mage::getModel('cataloginventory/stock_item')->loadByProduct($productID)->getQty();
	$quantityrequired[] = $option[selection_qty];

Finally (this could probably live outside this loop, but it's not doing any harm here :

$bundleNos = count($skus);
	}

That last line has given us a key piece of information - how many simple products go into making up this bundled product? If it's just one, then the next step is nice and easy - the quantity of this bundle that's available is determined by taking (i) the stock quantity of the simple product that makes it up, (ii) divided by the number of that simple product required by the bundle (iii) rounded down to the nearest whole number. So we do that calculation, make the results into a line in the text file, and write it to the text file :

if ($bundleNos == 1 ) {
	$thisQuantity = (floor($quantityinstock[0] / $quantityrequired[0]));
	$fields = array($productSKU, " ", " ", " ", $thisQuantity, " ");
	fputcsv($fp, $fields, chr(9));

If, however, there's more than one simple product in the bundle, the number of available bundle products is going to be the lowest value from the above calculation when we've carried it out for all the simple products that go into it.

That's a pretty convoluted sentence, but look at it this way - if your bundled product is made up of 1 A, 4 B's and 3 C's, if you've got 10 A's in stock, 6 B's in stock and 12 C's in stock, then you can only sell 1 of the bundled product, because that would use up 4 of your 6 available B's - after that you've only got 2 B's in stock, which isn't enough to make up one of your bundled product.

So we need to keep track of the quantity available for each simple product in the bundle (ie. the stock quantity, divided by the quantity needed for the bundle, rounded down to the nearest whole number) :

} else {
	$quantityAvailable = array();

Then we need to loop through the arrays we populated earlier with all the information about the simple products and write the quantity available of that simple product to the array :

for ($i = 0; $i <= ($bundleNos - 1); $i++) {
	$quantityAvailable[] = floor($quantityinstock[$i] / $quantityrequired[$i]);
	}

When that's done, we need to look for the lowest value in that array, because that shows us how many of the bundled we've got available to sell (it doesn't matter if you've got 1000 A's in stock - if you've only got 4 B's and you need 4 B's to make 1 bundle, then you've only got 1 bundle available). When we've got that, we can write the line for that bundle product in the tab delimited text file :

$minQuantity = min($quantityAvailable);
	$fields = array($productSKU, " ", " ", " ", $minQuantity, " ");
	fputcsv($fp, $fields, chr(9));
	}
}

Finally a little output to the screen to let us know we're done :

echo "Done";
?>