SELECT consignor, sold_at_auction, sold_in_bulk, FORMAT(hammer_collected,2) hp, FORMAT(retail_of_sold,2) retail, FORMAT(cost_of_sold,2) cogs, FORMAT(shipping_cost,2) shipping, FORMAT(bp_collected,2) bp, FORMAT(fees_collected,2) fees, FORMAT(tax,2) tax, CONCAT(ROUND(100*(hammer_collected+bp_collected+fees_collected-cost_of_sold)/cost_of_sold),'%') margin, FORMAT(hammer_collected+bp_collected+fees_collected-cost_of_collected-shipping_cost,2) revenue
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 IN (3,4),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" || u.username="Clean Bay Area",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, 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, 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 IN (3,4) 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" || u.username="Clean Bay Area",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, 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, 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 IN (3,4) AND ali.lot_status_id=3,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),0)) 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 IN (3,4) 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,2941),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 IN (3,4) AND ali.auction_id NOT IN (2842,2841,2840,2839,2838,2941),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 (SELECT payment.id,payment.tran_id,payment.tran_type,payment.created_on,payment.amount FROM payment INNER JOIN invoice ON (invoice.id=payment.tran_id) WHERE payment.amount > 0 AND payment.amount <> invoice.shipping_fees GROUP BY payment.created_on, payment.tran_id) p ON (p.tran_id=i.id AND p.tran_type='I')
INNER JOIN (SELECT payment.tran_id, MAX(payment.created_on) last_payment FROM payment INNER JOIN invoice ON (invoice.id=payment.tran_id) WHERE payment.tran_type='I' AND payment.amount > 0 AND payment.amount <> invoice.shipping_fees GROUP BY payment.tran_id) p2 ON (p2.tran_id=p.tran_id AND p2.last_payment=p.created_on)
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;
Comments