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;
# 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
|;
if ($form->{type} && is_valid_type($form->{type})) {
- push @where, 'dord.order_type = ?';
+ push @where, 'dord.record_type = ?';
push @values, $form->{type};
}
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)";
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 (
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
"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';
$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();
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;
$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);
}
$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"}),
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 = ?|;
Common::webdav_folder($form);
+ $validity_token->delete if $validity_token;
+ delete $form->{form_validity_token};
+
$main::lxdebug->leave_sub();
return 1;
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';
(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;
projects / mfinanz.git / blobdiff