X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=aaae3491fff2a6f96e32ba74b15dbea842403f27;hb=5b2980ad49f1937786d25f10fa9dac43cf255eb0;hp=5548d19dfa3d29eeab75fbe07059af292946db27;hpb=bd8bd95b1a2dc9c22365311be323d41e0f0cada3;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index 5548d19df..aaae3491f 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -38,6 +38,7 @@ use Data::Dumper;
 use List::MoreUtils qw(all);
 use YAML;
 
+use SL::CVar;
 use SL::DBUtils;
 
 sub get_part {
@@ -69,9 +70,6 @@ sub get_part {
 
   $form->{onhand} *= 1;
 
-  my %oid = ('Pg'     => 'a.oid',
-             'Oracle' => 'a.rowid');
-
   # part or service item
   $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
   if ($form->{assembly}) {
@@ -87,11 +85,11 @@ sub get_part {
          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}}|;
+         ORDER BY a.oid|;
     $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 
     $form->{assembly_rows} = 0;
-    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+    while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
       $form->{assembly_rows}++;
       foreach my $key (keys %{$ref}) {
         $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
@@ -138,7 +136,7 @@ sub get_part {
   $form->{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query);
 
   #find not used pricegroups
-  while ($tmp = pop(@{ $form->{PRICEGROUPS} })) {
+  while (my $tmp = pop(@{ $form->{PRICEGROUPS} })) {
     my $in_use = 0;
     foreach my $item (@pricegroups) {
       if ($item eq $tmp->{id}) {
@@ -152,7 +150,7 @@ sub get_part {
   # if not used pricegroups are avaible
   if (@pricegroups_not_used) {
 
-    foreach $name (@pricegroups_not_used) {
+    foreach my $name (@pricegroups_not_used) {
       $form->{"klass_$i"} = "$name->{id}";
       $form->{"pricegroup_id_$i"} = "$name->{id}";
       $form->{"pricegroup_$i"}    = "$name->{pricegroup}";
@@ -169,7 +167,7 @@ sub get_part {
     if ($form->{makemodel}) {
       $query = qq|SELECT m.make, m.model FROM makemodel m | .
                qq|WHERE m.parts_id = ?|;
-      @values = ($form->{id});
+      my @values = ($form->{id});
       $sth = $dbh->prepare($query);
       $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
 
@@ -190,7 +188,7 @@ sub get_part {
               FROM translation
               WHERE parts_id = ?|;
   my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-  while (my $tr = $trq->fetchrow_hashref(NAME_lc)) {
+  while (my $tr = $trq->fetchrow_hashref("NAME_lc")) {
     $form->{language_values} .= "---+++---" . join('--++--', @{$tr}{qw(language_id translation longdescription)});
   }
   $trq->finish;
@@ -201,7 +199,7 @@ sub get_part {
        FROM chart c, partstax pt
        WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|;
   $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-  while (($key) = $sth->fetchrow_array) {
+  while (my ($key) = $sth->fetchrow_array) {
     $form->{amount}{$key} = $key;
   }
 
@@ -244,7 +242,7 @@ sub get_pricegroups {
   my $pricegroups = selectall_hashref_query($form, $dbh, $query);
 
   my $i = 1;
-  foreach $pg (@{ $pricegroups }) {
+  foreach my $pg (@{ $pricegroups }) {
     $form->{"klass_$i"} = "$pg->{id}";
     $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
     $form->{"pricegroup_id_$i"} = "$pg->{id}";
@@ -364,7 +362,7 @@ sub save {
   my $partsgroup_id = 0;
 
   if ($form->{partsgroup}) {
-    ($partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
+    (my $partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
   }
 
   my ($subq_inventory, $subq_expense, $subq_income);
@@ -455,7 +453,7 @@ sub save {
   do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 
   if ($form->{language_values} ne "") {
-    foreach $item (split(/---\+\+\+---/, $form->{language_values})) {
+    foreach my $item (split(/---\+\+\+---/, $form->{language_values})) {
       my ($language_id, $translation, $longdescription) = split(/--\+\+--/, $item);
       if ($translation ne "") {
         $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription)
@@ -504,7 +502,7 @@ sub save {
   }
 
   # insert taxes
-  foreach $item (split(/ /, $form->{taxaccounts})) {
+  foreach my $item (split(/ /, $form->{taxaccounts})) {
     if ($form->{"IC_tax_$item"}) {
       $query =
         qq|INSERT INTO partstax (parts_id, chart_id)
@@ -529,7 +527,7 @@ sub save {
       }
     }
 
-    @a = localtime;
+    my @a = localtime;
     $a[5] += 1900;
     $a[4]++;
     my $shippingdate = "$a[5]-$a[4]-$a[3]";
@@ -539,23 +537,23 @@ sub save {
   }
 
   #set expense_accno=inventory_accno if they are different => bilanz
-  $vendor_accno =
+  my $vendor_accno =
     ($form->{expense_accno} != $form->{inventory_accno})
     ? $form->{inventory_accno}
     : $form->{expense_accno};
 
   # get tax rates and description
-  $accno_id =
+  my $accno_id =
     ($form->{vc} eq "customer") ? $form->{income_accno} : $vendor_accno;
   $query =
     qq|SELECT c.accno, c.description, t.rate, t.taxnumber
        FROM chart c, tax t
        WHERE (c.id = t.chart_id) AND (t.taxkey IN (SELECT taxkey_id FROM chart where accno = ?))
        ORDER BY c.accno|;
-  $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
+  my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
 
   $form->{taxaccount} = "";
-  while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+  while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
     $form->{taxaccount} .= "$ptr->{accno} ";
     if (!($form->{taxaccount2} =~ /\Q$ptr->{accno}\E/)) {
       $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
@@ -565,6 +563,11 @@ sub save {
     }
   }
 
+  CVar->save_custom_variables('dbh'       => $dbh,
+                              'module'    => 'IC',
+                              'trans_id'  => $form->{id},
+                              'variables' => $form);
+
   # commit
   my $rc = $dbh->commit;
   $dbh->disconnect;
@@ -590,7 +593,7 @@ sub update_assembly {
   $query =
     qq|UPDATE parts SET sellprice = sellprice + ?, weight = weight + ?
        WHERE id = ?|;
-  @values = ($qty * ($form->{sellprice} - $sellprice),
+  my @values = ($qty * ($form->{sellprice} - $sellprice),
              $qty * ($form->{weight} - $weight), conv_i($id));
   do_query($form, $dbh, $query, @values);
 
@@ -682,7 +685,7 @@ sub assembly_item {
     push(@values, conv_i($form->{id}));
   }
 
-  if ($partnumber) {
+  if ($form->{partnumber}) {
     $where .= qq| ORDER BY p.partnumber|;
   } else {
     $where .= qq| ORDER BY p.description|;
@@ -742,6 +745,7 @@ sub assembly_item {
 # not working:
 #   onhand                                   - as above, but masking the simple itemstatus results (doh!)
 #   masking of onhand in bsooqr mode         - ToDO: fixme
+#   warehouse onhand
 #
 # disabled sanity checks and changes:
 #  - searchitems = assembly will no longer disable bought
@@ -769,7 +773,7 @@ sub all_parts {
   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
   my @simple_l_switches    = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);
-  my @qsooqr_flags         = qw(invnumber ordnumber quonumber trans_id name module);
+  my @qsooqr_flags         = qw(invnumber ordnumber quonumber trans_id name module qty);
   my @deliverydate_flags   = qw(deliverydate);
 #  my @other_flags          = qw(onhand); # ToDO: implement these
 #  my @inactive_flags       = qw(l_subtotal short l_linetotal);
@@ -796,6 +800,24 @@ sub all_parts {
          ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
   );
   my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
+
+  my %table_prefix = (
+     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.',
+     lastcost     => ' ',
+     factor       => 'pfac.',
+     'SUM(ioi.qty)' => ' ',
+  );
+
+  my %renamed_columns = (
+    'factor'       => 'price_factor',
+    'SUM(ioi.qty)' => 'soldtotal',
+  );
+
   my %joins_needed;
 
   if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
@@ -807,6 +829,7 @@ sub all_parts {
   my @select_tokens = qw(id factor);
   my @where_tokens  = qw(1=1);
   my @group_tokens  = ();
+  my @bind_vars     = ();
 
   # special case transdate
   if (grep { $form->{$_} } qw(transdatefrom transdateto)) {
@@ -873,7 +896,7 @@ sub all_parts {
   # 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
   # Nette Idee von Sven, gibt aber Probleme wenn die Artikelnummern groesser als 32bit sind. Korrekt waere es, dass Sort-Natural-Modul zu nehmen
-  # Ich lass das mal hier drin, damit die Idee erhalten bleibt jb 28.5.2009 bug 1018 
+  # Ich lass das mal hier drin, damit die Idee erhalten bleibt jb 28.5.2009 bug 1018
   #$form->{sort} = join ', ', map { push @select_tokens, $_; ($table_prefix{$_} = "substring(partnumber,'[") . $_ } qw|^[:digit:]]+') [:digit:]]+')::INTEGER|
   #  if $form->{sort} eq 'partnumber';
 
@@ -888,6 +911,7 @@ sub all_parts {
             || $form->{quoted}  || $form->{rfq};
 
   my @bsooqr;
+  my @bsooqr_tokens = ();
   push @select_tokens, @qsooqr_flags                                          if $bsooqr;
   push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};
   push @select_tokens, $q_assembly_lastcost                                   if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost};
@@ -899,6 +923,9 @@ sub all_parts {
   push @bsooqr_tokens, q|module = 'oe' AND     quotation AND cv = 'vendor'|   if $form->{rfq};
   push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens              if $bsooqr;
 
+  $renamed_columns{onhand} = 'onhand_before_bsooqr';
+  $renamed_columns{qty}    = 'onhand';
+
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
@@ -929,26 +956,8 @@ sub all_parts {
 
   #============= build query ================#
 
-  %table_prefix = (
-     %table_prefix,
-     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.',
-     lastcost     => ' ',
-     factor       => 'pfac.',
-     'SUM(ioi.qty)' => ' ',
-  );
-
   $table_prefix{$q_assembly_lastcost} = ' ';
 
-  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;
 
@@ -957,6 +966,15 @@ sub all_parts {
   my $where_clause  = join ' AND ', map { "($_)" } @where_tokens;
   my $group_clause  = ' GROUP BY ' . join ', ',    map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
 
+  my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'IC',
+                                                            'trans_id_field' => 'p.id',
+                                                            'filter'         => $form);
+
+  if ($cvar_where) {
+    $where_clause .= qq| AND ($cvar_where)|;
+    push @bind_vars, @cvar_values;
+  }
+
   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);
@@ -978,13 +996,13 @@ sub all_parts {
          INNER JOIN assembly a ON (p.id = a.parts_id)
          $joins{pfac}
          WHERE a.id = ?|;
-    $sth = prepare_query($form, $dbh, $query);
+    my $sth = prepare_query($form, $dbh, $query);
 
-    foreach $item (@{ $form->{parts} }) {
+    foreach my $item (@{ $form->{parts} }) {
       push(@assemblies, $item);
       do_statement($form, $sth, $query, conv_i($item->{id}));
 
-      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+      while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
         $ref->{assemblyitem} = 1;
         map { $ref->{$_} /= $ref->{factor} || 1 } qw(sellprice listprice lastcost);
         push(@assemblies, $ref);
@@ -1054,7 +1072,7 @@ sub _create_filter_for_priceupdate {
   }
 
   foreach my $column (qw(make model)) {
-    next unless ($form->{$colum});
+    next unless ($form->{$column});
     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
     push(@where_values, '%' . $form->{$column} . '%');
   }
@@ -1072,7 +1090,7 @@ sub get_num_matches_for_priceupdate {
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
-  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+  my $dbh      = $form->get_standard_dbh($myconfig);
 
   my ($where, @where_values) = $self->_create_filter_for_priceupdate();
 
@@ -1090,7 +1108,7 @@ sub get_num_matches_for_priceupdate {
             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);
+    my ($result)  = selectfirst_array_query($form, $dbh, $query, @where_values);
     $num_updated += $result if (0 <= $result);
   }
 
@@ -1139,14 +1157,14 @@ sub update_prices {
       $operator = '*';
     }
 
-    $query =
+    my $query =
       qq|UPDATE parts SET $column = $column $operator ?
          WHERE id IN
            (SELECT p.id
             FROM parts p
             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
             WHERE $where)|;
-    my $result    = do_query($from, $dbh, $query, $value, @where_values);
+    my $result    = do_query($form, $dbh, $query, $value, @where_values);
     $num_updated += $result if (0 <= $result);
   }
 
@@ -1203,6 +1221,7 @@ sub create_links {
   my $dbh = $form->dbconnect($myconfig);
 
   my @values = ('%' . $module . '%');
+  my $query;
 
   if ($form->{id}) {
     $query =
@@ -1223,7 +1242,7 @@ sub create_links {
   }
 
   my $sth = prepare_execute_query($form, $dbh, $query, @values);
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     foreach my $key (split(/:/, $ref->{link})) {
       if ($key =~ /\Q$module\E/) {
         if (   ($ref->{id} eq $ref->{inventory_accno_id})
@@ -1292,7 +1311,7 @@ sub get_parts {
   my $sth = prepare_execute_query($form, $dbh, $query, @values);
 
   my $j = 0;
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     if (($ref->{partnumber} eq "*") && ($ref->{description} eq "")) {
       next;
     }
@@ -1321,7 +1340,7 @@ sub get_soldtotal {
   my ($dbh, $id) = @_;
 
   my $query = qq|SELECT sum(qty) FROM invoice WHERE parts_id = ?|;
-  my ($sum) = selectrow_query($form, $dbh, $query, conv_i($id));
+  my ($sum) = selectrow_query($main::form, $dbh, $query, conv_i($id));
   $sum ||= 0;
 
   $main::lxdebug->leave_sub();
@@ -1339,6 +1358,7 @@ sub retrieve_languages {
 
   my @values;
   my $where;
+  my $query;
 
   if ($form->{language_values} ne "") {
     $query =
@@ -1382,7 +1402,7 @@ sub follow_account_chain {
 
   while (1) {
     do_statement($form, $sth, $query, $accno_id);
-    $ref = $sth->fetchrow_hashref();
+    my $ref = $sth->fetchrow_hashref();
     last unless ($ref && $ref->{"is_valid"} &&
                  !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
     $accno_id = $ref->{"new_chart_id"};