X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=865bfae6aa9e408e2b51ec39c2dfccc0e8b5a75a;hb=5dd059ffbe9317acf466ad70610513ded8ad1c6f;hp=8b3ca5f96c4bdd1ddb7ff6361547e27f955ba180;hpb=981b670e29449b7ebffe6c910439a05e6613ec6b;p=kivitendo-erp.git

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 8b3ca5f96..865bfae6a 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -215,7 +215,8 @@ sub fa_auswahl {
       $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
     }
     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
-                   keys(%elster_land_fa)) {
+                   keys(%elster_land_fa)
+      ) {
       print qq|
                    elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
       $j++;
@@ -268,7 +269,8 @@ sub fa_auswahl {
     print qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
   } else {
     foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
-                   keys(%elster_land_fa)) {
+                   keys(%elster_land_fa)
+      ) {
 
       print qq|
                         <option value="$ffff"|;
@@ -556,7 +558,6 @@ sub process_query {
   $main::lxdebug->leave_sub();
 }
 
-
 sub ustva {
   $main::lxdebug->enter_sub();
 
@@ -567,12 +568,12 @@ sub ustva {
 
   my $last_period     = 0;
   my $category        = "pos_ustva";
-  my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74 
-                           85 65 66 61 62 67 63 64 59 69 39 83 
-                           Z43 Z45 Z53 Z62 Z65 Z67);
-                           
-  my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93 
-                           95 94 42 60 45 52 73 84);
+  my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74
+    85 65 66 61 62 67 63 64 59 69 39 83
+    Z43 Z45 Z53 Z62 Z65 Z67);
+
+  my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93
+    95 94 42 60 45 52 73 84);
 
   $form->{decimalplaces} *= 1;
 
@@ -583,10 +584,8 @@ sub ustva {
     $form->{"$item"} = 0;
   }
 
-
   &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
-                  $form, $category);
-
+                      $form, $category);
 
   #
   # Berechnung der USTVA Formularfelder
@@ -595,27 +594,27 @@ sub ustva {
   $form->{"86r"} = $form->{"861"};
   $form->{"97r"} = $form->{"971"};
   $form->{"93r"} = $form->{"931"};
-  $form->{"Z43"} = $form->{"51r"}+ $form->{"86r"} + 
-                   $form->{"36"} + $form->{"80"}  +
-                   $form->{"97r"}+ $form->{"93r"} + 
-                   $form->{"96"} + $form->{"98"};
+  $form->{"Z43"} =
+    $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} +
+    $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
   $form->{"Z45"} = $form->{"Z43"};
   $form->{"Z53"} = $form->{"Z43"};
-  $form->{"Z62"} = $form->{"Z43"}- $form->{"66"} -
-                   $form->{"61"} - $form->{"62"} -
-                   $form->{"63"} - $form->{"64"} -
-                   $form->{"59"};
-  $form->{"Z65"} = $form->{"Z62"}- $form->{"69"};
-  $form->{"83"}  = $form->{"Z65"}- $form->{"39"};
-  
+  $form->{"Z62"} =
+    $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
+    $form->{"63"} - $form->{"64"} - $form->{"59"};
+  $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
+  $form->{"83"}  = $form->{"Z65"} - $form->{"39"};
+
   foreach $item (@categories_cent) {
     $form->{$item} =
-      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), 2, '0');
+      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
+                           2, '0');
   }
 
   foreach $item (@categories_euro) {
     $form->{$item} =
-      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), 0, '0');
+      $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
+                           0, '0');
   }
 
   $dbh->disconnect;
