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