# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Account reconciliation routines
package RC;
+use SL::DBUtils;
+use SL::DB;
+
+use strict;
+
sub paymentaccounts {
$main::lxdebug->enter_sub();
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
- my $query = qq|SELECT c.accno, c.description
- FROM chart c
- WHERE c.link LIKE '%_paid%'
- AND (c.category = 'A' OR c.category = 'L')
- ORDER BY c.accno|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $query =
+ qq|SELECT accno, description | .
+ qq|FROM chart | .
+ qq|WHERE link LIKE '%_paid%' AND category IN ('A', 'L') | .
+ qq|ORDER BY accno|;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{PR} }, $ref;
- }
- $sth->finish;
- $dbh->disconnect;
+ $form->{PR} = selectall_hashref_query($form, $dbh, $query);
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form) = @_;
# connect to database, turn AutoCommit off
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = SL::DB->client->dbh;
- my ($query, $sth);
+ my ($query, @values);
# get cleared balance
if ($form->{fromdate}) {
- $query = qq|SELECT sum(a.amount),
- (SELECT DISTINCT c2.category FROM chart c2
- WHERE c2accno = '$form->{accno}') AS category
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- WHERE a.transdate < date '$form->{fromdate}'
- AND a.cleared = '1'
- AND c.accno = '$form->{accno}'
- |;
+ $query =
+ qq|SELECT sum(a.amount), | .
+ qq| (SELECT DISTINCT c2.category FROM chart c2 | .
+ qq| WHERE c2.accno = ?) AS category | .
+ qq|FROM acc_trans a | .
+ qq|JOIN chart c ON (c.id = a.chart_id) | .
+ qq|WHERE a.transdate < ? AND a.cleared = '1' AND c.accno = ?|;
+ @values = ($form->{accno}, conv_date($form->{fromdate}), $form->{accno});
+
} else {
- $query = qq|SELECT sum(a.amount),
- (SELECT DISTINCT c2.category FROM chart c2
- WHERE c2.accno = '$form->{accno}') AS category
- FROM acc_trans a
- JOIN chart c ON (c.id = a.chart_id)
- WHERE a.cleared = '1'
- AND c.accno = '$form->{accno}'
- |;
- }
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{beginningbalance}, $form->{category}) = $sth->fetchrow_array;
-
- $sth->finish;
-
- my %oid = ('Pg' => 'ac.oid',
- 'Oracle' => 'ac.rowid');
-
- $query = qq|SELECT c.name, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, a.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM customer c, acc_trans ac, ar a, chart ch
- WHERE c.id = a.customer_id
--- AND NOT ac.fx_transaction
- AND ac.cleared = '0'
- AND ac.trans_id = a.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
- |;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
- $query .= qq|
-
- UNION
- SELECT v.name, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, a.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM vendor v, acc_trans ac, ap a, chart ch
- WHERE v.id = a.vendor_id
--- AND NOT ac.fx_transaction
- AND ac.cleared = '0'
- AND ac.trans_id = a.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
- |;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
- $query .= qq|
-
- UNION
- SELECT g.description, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, g.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM gl g, acc_trans ac, chart ch
- WHERE g.id = ac.trans_id
--- AND NOT ac.fx_transaction
- AND ac.cleared = '0'
- AND ac.trans_id = g.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
- |;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query =
+ qq|SELECT sum(a.amount), | .
+ qq| (SELECT DISTINCT c2.category FROM chart c2 | .
+ qq| WHERE c2.accno = ?) AS category | .
+ qq|FROM acc_trans a | .
+ qq|JOIN chart c ON (c.id = a.chart_id) | .
+ qq|WHERE a.cleared = '1' AND c.accno = ?|;
+ @values = ($form->{accno}, $form->{accno});
+ }
- $query .= " ORDER BY 3,7,8";
+ ($form->{beginningbalance}, $form->{category}) =
+ selectrow_query($form, $dbh, $query, @values);
+
+ @values = ();
+ $query =
+ qq|SELECT c.name, ac.source, ac.transdate, ac.cleared, | .
+ qq| ac.fx_transaction, ac.amount, a.id, | .
+ qq| ac.acc_trans_id AS oid | .
+ qq|FROM customer c, acc_trans ac, ar a, chart ch | .
+ qq|WHERE c.id = a.customer_id | .
+ qq| AND ac.cleared = '0' | .
+ qq| AND ac.trans_id = a.id | .
+ qq| AND ac.chart_id = ch.id | .
+ qq| AND ch.accno = ? |;
+ push(@values, $form->{accno});
+
+ if($form->{fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{fromdate}));
+ }
+
+ if($form->{todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{todate}));
+ }
+
+ if($form->{additional_fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{additional_fromdate}));
+ }
+
+ if($form->{additional_todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{additional_todate}));
+ }
+
+ if($form->{filter_amount}){
+ $query .= qq| AND ac.amount = ? |;
+ push(@values, conv_i($form->{filter_amount}));
+ }
+
+ $query .=
+ qq|UNION | .
+
+ qq|SELECT v.name, ac.source, ac.transdate, ac.cleared, | .
+ qq| ac.fx_transaction, ac.amount, a.id, | .
+ qq| ac.acc_trans_id AS oid | .
+ qq|FROM vendor v, acc_trans ac, ap a, chart ch | .
+ qq|WHERE v.id = a.vendor_id | .
+ qq| AND ac.cleared = '0' | .
+ qq| AND ac.trans_id = a.id | .
+ qq| AND ac.chart_id = ch.id | .
+ qq| AND ch.accno = ? |;
+
+ push(@values, $form->{accno});
+
+ if($form->{fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{fromdate}));
+ }
+
+ if($form->{todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{todate}));
+ }
+
+ if($form->{additional_fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{additional_fromdate}));
+ }
+
+ if($form->{additional_todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{additional_todate}));
+ }
+
+ if($form->{filter_amount}){
+ $query .= qq| AND ac.amount = ? |;
+ push(@values, conv_i($form->{filter_amount}));
+ }
+
+ $query .=
+ qq|UNION | .
+
+ qq|SELECT g.description, ac.source, ac.transdate, ac.cleared, | .
+ qq| ac.fx_transaction, ac.amount, g.id, | .
+ qq| ac.acc_trans_id AS oid | .
+ qq|FROM gl g, acc_trans ac, chart ch | .
+ qq|WHERE g.id = ac.trans_id | .
+ qq| AND ac.cleared = '0' | .
+ qq| AND ac.trans_id = g.id | .
+ qq| AND ac.chart_id = ch.id | .
+ qq| AND ch.accno = ? |;
+
+ push(@values, $form->{accno});
+
+ if($form->{fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{fromdate}));
+ }
+
+ if($form->{todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{todate}));
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ if($form->{additional_fromdate}) {
+ $query .= qq| AND ac.transdate >= ? |;
+ push(@values, conv_date($form->{additional_fromdate}));
+ }
- while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{PR} }, $pr;
+ if($form->{additional_todate}){
+ $query .= qq| AND ac.transdate <= ? |;
+ push(@values, conv_date($form->{additional_todate}));
}
- $sth->finish;
- $dbh->disconnect;
+ if($form->{filter_amount}){
+ $query .= qq| AND ac.amount = ? |;
+ push(@values, conv_i($form->{filter_amount}));
+ }
+
+ $query .= " ORDER BY 3,7,8";
+
+ $form->{PR} = selectall_hashref_query($form, $dbh, $query, @values);
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my ($query, $i);
- my %oid = ('Pg' => 'oid',
- 'Oracle' => 'rowid');
-
- # clear flags
- for $i (1 .. $form->{rowcount}) {
- if ($form->{"cleared_$i"}) {
- $query = qq|UPDATE acc_trans SET cleared = '1'
- WHERE $oid{$myconfig->{dbdriver}} = $form->{"oid_$i"}|;
- $dbh->do($query) || $form->dberror($query);
-
- # clear fx_transaction
- if ($form->{"fxoid_$i"}) {
- $query = qq|UPDATE acc_trans SET cleared = '1'
- WHERE $oid{$myconfig->{dbdriver}} = $form->{"fxoid_$i"}|;
- $dbh->do($query) || $form->dberror($query);
+ SL::DB->client->with_transaction(sub {
+ my $dbh = SL::DB->client->dbh;
+
+ my ($query, $i);
+
+ # clear flags
+ for $i (1 .. $form->{rowcount}) {
+ if ($form->{"cleared_$i"}) {
+ $query =
+ qq|UPDATE acc_trans SET cleared = '1' | .
+ qq|WHERE acc_trans_id = ?|;
+ do_query($form, $dbh, $query, $form->{"oid_$i"});
+
+ # clear fx_transaction
+ if ($form->{"fxoid_$i"}) {
+ $query =
+ qq|UPDATE acc_trans SET cleared = '1' | .
+ qq|WHERE acc_trans_id = ?|;
+ do_query($form, $dbh, $query, $form->{"fxoid_$i"});
+ }
}
}
+ 1;
+ }) or do { die SL::DB->client->error };
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_statement_balance {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database, turn AutoCommit off
+ my $dbh = SL::DB->client->dbh;
+
+ my ($query, @values);
+
+ $query = qq|SELECT sum(amount) FROM acc_trans where chart_id=45 AND cleared='1'|;
+
+ if($form->{fromdate}) {
+ $query .= qq| AND transdate >= ? |;
+ push(@values, conv_date($form->{fromdate}));
+ }
+
+ if($form->{todate}){
+ $query .= qq| AND transdate <= ? |;
+ push(@values, conv_date($form->{todate}));
}
- $dbh->disconnect;
+ ($form->{statement_balance}) = selectrow_query($form, $dbh, $query, @values);
$main::lxdebug->leave_sub();
}
projects / kivitendo-erp.git / blobdiff