X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRC.pm;h=d7d71a3a89a759446b1609d0d1b1db891c233a38;hb=a27846ef2756ed0f59c29d256a5d43d6caaf0b58;hp=63a41f434c1e8bab99e1c5047cbf1f36593cfddd;hpb=ee072e4f077213bf6f8792ca8f0a1afebbb6282f;p=kivitendo-erp.git

diff --git a/SL/RC.pm b/SL/RC.pm
index 63a41f434..d7d71a3a8 100644
--- a/SL/RC.pm
+++ b/SL/RC.pm
@@ -25,7 +25,8 @@
 # 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
@@ -34,27 +35,25 @@
 
 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();
 }
@@ -65,104 +64,156 @@ sub payment_transactions {
   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();
 }
@@ -172,30 +223,57 @@ sub reconcile {
 
   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();
 }