[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4436] branches/1.3
- Subject: SF.net SVN: ledger-smb:[4436] branches/1.3
- From: ..hidden..
- Date: Sat, 10 Mar 2012 12:47:01 +0000
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.