программерское - wtf

[airmax]

SELECT
	payments.id,
	IF(ISNULL(payments.payment_docnumstr) OR payments.payment_docnumstr='',payments.id,payments.payment_docnumstr) as docnum,
	w.type,
	payments.pm_params,
	off.code AS offline_code,
	ex.code AS ex_curr_code, 
IF (
  w.type = 0,
    IF (off.`currency_id` = 1, 
	`payments`.in_amount,
        `payments`.in_amount * (
        SELECT
            course
        FROM
            courses
        WHERE
             val1_id = off.`currency_id`
        AND `date` > ((FLOOR(`payments`.regdate / 3600)  )* 3600)
        AND `date` < ((FLOOR(`payments`.regdate / 3600)+1)* 3600)
        ORDER BY `date` DESC
        LIMIT 1 )
    ),
    IF (off.`currency_id` = 1,
        `payments`.out_amount,
        `payments`.out_amount * (
        SELECT
            back_course
        FROM
            courses
        WHERE
             val1_id = off.`currency_id`
        AND `date` > ((FLOOR(`payments`.regdate / 3600)  )* 3600)
        AND `date` < ((FLOOR(`payments`.regdate / 3600)+1)* 3600)
        ORDER BY `date` DESC
        LIMIT 1 )

    )
  ) AS trans_amount, 
	IF (w.type = 0,
	IF (off.`currency_id` = 1, 
		payments.sys_com,
		payments.sys_com * (
		        SELECT
		            course
		        FROM
		            courses
		        WHERE
		             val1_id = off.`currency_id`
		        AND `date` > ((FLOOR(`payments`.regdate / 3600)  )* 3600)
		        AND `date` < ((FLOOR(`payments`.regdate / 3600)+1)* 3600)
		        ORDER BY `date` DESC
		        LIMIT 1
		)
	) ,
	IF (ex.`currency_id` = 1, 
		payments.sys_com,
		payments.sys_com * (
		        SELECT
		            back_course
		        FROM
		            courses
		        WHERE
		             val1_id = ex.`currency_id`
		        AND `date` > ((FLOOR(`payments`.regdate / 3600)  )* 3600)
		        AND `date` < ((FLOOR(`payments`.regdate / 3600)+1)* 3600)
		        ORDER BY `date` DESC
		        LIMIT 1
		)
	) 
	) AS trans_comm, 
	IF ( 
		w.type =0, 'Ввод', 'Вывод' 
	) AS trans_type, 
	ex.code AS trans_code, 
	DATE_FORMAT( FROM_UNIXTIME( payments.regdate ) , '%H:%i:%s' ) AS trans_time 
FROM `payments` 
LEFT JOIN `clients` c ON c.id = payments.client_id 
LEFT JOIN `offline_exchange_ways` w ON w.id = payments.`exchange_way_id` 
LEFT JOIN `offline_payment_methods` off ON off.id = w.offline_method_id 
LEFT JOIN `ex_currencies` ex ON ex.id = w.ex_currency_id 
WHERE payments.`status`= 4 AND 

	DATE_FORMAT( DATE_SUB( '2007-02-05 06:00:00', INTERVAL 1 DAY ) , '%Y-%m-%d 00:00:00' ) < DATE_FORMAT( FROM_UNIXTIME( payments.regdate ) , '%Y-%m-%d' ) 
	AND 
	DATE_FORMAT( FROM_UNIXTIME( payments.regdate ) , '%Y-%m-%d' ) < DATE_FORMAT( '2007-02-05' , '%Y-%m-%d 23:59:59' ) 
	 AND w.offline_method_id <> 14 ORDER BY id desc LIMIT 50 OFFSET 0

кажется, кому-то остро не хватает хранимых процедур =)

Comments

[cmhungry.livejournal.com]

ы....

[baton.livejournal.com]

Гы-гы, это еще вложенные запросы можно делать ;-)