X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=016e268ec50498c5d677e530ca0760bcfed97840;hb=826d79929635c726f7459296fafa7e630c00e72d;hp=5554916c4d784e80274bab2fc1c19c32d44620a1;hpb=fc1e397330501fecd1ea62511c0965f0a5ef27b6;p=kivitendo-erp.git

diff --git a/SL/RP.pm b/SL/RP.pm
index 5554916c4..016e268ec 100644
--- a/SL/RP.pm
+++ b/SL/RP.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.
 #======================================================================
 #
 # backend code for reports
@@ -38,6 +39,8 @@ use SL::DBUtils;
 use Data::Dumper;
 use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date);
 use List::Util qw(sum);
+use List::UtilsBy qw(partition_by sort_by);
+use SL::DB;
 
 # use warnings;
 use strict;
@@ -520,52 +523,52 @@ sub get_accounts_g {
             ELSE 0
             /* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */
             END
-                ) AS amount, c.$category
+                ) AS amount, c.$category, c.accno, c.description
        FROM acc_trans ac
        LEFT JOIN chart c ON (c.id  = ac.chart_id)
        LEFT JOIN ar      ON (ar.id = ac.trans_id)
       WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere)
 
-      GROUP BY c.$category
+      GROUP BY c.$category, c.accno, c.description
 
 /*
-       SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
+       SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id)
          JOIN ar a ON (a.id = ac.trans_id)
          WHERE $where $dpt_where
            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
            $project
-         GROUP BY c.$category
+         GROUP BY c.$category, c.accno, c.description
 */
          UNION
 
-         SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
+         SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id)
          JOIN ap a ON (a.id = ac.trans_id)
          WHERE $where $dpt_where
            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
            $project
-         GROUP BY c.$category
+         GROUP BY c.$category, c.accno, c.description
 
          UNION
 
-         SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
+         SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id)
          JOIN gl a ON (a.id = ac.trans_id)
          WHERE $where $dpt_where $glwhere
            AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
            $project
