X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=bc82b0c554b550f0485de88004212c73d64470fc;hb=6216f7b53a81a73641a2d98ea43f363b6a615b07;hp=fe666ec14d15b047d047ebadb19294466f685c4b;hpb=6ca711a5e060318111719f02ce91a4c101e9e812;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index fe666ec14..bc82b0c55 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -422,7 +422,8 @@ sub save {
          ean = ?,
          not_discountable = ?,
          microfiche = ?,
-         partsgroup_id = ?
+         partsgroup_id = ?,
+         price_factor_id = ?
        WHERE id = ?|;
   @values = ($form->{partnumber},
              $form->{description},
@@ -451,6 +452,7 @@ sub save {
              $form->{not_discountable} ? 't' : 'f',
              $form->{microfiche},
              conv_i($partsgroup_id),
+             conv_i($form->{price_factor_id}),
              conv_i($form->{id})
   );
   do_query($form, $dbh, $query, @values);
@@ -575,7 +577,7 @@ sub save {
   $form->{taxaccount} = "";
   while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
     $form->{taxaccount} .= "$ptr->{accno} ";
-    if (!($form->{taxaccount2} =~ /$ptr->{accno}/)) {
+    if (!($form->{taxaccount2} =~ /\Q$ptr->{accno}\E/)) {
       $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
       $form->{"$ptr->{accno}_description"} = $ptr->{description};
       $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
@@ -792,10 +794,10 @@ sub assembly_item {
 #   top100
 #
 # simple filter strings (every one of those also has a column flag prefixed with 'l_' associated):
-#   partnumber ean description partsgroup microfiche drawing 
+#   partnumber ean description partsgroup microfiche drawing
 #
 # column flags:
-#   l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup 
+#   l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup
 #
 # exclusives:
 #   itemstatus  = active | onhand | short | obsolete | orphaned
@@ -804,9 +806,9 @@ sub assembly_item {
 # joining filters:
 #   make model                               - makemodel
 #   serialnumber transdatefrom transdateto   - invoice/orderitems
-#   
+#
 # binary flags:
-#   bought sold onorder ordered rfq quoted   - aggreg joins with invoices/orders 
+#   bought sold onorder ordered rfq quoted   - aggreg joins with invoices/orders
 #   l_linetotal l_subtotal                   - aggreg joins to display totals (complicated) - NOT IMPLEMENTED here, implementation at frontend
 #   l_soldtotal                              - aggreg join to display total of sold quantity
 #   onhand                                   - as above, but masking the simple itemstatus results (doh!)
@@ -814,7 +816,7 @@ sub assembly_item {
 #   l_serialnumber                           - belonges to serialnumber filter
 #   l_deliverydate                           - displays deliverydate is sold etc. flags are active
 #
-# not working: 
+# 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
@@ -850,31 +852,32 @@ sub all_parts {
 #  my @inactive_flags       = qw(l_subtotal short l_linetotal);
 
   my %joins = (
-    partsgroup => 'LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id',
-    makemodel  => 'LEFT JOIN makemodel mm ON mm.parts_id = p.id',
-    invoice_oi => 
-      q|LEFT JOIN (                                                                                                                                      
-         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty,          assemblyitem, 'invoice'    AS ioi FROM invoice UNION             
-         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems                
+    partsgroup => 'LEFT JOIN partsgroup pg      ON (pg.id       = p.partsgroup_id)',
+    makemodel  => 'LEFT JOIN makemodel mm       ON (mm.parts_id = p.id)',
+    pfac       => 'LEFT JOIN price_factors pfac ON (pfac.id     = p.price_factor_id)',
+    invoice_oi =>
+      q|LEFT JOIN (
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty,          assemblyitem, 'invoice'    AS ioi FROM invoice UNION
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems
        ) AS ioi ON ioi.parts_id = p.id|,
-    apoe       => 
-      q|LEFT JOIN (                                                                                                                                     
+    apoe       =>
+      q|LEFT JOIN (
          SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id, NULL AS deliverydate FROM ap UNION
          SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,         deliverydate FROM ar UNION
          SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, NULL AS deliverydate FROM oe
        ) AS apoe ON ioi.trans_id = apoe.id|,
     cv         =>
-      q|LEFT JOIN (                                                                
-           SELECT id, name, 'customer' AS cv FROM customer UNION                   
-           SELECT id, name, 'vendor'   AS cv FROM vendor                           
+      q|LEFT JOIN (
+           SELECT id, name, 'customer' AS cv FROM customer UNION
+           SELECT id, name, 'vendor'   AS cv FROM vendor
          ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
   );
-  my @join_order = qw(partsgroup makemodel invoice_oi apoe cv);
-  my %joins_needed = (0) x scalar keys %joins;
+  my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
+  my %joins_needed;
 
   #===== switches and simple filters ========#
 
-  my @select_tokens = qw(id);
+  my @select_tokens = qw(id factor);
   my @where_tokens  = qw(1=1);
   my @group_tokens  = ();
 
@@ -889,10 +892,14 @@ sub all_parts {
     }
   }
 
+  my %simple_filter_table_prefix = (
+     description  => 'p.',
+  );
+
   foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) {
     next unless $form->{$_};
     $form->{"l_$_"} = '1'; # show the column
-    push @where_tokens, "$_ ILIKE ?"; 
+    push @where_tokens, "$simple_filter_table_prefix{$_}$_ ILIKE ?";
     push @bind_vars,    "%$form->{$_}%";
   }
 
@@ -909,16 +916,16 @@ sub all_parts {
   }
 
   for ($form->{itemstatus}) {
-    push @where_tokens, 'p.id NOT IN 
-        (SELECT DISTINCT parts_id FROM invoice UNION  
-         SELECT DISTINCT parts_id FROM assembly UNION 
+    push @where_tokens, 'p.id NOT IN
+        (SELECT DISTINCT parts_id FROM invoice UNION
+         SELECT DISTINCT parts_id FROM assembly UNION
          SELECT DISTINCT parts_id FROM orderitems)'    if /orphaned/;
     push @where_tokens, 'p.onhand = 0'                 if /orphaned/;
     push @where_tokens, 'NOT p.obsolete'               if /active/;
     push @where_tokens, '    p.obsolete',              if /obsolete/;
     push @where_tokens, 'p.onhand > 0',                if /onhand/;
     push @where_tokens, 'p.onhand < p.rop',            if /short/;
-  }                       
+  }
 
 
   my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));
@@ -928,11 +935,11 @@ sub all_parts {
   my $limit_clause = " LIMIT 100" if $form->{top100};
 
   #=== joins and complicated filters ========#
- 
+
   my $bsooqr = $form->{bought}  || $form->{sold}
             || $form->{ordered} || $form->{onorder}
             || $form->{quoted}  || $form->{rfq};
- 
+
   my @bsooqr;
   push @select_tokens, @qsooqr_flags                                          if $bsooqr;
   push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};
@@ -945,6 +952,7 @@ sub all_parts {
   push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens              if $bsooqr;
 
   $joins_needed{partsgroup}  = 1;
+  $joins_needed{pfac}        = 1;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
   $joins_needed{cv}          = 1 if $bsooqr;
   $joins_needed{apoe}        = 1 if $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
@@ -967,29 +975,37 @@ sub all_parts {
   if ($form->{l_soldtotal}) {
     push @where_tokens, 'ioi.qty >= 0';
     push @group_tokens, @select_tokens;
-    push @select_tokens, 'SUM(ioi.qty) AS soldtotal';
+    push @select_tokens, 'SUM(ioi.qty)';
   }
 
   #============= build query ================#
 
   my %table_prefix = (
-     deliverydate => 'apoe.', serialnumber => 'ioi.', 
-     transdate    => 'apoe.', trans_id     => 'ioi.', 
-     module       => 'apoe.', name         => 'cv.',  
-     ordnumber    => 'apoe.', make         => 'mm.',  
-     quonumber    => 'apoe.', model        => 'mm.',  
+     deliverydate => 'apoe.', serialnumber => 'ioi.',
+     transdate    => 'apoe.', trans_id     => 'ioi.',
+     module       => 'apoe.', name         => 'cv.',
+     ordnumber    => 'apoe.', make         => 'mm.',
+     quonumber    => 'apoe.', model        => 'mm.',
      invnumber    => 'apoe.', partsgroup   => 'pg.',
-     'SUM(ioi.qty) AS soldtotal' => ' ',
+     factor       => 'pfac.',
+     'SUM(ioi.qty)' => ' ',
   );
-  
+
+  my %renamed_columns = (
+    'factor'       => 'price_factor',
+    'SUM(ioi.qty)' => 'soldtotal',
+  );
+
   map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};
+  map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns;
 
-  my $select_clause = join ', ',    map { ($table_prefix{$_} || "p.") . $_ } @select_tokens;
+  my $select_clause = join ', ',    map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens;
   my $join_clause   = join ' ',     @joins{ grep $joins_needed{$_}, @join_order };
   my $where_clause  = join ' AND ', map { "($_)" } @where_tokens;
-  my $group_clause  = ' GROUP BY ' . join ', ',    map { ($table_prefix{$_} || "p.") . $_ } @group_tokens;
+  my $group_clause  = ' GROUP BY ' . join ', ',    map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
 
   my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|;
+
   $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
 
 ##  my $where = qq|1 = 1|;
@@ -1092,10 +1108,10 @@ sub all_parts {
 ##  if ($form->{l_soldtotal}) {
 ##    $form->{soldtotal} = 'soldtotal';
 ##    $query =
-##      qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, 
-##           p.bin, p.sellprice, p.listprice, p.lastcost, 
-##           p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal 
-##         FROM parts p 
+##      qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
+##           p.bin, p.sellprice, p.listprice, p.lastcost,
+##           p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal
+##         FROM parts p
 ##         LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
 ##         WHERE $where
 ##         $group
@@ -1546,7 +1562,7 @@ sub create_links {
   my $sth = prepare_execute_query($form, $dbh, $query, @values);
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
     foreach my $key (split(/:/, $ref->{link})) {
-      if ($key =~ /$module/) {
+      if ($key =~ /\Q$module\E/) {
         if (   ($ref->{id} eq $ref->{inventory_accno_id})
             || ($ref->{id} eq $ref->{income_accno_id})
             || ($ref->{id} eq $ref->{expense_accno_id})) {