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