SF.net SVN: ledger-smb:[3765] addons/1.3

[..hidden..]

Revision: 3765
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3765&view=rev
Author:   einhverfr
Date:     2011-09-28 03:35:16 +0000 (Wed, 28 Sep 2011)
Log Message:
-----------
Beginning of budgetting addon

Added Paths:
-----------
    addons/1.3/budgetting/
    addons/1.3/budgetting/branches/
    addons/1.3/budgetting/tags/
    addons/1.3/budgetting/trunk/
    addons/1.3/budgetting/trunk/LedgerSMB/
    addons/1.3/budgetting/trunk/LedgerSMB/DBObject/
    addons/1.3/budgetting/trunk/scripts/
    addons/1.3/budgetting/trunk/sql/
    addons/1.3/budgetting/trunk/sql/modules/
    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: addons/1.3/budgetting/trunk/sql/modules/budgetting-menu.sql
===================================================================
Added: addons/1.3/budgetting/trunk/sql/modules/budgetting-roles.sql
===================================================================
Added: addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql	                        (rev 0)
+++ addons/1.3/budgetting/trunk/sql/modules/budgetting-tables.sql	2011-09-28 03:35:16 UTC (rev 3765)
@@ -0,0 +1,35 @@
+CREATE TABLE budget_info (
+   id serial not null unique,
+   start_date date not null,
+   end_date date not null,
+   reference text primary key,
+   description text not null,
+   entered_by int not null references entity(id) 
+                  default person__get_my_entity_id(),
+   approved_by int references entity(id),
+   entered_at timestamp not null default now(),
+   approved_at timestamp,
+   check (start_date < end_date)
+);
+
+CREATE TABLE budget_to_department (
+    budget_id int not null unique references budget_info(id),
+    department_id int not null references department(id)
+);
+
+CREATE TABLE budget_to_project (
+    budget_id int not null unique references budget_info(id),
+    project_id int not null references project(id)
+);
+
+CREATE OR REPLACE budget_line (
+    budget_id int not null references budget_info(id),
+    account_id int not null references account(id),
+    description text,
+    amount numeric not null 
+);
+
+CREATE OR REPLACE budget_note (
+    primary key(id),
+    foreign key(ref_key) references budget_info(id)
+);

Added: 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-28 03:35:16 UTC (rev 3765)
@@ -0,0 +1,109 @@
+
+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,
+   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()
+RETURNS budget_info_ext AS
+$$
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
+returns budget_info AS
+$$ select * from budget_info_ext where id = $1; $$ language sql;
+
+CREATE OR REPLACE FUNCTION budget__get_details(in_id int)
+RETURNS SETOF budget_lines AS
+$$
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION budget__save_note(in_id, in_subject, in_note)
+RETURNS budget_note AS
+$$
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION budget__get_notes(in_id) 
+RETURNS SETOF budget_note AS
+$$ 
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION budget__variance_report(in_id) 
+RETURNS SETOF budget_variance_report
+AS
+$$
+$$ sql;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.