[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2448] trunk
- Subject: SF.net SVN: ledger-smb:[2448] trunk
- From: ..hidden..
- Date: Sat, 07 Feb 2009 01:05:21 +0000
Revision: 2448
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2448&view=rev
Author: einhverfr
Date: 2009-02-07 01:05:21 +0000 (Sat, 07 Feb 2009)
Log Message:
-----------
Reconciliation review of reports and approval is now working.
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/UI/reconciliation/report.html
trunk/UI/reconciliation/search.html
trunk/scripts/recon.pl
trunk/scripts/report.pl
trunk/sql/modules/Reconciliaton.sql
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-07 01:05:21 UTC (rev 2448)
@@ -99,6 +99,23 @@
$self->exec_method(funcname=>'reconciliation__pending_transactions');
}
+sub submit {
+ my $self = shift @_;
+ $i = 1;
+ my $ids = ();
+ $self->{line_ids} = '{';
+ while (my $id = $self->{"id_$i"}){
+ if ($self->{"cleared_$id"}){
+ push @$ids, $id;
+ $self->{line_ids} =~ s/$/$id,/;
+ }
+ ++ $i;
+ }
+ $self->{line_ids} =~ s/,$/}/;
+ $self->exec_method(funcname=>'reconciliation__submit_set');
+ $self->{dbh}->commit;
+}
+
sub import_file {
my $self = shift @_;
@@ -235,7 +252,6 @@
my $type = shift;
return $self->exec_method(
funcname=>'reconciliation__search',
- args=>[$self->{date_begin}, $self->{date_end}, $self->{account}, $self->{status}]
);
}
@@ -269,8 +285,9 @@
);
$our_balance = $ref->{reconciliation__get_cleared_balance};
+ $self->{beginning_balance} = $our_balance;
for my $line (@{$self->{report_lines}}){
- $our_balance += $line->{our_balance}
+ $our_balance += $line->{our_balance} if $self->{"cleared_$line->{id}"};
}
$self->{our_total} = $our_balance;
$self->{format_amount} = sub { return $self->format_amount(@_); }
Modified: trunk/UI/reconciliation/report.html
===================================================================
--- trunk/UI/reconciliation/report.html 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/UI/reconciliation/report.html 2009-02-07 01:05:21 UTC (rev 2448)
@@ -1,11 +1,20 @@
<?lsmb PROCESS 'ui-header.html' ?>
<?lsmb PROCESS 'elements.html' ?>
+<form action="recon.pl" method="post">
<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('Through date:') ?><?lsmb end_date ?></div>
+<div><?lsmb text('Statement Balance:') ?><?lsmb INCLUDE input element_data = {
+ name = "their_total",
+ type = "text",
+ class = "money",
+ size = "15",
+ value = their_total
+ } ?>
+</div>
+<div><?lsmb text('Beginning Balance:') ?><?lsmb beginning_balance ?></div>
<div><?lsmb text('Ledger Balance:') ?><?lsmb our_total ?></div>
<center>Report generated by <?lsmb user ?></center>
@@ -15,8 +24,7 @@
<?lsmb error ?>
</div>
<?lsmb END ?>
-<?lsmb line = 0 ?>
-<form action="recon.pl" method="post">
+<?lsmb i = 0 ?>
<table border=0>
<tr class="listheading">
<th><?lsmb text('Cleared') ?></th>
@@ -32,7 +40,7 @@
<?lsmb INCLUDE input element_data = {
type = "hidden",
name = "id_$i",
- value = "row.id"
+ value = row.id
} ?>
<?lsmb IF row.errorcode != 0 ?>
<tr style="background-color:pink;">
@@ -41,16 +49,17 @@
<?lsmb ELSE ?>
<tr>
<?lsmb END ?>
- <?lsmb IF (row.our_balance == row.their_balance)
+ <?lsmb IF (row.our_balance == row.their_balance) or
+ row.cleared or ${"cleared_$row.id"}
-?>
- <?lsmb row.approved = 1 ?>
- <?lsmb ELSE ?> <?lsmb row.approved = undef -?>
+ <?lsmb row.cleared = 'checked' ?>
+ <?lsmb ELSE ?> <?lsmb row.cleared = undef -?>
<?lsmb END -?>
<td><?lsmb INCLUDE input element_data = {
type = "checkbox"
- name = "approved_$row.id"
+ name = "cleared_$row.id"
value = row.id
- checked = row.approved
+ checked = row.cleared
}
?></td>
<td><?lsmb row.transaction_type ?> </td>
@@ -59,7 +68,8 @@
<td><?lsmb row.our_balance ?></td>
<td><?lsmb row.their_balance ?></td>
</tr>
- <?lsmb END ?>
+ <?lsmb i = i + 1 -?>
+ <?lsmb END -?>
</table>
<?lsmb INCLUDE input element_data = {
name = "report_id",
@@ -71,11 +81,21 @@
text = text('Update'),
value = 'update_recon_set',
class = "submit"
-} ?>
+} ?><?lsmb IF NOT submitted ?>
<?lsmb INCLUDE button element_data = {
name = "action",
text = text('Submit'),
value = 'submit_recon_set',
class = "submit"
} ?>
+<?lsmb END ?>
+<?lsmb IF submitted AND NOT approved AND can_approve ?>
+<?lsmb INCLUDE button element_data = {
+ name = "action",
+ text = text('Approve'),
+ value = 'Approve',
+ class = "submit"
+} ?>
+<?lsmb END ?>
+
</body></html>
Modified: trunk/UI/reconciliation/search.html
===================================================================
--- trunk/UI/reconciliation/search.html 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/UI/reconciliation/search.html 2009-02-07 01:05:21 UTC (rev 2448)
@@ -5,21 +5,70 @@
<div class="body">
<form name="reconciliation__search" method="post" action="recon.pl" id="reconciliation__search">
- <input type="hidden" name="action" value="<?lsmb mode?>_search">
<div>
- Date:<br>
- <input type="input" size="15" name="date_begin" alt="<?lsmb date_format ?>"> to <input type="input" size="15" name="date_end" alt="<?lsmb date_format ?>"><br>
+ <?lsmb INCLUDE input element_data = {
+ label = text('Date From'), #'
+ type = 'text',
+ class = 'date',
+ size = 12,
+ value = date_from,
+ name = 'date_from'
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ label = text('To:'),
+ type = 'text',
+ class = 'date',
+ size = 12,
+ value = date_to,
+ name = 'date_to'
+ } ?>
</div>
<div>
- Account:<br>
- <select name="account">
- <?lsmb FOR account IN accounts?>
- <option value="<?lsmb account.id?>"><?lsmb account.name?></option>
- <?lsmb END?>
- </select>
+ <?lsmb INCLUDE input element_data = {
+ label = text('Amount From'), #'
+ type = 'text',
+ class = 'money',
+ size = 12,
+ value = amount_from,
+ name = 'amount_from'
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ label = text('To:'),
+ type = 'text',
+ class = 'money',
+ size = 12,
+ value = amount_to,
+ name = 'amount_to'
+ } ?>
</div>
<div>
- <input type="submit" value="Search!">
+ <div>
+ <?lsmb INCLUDE select element_data = {
+ label = text('Account:'),
+ class = "coa-list",
+ options = account_list,
+ text_attr = 'account',
+ value_attr = 'id'
+ } ?>
</div>
+ <div>
+ <?lsmb INCLUDE input element_data = {
+ type = "hidden",
+ name = "approved",
+ value = approved
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ type = "hidden",
+ name = "submitted",
+ value = submitted
+ } ?>
+ </div>
+ <?lsmb INCLUDE button element_data = {
+ type = "submit",
+ name = "action",
+ text = text('Search'),
+ value = 'get_results'
+ } ?>
</form>
-</div>
\ No newline at end of file
+
+</div>
Modified: trunk/scripts/recon.pl
===================================================================
--- trunk/scripts/recon.pl 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/scripts/recon.pl 2009-02-07 01:05:21 UTC (rev 2448)
@@ -78,52 +78,84 @@
_display_report($recon);
}
-sub pending_search {
+sub submit_recon_set {
+ my ($request) = shift;
+ my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request);
+ $recon->submit();
+ my $template = LedgerSMB::Template->new(
+ user => $user,
+ template => 'reconciliation/submitted',
+ language => $user->{language},
+ format => 'HTML',
+ path=>"UI");
+ return $template->render($recon);
- &search(shift @_,"pending");
}
-
-sub approved_search {
-
- &search(shift @_,"approved");
-}
-
-sub search {
- my ($request,$type) = @_;
-
- if ($request->type() eq "POST") {
- # WE HAS DATUMS
- # INTENTIONAL BAD PLURALIZATION OF LATIN
-
+sub get_results {
+ my ($request) = @_;
my $search = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all');
- my $results = $search->search($type);
- my $total = $search->total();
-
-
+ my @results = $search->search();
+ my @accounts = $search->get_accounts();
+ my $act_hash = {};
+ for my $act (@accounts){
+ $act_hash->{"$act->{id}"} = $act->{account};
+ }
+ for my $row (@results){
+ $row->{account} = $act_hash->{"$row->{chart_id}"};
+ }
+ my $base_url = "recon.pl?action=update_recon_set";
+ $columns = {
+ account => $request->{_locale}->text('Account'),
+ their_total => $request->{_locale}->text('Balance'),
+ end_date => $request->{_locale}->text('Statement Date'),
+ submitted => $request->{_locale}->text('Submitted'),
+ approved => $request->{_locale}->text('Approved'),
+ };
+ my $cols = [];
+ @$cols = qw(account end_date their_total approved submitted);
+ my $recon =$search;
+ for my $row(@results){
+ $row->{their_total} = $recon->format_amount(
+ {amount => $row->{their_total}, money => 1});
+ $row->{end_date} = {
+ text => $row->{end_date},
+ href => "$base_url&report_id=$row->{id}"
+ };
+ }
+ $recon->{_results} = ..hidden..;
+ $recon->debug({file => '/tmp/recon'});
+ $recon->{title} = $request->{_locale}->text('Reconciliation Sets');
my $template = LedgerSMB::Template->new(
user => $user,
- template => 'reconciliation/report',
+ template => 'form-dynatable',
language => $user->{language},
format => 'HTML',
path=>"UI");
- return $template->render({report => $results, total => $total});
+ return $template->render({
+ form => $recon,
+ heading => $columns,
+ hiddens => $recon,
+ columns => $cols,
+ rows => ..hidden..
+ });
+}
+sub search {
+ my ($request,$type) = @_;
+
+
- } else {
my $recon = LedgerSMB::DBObject::Reconciliation->new(base=>$request, copy=>'all');
-
+ @{$recon->{account_list}} = $recon->get_accounts();
my $template = LedgerSMB::Template->new(
user => $user,
- template=>'reconciliation/search',
+ template=>'search',
language=>$user->{language},
format=>'HTML',
- path=>"UI",
- mode=>$type,
- accounts=>$recon->get_accounts()
+ path=>"UI/reconciliation",
);
return $template->render();
- }
}
=pod
@@ -233,8 +265,9 @@
=cut
sub _display_report {
- my $recon = shift;
+ my $recon = shift;
$recon->get();
+ $recon->{can_approve} = $recon->is_allowed_role('recon_supervisor');
$template = LedgerSMB::Template->new(
user=> $user,
template => 'reconciliation/report',
@@ -243,13 +276,15 @@
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}});
+ $l->{their_balance} = $recon->format_amount({amount => $l->{their_balance}, money => 1});
+ $l->{our_balance} = $recon->format_amount({amount => $l->{our_balance}, money => 1});
}
$recon->{their_total} = $recon->format_amount(
- {amount => $recon->{their_total}});
+ {amount => $recon->{their_total}, money => 1});
$recon->{our_total} = $recon->format_amount(
- {amount => $recon->{our_total}});
+ {amount => $recon->{our_total}, money => 1});
+ $recon->{beginning_balance} = $recon->format_amount(
+ {amount => $recon->{beginning_balance}, money => 1});
return $template->render($recon);
}
Modified: trunk/scripts/report.pl
===================================================================
--- trunk/scripts/report.pl 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/scripts/report.pl 2009-02-07 01:05:21 UTC (rev 2448)
@@ -39,4 +39,5 @@
$template->render($report);
}
+
1;
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2009-02-05 22:50:57 UTC (rev 2447)
+++ trunk/sql/modules/Reconciliaton.sql 2009-02-07 01:05:21 UTC (rev 2448)
@@ -7,6 +7,12 @@
end_date date not null default now()
);
+create table cr_approval (
+ report_id bigint references cr_report(id) primary key,
+ approved_by int references entity(id) not null,
+ approved_at timestamptz default now() not null
+);
+
CREATE TABLE cr_report_line (
id bigserial primary key not null,
report_id int NOT NULL references cr_report(id),
@@ -21,6 +27,7 @@
ledger_id int REFERENCES acc_trans(entry_id),
voucher_id int REFERENCES voucher(id),
overlook boolean not null default 'f',
+ cleared boolean not null default 'f',
check (ledger_id is not null or voucher_id is not null)
);
@@ -29,8 +36,19 @@
account text not null
);
+CREATE OR REPLACE FUNCTION reconciliation__submit_set(
+ in_report_id int, in_line_ids int[]) RETURNS bool AS
+$$
+BEGIN
+ UPDATE cr_report set submitted = true where id = in_report_id;
+ UPDATE cr_report_line SET cleared = true
+ WHERE report_id = in_report_id AND id = ANY(in_line_ids);
+ RETURN FOUND;
+END;
+$$ LANGUAGE PLPGSQL;
+
CREATE OR REPLACE FUNCTION reconciliation__get_cleared_balance(in_chart_id int)
RETURNS numeric AS
$$
@@ -57,24 +75,7 @@
in_user TEXT;
BEGIN
in_user := current_user;
- select * into current_row from cr_report_line
- where report_id = in_report_id;
-
- IF NOT FOUND THEN
- RAISE EXCEPTION 'Fatal Error: Pending report % not found', in_report_id;
- END IF;
- IF current_row.user = in_user THEN
- RAISE EXCEPTION 'Fatal Error: User % cannot self-approve report!', in_user;
- END IF;
-
- SELECT INTO total_errors count(*) from cr_report_line
- where report_id = in_report_id and errorcode <> 0;
-
- IF total_errors <> 0 THEN
- RAISE EXCEPTION 'Fatal Error: Cannot approve while % uncorrected errors remain.', total_errors;
- END IF;
-
-- so far, so good. Different user, and no errors remain. Therefore,
-- we can move it to completed reports.
--
@@ -83,8 +84,10 @@
-- filed it. This may require clunkier syntax..
--
-
- update cr_report set approved = 't', clear_time = now()
+ insert into cr_report_approval (report_id, approved_by)
+ values (in_report_id,
+ (select entity_id from users where username = SESSION_USER));
+ update cr_report set approved = 't'
where id = in_report_id;
return 1;
@@ -296,73 +299,33 @@
$$ language 'plpgsql';
---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 $$
+CREATE OR REPLACE FUNCTION reconciliation__search
+(in_date_from date, in_date_to date,
+ in_balance_from numeric, in_balance_to numeric,
+ in_chart_id int, in_submitted bool, in_approved bool)
+returns setof cr_report AS
+$$
+DECLARE report cr_report;
+BEGIN
+ FOR report IN
+ SELECT * FROM cr_report
+ WHERE
+ (in_date_from IS NULL OR in_date_from <= end_date) and
+ (in_date_to IS NULL OR in_date_to >= end_date) AND
+ (in_balance_from IS NULL
+ or in_balance_from <= their_total ) AND
+ (in_balance_to IS NULL
+ OR in_balance_to >= their_total) AND
+ (in_chart_id IS NULL OR in_chart_id = chart_id) AND
+ (in_submitted IS NULL or in_submitted = submitted) AND
+ (in_approved IS NULL OR in_approved = approved)
+ ORDER BY end_date, their_total
+ LOOP
+ RETURN NEXT report;
+ END LOOP;
+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.