SELECT consignor, sold_at_auction, sold_in_bulk, unsold, FORMAT(total_hammer,2) hp, FORMAT(hammer_collected,2) hp_paid, FORMAT(retail_of_sold,2) retail, FORMAT(cost_of_sold,2) cogs, FORMAT(bp,2) bp, FORMAT(bp_collected,2) bp_paid, FORMAT(fees,2) fees, FORMAT(fees_collected,2) fees_paid, FORMAT(tax,2) tax, FORMAT(total_hammer+bp+fees-cost_of_sold,2) revenue, CONCAT(ROUND(100*(total_hammer+bp+fees-cost_of_sold)/cost_of_sold),'%') margin, FORMAT(hammer_collected+bp_collected+fees_collected-cost_of_collected,2) revenue_collected

FROM (SELECT

u.username consignor,
SUM(ali.lot_status_id=3 AND ali.auction_id NOT IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941)) sold_at_auction,
SUM(ali.lot_status_id=3 AND ali.auction_id IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941)) sold_in_bulk,
SUM(ali.lot_status_id=2) unsold,
SUM(IF(ali.lot_status_id=3,li.hammer_price,0)) total_hammer,

SUM(IF(ali.lot_status_id=3 AND i.invoice_status_id=3,li.hammer_price,0)) hammer_collected,

SUM(IF(ali.lot_status_id=3,retail.numeric,0))/100 retail_of_sold,

SUM(IF(ali.lot_status_id=3,    
    IF(u.username LIKE "%home%" || u.username = "Rock and Republic" || u.username = "Hampstead" || u.username="united bids" || u.username="Acks Liquidation" || u.username="Qualitycloseouts",IF(ali.auction_id IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941) AND (ii.buyers_premium IS NULL OR ii.buyers_premium=0),IF(shipping.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND shipping.numeric <> 0 AND  pallets.numeric <> 0, shipping.numeric/pallets.numeric + 0.75*li.hammer_price, 0.75*li.hammer_price),IF(shipping.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND shipping.numeric <> 0 AND  pallets.numeric <> 0, shipping.numeric/pallets.numeric + 0.9*li.hammer_price, 0.9*li.hammer_price)),
        IF(u.username="Sams Club",0.2*retail.numeric/100,
            IF(cost.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND cost.numeric <> 0 AND pallets.numeric <> 0,IF(pallets_in_lot.numeric IS NULL,cost.numeric/pallets.numeric,pallets_in_lot.numeric*cost.numeric/pallets.numeric),
                IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Indianapolis",4500/31,
                    IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Greenville",5000/32,

                        IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Phoenix",15500/85,

                        IF(u.username="inmar" AND (cost.numeric IS NULL OR pallets.numeric IS NULL  OR cost.numeric = 0 OR pallets.numeric = 0),
                            IF(l.name="Indianapolis",6680/44,
                                IF(l.name="Greenville",6905/44,
                                    IF(l.name="Phoenix", 7400/44,
                                        IF(l.name="Nashville",6700/44,
                                            IF(l.name="Wilkes Barre",5055/36,
                                                IF(l.name="Atlanta", 6655/44,0)
                                            )
                                        )
                                   )
                             )
                        ),0)

                    )

                    )
                )
            )
        )
    ),    
0)) cost_of_sold,

