fixed.sql

WITH data3 AS(
	WITH data2 AS (
  		WITH data1 as (
		  SELECT
			p.id,
			p.sku,
			p.name,
			p.ordering_multiplier,
			p.minimum_desired_quantity,
			qs.quantity as on_hand,

		  ( SELECT COALESCE (( SELECT SUM( s.quantity)
		  FROM sale_invoicelineitem as s LEFT JOIN sale_invoice as i ON (i.id = s.invoice_id)
		  WHERE s.product_id = p.id AND i.date > NOW() - ( interval '7 days')
		  GROUP BY s.product_id), 0)) as week1,

		  ( SELECT COALESCE (( SELECT SUM( s.quantity)
		  FROM sale_invoicelineitem as s LEFT JOIN sale_invoice as i ON (i.id = s.invoice_id)
		  WHERE s.product_id = p.id AND i.date < NOW() - ( interval '7 days') AND
			i.date > NOW() - ( interval '14 days')
		  GROUP BY s.product_id), 0)) as week2,

		  ( SELECT COALESCE (( SELECT SUM( s.quantity)
		  FROM sale_invoicelineitem as s LEFT JOIN sale_invoice as i ON (i.id = s.invoice_id)
		  WHERE s.product_id = p.id AND i.date < NOW() - ( interval '14 days') AND
			i.date > NOW() - ( interval '21 days')
		  GROUP BY s.product_id), 0)) as week3,

		  ( SELECT COALESCE (( SELECT SUM( s.quantity)
		  FROM sale_invoicelineitem as s LEFT JOIN sale_invoice as i ON (i.id = s.invoice_id)
		  WHERE s.product_id = p.id AND i.date < NOW() - ( interval '21 days') AND
			i.date > NOW() - ( interval '28 days')
		  GROUP BY s.product_id), 0)) as week4,

			/*customer_ordered_qty is the sum of all customer order lines where the status of the of the order is 'O' Open,
				 'R' Received or 'P' picked -> these are orders that we need to fill and will required inventory to do so
				  */
		  ( SELECT COALESCE (( SELECT SUM(col.qty_ordered)
		  FROM nsale_customerorderline col FULL JOIN nsale_customerorder co ON co.id = col.order_id
		  WHERE
		  (col.product_id = p.id AND co.status = 'O') OR (col.product_id = p.id AND co.status = 'R')
		  OR (col.product_id = p.id AND co.status = 'P') OR
		  (col.product_id = p.id AND co.status = 'C')), 0)) as customer_ordered_qty,
			/*
				coming_for_stock is inventory that is confirmed to already be coming to the store - so we can safely assume that
				we will receive it in the very near future
				 */
		  ( SELECT COALESCE (( SELECT SUM(pil.actual_quantity)
		  FROM npurchasing_purchasinginvoiceline pil FULL JOIN npurchasing_purchasinginvoice pi
		  ON pil.invoice_id = pi.id
		  WHERE (pi.date_posted IS NULL AND pil.product_id = p.id)), 0)) as coming_for_stock,
			/*
				pending_return_to_vendor is inventory that will be sent back to the vendor usually because it is damaged or out of
				date, even though it is in our "on_hand" we have to treat it as if we cannot use it because most likely we cannot sell it
				 */
		  ( SELECT COALESCE (( SELECT SUM( CASE WHEN pil.actual_quantity < 0
		  THEN pil.actual_quantity * -1 END )
		  FROM npurchasing_purchasinginvoiceline pil FULL JOIN npurchasing_purchasinginvoice pi
		  ON pil.invoice_id = pi.id
		  WHERE (pi.date_posted IS NULL AND pil.product_id = p.id)), 0)) as pending_return_to_vendor

		  FROM product_product p
		  LEFT JOIN product_quantitystored qs ON p.id = qs.product_id
		  LEFT JOIN product_storagelocation sl ON qs.location_id = sl.id
		  WHERE p.default_supplier_id = 2 AND p.active = True AND p.do_not_reorder = False AND sl.id = 1
	  ) SELECT
		*,
		round(((week1 :: decimal + week2 + week3 + week4) / 4), 2) as weekly_average,
	  (on_hand - pending_return_to_vendor + coming_for_stock) as effective_inventory
	  FROM data1
  ) SELECT *,
	  (round( CASE WHEN effective_inventory > 0 THEN effective_inventory ELSE 0 END / NULLIF (weekly_average, 0), 2)) as weeks_of_supply,
	  CASE WHEN (weekly_average * 3 + customer_ordered_qty - effective_inventory) > 0 THEN (weekly_average * 3 + customer_ordered_qty - effective_inventory) ELSE 0 END as required_amount_to_order
	FROM data2
) SELECT 
	*, (ceil(required_amount_to_order) + ((ceil(required_amount_to_order) % ordering_multiplier))) as rounded_order_qty 
	, CASE WHEN ceil(required_amount_to_order) = 0 THEN 0 WHEN (ceil(required_amount_to_order) % ordering_multiplier) = 0 THEN ceil(required_amount_to_order) ELSE (ceil(required_amount_to_order) + (ordering_multiplier - (ceil(required_amount_to_order) % ordering_multiplier))) END as rounded_order_qty_fixed
	FROM data3
WHERE id IN (44, 330029, 48)
;