set client_min_messages = 'warning';


-- Beginnings of a budget module, released under the GPL v2 or later.
-- Copyright 2011 The LedgerSMB Core Team
--
-- Notes for future versions:
-- 1:  For 1.4, move to arrays of composites and unnest()
-- 2:  Move to new input argument semantics
-- 3:  Add array of composites to budget_info_ext for lines
-- 4:  Make department_id default to 0 and be not null
-- 5:  Convert type definitions to views.

BEGIN;

DROP TYPE IF EXISTS budget_info_ext CASCADE;

CREATE TYPE budget_info_ext AS (
   id INT,
   start_date date,
   end_date date ,
   reference text,
   description text,
   entered_by int,
   approved_by int,
   obsolete_by int,
   entered_at timestamp,
   approved_at timestamp,
   obsolete_at timestamp,
   entered_by_name text,
   approved_by_name text,
   obsolete_by_name text
);

COMMENT ON TYPE budget_info_ext IS
$$ This is the base budget_info type.  In 1.4, it will be renamed budget and
include an array of lines, but since we support 8.3, we cannot do that.

The id, start_date, end_date, reference, description, entered_by, approved_by,
entered_at, and approved_at fields reference the budget_info table.  The other
two fields refer to the possible joins. $$;

CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
returns budget_info_ext AS
$$
select bi.id, bi.start_date, bi.end_date, bi.reference, bi.description,
       bi.entered_by, bi.approved_by, bi.obsolete_by, bi.entered_at,
       bi.approved_at, bi.obsolete_at,
       ee.name, ae.name, oe.name
  from budget_info bi
  JOIN entity ee ON bi.entered_by = ee.id
  LEFT JOIN entity ae ON bi.approved_by = ae.id
  LEFT JOIN entity oe ON bi.obsolete_by = oe.id
 where bi.id = $1;
$$ language sql;

COMMENT ON FUNCTION budget__get_info(in_id int) IS
$$ Selects the budget info. $$;

CREATE OR REPLACE FUNCTION budget__get_business_units(in_id int)
returns setof business_unit AS
$$ select bu.*
     FROM business_unit bu
     JOIN budget_to_business_unit b2bu ON b2bu.bu_id = bu.id
     JOIN budget_info bi ON bi.id = b2bu.budget_id
    WHERE bi.id = $1
 ORDER BY bu.class_id;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION budget__search(
   in_start_date date,
   in_end_date date ,
   in_includes_date date,
   in_reference text,
   in_description text,
   in_entered_by int,
   in_approved_by int,
   in_obsolete_by int,
   in_business_units int[],
   in_is_approved bool, in_is_obsolete bool
) RETURNS SETOF budget_info_ext AS
$$
select bi.id, bi.start_date, bi.end_date, bi.reference, bi.description,
       bi.entered_by, bi.approved_by, bi.obsolete_by, bi.entered_at,
       bi.approved_at, bi.obsolete_at,
       ee.name, ae.name, oe.name
  from budget_info bi
  JOIN entity ee ON bi.entered_by = ee.id
  LEFT JOIN entity ae ON bi.approved_by = ae.id
  LEFT JOIN entity oe ON bi.obsolete_by = oe.id
 WHERE (start_date = $1 or $1 is null) AND ($2 = end_date or $2 is null)
       AND ($3 BETWEEN start_date AND end_date or $2 is null)
       AND ($4 ilike reference || '%' or $4 is null)
       AND (bi.description @@ plainto_tsquery($5) or $5 is null)
       AND ($6 = entered_by or $6 is null)
       AND ($7 = approved_by or $7 is null)
       AND ($8 = obsolete_by or $8 is null)
       AND ($10 IS NULL OR ($10 = (approved_by IS NOT NULL)))
       AND ($11 IS NULL OR ($11 = (obsolete_by IS NOT NULL)))
 ORDER BY reference;
$$ language sql;

COMMENT ON FUNCTION budget__search(
   in_start_date date,
   in_end_date date ,
   in_includes_date date,
   in_reference text,
   in_description text,
   in_entered_by int,
   in_approved_by int,
   in_obsolete_by int,
   in_business_units int[],
   in_is_approved bool,
   in_is_obsolete bool
)  IS $$ This is a general search for budgets$$;

