More db fixes

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1974 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 54 insertions, 5 deletions

diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index e8c0375b..08755ce6 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -441,10 +441,55 @@ DECLARE
         END LOOP;
         
         IF NOT FOUND THEN
-            RAISE EXCEPTION 'ID % not found!!!!!', in_entity_id;
+            RAISE EXCEPTION 'ID % not found', in_entity_id;
         END IF;                              
               
     END;
 $$ language plpgsql;                                                                  
 COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int) IS
 $$ This function return vendor or customer info, its under construction $$;
+
+CREATE OR REPLACE FUNCTION payment__retrieve
+(in_source text, in_meta_number text, in_account_class int, in_cash_accno text)
+RETURNS SETOF numeric AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+	FOR out_row IN 
+		SELECT amount * -1 AS amount
+		FROM acc_trans
+		WHERE source = in_source
+			AND trans_id IN (
+				SELECT id FROM ar 
+				WHERE in_account_class = 2 AND
+					entity_credit_account = 
+						(select id 
+						FROM entity_credit_account
+						WHERE meta_number 
+							= in_meta_number
+							AND entity_class = 
+							in_account_class)
+				UNION
+				SELECT id FROM ap
+				WHERE in_account_class = 1 AND
+					entity_credit_account = 
+						(select id 
+						FROM entity_credit_account
+						WHERE meta_number 
+							= in_meta_number
+							AND entity_class = 
+							in_account_class)
+			AND chart_id = 
+				(SELECT id FROM chart 
+				WHERE accno = in_cash_accno)
+	LOOP
+		return next out_row.amount;
+	END LOOP;	
+END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION payment__reverse
+(in_source text, in_date_paid date, in_credit_id int, in_cash_accno text)
+RETURNS INT 
+AS $$
+	
+$$ LANGUAGE PLPGSQL;
diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql
index 68f9f23f..45a4213f 100644
--- a/sql/modules/Roles.sql
+++ b/sql/modules/Roles.sql
@@ -1376,15 +1376,19 @@ GRANT ALL ON defaults TO public;
 GRANT ALL ON "session" TO public;
 GRANT ALL ON session_session_id_seq TO PUBLIC;
 GRANT SELECT ON users TO public;
-GRANT SELECT ON user_preference TO public;
+GRANT ALL ON user_preference TO public;
 GRANT SELECT ON custom_table_catalog TO PUBLIC;
 GRANT SELECT ON custom_field_catalog TO PUBLIC;
 grant select on menu_node, menu_attribute, menu_acl to public;
 GRANT select on chart, gifi, country to public;
- grant select on employee to public;
- GRANT SELECT ON parts, partsgroup TO public;
- GRANT SELECT ON language, project TO public;
+grant select on employee 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, status TO public; 
+GRANT ALL ON transactions, entity_employee, customer, vendor TO public;
 --TODO, lock recurring down more
 
+-- CT:  The following grant is required for now, but will hopefully become less 
+-- important when we get to 1.4 and can more sensibly lock things down.
+GRANT ALL ON dpt_trans TO public;