X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=fece79579b246e721289195d3a1c279450bae71f;hb=af59820cb06ba396ba10ecd88f43a92c331a6066;hp=8520f5091d94a681ac0a0d8c044e39a375847efc;hpb=29795499e0623f7ff7ea739ed3b61d66dad0e7c6;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index 8520f5091..fece79579 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -33,7 +33,10 @@
 #======================================================================
 
 package IC;
+
 use Data::Dumper;
+use YAML;
+
 use SL::DBUtils;
 
 sub get_part {
@@ -63,6 +66,8 @@ sub get_part {
   # copy to $form variables
   map { $form->{$_} = $ref->{$_} } (keys %{$ref});
 
+  $form->{onhand} *= 1;
+
   my %oid = ('Pg'     => 'a.oid',
              'Oracle' => 'a.rowid');
 
@@ -183,10 +188,12 @@ sub get_part {
 
   # get translations
   $form->{language_values} = "";
-  $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
+  $query = qq|SELECT language_id, translation, longdescription
+              FROM translation
+              WHERE parts_id = ?|;
   my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-  while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
-    $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
+  while (my $tr = $trq->fetchrow_hashref(NAME_lc)) {
+    $form->{language_values} .= "---+++---" . join('--++--', @{$tr}{qw(language_id translation longdescription)});
   }
   $trq->finish;
 
@@ -203,37 +210,25 @@ sub get_part {
   $sth->finish;
 
   # is it an orphan
-  $query =
-    qq|SELECT i.parts_id
-       FROM invoice i
-       WHERE (i.parts_id = ?)
-
-       UNION
-
-       SELECT o.parts_id
-       FROM orderitems o
-       WHERE (o.parts_id = ?)
+  my @referencing_tables = qw(invoice orderitems invoice inventory rmaitems);
+  my %column_map         = ( );
+  my $parts_id           = conv_i($form->{id});
 
-       UNION
+  $form->{orphaned}      = 1;
 
-       SELECT a.parts_id
-       FROM assembly a
-       WHERE (a.parts_id = ?)|;
-  @values = (conv_i($form->{id}), conv_i($form->{id}), conv_i($form->{id}));
-  ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
-  $form->{orphaned} = !$form->{orphaned};
+  foreach my $table (@referencing_tables) {
+    my $column  = $column_map{$table} || 'parts_id';
+    $query      = qq|SELECT $column FROM $table WHERE $column = ? LIMIT 1|;
+    my ($found) = selectrow_query($form, $dbh, $query, $parts_id);
 
-  $form->{"unit_changeable"} = 1;
-  foreach my $table (qw(invoice assembly orderitems inventory license)) {
-    $query = qq|SELECT COUNT(*) FROM $table WHERE parts_id = ?|;
-    my ($count) = selectrow_query($form, $dbh, $query, conv_i($form->{"id"}));
-
-    if ($count) {
-      $form->{"unit_changeable"} = 0;
+    if ($found) {
+      $form->{orphaned} = 0;
       last;
     }
   }
 
+  $form->{"unit_changeable"} = $form->{orphaned};
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
@@ -265,6 +260,8 @@ sub get_pricegroups {
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
+
+  return $pricegroups;
 }
 
 sub retrieve_buchungsgruppen {
@@ -330,14 +327,8 @@ sub save {
     }
 
     if ($form->{item} eq 'assembly') {
-      if ($form->{onhand} != 0) {
-        &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
-      }
-
       # delete assembly records
       do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
-
-      $form->{onhand} += $form->{stock};
     }
 
     # delete tax records
@@ -357,7 +348,6 @@ sub save {
     do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id});
 
     $form->{orphaned} = 1;
-    $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
     if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
       $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
     }
@@ -537,11 +527,6 @@ sub save {
       }
     }
 
-    # adjust onhand for the parts
-    if ($form->{onhand} != 0) {
-      &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
-    }
-
     @a = localtime;
     $a[5] += 1900;
     $a[4]++;
@@ -549,13 +534,6 @@ sub save {
 
     $form->get_employee($dbh);
 
-    # add inventory record
-    $query =
-      qq|INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id)
-         VALUES (0, ?, ?, '$shippingdate', ?)|;
-    @values = (conv_i($form->{id}), $form->{stock}, conv_i($form->{employee_id}));
-    do_query($form, $dbh, $query, @values);
-
   }
 
   #set expense_accno=inventory_accno if they are different => bilanz
@@ -655,67 +633,6 @@ sub retrieve_assemblies {
   $main::lxdebug->leave_sub();
 }
 
-sub restock_assemblies {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
-
-  for my $i (1 .. $form->{rowcount}) {
-
-    $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
-
-    if ($form->{"qty_$i"} != 0) {
-      &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
-    }
-
-  }
-
-  my $rc = $dbh->commit;
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-
-  return $rc;
-}
-
-sub adjust_inventory {
-  $main::lxdebug->enter_sub();
-
-  my ($dbh, $form, $id, $qty) = @_;
-
-  my $query =
-    qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
-       FROM parts p, assembly a
-       WHERE (a.parts_id = p.id) AND (a.id = ?)|;
-  my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
-
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-
-    my $allocate = $qty * $ref->{qty};
-
-    # is it a service item, then loop
-    $ref->{inventory_accno_id} *= 1;
-    next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
-
-    # adjust parts onhand
-    $form->update_balance($dbh, "parts", "onhand",
-                          qq|id = $ref->{id}|,
-                          $allocate * -1);
-  }
-
-  $sth->finish;
-
-  # update assembly
-  my $rc = $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $qty, $id);
-
-  $main::lxdebug->leave_sub();
-
-  return $rc;
-}
-
 sub delete {
   $main::lxdebug->enter_sub();
 
@@ -724,9 +641,9 @@ sub delete {
   # connect to database, turn off AutoCommit
   my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my %columns = ( "assembly" => "id", "alternate" => "id", "parts" => "id" );
+  my %columns = ( "assembly" => "id", "parts" => "id" );
 
-  for my $table (qw(prices partstax makemodel inventory assembly parts)) {
+  for my $table (qw(prices partstax makemodel inventory assembly license translation parts)) {
     my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
     do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
   }
@@ -815,9 +732,9 @@ sub assembly_item {
 #   short                                    - NOT IMPLEMENTED as form filter, only as itemstatus option
 #   l_serialnumber                           - belonges to serialnumber filter
 #   l_deliverydate                           - displays deliverydate is sold etc. flags are active
+#   l_soldtotal                              - aggreg join to display total of sold quantity, works as long as there's no bullshit in soldtotal
 #
 # not working:
-#   l_soldtotal                              - aggreg join to display total of sold quantity
 #   onhand                                   - as above, but masking the simple itemstatus results (doh!)
 #   masking of onhand in bsooqr mode         - ToDO: fixme
 #
@@ -837,7 +754,8 @@ sub all_parts {
   my ($self, $myconfig, $form) = @_;
   my $dbh = $form->get_standard_dbh($myconfig);
 
-  $form->{parts} = +{ };
+  $form->{parts}     = +{ };
+  $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
 
   my @simple_filters       = qw(partnumber ean description partsgroup microfiche drawing onhand);
   my @makemodel_filters    = qw(make model);
@@ -930,7 +848,17 @@ sub all_parts {
 
   my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
   $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols;
-  my $order_clause = " ORDER BY $form->{sort} " . ($form->{revers} ? 'DESC' : 'ASC');
+
+  my $sort_order = ($form->{revers} ? ' DESC' : ' ASC');
+
+  # special case: sorting by partnumber
+  # since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
+  # and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.
+  # ToDO: implement proper functional sorting
+  $form->{sort} = join ', ', map { push @select_tokens, $_; ($table_prefix{$_} = "substring(partnumber,'[") . $_ } qw|^[:digit:]]+') [:digit:]]+')::INTEGER|
+    if $form->{sort} eq 'partnumber';
+
+  my $order_clause = " ORDER BY $form->{sort} $sort_order";
 
   my $limit_clause = " LIMIT 100" if $form->{top100};
 
@@ -980,7 +908,8 @@ sub all_parts {
 
   #============= build query ================#
 
-  my %table_prefix = (
+  %table_prefix = (
+     %table_prefix,
      deliverydate => 'apoe.', serialnumber => 'ioi.',
      transdate    => 'apoe.', trans_id     => 'ioi.',
      module       => 'apoe.', name         => 'cv.',
@@ -1008,6 +937,8 @@ sub all_parts {
 
   $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
 
+  map { $_->{onhand} *= 1 } @{ $form->{parts} };
+
 ##  my $where = qq|1 = 1|;
 ##  my (@values, $var, $flds, $group, $limit);
 ##
@@ -1380,7 +1311,6 @@ sub all_parts {
     $form->{parts} = \@assemblies;
   }
 
-  $dbh->disconnect;
   $main::lxdebug->leave_sub();
 }
 
@@ -1679,11 +1609,14 @@ sub retrieve_languages {
     $query =
       qq|SELECT l.id, l.description, tr.translation, tr.longdescription
          FROM language l
-         LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|;
+         LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)
+         ORDER BY lower(l.description)|;
     @values = (conv_i($form->{id}));
 
   } else {
-    $query = qq|SELECT id, description FROM language|;
+    $query = qq|SELECT id, description
+                FROM language
+                ORDER BY lower(description)|;
   }
 
   my $languages = selectall_hashref_query($form, $dbh, $query, @values);
@@ -1825,4 +1758,43 @@ sub retrieve_accounts {
   $main::lxdebug->leave_sub(2);
 }
 
+sub get_basic_part_info {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+  my %params   = @_;
+
+  Common::check_params(\%params, qw(id));
+
+  my @ids      = 'ARRAY' eq ref $params{id} ? @{ $params{id} } : ($params{id});
+
+  if (!scalar @ids) {
+    $main::lxdebug->leave_sub();
+    return ();
+  }
+
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
+  my $dbh      = $form->get_standard_dbh($myconfig);
+
+  my $query    = qq|SELECT id, partnumber, description, unit FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
+
+  my $info     = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
+
+  if ('' eq ref $params{id}) {
+    $info = $info->[0] || { };
+
+    $main::lxdebug->leave_sub();
+    return $info;
+  }
+
+  my %info_map = map { $_->{id} => $_ } @{ $info };
+
+  $main::lxdebug->leave_sub();
+
+  return %info_map;
+}
+
+
 1;