epic-ts

[mfinanz.git] / SL / RP.pm

diff --git a/SL/RP.pm b/SL/RP.pm
index ef43307a66c39dcf81626698ff56de9f7cad94fd..7b1b01bc6f3ed475a419325e9c14be0a067d74d1 100644 (file)
--- a/SL/RP.pm
+++ b/SL/RP.pm
@@ -311,7 +311,8 @@ sub get_accounts {
          FROM invoice ac
          JOIN ar a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
          FROM invoice ac
          JOIN ar a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)

-         JOIN chart c on (p.income_accno_id = c.id)
+         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+         JOIN chart c on (t.income_accno_id = c.id)

          -- use transdate from subwhere
          WHERE (c.category = 'I')
            $subwhere
          -- use transdate from subwhere
          WHERE (c.category = 'I')
            $subwhere


@@ -332,7 +333,8 @@ sub get_accounts {
          FROM invoice ac
          JOIN ap a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
          FROM invoice ac
          JOIN ap a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)

-         JOIN chart c on (p.expense_accno_id = c.id)
+         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+         JOIN chart c on (t.expense_accno_id = c.id)

          WHERE (c.category = 'E')
            $subwhere
            $dpt_where
          WHERE (c.category = 'E')
            $subwhere
            $dpt_where


@@ -374,7 +376,8 @@ sub get_accounts {
       FROM invoice ac
       JOIN ar a ON (a.id = ac.trans_id)
       JOIN parts p ON (ac.parts_id = p.id)
       FROM invoice ac
       JOIN ar a ON (a.id = ac.trans_id)
       JOIN parts p ON (ac.parts_id = p.id)

-      JOIN chart c on (p.income_accno_id = c.id)
+      JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+      JOIN chart c on (t.income_accno_id = c.id)

       -- use transdate from subwhere
       WHERE (c.category = 'I')
         $subwhere
       -- use transdate from subwhere
       WHERE (c.category = 'I')
         $subwhere


@@ -388,7 +391,8 @@ sub get_accounts {
       FROM invoice ac
       JOIN ap a ON (a.id = ac.trans_id)
       JOIN parts p ON (ac.parts_id = p.id)
       FROM invoice ac
       JOIN ap a ON (a.id = ac.trans_id)
       JOIN parts p ON (ac.parts_id = p.id)

-      JOIN chart c on (p.expense_accno_id = c.id)
+      JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+      JOIN chart c on (t.expense_accno_id = c.id)

       WHERE (c.category = 'E')
         $subwhere
         $dpt_where
       WHERE (c.category = 'E')
         $subwhere
         $dpt_where


@@ -572,7 +576,8 @@ sub get_accounts_g {
          FROM invoice ac
          JOIN ar a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
          FROM invoice ac
          JOIN ar a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)

-         JOIN chart c on (p.income_accno_id = c.id)
+         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+         JOIN chart c on (t.income_accno_id = c.id)

          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
          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


@@ -584,7 +589,8 @@ sub get_accounts_g {
          FROM invoice ac
          JOIN ap a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)
          FROM invoice ac
          JOIN ap a ON (a.id = ac.trans_id)
          JOIN parts p ON (ac.parts_id = p.id)

-         JOIN chart c on (p.expense_accno_id = c.id)
+         JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+         JOIN chart c on (t.expense_accno_id = c.id)

          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
          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


@@ -617,7 +623,8 @@ sub get_accounts_g {
         FROM invoice ac
         JOIN ar a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)
         FROM invoice ac
         JOIN ar a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)

-        JOIN chart c on (p.income_accno_id = c.id)
+        JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+        JOIN chart c on (t.income_accno_id = c.id)

         WHERE (c.category = 'I')
           $prwhere
           $dpt_where
         WHERE (c.category = 'I')
           $prwhere
           $dpt_where


@@ -630,7 +637,8 @@ sub get_accounts_g {
         FROM invoice ac
         JOIN ap a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)
         FROM invoice ac
         JOIN ap a ON (a.id = ac.trans_id)
         JOIN parts p ON (ac.parts_id = p.id)

-        JOIN chart c on (p.expense_accno_id = c.id)
+        JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+        JOIN chart c on (t.expense_accno_id = c.id)

         WHERE (c.category = 'E')
           $prwhere
           $dpt_where
         WHERE (c.category = 'E')
           $prwhere
           $dpt_where


@@ -891,7 +899,8 @@ sub trial_balance {
       FROM invoice ac
       JOIN ar a ON (ac.trans_id = a.id)
       JOIN parts p ON (ac.parts_id = p.id)
       FROM invoice ac
       JOIN ar a ON (ac.trans_id = a.id)
       JOIN parts p ON (ac.parts_id = p.id)

-      JOIN chart c ON (p.income_accno_id = c.id)
+      JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+      JOIN chart c ON (t.income_accno_id = c.id)

       WHERE $invwhere
         $dpt_where
         $customer_where
       WHERE $invwhere
         $dpt_where
         $customer_where


@@ -904,7 +913,8 @@ sub trial_balance {
       FROM invoice ac
       JOIN ap a ON (ac.trans_id = a.id)
       JOIN parts p ON (ac.parts_id = p.id)
       FROM invoice ac
       JOIN ap a ON (ac.trans_id = a.id)
       JOIN parts p ON (ac.parts_id = p.id)

-      JOIN chart c ON (p.expense_accno_id = c.id)
+      JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+      JOIN chart c ON (t.expense_accno_id = c.id)

       WHERE $invwhere
         $dpt_where
         $customer_no_union
       WHERE $invwhere
         $dpt_where
         $customer_no_union


@@ -1017,7 +1027,8 @@ sub trial_balance {
            FROM invoice ac
            JOIN parts p ON (ac.parts_id = p.id)
            JOIN ap a ON (ac.trans_id = a.id)
            FROM invoice ac
            JOIN parts p ON (ac.parts_id = p.id)
            JOIN ap a ON (ac.trans_id = a.id)

-           JOIN chart c ON (p.expense_accno_id = c.id)
+           JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+           JOIN chart c ON (t.expense_accno_id = c.id)

            WHERE $invwhere
              $dpt_where
              $customer_no_union
            WHERE $invwhere
              $dpt_where
              $customer_no_union


@@ -1028,7 +1039,8 @@ sub trial_balance {
            FROM invoice ac
            JOIN parts p ON (ac.parts_id = p.id)
            JOIN ar a ON (ac.trans_id = a.id)
            FROM invoice ac
            JOIN parts p ON (ac.parts_id = p.id)
            JOIN ar a ON (ac.trans_id = a.id)

-           JOIN chart c ON (p.income_accno_id = c.id)
+           JOIN taxzone_charts t ON (p.buchungsgruppen_id = t.id)
+           JOIN chart c ON (t.income_accno_id = c.id)

            WHERE $invwhere
              $dpt_where
              $customer_where
            WHERE $invwhere
              $dpt_where
              $customer_where


@@ -1194,19 +1206,6 @@ sub trial_balance {
   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 

-sub get_storno {
-  $main::lxdebug->enter_sub();
-  my ($self, $dbh, $form) = @_;
-  my $arap = $form->{arap} eq "ar" ? "ar" : "ap";
-  my $query = qq|SELECT invnumber FROM $arap WHERE invnumber LIKE "Storno zu "|;
-  my $sth =  $dbh->prepare($query);
-  while(my $ref = $sth->fetchrow_hashref()) {
-    $ref->{invnumer} =~ s/Storno zu //g;
-    $form->{storno}{$ref->{invnumber}} = 1;
-  }
-  $main::lxdebug->leave_sub();
-}
-

 sub aging {
   $main::lxdebug->enter_sub();
 
 sub aging {
   $main::lxdebug->enter_sub();
 


@@ -1902,6 +1901,7 @@ sub erfolgsrechnung {
   my %categories = (I => "ERTRAG", E => "AUFWAND");
   my $fromdate = conv_dateq($form->{fromdate});
   my $todate = conv_dateq($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;
 
 
   $form->{total} = 0;
 


@@ -1909,18 +1909,18 @@ sub erfolgsrechnung {
     my %category = (
       name => $categories{$category},
       total => 0,
     my %category = (
       name => $categories{$category},
       total => 0,

-      accounts => get_accounts_ch($category),
+      accounts => get_accounts_ch($category)

     );
     foreach my $account (@{$category{accounts}}) {
     );
     foreach my $account (@{$category{accounts}}) {

-      $account->{total} += get_total_ch($account->{id}, $fromdate, $todate);
+      $account->{total} = get_total_ch($department_id, $account->{id}, $fromdate, $todate);

       $category{total} += $account->{total};
       $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};
     }
     $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);
   }
     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 {};
 
   $main::lxdebug->leave_sub();
   return {};


@@ -1930,7 +1930,7 @@ sub get_accounts_ch {
   $main::lxdebug->enter_sub();
 
   my ($category) = @_;
   $main::lxdebug->enter_sub();
 
   my ($category) = @_;

-  my ($inclusion);
+  my $inclusion = '' ;

 
   if ($category eq 'I') {
     $inclusion = "AND pos_er = NULL OR pos_er = '1'";
 
   if ($category eq 'I') {
     $inclusion = "AND pos_er = NULL OR pos_er = '1'";


@@ -1955,7 +1955,7 @@ sub get_accounts_ch {
 sub get_total_ch {
   $main::lxdebug->enter_sub();
 
 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)
   my $total = 0;
   my $query = qq|
     SELECT SUM(amount)


@@ -1964,7 +1964,16 @@ sub get_total_ch {
       AND transdate >= ?
       AND transdate <= ?
   |;
       AND transdate >= ?
       AND transdate <= ?
   |;

-  $total += _query($query, $chart_id, $fromdate, $todate)->[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;
 
   $main::lxdebug->leave_sub();
   return $total;