SET @suppliers_id = 411; -- 2644;
SET @date_report = '2017-03-15';
SELECT
opn.suppliers_id
, opn.categories_id
, opn.orders_id
, opn.id
, SUM(IF(NOW() < c.date_end, opn.products_price_purchase,0)) AS ПредварительныйЗаказ
, SUM(IF(NOW() > c.date_end AND o.orders_status IN(4,28,7,16,8,23), opn.products_price_purchase*opn.in_supply,0)) AS ЖдетОтправки
, SUM(IF(NOW() > c.date_end AND o.orders_status IN(5, 27), opn.products_price_purchase*opn.in_supply,0)) AS Доставляется
, SUM(IF(NOW() > c.date_end AND o.orders_status IN(6, 26), opn.products_price_purchase*opn.in_supply,0)) AS ДоставленоНоНеОплачено
, 0 AS ДоставленоНоОплаченоПозже
FROM orders_products_categories_view_1c AS opn
LEFT JOIN report_cash_inflow_product AS rp ON rp.orders_products_id = opn.id
JOIN orders AS o ON o.orders_id = opn.orders_id
JOIN categories AS c ON c.categories_id = opn.categories_id
WHERE 1 = 1
AND opn.suppliers_id =@suppliers_id
AND c.date_start >= '2014-10-10'
AND opn.is_deleted = 0
AND opn.is_return = 0
AND o.orders_status NOT IN(15,18,19,20,21,24,25,14,10)
AND rp.report_cash_inflow_product_id IS NULL
GROUP BY
 opn.suppliers_id
-- , opn.categories_id
-- , opn.orders_id

UNION ALL

SELECT
opn.suppliers_id
, opn.categories_id
, opn.orders_id
, opn.id
, 0 AS ПредварительныйЗаказ
, 0 AS ЖдетОтправки
, 0 AS Доставляется
, 0 AS ДоставленоНоНеОплачено
, SUM(rp.report_cash_inflow_product_count*rp.report_cash_inflow_product_sum) AS ДоставленоНоОплаченоПозже
FROM orders_products_categories_view_1c AS opn
JOIN report_cash_inflow_product AS rp ON rp.orders_products_id = opn.id AND rp.report_cash_inflow_product_date > @date_report
JOIN orders AS o ON o.orders_id = opn.orders_id
JOIN categories AS c ON c.categories_id = opn.categories_id
WHERE 1 = 1
AND opn.suppliers_id =@suppliers_id
AND c.date_start >= '2014-10-10'
AND opn.is_deleted = 0
AND o.orders_status NOT IN(15,18,19,20,21,24,25,14,10)
AND rp.report_cash_inflow_product_id IS NULL
GROUP BY
 opn.suppliers_id
-- , opn.categories_id
-- , opn.orders_id