GuV und BWA: Bei Projektfilter Zeitraum richtig setzen

[mfinanz.git] / SL / IC.pm

diff --git a/SL/IC.pm b/SL/IC.pm
index da263bede90583b806cc7d4a09235ce75bf3235e..b707e88cb37db73398a3d9fec1f877583fe4361c 100644 (file)
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -42,6 +42,7 @@ use SL::CVar;
 use SL::DBUtils;
 use SL::HTML::Restrict;
 use SL::TransNumber;
 use SL::DBUtils;
 use SL::HTML::Restrict;
 use SL::TransNumber;

+use SL::Util qw(trim);

 
 use strict;
 
 
 use strict;
 


@@ -72,6 +73,8 @@ sub get_part {
   # copy to $form variables
   map { $form->{$_} = $ref->{$_} } (keys %{$ref});
 
   # copy to $form variables
   map { $form->{$_} = $ref->{$_} } (keys %{$ref});
 

+  $form->{mtime} = $form->{itime} if !$form->{mtime};
+  $form->{lastmtime} = $form->{mtime};

   $form->{onhand} *= 1;
 
   # part or service item
   $form->{onhand} *= 1;
 
   # part or service item


@@ -397,6 +400,8 @@ sub save {
   );
   do_query($form, $dbh, $query, @values);
 
   );
   do_query($form, $dbh, $query, @values);
 

+  $form->new_lastmtime('parts');
+

   # delete translation records
   do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 
   # delete translation records
   do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 


@@ -568,6 +573,8 @@ sub update_assembly {
              $qty * ($form->{weight} - $weight), conv_i($id));
   do_query($form, $dbh, $query, @values);
 
              $qty * ($form->{weight} - $weight), conv_i($id));
   do_query($form, $dbh, $query, @values);
 

+  $form->new_lastmtime('parts') if $id == $form->{id};
+

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 


@@ -584,12 +591,12 @@ sub retrieve_assemblies {
 
   if ($form->{partnumber}) {
     $where .= qq| AND (p.partnumber ILIKE ?)|;
 
   if ($form->{partnumber}) {
     $where .= qq| AND (p.partnumber ILIKE ?)|;

-    push(@values, '%' . $form->{partnumber} . '%');
+    push(@values, $::form->like($form->{partnumber}));

   }
 
   if ($form->{description}) {
     $where .= qq| AND (p.description ILIKE ?)|;
   }
 
   if ($form->{description}) {
     $where .= qq| AND (p.description ILIKE ?)|;

-    push(@values, '%' . $form->{description} . '%');
+    push(@values, $::form->like($form->{description}));

   }
 
   # retrieve assembly items
   }
 
   # retrieve assembly items


@@ -645,7 +652,7 @@ sub assembly_item {
   while (my ($column, $table) = each(%columns)) {
     next unless ($form->{"${column}_$i"});
     $where .= qq| AND ${table}.${column} ILIKE ?|;
   while (my ($column, $table) = each(%columns)) {
     next unless ($form->{"${column}_$i"});
     $where .= qq| AND ${table}.${column} ILIKE ?|;

-    push(@values, '%' . $form->{"${column}_$i"} . '%');
+    push(@values, $::form->like($form->{"${column}_$i"}));

   }
 
   if ($form->{id}) {
   }
 
   if ($form->{id}) {


@@ -671,7 +678,7 @@ sub assembly_item {
   my $query =
     qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
        p.weight, p.onhand, p.unit, pg.partsgroup, p.lastcost,
   my $query =
     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
+       p.price_factor_id, pfac.factor AS price_factor, p.notes as longdescription

        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
        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


@@ -744,7 +751,7 @@ sub all_parts {
   my @apoe_filters         = qw(transdate);
   my @like_filters         = (@simple_filters, @invoice_oi_filters);
   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
   my @apoe_filters         = qw(transdate);
   my @like_filters         = (@simple_filters, @invoice_oi_filters);
   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));

-  my @simple_l_switches    = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop));
+  my @simple_l_switches    = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop insertdate));

   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);
   my @qsooqr_flags         = qw(invnumber ordnumber quonumber trans_id name module qty);
   my @deliverydate_flags   = qw(deliverydate);
   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);
   my @qsooqr_flags         = qw(invnumber ordnumber quonumber trans_id name module qty);
   my @deliverydate_flags   = qw(deliverydate);