CREATE OR REPLACE FUNCTION budget__save_info
(in_id int, in_start_date date, in_end_date date, in_reference text,
in_description text, in_business_units int[])
RETURNS budget_info_ext AS
$$
DECLARE
   retval budget_info_ext;
   t_id int;
BEGIN

   PERFORM * FROM budget_info WHERE id = in_id and approved_by is not null;
   IF FOUND THEN
       RAISE EXCEPTION 'report approved';
   END IF;

  UPDATE budget_info
     SET start_date = in_start_date,
         end_date = in_end_date,
         reference = in_reference,
         description = in_description
   WHERE id = in_id and approved_by is null;
  IF FOUND THEN
      t_id := in_id;
  ELSE
       INSERT INTO budget_info (start_date, end_date, reference, description)
            VALUES (in_start_date, in_end_date, in_reference, in_description);
       t_id = currval('budget_info_id_seq');

       INSERT INTO budget_to_business_unit(budget_id, bu_id, bu_class)
       SELECT t_id, id, class_id
         FROM business_unit
        WHERE id = ANY(in_business_units);
  END IF;
  retval := budget__get_info(t_id);
  return retval;
END;
$$ security definer language plpgsql;

COMMENT ON FUNCTION budget__save_info
(in_id int, in_start_date date, in_end_date date, in_reference text,
in_description text, in_business_units int[]) IS
$$Saves the extended budget info passed through to the function.  See the
comment on type budget_info_ext for more information.$$;

CREATE OR REPLACE FUNCTION budget__approve(in_id int)
RETURNS budget_info_ext AS $$
UPDATE budget_info
   set approved_at = now(), approved_by = person__get_my_entity_id()
 WHERE id = $1;

SELECT budget__get_info($1);
$$ language sql;

CREATE OR REPLACE FUNCTION budget__save_details(in_id int, in_details text[])
RETURNS budget_info_ext AS
$$
DECLARE
   loop_count int;
   retval budget_info_ext;
BEGIN
    FOR loop_count in
        array_lower(in_details, 1) ..
        array_upper(in_details, 1)
    LOOP
        INSERT INTO budget_line
                    (budget_id,
                     account_id,
                     description,
                     amount)
             VALUES (in_id,
                     in_details[loop_count][1]::int,
                     in_details[loop_count][2],
                     in_details[loop_count][3]::numeric);
    END LOOP;
    retval := budget__get_info(in_id);
    return retval;
END;
$$ language plpgsql;

COMMENT ON FUNCTION budget__save_details(in_id int, in_details text[]) IS
$$ This saves the line items for the budget.  in_details is an array n long
where each entry is {int account_id, text description, numeric amount}.  The
in_id parameter is the budget_id.$$;

DROP TYPE IF EXISTS budget_line_details CASCADE;

CREATE TYPE budget_line_details AS (
    budget_id int,
    account_id int,
    description text,
    amount numeric,
    accno text,
    acc_desc text,
    debit numeric,
    credit numeric
);


DROP FUNCTION IF EXISTS budget__get_details(int) CASCADE;
CREATE OR REPLACE FUNCTION budget__get_details(in_id int)
RETURNS SETOF budget_line_details AS
$$
  SELECT l.budget_id, l.account_id, l.description, l.amount,
         a.accno, a.description,
         CASE WHEN l.amount < 0 THEN l.amount * -1 ELSE NULL END,
         CASE WHEN l.amount > 0 THEN l.amount ELSE NULL END
    FROM budget_line l
    JOIN account a ON a.id = l.account_id
   where budget_id = $1;
$$ language sql;

COMMENT ON FUNCTION budget__get_details(in_id int) IS
$$ This retrieves the budget lines associated with a budget.$$;

CREATE OR REPLACE FUNCTION budget__get_notes(in_id int)
RETURNS SETOF budget_note AS
$$
   SELECT * FROM budget_note WHERE ref_key = $1;
