#
# 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',
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);
}