Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET sql_mode = ''
- SELECT
- DATE(DATE_ADD(main_table.created_at, INTERVAL 0 SECOND)) AS `period`,
- `main_table`.`store_id`,
- `main_table`.`status` AS `order_status`,
- `main_table`.`entity_id` AS `order_id`,
- `main_table`.`increment_id` AS `order_increment_id`,
- `main_table`.`created_at` AS `order_date`,
- IFNULL(item2.product_id,
- item2.product_id) AS `product_id`,
- `item2`.`name` AS `product_name`,
- item2.qty_ordered as ordered,
- IFNULL(item2.sku, catalog_product.sku) AS `sku`,
- IFNULL(manufacturer_value.value,
- "Not Set") AS `manufacturer`,
- `main_table`.`customer_id`,
- `main_table`.`customer_email`,
- IFNULL(CONCAT(main_table.customer_firstname, " ", main_table.customer_lastname),
- CONCAT(billing_address.firstname, " ",
- billing_address.lastname)) AS `customer_name`,
- `c_group`.`customer_group_code` AS `customer_group`,
- COALESCE(shipping_address.country_id, billing_address.country_id,
- "") AS `country`,
- COALESCE(shipping_address.region, billing_address.region,
- "") AS `region`,
- COALESCE(shipping_address.city, billing_address.city,
- "") AS `city`,
- COALESCE(shipping_address.postcode, billing_address.postcode,
- "") AS `zip_code`,
- COALESCE(shipping_address.street, billing_address.street,
- "") AS `address`,
- COALESCE(shipping_address.telephone, billing_address.telephone,
- "") AS `phone`,
- `main_table`.`coupon_code`,
- COALESCE(IFNULL(item2.qty_ordered, item2.qty_ordered),
- 0) AS `qty_ordered`,
- COALESCE(IFNULL(item2.qty_invoiced, item2.qty_invoiced),
- 0) AS `qty_invoiced`,
- COALESCE(IFNULL(item2.qty_shipped, item2.qty_shipped),
- 0) AS `qty_shipped`,
- COALESCE(IFNULL(item2.qty_refunded, item2.qty_refunded),
- 0) AS `qty_refunded`,
- COALESCE(IFNULL(item2.base_price, item2.base_price),
- 0) AS `item_price`,
- CASE item2.product_type
- WHEN "configurable"
- THEN COALESCE(SUM(item2.base_cost), item2.base_cost, 0)
- WHEN "bundle"
- THEN IF(bundle_item_price.price = 0, COALESCE(item2.base_cost, SUM(item2.base_cost), 0),
- COALESCE(SUM(item2.base_cost), 0))
- ELSE COALESCE(item2.base_cost,
- 0) END AS `item_cost`,
- COALESCE((IFNULL(item2.qty_ordered, item2.qty_ordered) * item2.base_price)) AS `subtotal`,
- COALESCE((IF(item2.base_discount_amount = 0, SUM(item2.base_discount_amount), item2.base_discount_amount)),
- 0) AS `discount`,
- COALESCE((IFNULL(item2.base_tax_amount, item2.base_tax_amount)), 0) AS `tax`,
- COALESCE((IFNULL(item2.base_row_total, item2.base_row_total) +
- IFNULL(item2.base_discount_tax_compensation_amount, 0.0000) +
- IFNULL(COALESCE(item2.base_weee_tax_applied_amount, 0), COALESCE(item2.base_weee_tax_applied_amount, 0)) -
- COALESCE(IF(item2.base_discount_amount = 0, SUM(item2.base_discount_amount), item2.base_discount_amount), 0)),
- 0) AS `total`,
- (IFNULL(item2.base_row_total, item2.base_row_total) + IFNULL(item2.base_tax_amount, item2.base_tax_amount) +
- IFNULL(item2.base_discount_tax_compensation_amount, 0.0000) +
- IFNULL(COALESCE(item2.base_weee_tax_applied_amount, 0), COALESCE(item2.base_weee_tax_applied_amount, 0)) -
- COALESCE(IF(item2.base_discount_amount = 0, SUM(item2.base_discount_amount), item2.base_discount_amount),
- 0)) AS `total_incl_tax`,
- COALESCE((COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_row_invoiced), NULL),
- item2.base_row_invoiced, item2.base_row_invoiced) +
- IFNULL(COALESCE(item2.base_discount_tax_compensation_invoiced, 0),
- COALESCE(item2.base_discount_tax_compensation_invoiced, 0)) -
- COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_discount_invoiced), NULL),
- item2.base_discount_invoiced, item2.base_discount_invoiced)),
- 0) AS `invoiced`,
- COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_tax_invoiced), NULL),
- item2.base_tax_invoiced, item2.base_tax_invoiced,
- 0) AS `tax_invoiced`,
- COALESCE((COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_row_invoiced), NULL),
- item2.base_row_invoiced, item2.base_row_invoiced) +
- IFNULL(COALESCE(item2.base_discount_tax_compensation_invoiced, 0),
- COALESCE(item2.base_discount_tax_compensation_invoiced, 0)) +
- COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_tax_invoiced), NULL),
- item2.base_tax_invoiced, item2.base_tax_invoiced) -
- COALESCE(IF(item2.product_type = "bundle" AND item2.base_price > 0, SUM(item2.base_discount_invoiced), NULL),
- item2.base_discount_invoiced, item2.base_discount_invoiced)),
- 0) AS `invoiced_incl_tax`,
- COALESCE(((IF((IFNULL(item2.qty_refunded, item2.qty_refunded) > 0), 1, 0) *
- ((IFNULL(item2.qty_refunded, item2.qty_refunded) / IFNULL(item2.qty_invoiced, item2.qty_invoiced)) *
- (IFNULL(item2.qty_invoiced, item2.qty_invoiced) * IFNULL(item2.base_price, item2.base_price) - ABS(COALESCE(
- IF(
- item2.base_discount_amount
- =
- 0,
- SUM(
- item2.base_discount_amount),
- item2.base_discount_amount),
- 0)))))),
- 0) AS `refunded`,
- COALESCE(IF((IFNULL(item2.qty_refunded, item2.qty_refunded) > 0),
- (IFNULL(item2.qty_refunded, item2.qty_refunded) / IFNULL(item2.qty_invoiced, item2.qty_invoiced) *
- IFNULL(item2.base_tax_invoiced, item2.base_tax_invoiced)), 0),
- 0) AS `tax_refunded`,
- COALESCE(((IF((IFNULL(item2.qty_refunded, item2.qty_refunded) > 0), 1, 0) * ((IFNULL(item2.qty_refunded,
- item2.qty_refunded) * (
- IFNULL(item2.qty_invoiced,
- item2.qty_invoiced) *
- IFNULL(item2.base_price,
- item2.base_price) - ABS(
- COALESCE(IF(
- item2.base_discount_amount
- = 0, SUM(
- item2.base_discount_amount),
- item2.base_discount_amount),
- 0))) /
- IFNULL(item2.qty_invoiced,
- item2.qty_invoiced)) + IF((IFNULL(
- item2.qty_refunded,
- item2.qty_refunded)
- > 0), (
- IFNULL(
- item2.qty_refunded,
- item2.qty_refunded)
- /
- IFNULL(
- item2.qty_invoiced,
- item2.qty_invoiced)
- *
- IFNULL(
- item2.base_tax_invoiced,
- item2.base_tax_invoiced)),
- 0)))),
- 0) AS `refunded_incl_tax`,
- `main_table`.`base_to_global_rate` AS `to_global_rate`
- FROM `sales_order` AS `main_table`
- INNER JOIN `sales_order_item` AS `item` ON (item2.order_id = main_table.entity_id AND item2.parent_item_id IS NULL)
- LEFT JOIN `sales_order_item` AS `item2` ON (
- item2.order_id = main_table.entity_id AND item2.parent_item_id IS NOT NULL AND item2.parent_item_id = item2.item_id
- AND item2.product_type IN ("configurable", "bundle"))
- LEFT JOIN (SELECT
- `bundle_items`.`parent_item_id`,
- `bundle_items`.`order_id`,
- Sum(`bundle_items`.`price`) AS price,
- Sum(`bundle_items`.`base_cost`) AS items_cost
- FROM `sales_order_item` AS `bundle_items`
- INNER JOIN `sales_order_item` AS `bundle`
- ON bundle_items.parent_item_id = bundle.item_id AND bundle.product_type IN ("bundle")
- GROUP BY `bundle_items`.`parent_item_id`,
- `bundle_items`.`order_id`) AS `bundle_item_price`
- ON (main_table.entity_id = bundle_item_price.order_id AND item2.item_id = bundle_item_price.parent_item_id)
- LEFT JOIN `catalog_product_entity` AS `catalog_product` ON item2.product_id = catalog_product.entity_id
- LEFT JOIN `customer_group` AS `c_group` ON IFNULL(main_table.customer_group_id, 0) = c_group.customer_group_id
- LEFT JOIN `sales_order_address` AS `shipping_address`
- ON shipping_address.parent_id = main_table.entity_id AND shipping_address.address_type = "shipping"
- LEFT JOIN `sales_order_address` AS `billing_address`
- ON billing_address.parent_id = main_table.entity_id AND billing_address.address_type = "billing"
- LEFT JOIN `catalog_product_entity_int` AS `item_manufacturer`
- ON item_manufacturer.entity_id = item2.product_id AND item_manufacturer.attribute_id = 83
- LEFT JOIN `eav_attribute_option_value` AS `manufacturer_value`
- ON item_manufacturer.value = manufacturer_value.option_id AND manufacturer_value.store_id = 0
- WHERE (item2.product_type = 'bundle') and item.order_id = 4
- GROUP BY `item2`.`item_id`
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement