SELECT p.id AS 'Product ID',
p.barcode AS 'Barcode',
p.name AS 'Product Name',
b.name AS 'Brand Name',
m.name AS 'Merchant Name',
m.description AS 'Merchant Name 2',
c.cat_1 AS 'Primary Category',
c.cat_2 AS 'Sub Category',
so.transaction_id AS 'Transaction ID',
so.order_date AS 'Confirm Date',
so.claim_date AS 'Claim Date',
so.status as 'Current Status',
so.seven_connect_ref_num AS 'Seven Connect Ref Num',
CAST(so.delivery_location_id AS SIGNED) AS 'Store ID',
rn.store_name AS 'Store Name',
so.total_price AS 'Peso Paid',
IFNULL(pp.amount, pp2.amount) AS 'Product Price',
IFNULL(pc.amount, pc2.amount) AS 'Product Cost',
IF(so.is_point_cash = 1, MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END), 0) as 'Points Used',
IFNULL(cast(so.is_wallet AS SIGNED),0) AS 'Is_Wallet',
soi.quantity AS 'Volume',
CAST(so.is_cod AS SIGNED) AS 'Is_COD',
IFNULL(so.coupon_code,'N/A') AS 'Coupon Code',
CAST(cu.mobile_number AS SIGNED) AS 'Mobile Number',
IFNULL(CAST(is_point_cash AS SIGNED),0) AS 'Is_Pts+peso',
so.requesting_device AS 'Requesting Device',
CASE
    WHEN so.is_wallet = 1 AND so.is_point_cash = 1 THEN MAX(CASE WHEN sop.wallet_type = 'CLIQQWALLET' THEN sop.amount END) + MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END)
    WHEN so.is_wallet = 1 AND so.is_point_cash IS NULL THEN MAX(CASE WHEN sop.wallet_type = 'CLIQQWALLET' THEN sop.amount END)
    WHEN (so.is_wallet = 0 OR so.is_wallet IS NULL) AND so.is_point_cash = 1 THEN so.total_price + MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END)
    WHEN (so.is_wallet = 0 OR so.is_wallet IS NULL) AND so.is_point_cash IS NULL THEN so.total_price
END as 'Total Payment',
CASE
    WHEN so.is_wallet = 1 AND so.is_point_cash = 1 THEN MAX(CASE WHEN sop.wallet_type = 'CLIQQWALLET' THEN sop.amount END) + MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END)
    WHEN so.is_wallet = 1 AND so.is_point_cash IS NULL THEN MAX(CASE WHEN sop.wallet_type = 'CLIQQWALLET' THEN sop.amount END)
    WHEN (so.is_wallet = 0 OR so.is_wallet IS NULL) AND so.is_point_cash = 1 THEN so.total_price + MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END)
    WHEN (so.is_wallet = 0 OR so.is_wallet IS NULL) AND so.is_point_cash IS NULL THEN so.total_price
END - so.total_price - IF(so.is_point_cash = 1, MAX(CASE WHEN sop.currency = 'LPTS' THEN sop.amount END), 0) as 'Wallet Credits Used'

FROM ecms.sales_order so
LEFT JOIN ecms.sales_order_item soi ON soi.sales_order_id = so.id
LEFT JOIN ecms.sales_order_payment sop ON sop.sales_order_id = so.id
LEFT JOIN ecms.customer cu ON cu.id = so.customer_id
LEFT JOIN ecms.history h ON h.transaction_id = so.transaction_id
LEFT JOIN ecms.product p ON p.id = soi.product_id
LEFT JOIN ecms.product_price pp ON pp.product_id = p.id AND pp.date_created <= so.date_created AND so.date_created BETWEEN COALESCE(pp.from_date, so.date_created) AND COALESCE(pp.thru_date, so.date_created)
LEFT JOIN ecms.product_price pp2 ON pp2.product_id = p.id
LEFT JOIN ecms.product_cost pc ON pc.product_id = p.id AND pc.date_created <= so.date_created AND so.date_created BETWEEN COALESCE(pc.from_date, so.date_created) AND COALESCE(pc.thru_date, so.date_created)
LEFT JOIN ecms.product_cost pc2 ON pc2.product_id = p.id
LEFT JOIN ecms.merchant m ON m.id = p.merchant_id
LEFT JOIN ecms.brand b ON b.id = p.brand_id
LEFT JOIN ecms.route_number rn ON rn.facility_id = so.delivery_location_id
LEFT JOIN (
SELECT

c0.id,
IFNULL(c1.name, c0.name) as cat_1,
c0.name as cat_2

FROM ecms.category c0
LEFT JOIN ecms.category_rollup cr ON category_id = c0.id
LEFT JOIN ecms.category c1 ON c1.id = cr.parent_category_id
)c ON c.id = p.category_id

WHERE so.order_type = 'ITEM FULFILLMENT'
AND so.status IN ('FOR CONFIRMATION','PL GEN COMPLETED','CONFIRMED','FOR SORTING','FOR DELIVERY','IN-TRANSIT','UNSERVED','LOST','DELIVERED TO STORE','CLAIMED','FOR PULL OUT','FOR RETURN TO MERCHANT','RETURNED TO MERCHANT','FOR REFUND','REFUNDED','DELIVERED TO WAREHOUSE_REGIONAL','IN-TRANSIT_REGIONAL','ARRIVED AT STORE','ARRIVED AT WAREHOUSE','ON VEHICLE TO STORE','ON VEHICLE TO WAREHOUSE')
AND p.name NOT IN ('CLiQQ Live Test Product','Live Test Product','711 TOUR Race Kit 48KM 500','711 TOUR Race Kit 106KM 900')

AND date(so.order_date) BETWEEN '2020-01-01 00:00:00' AND '2020-01-23 23:59:59'
AND so.requesting_device IN ('PWA','KIOSK','CLIQQ')

GROUP BY 9,1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,20,21,22,23,24,25,26