Massenerstellen von Rechnungen aus LS: js: Klasse "loading" setzen raus
[kivitendo-erp.git] / t / year_end / year_end.t
1 use strict;
2 use warnings;
3
4 use Test::More tests => 18;
5 use lib 't';
6 use utf8;
7
8 use Carp;
9 use Data::Dumper;
10 use Support::TestSetup;
11 use Test::Exception;
12 use SL::DBUtils qw(selectall_hashref_query);
13
14 use SL::DB::BankAccount;
15 use SL::DB::Chart;
16 use SL::DB::Invoice;
17 use SL::DB::PurchaseInvoice;
18
19 use SL::Dev::Record qw(create_ar_transaction create_ap_transaction create_gl_transaction);
20
21 use SL::Controller::YearEndTransactions;
22   
23 Support::TestSetup::login();
24
25 clear_up();
26
27 # comments:
28
29 # * in the default test client the tax accounts are configured as I/E rather than A/L
30 # * also the default test client has the accounting method "cash" rather than "accrual"
31 #   (Ist-versteuerung, rather than Soll-versteuerung)
32
33 # use 2019 instead of 2020 because of tax changes in Germany (19/16 and 7/5) because we check for account sums
34 my $year = 2019 if DateTime->today_local->year == 2020;
35 my $start_of_year = DateTime->new(year => $year, month => 01, day => 01);
36 my $booking_date  = DateTime->new(year => $year, month => 12, day => 22);
37
38 note('configuring accounts');
39 my $bank_account = SL::DB::BankAccount->new(
40   account_number  => '123',
41   bank_code       => '123',
42   iban            => '123',
43   bic             => '123',
44   bank            => '123',
45   chart_id        => SL::DB::Manager::Chart->find_by(description => 'Bank')->id,
46   name            => SL::DB::Manager::Chart->find_by(description => 'Bank')->description,
47 )->save;
48
49 my $profit_account = SL::DB::Manager::Chart->find_by(accno => '0890') //
50                      SL::DB::Chart->new(
51                        accno          => '0890',
52                        description    => 'Gewinnvortrag vor Verwendung',
53                        charttype      => 'A',
54                        category       => 'Q',
55                        link           => '',
56                        taxkey_id      => '0',
57                        datevautomatik => 'f',
58                      )->save;
59
60 my $loss_account = SL::DB::Manager::Chart->find_by(accno => '0868') //
61                    SL::DB::Chart->new(
62                      accno          => '0868',
63                      description    => 'Verlustvortrag vor Verwendung',
64                      charttype      => 'A',
65                      category       => 'Q',
66                      link           => '',
67                      taxkey_id      => '0',
68                      datevautomatik => 'f',
69                    )->save;
70
71 my $carry_over_chart = SL::DB::Manager::Chart->find_by(accno => 9000); 
72 my $income_chart     = SL::DB::Manager::Chart->find_by(accno => '8400'); # income 19%, taxkey 3
73 my $bank             = SL::DB::Manager::Chart->find_by(description => 'Bank');
74 my $cash             = SL::DB::Manager::Chart->find_by(description => 'Kasse');
75 my $privateinlagen   = SL::DB::Manager::Chart->find_by(description => 'Privateinlagen');
76 my $betriebsbedarf   = SL::DB::Manager::Chart->find_by(description => 'Betriebsbedarf'); 
77
78 my $dbh = SL::DB->client->dbh;
79 $dbh->do('UPDATE defaults SET carry_over_account_chart_id     = ' . $carry_over_chart->id);
80 $dbh->do('UPDATE defaults SET profit_carried_forward_chart_id = ' . $profit_account->id);
81 $dbh->do('UPDATE defaults SET loss_carried_forward_chart_id   = ' . $loss_account->id);
82
83
84 note('creating transactions');
85 my $ar_transaction = create_ar_transaction(
86   taxincluded => 0,
87   transdate   => $booking_date,
88   bookings    => [
89                    {
90                      chart  => $income_chart, # income 19%, taxkey 3
91                      amount => 140,
92                    }
93                  ],
94 );
95   
96 $ar_transaction->pay_invoice(
97                               chart_id     => $bank_account->chart_id,
98                               amount       => $ar_transaction->amount,
99                               transdate    => $booking_date,
100                               payment_type => 'without_skonto',
101                             );
102
103 my $ar_transaction2 = create_ar_transaction(
104   taxincluded => 1,
105   transdate   => $booking_date,
106   bookings    => [
107                    {
108                      chart  => $income_chart, # income 19%, taxkey 3
109                      amount => 166.60,
110                    }
111                  ],
112 );
113
114 my $ap_transaction = create_ap_transaction(
115   taxincluded => 0,
116   transdate   => $booking_date,
117   bookings    => [
118                    {
119                      chart  => SL::DB::Manager::Chart->find_by( accno => '3400' ), # Wareneingang 19%, taxkey 9
120                      amount => 100,
121                    }
122                  ],
123 );
124
125 gl_booking(40, $start_of_year, 'foo', 'bar', $bank, $privateinlagen, 1, 0);
126
127 is(SL::DB::Manager::AccTransaction->get_all_count(                                ), 13, 'acc_trans transactions created ok');
128 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]),  2, 'acc_trans ob_transactions created ok');
129 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]),  0, 'no cb_transactions created ok');
130
131 is_deeply( &get_account_balances, 
132            [
133              {
134                'accno'        => '1200',
135                'account_type' => 'asset_account',
136                'sum'          => '-206.60000'
137              },
138              {
139                'accno'        => '1400',
140                'account_type' => 'asset_account',
141                'sum'          => '-166.60000'
142              },
143              {
144                'accno'        => '1600',
145                'account_type' => 'asset_account',
146                'sum'          => '119.00000'
147              },
148              {
149                'accno'        => '1890',
150                'account_type' => 'asset_account',
151                'sum'          => '40.00000'
152              },
153              {
154                'accno'        => '1576',
155                'account_type' => 'profit_loss_account',
156                'sum'          => '-19.00000'
157              },
158              {
159                'accno'        => '1776',
160                'account_type' => 'profit_loss_account',
161                'sum'          => '53.20000'
162              },
163              {
164                'accno'        => '3400',
165                'account_type' => 'profit_loss_account',
166                'sum'          => '-100.00000'
167              },
168              {
169                'accno'        => '8400',
170                'account_type' => 'profit_loss_account',
171                'sum'          => '280.00000'
172              }
173            ],
174            'account balances before year_end bookings ok',
175 );
176
177 #  accno |    account_type     |    sum     
178 # -------+---------------------+------------
179 #  1200  | asset_account       | -206.60000
180 #  1400  | asset_account       | -166.60000
181 #  1600  | asset_account       |  119.00000
182 #  1890  | asset_account       |   40.00000
183 #  1576  | profit_loss_account |  -19.00000
184 #  1776  | profit_loss_account |   53.20000
185 #  3400  | profit_loss_account | -100.00000
186 #  8400  | profit_loss_account |  280.00000
187
188
189 note('running year-end transactions');
190 my $start_date = DateTime->new(year => $year, month => 1,  day => 1);  
191 my $cb_date    = DateTime->new(year => $year, month => 12, day => 31);
192 my $ob_date    = $cb_date->clone->add(days => 1);
193
194 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
195                                                          cb_date    => $cb_date,
196                                                        );
197
198 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok');
199 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok');
200 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]),  5, 'GL cb_transactions created ok');
201 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]),  4, 'GL ob_transactions created ok');
202
203 my $final_account_balances = [
204                                {
205                                  'accno' => '0890',
206                                  'amount' => undef,
207                                  'amount_with_cb' => '0.00000',
208                                  'cat' => 'Q',
209                                  'cb_amount' => '0.00000',
210                                  'ob_amount' => undef,
211                                  'ob_next_year' => '214.20000',
212                                  'type' => 'asset',
213                                  'year_end_amount' => undef
214                                },
215                                {
216                                  'accno' => '1200',
217                                  'amount' => '-166.60000',
218                                  'amount_with_cb' => '0.00000',
219                                  'cat' => 'A',
220                                  'cb_amount' => '-206.60000',
221                                  'ob_amount' => '-40.00000',
222                                  'ob_next_year' => '-206.60000',
223                                  'type' => 'asset',
224                                  'year_end_amount' => '-206.60000'
225                                },
226                                {
227                                  'accno' => '1400',
228                                  'amount' => '-166.60000',
229                                  'amount_with_cb' => '0.00000',
230                                  'cat' => 'A',
231                                  'cb_amount' => '-166.60000',
232                                  'ob_amount' => undef,
233                                  'ob_next_year' => '-166.60000',
234                                  'type' => 'asset',
235                                  'year_end_amount' => '-166.60000'
236                                },
237                                {
238                                  'accno' => '1600',
239                                  'amount' => '119.00000',
240                                  'amount_with_cb' => '0.00000',
241                                  'cat' => 'L',
242                                  'cb_amount' => '119.00000',
243                                  'ob_amount' => undef,
244                                  'ob_next_year' => '119.00000',
245                                  'type' => 'asset',
246                                  'year_end_amount' => '119.00000'
247                                },
248                                {
249                                  'accno' => '1890',
250                                  'amount' => undef,
251                                  'amount_with_cb' => '0.00000',
252                                  'cat' => 'Q',
253                                  'cb_amount' => '40.00000',
254                                  'ob_amount' => '40.00000',
255                                  'ob_next_year' => '40.00000',
256                                  'type' => 'asset',
257                                  'year_end_amount' => '40.00000'
258                                },
259                                {
260                                  'accno' => '9000',
261                                  'amount' => undef,
262                                  'amount_with_cb' => '0.00000',
263                                  'cat' => 'A',
264                                  'cb_amount' => '0.00000',
265                                  'ob_amount' => undef,
266                                  'ob_next_year' => '0.00000',
267                                  'type' => 'asset',
268                                  'year_end_amount' => undef
269                                },
270                                {
271                                  'accno' => '1576',
272                                  'amount' => '-19.00000',
273                                  'amount_with_cb' => '0.00000',
274                                  'cat' => 'E',
275                                  'cb_amount' => '-19.00000',
276                                  'ob_amount' => undef,
277                                  'ob_next_year' => undef,
278                                  'type' => 'pl',
279                                  'year_end_amount' => '-19.00000'
280                                },
281                                {
282                                  'accno' => '1776',
283                                  'amount' => '53.20000',
284                                  'amount_with_cb' => '0.00000',
285                                  'cat' => 'I',
286                                  'cb_amount' => '53.20000',
287                                  'ob_amount' => undef,
288                                  'ob_next_year' => undef,
289                                  'type' => 'pl',
290                                  'year_end_amount' => '53.20000'
291                                },
292                                {
293                                  'accno' => '3400',
294                                  'amount' => '-100.00000',
295                                  'amount_with_cb' => '0.00000',
296                                  'cat' => 'E',
297                                  'cb_amount' => '-100.00000',
298                                  'ob_amount' => undef,
299                                  'ob_next_year' => undef,
300                                  'type' => 'pl',
301                                  'year_end_amount' => '-100.00000'
302                                },
303                                {
304                                  'accno' => '8400',
305                                  'amount' => '280.00000',
306                                  'amount_with_cb' => '0.00000',
307                                  'cat' => 'I',
308                                  'cb_amount' => '280.00000',
309                                  'ob_amount' => undef,
310                                  'ob_next_year' => undef,
311                                  'type' => 'pl',
312                                  'year_end_amount' => '280.00000'
313                                }
314                              ];
315
316 # running _year_end_bookings several times shouldn't change the anything, the
317 # second and third run should be no-ops, at least while no further bookings where
318 # made
319
320 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
321                                                          cb_date    => $cb_date,
322                                                        );
323
324 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 14, 'acc_trans cb_transactions created ok');
325 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 10, 'acc_trans ob_transactions created ok');
326 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]),  5, 'GL cb_transactions created ok');
327 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]),  4, 'GL ob_transactions created ok');
328
329
330 # all asset accounts should be the same, except 0890, which should be the sum of p/l-accounts
331 # all p/l account should be 0
332
333 #  accno |    account_type     |    sum     
334 # -------+---------------------+------------
335 #  0890  | asset_account       |  214.20000
336 #  1200  | asset_account       | -206.60000
337 #  1400  | asset_account       | -166.60000
338 #  1600  | asset_account       |  119.00000
339 #  1890  | asset_account       |   40.00000
340 #  9000  | asset_account       |    0.00000
341 #  1576  | profit_loss_account |    0.00000
342 #  1776  | profit_loss_account |    0.00000
343 #  3400  | profit_loss_account |    0.00000
344 #  8400  | profit_loss_account |    0.00000
345 # (10 rows)
346
347 is_deeply( &get_final_balances, 
348            $final_account_balances,
349            'balances after second year_end ok (nothing changed)');
350
351
352 # select c.accno,
353 #        c.description,
354 #        c.category as cat,
355 #        sum(a.amount     ) filter (where ob_transaction is true                              and a.transdate  < '2020-01-01') as ob_amount,
356 #        sum(a.amount     ) filter (where cb_transaction is false and ob_transaction is false and a.transdate  < '2020-01-01') as amount,
357 #        sum(a.amount     ) filter (where cb_transaction is false                             and a.transdate  < '2020-01-01') as year_end_amount,
358 #        sum(a.amount     ) filter (where                                                         a.transdate  < '2020-01-01') as amount_with_cb,
359 #        sum(a.amount * -1) filter (where cb_transaction is true                              and a.transdate  < '2020-01-01') as cb_amount,
360 #        sum(a.amount     ) filter (where ob_transaction is true                              and a.transdate >= '2020-01-01') as ob_next_year,
361 #        case when c.category = ANY( '{I,E}'     ) then 'pl'
362 #             when c.category = ANY( '{A,C,L,Q}' ) then 'asset'
363 #                                                  else null
364 #             end                                                                         as type
365 #   from acc_trans a
366 #        inner join chart c on (c.id = a.chart_id)
367 #  where     a.transdate >= '2019-01-01'
368 #        and a.transdate <= '2020-01-01'
369 #  group by c.id, c.accno, c.category
370 #  order by type, c.accno;
371 #  accno |             description             | cat | ob_amount |   amount   | year_end_amount | amount_with_cb | cb_amount  | ob_next_year | type  
372 # -------+-------------------------------------+-----+-----------+------------+-----------------+----------------+------------+--------------+-------
373 #  0890  | Gewinnvortrag vor Verwendung        | Q   |           |            |                 |        0.00000 |    0.00000 |    214.20000 | asset
374 #  1200  | Bank                                | A   | -40.00000 | -166.60000 |      -206.60000 |        0.00000 | -206.60000 |   -206.60000 | asset
375 #  1400  | Ford. a.Lieferungen und Leistungen  | A   |           | -166.60000 |      -166.60000 |        0.00000 | -166.60000 |   -166.60000 | asset
376 #  1600  | Verbindlichkeiten aus Lief.u.Leist. | L   |           |  119.00000 |       119.00000 |        0.00000 |  119.00000 |    119.00000 | asset
377 #  1890  | Privateinlagen                      | Q   |  40.00000 |            |        40.00000 |        0.00000 |   40.00000 |     40.00000 | asset
378 #  9000  | Saldenvorträge,Sachkonten           | A   |           |            |                 |        0.00000 |    0.00000 |      0.00000 | asset
379 #  1576  | Abziehbare Vorsteuer 19 %           | E   |           |  -19.00000 |       -19.00000 |        0.00000 |  -19.00000 |              | pl
380 #  1776  | Umsatzsteuer 19 %                   | I   |           |   53.20000 |        53.20000 |        0.00000 |   53.20000 |              | pl
381 #  3400  | Wareneingang 16%/19% Vorsteuer      | E   |           | -100.00000 |      -100.00000 |        0.00000 | -100.00000 |              | pl
382 #  8400  | Erlöse 16%/19% USt.                 | I   |           |  280.00000 |       280.00000 |        0.00000 |  280.00000 |              | pl
383 # (10 rows) 
384
385 # ob_amount + amount = year_end_amount
386 # amount_with_cb should be 0 after year-end transactions
387 # year_end_amount and cb_amount should be the same (will be true with amount_with_cb = 0)
388 # cb_amount should match ob_next_year for asset accounts, except for profit-carried-forward
389 # ob_next_year should be empty for profit-loss-accounts
390
391 # Oops, we forgot some bookings, lets quickly add them and run
392 #_year_end_bookings again.
393
394 # Just these new bookings by themselves will lead to a loss, so the loss account
395 # will be booked rather than the profit account.
396 # It would probably be better to check the total profit/loss so far, and
397 # adjust that profit-loss-carry-over # chart, rather than creating a new entry
398 # for the loss.
399
400 gl_booking(10, $booking_date, 'foo', 'bar', $cash, $bank, 0, 0);
401 gl_booking(5,  $booking_date, 'foo', 'bar', $betriebsbedarf, $cash, 0, 0);
402
403 SL::Controller::YearEndTransactions::_year_end_bookings( start_date => $start_date,
404                                                          cb_date    => $cb_date,
405                                                        );
406
407 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ cb_transaction => 1 ]), 23, 'acc_trans cb_transactions created ok');
408 is(SL::DB::Manager::AccTransaction->get_all_count(where => [ ob_transaction => 1 ]), 16, 'acc_trans ob_transactions created ok');
409 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ cb_transaction => 1 ]),  9, 'GL cb_transactions created ok');
410 is(SL::DB::Manager::GLTransaction->get_all_count( where => [ ob_transaction => 1 ]),  7, 'GL ob_transactions created ok');
411
412 is_deeply( &get_final_balances, 
413            [
414              {
415                'accno' => '0868',
416                'amount' => undef,
417                'amount_with_cb' => '0.00000',
418                'cat' => 'Q',
419                'cb_amount' => '0.00000',
420                'ob_amount' => undef,
421                'ob_next_year' => '-5.00000',
422                'type' => 'asset',
423                'year_end_amount' => undef
424              },
425              {
426                'accno' => '0890',
427                'amount' => undef,
428                'amount_with_cb' => '0.00000',
429                'cat' => 'Q',
430                'cb_amount' => '0.00000',
431                'ob_amount' => undef,
432                'ob_next_year' => '214.20000',
433                'type' => 'asset',
434                'year_end_amount' => undef
435              },
436              {
437                'accno' => '1000',
438                'amount' => '-5.00000',
439                'amount_with_cb' => '0.00000',
440                'cat' => 'A',
441                'cb_amount' => '-5.00000',
442                'ob_amount' => undef,
443                'ob_next_year' => '-5.00000',
444                'type' => 'asset',
445                'year_end_amount' => '-5.00000'
446              },
447              {
448                'accno' => '1200',
449                'amount' => '-156.60000',
450                'amount_with_cb' => '0.00000',
451                'cat' => 'A',
452                'cb_amount' => '-196.60000',
453                'ob_amount' => '-40.00000',
454                'ob_next_year' => '-196.60000',
455                'type' => 'asset',
456                'year_end_amount' => '-196.60000'
457              },
458              {
459                'accno' => '1400',
460                'amount' => '-166.60000',
461                'amount_with_cb' => '0.00000',
462                'cat' => 'A',
463                'cb_amount' => '-166.60000',
464                'ob_amount' => undef,
465                'ob_next_year' => '-166.60000',
466                'type' => 'asset',
467                'year_end_amount' => '-166.60000'
468              },
469              {
470                'accno' => '1600',
471                'amount' => '119.00000',
472                'amount_with_cb' => '0.00000',
473                'cat' => 'L',
474                'cb_amount' => '119.00000',
475                'ob_amount' => undef,
476                'ob_next_year' => '119.00000',
477                'type' => 'asset',
478                'year_end_amount' => '119.00000'
479              },
480              {
481                'accno' => '1890',
482                'amount' => undef,
483                'amount_with_cb' => '0.00000',
484                'cat' => 'Q',
485                'cb_amount' => '40.00000',
486                'ob_amount' => '40.00000',
487                'ob_next_year' => '40.00000',
488                'type' => 'asset',
489                'year_end_amount' => '40.00000'
490              },
491              {
492                'accno' => '9000',
493                'amount' => undef,
494                'amount_with_cb' => '0.00000',
495                'cat' => 'A',
496                'cb_amount' => '0.00000',
497                'ob_amount' => undef,
498                'ob_next_year' => '0.00000',
499                'type' => 'asset',
500                'year_end_amount' => undef
501              },
502              {
503                'accno' => '1576',
504                'amount' => '-19.80000',
505                'amount_with_cb' => '0.00000',
506                'cat' => 'E',
507                'cb_amount' => '-19.80000',
508                'ob_amount' => undef,
509                'ob_next_year' => undef,
510                'type' => 'pl',
511                'year_end_amount' => '-19.80000'
512              },
513              {
514                'accno' => '1776',
515                'amount' => '53.20000',
516                'amount_with_cb' => '0.00000',
517                'cat' => 'I',
518                'cb_amount' => '53.20000',
519                'ob_amount' => undef,
520                'ob_next_year' => undef,
521                'type' => 'pl',
522                'year_end_amount' => '53.20000'
523              },
524              {
525                'accno' => '3400',
526                'amount' => '-100.00000',
527                'amount_with_cb' => '0.00000',
528                'cat' => 'E',
529                'cb_amount' => '-100.00000',
530                'ob_amount' => undef,
531                'ob_next_year' => undef,
532                'type' => 'pl',
533                'year_end_amount' => '-100.00000'
534              },
535              {
536                'accno' => '4980',
537                'amount' => '-4.20000',
538                'amount_with_cb' => '0.00000',
539                'cat' => 'E',
540                'cb_amount' => '-4.20000',
541                'ob_amount' => undef,
542                'ob_next_year' => undef,
543                'type' => 'pl',
544                'year_end_amount' => '-4.20000'
545              },
546              {
547                'accno' => '8400',
548                'amount' => '280.00000',
549                'amount_with_cb' => '0.00000',
550                'cat' => 'I',
551                'cb_amount' => '280.00000',
552                'ob_amount' => undef,
553                'ob_next_year' => undef,
554                'type' => 'pl',
555                'year_end_amount' => '280.00000'
556              },
557            ],
558            'balances after third year_end ok');
559
560 clear_up();
561 done_testing;
562
563 1;
564
565 sub clear_up {
566   foreach (qw(BankAccount
567               GLTransaction
568               AccTransaction
569               InvoiceItem
570               Invoice
571               PurchaseInvoice
572               Part
573               Customer
574              )
575            ) {
576     "SL::DB::Manager::${_}"->delete_all(all => 1);
577   }
578 };
579  
580 sub get_account_balances {
581   my $query = <<SQL;
582   select c.accno,
583          case when c.category = ANY( '{I,E}'   )   then 'profit_loss_account'
584               when c.category = ANY( '{A,C,L,Q}' ) then 'asset_account'
585                                                    else null
586               end as account_type,
587          sum(a.amount)
588     from acc_trans a
589          left join chart c on (c.id = a.chart_id)
590 group by c.accno, account_type
591 order by account_type, c.accno;
592 SQL
593
594   my $result = selectall_hashref_query($::form, $dbh, $query);
595   return $result;
596 };
597
598 sub get_final_balances {
599   my $query = <<SQL;
600  select c.accno,
601         c.category as cat,
602         sum(a.amount     ) filter (where ob_transaction is true                              and a.transdate  < ?) as ob_amount,
603         sum(a.amount     ) filter (where cb_transaction is false and ob_transaction is false and a.transdate  < ?) as amount,
604         sum(a.amount     ) filter (where cb_transaction is false                             and a.transdate  < ?) as year_end_amount,
605         sum(a.amount     ) filter (where                                                         a.transdate  < ?) as amount_with_cb,
606         sum(a.amount * -1) filter (where cb_transaction is true                              and a.transdate  < ?) as cb_amount,
607         sum(a.amount     ) filter (where ob_transaction is true                              and a.transdate  = ?) as ob_next_year,
608         case when c.category = ANY( '{I,E}'     ) then 'pl'
609              when c.category = ANY( '{A,C,L,Q}' ) then 'asset'
610                                                   else null
611              end as type
612    from acc_trans a
613         inner join chart c on (c.id = a.chart_id)
614   where     a.transdate >= ?
615         and a.transdate <= ?
616   group by c.id, c.accno, c.category
617   order by type, c.accno
618 SQL
619
620   my $result = selectall_hashref_query($::form, $dbh, $query, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $ob_date, $start_date, $ob_date);
621   return $result;
622 }
623
624 sub gl_booking {
625   # wrapper around SL::Dev::Record::create_gl_transaction for quickly creating transactions
626   my ($amount, $date, $reference, $description, $gegenkonto, $konto, $ob, $cb) = @_;
627
628   # my $transdate = $::locale->parse_date_to_object($date);
629
630   return create_gl_transaction(
631     ob_transaction => $ob,
632     cb_transaction => $cb,
633     transdate      => $date,
634     reference      => $reference,
635     description    => $description,
636     bookings       => [
637                         {
638                           chart  => $konto,
639                           credit => $amount,
640                         },
641                         {
642                           chart => $gegenkonto,
643                           debit => $amount,
644                         },
645                       ],
646   );
647 };