बफ़र्स के लिए बफ़र या कोड की 30 पंक्तियों में C # नहीं में एक वर्चुअल क्लिपबोर्ड लिखें

SELECT IFNULL(SUM(t_step3.half_step3 - IFNULL(t_step1.bonus, 0) - IFNULL(t_step2.bonus, 0)), 0) AS 'bonus' FROM ( SELECT t_orders2cat.order_id AS 'id', (t_o.balance_rub - SUM(t_orders2cat.dprice_rub - t_orders2cat.delivery_cost_rub) - IFNULL(t_gurkin.half_step3, 0) - IFNULL(t_o2p.cost_expenses_rub, 0)) * ( IF(base_on_tender = 0, IFNULL(t_category_percent.percent, 1), IF(base_on_tender = 1, IFNULL(t_category_percent.percent, 1) - 0.1, IFNULL(t_category_percent.percent, 1) - 0.5) ) - IF(t_orders.tech_helper_id != 0, t_users.tech_bonus_percent, 0) ) / 100 AS 'half_step3' FROM t_orders2cat, t_orders LEFT JOIN t_category_percent ON t_orders.category_percent_id = t_category_percent.id LEFT JOIN t_users ON t_orders.tech_helper_id = t_users.id LEFT JOIN (SELECT order_id, SUM(cost_expenses_rub) AS 'cost_expenses_rub' FROM t_orders2pnr GROUP BY t_orders2pnr.order_id HAVING SUM(cost_expenses_rub) > 0) AS t_o2p ON t_orders.id = t_o2p.order_id LEFT JOIN ( SELECT t_orders.id AS 'order_id', SUM(deg_discount(deg_convert_money( t_orders.rate_eur, t_orders.rate_usd, t_orders.rate_jpy, t_orders2cat.price, t_orders2cat.currency_id, t_orders.currency_id), t_orders2cat.discount) * t_orders2cat.count - t_orders2cat.dprice_rub - t_orders2cat.delivery_cost_rub - IFNULL(t_orders2pnr.cost_expenses_rub, 0) ) * t_p.balance_rub / t_orders.balance AS 'half_step3' FROM t_orders LEFT JOIN t_orders2pnr ON t_orders.id = t_orders2pnr.order_id, t_orders2cat, t_cat, t_vendors, ( SELECT order_id, SUM(summa_rub) AS 'balance_rub' FROM t_payments GROUP BY order_id ) AS t_p, ( SELECT t_o.id, GREATEST(IFNULL(t_o.date_shipment, t_o.date_pnr_finish), IFNULL(t_o.date_pnr_finish, t_o.date_shipment), MAX(t_payments.date_payment)) AS 'date_closed', SUM(t_payments.summa_rub) AS 'balance_rub' FROM t_payments, ( SELECT t_orders.id, COUNT(t_orders2cat.id) AS 'orders2cat_count', MAX(t_orders2cat.date_from) AS 'date_shipment', MAX(t_orders2cat.status_id) AS 'orders2cat_status', COUNT(t_orders2pnr.id) AS 'pnr_count', MAX(t_orders2pnr.date) AS 'date_pnr_finish', MAX(t_orders2pnr.status_id) AS 'pnr_status' FROM t_orders LEFT JOIN t_orders2cat ON t_orders.id = t_orders2cat.order_id LEFT JOIN t_orders2pnr ON t_orders.id = t_orders2pnr.order_id WHERE t_orders.user_id = in_user_id AND t_orders.items_finished >= t_orders.items_count AND t_orders.summa - t_orders.balance < 2 GROUP BY t_orders.id ) AS t_o WHERE t_payments.order_id = t_o.id GROUP BY t_payments.order_id HAVING EXTRACT(YEAR_MONTH FROM date_closed) = EXTRACT(YEAR_MONTH FROM in_date) ) AS t_o WHERE t_orders.id = t_o.id AND t_orders.id = t_p.order_id AND t_orders.id = t_orders2cat.order_id AND t_orders2cat.cat_id = t_cat.id AND t_orders.user_id = in_user_id AND t_cat.vendor_id = t_vendors.id AND t_vendors.user_id = 158 GROUP BY t_orders.id ) AS t_gurkin ON t_orders.id = t_gurkin.order_id, ( SELECT t_o.id, GREATEST(IFNULL(t_o.date_shipment, t_o.date_pnr_finish), IFNULL(t_o.date_pnr_finish, t_o.date_shipment), MAX(t_payments.date_payment)) AS 'date_closed', SUM(t_payments.summa_rub) AS 'balance_rub' FROM t_payments, ( SELECT t_orders.id, COUNT(t_orders2cat.id) AS 'orders2cat_count', MAX(t_orders2cat.date_from) AS 'date_shipment', MAX(t_orders2cat.status_id) AS 'orders2cat_status', COUNT(t_orders2pnr.id) AS 'pnr_count', MAX(t_orders2pnr.date) AS 'date_pnr_finish', MAX(t_orders2pnr.status_id) AS 'pnr_status' FROM t_orders LEFT JOIN t_orders2cat ON t_orders.id = t_orders2cat.order_id LEFT JOIN t_orders2pnr ON t_orders.id = t_orders2pnr.order_id WHERE t_orders.user_id = in_user_id AND t_orders.items_finished >= t_orders.items_count AND t_orders.summa - t_orders.balance < 2 GROUP BY t_orders.id ) AS t_o WHERE t_payments.order_id = t_o.id GROUP BY t_payments.order_id HAVING EXTRACT(YEAR_MONTH FROM date_closed) = EXTRACT(YEAR_MONTH FROM in_date) ) AS t_o WHERE t_orders2cat.order_id = t_o.id AND t_orders2cat.order_id = t_orders.id GROUP BY t_orders2cat.order_id ) AS t_step3 LEFT JOIN ( SELECT t_orders.id, t_o.average_curs * (t_orders.summa - IFNULL(t_gurkin.step1, 0)) * ( IF(base_on_tender = 0, IFNULL(t_category_percent.percent, 1), IF(base_on_tender = 1, IFNULL(t_category_percent.percent, 1) - 0.1, IFNULL(t_category_percent.percent, 1) - 0.5 ) ) - IF(t_orders.tech_helper_id != 0, t_users.tech_bonus_percent, 0) ) / 100 * 0.3 AS 'bonus' FROM t_orders LEFT JOIN t_category_percent ON t_orders.category_percent_id = t_category_percent.id LEFT JOIN t_users ON t_orders.tech_helper_id = t_users.id LEFT JOIN ( SELECT t_orders.id AS 'order_id', SUM(deg_discount(deg_convert_money( t_orders.rate_eur, t_orders.rate_usd, t_orders.rate_jpy, t_orders2cat.price, t_orders2cat.currency_id, t_orders.currency_id), t_orders2cat.discount) * t_orders2cat.count) * t_o.payment_rub / t_o.payment_currency AS 'step1' FROM t_orders, t_orders2cat, t_cat, t_vendors, ( SELECT t_orders.id, SUM(t_payments.summa_rub) AS 'payment_rub', SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_payments.summa_rub, 1, t_orders.currency_id)) AS 'payment_currency', t_orders.summa FROM t_orders, t_payments, t_rates WHERE t_orders.id = t_payments.order_id AND t_payments.date_rates = t_rates.date AND EXTRACT(YEAR_MONTH FROM t_orders.date_firstpay) = EXTRACT(YEAR_MONTH FROM t_payments.date_payment) GROUP BY t_payments.order_id HAVING payment_currency / t_orders.summa >= 0.3 ) AS t_o WHERE t_orders.id = t_orders2cat.order_id AND t_orders2cat.order_id = t_o.id AND t_orders.user_id = in_user_id AND t_orders2cat.cat_id = t_cat.id AND t_vendors.user_id = 158 AND t_cat.vendor_id = t_vendors.id GROUP BY t_o.id ) AS t_gurkin ON t_orders.id = t_gurkin.order_id, ( SELECT t_orders.id, SUM(t_payments.summa_rub) / SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_payments.summa_rub, 1, t_orders.currency_id)) AS 'average_curs' FROM t_payments, t_rates, t_orders, ( SELECT t_payments.order_id, MIN(t_payments.date_payment) AS 'date_payment' FROM t_payments WHERE 0.3 <= ( SELECT SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_p.summa_rub, 1, t_o.currency_id)) / t_o.summa AS 'summa_cur' FROM t_payments AS t_p, t_orders AS t_o, t_rates WHERE t_p.order_id = t_o.id AND t_p.date_rates = t_rates.date AND t_p.date_payment <= t_payments.date_payment AND t_o.id = t_payments.order_id ) GROUP BY t_payments.order_id ) AS t_o WHERE t_payments.order_id = t_o.order_id AND t_payments.order_id = t_orders.id AND t_payments.date_rates = t_rates.date AND EXTRACT(YEAR_MONTH FROM t_payments.date_payment) <= EXTRACT(YEAR_MONTH FROM t_o.date_payment) AND t_orders.user_id = in_user_id GROUP BY t_orders.id ) AS t_o WHERE t_orders.id = t_o.id AND t_orders.user_id = in_user_id ) AS t_step1 ON t_step3.id = t_step1.id LEFT JOIN ( SELECT t_orders.id, SUM(t_payments.summa_rub) / SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_payments.summa_rub, 1, t_orders.currency_id)) * (t_orders.summa - IFNULL(t_gurkin.step2, 0)) * ( IF(base_on_tender = 0, IFNULL(t_category_percent.percent, 1), IF(base_on_tender = 1, IFNULL(t_category_percent.percent, 1) - 0.1, IFNULL(t_category_percent.percent, 1) - 0.5 ) ) - IF(t_orders.tech_helper_id != 0, t_users.tech_bonus_percent, 0) ) / 100 * 0.3 AS 'bonus' FROM t_payments, t_rates, t_orders LEFT JOIN t_category_percent ON t_orders.category_percent_id = t_category_percent.id LEFT JOIN t_users ON t_orders.tech_helper_id = t_users.id LEFT JOIN ( SELECT t_orders.id AS 'order_id', SUM(deg_discount(deg_convert_money( t_orders.rate_eur, t_orders.rate_usd, t_orders.rate_jpy, t_orders2cat.price, t_orders2cat.currency_id, t_orders.currency_id), t_orders2cat.discount) * t_orders2cat.count) * t_o.payment_rub / t_o.payment_currency AS 'step2' FROM t_orders, t_orders2cat, t_cat, t_vendors, ( SELECT t_orders.id, SUM(t_payments.summa_rub) AS 'payment_rub', SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_payments.summa_rub, 1, t_orders.currency_id)) AS 'payment_currency' FROM t_payments, t_rates, t_orders, ( SELECT t_o.id, GREATEST(date_exit, date_payment) AS 'date_payment_bonus' FROM ( SELECT t_o.id AS 'id', MAX(t_containers.date_port_exit) AS 'date_exit' FROM t_orders2cat, t_invoices2orders2cat, t_containers2invoices, t_containers, ( SELECT t_orders.id FROM t_orders, t_orders2cat WHERE t_orders.id = t_orders2cat.order_id AND t_orders.user_id = in_user_id AND 4 <= ALL (SELECT t_o2c.status_id FROM t_orders2cat AS t_o2c WHERE t_o2c.order_id = t_orders.id) GROUP BY t_orders.id ) AS t_o WHERE t_orders2cat.id = t_invoices2orders2cat.order2cat_id AND t_invoices2orders2cat.invoice_id = t_containers2invoices.invoice_id AND t_containers2invoices.container_id = t_containers.id AND t_orders2cat.order_id = t_o.id GROUP BY t_o.id ) AS t_o, ( SELECT t_payments.order_id, MIN(t_payments.date_payment) AS 'date_payment' FROM t_payments WHERE 0.8 <= ( SELECT SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_p.summa_rub, 1, t_o.currency_id)) / t_o.summa AS 'summa_cur' FROM t_payments AS t_p, t_orders AS t_o, t_rates WHERE t_p.order_id = t_o.id AND t_o.user_id = in_user_id AND t_p.date_rates = t_rates.date AND t_p.date_payment <= t_payments.date_payment AND t_o.id = t_payments.order_id ) GROUP BY t_payments.order_id ) t_p WHERE t_o.id = t_p.order_id ) AS t_o WHERE t_payments.order_id = t_o.id AND t_payments.date_rates = t_rates.date AND t_payments.order_id = t_orders.id AND EXTRACT(YEAR_MONTH FROM t_payments.date_payment) <= EXTRACT(YEAR_MONTH FROM t_o.date_payment_bonus) GROUP BY t_orders.id ) AS t_o WHERE t_orders.id = t_orders2cat.order_id AND t_orders2cat.order_id = t_o.id AND t_orders2cat.cat_id = t_cat.id AND t_orders.user_id = in_user_id AND t_vendors.user_id = 158 AND t_cat.vendor_id = t_vendors.id GROUP BY t_o.id ) AS t_gurkin ON t_orders.id = t_gurkin.order_id, ( SELECT t_o.id, GREATEST(date_exit, date_payment) AS 'date_payment_bonus' FROM ( SELECT t_o.id AS 'id', MAX(t_containers.date_port_exit) AS 'date_exit' FROM t_orders2cat, t_invoices2orders2cat, t_containers2invoices, t_containers, ( SELECT t_orders.id FROM t_orders, t_orders2cat WHERE t_orders.id = t_orders2cat.order_id AND t_orders.user_id = in_user_id AND 4 <= ALL (SELECT t_o2c.status_id FROM t_orders2cat AS t_o2c WHERE t_o2c.order_id = t_orders.id) AND t_orders.user_id = in_user_id GROUP BY t_orders.id ) AS t_o WHERE t_orders2cat.id = t_invoices2orders2cat.order2cat_id AND t_invoices2orders2cat.invoice_id = t_containers2invoices.invoice_id AND t_containers2invoices.container_id = t_containers.id AND t_orders2cat.order_id = t_o.id GROUP BY t_o.id ) AS t_o, ( SELECT t_payments.order_id, MIN(t_payments.date_payment) AS 'date_payment' FROM t_payments WHERE 0.8 <= ( SELECT SUM(deg_convert_money(t_rates.eur, t_rates.usd, t_rates.jpy, t_p.summa_rub, 1, t_o.currency_id)) / t_o.summa AS 'summa_cur' FROM t_payments AS t_p, t_orders AS t_o, t_rates WHERE t_p.order_id = t_o.id AND t_o.user_id = in_user_id AND t_p.date_rates = t_rates.date AND t_p.date_payment <= t_payments.date_payment AND t_o.id = t_payments.order_id ) GROUP BY t_payments.order_id ) t_p WHERE t_o.id = t_p.order_id ) AS t_o WHERE t_o.id = t_payments.order_id AND t_orders.id = t_o.id AND t_orders.user_id = in_user_id AND t_payments.date_rates = t_rates.date AND EXTRACT(YEAR_MONTH FROM t_payments.date_payment) <= EXTRACT(YEAR_MONTH FROM t_o.date_payment_bonus) GROUP BY t_orders.id ) AS t_step2 ON t_step3.id = t_step2.id;