Skip to content

Instantly share code, notes, and snippets.

@un1ko85
Created March 31, 2019 08:32
Show Gist options
  • Save un1ko85/e0325b98211e9c4cd6a4f14c75a8b9d7 to your computer and use it in GitHub Desktop.
Save un1ko85/e0325b98211e9c4cd6a4f14c75a8b9d7 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment