Adding missing drafts.sql

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2166 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 88 insertions, 0 deletions

diff --git a/sql/modules/Drafts.sql b/sql/modules/Drafts.sql
new file mode 100644
index 00000000..40312388
--- /dev/null
+++ b/sql/modules/Drafts.sql
@@ -0,0 +1,88 @@
+CREATE TYPE draft_search_result AS (
+	id int,
+	transdate date,
+	description text,
+	amount numeric
+);
+
+CREATE OR REPLACE FUNCTION draft__search(in_type text, in_with_accno text, 
+in_from_date date, in_to_date date, in_amount_le numeric, in_amount_ge numeric)
+returns setof draft_search_result AS
+$$
+DECLARE out_row RECORD;
+BEGIN
+	FOR out_row IN
+		SELECT trans.id, trans.transdate, trans.description, 
+			sum(case when in_type = 'ap' AND chart.link = 'AP'
+				 THEN line.amount
+				 WHEN in_type = 'ar' AND chart.link = 'AR'
+				 THEN line.amount * -1
+				 WHEN in_type = 'gl' AND line.amount > 0
+				 THEN line.amount
+			 	 ELSE 0
+			    END) as amount
+		FROM (
+			SELECT id, transdate, description, approved from ap
+			WHERE in_type = 'ap'
+			UNION
+			SELECT id, transdate, description, approved from ar
+			WHERE in_type = 'ar'
+			UNION
+			SELECT id, transdate, description, approved from gl
+			WHERE in_type = 'gl'
+			) trans
+		JOIN acc_trans line ON (trans.id = line.trans_id)
+		JOIN chart ON (line.chart_id = chart.id)
+		WHERE (in_from_date IS NULL or trans.transdate >= in_from_date)
+			AND (in_to_date IS NULL 
+				or trans.transdate <= in_to_date)
+			AND trans.approved IS FALSE
+			AND trans.id NOT IN (select trans_id from voucher)
+		GROUP BY trans.id, trans.transdate, trans.description
+		HAVING (in_with_accno IS NULL or in_with_accno = 
+			ANY(as_array(chart.accno)))
+	LOOP
+		RETURN NEXT out_row;
+	END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION draft_approve(in_id int) returns bool as
+$$
+declare 
+	t_table text;
+begin
+	SELECT table_name into t_table FROM transactions where id = in_id;
+
+        IF (t_table = 'ar') THEN
+		UPDATE ar set approved = true where id = in_id;
+	ELSIF (t_table = 'ap') THEN
+		UPDATE ap set approved = true where id = in_id;
+	ELSIF (t_table = 'gl') THEN
+		UPDATE gl set approved = true where id = in_id;
+	ELSE
+		raise exception 'Invalid table % in draft_approve for transaction %', t_table, in_id;
+	END IF;
+	RETURN FOUND;
+END;
+$$ LANGUAGE PLPGSQL SECURITY DEFINER;
+
+CREATE OR REPLACE FUNCTION draft_delete(in_id int) returns bool as
+$$
+declare 
+	t_table text;
+begin
+	SELECT table_name into t_table FROM transactions where id = in_id;
+        IF t_table = 'ar' THEN
+		DELETE FROM ar WHERE id = in_id AND approved IS FALSE;
+	ELSIF t_table = 'ap' THEN
+		DELETE FROM ap WHERE id = in_id AND approved IS FALSE;
+	ELSIF t_table = 'gl' THEN
+		DELETE FROM gl WHERE id = in_id AND approved IS FALSE;
+	ELSE
+		raise exception 'Invalid table % in draft_delete for transaction %', t_table, in_id;
+	END IF;
+	RETURN FOUND;
+END;
+$$ LANGUAGE PLPGSQL SECURITY DEFINER;
+