Dev bulletin 11/15

Currently there's a difference between FE and BE MS orders:

• for FE MS orders, the sales-ship-item stores product id of configurable-product; and store the SKU of the simple-product.
• for BE MS orders, the sales-ship-item stores product id of simple-product; and store SKU of the simple-product
  
  Example: right is front-end, left is back-end. Same product orders; but data stored differently in database

To address this difference; in reports; we currently need to UNION two queries. For example, ItemsOnOrderByItem: app/code/Frans/AdminReport/Controller/Adminhtml/Action/ItemsOrdByItemCsv.php 

Query:

# First set, pull from configurable prod. Because FE MS orders has sales_shipment_item storing prod-id of CONFIGURABLE PROD, we need to union this set. In the
# future we might want to rewrite either FE or BE to make sure they are consistent
SELECT
#     ,ss.entity_id,o.updated_at, o.entity_id,ssi.product_id
max(ssi.parent_id) example_shipment_id,max(ssi.product_id) example_product_id
,ssi.sku as prod_sku,max(ce_vc.value) as product_name # 9/27
 ,max(aovcolor.value) color_title
 ,sum(ssi.qty) quantity #9/27 pull qty from configurable prod

FROM sales_shipment_item ssi
JOIN catalog_product_entity pconf ON pconf.entity_id=ssi.product_id AND pconf.type_id ='configurable'

UNION
## Second set, pull from simple prod
SELECT
#     ,ss.entity_id,o.updated_at, o.entity_id,ssi.product_id
max(ssi.parent_id) example_shipment_id,max(ssi.product_id) example_product_id
,ssi.sku as prod_sku,max(ce_vc.value) as product_name # 9/27
 ,max(aovcolor.value) color_title
 ,sum(COALESCE(ssi_conf.qty,ssi.qty)) quantity #9/27 pull qty from configurable prod, if any

FROM sales_shipment_item ssi
JOIN catalog_product_entity psimp ON psimp.entity_id=ssi.product_id AND psimp.type_id ='simple'

Because FE MS orders has sales_shipment_item storing prod-id of CONFIGURABLE PROD, we need to union. In the future we might want to rewrite either FE or BE to make sure they are consistent.