X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=f5f014b9d11267ba7832ae3cb9509dc75d10b706;hb=6669edf8fb3d3e7e917aca49fa9b1e8387b0943a;hp=67fbba09c8e8a47995ab25555104bec03412c18b;hpb=fca2825c79e6740fd511a2f86df9cb1ff898bfdf;p=kivitendo-erp.git

diff --git a/SL/IC.pm b/SL/IC.pm
index 67fbba09c..f5f014b9d 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -164,28 +164,25 @@ sub get_part {
   #correct rows
   $form->{price_rows} = $i - 1;
 
-  unless ($form->{item} eq 'service') {
-
-    # get makes
-    if ($form->{makemodel}) {
-    #hli
-      $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
-               qq|WHERE m.parts_id = ? order by m.sortorder asc|;
-      my @values = ($form->{id});
-      $sth = $dbh->prepare($query);
-      $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
-
-      my $i = 1;
-
-      while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
-                $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
-      {
-        $i++;
-      }
-      $sth->finish;
-      $form->{makemodel_rows} = $i - 1;
-
+  # get makes
+  if ($form->{makemodel}) {
+  #hli
+    $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
+             qq|WHERE m.parts_id = ? order by m.sortorder asc|;
+    my @values = ($form->{id});
+    $sth = $dbh->prepare($query);
+    $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
+
+    my $i = 1;
+
+    while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
+              $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
+    {
+      $i++;
     }
+    $sth->finish;
+    $form->{makemodel_rows} = $i - 1;
+
   }
 
   # get translations
@@ -330,10 +327,8 @@ sub save {
     }
     $sth->finish;
 
-    if ($form->{item} ne 'service') {
-      # delete makemodel records
-      do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
-    }
+    # delete makemodel records
+    do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
 
     if ($form->{item} eq 'assembly') {
       # delete assembly records
@@ -792,12 +787,13 @@ sub all_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 @project_filters      = qw(projectnumber projectdescription);
   my @makemodel_filters    = qw(make model);
   my @invoice_oi_filters   = qw(serialnumber soldtotal);
   my @apoe_filters         = qw(transdate);
   my @like_filters         = (@simple_filters, @invoice_oi_filters);
-  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 @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 bin rop image));
   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);
@@ -816,14 +812,14 @@ sub all_parts {
     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,         deliverydate, 'invoice'    AS ioi, id FROM invoice UNION
-         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty,          assemblyitem,         deliverydate, 'invoice'    AS ioi, project_id, id FROM invoice UNION
+         SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, project_id, id FROM orderitems
        ) AS ioi ON ioi.parts_id = p.id|,
     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, 'invoice'    AS ioi FROM ap UNION
-         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,            deliverydate, 'invoice'    AS ioi FROM ar UNION
-         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe
+         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id,    NULL AS deliverydate, globalproject_id, 'invoice'    AS ioi FROM ap UNION
+         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,            deliverydate, globalproject_id, 'invoice'    AS ioi FROM ar UNION
+         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
        ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
     cv         =>
       q|LEFT JOIN (
@@ -831,8 +827,9 @@ sub all_parts {
            SELECT id, name, 'vendor'   AS cv FROM vendor
          ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
     mv         => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
+    project    => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
   );
-  my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac);
+  my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
 
   my %table_prefix = (
      deliverydate => 'apoe.', serialnumber => 'ioi.',
@@ -842,8 +839,8 @@ sub all_parts {
      quonumber    => 'apoe.', model        => 'mm.',
      invnumber    => 'apoe.', partsgroup   => 'pg.',
      lastcost     => 'p.',  , soldtotal    => ' ',
-     factor       => 'pfac.',
-     'SUM(ioi.qty)' => ' ',
+     factor       => 'pfac.', projectnumber => 'pj.',
+     'SUM(ioi.qty)' => ' ',   projectdescription => 'pj.',
      description  => 'p.',
      qty          => 'ioi.',
      serialnumber => 'ioi.',
@@ -857,7 +854,7 @@ sub all_parts {
   # of the scecified table will gently override (coalesce actually) the original value
   # use it to conditionally coalesce values from subtables
   my @column_override = (
-    #  column name,   prefix,  joins_needed
+    #  column name,   prefix,  joins_needed,  nick name (in case column is named like another)
     [ 'description',  'ioi.',  'invoice_oi'  ],
     [ 'deliverydate', 'ioi.',  'invoice_oi'  ],
     [ 'transdate',    'apoe.', 'apoe'        ],
@@ -871,6 +868,11 @@ sub all_parts {
     'SUM(ioi.qty)' => 'soldtotal',
     'ioi.id'       => 'ioi_id',
     'ioi.ioi'      => 'ioi',
+    'projectdescription' => 'projectdescription',
+  );
+
+  my %real_column = (
+    projectdescription => 'description',
   );
 
   if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
@@ -880,19 +882,20 @@ sub all_parts {
   my $make_token_builder = sub {
     my $joins_needed = shift;
     sub {
-      my ($col, $alias) = @_;
+      my ($nick, $alias) = @_;
+      my ($col) = $real_column{$nick} || $nick;
       my @coalesce_tokens =
         map  { ($_->[1] || 'p.') . $_->[0] }
         grep { !$_->[2] || $joins_needed->{$_->[2]} }
-        grep {  $_->[0] eq $col }
-        @column_override, [ $col, $table_prefix{$col} ];
+        grep { ($_->[3] || $_->[0]) eq $nick }
+        @column_override, [ $col, $table_prefix{$nick}, undef , $nick ];
 
       my $coalesce = scalar @coalesce_tokens > 1;
       return ($coalesce
         ? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
         : shift                              @coalesce_tokens)
-        . ($alias && ($coalesce || $renamed_columns{$col})
-        ?  " AS " . ($renamed_columns{$col} || $col)
+        . ($alias && ($coalesce || $renamed_columns{$nick})
+        ?  " AS " . ($renamed_columns{$nick} || $nick)
         : '');
     }
   };
@@ -1001,11 +1004,12 @@ sub all_parts {
 
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
+  $joins_needed{project}     = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
   $joins_needed{mv}          = 1 if $joins_needed{makemodel};
   $joins_needed{cv}          = 1 if $bsooqr;
-  $joins_needed{apoe}        = 1 if $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
-  $joins_needed{invoice_oi}  = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
+  $joins_needed{apoe}        = 1 if $joins_needed{project} || $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
+  $joins_needed{invoice_oi}  = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
 
   # special case for description search.
   # up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
@@ -1077,13 +1081,20 @@ sub all_parts {
 
   map { $_->{onhand} *= 1 } @{ $form->{parts} };
 
+  # fix qty sign in ap. those are saved negative
+  if ($bsooqr && $form->{bought}) {
+    for my $row (@{ $form->{parts} }) {
+      $row->{qty} *= -1 if $row->{module} eq 'ir';
+    }
+  }
+
   # post processing for assembly parts lists (bom)
   # for each part get the assembly parts and add them into the partlist.
   my @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.unit, p.bin, p.notes,
            p.sellprice, p.listprice, p.lastcost,
            p.rop, p.weight, p.priceupdate,
            p.image, p.drawing, p.microfiche,
@@ -1110,6 +1121,26 @@ sub all_parts {
     $form->{parts} = \@assemblies;
   }
 
+  if ($form->{l_pricegroups} ) {
+    my $query = <<SQL;
+       SELECT parts_id, price, pricegroup_id
+       FROM prices
+       WHERE parts_id = ?
+SQL
+
+    my $sth = prepare_query($form, $dbh, $query);
+
+    foreach my $part (@{ $form->{parts} }) {
+      do_statement($form, $sth, $query, conv_i($part->{id}));
+
+      while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+        $part->{"pricegroup_$ref->{pricegroup_id}"} = $ref->{price};
+      }
+      $sth->finish;
+    }
+  };
+
+
   $main::lxdebug->leave_sub();
 
   return wantarray ? @{ $form->{parts} } : $form->{parts};
@@ -1534,6 +1565,11 @@ sub retrieve_accounts {
     } else {
       $transdate = $form->{deliverydate};
     }
+  } elsif (($form->{type} eq "credit_note") and $form->{deliverydate}) {
+    # if credit_note has a deliverydate, use this instead of invdate
+    # useful for credit_notes of invoices from an old period with different tax
+    # if there is no deliverydate then invdate is used, old default (see next elsif)
+    $transdate = $form->{deliverydate};
   } elsif (($form->{type} eq "credit_note") || ($form->{script} eq 'ir.pl')) {
     $transdate = $form->{invdate};
   } else {