sales product .sql

-- Продажи товаров с атрибутами
SELECT
    orders.created_at AS created_at,
    products.id,
    -- Категории
       (
       SELECT string_agg(c.title, ' / ')
       FROM hm_taxonomies_category_product c_p
              JOIN hm_taxonomies_categories c ON c_p.category_id = c.id
       WHERE c_p.product_id = products.id -- todo: order
       ) AS categories,
    orders_item.retail_price::float / (10000.0) * orders_item.quantity AS summ_retail_price,     -- розничная цена фасовки
    orders_item.purchase_price::float / (10000.0) * orders_item.quantity AS summ_purchase_price, -- закупочная цена фасовки
    (
        GREATEST(round((SELECT abs((sum(adjustments.amount) / 10000.0)) AS abs
        FROM hm_adjustments adjustments
                                               WHERE (((adjustments.adjustable_type)::text = 'order_item'::text) AND
                                                      (adjustments.amount < 0) AND
                                                      (adjustments.adjustable_id = orders_item.id) AND
                                                      (adjustments.is_neutral <> true))), 2),
                                        (0)::numeric)) AS discount_amount,
       attributes.*

FROM hm_store_orders_items orders_item

    JOIN hm_store_orders orders ON orders.id = orders_item.order_id
    JOIN hm_store_product_packings packings ON packings.id = orders_item.purchasable_id
    JOIN hm_store_products products ON products.id = packings.product_id
    JOIN hm_measure_units unit_id ON unit_id.id = products.unit_id
    JOIN hm_dw_attributed_products attributes ON attributes.product_id = products.id

WHERE
--     orders_item.created_at >  CURRENT_DATE - INTERVAL '12 months' AND
    (
        SELECT state.type
        FROM hm_store_states state
        WHERE (state.graph = 'order_process' AND state.stateful_type = 'order' AND state.stateful_id = orders.id)
        ORDER BY state.id DESC
        LIMIT 1
    ) <> 'cancelled'

ORDER BY created_at;