Import Structured Pricing Tiers using the Magento SOAP API
05/13/11 at 01:05:10 pmMagento has a fantastic API that, while robust, can also be frustrating due to a lack of detail in some areas of the documentation. If you are looking to automate a tier pricing structure into your Magento deployment from a back office ERP system or some other source, knowing a few details about about the Product_Tier_Price resource can save you a boatload of frustration. The below code will address the need to create a real time link for pricing between Magento and your accounting platform.
First, lets lay out a scenario:
We have five customer price levels (or pricing tiers in Magento speak) inside of our ERP database. We want to build a scenario whereby price changes inside the ERP application automatically push out to our website in realtime. If you’re tasked with backend software set that’s fairly antiquated in terms of technology, then the best way to accomplish this is through the use of staging tables. This is because proprietary database technologies, like the ProvideX flat file platform used in Sage’s MAS ERP for instance, may not be very friendly to generic ODBC connections. In this situation a better approach will be to separate the process into two jobs, the first using tools native to the ERP to dump the raw data into a staging table, and the second to actually make the call to the Mage API, format the data correctly, send it out to the web server, and remove it from the staging db.
The first thing to do is create a staging database that both applications will use to relay information to each other. Create a separate database in the same instance of MySQL that hosts your Magento database. In this example I aptly named the db, ‘Magento_Staging.’
Create the table for the transaction:
CREATE TABLE IF NOT EXISTS `MAS_PriceCode` (
`ItemCode` varchar(30) NOT NULL,
`CustomerPriceLevel` char(1) NOT NULL,
`DiscountMarkup1` decimal(16,2) NOT NULL
)
As you can see I am only pulling in the SKU, level, and Price in this example. I am not pulling in a quantity variant. You can adjust your table and structure accordingly if you need to implement a quantity variant.
Next step is to configure your backend system to send the data up to the staging database. This process can vary based on architecture and falls a bit beyond the scope of this post, but one very important note to this is that when configuring triggers to write tier modifications out, make sure you send out all price tiers associated with that sku regardless of change. This is essential because when you call the update function in the Magento SOAP API, you pass it a two dimensional array of values that will wipe any present tier data from the Mage db and replace it with contents of the array. So if you have five current tiers, and you only pass it a single tier though the SOAP call, you’ll only wind up with a single price tier for your item in Magento. This is one of those little tidbits that the documentation fails to mention.
Staging table example
Once you have your data into your staging table, the next step is to actually call the SOAP reference and process it.
Create a new php document and establish a connection to your mysql server and to the SOAPClient interface.
//establish MySQL Connection
$conn = mysql_connect(your_mage_mysql_server, username, password);
mysql_select_db(your_mage_database);
//establish SOAP API Connection
$proxy = new SoapClient('http://www.yourwebsite.com/index.php/api/?wsdl');
$sessionId = $proxy->login(API_User, API_Key);Next, you need to create a query that will determine the item’s key field based on the SKU and create an intersecting set of data. The API documentation is misleading in this area, as in the examples would appear as though you can simply reference a sku and push the data straight in. This is not the case, however. You need to first determine the item’s number in the catalog_product_entity table within Magento.
$sql = "SELECT entity_id, CustomerPriceLevel, DiscountMarkup1
FROM insynch_staging.MAS_PriceCode, magento.catalog_product_entity
WHERE insynch_staging.MAS_PriceCode.ItemCode = magento.catalog_product_entity.sku";
From here all thats left to do is build the query result and iterate through it, creating the formatted data and calling the .update method each time the array build is complete. I created an array called $tierPrices to hold the values for each tier until the API call is made.
Remember, two important things:
1) Update calls are sku specific so you can only load price tiers for one sku at a time.
2) All tiers for any given sku must be loaded on the same call.
For these reasons you will have to develop some logic within your iteration to determine when to just write the line to the array and when to write the line and make the SOAP call. In this instance, I know each item has five tiers, so I run an incremental counter that calls the update method at ’5′, blanks $tierPrices array with an unset command, and continues on to the next line.
while ($row = mysql_fetch_array($result)) {
//convert the sku to the magento item key field
$sku = $row['entity_id'];
//convert the price level the magento key field
if ($row['CustomerPriceLevel'] == 'A') {$groupno = '2';}
elseif ($row['CustomerPriceLevel'] == 'B') {$groupno = '1';}
elseif ($row['CustomerPriceLevel'] == 'C') {$groupno = '4';}
elseif ($row['CustomerPriceLevel'] == 'G') {$groupno = '8';}
elseif ($row['CustomerPriceLevel'] == 'W') {$groupno = '5';};
//test to see if next row is not the same sku
if ($i < 5) {
//continue with current array. do not call API
$tierPrices[] = array(
'customer_group_id' => $groupno,
'website' => 'all',
'qty' => '1.0000',
'price' => $row['DiscountMarkup1']
);
$i++;
}
//add item to array, call API, empty array, reset counter
elseif ($i == 5) {
$tierPrices[] = array(
'customer_group_id' => $groupno,
'website' => 'all',
'qty' => '1.0000',
'price' => $row['DiscountMarkup1']
);
$proxy->call($sessionId, 'product_tier_price.update', array($sku, $tierPrices));
echo 'Execute SOAP
';
unset($tierPrices);
$i = 1;
}
}
Lastly, once we’re finished we want to empty the data out of the staging database. Otherwise we’ll have to parse that data every time the script runs, which will eventually grow inefficient, given the fact that API calls generally take 1-2 seconds per transaction. If you garner absolute certainty about the referential integrity of your data, you can just fire a TRUNCATE query at the end of your script. Otherwise, you may want to make the row delete a part of the iteration, skipping deletes on write failures so you know exactly what failed.
As in any production level script, you’ll want to encapsulate your code in try / catch statements to account for any possible errors that may occur.
When you’re finished, go into the Magento backend and look at your items in question and you should see the newly updated price tiers in your item detail.
Pricing tiers as displayed in Magento Admin
Call the script in your server cron to automate the whole thing and voila! Realtime pricing updates on your website!
Powered by WordPress with GimpStyle Theme design by Horacio Bella.
Entries and comments feeds.
Valid XHTML and CSS.