[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2440] trunk
- Subject: SF.net SVN: ledger-smb:[2440] trunk
- From: ..hidden..
- Date: Tue, 03 Feb 2009 22:01:08 +0000
Revision: 2440
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2440&view=rev
Author: einhverfr
Date: 2009-02-03 22:01:07 +0000 (Tue, 03 Feb 2009)
Log Message:
-----------
More reconciliaiton issues resolved
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/LedgerSMB/Reconciliation/CSV.pm
trunk/LedgerSMB/Template/HTML.pm
trunk/LedgerSMB/Template.pm
trunk/LedgerSMB.pm
trunk/UI/reconciliation/report.html
trunk/UI/reconciliation/upload.html
trunk/bin/oe.pl
trunk/scripts/recon.pl
trunk/sql/modules/Reconciliaton.sql
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-03 22:01:07 UTC (rev 2440)
@@ -91,6 +91,7 @@
use LedgerSMB::DBObject;
use LedgerSMB::Reconciliation::CSV;
+
# don't need new
sub import_file {
@@ -144,8 +145,10 @@
# Total is in here somewhere, too
# gives us a report ID to insert with.
- my $report_id = $self->exec_method(funcname=>'reconciliation__new_report_id');
+ my @reports = $self->exec_method(funcname=>'reconciliation__new_report_id');
+ my $report_id = $reports[0]->{reconciliation__new_report_id};
$self->{report_id} = $report_id;
+ $self->exec_method(funcname=>'reconciliation__pending_transactions');
# Now that we have this, we need to create the internal report representation.
# Ideally, we OUGHT to not return anything here, save the report number.
@@ -166,16 +169,14 @@
args=>[
$report_id,
$entry->{scn},
- $entry->{chart_id},
$self->{user},
- $self->{date},
+ $entry->{cleared_date},
$entry->{amount}, # needs leading 0's trimmed.
]
);
$entry{report_id} = $report_id;
}
- $self->exec_method(funcname=>'reconciliation__pending_transactions');
$self->{dbh}->commit;
return ($report_id, $entries); # returns the report ID.
@@ -237,8 +238,7 @@
my $self = shift @_;
return $self->exec_method(
- funcname=>'reconciliation__pending',
- args=>[$self->{month}]
+ funcname=>'reconciliation__pending'
);
}
@@ -252,6 +252,25 @@
);
}
+sub get {
+ my ($self) = shift @_;
+ my ($ref) = $self->exec_method(funcname=>'reconciliation__report_summary');
+ $self->merge($ref);
+ @{$self->{report_lines}} = $self->exec_method(
+ funcname=>'reconciliation__report_details'
+ );
+ my ($ref) = $self->exec_method(
+ funcname=>'reconciliation__get_cleared_balance'
+ );
+
+ $our_balance = $ref->{reconciliation__get_cleared_balance};
+ for my $line (@{$self->{report_lines}}){
+ $our_balance += $line->{our_balance}
+ }
+ $self->{our_total} = $our_balance;
+ $self->{format_amount} = sub { return $self->format_amount(@_); }
+}
+
sub get_accounts {
my $self = shift @_;
Modified: trunk/LedgerSMB/Reconciliation/CSV.pm
===================================================================
--- trunk/LedgerSMB/Reconciliation/CSV.pm 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/LedgerSMB/Reconciliation/CSV.pm 2009-02-03 22:01:07 UTC (rev 2440)
@@ -31,6 +31,7 @@
my $self = shift @_;
my $contents = $self->load_file($self->{csv_filename});
my $func = "process_$self->{accno}";
+ my $func =~ s/-/_/g;
@entries = eval{&$func($self, $contents)};
if (!$!){
@{$self->{recon_entries}} = @entries;
Modified: trunk/LedgerSMB/Template/HTML.pm
===================================================================
--- trunk/LedgerSMB/Template/HTML.pm 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/LedgerSMB/Template/HTML.pm 2009-02-03 22:01:07 UTC (rev 2440)
@@ -68,6 +68,8 @@
return escapeHTML($rawvars);
} elsif ($type eq 'SCALAR' or $type eq 'Math::BigInt::GMP') {
return escapeHTML($$rawvars);
+ } elsif ($type eq 'CODE'){
+ return $rawvars;
} else { # Hashes and objects
for ( keys %{$rawvars} ) {
$vars->{preprocess($_)} = preprocess( $rawvars->{$_} );
Modified: trunk/LedgerSMB/Template.pm
===================================================================
--- trunk/LedgerSMB/Template.pm 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/LedgerSMB/Template.pm 2009-02-03 22:01:07 UTC (rev 2440)
@@ -202,13 +202,6 @@
my ($self, $vars) = @_;
return unless $self->{myconfig};
use LedgerSMB;
- if (UNIVERSAL::isa($vars, 'Math::BigFloat')){
- $vars =
- LedgerSMB::format_amount('LedgerSMB',
- amount => $vars,
- user => $self->{myconfig},
- precision => 2);
- }
my $type = ref($vars);
if ($type eq 'SCALAR' || !$type){
Modified: trunk/LedgerSMB.pm
===================================================================
--- trunk/LedgerSMB.pm 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/LedgerSMB.pm 2009-02-03 22:01:07 UTC (rev 2440)
@@ -650,6 +650,26 @@
$date;
}
+sub sanitize_for_display {
+ my $self = shift;
+ my $var = shift;
+ $self->error('Untested API');
+ if (!$var){
+ $var = $self;
+ }
+ for my $k (keys %$var){
+ my $type = ref($var);
+ if (UNIVERSAL::isa($var->{$k}, 'Math::BigFloat')){
+ $var->{$k} =
+ $self->format_amount({amount => $var->{$k}});
+ }
+ elsif ($type == 'HASH'){
+ $self->sanitize_for_display($var->{$k});
+ }
+ }
+
+}
+
# To be replaced with a generic interface to an Error class
sub error {
Modified: trunk/UI/reconciliation/report.html
===================================================================
--- trunk/UI/reconciliation/report.html 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/UI/reconciliation/report.html 2009-02-03 22:01:07 UTC (rev 2440)
@@ -1,38 +1,36 @@
-<center>Reconciliation Report for <?lsmb total.account ?> for the month of <?lsmbtotal.month?></center>
+<?lsmb PROCESS 'ui-header.html' ?>
+<?lsmb INCLUDE 'elements.html' ?>
-<center>
- <?lsmb IF total.errorcode != 0 ?>
- <div style="color:blue; border-style:solid; border-width:1px; border-color: blue;">
- <?lsmb ELSE ?>
- <div style="color:red; border-style:solid; border-width:1px; border-color: blue;">
- <?lsmb END ?>
- Our Balance: total.our_balance | Bank Balance: total.their_balance
- </div>
-</center>
+<div class="listtop" id="title"><?lsmb text('Reconciliation Report') ?></div>
+<div class="info">
+<div> <?lsmb text('Account:') ?><?lsmb account ?></div>
+<div><?lsmb text('Through date') ?><?lsmb end_date ?></div>
+<div><?lsmb text('Statement Balance:') ?><?lsmb their_total ?></div>
+<div><?lsmb text('Ledger Balance:') ?><?lsmb our_total ?></div>
-<center>Report generated by <?lsmb total.user ?></center>
+<center>Report generated by <?lsmb user ?></center>
-<?lsmb IF recon.error ?>
+<?lsmb IF error ?>
<div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" >
- <?lsmb recon.error ?>
+ <?lsmb error ?>
</div>
<?lsmb END ?>
<table border=0>
- <tr>
- <td>Clear date</td>
- <td>Transaction Type</td>
- <td>Our Balance</td>
- <td>Their Balance</td>
- <td>Error Corrections</td>
- <td>Error Code</td>
- <td></td>
+ <tr class="listheading">
+ <th><?lsmb text('Clear date') ?></th>
+ <th><?lsmb text('Transaction Type') ?></th>
+ <th><?lsmb text('Our Balance') ?></th>
+ <th><?lsmb text('Their Balance') ?></th>
+ <th><?lsmb text('Error Corrections') ?></th>
+ <th><?lsmb text('Error Code') ?></th>
+ <th></th>
</tr>
- <?lsmb FOREACH row = records ?>
+ <?lsmb FOREACH row = report_lines ?>
<?lsmb IF row.errorcode != 0 ?>
- <tr style="background-color:red;">
+ <tr style="background-color:pink;">
<?lsmb ELSIF row.id == corrected ?>
<tr style="background-color:yellow;">
<?lsmb ELSE ?>
@@ -41,7 +39,7 @@
<td><?lsmb row.clear_time ?></td>
<td><?lsmb row.transaction_type ?> </td>
<td><?lsmb row.our_balance ?></td>
- <td><?lsmb row.their_balance?></td>
+ <td><?lsmb row.their_balance ?></td>
<td><?lsmb row.corrections ?></td>
<?lsmb IF row.errorcode > 0 ?>
<td><?lsmb row.errorcode ?> <a href="/reconciliation.pl?action=corrections&entry=<?lsmbrow.entry_id?>">View Corrections</a> </td>
@@ -53,7 +51,7 @@
<?lsmb END ?>
</table>
-<?lsmb IF NOT recon.error?>
+<?lsmb IF NOT error?>
<form name="approval" method="POST" action="/reconciliation.pl">
<input type="submit" name="action" value="Approve" />
</form>
Modified: trunk/UI/reconciliation/upload.html
===================================================================
--- trunk/UI/reconciliation/upload.html 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/UI/reconciliation/upload.html 2009-02-03 22:01:07 UTC (rev 2440)
@@ -24,6 +24,13 @@
label = "Account"
} ?>
<?lsmb INCLUDE input element_data = {
+ name = "total",
+ class = "numeric",
+ label = text('Statement Balance'), #'
+ type = "text",
+ size = 12
+ } ?>
+ <?lsmb INCLUDE input element_data = {
name = "end_date",
label = text('To Date'), #'
class = "date",
Modified: trunk/bin/oe.pl
===================================================================
--- trunk/bin/oe.pl 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/bin/oe.pl 2009-02-03 22:01:07 UTC (rev 2440)
@@ -1084,9 +1084,7 @@
$form->{"${_}_base"} += $amount;
}
if ( !$form->{taxincluded} ) {
- my @taxes = Tax::init_taxes( $form, $form->{taxaccounts} );
- $amount +=
- Tax::calculate_taxes( ..hidden.., $form, $amount, 0 );
+ _calc_taxes();
}
$form->{creditremaining} -= $amount;
Modified: trunk/scripts/recon.pl
===================================================================
--- trunk/scripts/recon.pl 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/scripts/recon.pl 2009-02-03 22:01:07 UTC (rev 2440)
@@ -259,7 +259,7 @@
);
return $template->render($recon);
}
-
+ $recon->get();
$template = LedgerSMB::Template->new(
user=> $user,
template => 'reconciliation/report',
@@ -267,6 +267,15 @@
format=>'HTML',
path=>"UI"
);
+ for my $l (@{$recon->{report_lines}}){
+ $l->{their_balance} = $recon->format_amount({amount => $l->{their_balance}});
+ $l->{our_balance} = $recon->format_amount({amount => $l->{our_balance}});
+ }
+ $recon->{their_total} = $recon->format_amount(
+ {amount => $recon->{their_total}});
+ $recon->{our_total} = $recon->format_amount(
+ {amount => $recon->{our_total}});
+
return $template->render($recon);
}
else {
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2009-02-02 01:34:10 UTC (rev 2439)
+++ trunk/sql/modules/Reconciliaton.sql 2009-02-03 22:01:07 UTC (rev 2440)
@@ -1,14 +1,14 @@
CREATE TABLE cr_report (
id bigserial primary key not null,
chart_id int not null references chart(id),
- our_total numeric default 0,
+ their_total numeric not null,
approved boolean not null default 'f',
end_date date not null default now()
);
CREATE TABLE cr_report_line (
id bigserial primary key not null,
- report_id int NOT NULL,
+ report_id int NOT NULL references cr_report(id),
scn text not null, -- SCN is the check #
their_balance numeric,
our_balance numeric,
@@ -38,6 +38,17 @@
insert_time timestamptz not null default now()
);
+CREATE OR REPLACE FUNCTION reconciliation__get_cleared_balance(in_chart_id int)
+RETURNS numeric AS
+$$
+ select CASE WHEN c.category = 'A' THEN sum(ac.amount) * -1 ELSE
+ sum(ac.amount) END
+ FROM chart c
+ JOIN acc_trans ac ON (ac.chart_id = c.id)
+ WHERE c.id = $1 AND ac.cleared is true
+ GROUP BY c.id, c.category;
+$$ LANGUAGE sql;
+
-- to correct OUR wrong amount.
CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$
@@ -266,7 +277,7 @@
in_total numeric, in_end_date date) returns INT as $$
- INSERT INTO cr_report(chart_id, our_total, end_date) values ($1, $2, $3);
+ INSERT INTO cr_report(chart_id, their_total, end_date) values ($1, $2, $3);
SELECT currval('cr_report_id_seq')::int;
$$ language 'sql';
@@ -274,7 +285,6 @@
create or replace function reconciliation__add_entry(
in_report_id INT,
in_scn TEXT,
- in_chart_id int,
in_user TEXT,
in_date TIMESTAMP,
in_amount numeric
@@ -286,81 +296,101 @@
t_errorcode INT;
our_value NUMERIC;
lid INT;
+ in_count int;
+ t_scn TEXT;
BEGIN
- in_account := in_chart_id;
-
- SELECT * INTO la FROM acc_trans gl
- JOIN chart c on gl.chart_id = c.id
- JOIN ap ON gl.trans_id = ap.id
- JOIN coa_to_account cta on cta.chart_id = gl.chart_id
- WHERE gl.source ~ in_scn -- does it look like it?
- and cta.account = in_account
- and gl.amount = in_amount
- AND gl.transdate = in_date;
-
- lid := NULL;
- IF NOT FOUND THEN
- -- they have it, we don't. This is Bad, and implies either a bank
- -- charge or an unaccounted cheque.
-
- if in_scn <> '' and in_scn IS NOT NULL THEN
-
- -- It's a bank charge. Approval action will probably be
- -- adding it as an entry to the general ledger.
- t_errorcode := 2;
- our_value := 0;
- ELSE
- -- Okay, now this is bad.
- -- They have a cheque/sourced charge that we don't.
- -- REsolution action is going to be
- t_errorcode := 3;
- our_value := 0;
- END IF;
-
- ELSif la.amount <> in_amount THEN
-
- t_errorcode := 1;
- our_value := la.amount;
- lid := la.entry_id;
-
- ELSE
- -- it reconciles. No problem.
-
- t_errorcode := 0;
- our_value := la.amount;
- lid := la.entry_id;
-
- END IF;
-
- INSERT INTO cr_report_line (
- report_id,
- scn,
- their_balance,
- our_balance,
- errorcode,
- "user",
- clear_time,
- ledger_id
- )
- VALUES (
- in_report_id,
- in_scn,
- in_amount,
- la.amount,
- t_errorcode,
- (select id from users where username = in_user),
- in_date,
- lid
- );
-
- -- success, basically. This could very likely be collapsed to
- -- do the compare check here, instead of in the Perl app. Save us a DB
- -- call.
+ IF in_scn = '' THEN
+ t_scn := NULL;
+ ELSE
+ t_scn := in_scn;
+ END IF;
+ IF t_scn IS NOT NULL THEN
+ SELECT count(*) INTO in_count FROM cr_report_line
+ WHERE in_scn = scn AND report_id = in_report_id
+ AND their_balance = 0;
+
+ IF in_count = 0 THEN
+ INSERT INTO cr_report_line
+ (report_id, scn, their_balance, our_balance)
+ VALUES
+ (in_report_id, t_scn, in_amount, 0);
+ ELSIF in_count = 1 THEN
+ UPDATE cr_report_line
+ SET their_balance = in_amount
+ WHERE n_scn = scn AND report_id = in_report_id
+ AND their_balance = 0;
+ ELSE
+ SELECT count(*) INTO in_count FROM cr_report_line
+ WHERE in_scn = scn AND report_id = in_report_id
+ AND our_value = in_amount and their_balance = 0;
+
+ IF in_count = 0 THEN -- no match among many of values
+ SELECT id INTO lid FROM cr_report_line
+ WHERE in_scn = scn AND report_id = in_report_id
+ ORDER BY our_balance ASC limit 1;
+
+ UPDATE cr_report_line
+ SET their_balance = in_amount
+ WHERE id = lid;
+
+ ELSIF in_count = 1 THEN -- EXECT MATCH
+ UPDATE cr_report_line
+ SET their_balance = in_amount
+ WHERE in_scn = scn AND report_id = in_report_id
+ AND our_value = in_amount
+ AND their_balance = 0;
+ ELSE -- More than one match
+ SELECT id INTO lid FROM cr_report_line
+ WHERE in_scn = scn AND report_id = in_report_id
+ AND our_value = in_amount
+ ORDER BY id ASC limit 1;
+
+ UPDATE cr_report_line
+ SET their_balance = in_amount
+ WHERE id = lid;
+
+ END IF;
+ END IF;
+ ELSE -- scn IS NULL, check on amount instead
+ SELECT count(*) INTO in_count FROM cr_report_line
+ WHERE report_id = in_report_id AND amount = in_amount
+ AND their_balance = 0;
+
+ IF in_count = 0 THEN -- no match
+ INSERT INTO cr_report_line
+ (report_id, scn, their_balance, our_balance)
+ VALUES
+ (in_report_id, t_scn, in_amount, 0);
+ ELSIF in_count = 1 THEN -- perfect match
+ UPDATE cr_report_line SET their_balance = in_amount
+ WHERE report_id = in_report_id AND amount = in_amount
+ AND their_balance = 0;
+ ELSE -- more than one match
+ SELECT min(id) INTO lid FROM cr_report_line
+ WHERE report_id = in_report_id AND amount = in_amount
+ AND their_balance = 0;
+
+ UPDATE cr_report_line SET their_balance = in_amount
+ WHERE id = lid;
+
+ END IF;
+ END IF;
return 1;
END;
$$ language 'plpgsql';
+comment on function reconciliation__add_entry(
+ in_report_id INT,
+ in_scn TEXT,
+ in_user TEXT,
+ in_date TIMESTAMP,
+ in_amount numeric
+) IS
+$$ This function is very sensitive to ordering of inputs. NULL or empty in_scn values MUST be submitted after meaningful scns. It is also highly recommended
+that within each category, one submits in order of amount. We should therefore
+wrap it in another function which can operate on a set. Implementation TODO.$$;
+
-- this needs help.....
create or replace function reconciliation__pending_transactions (in_end_date DATE, in_chart_id int, in_report_id int) RETURNS int as $$
@@ -381,24 +411,21 @@
UNION
select id, NULL, 'gl' as table FROM gl) gl
ON (gl.table = t.table_name AND gl.id = t.id)
- LEFT JOIN cr_report_line rl
- ON (rl.ledger_id = ac.entry_id)
WHERE ac.cleared IS FALSE
AND ac.chart_id = in_chart_id
AND ac.transdate <= in_end_date
GROUP BY gl.entity_credit_account, ac.source, ac.transdate,
- ac.memo, ac.voucher_id
- HAVING count(rl.ledger_id) = 0;
+ ac.memo, ac.voucher_id;
RETURN in_report_id;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof cr_report as $$
+CREATE OR REPLACE FUNCTION reconciliation__report_details (in_report_id INT) RETURNS setof cr_report_line as $$
DECLARE
- row cr_report;
+ row cr_report_line;
BEGIN
- FOR row IN select * from cr_report where id = in_report_id LOOP
+ FOR row IN select * from cr_report_line where report_id = in_report_id LOOP
RETURN NEXT row;
@@ -407,6 +434,19 @@
$$ language 'plpgsql';
+CREATE OR REPLACE FUNCTION reconciliation__report_summary (in_report_id INT) RETURNS cr_report as $$
+
+ DECLARE
+ row cr_report;
+ BEGIN
+ select * into row from cr_report where id = in_report_id;
+
+ RETURN row;
+
+ END;
+
+$$ language 'plpgsql';
+
CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof cr_report AS $$
DECLARE
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.