[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3596] trunk
- Subject: SF.net SVN: ledger-smb:[3596] trunk
- From: ..hidden..
- Date: Sat, 30 Jul 2011 05:54:18 +0000
Revision: 3596
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3596&view=rev
Author: einhverfr
Date: 2011-07-30 05:54:17 +0000 (Sat, 30 Jul 2011)
Log Message:
-----------
Some code pruning in reconciliation, doc strings there too
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Reconciliation.pm
trunk/scripts/payment.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/Reconciliation.sql
trunk/t/98-pod-coverage.t
Modified: trunk/LedgerSMB/DBObject/Reconciliation.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Reconciliation.pm 2011-07-29 22:20:49 UTC (rev 3595)
+++ trunk/LedgerSMB/DBObject/Reconciliation.pm 2011-07-30 05:54:17 UTC (rev 3596)
@@ -12,6 +12,8 @@
=head1 METHODS
+Please note, this module needs a lot of cleanup.
+
=over
=item new ($class, base => $LedgerSMB::hash)
@@ -23,41 +25,6 @@
use any subclass of that. The per-session dbh is passed between the objects
this way as is any information that is needed.
-=item reconcile($self, $total, $month, $entries)
-
-Accepts the total balance, as well as a list of all entries from the bank
-statement as an array reference, and generates the pending report from
-this list.
-The first entry is always the total balance of the general ledger as
-compared to the balance held by the bank.
-
-Month is taken to be the date that the statement as represented by Entries
-is applicable to.
-
-Returns the new report ID. || An arrayref of entries.
-
-=item approve($self,$reportid)
-
-Approves the pending report $reportid.
-Checks for error codes in the pending report, and approves the report if none
-are found.
-
-Limitations: The creating user may not approve the report.
-
-Returns 1 on success.
-
-=item correct_entry($self, $report_id, $source_control_number, $new_balance)
-
-If the given entry $source_control_number in the report $report_id has an error
-code, the entry will be updated with $new_balance, and the error code
-recomputed.
-
-Returns the error code assigned to this entry.
-
- 0 for success
- 1 for found in general ledger, but does not match $new_balance
- 2 $source_control_number cannot be found in the general ledger
-
=item get_report($self, $report_id)
Collects all the rows from the database in the given report. Returns an
@@ -75,14 +42,6 @@
Returns a single entry from the pending reports table, either cleared or
uncleared.
-=back
-
-=head1 Copyright (C) 2007, The LedgerSMB core team.
-
-This file is licensed under the Gnu General Public License version 2, or at your
-option any later version. A copy of the license should have been included with
-your software.
-
=cut
package LedgerSMB::DBObject::Reconciliation;
@@ -94,6 +53,13 @@
# don't need new
+=item update
+
+Updates the report, pulling in any new transactions in the date range into the
+transaction list.
+
+=cut
+
sub update {
my $self = shift @_;
$self->exec_method(funcname=>'reconciliation__pending_transactions');
@@ -114,6 +80,12 @@
$self->{line_ids} =~ s/,?$/}/;
}
+=item submit
+
+Submits the reconciliation set for approval.
+
+=cut
+
sub submit {
my $self = shift @_;
$self->_pre_save;
@@ -121,6 +93,12 @@
$self->{dbh}->commit;
}
+=item save
+
+Saves the reconciliation set for later work
+
+=cut
+
sub save {
my $self = shift @_;
$self->_pre_save;
@@ -128,6 +106,14 @@
$self->{dbh}->commit;
}
+=item import_file
+
+Calls the file import function. This is generally assumed to be a csv file
+although the plugin is very modular and plugins could be written for other
+formats. The format structure is per account id.
+
+=cut
+
sub import_file {
my $self = shift @_;
@@ -138,6 +124,18 @@
return $self->{import_entries};
}
+=item approve($self,$reportid)
+
+Approves the pending report $reportid.
+Checks for error codes in the pending report, and approves the report if none
+are found.
+
+Limitations: The creating user may not approve the report.
+
+Returns 1 on success.
+
+=cut
+
sub approve {
my $self = shift @_;
@@ -157,6 +155,12 @@
$self->{dbh}->commit;
}
+=item new_report
+
+Creates a new report with data entered.
+
+=cut
+
sub new_report {
my $self = shift @_;
@@ -179,20 +183,15 @@
return ($report_id, $entries); # returns the report ID.
}
-=pod
-=over
+=item delete_report ($self, $report_id)
-=item delete ($self, $report_id)
-
Requires report_id
Deletes a report based on the report id.
Stored procedure returns true if deleted, false if not deleted/could not be
found, and raises EXCEPTION on report already approved.
-=back
-
=cut
sub delete_report {
@@ -223,6 +222,15 @@
return $bool;
}
+=item add_entries
+
+Adds entries from the import file routine.
+
+This function is extremely order dependent. Meaningful scn's must be submitted
+first it is also recommended that amounts be ordered where scn's are not found.
+
+=cut
+
sub add_entries {
my $self = shift;
my $entries = $self->{import_entries};
@@ -253,41 +261,17 @@
$self->{dbh}->commit;
}
-sub correct_entry {
-
- my $self = shift @_;
- my $report_id = $self->{report_id}; # shift @_;
- my $scn = $self->{id}; #shift @_;
- my $new_amount = $self->{new_amount}; #shift @_;
-
- # correct should return the new code value - whether or not it actually "matches"
- my $code = $self->exec_method(
- funcname=>'reconciliation__correct',
- args=>[$report_id, $scn, $new_amount]
- );
- return $code[0]->{'correct'};
-}
+=item search
-sub get_corrections {
-
- my $self = shift @_;
-
- return $self->exec_method(
- funcname=>'reconciliation__corrections',
- args=>[$self->{report_id}, $self->{entry_id}]
- );
-}
+Searches for reconciliation reports. No inputs mandatory.
-sub entry {
-
- my $self = shift @_;
-
- return $self->exec_method(
- funcname=>'reconciliation__single_entry',
- args=>[$self->{report_id}, $self->{entry_id}]
- );
-}
+date_from and date_to specify ranges.
+balance_from and balance_to specify ranges
+chart_id specifies an account
+submitted and approved are exact matches to status.
+=cut
+
sub search {
my $self = shift @_;
@@ -297,24 +281,48 @@
);
}
-sub get_pending {
-
- my $self = shift @_;
- return $self->exec_method(
- funcname=>'reconciliation__pending'
- );
-}
+=item get
-sub get_report_list {
-
- my $self = shift @_;
-
- return $self->exec_method(
- funcname=>'reconciliation__report_list',
- args=>[$self->{account},$self->{report}]
- );
-}
+Gets all information relating to a reconciliation report.
+id must be set.
+
+Populates main hash with values from cr_report
+
+Also populates
+
+=over
+
+=item report_lines
+
+a list of report lines
+
+=item account_info
+
+a hashrefo of information from the account table.
+
+=item beginning_balance
+
+=item cleared_total
+
+=item outstanding_total
+
+=item mismatch_our_total
+
+=item mismatch_our_credits
+
+=item mismatch_our_debits
+
+=item mismatch_their_total
+
+=item mismatch_their_credits
+
+=item mismatch_their_debits
+
+=back
+
+=cut
+
sub get {
my ($self) = shift @_;
my ($ref) = $self->exec_method(funcname=>'reconciliation__report_summary');
@@ -390,6 +398,12 @@
}
}
+=item get_accounts
+
+This is a simple wrapper around reconciliation__account_list
+
+=cut
+
sub get_accounts {
my $self = shift @_;
@@ -397,4 +411,15 @@
funcname=>'reconciliation__account_list',
);
}
+
+=back
+
+=head1 Copyright (C) 2007, The LedgerSMB core team.
+
+This file is licensed under the Gnu General Public License version 2, or at your
+option any later version. A copy of the license should have been included with
+your software.
+
+=cut
+
1;
Modified: trunk/scripts/payment.pl
===================================================================
--- trunk/scripts/payment.pl 2011-07-29 22:20:49 UTC (rev 3595)
+++ trunk/scripts/payment.pl 2011-07-30 05:54:17 UTC (rev 3596)
@@ -172,7 +172,7 @@
};
for my $invrow (1 .. $request->{"invoice_count_$cid"}){
my $inv_id = $request->{"invoice_${cid}_$invrow"};
- if ($request->{"paid_$cid"} = 'all'){
+ if ($request->{"paid_$cid"} eq 'all'){
$ref->{amount} += $request->{"payment_$inv_id"};
} else {
$ref->{amount} += $request->{"net_$inv_id"};
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-07-29 22:20:49 UTC (rev 3595)
+++ trunk/sql/Pg-database.sql 2011-07-30 05:54:17 UTC (rev 3596)
@@ -469,6 +469,14 @@
COMMENT ON TABLE users IS $$username is the actual primary key here because we
do not want duplicate users$$;
+CREATE OR REPLACE FUNCTION person__get_my_entity_id() RETURNS INT AS
+$$
+ SELECT entity_id from users where username = SESSION_USER;
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION person__get_my_entity_id() IS
+$$ Returns the entity_id of the current, logged in user.$$;
+
create table lsmb_roles (
user_id integer not null references users(id) ON DELETE CASCADE,
@@ -4443,4 +4451,57 @@
view_name text not null unique
);
+CREATE TABLE cr_report (
+ id bigserial primary key not null,
+ chart_id int not null references account(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(),
+ updated timestamp not null default now(),
+ entered_by int not null default person__get_my_entity_id() references entity(id),
+ entered_username text not null default SESSION_USER,
+ deleted boolean not null default 'f'::boolean,
+ deleted_by int references entity(id),
+ approved_by int references entity(id),
+ approved_username text,
+ CHECK (deleted is not true or approved is not true)
+);
+
+COMMENT ON TABLE cr_report IS
+$$This table holds header data for cash reports.$$;
+
+CREATE TABLE cr_report_line (
+ id bigserial primary key not null,
+ report_id int NOT NULL references cr_report(id),
+ scn text, -- SCN is the check #
+ their_balance numeric,
+ our_balance numeric,
+ errorcode INT,
+ "user" int references entity(id) not null,
+ clear_time date,
+ insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
+ trans_type text,
+ post_date date,
+ 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'
+);
+
+COMMENT ON TABLE cr_report_line IS
+$$ This stores line item data on transaction lines and whether they are
+cleared.$$;
+
+COMMENT ON COLUMN cr_report_line.scn IS
+$$ This is the check number. Maps to acc_trans.source $$;
+
+CREATE TABLE cr_coa_to_account (
+ chart_id int not null references account(id),
+ account text not null
+);
+
+COMMENT ON TABLE cr_coa_to_account IS
+$$ Provides name mapping for the cash reconciliation screen.$$;
+
commit;
Modified: trunk/sql/modules/Reconciliation.sql
===================================================================
--- trunk/sql/modules/Reconciliation.sql 2011-07-29 22:20:49 UTC (rev 3595)
+++ trunk/sql/modules/Reconciliation.sql 2011-07-30 05:54:17 UTC (rev 3596)
@@ -1,43 +1,3 @@
-CREATE TABLE cr_report (
- id bigserial primary key not null,
- chart_id int not null references account(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(),
- updated timestamp not null default now(),
- entered_by int not null default person__get_my_entity_id() references entity(id),
- entered_username text not null default SESSION_USER,
- deleted boolean not null default 'f'::boolean,
- deleted_by int references entity(id),
- approved_by int references entity(id),
- approved_username text,
- CHECK (deleted is not true or approved is not true)
-);
-
-CREATE TABLE cr_report_line (
- id bigserial primary key not null,
- report_id int NOT NULL references cr_report(id),
- scn text, -- SCN is the check #
- their_balance numeric,
- our_balance numeric,
- errorcode INT,
- "user" int references entity(id) not null,
- clear_time date,
- insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
- trans_type text,
- post_date date,
- 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'
-);
-
-CREATE TABLE cr_coa_to_account (
- chart_id int not null references account(id),
- account text not null
-);
-
CREATE OR REPLACE FUNCTION reconciliation__submit_set(
in_report_id int, in_line_ids int[]) RETURNS bool AS
$$
@@ -49,6 +9,12 @@
END;
$$ LANGUAGE PLPGSQL;
+COMMENT ON FUNCTION reconciliation__submit_set(
+ in_report_id int, in_line_ids int[]) IS
+$$Submits a reconciliation report for approval.
+in_line_ids is used to specify which report lines are cleared, finalizing the
+report.$$;
+
CREATE OR REPLACE FUNCTION reconciliation__save_set(
in_report_id int, in_line_ids int[]) RETURNS bool AS
$$
@@ -62,6 +28,10 @@
END;
$$ LANGUAGE PLPGSQL;
+COMMENT ON FUNCTION reconciliation__save_set(
+ in_report_id int, in_line_ids int[]) IS
+$$Sets which lines of the report are cleared.$$;
+
CREATE OR REPLACE FUNCTION reconciliation__delete_report(in_report_id int)
RETURNS bool AS
$$
@@ -78,8 +48,10 @@
PERFORM id FROM cr_report WHERE id = in_report_id AND approved = TRUE;
- IF FOUND THEN
- RAISE EXCEPTION 'reconcilation__delete_report(): report % is approved; cannot delete.', in_report_id;
+ IF FOUND THEN --changing the verbose message to a notice and adding a
+ --program-helpful exception --CT
+ RAISE NOTICE 'reconcilation__delete_report(): report % is approved; cannot delete.', in_report_id;
+ RAISE EXCEPTION 'Cannot delete approved';
END IF;
PERFORM id
@@ -88,10 +60,13 @@
AND submitted = TRUE
AND entered_by = person__get_my_entity_id();
- IF FOUND THEN
- -- Creators cannot delete their own reports if they've been submitted.
- RAISE EXCEPTION 'reconciliation__delete_report(): creators cannot delete their own report after submission. %', in_report_id;
- END IF;
+ -- IF FOUND THEN
+ -- Creators cannot delete their own reports if they've been submitted. -AS
+ -- Why not? If it hasn't been approved.....
+ -- Also concerned about single-user setups here so commenting out
+ -- this block --CT
+ -- RAISE EXCEPTION 'reconciliation__delete_report(): creators cannot delete their own report after submission. %', in_report_id;
+ -- END IF;
UPDATE cr_report
SET deleted = TRUE,
@@ -102,6 +77,13 @@
END;
$$ language plpgsql;
+COMMENT ON FUNCTION reconciliation__delete_report(in_report_id int) IS
+$$Deletes the report if the report exists and is unapproved.
+
+Note this does not actually delete anything from the database. Deleted reports
+are kept around in case they need to be investigated. It only marks them as
+deleted so that they can never be approved.$$;
+
CREATE OR REPLACE FUNCTION reconciliation__get_cleared_balance(in_chart_id int)
RETURNS numeric AS
$$
@@ -118,6 +100,13 @@
GROUP BY c.id, c.category;
$$ LANGUAGE sql;
+COMMENT ON FUNCTION reconciliation__get_cleared_balance(in_chart_id int) IS
+$$ Gets the cleared balance of the account specified by chart_id.
+This is specified in normal format (i.e. positive numbers for debits for asset
+and espense accounts, and positive numbers for credits in other accounts
+
+Note that currently contra accounts will show negative balances.$$;
+
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;
@@ -181,15 +170,23 @@
$$ language 'plpgsql' security definer;
-CREATE OR REPLACE FUNCTION reconciliation__new_report_id (in_chart_id int,
+COMMENT ON FUNCTION reconciliation__report_approve (in_report_id INT) IS
+$$Marks the report approved and marks all cleared transactions in it cleared.$$;
-in_total numeric, in_end_date date) returns INT as $$
+-- 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 $$
+
INSERT INTO cr_report(chart_id, their_total, end_date) values ($1, $2, $3);
SELECT currval('cr_report_id_seq')::int;
$$ language 'sql';
+COMMENT ON FUNCTION reconciliation__new_report_id
+(in_chart_id int, in_total numeric, in_end_date date) IS
+$$ Inserts creates a new report and returns the id.$$;
+
create or replace function reconciliation__add_entry(
in_report_id INT,
in_scn TEXT,
@@ -325,11 +322,17 @@
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
+$$
+This function is used for automatically matching entries from an external source
+like a bank-produced csv file.
+
+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.$$;
+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 $$
+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;
@@ -370,6 +373,10 @@
END;
$$ LANGUAGE plpgsql;
+COMMENT ON function reconciliation__pending_transactions
+(in_end_date DATE, in_chart_id int, in_report_id int, in_their_total numeric) IS
+$$Ensures that the list of pending transactions in the report is up to date. $$;
+
CREATE OR REPLACE FUNCTION reconciliation__report_details (in_report_id INT) RETURNS setof cr_report_line as $$
DECLARE
@@ -387,6 +394,9 @@
$$ language 'plpgsql';
+COMMENT ON FUNCTION reconciliation__report_details (in_report_id INT) IS
+$$ Returns the details of the report. $$;
+
CREATE OR REPLACE FUNCTION reconciliation__report_summary (in_report_id INT) RETURNS cr_report as $$
DECLARE
@@ -400,6 +410,7 @@
$$ language 'plpgsql';
+-- why is this not called reconciliation__get_report or something? --CT
CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof cr_report AS $$
DECLARE
@@ -419,6 +430,9 @@
$$ language 'plpgsql';
+COMMENT ON FUNCTION reconciliation__get_total (in_report_id INT) IS
+$$ Retrieves all header info from the reconciliation report.$$;
+
CREATE OR REPLACE FUNCTION reconciliation__search
(in_date_from date, in_date_to date,
in_balance_from numeric, in_balance_to numeric,
@@ -448,6 +462,16 @@
END;
$$ language plpgsql;
+COMMENT ON 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) IS
+$$ Searches for reconciliation reports.
+NULLs match all values.
+in_date_to and in_date_from give a range of reports. All other inputs are
+exact matches.
+$$;
+
create type recon_accounts as (
name text,
accno text,
@@ -455,14 +479,18 @@
);
create or replace function reconciliation__account_list () returns setof recon_accounts as $$
- SELECT
+ SELECT
coa.accno || ' ' || coa.description as name,
coa.accno, coa.id as id
- FROM account coa, cr_coa_to_account cta
- WHERE cta.chart_id = coa.id
+ FROM account coa
+ JOIN cr_coa_to_account cta ON cta.chart_id = coa.id
ORDER BY coa.accno;
$$ language sql;
+COMMENT ON function reconciliation__account_list () IS
+$$ returns set of accounts set up for reconciliation. Currently we pull the
+account number and description from the account table.$$;
+
CREATE OR REPLACE FUNCTION reconciliation__get_current_balance
(in_account_id int, in_date date) returns numeric as
$$
@@ -490,6 +518,12 @@
END;
$$ language plpgsql;
+COMMENT ON FUNCTION reconciliation__get_current_balance
+(in_account_id int, in_date date) IS
+$$ Gets the current balance of all approved transactions against a specific
+account. For asset and expense accounts this is the debit balance, for others
+this is the credit balance.$$;
+
CREATE OR REPLACE VIEW recon_payee AS
SELECT n.name AS payee, rr.id, rr.report_id, rr.scn, rr.their_balance, rr.our_balance, rr.errorcode, rr."user", rr.clear_time, rr.insert_time, rr.trans_type, rr.post_date, rr.ledger_id, rr.voucher_id, rr.overlook, rr.cleared
FROM cr_report_line rr
@@ -520,4 +554,7 @@
RETURN NEXT row;
END LOOP;
END;
-$$ language 'plpgsql';
\ No newline at end of file
+$$ language 'plpgsql';
+
+COMMENT ON FUNCTION reconciliation__report_details_payee (in_report_id INT) IS
+$$ Pulls the payee information for the reconciliation report.$$;
Modified: trunk/t/98-pod-coverage.t
===================================================================
--- trunk/t/98-pod-coverage.t 2011-07-29 22:20:49 UTC (rev 3595)
+++ trunk/t/98-pod-coverage.t 2011-07-30 05:54:17 UTC (rev 3596)
@@ -13,7 +13,7 @@
if ($@){
plan skip_all => "Test::Pod::Coverage required for testing POD coverage";
} else {
- plan tests => 26;
+ plan tests => 27;
}
pod_coverage_ok("LedgerSMB");
pod_coverage_ok("LedgerSMB::Form");
@@ -43,4 +43,5 @@
pod_coverage_ok("LedgerSMB::DBObject::Payment",
{also_private => [qr/^(format_ten_|num2text_)/]}
);
+pod_coverage_ok("LedgerSMB::DBObject::Reconciliation");
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.