diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Voucher.sql | 38 | ||||
| -rw-r--r-- | sql/modules/chart.sql | 13 |
2 files changed, 51 insertions, 0 deletions
diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index f095b92a..31de0bae 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -330,3 +330,41 @@ BEGIN RETURN 1; END; $$ language plpgsql; + +CREATE OR REPLACE FUNCTION voucher__delete(in_voucher_id int) +RETURNS int AS +$$ +DECLARE + voucher_row RECORD; +BEGIN + SELECT * INTO voucher_row FROM voucher WHERE id = in_voucher_id; + IF voucher_row.batch_class IN (1, 2, 5) THEN + DELETE from acc_trans WHERE trans_id = voucher_row.trans_id; + DELETE FROM ar WHERE id = voucher_row.trans_id; + DELETE FROM ap WHERE id = voucher_row.trans_id; + DELETE FROM gl WHERE id = voucher_row.trans_id; + DELETE FROM voucher WHERE id = voucher_row.id; + DELETE FROM transactions WHERE id = voucher_row.trans_id; + ELSE + update ar set paid = amount + + (select sum(amount) from acc_trans + join chart ON (acc_trans.chart_id = chart.id) + where link = 'AR' AND trans_id = ar.id + AND (voucher_id IS NULL + OR voucher_id <> voucher_row.id)) + where id in (select trans_id from acc_trans + where voucher_id = voucher_row.id); + + update ap set paid = amount - (select sum(amount) from acc_trans + join chart ON (acc_trans.chart_id = chart.id) + where link = 'AP' AND trans_id = ap.id + AND (voucher_id IS NULL + OR voucher_id <> voucher_row.id)) + where id in (select trans_id from acc_trans + where voucher_id = voucher_row.id); + + DELETE FROM acc_trans where voucher_id = voucher_row.id; + END IF; + RETURN 1; +END; +$$ LANGUAGE PLPGSQL; diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql index 4575bc69..0a6b6e58 100644 --- a/sql/modules/chart.sql +++ b/sql/modules/chart.sql @@ -1,3 +1,16 @@ +CREATE OR REPLACE FUNCTION chart_list_all() +RETURNS SETOF chart AS +$$ +DECLARE out_row chart%ROWTYPE; +BEGIN + FOR out_row IN + SELECT * FROM chart ORDER BY accno + LOOP + RETURN next out_row; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION chart_list_cash(in_account_class int) RETURNS SETOF chart AS $$ |