dwh_categories

CREATE OR REPLACE ALGORITHM = MERGE VIEW dwh_categories AS

SELECT CAST(c.categories_id AS INT) AS categories_id
    , CAST(IFNULL(c.suppliers_id,0) AS INT) AS suppliers_id
    , CAST(IFNULL(c.suppliers_contracts_id,0) AS INT) AS suppliers_contracts_id
    , CAST(DATE_FORMAT(IFNULL(c.date_start, '0000-00-00'),'%Y%m%d') AS INT) AS date_start_id
    , CAST(DATE_FORMAT(IFNULL(c.date_end, '0000-00-00'),'%Y%m%d') AS INT) AS date_end_id
    , CAST(DATE_FORMAT(IFNULL(c.date_start, '0000-00-00'),'%H%i00') AS INT) AS time_start_id
    , CAST(DATE_FORMAT(IFNULL(c.date_end, '0000-00-00'),'%H%i00') AS INT) AS time_end_id
    , IFNULL(DATEDIFF(c.date_end,c.date_start),0) AS categories_duration
    , c.categories_status AS categories_status_id
    , cd.categories_name AS categories_name
    , cd.categories_heading_title AS categories_title
    , IFNULL(c.categories_parent, c.categories_id) AS categories_parent_id
    , IFNULL(cdp.categories_name, cd.categories_name) AS categories_parent_name
    , IF(c.categories_parent IS NULL OR c.categories_parent = 0, 1, 0) AS is_main_categories
    , IFNULL(cr12.customers_id,0) AS content_employees_id
    , IFNULL(c.customers_id,0)  AS brand_employees_id
    , IFNULL(cr28.customers_id,0) AS analitic_employees_id
    , (SELECT
            COUNT(DISTINCT cbtp.classifier_age_groups_id) AS count_age_groups
        FROM classifier_binds_to_products AS cbtp
            JOIN products_to_categories AS ptc ON cbtp.object_id = ptc.products_id
        WHERE ptc.categories_id = c.categories_id
        ) AS count_age_groups
    , (SELECT
            COUNT(DISTINCT cbtp.classifier_categories_id) AS count_categories
        FROM classifier_binds_to_products AS cbtp
            JOIN products_to_categories AS ptc ON cbtp.object_id = ptc.products_id
        WHERE ptc.categories_id = c.categories_id
        ) AS count_categories
    , (SELECT
            COUNT(DISTINCT btc.brands_id) AS count_brands
        FROM brands_to_categories AS btc
        WHERE btc.categories_id = c.categories_id
        ) AS count_brands

--  , IFNULL(c.categories_types_id,0) AS categories_types_id
    , cf.categories_forms_name as categories_types_name
    -- , IFNULL(c.categories_types_id,0) AS categories_stock_types_id
    , ct.categories_types_name as categories_stock_name
    , IFNULL(cg.classifier_groups_name, '-') AS categories_priority_groups_name

FROM categories AS c
    JOIN categories_description AS cd ON cd.categories_id = c.categories_id
    JOIN categories_status AS cs ON cs.status_id = c.categories_status
    -- контент-менеджер
    LEFT JOIN categories_responsible AS cr12 ON cr12.categories_id = c.categories_id AND cr12.acl_roles_id = 12

    LEFT JOIN categories_responsible AS cr28 ON cr28.categories_id = c.categories_id AND cr28.acl_roles_id = 28
    -- LEFT JOIN customers AS c28 ON c28.customers_id = cr28.customers_id
    LEFT JOIN categories_description as cdp on cdp.categories_id = c.categories_parent
LEFT JOIN categories_types as ct on ct.categories_types_id = c.categories_types_id
LEFT JOIN categories_forms as cf on cf.categories_forms_id = c.categories_forms_id
LEFT JOIN classifier_groups as cg on cg.classifier_groups_id = c.classifier_groups_id

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