-- --------------------------------------Ingredient Details----------------------------------------------------------------
DROP FUNCTION bill."ReportIngredientByDay"(DATE, DATE, uuid, uuid, uuid);

CREATE OR REPLACE FUNCTION bill."ReportIngredientByDay"(
    _from DATE,
    _to DATE,
    _ingredient_id uuid,
    _restaurant_id uuid,
    _stock_id uuid
  )
  RETURNS TABLE(
    json json
  ) AS
$BODY$
DECLARE
BEGIN
  IF (_from > _to) THEN _from = _to; END IF;
  RETURN query
    SELECT array_to_json(array_agg(row_to_json(t)))
    FROM(
          SELECT s.DAY,
                (
                  SELECT SUM(ii.delta_count)
                  FROM inventory_ingredients ii
                  Inner Join inventories inven ON inven.id = ii.inventory_id
                  WHERE ii.ingredient_id = _ingredient_id
                    AND inven.is_active = TRUE
                    AND inven.draft = FALSE
                    AND inven.stock_id = _stock_id
                    AND inven.DATE >= s.DAY
                    AND inven.DATE <= s.DAY
                ) AS inventory,
                (
                    SELECT COALESCE(SUM(ii.COUNT), 0)
                    FROM invoice_ingredients ii
                    Inner Join invoices invo ON invo.id = ii.invoice_id
                    WHERE ii.is_active = TRUE
                      AND ii.ingredient_id = _ingredient_id
                      AND invo.is_active = TRUE
                      AND invo.draft = FALSE
                      AND invo.receiver_id = _stock_id
                      AND invo.DATE >= s.DAY
                      AND invo.DATE <= s.DAY
                ) AS invoice,
                (
                  SELECT COALESCE(SUM(ri.COUNT), 0)
                  FROM relocation_ingredients ri
                  Inner Join relocations r ON r.id = ri.relocation_id
                  WHERE ri.ingredient_id = _ingredient_id
                    AND r.is_active = TRUE
                    AND r.draft = FALSE
                    AND r.receiver_stock_id = _stock_id
                    AND r.DATE >= s.DAY
                    AND r.DATE <= s.DAY
                ) AS in_relocation,
                (
                  SELECT COALESCE(SUM(wi.COUNT), 0)
                  FROM write_off_ingredients wi
                  Inner Join write_offs w ON w.id = wi.write_off_id
                  WHERE wi.ingredient_id = _ingredient_id
                    AND w.is_active = TRUE
                    AND w.draft = FALSE
                    AND w.stock_id = _stock_id
                    AND w.DATE >= s.DAY
                    AND w.DATE <= s.DAY
                ) AS write_off,
                (
                  SELECT COALESCE(SUM(ri.COUNT), 0)
                  FROM relocation_ingredients ri
                  Inner Join relocations r ON r.id = ri.relocation_id
                  WHERE ri.ingredient_id = _ingredient_id
                    AND r.is_active = TRUE
                    AND r.draft = FALSE
                    AND r.sender_stock_id = _stock_id
                    AND r.DATE >= s.DAY
                    AND r.DATE <= s.DAY
                ) AS out_relocation,
                (
                  SELECT COALESCE(SUM(bc.COUNT), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_solds bcs ON bcs.id = bc.object_id
                  Inner Join bill.bills b ON b.id = bcs.bill_id
                  WHERE bc.ingredient_id = _ingredient_id
                    AND bc.stock_id = _stock_id
                    AND bc.DATE >= s.DAY
                    AND bc.DATE <= s.DAY
                    AND bc.calculate_type = 'subtract'
                    AND b.bill_type <> 5
                    AND b.bill_type <> 99
                ) AS solds,
                (
                  SELECT COALESCE(SUM(bc.COUNT), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_resigns bcr ON bcr.id = bc.object_id
                  Inner Join bill.bill_course_solds bcs ON bcs.id = bcr.bill_course_sold_id
                  Inner Join bill.bills b ON b.id = bcs.bill_id
                  WHERE bc.ingredient_id = _ingredient_id
                    AND bc.stock_id = _stock_id
                    AND bc.DATE >= s.DAY
                    AND bc.DATE <= s.DAY
                    AND bc.calculate_type = 'add'
                    AND b.bill_type <> 5
                    AND b.bill_type <> 99
                ) AS resings
          FROM generate_series(_from, _to, '1 day') AS s(DAY)
        ) t;
END;

$BODY$
  LANGUAGE plpgsql STABLE
  COST 50
  ROWS 1000;
ALTER FUNCTION bill."ReportIngredientByDay"(DATE, DATE, uuid, uuid, uuid)
  OWNER TO developer;