Shipping SQL View Overview (Magento CE 1.3.2.4 and 1.4)
Table of Contents
Shipping SQL View Overview (Magento CE 1.3.2.4 and 1.4) |
There may be a better way to write this, or do accomplish this kind of things, but this is at least a start. Feel free to comment about it here: http://www.magentocommerce.com/group/blog/action/viewpost/302/group/207/
Shipping SQL View - Magento CE 1.3.2.4 |
Here is my select statement:
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*/
After you have the view created then you can treat the view similar to how you treat a table.
select * from vi_sales_order_shipping
or if you want to reference a single order
select * from vi_sales_order_shipping where order_no = '100000004'
There is a limitation with FedEx Ship Manager to 1000 orders into the ‘hold’ file. So if you need to retrieve only orders who are in an specified status just add the following SQL statement at the end of the query (after from sales_order so).
, sales_order_varchar xsov where so.entity_id = xsov.entity_id and xsov.attribute_id = '215' and xsov.value = 'processing' /* change to the desired status */
Shipping SQL View - Magento CE 1.4.1 |
create view vi_sales_order_shipping as
select
`so`.`entity_id` AS `id`,
`so`.`increment_id` AS `order_no`,
`so`.`customer_email` AS `customer_email`,
`sosa`.`firstname` AS `shipto_firstname`,
`sosa`.`lastname` AS `shipto_lastname`,
concat(`sosa`.`firstname`,_utf8' ',`sosa`.`lastname`) AS `shipto_contact_name`,
`sosa`.`company` AS `shipto_company`,
substring_index(`sosa`.`street`,char(10),1) AS `shipto_street_line_1`,
if((`sosa`.`street` like concat(_utf8'%',char(10),_utf8'%')),substring_index(`sosa`.`street`,char(10),-(1)),_utf8'') AS `shipto_street_line_2`,
`sosa`.`city` AS `shipto_city`,
`sosa`.`region` AS `shipto_region`,
`sosa_dcr`.`code` AS `shipto_region_code`,
`sosa`.`postcode` AS `shipto_postcode`,
`sosa`.`country_id` AS `shipto_country_id`,
`sosa`.`telephone` AS `shipto_telephone`,
`soba`.`firstname` AS `billto_firstname`,
`soba`.`lastname` AS `billto_lastname`,
concat(`soba`.`firstname`,_utf8' ',`soba`.`lastname`) AS `billto_contact_name`,
`soba`.`company` AS `billto_company`,
substring_index(`soba`.`street`,char(10),1) AS `billto_street_line_1`,
if((`soba`.`street` like concat(_utf8'%',char(10),_utf8'%')),
substring_index(`soba`.`street`,char(10),-(1)),_utf8'') AS `billto_street_line_2`,
`soba`.`city` AS `billto_city`,
`soba`.`region` AS `billto_region`,
`soba_dcr`.`code` AS `billto_region_code`,
`soba`.`postcode` AS `billto_postcode`,
`soba`.`country_id` AS `billto_country_id`,
`soba`.`telephone` AS `billto_telephone`,
`so`.`weight` AS `order_weight`,
`so`.`subtotal` AS `order_subtotal`,
`so`.`grand_total` AS `order_total`
from `sales_flat_order` `so`
left join `sales_flat_order_address` `sosa` on `sosa`.`entity_id` = `so`.`shipping_address_id`
left join `directory_country_region` `sosa_dcr` on `sosa_dcr`.`region_id` = `sosa`.`region_id`
left join `sales_flat_order_address` `soba` on `soba`.`entity_id` = `so`.`billing_address_id`
left join `directory_country_region` `soba_dcr` on `soba_dcr`.`region_id` = `soba`.`region_id`
