diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 35 | ||||
| -rw-r--r-- | sql/modules/Payment.sql | 345 |
2 files changed, 238 insertions, 142 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index ca907d50..ef6c4de2 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -359,7 +359,12 @@ glnumber|1 projectnumber|1 queue_payments|0 poll_frequency|1 +<<<<<<< .mine +rcptnumber|1 +paynumber|1 +======= separate_duties|1 +>>>>>>> .r2141 \. COMMENT ON TABLE defaults IS $$ @@ -469,6 +474,7 @@ CREATE TABLE entity_credit_account ( entity_class int not null references entity_class(id) check ( entity_class in (1,2) ), discount numeric, discount_terms int default 0, + discount_account_id int references chart(id), taxincluded bool default 'f', creditlimit NUMERIC default 0, terms int2 default 0, @@ -738,35 +744,6 @@ CREATE TABLE ap ( ); COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$; - --- Payment stuff by David Mora - -CREATE TABLE payment ( - id serial primary key, - reference text NOT NULL, - payment_class integer NOT NULL, - amount numeric NOT NULL, - payment_date date default current_date, - closed bool default FALSE, - person_id integer references person(id), - currency char(3), - notes text, - department_id integer default 0); - -COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, etc... $$; -COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment orders, it will be differentiate by payment_class $$; -COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$; -CREATE INDEX payment_id_idx ON Payment(id); - -CREATE TABLE payment_links ( - payment_id integer references Payment(id), - transaction_id integer NOT NULL, - amount numeric); -COMMENT ON TABLE payment_links IS $$ This table will link payment to ar/ap transactions $$; -COMMENT ON COLUMN payment_links.transaction_id IS $$ This column lacks some data integrity controls that must be implemented in the future $$; - - - -- CREATE TABLE taxmodule ( taxmodule_id serial PRIMARY KEY, diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 2ab6cf69..87cca5ca 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -1,3 +1,24 @@ +CREATE OR REPLACE FUNCTION payment_get_entity_accounts +(in_account_class int, + in_vc_name text, + in_vc_idn int) + returns SETOF entity AS + $$ + DECLARE out_entity entity%ROWTYPE; + BEGIN + FOR out_entity IN + 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 (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn) + LOOP + RETURN NEXT out_entity; + END LOOP; + END; + $$ LANGUAGE PLPGSQL; + -- payment_get_open_accounts and the option to get all accounts need to be -- refactored and redesigned. -- CT CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int) @@ -11,15 +32,15 @@ BEGIN 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 - e.id IN (SELECT entity_id FROM ap - WHERE amount <> paid - GROUP BY entity_id) - WHEN in_account_class = 2 THEN - e.id IN (SELECT entity_id FROM ar - WHERE amount <> paid - GROUP BY entity_id) - END + -- AND CASE WHEN in_account_class = 1 THEN + -- e.id IN (SELECT entity_id FROM ap + -- WHERE amount <> paid + -- GROUP BY entity_id) + -- WHEN in_account_class = 2 THEN + -- e.id IN (SELECT entity_id FROM ar + -- WHERE amount <> paid + -- GROUP BY entity_id) + -- END LOOP RETURN NEXT out_entity; END LOOP; @@ -74,55 +95,58 @@ RETURNS SETOF payment_invoice AS $$ DECLARE payment_inv payment_invoice; BEGIN - FOR payment_inv IN - SELECT a.id AS invoice_id, a.invnumber, - a.transdate AS invoice_date, a.amount, - CASE WHEN discount_terms - > extract('days' FROM age(a.transdate)) - THEN 0 - ELSE (a.amount - a.paid) * c.discount / 100 - END AS discount, - a.amount - a.paid - - CASE WHEN discount_terms - > extract('days' FROM age(a.transdate)) - THEN 0 - ELSE (a.amount - a.paid) * c.discount / 100 - END - AS due - FROM (SELECT id, invnumber, transdate, amount, entity_id, + FOR payment_inv IN + SELECT a.id AS invoice_id, a.invnumber AS invnumber, + a.transdate AS invoice_date, a.amount AS amount, + (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate)) + THEN 0 + ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100 + END) AS discount, ac.due + FROM (SELECT id, invnumber, transdate, amount, entity_id, 1 as invoice_class, paid, curr, entity_credit_account, department_id FROM ap UNION - SELECT id, invnumber, transdate, amount, entity_id, + SELECT id, invnumber, transdate, amount, entity_id, 2 AS invoice_class, paid, curr, entity_credit_account, department_id - FROM ar - ) a - JOIN entity_credit_account c ON (c.id = a.entity_credit_account - OR (a.entity_credit_account IS NULL and - a.entity_id = c.entity_id)) - WHERE a.invoice_class = in_account_class - 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) - + FROM ar + ) a + JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount + WHEN in_account_class = 2 + THEN amount * -1 + END) as due + FROM acc_trans + GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id) + JOIN chart ON (chart.id = ac.chart_id) + JOIN entity_credit_account c ON (c.id = a.entity_credit_account + OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id)) + WHERE ((chart.link = 'AP' AND in_account_class = 1) + OR (chart.link = 'AR' AND in_account_class = 2)) + AND a.invoice_class = in_account_class + AND c.entity_class = in_account_class + AND c.id = in_entity_credit_id + 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) + GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms LOOP RETURN NEXT payment_inv; END LOOP; END; $$ LANGUAGE PLPGSQL; + + COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS $$ This function takes three arguments: Type: 1 for vendor, 2 for customer @@ -426,101 +450,201 @@ sub-array, the first element is the (integer) transaction id, and the second is the amount for that transaction. If the total of the amounts do not add up to in_total, then an error is generated. $$; +-- +-- WE NEED A PAYMENT TABLE +-- +CREATE TABLE payment ( + id serial primary key, + reference text NOT NULL, + gl_id integer references gl(id), + payment_class integer NOT NULL, + payment_date date default current_date, + closed bool default FALSE, + entity_id integer references entity(id), + employee_id integer references entity_employee(entity_id), + currency char(3), + notes text, + department_id integer default 0); + +COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$; +COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$; +COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$; +COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$; +CREATE INDEX payment_id_idx ON payment(id); + +CREATE TABLE payment_links ( + payment_id integer references Payment(id), + entry_id integer references acc_trans(entry_id), + type integer); +COMMENT ON TABLE payment_links IS $$ + An explanation to the type field. + * A type 0 means the link is referencing an ar/ap and was created + using an overpayment movement after the receipt was created + * A type 1 means the link is referencing an ar/ap and was made + on the payment creation, its not the product of an overpayment movement + * A type 2 means the link is not referencing an ar/ap and its the product + of the overpayment logic + + With this ideas in order we can do the following + + To get the payment amount we will sum the entries with type > 0. + To get the linked amount we will sum the entries with type < 2. + The overpayment account can be obtained from the entries with type = 2. + + This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M. +$$; + + CREATE OR REPLACE FUNCTION payment_post -(in_payment_date date, - in_account_class int, - in_person_id int, - in_currency char(3), - in_notes text, - in_department int, - in_gl_description text, - in_cash_accno int[], - in_cash_amount int[], - in_cash_approved bool[], - in_cash_source text[], - in_accno int[], - in_amount int[], - in_approved bool[], - in_source text[], - in_transaction_id int[], - in_type int[], - in_approved bool) +(in_datepaid date, + in_account_class int, + in_entity_id int, + in_curr char(3), + in_notes text, + in_department_id int, + in_gl_description text, + in_cash_account_id int[], + in_amount numeric[], + in_cash_approved bool[], + in_source text[], + in_transaction_id int[], + in_op_amount numeric[], + in_op_cash_account_id int[], + in_op_source text[], + in_op_memo text[], + in_op_account_id int[], + in_approved bool) RETURNS INT AS $$ DECLARE var_payment_id int; DECLARE var_gl_id int; +DECLARE var_entry record; DECLARE var_entry_id int[]; DECLARE out_count int; - +DECLARE coa_id record; +DECLARE var_employee int; +DECLARE var_account_id int; BEGIN - -- FIRST WE HAVE TO INSERT THE PAYMENT + SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1; + -- + -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION -- THE ID IS GENERATED BY payment_id_seq -- INSERT INTO payment (reference, payment_class, payment_date, - person_id, currency, notes, department_id) + employee_id, currency, notes, department_id, entity_id) VALUES ((CASE WHEN in_account_class = 1 THEN setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql ELSE -- and it is very usefull setting_increment('paynumber') END), - in_account_class, in_payment_date, in_person_id, - in_currency, in_notes, in_department); + in_account_class, in_datepaid, var_employee, + in_curr, in_notes, in_department_id, in_entity_id); SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table - -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS - -- THE ID IS GENERATED BY gl_id_seq - -- - INSERT INTO gl (reference, description, transdate, - person_id, notes, approved, department_id) - VALUES (setting_increment('glnumber'), - in_gl_description, in_payment_date, in_person_id, - in_notes, in_department, coalesce(in_approved, true)); - SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT + -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... -- -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS -- -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO FOR out_count IN - array_lower(in_cash_accno, 1) .. - array_upper(in_cash_accno, 1) + array_lower(in_cash_account_id, 1) .. + array_upper(in_cash_account_id, 1) LOOP INSERT INTO acc_trans (chart_id, amount, trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]), - CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1 - ELSE in_cash_amount[out_count] + VALUES (in_cash_account_id[out_count], + CASE WHEN in_account_class = 2 THEN in_amount[out_count] + ELSE in_amount[out_count]* - 1 END, - var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true), - in_cash_source[out_count]); - --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK + in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), + in_source[out_count]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1); END LOOP; - -- - -- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT - -- - FOR var_count IN - array_lower(in_accno, 1) .. - array_upper(in_accno, 1) + -- NOW LETS HANDLE THE AR/AP ACCOUNTS + -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE + FOR out_count IN + array_lower(in_transaction_id, 1) .. + array_upper(in_transaction_id, 1) LOOP - INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]), + SELECT INTO var_account_id chart_id FROM acc_trans as ac + JOIN chart as c ON (c.id = ac.chart_id) + WHERE + trans_id = in_transaction_id[out_count] AND + ( c.link = 'AP' OR c.link = 'AR' ); + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source) + VALUES (var_account_id, CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1 ELSE in_amount[out_count] END, - var_gl_id, in_payment_date, coalesce(in_approved[1], true), + in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true), in_source[out_count]); - -- - -- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW - -- - INSERT INTO payment_links - VALUES (var_payment_id, currval(acc_trans_entry_id_seq), - in_transaction_id[out_count], in_type[var_count]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1); + END LOOP; - return 0; +-- +-- WE NEED TO HANDLE THE OVERPAYMENTS NOW +-- + -- + -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS + -- THE ID IS GENERATED BY gl_id_seq + -- + IF (array_upper(in_op_cash_account_id, 1) > 0) THEN + INSERT INTO gl (reference, description, transdate, + person_id, notes, approved, department_id) + VALUES (setting_increment('glnumber'), + in_gl_description, in_datepaid, var_employee, + in_notes, in_approved, in_department_id); + SELECT currval('id') INTO var_gl_id; +-- +-- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE +-- + UPDATE payment SET gl_id = var_gl_id + WHERE id = var_payment_id; + -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... + -- + -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS + -- + FOR out_count IN + array_lower(in_op_cash_account_id, 1) .. + array_upper(in_op_cash_account_id, 1) + LOOP + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source) + VALUES (in_op_cash_account_id[out_count], + CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] + ELSE in_op_amount[out_count] * - 1 + END, + var_gl_id, in_datepaid, coalesce(in_approved, true), + in_op_source[out_count]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2); + END LOOP; + -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS + FOR out_count IN + array_lower(in_op_account_id, 1) .. + array_upper(in_op_account_id, 1) + LOOP + INSERT INTO acc_trans (chart_id, amount, + trans_id, transdate, approved, source, memo) + VALUES (in_op_account_id[out_count], + CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1 + ELSE in_op_amount[out_count] + END, + var_gl_id, in_datepaid, coalesce(in_approved, true), + in_op_source[out_count], in_op_memo[out_count]); + INSERT INTO payment_links + VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2); + END LOOP; + END IF; + return 0; 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 @@ -568,15 +692,9 @@ $$ DECLARE resultrow record; BEGIN FOR resultrow IN - SELECT curr AS curr FROM ar - WHERE amount <> paid - OR paid IS NULL - AND in_account_class=2 + SELECT DISTINCT curr FROM ar UNION - SELECT curr FROM ap - WHERE amount <> paid - OR paid IS NULL - AND in_account_class=1 + SELECT DISTINCT curr FROM ap ORDER BY curr LOOP return next resultrow.curr; @@ -627,7 +745,7 @@ CREATE TYPE payment_location_result 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) +CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int) RETURNS SETOF payment_location_result AS $$ DECLARE out_row RECORD; @@ -640,7 +758,8 @@ DECLARE out_row RECORD; 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 + JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id) + WHERE ec.id = in_entity_credit_id AND lc.id = in_location_class_id ORDER BY lc.id, l.id, c.name LOOP |