Revision 3c938e03
Von Martin Helmling mh@waldpark.octosoft.eu vor fast 8 Jahren hinzugefügt
| bin/mozilla/wh.pl | ||
|---|---|---|
| 581 | 581 |
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); |
| 582 | 582 |
|
| 583 | 583 |
# filter stuff |
| 584 |
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore);
|
|
| 584 |
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore transtype_id transtype_ids comment projectnumber);
|
|
| 585 | 585 |
|
| 586 | 586 |
$filter{qty_op} = WH->convert_qty_op($form->{qty_op});
|
| 587 | 587 |
if ($filter{qty_op}) {
|
| ... | ... | |
| 593 | 593 |
} |
| 594 | 594 |
# /filter stuff |
| 595 | 595 |
|
| 596 |
my $allrows = 0; |
|
| 597 |
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
|
|
| 598 |
|
|
| 599 |
# manual paginating |
|
| 600 |
my $pages = {};
|
|
| 601 |
my $page = $::form->{page} || 1;
|
|
| 602 |
$pages->{per_page} = $::form->{per_page} || 15;
|
|
| 603 |
my $first_nr = ($page - 1) * $pages->{per_page};
|
|
| 604 |
my $last_nr = $first_nr + $pages->{per_page};
|
|
| 605 |
|
|
| 606 |
# no optimisation if qty op |
|
| 607 |
if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
|
|
| 608 |
$filter{limit} = $pages->{per_page};
|
|
| 609 |
$filter{offset} = ($page - 1) * $pages->{per_page};
|
|
| 610 |
$first_nr = 0; |
|
| 611 |
$last_nr = $pages->{per_page};
|
|
| 612 |
} |
|
| 613 |
|
|
| 614 |
my @contents = WH->get_warehouse_journal(%filter); |
|
| 615 |
# get maxcount |
|
| 616 |
if (!$form->{maxrows}) {
|
|
| 617 |
$form->{maxrows} = scalar @contents ;
|
|
| 618 |
} |
|
| 619 |
|
|
| 596 | 620 |
my $report = SL::ReportGenerator->new(\%myconfig, $form); |
| 597 | 621 |
|
| 598 | 622 |
my @hidden_variables = map { "l_${_}" } @columns;
|
| 599 |
push @hidden_variables, qw(warehouse_id bin_id partnumber description chargenumber bestbefore qty_op qty qty_unit fromdate todate);
|
|
| 623 |
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);
|
|
| 600 | 624 |
push @hidden_variables, qw(classification_id); |
| 601 | 625 |
|
| 602 | 626 |
my %column_defs = ( |
| ... | ... | |
| 622 | 646 |
'oe_id' => { 'text' => $locale->text('Document'), },
|
| 623 | 647 |
); |
| 624 | 648 |
|
| 649 |
if ($form->{transtype_ids} && 'ARRAY' eq ref $form->{transtype_ids}) {
|
|
| 650 |
for (my $i = 0; $i < scalar(@{ $form->{transtype_ids} }); $i++) {
|
|
| 651 |
delete $form->{transtype_ids}[$i] if $form->{transtype_ids}[$i] eq '';
|
|
| 652 |
} |
|
| 653 |
$form->{transtype_ids} = join(",", @{ $form->{transtype_ids} });
|
|
| 654 |
} |
|
| 655 |
|
|
| 625 | 656 |
my $href = build_std_url('action=generate_journal', grep { $form->{$_} } @hidden_variables);
|
| 626 |
my $page = $::form->{page} || 1;
|
|
| 657 |
$href .= "&maxrows=".$form->{maxrows};
|
|
| 658 |
|
|
| 627 | 659 |
map { $column_defs{$_}->{link} = $href ."&page=".$page. "&sort=${_}&order=" . Q($_ eq $form->{sort} ? 1 - $form->{order} : $form->{order}) } @columns;
|
| 628 | 660 |
|
| 629 | 661 |
my %column_alignment = map { $_ => 'right' } qw(qty);
|
| 630 | 662 |
|
| 631 | 663 |
map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
|
| 664 |
$column_defs{partunit}->{visible} = 1;
|
|
| 632 | 665 |
$column_defs{type_and_classific}->{visible} = 1;
|
| 633 | 666 |
$column_defs{type_and_classific}->{link} ='';
|
| 634 | 667 |
|
| ... | ... | |
| 646 | 679 |
$locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
|
| 647 | 680 |
|
| 648 | 681 |
my $all_units = AM->retrieve_units(\%myconfig, $form); |
| 649 |
my @contents = WH->get_warehouse_journal(%filter); |
|
| 650 | 682 |
|
| 651 | 683 |
my %doc_types = ( 'sales_quotation' => { script => 'oe', title => $locale->text('Sales quotation') },
|
| 652 | 684 |
'sales_order' => { script => 'oe', title => $locale->text('Sales Order') },
|
| ... | ... | |
| 658 | 690 |
'purchase_invoice' => { script => 'ir', title => $locale->text('Purchase Invoice') },
|
| 659 | 691 |
); |
| 660 | 692 |
|
| 661 |
my $allrows = 0; |
|
| 662 |
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
|
|
| 663 |
|
|
| 664 |
# manual paginating |
|
| 665 |
my $pages = {};
|
|
| 666 |
$pages->{per_page} = $::form->{per_page} || 15;
|
|
| 667 |
my $first_nr = ($page - 1) * $pages->{per_page};
|
|
| 668 |
my $last_nr = $first_nr + $pages->{per_page};
|
|
| 669 | 693 |
my $idx = 0; |
| 670 | 694 |
|
| 671 | 695 |
foreach my $entry (@contents) {
|
| 672 | 696 |
$entry->{type_and_classific} = $::request->presenter->type_abbreviation($entry->{part_type}).
|
| 673 | 697 |
$::request->presenter->classification_abbreviation($entry->{classification_id});
|
| 674 |
$entry->{qty} = $form->format_amount_units('amount' => $entry->{qty},
|
|
| 675 |
'part_unit' => $entry->{partunit},
|
|
| 676 |
'conv_units' => 'convertible'); |
|
| 698 |
$entry->{qty} = $form->format_amount(\%myconfig, $entry->{qty});
|
|
| 699 |
# $entry->{qty} = $form->format_amount_units('amount' => $entry->{qty},
|
|
| 700 |
# 'part_unit' => $entry->{partunit},
|
|
| 701 |
# 'conv_units' => 'convertible'); |
|
| 677 | 702 |
$entry->{trans_type} = $locale->text($entry->{trans_type});
|
| 678 | 703 |
|
| 679 | 704 |
my $row = { };
|
| ... | ... | |
| 698 | 723 |
} |
| 699 | 724 |
|
| 700 | 725 |
if ( $allrows || ($idx >= $first_nr && $idx < $last_nr )) {
|
| 701 |
$report->add_data($row);
|
|
| 726 |
$report->add_data($row); |
|
| 702 | 727 |
} |
| 703 | 728 |
$idx++; |
| 704 | 729 |
} |
| 705 | 730 |
|
| 706 | 731 |
if ( ! $allrows ) {
|
| 707 |
$pages->{max} = SL::DB::Helper::Paginated::ceil($idx, $pages->{per_page}) || 1;
|
|
| 732 |
$pages->{max} = SL::DB::Helper::Paginated::ceil($form->{maxrows}, $pages->{per_page}) || 1;
|
|
| 708 | 733 |
$pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
|
| 709 | 734 |
$pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
|
| 710 | 735 |
|
| 711 | 736 |
$report->set_options('raw_bottom_info_text' => $form->parse_html_template('common/paginate',
|
| 712 |
{ 'pages' => $pages , 'base_url' => $href}) );
|
|
| 737 |
{ 'pages' => $pages , 'base_url' => $href.'&sort='.$form->{sort}.'&order='.$form->{order}}) );
|
|
| 713 | 738 |
} |
| 714 | 739 |
$report->generate_with_headers(); |
| 715 | 740 |
|
| ... | ... | |
| 756 | 781 |
|
| 757 | 782 |
$form->{title} = $locale->text("Report about warehouse contents");
|
| 758 | 783 |
$form->{sort} ||= 'partnumber';
|
| 759 |
$form->{sort} ||= 'partunit';
|
|
| 760 | 784 |
my $sort_col = $form->{sort};
|
| 761 | 785 |
|
| 762 | 786 |
my %filter; |
| 763 |
my @columns = qw(warehousedescription bindescription partnumber type_and_classific partdescription chargenumber bestbefore comment qty partunit stock_value); |
|
| 787 |
my @columns = qw(warehousedescription bindescription partnumber type_and_classific partdescription chargenumber bestbefore comment qty partunit purchase_price stock_value);
|
|
| 764 | 788 |
|
| 765 | 789 |
# filter stuff |
| 766 | 790 |
map { $filter{$_} = $form->{$_} if ($form->{$_}) } qw(warehouse_id bin_id classification_id partnumber description chargenumber bestbefore date include_invalid_warehouses);
|
| ... | ... | |
| 801 | 825 |
} |
| 802 | 826 |
# /filter stuff |
| 803 | 827 |
|
| 828 |
$form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
|
|
| 829 |
|
|
| 830 |
# manual paginating |
|
| 831 |
my $allrows = 0; |
|
| 832 |
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
|
|
| 833 |
my $page = $::form->{page} || 1;
|
|
| 834 |
my $pages = {};
|
|
| 835 |
$pages->{per_page} = $::form->{per_page} || 20;
|
|
| 836 |
my $first_nr = ($page - 1) * $pages->{per_page};
|
|
| 837 |
my $last_nr = $first_nr + $pages->{per_page};
|
|
| 838 |
|
|
| 839 |
# no optimisation if qty op |
|
| 840 |
if ( !$allrows && $form->{maxrows} && !$filter{qty_op}) {
|
|
| 841 |
$filter{limit} = $pages->{per_page};
|
|
| 842 |
$filter{offset} = ($page - 1) * $pages->{per_page};
|
|
| 843 |
$first_nr = 0; |
|
| 844 |
$last_nr = $pages->{per_page};
|
|
| 845 |
} |
|
| 846 |
|
|
| 847 |
my @contents = WH->get_warehouse_report(%filter); |
|
| 848 |
|
|
| 849 |
# get maxcount |
|
| 850 |
if (!$form->{maxrows}) {
|
|
| 851 |
$form->{maxrows} = scalar @contents ;
|
|
| 852 |
} |
|
| 853 |
|
|
| 804 | 854 |
$form->{subtotal} = '' if (!first { $_ eq $sort_col } qw(partnumber partdescription));
|
| 805 | 855 |
|
| 806 |
$form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
|
|
| 807 | 856 |
my $report = SL::ReportGenerator->new(\%myconfig, $form); |
| 808 | 857 |
|
| 809 | 858 |
my @hidden_variables = map { "l_${_}" } @columns;
|
| ... | ... | |
| 822 | 871 |
'qty' => { 'text' => $locale->text('Qty'), },
|
| 823 | 872 |
'partunit' => { 'text' => $locale->text('Unit'), },
|
| 824 | 873 |
'stock_value' => { 'text' => $locale->text('Stock value'), },
|
| 874 |
'purchase_price' => { 'text' => $locale->text('Purchase Price'), },
|
|
| 825 | 875 |
); |
| 826 | 876 |
|
| 827 | 877 |
my $href = build_std_url('action=generate_report', grep { $form->{$_} } @hidden_variables);
|
| 828 |
my $page = $::form->{page} || 1;
|
|
| 878 |
$href .= "&maxrows=".$form->{maxrows};
|
|
| 879 |
|
|
| 829 | 880 |
map { $column_defs{$_}->{link} = $href . "&page=".$page."&sort=${_}&order=" . Q($_ eq $sort_col ? 1 - $form->{order} : $form->{order}) } @columns;
|
| 830 | 881 |
|
| 831 |
my %column_alignment = map { $_ => 'right' } qw(qty stock_value);
|
|
| 882 |
my %column_alignment = map { $_ => 'right' } qw(qty purchase_price stock_value);
|
|
| 832 | 883 |
|
| 833 | 884 |
map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
|
| 834 | 885 |
|
| 886 |
$column_defs{partunit}->{visible} = 1;
|
|
| 835 | 887 |
$column_defs{type_and_classific}->{visible} = 1;
|
| 836 | 888 |
$column_defs{type_and_classific}->{link} ='';
|
| 837 | 889 |
|
| ... | ... | |
| 850 | 902 |
$locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
|
| 851 | 903 |
|
| 852 | 904 |
my $all_units = AM->retrieve_units(\%myconfig, $form); |
| 853 |
my @contents = WH->get_warehouse_report(%filter); |
|
| 854 |
|
|
| 855 | 905 |
my $idx = 0; |
| 856 | 906 |
|
| 857 | 907 |
my @subtotals_columns = qw(qty stock_value); |
| ... | ... | |
| 859 | 909 |
|
| 860 | 910 |
my $total_stock_value = 0; |
| 861 | 911 |
|
| 862 |
my $allrows = 0; |
|
| 863 |
$allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
|
|
| 864 |
|
|
| 865 |
# manual paginating |
|
| 866 |
my $pages = {};
|
|
| 867 |
$pages->{per_page} = $::form->{per_page} || 20;
|
|
| 868 |
my $first_nr = ($page - 1) * $pages->{per_page};
|
|
| 869 |
my $last_nr = $first_nr + $pages->{per_page};
|
|
| 870 |
|
|
| 871 | 912 |
foreach my $entry (@contents) {
|
| 872 | 913 |
|
| 873 | 914 |
$entry->{type_and_classific} = $::request->presenter->type_abbreviation($entry->{part_type}).
|
| ... | ... | |
| 879 | 920 |
# 'part_unit' => $entry->{partunit},
|
| 880 | 921 |
# 'conv_units' => 'convertible'); |
| 881 | 922 |
$entry->{stock_value} = $form->format_amount(\%myconfig, $entry->{stock_value} * 1, 2);
|
| 923 |
$entry->{purchase_price} = $form->format_amount(\%myconfig, $entry->{purchase_price} * 1, 2);
|
|
| 882 | 924 |
|
| 883 | 925 |
my $row_set = [ { map { $_ => { 'data' => $entry->{$_}, 'align' => $column_alignment{$_} } } @columns } ];
|
| 884 | 926 |
|
| ... | ... | |
| 892 | 934 |
# 'part_unit' => $entry->{partunit},
|
| 893 | 935 |
# 'conv_units' => 'convertible'); |
| 894 | 936 |
$row->{stock_value}->{data} = $form->format_amount(\%myconfig, $subtotals{stock_value} * 1, 2);
|
| 937 |
$row->{purchase_price}->{data} = $form->format_amount(\%myconfig, $subtotals{purchase_price} * 1, 2);
|
|
| 895 | 938 |
|
| 896 | 939 |
%subtotals = map { $_ => 0 } @subtotals_columns;
|
| 897 | 940 |
|
| ... | ... | |
| 899 | 942 |
} |
| 900 | 943 |
|
| 901 | 944 |
if ( $allrows || ($idx >= $first_nr && $idx < $last_nr )) {
|
| 902 |
$report->add_data($row_set); |
|
| 945 |
$report->add_data($row_set);
|
|
| 903 | 946 |
} |
| 904 | 947 |
$idx++; |
| 905 | 948 |
} |
| ... | ... | |
| 917 | 960 |
|
| 918 | 961 |
$report->add_data($row); |
| 919 | 962 |
} |
| 963 |
if ( ! $allrows ) {
|
|
| 964 |
$pages->{max} = SL::DB::Helper::Paginated::ceil($form->{maxrows}, $pages->{per_page}) || 1;
|
|
| 965 |
$pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
|
|
| 966 |
$pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
|
|
| 967 |
|
|
| 968 |
$report->set_options('raw_bottom_info_text' => $form->parse_html_template('common/paginate',
|
|
| 969 |
{'pages' => $pages , 'base_url' => $href}) );
|
|
| 970 |
} |
|
| 920 | 971 |
|
| 921 | 972 |
$report->generate_with_headers(); |
| 922 | 973 |
|
Auch abrufbar als: Unified diff
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)