projekt kivitendo

package SL::Controller::DeliveryPlan;

use strict;

use parent qw(SL::Controller::Base);

use Clone qw(clone);

use SL::DB::OrderItem;

use SL::Controller::Helper::ParseFilter;

use SL::Controller::Helper::ReportGenerator;

__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); });

sub action_list {

  my ($self) = @_;

  my %list_params = (

    sort_by  => $::form->{sort_by} || 'reqdate',

    sort_dir => $::form->{sort_dir},

    filter   => $::form->{filter},

    page     => $::form->{page},

  );

  my $db_args = $self->setup_for_list(%list_params);

  $self->{pages} = SL::DB::Manager::OrderItem->paginate(%list_params, args => $db_args);

  $self->{flat_filter} = { map { $_->{key} => $_->{value} } $::form->flatten_variables('filter') };

  my $top    = $::form->parse_html_template('delivery_plan/report_top', { FORM => $::form, SELF => $self });

  my $bottom = $::form->parse_html_template('delivery_plan/report_bottom', { SELF => $self });

  $self->prepare_report(

    report_generator_options => {

      raw_top_info_text    => $top,

      raw_bottom_info_text => $bottom,

      controller_class     => 'DeliveryPlan',

    },

    report_generator_export_options => [

      'list', qw(filter sort_by sort_dir),

    ],

    db_args => $db_args,

  );

  $self->{orderitems} = SL::DB::Manager::OrderItem->get_all(%$db_args);

  $self->list_objects;

}

# private functions

