From ee7d31ef17d83d0928587af8910a8d3d6e12fecc Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Tue, 4 Dec 2007 22:13:25 +0000
Subject: More Roles fixes

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1942 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/modules/Payment.sql | 10 +++++++---
 sql/modules/Roles.sql   |  2 ++
 sql/modules/chart.sql   | 22 ++++++++++++++++++++++
 3 files changed, 31 insertions(+), 3 deletions(-)

(limited to 'sql')

diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index c39e6945..d19a821b 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -130,7 +130,7 @@ BEGIN
 		         sum(a.amount - a.paid) AS total_due, 
 		         compound_array(ARRAY[[
 		              a.id::text, a.invnumber, a.transdate::text, 
-		              a.amount::text, 
+		              a.amount::text, a.paid::text,
 		              (CASE WHEN c.discount_terms 
 		                        > extract('days' FROM age(a.transdate))
 		                   THEN 0
@@ -148,12 +148,15 @@ BEGIN
 		    FROM entity e
 		    JOIN entity_credit_account c ON (e.id = c.entity_id)
 		    JOIN (SELECT id, invnumber, transdate, amount, entity_id, 
-		                 paid, curr, 1 as invoice_class 
+		                 paid, curr, 1 as invoice_class, 
+		                 entity_credit_account 
 		            FROM ap
 		           UNION
 		          SELECT id, invnumber, transdate, amount, entity_id,
-		                 paid, curr, 2 as invoice_class
+		                 paid, curr, 2 as invoice_class, 
+		                 entity_credit_account
 		            FROM ar
+			ORDER BY transdate
 		         ) a USING (entity_id)
 		    JOIN transactions t ON (a.id = t.id)
 		   WHERE a.invoice_class = in_account_class
@@ -163,6 +166,7 @@ BEGIN
 		                          WHERE batch_id = in_batch_id))
 		         AND c.entity_class = in_account_class
 		         AND a.curr = in_currency
+		         AND a.entity_credit_account = c.id
 		         AND a.amount - a.paid <> 0
 			 AND t.locked_by NOT IN 
 				(select "session_id" FROM "session"
diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql
index 8ae539e9..3ed98d73 100644
--- a/sql/modules/Roles.sql
+++ b/sql/modules/Roles.sql
@@ -1385,4 +1385,6 @@ GRANT select on chart, gifi, country to public;
  GRANT SELECT ON parts, partsgroup TO public;
  GRANT SELECT ON language, project TO public;
 GRANT SELECT ON business, exchangerate, department, shipto, tax TO public;
+GRANT ALL ON recurring, recurringemail, recurringprint TO public; 
+--TODO, lock recurring down more
 
diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql
index 11e2e38b..4575bc69 100644
--- a/sql/modules/chart.sql
+++ b/sql/modules/chart.sql
@@ -21,3 +21,25 @@ END;
 $$ language plpgsql;
 COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
 $$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;
+
+CREATE OR REPLACE FUNCTION chart_get_ar_ap(in_account_class int)
+RETURNS SETOF chart AS
+$$
+DECLARE out_row chart%ROWTYPE;
+BEGIN
+	IF in_account_class NOT IN (1, 2) THEN
+		RAISE EXCEPTION 'Bad Account Type';
+	END IF;
+       FOR out_row IN
+               SELECT * FROM chart 
+               WHERE link = CASE WHEN in_account_class = 1 THEN 'AP'
+                               WHEN in_account_class = 2 THEN 'AR'
+                               END
+       LOOP
+               RETURN NEXT out_row;
+       END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+COMMENT ON FUNCTION chart_get_ar_ap(in_account_class int) IS
+$$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;
+
-- 
cgit v1.2.3