projekt kivitendo

package SL::Controller::LiquidityProjection;

use strict;

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

use SL::Locale::String;

use SL::LiquidityProjection;

use SL::Util qw(_hashify);

__PACKAGE__->run_before('check_auth');

use Rose::Object::MakeMethods::Generic (

  scalar                  => [ qw(liquidity) ],

  'scalar --get_set_init' => [ qw(oe_report_columns_str) ],

);

#

# actions

#

sub action_show {

  my ($self) = @_;

  $self->liquidity(SL::LiquidityProjection->new(%{ $::form->{params} })->create) if $::form->{params};

  $::form->{params} ||= {

    months            => 6,

    type              => 1,

    salesman          => 1,

    buchungsgruppe    => 1,

  };

  $self->render('liquidity_projection/show', title => t8('Liquidity projection'));

}

#

# filters

#

sub check_auth                 { $::auth->assert('report') }

sub init_oe_report_columns_str { join '&', map { "$_=Y" } qw(open delivered notdelivered l_ordnumber l_transdate l_reqdate l_name l_employee l_salesman l_netamount l_amount l_transaction_description) }

#

# helpers

#

sub link_to_old_orders {

  my $self    = shift;

  my %params  = _hashify(0, @_);

  my $reqdate = $params{reqdate};

  my $months  = $params{months} * 1;

  my $fields  = '';

  if ($reqdate eq 'old') {

    $fields .= '&reqdate_unset_or_old=Y';

  } elsif ($reqdate eq 'future') {

    my @now  = localtime;

    $fields .= '&reqdatefrom=' . $self->iso_to_display(SL::LiquidityProjection::_the_date($now[5] + 1900, $now[4] + 1 + $months) . '-01');

  } else {

    $reqdate =~ m/(\d+)-(\d+)/;

    $fields .=  '&reqdatefrom=' . $self->iso_to_display($reqdate . '-01');

    $fields .=  '&reqdateto='   . $self->iso_to_display($reqdate . sprintf('-%02d', DateTime->last_day_of_month(year => $1, month => $2)->day));

  }

  return "oe.pl?action=orders&type=sales_order&vc=customer&" . $self->oe_report_columns_str . $fields;

}

sub iso_to_display {

  my ($self, $date) = @_;

  $::locale->reformat_date({ dateformat => 'yyyy-mm-dd' }, $date, $::myconfig{dateformat});

}

1;

package SL::LiquidityProjection;

use strict;

use List::MoreUtils qw(uniq);

use SL::DBUtils;

sub new {

  my $package       = shift;

  my $self          = bless {}, $package;

  my %params        = @_;

  $self->{params}   = \%params;

  my @now           = localtime;

  my $now_year      = $now[5] + 1900;

  my $now_month     = $now[4] + 1;

  $self->{min_date} = _the_date($now_year, $now_month);

  $self->{max_date} = _the_date($now_year, $now_month + $params{months} - 1);

  $self;

}

# Algorithmus:

#

# Für den aktuellen Monat und alle x Folgemonate soll der geplante

# Liquiditätszufluss aufgeschlüsselt werden. Der Zufluss berechnet

# sich dabei aus:

#

# 1. Summe aller offenen Auträge

#

# 2. abzüglich aller zu diesen Aufträgen erstellten Rechnungen

# (Teillieferungen/Teilrechnungen)

#

# 3. zuzüglich alle aktiven Wartungsverträge, die in dem jeweiligen

# Monat ihre Saldierungsperiode haben, außer Wartungsverträgen, die

# für den jeweiligen Monat bereits abgerechnet wurden.

#

# Diese Werte sollen zusätzlich optional nach Verkäufer(in) und nach

# Buchungsgruppe aufgeschlüsselt werden.

#

# Diese Lösung geht deshalb immer über die Positionen der Belege

# (wegen der Buchungsgruppe) und berechnet die Summen daraus manuell.

#

# Alle Aufträge, deren Lieferdatum leer ist, oder deren Lieferdatum

# vor dem aktuellen Monat liegt, werden in einer Kategorie 'alt'

# zusammengefasst.

#

# Alle Aufträge, deren Lieferdatum nach dem zu betrachtenden Zeitraum

# (aktueller Monat + x Monate) liegen, werden in einer Kategorie

# 'Zukunft' zusammengefasst.

#

# Insgesamt läuft es wie folgt ab:

#

# 1. Es wird das Datum aller periodisch erzeugten Rechnungen innerhalb

# des Betrachtungszeitraumes herausgesucht.

