More db fixes

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

3 files changed, 81 insertions, 28 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 85b5c39d..6e159ac3 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -791,7 +791,7 @@ SELECT SETVAL('batch_class_id_seq',6);
 CREATE TABLE batch (
   id serial primary key,
   batch_class_id integer references batch_class(id) not null,
-  description text,
+  control_code text,
   approved_on date default null,
   approved_by int references entity_employee(entity_id),
   created_by int references entity_employee(entity_id),
@@ -806,7 +806,7 @@ CREATE TABLE voucher (
   trans_id int REFERENCES transactions(id) NOT NULL,
   batch_id int references batch(id) not null,
   id serial NOT NULL,
-  batch_class int references batch_class not null,
+  batch_class int references batch_class(id) not null,
   PRIMARY KEY (batch_class, batch_id, trans_id)
 );
 
@@ -1710,18 +1710,17 @@ CREATE TABLE menu_node (
 -- Name: menu_node_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
 --
 
-SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'), 193, true);
+SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'), 204, true);
 
 
 --
 -- Data for Name: menu_node; Type: TABLE DATA; Schema: public; Owner: ledgersmb
 --
-
 COPY menu_node (id, label, parent, "position") FROM stdin;
 0	Top-level	\N	0
 1	AR	0	1
 2	Add Transaction	1	1
-3	Sales Invoice	1	2
+144	Departments	128	8
 5	Transactions	4	1
 6	Outstanding	4	2
 7	AR Aging	4	3
@@ -1738,7 +1737,7 @@ COPY menu_node (id, label, parent, "position") FROM stdin;
 20	Close Till	16	4
 21	AP	0	3
 22	Add Transaction	21	1
-23	Vendor Invoice	21	2
+145	Add Department	144	1
 25	Transactions	24	1
 26	Outstanding	24	2
 27	AP Aging	24	3
@@ -1753,12 +1752,11 @@ COPY menu_node (id, label, parent, "position") FROM stdin;
 38	Payment	35	3
 37	Receipts	35	2
 39	Payments	35	4
-40	Transfer	35	5
+146	List Departments	144	2
 42	Receipts	41	1
 43	Payments	41	2
 44	Reconciliation	41	3
-41	Reports	35	7
-45	Reconciliation	35	6
+147	Type of Business	128	9
 46	HR	0	5
 47	Employees	46	1
 48	Add Employee	47	1
@@ -1856,10 +1854,6 @@ COPY menu_node (id, label, parent, "position") FROM stdin;
 141	Warehouses	128	7
 142	Add Warehouse	141	1
 143	List Warehouse	141	2
-144	Departments	128	8
-145	Add Department	144	1
-146	List Departments	144	2
-147	Type of Business	128	9
 148	Add Business	147	1
 149	List Businesses	147	2
 150	Language	128	10
@@ -1906,18 +1900,28 @@ COPY menu_node (id, label, parent, "position") FROM stdin;
 191	Preferences	0	17
 192	New Window	0	18
 193	Logout	0	19
-11	Customers	1	6
-4	Reports	1	5
-194	Credit Note	1	3
-195	Credit Invoice	1	4
-24	Reports	21	5
-30	Vendors	21	6
-196	Debit Note	21	3
-197	Debit Invoice	21	4
+198	AR Voucher	1	2
+3	Sales Invoice	1	3
+11	Customers	1	7
+4	Reports	1	6
+194	Credit Note	1	4
+195	Credit Invoice	1	5
+199	AP Voucher	21	2
+23	Vendor Invoice	21	3
+24	Reports	21	6
+30	Vendors	21	7
+196	Debit Note	21	4
+197	Debit Invoice	21	5
+200	Vouchers	35	5
+40	Transfer	35	6
+41	Reports	35	8
+45	Reconciliation	35	7
+203	Receipts	200	3
+204	Reverse Receipts	200	4
+201	Payments	200	1
+202	Reverse Payment	200	2
 \.
 
-
-
 --
 -- Name: menu_node_parent_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace: 
 --
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;