Commiting David Mora's payment code

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

4 files changed, 102 insertions, 6 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 27c61a4e..ed32224d 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -1935,7 +1935,7 @@ CREATE TABLE menu_attribute (
 -- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
 --
 
-SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 536, true);
+SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 550, true);
 
 
 --
@@ -2021,7 +2021,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin;
 34	action	add	81
 34	db	vendor	82
 35	menu	1	83
-36	module	cp.pl	84
+36	module	payment.pl	84
 36	action	payment	85
 36	type	receipt	86
 37	module	cp.pl	87
@@ -2462,6 +2462,7 @@ COPY menu_attribute (node_id, attribute, value, id) FROM stdin;
 194	type	credit_note	548
 195	type	credit_invoice	542
 197	type	debit_invoice	546
+36	account_class	1	550
 \.
 
 
diff --git a/sql/modules/Defaults.sql b/sql/modules/Defaults.sql
new file mode 100644
index 00000000..62597faf
--- /dev/null
+++ b/sql/modules/Defaults.sql
@@ -0,0 +1,15 @@
+-- Probably want to move this to the Settings module
+
+CREATE OR REPLACE FUNCTION defaults_get_defaultcurrency() 
+RETURNS SETOF char(3) AS
+$$
+DECLARE defaultcurrency defaults.value%TYPE;
+      BEGIN   
+           SELECT INTO defaultcurrency substr(value,1,3)
+           FROM defaults
+           WHERE setting_key = 'curr';
+           RETURN NEXT defaultcurrency;
+      END;
+$$ language plpgsql;                                                                  
+COMMENT ON FUNCTION defaults_get_defaultcurrency() IS
+$$ This function return the default currency asigned by the program. $$;
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index f6332099..7f2d16e7 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -57,7 +57,7 @@ CREATE TYPE payment_invoice AS (
 );
 
 CREATE OR REPLACE FUNCTION payment_get_open_invoices
-(in_account_class int, in_entity_id int, in_currency char(3))
+(in_account_class int, in_entity_id int, in_curr char(3))
 RETURNS SETOF payment_invoice AS
 $$
 DECLARE payment_inv payment_invoice;
@@ -87,7 +87,7 @@ BEGIN
 		  JOIN entity_credit_account c USING (entity_id)
 		 WHERE a.invoice_class = in_account_class
 		       AND c.entity_class = in_account_class
-		       AND a.curr = in_currency
+		       AND a.curr = in_curr
 	LOOP
 		RETURN NEXT payment_inv;
 	END LOOP;
@@ -272,17 +272,20 @@ 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
+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
+          OR paid IS NULL
+          AND in_account_class=2 
           UNION
           SELECT curr FROM ap
           WHERE amount <> paid
+          OR paid IS NULL
           AND in_account_class=1
           ORDER BY curr
           LOOP
@@ -290,3 +293,57 @@ BEGIN
         END LOOP;
 END;
 $$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) 
+RETURNS NUMERIC AS
+$$
+DECLARE 
+    out_exrate exchangerate.buy%TYPE;
+
+    BEGIN 
+        IF in_account_class = 1 THEN
+          SELECT INTO out_exrate buy 
+          FROM exchangerate
+          WHERE transdate = in_date AND curr = in_currency;
+        ELSE 
+          SELECT INTO out_exrate sell 
+          FROM exchangerate
+          WHERE transdate = in_date AND curr = in_currency;   
+        END IF;
+        RETURN out_exrate;
+    END;
+$$ language plpgsql;                                                                  
+COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS
+$$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$;
+
+
+CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int) 
+RETURNS SETOF entity AS
+$$
+DECLARE 
+    out_info entity%ROWTYPE;
+
+    BEGIN
+        FOR out_info IN
+  
+            SELECT e.id, e.name FROM entity e
+            JOIN company c ON (e.id = c.entity_id)
+            WHERE e.id = in_entity_id
+          
+          --SELECT e.id, c.legal_name, l.line_one, l.city_province, cy.name FROM entity e
+          --JOIN company c ON (e.id = c.entity_id)
+          --JOIN company_to_location cl ON (c.id = cl.company_id)
+          --JOIN location l ON (l.id = cl.location_id)
+          --JOIN country cy ON (cy.id = l.country_id)
+        LOOP
+          return next out_info;
+        END LOOP;
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'ID % not found!!!!!', in_entity_id;
+        END IF;                              
+              
+    END;
+$$ language plpgsql;                                                                  
+COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int) IS
+$$ This function return vendor or customer info, its under construction $$;
\ No newline at end of file
diff --git a/sql/modules/chart.sql b/sql/modules/chart.sql
new file mode 100644
index 00000000..11e2e38b
--- /dev/null
+++ b/sql/modules/chart.sql
@@ -0,0 +1,23 @@
+CREATE OR REPLACE FUNCTION chart_list_cash(in_account_class int)
+RETURNS SETOF chart AS
+$$
+DECLARE resultrow record;
+        link_string text;
+BEGIN
+        IF in_account_class = 1 THEN
+           link_string := '%AR_paid%';
+        ELSE 
+           link_string := '%AP_paid%';
+        END IF;
+
+        FOR resultrow IN
+          SELECT *  FROM chart
+          WHERE link LIKE link_string
+          ORDER BY accno
+          LOOP
+          return next resultrow;
+        END LOOP;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
+$$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;