use strict;
-sub get_pricegroups {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->get_standard_dbh;
-
- # get pricegroups
- my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|;
- my $pricegroups = selectall_hashref_query($form, $dbh, $query);
-
- my $i = 1;
- foreach my $pg (@{ $pricegroups }) {
- $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
- $form->{"pricegroup_id_$i"} = "$pg->{id}";
- $form->{"pricegroup_$i"} = "$pg->{pricegroup}";
- $i++;
- }
-
- #correct rows
- $form->{price_rows} = $i - 1;
-
- $main::lxdebug->leave_sub();
-
- return $pricegroups;
-}
-
sub retrieve_buchungsgruppen {
$main::lxdebug->enter_sub();
$main::lxdebug->leave_sub();
}
-
sub assembly_item {
$main::lxdebug->enter_sub();
#
# column flags:
# l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_rop l_image l_drawing l_microfiche l_partsgroup
+# l_warehouse l_bin
#
# exclusives:
# itemstatus = active | onhand | short | obsolete | orphaned
# joining filters:
# make model - makemodel
# serialnumber transdatefrom transdateto - invoice/orderitems
+# warehouse - warehouse
+# bin - bin
#
# binary flags:
# bought sold onorder ordered rfq quoted - aggreg joins with invoices/orders
# onhand - as above, but masking the simple itemstatus results (doh!)
# warehouse onhand
# search by overrides of description
+# soldtotal drops option default warehouse and bin
+# soldtotal can not work if there are no documents checked
#
# disabled sanity checks and changes:
# - searchitems = assembly will no longer disable bought
my ($self, $myconfig, $form) = @_;
my $dbh = $form->get_standard_dbh($myconfig);
+ # sanity backend check
+ croak "Cannot combine soldtotal with default bin or default warehouse" if ($form->{l_soldtotal} && ($form->{l_bin} || $form->{l_warehouse}));
+
$form->{parts} = +{ };
$form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
my @like_filters = (@simple_filters, @invoice_oi_filters);
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop insertdate));
+ my %no_simple_l_switches = (warehouse => 'wh.description as warehouse', bin => 'bin.description as bin');
my @oe_flags = qw(bought sold onorder ordered rfq quoted);
my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty);
my @deliverydate_flags = qw(deliverydate);
) 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',
project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
+ warehouse => 'LEFT JOIN warehouse AS wh ON wh.id = p.warehouse_id',
+ bin => 'LEFT JOIN bin ON bin.id = p.bin_id',
);
- my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
+ my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project warehouse bin);
my %table_prefix = (
deliverydate => 'apoe.', serialnumber => 'ioi.',
$joins_needed{cv} = 1 if $bsooqr;
$joins_needed{apoe} = 1 if $joins_needed{project} || $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
$joins_needed{invoice_oi} = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
+ $joins_needed{bin} = 1 if $form->{l_bin};
+ $joins_needed{warehouse} = 1 if $form->{l_warehouse};
# special case for description search.
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
my $group_clause = @group_tokens ? ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens : '';
+ # key of %no_simple_l_switch is the logical l_switch.
+ # the assigned value is the 'not so simple
+ # select token'
+ my $no_simple_select_clause;
+ foreach my $no_simple_l_switch (keys %no_simple_l_switches) {
+ next unless $form->{"l_${no_simple_l_switch}"};
+ $no_simple_select_clause .= ', '. $no_simple_l_switches{$no_simple_l_switch};
+ }
+ $select_clause .= $no_simple_select_clause;
+
my %oe_flag_to_cvar = (
bought => 'invoice',
sold => 'invoice',
$main::lxdebug->leave_sub();
- return @{ $form->{parts} };
-}
-
-sub _create_filter_for_priceupdate {
- $main::lxdebug->enter_sub();
-
- my $self = shift;
- my $myconfig = \%main::myconfig;
- my $form = $main::form;
-
- my @where_values;
- my $where = '1 = 1';
-
- foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
- my $column = $item;
- $column =~ s/.*\.//;
- next unless ($form->{$column});
-
- $where .= qq| AND $item ILIKE ?|;
- push(@where_values, like($form->{$column}));
- }
-
- foreach my $item (qw(description serialnumber)) {
- next unless ($form->{$item});
-
- $where .= qq| AND (${item} ILIKE ?)|;
- push(@where_values, like($form->{$item}));
- }
-
-
- # items which were never bought, sold or on an order
- if ($form->{itemstatus} eq 'orphaned') {
- $where .=
- qq| AND (p.onhand = 0)
- AND p.id NOT IN
- (
- SELECT DISTINCT parts_id FROM invoice
- UNION
- SELECT DISTINCT parts_id FROM assembly
- UNION
- SELECT DISTINCT parts_id FROM orderitems
- )|;
-
- } elsif ($form->{itemstatus} eq 'active') {
- $where .= qq| AND p.obsolete = '0'|;
-
- } elsif ($form->{itemstatus} eq 'obsolete') {
- $where .= qq| AND p.obsolete = '1'|;
-
- } elsif ($form->{itemstatus} eq 'onhand') {
- $where .= qq| AND p.onhand > 0|;
-
- } elsif ($form->{itemstatus} eq 'short') {
- $where .= qq| AND p.onhand < p.rop|;
-
- }
-
- 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, like($form->{$column}));
- }
-
- $main::lxdebug->leave_sub();
-
- return ($where, @where_values);
-}
-
-sub get_num_matches_for_priceupdate {
- $main::lxdebug->enter_sub();
-
- my $self = shift;
-
- my $myconfig = \%main::myconfig;
- my $form = $main::form;
-
- my $dbh = $form->get_standard_dbh($myconfig);
-
- my ($where, @where_values) = $self->_create_filter_for_priceupdate();
-
- my $num_updated = 0;
- my $query;
-
- for my $column (qw(sellprice listprice)) {
- next if ($form->{$column} eq "");
-
- $query =
- qq|SELECT COUNT(*)
- FROM parts
- WHERE id IN
- (SELECT p.id
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where)|;
- my ($result) = selectfirst_array_query($form, $dbh, $query, @where_values);
- $num_updated += $result if (0 <= $result);
- }
-
- $query =
- qq|SELECT COUNT(*)
- FROM prices
- WHERE parts_id IN
- (SELECT p.id
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where) AND (pricegroup_id = ?)|;
- my $sth = prepare_query($form, $dbh, $query);
-
- for my $i (1 .. $form->{price_rows}) {
- next if ($form->{"price_$i"} eq "");
-
- my ($result) = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));
- $num_updated += $result if (0 <= $result);
- }
- $sth->finish();
-
- $main::lxdebug->leave_sub();
-
- return $num_updated;
-}
-
-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 = SL::DB->client->dbh;
-
- for my $column (qw(sellprice listprice)) {
- next if ($form->{$column} eq "");
-
- my $value = $form->parse_amount($myconfig, $form->{$column});
- my $operator = '+';
-
- if ($form->{"${column}_type"} eq "percent") {
- $value = ($value / 100) + 1;
- $operator = '*';
- }
-
- my $query =
- qq|UPDATE parts SET $column = $column $operator ?
- WHERE id IN
- (SELECT p.id
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where)|;
- my $result = do_query($form, $dbh, $query, $value, @where_values);
- $num_updated += $result if (0 <= $result);
- }
-
- my $q_add =
- qq|UPDATE prices SET price = price + ?
- WHERE parts_id IN
- (SELECT p.id
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where) AND (pricegroup_id = ?)|;
- my $sth_add = prepare_query($form, $dbh, $q_add);
-
- my $q_multiply =
- qq|UPDATE prices SET price = price * ?
- WHERE parts_id IN
- (SELECT p.id
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where) AND (pricegroup_id = ?)|;
- my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
-
- for my $i (1 .. $form->{price_rows}) {
- next if ($form->{"price_$i"} eq "");
-
- my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
- my $result;
-
- if ($form->{"pricegroup_type_$i"} eq "percent") {
- $result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
- } else {
- $result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
- }
-
- $num_updated += $result if (0 <= $result);
- }
-
- $sth_add->finish();
- $sth_multiply->finish();
-
- return $num_updated;
+ return $form->{parts};
}
# get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
p.id IN ($in)
SQL
- my $sth_tax = prepare_query($::form, $dbh, <<SQL);
+ my $query_tax = <<SQL;
SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber
FROM tax t
LEFT JOIN chart c ON c.id = t.chart_id
WHERE tk.chart_id = ? AND startdate <= ?
ORDER BY startdate DESC LIMIT 1)
SQL
+ my $sth_tax = prepare_query($::form, $dbh, $query_tax);
while (my ($index => $part_id) = each %args) {
my $ref = $accno_by_part{$part_id} or next;
$form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
- $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate));
+ $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)) || $::form->dberror($query_tax);
$ref = $sth_tax->fetchrow_hashref or next;
$form->{"taxaccounts_$index"} = $ref->{"accno"};
next unless $id;
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{part_type} }, $prt->part_type;
+ push @{ $template_arrays{part_abbreviation} }, $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);
}
projects / kivitendo-erp.git / blobdiff