SELECT r.refunds_id As 'Коробка'
, DATE(r.date_accepted) AS DATE
, GROUP_CONCAT(DISTINCT ro.orders_id) as orders_id
, MAX(r.redelivery_price_origin) AS 'Сумма обратной пересылки'
, MAX(r.redelivery_price) AS 'Возмещенная сумма обратной пересылки'
, MAX(r.delivery_price) AS 'Возмещенная сумма доставки'
, COUNT(DISTINCT ro.orders_id) AS 'Количество заказов'
, COUNT(DISTINCT opn.id) AS 'Количество товаров в заказе всего'
, COUNT(DISTINCT rp.orders_products_id) AS 'Количество возвращенных товаров'


, IF(IFNULL(f.SHORTNAME, '') IN ('АО', 'Аобл', 'край', 'обл', 'Респ') OR (f.id IN(452231, 891320, 1063058)), COALESCE(f.name, CONCAT(f.OFFNAME, ' ', IFNULL(f.SHORTNAME, ''))),
		IF(IFNULL(f2.SHORTNAME, '') IN ('АО', 'Аобл', 'край', 'обл', 'Респ') OR (f2.id IN(452231, 891320, 1063058)), COALESCE(f2.name, CONCAT(f2.OFFNAME, ' ', IFNULL(f2.SHORTNAME, ''))),
        IF(IFNULL(f3.SHORTNAME, '') IN ('АО', 'Аобл', 'край', 'обл', 'Респ') OR (f3.id IN(452231, 891320, 1063058)), COALESCE(f3.name, CONCAT(f3.OFFNAME, ' ', IFNULL(f3.SHORTNAME, ''))),
            IF(IFNULL(f4.SHORTNAME, '') IN ('АО', 'Аобл', 'край', 'обл', 'Респ') OR (f4.id IN(452231, 891320, 1063058)), COALESCE(f4.name, CONCAT(f4.OFFNAME, ' ', IFNULL(f4.SHORTNAME, ''))),
                IF(IFNULL(f5.SHORTNAME, '') IN ('АО', 'Аобл', 'край', 'обл', 'Респ') OR (f5.id IN(452231, 891320, 1063058)), COALESCE(f5.name, CONCAT(f5.OFFNAME, ' ', IFNULL(f5.SHORTNAME, ''))), '-')))))
					AS fias_region_name

	, IFNULL(CONCAT(COALESCE(f.OFFNAME,'-'), ' ', IFNULL(f.SHORTNAME, '-')), '-') AS fias_locality_name
		,IF(IFNULL(f.SHORTNAME, '') IN ('р-н', 'г', 'пгт'), COALESCE(f.name, CONCAT(f.OFFNAME, ' ', IFNULL(f.SHORTNAME, ''))),
		IF(IFNULL(f2.SHORTNAME, '') IN ('р-н', 'г', 'пгт'), COALESCE(f2.name, CONCAT(f2.OFFNAME, ' ', IFNULL(f2.SHORTNAME, ''))),
        IF(IFNULL(f3.SHORTNAME, '') IN ('р-н', 'г', 'пгт'), COALESCE(f3.name, CONCAT(f3.OFFNAME, ' ', IFNULL(f3.SHORTNAME, ''))),
            IF(IFNULL(f4.SHORTNAME, '') IN ('р-н', 'г', 'пгт'), COALESCE(f4.name, CONCAT(f4.OFFNAME, ' ', IFNULL(f4.SHORTNAME, ''))),
                IF(IFNULL(f5.SHORTNAME, '') IN ('р-н', 'г', 'пгт'), COALESCE(f5.name, CONCAT(f5.OFFNAME, ' ', IFNULL(f5.SHORTNAME, ''))), '-')))))  AS fias_district_name

FROM refunds AS r
JOIN refunds_products AS rp ON rp.refunds_id = r.refunds_id
JOIN refunds_orders AS ro ON ro.refunds_id = r.refunds_id
JOIN orders AS o ON o.orders_id = ro.orders_id
JOIn orders_products_new as opn on opn.orders_id = o.orders_id and opn.is_deleted = 0
JOIN fias AS f ON f.id = o.customers_fias_id
LEFT JOIN fias AS f2 ON f2.AOGUID = f.PARENTGUID
LEFT JOIN fias AS f3 ON f3.AOGUID = f2.PARENTGUID
LEFT JOIN fias AS f4 ON f4.AOGUID = f3.PARENTGUID
LEFT JOIN fias AS f5 ON f5.AOGUID = f4.PARENTGUID
WHERE (r.redelivery_price > 0 or r.redelivery_price_origin > 0)
 AND r.date_accepted >= '2016-01-01'
GROUP BY r.refunds_id
ORDER BY fias_region_name, date