# l_warehouse l_bin
#
# exclusives:
-# itemstatus = active | onhand | short | obsolete | orphaned
+# itemstatus = active | onhand | short | order_locked | obsolete | orphaned
# searchitems = part | assembly | service
#
# joining filters:
$form->{parts} = +{ };
$form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
- my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand);
+ my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand notes);
my @project_filters = qw(projectnumber projectdescription);
my @makemodel_filters = qw(make model);
my @invoice_oi_filters = qw(serialnumber soldtotal);
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 %no_simple_l_switches = (warehouse => 'wh.description as warehouse', bin => 'bin.description as bin', price_factor_description => 'pfac.description as price_factor_description');
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);
my %joins = (
partsgroup => 'LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)',
- makemodel => 'LEFT JOIN makemodel mm ON (mm.parts_id = p.id)',
+ makemodel => "LEFT JOIN LATERAL (
+ SELECT string_agg(mv.vendornumber || ' ' || mv.name, ', ') AS make,
+ string_agg(mm.model, ', ') AS model
+ FROM makemodel mm
+ LEFT JOIN vendor mv ON (mv.id = mm.make)
+ WHERE mm.parts_id = p.id
+ ) mm ON TRUE",
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)',
invoice_oi =>
q|LEFT JOIN (
) AS ioi ON ioi.parts_id = p.id|,
apoe =>
q|LEFT JOIN (
- SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, globalproject_id, 'invoice' AS ioi FROM ap UNION
- SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, globalproject_id, 'invoice' AS ioi FROM ar UNION
- SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
+ SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, 'purchase_invoice' AS record_type, NULL AS customer_id, vendor_id, NULL AS deliverydate, globalproject_id, 'invoice' AS ioi FROM ap UNION
+ SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, 'sales_invoice' AS record_type, customer_id, NULL AS vendor_id, deliverydate, globalproject_id, 'invoice' AS ioi FROM ar UNION
+ SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, record_type::text, customer_id, vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
cv =>
q|LEFT JOIN (
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',
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 warehouse bin);
+ my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac project warehouse bin);
my %table_prefix = (
deliverydate => 'apoe.', serialnumber => 'ioi.',
description => 'p.',
qty => 'ioi.',
serialnumber => 'ioi.',
- quotation => 'apoe.',
+ record_type => 'apoe.',
cv => 'cv.',
"ioi.id" => ' ',
"ioi.ioi" => ' ',
push @where_tokens, ' p.obsolete', if /obsolete/;
push @where_tokens, 'p.onhand > 0', if /onhand/;
push @where_tokens, 'p.onhand < p.rop', if /short/;
+ push @where_tokens, 'p.order_locked', if /order_locked/;
}
my $q_assembly_lastcost =
# 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.
+ # the lateral join mm already creates a string consisting of vendornumbers and vendornames in mm.make
+ # and the models in mm.make
if ($form->{make}) {
- push @where_tokens, 'mv.name ILIKE ?';
+ push @where_tokens, 'mm.make ILIKE ?';
push @bind_vars, like($form->{make});
}
if ($form->{model}) {
my $bsooqr = any { $form->{$_} } @oe_flags;
my @bsooqr_tokens = ();
- push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi' if $bsooqr;
+ push @select_tokens, @qsooqr_flags, 'record_type', '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->{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};
- push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'vendor'| if $form->{onorder};
- push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'customer'| if $form->{quoted};
- push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'vendor'| if $form->{rfq};
+ push @bsooqr_tokens, q|module = 'oe' AND record_type = 'sales_order'| if $form->{ordered};
+ push @bsooqr_tokens, q|module = 'oe' AND record_type = 'purchase_order'| if $form->{onorder};
+ push @bsooqr_tokens, q|module = 'oe' AND record_type = 'sales_quotation'| if $form->{quoted};
+ push @bsooqr_tokens, q|module = 'oe' AND record_type = 'request_quotation'| if $form->{rfq};
push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr;
$joins_needed{partsgroup} = 1;
$joins_needed{pfac} = 1;
$joins_needed{project} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
$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{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;
}
$form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
+ $form->{"${_}_accno_id_$index"} = $accounts{"${_}_accno_id"} for qw(inventory expense); # only for purchase_invoice
$sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)) || $::form->dberror($query_tax);
- $ref = $sth_tax->fetchrow_hashref or next;
+ my $tax_ref;
+ $tax_ref = $sth_tax->fetchrow_hashref or next;
+
+ $form->{"expense_accno_tax_id_$index"} = $tax_ref->{tax_id}; # only for purchase_invoice
- $form->{"taxaccounts_$index"} = $ref->{"accno"};
- $form->{"taxaccounts"} .= "$ref->{accno} "if $form->{"taxaccounts"} !~ /$ref->{accno}/;
+ $form->{"taxaccounts_$index"} = $tax_ref->{"accno"};
+ $form->{"taxaccounts"} .= "$tax_ref->{accno} "if $form->{"taxaccounts"} !~ /$tax_ref->{accno}/;
- $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber tax_id);
+ $form->{"$tax_ref->{accno}_${_}"} = $tax_ref->{$_} for qw(rate description taxnumber tax_id);
}
$sth_tax->finish;
}
my $placeholders = join ', ', ('?') x scalar(@part_ids);
- my $query = qq|SELECT mm.parts_id, mm.model, mm.lastcost, v.name AS make
+ my $query = qq|SELECT mm.parts_id, mm.model,
+ mm.part_description AS mm_part_description,
+ mm.part_longdescription AS mm_part_longdescription,
+ mm.lastcost, v.name AS make
FROM makemodel mm
LEFT JOIN vendor v ON (mm.make = v.id)
WHERE mm.parts_id IN ($placeholders)|;
$sth->finish();
+
+ $query = qq|SELECT bm.parts_id,
+ bm.model AS business_model,
+ bm.part_description AS bm_part_description,
+ bm.part_longdescription AS bm_part_longdescription,
+ b.description AS business_make
+ FROM business_models bm
+ LEFT JOIN business b ON (bm.business_id = b.id)
+ WHERE bm.parts_id IN ($placeholders)|;
+
+ my %businessmodel = ();
+
+ $sth = prepare_execute_query($form, $dbh, $query, @part_ids);
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $businessmodel{$ref->{parts_id}} ||= [];
+ push @{ $businessmodel{$ref->{parts_id}} }, $ref;
+ }
+
+ $sth->finish();
+
$query = qq|SELECT
cp.parts_id,
- cp.customer_partnumber AS customer_model,
- c.name AS customer_make
+ cp.customer_partnumber AS customer_model,
+ cp.part_description AS cm_part_description,
+ cp.part_longdescription AS cm_part_longdescription,
+ c.name AS customer_make
FROM part_customer_prices cp
LEFT JOIN customer c ON (cp.customer_id = c.id)
WHERE cp.parts_id IN ($placeholders)|;
$sth->finish();
- my @columns = qw(ean image microfiche drawing);
+ my @columns = qw(ean image microfiche drawing tariff_code);
$query = qq|SELECT id, | . join(', ', @columns) . qq|
FROM parts
my %data = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
my %template_arrays;
- map { $template_arrays{$_} = [] } (qw(make model customer_make customer_model), @columns);
+ map { $template_arrays{$_} = [] } (qw(make model mm_part_description mm_part_longdescription business_make business_model bm_part_description bm_part_longdescription customer_make customer_model cm_part_description cm_part_longdescription), @columns);
foreach my $i (1 .. $rowcount) {
my $id = $form->{"${prefix}${i}"};
push @{ $template_arrays{make} }, [];
push @{ $template_arrays{model} }, [];
+ push @{ $template_arrays{mm_part_description} }, [];
+ push @{ $template_arrays{mm_part_longdescription} }, [];
if ($makemodel{$id}) {
foreach my $ref (@{ $makemodel{$id} }) {
- map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model);
+ map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model mm_part_description mm_part_longdescription);
+ }
+ }
+
+ push @{ $template_arrays{business_make} }, [];
+ push @{ $template_arrays{business_model} }, [];
+ push @{ $template_arrays{bm_part_description} }, [];
+ push @{ $template_arrays{bm_part_longdescription} }, [];
+
+ if ($businessmodel{$id}) {
+ foreach my $ref (@{ $businessmodel{$id} }) {
+ map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(business_make business_model bm_part_description bm_part_longdescription);
}
}
push @{ $template_arrays{customer_make} }, [];
push @{ $template_arrays{customer_model} }, [];
+ push @{ $template_arrays{cm_part_description} }, [];
+ push @{ $template_arrays{cm_part_longdescription} }, [];
if ($customermodel{$id}) {
foreach my $ref (@{ $customermodel{$id} }) {
- push @{ $template_arrays{$_}->[-1] }, $ref->{$_} for qw(customer_make customer_model);
+ push @{ $template_arrays{$_}->[-1] }, $ref->{$_} for qw(customer_make customer_model cm_part_description cm_part_longdescription);
}
}
projects / mfinanz.git / blobdiff