Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 3c938e03

Von Martin Helmling mh@waldpark.octosoft.eu vor etwa 8 Jahren hinzugefügt

  • ID 3c938e03dbb186f9c163e19473e9572f7fa16aea
  • Vorgänger 655f8af6
  • Nachfolger cce2f7c3

Paginierung schneller machen für Lager(Paginierung repariert)

Lagerbuchungen und Lagerbestand optimieren

1. Auf Rose umstellen grosser Aufwand.
2. Roseverhalten nachimplementieren:
2.1. Nur einen Query haben, danach kein herausnehmen von Zeilen
2.2. Dann funktioniert Paginierung mit LIMIT und OFFSET (wie Rose)
2.3. Rose macht ein zu Beginn ein COUNT um die Gesamtmenge zu ermitteln,wir merken uns das nach dem ersten Query

Der Mengenfilter kann schlecht in SQL integriert werden wegen Einheitenumrechnung (eventuell später noch eine PSQL Funktion einbauen)

Unterschiede anzeigen:

SL/WH.pm
my $sort_order = $form->{order};
$sort_col = $filter{sort} unless $sort_col;
$sort_col = 'shippingdate' if $sort_col eq 'date';
$sort_order = ($sort_col = 'shippingdate') unless $sort_col;
$sort_col = 'shippingdate' if $sort_col eq 'date';
$sort_order = $filter{order} unless $sort_order;
my $sort_spec = "${sort_col} " . ($sort_order ? " DESC" : " ASC");
my %orderspecs = (
'shippingdate' => ['shippingdate', 'r_itime', 'r_parts_id'],
'bin_to' => ['bin_to', 'r_itime', 'r_parts_id'],
'bin_from' => ['bin_from', 'r_itime', 'r_parts_id'],
'warehouse_to' => ['warehouse_to, r_itime, r_parts_id'],
'warehouse_from' => ['warehouse_from, r_itime, r_parts_id'],
'partnumber' => ['partnumber'],
'partdescription'=> ['partdescription'],
'partunit' => ['partunit, r_itime, r_parts_id'],
'qty' => ['qty, r_itime, r_parts_id'],
'oe_id' => ['oe_id'],
'comment' => ['comment'],
'trans_type' => ['trans_type'],
'employee' => ['employee'],
'projectnumber' => ['projectnumber'],
);
$sort_order = $filter{order} unless $sort_order;
my $ASC = ($sort_order ? " DESC" : " ASC");
my $sort_spec = join("$ASC , ", @{$orderspecs{$sort_col}}). " $ASC";
my $where_clause = @filter_ary ? join(" AND ", @filter_ary) . " AND " : '';
......
};
$form->{l_classification_id} = 'Y';
$form->{l_part_type} = 'Y';
$form->{l_itime} = 'Y';
$form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id};
# build the select clauses.
......
}
my $group_clause = join ", ", map { +/^l_/; "r_$'" }
( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate) );
( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate l_itime) );
$where_clause = defined($where_clause) ? $where_clause : '';
my $query =
qq|SELECT DISTINCT $select{trans}
qq|SELECT * FROM (SELECT DISTINCT $select{trans}
FROM inventory i1
LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id
LEFT JOIN parts p ON i1.parts_id = p.id
......
WHERE $where_clause i1.qty > 0 AND
i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 )
GROUP BY $group_clause
ORDER BY r_${sort_spec}|;
ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
$query .= " LIMIT $filter{limit}" if $filter{limit} ;
$query .= " OFFSET $filter{offset}" if $filter{offset} ;
my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars);
......
push @filter_vars, trim($form->{bestbefore});
}
if ($filter{classification_id}) {
push @filter_ary, "p.classification_id = ?";
push @filter_vars, $filter{classification_id};
}
if ($filter{ean}) {
push @filter_ary, "p.ean ILIKE ?";
push @filter_vars, like($filter{ean});
......
"warehousedescription" => "w.description",
"partunit" => "p.unit",
"stock_value" => "p.lastcost / COALESCE(pfac.factor, 1)",
"purchase_price" => "p.lastcost",
);
$form->{l_classification_id} = 'Y';
$form->{l_part_type} = 'Y';
......
qw(l_parts_id l_qty l_partunit) );
my $query =
qq|SELECT $select_clause
qq|SELECT * FROM ( SELECT $select_clause
FROM inventory i
LEFT JOIN parts p ON i.parts_id = p.id
LEFT JOIN bin b ON i.bin_id = b.id
......
$joins
WHERE $where_clause
GROUP BY $group_clause
ORDER BY $sort_spec|;
ORDER BY $sort_spec ) AS lines WHERE qty<>0|;
$query .= " LIMIT $filter{limit}" if $filter{limit} ;
$query .= " OFFSET $filter{offset}" if $filter{offset} ;
my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars);
bin/mozilla/wh.pl
my @columns = qw(trans_id date warehouse_from bin_from warehouse_to bin_to partnumber type_and_classific partdescription chargenumber bestbefore trans_type comment qty unit partunit employee oe_id projectnumber);
# filter stuff
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore);
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore transtype_id transtype_ids comment projectnumber);
$filter{qty_op} = WH->convert_qty_op($form->{qty_op});
if ($filter{qty_op}) {
......
}
# /filter stuff
my $allrows = 0;
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
# manual paginating
my $pages = {};
my $page = $::form->{page} || 1;
$pages->{per_page} = $::form->{per_page} || 15;
my $first_nr = ($page - 1) * $pages->{per_page};
my $last_nr = $first_nr + $pages->{per_page};
# no optimisation if qty op
if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
$filter{limit} = $pages->{per_page};
$filter{offset} = ($page - 1) * $pages->{per_page};
$first_nr = 0;
$last_nr = $pages->{per_page};
}
my @contents = WH->get_warehouse_journal(%filter);
# get maxcount
if (!$form->{maxrows}) {
$form->{maxrows} = scalar @contents ;
}
my $report = SL::ReportGenerator->new(\%myconfig, $form);
my @hidden_variables = map { "l_${_}" } @columns;
push @hidden_variables, qw(warehouse_id bin_id partnumber description chargenumber bestbefore qty_op qty qty_unit fromdate todate);
push @hidden_variables, qw(warehouse_id bin_id partnumber description chargenumber bestbefore qty_op qty qty_unit unit partunit fromdate todate transtype_ids comment projectnumber);
push @hidden_variables, qw(classification_id);
my %column_defs = (
......
'oe_id' => { 'text' => $locale->text('Document'), },
);
if ($form->{transtype_ids} && 'ARRAY' eq ref $form->{transtype_ids}) {
for (my $i = 0; $i < scalar(@{ $form->{transtype_ids} }); $i++) {
delete $form->{transtype_ids}[$i] if $form->{transtype_ids}[$i] eq '';
}
$form->{transtype_ids} = join(",", @{ $form->{transtype_ids} });
}
my $href = build_std_url('action=generate_journal', grep { $form->{$_} } @hidden_variables);
my $page = $::form->{page} || 1;
$href .= "&maxrows=".$form->{maxrows};
map { $column_defs{$_}->{link} = $href ."&page=".$page. "&sort=${_}&order=" . Q($_ eq $form->{sort} ? 1 - $form->{order} : $form->{order}) } @columns;
my %column_alignment = map { $_ => 'right' } qw(qty);
map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
$column_defs{partunit}->{visible} = 1;
$column_defs{type_and_classific}->{visible} = 1;
$column_defs{type_and_classific}->{link} ='';
......
$locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
my $all_units = AM->retrieve_units(\%myconfig, $form);
my @contents = WH->get_warehouse_journal(%filter);
my %doc_types = ( 'sales_quotation' => { script => 'oe', title => $locale->text('Sales quotation') },
'sales_order' => { script => 'oe', title => $locale->text('Sales Order') },
......
'purchase_invoice' => { script => 'ir', title => $locale->text('Purchase Invoice') },
);
my $allrows = 0;
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
# manual paginating
my $pages = {};
$pages->{per_page} = $::form->{per_page} || 15;
my $first_nr = ($page - 1) * $pages->{per_page};
my $last_nr = $first_nr + $pages->{per_page};
my $idx = 0;
foreach my $entry (@contents) {
$entry->{type_and_classific} = $::request->presenter->type_abbreviation($entry->{part_type}).
$::request->presenter->classification_abbreviation($entry->{classification_id});
$entry->{qty} = $form->format_amount_units('amount' => $entry->{qty},
'part_unit' => $entry->{partunit},
'conv_units' => 'convertible');
$entry->{qty} = $form->format_amount(\%myconfig, $entry->{qty});
# $entry->{qty} = $form->format_amount_units('amount' => $entry->{qty},
# 'part_unit' => $entry->{partunit},
# 'conv_units' => 'convertible');
$entry->{trans_type} = $locale->text($entry->{trans_type});
my $row = { };
......
}
if ( $allrows || ($idx >= $first_nr && $idx < $last_nr )) {
$report->add_data($row);
$report->add_data($row);
}
$idx++;
}
if ( ! $allrows ) {
$pages->{max} = SL::DB::Helper::Paginated::ceil($idx, $pages->{per_page}) || 1;
$pages->{max} = SL::DB::Helper::Paginated::ceil($form->{maxrows}, $pages->{per_page}) || 1;
$pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
$pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
$report->set_options('raw_bottom_info_text' => $form->parse_html_template('common/paginate',
{ 'pages' => $pages , 'base_url' => $href}) );
{ 'pages' => $pages , 'base_url' => $href.'&sort='.$form->{sort}.'&order='.$form->{order}}) );
}
$report->generate_with_headers();
......
$form->{title} = $locale->text("Report about warehouse contents");
$form->{sort} ||= 'partnumber';
$form->{sort} ||= 'partunit';
my $sort_col = $form->{sort};
my %filter;
my @columns = qw(warehousedescription bindescription partnumber type_and_classific partdescription chargenumber bestbefore comment qty partunit stock_value);
my @columns = qw(warehousedescription bindescription partnumber type_and_classific partdescription chargenumber bestbefore comment qty partunit purchase_price stock_value);
# filter stuff
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore date include_invalid_warehouses);
......
}
# /filter stuff
$form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
# manual paginating
my $allrows = 0;
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
my $page = $::form->{page} || 1;
my $pages = {};
$pages->{per_page} = $::form->{per_page} || 20;
my $first_nr = ($page - 1) * $pages->{per_page};
my $last_nr = $first_nr + $pages->{per_page};
# no optimisation if qty op
if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
$filter{limit} = $pages->{per_page};
$filter{offset} = ($page - 1) * $pages->{per_page};
$first_nr = 0;
$last_nr = $pages->{per_page};
}
my @contents = WH->get_warehouse_report(%filter);
# get maxcount
if (!$form->{maxrows}) {
$form->{maxrows} = scalar @contents ;
}
$form->{subtotal} = '' if (!first { $_ eq $sort_col } qw(partnumber partdescription));
$form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
my $report = SL::ReportGenerator->new(\%myconfig, $form);
my @hidden_variables = map { "l_${_}" } @columns;
......
'qty' => { 'text' => $locale->text('Qty'), },
'partunit' => { 'text' => $locale->text('Unit'), },
'stock_value' => { 'text' => $locale->text('Stock value'), },
'purchase_price' => { 'text' => $locale->text('Purchase Price'), },
);
my $href = build_std_url('action=generate_report', grep { $form->{$_} } @hidden_variables);
my $page = $::form->{page} || 1;
$href .= "&maxrows=".$form->{maxrows};
map { $column_defs{$_}->{link} = $href . "&page=".$page."&sort=${_}&order=" . Q($_ eq $sort_col ? 1 - $form->{order} : $form->{order}) } @columns;
my %column_alignment = map { $_ => 'right' } qw(qty stock_value);
my %column_alignment = map { $_ => 'right' } qw(qty purchase_price stock_value);
map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
$column_defs{partunit}->{visible} = 1;
$column_defs{type_and_classific}->{visible} = 1;
$column_defs{type_and_classific}->{link} ='';
......
$locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
my $all_units = AM->retrieve_units(\%myconfig, $form);
my @contents = WH->get_warehouse_report(%filter);
my $idx = 0;
my @subtotals_columns = qw(qty stock_value);
......
my $total_stock_value = 0;
my $allrows = 0;
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
# manual paginating
my $pages = {};
$pages->{per_page} = $::form->{per_page} || 20;
my $first_nr = ($page - 1) * $pages->{per_page};
my $last_nr = $first_nr + $pages->{per_page};
foreach my $entry (@contents) {
$entry->{type_and_classific} = $::request->presenter->type_abbreviation($entry->{part_type}).
......
# 'part_unit' => $entry->{partunit},
# 'conv_units' => 'convertible');
$entry->{stock_value} = $form->format_amount(\%myconfig, $entry->{stock_value} * 1, 2);
$entry->{purchase_price} = $form->format_amount(\%myconfig, $entry->{purchase_price} * 1, 2);
my $row_set = [ { map { $_ => { 'data' => $entry->{$_}, 'align' => $column_alignment{$_} } } @columns } ];
......
# 'part_unit' => $entry->{partunit},
# 'conv_units' => 'convertible');
$row->{stock_value}->{data} = $form->format_amount(\%myconfig, $subtotals{stock_value} * 1, 2);
$row->{purchase_price}->{data} = $form->format_amount(\%myconfig, $subtotals{purchase_price} * 1, 2);
%subtotals = map { $_ => 0 } @subtotals_columns;
......
}
if ( $allrows || ($idx >= $first_nr && $idx < $last_nr )) {
$report->add_data($row_set);
$report->add_data($row_set);
}
$idx++;
}
......
$report->add_data($row);
}
if ( ! $allrows ) {
$pages->{max} = SL::DB::Helper::Paginated::ceil($form->{maxrows}, $pages->{per_page}) || 1;
$pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
$pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
$report->set_options('raw_bottom_info_text' => $form->parse_html_template('common/paginate',
{'pages' => $pages , 'base_url' => $href}) );
}
$report->generate_with_headers();
templates/webpages/wh/report_filter.html
<tr>
<td align="right"><input name="l_stock_value" id="l_stock_value" class="checkbox" type="checkbox" value="Y"></td>
<td nowrap><label for="l_stock_value">[% 'Stock value' | $T8 %]</label></td>
<td align="right"><input name="l_purchase_price" id="l_purchase_price" class="checkbox" type="checkbox" value="Y"></td>
<td nowrap><label for="l_purchase_price">[% 'Purchase Price' | $T8 %]</label></td>
</tr>
</table>

Auch abrufbar als: Unified diff