SELECT li.account_id AS account_id, li.id AS id, li.name AS name, li.description AS lot_desc, li.item_num AS item_num, li.item_num_ext AS item_num_ext, li.hammer_price AS hammer_price, li.low_estimate AS low_est, li.high_estimate AS high_est, li.warranty AS warranty, ali.id AS alid, ali.lot_num_prefix AS num_prefix, ali.lot_num AS num, ali.lot_num_ext AS num_ext, ali.quantity AS qty, IF (ali.quantity_x_money = true,1,0) AS qty_x_money, ali.lot_status_id AS lot_status, alic.starting_bid_normalized AS starting_bid_normalized, alic.current_bid AS current_bid, alic.current_bidder_id AS current_bidder_id, alic.current_max_bid AS current_max_bid, alic.asking_bid AS asking_bid, alic.bid_count AS bid_count, UNIX_TIMESTAMP(IF(a.auction_type = 'L', a.start_date_gmt, IF(a.extend_all, a.start_date_gmt, alic.start_date_gmt))) - UNIX_TIMESTAMP('2016-09-16 20:52:12') AS seconds_before, UNIX_TIMESTAMP(IF(a.auction_type = 'L', a.end_date_gmt, IF(a.extend_all, IF(IFNULL(a.stagger_closing, 0), SUBDATE(a.end_date_gmt, INTERVAL FLOOR((ac.total_lots - ali.order) / a.lots_per_interval) * a.stagger_closing MINUTE), a.end_date_gmt), alic.end_date_gmt))) - UNIX_TIMESTAMP('2016-09-16 20:52:12') AS seconds_left, a.id AS aid, a.sale_no AS sale_no, a.sale_no_ext AS sale_no_ext, a.name AS auc_name, a.auction_status_id AS auc_status, a.auction_type AS auc_type, a.event_type AS event_type, a.start_date AS auc_st_dt, a.end_date AS auc_en_dt, a.start_timezone_id AS auc_st_tz, a.end_timezone_id AS auc_en_tz, a.stagger_closing AS stagger_closing, IF(a.reverse, 1, 0) AS auc_reverse, a.bidding_paused AS bidding_paused, a.absentee_bids_display AS absentee_bids_display, a.above_starting_bid AS above_starting_bid, a.reserve_not_met_notice AS reserve_not_met_notice, a.extend_all AS extend_all, toi.id AS toiid, IF(a.auction_type = 'L', a.start_date_gmt, IF(a.extend_all, a.start_date_gmt, alic.start_date_gmt)) AS lot_st_dt, IF(a.auction_type = 'L', a.end_date_gmt, IF(a.extend_all, IF(IFNULL(a.stagger_closing, 0), SUBDATE(a.end_date_gmt, INTERVAL FLOOR((ac.total_lots - ali.order) / a.lots_per_interval) * a.stagger_closing MINUTE), a.end_date_gmt), alic.end_date_gmt)) AS lot_en_dt, IF (a.auction_type = 'T', toi.buy_now_amount, loi.buy_now_amount) AS buy_amount, IF (toi.no_bidding, 1, 0) AS no_bidding, IF (toi.best_offer, 1, 0) AS best_offer, IF(a.reverse=0x00 OR a.reverse IS NULL, IFNULL(toi.pay_per_bid_amount, 0), 0) AS pay_per_bid_amount, IF (toi.pay_per_bid_increment, toi.pay_per_bid_increment, 0) AS pay_per_bid_increment, IF(a.auction_type = 'T', a.end_date_gmt, a.start_date_gmt) AS auc_end_date, ali.lot_status_id AS status_id, (SELECT IF (a.auction_type = 'T', (IF (((SELECT max_bid FROM bid_transaction WHERE auction_id = ali.auction_id AND lot_item_id = li.id AND (deleted IS NULL OR deleted = false) AND user_id = 1 AND user_id > 0 ORDER BY id DESC LIMIT 1)) is null, NULL, (SELECT max_bid FROM bid_transaction WHERE auction_id = ali.auction_id AND lot_item_id = li.id AND (deleted IS NULL OR deleted = false) AND user_id = 1 AND user_id > 0 ORDER BY id DESC LIMIT 1))), (IFNULL(ab.max_bid, 0)))) AS max_bid, IF (a.auction_type = 'T', IF (alic.start_date_gmt >= '2016-09-16 20:52:12' ,2, IF (alic.end_date_gmt > '2016-09-16 20:52:12', if (ali.lot_status_id = 1,1,3), 3) ), IF (alic.start_date_gmt >= '2016-09-16 00:00:00' AND a.auction_status_id = 1 ,2, IF (a.auction_status_id = 2 OR a.auction_status_id = 6, if (ali.lot_status_id = 1,1,3), 3) ) ) AS auc_status_order, IF (li.hammer_price > 0, li.hammer_price, (SELECT IF (a.auction_type = 'T', (IF (ali.current_bid_id IS NULL, li.starting_bid, (SELECT bid FROM bid_transaction WHERE id = ali.current_bid_id))), (li.low_estimate)))) as price, acc.company_name AS acct_company_name, acc.name AS acct_name , IF (a.require_lot_change_confirmation=0x00 OR a.require_lot_change_confirmation IS NULL, false , true ) AS rlcc , ali.listing_only OR a.listing_only AS listing , a.event_type AS event_type , li.hammer_price AS hammer_price , li.reserve_price AS reserve_price , ali.lot_status_id AS lot_status_id , ali.lot_status_id AS status_id , ali.is_bulk_master AS is_bulk_master , ali.bulk_master_id AS bulk_master_id , alic.bulk_master_asking_bid AS bulk_master_asking_bid , li.winning_bidder_id AS winning_bidder_id ,alic.starting_bid_normalized AS starting_bid_normalized ,alic.current_bid AS current_bid ,alic.current_bidder_id AS current_bidder_id ,alic.current_max_bid AS current_max_bid ,alic.asking_bid AS asking_bid ,alic.bid_count AS bid_count ,alic.bulk_master_asking_bid AS bulk_master_asking_bid , IFNULL((SELECT max_bid FROM bid_transaction WHERE auction_id = ali.auction_id AND lot_item_id = ali.lot_item_id AND (deleted IS NULL OR deleted = false) AND failed = 0 AND user_id = 1 AND user_id > 0 ORDER BY id DESC LIMIT 1), NULL) AS max_bid_timed , a.absentee_bids_display AS absentee_bids_display , a.allow_force_bid AS allow_force_bid , a.listing_only AS auction_listing , li.auction_id AS winning_auction_id , IFNULL(toi.buy_now_amount, 0) as buy_now_amount , IFNULL(loi.buy_now_amount, 0) AS buy_amount , IFNULL(ab.max_bid, null) AS max_bid, IFNULL(ab.or_id, '') AS or_bid , ali.listing_only AS listing , ali.auction_id AS aid , a.auction_type AS auction_type , ali.auction_id AS auction_id , a.auction_status_id AS auction_status_id , li.changes AS changes FROM auction_lot_item AS ali INNER JOIN lot_item AS li ON ali.lot_item_id = li.id INNER JOIN account AS acc ON acc.id = li.account_id AND acc.active = 0x01 INNER JOIN auction AS a ON ali.auction_id = a.id AND a.published = true AND a.auction_status_id in (1,2,3,6) INNER JOIN auction_cache AS ac ON a.id = ac.auction_id LEFT JOIN bid_transaction AS bt ON bt.id = ali.current_bid_id LEFT JOIN timed_online_item AS toi ON toi.auction_id = ali.auction_id AND toi.lot_item_id = li.id LEFT JOIN live_online_item AS loi ON loi.auction_id = ali.auction_id AND loi.lot_item_id = li.id LEFT JOIN absentee_bid AS ab ON ab.auction_id = ali.auction_id AND ab.user_id = 1 AND ab.lot_item_id = li.id LEFT JOIN auction_lot_item_cache alic ON alic.auction_lot_item_id = ali.id WHERE li.active = true AND IF(a.`only_ongoing_lots`, ali.lot_status_id = 1, ali.lot_status_id IN (1,2,3,6)) AND (IF (((a.not_show_upcoming_lots = 1 OR a.only_ongoing_lots = 1) AND a.auction_type ='T'), IF(a.auction_type = 'L', a.start_date_gmt, IF(a.extend_all, a.start_date_gmt, alic.start_date_gmt)) < '2016-09-16 20:52:12', 1=1) AND ali.lot_status_id IN (1,2,3,6)) AND ali.lot_status_id in (1,2,3,6) AND (0 OR a.auction_type = 'L' OR a.auction_type = 'T' ) ORDER BY auc_status_order, CASE auc_status_order WHEN 1 THEN auc_end_date END ASC, CASE auc_status_order WHEN 2 THEN alic.start_date_gmt END ASC, CASE auc_status_order WHEN 3 THEN auc_end_date END DESC, ali.`order` ASC, a.start_date desc, a.sale_no, ali.lot_num_prefix, ali.lot_num, ali.lot_num_ext LIMIT 0,12
Comments