dwh_categories_bonuses

CREATE OR REPLACE ALGORITHM = MERGE VIEW dwh_categories_bonuses AS

SELECT

 cb.categories_bonuses_id
, cb.categories_bonuses_type AS type
, cb.categories_bonuses_amount AS amount
, cb.categories_bonuses_credits AS credits
, cb.categories_bonuses_credits_lifetime AS redits_lifetime
, cb.categories_id AS categories_id
, cb.is_deleted
, CAST(DATE_FORMAT(cb.date_added,'%Y%m%d') AS INT) AS date_added_id
FROM categories_bonuses AS cb
JOIN dwh_upload_config AS duc ON duc.component = 'dwh_categories_bonuses'
WHERE
         (duc.date_from_use = 0 OR cb.date_added >= duc.date_from)
         AND (duc.date_to_use = 0 OR cb.date_added <= duc.date_to)
         AND (duc.id_from_use = 0 OR cb.categories_bonuses_id >= duc.id_from)
         AND (duc.id_to_use = 0 OR cb.categories_bonuses_id <= duc.id_to)

;

CREATE OR REPLACE ALGORITHM = MERGE VIEW dwh_categories_bonuses_to_orders AS

SELECT cbto.orders_id
, cbto.categories_bonuses_id
, cb.categories_bonuses_credits AS credits
, CAST(DATE_FORMAT(cbto.date_added,'%Y%m%d') AS INT) AS lifetime_start_id
, CAST(DATE_FORMAT(DATE_ADD(cbto.date_added, INTERVAL cb.categories_bonuses_credits_lifetime DAY),'%Y%m%d') AS INT) AS lifetime_end_id
FROM categories_bonuses_to_orders AS cbto
JOIN categories_bonuses AS cb ON cb.categories_bonuses_id = cbto.categories_bonuses_id
JOIN dwh_upload_config AS duc ON duc.component = 'dwh_categories_bonuses_to_orders'
WHERE
         (duc.date_from_use = 0 OR cbto.date_added >= duc.date_from)
         AND (duc.date_to_use = 0 OR cbto.date_added <= duc.date_to)
         AND (duc.id_from_use = 0 OR cbto.orders_id >= duc.id_from)
         AND (duc.id_to_use = 0 OR cbto.orders_id <= duc.id_to)
;