orders.sql

SELECT
    orders.created_at AS created_at,
    products.id AS product_id,
    products.article,
       (
        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
        ) AS category,
    products.title AS product_title,
    unit_id.title AS unit_title,
    products.on_hand AS stock,
    products.sold as sold,
    orders_item.quantity AS item_qty,   -- количество фасовок в заказе
    packings.quantity AS packings_qty,                            -- количество единиц в фасовках
    orders_item.quantity * packings.quantity as product_qty,      -- количество единиц
    (orders_item.retail_price::float / (10000.0)) / packings.quantity AS retail_price_unit,  -- розничная цена единицы
    (orders_item.purchase_price::float / (10000.0)) / packings.quantity AS purchase_price_unit, -- закупочная цена единицы
    orders_item.retail_price::float / (10000.0) AS retail_price,  -- розничная цена фасовки
    orders_item.purchase_price::float / (10000.0) AS purchase_price, -- закупочная цена фасовки
-- Надо вывести все в одной колонке
    (products.classes->>'revenue_class') || (products.classes->>'profit_class') || '/' || (products.classes->>'variance_class') AS revenue_class

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

WHERE (
    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 product_id