Merging in David Mora's payment changes. Still working on the template and workflow scripts.

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

2 files changed, 36 insertions, 4 deletions

diff --git a/sql/modules/Date.sql b/sql/modules/Date.sql
new file mode 100644
index 00000000..7b942c2a
--- /dev/null
+++ b/sql/modules/Date.sql
@@ -0,0 +1,17 @@
+CREATE OR REPLACE FUNCTION date_get_all_years() returns setof INT AS
+$$
+DECLARE
+    date_out record;
+    BEGIN
+        FOR date_out IN
+           SELECT EXTRACT('YEAR' from transdate) AS year
+           FROM acc_trans
+           GROUP BY EXTRACT('YEAR' from transdate)
+           ORDER BY year
+        LOOP
+             return next date_out.year;
+        END LOOP;
+    END;
+$$ language plpgsql;                                                                  
+COMMENT ON FUNCTION date_get_all_years() IS
+$$ This function return each year inside transdate in transactions. $$;
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 7b771734..f6332099 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -215,7 +215,7 @@ $$ 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, 
+	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):
@@ -272,6 +272,21 @@ comment on function department_list(in_role char) is
 $$ This function returns all department that match the role provided as
 the argument.$$;
 
-
-
-
+CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)returns setof char(3) AS
+$$
+DECLARE resultrow record;
+BEGIN
+        FOR resultrow IN
+          SELECT curr FROM ar
+          WHERE amount <> paid
+          AND in_account_class=2
+          UNION
+          SELECT curr FROM ap
+          WHERE amount <> paid
+          AND in_account_class=1
+          ORDER BY curr
+          LOOP
+         return next resultrow.curr;
+        END LOOP;
+END;
+$$ language plpgsql;