SELECT
Q.*,
Q.selling_amount / NULLIF(Q.order_stock, 0) AS avg_selling_price,
Q.selling_amount / NULLIF(Q.order_count, 0) AS avg_order_amount,
COALESCE((Q.selling_amount - Q.supplier_amount) * 100. / NULLIF(Q.selling_amount, 0),0) AS percent_order_margin_amount,
Q.art_sold_before_cancels * 100. / NULLIF(Q.art_count, 0) AS percent_art_sold_before_cancels,
Q.art_sold * 100. / NULLIF(Q.art_count, 0) AS percent_art_sold
FROM (
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,
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 ORG.name ), NULL), ', ') AS org_names,
array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT OW.name), NULL), ', ') AS owner_names,
array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT U.name ), NULL), ', ') AS buyer_names
FROM campaign C
LEFT JOIN orderitem OI ON OI.pp_campaign_id = C.pp_campaign_id
LEFT JOIN batch B ON B.pp_batch_id = OI.pp_batch_id
INNER JOIN supplier_contract CC ON CC.ref1c = OI.contract_ref1c
INNER JOIN supplier_org ORG ON ORG.id = CC.org_id
LEFT JOIN users U on U.ref1c = B.buyer_owner_ref1c
LEFT JOIN owner OW ON OW.ref1c = CC.owner_id
WHERE C.start_at >= '2018-08-17'::date AND C.finish_at < '2018-08-31'::date + 1
AND (NULL::int[] IS NULL OR ORG.supplier_id = ANY(NULL::int[]))
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 C.suppliers && (NULL::uuid[]))
AND (NULL::uuid[] IS NULL OR B.buyer_owner_ref1c = ANY(NULL::uuid[]))
GROUP BY C.id
) Q