sub setup_for_list {

  my ($self, %params) = @_;

  $self->{filter} = {};

  my %args = (

    parse_filter(

      $self->_pre_parse_filter($::form->{filter}, $self->{filter}),

      with_objects => [ 'order', 'order.customer', 'part' ],

      launder_to => $self->{filter},

    ),

    sort_by => $self->set_sort_params(%params),

    page    => $params{page},

  );

  $args{query} = [ @{ $args{query} || [] },

    (

      'order.customer_id' => { gt => 0 },

      'order.closed' => 0,

      or => [ 'order.quotation' => 0, 'order.quotation' => undef ],

      # filter by shipped_qty < qty, read from innermost to outermost

      'id' => [ \"

        -- 3. resolve the desired information about those

        SELECT oi.id FROM (

          -- 2. slice only part, orderitem and both quantities from it

          SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM (

            -- 1. join orderitems and deliverorder items via record_links.

            --    also add customer data to filter for sales_orders

            SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty

            FROM orderitems oi, oe, record_links rl, delivery_order_items doi

            WHERE

              oe.id = oi.trans_id AND

              oe.customer_id IS NOT NULL AND

              (oe.quotation = 'f' OR oe.quotation IS NULL) AND

              NOT oe.closed AND

              rl.from_id = oe.id AND

              rl.from_id = oi.trans_id AND

              oe.id = oi.trans_id AND

              rl.from_table = 'oe' AND

              rl.to_table = 'delivery_orders' AND

              rl.to_id = doi.delivery_order_id AND

              oi.parts_id = doi.parts_id

          ) tuples GROUP BY parts_id, trans_id, qty

        ) partials

        LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id

        WHERE oi.qty > doi_qty

        UNION ALL

        -- 4. since the join over record_links fails for sales_orders wihtout any delivery order

        --    retrieve those without record_links at all

        SELECT oi.id FROM orderitems oi, oe

        WHERE

          oe.id = oi.trans_id AND

          oe.customer_id IS NOT NULL AND

          (oe.quotation = 'f' OR oe.quotation IS NULL) AND

          NOT oe.closed AND

          oi.trans_id NOT IN (

            SELECT from_id

            FROM record_links rl

            WHERE

              rl.from_table ='oe' AND

              rl.to_table = 'delivery_orders'

          )

      " ],

    )

  ];

  return \%args;

}

sub set_sort_params {

  my ($self, %params) = @_;

  my $sort_str;

  ($self->{sort_by}, $self->{sort_dir}, $sort_str) =

    SL::DB::Manager::OrderItem->make_sort_string(%params);

  return $sort_str;

}

sub prepare_report {

  my ($self, %params) = @_;

  my $objects  = $params{objects} || [];

  my $report = SL::ReportGenerator->new(\%::myconfig, $::form);

  $self->{report} = $report;

  my @columns  = qw(reqdate customer ordnumber partnumber description qty shipped_qty);

  my @visible  = qw(reqdate partnumber description qty shipped_qty ordnumber customer);

  my @sortable = qw(reqdate partnumber description                 ordnumber customer);

  my %column_defs = (

    reqdate                 => { text => $::locale->text('Reqdate'),

                                  sub => sub { $_[0]->reqdate_as_date || $_[0]->order->reqdate_as_date }},

    description             => { text => $::locale->text('Description'),

                                  sub => sub { $_[0]->description },

                             obj_link => sub { $self->link_to($_[0]->part) }},

    partnumber              => { text => $::locale->text('Part Number'),

                                  sub => sub { $_[0]->part->partnumber },

                             obj_link => sub { $self->link_to($_[0]->part) }},

    qty                     => { text => $::locale->text('Qty'),

                                  sub => sub { $_[0]->qty_as_number . ' ' . $_[0]->unit }},

    shipped_qty             => { text => $::locale->text('shipped'),

                                  sub => sub { $::form->format_amount(\%::myconfig, $_[0]->shipped_qty, 2) . ' ' . $_[0]->unit }},

    ordnumber               => { text => $::locale->text('Order'),

                                  sub => sub { $_[0]->order->ordnumber },

                             obj_link => sub { $self->link_to($_[0]->order) }},

    customer                => { text => $::locale->text('Customer'),

                                  sub => sub { $_[0]->order->customer->name },

                             obj_link => sub { $self->link_to($_[0]->order->customer) }},

  );

  for my $col (@sortable) {

    $column_defs{$col}{link} = $self->url_for(

      action   => 'list',

      sort_by  => $col,

      sort_dir => ($self->{sort_by} eq $col ? 1 - $self->{sort_dir} : $self->{sort_dir}),

      page     => $self->{pages}{cur},

      %{ $self->{flat_filter} },

    );

  }

  map { $column_defs{$_}->{visible} = 1 } @visible;

  $report->set_columns(%column_defs);

  $report->set_column_order(@columns);

  $report->set_options(allow_pdf_export => 1, allow_csv_export => 1);

  $report->set_sort_indicator(%params);

  $report->set_export_options(@{ $params{report_generator_export_options} || [] });

  $report->set_options(

    %{ $params{report_generator_options} || {} },

    output_format        => 'HTML',

    top_info_text        => $::locale->text('Delivery Plan for currently outstanding sales orders'),

    title                => $::locale->text('Delivery Plan'),

  );

  $report->set_options_from_form;

  SL::DB::Manager::OrderItem->disable_paginating(args => $params{db_args}) if $report->{options}{output_format} =~ /^(pdf|csv)$/i;

  $self->{report_data} = {

    column_defs => \%column_defs,

    columns     => \@columns,

    visible     => \@visible,

    sortable    => \@sortable,

  };

}

sub list_objects {

  my ($self) = @_;

  my $column_defs = $self->{report_data}{column_defs};

  for my $obj (@{ $self->{orderitems} || [] }) {

    $self->{report}->add_data({

      map {

        $_ => {

          data => $column_defs->{$_}{sub} ? $column_defs->{$_}{sub}->($obj)

                : $obj->can($_)           ? $obj->$_

                :                           $obj->{$_},

          link => $column_defs->{$_}{obj_link} ? $column_defs->{$_}{obj_link}->($obj) : '',

        },

      } @{ $self->{report_data}{columns} || {} }

    });

  }

  return $self->{report}->generate_with_headers;

}

sub link_to {

  my ($self, $object, %params) = @_;

  return unless $object;

  my $action = $params{action} || 'edit';

  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";

  }

  if ($object->isa('SL::DB::Part')) {

    my $id     = $object->id;

    return "ic.pl?action=$action&id=$id";

  }

  if ($object->isa('SL::DB::Customer')) {

    my $id     = $object->id;

    return "ct.pl?action=$action&id=$id&db=customer";

  }

}

