[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[4436] branches/1.3



Revision: 4436
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4436&view=rev
Author:   einhverfr
Date:     2012-03-10 12:47:01 +0000 (Sat, 10 Mar 2012)
Log Message:
-----------
Committing recon fix for further testing

Modified Paths:
--------------
    branches/1.3/UI/reconciliation/search.html
    branches/1.3/UI/reconciliation/upload.html
    branches/1.3/sql/Pg-database.sql
    branches/1.3/sql/modules/Fixes.sql
    branches/1.3/sql/modules/Reconciliation.sql

Modified: branches/1.3/UI/reconciliation/search.html
===================================================================
--- branches/1.3/UI/reconciliation/search.html	2012-03-10 11:28:29 UTC (rev 4435)
+++ branches/1.3/UI/reconciliation/search.html	2012-03-10 12:47:01 UTC (rev 4436)
@@ -1,7 +1,7 @@
 <?lsmb PROCESS 'ui-header.html' ?>
 <?lsmb PROCESS 'elements.html' ?>
 
-<div class="title">Search Reconciliation Reports</div>
+<div class="title"><?lsmb text('Search Reconciliation Reports') ?></div>
         
 <div class="body">
         <form name="reconciliation__search" method="post" action="recon.pl" id="reconciliation__search">

Modified: branches/1.3/UI/reconciliation/upload.html
===================================================================
--- branches/1.3/UI/reconciliation/upload.html	2012-03-10 11:28:29 UTC (rev 4435)
+++ branches/1.3/UI/reconciliation/upload.html	2012-03-10 12:47:01 UTC (rev 4436)
@@ -38,6 +38,15 @@
 		size = 14
 	} ?>
 	</div>
+        <div id="fx_row">
+        <?lsmb INCLUDE input element_data = {
+           label = text('Reconcile as FX') #'
+           value = 1
+           type = "checkbox"
+           checked = recon_fx
+           name = "recon_fx"
+        } ?>
+        </div>
 	<div id="button-row">
 	<?lsmb INCLUDE button element_data = {
 		type = "submit",

Modified: branches/1.3/sql/Pg-database.sql
===================================================================
--- branches/1.3/sql/Pg-database.sql	2012-03-10 11:28:29 UTC (rev 4435)
+++ branches/1.3/sql/Pg-database.sql	2012-03-10 12:47:01 UTC (rev 4436)
@@ -4625,6 +4625,7 @@
     deleted_by int references entity(id),
     approved_by int references entity(id),
     approved_username text,
+    recon_fx bool default false,
     CHECK (deleted is not true or approved is not true)
 );
 

Modified: branches/1.3/sql/modules/Fixes.sql
===================================================================
--- branches/1.3/sql/modules/Fixes.sql	2012-03-10 11:28:29 UTC (rev 4435)
+++ branches/1.3/sql/modules/Fixes.sql	2012-03-10 12:47:01 UTC (rev 4436)
@@ -253,3 +253,11 @@
  WHERE qty IS NOT NULL and total = 0;
 
 COMMIT;
+
+BEGIN;
+
+-- FX RECON 
+
+ALTER TABLE cr_report ADD recon_fx bool default false;
+
+COMMIT;

Modified: branches/1.3/sql/modules/Reconciliation.sql
===================================================================
--- branches/1.3/sql/modules/Reconciliation.sql	2012-03-10 11:28:29 UTC (rev 4435)
+++ branches/1.3/sql/modules/Reconciliation.sql	2012-03-10 12:47:01 UTC (rev 4436)
@@ -1,3 +1,5 @@
+BEGIN;
+
 CREATE OR REPLACE FUNCTION reconciliation__submit_set(
 	in_report_id int, in_line_ids int[]) RETURNS bool AS
 $$
@@ -99,6 +101,8 @@
 END;
 $$ LANGUAGE PLPGSQL;
 
+DROP TRIGGER IF EXISTS block_change_when_approved ON cr_report;
+
 CREATE TRIGGER block_change_when_approved BEFORE UPDATE OR DELETE ON cr_report
 FOR EACH ROW EXECUTE PROCEDURE cr_report_block_changing_approved();
 