@@ -820,10 +827,12 @@ sub all_parts {
     'ioi.id'       => 'ioi_id',
     'ioi.ioi'      => 'ioi',
     'projectdescription' => 'projectdescription',
     'ioi.id'       => 'ioi_id',
     'ioi.ioi'      => 'ioi',
     'projectdescription' => 'projectdescription',

+    'insertdate'   => 'insertdate',

   );
 
   my %real_column = (
     projectdescription => 'description',
   );
 
   my %real_column = (
     projectdescription => 'description',

+    insertdate         => 'itime::DATE',

   );
 
   if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
   );
 
   if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {


@@ -854,13 +863,37 @@ sub all_parts {
   #===== switches and simple filters ========#
 
   # special case transdate
   #===== switches and simple filters ========#
 
   # special case transdate

-  if (grep { $form->{$_} } qw(transdatefrom transdateto)) {
+  if (grep { trim($form->{$_}) } qw(transdatefrom transdateto)) {

     $form->{"l_transdate"} = 1;
     push @select_tokens, 'transdate';
     for (qw(transdatefrom transdateto)) {
     $form->{"l_transdate"} = 1;
     push @select_tokens, 'transdate';
     for (qw(transdatefrom transdateto)) {

-      next unless $form->{$_};
+      my $value = trim($form->{$_});
+      next unless $value;

       push @where_tokens, sprintf "transdate %s ?", /from$/ ? '>=' : '<=';
       push @where_tokens, sprintf "transdate %s ?", /from$/ ? '>=' : '<=';

-      push @bind_vars,    $form->{$_};
+      push @bind_vars,    $value;
+    }
+  }
+
+  # special case smart search
+  if ($form->{all}) {
+    $form->{"l_$_"} = 1 for qw(partnumber description unit sellprice lastcost cvar_packaging linetotal);
+    push @where_tokens, "p.partnumber ILIKE ? OR p.description ILIKE ?";
+    push @bind_vars,    "%$form->{all}%", "%$form->{all}%";
+  }
+
+  # special case insertdate
+  if (grep { trim($form->{$_}) } qw(insertdatefrom insertdateto)) {
+    $form->{"l_insertdate"} = 1;
+    push @select_tokens, 'insertdate';
+
+    my $token_builder = $make_token_builder->();
+    my $token = $token_builder->('insertdate');
+
+    for (qw(insertdatefrom insertdateto)) {
+      my $value = trim($form->{$_});
+      next unless $value;
+      push @where_tokens, sprintf "$token %s ?", /from$/ ? '>=' : '<=';
+      push @bind_vars,    $value;

     }
   }
 
     }
   }
 


@@ -884,7 +917,7 @@ sub all_parts {
     next unless $form->{$_};
     $form->{"l_$_"} = '1'; # show the column
     push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";
     next unless $form->{$_};
     $form->{"l_$_"} = '1'; # show the column
     push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";

-    push @bind_vars,    "%$form->{$_}%";
+    push @bind_vars,    $::form->like($form->{$_});

   }
 
   foreach (@simple_l_switches) {
   }
 
   foreach (@simple_l_switches) {


@@ -925,11 +958,11 @@ sub all_parts {
   # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
   if ($form->{make}) {
     push @where_tokens, 'mv.name ILIKE ?';
   # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
   if ($form->{make}) {
     push @where_tokens, 'mv.name ILIKE ?';

-    push @bind_vars, "%$form->{make}%";
+    push @bind_vars, $::form->like($form->{make});

   }
   if ($form->{model}) {
     push @where_tokens, 'mm.model ILIKE ?';
   }
   if ($form->{model}) {
     push @where_tokens, 'mm.model ILIKE ?';

-    push @bind_vars, "%$form->{model}%";
+    push @bind_vars, $::form->like($form->{model});

   }
 
   # special case: sorting by partnumber
   }
 
   # special case: sorting by partnumber


@@ -997,7 +1030,7 @@ sub all_parts {
 
   my $token_builder = $make_token_builder->(\%joins_needed);
 
 
   my $token_builder = $make_token_builder->(\%joins_needed);
 

-  my @sort_cols    = (@simple_filters, qw(id priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate shop));
+  my @sort_cols    = (@simple_filters, qw(id priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate insertdate shop));

      $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; # sort by id if unknown or invisible column
   my $sort_order   = ($form->{revers} ? ' DESC' : ' ASC');
   my $order_clause = " ORDER BY " . $token_builder->($form->{sort}) . ($form->{revers} ? ' DESC' : ' ASC');
      $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; # sort by id if unknown or invisible column
   my $sort_order   = ($form->{revers} ? ' DESC' : ' ASC');
   my $order_clause = " ORDER BY " . $token_builder->($form->{sort}) . ($form->{revers} ? ' DESC' : ' ASC');


@@ -1055,7 +1088,7 @@ sub all_parts {
   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
     $query =
       qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
     $query =
       qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,

-           p.unit, p.notes,
+           p.unit, p.notes, p.itime::DATE as insertdate,

            p.sellprice, p.listprice, p.lastcost,
            p.rop, p.weight, p.priceupdate,
            p.image, p.drawing, p.microfiche,
            p.sellprice, p.listprice, p.lastcost,
            p.rop, p.weight, p.priceupdate,
            p.image, p.drawing, p.microfiche,


@@ -1123,14 +1156,14 @@ sub _create_filter_for_priceupdate {
     next unless ($form->{$column});
 
     $where .= qq| AND $item ILIKE ?|;
     next unless ($form->{$column});
 
     $where .= qq| AND $item ILIKE ?|;

-    push(@where_values, '%' . $form->{$column} . '%');
+    push(@where_values, $::form->like($form->{$column}));

   }
 
   foreach my $item (qw(description serialnumber)) {
     next unless ($form->{$item});
 
     $where .= qq| AND (${item} ILIKE ?)|;
   }
 
   foreach my $item (qw(description serialnumber)) {
     next unless ($form->{$item});
 
     $where .= qq| AND (${item} ILIKE ?)|;

-    push(@where_values, '%' . $form->{$item} . '%');
+    push(@where_values, $::form->like($form->{$item}));

   }
 
 
   }
 
 


@@ -1164,7 +1197,7 @@ sub _create_filter_for_priceupdate {
   foreach my $column (qw(make model)) {
     next unless ($form->{$column});
     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
   foreach my $column (qw(make model)) {
     next unless ($form->{$column});
     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;

-    push(@where_values, '%' . $form->{$column} . '%');
+    push(@where_values, $::form->like($form->{$column}));

   }
 
   $main::lxdebug->leave_sub();
   }
 
   $main::lxdebug->leave_sub();


@@ -1378,15 +1411,15 @@ sub get_parts {
 
   if ($sortorder eq "all") {
     $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
 
   if ($sortorder eq "all") {
     $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;

-    push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
+    push(@values, $::form->like($form->{partnumber}), $::form->like($form->{description}));

 
   } elsif ($sortorder eq "partnumber") {
     $where .= qq| AND (partnumber ILIKE ?)|;
 
   } elsif ($sortorder eq "partnumber") {
     $where .= qq| AND (partnumber ILIKE ?)|;

-    push(@values, '%' . $form->{partnumber} . '%');
+    push(@values, $::form->like($form->{partnumber}));

 
   } elsif ($sortorder eq "description") {
     $where .= qq| AND (description ILIKE ?)|;
 
   } elsif ($sortorder eq "description") {
     $where .= qq| AND (description ILIKE ?)|;

-    push(@values, '%' . $form->{description} . '%');
+    push(@values, $::form->like($form->{description}));

     $order = "description";
 
   }
     $order = "description";
 
   }


@@ -1696,7 +1729,7 @@ sub prepare_parts_for_printing {
 
   $sth->finish();
 
 
   $sth->finish();
 

-  my @columns = qw(ean image microfiche drawing weight);
+  my @columns = qw(ean image microfiche drawing);

 
   $query      = qq|SELECT id, | . join(', ', @columns) . qq|
                    FROM parts
 
   $query      = qq|SELECT id, | . join(', ', @columns) . qq|
                    FROM parts


@@ -1704,7 +1737,8 @@ sub prepare_parts_for_printing {
 
   my %data    = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
 
 
   my %data    = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
 

-  map { $form->{TEMPLATE_ARRAYS}{$_} = [] } (qw(make model), @columns);
+  my %template_arrays;
+  map { $template_arrays{$_} = [] } (qw(make model), @columns);

 
   foreach my $i (1 .. $rowcount) {
     my $id = $form->{"${prefix}${i}"};
 
   foreach my $i (1 .. $rowcount) {
     my $id = $form->{"${prefix}${i}"};


@@ -1712,16 +1746,16 @@ sub prepare_parts_for_printing {
     next if (!$id);
 
     foreach (@columns) {
     next if (!$id);
 
     foreach (@columns) {

-      push @{ $form->{TEMPLATE_ARRAYS}{$_} }, $data{$id}->{$_};
+      push @{ $template_arrays{$_} }, $data{$id}->{$_};

     }
 
     }
 

-    push @{ $form->{TEMPLATE_ARRAYS}{make} },  [];
-    push @{ $form->{TEMPLATE_ARRAYS}{model} }, [];
+    push @{ $template_arrays{make} },  [];
+    push @{ $template_arrays{model} }, [];

 
     next if (!$makemodel{$id});
 
     foreach my $ref (@{ $makemodel{$id} }) {
 
     next if (!$makemodel{$id});
 
     foreach my $ref (@{ $makemodel{$id} }) {

-      map { push @{ $form->{TEMPLATE_ARRAYS}{$_}->[-1] }, $ref->{$_} } qw(make model);
+      map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model);

     }
   }
 
     }
   }
 


@@ -1732,9 +1766,10 @@ sub prepare_parts_for_printing {
     my $id = $form->{"${prefix}${i}"};
     next unless $id;
 
     my $id = $form->{"${prefix}${i}"};
     next unless $id;
 

-    push @{ $form->{TEMPLATE_ARRAYS}{part_type} },  $parts_by_id{$id}->type;
+    push @{ $template_arrays{part_type} },  $parts_by_id{$id}->type;

   }
 
   }
 

+  return %template_arrays;

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }