Integrating Fed Ex Ship Manager with Magento
If you’ve ever wanted to get your shipping a bit more automated, look no further than the good folks on the Magento community forums. “mjohnsonperl” was good enough to write an article on how to do just that, using FedEx Ship Manager. I’ve added some code to his original query to bring the ship method over to Ship Manager as well. Below is the complete, modified query.
BTW, if you’ve ever tried to write/modify a query for a database built using EAV data modeling… well, you get to be friends with Jack Daniels really fast, because that’s the only way to understand the data when looking at ad hoc queries. Thank goodness for Morphine… the band. Look them up in iTunes.
Original FedEx Ship Manager Article
Original MySQL View for Ship Manager
create view vi_sales_order_shipping as
select so.increment_id as 'order_no',
( select xsov.value
from sales_order_varchar xsov
inner join eav_attribute xea on xsov.attribute_id = xea.attribute_id
where xsov.entity_id = so.entity_id and
xea.attribute_code = 'customer_email'
) as 'customer_email',
/* --- SHIP TO ADDRESS --- */
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'firstname'
) as 'shipto_firstname',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'lastname'
) as 'shipto_lastname',
concat(
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'firstname'
), ' ',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'lastname'
)
) as 'shipto_contact_name',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'company'
) as 'shipto_company',
( select substring_index(xsoev.value, char(10), 1 )
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'street'
) as 'shipto_street_line_1',
( select case when xsoev.value like concat('%', char(10), '%')
then substring_index(xsoev.value, char(10), -1 ) else '' end
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'street'
) as 'shipto_street_line_2',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'city'
) as 'shipto_city',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'region'
) as 'shipto_region',
( select dcr.code
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_int xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
inner join directory_country_region dcr on dcr.region_id = xsoev.value
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'region_id'
) as 'shipto_region_code',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'postcode'
) as 'shipto_postcode',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'country_id'
) as 'shipto_country_id',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'shipping_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'telephone'
) as 'shipto_telephone',
/* --- BILL TO ADDRESS --- */
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'firstname'
) as 'billto_firstname',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'lastname'
) as 'billto_lastname',
concat(
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'firstname'
), ' ',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'lastname'
)
) as 'billto_contact_name',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'company'
) as 'billto_company',
( select substring_index(xsoev.value, char(10), 1 )
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'street'
) as 'billto_street_line_1',
( select case when xsoev.value like concat('%', char(10), '%')
then substring_index(xsoev.value, char(10), -1 ) else '' end
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'street'
) as 'billto_street_line_2',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'city'
) as 'billto_city',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'region'
) as 'billto_region',
( select dcr.code
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_int xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
inner join directory_country_region dcr on dcr.region_id = xsoev.value
where so.entity_id = soi_sa.entity_id and xea.attribute_code = 'region_id'
) as 'billto_region_code',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'postcode'
) as 'billto_postcode',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'country_id'
) as 'billto_country_id',
( select xsoev.value
from sales_order_int soi_sa
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id
and soi_sa_ea.attribute_code = 'billing_address_id'
inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
where so.entity_id = soi_sa.entity_id and
xea.attribute_code = 'telephone'
) as 'billto_telephone',
/* --- ORDER DETAILS --- */
( select xsov.value
from sales_order_varchar xsov
inner join eav_attribute xea on xsov.attribute_id = xea.attribute_id
where xsov.entity_id = so.entity_id and
xea.attribute_code = 'shipping_description'
) as 'order_ship_description',
( select xsov.value
from sales_order_varchar xsov
inner join eav_attribute xea on xsov.attribute_id = xea.attribute_id
where xsov.entity_id = so.entity_id and
xea.attribute_code = 'shipping_method'
) as 'order_ship_method',
( select xsod.value
from sales_order_decimal xsod
inner join eav_attribute xea on xsod.attribute_id = xea.attribute_id
where xsod.entity_id = so.entity_id and
xea.attribute_code = 'weight'
) as 'order_weight',
so.subtotal as 'order_subtotal',
so.grand_total as 'order_total'
from sales_order so
/*where so.increment_id = '100000412';*/
/*use the above to filter by a specific order number*/
2 Responses to “Integrating Fed Ex Ship Manager with Magento”
Leave a Reply
mjohnsonperl on June 3rd, 2010
Thanks for the credit. It took a while to understand how in the world to create the query in the first place, and yeah the EAV is hard to write for. I might do a complete rewrite of it at some point now that I’m more familiar with Magento, but this still seems to work well.
CmdrAndreysn on September 8th, 2010
But I don’t want to be friends with Jack Daniels again, it wasn’t a very healthy relationship the first time ’round…
Thanks for the post, I really appreciate it as this will be our next project…