X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=51296f1bedf1f938a64f3d11e60eeacefda8a947;hb=00c278b5eebfd2b245a9d75021126af809318f93;hp=ff63556352f7282f8ba0f48c57ec8c081914f796;hpb=0576299f2aa98125991c4ffcd2f75073ea583d7d;p=kivitendo-erp.git

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index ff6355635..51296f1be 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,4 +558,333 @@ sub process_query {
   $main::lxdebug->leave_sub();
 }
 
+sub ustva {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  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);
+
+  $form->{decimalplaces} *= 1;
+
+  foreach $item (@categories_cent) {
+    $form->{"$item"} = 0;
+  }
+  foreach $item (@categories_euro) {
+    $form->{"$item"} = 0;
+  }
+
+  &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
+                      $form, $category);
+
+  #
+  # Berechnung der USTVA Formularfelder
+  #
+  $form->{"51r"} = $form->{"511"};
+  $form->{"86r"} = $form->{"861"};
+  $form->{"97r"} = $form->{"971"};
+  $form->{"93r"} = $form->{"931"};
+  $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"};
+
+  foreach $item (@categories_cent) {
+    $form->{$item} =
+      $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');
+  }
+
+  $dbh->disconnect;
+
+  $main::lxdebug->leave_sub();
+}
+
+sub get_accounts_ustva {
+  $main::lxdebug->enter_sub();
+
+  my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
+
+  my ($null, $department_id) = split /--/, $form->{department};
+
+  my $query;
+  my $dpt_where;
+  my $dpt_join;
+  my $project;
+  my $where    = "1 = 1";
+  my $glwhere  = "";
+  my $subwhere = "";
+  my $ARwhere  = "";
+  my $arwhere  = "";
+  my $item;
+
+  if ($fromdate) {
+    if ($form->{method} eq 'cash') {
+      $subwhere .= " AND transdate >= '$fromdate'";
+      $glwhere = " AND ac.transdate >= '$fromdate'";
+      $ARwhere .= " AND acc.transdate >= '$fromdate'";
+    }
+    $where .= " AND ac.transdate >= '$fromdate'";
+  }
+
+  if ($todate) {
+    $where    .= " AND ac.transdate <= '$todate'";
+    $ARwhere  .= " AND acc.transdate <= '$todate'";
+    $subwhere .= " AND transdate <= '$todate'";
+  }
+
+  if ($department_id) {
+    $dpt_join = qq|
+               JOIN department t ON (a.department_id = t.id)
+		  |;
+    $dpt_where = qq|
+               AND t.id = $department_id
+	           |;
+  }
+
+  if ($form->{project_id}) {
+    $project = qq|
+                 AND ac.project_id = $form->{project_id}
+		 |;
+  }
+
+  if ($form->{method} eq 'cash') {
+
+    $query = qq|
+
+ SELECT
+   SUM( ac.amount *
+      -- Bezahlt / Rechnungssumme
+     ( 
+       SELECT SUM(acc.amount)
+       FROM acc_trans acc
+       INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
+       WHERE
+        1=1 
+        $ARwhere
+        AND acc.trans_id = ac.trans_id
+        )
+     / 
+     ( 
+      select amount from ar where id = ac.trans_id  
+     )
+   ) AS amount,
+   c.pos_ustva
+   FROM acc_trans ac
+   JOIN chart c ON (c.id = ac.chart_id)
+   --JOIN ar ON (ar.id = ac.trans_id)
+   where 
+     1=1 
+     -- 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
+
+	         SELECT sum(ac.amount) AS amount,
+		 c.$category
+		 FROM acc_trans ac
+		 JOIN chart c ON (c.id = ac.chart_id)
+		 JOIN ap a ON (a.id = ac.trans_id)
+		 $dpt_join
+		 WHERE $where
+		 $dpt_where
+		 AND ac.trans_id IN
+		   (
+		     SELECT trans_id
+		     FROM acc_trans
+		     JOIN chart ON (chart_id = id)
+		     WHERE link LIKE '%AP_amount%'
+		     $subwhere
+		   )
+
+		 $project
+		 GROUP BY c.$category
+
+        UNION
+
+		 SELECT sum(ac.amount) 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
+		 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
+		 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) {
+      $dpt_join = qq|
+	      JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
+	      |;
+      $dpt_where = qq|
+               AND t.department_id = $department_id
+	      |;
+    }
+
+    $query = qq|
+
+		 SELECT sum(ac.amount) AS amount,
+		 c.$category
+		 FROM acc_trans ac
+		 JOIN chart c ON (c.id = ac.chart_id)
+		 $dpt_join
+		 WHERE $where
+		 $dpt_where
+		 $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;
+
+  # 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) {
+      $ref->{amount} *= -1;
+    }
+    if ($category eq "pos_bwa") {
+      if ($last_period) {
+        $form->{ $ref->{$category} }{kumm} += $ref->{amount};
+      } else {
+        $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
+      }
+    } else {
+      $form->{ $ref->{$category} } += $ref->{amount};
+    }
+  }
+  $sth->finish;
+
+  $main::lxdebug->leave_sub();
+}
+
 1;