# unfortunately ParseFilter can't handle compount filters.

# so we clone the original filter (still need that for serializing)

# rip out the options we know an replace them with the compound options.

# ParseFilter will take care of the prefixing then.

sub _pre_parse_filter {

  my ($self, $orig_filter, $launder_to) = @_;

  return undef unless $orig_filter;

  my $filter = clone($orig_filter);

  if ($filter->{part} && $filter->{part}{type}) {

    $launder_to->{part}{type} = delete $filter->{part}{type};

    my @part_filters = grep $_, map {

      $launder_to->{part}{type}{$_} ? SL::DB::Manager::Part->type_filter($_) : ()

    } qw(part service assembly);

    push @{ $filter->{and} }, or => [ @part_filters ] if @part_filters;

  }

  if ($filter->{'reqdate:date::le'}) {

    $launder_to->{'reqdate_date__le'} = delete $filter->{'reqdate:date::le'};

    my $parsed_date = DateTime->from_lxoffice($launder_to->{'reqdate_date__le'});

    push @{ $filter->{and} }, or => [

      'reqdate' => { le => $parsed_date },

      and => [

        'reqdate' => undef,

        'order.reqdate' => { le => $parsed_date },

      ]

    ] if $parsed_date;

  }

  return $filter;

}

1;

                        position      => [ 'trans_id', 'runningnumber' ],

                        reqdate       => [ 'COALESCE(orderitems.reqdate, order.reqdate)' ],

                        transdate     => [ 'transdate::date', 'order.reqdate' ],

  { name => "Clone",                               url => "http://search.cpan.org/~rdf/",       debian => 'libclone-perl' },

  'Assembly'                    => 'Erzeugnis',

  'Delivery Orders for this document' => 'Lieferscheine für dieses Dokument',

  'Delivery Plan'               => 'Lieferplan',

  'Delivery Plan for currently outstanding sales orders' => 'Lieferplan für offene Verkaufsaufträge',

  'Delivery information deleted.' => 'Lieferinformation gelöscht.',

  'Delivery information saved.' => 'Lieferinformation gespeichert.',

  'Hide Filter'                 => 'Filter verbergen',

  'Reset'                       => 'Zurücksetzen',

  'Show Filter'                 => 'Filter zeigen',

[AR--Reports--Delivery Plan]

ACCESS=sales_order_edit

module=controller.pl

action=DeliveryPlan/list

[%- USE T8 %]

[%- USE L %]

[%- USE LxERP %]

[%- USE HTML %]

<form action='controller.pl' method='post'>

<div class='filter_toggle'>

<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Show Filter' | $T8 %]</a>

  [% SELF.filter_summary %]

</div>

<div class='filter_toggle' style='display:none'>

