use SL::DBUtils;
use SL::HTML::Restrict;
use SL::TransNumber;
+use SL::Util qw(trim);
+use SL::DB;
use strict;
# copy to $form variables
map { $form->{$_} = $ref->{$_} } (keys %{$ref});
+ $form->{mtime} = $form->{itime} if !$form->{mtime};
+ $form->{lastmtime} = $form->{mtime};
$form->{onhand} *= 1;
# part or service item
my $i = 1;
foreach my $pg (@{ $pricegroups }) {
- $form->{"klass_$i"} = "$pg->{id}";
- $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
+ $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
$form->{"pricegroup_id_$i"} = "$pg->{id}";
$form->{"pricegroup_$i"} = "$pg->{pricegroup}";
$i++;
}
sub save {
+ my ($self, $myconfig, $form) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
+
+ $main::lxdebug->leave_sub();
+ return $rc;
+}
+
+sub _save {
my ($self, $myconfig, $form) = @_;
my @values;
- # connect to database, turn off AutoCommit
- my $dbh = $form->get_standard_dbh;
+
+ my $dbh = SL::DB->client->dbh;
my $restricter = SL::HTML::Restrict->create;
# save the part
}
# get old price
- $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
- my ($sellprice, $weight) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
-
- # if item is part of an assembly adjust all assemblies
- $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
- $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
- while (my ($id, $qty) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $id, $qty, $sellprice * 1, $weight * 1);
- }
- $sth->finish;
+ $query = qq|SELECT sellprice FROM parts WHERE id = ?|;
+ my ($sellprice) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
# delete makemodel records
do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
partnumber = ?,
description = ?,
makemodel = ?,
- alternate = 'f',
assembly = ?,
listprice = ?,
sellprice = ?,
);
do_query($form, $dbh, $query, @values);
+ $form->new_lastmtime('parts');
+
# delete translation records
do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
SQL
do_query($form, $dbh, $query, ($form->{id}) x 2);
- # commit
- my $rc = $dbh->commit;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-sub update_assembly {
- $main::lxdebug->enter_sub();
-
- my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
-
- my $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
- my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
-
- while (my ($pid, $aqty) = $sth->fetchrow_array) {
- &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
- }
- $sth->finish;
-
- $query =
- qq|UPDATE parts SET sellprice = sellprice + ?, weight = weight + ?
- WHERE id = ?|;
- my @values = ($qty * ($form->{sellprice} - $sellprice),
- $qty * ($form->{weight} - $weight), conv_i($id));
- do_query($form, $dbh, $query, @values);
-
- $main::lxdebug->leave_sub();
+ return 1;
}
sub retrieve_assemblies {
if ($form->{partnumber}) {
$where .= qq| AND (p.partnumber ILIKE ?)|;
- push(@values, '%' . $form->{partnumber} . '%');
+ push(@values, like($form->{partnumber}));
}
if ($form->{description}) {
$where .= qq| AND (p.description ILIKE ?)|;
- push(@values, '%' . $form->{description} . '%');
+ push(@values, like($form->{description}));
}
# retrieve assembly items
}
sub delete {
+ my ($self, $myconfig, $form) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_delete, $self, $myconfig, $form);
+
+ $main::lxdebug->leave_sub();
+ return $rc;
+}
+
+sub _delete {
my ($self, $myconfig, $form) = @_;
my @values = (conv_i($form->{id}));
- # connect to database, turn off AutoCommit
- my $dbh = $form->get_standard_dbh;
my %columns = ( "assembly" => "id", "parts" => "id" );
for my $table (qw(prices makemodel inventory assembly translation parts)) {
my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
- do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
+ do_query($form, SL::DB->client->dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
}
- # commit
- my $rc = $dbh->commit;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub assembly_item {
while (my ($column, $table) = each(%columns)) {
next unless ($form->{"${column}_$i"});
$where .= qq| AND ${table}.${column} ILIKE ?|;
- push(@values, '%' . $form->{"${column}_$i"} . '%');
+ push(@values, like($form->{"${column}_$i"}));
}
if ($form->{id}) {
$where .= qq| ORDER BY p.description|;
}
- # connect to database
- my $dbh = $form->get_standard_dbh;
-
my $query =
qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
p.weight, p.onhand, p.unit, pg.partsgroup, p.lastcost,
- p.price_factor_id, pfac.factor AS price_factor
+ p.price_factor_id, pfac.factor AS price_factor, p.notes as longdescription
FROM parts p
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
LEFT JOIN price_factors pfac ON pfac.id = p.price_factor_id
WHERE $where|;
- $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
+ $form->{item_list} = selectall_hashref_query($form, SL::DB->client->dbh, $query, @values);
$main::lxdebug->leave_sub();
}
#===== switches and simple filters ========#
# special case transdate
- if (grep { $form->{$_} } qw(transdatefrom transdateto)) {
+ if (grep { trim($form->{$_}) } qw(transdatefrom transdateto)) {
$form->{"l_transdate"} = 1;
push @select_tokens, 'transdate';
for (qw(transdatefrom transdateto)) {
- next unless $form->{$_};
+ my $value = trim($form->{$_});
+ next unless $value;
push @where_tokens, sprintf "transdate %s ?", /from$/ ? '>=' : '<=';
- push @bind_vars, $form->{$_};
+ push @bind_vars, $value;
}
}
+ # special case smart search
+ if ($form->{all}) {
+ $form->{"l_$_"} = 1 for qw(partnumber description unit sellprice lastcost cvar_packaging linetotal);
+ push @where_tokens, "p.partnumber ILIKE ? OR p.description ILIKE ?";
+ push @bind_vars, (like($form->{all})) x 2;
+ }
+
# special case insertdate
- if (grep { $form->{$_} } qw(insertdatefrom insertdateto)) {
+ if (grep { trim($form->{$_}) } qw(insertdatefrom insertdateto)) {
$form->{"l_insertdate"} = 1;
push @select_tokens, 'insertdate';
my $token = $token_builder->('insertdate');
for (qw(insertdatefrom insertdateto)) {
- next unless $form->{$_};
+ my $value = trim($form->{$_});
+ next unless $value;
push @where_tokens, sprintf "$token %s ?", /from$/ ? '>=' : '<=';
- push @bind_vars, $form->{$_};
+ push @bind_vars, $value;
}
}
next unless $form->{$_};
$form->{"l_$_"} = '1'; # show the column
push @where_tokens, "$table_prefix{$_}$_ ILIKE ?";
- push @bind_vars, "%$form->{$_}%";
+ push @bind_vars, like($form->{$_});
}
foreach (@simple_l_switches) {
# 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}%";
+ push @bind_vars, like($form->{make});
}
if ($form->{model}) {
push @where_tokens, 'mm.model ILIKE ?';
- push @bind_vars, "%$form->{model}%";
+ push @bind_vars, like($form->{model});
}
# special case: sorting by partnumber
next unless ($form->{$column});
$where .= qq| AND $item ILIKE ?|;
- push(@where_values, '%' . $form->{$column} . '%');
+ push(@where_values, like($form->{$column}));
}
foreach my $item (qw(description serialnumber)) {
next unless ($form->{$item});
$where .= qq| AND (${item} ILIKE ?)|;
- push(@where_values, '%' . $form->{$item} . '%');
+ push(@where_values, like($form->{$item}));
}
foreach my $column (qw(make model)) {
next unless ($form->{$column});
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
- push(@where_values, '%' . $form->{$column} . '%');
+ push(@where_values, like($form->{$column}));
}
$main::lxdebug->leave_sub();
}
sub update_prices {
+ my ($self, $myconfig, $form) = @_;
$main::lxdebug->enter_sub();
+ my $num_updated = SL::DB->client->with_transaction(\&_update_prices, $self, $myconfig, $form);
+
+ $main::lxdebug->leave_sub();
+ return $num_updated;
+}
+
+sub _update_prices {
my ($self, $myconfig, $form) = @_;
my ($where, @where_values) = $self->_create_filter_for_priceupdate();
my $num_updated = 0;
# connect to database
- my $dbh = $form->get_standard_dbh;
+ my $dbh = SL::DB->client->dbh;
for my $column (qw(sellprice listprice)) {
next if ($form->{$column} eq "");
$sth_add->finish();
$sth_multiply->finish();
- my $rc= $dbh->commit;
-
- $main::lxdebug->leave_sub();
-
return $num_updated;
}
# connect to database
my $dbh = $form->get_standard_dbh;
- my @values = ('%' . $module . '%');
+ my @values = like($module);
my $query;
if ($form->{id}) {
if ($sortorder eq "all") {
$where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
- push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
+ push(@values, like($form->{partnumber}), like($form->{description}));
} elsif ($sortorder eq "partnumber") {
$where .= qq| AND (partnumber ILIKE ?)|;
- push(@values, '%' . $form->{partnumber} . '%');
+ push(@values, like($form->{partnumber}));
} elsif ($sortorder eq "description") {
$where .= qq| AND (description ILIKE ?)|;
- push(@values, '%' . $form->{description} . '%');
+ push(@values, like($form->{description}));
$order = "description";
}