@@ -637,7 +636,8 @@ sub get_accounts_ustva {
   my $where    = "1 = 1";
   my $glwhere  = "";
   my $subwhere = "";
-  my $ARwhere = "";
+  my $ARwhere  = "";
+  my $arwhere  = "";
   my $item;
 
   if ($fromdate) {
@@ -645,14 +645,13 @@ sub get_accounts_ustva {
       $subwhere .= " AND transdate >= '$fromdate'";
       $glwhere = " AND ac.transdate >= '$fromdate'";
       $ARwhere .= " AND acc.transdate >= '$fromdate'";
-      $where .= " AND ac.transdate >= '$fromdate'";
     }
+    $where .= " AND ac.transdate >= '$fromdate'";
   }
 
   if ($todate) {
     $where    .= " AND ac.transdate <= '$todate'";
-    $ARwhere    .= " AND acc.transdate <= '$todate'";
-    $arwhere    .= " AND ac.transdate <= '$todate'";    
+    $ARwhere  .= " AND acc.transdate <= '$todate'";
     $subwhere .= " AND transdate <= '$todate'";
   }
 
@@ -695,10 +694,11 @@ sub get_accounts_ustva {
    c.pos_ustva
    FROM acc_trans ac
    JOIN chart c ON (c.id = ac.chart_id)
-   JOIN ar ON (ar.id = ac.trans_id)
+   --JOIN ar ON (ar.id = ac.trans_id)
    where 
      1=1 
-     $arwhere
+     -- Here no where, please. All Transactions ever should be
+     -- testet if they are paied in the USTVA report period.
    GROUP BY c.pos_ustva
 	UNION
 
@@ -715,7 +715,7 @@ sub get_accounts_ustva {
 		     SELECT trans_id
 		     FROM acc_trans
 		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AP_paid%'
+		     WHERE link LIKE '%AP_amount%'
 		     $subwhere
 		   )
 
@@ -724,14 +724,17 @@ sub get_accounts_ustva {
 
         UNION
 
-		 SELECT sum(ac.amount) AS amount,
+		 SELECT sum(
+		   CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
+		        WHEN c.link LIKE '%AP%' THEN ac.amount * 1
+                   END
+		 ) AS amount,
 		 c.$category
 		 FROM acc_trans ac
 		 JOIN chart c ON (c.id = ac.chart_id)
 		 JOIN gl a ON (a.id = ac.trans_id)
 		 $dpt_join
 		 WHERE $where
-		 $glwhere
 		 $dpt_from
 		 AND NOT (c.link = 'AR' OR c.link = 'AP')
 		 $project
@@ -739,61 +742,6 @@ sub get_accounts_ustva {
 
 		 |;
 
-    if ($form->{project_id}) {
-
-      $query .= qq|
-
-	 UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) AS amount,
-		 c.$category
-		 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)
-	         $dpt_join
-	-- use transdate from subwhere
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'I'
-		 $dpt_where
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AR_paid%'
-		     $subwhere
-		   )
-
-		 $project
-		 GROUP BY c.$category
-
-	 UNION
-
-		 SELECT SUM(ac.sellprice) AS amount,
-		 c.$category
-		 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)
-	         $dpt_join
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'E'
-		 $dpt_where
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AP_paid%'
-		     $subwhere
-		   )
-
-		 $project
-		 GROUP BY c.$category
-		 |;
-    }
-
   } else {
 
     if ($department_id) {
@@ -807,7 +755,12 @@ sub get_accounts_ustva {
 
     $query = qq|
 
-		 SELECT sum(ac.amount) AS amount,
+		 SELECT sum(
+		   CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
+		        WHEN c.link LIKE '%AP%' THEN ac.amount * 1
+                   END		 
+		 
+		 ) AS amount,
 		 c.$category
 		 FROM acc_trans ac
 		 JOIN chart c ON (c.id = ac.chart_id)
@@ -817,57 +770,24 @@ sub get_accounts_ustva {
 		 $project
 		 GROUP BY c.$category
 		 |;
-
-    if ($form->{project_id}) {
-
-      $query .= qq|
-
-	UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) AS amount,
-		 c.$category
-		 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)
-	         $dpt_join
-	-- use transdate from subwhere
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'I'
-		 $dpt_where
-		 $project
-		 GROUP BY c.$category
-
-	UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
-		 c.$category
-		 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)
-	         $dpt_join
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'E'
-		 $dpt_where
-		 $project
-		 GROUP BY c.$category
-		 |;
-
-    }
   }
 
   my @accno;
   my $accno;
   my $ref;
-  #print $query;
+
+  # Show all $query in Debuglevel LXDebug::QUERY
+  $callingdetails = (caller (0))[3];
+  $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
+              
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    if ($ref->{amount} < 0) {
+# Bug 365 solved?!
+#    if ($ref->{amount} < 0) {
       $ref->{amount} *= -1;
-    }
+#    }
     if ($category eq "pos_bwa") {
       if ($last_period) {
         $form->{ $ref->{$category} }{kumm} += $ref->{amount};
@@ -883,5 +803,4 @@ sub get_accounts_ustva {
   $main::lxdebug->leave_sub();
 }
 
-
 1;