X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/725eb2559b0e9fd22c2407d5086e1f8c78ae4ebd..f217d072d76183bc07723dcc29503b732bd2022d:/SL/Controller/DeliveryValueReport.pm diff --git a/SL/Controller/DeliveryValueReport.pm b/SL/Controller/DeliveryValueReport.pm index 05a1064fe..f5608336e 100644 --- a/SL/Controller/DeliveryValueReport.pm +++ b/SL/Controller/DeliveryValueReport.pm @@ -5,18 +5,20 @@ use parent qw(SL::Controller::Base); use Clone qw(clone); use SL::DB::OrderItem; +use SL::DB::Order::TypeData qw(:types); use SL::DB::Business; use SL::Controller::Helper::GetModels; use SL::Controller::Helper::ReportGenerator; use SL::Locale::String; +use SL::Helper::ShippedQty; use SL::AM; -use SL::DBUtils (); +use SL::DBUtils qw(selectall_as_map); +use List::MoreUtils qw(uniq); use Carp; use Data::Dumper; use Rose::Object::MakeMethods::Generic ( - scalar => [ qw(db_args flat_filter) ], - 'scalar --get_set_init' => [ qw(models vc all_employees all_businesses) ], + 'scalar --get_set_init' => [ qw(models vc all_employees all_businesses all_partsgroups) ], ); __PACKAGE__->run_before(sub { $::auth->assert('delivery_value_report'); }); @@ -38,7 +40,7 @@ my %sort_columns = ( delivered_qty => t8('transferred in / out'), netto_delivered_qty => t8('Net value transferred in / out'), do_closed_qty => t8('Qty in closed delivery orders'), - netto_do_closed_qty => t8('Net value in closed delivery orders') + netto_do_closed_qty => t8('Net value in closed delivery orders'), ); @@ -55,16 +57,18 @@ sub action_list { my $orderitems = $self->models->get; $self->calc_qtys_price($orderitems); + $self->setup_list_action_bar; $self->report_generator_list_objects(report => $self->{report}, objects => $orderitems); } sub prepare_report { my ($self) = @_; - my $vc = $self->vc; - my $report = SL::ReportGenerator->new(\%::myconfig, $::form); - my $csv_option = $::form->{report_generator_output_format}; - $self->{report} = $report; + my $vc = $self->vc; + my $report = SL::ReportGenerator->new(\%::myconfig, $::form); + my $csv_option = $::form->{report_generator_output_format}; + $report->{title} = t8('Delivery Value Report'); + $self->{report} = $report; my @columns = qw(reqdate customer vendor ordnumber partnumber description unit qty netto_qty not_shipped_qty netto_not_shipped_qty shipped_qty netto_shipped_qty delivered_qty @@ -82,29 +86,18 @@ sub prepare_report { obj_link => sub { $self->link_to($_[0]->part) } }, partnumber => { sub => sub { $_[0]->part->partnumber }, obj_link => sub { $self->link_to($_[0]->part) } }, - qty => { sub => sub { $_[0]->qty_as_number . - ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } }, - netto_qty => { sub => sub { $::form->format_amount(\%::myconfig, - ($_[0]->qty * $_[0]->sellprice * (1 - $_[0]->discount) / - ($_[0]->price_factor || 1), 2)) },}, - unit => { sub => sub { $_[0]->unit }, - visible => $rp_csv_mod }, - shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{shipped_qty}, 2) . - ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } }, - netto_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_shipped_qty}, 2) },}, - not_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]->qty - $_[0]{shipped_qty} - - $_[0]{delivered_qty} - $_[0]{do_closed_qty}, 2) . - ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } }, - delivered_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{delivered_qty}, 2) . - ($rp_csv_mod ? '' : ' ' . $_[0]->unit) } }, - netto_delivered_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_delivered_qty}, 2) },}, - netto_not_shipped_qty => { sub => sub { $::form->format_amount(\%::myconfig,(($_[0]->qty - - $_[0]{shipped_qty} - $_[0]{delivered_qty} - $_[0]{do_closed_qty}) - * ($_[0]->sellprice * (1 - $_[0]->discount) / - ($_[0]->price_factor || 1)), 2)) },}, - do_closed_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{do_closed_qty}, 2) . - ($rp_csv_mod ? '' : ' ' . $_[0]->unit) },}, - netto_do_closed_qty => { sub => sub { $::form->format_amount(\%::myconfig, $_[0]{netto_do_closed_qty}, 2) },}, + qty => { sub => sub { _format_qty($_[0], 'qty', $rp_csv_mod) } }, + netto_qty => { sub => sub { _format_val($_[0], 'qty') },}, + unit => { sub => sub { $_[0]->unit }, + visible => $rp_csv_mod }, + shipped_qty => { sub => sub { _format_qty($_[0], 'shipped_qty', $rp_csv_mod) } }, + netto_shipped_qty => { sub => sub { _format_val($_[0], 'shipped_qty') },}, + not_shipped_qty => { sub => sub { _format_qty($_[0], 'not_shipped_qty', $rp_csv_mod) } }, + netto_not_shipped_qty => { sub => sub { _format_val($_[0], 'not_shipped_qty') },}, + delivered_qty => { sub => sub { _format_qty($_[0], 'delivered_qty', $rp_csv_mod) } }, + netto_delivered_qty => { sub => sub { _format_val($_[0], 'delivered_qty') },}, + do_closed_qty => { sub => sub { _format_qty($_[0], 'do_closed_qty', $rp_csv_mod) },}, + netto_do_closed_qty => { sub => sub { _format_val($_[0], 'do_closed_qty') },}, ordnumber => { sub => sub { $_[0]->order->ordnumber }, obj_link => sub { $self->link_to($_[0]->order) } }, vendor => { sub => sub { $_[0]->order->vendor->name }, @@ -150,13 +143,14 @@ sub prepare_report { sub make_filter_summary { my ($self) = @_; my $vc = $self->vc; - my ($business, $employee); + my ($business, $employee, $partsgroup); my $filter = $::form->{filter} || {}; my @filter_strings; - $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"}; - $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id}; + $business = SL::DB::Business->new(id => $filter->{order}{customer}{"business_id"})->load->description if $filter->{order}{customer}{"business_id"}; + $employee = SL::DB::Employee->new(id => $filter->{order}{employee_id})->load->name if $filter->{order}{employee_id}; + $partsgroup = SL::DB::PartsGroup->new(id => $filter->{part}{partsgroup_id})->load->partsgroup if $filter->{part}{partsgroup_id}; my @filters = ( [ $filter->{order}{"ordnumber:substr::ilike"}, $::locale->text('Number') ], @@ -172,6 +166,7 @@ sub make_filter_summary { [ $filter->{order}{customer}{"customernumber:substr::ilike"}, $::locale->text('Customer Number') ], [ $business, $::locale->text('Customer type') ], [ $employee, $::locale->text('Employee') ], + [ $partsgroup, $::locale->text('Partsgroup') ], ); # flags for with_object 'part' @@ -199,6 +194,7 @@ sub make_filter_summary { sub init_models { my ($self) = @_; my $vc = $self->vc; + my $record_type = ($vc eq 'customer' ? SALES_ORDER_TYPE() : PURCHASE_ORDER_TYPE()); SL::Controller::Helper::GetModels->new( controller => $self, model => 'OrderItem', @@ -211,7 +207,7 @@ sub init_models { }, # show only open (sales|purchase) orders query => [ 'order.closed' => '0', "order.${vc}_id" => { gt => 0 }, - 'order.quotation' => 0 ], + 'order.record_type' => $record_type ], with_objects => [ 'order', "order.$vc", 'part' ], additional_url_params => { vc => $vc}, ) @@ -226,6 +222,9 @@ sub init_all_employees { sub init_all_businesses { return SL::DB::Manager::Business->get_all_sorted; } +sub init_all_partsgroups { + return SL::DB::Manager::PartsGroup->get_all_sorted; +} sub link_to { @@ -236,14 +235,12 @@ sub link_to { if ($object->isa('SL::DB::Order')) { my $type = $object->type; - my $vc = $object->is_sales ? 'customer' : 'vendor'; my $id = $object->id; - - return "oe.pl?action=$action&type=$type&vc=$vc&id=$id"; + return "controller.pl?action=Order/$action&type=$type&id=$id"; } if ($object->isa('SL::DB::Part')) { my $id = $object->id; - return "ic.pl?action=$action&id=$id"; + return "controller.pl?action=Part/$action&part.id=$id"; } if ($object->isa('SL::DB::Customer')) { my $id = $object->id; @@ -251,50 +248,72 @@ sub link_to { } } +sub _format_qty { + my ($item, $col, $csv_mod) = @_; + + $::form->format_amount(\%::myconfig, $item->{$col}, 2) . ($csv_mod ? '' : ' ' . $item->unit) +} + +sub _format_val { + my ($item, $col) = @_; + + $::form->format_amount(\%::myconfig, $item->{$col} * $item->sellprice * (1 - $item->discount) / ($item->price_factor || 1), 2) +} + sub calc_qtys_price { my ($self, $orderitems) = @_; - # using $orderitem->shipped_qty 40 times is far too slow. need to do it manually - # also for calc net values return unless scalar @$orderitems; - my %orderitems_by_id = map { $_->id => $_ } @$orderitems; + SL::Helper::ShippedQty + ->new(require_stock_out => 1) + ->calculate($orderitems) + ->write_to_objects; - my $query = <get_standard_dbh, $query, map { $_->id } @$orderitems); - - for my $row (@$result) { - my $item = $orderitems_by_id{ $row->{id} }; - $item->{shipped_qty} ||= 0; - $item->{delivered_qty} ||= 0; - $item->{do_closed_qty} ||= 0; - $item->{shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered} || $row->{closed}); - $item->{delivered_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{delivered} && !$row->{closed}); - $item->{do_closed_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} if ($row->{closed}); - $item->{not_shipped_qty} += AM->convert_unit($row->{unit} => $item->unit) * $row->{qty} unless ($row->{delivered}); - - my $price_factor = $row->{price_factor} || 1; - $item->{netto_shipped_qty} = $item->{shipped_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor; - $item->{netto_delivered_qty} = $item->{delivered_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor; - $item->{netto_do_closed_qty} = $item->{do_closed_qty} * $row->{sellprice} * (1 - $row->{discount} ) / $price_factor; + $_->{delivered_qty} = delete $_->{shipped_qty} for @$orderitems; + my $helper = SL::Helper::ShippedQty + ->new(require_stock_out => 0, keep_matches => 1) + ->calculate($orderitems) + ->write_to_objects; + + for my $item (@$orderitems) { + $item->{not_shipped_qty} = $item->qty - $item->{shipped_qty}; + $item->{do_closed_qty} = 0; + + my $price_factor = $item->price_factor || 1; } -} + if (my @all_doi_ids = uniq map { $_->[1] } @{ $helper->matches }) { + my %oi_by_id = map { $_->id => $_ } @$orderitems; + my $query = sprintf <<'', join ', ', ("?")x@all_doi_ids; + SELECT DISTINCT doi.id, closed FROM delivery_orders + LEFT JOIN delivery_order_items doi ON (doi.delivery_order_id = delivery_orders.id) + WHERE doi.id IN (%s) + my %doi_is_closed = selectall_as_map($::form, SL::DB->client->dbh, $query, (id => 'closed'), @all_doi_ids); + for my $match (@{ $helper->matches }) { + next unless $doi_is_closed{$match->[1]}; + $oi_by_id{$match->[0]}->{do_closed_qty} += $match->[2]; + } + } +} + +sub setup_list_action_bar { + my ($self, %params) = @_; + + for my $bar ($::request->layout->get('actionbar')) { + $bar->add( + action => [ + t8('Update'), + submit => [ '#filter_form', { action => 'DeliveryValueReport/list' } ], + accesskey => 'enter', + ], + ); + } +} 1;