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