diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 4 | ||||
| -rw-r--r-- | sql/modules/Roles.sql | 41 | ||||
| -rw-r--r-- | sql/modules/Voucher.sql | 20 |
3 files changed, 29 insertions, 36 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index ed32224d..8cebe852 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1935,7 +1935,7 @@ CREATE TABLE menu_attribute ( -- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb -- -SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 550, true); +SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 551, true); -- @@ -2462,7 +2462,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 194 type credit_note 548 195 type credit_invoice 542 197 type debit_invoice 546 -36 account_class 1 550 +36 account_class 1 551 \. diff --git a/sql/modules/Roles.sql b/sql/modules/Roles.sql index 0629c80a..126f4d2f 100644 --- a/sql/modules/Roles.sql +++ b/sql/modules/Roles.sql @@ -11,8 +11,6 @@ GRANT SELECT ON company_to_contact TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON company_to_entity TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON company_to_location TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON customertax TO lsmb_<?lsmb dbname ?>__read_contact; -GRANT SELECT ON employee TO lsmb_<?lsmb dbname ?>__read_contact; -GRANT SELECT ON customer TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON contact_class TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON entity_class TO lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON entity_bank_account TO lsmb_<?lsmb dbname ?>__read_contact; @@ -59,8 +57,6 @@ GRANT INSERT ON company_to_contact TO lsmb_<?lsmb dbname ?>__create_contact; GRANT INSERT ON company_to_entity TO lsmb_<?lsmb dbname ?>__create_contact; GRANT INSERT ON company_to_location TO lsmb_<?lsmb dbname ?>__create_contact; GRANT INSERT ON customertax TO lsmb_<?lsmb dbname ?>__create_contact; -GRANT INSERT ON employee TO lsmb_<?lsmb dbname ?>__create_contact; -GRANT INSERT ON customer TO lsmb_<?lsmb dbname ?>__create_contact; GRANT INSERT ON entity_bank_account TO lsmb_<?lsmb dbname ?>__create_contact; GRANT ALL ON entity_bank_account_id_seq TO lsmb_<?lsmb dbname ?>__create_contact; GRANT INSERT ON entity_note TO lsmb_<?lsmb dbname ?>__create_contact; @@ -100,8 +96,6 @@ GRANT UPDATE ON company_to_contact TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON company_to_entity TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON company_to_location TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON customertax TO lsmb_<?lsmb dbname ?>__create_contact; -GRANT UPDATE ON employee TO lsmb_<?lsmb dbname ?>__create_contact; -GRANT UPDATE ON customer TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON entity_bank_account TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON entity_note TO lsmb_<?lsmb dbname ?>__create_contact; GRANT UPDATE ON entity_class_to_entity TO lsmb_<?lsmb dbname ?>__create_contact; @@ -206,8 +200,8 @@ IN ROLE lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON ar TO lsmb_<?lsmb dbname ?>__list_ar_transactions; GRANT SELECT ON acc_trans TO lsmb_<?lsmb dbname ?>__list_ar_transactions; -GRANT SELECT ON invoice TO lsmb_<?lsmb dbname ?>__list_ar_transactions -GRANT SELECT ON inventory TO lsmb_<?lsmb dbname ?>__list_ar_transactions +GRANT SELECT ON invoice TO lsmb_<?lsmb dbname ?>__list_ar_transactions; +GRANT SELECT ON inventory TO lsmb_<?lsmb dbname ?>__list_ar_transactions; INSERT INTO menu_acl (node_id, acl_type, role_name) values (1, 'allow', 'lsmb_<?lsmb dbname ?>__list_ar_transactions'); @@ -318,7 +312,7 @@ IN ROLE lsmb_<?lsmb dbname ?>__read_contact; GRANT INSERT ON ap TO lsmb_<?lsmb dbname ?>__create_ap_transaction; GRANT ALL ON id TO lsmb_<?lsmb dbname ?>__create_ap_transaction; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__create_ap_transaction; -GRANT ALL ON acc_trans_entry_id TO lsmb_<?lsmb dbname ?>__create_ap_transaction; +GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__create_ap_transaction; INSERT INTO menu_acl (node_id, acl_type, role_name) values (21, 'allow', 'lsmb_<?lsmb dbname ?>__create_ap_transaction'); @@ -335,7 +329,7 @@ lsmb_<?lsmb dbname ?>__create_batch; GRANT INSERT ON ar TO lsmb_<?lsmb dbname ?>__create_ap_transaction_voucher; GRANT ALL ON id TO lsmb_<?lsmb dbname ?>__create_ap_transaction_voucher; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__create_ap_transaction_voucher; -GRANT ALL ON acc_trans_entry_id TO lsmb_<?lsmb dbname ?>__create_ap_transaction_voucher; +GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__create_ap_transaction_voucher; -- TODO add Menu ACLs @@ -374,8 +368,8 @@ IN ROLE lsmb_<?lsmb dbname ?>__read_contact; GRANT SELECT ON ap TO lsmb_<?lsmb dbname ?>__list_ap_transactions; GRANT SELECT ON acc_trans TO lsmb_<?lsmb dbname ?>__list_ap_transactions; -GRANT SELECT ON invoice TO lsmb_<?lsmb dbname ?>__list_ap_transactions -GRANT SELECT ON inventory TO lsmb_<?lsmb dbname ?>__list_ap_transactions +GRANT SELECT ON invoice TO lsmb_<?lsmb dbname ?>__list_ap_transactions; +GRANT SELECT ON inventory TO lsmb_<?lsmb dbname ?>__list_ap_transactions; INSERT INTO menu_acl (node_id, acl_type, role_name) values (21, 'allow', 'lsmb_<?lsmb dbname ?>__list_ap_transactions'); @@ -413,7 +407,6 @@ lsmb_<?lsmb dbname ?>__list_ap_transactions; CREATE ROLE lsmb_<?lsmb dbname ?>__create_purchase_order WITH INHERIT NOLOGIN IN ROLE lsmb_<?lsmb dbname ?>__read_contact; -WITH INHERIT NOLOGIN; GRANT INSERT ON oe TO lsmb_<?lsmb dbname ?>__create_purchase_order; GRANT INSERT ON orderitems TO lsmb_<?lsmb dbname ?>__create_purchase_order; @@ -530,7 +523,7 @@ values (18, 'allow', 'lsmb_<?lsmb dbname ?>__list_all_open'); CREATE ROLE lsmb_<?lsmb dbname ?>__pos_cashier WITH INHERIT NOLOGIN -lsmb_<?lsmb dbname ?>__create_pos_invoice, +IN ROLE lsmb_<?lsmb dbname ?>__create_pos_invoice, lsmb_<?lsmb dbname ?>__close_till; CREATE ROLE lsmb_<?lsmb dbname ?>__all_pos @@ -542,11 +535,11 @@ lsmb_<?lsmb dbname ?>__list_all_open; CREATE ROLE lsmb_<?lsmb dbname ?>__reconcile WITH INHERIT NOLOGIN; -GRANT INSERT ON pending_reports TO lsmb_<?lsmb dbname ?>__reconcile; -GRANT INSERT on report_corrections TO lsmb_<?lsmb dbname ?>__reconcile; +-- GRANT INSERT ON pending_reports TO lsmb_<?lsmb dbname ?>__reconcile; +-- GRANT INSERT on report_corrections TO lsmb_<?lsmb dbname ?>__reconcile; GRANT SELECT ON acc_trans TO lsmb_<?lsmb dbname ?>__reconcile; -GRANT ALL ON pending_reports_id_seq TO lsmb_<?lsmb dbname ?>__reconcile; -GRANT ALL ON report_corrections_id_seq TO lsmb_<?lsmb dbname ?>__reconcile; +-- GRANT ALL ON pending_reports_id_seq TO lsmb_<?lsmb dbname ?>__reconcile; +-- GRANT ALL ON report_corrections_id_seq TO lsmb_<?lsmb dbname ?>__reconcile; INSERT INTO menu_acl (node_id, acl_type, role_name) values (35, 'allow', 'lsmb_<?lsmb dbname ?>_reconcile'); @@ -557,7 +550,7 @@ values (45, 'allow', 'lsmb_<?lsmb dbname ?>_reconcile'); CREATE ROLE lsmb_<?lsmb dbname ?>__approve_reconciliation WITH INHERIT NOLOGIN; -GRANT UPDATE ON pending_reports TO lsmb_<?lsmb dbname ?>__reconcile; +-- GRANT UPDATE ON pending_reports TO lsmb_<?lsmb dbname ?>__reconcile; GRANT SELECT ON acc_trans TO lsmb_<?lsmb dbname ?>__reconcile; INSERT INTO menu_acl (node_id, acl_type, role_name) @@ -575,7 +568,7 @@ lsmb_<?lsmb dbname ?>__approve_reconciliation; CREATE ROLE lsmb_<?lsmb dbname ?>__process_payment WITH INHERIT NOLOGIN -IN ROLE ap_list_transactions; +IN ROLE lsmb_<?lsmb dbname ?>__list_ap_transactions; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__process_payment; GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__process_payment; @@ -591,7 +584,7 @@ values (39, 'allow', 'lsmb_<?lsmb dbname ?>__process_payment'); CREATE ROLE lsmb_<?lsmb dbname ?>__process_receipt WITH INHERIT NOLOGIN -IN ROLE ar_list_transactions; +IN ROLE lsmb_<?lsmb dbname ?>__ar_list_transactions; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__process_receipt; GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__process_receipt; @@ -796,7 +789,7 @@ WITH INHERIT NOLOGIN; GRANT INSERT ON gl TO lsmb_<?lsmb dbname ?>__create_transaction; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__create_transaction; GRANT ALL ON id TO lsmb_<?lsmb dbname ?>__create_transaction; -GRANT ALL ON acc_trans_entry_id TO lsmb_<?lsmb dbname ?>__create_transaction; +GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__create_transaction; INSERT INTO menu_acl (node_id, acl_type, role_name) values (73, 'allow', 'lsmb_<?lsmb dbname ?>__create_transaction'); @@ -816,7 +809,7 @@ WITH INHERIT NOLOGIN; GRANT INSERT ON gl TO lsmb_<?lsmb dbname ?>__create_transaction_voucher; GRANT INSERT ON acc_trans TO lsmb_<?lsmb dbname ?>__create_transaction_voucher; GRANT ALL ON id TO lsmb_<?lsmb dbname ?>__create_transaction_voucher; -GRANT ALL ON acc_trans_entry_id TO lsmb_<?lsmb dbname ?>__create_transaction_voucher; +GRANT ALL ON acc_trans_entry_id_seq TO lsmb_<?lsmb dbname ?>__create_transaction_voucher; -- TODO Add menu permissions @@ -1126,7 +1119,7 @@ WITH INHERIT NOLOGIN IN ROLE lsmb_<?lsmb dbname ?>__create_account, lsmb_<?lsmb dbname ?>__set_taxes, lsmb_<?lsmb dbname ?>__edit_account, -lsmb_<?lsmb dbname ?>__create_gifi; +lsmb_<?lsmb dbname ?>__create_gifi, lsmb_<?lsmb dbname ?>__edit_gifi; CREATE ROLE lsmb_<?lsmb dbname ?>__create_department diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index 0af6f264..5227a848 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -1,22 +1,22 @@ CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer) -RETURNS batches AS +RETURNS batch AS $$ DECLARE - batch_out batches%ROWTYPE; + batch_out batch%ROWTYPE; BEGIN - SELECT * INTO batch_out FROM batches b WHERE b.id = in_batch_id; + SELECT * INTO batch_out FROM batch b WHERE b.id = in_batch_id; RETURN batch_out; END; $$ language plpgsql; -CREATE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date, +CREATE OR REPLACE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date, in_batch_number text, in_description text, in_id integer) RETURNS integer AS $$ BEGIN - UPDATE batches + UPDATE batch SET batch_number = in_batch_number, description = in_description, entered = in_entered @@ -26,7 +26,7 @@ BEGIN RETURN in_id; END IF; - INSERT INTO batches (batch, employee_id, batch_number, description, + INSERT INTO batch (batch, employee_id, batch_number, description, entered) VALUES (in_batch, (SELECT id FROM employees WHERE login = in_login), in_batch_number, description); @@ -185,16 +185,16 @@ BEGIN UPDATE acc_trans SET approved = true WHERE id IN (select trans_id FROM voucher WHERE batch_id = in_batch_id - AND batch_class IN (3, 4, 7, 8); + AND batch_class IN (3, 4, 7, 8)); UPDATE batch SET approved_on = now(), approved_by = (select entity_id FROM users WHERE login = SESSION_USER) - WHERE batch_id = in_batch_id + WHERE batch_id = in_batch_id; - RETURN now()::date -END;; + RETURN now()::date; +END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION batch_list_classes() RETURNS SETOF batch_class AS |