Magento – Export Orders – A function to return USPS state abbreviation

It seems the Magento SOAP API is only returning the full state name to me (country being United States). I know that Magento knows the abbreviation – I see it in the source on the customer/address/edit page.

But I’m only getting the full state name via SOAP for my SOAP Order Exporter. No matter, here’s a function that’ll return the state abbreviation. Thank you to the USPS for the state listing. The array is all uppercase because that’s how it came in from the USPS, and I just pasted into my vi editor and used it as-is.

function
getStateAbbreviation( $stateIn ) {
  $stateArray = array (
    "ALABAMA"=>"AL",
    "ALASKA"=>"AK",
    "AMERICAN SAMOA"=>"AS",
    "ARIZONA"=>"AZ",
    "ARKANSAS"=>"AR",
    "ARMED FORCES AFRICA"=>"AE",
    "ARMED FORCES AMERICAS"=>"AA",
    "ARMED FORCES CANADA"=>"AE",
    "ARMED FORCES EUROPE"=>"AE",
    "ARMED FORCES MIDDLE EAST"=>"AE",
    "ARMED FORCES PACIFIC"=>"AP",
    "CALIFORNIA"=>"CA",
    "COLORADO"=>"CO",
    "CONNECTICUT"=>"CT",
    "DELAWARE"=>"DE",
    "DISTRICT OF COLUMBIA"=>"DC",
    "FEDERATED STATES OF MICRONESIA"=>"FM",
    "FLORIDA"=>"FL",
    "GEORGIA"=>"GA",
    "GUAM"=>"GU",
    "HAWAII"=>"HI",
    "IDAHO"=>"ID",
    "ILLINOIS"=>"IL",
    "INDIANA"=>"IN",
    "IOWA"=>"IA",
    "KANSAS"=>"KS",
    "KENTUCKY"=>"KY",
    "LOUISIANA"=>"LA",
    "MAINE"=>"ME",
    "MARSHALL ISLANDS"=>"MH",
    "MARYLAND"=>"MD",
    "MASSACHUSETTS"=>"MA",
    "MICHIGAN"=>"MI",
    "MINNESOTA"=>"MN",
    "MISSISSIPPI"=>"MS",
    "MISSOURI"=>"MO",
    "MONTANA"=>"MT",
    "NEBRASKA"=>"NE",
    "NEVADA"=>"NV",
    "NEW HAMPSHIRE"=>"NH",
    "NEW JERSEY"=>"NJ",
    "NEW MEXICO"=>"NM",
    "NEW YORK"=>"NY",
    "NORTH CAROLINA"=>"NC",
    "NORTH DAKOTA"=>"ND",
    "NORTHERN MARIANA ISLANDS"=>"MP",
    "OHIO"=>"OH",
    "OKLAHOMA"=>"OK",
    "OREGON"=>"OR",
    "PALAU"=>"PW",
    "PENNSYLVANIA"=>"PA",
    "PUERTO RICO"=>"PR",
    "RHODE ISLAND"=>"RI",
    "SOUTH CAROLINA"=>"SC",
    "SOUTH DAKOTA"=>"SD",
    "TENNESSEE"=>"TN",
    "TEXAS"=>"TX",
    "UTAH"=>"UT",
    "VERMONT"=>"VT",
    "VIRGINIA"=>"VA",
    "VIRGIN ISLANDS"=>"VI",
    "WASHINGTON"=>"WA",
    "WEST VIRGINIA"=>"WV",
    "WISCONSIN"=>"WI",
    "WYOMING"=>"WY"
    );
 
    $key = strtoupper($stateIn);
    return( $stateArray[$key] );
}

Just a quickie, but it sure does help.

Magento – Export Orders

SPECIAL NOTICE:

Aug 4, 2009: This article refers to the version 1 SOAP interface. The version 2 interface handles things a bit more cleanly; structs are passed back and forth instead of associative arrays in many cases. You can visit my WSDL dump to see some of the differences between the two: http://www.westwideweb.com/gimme-gimme.php

