X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=a2703b64f656fc8e93e6326afbb6eba81f41eae1;hb=9fa58e1b0d129470537be4808bf5d40f7c0047d9;hp=670710224bba1df505fadd7de0677cd55693745a;hpb=b3501bdfd1971fd17d06e47cdf994c7545c3c13c;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index 670710224..a2703b64f 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -34,6 +34,7 @@
 
 package IC;
 use Data::Dumper;
+use SL::DBUtils;
 
 sub get_part {
   $main::lxdebug->enter_sub();
@@ -53,9 +54,10 @@ sub get_part {
 		 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
 		 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
 		 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-                 WHERE p.id = $form->{id}|;
+                 WHERE p.id = ? |;
+  my @vars = ($form->{id});
   my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
   my $ref = $sth->fetchrow_hashref(NAME_lc);
 
   # copy to $form variables
@@ -78,12 +80,12 @@ sub get_part {
                 FROM parts p
 		JOIN assembly a ON (a.parts_id = p.id)
 		LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-		WHERE a.id = $form->{id}
-		ORDER BY $oid{$myconfig->{dbdriver}}|;
-
+		WHERE a.id = ?
+		ORDER BY ?|;
+    @vars = ($form->{id}, $oid{$myconfig->{dbdriver}});
     $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
+    $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
+    
     $form->{assembly_rows} = 0;
     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
       $form->{assembly_rows}++;
@@ -105,11 +107,12 @@ sub get_part {
   # get prices
   $query =
     qq|SELECT p.parts_id, p.pricegroup_id, p.price, (SELECT pg.pricegroup FROM pricegroup pg WHERE pg.id=p.pricegroup_id) AS pricegroup FROM prices p
-              WHERE parts_id = $form->{id}
+              WHERE parts_id = ? 
               ORDER by pricegroup|;
 
+  @vars = ($form->{id});
   $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
 
   @pricegroups          = ();
   @pricegroups_not_used = ();
@@ -177,10 +180,10 @@ sub get_part {
     # get makes
     if ($form->{makemodel}) {
       $query = qq|SELECT m.make, m.model FROM makemodel m
-                  WHERE m.parts_id = $form->{id}|;
-
+                  WHERE m.parts_id = ?|;
+      @vars = ($form->{id});
       $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
+      $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
 
       my $i = 1;
       while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
@@ -195,9 +198,10 @@ sub get_part {
 
   # get translations
   $form->{language_values} = "";
-  $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = $form->{id}|;
+  $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
+  @vars = ($form->{id});
   $trq = $dbh->prepare($query);
-  $trq->execute || $form->dberror($query);
+  $trq->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
   while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
     $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
   }
@@ -310,7 +314,8 @@ sub retrieve_buchungsgruppen {
 
   # get buchungsgruppen
   $query = qq|SELECT id, description
-              FROM buchungsgruppen|;
+              FROM buchungsgruppen
+              ORDER BY sortkey|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -327,16 +332,6 @@ sub save {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form) = @_;
-  $form->{IC_expense} = "1000";
-  $form->{IC_income} = "2000";
-
-  if ($form->{item} ne 'service') {
-    $form->{IC} = $form->{IC_expense};
-  }
-
-  ($form->{inventory_accno}) = split(/--/, $form->{IC});
-  ($form->{expense_accno})   = split(/--/, $form->{IC_expense});
-  ($form->{income_accno})    = split(/--/, $form->{IC_income});
 
   # connect to database, turn off AutoCommit
   my $dbh = $form->dbconnect_noauto($myconfig);
@@ -458,10 +453,10 @@ sub save {
 
     $form->{orphaned} = 1;
     $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
-    if ($form->{partnumber} eq "" && $form->{inventory_accno} eq "") {
+    if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
       $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
     }
-    if ($form->{partnumber} eq "" && $form->{inventory_accno} ne "") {
+    if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
       $form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
     }
 
@@ -472,6 +467,30 @@ sub save {
     ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
   }
 
+  my ($subq_inventory, $subq_expense, $subq_income);
+  if ($form->{"item"} eq "part") {
+    $subq_inventory =
+      qq|(SELECT bg.inventory_accno_id | .
+      qq| FROM buchungsgruppen bg | .
+      qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+  } else {
+    $subq_inventory = "NULL";
+  }
+
+  if ($form->{"item"} ne "assembly") {
+    $subq_expense =
+      qq|(SELECT bg.expense_accno_id_0 | .
+      qq| FROM buchungsgruppen bg | .
+      qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+  } else {
+    $subq_expense = "NULL";
+  }
+
+  $subq_income =
+    qq|(SELECT bg.income_accno_id_0 | .
+    qq| FROM buchungsgruppen bg | .
+    qq| WHERE bg.id = | . $dbh->quote($form->{"buchungsgruppen_id"}) . qq|)|;
+
   $query = qq|UPDATE parts SET
 	      partnumber = '$form->{partnumber}',
 	      description = '$form->{description}',
@@ -490,18 +509,16 @@ sub save {
 	      bin = '$form->{bin}',
 	      buchungsgruppen_id = '$form->{buchungsgruppen_id}',
 	      payment_id = '$form->{payment_id}',
-	      inventory_accno_id = (SELECT c.id FROM chart c
-				    WHERE c.accno = '$form->{inventory_accno}'),
-	      income_accno_id = (SELECT c.id FROM chart c
-				 WHERE c.accno = '$form->{income_accno}'),
-	      expense_accno_id = (SELECT c.id FROM chart c
-				  WHERE c.accno = '$form->{expense_accno}'),
+	      inventory_accno_id = $subq_inventory,
+	      income_accno_id = $subq_income,
+	      expense_accno_id = $subq_expense,
               obsolete = '$form->{obsolete}',
 	      image = '$form->{image}',
 	      drawing = '$form->{drawing}',
 	      shop = '$form->{shop}',
               ve = '$form->{ve}',
               gv = '$form->{gv}',
+              ean = '$form->{ean}',
               not_discountable = '$form->{not_discountable}',
 	      microfiche = '$form->{microfiche}',
 	      partsgroup_id = $partsgroup_id
@@ -933,6 +950,11 @@ sub all_parts {
     }
   }
 
+  if ($form->{ean}) {
+    $var = $form->like(lc $form->{ean});
+    $where .= " AND lower(ean) LIKE '$var'";
+  }
+  
   if ($form->{searchitems} eq 'part') {
     $where .= " AND p.inventory_accno_id > 0";
   }
@@ -1127,7 +1149,7 @@ sub all_parts {
 		 p.priceupdate, p.image, p.drawing, p.microfiche,
 		 pg.partsgroup,
 		 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
-		 ct.name|;
+		 ct.name, NULL AS deliverydate|;
 
       if ($form->{ordered}) {
         $query .= qq|$union
@@ -1154,7 +1176,7 @@ sub all_parts {
 		   p.priceupdate, p.image, p.drawing, p.microfiche,
 		   pg.partsgroup,
 		   '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
-		   ct.name|;
+		   ct.name, NULL AS deliverydate|;
 
         $query .= qq|$union
 	            SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
@@ -1192,7 +1214,7 @@ sub all_parts {
 		 p.priceupdate, p.image, p.drawing, p.microfiche,
 		 pg.partsgroup,
 		 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
-		 ct.name|;
+		 ct.name, NULL AS deliverydate|;
 
       if ($form->{quoted}) {
         $query .= qq|$union
@@ -1219,7 +1241,7 @@ sub all_parts {
 		   p.priceupdate, p.image, p.drawing, p.microfiche,
 		   pg.partsgroup,
 		   '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
-		   ct.name|;
+		   ct.name, NULL AS deliverydate|;
 
         $query .= qq|$union
 	            SELECT $flds, 'oe' AS module, 'request_quotation' AS type,
@@ -1510,7 +1532,7 @@ sub create_links {
   # get payment terms
   $query = qq|SELECT id, description
               FROM payment_terms
-	      ORDER BY 1|;
+              ORDER BY sortkey|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -1519,22 +1541,12 @@ sub create_links {
   }
   $sth->finish;
 
-  if ($form->{id}) {
-    $query = qq|SELECT weightunit
-                FROM defaults|;
+  if (!$form->{id}) {
+    $query = qq|SELECT current_date FROM defaults|;
     $sth = $dbh->prepare($query);
     $sth->execute || $form->dberror($query);
 
-    ($form->{weightunit}) = $sth->fetchrow_array;
-    $sth->finish;
-
-  } else {
-    $query = qq|SELECT weightunit, current_date
-                FROM defaults|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    ($form->{weightunit}, $form->{priceupdate}) = $sth->fetchrow_array;
+    ($form->{priceupdate}) = $sth->fetchrow_array;
     $sth->finish;
   }
 
@@ -1733,14 +1745,46 @@ sub retrieve_languages {
 
 }
 
-sub retrieve_taxaccounts {
-  $main::lxdebug->enter_sub();
+sub follow_account_chain {
+  $main::lxdebug->enter_sub(2);
+
+  my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
+
+  my @visited_accno_ids = ($accno_id);
+
+  my ($query, $sth);
+
+  $query =
+    "SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, " .
+    "  cnew.accno " .
+    "FROM chart c " .
+    "LEFT JOIN chart cnew ON c.new_chart_id = cnew.id " .
+    "WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)";
+  $sth = $dbh->prepare($query);
+
+  while (1) {
+    $sth->execute($accno_id) || $form->dberror($query . " ($accno_id)");
+    $ref = $sth->fetchrow_hashref();
+    last unless ($ref && $ref->{"is_valid"} &&
+                 !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
+    $accno_id = $ref->{"new_chart_id"};
+    $accno = $ref->{"accno"};
+    push(@visited_accno_ids, $accno_id);
+  }
+
+  $main::lxdebug->leave_sub(2);
+
+  return ($accno_id, $accno);
+}
+
+sub retrieve_accounts {
+  $main::lxdebug->enter_sub(2);
 
   my ($self, $myconfig, $form, $parts_id, $index, $copy_accnos) = @_;
 
   my ($query, $sth, $dbh);
 
-  return $main::lxdebug->leave_sub() if (!defined($form->{"taxzone_id"}));
+  $form->{"taxzone_id"} *= 1;
 
   $dbh = $form->dbconnect($myconfig);
 
@@ -1751,6 +1795,8 @@ sub retrieve_taxaccounts {
     } else {
       $transdate = $form->{deliverydate};
     }
+  } elsif ($form->{type} eq "credit_note") {
+    $transdate = $form->{invdate};
   } else {
     $transdate = $form->{transdate};
   }
@@ -1761,49 +1807,49 @@ sub retrieve_taxaccounts {
     $transdate = $dbh->quote($transdate);
   }
 
-  my $inc_exp = $form->{vc} eq "customer" ? "income" : "expense";
-
-  my $accno_str = "${inc_exp}_accno_id_$form->{taxzone_id}";
-
   $query =
-    "SELECT bg.$accno_str AS accno_id, c.accno " .
-    "FROM buchungsgruppen bg " .
-    "LEFT JOIN chart c ON bg.$accno_str = c.id " .
-    "WHERE bg.id = (SELECT p.buchungsgruppen_id FROM parts p WHERE p.id = ?)";
+    "SELECT " .
+    "  p.inventory_accno_id AS is_part, " .
+    "  bg.inventory_accno_id, " .
+    "  bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, " .
+    "  bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, " .
+    "  c1.accno AS inventory_accno, " .
+    "  c2.accno AS income_accno, " .
+    "  c3.accno AS expense_accno " .
+    "FROM parts p " .
+    "LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id " .
+    "LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id " .
+    "LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id " .
+    "LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id " .
+    "WHERE p.id = ?";
   $sth = $dbh->prepare($query);
   $sth->execute($parts_id) || $form->dberror($query . " ($parts_id)");
   my $ref = $sth->fetchrow_hashref();
   $sth->finish();
 
+#   $main::lxdebug->message(0, "q $query");
+
   if (!$ref) {
     $dbh->disconnect();
-    return $lxdebug->leave_sub();
+    return $main::lxdebug->leave_sub(2);
   }
 
-  my ($accno_id, $accno) = ($ref->{"accno_id"}, $ref->{"accno"});
-  my ($old_accno_id, $old_accno) = ($ref->{"accno_id"}, $ref->{"accno"});
-
-  my @visited_accno_ids = ($accno_id);
-
-  $query =
-    "SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, " .
-    "  cnew.accno " .
-    "FROM chart c " .
-    "LEFT JOIN chart cnew ON c.new_chart_id = cnew.id " .
-    "WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)";
-  $sth = $dbh->prepare($query);
+  $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
 
-  while (1) {
-    $sth->execute($accno_id) || $form->dberror($query . " ($accno_id)");
-    $ref = $sth->fetchrow_hashref();
-    last unless ($ref && $ref->{"is_valid"} &&
-                 !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
-    $accno_id = $ref->{"new_chart_id"};
-    $accno = $ref->{"accno"};
-    push(@visited_accno_ids, $accno_id);
+  my %accounts;
+  foreach my $type (qw(inventory income expense)) {
+    next unless ($ref->{"${type}_accno_id"});
+    ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
+      $self->follow_account_chain($form, $dbh, $transdate,
+                                  $ref->{"${type}_accno_id"},
+                                  $ref->{"${type}_accno"});
   }
 
-#   $main::lxdebug->message(0, "found final accno_id $accno_id accno $accno for old accno_id $old_accno_id accno $old_accno");
+  map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
+      qw(inventory income expense));
+
+  my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
+  my $accno_id = $accounts{"${inc_exp}_accno_id"};
 
   $query =
     "SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber " .
@@ -1812,15 +1858,19 @@ sub retrieve_taxaccounts {
     "WHERE t.id IN " .
     "  (SELECT tk.tax_id " .
     "   FROM taxkeys tk " .
-    "   WHERE tk.chart_id = $accno_id AND startdate <= $transdate " .
+    "   WHERE tk.chart_id = ? AND startdate <= " . quote_db_date($transdate) .
     "   ORDER BY startdate DESC LIMIT 1) ";
+  @vars = ($accno_id);
   $sth = $dbh->prepare($query);
-  $sth->execute() || $form->dberror($query);
+  $sth->execute(@vars) || $form->dberror("$query (" . join(', ', @vars) . ")");
   $ref = $sth->fetchrow_hashref();
   $sth->finish();
   $dbh->disconnect();
 
-  return $lxdebug->leave_sub() unless ($ref);
+  unless ($ref) {
+    $main::lxdebug->leave_sub(2);
+    return;
+  }
 
   $form->{"taxaccounts_$index"} = $ref->{"accno"};
   if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
@@ -1835,8 +1885,7 @@ sub retrieve_taxaccounts {
 #                           " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
 #                           " || taxaccounts " . $form->{"taxaccounts"});
 
-  $sth->finish();
-
-  $main::lxdebug->leave_sub();
+  $main::lxdebug->leave_sub(2);
 }
+
 1;