X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ff159a4d47b9a2d10744dcfc23da2c63605c8a32..eeb5375ee7727c956cc357cc8f90b19d1bfe80b9:/SL/DO.pm

diff --git a/SL/DO.pm b/SL/DO.pm
index ebc605159..500d1efb1 100644
--- a/SL/DO.pm
+++ b/SL/DO.pm
@@ -44,6 +44,7 @@ use SL::CVar;
 use SL::DB::DeliveryOrder;
 use SL::DB::DeliveryOrder::TypeData qw(:types is_valid_type);
 use SL::DB::Status;
+use SL::DB::ValidityToken;
 use SL::DBUtils;
 use SL::Helper::ShippedQty;
 use SL::HTML::Restrict;
@@ -67,20 +68,23 @@ sub transactions {
   # connect to database
   my $dbh = $form->get_standard_dbh($myconfig);
 
-  my (@where, @values, $where);
+  my (@where, @values);
 
   my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
 
+  $form->{l_order_confirmation_number} = 'Y' if $form->{order_confirmation_number};
+
   my $query =
     qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.cusordnumber,
          dord.transdate, dord.reqdate,
+         dord.vendor_confirmation_number,
          ct.${vc}number, ct.name, ct.business_id,
          dord.${vc}_id, dord.globalproject_id,
          dord.closed, dord.delivered, dord.shippingpoint, dord.shipvia,
          dord.transaction_description, dord.itime::DATE AS insertdate,
          pr.projectnumber AS globalprojectnumber,
          dep.description AS department,
-         dord.order_type,
+         dord.record_type,
          e.name AS employee,
          sm.name AS salesman
        FROM delivery_orders dord
@@ -93,7 +97,7 @@ sub transactions {
 |;
 
   if ($form->{type} && is_valid_type($form->{type})) {
-    push @where, 'dord.order_type = ?';
+    push @where, 'dord.record_type = ?';
     push @values, $form->{type};
   }
 
@@ -140,12 +144,25 @@ sub transactions {
     push @values, $::myconfig{login};
   }
 
-  foreach my $item (qw(donumber ordnumber cusordnumber transaction_description)) {
+  foreach my $item (qw(donumber ordnumber cusordnumber transaction_description vendor_confirmation_number)) {
     next unless ($form->{$item});
     push @where,  qq|dord.$item ILIKE ?|;
     push @values, like($form->{$item});
   }
 
+  if ($form->{order_confirmation_number}) {
+    push @where, qq|(EXISTS (SELECT id FROM oe
+                             WHERE ordnumber ILIKE ?
+                               AND record_type = 'purchase_order_confirmation'
+                               AND id IN
+                               (SELECT from_id FROM record_links
+                                WHERE to_id = dord.id
+                                  AND to_table = 'delivery_orders'
+                                  AND from_table = 'oe')
+                    ))|;
+    push @values, like($form->{order_confirmation_number});
+  }
+
   if (($form->{open} || $form->{closed}) &&
       ($form->{open} ne $form->{closed})) {
     push @where, ($form->{open} ? "NOT " : "") . "COALESCE(dord.closed, FALSE)";
@@ -191,6 +208,36 @@ sub transactions {
     push @values, conv_date($form->{insertdateto});
   }
 
+  $form->{fulltext} = trim($form->{fulltext});
+  if ($form->{fulltext}) {
+    my @fulltext_fields = qw(dord.notes
+                             dord.intnotes
+                             dord.shippingpoint
+                             dord.shipvia
+                             dord.transaction_description
+                             dord.donumber
+                             dord.ordnumber
+                             dord.cusordnumber
+                             dord.oreqnumber
+                             dord.vendor_confirmation_number
+                             oe.ordnumber
+                            );
+    my $tmp_where = '';
+    $tmp_where .= join ' OR ', map {"$_ ILIKE ?"} @fulltext_fields;
+    push(@values, like($form->{fulltext})) for 1 .. (scalar @fulltext_fields);
+
+    $tmp_where .= <<SQL;
+      OR EXISTS (
+        SELECT files.id FROM files LEFT JOIN file_full_texts ON (file_full_texts.file_id = files.id)
+          WHERE files.object_id = dord.id AND files.object_type = ?
+            AND file_full_texts.full_text ILIKE ?)
+SQL
+    push(@values, $form->{type});
+    push(@values, like($form->{fulltext}));
+    push @where, $tmp_where;
+
+  }
+
   if ($form->{parts_partnumber}) {
     push @where, <<SQL;
       EXISTS (
@@ -218,6 +265,27 @@ SQL
     push @values, like($form->{parts_description});
   }
 
+  if ($form->{chargenumber}) {
+    push @where, <<SQL;
+      EXISTS (
+        SELECT delivery_order_items_stock.id
+        FROM delivery_order_items_stock
+        LEFT JOIN delivery_order_items ON (delivery_order_items.id = delivery_order_items_stock.delivery_order_item_id)
+        WHERE delivery_order_items.delivery_order_id = dord.id
+          AND delivery_order_items_stock.chargenumber ILIKE ?
+        LIMIT 1
+      )
+SQL
+    push @values, like($form->{chargenumber});
+  }
+
+  if ($form->{ids}) {
+    unshift @{$form->{ids}}, 0; # no error and no results if no ids are given
+    my $placeholders = join(', ', ('?') x scalar(@{$form->{ids}}));
+    push @where, "dord.id IN ($placeholders)";
+    push @values, @{$form->{ids}};
+  }
+
   if ($form->{all}) {
     my @tokens = parse_line('\s+', 0, $form->{all});
     # ordnumber quonumber customer.name vendor.name transaction_description
@@ -246,6 +314,7 @@ SQL
     "transaction_description" => "dord.transaction_description",
     "department"              => "lower(dep.description)",
     "insertdate"              => "dord.itime",
+    "vendor_confirmation_number" => "dord.vendor_confirmation_number",
   );
 
   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
@@ -258,23 +327,63 @@ SQL
 
   $form->{DO} = selectall_hashref_query($form, $dbh, $query, @values);
 
+  my $record_type = $vc eq 'customer' ? 'sales_order' : 'purchase_order';
   if (scalar @{ $form->{DO} }) {
     $query =
       qq|SELECT id
          FROM oe
-         WHERE NOT COALESCE(quotation, FALSE)
-           AND (ordnumber = ?)
-           AND (COALESCE(${vc}_id, 0) != 0)|;
+         WHERE (record_type = '$record_type'
+           AND ordnumber = ?)|;
 
     my $sth = prepare_query($form, $dbh, $query);
 
+
+    my ($items_query, $items_sth);
+    if ($form->{l_items}) {
+      $items_query =
+        qq|SELECT id
+          FROM delivery_order_items
+          WHERE delivery_order_id = ?
+          ORDER BY position|;
+
+      $items_sth = prepare_query($form, $dbh, $items_query);
+    }
+
+    my ($order_confirmation_query, $order_confirmation_sth);
+    if ($form->{l_order_confirmation_number}) {
+      $order_confirmation_query =
+        qq|SELECT id, ordnumber FROM oe
+           WHERE id IN
+               (SELECT from_id FROM record_links
+                WHERE from_table = 'oe'
+                  AND to_table = 'delivery_orders'
+                  AND to_id = ?)
+             AND record_type = 'purchase_order_confirmation' ORDER BY ordnumber|;
+
+      $order_confirmation_sth = prepare_query($form, $dbh, $order_confirmation_query);
+    }
+
     foreach my $dord (@{ $form->{DO} }) {
+      if ($form->{l_items}) {
+        do_statement($form, $items_sth, $items_query, $dord->{id});
+        $dord->{item_ids} = $dbh->selectcol_arrayref($items_sth);
+        $dord->{item_ids} = undef if !@{$dord->{item_ids}};
+      }
+
+      if ($form->{l_order_confirmation_number}) {
+        do_statement($form, $order_confirmation_sth, $order_confirmation_query, $dord->{id});
+        my @r = @{$order_confirmation_sth->fetchall_arrayref()};
+        push @{$dord->{order_confirmation_numbers}}, { id => $_->[0], number => $_->[1] } for @r;
+      }
+
       next unless ($dord->{ordnumber});
       do_statement($form, $sth, $query, $dord->{ordnumber});
       ($dord->{oe_id}) = $sth->fetchrow_array();
     }
 
     $sth->finish();
+    $items_sth->finish()              if $form->{l_items};
+    $order_confirmation_sth->finish() if $form->{l_order_confirmation_number};
   }
 
   $main::lxdebug->leave_sub();
@@ -298,6 +407,16 @@ sub _save {
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
+  my $validity_token;
+  if (!$form->{id}) {
+    $validity_token = SL::DB::Manager::ValidityToken->fetch_valid_token(
+      scope => SL::DB::ValidityToken::SCOPE_DELIVERY_ORDER_SAVE(),
+      token => $form->{form_validity_token},
+    );
+
+    die $::locale->text('The form is not valid anymore.') if !$validity_token;
+  }
+
   my $dbh = SL::DB->client->dbh;
   my $restricter = SL::HTML::Restrict->create;
 
@@ -334,7 +453,7 @@ sub _save {
     $query = qq|SELECT nextval('id')|;
     ($form->{id}) = selectrow_query($form, $dbh, $query);
 
-    $query = qq|INSERT INTO delivery_orders (id, donumber, employee_id, currency_id, taxzone_id, order_type) VALUES (?, '', ?, (SELECT currency_id FROM defaults LIMIT 1), ?, ?)|;
+    $query = qq|INSERT INTO delivery_orders (id, donumber, employee_id, currency_id, taxzone_id, record_type) VALUES (?, '', ?, (SELECT currency_id FROM defaults LIMIT 1), ?, ?)|;
     do_query($form, $dbh, $query, $form->{id}, conv_i($form->{employee_id}), $form->{taxzone_id}, SALES_DELIVERY_ORDER_TYPE);
   }
 
@@ -430,7 +549,7 @@ SQL
                $form->{"qty_$i"}, $baseqty,
                $form->{"sellprice_$i"}, $form->{"discount_$i"} / 100,
                $form->{"unit_$i"}, conv_date($items_reqdate), conv_i($form->{"project_id_$i"}),
-               $form->{"serialnumber_$i"},
+               trim($form->{"serialnumber_$i"}),
                $form->{"lastcost_$i"},
                conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
                conv_i($form->{"marge_price_factor_$i"}),
@@ -521,7 +640,7 @@ SQL
          shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, closed = ?,
          delivered = ?, department_id = ?, language_id = ?, shipto_id = ?, billing_address_id = ?,
          globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?,
-         order_type = ?, taxzone_id = ?, taxincluded = ?, payment_id = ?, currency_id = (SELECT id FROM currencies WHERE name = ?),
+         record_type = ?, taxzone_id = ?, taxincluded = ?, payment_id = ?, currency_id = (SELECT id FROM currencies WHERE name = ?),
          delivery_term_id = ?
        WHERE id = ?|;
 
@@ -574,6 +693,9 @@ SQL
 
   Common::webdav_folder($form);
 
+  $validity_token->delete if $validity_token;
+  delete $form->{form_validity_token};
+
   $main::lxdebug->leave_sub();
 
   return 1;
@@ -852,8 +974,10 @@ sub retrieve {
                                            trans_id   => $doi->{delivery_order_items_id},
                                           );
     map { $doi->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
-  }
 
+    my $makemodel = SL::DB::Manager::MakeModel->find_by(parts_id => $doi->{id}, make =>$form->{vendor_id});
+    $doi->{vendor_partnumber} = $makemodel->model if $makemodel;
+  }
   if ($mode eq 'single') {
     my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in';
 
@@ -945,7 +1069,7 @@ sub order_details {
                            (SELECT description FROM warehouse WHERE id = ?) AS warehouse|;
   my $h_bin_wh = prepare_query($form, $dbh, $q_bin_wh);
 
-  my $in_out   = $form->{type} =~ /^sales/ ? 'out' : 'in';
+  my $in_out   = $form->{type} =~ /^sales|^supplier/ ? 'out' : 'in';
 
   my $num_si   = 0;