Doku: Zuviele TeX-Pakete angegeben
[kivitendo-erp.git] / SL / LiquidityProjection.pm
1 package SL::LiquidityProjection;
2
3 use strict;
4
5 use List::MoreUtils qw(uniq);
6
7 use SL::DBUtils;
8 use SL::DB::PeriodicInvoicesConfig;
9
10 sub new {
11   my $package       = shift;
12   my $self          = bless {}, $package;
13
14   my %params        = @_;
15
16   $self->{params}   = \%params;
17
18   my @now           = localtime;
19   my $now_year      = $now[5] + 1900;
20   my $now_month     = $now[4] + 1;
21
22   $self->{min_date} = _the_date($now_year, $now_month);
23   $self->{max_date} = _the_date($now_year, $now_month + $params{months} - 1);
24
25   $self;
26 }
27
28 # Algorithmus:
29 #
30 # Für den aktuellen Monat und alle x Folgemonate soll der geplante
31 # Liquiditätszufluss aufgeschlüsselt werden. Der Zufluss berechnet
32 # sich dabei aus:
33 #
34 # 1. Summe aller offenen Auträge
35 #
36 # 2. abzüglich aller zu diesen Aufträgen erstellten Rechnungen
37 # (Teillieferungen/Teilrechnungen)
38 #
39 # 3. zuzüglich alle aktiven Wartungsverträge, die in dem jeweiligen
40 # Monat ihre Saldierungsperiode haben, außer Wartungsverträgen, die
41 # für den jeweiligen Monat bereits abgerechnet wurden.
42 #
43 # Diese Werte sollen zusätzlich optional nach Verkäufer(in) und nach
44 # Buchungsgruppe aufgeschlüsselt werden.
45 #
46 # Diese Lösung geht deshalb immer über die Positionen der Belege
47 # (wegen der Buchungsgruppe) und berechnet die Summen daraus manuell.
48 #
49 # Alle Aufträge, deren Lieferdatum leer ist, oder deren Lieferdatum
50 # vor dem aktuellen Monat liegt, werden in einer Kategorie 'alt'
51 # zusammengefasst.
52 #
53 # Alle Aufträge, deren Lieferdatum nach dem zu betrachtenden Zeitraum
54 # (aktueller Monat + x Monate) liegen, werden in einer Kategorie
55 # 'Zukunft' zusammengefasst.
56 #
57 # Insgesamt läuft es wie folgt ab:
58 #
59 # 1. Es wird das Datum aller periodisch erzeugten Rechnungen innerhalb
60 # des Betrachtungszeitraumes herausgesucht.
61 #
62 # 2. Alle aktiven Wartungsvertragskonfigurationen werden
63 # ausgelesen. Die Saldierungsmonate werden solange aufaddiert, wie der
64 # dabei herauskommende Monat nicht nach dem zu betrachtenden Zeitraum
65 # liegt.
66 #
67 # 3. Für jedes Saldierungsintervall, das innerhalb des
68 # Betrachtungszeitraumes liegt, und für das es für den Monat noch
69 # keine Rechnung gibt (siehe 1.), wird diese Konfiguration für den
70 # Monat vorgemerkt.
71 #
72 # 4. Es werden für alle offenen Kundenaufträge die Positionen
73 # ausgelesen und mit Verkäufer(in), Buchungsgruppe verknüpft. Aus
74 # Menge, Einzelpreis und Zeilenrabatt wird die Zeilensumme berechnet.
75 #
76 # 5. Mit den Informationen aus 3. und 4. werden Datenstrukturen
77 # initialisiert, die für die Gesamtsummen, für alle Verkäufer(innen),
78 # für alle Buchungsgruppen, für alle Monate Werte enthalten.
79 #
80 # 6. Es wird über alle Einträge aus 4. iteriert. Die Zeilensummen
81 # werden in den entsprechenden Datenstrukturen aus 5. addiert.
82 #
83 # 7. Es wird über alle Einträge aus 3. iteriert. Die Zeilensummen
84 # werden in den entsprechenden Datenstrukturen aus 5. addiert.
85 #
86 # 8. Es werden alle Rechnungspositionen ausgelesen, bei denen die
87 # Auftragsnummer einer der aus 5. ermittelten Aufträge entspricht.
88 #
89 # 9. Es wird über alle Einträge aus 8. iteriert. Die Zeilensummen
90 # werden von den entsprechenden Datenstrukturen aus 5. abgezogen. Als
91 # Datum wird dabei das Datum des zu der Rechnung gehörenden Auftrages
92 # genommen. Als Buchungsgruppe wird die Buchungsgruppe der Zeile
93 # genommen. Falls es passieren sollte, dass diese Buchungsgruppe in
94 # den Aufträgen nie vorgekommen ist (sprich Rechnung enthält
95 # Positionen, die im Auftrag nicht enthalten sind, und die komplett
96 # andere Buchungsgruppen verwenden), so wird schlicht die allererste
97 # in 4. gefundene Buchungsgruppe damit belastet.
98
99 sub create {
100   my ($self)   = @_;
101   my %params   = %{ $self->{params} };
102
103   my $dbh      = $params{dbh} || $::form->get_standard_dbh;
104   my ($sth, $ref, $query);
105
106   $params{months} ||= 6;
107
108   # 1. Auslesen aller erzeugten periodischen Rechnungen im
109   # Betrachtungszeitraum
110   my $q_min_date = $dbh->quote($self->{min_date} . '-01');
111   $query         = <<SQL;
112     SELECT pi.config_id, to_char(pi.period_start_date, 'YYYY-MM') AS period_start_date
113     FROM periodic_invoices pi
114     LEFT JOIN periodic_invoices_configs pcfg ON (pi.config_id = pcfg.id)
115     WHERE pcfg.active
116       AND NOT pcfg.periodicity = 'o'
117       AND (pi.period_start_date >= to_date($q_min_date, 'YYYY-MM-DD'))
118 SQL
119
120   my %periodic_invoices;
121   $sth = prepare_execute_query($::form, $dbh, $query);
122   while ($ref = $sth->fetchrow_hashref) {
123     $periodic_invoices{ $ref->{config_id} }                                ||= { };
124     $periodic_invoices{ $ref->{config_id} }->{ $ref->{period_start_date} }   = 1;
125   }
126   $sth->finish;
127
128   # 2. Auslesen aktiver Wartungsvertragskonfigurationen
129   $query = <<SQL;
130     SELECT (oi.qty * (1 - oi.discount) * oi.sellprice) AS linetotal,
131       bg.description AS buchungsgruppe,
132       CASE WHEN COALESCE(e.name, '') = '' THEN e.login ELSE e.name END AS salesman,
133       pcfg.periodicity, pcfg.order_value_periodicity, pcfg.id AS config_id,
134       EXTRACT(year FROM pcfg.start_date) AS start_year, EXTRACT(month FROM pcfg.start_date) AS start_month
135     FROM orderitems oi
136     LEFT JOIN oe                             ON (oi.trans_id                              = oe.id)
137     LEFT JOIN periodic_invoices_configs pcfg ON (oi.trans_id                              = pcfg.oe_id)
138     LEFT JOIN parts p                        ON (oi.parts_id                              = p.id)
139     LEFT JOIN buchungsgruppen bg             ON (p.buchungsgruppen_id                     = bg.id)
140     LEFT JOIN employee e                     ON (COALESCE(oe.salesman_id, oe.employee_id) = e.id)
141     WHERE pcfg.active
142       AND NOT pcfg.periodicity = 'o'
143 SQL
144
145   # 3. Iterieren über Saldierungsintervalle, vormerken
146   my @scentries;
147   $sth = prepare_execute_query($::form, $dbh, $query);
148   while ($ref = $sth->fetchrow_hashref) {
149     my ($year, $month) = ($ref->{start_year}, $ref->{start_month});
150     my $date;
151
152     while (($date = _the_date($year, $month)) le $self->{max_date}) {
153       my $billing_len = $SL::DB::PeriodicInvoicesConfig::PERIOD_LENGTHS{ $ref->{periodicity} } || 1;
154
155       if (($date ge $self->{min_date}) && (!$periodic_invoices{ $ref->{config_id} } || !$periodic_invoices{ $ref->{config_id} }->{$date})) {
156         my $order_value_periodicity = $ref->{order_value_periodicity} eq 'p' ? $ref->{periodicity} : $ref->{order_value_periodicity};
157         my $order_value_len         = $SL::DB::PeriodicInvoicesConfig::ORDER_VALUE_PERIOD_LENGTHS{$order_value_periodicity} || 1;
158
159         push @scentries, { buchungsgruppe => $ref->{buchungsgruppe},
160                            salesman       => $ref->{salesman},
161                            linetotal      => $ref->{linetotal} * $billing_len / $order_value_len,
162                            date           => $date,
163                          };
164       }
165
166       ($year, $month) = _fix_date($year, $month + $billing_len);
167     }
168   }
169   $sth->finish;
170
171   # 4. Auslesen offener Aufträge
172   $query = <<SQL;
173     SELECT (oi.qty * (1 - oi.discount) * oi.sellprice) AS linetotal,
174       bg.description AS buchungsgruppe,
175       CASE WHEN COALESCE(e.name, '') = '' THEN e.login ELSE e.name END AS salesman,
176       oe.ordnumber, EXTRACT(month FROM oe.reqdate) AS month, EXTRACT(year  FROM oe.reqdate) AS year
177     FROM orderitems oi
178     LEFT JOIN oe                 ON (oi.trans_id                              = oe.id)
179     LEFT JOIN parts p            ON (oi.parts_id                              = p.id)
180     LEFT JOIN buchungsgruppen bg ON (p.buchungsgruppen_id                     = bg.id)
181     LEFT JOIN employee e         ON (COALESCE(oe.salesman_id, oe.employee_id) = e.id)
182     WHERE (oe.customer_id IS NOT NULL)
183       AND NOT COALESCE(oe.quotation, FALSE)
184       AND NOT COALESCE(oe.closed,    FALSE)
185       AND (oe.id NOT IN (SELECT oe_id FROM periodic_invoices_configs WHERE periodicity <> 'o'))
186 SQL
187
188   # 5. Initialisierung der Datenstrukturen zum Speichern der
189   # Ergebnisse
190   my @entries               = selectall_hashref_query($::form, $dbh, $query);
191   my @salesmen              = uniq map { $_->{salesman}       } (@entries, @scentries);
192   my @buchungsgruppen       = uniq map { $_->{buchungsgruppe} } (@entries, @scentries);
193   my @now                   = localtime;
194   my @dates                 = map { $self->_date_for($now[5] + 1900, $now[4] + $_) } (0..$self->{params}->{months} + 1);
195   my %dates_by_ordnumber    = map { $_->{ordnumber} => $self->_date_for($_) } @entries;
196   my %salesman_by_ordnumber = map { $_->{ordnumber} => $_->{salesman}       } @entries;
197   my %date_sorter           = ( old => '0000-00', future => '9999-99' );
198
199   my $projection    = { total          =>               { map { $_ => 0 } @dates },
200                         order          =>               { map { $_ => 0 } @dates },
201                         partial        =>               { map { $_ => 0 } @dates },
202                         support        =>               { map { $_ => 0 } @dates },
203                         salesman       => { map { $_ => { map { $_ => 0 } @dates } } @salesmen        },
204                         buchungsgruppe => { map { $_ => { map { $_ => 0 } @dates } } @buchungsgruppen },
205                         sorted         => { month          => [ sort { ($date_sorter{$a} || $a) cmp ($date_sorter{$b} || $b) } @dates           ],
206                                             salesman       => [ sort { $a                       cmp $b                       } @salesmen        ],
207                                             buchungsgruppe => [ sort { $a                       cmp $b                       } @buchungsgruppen ],
208                                             type           => [ qw(order partial support)                                                       ],
209                                           },
210                       };
211
212   # 6. Aufsummieren der Auftragspositionen
213   foreach $ref (@entries) {
214     my $date = $self->_date_for($ref);
215
216     $projection->{total}->{$date}                                      += $ref->{linetotal};
217     $projection->{order}->{$date}                                      += $ref->{linetotal};
218     $projection->{salesman}->{ $ref->{salesman} }->{$date}             += $ref->{linetotal};
219     $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} }->{$date} += $ref->{linetotal};
220   }
221
222   # 7. Aufsummieren der Wartungsvertragspositionen
223   foreach $ref (@scentries) {
224     my $date = $ref->{date};
225
226     $projection->{total}->{$date}                                      += $ref->{linetotal};
227     $projection->{support}->{$date}                                    += $ref->{linetotal};
228     $projection->{salesman}->{ $ref->{salesman} }->{$date}             += $ref->{linetotal};
229     $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} }->{$date} += $ref->{linetotal};
230   }
231
232   if (%dates_by_ordnumber) {
233     # 8. Auslesen von Positionen von Teilrechnungen zu Aufträgen
234     my $ordnumbers = join ', ', map { $dbh->quote($_) } keys %dates_by_ordnumber;
235     $query         = <<SQL;
236       SELECT (i.qty * (1 - i.discount) * i.sellprice) AS linetotal,
237         bg.description AS buchungsgruppe,
238         ar.ordnumber
239       FROM invoice i
240       LEFT JOIN ar                 ON (i.trans_id           = ar.id)
241       LEFT JOIN parts p            ON (i.parts_id           = p.id)
242       LEFT JOIN buchungsgruppen bg ON (p.buchungsgruppen_id = bg.id)
243       WHERE (ar.ordnumber IN ($ordnumbers))
244 SQL
245
246     @entries = selectall_hashref_query($::form, $dbh, $query);
247
248     # 9. Abziehen der abgerechneten Positionen
249     foreach $ref (@entries) {
250       my $date           = $dates_by_ordnumber{    $ref->{ordnumber} } || die;
251       my $salesman       = $salesman_by_ordnumber{ $ref->{ordnumber} } || die;
252       my $buchungsgruppe = $projection->{buchungsgruppe}->{ $ref->{buchungsgruppe} } ? $ref->{buchungsgruppe} : $buchungsgruppen[0];
253
254       $projection->{partial}->{$date}                           -= $ref->{linetotal};
255       $projection->{total}->{$date}                             -= $ref->{linetotal};
256       $projection->{salesman}->{$salesman}->{$date}             -= $ref->{linetotal};
257       $projection->{buchungsgruppe}->{$buchungsgruppe}->{$date} -= $ref->{linetotal};
258     }
259   }
260
261   return $projection;
262 }
263
264 # Skaliert '$year' und '$month' so, dass 1 <= Monat <= 12 gilt. Zum
265 # Einfachen Addieren gedacht, z.B.
266 #
267 # my ($new_year, $new_month) = _fix_date($old_year, $old_month + 6);
268
269 sub _fix_date {
270   my $year   = shift;
271   my $month  = shift;
272
273   $year     += int(($month - 1) / 12);
274   $month     = (($month - 1) % 12 ) + 1;
275
276   ($year, $month);
277 }
278
279 # Formartiert Jahr & Monat wie benötigt.
280
281 sub _the_date {
282   sprintf '%04d-%02d', _fix_date(@_);
283 }
284
285 # Mappt Datum auf Kategorie. Ist das Datum leer, oder liegt es vor dem
286 # Betrachtungszeitraum, so ist die Kategorie 'old'. Liegt das Datum
287 # nach dem Betrachtungszeitraum, so ist die Kategorie
288 # 'future'. Andernfalls ist sie das formartierte Datum selber.
289
290 sub _date_for {
291   my $self = shift;
292   my $ref  = ref $_[0] eq 'HASH' ? shift : { year => $_[0], month => $_[1] };
293
294   return 'old' if !$ref->{year} || !$ref->{month};
295
296   my $date = _the_date($ref->{year}, $ref->{month});
297
298     $date lt $self->{min_date} ? 'old'
299   : $date gt $self->{max_date} ? 'future'
300   :                              $date;
301 }
302
303 1;