EXPLAIN SELECT
sales_date
, merchant
, merchant_name
, package_class AS package__class
, rate_type AS rate__type
, package_desc AS package__desc
, SUM(order_grouped_shipping) + SUM(ticket_shipping) AS net_shipping
, SUM(ticket_fee) AS fee_amount
, SUM(order_grouped_cart_adjustment) AS net_cadj
, (
SUM(order_grouped_shipping) + SUM(ticket_shipping) + SUM(ticket_fee) + SUM(order_grouped_fee) + SUM(order_grouped_cart_adjustment) + SUM(ticket_tax) + SUM(ticket_retail)
) AS order_total
, SUM(ticket_count) AS ticket_count
, SUM(ticket_tax) AS tax_amount
, SUM(ticket_retail) AS retail
, SUM(order_grouped_fee) AS refund_adjustment
FROM
(
SELECT
DATE_FORMAT(o.local_order_date,'%m-%d-%Y')AS sales_date
, merchant
, merchant_name
, package_class
, rate_type
, package_desc
, oi.master_ticket_id
, oi.ticket_id
, SUM(
IF(
oi.type = 'T'
, IF(oi.action = 'R', - 1, 1)
, 0
)
) AS ticket_count
, SUM(
IF(
oi.type = 'S'
AND ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) * (
order_stats.package_tickets / order_stats.order_tickets
) AS order_grouped_shipping
, SUM(
IF(
oi.type = 'S'
AND NOT ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) AS ticket_shipping
, SUM(
IF(
oi.type = 'F'
AND ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) * (
order_stats.package_tickets / order_stats.order_tickets
) AS order_grouped_fee
, SUM(
IF(
oi.type = 'F'
AND NOT ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) AS ticket_fee
, 0 * SUM(
IF(
oi.type = 'A'
AND ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) * (
order_stats.package_tickets / order_stats.order_tickets
) AS order_grouped_cart_adjustment
, 0 * SUM(
IF(
oi.type = 'A'
AND NOT ISNULL(oi.ticket_id)
, oi.retail_amount
, 0
)
) AS ticket_cart_adjustment
, SUM(
IF(
oi.type = 'T'
AND NOT ISNULL(oi.ticket_id)
, th.tax_amount * IF(oi.action = 'R', - 1, 1)
, 0
)
) AS ticket_tax
, SUM(
IF(
oi.type = 'T'
, th.retail_amount * IF(oi.action = 'R', - 1, 1)
, 0
)
) AS ticket_retail
FROM
order_items oi
INNER JOIN ticket_order_header o
ON oi.master_ticket_id = o.master_ticket_id
inner join merchant m1 on o.merchant_id = m1.merchant_id
LEFT JOIN ticket_header th
ON (oi.ticket_id = th.ticket_id OR oi.type = 'A')
AND oi.master_ticket_id = th.master_ticket_id
INNER JOIN
(SELECT
o2.master_ticket_id
, th2.ticket_id
, IF(
get_ticket_count_by_order (o2.master_ticket_id) = 0
, COUNT(DISTINCT th2.ticket_id)
, COUNT(
DISTINCT IF(
EXISTS
(SELECT
*
FROM
merchant_package_characs mpc
WHERE mpc.merchant_id = th2.merchant_id
AND mpc.package_id = th2.package_id
AND mpc.name = 'package_type'
AND mpc.value = 'Merchandise')
, NULL
, th2.ticket_id
)
)
) AS package_tickets
, IF(
get_ticket_count_by_order (o2.master_ticket_id) = 0
, COUNT(DISTINCT th2.ticket_id)
, get_ticket_count_by_order (th2.master_ticket_id)
) AS order_tickets
, o2.merchant_id AS merchant
, m.name AS merchant_name
, package_class
, customer_type_name AS rate_type
, package_desc
FROM
ticket_order_header o2
INNER JOIN ticket_header th2
ON o2.master_ticket_id = th2.master_ticket_id
INNER JOIN package_header
ON th2.package_id = package_header.package_id
INNER JOIN customer_type
ON th2.customer_type = customer_type.customer_type
INNER JOIN merchant m ON o2.merchant_id = m.merchant_id
WHERE (o2.merchant_id IN (6234) or m.parent_econsignment_merchant_id IN (0) )
AND order_date >= '2012-01-01'
AND order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
GROUP BY o2.master_ticket_id, th2.ticket_id) order_stats
ON order_stats.master_ticket_id = o.master_ticket_id
AND (
(
ISNULL(order_stats.ticket_id)
AND ISNULL(th.ticket_id)
)
OR order_stats.ticket_id = th.ticket_id
)
WHERE (o.merchant_id IN (6234) or m1.parent_econsignment_merchant_id IN (0))
AND o.local_order_date >= '2012-01-01'
AND o.local_order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
GROUP BY oi.master_ticket_id
, oi.ticket_id
) AS main
GROUP BY main.merchant
, main.package_class
, main.rate_type
, main.package_desc
LIMIT 0, 1500