#

# 2. Alle aktiven Wartungsvertragskonfigurationen werden

# ausgelesen. Die Saldierungsmonate werden solange aufaddiert, wie der

# dabei herauskommende Monat nicht nach dem zu betrachtenden Zeitraum

# liegt.

#

# 3. Für jedes Saldierungsintervall, das innerhalb des

# Betrachtungszeitraumes liegt, und für das es für den Monat noch

# keine Rechnung gibt (siehe 1.), wird diese Konfiguration für den

# Monat vorgemerkt.

#

# 4. Es werden für alle offenen Kundenaufträge die Positionen

# ausgelesen und mit Verkäufer(in), Buchungsgruppe verknüpft. Aus

# Menge, Einzelpreis und Zeilenrabatt wird die Zeilensumme berechnet.

#

# 5. Mit den Informationen aus 3. und 4. werden Datenstrukturen

# initialisiert, die für die Gesamtsummen, für alle Verkäufer(innen),

# für alle Buchungsgruppen, für alle Monate Werte enthalten.

#

# 6. Es wird über alle Einträge aus 4. iteriert. Die Zeilensummen

# werden in den entsprechenden Datenstrukturen aus 5. addiert.

#

# 7. Es wird über alle Einträge aus 3. iteriert. Die Zeilensummen

# werden in den entsprechenden Datenstrukturen aus 5. addiert.

#

# 8. Es werden alle Rechnungspositionen ausgelesen, bei denen die

# Auftragsnummer einer der aus 5. ermittelten Aufträge entspricht.

#

# 9. Es wird über alle Einträge aus 8. iteriert. Die Zeilensummen

# werden von den entsprechenden Datenstrukturen aus 5. abgezogen. Als

# Datum wird dabei das Datum des zu der Rechnung gehörenden Auftrages

# genommen. Als Buchungsgruppe wird die Buchungsgruppe der Zeile

# genommen. Falls es passieren sollte, dass diese Buchungsgruppe in

# den Aufträgen nie vorgekommen ist (sprich Rechnung enthält

# Positionen, die im Auftrag nicht enthalten sind, und die komplett

# andere Buchungsgruppen verwenden), so wird schlicht die allererste

# in 4. gefundene Buchungsgruppe damit belastet.

