Adding new payment posting routine for single payments from David Mora

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

1 files changed, 89 insertions, 40 deletions

diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 1f68d293..e5b02f3c 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -384,52 +384,101 @@ sub-array, the first element is the (integer) transaction id, and the second
 is the amount for that transaction.  If the total of the amounts do not add up 
 to in_total, then an error is generated. $$;
 
+
 CREATE OR REPLACE FUNCTION payment_post 
-(in_trans_id int, in_batch_id int, in_source text, in_amount numeric, 
-	in_ar_ap_accno text, in_cash_accno text, in_approved bool, 
-	in_payment_date date, in_account_class int)
+(in_payment_date   date,
+ in_account_class  int,
+ in_person_id      int,
+ in_currency       char(3),
+ in_notes          text,
+ in_department     int,
+ in_gl_description text,
+ in_cash_accno     int[],
+ in_cash_amount    int[],
+ in_cash_approved  bool[],
+ in_cash_source    text[],
+ in_accno          int[],
+ in_amount         int[],
+ in_approved       bool[],
+ in_source         text[],
+ in_transaction_id int[],
+ in_type           int[],
+ in_approved bool)
 RETURNS INT AS
 $$
-DECLARE out_entry_id int;
+DECLARE var_payment_id int;
+DECLARE var_gl_id int;
+DECLARE var_entry_id int[];
+DECLARE out_count int;
+
 BEGIN
-	INSERT INTO acc_trans (chart_id, amount,
-	            trans_id, transdate, approved, source)
-	VALUES ((SELECT id FROM chart WHERE accno = in_ar_ap_accno), 
-	        CASE WHEN in_account_class = 1 THEN in_amount * -1 
-	             ELSE amount
-	        END,
-	        in_trans_id, in_payment_date, in_approved, in_source);
-
-	INSERT INTO acc_trans (chart_id, amount,
-	            trans_id, transdate, approved, source)
-	VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno), 
-	        CASE WHEN in_account_class = 2 THEN in_amount * -1 
-	             ELSE amount
-	        END,
-	        in_trans_id, in_payment_date, coalesce(in_approved, true), 
-	        in_source);
-
-	SELECT currval('acc_trans_entry_id_seq') INTO out_entry_id;
-	RETURN out_entry_id;
+        -- FIRST WE HAVE TO INSERT THE PAYMENT
+        -- THE ID IS GENERATED BY payment_id_seq
+        --
+   	INSERT INTO payment (reference, payment_class, payment_date,
+	                      person_id, currency, notes, department_id) 
+	VALUES ((CASE WHEN in_account_class = 1 THEN
+	                                setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql 
+			             ELSE 						-- and it is very usefull				
+			                setting_increment('paynumber') 
+			             END),
+	         in_account_class, in_payment_date, in_person_id,
+                 in_currency, in_notes, in_department);
+        SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
+        -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS
+        -- THE ID IS GENERATED BY gl_id_seq
+        --
+        INSERT INTO gl (reference, description, transdate,
+	                      person_id, notes, approved, department_id) 
+	VALUES (setting_increment('glnumber'),
+	         in_gl_description, in_payment_date, in_person_id,
+                 in_notes, in_department, coalesce(in_approved, true));
+        SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
+        -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS... 
+        --
+        -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
+        --
+        -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
+	FOR out_count IN 
+			array_lower(in_cash_accno, 1) ..
+			array_upper(in_cash_accno, 1)
+	LOOP
+	        INSERT INTO acc_trans (chart_id, amount,
+		                       trans_id, transdate, approved, source)
+		VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]), 
+		        CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1 
+		        ELSE in_cash_amount[out_count]
+		        END,
+		        var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true), 
+		        in_cash_source[out_count]);
+		        --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK
+	END LOOP;
+	--
+	-- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT
+	--
+	FOR var_count IN
+		     array_lower(in_accno, 1) ..
+		     array_upper(in_accno, 1)
+	LOOP
+	        INSERT INTO acc_trans (chart_id, amount,
+		                       trans_id, transdate, approved, source)
+		VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]), 
+		        CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1 
+		        ELSE in_amount[out_count]
+		        END,
+		        var_gl_id, in_payment_date, coalesce(in_approved[1], true), 
+		        in_source[out_count]);
+	-- 
+	-- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW
+	--	        
+		INSERT INTO payment_links 
+		VALUES (var_payment_id, currval(acc_trans_entry_id_seq),
+		                                  in_transaction_id[out_count], in_type[var_count]);
+	END LOOP;
+       return 0;
 END;
 $$ 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 date, 
---      in_account_class int)
---IS $$
---This function takes the following arguments (prefaced with in_ in the db):
---trans_id:  Id for ar/ap transaction.
---source: text for source documnet identifier (for example, check number)
---amount:  numeric for the amount of the transaction
---ar_ap_accno:  AR/AP account number
---cash_accno:  Cash Account number, i.e. the account where the payment will be 
---held
---approved:  False, for a voucher.
-
---This function posts the payment or saves the payment voucher. 
---$$;
+-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
 
 
 -- Move this to the projects module when we start on that. CT