Menu: HTML Menü parsing n+1 beseitigt

[kivitendo-erp.git] / SL / RC.pm

diff --git a/SL/RC.pm b/SL/RC.pm
index aa0b492..89904d8 100644 (file)
--- a/SL/RC.pm
+++ b/SL/RC.pm
@@ -36,6 +36,8 @@ package RC;
 
 use SL::DBUtils;
 
+use strict;
+
 sub paymentaccounts {
   $main::lxdebug->enter_sub();
 
@@ -91,13 +93,11 @@ sub payment_transactions {
   ($form->{beginningbalance}, $form->{category}) =
     selectrow_query($form, $dbh, $query, @values);
 
-  my %oid = ('Pg'     => 'ac.acc_trans_id',
-             'Oracle' => 'ac.rowid');
   @values = ();
   $query =
     qq|SELECT c.name, ac.source, ac.transdate, ac.cleared, | .
     qq|  ac.fx_transaction, ac.amount, a.id, | .
-    qq|  $oid{$myconfig->{dbdriver}} AS oid | .
+    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' | .
@@ -116,12 +116,27 @@ sub payment_transactions {
     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|  $oid{$myconfig->{dbdriver}} AS oid | .
+    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' | .
@@ -141,12 +156,27 @@ sub payment_transactions {
     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|  $oid{$myconfig->{dbdriver}} AS oid | .
+    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' | .
@@ -166,7 +196,22 @@ sub payment_transactions {
     push(@values, conv_date($form->{todate}));
   }
 
-  $query .= " ORDER BY 3,7,8";
+  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 .= " ORDER BY 3,7,8 LIMIT 6";
 
   $form->{PR} = selectall_hashref_query($form, $dbh, $query, @values);
 
@@ -184,22 +229,20 @@ sub reconcile {
   my $dbh = $form->dbconnect($myconfig);
 
   my ($query, $i);
-  my %oid = ('Pg'     => 'acc_trans_id',
-             'Oracle' => 'rowid');
 
   # clear flags
   for $i (1 .. $form->{rowcount}) {
     if ($form->{"cleared_$i"}) {
       $query =
         qq|UPDATE acc_trans SET cleared = '1' | .
-        qq|WHERE $oid{$myconfig->{dbdriver}} = ?|;
+        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 $oid{$myconfig->{dbdriver}} = ?|;
+          qq|WHERE acc_trans_id = ?|;
         do_query($form, $dbh, $query, $form->{"fxoid_$i"});
       }
     }
@@ -210,4 +253,33 @@ sub reconcile {
   $main::lxdebug->leave_sub();
 }
 
+sub get_statement_balance {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database, turn AutoCommit off
+  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  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}));
+  }
+
+  ($form->{statement_balance}) = selectrow_query($form, $dbh, $query, @values);
+
+  $dbh->disconnect;
+
+  $main::lxdebug->leave_sub();
+}
+
 1;