Skip to content

Instantly share code, notes, and snippets.

@un1ko85
Last active March 31, 2019 08:30
Show Gist options
  • Save un1ko85/3414b2df9911dafc63c36649814a09cc to your computer and use it in GitHub Desktop.
Save un1ko85/3414b2df9911dafc63c36649814a09cc to your computer and use it in GitHub Desktop.
Отчет всех движений товаров в магазине
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment