[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3777] addons/1.3/budgetting/trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[3777] addons/1.3/budgetting/trunk/sql/modules
- From: ..hidden..
- Date: Thu, 29 Sep 2011 07:01:54 +0000
Revision: 3777
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3777&view=rev
Author: einhverfr
Date: 2011-09-29 07:01:54 +0000 (Thu, 29 Sep 2011)
Log Message:
-----------
More budgetting stuff
Modified Paths:
--------------
addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql
Added Paths:
-----------
addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql
addons/1.3/budgetting/trunk/sql/modules/test/
addons/1.3/budgetting/trunk/sql/modules/test/Budgetting.sql
Removed Paths:
-------------
addons/1.3/budgetting/trunk/sql/modules/budgetting.sql
Copied: addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql (from rev 3774, addons/1.3/budgetting/trunk/sql/modules/budgetting.sql)
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql (rev 0)
+++ addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql 2011-09-29 07:01:54 UTC (rev 3777)
@@ -0,0 +1,299 @@
+-- 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.
+
+
+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,
+ department_id int,
+ department_name text,
+ project_id int,
+ projectnumber 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 can't 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__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_department_id int,
+ in_project_id int,
+ in_is_approved bool, in_is_obsolete bool
+) RETURNS SETOF budget_info_ext AS
+$$
+select bi.*, ee.name, ae.name, oe.name, bd.department_id, d.description,
+ bp.project_id, p.projectnumber
+ from budget_info
+ JOIN entity ee ON bi.entered_by = ee.id
+ LEFT JOIN budget_to_department bd ON bd.budget_id = bi.id
+ LEFT JOIN entity ae ON bi.approved_by = ae.id
+ LEFT JOIN entity oe ON bi.obsolete_by = oe.id
+ LEFT JOIN budget_to_project bp ON bp.budget_id = bi.id
+ LEFT JOIN department d ON d.id = bd.department_id
+ LEFT JOIN project p ON bp.project_id = p.id
+ WHERE (start_date = $1) or ($2 = end_date)
+ OR ($3 BETWEEN start_date AND end_date)
+ OR ($4 ilike reference || '%') OR (description @@ plain_to_tsquery($5))
+ OR ($6 = entered_by) OR ($7 = approved_by) OR ($8 = obsolete_by)
+ OR ($9 = department_id) OR ($10 = project_id)
+ OR ($11 IS NULL OR ($11 = (approved_by IS NOT NULL)))
+ OR ($12 IS NULL OR ($12 = (obsolete_by IS NOT NULL)))
+ ORDER BY department_id, project_id, reference;
+$$ language sql;
+
+COMMENT ON FUNCTION 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_department_id int,
+ in_project_id 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_department_id int, in_project_id int)
+RETURNS budget_info_ext AS
+$$
+DECLARE
+ retval budget_info_ext;
+ t_id int;
+BEGIN
+ 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 is not true;
+ IF FOUND THEN
+ t_id := in_id;
+ ELSE
+ PERFORM * FROM budget_info WHERE id = in_id and approved is true;
+ IF FOUND THEN
+ RAISE EXCEPTION 'report approved';
+ END IF;
+ 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');
+ END IF;
+ IF in_project_id IS NOT NULL THEN
+ UPDATE budget_to_project
+ SET project_id = in_project_id
+ WHERE budget_id = t_id;
+
+ IF NOT FOUND THEN
+ INSERT INTO budget_to_project(budget_id, project_id)
+ VALUES (t_id, in_project_id);
+ END IF;
+ END IF;
+ IF in_department_id IS NOT NULL THEN
+ UPDATE budget_to_department
+ SET department_id = in_department_id
+ WHERE budget_id = t_id;
+
+ IF NOT FOUND THEN
+ INSERT INTO budget_to_department(budget_id, department_id)
+ VALUES (t_id, in_department_id);
+ END IF;
+ END IF;
+ SELECT bi.*, in_department_id, in_project_id FROM budget_info
+ WHERE id = t_id;
+END;
+$$ 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_department_id int, in_project_id 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__save_details(in_id int, in_details text[])
+RETURNS budget_info_ext AS
+$$
+DECLARE
+ loop_count int;
+ retval int;
+BEGIN
+ FOR loop_count in
+ array_lower(in_transactions, 1) ..
+ array_upper(in_transactions, 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;
+ return budget__get_info(in_id);
+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.$$;
+
+CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
+returns budget_info AS
+$$
+select bi.*, ee.name, ae.name, oe.name, bd.department_id, d.description,
+ bp.project_id, p.projectnumber
+ from budget_info
+ JOIN entity ee ON bi.entered_by = ee.id
+ LEFT JOIN budget_to_department bd ON bd.budget_id = bi.id
+ LEFT JOIN entity ae ON bi.approved_by = ae.id
+ LEFT JOIN entity oe ON bi.obsolete_by = oe.id
+ LEFT JOIN budget_to_project bp ON bp.budget_id = bi.id
+ LEFT JOIN department d ON d.id = bd.department_id
+ LEFT JOIN project p ON bp.project_id = p.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_details(in_id int)
+RETURNS SETOF budget_line AS
+$$
+ SELECT * FROM budget_line where budget_id = in_id;
+$$ 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__save_note(in_id, in_subject, in_note)
+RETURNS budget_note AS
+$$
+INSERT INTO budget_note (note_class, subject, note, ref_key)
+ values (5, in_subject, in_note, in_id);
+
+SELECT * FROM budget_note WHERE id = currval('note_id_seq'::regclass);
+$$ language sql;
+
+COMMENT ON FUNCTION budget__save_note(in_id, in_subject, in_note) IS
+$$ Saves a note attached to a budget.$$;
+
+CREATE OR REPLACE FUNCTION budget__get_notes(in_id)
+RETURNS SETOF budget_note AS
+$$
+SELECT * FROM budget_note WHERE ref_key = in_id
+ ORDER BY created;
+$$ language sql;
+
+COMMENT ON FUNCTION budget__get_notes(in_id) IS
+$$ Returns all notes associated with a budget, by default in the order they
+were created.$$;
+
+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)
+RETURNS SETOF budget_variance_report
+AS
+$$
+ WITH agg_account (amount, id, accno, description, 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,
+ a.id, a.account, a.description, ac.transdate
+ FROM acc_trans ac
+ JOIN account ON ac.chart_id = account.id
+ )
+ SELECT a.accno, a.description, a.id, b.description, b.amount
+ sum(a.amount), b.amount - sum(a.amount) AS variance
+ FROM budget_info bi
+ JOIN agg_account a ON a.transdate BETWEEN bi.start_date and bi.end_date
+ JOIN budget_line b ON bi.id = b.budget_id
+ WHERE bi.id = $1;
+$$ 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;
+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.$$;
Modified: addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql 2011-09-29 06:25:49 UTC (rev 3776)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql 2011-09-29 07:01:54 UTC (rev 3777)
@@ -7,6 +7,7 @@
entered_by int not null references entity(id)
default person__get_my_entity_id(),
approved_by int references entity(id),
+ obsolete_by int references entity(id),
entered_at timestamp not null default now(),
approved_at timestamp,
obsolete_at timestamp,
Deleted: addons/1.3/budgetting/trunk/sql/modules/budgetting.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting.sql 2011-09-29 06:25:49 UTC (rev 3776)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting.sql 2011-09-29 07:01:54 UTC (rev 3777)
@@ -1,249 +0,0 @@
--- 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.
-
-
-CREATE TYPE budget_info_ext AS (
- id INT,
- start_date date,
- end_date date ,
- reference text,
- description text,
- entered_by int,
- approved_by int,
- entered_at timestamp,
- approved_at timestamp,
- obsolete_by timestamp,
- entered_by_name text
- approved_by_name text,
- obsolete_by_name text,
- department_id int,
- project_id int
-);
-
-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 can't 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__save_info
-(in_id int, in_start_date date, in_end_date date, in_reference text,
-in_description text, in_department_id int, in_project_id int)
-RETURNS budget_info_ext AS
-$$
-DECLARE
- retval budget_info_ext;
- t_id int;
-BEGIN
- 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 is not true;
- IF FOUND THEN
- t_id := in_id;
- ELSE
- PERFORM * FROM budget_info WHERE id = in_id and approved is true;
- IF FOUND THEN
- RAISE EXCEPTION 'report approved';
- END IF;
- 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');
- END IF;
- IF in_project_id IS NOT NULL THEN
- UPDATE budget_to_project
- SET project_id = in_project_id
- WHERE budget_id = t_id;
-
- IF NOT FOUND THEN
- INSERT INTO budget_to_project(budget_id, project_id)
- VALUES (t_id, in_project_id);
- END IF;
- END IF;
- IF in_department_id IS NOT NULL THEN
- UPDATE budget_to_department
- SET department_id = in_department_id
- WHERE budget_id = t_id;
-
- IF NOT FOUND THEN
- INSERT INTO budget_to_department(budget_id, department_id)
- VALUES (t_id, in_department_id);
- END IF;
- END IF;
- SELECT bi.*, in_department_id, in_project_id FROM budget_info
- WHERE id = t_id;
-END;
-$$ 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_department_id int, in_project_id 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__save_details(in_id int, in_details text[])
-RETURNS budget_info_ext AS
-$$
-DECLARE
- loop_count int;
- retval int;
-BEGIN
- FOR loop_count in
- array_lower(in_transactions, 1) ..
- array_upper(in_transactions, 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;
- return budget__get_info(in_id);
-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.$$;
-
-CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
-returns budget_info AS
-$$
-select bi.*, ee.name, ae.name, oe.name, bd.department_id, bp.project_id
- from budget_info
- JOIN budget_to_department bd ON bd.budget_id = bi.id
- 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
- JOIN budget_to_project bp ON bp.budget_id = bi.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_details(in_id int)
-RETURNS SETOF budget_line AS
-$$
- SELECT * FROM budget_line where budget_id = in_id;
-$$ 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__save_note(in_id, in_subject, in_note)
-RETURNS budget_note AS
-$$
-INSERT INTO budget_note (note_class, subject, note, ref_key)
- values (5, in_subject, in_note, in_id);
-
-SELECT * FROM budget_note WHERE id = currval('note_id_seq'::regclass);
-$$ language sql;
-
-COMMENT ON FUNCTION budget__save_note(in_id, in_subject, in_note) IS
-$$ Saves a note attached to a budget.$$;
-
-CREATE OR REPLACE FUNCTION budget__get_notes(in_id)
-RETURNS SETOF budget_note AS
-$$
-SELECT * FROM budget_note WHERE ref_key = in_id
- ORDER BY created;
-$$ language sql;
-
-COMMENT ON FUNCTION budget__get_notes(in_id) IS
-$$ Returns all notes associated with a budget, by default in the order they
-were created.$$;
-
-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)
-RETURNS SETOF budget_variance_report
-AS
-$$
- WITH agg_account (amount, id, accno, description, 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,
- a.id, a.account, a.description, ac.transdate
- FROM acc_trans ac
- JOIN account ON ac.chart_id = account.id
- )
- SELECT a.accno, a.description, a.id, b.description, b.amount
- sum(a.amount), b.amount - sum(a.amount) AS variance
- FROM budget_info bi
- JOIN agg_account a ON a.transdate BETWEEN bi.start_date and bi.end_date
- JOIN budget_line b ON bi.id = b.budget_id
- WHERE bi.id = $1;
-$$ 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;
-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.$$;
Added: addons/1.3/budgetting/trunk/sql/modules/test/Budgetting.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/test/Budgetting.sql (rev 0)
+++ addons/1.3/budgetting/trunk/sql/modules/test/Budgetting.sql 2011-09-29 07:01:54 UTC (rev 3777)
@@ -0,0 +1,10 @@
+BEGIN;
+
+\i Base.sql
+
+INSERT INTO department (id, department) values (-1, 'test1');
+INSERT INTO department (id, department) values (-2, 'test2');
+INSERT INTO project (id, projectnumber) values (-1, 'test1');
+INSERT INTO project (id, projectnumber) values (-2, 'test2');
+
+COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.