use SL::HTML::Restrict;
use SL::TransNumber;
use SL::Util qw(trim);
+use SL::DB;
+use Carp;
use strict;
$form->{onhand} *= 1;
# part or service item
- $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
- if ($form->{assembly}) {
- $form->{item} = 'assembly';
+ if ($form->{part_type} eq 'assembly') {
# retrieve assembly items
$query =
qq|SELECT p.id, p.partnumber, p.description,
+ p.classification_id,
p.sellprice, p.lastcost, p.weight, a.qty, a.bom, p.unit,
pg.partsgroup, p.price_factor_id, pfac.factor AS price_factor
FROM parts p
foreach my $key (keys %{$ref}) {
$form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
}
+ $form->{"type_and_classific_$form->{assembly_rows}"} = $::request->presenter->type_abbreviation($ref->{part_type}).
+ $::request->presenter->classification_abbreviation($ref->{classification_id});
}
$sth->finish;
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
my $makemodel = ($form->{make_1} || $form->{model_1} || ($form->{makemodel_rows} > 1)) ? 1 : 0;
- $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
my ($query, $sth);
my $priceupdate = ', priceupdate = current_date';
if ($form->{id}) {
- my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, id => $form->{id});
+ my $trans_number = SL::TransNumber->new(type => $form->{part_type}, dbh => $dbh, number => $form->{partnumber}, id => $form->{id});
if (!$trans_number->is_unique) {
$::lxdebug->leave_sub;
return 3;
}
# 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}));
- if ($form->{item} eq 'assembly') {
+ if ($form->{part_type} eq 'assembly') {
# delete assembly records
do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
}
$priceupdate = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
} else {
- my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, save => 1);
+ my $trans_number = SL::TransNumber->new(type => $form->{part_type}, dbh => $dbh, number => $form->{partnumber}, save => 1);
if ($form->{partnumber} && !$trans_number->is_unique) {
$::lxdebug->leave_sub;
$form->{partnumber} ||= $trans_number->create_unique;
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
- do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit) VALUES (?, ?, ?)|, $form->{id}, $form->{partnumber}, $form->{unit});
+ do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit, part_type) VALUES (?, ?, ?, ?)|, $form->{id}, $form->{partnumber}, $form->{unit}, $form->{part_type});
$form->{orphaned} = 1;
}
}
my ($subq_inventory, $subq_expense, $subq_income);
- if ($form->{"item"} eq "part") {
+ if ($form->{part_type} eq "part") {
$subq_inventory =
qq|(SELECT bg.inventory_accno_id
FROM buchungsgruppen bg
$subq_inventory = "NULL";
}
- if ($form->{"item"} ne "assembly") {
+ if ($form->{part_type} ne "assembly") {
$subq_expense =
qq|(SELECT tc.expense_accno_id
FROM taxzone_charts tc
partnumber = ?,
description = ?,
makemodel = ?,
- alternate = 'f',
- assembly = ?,
+ classification_id = ?,
listprice = ?,
sellprice = ?,
lastcost = ?,
has_sernumber = ?,
not_discountable = ?,
microfiche = ?,
+ part_type = ?,
partsgroup_id = ?,
price_factor_id = ?
$priceupdate
@values = ($form->{partnumber},
$form->{description},
$makemodel ? 't' : 'f',
- $form->{assembly} ? 't' : 'f',
+ $form->{classification_id},
$form->{listprice},
$form->{sellprice},
$form->{lastcost},
$form->{has_sernumber} ? 't' : 'f',
$form->{not_discountable} ? 't' : 'f',
$form->{microfiche},
+ $form->{part_type},
conv_i($partsgroup_id),
conv_i($form->{price_factor_id}),
conv_i($form->{id})
}
# add assembly records
- if ($form->{item} eq 'assembly') {
+ if ($form->{part_type} eq 'assembly') {
# check additional assembly row
my $i = $form->{assembly_rows};
# if last row is not empty add them
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);
-
- $form->new_lastmtime('parts') if $id == $form->{id};
-
- $main::lxdebug->leave_sub();
+ return 1;
}
sub retrieve_assemblies {
FROM parts p2, assembly a
WHERE (p2.id = a.parts_id) AND (a.id = p.id)) AS inventory
FROM parts p
- WHERE NOT p.obsolete AND p.assembly $where|;
+ WHERE NOT p.obsolete AND p.part_type = 'assembly' $where|;
$form->{assembly_items} = selectall_hashref_query($form, $dbh, $query, @values);
}
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 {
$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.classification_id,
p.weight, p.onhand, p.unit, pg.partsgroup, p.lastcost,
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();
}
# my @other_flags = qw(onhand); # ToDO: implement these
# my @inactive_flags = qw(l_subtotal short l_linetotal);
- my @select_tokens = qw(id factor);
+ my @select_tokens = qw(id factor part_type classification_id);
my @where_tokens = qw(1=1);
my @group_tokens = ();
my @bind_vars = ();
insertdate => 'itime::DATE',
);
- if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
+ if ($form->{l_assembly} && $form->{l_lastcost}) {
@simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches;
}
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, "%$form->{all}%", "%$form->{all}%";
+ push @bind_vars, (like($form->{all})) x 2;
}
# special case insertdate
push @select_tokens, $_;
}
- for ($form->{searchitems}) {
- push @where_tokens, 'p.inventory_accno_id > 0' if /part/;
- push @where_tokens, 'p.inventory_accno_id IS NULL' if /service/;
- push @where_tokens, 'NOT p.assembly' if /service/;
- push @where_tokens, ' p.assembly' if /assembly/;
+ # Oder Bedingungen fuer Ware Dienstleistung Erzeugnis:
+ if ($form->{l_part} || $form->{l_assembly} || $form->{l_service}) {
+ my @or_tokens = ();
+ push @or_tokens, "p.part_type = 'service'" if $form->{l_service};
+ push @or_tokens, "p.part_type = 'assembly'" if $form->{l_assembly};
+ push @or_tokens, "p.part_type = 'part'" if $form->{l_part};
+ push @where_tokens, join ' OR ', map { "($_)" } @or_tokens;
+ }
+ else {
+ # gar keine Teile
+ push @where_tokens, q|'F' = 'T'|;
+ }
+
+ if ( $form->{classification_id} > 0 ) {
+ push @where_tokens, "p.classification_id = ?";
+ push @bind_vars, $form->{classification_id};
}
for ($form->{itemstatus}) {
push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi' if $bsooqr;
push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate};
- push @select_tokens, $q_assembly_lastcost if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost};
+ push @select_tokens, $q_assembly_lastcost if $form->{l_assembly} && $form->{l_lastcost};
push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem| if $form->{bought};
push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem| if $form->{sold};
push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};
# 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}) {
+ if ($form->{l_assembly} && $form->{bom}) {
$query =
qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
p.unit, p.notes, p.itime::DATE as insertdate,
$form->{parts} = \@assemblies;
}
- if ($form->{l_pricegroups} ) {
+ if ($form->{l_pricegroups} && SL::DB::Manager::Price->get_all_count() > 0 ) {
my $query = <<SQL;
SELECT parts_id, price, pricegroup_id
FROM prices
}
$sth->finish;
}
- };
-
+ }
$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}) {
}
my $query =
- qq|SELECT id, partnumber, description, unit, sellprice
+ qq|SELECT id, partnumber, description, unit, sellprice,
+ classification_id
FROM parts
WHERE $where ORDER BY $order|;
}
$j++;
+ $form->{"type_and_classific_$j"} = $::request->presenter->type_abbreviation($ref->{part_type}).
+ $::request->presenter->classification_abbreviation($ref->{classification_id});
$form->{"id_$j"} = $ref->{id};
$form->{"partnumber_$j"} = $ref->{partnumber};
$form->{"description_$j"} = $ref->{description};
my %accno_by_part = map { $_->{id} => $_ }
selectall_hashref_query($form, $dbh, <<SQL, @part_ids);
SELECT
- p.id, p.inventory_accno_id AS is_part,
+ p.id, p.part_type,
bg.inventory_accno_id,
tc.income_accno_id AS income_accno_id,
tc.expense_accno_id AS expense_accno_id,
while (my ($index => $part_id) = each %args) {
my $ref = $accno_by_part{$part_id} or next;
- $ref->{"inventory_accno_id"} = undef unless $ref->{"is_part"};
+ $ref->{"inventory_accno_id"} = undef unless $ref->{"part_type"} eq 'part';
my %accounts;
for my $type (qw(inventory income expense)) {
for my $i (1..$rowcount) {
my $id = $form->{"${prefix}${i}"};
next unless $id;
-
- push @{ $template_arrays{part_type} }, $parts_by_id{$id}->type;
+ my $prt = $parts_by_id{$id};
+ my $type_abbr = $::request->presenter->type_abbreviation($prt->part_type);
+ push @{ $template_arrays{part_type} }, $type_abbr;
+ push @{ $template_arrays{type_and_classific}}, $type_abbr.$::request->presenter->classification_abbreviation($prt->classification_id);
+ push @{ $template_arrays{separate} }, $::request->presenter->separate_abbreviation($prt->classification_id);
}
- return %template_arrays;
$main::lxdebug->leave_sub();
+ return %template_arrays;
}
sub normalize_text_blocks {
$main::lxdebug->leave_sub();
}
-
1;
projects / mfinanz.git / blobdiff