diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 5 | ||||
| -rw-r--r-- | sql/modules/Defaults.sql | 15 | ||||
| -rw-r--r-- | sql/modules/Payment.sql | 65 | ||||
| -rw-r--r-- | sql/modules/chart.sql | 23 |
4 files changed, 102 insertions, 6 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 27c61a4e..ed32224d 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'), 536, true); +SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 550, true); -- @@ -2021,7 +2021,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin; 34 action add 81 34 db vendor 82 35 menu 1 83 -36 module cp.pl 84 +36 module payment.pl 84 36 action payment 85 36 type receipt 86 37 module cp.pl 87 @@ -2462,6 +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 \. diff --git a/sql/modules/Defaults.sql b/sql/modules/Defaults.sql new file mode 100644 index 00000000..62597faf --- /dev/null +++ b/sql/modules/Defaults.sql @@ -0,0 +1,15 @@ +-- Probably want to move this to the Settings module + +CREATE OR REPLACE FUNCTION defaults_get_defaultcurrency() +RETURNS SETOF char(3) AS +$$ +DECLARE defaultcurrency defaults.value%TYPE; + BEGIN + SELECT INTO defaultcurrency substr(value,1,3) + FROM defaults + WHERE setting_key = 'curr'; + RETURN NEXT defaultcurrency; + END; +$$ language plpgsql; +COMMENT ON FUNCTION defaults_get_defaultcurrency() IS +$$ This function return the default currency asigned by the program. $$; diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index f6332099..7f2d16e7 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -57,7 +57,7 @@ CREATE TYPE payment_invoice AS ( ); CREATE OR REPLACE FUNCTION payment_get_open_invoices -(in_account_class int, in_entity_id int, in_currency char(3)) +(in_account_class int, in_entity_id int, in_curr char(3)) RETURNS SETOF payment_invoice AS $$ DECLARE payment_inv payment_invoice; @@ -87,7 +87,7 @@ BEGIN JOIN entity_credit_account c USING (entity_id) WHERE a.invoice_class = in_account_class AND c.entity_class = in_account_class - AND a.curr = in_currency + AND a.curr = in_curr LOOP RETURN NEXT payment_inv; END LOOP; @@ -272,17 +272,20 @@ comment on function department_list(in_role char) is $$ This function returns all department that match the role provided as the argument.$$; -CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)returns setof char(3) AS +CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int) +RETURNS SETOF char(3) AS $$ DECLARE resultrow record; BEGIN FOR resultrow IN SELECT curr FROM ar WHERE amount <> paid - AND in_account_class=2 + OR paid IS NULL + AND in_account_class=2 UNION SELECT curr FROM ap WHERE amount <> paid + OR paid IS NULL AND in_account_class=1 ORDER BY curr LOOP @@ -290,3 +293,57 @@ BEGIN END LOOP; END; $$ language plpgsql; + +CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) +RETURNS NUMERIC AS +$$ +DECLARE + out_exrate exchangerate.buy%TYPE; + + BEGIN + IF in_account_class = 1 THEN + SELECT INTO out_exrate buy + FROM exchangerate + WHERE transdate = in_date AND curr = in_currency; + ELSE + SELECT INTO out_exrate sell + FROM exchangerate + WHERE transdate = in_date AND curr = in_currency; + END IF; + RETURN out_exrate; + END; +$$ language plpgsql; +COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS +$$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$; + + +CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int) +RETURNS SETOF entity AS +$$ +DECLARE + out_info entity%ROWTYPE; + + BEGIN + FOR out_info IN + + SELECT e.id, e.name FROM entity e + JOIN company c ON (e.id = c.entity_id) + WHERE e.id = in_entity_id + + --SELECT e.id, c.legal_name, l.line_one, l.city_province, cy.name FROM entity e + --JOIN company c ON (e.id = c.entity_id) + --JOIN company_to_location cl ON (c.id = cl.company_id) + --JOIN location l ON (l.id = cl.location_id) + --JOIN country cy ON (cy.id = l.country_id) + LOOP + return next out_info; + END LOOP; + + IF NOT FOUND THEN + 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 $$;
\ No newline at end of file diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql new file mode 100644 index 00000000..11e2e38b --- /dev/null +++ b/sql/modules/chart.sql @@ -0,0 +1,23 @@ +CREATE OR REPLACE FUNCTION chart_list_cash(in_account_class int) +RETURNS SETOF chart AS +$$ +DECLARE resultrow record; + link_string text; +BEGIN + IF in_account_class = 1 THEN + link_string := '%AR_paid%'; + ELSE + link_string := '%AP_paid%'; + END IF; + + FOR resultrow IN + SELECT * FROM chart + WHERE link LIKE link_string + ORDER BY accno + LOOP + return next resultrow; + END LOOP; +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 $$; |