[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3871] addons/1.3/budgetting/trunk/sql/modules/ Budgetting.sql
- Subject: SF.net SVN: ledger-smb:[3871] addons/1.3/budgetting/trunk/sql/modules/ Budgetting.sql
- From: ..hidden..
- Date: Sun, 16 Oct 2011 00:36:44 +0000
Revision: 3871
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3871&view=rev
Author: einhverfr
Date: 2011-10-16 00:36:44 +0000 (Sun, 16 Oct 2011)
Log Message:
-----------
Budgetting stored procs now all load
Modified Paths:
--------------
addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql
Modified: addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql 2011-10-15 23:48:11 UTC (rev 3870)
+++ addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql 2011-10-16 00:36:44 UTC (rev 3871)
@@ -9,6 +9,19 @@
-- 5: Convert type definitions to views.
+-- 1.4 note: Move to Util.sql
+CREATE OR REPLACE FUNCTION project_list_open(in_date date)
+RETURNS SETOF project AS
+$$ SELECT * FROM project
+ WHERE $1 BETWEEN coalesce(startdate, $1) AND coalesce(enddate, $1)
+ORDER BY projectnumber;
+$$ language sql;
+
+-- 1.4 note: Move to Util.sql
+CREATE OR REPLACE FUNCTION department_list()
+RETURNS SETOF department AS
+$$ SELECT * FROM department ORDER BY description $$ language sql;
+
CREATE TYPE budget_info_ext AS (
id INT,
start_date date,
@@ -21,7 +34,7 @@
entered_at timestamp,
approved_at timestamp,
obsolete_at timestamp,
- entered_by_name text
+ entered_by_name text,
approved_by_name text,
obsolete_by_name text,
department_id int,
@@ -46,14 +59,18 @@
in_description text,
in_entered_by int,
in_approved_by int,
+ in_obsolete_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,
+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, bd.department_id, d.description,
bp.project_id, p.projectnumber
- from budget_info
+ from budget_info bi
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
@@ -63,7 +80,8 @@
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 ($4 ilike reference || '%')
+ OR (bi.description @@ plainto_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)))
@@ -71,7 +89,7 @@
ORDER BY department_id, project_id, reference;
$$ language sql;
-COMMENT ON FUNCTION CREATE OR REPLACE FUNCTION budget__search(
+COMMENT ON FUNCTION budget__search(
in_start_date date,
in_end_date date ,
in_includes_date date,
@@ -79,6 +97,7 @@
in_description text,
in_entered_by int,
in_approved_by int,
+ in_obsolete_by int,
in_department_id int,
in_project_id int,
in_is_approved bool,
@@ -147,7 +166,7 @@
$$
DECLARE
loop_count int;
- retval int;
+ retval budget_info_ext;
BEGIN
FOR loop_count in
array_lower(in_transactions, 1) ..
@@ -163,7 +182,8 @@
in_details[loop_count][2],
in_details[loop_count][3]::numeric);
END LOOP;
- return budget__get_info(in_id);
+ retval := budget__get_info(in_id);
+ return retval;
END;
$$ language plpgsql;
@@ -173,11 +193,14 @@
in_id parameter is the budget_id.$$;
CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
-returns budget_info AS
+returns budget_info_ext AS
$$
-select bi.*, ee.name, ae.name, oe.name, bd.department_id, d.description,
+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, bd.department_id, d.description,
bp.project_id, p.projectnumber
- from budget_info
+ from budget_info bi
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
@@ -194,7 +217,7 @@
CREATE OR REPLACE FUNCTION budget__get_details(in_id int)
RETURNS SETOF budget_line AS
$$
- SELECT * FROM budget_line where budget_id = in_id;
+ SELECT * FROM budget_line where budget_id = $1;
$$ language sql;
COMMENT ON FUNCTION budget__get_details(in_id int) IS
@@ -209,26 +232,28 @@
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, in_subject, in_note)
+CREATE OR REPLACE FUNCTION budget__save_note
+(in_id int, in_subject text, in_note text)
RETURNS budget_note AS
$$
INSERT INTO budget_note (note_class, subject, note, ref_key)
- values (5, in_subject, in_note, in_id);
+ values (5, $2, $3, $1);
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
+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)
+CREATE OR REPLACE FUNCTION budget__get_notes(in_id int)
RETURNS SETOF budget_note AS
$$
-SELECT * FROM budget_note WHERE ref_key = in_id
+SELECT * FROM budget_note WHERE ref_key = $1
ORDER BY created;
$$ language sql;
-COMMENT ON FUNCTION budget__get_notes(in_id) IS
+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.$$;
@@ -245,7 +270,7 @@
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)
+CREATE OR REPLACE FUNCTION budget__variance_report(in_id int)
RETURNS SETOF budget_variance_report
AS
$$
@@ -254,17 +279,20 @@
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
+ a.id, a.accno, a.description, ac.transdate
FROM acc_trans ac
+ JOIN account a ON ac.chart_id = a.id
JOIN account ON ac.chart_id = account.id
)
- SELECT a.accno, a.description, a.id, b.description, b.amount
+ 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;
+ WHERE bi.id = $1
+ GROUP BY a.accno, a.description, a.id, b.description, b.amount
+ ORDER BY a.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.$$;
@@ -273,7 +301,7 @@
RETURNS budget_info_ext AS
$$
UPDATE budget_info
- set obsolete_by = person__get_my_entity_id, obsolete_at = now()
+ set obsolete_by = person__get_my_entity_id(), obsolete_at = now()
WHERE id = $1;
SELECT budget__get_info($1)
$$ language sql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.