dwh_orders

CREATE OR REPLACE ALGORITHM = MERGE VIEW dwh_orders AS

SELECT o.orders_id AS orders_id -- '№ заказа'
    , o.customers_id AS customers_id -- 'ID клиента'
    , IFNULL(umS.utm_value, '') AS UTM_source -- 'UTM_source (заказ)'
    , IFNULL(umM.utm_value, '') AS UTM_medium -- 'UTM_medium (заказ)'
    , IFNULL(umС.utm_value, '') AS UTM_campaign -- 'UTM_campaign (заказ)'
    , IFNULL(umT.utm_value, '') AS UTM_term -- 'UTM_term (заказ)'
    , IFNULL(umContent.utm_value, '') AS UTM_content -- 'UTM_content (заказ)'
    , os.orders_status_id
    , o.customers_status AS current_customers_status_id
    , IF(os.orders_status_id IN (10, 22), 'Доставлен', os.orders_status_name) AS orders_status_name -- 'Статус заказа'
    , IF(o.is_partial_return = 1, 'Частичный возврат', os.orders_status_name) AS orders_status_partial_return_name -- 'Статус заказа (с возвратом)'
    , IF(ot.discount_type = 2, ot.discount, 0) AS orders_sum_discount_promo -- 'Скидка за промо-код, руб.'
    , CAST(IFNULL(ot.discount, 0) AS DECIMAL(15,2)) AS orders_sum_discount
    , IFNULL(ot.discount_type, 0) AS discount_type
    , IFNULL(ot.credit,0) AS orders_sum_credit -- 'Бонусы в заказе'
    , IFNULL((SELECT
                        pl.number
                FROM points_log_to_orders AS plto
                    LEFT JOIN remote_points_log AS pl
                                ON pl.points_log_id = plto.points_log_id
                                AND pl.points_change_type_id IN (13, 14, 15, 16, 17, 18)
                    WHERE plto.orders_id = o.orders_id
                    ORDER BY plto.points_log_id DESC
                    LIMIT 1
                                )

                                , 0) AS orders_sum_points_count  -- 'Баллы в заказе'

    , CAST(IFNULL(ot.total, 0) + IFNULL(ot.noncash, 0) AS DECIMAL(15,2)) AS orders_sum_total -- 'Сумма заказа, руб.'
    , IFNULL(ot.noncash, 0) AS orders_sum_noncash
    , IFNULL(ot.subtotal, 0) AS orders_sum_subtotal
    , IFNULL(ot.shipping_original,0) AS orders_sum_shipping_original
    , o.orders_consolidation
    , o.shipping_class AS shipping_class -- 'Способ доставки'
    , CAST(IFNULL(ot.shipping,0) AS DECIMAL(15,2)) AS orders_sum_shipping -- 'Стоимость доставки, руб.'
    , IFNULL(ot.service_cost,0) AS orders_sum_service_cost -- 'Стоимость сервисного сбора, руб.'
    , CONCAT(o.payment_method) AS payment_method -- 'Способ оплаты'
    , IFNULL(CONCAT(bg.boxes_group_title, ' (', b.boxes_title, ')'), 'без упаковки') AS boxes_type -- 'Упаковка'
    , o.customers_fias_id
    , IF(o.customers_fias_id IS NULL OR o.customers_fias_id = '' OR f.id IS NULL, TRIM(COALESCE(o.customers_city, '')), COALESCE(f.offname, '')) AS orders_delivery_sity -- 'Название нас.пункта доставки'
    , IFNULL(op.platform, '') AS orders_platform_source -- 'Устройство, с которого сделан заказ'
    , DATE(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00')) AS orders_purchased_date
    , CAST(DATE_FORMAT(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00'),'%Y%m%d') AS INT) AS orders_purchased_date_id
    , TIME(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00')) AS orders_purchased_time -- 'Время заказа'
    , CAST(DATE_FORMAT(COALESCE(po.date_purchased, o.date_purchased, '0000-00-00'),'%H%i%s') AS INT) AS orders_purchased_time_id
    , DATE(COALESCE(o.date_delivery_real, '0000-00-00')) AS orders_date_delivery_real
    , CAST(DATE_FORMAT(COALESCE(o.date_delivery_real, '0000-00-00'),'%Y%m%d') AS INT) AS orders_date_delivery_real_id
    , IFNULL(pr.promocodes_word, '') AS promocodes_name -- 'Название промокода'
    , IFNULL(pr.promocodes_value, '') AS promocodes_value -- 'Сумма скидки за промокод'
    , IF((SELECT MIN(oo.orders_id) FROM orders AS oo WHERE oo.customers_id = o.customers_id) = o.orders_id, 1,0) AS is_first_customers_orders
    , IFNULL(osr.parent_orders_id, o.orders_id) AS parent_orders_id
    , IF(osr.parent_orders_id IS NULL, COALESCE((SELECT distinct 1 FROM orders_separate_results AS osr2 WHERE osr2.parent_orders_id = o.orders_id),0), 2) parent_orders_type_id
    , o.last_date_update AS last_date_update
    , c.sites_id AS sites_id
    , o.customers_postamat_id AS postamat_id

FROM orders AS o
    JOIN orders_status AS os ON os.orders_status_id = o.orders_status
    LEFT JOIN fias AS f ON f.id = o.customers_fias_id
    JOIN customers c ON c.customers_id = o.customers_id
    LEFT JOIN customers_source cs ON cs.customers_id = c.customers_id
    LEFT JOIN utm_marks_binds umb ON umb.utm_marks_binds_id = cs.utm_marks_binds_id
    LEFT JOIN utm_marks umS ON umS.utm_marks_id = umb.utm_source
    LEFT JOIN utm_marks umM ON umM.utm_marks_id = umb.utm_medium
    LEFT JOIN utm_marks umС ON umС.utm_marks_id = umb.utm_campaign
    LEFT JOIN utm_marks umT ON umT.utm_marks_id = umb.utm_term
    LEFT JOIN utm_marks umContent ON umContent.utm_marks_id = umb.utm_content
    LEFT JOIN orders_total ot ON ot.orders_id = o.orders_id

    LEFT JOIN boxes b ON b.boxes_id = o.orders_box_id
    LEFT JOIN boxes_group bg ON bg.boxes_group_id = b.boxes_group_id
    LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
             -- LEFT JOIN tmp_orders_first_customer_order as tmporfco on tmporfco.orders_id = o.orders_id
    LEFT JOIN orders_to_promocodes AS otp ON otp.orders_id = o.orders_id
    LEFT JOIN promocodes AS pr ON pr.promocodes_id = otp.promocodes_id
    LEFT JOIN orders_separate_results AS osr ON osr.orders_id = o.orders_id
    LEFT JOIN orders AS po ON osr.parent_orders_id = po.orders_id

    JOIN dwh_upload_config AS duc on duc.component = 'dwh_orders'
WHERE
    (duc.date_from_use = 0 OR o.last_date_update >= duc.date_from)
    AND (duc.date_to_use = 0 OR o.last_date_update <= duc.date_to)
    AND (duc.id_from_use = 0 OR o.orders_id >= duc.id_from)
    AND (duc.id_to_use = 0 OR o.orders_id <= duc.id_to)
;