[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2439] trunk
- Subject: SF.net SVN: ledger-smb:[2439] trunk
- From: ..hidden..
- Date: Mon, 02 Feb 2009 01:34:11 +0000
Revision: 2439
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2439&view=rev
Author: einhverfr
Date: 2009-02-02 01:34:10 +0000 (Mon, 02 Feb 2009)
Log Message:
-----------
Reconciliation overhaul. Needs a few minor things before it works properly, but is mostly usable now.
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/LedgerSMB/Reconciliation/CSV.pm
trunk/Makefile.PL
trunk/UI/Contact/contact.css
trunk/UI/Contact/contact.html
trunk/UI/reconciliation/report.html
trunk/UI/reconciliation/upload.html
trunk/scripts/recon.pl
trunk/sql/modules/Reconciliaton.sql
Added Paths:
-----------
trunk/LedgerSMB/Reconciliation/CSV/
trunk/LedgerSMB/Reconciliation/CSV/Formats/
trunk/recon.pl
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2009-02-02 01:34:10 UTC (rev 2439)
@@ -145,15 +145,10 @@
# gives us a report ID to insert with.
my $report_id = $self->exec_method(funcname=>'reconciliation__new_report_id');
+ $self->{report_id} = $report_id;
# 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.
- unshift @{$entries}, {
- scn => -1,
- balance=> $total,
- old_balance=> $self->exec_method(funcname=>'reconciliation__current_balance'),
- date=>$month
- };
for my $entry ( @{$entries} ) {
# Codes:
@@ -171,17 +166,18 @@
args=>[
$report_id,
$entry->{scn},
- $entry->{amount}, # needs leading 0's trimmed.
- $entry->{account},
+ $entry->{chart_id},
$self->{user},
- $self->{date}
+ $self->{date},
+ $entry->{amount}, # needs leading 0's trimmed.
]
);
$entry{report_id} = $report_id;
}
+
+ $self->exec_method(funcname=>'reconciliation__pending_transactions');
+ $self->{dbh}->commit;
- $self->exec_method(funcname=>'reconciliation__pending_transactions', args=>[$report_id, $date]);
-
return ($report_id, $entries); # returns the report ID.
}
@@ -263,4 +259,4 @@
funcname=>'reconciliation__account_list',
);
}
-1;
\ No newline at end of file
+1;
Modified: trunk/LedgerSMB/Reconciliation/CSV.pm
===================================================================
--- trunk/LedgerSMB/Reconciliation/CSV.pm 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/LedgerSMB/Reconciliation/CSV.pm 2009-02-02 01:34:10 UTC (rev 2439)
@@ -6,6 +6,11 @@
use base qw/LedgerSMB/;
use DateTime;
+opendir (DCSV, 'LedgerSMB/Reconciliation/CSV/Formats');
+for my $format (readdir(DCSV)){
+ do "LedgerSMB/Reconciliation/CSV/Formats/$format";
+};
+
sub load_file {
my $self = shift @_;
@@ -22,48 +27,23 @@
sub process {
- # thoroughly implementation-dependent.
+ # thoroughly implementation-dependent, so depends on helper-functions
my $self = shift @_;
my $contents = $self->load_file($self->{csv_filename});
-
- foreach my $line (split /\n/,$contents) {
- # Unpack for the format it is inexplicably in
- ($accno,
- $checkno,
- $issuedate,
- $amount,
- $cleared,
- $last_three) = unpack("A10A10A6A10A6A3",$line);
-
- push @{ $self->{entries} }, {
- account_num => $accno,
- scn => $checkno,
- issue_date => $issuedate,
- amount => $amount,
- cleared_date => $cleared
- };
- }
- # Okay, now how do I test to see if this is actually, y'know, bad data.
-
- for my $line (@{ $self->{entries} }) {
-
- # First check the account number.
- # According to the docs I have, it's all numbers.
-
- ;
- }
-
- return;
+ my $func = "process_$self->{accno}";
+ @entries = eval{&$func($self, $contents)};
+ if (!$!){
+ @{$self->{recon_entries}} = @entries;
+ $self->{file_upload} = 1;
+ }
+ else {
+ $self->{file_upload} = 0;
+ }
}
sub is_error {
-
-
+ my $self = shift @_;
+ return $self->{invalid_format};
}
-sub error {
-
-
-}
-
1;
Modified: trunk/Makefile.PL
===================================================================
--- trunk/Makefile.PL 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/Makefile.PL 2009-02-02 01:34:10 UTC (rev 2439)
@@ -12,6 +12,7 @@
requires 'Data::Dumper';
requires 'Locale::Maketext';
+requires 'DateTime';
requires 'Locale::Maketext::Lexicon' => '0.56';
requires 'DBI' => '1.00';
requires 'MIME::Base64';
Modified: trunk/UI/Contact/contact.css
===================================================================
--- trunk/UI/Contact/contact.css 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/UI/Contact/contact.css 2009-02-02 01:34:10 UTC (rev 2439)
@@ -31,3 +31,29 @@
div label {
margin-left: 1em;
}
+
+/* This shouldnot go in SVN trunk */
+#company_div .note_contents {
+ display: none;
+}
+
+table {
+ width: 100%;
+}
+
+#credit-row {
+ display: none;
+}
+
+#discount-gifi-row {
+ display: none;
+}
+
+#line-two {
+ display: none;
+}
+
+#line-three {
+ display: none;
+}
+
Modified: trunk/UI/Contact/contact.html
===================================================================
--- trunk/UI/Contact/contact.html 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/UI/Contact/contact.html 2009-02-02 01:34:10 UTC (rev 2439)
@@ -234,6 +234,9 @@
</div>
<?lsmb END ?>
</div>
+<?lsmb IF entity_id;
+INCLUDE 'rates_div.html';
+END -?>
<?lsmb IF entity_id ?>
<div id="credit_div">
<div class="listtop"><strong>Accounts</strong></div>
@@ -545,6 +548,24 @@
type = "text",
size = "20"
} ?>
+ <!--
+ if the following button and script show up in svn
+ /trunk, please remove them. -CT -->
+ <script type="text/javascript">
+ function show_lines(){
+ var e;
+ e = document.getElementById('line-two');
+ e.style.display = 'inline';
+ e = document.getElementById('line-three');
+ e.style.display = 'inline';
+ e = document.getElementById('show-lines-button');
+ e.style.display = 'none';
+ return false;
+ }
+ </script>
+ <button onclick="javascript: return show_lines()"
+ id="show-lines-button" type="button"><?lsmb text('Show More Lines') ?>
+ </button> <!-- end custom section -->
</div>
<div>
<?lsmb PROCESS input element_data = {
Modified: trunk/UI/reconciliation/report.html
===================================================================
--- trunk/UI/reconciliation/report.html 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/UI/reconciliation/report.html 2009-02-02 01:34:10 UTC (rev 2439)
@@ -1,22 +1,22 @@
<center>Reconciliation Report for <?lsmb total.account ?> for the month of <?lsmbtotal.month?></center>
<center>
- <?lsmbIF total.errorcode != 0 ?>
+ <?lsmb IF total.errorcode != 0 ?>
<div style="color:blue; border-style:solid; border-width:1px; border-color: blue;">
- <?lsmbELSE?>
+ <?lsmb ELSE ?>
<div style="color:red; border-style:solid; border-width:1px; border-color: blue;">
- <?lsmbEND?>
+ <?lsmb END ?>
Our Balance: total.our_balance | Bank Balance: total.their_balance
</div>
</center>
<center>Report generated by <?lsmb total.user ?></center>
-<?lsmb if recon.error ?>
+<?lsmb IF recon.error ?>
<div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" >
<?lsmb recon.error ?>
</div>
-<?lsmbend?>
+<?lsmb END ?>
<table border=0>
@@ -31,13 +31,13 @@
</tr>
<?lsmb FOREACH row = records ?>
- <?lsmbIF row.errorcode != 0 ?>
+ <?lsmb IF row.errorcode != 0 ?>
<tr style="background-color:red;">
- <?lsmb ELSIF row.id = corrected ?>
+ <?lsmb ELSIF row.id == corrected ?>
<tr style="background-color:yellow;">
- <?lsmbELSE?>
+ <?lsmb ELSE ?>
<tr>
- <?lsmbEND?>
+ <?lsmb END ?>
<td><?lsmb row.clear_time ?></td>
<td><?lsmb row.transaction_type ?> </td>
<td><?lsmb row.our_balance ?></td>
@@ -45,16 +45,16 @@
<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>
- <?lsmbELSE?>
+ <?lsmb ELSE ?>
<td>0</td>
- <?lsmbEND?>
+ <?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 if not recon.error?>
+<?lsmb IF NOT recon.error?>
<form name="approval" method="POST" action="/reconciliation.pl">
<input type="submit" name="action" value="Approve" />
</form>
-<?lsmb end?>
\ No newline at end of file
+<?lsmb END ?>
Modified: trunk/UI/reconciliation/upload.html
===================================================================
--- trunk/UI/reconciliation/upload.html 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/UI/reconciliation/upload.html 2009-02-02 01:34:10 UTC (rev 2439)
@@ -10,11 +10,35 @@
Please check your CSV file and try again.
</div>
<?lsmb END?>
- <form name="csv_upload" method="POST" action="/reconciliation.pl">
+ <form name="csv_upload" method="POST" action="recon.pl">
- <input type="hidden" name="action" value="new_report" />
+ <div id="csv-file">
<label for="file_upload">CSV File:</label>
- <input type="file" name="csv_file" id="file_upload" />
- <input type="submit" action="submit" value="Create New Report">
+ <input type="file" name="csv_file" id="file_upload" /></div>
+ <div id="acc-date-row">
+ <?lsmb INCLUDE select element_data = {
+ name = "chart_id",
+ options = accounts,
+ text_attr = "name",
+ value_attr = "id",
+ label = "Account"
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ name = "end_date",
+ label = text('To Date'), #'
+ class = "date",
+ type = 'text',
+ size = 14
+ } ?>
+ </div>
+ <div id="button-row">
+ <?lsmb INCLUDE button element_data = {
+ type = "submit",
+ name = "action",
+ value = "new_report",
+ class = "submit",
+ text = text('Create New Report'), #'
+ } ?>
+ </div>
</form>
-</div>
\ No newline at end of file
+</div>
Copied: trunk/recon.pl (from rev 2438, trunk/customer.pl)
===================================================================
--- trunk/recon.pl (rev 0)
+++ trunk/recon.pl 2009-02-02 01:34:10 UTC (rev 2439)
@@ -0,0 +1,3 @@
+#!/usr/bin/perl
+
+require 'lsmb-request.pl';
Modified: trunk/scripts/recon.pl
===================================================================
--- trunk/scripts/recon.pl 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/scripts/recon.pl 2009-02-02 01:34:10 UTC (rev 2439)
@@ -235,19 +235,20 @@
my $template;
my $return;
-
+ my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all');
if ($request->type() eq "POST") {
# We can assume that we're doing something useful with new data.
# We can also assume that we've got a file.
- my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all');
# $self is expected to have both the file handling logic, as well as
# the logic to load the processing module.
# Why isn't this testing for errors?
my ($report_id, $entries) = $recon->new_report($recon->import_file());
- if ($recon->is_error()) {
+ $recon->{dbh}->commit;
+ if ($recon->{error}) {
+ $recon->{error};
$template = LedgerSMB::Template->new(
user=>$user,
@@ -256,26 +257,22 @@
format=>'HTML',
path=>"UI"
);
- return $template->render({error=>$recon->error()});
+ return $template->render($recon);
}
$template = LedgerSMB::Template->new(
user=> $user,
- template => 'reconciliation/new_report',
+ template => 'reconciliation/report',
language => $user->{language},
format=>'HTML',
path=>"UI"
);
- return $template->render(
- {
- entries=>$entries,
- report_id=>$report_id
- }
- );
+ return $template->render($recon);
}
else {
# we can assume we're to generate the "Make a happy new report!" page.
+ @{$recon->{accounts}} = $recon->get_accounts;
$template = LedgerSMB::Template->new(
user => $user,
template => 'reconciliation/upload',
@@ -283,7 +280,7 @@
format => 'HTML',
path=>"UI"
);
- return $template->render();
+ return $template->render($recon);
}
return undef;
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2009-01-06 17:51:10 UTC (rev 2438)
+++ trunk/sql/modules/Reconciliaton.sql 2009-02-02 01:34:10 UTC (rev 2439)
@@ -1,31 +1,39 @@
-CREATE TABLE reports (
+CREATE TABLE cr_report (
id bigserial primary key not null,
+ chart_id int not null references chart(id),
+ our_total numeric default 0,
+ 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,
- account text not null,
scn text not null, -- SCN is the check #
their_balance numeric,
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
+ "user" int references entity(id) not null, -- why ois this not an entity reference?
+ corrections INT NOT NULL DEFAULT 0,
clear_time TIMESTAMP NOT NULL,
insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
ledger_id int REFERENCES acc_trans(entry_id),
+ voucher_id int REFERENCES voucher(id),
overlook boolean not null default 'f',
- approved boolean not null default 'f'
+ check (ledger_id is not null or voucher_id is not null)
);
-CREATE TABLE coa_to_account (
+CREATE TABLE cr_coa_to_account (
chart_id int not null references chart(id),
- account text not null CHECK (account ~ '[0-9]{7}(xxx)')
+ account text not null
);
-CREATE TABLE report_corrections (
+CREATE TABLE cr_report_corrections (
id serial primary key not null,
correction_id int not null default 1,
- entry references reports(id) not null,
- user references entity(id) not null, -- why is this not an entity reference?
+ "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()
);
@@ -36,14 +44,14 @@
DECLARE
new_code INT;
current_row RECORD;
- l_row acc_trans;
+ l_row RECORD;
in_user TEXT;
full_reason TEXT;
BEGIN
- select into in_user from current_user;
+ in_user := current_user;
- select into current_row from reports where reports.id = in_report_id and reports.id = in_id;
- select into l_row from acc_trans where entry_id = current_row.lid;
+ 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;
@@ -52,25 +60,25 @@
IF user <> current_row.user THEN
IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
- update reports pr
- set pr.corrections = reports.corrections + 1,
- pr.new_balance = in_new_amount,
- error_code = 0
+ 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 select reconciliation__update_ledger(current_row.lid, in_new_amount, full_reason)
- ELSE IF current_row.our_balance = in_new_amount THEN
+ 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.error_code;
+ return current_row.errorcode;
END IF;
END IF;
- return current_row.error_code;
+ return current_row.errorcode;
END;
$$ language 'plpgsql';
@@ -83,8 +91,11 @@
current_row RECORD;
in_user TEXT;
BEGIN
- select into in_user from current_user;
- select into current_row from reports where reports.id = in_id and reports.report_id = in_report_id;
+ 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;
@@ -92,46 +103,49 @@
IF user <> current_row.user THEN
IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
- update reports pr
- set pr.corrections = reports.corrections + 1,
- pr.new_balance = in_new_amount,
- error_code = 0
+ 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;
- ELSE IF current_row.their_balance = in_new_amount THEN
+ 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.error_code;
+ return current_row.errorcode;
END IF;
END IF;
- return current_row.error_code;
+ return current_row.errorcode;
END;
$$ language 'plpgsql';
-CREATE OR REPLACE reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
+CREATE OR REPLACE function reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
DECLARE
in_user TEXT;
- pending_entry reports;
+ pending_entry cr_report_line;
BEGIN
- select into in_user from current_user;
+ in_user := current_user;
- select into pending_entry from reports where report_id = in_report_id and id = in_id;
+ 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.";
- ELSE IF pending_entry.errorcode <> 4 THEN
+ 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.";
+ RAISE EXCEPTION 'Selected entry not permitted to be passed through.';
ELSE
-- Then we mark it passthroughable, and "approve" will overlook it.
- update reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
+ update cr_report_line set overlook = 't', errorcode = 0
+ where report_id = in_report_id and id = in_id;
+
return 0;
END IF;
END;
@@ -142,22 +156,24 @@
DECLARE
in_user TEXT;
- pending_entry reports;
+ 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;
- ELSE IF pending_entry.errorcode <> 2 THEN
+ 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.";
+ 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.
- select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date, 'Bank charge');
+ PERFORM create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time, 'Bank charge');
- update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
+ update cr_report_line set errorcode = 0
+ where report_id = in_report_id and id = in_id;
+
return 0;
END IF;
END;
@@ -168,16 +184,17 @@
DECLARE
in_user TEXT;
- pending_entry reports;
+ 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;
- ELSE IF pending_entry.errorcode <> 3 THEN
+ 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.";
+ RAISE EXCEPTION 'Not an unaccounted charge; cannot be retroactively added to the ledger.';
ELSE
-- Then we mark it passthroughable, and "approve" will overlook it.
@@ -185,9 +202,11 @@
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_date,note);
+ select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time,note);
- update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
+ update cr_report_line set errorcode = 0
+ where report_id = in_report_id and id = in_id;
+
return in_id;
END IF;
END;
@@ -196,35 +215,38 @@
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;
- -- moves the approval to "reports", I guess, or some other "final" table.
+ -- moves the approval to "cr_report_line", I guess, or some other "final" table.
--
-- Pending may just be a single flag in the database to mark that it is
-- not finalized. Will need to discuss with Chris.
DECLARE
current_row RECORD;
- completed reports;
+ completed cr_report_line;
total_errors INT;
in_user TEXT;
BEGIN
-
- select into in_user current_user;
- select into current_row distinct on user * from reports where report_id = in_report_id;
+ 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;
+ 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;
+ RAISE EXCEPTION 'Fatal Error: User % cannot self-approve report!', in_user;
END IF;
- SELECT INTO total_errors count(*) from reports where report_id = in_report_id and error_code <> 0;
+ 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;
+ 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.
+ -- so far, so good. Different user, and no errors remain. Therefore,
+ -- we can move it to completed reports.
--
-- User may not be necessary - I would think it better to use the
-- in_user, to note who approved the report, than the user who
@@ -232,36 +254,42 @@
--
- update reports set approved = 't', clear_time = now() where report_id = in_report_id;
+ update cr_report set approved = 't', clear_time = now()
+ where id = in_report_id;
return 1;
END;
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION reconciliation__new_report_id () returns INT as $$
+CREATE OR REPLACE FUNCTION reconciliation__new_report_id (in_chart_id int,
- SELECT nextval('pending_report_report_id_seq');
+in_total numeric, in_end_date date) returns INT as $$
+ INSERT INTO cr_report(chart_id, our_total, end_date) values ($1, $2, $3);
+ SELECT currval('cr_report_id_seq')::int;
+
$$ language 'sql';
create or replace function reconciliation__add_entry(
in_report_id INT,
- in_scn INT,
- in_amount numeric,
- in_account INT,
+ in_scn TEXT,
+ in_chart_id int,
in_user TEXT,
- in_date TIMESTAMP
+ in_date TIMESTAMP,
+ in_amount numeric
) RETURNS INT AS $$
- DELCARE
+ DECLARE
+ in_account int;
la RECORD;
- errorcode INT;
+ t_errorcode INT;
our_value NUMERIC;
lid INT;
BEGIN
+ in_account := in_chart_id;
- SELECT INTO la FROM acc_trans gl
+ 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
@@ -279,38 +307,38 @@
-- It's a bank charge. Approval action will probably be
-- adding it as an entry to the general ledger.
- errorcode := 2;
+ 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
- errorcode := 3;
+ t_errorcode := 3;
our_value := 0;
END IF;
- ELSE if la.amount <> in_amount THEN
+ ELSif la.amount <> in_amount THEN
- errorcode := 1;
+ t_errorcode := 1;
our_value := la.amount;
lid := la.entry_id;
ELSE
-- it reconciles. No problem.
- errorcode := 0;
+ t_errorcode := 0;
our_value := la.amount;
lid := la.entry_id;
END IF;
- INSERT INTO reports (
+ INSERT INTO cr_report_line (
report_id,
scn,
their_balance,
our_balance,
- error_code,
- user,
+ errorcode,
+ "user",
clear_time,
ledger_id
)
@@ -318,9 +346,9 @@
in_report_id,
in_scn,
in_amount,
- gl.amount,
- errorcode,
- in_user,
+ la.amount,
+ t_errorcode,
+ (select id from users where username = in_user),
in_date,
lid
);
@@ -333,28 +361,44 @@
END;
$$ language 'plpgsql';
-create or replace function reconciliation__pending_transactions (in_month DATE) RETURNS setof acc_trans as $$
+-- 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 acc_trans;
+ gl_row RECORD;
BEGIN
- FOR gl_row IN
- select gl.* from acc_trans gl, reports pr
- where gl.cleared = 'f'
- and date_trunc('month',gl.transdate) <= date_trunc('month', in_month)
- and gl.entry_id <> pr.ledger_id -- there's no entries in the reports for this
- LOOP
- RETURN NEXT gl_row;
- END LOOP;
+ INSERT INTO cr_report_line (report_id, scn, their_balance,
+ our_balance, "user", voucher_id, ledger_id)
+ 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)
+ FROM acc_trans ac
+ JOIN transactions t on (ac.trans_id = t.id)
+ JOIN (select id, entity_credit_account, 'ar' as table FROM ar
+ UNION
+ select id, entity_credit_account, 'ap' as table FROM ap
+ 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;
+ RETURN in_report_id;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof reports as $$
+CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof cr_report as $$
DECLARE
- row reports;
+ row cr_report;
BEGIN
- FOR row IN select * from reports where report_id = in_report_id LOOP
+ FOR row IN select * from cr_report where id = in_report_id LOOP
RETURN NEXT row;
@@ -363,68 +407,74 @@
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof reports AS $$
+CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof cr_report AS $$
DECLARE
- row reports;
+ row cr_report;
BEGIN
- SELECT INTO row FROM reports
- WHERE ledger_id IS NULL
- and report_id = in_report_id
+ SELECT * INTO row FROM cr_report
+ where id = in_report_id
AND scn = -1;
IF NOT FOUND THEN -- I think this is a fairly major error condition
- RAISE EXCEPTION "No Bank Total found.";
+ RAISE EXCEPTION 'Bad report id.';
ELSE
- return row;
+ return next row;
END IF;
END;
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION reconciliation__corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$
+CREATE OR REPLACE FUNCTION reconciliation__corrections (in_report_id INT, in_id INT) returns setof cr_report_corrections AS $$
DECLARE
- corr report_corrections;
+ corr cr_report_corrections;
BEGIN
- SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1;
+ 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.";
+ RAISE EXCEPTION 'No corrections for selected entry.';
ELSE
- FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP
+
+ 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 'plplsql';
+$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_report_id INT, in_id INT) returns setof reports AS $$
+CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_id INT) returns cr_report_line AS $$
DECLARE
- row reports;
+ row cr_report_line;
BEGIN
- SELECT INTO row FROM reports WHERE report_id = in_report_id and id = in_id LIMIT 1;
+ 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";
- ELSE
- RETURN row;
+ 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 reports AS $$
+) RETURNS setof cr_report AS $$
DECLARE
row reports;
@@ -435,7 +485,7 @@
BEGIN
if in_status = "pending" then
- v_status = 'ft'::bool;
+ v_status = 'f'::bool;
ELSIF in_status = "approved" THEN
v_status = 't'::bool;
@@ -446,29 +496,29 @@
or in_account IS NOT NULL
or v_status IS NOT NULL
THEN
- statement = "select pr.* from reports pr ";
- statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
+ 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 + "join chart c on at.chart_id = c.id ";
- where_stmt = "c.accno =~ " + quote_literal(in_account) + " AND ";
+ 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 + "insert_time >= " + quote_literal(in_date_begin) + " AND ";
+ 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 + "insert_time <= " + quote_literal(in_date_end) + " AND ";
+ 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 + " approved = 't'::bool AND ";
+ where_stmt = where_stmt || $s$ approved = 't'::bool AND $s$;
ELSIF v_status == 'f'::bool THEN
- where_stmt = where_stmt + " approved = 'f'::bool AND ";
+ where_stmt = where_stmt || $s$ approved = 'f'::bool AND $s$;
END IF;
END IF;
@@ -488,13 +538,42 @@
create type recon_accounts as (
name text,
+ accno text,
id int
);
create or replace function reconciliation__account_list () returns setof recon_accounts as $$
SELECT
coa.accno || ' ' || coa.description as name,
- coa.id as id
- FROM chart coa, coa_to_account cta
+ coa.accno, coa.id as id
+ FROM chart coa, cr_coa_to_account cta
WHERE cta.chart_id = coa.id;
-$$ language sql;
\ No newline at end of file
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION reconciliation__get_current_balance
+(in_account_id int, in_date date) returns numeric as
+$$
+DECLARE outval NUMERIC;
+BEGIN
+ SELECT CASE WHEN (select category FROM chart WHERE id = in_account_id)
+ IN ('A', 'E') THEN sum(a.amount) * -1
+ ELSE sum(a.amount) END
+ INTO out_val
+ FROM acc_trans a
+ JOIN (
+ SELECT id FROM ar
+ WHERE approved is true
+ UNION
+ SELECT id FROM ap
+ WHERE approved is true
+ UNION
+ SELECT id FROM gl
+ WHERE approved is true
+ ) gl ON a.trans_id = gl.id
+ WHERE a.approved IS TRUE
+ AND a.chart_id = in_account_id
+ AND a.transdate <= in_date;
+
+ RETURN outval;
+END;
+$$ language plpgsql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.