-         GROUP BY c.$category
+         GROUP BY c.$category, c.accno, c.description
         |;
 
     if ($form->{project_id}) {
       $query .= qq|
          UNION
 
-         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
+         SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
          FROM invoice ac
          JOIN ar a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
@@ -573,11 +576,11 @@ sub get_accounts_g {
          WHERE (c.category = 'I') $prwhere $dpt_where
            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
            $project
-         GROUP BY c.$category
+         GROUP BY c.$category, c.accno, c.description
 
          UNION
 
-         SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
+         SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
          FROM invoice ac
          JOIN ap a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
@@ -585,7 +588,7 @@ sub get_accounts_g {
          WHERE (c.category = 'E') $prwhere $dpt_where
            AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
          $project
-         GROUP BY c.$category
+         GROUP BY c.$category, c.accno, c.description
          |;
     }
 
@@ -598,19 +601,19 @@ sub get_accounts_g {
     }
 
     $query = qq|
-        SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
+        SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
         FROM acc_trans ac
         JOIN chart c ON (c.id = ac.chart_id)
         WHERE $where
           $dpt_where_without_arapgl
           $project
-        GROUP BY c.$category |;
+        GROUP BY c.$category, c.accno, c.description |;
 
     if ($form->{project_id}) {
       $query .= qq|
         UNION
 
-        SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
+        SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
         FROM invoice ac
         JOIN ar a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)
@@ -619,11 +622,11 @@ sub get_accounts_g {
           $prwhere
           $dpt_where
           $project
-        GROUP BY c.$category
+        GROUP BY c.$category, c.accno, c.description
 
         UNION
 
-        SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
+        SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category, c.accno, c.description
         FROM invoice ac
         JOIN ap a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)
@@ -632,7 +635,7 @@ sub get_accounts_g {
           $prwhere
           $dpt_where
           $project
-        GROUP BY c.$category |;
+        GROUP BY c.$category, c.accno, c.description |;
     }
   }
 
@@ -640,15 +643,27 @@ sub get_accounts_g {
   my $accno;
   my $ref;
 
+  # store information for chart list in $form->{charts}
   foreach my $ref (selectall_hashref_query($form, $dbh, $query)) {
+    unless ( defined $form->{charts}->{$ref->{accno}}  ) {
+      # a chart may appear several times in the resulting hashref, init it the first time
+      $form->{charts}->{$ref->{accno}} = { amount      => 0,
+                                           "$category" => $ref->{"$category"},
+                                           accno       => $ref->{accno},
+                                           description => $ref->{description},
+                                         };
+    }
     if ($category eq "pos_bwa") {
       if ($last_period) {
         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
       } else {
         $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
+        # only increase chart amount for current period, not last_period
+        $form->{charts}->{$ref->{accno}}->{amount} +=  $ref->{amount},
       }
     } else {
       $form->{ $ref->{$category} } += $ref->{amount};
+      $form->{charts}->{$ref->{accno}}->{amount} +=  $ref->{amount}; # no last_period for eur
     }
   }
 
@@ -660,7 +675,7 @@ sub trial_balance {
 
   my ($self, $myconfig, $form, %options) = @_;
 
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my ($query, $sth, $ref);
   my %balance = ();
@@ -1162,8 +1177,6 @@ sub trial_balance {
 
   }
 
-  $dbh->disconnect;
-
   # debits and credits for headings
   foreach my $accno (@headingaccounts) {
     foreach $ref (@{ $form->{TB} }) {
@@ -1200,7 +1213,7 @@ sub aging {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh     = $form->dbconnect($myconfig);
+  my $dbh     = SL::DB->client->dbh;
 
   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
 
@@ -1332,9 +1345,6 @@ sub aging {
 
   $sth->finish;
 
-  # disconnect
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -1343,8 +1353,7 @@ sub get_customer {
 
   my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $ct = $form->{ct} eq "customer" ? "customer" : "vendor";
 
@@ -1354,7 +1363,6 @@ sub get_customer {
        WHERE ct.id = ?|;
   ($form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc}) =
     selectrow_query($form, $dbh, $query, $form->{"${ct}_id"});
-  $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
@@ -1364,8 +1372,7 @@ sub tax_report {
 
   my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my ($null, $department_id) = split /--/, $form->{department};
 
@@ -1454,8 +1461,6 @@ sub tax_report {
 
   $form->{TR} = selectall_hashref_query($form, $dbh, $query);
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -1465,7 +1470,7 @@ sub paymentaccounts {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database, turn AutoCommit off
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $ARAP = $form->{db} eq "ar" ? "AR" : "AP";
 
@@ -1476,8 +1481,6 @@ sub paymentaccounts {
        WHERE link LIKE '%${ARAP}_paid%'|;
   $form->{PR} = selectall_hashref_query($form, $dbh, $query);
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -1487,7 +1490,7 @@ sub payments {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database, turn AutoCommit off
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $ml = 1;
   my $arap;
@@ -1608,8 +1611,6 @@ sub payments {
     $sth_details->finish();
   }
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -1618,8 +1619,7 @@ sub bwa {
 
   my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $last_period = 0;
   my $category;
@@ -1646,6 +1646,15 @@ sub bwa {
     &get_accounts_g($dbh, $last_period, $kummfromdate, $kummtodate, $form, "pos_bwa");
   }
 
+  my %charts_by_category =
+    partition_by { $_->{pos_bwa} }
+    sort_by      { $_->{accno}   }
+    map          { $form->{charts}->{$_} }
+    keys %{ $form->{charts} };
+  $form->{"charts_by_category"} = \%charts_by_category;
+
+  $form->{category_names} = AM->get_bwa_categories($myconfig, $form);
+
   my @periods        = qw(jetzt kumm);
   my @gesamtleistung = qw(1 3);
   my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
@@ -1819,7 +1828,6 @@ sub bwa {
     }
 
   }
-  $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
@@ -1847,19 +1855,34 @@ sub income_statement {
                   $form, "pos_eur");
 
 
+  # add extra information to form to be used by template
+  my %charts_by_category =
+    partition_by { $_->{pos_eur} }
+    sort_by      { $_->{accno}   }
+    map          { $form->{charts}->{$_} }
+    keys %{ $form->{charts} };
+  $form->{"charts_by_category"} = \%charts_by_category;
+
+  $form->{"categories_income"}  = \@categories_einnahmen;
+  $form->{"categories_expense"} = \@categories_ausgaben;
+
+  $form->{category_names} = AM->get_eur_categories($myconfig, $form);
+
+  my %eur_amounts;
+
   foreach my $item (@categories_einnahmen) {
-    $form->{"eur${item}"} =
-      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
+    $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
     $form->{"sumeura"} += $form->{$item};
   }
   foreach my $item (@categories_ausgaben) {
-    $form->{"eur${item}"} =
-      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
+    $eur_amounts{$item} = $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
     $form->{"sumeurb"} += $form->{$item};
   }
 
   $form->{"guvsumme"} = $form->{"sumeura"} - $form->{"sumeurb"};
 
+  $form->{eur_amounts} = \%eur_amounts;
+
   foreach my $item (@ergebnisse) {
     $form->{$item} =
       $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2);
@@ -1873,16 +1896,13 @@ sub erfolgsrechnung {
   my ($self, $myconfig, $form) = @_;
   $form->{company} = $::instance_conf->get_company;
   $form->{address} = $::instance_conf->get_address;
-  #injection-filter
-  $form->{fromdate} =~ s/[^0-9\.]//g;
-  $form->{todate} =~ s/[^0-9\.]//g;
-  #input validation
-  $form->{fromdate} = "01.01.2000" if $form->{fromdate} !~ m/[0-9]*\.[0-9]*\.[0-9]*/;
-  $form->{todate} = $form->current_date(%{$myconfig}) if $form->{todate} !~ m/[0-9]*\.[0-9]*\.[0-9]*/;
+  $form->{fromdate} = DateTime->new(year => 2000, month => 1, day => 1)->to_kivitendo unless $form->{fromdate};
+  $form->{todate} = $form->current_date(%{$myconfig}) unless $form->{todate};
 
   my %categories = (I => "ERTRAG", E => "AUFWAND");
   my $fromdate = conv_dateq($form->{fromdate});
   my $todate = conv_dateq($form->{todate});
+  my $department_id = conv_i((split /--/, $form->{department})[1], 'NULL');
 
   $form->{total} = 0;
 
@@ -1890,18 +1910,18 @@ sub erfolgsrechnung {
     my %category = (
       name => $categories{$category},
       total => 0,
-      accounts => get_accounts_ch($category),
+      accounts => get_accounts_ch($category)
     );
     foreach my $account (@{$category{accounts}}) {
-      $account->{total} += ($account->{category} eq $category ? 1 : -1) * get_total_ch($account->{id}, $fromdate, $todate);
+      $account->{total} = get_total_ch($department_id, $account->{id}, $fromdate, $todate);
       $category{total} += $account->{total};
-      $account->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $account->{total}), 2);
+      $account->{total} = $form->format_amount($myconfig, $form->round_amount($account->{total}, 2), 2);
     }
     $form->{total} += $category{total};
-    $category{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $category{total}), 2);
+    $category{total} = $form->format_amount($myconfig, $form->round_amount($category{total}, 2), 2);
     push(@{$form->{categories}}, \%category);
   }
-  $form->{total} = $form->format_amount($myconfig, $form->parse_amount($myconfig, $form->{total}), 2);
+  $form->{total} = $form->format_amount($myconfig, $form->round_amount($form->{total}, 2), 2);
 
   $main::lxdebug->leave_sub();
   return {};
@@ -1911,12 +1931,12 @@ sub get_accounts_ch {
   $main::lxdebug->enter_sub();
 
   my ($category) = @_;
-  my ($inclusion);
+  my $inclusion = '' ;
 
   if ($category eq 'I') {
-    $inclusion = "AND pos_er = NULL OR pos_er > '0' AND pos_er <= '5'";
+    $inclusion = "AND pos_er = NULL OR pos_er = '1'";
   } elsif ($category eq 'E') {
-    $inclusion = "AND pos_er = NULL OR pos_er >= '6' AND pos_er < '100'";
+    $inclusion = "AND pos_er = NULL OR pos_er = '6'";
   } else {
     $inclusion = "";
   }
@@ -1924,10 +1944,10 @@ sub get_accounts_ch {
   my $query = qq|
     SELECT id, accno, description, category
     FROM chart
-    WHERE category = '$category' $inclusion
+    WHERE category = ? $inclusion
     ORDER BY accno
   |;
-  my $accounts = _query($query);
+  my $accounts = _query($query, $category);
 
   $main::lxdebug->leave_sub();
   return $accounts;
@@ -1936,16 +1956,25 @@ sub get_accounts_ch {
 sub get_total_ch {
   $main::lxdebug->enter_sub();
 
-  my ($chart_id, $fromdate, $todate) = @_;
+  my ($department_id, $chart_id, $fromdate, $todate) = @_;
   my $total = 0;
   my $query = qq|
     SELECT SUM(amount)
     FROM acc_trans
-    WHERE chart_id = '$chart_id'
-      AND transdate >= $fromdate
-      AND transdate <= $todate
+    WHERE chart_id = ?
+      AND transdate >= ?
+      AND transdate <= ?
   |;
-  $total += _query($query)->[0]->{sum};
+  if ($department_id) {
+    $query .= qq| AND COALESCE(
+        (SELECT department_id FROM ar WHERE ar.id=trans_id),
+        (SELECT department_id FROM gl WHERE gl.id=trans_id),
+        (SELECT department_id FROM ap WHERE ap.id=trans_id)
+    ) = ? |;
+    $total += _query($query, $chart_id, $fromdate, $todate, $department_id)->[0]->{sum};
+  } else {
+    $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum};
+  }
 
   $main::lxdebug->leave_sub();
   return $total;