sub create {

  my ($self)   = @_;

  my %params   = %{ $self->{params} };

  my $dbh      = $params{dbh} || $::form->get_standard_dbh;

  my ($sth, $ref, $query);

  $params{months} ||= 6;

  # 1. Auslesen aller erzeugten periodischen Rechnungen im

  # Betrachtungszeitraum

  my $q_min_date = $dbh->quote($self->{min_date} . '-01');

  $query         = <<SQL;

    SELECT pi.config_id, to_char(pi.period_start_date, 'YYYY-MM') AS period_start_date

    FROM periodic_invoices pi

    LEFT JOIN periodic_invoices_configs pcfg ON (pi.config_id = pcfg.id)

    WHERE pcfg.active

      AND (pi.period_start_date >= to_date($q_min_date, 'YYYY-MM-DD'))

SQL

  my %periodic_invoices;

  $sth = prepare_execute_query($::form, $dbh, $query);

  while ($ref = $sth->fetchrow_hashref) {

    $periodic_invoices{ $ref->{config_id} }                                ||= { };

    $periodic_invoices{ $ref->{config_id} }->{ $ref->{period_start_date} }   = 1;

  }

  $sth->finish;

  # 2. Auslesen aktiver Wartungsvertragskonfigurationen

  $query = <<SQL;

    SELECT (oi.qty * (1 - oi.discount) * oi.sellprice) AS linetotal,

      bg.description AS buchungsgruppe,

      CASE WHEN COALESCE(e.name, '') = '' THEN e.login ELSE e.name END AS salesman,

      pcfg.periodicity, pcfg.id AS config_id,

      EXTRACT(year FROM pcfg.start_date) AS start_year, EXTRACT(month FROM pcfg.start_date) AS start_month

    FROM orderitems oi

    LEFT JOIN oe                             ON (oi.trans_id                              = oe.id)

    LEFT JOIN periodic_invoices_configs pcfg ON (oi.trans_id                              = pcfg.oe_id)

    LEFT JOIN parts p                        ON (oi.parts_id                              = p.id)

    LEFT JOIN buchungsgruppen bg             ON (p.buchungsgruppen_id                     = bg.id)

    LEFT JOIN employee e                     ON (COALESCE(oe.salesman_id, oe.employee_id) = e.id)

    WHERE pcfg.active

SQL

  # 3. Iterieren über Saldierungsintervalle, vormerken

  my %periodicities = ( 'm' => 1, 'q' => 3,  'y' => 12 );

  my @scentries;

  $sth = prepare_execute_query($::form, $dbh, $query);

  while ($ref = $sth->fetchrow_hashref) {

    my ($year, $month) = ($ref->{start_year}, $ref->{start_month});

    my $date;

    while (($date = _the_date($year, $month)) le $self->{max_date}) {

      if (($date ge $self->{min_date}) && (!$periodic_invoices{ $ref->{config_id} } || !$periodic_invoices{ $ref->{config_id} }->{$date})) {

        push @scentries, { buchungsgruppe => $ref->{buchungsgruppe},

                           salesman       => $ref->{salesman},

                           linetotal      => $ref->{linetotal},

                           date           => $date,

                         };

      }

      ($year, $month) = _fix_date($year, $month + ($periodicities{ $ref->{periodicity} } || 1));

    }

  }

  $sth->finish;

  # 4. Auslesen offener Aufträge

  $query = <<SQL;

    SELECT (oi.qty * (1 - oi.discount) * oi.sellprice) AS linetotal,

      bg.description AS buchungsgruppe,

      CASE WHEN COALESCE(e.name, '') = '' THEN e.login ELSE e.name END AS salesman,

      oe.ordnumber, EXTRACT(month FROM oe.reqdate) AS month, EXTRACT(year  FROM oe.reqdate) AS year

    FROM orderitems oi

    LEFT JOIN oe                 ON (oi.trans_id                              = oe.id)

    LEFT JOIN parts p            ON (oi.parts_id                              = p.id)

    LEFT JOIN buchungsgruppen bg ON (p.buchungsgruppen_id                     = bg.id)

    LEFT JOIN employee e         ON (COALESCE(oe.salesman_id, oe.employee_id) = e.id)

    WHERE (oe.customer_id IS NOT NULL)

      AND NOT COALESCE(oe.quotation, FALSE)

      AND NOT COALESCE(oe.closed,    FALSE)

      AND (oe.id NOT IN (SELECT oe_id FROM periodic_invoices_configs))

SQL

  # 5. Initialisierung der Datenstrukturen zum Speichern der

  # Ergebnisse

  my @entries               = selectall_hashref_query($::form, $dbh, $query);

  my @salesmen              = uniq map { $_->{salesman}       } (@entries, @scentries);

  my @buchungsgruppen       = uniq map { $_->{buchungsgruppe} } (@entries, @scentries);

  my @now                   = localtime;

  my @dates                 = map { $self->_date_for($now[5] + 1900, $now[4] + $_) } (0..$self->{params}->{months} + 1);

  my %dates_by_ordnumber    = map { $_->{ordnumber} => $self->_date_for($_) } @entries;

  my %salesman_by_ordnumber = map { $_->{ordnumber} => $_->{salesman}       } @entries;

  my %date_sorter           = ( old => '0000-00', future => '9999-99' );

  my $projection    = { total          =>               { map { $_ => 0 } @dates },

                        order          =>               { map { $_ => 0 } @dates },

                        partial        =>               { map { $_ => 0 } @dates },

                        support        =>               { map { $_ => 0 } @dates },

                        salesman       => { map { $_ => { map { $_ => 0 } @dates } } @salesmen        },

                        buchungsgruppe => { map { $_ => { map { $_ => 0 } @dates } } @buchungsgruppen },

                        sorted         => { month          => [ sort { ($date_sorter{$a} || $a) cmp ($date_sorter{$b} || $b) } @dates           ],

                                            salesman       => [ sort { $a                       cmp $b                       } @salesmen        ],

                                            buchungsgruppe => [ sort { $a                       cmp $b                       } @buchungsgruppen ],

                                            type           => [ qw(order partial support)                                                       ],

                                          },

                      };

  # 6. Aufsummieren der Auftragspositionen

  foreach $ref (@entries) {

    my $date = $self->_date_for($ref);

    $projection->{total}->{$date}                                      += $ref->{linetotal};

    $projection->{order}->{$date}                                      += $ref->{linetotal};

    $projection->{salesman}->{ $ref->{salesman} }->{$date}             += $ref->{linetotal};

    $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} }->{$date} += $ref->{linetotal};

  }

  # 7. Aufsummieren der Wartungsvertragspositionen

  foreach $ref (@scentries) {

    my $date = $ref->{date};

    $projection->{total}->{$date}                                      += $ref->{linetotal};

    $projection->{support}->{$date}                                    += $ref->{linetotal};

    $projection->{salesman}->{ $ref->{salesman} }->{$date}             += $ref->{linetotal};

    $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} }->{$date} += $ref->{linetotal};

  }

  if (%dates_by_ordnumber) {

    # 8. Auslesen von Positionen von Teilrechnungen zu Aufträgen

    my $ordnumbers = join ', ', map { $dbh->quote($_) } keys %dates_by_ordnumber;

    $query         = <<SQL;

      SELECT (i.qty * (1 - i.discount) * i.sellprice) AS linetotal,

        bg.description AS buchungsgruppe,

        ar.ordnumber

      FROM invoice i

      LEFT JOIN ar                 ON (i.trans_id           = ar.id)

      LEFT JOIN parts p            ON (i.parts_id           = p.id)

      LEFT JOIN buchungsgruppen bg ON (p.buchungsgruppen_id = bg.id)

      WHERE (ar.ordnumber IN ($ordnumbers))

SQL

    @entries = selectall_hashref_query($::form, $dbh, $query);

    # 9. Abziehen der abgerechneten Positionen

    foreach $ref (@entries) {

      my $date           = $dates_by_ordnumber{    $ref->{ordnumber} } || die;

      my $salesman       = $salesman_by_ordnumber{ $ref->{ordnumber} } || die;

      my $buchungsgruppe = $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} } ? $ref->{buchungsgruppe} : $buchungsgruppen[0];

      $projection->{partial}->{$date}                           -= $ref->{linetotal};

      $projection->{total}->{$date}                             -= $ref->{linetotal};

      $projection->{salesman}->{$salesman}->{$date}             -= $ref->{linetotal};

      $projection->{buchungsgruppe}->{$buchungsgruppe}->{$date} -= $ref->{linetotal};

    }

  }

  return $projection;

}

