Untitled

SELECT CASE
                                        WHEN trip.TYPE = 'transfer'
                                            THEN 'transfer'
                                        WHEN order_profit.debit_company_id IS NULL
                                                AND waybill_netting_partner_city.company_id IS NULL
                                                AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                                AND order_profit.TYPE != 'shipping_satellite'
                                            THEN 'local_gd'
                                        WHEN (order_profit.credit_company_id = 62
                                        AND order_profit.debit_company_id IS NOT NULL
                                        AND order_profit.TYPE IN ('forwarding_netting')
                                        AND (`order`.service_id IN (11662, 1001790)
                                            OR `order`.category_id = 113) OR order_profit.credit_company_id IS NULL
                                        AND waybill_netting_partner_city.company_id IS NOT NULL
                                        AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                        AND order_profit.TYPE != 'shipping_satellite')
                                            THEN 'local_partners'
                                        WHEN IFNULL(waybill.parent_service_id, waybill.service_id) NOT IN (91,92)
                                            THEN 'intercity'
                                        ELSE
                                            'other'
                                    END AS TYPE,
                                    DATE_FORMAT(`order`.report_date, '%Y-%m') AS MONTH,
                                    SUM(order_profit.VALUE) AS VALUE,

                                    ORDER.order_id,
                                    ORDER.title,
                                ORDER.report_date,
                                ORDER.giveout_date,
                                shipping.reduced_volume,
                                ORDER.STATUS,
                                IF(ORDER.service_id IN (46, 11662) AND ORDER.map_from_city_id = ORDER.map_to_city_id, 0, 1) AS shipping_different_city,
                                from_city.title AS from_city,
                                to_city.title AS to_city

                    FROM (
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (333196)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 17:20:07'
                                    AND `order`.service_id = 1001790
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    SUM(IF(from_contact.TYPE = 'storehouse' AND to_contact.TYPE = 'storehouse' AND trip.STATUS <> 'complete', 1, 0)) AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                    LEFT JOIN waybill ON (`order`.order_id = waybill.order_id AND waybill.service_id = 90)
                                    LEFT JOIN trip_contents ON (waybill.waybill_id = trip_contents.waybill_id)
                                    LEFT JOIN trip ON (trip_contents.trip_id = trip.trip_id AND trip.TYPE = 'intercity')
                                    LEFT JOIN route ON (trip.trip_id = route.trip_id)
                                    LEFT JOIN contact from_contact ON (route.from_contact_id = from_contact.contact_id)
                                    LEFT JOIN contact to_contact ON (route.to_contact_id = to_contact.contact_id)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (46,11662,942285,100)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id

                            )
                        ) t1
                        LEFT JOIN `order` ON (t1.order_id = `order`.order_id)
                        LEFT JOIN waybill shipping ON (`order`.order_id = shipping.order_id
                            AND shipping.service_id = 90
                            AND `order`.service_id != 1001790
                            AND shipping.from_city_id <> shipping.to_city_id)
                        LEFT JOIN account ON (`order`.account_id = account.account_id)
                        LEFT JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                        LEFT JOIN employee ON (client.user_id = employee.user_id)
                        LEFT JOIN contact office_contact ON (employee.office_contact_id = office_contact.contact_id)
                        LEFT JOIN geo AS from_city ON from_city.geo_id = ORDER.map_from_city_id
                        LEFT JOIN geo AS to_city ON to_city.geo_id = ORDER.map_to_city_id
                        LEFT JOIN geo office_geo ON (office_contact.geo_id = office_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp netting_partner_city ON (DATE(`order`.report_date) = netting_partner_city.DATE
                            AND netting_partner_city.geo_id = office_contact.geo_id)
                        LEFT JOIN company netting_partner_company ON (netting_partner_city.company_id = netting_partner_company.company_id)
                        LEFT JOIN company employee_company ON (IFNULL(employee.company_id, 62) = employee_company.company_id)
                        LEFT JOIN company ON (IFNULL(IFNULL(netting_partner_company.parent_id, netting_partner_company.company_id), IF(employee_company.TYPE = 'main', 62, employee.company_id)) = company.company_id)
                        LEFT JOIN contact company_contact ON (company.contact_id = company_contact.contact_id)
                        LEFT JOIN geo company_geo ON (company_contact.geo_id = company_geo.geo_id)
                        LEFT JOIN order_profit ON (`order`.order_id = order_profit.order_id)
                        LEFT JOIN trip ON (order_profit.trip_id = trip.trip_id)
                        LEFT JOIN waybill ON (order_profit.waybill_id = waybill.waybill_id)
                        LEFT JOIN waybill parent_waybill ON (waybill.order_id = parent_waybill.order_id AND waybill.parent_service_id = parent_waybill.service_id)
                        LEFT JOIN service inner_service ON (waybill.service_id = inner_service.service_id)
                        LEFT JOIN geo waybill_geo ON (IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp waybill_netting_partner_city ON (DATE(`order`.report_date) = waybill_netting_partner_city.DATE
                            AND IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_netting_partner_city.geo_id)
                        LEFT JOIN company waybill_company ON (waybill_company.company_id = waybill_netting_partner_city.company_id)
                        LEFT JOIN company debit_company ON (order_profit.debit_company_id = debit_company.company_id)
                        LEFT JOIN contact debit_company_contact ON (debit_company.contact_id = debit_company_contact.contact_id)
                        LEFT JOIN geo debit_company_geo ON (debit_company_contact.geo_id = debit_company_geo.geo_id)
                        LEFT JOIN company credit_company ON (order_profit.credit_company_id = credit_company.company_id)
                        LEFT JOIN contact credit_company_contact ON (credit_company.contact_id = credit_company_contact.contact_id)
                        LEFT JOIN geo credit_company_geo ON (credit_company_contact.geo_id = credit_company_geo.geo_id)

                                WHERE IF(company.company_id IN (71,163,167,302) AND `order`.report_date < '2018-08-01 00:00:00' OR company.company_id IN (105, 119) AND `order`.report_date < '2019-11-01 00:00:00' OR company.company_id IN (41) AND `order`.report_date < '2020-02-01 00:00:00', 'processing', IFNULL(company.netting_scheme, 'main')) != 'processing'
 AND `order`.order_id IN (25861471)
                                    AND (
                                        order_profit.debit_company_id IS NULL
                                            AND order_profit.credit_company_id IS NOT NULL
                                            AND order_profit.TYPE IN ('shipping','shipping_trip_cost')
                                            AND IF(credit_company.company_id IN (71,163,167,302) AND `order`.report_date < '2018-08-01 00:00:00' OR credit_company.company_id IN (105, 119) AND `order`.report_date < '2019-11-01 00:00:00' OR credit_company.company_id IN (41) AND `order`.report_date < '2020-02-01 00:00:00', 'processing', IFNULL(credit_company.netting_scheme, 'main')) != 'processing'
                                        OR
                                        order_profit.credit_company_id = 62
                                            AND order_profit.debit_company_id IS NOT NULL
                                            AND order_profit.TYPE IN ('shipping_route_segment')
                                        OR
                                        order_profit.debit_company_id IS NULL
                                                AND waybill_netting_partner_city.company_id IS NULL
                                                AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                                AND order_profit.TYPE != 'shipping_satellite'
                                        OR
                                        order_profit.credit_company_id = 62
                                        AND order_profit.debit_company_id IS NOT NULL
                                        AND order_profit.TYPE IN ('forwarding_netting')
                                        AND (`order`.service_id IN (11662, 1001790)
                                            OR `order`.category_id = 113)
                                        OR
                                        order_profit.credit_company_id IS NULL
                                        AND waybill_netting_partner_city.company_id IS NOT NULL
                                        AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                        AND order_profit.TYPE != 'shipping_satellite'
                                        OR
                                            order_profit.debit_company_id IS NULL
                                            AND order_profit.TYPE IN ('extra_service')
                                            AND waybill_netting_partner_city.company_id IS NULL
                                            AND waybill.parent_service_id = 90
                                    )
                                GROUP BY ORDER.order_id, TYPE

                                UNION ALL

                                SELECT CASE
                                        WHEN trip.TYPE = 'transfer'
                                            THEN 'transfer'
                                        WHEN IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                                AND order_profit.debit_company_id IS NULL
                                                AND waybill_netting_partner_city.company_id IS NULL
                                                AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                                AND order_profit.TYPE != 'shipping_satellite'
                                            THEN 'local_gd'
                                        WHEN IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                                AND (order_profit.credit_company_id = 62
                                        AND order_profit.debit_company_id IS NOT NULL
                                        AND order_profit.TYPE IN ('forwarding_netting')
                                        AND (`order`.service_id IN (11662, 1001790)
                                            OR `order`.category_id = 113) OR order_profit.credit_company_id IS NULL
                                        AND waybill_netting_partner_city.company_id IS NOT NULL
                                        AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                        AND order_profit.TYPE != 'shipping_satellite')
                                            THEN 'local_partners'
                                        WHEN IFNULL(waybill.parent_service_id, waybill.service_id) NOT IN (91,92)
                                            THEN 'intercity'
                                        ELSE
                                            'other'
                                    END AS TYPE,
                                    DATE_FORMAT(`order`.report_date, '%Y-%m') AS MONTH,
                                    SUM(order_profit.VALUE) AS VALUE,
                                    ORDER.order_id,
                                    ORDER.title,
                                ORDER.report_date,
                                ORDER.giveout_date,
                                shipping.reduced_volume,
                                ORDER.STATUS,
                                IF(ORDER.service_id IN (46, 11662) AND ORDER.map_from_city_id = ORDER.map_to_city_id, 0, 1) AS shipping_different_city,
                                from_city.title AS from_city,
                                to_city.title AS to_city

                    FROM (
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (333196)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 17:20:07'
                                    AND `order`.service_id = 1001790
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    SUM(IF(from_contact.TYPE = 'storehouse' AND to_contact.TYPE = 'storehouse' AND trip.STATUS <> 'complete', 1, 0)) AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                    LEFT JOIN waybill ON (`order`.order_id = waybill.order_id AND waybill.service_id = 90)
                                    LEFT JOIN trip_contents ON (waybill.waybill_id = trip_contents.waybill_id)
                                    LEFT JOIN trip ON (trip_contents.trip_id = trip.trip_id AND trip.TYPE = 'intercity')
                                    LEFT JOIN route ON (trip.trip_id = route.trip_id)
                                    LEFT JOIN contact from_contact ON (route.from_contact_id = from_contact.contact_id)
                                    LEFT JOIN contact to_contact ON (route.to_contact_id = to_contact.contact_id)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (46,11662,942285,100)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id

                            )
                        ) t1
                        LEFT JOIN `order` ON (t1.order_id = `order`.order_id)
                        LEFT JOIN waybill shipping ON (`order`.order_id = shipping.order_id
                            AND shipping.service_id = 90
                            AND `order`.service_id != 1001790
                            AND shipping.from_city_id <> shipping.to_city_id)
                        LEFT JOIN account ON (`order`.account_id = account.account_id)
                        LEFT JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                        LEFT JOIN employee ON (client.user_id = employee.user_id)
                        LEFT JOIN contact office_contact ON (employee.office_contact_id = office_contact.contact_id)
                        LEFT JOIN geo AS from_city ON from_city.geo_id = ORDER.map_from_city_id
                        LEFT JOIN geo AS to_city ON to_city.geo_id = ORDER.map_to_city_id
                        LEFT JOIN geo office_geo ON (office_contact.geo_id = office_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp netting_partner_city ON (DATE(`order`.report_date) = netting_partner_city.DATE
                            AND netting_partner_city.geo_id = office_contact.geo_id)
                        LEFT JOIN company netting_partner_company ON (netting_partner_city.company_id = netting_partner_company.company_id)
                        LEFT JOIN company employee_company ON (IFNULL(employee.company_id, 62) = employee_company.company_id)
                        LEFT JOIN company ON (IFNULL(IFNULL(netting_partner_company.parent_id, netting_partner_company.company_id), IF(employee_company.TYPE = 'main', 62, employee.company_id)) = company.company_id)
                        LEFT JOIN contact company_contact ON (company.contact_id = company_contact.contact_id)
                        LEFT JOIN geo company_geo ON (company_contact.geo_id = company_geo.geo_id)
                        LEFT JOIN order_profit ON (`order`.order_id = order_profit.order_id)
                        LEFT JOIN trip ON (order_profit.trip_id = trip.trip_id)
                        LEFT JOIN waybill ON (order_profit.waybill_id = waybill.waybill_id)
                        LEFT JOIN waybill parent_waybill ON (waybill.order_id = parent_waybill.order_id AND waybill.parent_service_id = parent_waybill.service_id)
                        LEFT JOIN service inner_service ON (waybill.service_id = inner_service.service_id)
                        LEFT JOIN geo waybill_geo ON (IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp waybill_netting_partner_city ON (DATE(`order`.report_date) = waybill_netting_partner_city.DATE
                            AND IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_netting_partner_city.geo_id)
                        LEFT JOIN company waybill_company ON (waybill_company.company_id = waybill_netting_partner_city.company_id)
                        LEFT JOIN company debit_company ON (order_profit.debit_company_id = debit_company.company_id)
                        LEFT JOIN contact debit_company_contact ON (debit_company.contact_id = debit_company_contact.contact_id)
                        LEFT JOIN geo debit_company_geo ON (debit_company_contact.geo_id = debit_company_geo.geo_id)
                        LEFT JOIN company credit_company ON (order_profit.credit_company_id = credit_company.company_id)
                        LEFT JOIN contact credit_company_contact ON (credit_company.contact_id = credit_company_contact.contact_id)
                        LEFT JOIN geo credit_company_geo ON (credit_company_contact.geo_id = credit_company_geo.geo_id)

                                WHERE IF(company.company_id IN (71,163,167,302) AND `order`.report_date < '2018-08-01 00:00:00' OR company.company_id IN (105, 119) AND `order`.report_date < '2019-11-01 00:00:00' OR company.company_id IN (41) AND `order`.report_date < '2020-02-01 00:00:00', 'processing', IFNULL(company.netting_scheme, 'main')) = 'processing'
 AND `order`.order_id IN (25861471)
                                    AND (
                                        order_profit.credit_company_id = 62
                                            AND order_profit.debit_company_id IS NULL
                                            AND order_profit.TYPE IN ('shipping')
                                        OR
                                        order_profit.debit_company_id IS NULL
                                            AND order_profit.credit_company_id IS NOT NULL
                                            AND waybill_netting_partner_city.company_id IS NULL
                                            AND IFNULL(waybill.parent_service_id, waybill.service_id) IN (91,92)
                                        OR
                                            order_profit.debit_company_id IS NULL
                                            AND order_profit.TYPE IN ('extra_service')
                                            AND waybill_netting_partner_city.company_id IS NULL
                                            AND waybill.parent_service_id = 90
                                    )
                                GROUP BY ORDER.order_id, TYPE

                                UNION ALL

                                SELECT
                                    'local_partners' AS TYPE,
                                    DATE_FORMAT(`order`.report_date, '%Y-%m') AS MONTH,
                                    -SUM(order_profit.VALUE) AS VALUE,
                                    ORDER.order_id,
                                    ORDER.title,
                                ORDER.report_date,
                                ORDER.giveout_date,
                                shipping.reduced_volume,
                                ORDER.STATUS,
                                IF(ORDER.service_id IN (46, 11662) AND ORDER.map_from_city_id = ORDER.map_to_city_id, 0, 1) AS shipping_different_city,
                                from_city.title AS from_city,
                                to_city.title AS to_city

                    FROM (
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (333196)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 17:20:07'
                                    AND `order`.service_id = 1001790
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    SUM(IF(from_contact.TYPE = 'storehouse' AND to_contact.TYPE = 'storehouse' AND trip.STATUS <> 'complete', 1, 0)) AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                    LEFT JOIN waybill ON (`order`.order_id = waybill.order_id AND waybill.service_id = 90)
                                    LEFT JOIN trip_contents ON (waybill.waybill_id = trip_contents.waybill_id)
                                    LEFT JOIN trip ON (trip_contents.trip_id = trip.trip_id AND trip.TYPE = 'intercity')
                                    LEFT JOIN route ON (trip.trip_id = route.trip_id)
                                    LEFT JOIN contact from_contact ON (route.from_contact_id = from_contact.contact_id)
                                    LEFT JOIN contact to_contact ON (route.to_contact_id = to_contact.contact_id)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (46,11662,942285,100)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id

                            )
                        ) t1
                        LEFT JOIN `order` ON (t1.order_id = `order`.order_id)
                        LEFT JOIN waybill shipping ON (`order`.order_id = shipping.order_id
                            AND shipping.service_id = 90
                            AND `order`.service_id != 1001790
                            AND shipping.from_city_id <> shipping.to_city_id)
                        LEFT JOIN account ON (`order`.account_id = account.account_id)
                        LEFT JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                        LEFT JOIN employee ON (client.user_id = employee.user_id)
                        LEFT JOIN contact office_contact ON (employee.office_contact_id = office_contact.contact_id)
                        LEFT JOIN geo AS from_city ON from_city.geo_id = ORDER.map_from_city_id
                        LEFT JOIN geo AS to_city ON to_city.geo_id = ORDER.map_to_city_id
                        LEFT JOIN geo office_geo ON (office_contact.geo_id = office_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp netting_partner_city ON (DATE(`order`.report_date) = netting_partner_city.DATE
                            AND netting_partner_city.geo_id = office_contact.geo_id)
                        LEFT JOIN company netting_partner_company ON (netting_partner_city.company_id = netting_partner_company.company_id)
                        LEFT JOIN company employee_company ON (IFNULL(employee.company_id, 62) = employee_company.company_id)
                        LEFT JOIN company ON (IFNULL(IFNULL(netting_partner_company.parent_id, netting_partner_company.company_id), IF(employee_company.TYPE = 'main', 62, employee.company_id)) = company.company_id)
                        LEFT JOIN contact company_contact ON (company.contact_id = company_contact.contact_id)
                        LEFT JOIN geo company_geo ON (company_contact.geo_id = company_geo.geo_id)
                        LEFT JOIN order_profit ON (`order`.order_id = order_profit.order_id)
                        LEFT JOIN trip ON (order_profit.trip_id = trip.trip_id)
                        LEFT JOIN waybill ON (order_profit.waybill_id = waybill.waybill_id)
                        LEFT JOIN waybill parent_waybill ON (waybill.order_id = parent_waybill.order_id AND waybill.parent_service_id = parent_waybill.service_id)
                        LEFT JOIN service inner_service ON (waybill.service_id = inner_service.service_id)
                        LEFT JOIN geo waybill_geo ON (IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp waybill_netting_partner_city ON (DATE(`order`.report_date) = waybill_netting_partner_city.DATE
                            AND IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_netting_partner_city.geo_id)
                        LEFT JOIN company waybill_company ON (waybill_company.company_id = waybill_netting_partner_city.company_id)
                        LEFT JOIN company debit_company ON (order_profit.debit_company_id = debit_company.company_id)
                        LEFT JOIN contact debit_company_contact ON (debit_company.contact_id = debit_company_contact.contact_id)
                        LEFT JOIN geo debit_company_geo ON (debit_company_contact.geo_id = debit_company_geo.geo_id)
                        LEFT JOIN company credit_company ON (order_profit.credit_company_id = credit_company.company_id)
                        LEFT JOIN contact credit_company_contact ON (credit_company.contact_id = credit_company_contact.contact_id)
                        LEFT JOIN geo credit_company_geo ON (credit_company_contact.geo_id = credit_company_geo.geo_id)

                                WHERE IF(company.company_id IN (71,163,167,302) AND `order`.report_date < '2018-08-01 00:00:00' OR company.company_id IN (105, 119) AND `order`.report_date < '2019-11-01 00:00:00' OR company.company_id IN (41) AND `order`.report_date < '2020-02-01 00:00:00', 'processing', IFNULL(company.netting_scheme, 'main')) = 'processing'
 AND `order`.order_id IN (25861471)
                                    AND order_profit.debit_company_id = 62
                                        AND order_profit.credit_company_id IS NOT NULL
                                        AND order_profit.TYPE IN ('forwarding_netting')
                                        AND (`order`.service_id IN (11662, 1001790 )
                                            OR `order`.category_id = 113)

                                UNION ALL

                                SELECT
                                    'local_partners' AS TYPE,
                                    DATE_FORMAT(`order`.report_date, '%Y-%m') AS MONTH,
                                    -SUM(order_profit.VALUE) AS VALUE,
                                    ORDER.order_id,
                                    ORDER.title,
                                ORDER.report_date,
                                ORDER.giveout_date,
                                shipping.reduced_volume,
                                ORDER.STATUS,
                                IF(ORDER.service_id IN (46, 11662) AND ORDER.map_from_city_id = ORDER.map_to_city_id, 0, 1) AS shipping_different_city,
                                from_city.title AS from_city,
                                to_city.title AS to_city

                    FROM (
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (333196)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    0 AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 17:20:07'
                                    AND `order`.service_id = 1001790
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id
                            )
                            UNION
                            (
                                SELECT `order`.order_id,
                                    `order`.map_from_city_id,
                                    `order`.map_to_city_id,
                                    SUM(IF(from_contact.TYPE = 'storehouse' AND to_contact.TYPE = 'storehouse' AND trip.STATUS <> 'complete', 1, 0)) AS trip_id
                                FROM `order`
                                    INNER JOIN account ON (`order`.account_id = account.account_id)
                                    INNER JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                                    LEFT JOIN waybill ON (`order`.order_id = waybill.order_id AND waybill.service_id = 90)
                                    LEFT JOIN trip_contents ON (waybill.waybill_id = trip_contents.waybill_id)
                                    LEFT JOIN trip ON (trip_contents.trip_id = trip.trip_id AND trip.TYPE = 'intercity')
                                    LEFT JOIN route ON (trip.trip_id = route.trip_id)
                                    LEFT JOIN contact from_contact ON (route.from_contact_id = from_contact.contact_id)
                                    LEFT JOIN contact to_contact ON (route.to_contact_id = to_contact.contact_id)
                                WHERE `order`.report_date BETWEEN '2021-01-01 00:00:00' AND '2021-04-01 00:00:00'
                                    AND `order`.service_id IN (46,11662,942285,100)
                                    AND `order`.STATUS IN ('store','sf','trip','dep_store','closed')
                                    AND client.client_id NOT IN (3837237,4340396)
                                     AND `order`.service_id IN (46,11662,333196,1001790,942285,100)
                                GROUP BY `order`.order_id

                            )
                        ) t1
                        LEFT JOIN `order` ON (t1.order_id = `order`.order_id)
                        LEFT JOIN waybill shipping ON (`order`.order_id = shipping.order_id
                            AND shipping.service_id = 90
                            AND `order`.service_id != 1001790
                            AND shipping.from_city_id <> shipping.to_city_id)
                        LEFT JOIN account ON (`order`.account_id = account.account_id)
                        LEFT JOIN client ON (account.subject_id = client.client_id AND account.type_id = 21)
                        LEFT JOIN employee ON (client.user_id = employee.user_id)
                        LEFT JOIN contact office_contact ON (employee.office_contact_id = office_contact.contact_id)
                        LEFT JOIN geo AS from_city ON from_city.geo_id = ORDER.map_from_city_id
                        LEFT JOIN geo AS to_city ON to_city.geo_id = ORDER.map_to_city_id
                        LEFT JOIN geo office_geo ON (office_contact.geo_id = office_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp netting_partner_city ON (DATE(`order`.report_date) = netting_partner_city.DATE
                            AND netting_partner_city.geo_id = office_contact.geo_id)
                        LEFT JOIN company netting_partner_company ON (netting_partner_city.company_id = netting_partner_company.company_id)
                        LEFT JOIN company employee_company ON (IFNULL(employee.company_id, 62) = employee_company.company_id)
                        LEFT JOIN company ON (IFNULL(IFNULL(netting_partner_company.parent_id, netting_partner_company.company_id), IF(employee_company.TYPE = 'main', 62, employee.company_id)) = company.company_id)
                        LEFT JOIN contact company_contact ON (company.contact_id = company_contact.contact_id)
                        LEFT JOIN geo company_geo ON (company_contact.geo_id = company_geo.geo_id)
                        LEFT JOIN order_profit ON (`order`.order_id = order_profit.order_id)
                        LEFT JOIN trip ON (order_profit.trip_id = trip.trip_id)
                        LEFT JOIN waybill ON (order_profit.waybill_id = waybill.waybill_id)
                        LEFT JOIN waybill parent_waybill ON (waybill.order_id = parent_waybill.order_id AND waybill.parent_service_id = parent_waybill.service_id)
                        LEFT JOIN service inner_service ON (waybill.service_id = inner_service.service_id)
                        LEFT JOIN geo waybill_geo ON (IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_geo.geo_id)
# @todo переделать на geo_representation
                        LEFT JOIN geo_representation_tmp waybill_netting_partner_city ON (DATE(`order`.report_date) = waybill_netting_partner_city.DATE
                            AND IFNULL(waybill.from_city_id, waybill.to_city_id) = waybill_netting_partner_city.geo_id)
                        LEFT JOIN company waybill_company ON (waybill_company.company_id = waybill_netting_partner_city.company_id)
                        LEFT JOIN company debit_company ON (order_profit.debit_company_id = debit_company.company_id)
                        LEFT JOIN contact debit_company_contact ON (debit_company.contact_id = debit_company_contact.contact_id)
                        LEFT JOIN geo debit_company_geo ON (debit_company_contact.geo_id = debit_company_geo.geo_id)
                        LEFT JOIN company credit_company ON (order_profit.credit_company_id = credit_company.company_id)
                        LEFT JOIN contact credit_company_contact ON (credit_company.contact_id = credit_company_contact.contact_id)
                        LEFT JOIN geo credit_company_geo ON (credit_company_contact.geo_id = credit_company_geo.geo_id)

                                WHERE IF(company.company_id IN (71,163,167,302) AND `order`.report_date < '2018-08-01 00:00:00' OR company.company_id IN (105, 119) AND `order`.report_date < '2019-11-01 00:00:00' OR company.company_id IN (41) AND `order`.report_date < '2020-02-01 00:00:00', 'processing', IFNULL(company.netting_scheme, 'main')) != 'processing'
 AND `order`.order_id IN (25861471)
                                    AND order_profit.debit_company_id = 62
                                        AND order_profit.credit_company_id IS NOT NULL
                                        AND order_profit.TYPE IN ('forwarding_netting')
                                        AND (`order`.service_id IN (11662, 1001790 )
                                            OR `order`.category_id = 113)

                                GROUP BY ORDER.order_id, TYPE