I'm trying to modify the following MySQL query for Magento. What it does now is returns product information for simple and configurable products for a report. Unfortunately, the simple products don't have all the info that the parent configurable products have.
What I'd like it to do is use the table catalog_product_super_link to look up the parent product id of every product so that for all products, I'm getting the data of the parent configurable product. The super link table has the values for all product id's in one column, and the corresponding parent in another. For configurable products, the product id and parent id are the same.
Make sense?
The query I've got is:
SELECT IFNULL(category_name.value, category_default_name.value) AS `primarycategory`
, `product`.`sku`
, IFNULL(product_name.value, product_default_name.value) AS `product_name`
, `attribute_option_value`.`value` AS `story`
, (
select GROUP_CONCAT(category_name.value)
from catalog_category_entity as category
, catalog_category_entity_varchar as category_name
, catalog_category_product as category_product
WHERE category_name.entity_id = category.entity_id
AND category_name.store_id = 0
AND category_name.entity_type_id = 3
AND category_name.attribute_id = 33
AND category_product.product_id = order_item.product_id
AND category_product.category_id = category.entity_id
AND category_name.value<>'Default Category'
) AS `category`
, round(sum(order_item.qty_ordered)) AS `unitssoldnum`
, ROUND(sum(order_item.price-order_item.discount_amount-order_item.tax_amount)
, 2
) AS `unitssoldcur`
, ROUND((
SUM(order_item.price-order_item.discount_amount-order_item.tax_amount)
- (IFNULL(product_cost.value, product_default_cost.value) * SUM(order_item.qty_ordered))
)
, 2
) AS `grossmargincur`
, ROUND((
(
SUM(order_item.price-order_item.discount_amount-order_item.tax_amount)
- (IFNULL(product_cost.value, product_default_cost.value) * SUM(order_item.qty_ordered))
)
/ (SUM(order_item.price-order_item.discount_amount-order_item.tax_amount))
)
* 100
, 2
) AS `grossmarginpercent`
, (
SELECT round(SUM(qty))
FROM cataloginventory_stock_item AS stock
, catalog_product_super_link AS product_link
WHERE product_link.product_id = stock.product_id
AND product_link.parent_id=order_item.product_id
) AS `sohatstore`
, (
SELECT round(SUM(qty)/SUM(order_item.qty_ordered))
FROM cataloginventory_stock_item AS stock
, catalog_product_super_link AS product_link
WHERE product_link.product_id = stock.product_id
AND product_link.parent_id=order_item.product_id
) AS `storecover`
, (
SELECT round(SUM(order_item.qty_ordered)
/(SUM(qty)+SUM(order_item.qty_ordered))
*100
, 2
)
FROM cataloginventory_stock_item AS stock
, catalog_product_super_link AS product_link
WHERE product_link.product_id = stock.product_id
AND product_link.parent_id=order_item.product_id
) AS `sellthrupercent`
, ROUND(SUM(order_item.price),2) AS `originalretail`
, ROUND(SUM(IFNULL(product_cost.value, product_default_cost.value))
, 2
) AS `costextax`
, (
SELECT round(SUM(qty_ordered))
FROM sales_flat_order_item AS order_total
WHERE order_total.product_id = order_item.product_id
) AS `totalsoldtilldate`
, (
SELECT ROUND((
SELECT round(SUM(qty_ordered))
FROM sales_flat_order_item AS order_total
WHERE order_total.product_id = order_item.product_id
)
/(
(
SELECT round(SUM(qty_ordered))
FROM sales_flat_order_item AS order_total
WHERE order_total.product_id = order_item.product_id
)
+(
SELECT round(SUM(qty))
FROM cataloginventory_stock_item AS stock
, catalog_product_super_link AS product_link
WHERE product_link.product_id = stock.product_id
AND product_link.parent_id=order_item.product_id
)
)*100
, 2
)
) AS `totalsellthru`
, (
SELECT ROUND((
(
sum(order_total.price-order_total.discount_amount-order_total.tax_amount)
- sum(order_total.base_cost * order_total.qty_ordered)
)
/sum(order_total.price-order_total.discount_amount-order_total.tax_amount)
)*100
, 2
)
FROM sales_flat_order_item AS order_total
WHERE order_total.product_id = order_item.product_id
) AS `totalgrossmarginpercent`
FROM `sales_flat_order` AS `order`
INNER JOIN `sales_flat_order_item` AS `order_item` ON order_item.order_id = order.entity_id
INNER JOIN `catalog_product_entity` AS `product`
ON product.entity_id = order_item.product_id
AND product.entity_type_id = 4
AND product.type_id NOT IN('grouped', 'bundle')
LEFT JOIN `catalog_product_entity_varchar` AS `product_name`
ON product_name.entity_id = product.entity_id
AND product_name.store_id = order.store_id
AND product_name.entity_type_id = 4
AND product_name.attribute_id = 60
LEFT JOIN `catalog_product_entity_varchar` AS `product_default_name`
ON product_default_name.entity_id = product.entity_id
AND product_default_name.store_id = 0
AND product_default_name.entity_type_id = 4
AND product_default_name.attribute_id = 60
LEFT JOIN `catalog_product_entity_decimal` AS `product_cost`
ON product_cost.entity_id = product.entity_id
AND product_cost.store_id = order.store_id
AND product_cost.entity_type_id = 4
AND product_cost.attribute_id = 68
LEFT JOIN `catalog_product_entity_decimal` AS `product_default_cost`
ON product_default_cost.entity_id = product.entity_id
AND product_default_cost.store_id = 0
AND product_default_cost.entity_type_id = 4
AND product_default_cost.attribute_id = 68
LEFT JOIN `catalog_category_entity` AS `category`
ON category.entity_id = (select MAX(category_id) from catalog_category_product where product_id = product.entity_id)
AND category.entity_type_id = 3
LEFT JOIN `catalog_category_entity_varchar` AS `category_name`
ON category_name.entity_id = category.parent_id
AND category_name.store_id = order.store_id
AND category_name.entity_type_id = 3
AND category_name.attribute_id = 33
LEFT JOIN `catalog_category_entity_varchar` AS `category_default_name`
ON category_default_name.entity_id = category.parent_id
AND category_default_name.store_id = 0
AND category_default_name.entity_type_id = 3
AND category_default_name.attribute_id = 33
LEFT JOIN `catalog_product_entity_int` AS `story_name`
ON story_name.entity_id = product.entity_id
AND story_name.store_id = 1
AND story_name.entity_type_id = 4
AND story_name.attribute_id = 138
LEFT JOIN `catalog_product_entity_int` AS `story_default_name`
ON story_default_name.entity_id = product.entity_id
AND story_default_name.store_id = 0
AND story_default_name.entity_type_id = 4
AND story_default_name.attribute_id = 138
LEFT JOIN `eav_attribute_option` AS `attribute_option`
ON attribute_option.option_id = IFNULL(story_name.value, story_default_name.value)
LEFT JOIN `eav_attribute_option_value` AS `attribute_option_value`
ON attribute_option_value.option_id = attribute_option.option_id
AND attribute_option_value.store_id = 0
WHERE (order.state <> 'canceled')
AND (DATE(order.created_at) <= DATE('2012-07-03'))
AND (DATE(order.created_at) >= DATE('2012-07-01'))
GROUP BY `product_name`
ORDER BY `category_name`.`value` ASC
, `category_default_name`.`value` ASC
, `product_name`.`value` ASC
, `product_default_name`.`value` ASC
If anyone could point me in the right direction it would be greatly appreciated :)
Here's a sample of the output I get for configurable products:
"Category Name",Product,Name,Story,"Category Description","Units Sold (#)","Units Sold ($)","GM ($)","GM (%)","SOH at Store","Store Cover","Sell through %","Original Retail Price","Cost Ex Tax","Total Units Sold (#)","Total Sell Thru %","GM% Total"
Bottoms,31875,"Denim Babe Jean",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$39.25,65.59,118,59,1.67,$68.00,$10.30,81,40.70,69.38
Bottoms,34475,"Diamante Rip Boot Leg Jean",DENIM,"Bottoms,SALE,Denim,Jeans,Bootleg Jean,Pants",2,$29.92,-$7.56,-25.25,28,14,6.67,$34.00,$18.74,5,15.15,19.90
Bottoms,35853,"Embroidered Back Wide Leg Jean",DENIM,"Bottoms,SALE,Denim,Jeans,Wideleg Jean,Pants",2,$29.92,-$9.08,-30.35,11,6,15.38,$34.00,$19.50,22,66.67,23.93
Bottoms,34278,"Firecracker Bootleg Jean",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$29.92,50.00,9,5,18.18,$68.00,$14.96,39,81.25,43.99
Bottoms,28450,"Glamourzon Jean","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$59.84,100.00,101,51,1.94,$68.00,$0.00,51,33.55,100.00
And here's a sample of what I get with simple products. Notice all the missing data. That's why for my report I want to look up the parent product id and use it's data.
"Category Name",Product,Name,Story,"Category Description","Units Sold (#)","Units Sold ($)","GM ($)","GM (%)","SOH at Store","Store Cover","Sell through %","Original Retail Price","Cost Ex Tax","Total Units Sold (#)","Total Sell Thru %","GM% Total"
Bottoms,2000004614430,"Denim Babe Jean - Dark Denim - 24",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$20.59,,,,,$0.00,$10.30,15,,
Bottoms,2000004901523,"Diamante Rip Boot Leg Jean - Black - 14",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$37.48,,,,,$0.00,$18.74,2,,
Bottoms,2000004883881,"Firecracker Bootleg Jean - Denim Dark - 16",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$29.92,,,,,$0.00,$14.96,9,,
Bottoms,2000004159955,"Glamourzon Jean - Dark Denim - 24","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,$0.00,,,,,$0.00,$0.00,11,,
Bottoms,2005010849432,"Glamourzon Night Jean - Black - 28","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$32.80,,,,,$0.00,$16.40,7,,-68.82
I hope that answered your question. Thanks!
