diff options
Diffstat (limited to 'sql/modules')
| -rw-r--r-- | sql/modules/Payment.sql | 111 | ||||
| -rw-r--r-- | sql/modules/chart.sql | 28 |
2 files changed, 97 insertions, 42 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index e5b02f3c..a0a36b3c 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -6,16 +6,17 @@ $$ DECLARE out_entity entity%ROWTYPE; BEGIN FOR out_entity IN - SELECT ec.id, e.name, e.entity_class, e.created + SELECT ec.id, cp.legal_name as name, e.entity_class, e.created FROM entity e JOIN entity_credit_account ec ON (ec.entity_id = e.id) + JOIN company cp ON (cp.entity_id = e.id) WHERE ec.entity_class = in_account_class AND CASE WHEN in_account_class = 1 THEN - id IN (SELECT entity_id FROM ap + e.id IN (SELECT entity_id FROM ap WHERE amount <> paid GROUP BY entity_id) WHEN in_account_class = 2 THEN - id IN (SELECT entity_id FROM ar + e.id IN (SELECT entity_id FROM ar WHERE amount <> paid GROUP BY entity_id) END @@ -61,12 +62,19 @@ CREATE TYPE payment_invoice AS ( ); CREATE OR REPLACE FUNCTION payment_get_open_invoices -(in_account_class int, in_entity_credit_id int, in_curr char(3)) +(in_account_class int, + in_entity_credit_id int, + in_curr char(3), + in_datefrom date, + in_dateto date, + in_amountfrom numeric, + in_amountto numeric, + in_department_id int) RETURNS SETOF payment_invoice AS $$ DECLARE payment_inv payment_invoice; BEGIN - FOR payment_inv IN + FOR payment_inv IN SELECT a.id AS invoice_id, a.invnumber, a.transdate AS invoice_date, a.amount, CASE WHEN discount_terms @@ -79,15 +87,16 @@ BEGIN > extract('days' FROM age(a.transdate)) THEN 0 ELSE (a.amount - a.paid) * c.discount / 100 - END AS due + END + AS due FROM (SELECT id, invnumber, transdate, amount, entity_id, 1 as invoice_class, paid, curr, - entity_credit_account + entity_credit_account, department_id FROM ap UNION SELECT id, invnumber, transdate, amount, entity_id, 2 AS invoice_class, paid, curr, - entity_credit_account + entity_credit_account, department_id FROM ar ) a JOIN entity_credit_account c ON (c.id = a.entity_credit_account @@ -97,13 +106,24 @@ BEGIN AND c.entity_class = in_account_class AND a.amount - a.paid <> 0 AND a.curr = in_curr + AND (a.transdate >= in_datefrom + OR in_datefrom IS NULL) + AND (a.transdate <= in_dateto + OR in_dateto IS NULL) + AND (a.amount >= in_amountfrom + OR in_amountfrom IS NULL) + AND (a.amount <= in_amountto + OR in_amountto IS NULL) + AND (a.department_id = in_department_id + OR in_department_id IS NULL) + LOOP RETURN NEXT payment_inv; END LOOP; END; $$ LANGUAGE PLPGSQL; -COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3)) IS +COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric) IS $$ This function takes three arguments: Type: 1 for vendor, 2 for customer Entity_id: The entity_id of the customer or vendor @@ -479,8 +499,6 @@ BEGIN END; $$ LANGUAGE PLPGSQL; -- I HAVE TO MAKE A COMMENT ON THIS FUNCTION - - -- Move this to the projects module when we start on that. CT CREATE OR REPLACE FUNCTION project_list_open(in_date date) RETURNS SETOF project AS @@ -566,37 +584,50 @@ $$ 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). $$; +-- +-- payment_location_result has the same arch as location_result, except for one field +-- This should be unified on the API when we get things working - David Mora +-- +CREATE TYPE payment_location_result AS ( + id int, + line_one text, + line_two text, + line_three text, + city text, + state text, + country text, + class text +); + -CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int) -RETURNS SETOF entity AS +-- +-- payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities +-- This should be unified on the API when we get things working - David Mora +-- +CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int) +RETURNS SETOF payment_location_result 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 $$; +DECLARE out_row RECORD; + BEGIN + FOR out_row IN + SELECT l.id, l.line_one, l.line_two, l.line_three, l.city, + l.state, c.name, lc.class + FROM location l + JOIN company_to_location ctl ON (ctl.location_id = l.id) + JOIN company cp ON (ctl.company_id = cp.id) + JOIN location_class lc ON (ctl.location_class = lc.id) + JOIN country c ON (c.id = l.country_id) + WHERE cp.entity_id = in_entity_id AND + lc.id = in_location_class_id + ORDER BY lc.id, l.id, c.name + LOOP + RETURN NEXT out_row; + END LOOP; + END; +$$ LANGUAGE PLPGSQL; + +COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int) IS +$$ This function returns vendor or customer info $$; CREATE TYPE payment_record AS ( amount numeric, diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql index 53460766..2e136371 100644 --- a/sql/modules/chart.sql +++ b/sql/modules/chart.sql @@ -20,9 +20,9 @@ DECLARE resultrow record; link_string text; BEGIN IF in_account_class = 1 THEN - link_string := '%AR_paid%'; - ELSE link_string := '%AP_paid%'; + ELSE + link_string := '%AR_paid%'; END IF; FOR resultrow IN @@ -58,3 +58,27 @@ $$ 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 $$; + +CREATE OR REPLACE FUNCTION chart_list_overpayment(in_account_class int) +RETURNS SETOF chart AS +$$ +DECLARE resultrow record; + link_string text; +BEGIN + IF in_account_class = 1 THEN + link_string := '%AP_overpayment%'; + ELSE + link_string := '%AR_overpayment%'; + 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 overpayment accounts acording with in_account_class which must be 1 or 2 $$; |