Pasted

SELECT consignor, bol, auction_pallets, cogs, shipping_cost, COUNT(lot_id) lots_sold, FORMAT(Revenue,2) Revenue, FORMAT(GREATEST(0, Total_Cost - Revenue),2) cash_in_play, FORMAT(Total_Cost,2) Total_Cost, FORMAT(Recovered,2) Cost_Of_Sold, ROUND(Total_Cost-Recovered,2) Cost_Unrecovered
 FROM
 (SELECT
 li.id lot_id,
 u.username consignor,
 bol.text bol,
 ROUND(pallets.numeric/100) auction_pallets,
 ROUND(cost.numeric/100) cogs,
 ROUND(IF(shipping.numeric,shipping.numeric,0)/100) shipping_cost,
 (
SELECT SUM(li2.hammer_price * 1.2 + 7.5) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
WHERE bol2.text=bol.text AND li2.hammer_price IS NOT NULL AND li2.active
) Revenue,
 (
SELECT SUM((cost2.numeric+IF(shipping2.numeric,shipping2.numeric,0))/pallets2.numeric) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71 AND bol2.active)
INNER JOIN lot_item_cust_data pallets2 ON (pallets2.lot_item_id = li2.id and pallets2.lot_item_cust_field_id = 74 AND pallets2.active)
INNER JOIN lot_item_cust_data cost2 ON (cost2.lot_item_id = li2.id and cost2.lot_item_cust_field_id = 76 AND cost2.active)
LEFT JOIN lot_item_cust_data shipping2 ON (shipping2.lot_item_id = li2.id and shipping2.lot_item_cust_field_id = 77 AND shipping2.active)
WHERE bol2.text=bol.text AND li2.hammer_price IS NOT NULL AND li2.active
) Recovered,
 ROUND((cost.numeric+IF(shipping.numeric,shipping.numeric,0))/100) Total_Cost
 FROM lot_item li
 LEFT JOIN user u ON (u.id=li.consignor_id)
 INNER JOIN lot_item_cust_data bol ON (bol.lot_item_id = li.id and bol.lot_item_cust_field_id = 71 AND bol.active)
 INNER JOIN lot_item_cust_data pallets ON (pallets.lot_item_id = li.id and pallets.lot_item_cust_field_id = 74 AND pallets.active)
 INNER JOIN lot_item_cust_data cost ON (cost.lot_item_id = li.id and cost.lot_item_cust_field_id = 76 AND cost.active)
 LEFT JOIN lot_item_cust_data shipping ON (shipping.lot_item_id = li.id AND shipping.lot_item_cust_field_id = 77 AND shipping.active)
 WHERE li.date_sold {DateRange} AND cost.numeric IS NOT NULL AND u.username NOT IN ('home depot','Rock and Republic','Acks Liquidation','Clean Bay Area','Qualitycloseouts','united bids')
 ) tmp
 GROUP BY consignor, bol, auction_pallets, cogs, shipping_cost
 UNION
 SELECT consignor, bol, auction_pallets, cogs, shipping_cost, lots_sold, FORMAT(Revenue,2) Revenue, FORMAT(GREATEST(0, Total_Cost - Revenue),2) cash_in_play, FORMAT(Total_Cost,2) Total_Cost, FORMAT(Recovered,2) Cost_Of_Sold, ROUND(Total_Cost-Recovered,2) Cost_Unrecovered
 FROM
 (SELECT
 COUNT(li3.id) lots_sold,
 'All' consignor,
 'All' bol,
 '-' auction_pallets,
 '-' cogs,
 '-' shipping_cost,
 (
SELECT SUM(li2.hammer_price * 1.2 + 7.5) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
WHERE bol2.text IN (SELECT bol4.text FROM lot_item li4 INNER JOIN lot_item_cust_data bol4 ON(bol4.lot_item_id = li4.id and bol4.lot_item_cust_field_id = 71) WHERE li4.hammer_price IS NOT NULL AND li4.date_sold {DateRange}) AND li2.hammer_price IS NOT NULL
) Revenue,
 (
SELECT SUM(cost2.numeric/pallets2.numeric) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
INNER JOIN lot_item_cust_data pallets2 ON (pallets2.lot_item_id = li2.id and pallets2.lot_item_cust_field_id = 74)
INNER JOIN lot_item_cust_data cost2 ON (cost2.lot_item_id = li2.id and cost2.lot_item_cust_field_id = 76)
WHERE bol2.text=bol3.text AND li2.hammer_price IS NOT NULL
) Recovered,
 '-' Total_Cost
 FROM lot_item li3
 LEFT JOIN user u3 ON (u3.id=li3.consignor_id)
 INNER JOIN lot_item_cust_data bol3 ON (bol3.lot_item_id = li3.id and bol3.lot_item_cust_field_id = 71)
 INNER JOIN lot_item_cust_data pallets3 ON (pallets3.lot_item_id = li3.id and pallets3.lot_item_cust_field_id = 74)
 INNER JOIN lot_item_cust_data cost3 ON (cost3.lot_item_id = li3.id and cost3.lot_item_cust_field_id = 76)
 WHERE li3.date_sold {DateRange} AND cost3.numeric IS NOT NULL AND u3.username NOT IN ('home depot','Rock and Republic','Acks Liquidation','Clean Bay Area','Qualitycloseouts','united bids')
 ) tmp2;