- #=====================================================================
+#=====================================================================
# LX-Office ERP
# Copyright (C) 2004
# Based on SQL-Ledger Version 2.1.9
# get makes
if ($form->{makemodel}) {
- $query = qq|SELECT m.make, m.model FROM makemodel m | .
- qq|WHERE m.parts_id = ?|;
+ #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"}) = $sth->fetchrow_array)
+
+ while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
+ $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
{
$i++;
}
# insert makemodel records
unless ($form->{item} eq 'service') {
+ my $lastupdate = '';
+ my $value = 0;
for my $i (1 .. $form->{makemodel_rows}) {
if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
-
- $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
- qq|VALUES (?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
+ #hli
+ $value = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
+ if ($value == $form->{"old_lastcost_$i"})
+ {
+ if ($form->{"lastupdate_$i"} eq "") {
+ $lastupdate = 'now()';
+ } else {
+ $lastupdate = $dbh->quote($form->{"lastupdate_$i"});
+ }
+ } else {
+ $lastupdate = 'now()';
+ }
+ $query = qq|INSERT INTO makemodel (parts_id, make, model, lastcost, lastupdate, sortorder) | .
+ qq|VALUES (?, ?, ?, ?, ?, ?)|;
+ @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"}, $value, $lastupdate, conv_i($form->{"sortorder_$i"}) );
do_query($form, $dbh, $query, @values);
}
}
}
- CVar->save_custom_variables('dbh' => $dbh,
- 'module' => 'IC',
- 'trans_id' => $form->{id},
- 'variables' => $form);
+ CVar->save_custom_variables(dbh => $dbh,
+ module => 'IC',
+ trans_id => $form->{id},
+ variables => $form,
+ save_validity => 1);
# commit
my $rc = $dbh->commit;
my @makemodel_filters = qw(make model);
my @invoice_oi_filters = qw(serialnumber soldtotal);
my @apoe_filters = qw(transdate);
- my @like_filters = (@simple_filters, @makemodel_filters, @invoice_oi_filters);
+ 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 @oe_flags = qw(bought sold onorder ordered rfq quoted);
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|,
+ mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
);
- my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
+ my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac);
my %table_prefix = (
deliverydate => 'apoe.', serialnumber => 'ioi.',
ordnumber => 'apoe.', make => 'mm.',
quonumber => 'apoe.', model => 'mm.',
invnumber => 'apoe.', partsgroup => 'pg.',
- lastcost => ' ', , soldtotal => ' ',
+ lastcost => 'p.', , soldtotal => ' ',
factor => 'pfac.',
'SUM(ioi.qty)' => ' ',
description => 'p.',
WHERE (a_lc.id = p.id)) AS lastcost|;
$table_prefix{$q_assembly_lastcost} = ' ';
+ # special case makemodel search
+ # all_parts is based upon the assumption that every parameter is named like the column it represents
+ # unfortunately make would have to match vendor.name which is already taken for vendor.name in bsooqr mode.
+ # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
+ if ($form->{make}) {
+ push @where_tokens, 'mv.name ILIKE ?';
+ push @bind_vars, "%$form->{make}%";
+ }
+ if ($form->{model}) {
+ push @where_tokens, 'mm.model ILIKE ?';
+ push @bind_vars, "%$form->{model}%";
+ }
+
# special case: sorting by partnumber
# since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
# and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.
$joins_needed{partsgroup} = 1;
$joins_needed{pfac} = 1;
$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;
}
if ($transdate eq "") {
- $transdate = "current_date";
+ $transdate = DateTime->today_local->to_lxoffice;
} else {
$transdate = $dbh->quote($transdate);
}
projects / kivitendo-erp.git / blobdiff