[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2446] trunk
- Subject: SF.net SVN: ledger-smb:[2446] trunk
- From: ..hidden..
- Date: Thu, 05 Feb 2009 19:51:33 +0000
Revision: 2446
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2446&view=rev
Author: einhverfr
Date: 2009-02-05 19:51:32 +0000 (Thu, 05 Feb 2009)
Log Message:
-----------
Interface in place to handle cleared transactions
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/UI/reconciliation/report.html
trunk/scripts/recon.pl
trunk/sql/modules/Reconciliaton.sql
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-04 20:15:34 UTC (rev 2445)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-05 19:51:32 UTC (rev 2446)
@@ -94,6 +94,11 @@
# don't need new
+sub update {
+ my $self = shift @_;
+ $self->exec_method(funcname=>'reconciliation__pending_transactions');
+}
+
sub import_file {
my $self = shift @_;
Modified: trunk/UI/reconciliation/report.html
===================================================================
--- trunk/UI/reconciliation/report.html 2009-02-04 20:15:34 UTC (rev 2445)
+++ trunk/UI/reconciliation/report.html 2009-02-05 19:51:32 UTC (rev 2446)
@@ -1,5 +1,5 @@
<?lsmb PROCESS 'ui-header.html' ?>
-<?lsmb INCLUDE 'elements.html' ?>
+<?lsmb PROCESS 'elements.html' ?>
<div class="listtop" id="title"><?lsmb text('Reconciliation Report') ?></div>
<div class="info">
@@ -15,20 +15,25 @@
<?lsmb error ?>
</div>
<?lsmb END ?>
-
+<?lsmb line = 0 ?>
+<form action="recon.pl" method="post">
<table border=0>
-
<tr class="listheading">
+ <th><?lsmb text('Cleared') ?></th>
+ <th><?lsmb text('Transaction Type') ?></th>
<th><?lsmb text('Clear date') ?></th>
- <th><?lsmb text('Transaction Type') ?></th>
+ <th><?lsmb text('Posted Date') ?></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 = report_lines ?>
+ <?lsmb INCLUDE input element_data = {
+ type = "hidden",
+ name = "id_$i",
+ value = "row.id"
+ } ?>
<?lsmb IF row.errorcode != 0 ?>
<tr style="background-color:pink;">
<?lsmb ELSIF row.id == corrected ?>
@@ -36,18 +41,41 @@
<?lsmb ELSE ?>
<tr>
<?lsmb END ?>
+ <?lsmb IF (row.our_balance == row.their_balance)
+ -?>
+ <?lsmb row.approved = 1 ?>
+ <?lsmb ELSE ?> <?lsmb row.approved = undef -?>
+ <?lsmb END -?>
+ <td><?lsmb INCLUDE input element_data = {
+ type = "checkbox"
+ name = "approved_$row.id"
+ value = row.id
+ checked = row.approved
+ }
+ ?></td>
+ <td><?lsmb row.transaction_type ?> </td>
<td><?lsmb row.clear_time ?></td>
- <td><?lsmb row.transaction_type ?> </td>
+ <td><?lsmb row.post_date ?></td>
<td><?lsmb row.our_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>
- <?lsmb ELSE ?>
- <td>0</td>
- <?lsmb END ?>
- <td><a href="/reconciliation.pl?action=correct&report_id=<?lsmbrow.report_id?>&entry_id=<?lsmbrow.entry_id?>">Correct</a></td>
</tr>
<?lsmb END ?>
</table>
-
+<?lsmb INCLUDE input element_data = {
+ name = "report_id",
+ type = "hidden",
+ value = report_id,
+} ?>
+<?lsmb INCLUDE button element_data = {
+ name = "action",
+ text = text('Update'),
+ value = 'update_recon_set',
+ class = "submit"
+} ?>
+<?lsmb INCLUDE button element_data = {
+ name = "action",
+ text = text('Submit'),
+ value = 'submit_recon_set',
+ class = "submit"
+} ?>
+</body></html>
Modified: trunk/scripts/recon.pl
===================================================================
--- trunk/scripts/recon.pl 2009-02-04 20:15:34 UTC (rev 2445)
+++ trunk/scripts/recon.pl 2009-02-05 19:51:32 UTC (rev 2446)
@@ -71,6 +71,13 @@
=cut
+sub update_recon_set {
+ my ($request) = shift;
+ my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request);
+ $recon->update();
+ _display_report($recon);
+}
+
sub pending_search {
&search(shift @_,"pending");
@@ -225,6 +232,28 @@
=cut
+sub _display_report {
+ my $recon = shift;
+ $recon->get();
+ $template = LedgerSMB::Template->new(
+ user=> $user,
+ template => 'reconciliation/report',
+ language => $user->{language},
+ 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);
+}
+
sub new_report {
my ($request) = @_;
# how are we going to allow this to be created? Grr.
@@ -259,24 +288,7 @@
);
return $template->render($recon);
}
- $recon->get();
- $template = LedgerSMB::Template->new(
- user=> $user,
- template => 'reconciliation/report',
- language => $user->{language},
- 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);
+ _display_report($recon);
}
else {
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2009-02-04 20:15:34 UTC (rev 2445)
+++ trunk/sql/modules/Reconciliaton.sql 2009-02-05 19:51:32 UTC (rev 2446)
@@ -3,6 +3,7 @@
chart_id int not null references chart(id),
their_total numeric not null,
approved boolean not null default 'f',
+ submitted boolean not null default 'f',
end_date date not null default now()
);
@@ -14,9 +15,9 @@
our_balance numeric,
errorcode INT,
"user" int references entity(id) not null, -- why ois this not an entity reference?
- corrections INT NOT NULL DEFAULT 0,
clear_time date,
insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
+ post_date date,
ledger_id int REFERENCES acc_trans(entry_id),
voucher_id int REFERENCES voucher(id),
overlook boolean not null default 'f',
@@ -29,14 +30,6 @@
);
-CREATE TABLE cr_report_corrections (
- id serial primary key not null,
- correction_id int not null default 1,
- "entry" int references cr_report_line(id) not null,
- "user" int references entity(id) not null, -- why is this not an entity reference?
- reason text not null,
- insert_time timestamptz not null default now()
-);
CREATE OR REPLACE FUNCTION reconciliation__get_cleared_balance(in_chart_id int)
RETURNS numeric AS
@@ -49,180 +42,6 @@
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 $$
-
- DECLARE
- new_code INT;
- current_row RECORD;
- l_row RECORD;
- in_user TEXT;
- full_reason TEXT;
- BEGIN
- in_user := current_user;
-
- select * into current_row from cr_report_line l where l.id = in_report_id and l.id = in_id;
- select * into l_row from acc_trans where entry_id = current_row.lid;
-
- IF NOT FOUND THEN
- RAISE EXCEPTION 'No such id % in this report.', in_scn;
- END IF;
-
- IF user <> current_row.user THEN
-
- IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
- update cr_report_line
- set corrections = corrections + 1,
- new_balance = in_new_amount,
- errorcode = 0
- where id = in_report_id and scn = in_scn;
- return 0;
-
- -- After that, it is required to update the general ledger.
- full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %.
- Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
- perform reconciliation__update_ledger(current_row.lid, in_new_amount, full_reason);
- ELSIF current_row.our_balance = in_new_amount THEN
- -- This should be something, does it equal the original
- -- balance? If so, there's no change.
- return current_row.errorcode;
- END IF;
- END IF;
-
- return current_row.errorcode;
-
- END;
-$$ language 'plpgsql';
-
--- to correct an incorrect bank statement value.
-CREATE OR REPLACE FUNCTION reconciliation__correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$
-
- DECLARE
- new_code INT;
- current_row RECORD;
- in_user TEXT;
- BEGIN
- in_user := current_user;
-
- select * into current_row from cr_report_line r
- where r.id = in_id and r.report_id = in_report_id;
-
- IF NOT FOUND THEN
- RAISE EXCEPTION 'No such SCN % in this report.', in_scn;
- END IF;
-
- IF user <> current_row.user THEN
-
- IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
- update cr_report_line
- set corrections = corrections + 1,
- new_balance = in_new_amount,
- errorcode = 0
- where id = in_report_id and scn = in_scn;
- return 0;
-
- ELSIF current_row.their_balance = in_new_amount THEN
- -- This should be something, does it equal the original
- -- balance? If so, there's no change.
- return current_row.errorcode;
- END IF;
- END IF;
-
- return current_row.errorcode;
-
- END;
-$$ language 'plpgsql';
-
-
-CREATE OR REPLACE function reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
-
- DECLARE
- in_user TEXT;
- pending_entry cr_report_line;
- BEGIN
- in_user := current_user;
-
- select * into pending_entry
- from cr_report_line l where report_id = in_report_id and id = in_id;
-
- IF NOT FOUND THEN
- -- Raise an exception.
- RAISE EXCEPTION 'Cannot find entry.';
- ELSIF pending_entry.errorcode <> 4 THEN
- -- Only error codes of 4 may be "passed through" safely.
- RAISE EXCEPTION 'Selected entry not permitted to be passed through.';
-
- ELSE
- -- Then we mark it passthroughable, and "approve" will overlook it.
- update cr_report_line set overlook = 't', errorcode = 0
- where report_id = in_report_id and id = in_id;
-
- return 0;
- END IF;
- END;
-
-$$ language 'plpgsql';
-
-CREATE OR REPLACE FUNCTION reconciliation__correct_bank_charge (in_report_id int, in_id int) returns INT AS $$
-
- DECLARE
- in_user TEXT;
- pending_entry cr_report_line;
- BEGIN
-
- IF NOT FOUND THEN
- -- Raise an exception.
- RAISE EXCEPTION 'Cannot find entry with ID % in report %.', in_id, in_report_id;
- ELSIF pending_entry.errorcode <> 2 THEN
- -- Only error codes of 2 may be "passed through" safely.
- RAISE EXCEPTION 'Attempt to retroactively add a non-bank-charge entry to the ledger.';
-
- ELSE
- -- Then we mark it passthroughable, and "approve" will overlook it.
-
- PERFORM create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time, 'Bank charge');
-
- update cr_report_line set errorcode = 0
- where report_id = in_report_id and id = in_id;
-
- return 0;
- END IF;
- END;
-
-$$ LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION reconciliation__correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$
-
- DECLARE
- in_user TEXT;
- pending_entry cr_report_line;
- note TEXT;
- BEGIN
- in_user := session_user;
-
- IF NOT FOUND THEN
- -- Raise an exception.
- RAISE EXCEPTION 'Cannot find entry with ID % in report %.', in_id, in_report_id;
- ELSIF pending_entry.errorcode <> 3 THEN
- -- Only error codes of 3 may be "passed through" safely.
- RAISE EXCEPTION 'Not an unaccounted charge; cannot be retroactively added to the ledger.';
-
- ELSE
- -- Then we mark it passthroughable, and "approve" will overlook it.
-
- note := 'Retroactive addition of an unaccounted entry, of value %.
- Being added by user % with the following explanation: %', pending_entry.their_balance, in_user, in_reason;
-
- select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time,note);
-
- update cr_report_line set errorcode = 0
- where report_id = in_report_id and id = in_id;
-
- return in_id;
- END IF;
- END;
-$$ language 'plpgsql';
-
CREATE OR REPLACE FUNCTION reconciliation__report_approve (in_report_id INT) returns INT as $$
-- Does some basic checks before allowing the approval to go through;
@@ -395,18 +214,17 @@
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 $$
DECLARE
gl_row RECORD;
BEGIN
INSERT INTO cr_report_line (report_id, scn, their_balance,
- our_balance, "user", voucher_id, ledger_id)
+ our_balance, "user", voucher_id, ledger_id, post_date)
SELECT in_report_id, ac.source, 0, sum(amount) * -1 AS amount,
(select entity_id from users
where username = CURRENT_USER),
- ac.voucher_id, min(ac.entry_id)
+ 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, 'ar' as table FROM ar
@@ -415,11 +233,16 @@
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.report_id = in_report_id
+ AND ((rl.ledger_id = ac.trans_id
+ AND ac.voucher_id IS NULL)
+ OR (rl.voucher_id = ac.voucher_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;
+ ac.memo, ac.voucher_id
+ HAVING count(rl.id) = 0;
RETURN in_report_id;
END;
$$ LANGUAGE plpgsql;
@@ -429,7 +252,10 @@
DECLARE
row cr_report_line;
BEGIN
- FOR row IN select * from cr_report_line where report_id = in_report_id LOOP
+ FOR row IN
+ select * from cr_report_line where report_id = in_report_id
+ order by post_date
+ LOOP
RETURN NEXT row;
@@ -470,116 +296,73 @@
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION reconciliation__corrections (in_report_id INT, in_id INT) returns setof cr_report_corrections AS $$
+--CREATE OR REPLACE FUNCTION reconciliation__search (
+-- in_date_begin DATE,
+-- in_date_end DATE,
+-- in_account TEXT,
+-- in_status TEXT
+--) RETURNS setof cr_report AS $$
- DECLARE
- corr cr_report_corrections;
- BEGIN
-
- SELECT * INTO corr FROM cr_report_corrections
- WHERE report_id = in_report_id AND id = in_id LIMIT 1;
-
- IF NOT FOUND THEN
- RAISE EXCEPTION 'No corrections for selected entry.';
- ELSE
-
- FOR corr IN
- select * from cr_report_corrections
- WHERE report_id = in_report_id AND id = in_id
- LOOP
- RETURN NEXT corr;
- END LOOP;
- END IF;
- END;
-
-$$ language 'plpgsql';
-
-CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_id INT) returns cr_report_line AS $$
-
- DECLARE
- row cr_report_line;
- BEGIN
-
- SELECT * INTO row FROM cr_report_line WHERE id = in_id LIMIT 1;
- -- if there's more than one, that's a Bad Thing
-
- IF NOT FOUND THEN
- RAISE EXCEPTION 'Could not find selected report entry';
- END IF;
-
- RETURN row;
- END;
-
-$$ language 'plpgsql';
-
--- TODO: Rewrite this function
-CREATE OR REPLACE FUNCTION reconciliation__search (
- in_date_begin DATE,
- in_date_end DATE,
- in_account TEXT,
- in_status TEXT
-) RETURNS setof cr_report AS $$
-
- DECLARE
- row reports;
- statement text;
- where_stmt text;
- v_status BOOLEAN;
- v_accum NUMERIC;
- BEGIN
-
- if in_status = "pending" then
- v_status = 'f'::bool;
- ELSIF in_status = "approved" THEN
-
- v_status = 't'::bool;
- END IF;
-
- IF in_date_begin IS NOT NULL
- or in_date_end IS NOT NULL
- or in_account IS NOT NULL
- or v_status IS NOT NULL
- THEN
- statement = 'select pr.* from reports pr ';
- statement = statement || $s$join acc_trans at on pr.ledger_id = at.entry_id $s$;
-
- IF in_account IS NOT NULL THEN
-
- statement = statement || $s$join chart c on at.chart_id = c.id $s$;
- where_stmt = $s$c.accno =~ $s$ || quote_literal(in_account) || $s$ AND $s$;
- END IF;
-
- IF in_date_begin IS NOT NULL THEN
- where_stmt = where_stmt || $s$insert_time >= $s$ || quote_literal(in_date_begin) || $s$ AND $s$;
- END IF;
-
- IF in_date_end IS NOT NULL THEN
- where_stmt = where_stmt || $s$insert_time <= $s$ || quote_literal(in_date_end) || $s$ AND $s$;
- END IF;
-
- IF in_status IS NOT NULL THEN
-
- if v_status == 't'::bool THEN
- where_stmt = where_stmt || $s$ approved = 't'::bool AND $s$;
- ELSIF v_status == 'f'::bool THEN
- where_stmt = where_stmt || $s$ approved = 'f'::bool AND $s$;
- END IF;
-
- END IF;
-
- FOR row in EXECUTE statement LOOP
- RETURN NEXT row;
- END LOOP;
- ELSE
-
- FOR row IN SELECT * FROM reports LOOP
- RETURN NEXT row;
- END LOOP;
-
- END IF;
- END;
-$$ language 'plpgsql';
-
+-- DECLARE
+-- row reports;
+---- statement text;
+-- where_stmt text;
+-- v_status BOOLEAN;
+-- v_accum NUMERIC;
+-- BEGIN
+--
+-- if in_status = "pending" then
+-- v_status = 'f'::bool;
+-- ELSIF in_status = "approved" THEN
+--
+-- v_status = 't'::bool;
+-- END IF;
+--
+-- IF in_date_begin IS NOT NULL
+-- or in_date_end IS NOT NULL
+-- or in_account IS NOT NULL
+-- or v_status IS NOT NULL
+-- THEN
+-- statement = 'select pr.* from reports pr ';
+---- statement = statement || $s$join acc_trans at on pr.ledger_id = at.entry_id $s$;
+--
+-- IF in_account IS NOT NULL THEN
+--
+-- statement = statement || $s$join chart c on at.chart_id = c.id $s$;
+-- where_stmt = $s$c.accno =~ $s$ || quote_literal(in_account) || $s$ AND $s$;
+-- END IF;
+--
+-- IF in_date_begin IS NOT NULL THEN
+-- where_stmt = where_stmt || $s$insert_time >= $s$ || quote_literal(in_date_begin) || $s$ AND $s$;
+-- END IF;
+--
+-- IF in_date_end IS NOT NULL THEN
+-- where_stmt = where_stmt || $s$insert_time <= $s$ || quote_literal(in_date_end) || $s$ AND $s$;
+-- END IF;
+--
+-- IF in_status IS NOT NULL THEN
+--
+-- if v_status == 't'::bool THEN
+---- where_stmt = where_stmt || $s$ approved = 't'::bool AND $s$;
+-- ELSIF v_status == 'f'::bool THEN
+-- where_stmt = where_stmt || $s$ approved = 'f'::bool AND $s$;
+-- END IF;
+--
+-- END IF;
+--
+-- FOR row in EXECUTE statement LOOP
+-- RETURN NEXT row;
+-- END LOOP;
+-- ELSE
+--
+-- FOR row IN SELECT * FROM reports LOOP
+-- RETURN NEXT row;
+-- END LOOP;
+--
+-- END IF;
+-- END;
+--$$ language 'plpgsql';
+--
create type recon_accounts as (
name text,
accno text,
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.