# Skaliert '$year' und '$month' so, dass 1 <= Monat <= 12 gilt. Zum

# Einfachen Addieren gedacht, z.B.

#

# my ($new_year, $new_month) = _fix_date($old_year, $old_month + 6);

sub _fix_date {

  my $year   = shift;

  my $month  = shift;

  $year     += int(($month - 1) / 12);

  $month     = (($month - 1) % 12 ) + 1;

  ($year, $month);

}

# Formartiert Jahr & Monat wie benötigt.

sub _the_date {

  sprintf '%04d-%02d', _fix_date(@_);

}

# Mappt Datum auf Kategorie. Ist das Datum leer, oder liegt es vor dem

# Betrachtungszeitraum, so ist die Kategorie 'old'. Liegt das Datum

# nach dem Betrachtungszeitraum, so ist die Kategorie

# 'future'. Andernfalls ist sie das formartierte Datum selber.

sub _date_for {

  my $self = shift;

  my $ref  = ref $_[0] eq 'HASH' ? shift : { year => $_[0], month => $_[1] };

  return 'old' if !$ref->{year} || !$ref->{month};

  my $date = _the_date($ref->{year}, $ref->{month});

    $date lt $self->{min_date} ? 'old'

  : $date gt $self->{max_date} ? 'future'

  :                              $date;

}

1;

  if ($form->{reqdate_unset_or_old}) {

    $query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;

  }

                                                        business_id shippingpoint taxzone_id reqdate_unset_or_old

  push @options, $locale->text('Reqdate not set or before current month')                                 if $form->{reqdate_unset_or_old};

  'Basis of calculation'        => 'Berechnungsgrundlage',

  'Break down by'               => 'Aufschlüsseln nach',

  'Liquidity projection'        => 'Liquiditätsübersicht',

  'Number of months'            => 'Anzahl Monate',

  'Partial invoices'            => 'Teilrechnungen',

  'Reqdate not set or before current month' => 'Lieferdatum nicht gesetzt oder vor aktuellem Monat',

  'old'                         => 'alt',

  'prospective'                 => 'zukünftig',

[Reports--Liquidity projection]

ACCESS=report

module=controller.pl

action=LiquidityProjection/show

