Untitled
raw download clone
SQL
views 82
,
size 3928 b
WITH campaigns AS (
	SELECT
		C.pp_campaign_id,
		C.description || ' ' || C.pp_campaign_id AS campaign_name,
		C.url,
		C.start_at,
		C.finish_at,
		C.art_count,
		COALESCE(SUM(DISTINCT(C.data->'stock_count_by_supplier'->>ORG.ref1c::text)::int), 0) AS stock_count_by_supplier,
		COALESCE(SUM(DISTINCT(C.data->'art_count_by_supplier'->>ORG.ref1c::text)::int), 0) AS art_count_by_supplier,
		ARRAY_TO_STRING(ARRAY_REMOVE(ARRAY_AGG(DISTINCT ORG.name ), NULL), ', ') AS org_names
	FROM campaign C
	INNER JOIN supplier_org ORG ON ORG.ref1c = ANY(C.suppliers)
	WHERE C.start_at >= '2018-08-20'::date AND C.start_at < '2018-09-03'::date + 1
		AND (NULL::text IS NULL OR C.description ILIKE '%' || NULL::text || '%')
		AND (NULL::int IS NULL OR C.pp_campaign_id = NULL::int)
		AND (NULL::uuid[] IS NULL OR ORG.ref1c = ANY(NULL::uuid[]))
		AND (NULL::int[] IS NULL OR ORG.supplier_id = ANY(NULL::int[]))											 
	GROUP BY C.id
),
			 
orderitems AS (
	SELECT
		OI.pp_campaign_id,
		ARRAY_AGG(OI.pp_batch_id) AS pp_batch_ids,
		COALESCE(COUNT(DISTINCT OI.pp_product_id::bigint << 32 + OI.pp_color_id) FILTER (WHERE NOT OI.is_canceled), 0) AS art_sold,
		COUNT(DISTINCT OI.pp_product_id::bigint << 32 + OI.pp_color_id) AS art_sold_before_cancels,
		COALESCE(SUM(OI.current_quantity) FILTER (WHERE NOT OI.is_canceled), 0) order_stock,
		COALESCE(COUNT(DISTINCT OI.order_id) FILTER (WHERE NOT OI.is_canceled), 0) AS order_count,
		COALESCE(SUM(OI.current_quantity * OI.supplier_price) FILTER (WHERE NOT OI.is_canceled), 0) supplier_amount,
		COALESCE(SUM(OI.current_quantity * OI.new_price) FILTER (WHERE NOT OI.is_canceled), 0) AS selling_amount,
		SUM(OI.quantity * OI.new_price) AS selling_amount_before_cancels,
		(SELECT SUM(delivery_cost) FROM orders WHERE id = ANY(ARRAY_AGG(DISTINCT OI.order_id))) AS delivery_amount,
		ARRAY_TO_STRING(ARRAY_REMOVE(ARRAY_AGG(DISTINCT OW.name), NULL), ', ') AS owner_names															
	FROM orderitem OI
	INNER JOIN campaigns C on C.pp_campaign_id = OI.pp_campaign_id
	INNER JOIN supplier_contract SC ON SC.ref1c = OI.contract_ref1c
	INNER JOIN supplier_org ORG ON ORG.id = SC.org_id																
    INNER JOIN owner OW ON OW.ref1c = SC.owner_id
	WHERE OI.created_at >= '2018-08-20'::date AND OI.created_at < '2018-09-03'::date + 1
	 AND (NULL::uuid[] IS NULL OR ORG.ref1c = ANY(NULL::uuid[])) 
	GROUP BY OI.pp_campaign_id
),
			 
users AS (
	SELECT
		OI.pp_campaign_id,
		ARRAY_AGG(DISTINCT(buyer_owner_ref1c)) AS buyers_owners_ref1c,
		array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT U.name ), NULL), ', ') AS buyer_names
	FROM batch B
	INNER JOIN orderitems OI ON B.pp_batch_id = ANY(OI.pp_batch_ids)
	LEFT JOIN users U on U.ref1c = B.buyer_owner_ref1c
	GROUP BY OI.pp_campaign_id
)

SELECT C.pp_campaign_id,
	   C.campaign_name,
	   C.url,
	   C.start_at,
	   C.finish_at,
       C.art_count,
	   C.stock_count_by_supplier,
	   C.art_count_by_supplier,
	   C.org_names,
	   OI.art_sold,
	   OI.art_sold_before_cancels,
	   OI.order_stock,
	   OI.order_count,
	   OI.supplier_amount,
	   OI.selling_amount,
	   OI.selling_amount_before_cancels,
	   OI.delivery_amount,
	   U.buyer_names,
	   OI.owner_names,
	   OI.selling_amount / NULLIF(OI.order_stock, 0) AS avg_selling_price,
       OI.selling_amount / NULLIF(OI.order_count, 0) AS avg_order_amount,
       COALESCE((OI.selling_amount - OI.supplier_amount) * 100. / NULLIF(OI.selling_amount, 0),0) AS percent_order_margin_amount,
       OI.art_sold_before_cancels * 100. / NULLIF(C.art_count_by_supplier, 0) AS percent_art_sold_before_cancels,
       OI.art_sold * 100. / NULLIF(C.art_count_by_supplier, 0) AS percent_art_sold
FROM campaigns C
LEFT JOIN orderitems OI ON OI.pp_campaign_id = C.pp_campaign_id
LEFT JOIN users U ON U.pp_campaign_id = C.pp_campaign_id
WHERE (NULL::uuid[] IS NULL OR NULL::uuid[] && U.buyers_owners_ref1c)
close fullscreen
Login or Register to edit or fork this paste. It's free.