diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Payment.sql | 129 |
1 files changed, 89 insertions, 40 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 1f68d293..e5b02f3c 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -384,52 +384,101 @@ 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. $$; + CREATE OR REPLACE FUNCTION payment_post -(in_trans_id int, in_batch_id int, in_source text, in_amount numeric, - in_ar_ap_accno text, in_cash_accno text, in_approved bool, - in_payment_date date, in_account_class int) +(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) RETURNS INT AS $$ -DECLARE out_entry_id int; +DECLARE var_payment_id int; +DECLARE var_gl_id int; +DECLARE var_entry_id int[]; +DECLARE out_count int; + BEGIN - INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_ar_ap_accno), - CASE WHEN in_account_class = 1 THEN in_amount * -1 - ELSE amount - END, - in_trans_id, in_payment_date, in_approved, in_source); - - INSERT INTO acc_trans (chart_id, amount, - trans_id, transdate, approved, source) - VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno), - CASE WHEN in_account_class = 2 THEN in_amount * -1 - ELSE amount - END, - in_trans_id, in_payment_date, coalesce(in_approved, true), - in_source); - - SELECT currval('acc_trans_entry_id_seq') INTO out_entry_id; - RETURN out_entry_id; + -- FIRST WE HAVE TO INSERT THE PAYMENT + -- THE ID IS GENERATED BY payment_id_seq + -- + INSERT INTO payment (reference, payment_class, payment_date, + person_id, currency, notes, department_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); + 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 + -- 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) + 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] + 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 + END LOOP; + -- + -- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT + -- + FOR var_count IN + array_lower(in_accno, 1) .. + array_upper(in_accno, 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]), + 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_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]); + END LOOP; + return 0; END; $$ LANGUAGE PLPGSQL; - --- COMMENT ON FUNCTION payment_post --- (in_trans_id int, in_source text, in_amount numeric, in_ar_ap_accno text, --- in_cash_accno text, in_approved bool, in_payment_date date, --- in_account_class int) ---IS $$ ---This function takes the following arguments (prefaced with in_ in the db): ---trans_id: Id for ar/ap transaction. ---source: text for source documnet identifier (for example, check number) ---amount: numeric for the amount of the transaction ---ar_ap_accno: AR/AP account number ---cash_accno: Cash Account number, i.e. the account where the payment will be ---held ---approved: False, for a voucher. - ---This function posts the payment or saves the payment voucher. ---$$; +-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION -- Move this to the projects module when we start on that. CT |