[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3774] addons/1.3/budgetting/trunk
- Subject: SF.net SVN: ledger-smb:[3774] addons/1.3/budgetting/trunk
- From: ..hidden..
- Date: Thu, 29 Sep 2011 06:11:38 +0000
Revision: 3774
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3774&view=rev
Author: einhverfr
Date: 2011-09-29 06:11:37 +0000 (Thu, 29 Sep 2011)
Log Message:
-----------
More budget code
Modified Paths:
--------------
addons/1.3/budgetting/trunk/sql/modules/budgetting-menu.sql
addons/1.3/budgetting/trunk/sql/modules/budgetting-roles.sql
addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql
addons/1.3/budgetting/trunk/sql/modules/budgetting.sql
Added Paths:
-----------
addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget.pm
addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget_Report.pm
Added: addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget.pm
===================================================================
--- addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget.pm (rev 0)
+++ addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget.pm 2011-09-29 06:11:37 UTC (rev 3774)
@@ -0,0 +1,66 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Budget
+
+=cut
+
+package LedgerSMB::DBObject::Budget;
+use strict;
+
+=head1 SYNOPSIS
+
+This module provides budget management routines, such as entering budgets,
+approving or rejecting them, and marking them obsolete. It does not include
+more free-form areas like reporting. For those, see
+LedgerSMB::DBObject::Budget_Report.
+
+=head1 INHERITANCE
+
+=over
+
+=item LedgerSMB
+
+=item LedgerSMB::DBObject
+
+=back
+
+=cut
+
+use base qw(LedgerSMB::DBObject);
+
+=head1 PROPERTIES
+
+=over
+
+=item $id INT,
+=item $start_date date,
+=item $end_date date ,
+=item $reference text,
+=item $description text,
+=item $entered_by int,
+=item $approved_by int,
+=item $entered_at timestamp,
+=item $approved_at timestamp,
+=item $obsolete_by timestamp,
+=item $entered_by_name text
+=item $approved_by_name text,
+=item $obsolete_by_name text,
+=item $department_id int,
+=item $project_id int
+=item @lines
+
+=head1 METHODS
+
+=head1 SEE ALSO
+
+=over
+
+=item LedgerSMB
+
+=item LedgerSMB::DBObject
+
+=item LedgerSMB::DBObject::Budget_Report
+
+=back
+
+=head1 COPYRIGHT AND LICENSE
Added: addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget_Report.pm
===================================================================
--- addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget_Report.pm (rev 0)
+++ addons/1.3/budgetting/trunk/LedgerSMB/DBObject/Budget_Report.pm 2011-09-29 06:11:37 UTC (rev 3774)
@@ -0,0 +1,33 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Budget_Report
+
+=cut
+
+package LedgerSMB::DBObject::Budget_Report;
+use strict;
+
+=head1 SYNOPSIS
+
+Reporting routines for budgets. Currently only displays a variance report.
+
+=head1 INHERITANCE
+
+=cut
+
+=over
+
+=item LedgerSMB
+
+=item LedgerSMB::DBObject
+
+=back
+
+=cut
+
+use base qw(LedgerSMB::DBObject);
+
+=head1 PROPERTIES
+
+=head1 METHODS
+
Modified: addons/1.3/budgetting/trunk/sql/modules/budgetting-menu.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting-menu.sql 2011-09-28 20:58:39 UTC (rev 3773)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting-menu.sql 2011-09-29 06:11:37 UTC (rev 3774)
@@ -0,0 +1 @@
+-- TODO
Modified: addons/1.3/budgetting/trunk/sql/modules/budgetting-roles.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting-roles.sql 2011-09-28 20:58:39 UTC (rev 3773)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting-roles.sql 2011-09-29 06:11:37 UTC (rev 3774)
@@ -0,0 +1,21 @@
+--TODO
+CREATE ROLE "lsmb_<?lsmb dbname ?>__budget_enter" WITH INHERIT NOLOGIN;
+CREATE ROLE "lsmb_<?lsmb dbname ?>__budget_approve" WITH INHERIT NOLOGIN;
+CREATE ROLE "lsmb_<?lsmb dbname ?>__budget_obsolete" WITH INHERIT NOLOGIN;
+
+GRANT SELECT
+ON budget_info, budget_line, budget_to_department, budget_to_project
+TO public;
+
+GRANT INSERT
+ON budget_info, budget_line, budget_to_department, budget_to_project
+TO "lsmb_<?lsmb dbname ?>__budget_enter";
+
+GRANT UPDATE (approved_at, approved_by) on budget_info
+TO "lsmb_<?lsmb dbname ?>__budget_approve"
+
+GRANT UPDATE (obsolete_at, obsolete_by) on budget_info
+TO "lsmb_<?lsmb dbname ?>__budget_obsolete";
+
+GRANT EXECUTE ON FUNCTION budget__reject(in_id int)
+TO "lsmb_<?lsmb dbname ?>__budget_approve";
Modified: addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql 2011-09-28 20:58:39 UTC (rev 3773)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql 2011-09-29 06:11:37 UTC (rev 3774)
@@ -9,6 +9,8 @@
approved_by int references entity(id),
entered_at timestamp not null default now(),
approved_at timestamp,
+ obsolete_at timestamp,
+ obsolete_by int references entity(id),
check (start_date < end_date)
);
@@ -29,7 +31,10 @@
amount numeric not null
);
+INSERT INTO note_class (id, class) values ('5', 'Budget');
+
CREATE OR REPLACE budget_note (
primary key(id),
+ check (note_class = 5),
foreign key(ref_key) references budget_info(id)
);
Modified: addons/1.3/budgetting/trunk/sql/modules/budgetting.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting.sql 2011-09-28 20:58:39 UTC (rev 3773)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting.sql 2011-09-29 06:11:37 UTC (rev 3774)
@@ -1,4 +1,14 @@
+-- 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,
@@ -9,6 +19,10 @@
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
);
@@ -78,32 +92,158 @@
$$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()
+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 * from budget_info_ext where id = $1; $$ language sql;
+$$
+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_lines AS
+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.$$;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.