<a href='#' onClick='javascript:$(".filter_toggle").toggle()'>[% 'Hide Filter' | $T8 %]</a>

 <table id='filter_table'>

  <tr>

   <th align="right">[% 'Number' | $T8 %]</th>

   <td>[% L.input_tag('filter.order.ordnumber:substr::ilike', filter.order.ordnumber_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Part Number' | $T8 %]</th>

   <td>[% L.input_tag('filter.part.partnumber:substr::ilike', filter.part.partnumber_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Part Description' | $T8 %]</th>

   <td>[% L.input_tag('filter.description:substr::ilike', filter.description_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Delivery Date' | $T8 %]</th>

   <td>[% L.date_tag('filter.reqdate:date::le', filter.reqdate_date__le, cal_align = 'BR') %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Quantity' | $T8 %]</th>

   <td>[% L.input_tag('filter.qty:number', filter.qty_number, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Customer' | $T8 %]</th>

   <td>[% L.input_tag('filter.order.customer.name:substr::ilike', filter.order.customer.name_substr__ilike, size = 20) %]</td>

  </tr>

  <tr>

   <th align="right">[% 'Type' | $T8 %]</th>

   <td>

     [% L.checkbox_tag('filter.part.type.part',     checked=filter.part.type.part,     label=LxERP.t8('Part')) %]

     [% L.checkbox_tag('filter.part.type.service',  checked=filter.part.type.service,  label=LxERP.t8('Service')) %]

     [% L.checkbox_tag('filter.part.type.assembly', checked=filter.part.type.assembly, label=LxERP.t8('Assembly')) %]

   </td>

  </tr>

 </table>

[% L.hidden_tag('action', 'DeliveryPlan/dispatch') %]

[% L.hidden_tag('sort_by', FORM.sort_by) %]

[% L.hidden_tag('sort_dir', FORM.sort_dir) %]

[% L.hidden_tag('page', FORM.page) %]

[% L.input_tag('action_list', LxERP.t8('Continue'), type = 'submit', class='submit')%]

<a href='#' onClick='javascript:$("#filter_table input").attr("value","");$("#filter_table option").attr("selected","")'>[% 'Reset' | $T8 %]</a>

</div>

</form>

[% USE HTML %][% USE T8 %][% USE L %][% USE LxERP %]

[% BLOCK header %]

 [% SET new_sort_dir = SELF.sort_by == sort_by ? 1 - SELF.sort_dir : SELF.sort_dir %]

 <th width="[% size %]%">

  <a href="[% SELF.url_for(action => 'list') %]&sort_by=[% sort_by %]&sort_dir=[% new_sort_dir %]&page=[% FORM.page %]">

   [%- title %]

   [%- IF SELF.sort_by == sort_by %]

    <img src="image/[% IF SELF.sort_dir %]down[% ELSE %]up[% END %].png" border="0">

   [%- END %]

  </a>

 </th>

[% END %]

<div id="orders">

[%- IF !SELF.orderitems.size %]

 <p>[%- 'There are no outstanding deliveries at the moment.' | $T8 %]</p>

[%- ELSE %]

 <table width=100%>

  <tr class="listheading">

   [% PROCESS header   title=LxERP.t8('Date')        sort_by='transdate',     size=15 %]

   [% PROCESS header   title=LxERP.t8('Description') sort_by='description',   size=15 %]

   [% PROCESS header   title=LxERP.t8('Part Number') sort_by='partnumber',    size=15 %]

   [% PROCESS header   title=LxERP.t8('Qty')         sort_by='qty',           size=10 %]

   [% PROCESS header   title=LxERP.t8('Order')       sort_by='ordnumber',     size=10 %]

   [% PROCESS header   title=LxERP.t8('Customer')    sort_by='customer',      size=10 %]

  </tr>

  [%- FOREACH row = SELF.orderitems %]

  <tr class="listrow[% loop.count % 2 %]">

   <td>[% row.transdate ? row.transdate : row.order.reqdate_as_date %]</td>

   <td>[% row.part.partnumber | html %]</td>

   <td>[% row.description | html %]</td>

   <td class='numeric'>[% LxERP.format_amount(row.qty, 2) | html %]</td>

   <td>[% row.order.ordnumber | html %]</td>

   <td>[% row.order.customer.name | html %]</td>

  </tr>

  [%- END %]

 </table>

 <p align=right>[% PROCESS 'common/paginate.html' pages=SELF.pages, base_url=SELF.url_for(action='list', sort_dir=SELF.sort_dir, sort_by=SELF.sort_by) %]</p>

[%- END %]

</div>

[%- USE T8 %]

<h1>[% 'Delivery Plan' | $T8 %]</h1>

[%- PROCESS 'delivery_plan/_filter.html' filter=FORM.filter %]

 <hr>

[%- PROCESS 'delivery_plan/_list.html' %]

[% SET report_bottom_url_args = {} %]

[%     report_bottom_url_args.import(SELF.flat_filter) %]

[%     report_bottom_url_args.import({action='list', sort_dir=SELF.sort_dir, sort_by=SELF.sort_by}) %]

<p align=right>[% PROCESS 'common/paginate.html' pages=SELF.pages, base_url=SELF.url_for(report_bottom_url_args) %]</p>

[%- USE L %]

[%- PROCESS 'delivery_plan/_filter.html' filter=SELF.filter %]

 <hr>