@@ -200,9 +204,10 @@
 
 -- XXX Badly named, rename for 1.4.  --CT
 CREATE OR REPLACE FUNCTION reconciliation__new_report_id 
-(in_chart_id int, in_total numeric, in_end_date date) returns INT as $$
+(in_chart_id int, in_total numeric, in_end_date date, in_recon_fx) returns INT as $$
 
-    INSERT INTO cr_report(chart_id, their_total, end_date) values ($1, $2, $3);
+    INSERT INTO cr_report(chart_id, their_total, end_date, recon_fx) 
+    values ($1, $2, $3, $4);
     SELECT currval('cr_report_id_seq')::int;
 
 $$ language 'sql';
@@ -360,38 +365,60 @@
 that within each category, one submits in order of amount.  We should therefore
 wrap it in another function which can operate on a set, perhaps in 1.4....$$;
 
+
 create or replace function reconciliation__pending_transactions 
 (in_end_date DATE, in_chart_id int, in_report_id int, in_their_total numeric) 
 RETURNS int as $$
     
     DECLARE
         gl_row RECORD;
+        t_recon_fx BOOL;
     BEGIN
+                SELECT recon_fx INTO t_recon_fx FROM cr_report WHERE id = in_report_id;
+ 
 		INSERT INTO cr_report_line (report_id, scn, their_balance, 
 			our_balance, "user", voucher_id, ledger_id, post_date)
 		SELECT in_report_id, 
 		       COALESCE(ac.source, gl.ref),
 		       0, 
-		       sum(amount) AS amount,
+		       sum(amount / CASE WHEN t_recon_fx IS NOT TRUE OR gl.table = 'gl'
+                                         THEN 1
+                                         WHEN t_recon_fx and gl.table = 'ap' 
+                                         THEN ex.sell
+                                         WHEN t_recon_fx and gl.table = 'ar' 
+                                         THEN ex.buy
+                                    END) AS amount,
 				(select entity_id from users 
 				where username = CURRENT_USER),
 			ac.voucher_id, min(ac.entry_id), ac.transdate
 		FROM acc_trans ac
 		JOIN transactions t on (ac.trans_id = t.id)
-		JOIN (select id, entity_credit_account::text as ref, 'ar' as table FROM ar where approved
+		JOIN (select id, entity_credit_account::text as ref, curr, 
+                             transdate, 'ar' as table 
+                        FROM ar where approved
 			UNION
-		      select id, entity_credit_account::text, 'ap' as table FROM ap WHERE approved
+		      select id, entity_credit_account::text, curr, 
+                             transdate, 'ap' as table 
+                        FROM ap WHERE approved
 			UNION
-		      select id, reference, 'gl' as table FROM gl WHERE approved) gl 
+		      select id, reference, '', 
+                             transdate, 'gl' as table 
+                        FROM gl WHERE approved) gl 
 			ON (gl.table = t.table_name AND gl.id = t.id)
 		LEFT JOIN cr_report_line rl ON (rl.report_id = in_report_id
 			AND ((rl.ledger_id = ac.entry_id 
 				AND ac.voucher_id IS NULL) 
 				OR (rl.voucher_id = ac.voucher_id)))
+                LEFT JOIN exchangerate ex ON gl.transdate = ex.transdate
 		WHERE ac.cleared IS FALSE
 			AND ac.approved IS TRUE
 			AND ac.chart_id = in_chart_id
 			AND ac.transdate <= in_end_date
+                        AND ((t_recon_fx is not true 
+                                and ac.fx_transaction is not true) 
+                            OR (t_recon_fx is true 
+                                AND (gl.table <> 'gl' OR ac.fx_transaction
+                                                      IS TRUE))) 
 		GROUP BY gl.ref, ac.source, ac.transdate,
 			ac.memo, ac.voucher_id, gl.table
 		HAVING count(rl.id) = 0;
@@ -502,6 +529,8 @@
 exact matches.
 $$;
 
+DROP TYPE IF EXISTS recon_accounts CASCADE;
+
 create type recon_accounts as (
     name text,
     accno text,
@@ -588,3 +617,5 @@
 
 COMMENT ON FUNCTION reconciliation__report_details_payee (in_report_id INT) IS
 $$ Pulls the payee information for the reconciliation report.$$;
+
+COMMIT;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.