summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--LedgerSMB/AA.pm28
-rw-r--r--LedgerSMB/Form.pm54
-rw-r--r--LedgerSMB/Reconciliation.pm132
3 files changed, 179 insertions, 35 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index 22f32474..1165b232 100644
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -944,19 +944,18 @@ sub get_name {
# get customer/vendor
my $query = qq|
- SELECT c.name AS $form->{vc}, c.discount, c.creditlimit,
- c.terms, c.email, c.cc, c.bcc, c.taxincluded,
- c.address1, c.address2, c.city, c.state,
- c.zipcode, c.country, c.curr AS currency,
+ SELECT entity.name AS $form->{vc}, c.discount,
+ c.creditlimit,
+ c.terms, c.taxincluded,
+ c.curr AS currency,
c.language_code, $duedate AS duedate,
- c.notes AS intnotes,
b.discount AS tradediscount,
- b.description AS business,
- e.name AS employee, e.id AS employee_id
+ b.description AS business
FROM $form->{vc} c
+ JOIN entity ON (entity.id = c.entity_id)
LEFT JOIN business b ON (b.id = c.business_id)
- LEFT JOIN employees e ON (e.id = c.employee_id)
WHERE c.id = ?|;
+ # TODO: Add location join
@queryargs = ( $form->{"$form->{vc}_id"} );
my $sth = $dbh->prepare($query);
@@ -974,6 +973,8 @@ sub get_name {
for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
$sth->finish;
+ # TODO: Retrieve contact records
+
my $buysell = ( $form->{vc} eq 'customer' ) ? "buy" : "sell";
# if no currency use defaultcurrency
@@ -1005,7 +1006,7 @@ sub get_name {
$query = qq|
SELECT SUM(amount - paid)
FROM $arap
- WHERE $form->{vc}_id = ?|;
+ WHERE id = ?|;
$sth = $dbh->prepare($query);
$sth->execute( $form->{"$form->{vc}_id"} )
@@ -1023,7 +1024,8 @@ sub get_name {
WHERE e.curr = o.curr
AND e.transdate = o.transdate)
FROM oe o
- WHERE o.$form->{vc}_id = ?
+ WHERE o.entity_id =
+ (select entity_id from $form->{vc} WHERE id = ?)
AND o.quotation = '0' AND o.closed = '0'|;
$sth = $dbh->prepare($query);
@@ -1124,15 +1126,15 @@ sub get_name {
$query = qq|
SELECT c.accno, c.description, c.link,
c.category,
- ac.project_id
+ ac.project_id,
a.department_id
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
JOIN $arap a ON (a.id = ac.trans_id)
- WHERE a.$form->{vc}_id = ?
+ WHERE a.entity_id = ?
AND a.id = (SELECT max(id)
FROM $arap
- WHERE $form->{vc}_id =
+ WHERE entity_id =
?)
|;
diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index dba2194d..206669c6 100644
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -621,7 +621,7 @@ sub get_my_emp_num {
# we got a connection, check the version
my $query = qq|
- SELECT employeenumber FROM employees
+ SELECT employeenumber FROM employee
WHERE login = ?|;
my $sth = $dbh->prepare($query);
$sth->execute( $form->{login} ) || $form->dberror($query);
@@ -1815,9 +1815,11 @@ sub get_employee {
my $login = $self->{login};
$login =~ s/@.*//;
- my $query = qq|SELECT name, id
- FROM employees
- WHERE login = ?|;
+ my $query = qq|
+ SELECT name, id
+ FROM entity WHERE id IN (select entity_id
+ FROM employee
+ WHERE login = ?)|;
$sth = $self->{dbh}->prepare($query);
$sth->execute($login);
@@ -1910,16 +1912,18 @@ sub all_vc {
if ( $count < $myconfig->{vclimit} ) {
$self->{"${vc}_id"} *= 1;
+ $where = "AND $where" if $where;
$query = qq|SELECT id, name
- FROM $vc
- WHERE 1=1
- $where
+ FROM entity
+ WHERE id IN (select entity_id
+ FROM $vc)
+ $where
UNION
SELECT id,name
- FROM $vc
+ FROM entity
WHERE id = ?
ORDER BY name|;
@@ -2018,24 +2022,27 @@ sub all_employees {
my @whereargs = ();
# setup employees/sales contacts
- my $query = qq|SELECT id, name
- FROM employees
- WHERE 1 = 1|;
+ my $query = qq|
+ SELECT id, name
+ FROM entity
+ WHERE id IN (SELECT entity_id FROM employee
+ WHERE|;
if ($transdate) {
- $query .= qq| AND (startdate IS NULL OR startdate <= ?)
- AND (enddate IS NULL OR enddate >= ?)|;
+ $query .= qq| (startdate IS NULL OR startdate <= ?)
+ AND (enddate IS NULL OR enddate >= ?) AND|;
@whereargs = ( $transdate, $transdate );
}
else {
- $query .= qq| AND enddate IS NULL|;
+ $query .= qq| enddate IS NULL AND|;
}
if ($sales) {
- $query .= qq| AND sales = '1'|;
+ $query .= qq| sales = '1' AND|;
}
- $query .= qq| ORDER BY name|;
+ $query =~ s/(WHERE|AND)$//;
+ $query .= qq|) ORDER BY name|;
my $sth = $dbh->prepare($query);
$sth->execute(@whereargs) || $self->dberror($query);
@@ -2245,7 +2252,7 @@ sub create_links {
c.language_code, a.ponumber
FROM $arap a
JOIN $vc c ON (a.${vc}_id = c.id)
- LEFT JOIN employees e ON (e.id = a.employee_id)
+ LEFT JOIN employee e ON (e.id = a.employee_id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE a.id = ?|;
@@ -2375,7 +2382,6 @@ sub lastname_used {
my $dbh = $self->{dbh};
$vc ||= $self->{vc}; # add default to correct for improper passing
my $arap = ( $vc eq 'customer' ) ? "ar" : "ap";
- my $where = "1 = 1";
my $sth;
if ( $self->{type} =~ /_order/ ) {
@@ -2387,14 +2393,18 @@ sub lastname_used {
$arap = 'oe';
$where = "quotation = '1'";
}
-
+ $where = "AND $where " if $where;
+ $inv_notes = "ct.invoice_notes," if $vc eq 'customer';
my $query = qq|
- SELECT ct.name AS $vc, ct.curr AS currency, ct.id AS ${vc}_id,
+ SELECT entity.name, ct.curr AS currency, ct.id AS ${vc}_id,
current_date + ct.terms AS duedate,
- ct.notes,
+ $inv_notes
ct.curr AS currency
FROM $vc ct
- WHERE ct.id = (select ${vc}_id from $arap where $where AND ${vc}_id IS NOT NULL order by id DESC limit 1)|;
+ JOIN entity ON (ct.entity_id = entity.id)
+ WHERE entity.id = (select entity_id from $arap
+ where entity_id IS NOT NULL $where
+ order by id DESC limit 1)|;
$sth = $dbh->prepare($query);
$sth->execute() || $self->dberror($query);
diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm
new file mode 100644
index 00000000..e9963ae9
--- /dev/null
+++ b/LedgerSMB/Reconciliation.pm
@@ -0,0 +1,132 @@
+
+=pod
+
+=head1 NAME
+
+LedgerSMB::DBObject::Reconciliation - LedgerSMB class defining the core
+database interaction logic for Reconciliation.
+
+=head1 SYOPSIS
+
+This module creates object instances based on LedgerSMB's in-database ORM.
+
+=head1 METHODS
+
+=over
+
+=item new ($class, base => $LedgerSMB::hash)
+
+This is the base constructor for all child classes. It must be used with base
+argument because this is necessary for database connectivity and the like.
+
+Of course the base object can be any object that inherits LedgerSMB, so you can
+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, $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.
+
+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
+
+=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;
+
+use base qw(LedgerSMB::DBObject);
+
+# don't need new
+
+sub reconcile {
+
+ my $self = shift @_;
+ my $total = shift @_;
+ my $entries = shift @_; # expects an arrayref.
+
+ # Total is in here somewhere, too
+
+ my $report_id = $self->new_report(); # gives us a report ID to insert with.
+
+ # 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 => 0, balance=> $total, old_balance=> $self->current_balance, code=> $self->compare_total($total) };
+ for my $entry (@{$entries}) {
+
+ # Codes:
+ # 0 is success
+ # 1 is found, but mismatch
+ # 2 is not found
+ $entry{report_id} = $report_id;
+ $entry{code} = $self->add_entry( $entry );
+
+ }
+ # Based on chatting with Chris T, we are going to use an arrayref of hashrefs to handle
+ # the varying return states.
+ return $entries; # returns the report ID.
+}
+
+sub approve {
+
+ my $self = shift @_;
+ # the user should be embedded into the $self object.
+ my $report_id = shift @_;
+
+ my $code = $self->report_approve($report_id,$self->{user}->{id}); # user
+
+ if ($code == 0) { # no problem.
+ return $code;
+ }
+ # this is destined to change as we figure out the Error system.
+ elsif ($code == 99) {
+
+ $self->error("User $self->{user}->{name} cannot approve report, must be a different user.");
+ }
+}
+
+sub correct_entry {
+
+ my $self = shift @_;
+ my $report_id = shift @_;
+ my $scn = shift @_;
+ my $new_amount = shift @_;
+
+ # correct should return the new code value - whether or not it actually "matches"
+ my $code = $self->correct($report_id, $scn, $new_amount);
+ return $code[0]->{'correct'};
+}
+
+1;