Shipping SQL View Overview (Magento CE 1.3.2.4 and 1.4)

Last modified by loopion on Thu, October 21, 2010 16:53
Source|Old Revisions  |  Back To Group

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`



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs