SELECT oc.id,
           oc.project_id,
           oc.state_id,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_arrive_ts), 'YYYY-MM-DD'::text)   AS arrive_date,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_arrive_ts), 'HH24:MI:SS'::text)   AS arrive_time,
           timezone((bpd.time_zone_name)::text, oc.state_arrive_ts)                                AS arrive_ts,
           to_char(timezone((bpd.time_zone_name)::text, inc.update_date), 'YYYY-MM-DD'::text)      AS up_price_date,
           to_char(timezone((bpd.time_zone_name)::text, inc.update_date), 'HH24:MI:SS'::text)      AS up_price_time,
           timezone((bpd.time_zone_name)::text, inc.update_date)                                   AS up_price_ts,
           CASE
               WHEN (inc.update_date IS NOT NULL and oc.payment_sub_price != oc.payment_trip_price) THEN true
               ELSE false
               END                                                                                 AS is_up_price,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_taken_ts), 'YYYY-MM-DD'::text)    AS take_date,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_taken_ts), 'HH24:MI:SS'::text)    AS take_time,
           to_char(timezone((bpd.time_zone_name)::text, ohsn.date), 'HH24:MI:SS'::text)            AS wait_time,
           to_char(timezone((bpd.time_zone_name)::text, ohs.date), 'HH24:MI:SS'::text)             AS wait_time_end,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_complete_ts), 'YYYY-MM-DD'::text) AS complete_date,
           to_char(timezone((bpd.time_zone_name)::text, oc.state_complete_ts), 'HH24:MI:SS'::text) AS complete_time,
           timezone((bpd.time_zone_name)::text, oc.state_complete_ts)                              AS complete_ts,
           bz_f.name                                                                               AS zone_from,
           bz_t.name                                                                               AS zone_to,
           oc.payment_trip_price,
           oc.payment_sub_price,
           oc.payment_avg_price,
           (oc.payment_trip_price - oc.payment_sub_price)                                          AS diff_price,
           CASE
               WHEN (inc.update_type = 1 and oc.payment_sub_price != oc.payment_trip_price) THEN 'Да'::text
               ELSE ''::text
               END                                                                                 AS client_update,
           CASE
               WHEN (inc.update_type = 2 and oc.payment_sub_price != oc.payment_trip_price) THEN 'Да'::text
               ELSE ''::text
               END                                                                                 AS driver_update,
           oc.driver_id,
           (((dc.name)::text || ' '::text) || (dc.lastname)::text)                                 AS driver_names,
           dc.phone                                                                                AS driver_phone,
           CASE
               WHEN (oc.state_id = 601) THEN 'Да'::text
               ELSE ''::text
               END                                                                                 AS partner,
           cc.nickname                                                                             AS client_nickname,
           cc.user_id,
           CASE
               WHEN (oc.contact_phone IS NULL) THEN cc.phone_additional
               ELSE oc.contact_phone
               END                                                                                 AS client_phone,
           CASE
               WHEN (oc.operator_id IS NULL) THEN 'Приложение'::text
               ELSE (oc.operator_id)::text
               END                                                                                 AS operator,
           oc.operator_id,
           CASE
               WHEN (oc.payment_method_id = 3) THEN comp.name_short
               ELSE ''::character varying
               END                                                                                 AS company_name,
           oc.comment,
           dst.description                                                                         AS state,
           dbp.display_name                                                                        AS billing_type,
           oc.billing_type                                                                         AS billing_type_id,
           CASE
               WHEN (oc.billing_type = 1) THEN dt.name
               WHEN (oc.billing_type = 2) THEN dp.name
               WHEN (oc.billing_type = 3) THEN ds.name
               ELSE NULL::character varying
               END                                                                                 AS billing_name,
           ((((((oc.route -> 0) ->> 'town'::text) || ' '::text) || ((oc.route -> 0) ->> 'street'::text)) ||
             ' '::text) ||
            ((oc.route -> 0) ->> 'house'::text))                                                   AS route_from,
           CASE
               WHEN ((oc.route -> 2) IS NOT NULL) THEN (
                       (((((oc.route -> 1) ->> 'town'::text) || ' '::text) || ((oc.route -> 1) ->> 'street'::text)) ||
                        ' '::text) || ((oc.route -> 1) ->> 'house'::text))
               ELSE ''::text
               END                                                                                 AS route_lap_first,
           CASE
               WHEN ((oc.route -> 3) IS NOT NULL) THEN (
                       (((((oc.route -> 2) ->> 'town'::text) || ' '::text) || ((oc.route -> 2) ->> 'street'::text)) ||
                        ' '::text) || ((oc.route -> 2) ->> 'house'::text))
               ELSE ''::text
               END                                                                                 AS route_lap_second,
           CASE
               WHEN ((oc.route -> 4) IS NOT NULL) THEN (
                       (((((oc.route -> 3) ->> 'town'::text) || ' '::text) || ((oc.route -> 3) ->> 'street'::text)) ||
                        ' '::text) || ((oc.route -> 3) ->> 'house'::text))
               ELSE ''::text
               END                                                                                 AS route_lap_third,
           ((((((oc.route -> '-1'::integer) ->> 'town'::text) || ' '::text) ||
              ((oc.route -> '-1'::integer) ->> 'street'::text)) || ' '::text) ||
            ((oc.route -> '-1'::integer) ->> 'house'::text))                                       AS route_to,
           (st_di.distanses ->> '202'::text)                                                       AS ds_to_client_wait,
           (st_di.distanses ->> '301'::text)                                                       AS ds_to_client_complete,
           (st_di.distanses_to_end ->> '301'::text)                                                AS ds_to_end_complete,
           oc.route_distance,
           (st_di.distanses ->> '201'::text)                                                       AS ds_to_client_found,
           (st_di.distanses ->> '401'::text)                                                       AS ds_to_client_declined,
           (st_di.distanses_to_end ->> '401'::text)                                                AS ds_to_end_declined,
           (st_di.distanses ->> '403'::text)                                                       AS ds_to_client_declined_cl,
           (st_di.distanses_to_end ->> '403'::text)                                                AS ds_to_end_declined_cl,
           state.destination
    FROM (((((((((((((((orders.common oc
        LEFT JOIN (SELECT h_payment.id,
                          h_payment.order_id,
                          h_payment.update_date,
                          h_payment.d_payment_update_type_id                                             AS update_type,
                          row_number() OVER (PARTITION BY h_payment.order_id ORDER BY h_payment.id DESC) AS rownum
                   FROM orders.h_payment
                   WHERE (h_payment.d_payment_update_type_id = ANY (ARRAY [1, 2]))) inc ON (((inc.rownum = 1) AND (inc.order_id = oc.id))))
        LEFT JOIN base.zones bz_f ON ((bz_f.id = oc.route_zone_from_id)))
        LEFT JOIN base.zones bz_t ON ((bz_t.id = oc.route_zone_to_id)))
        LEFT JOIN base.projects_data bpd ON ((bpd.id = oc.project_id)))
        LEFT JOIN application.driver_card dc ON ((dc.user_id = oc.driver_id)))
        LEFT JOIN application.client_card cc ON ((cc.user_id = oc.client_id)))
        LEFT JOIN (select min(update_date) as date, order_id
                   from orders.h_state
                   where state_id = 203
                   group by state_id, order_id) ohs on ohs.order_id = oc.id
        LEFT JOIN (select update_date as date, order_id
                   from orders.h_state
                   where state_id = 202) ohsn on ohsn.order_id = oc.id
        LEFT JOIN contract.clients cont ON ((cont.client_id = oc.client_id)))
        LEFT JOIN contract.company_card comp ON ((cont.company_id = comp.id)))
        LEFT JOIN orders.d_states dst ON ((dst.id = oc.state_id)))
        LEFT JOIN billing.d_billing_types dbp ON ((dbp.id = oc.billing_type)))
        LEFT JOIN billing.d_tariffs dt ON ((dt.id = oc.billing_id)))
        LEFT JOIN billing.d_packages dp ON ((dp.id = oc.billing_id)))
        LEFT JOIN billing.d_sessions ds ON ((ds.id = oc.billing_id)))
        LEFT JOIN (SELECT h_state.order_id,
                          h_state.driver_id,
                          json_object(array_agg((h_state.state_id)::text),
                                      array_agg((h_state.distance)::text))        AS distanses,
                          json_object(array_agg((h_state.state_id)::text),
                                      array_agg((h_state.distance_to_end)::text)) AS distanses_to_end
                   FROM orders.h_state
                   GROUP BY h_state.order_id, h_state.driver_id) st_di ON (((st_di.order_id = oc.id) AND (oc.driver_id = st_di.driver_id))))
             LEFT JOIN (SELECT res.order_id,
                               array_to_string(
                                       array_agg(((((((res.driver_id || ' '::text) || res.status) || ' '::text) ||
                                                    (res.phone)::text) || ' '::text) || res.update_date)),
                                       chr(10)) AS destination
                        FROM (SELECT hs.order_id,
                                     CASE
                                         WHEN (hs.state_id = 201) THEN 'Назначен'::text
                                         ELSE (('Снялся по причине "'::text || (tr.description)::text) || '"'::text)
                                         END AS status,
                                     hs.driver_id,
                                     dc_1.phone,
                                     hs.update_date
                              FROM (((orders.h_state hs
                                  LEFT JOIN orders.d_states ds_1 ON ((ds_1.id = hs.state_id)))
                                  LEFT JOIN application.driver_card dc_1 ON ((dc_1.user_id = hs.driver_id)))
                                       LEFT JOIN orders.d_tookoff_reasons tr
                                                 ON ((tr.id = (substr(reverse((hs.comment)::text), 1, 1))::integer)))
                              WHERE ((hs.driver_id IS NOT NULL) AND (hs.state_id = ANY (ARRAY [201, 102])))) res
                        GROUP BY res.order_id) state ON ((state.order_id = oc.id)));