X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ccde19fe1c1eb5ea7f0d6cb2d9d2f6b522d6ca01..ec2e4394a02c907ca241fa6d87ee43f9a56e6308:/SL/IC.pm

diff --git a/SL/IC.pm b/SL/IC.pm
index 784366582..75e3191eb 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -35,6 +35,7 @@
 package IC;
 
 use Data::Dumper;
+use List::MoreUtils qw(all);
 use YAML;
 
 use SL::DBUtils;
@@ -80,10 +81,11 @@ sub get_part {
     $query =
       qq|SELECT p.id, p.partnumber, p.description,
            p.sellprice, p.lastcost, p.weight, a.qty, a.bom, p.unit,
-           pg.partsgroup
+           pg.partsgroup, p.price_factor_id, pfac.factor AS price_factor
          FROM parts p
          JOIN assembly a ON (a.parts_id = p.id)
          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+         LEFT JOIN price_factors pfac ON pfac.id = p.price_factor_id
          WHERE (a.id = ?)
          ORDER BY $oid{$myconfig->{dbdriver}}|;
     $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
@@ -125,8 +127,6 @@ sub get_part {
   while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
           $form->{"price_$i"}, $form->{"pricegroup_$i"})
          = $sth->fetchrow_array()) {
-    $form->{"price_$i"} = $form->round_amount($form->{"price_$i"}, 5);
-    $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
     push @pricegroups, $form->{"pricegroup_id_$i"};
     $i++;
   }