$$ LANGUAGE SQL;

COMMENT ON FUNCTION budget__get_notes(in_id int) IS
$$ Retrieves the notes associated with the budget.$$;

CREATE OR REPLACE FUNCTION budget__save_note
(in_id int, in_subject text, in_note text)
RETURNS budget_note AS
$$
INSERT INTO budget_note (subject, note, ref_key)
     values ($2, $3, $1);

SELECT * FROM budget_note WHERE id = currval('note_id_seq'::regclass);
$$ language sql;

COMMENT ON FUNCTION budget__save_note
(in_id int, in_subject text, in_note text) IS
$$ Saves a note attached to a budget.$$;

CREATE OR REPLACE FUNCTION budget__get_notes(in_id int)
RETURNS SETOF budget_note AS
$$
SELECT * FROM budget_note WHERE ref_key = $1
 ORDER BY created;
$$ language sql;

COMMENT ON FUNCTION budget__get_notes(in_id int) IS
$$ Returns all notes associated with a budget, by default in the order they
were created.$$;

DROP TYPE IF EXISTS budget_variance_report CASCADE;
CREATE TYPE budget_variance_report AS (
    accno text,
    account_label text,
    account_id int,
    budget_description text,
    budget_amount numeric,
    used_amount numeric,
    variance numeric
);

COMMENT ON TYPE budget_variance_report IS
$$ This is the base type for the budget variance report.$$;

CREATE OR REPLACE FUNCTION budget__variance_report(in_id int)
RETURNS SETOF budget_variance_report
AS
$$
   WITH agg_account (amount, id, transdate)
        AS ( SELECT ac.amount *
                    CASE WHEN a.contra THEN -1 ELSE 1 END *
                    CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
                    AS amount,
                    ac.chart_id, ac.transdate
               FROM acc_trans ac
               JOIN account a ON ac.chart_id = a.id
           )
   SELECT act.accno, act.description, act.id, b.description, b.amount,
          coalesce(sum(a.amount), 0),
          b.amount - coalesce(sum(a.amount), 0) AS variance
     FROM budget_info bi
     JOIN budget_line b ON bi.id = b.budget_id
     JOIN account act ON act.id = b.account_id
LEFT JOIN agg_account a ON a.transdate BETWEEN bi.start_date and bi.end_date
                           AND a.id = b.account_id
    WHERE bi.id = $1
 GROUP BY act.accno, act.description, act.id, b.description, b.amount
 ORDER BY act.accno;
$$ language sql;

COMMENT ON FUNCTION budget__variance_report(in_id int) IS
$$ Retrieves a variance report for budget with an id of in_id.$$;

CREATE OR REPLACE FUNCTION budget__mark_obsolete(in_id int)
RETURNS budget_info_ext AS
$$
UPDATE budget_info
   set obsolete_by = person__get_my_entity_id(), obsolete_at = now()
 WHERE id = $1 and approved_by is not null;
SELECT budget__get_info($1)
$$ language sql;

COMMENT ON FUNCTION budget__mark_obsolete(in_id int) IS
$$ Marks a budget as obsolete $$;

CREATE OR REPLACE FUNCTION budget__reject(in_id int)
RETURNS bool AS
$$
BEGIN

DELETE FROM budget_line
 WHERE budget_id IN (SELECT id from budget_info
                      WHERE id = in_id AND approved_by IS NULL);

DELETE FROM budget_to_project
 WHERE budget_id IN (SELECT id from budget_info
                      WHERE id = in_id AND approved_by IS NULL);

DELETE FROM budget_to_department
 WHERE budget_id IN (SELECT id from budget_info
                      WHERE id = in_id AND approved_by IS NULL);

DELETE FROM budget_info WHERE id = in_id AND approved_by IS NULL;

RETURN FOUND;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
REVOKE EXECUTE ON FUNCTION budget__reject(in_id int) FROM public;

COMMENT ON FUNCTION budget__reject(in_id int) IS
$$ Deletes unapproved budgets only.$$;

update defaults set value = 'yes' where setting_key = 'module_load_ok';


COMMIT;