Introduction

I was at least mildly surprised that Magento did not have a built-in order export module.

Some folks need to export to QuickBooks; others, I guess, just like to have a spreadsheet format to shake and bake to their heart’s content. Still others, like me, need to export orders for consumption by a warehousing (“ERP”) and distribution system. I am building orders for a system that handles all warehousing and shipping duties. It’s imperative that orders coming from Magento are consumable by the distribution system to actually distribute the product!

Approaches

Sebastian Enzinger has a fully Magento-integrated module here, and I highly recommend you give it a look to see if it suits your needs. My solution here … well, it doesn’t come close to Sebastian’s that’s the truth.  It is, however, a fully Magento-correct SOAP API implementation. The difference – I’m running mine Warehouse System side.

It’s a lot easier for me in this case to have the already trained staff take a menu option from their familiar Text Screen interface than it is to ask them to log into a web site, select an option, hop back to the Text Screens and – well, you get the idea.

That’s the magic of SOAP. There is nothing I can do short of rolling my own WSDL implementation on a legacy system that can’t even speak SQL that will work for me. Rather, Magento having so lovingly crafted a WSDL and SOAP API, allows me to let the legacy system call Magento for orders.

SOAP API

The Magento SOAP API is, to put it kindly, sparsely documented. But I’m not complaining. The thing is terrific in what it does; I’m happy to let documentation (such as this post, come to think of it) catch up with the fabulous engine under the hood. It is, in fact, more well documented than the CyberSource SOAP API (See also: http://www.westwideweb.com/gimme-gimme.php ). Some may consider that damning with faint praise; I don’t mean it that way. But if you’re not a SOAP/ php/ Java or what-have-you-guru, you’ll have some exciting challenges before you. Even if you are a guru, you could be in for some excitement.

Some Code

In php. Refer to Activecodeonline.com’s excellent tutorial on Consuming Web Services via SOAP to get your Magento instance ready for this code.

First, though, a function that turns an array into an object. I like to make an object from the arrays, it makes the code a little easier to read, and a lot easier to write. So here’s a function to do just that:

function
array_to_obj($array, &$obj)
{
    foreach ($array as $key => $value) {
    if (is_array($value)) {
        $obj->$key = new stdClass();
        array_to_obj($value, $obj->$key);
    }
    else {
        $obj->$key = $value;
    }
  }
  return $obj;
}

Ok, that’s out of the way. Let’s get hooked into Magento:

    $proxy = new SoapClient('YourMagentoURL/index.php/api/soap/?wsdl');
    $sessionId = $proxy->login('webServiceUsername', 'webServicePassword');

Next, let’s get a list of orders in status “Processing” to work with:

    $orderListRaw = $proxy->call($sessionId, 'sales_order.list',
                    array(array('status'=>array('='=>'processing'))));

$orderListRaw now contains an array of – well, let’s call them Order Headers. So let’s loop through these headers (my term) and grab the whole order from them.

    $howManyOrders = count($orderListRaw);
    for( $i = 0; $i < $howManyOrders; $i++ ) {
       array_to_obj( $orderListRaw[$i], $orderHead );
       $orderAsArray = $proxy->call($sessionId, 'sales_order.info',
                   $orderHead->increment_id );
       array_to_obj( $orderAsArray, $o );
     ...

At this point, “$o” is a complete order, as an object. Let me grab the billing_address info:

    $bi = $o->billing_address; // Shorthand the billing address info

First name, Last Name, Company and address are now available as:

    $bi->firstname
    $bi->lastname
    $bi->company
    $bi->street
    $bi->city
    $bi->region
    $bi->postcode
    $bi->country_id

Similarly, shipping information is here:

    $si = $o->shipping_address;

For line item details, I go back to the $orderListRaw array, and pull out the array of items it contains. This is simpler to me because I can simply “count()” the number of elements in the items array to know how many line items are on this order. I loop through the line items thus:

    $lineItems = $orderAsArray["items"];
    $numberOfLines = count( $lineItems );
    for( $j = 0; $j < $numberOfLines; $j++ ) {
       array_to_obj( $lineItems[$j], $li );
       /* Now I have fields: $li->sku, $li->price, $li->base_row_total ... */
       ...
      /* Do something with line items here ...*/

Lastly, I need payment and shipping information, which I can access like so:

    $o->subtotal
    $o->tax_amount
    $o->weight
    $o->shipping_method
    $o->shipping_amount
    $o->grand_total
    $o->payment->last_trans_id
    $o->payment->method

There are many fields available to you, and many permutations of the data you can export. If you have any comments, questions or suggestions, drop me a line!

Magento – Minimum Advertised Price (MAP) Kludge

Special Note May 28, 2009

I’m keeping this here for historical purposes, but I will revisit. I strongly discourage using the SQL code I’ve specified here, and once I’ve got things working appropriately I will post my findings with a more appropriate Magento-friendly solution.

Introduction

Many manufacturers enforce Minimum Advertised Price (MAP) policy with their resellers, in order, I think, to level the playing field. Especially in electronics, especially when a new product comes out. Over time, the manufacturer will drop the MAP for a particular product as new products come out.

Magento, as of 1.1.6 (and 1.1.7, apparently), does not natively support this construct. After reading over the responses in the thread “How Do I Implement Minimum Advertised (MAP) Pricing?“, I came away with a few concepts that, slightly tweaked, would work for me.

In my case my store’s pricing, stocking and MAP policies are dictated by our backend distribution system.  From this backend system, I create a csv that I upload to Magento via the Profile feature.

Different MAP Policies

I have 3 kinds of MAP I deal with:

  • The first, simplest and most common policy is that I can sell at my price, but I have to show MAP. Click Add to Cart to put the item in your cart and reveal the price.
  • The second, less common, policy is that I can’t sell for less than MAP online. Effectively, then, it isn’t MAP at all. The sell price is set to MAP.  MAP is a non-issue,  Magento-side.
  • The third, least common is: I can’t even sell this thing online. That one solves itself by never entering Magento!

Changing Magento – a Little

The more I pondered, I came away thinking it wasn’t necessary for a product to “know” it was a MAP product in Magento. That would mean an extra attribute that doesn’t really buy me anything, except more maintenance. Remember, MAP lives in my backend system already.

I just want to add the item to the cart at a different price than the one displayed. Tiered Pricing already does that. If I set my Tier Price level to 1 @ $MySellPrice, it just about does exactly what I want it to do. The more I thought about it, this solution is just fine – in may case.

By default it wants to say “Buy q at $x and Save y% ,”  so I changed:

app/design/.../template/catalog/product/view/tierprices.phtml ; around line 40, I commented out the php code like this: <?php /* echo $this->__('Buy %1$s for %2$s each', $_price['price_qty'], $_price['formated_price']) */ ?>

Next, around line 45, I changed:

<?php echo $this->__('and') ?>&nbsp;<strong class="benefit"><?php echo $this->__('save')?>&nbsp;<?php echo $_price['savePercent']?>%

to

<?php echo $this->__('Add to cart for best Price') ?>

Additionally, I lopped off the errant </strong> from line 46, as it wasn’t needed any more (its opening tag was formerly on line 45).

app/design/.../template/catalog/product/price.phtml ; around line 87, I deleted these lines:

<a href="<?php echo $_product->getProductUrl(); ?>" class="minimal-price-link">
<span class="label"><?php echo $this->__('As low as:') ?></span>
<span class="price" id="product-minimal-price-<?php echo $_id ?><?php echo $this->getIdSuffix() ?>"><?php echo Mage::helper('core')->cu    rrency($_minimalPrice,true,false) ?></span>

Changing SQL – I know it’s a No No, but …

Since I had a csv, here’s how I did it. I exported from my backend system – written in a form of Business Basic. The Basic program reached out and grabbed the entity_id for my SKU, and wrote the entity_id, and sell-for price into a csv file. The format:

sku <TAB> entity_id <TAB>   display_price <TAB>  sell_price <TAB>  map_type

The SKU, display_price and map_type are irrelevant to Magento, but relevant to my sanity in trying to make sure everything matches up to the backend system.

A little php code wrapped around a SQL insert, and I’m all set. Here’s a pseudo-code to help get you started:

$fh=fopen("myCsvFile.csv";
while( !feof($fh) ) {

$buffer = fgets($fh, 8192);
$item = explode( "\t", $buffer );
$i = 0;

/* Parse out field names as described above */
$sku = $item[$i++];
$entity_id = $item[$i++];
$display_price = $item[$i++];
$sell_price = trim($item[$i++]);
$map_type = $item[$i++];

/* Construct a query. I've assumed a re-rimport of everything every time - I'm truncating the table every time before I start inserting. */

$query = sprintf( "
INSERT INTO catalog_product_entity_tier_price
( entity_id, all_groups, customer_group_id, qty, value, website_id )
VALUES ( '%s', 1, 0, 1.0000, '%s', 0 )", $entity_id, $sell_price );

/* Execute query here */

}

Even though I’m doing this in SQL, there’s no reason you can’t do it right in Magento. If you have, say, 20 MAP items, just make the changes to the template phtml files, and then set the tier pricing.

Pros and Cons

Pros

  • If you don’t need tiered pricing otherwise, this is a quick and easy solution.
  • Runs fast.

Cons

  • Breaks tier pricing for anything else at the moment. But stay tuned, we’ll be refining that over the 2-3 weeks or so to restore that ability.
  • Totally not the “right” way to do things in Magento.

Next

First, I’ll need to fix tiered pricing, because I need that for my next project. Even though I’ve left tiered pricing broken in this pass, there’s nothing about the fundamentals here that will break it going forward. That is, using Quantity Break 1 to mean “Show one Price, Sell at Another” doesn’t break Quantity Break 2 and so on.

Of course if you’re providing quantity break pricing on MAP items, things will begin to get sticky. As for me, I’m ignoring that problem. The site I need to build that will have that scenario will force customer login to see pricing.

Magento – display SKU on product listing page

In list.phtml, I want to be able to see the SKU in the product listing. list.phtml is a simple if a tad large “if-else” construct, the first part of the if being list view, the else part being the grid view. So, if you want it in both places, you’ll have to hit 2 spots. list.phtml is found in app/design/frontend/pathToYourTheme/template/catalog/product.

On this particular project, SKU is very important. We mail out several catalogs to various verticals, and some people don’t know anything but the SKU when they’re ordering.

My theme is based on default. Your actual line numbers may vary, but here’s the context for list view, the “if” of our template:


55 <?php // Product description ?>
56 <div class="product-shop">
57 <h5><a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->htmlEscape($_product->getName()) ?>"><?php echo $this->htmlEscape($_product->getName())?></a></h5>
58 <?php echo $this->htmlEscape($_product->getSku()); ?>
59 <?php if($_product->getRatingSummary()): ?>
60 <?php echo $this->getReviewsSummaryHtml($_product) ?>
61 <?php endif; ?>

Grid view is the “else” of this section, it’s down a little further in list.phtml:


102 <h5><a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->htmlEscape($_product->getName()) ?>"><? php echo $this->htmlEscape($_product->getName()) ?></a></h5>
103 <?php echo $this->htmlEscape($_product->getSku()); ?>
104 <?php if($_product->getRatingSummary()): ?>
105 <?php echo $this->getReviewsSummaryHtml($_product, 'short') ?>
106 <?php endif; ?>

And that’s it! You’ll have a nice display of your SKU.