SUM(IF(i.invoice_status_id=3 AND ali.lot_status_id=3,    
    IF(u.username LIKE "%home%" || u.username = "Rock and Republic" || u.username = "Hampstead" || u.username="united bids" || u.username="Acks Liquidation" || u.username="Qualitycloseouts",IF(ali.auction_id IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941) AND (ii.buyers_premium IS NULL OR ii.buyers_premium=0),IF(shipping.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND shipping.numeric <> 0 AND  pallets.numeric <> 0, shipping.numeric/pallets.numeric + 0.75*li.hammer_price, 0.75*li.hammer_price),IF(shipping.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND shipping.numeric <> 0 AND  pallets.numeric <> 0, shipping.numeric/pallets.numeric + 0.9*li.hammer_price, 0.9*li.hammer_price)),
        IF(u.username="Sams Club",0.2*retail.numeric/100,
            IF(cost.numeric IS NOT NULL AND pallets.numeric IS NOT NULL AND cost.numeric <> 0 AND pallets.numeric <> 0,IF(pallets_in_lot.numeric IS NULL,cost.numeric/pallets.numeric,pallets_in_lot.numeric*cost.numeric/pallets.numeric),
                IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Indianapolis",4500/31,
                    IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Greenville",5000/32,

                        IF((u.username="TRG" AND (cost.numeric IS NULL OR pallets.numeric IS NULL OR cost.numeric = 0 OR pallets.numeric = 0)) AND l.name="Phoenix",15500/85,

                        IF(u.username="inmar" AND (cost.numeric IS NULL OR pallets.numeric IS NULL  OR cost.numeric = 0 OR pallets.numeric = 0),
                            IF(l.name="Indianapolis",6680/44,
                                IF(l.name="Greenville",6905/44,
                                    IF(l.name="Phoenix", 7400/44,
                                        IF(l.name="Nashville",6700/44,
                                            IF(l.name="Wilkes Barre",5055/36,
                                                IF(l.name="Atlanta", 6655/44,0)
                                            )
                                        )
                                   )
                             )
                        ),0)

                    )

                    )
                )
            )
        )
    ),    
0)) cost_of_collected,

SUM(IF(i.invoice_status_id=3 AND ali.lot_status_id=3,shipping.numeric/pallets.numeric)) shipping_cost

SUM(IF(ali.lot_status_id=3 AND ii.buyers_premium IS NOT NULL AND ii.buyers_premium > 0, ii.buyers_premium, 0)) bp,
SUM(IF(i.invoice_status_id=3 AND ii.buyers_premium IS NOT NULL AND ii.buyers_premium > 0, ii.buyers_premium, 0)) bp_collected,

SUM(IF(ali.lot_status_id=3 AND ali.auction_id NOT IN (2842,2841,2840,2839,2838),7.5,IF(ali.lot_status_id=3 AND ali.auction_id IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941),5*pallets_in_lot.numeric,0))) fees,
SUM(IF(i.invoice_status_id=3 AND ali.auction_id NOT IN (2842,2841,2840,2839,2838),7.5,IF(ali.lot_status_id=3 AND ali.auction_id IN (2842,2841,2840,2839,2838,2935,2936,2937,2938,2939,2941),5*pallets_in_lot.numeric,0))) fees_collected,

SUM(IF(ali.lot_status_id=3 AND i.tax IS NOT NULL AND i.tax>0,(ii.hammer_price+ii.buyers_premium)*(ii.sales_tax/100),0)) tax

FROM auction_lot_item ali

INNER JOIN lot_item li ON (ali.lot_item_id=li.id)

INNER JOIN auction a ON (a.id=ali.auction_id)

LEFT JOIN user u ON (u.id=li.consignor_id)

LEFT JOIN location l ON (l.id=a.location_id)

LEFT JOIN lot_item_cust_data retail ON (retail.lot_item_id = li.id AND retail.lot_item_cust_field_id = 45 AND retail.active)

LEFT 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 pallets ON (pallets.lot_item_id = li.id AND pallets.lot_item_cust_field_id = 74 AND pallets.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)

LEFT JOIN lot_item_cust_data pallets_in_lot ON (pallets_in_lot.lot_item_id = li.id AND pallets_in_lot.lot_item_cust_field_id = 79 AND pallets_in_lot.active)

INNER JOIN invoice_item ii ON (ii.lot_item_id=li.id AND ii.active)

INNER JOIN invoice i ON (ii.invoice_id=i.id AND i.invoice_status_id NOT IN (5,6))

INNER JOIN payment p ON (p.tran_id=i.id AND p.tran_type='I')

INNER JOIN (SELECT tran_id, MAX(id) highest_id, MAX(created_on) last_payment FROM payment WHERE tran_type='I' GROUP BY tran_id) p2 ON (p2.tran_id=p.tran_id AND p2.last_payment=p.created_on AND p.id=p2.highest_id)

WHERE p.created_on {DateRange} AND i.invoice_status_id IN (3,4) AND i.account_id=19 AND li.active and ali.lot_status_id IN (3,6)

GROUP BY u.username WITH ROLLUP) Temp;