[%- USE LxERP -%][%- USE L -%]

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

 [% L.hidden_tag('action', 'LiquidityProjection/show') %]

 <table border="0">

  <tr>

   <th align="right">[% LxERP.t8("Number of months") %]</th>

   <td>[% L.input_tag("params.months", FORM.params.months, class="initial_focus") %]</td>

  </tr>

  <tr>

   <th align="right" valign="top">[% LxERP.t8("Break down by") %]</th>

   <td valign="top">

    [% L.checkbox_tag("params.type",           value=1, checked=FORM.params.type,           label=LxERP.t8("Basis of calculation")) %]

    <br>

    [% L.checkbox_tag("params.salesman",       value=1, checked=FORM.params.salesman,       label=LxERP.t8("Salesman")) %]

    <br>

    [% L.checkbox_tag("params.buchungsgruppe", value=1, checked=FORM.params.buchungsgruppe, label=LxERP.t8("Buchungsgruppe")) %]

   </td>

  </tr>

 </table>

 <p>

  [% L.submit_tag("dummy", LxERP.t8("Show")) %]

 </p>

</form>

[%- USE HTML -%][%- USE LxERP -%]

[%- SET name_col = FORM.params.salesman || FORM.params.buchungsgruppe || FORM.params.type %]

<table border="0">

 <tr>

  <th class="listheading">[% LxERP.t8("Type") %]</th>

  [%- IF name_col %]

   <th class="listheading">[% LxERP.t8("Name") %]</th>

  [%- END %]

  [%- FOREACH month = SELF.liquidity.sorted.month %]

   <th class="listheading" align="right">[%- IF month == 'old' %][% LxERP.t8("old") %][% ELSIF month == 'future' %][% LxERP.t8("prospective") %][% ELSE %][%- HTML.escape(month) %][% END %]</th>

  [%- END %]

 </tr>

 [% IF FORM.params.type %]

  [% FOREACH type = SELF.liquidity.sorted.type %]

   <tr class="listrow">

    <td>[% IF loop.first %][% LxERP.t8("Basis of calculation") %][% END %]</td>

    <td>

     [% IF    type == 'order' %][% LxERP.t8("Sales Orders") %]

     [% ELSIF type == 'partial' %][% LxERP.t8("Partial invoices") %]

     [% ELSE %][% LxERP.t8("Periodic Invoices") %]

     [% END %]

    </td>

    [%- FOREACH month = SELF.liquidity.sorted.month %]

     <td align="right">[% LxERP.format_amount(SELF.liquidity.$type.$month, 2) %]</td>

    [%- END %]

   </tr>

  [%- END %]

 [%- END %]

 [%- IF FORM.params.salesman %]

  [%- FOREACH salesman = SELF.liquidity.sorted.salesman %]

   <tr class="listrow">

    <td>[% IF loop.first %][% LxERP.t8("Salesman") %][% END %]</td>

    <td>[%- HTML.escape(salesman) %]</td>

    [%- FOREACH month = SELF.liquidity.sorted.month %]

     <td align="right">[% LxERP.format_amount(SELF.liquidity.salesman.$salesman.$month, 2) %]</td>

    [%- END %]

   </tr>

  [%- END %]

 [%- END %]

 [%- IF FORM.params.buchungsgruppe %]

  [%- FOREACH buchungsgruppe = SELF.liquidity.sorted.buchungsgruppe %]

   <tr class="listrow">

    <td>[% IF loop.first %][% LxERP.t8("Buchungsgruppe") %][% END %]</td>

    <td>[%- HTML.escape(buchungsgruppe) %]</td>

    [%- FOREACH month = SELF.liquidity.sorted.month %]

     <td align="right">[% LxERP.format_amount(SELF.liquidity.buchungsgruppe.$buchungsgruppe.$month, 2) %]</td>

    [%- END %]

   </tr>

  [%- END %]

 [%- END %]

 <tr class="listrow listtotal">

  <td>[% LxERP.t8("Total") %]</td>

  [% IF name_col %]<td></td>[% END %]

  [%- FOREACH month = SELF.liquidity.sorted.month %]

   <td align="right">

    [% IF SELF.liquidity.total.$month > 0 %]

     <a href="[% HTML.escape(SELF.link_to_old_orders(reqdate=month, months=params.months)) %]">

    [% END %]

    [% LxERP.format_amount(SELF.liquidity.total.$month, 2) %]

    [% IF SELF.liquidity.total.$month > 0 %]

     </a>

    [% END %]

   </td>

  [%- END %]

 </tr>

</table>

[% USE HTML %][% USE LxERP %][%- USE L -%]

<body>

 <h1>[% HTML.escape(title) %]</h1>

 [% PROCESS 'liquidity_projection/_filter.html' %]

 [%- IF SELF.liquidity %]

  <hr>

  [% PROCESS 'liquidity_projection/_result.html' %]

 [% END %]

</body>

</html>