From d257ea9283277cfac10c93b247e252805119720f Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Fri, 21 Dec 2007 02:37:23 +0000
Subject: Adding ability to delete vouchers from batch

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1989 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/modules/Voucher.sql | 38 ++++++++++++++++++++++++++++++++++++++
 sql/modules/chart.sql   | 13 +++++++++++++
 2 files changed, 51 insertions(+)

(limited to 'sql/modules')

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
 $$
-- 
cgit v1.2.3