Отчет всех движений товаров в магазине

products_reposts.sql

SELECT
products.id as id,
products.title as product_title,
products.article,
products.state,
products.on_hand as stock, --количество в базовых единицах оприходования: бисер в граммах, бусины в шт, нитях.
category.title as category,
products.sold as sold, -- количество проданных базовых единиц товара
(products.classes->>'revenue_class') || (products.classes->>'profit_class') || '/' || (products.classes->>'variance_class') AS revenue_class,

(
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 categories,

(
    SELECT count(distinct orders_item.order_id)
    FROM hm_store_orders_items AS 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_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'
        AND (products.id = packings.product_id)
        AND orders_item.created_at > CURRENT_DATE - INTERVAL '3 months'
) as orders_3month, -- Заказов товара за 3 месяца

( select sum(forecast.forecast)
from hm_dw_sales_forecast forecast
where (products.id = forecast.product_id) and forecast.method = 'svm'
) as forecast_3month_svm, -- Прогноз товара за 3 месяца алгоритмом SVM

( select sum(forecast.forecast)
from hm_dw_sales_forecast forecast
where (products.id = forecast.product_id) and forecast.method = 'arima'
) as forecast_3month_arima, -- Прогноз товара за 3 месяца алгоритмом ARIMA

( select sum(orders_item.quantity * packings.quantity)
from hm_store_orders_items as 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_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'
        AND (products.id = packings.product_id)
        AND orders_item.created_at > CURRENT_DATE - INTERVAL '3 months'
) as sale_3month, -- Продаж товара за 3 месяца

( select sum(orders_item.quantity * packings.quantity)
from hm_store_orders_items as 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_measure_units unit_id ON unit_id.id = products.unit_id
where (packings.product_id = products.id) and (orders_item.created_at > CURRENT_DATE - INTERVAL '1 months')
) as sale_1month, -- Продаж товара за 1 месяц

products.retail_price::float / (10000.0) as retail_price, -- розничная цена фасовки
products.purchase_price::float / (10000.0) as purchase_price, -- закупочная цена фасовки

( select sum(purchase_price::float / (10000.0) * (orders_item.quantity * packings.quantity))
from hm_store_orders_items as 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_measure_units unit_id ON unit_id.id = products.unit_id
where (packings.product_id = products.id) and (orders_item.created_at > CURRENT_DATE - INTERVAL '1 months')
) as sale_month_metrics, -- себестоимость проданного за период товара

products.on_hand * (products.purchase_price::float / (10000.0)) as summ_product_purchase_stock -- стоимость всего товарного запаса

FROM hm_store_products products

LEFT JOIN (
SELECT DISTINCT ON (category_product.product_id) category_product.product_id, category.title, category.parent_id, category.id
FROM (
hm_taxonomies_categories category
JOIN hm_taxonomies_category_product category_product ON category_product.category_id = category.id
)
WHERE category.parent_id IS NULL
) category ON category.product_id = products.id