From 3c938e03dbb186f9c163e19473e9572f7fa16aea Mon Sep 17 00:00:00 2001 From: "Martin Helmling mh@waldpark.octosoft.eu" Date: Mon, 6 Jul 2015 17:46:22 +0200 Subject: [PATCH] =?utf8?q?Paginierung=20schneller=20machen=20f=C3=BCr=20La?= =?utf8?q?ger(Paginierung=20repariert)?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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) --- SL/WH.pm | 49 +++++++-- bin/mozilla/wh.pl | 121 ++++++++++++++++------- templates/webpages/wh/report_filter.html | 2 + 3 files changed, 129 insertions(+), 43 deletions(-) diff --git a/SL/WH.pm b/SL/WH.pm index df5bad909..78a1442e5 100644 --- a/SL/WH.pm +++ b/SL/WH.pm @@ -420,10 +420,29 @@ sub get_warehouse_journal { 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 " : ''; @@ -466,6 +485,8 @@ sub get_warehouse_journal { }; $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. @@ -476,12 +497,12 @@ sub get_warehouse_journal { } 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 @@ -529,7 +550,10 @@ sub get_warehouse_journal { 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); @@ -700,6 +724,11 @@ sub get_warehouse_report { 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}); @@ -763,6 +792,7 @@ sub get_warehouse_report { "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'; @@ -784,7 +814,7 @@ sub get_warehouse_report { 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 @@ -792,7 +822,10 @@ sub get_warehouse_report { $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); diff --git a/bin/mozilla/wh.pl b/bin/mozilla/wh.pl index 2ef05a608..5df5ac052 100644 --- a/bin/mozilla/wh.pl +++ b/bin/mozilla/wh.pl @@ -581,7 +581,7 @@ sub generate_journal { 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}) { @@ -593,10 +593,34 @@ sub generate_journal { } # /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 = ( @@ -622,13 +646,22 @@ sub generate_journal { '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} =''; @@ -646,7 +679,6 @@ sub generate_journal { $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') }, @@ -658,22 +690,15 @@ sub generate_journal { '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 = { }; @@ -698,18 +723,18 @@ sub generate_journal { } 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(); @@ -756,11 +781,10 @@ sub generate_report { $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); @@ -801,9 +825,34 @@ sub generate_report { } # /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; @@ -822,16 +871,19 @@ sub generate_report { '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} =''; @@ -850,8 +902,6 @@ sub generate_report { $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); @@ -859,15 +909,6 @@ sub generate_report { 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}). @@ -879,6 +920,7 @@ sub generate_report { # '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 } ]; @@ -892,6 +934,7 @@ sub generate_report { # '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; @@ -899,7 +942,7 @@ sub generate_report { } if ( $allrows || ($idx >= $first_nr && $idx < $last_nr )) { - $report->add_data($row_set); + $report->add_data($row_set); } $idx++; } @@ -917,6 +960,14 @@ sub generate_report { $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(); diff --git a/templates/webpages/wh/report_filter.html b/templates/webpages/wh/report_filter.html index d68ebeae3..6c512e357 100644 --- a/templates/webpages/wh/report_filter.html +++ b/templates/webpages/wh/report_filter.html @@ -185,6 +185,8 @@ + + -- 2.20.1