package WH;
use Carp qw(croak);
+use List::MoreUtils qw(any);
use SL::AM;
use SL::DBUtils;
# if of a property number or description is requested,
# automatically check the matching id too.
- map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}description"} || $form->{"l_${_}number"}); } qw(warehouse bin);
+ map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}"} || $form->{"l_${_}number"}); } qw(warehouse bin);
# customize shown entry for not available fields.
$filter{na} = '-' unless $filter{na};
}
$select_tokens{'trans'} = {
- "parts_id" => "i1.parts_id",
- "qty" => "ABS(SUM(i1.qty))",
- "partnumber" => "p.partnumber",
- "partdescription" => "p.description",
- "classification_id" => "p.classification_id",
- "part_type" => "p.part_type",
- "bindescription" => "b.description",
- "chargenumber" => "i1.chargenumber",
- "bestbefore" => "i1.bestbefore",
- "warehousedescription" => "w.description",
- "partunit" => "p.unit",
- "bin_from" => "b1.description",
- "bin_to" => "b2.description",
- "warehouse_from" => "w1.description",
- "warehouse_to" => "w2.description",
- "comment" => "i1.comment",
- "trans_type" => "tt.description",
- "trans_id" => "i1.trans_id",
- "id" => "i1.id",
- "oe_id" => "COALESCE(i1.oe_id, i2.oe_id)",
- "invoice_id" => "COALESCE(i1.invoice_id, i2.invoice_id)",
- "date" => "i1.shippingdate",
- "itime" => "i1.itime",
- "shippingdate" => "i1.shippingdate",
- "employee" => "e.name",
- "projectnumber" => "COALESCE(pr.projectnumber, '$filter{na}')",
+ "parts_id" => "i1.parts_id",
+ "qty" => "ABS(SUM(i1.qty))",
+ "partnumber" => "p.partnumber",
+ "partdescription" => "p.description",
+ "classification_id" => "p.classification_id",
+ "part_type" => "p.part_type",
+ "bin" => "b.description",
+ "chargenumber" => "i1.chargenumber",
+ "bestbefore" => "i1.bestbefore",
+ "warehouse" => "w.description",
+ "partunit" => "p.unit",
+ "bin_from" => "b1.description",
+ "bin_to" => "b2.description",
+ "warehouse_from" => "w1.description",
+ "warehouse_to" => "w2.description",
+ "comment" => "i1.comment",
+ "trans_type" => "tt.description",
+ "trans_id" => "i1.trans_id",
+ "id" => "i1.id",
+ "oe_id" => "COALESCE(i1.oe_id, i2.oe_id)",
+ "invoice_id" => "COALESCE(i1.invoice_id, i2.invoice_id)",
+ "date" => "i1.shippingdate",
+ "itime" => "i1.itime",
+ "shippingdate" => "i1.shippingdate",
+ "employee" => "e.name",
+ "projectnumber" => "COALESCE(pr.projectnumber, '$filter{na}')",
};
$select_tokens{'out'} = {
if ($form->{l_oe_id}) {
$q_oe_id = <<SQL;
SELECT dord.id AS id, dord.donumber AS number,
- CASE
- WHEN dord.customer_id IS NULL THEN 'purchase_delivery_order'
- ELSE 'sales_delivery_order'
- END AS type
+ dord.record_type::text AS type
FROM delivery_orders dord
WHERE dord.id = ?
# filters
my (@filter_ary, @filter_vars, @wh_bin_filter_ary, @wh_bin_filter_vars);
- delete $form->{include_empty_bins} unless ($form->{l_warehousedescription} || $form->{l_bindescription});
+ delete $form->{include_empty_bins} unless ($form->{l_warehouse} || $form->{l_bin});
if ($filter{warehouse_id}) {
push @wh_bin_filter_ary, "w.id = ?";
# if of a property number or description is requested,
# automatically check the matching id too.
- map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}description"} || $form->{"l_${_}number"}); } qw(warehouse bin);
+ map { $form->{"l_${_}id"} = "Y" if ($form->{"l_${_}"} || $form->{"l_${_}number"}); } qw(warehouse bin);
# make order, search in $filter and $form
my $sort_col = $form->{sort};
"partdescription" => "p.description",
"classification_id" => "p.classification_id",
"part_type" => "p.part_type",
- "bindescription" => "b.description",
+ "bin" => "b.description",
"binid" => "b.id",
"chargenumber" => "i.chargenumber",
"bestbefore" => "i.bestbefore",
"ean" => "p.ean",
"chargeid" => "c.id",
- "warehousedescription" => "w.description",
+ "warehouse" => "w.description",
"partunit" => "p.unit",
"stock_value" => ($form->{stock_value_basis} // '') eq 'list_price' ? "p.listprice / COALESCE(pfac.factor, 1)" : "p.lastcost / COALESCE(pfac.factor, 1)",
"purchase_price" => "p.lastcost",
"list_price" => "p.listprice",
+ "price_factor" => ($form->{l_purchase_price} || $form->{l_list_price}) ? "pfac.description" : undef,
);
$form->{l_classification_id} = 'Y';
$form->{l_part_type} = 'Y';
+ $form->{l_price_factor} = 'Y' if $form->{l_purchase_price} || $form->{l_list_price};
my $select_clause = join ', ', map { +/^l_/; "$select_tokens{$'} AS $'" }
( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
qw(l_parts_id l_partunit) );
+ my @join_values = ();
my %join_tokens = (
- "stock_value" => "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)",
- );
+ "stock_value" => "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)",
+ );
+ $join_tokens{price_factor} = "LEFT JOIN price_factors pfac ON (p.price_factor_id = pfac.id)" if !$form->{l_stock_value};
my $joins = join ' ', grep { $_ } map { +/^l_/; $join_tokens{"$'"} }
( grep( { !/qty/ and !/^l_cvar/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
qw(l_parts_id l_qty l_partunit) );
+ # add cvar for sorting
+ if (($form->{sort} // '') =~ /^cvar_/) {
+ my $sort_name = $form->{sort};
+ my $cvar_name = $sort_name;
+ $cvar_name =~ s/^cvar_//;
+ my $cvar_configs = CVar->get_configs('module' => 'IC');
+ my @allowed_cvar_names =
+ map {$_->{name}}
+ grep {$_->{type} =~ m/text|textfield|htmlfield/}
+ @$cvar_configs;
+ unless (any {$sort_name eq 'cvar_' . $_} @allowed_cvar_names) {
+ die "unsupported sort on cvar field";
+ }
+
+ $select_clause .= ", cvar_fields.$sort_name";
+ $group_clause .= ", cvar_fields.$sort_name";
+ $joins .= qq|
+ LEFT JOIN (
+ SELECT text_value as $sort_name, trans_id
+ FROM custom_variable_configs cvar_cfg
+ LEFT JOIN custom_variables cvar
+ ON (cvar_cfg.module = 'IC' AND cvar_cfg.name = ?
+ AND cvar_cfg.id = cvar.config_id)
+ ) cvar_fields ON (cvar_fields.trans_id = p.id)
+ |;
+ push @join_values, $cvar_name
+ }
+ @filter_vars = (@join_values, @filter_vars);
+
my ($cvar_where, @cvar_values) = CVar->build_filter_query(
module => 'IC',
trans_id_field => 'p.id',
if ($form->{include_empty_bins}) {
$query =
qq|SELECT
- w.id AS warehouseid, w.description AS warehousedescription,
- b.id AS binid, b.description AS bindescription
+ w.id AS warehouseid, w.description AS warehouse,
+ b.id AS binid, b.description AS bin
FROM bin b
LEFT JOIN warehouse w ON (b.warehouse_id = w.id)|;
}
$sth->finish();
- if (grep { $orderby eq $_ } qw(bindescription warehousedescription)) {
+ if (grep { $orderby eq $_ } qw(bin warehouse)) {
@contents = sort { ($a->{$orderby} cmp $b->{$orderby}) * (($form->{order}) ? 1 : -1) } @contents;
}
}
projects / mfinanz.git / blobdiff