@@ -154,8 +154,6 @@ sub get_part {
 
     foreach $name (@pricegroups_not_used) {
       $form->{"klass_$i"} = "$name->{id}";
-      $form->{"price_$i"} = $form->round_amount($form->{sellprice}, 5);
-      $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
       $form->{"pricegroup_id_$i"} = "$name->{id}";
       $form->{"pricegroup_$i"}    = "$name->{pricegroup}";
       $i++;
@@ -307,6 +305,8 @@ sub save {
 
   my ($query, $sth);
 
+  my $priceupdate = ', priceupdate = current_date';
+
   if ($form->{id}) {
 
     # get old price
@@ -337,6 +337,11 @@ sub save {
     # delete translations
     do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 
+    # Check whether or not the prices have changed. If they haven't
+    # then 'priceupdate' should not be updated.
+    my $previous_values = selectfirst_hashref_query($form, $dbh, qq|SELECT * FROM parts WHERE id = ?|, conv_i($form->{id})) || {};
+    $priceupdate        = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
+
   } else {
     my ($count) = selectrow_query($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
     if ($count) {
@@ -392,7 +397,6 @@ sub save {
          sellprice = ?,
          lastcost = ?,
          weight = ?,
-         priceupdate = ?,
          unit = ?,
          notes = ?,
          formel = ?,
@@ -414,6 +418,7 @@ sub save {
          microfiche = ?,
          partsgroup_id = ?,
          price_factor_id = ?
+         $priceupdate
        WHERE id = ?|;
   @values = ($form->{partnumber},
              $form->{description},
@@ -423,7 +428,6 @@ sub save {
              $form->{sellprice},
              $form->{lastcost},
              $form->{weight},
-             conv_date($form->{priceupdate}),
              $form->{unit},
              $form->{notes},
              $form->{formel},
@@ -690,8 +694,9 @@ sub assembly_item {
   my $dbh = $form->dbconnect($myconfig);
 
   my $query =
-    qq|SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, p.onhand, p.unit, pg.partsgroup,
-              p.price_factor_id, pfac.factor AS price_factor
+    qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
+       p.weight, p.onhand, p.unit, pg.partsgroup, p.lastcost,
+       p.price_factor_id, pfac.factor AS price_factor
        FROM parts p
        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
        LEFT JOIN price_factors pfac ON pfac.id = p.price_factor_id
@@ -956,17 +961,21 @@ sub all_parts {
 
   map { $_->{onhand} *= 1 } @{ $form->{parts} };
 
+  # post processing for assembly parts lists (bom)
+  # for each part get the assembly parts and add them into the partlist.
   my @assemblies;
-  # include individual items for assemblies
   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
     $query =
       qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
            p.unit, p.bin,
            p.sellprice, p.listprice, p.lastcost,
            p.rop, p.weight, p.priceupdate,
-           p.image, p.drawing, p.microfiche
-         FROM parts p, assembly a
-         WHERE (p.id = a.parts_id) AND (a.id = ?)|;
+           p.image, p.drawing, p.microfiche,
+           pfac.factor
+         FROM parts p
+         INNER JOIN assembly a ON (p.id = a.parts_id)
+         $joins{pfac}
+         WHERE a.id = ?|;
     $sth = prepare_query($form, $dbh, $query);
 
     foreach $item (@{ $form->{parts} }) {
@@ -975,6 +984,7 @@ sub all_parts {
 
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         $ref->{assemblyitem} = 1;
+        map { $ref->{$_} /= $ref->{factor} || 1 } qw(sellprice listprice lastcost);
         push(@assemblies, $ref);
       }
       $sth->finish;
@@ -987,51 +997,35 @@ sub all_parts {
   $main::lxdebug->leave_sub();
 }
 
-sub update_prices {
+sub _create_filter_for_priceupdate {
   $main::lxdebug->enter_sub();
 
-  my ($self, $myconfig, $form) = @_;
+  my $self     = shift;
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
   my @where_values;
   my $where = '1 = 1';
-  my $var;
 
-  my $group;
-  my $limit;
+  foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
+    my $column = $item;
+    $column =~ s/.*\.//;
+    next unless ($form->{$column});
 
-  if ($item ne 'make') {
-    foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
-      my $column = $item;
-      $column =~ s/.*\.//;
-      next unless ($form->{$column});
-      $where .= qq| AND $item ILIKE ?|;
-      push(@where_values, '%' . $form->{$column} . '%');
-    }
+    $where .= qq| AND $item ILIKE ?|;
+    push(@where_values, '%' . $form->{$column} . '%');
   }
 
-  # special case for description
-  if ($form->{description}
-      && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
-           || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
-    $where .= qq| AND (p.description ILIKE ?)|;
-    push(@where_values, '%' . $form->{description} . '%');
-  }
+  foreach my $item (qw(description serialnumber)) {
+    next unless ($form->{$item});
 
-  # special case for serialnumber
-  if ($form->{l_serialnumber} && $form->{serialnumber}) {
-    $where .= qq| AND serialnumber ILIKE ?|;
-    push(@where_values, '%' . $form->{serialnumber} . '%');
+    $where .= qq| AND (${item} ILIKE ?)|;
+    push(@where_values, '%' . $form->{$item} . '%');
   }
 
 
   # items which were never bought, sold or on an order
   if ($form->{itemstatus} eq 'orphaned') {
-    $form->{onhand}  = $form->{short}   = 0;
-    $form->{bought}  = $form->{sold}    = 0;
-    $form->{onorder} = $form->{ordered} = 0;
-    $form->{rfq}     = $form->{quoted}  = 0;
-
-    $form->{transdatefrom} = $form->{transdateto} = "";
-
     $where .=
       qq| AND (p.onhand = 0)
           AND p.id NOT IN
@@ -1042,23 +1036,19 @@ sub update_prices {
               UNION
               SELECT DISTINCT parts_id FROM orderitems
             )|;
-  }
 
-  if ($form->{itemstatus} eq 'active') {
+  } elsif ($form->{itemstatus} eq 'active') {
     $where .= qq| AND p.obsolete = '0'|;
-  }
 
-  if ($form->{itemstatus} eq 'obsolete') {
+  } elsif ($form->{itemstatus} eq 'obsolete') {
     $where .= qq| AND p.obsolete = '1'|;
-    $form->{onhand} = $form->{short} = 0;
-  }
 
-  if ($form->{itemstatus} eq 'onhand') {
+  } elsif ($form->{itemstatus} eq 'onhand') {
     $where .= qq| AND p.onhand > 0|;
-  }
 
-  if ($form->{itemstatus} eq 'short') {
+  } elsif ($form->{itemstatus} eq 'short') {
     $where .= qq| AND p.onhand < p.rop|;
+
   }
 
   foreach my $column (qw(make model)) {
@@ -1067,6 +1057,72 @@ sub update_prices {
     push(@where_values, '%' . $form->{$column} . '%');
   }
 
+  $main::lxdebug->leave_sub();
+
+  return ($where, @where_values);
+}
+
+sub get_num_matches_for_priceupdate {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
+  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+  my ($where, @where_values) = $self->_create_filter_for_priceupdate();
+
+  my $num_updated = 0;
+  my $query;
+
+  for my $column (qw(sellprice listprice)) {
+    next if ($form->{$column} eq "");
+
+    $query =
+      qq|SELECT COUNT(*)
+         FROM parts
+         WHERE id IN
+           (SELECT p.id
+            FROM parts p
+            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+            WHERE $where)|;
+    my ($result)  = selectfirst_array_query($from, $dbh, $query, @where_values);
+    $num_updated += $result if (0 <= $result);
+  }
+
+  $query =
+    qq|SELECT COUNT(*)
+       FROM prices
+       WHERE parts_id IN
+         (SELECT p.id
+          FROM parts p
+          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+          WHERE $where) AND (pricegroup_id = ?)|;
+  my $sth = prepare_query($form, $dbh, $query);
+
+  for my $i (1 .. $form->{price_rows}) {
+    next if ($form->{"price_$i"} eq "");
+
+    my ($result)  = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));
+    $num_updated += $result if (0 <= $result);
+  }
+  $sth->finish();
+
+  $main::lxdebug->leave_sub();
+
+  return $num_updated;
+}
+
+sub update_prices {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  my ($where, @where_values) = $self->_create_filter_for_priceupdate();
+  my $num_updated = 0;
+
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 
@@ -1088,7 +1144,8 @@ sub update_prices {
             FROM parts p
             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
             WHERE $where)|;
-    do_query($from, $dbh, $query, $value, @where_values);
+    my $result    = do_query($from, $dbh, $query, $value, @where_values);
+    $num_updated += $result if (0 <= $result);
   }
 
   my $q_add =
@@ -1113,12 +1170,15 @@ sub update_prices {
     next if ($form->{"price_$i"} eq "");
 
     my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
+    my $result;
 
     if ($form->{"pricegroup_type_$i"} eq "percent") {
-      do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
+      $result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
     } else {
-      do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
+      $result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
     }
+
+    $num_updated += $result if (0 <= $result);
   }
 
   $sth_add->finish();
@@ -1129,7 +1189,7 @@ sub update_prices {
 
   $main::lxdebug->leave_sub();
 
-  return $rc;
+  return $num_updated;
 }
 
 sub create_links {
@@ -1507,7 +1567,7 @@ sub prepare_parts_for_printing {
 
   $sth->finish();
 
-  my @columns = qw(ean);
+  my @columns = qw(ean image microfiche drawing weight);
 
   $query      = qq|SELECT id, | . join(', ', @columns) . qq|
                    FROM parts
@@ -1515,7 +1575,7 @@ sub prepare_parts_for_printing {
 
   my %data    = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
 
-  map { $form->{$_} = [] } (qw(make model), @columns);
+  map { $form->{TEMPLATE_ARRAYS}{$_} = [] } (qw(make model), @columns);
 
   foreach my $i (1 .. $rowcount) {
     my $id = $form->{"${prefix}${i}"};
@@ -1523,16 +1583,16 @@ sub prepare_parts_for_printing {
     next if (!$id);
 
     foreach (@columns) {
-      push @{ $form->{$_} }, $data{$id}->{$_};
+      push @{ $form->{TEMPLATE_ARRAYS}{$_} }, $data{$id}->{$_};
     }
 
-    push @{ $form->{make} },  [];
-    push @{ $form->{model} }, [];
+    push @{ $form->{TEMPLATE_ARRAYS}{make} },  [];
+    push @{ $form->{TEMPLATE_ARRAYS}{model} }, [];
 
     next if (!$makemodel{$id});
 
     foreach my $ref (@{ $makemodel{$id} }) {
-      map { push @{ $form->{$_}->[-1] }, $ref->{$_} } qw(make model);
+      map { push @{ $form->{TEMPLATE_ARRAYS}{$_}->[-1] }, $ref->{$_} } qw(make model);
     }
   }