t/gl/gl.t

   1 use strict;
   2 use Test::More tests => 8;
   3
   4 use lib 't';
   5 use Support::TestSetup;
   6 use Carp;
   7 use Test::Exception;
   8 use SL::DB::Chart;
   9 use SL::DB::TaxKey;
  10 use SL::DB::GLTransaction;
  11 use Data::Dumper;
  12 use SL::DBUtils qw(selectall_hashref_query);
  13
  14 Support::TestSetup::login();
  15
  16 clear_up();
  17
  18 my $cash           = SL::DB::Manager::Chart->find_by( description => 'Kasse'          );
  19 my $bank           = SL::DB::Manager::Chart->find_by( description => 'Bank'           );
  20 my $betriebsbedarf = SL::DB::Manager::Chart->find_by( description => 'Betriebsbedarf' );
  21
  22 my $tax_9 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19);
  23 my $tax_8 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07);
  24 my $tax_0 = SL::DB::Manager::Tax->find_by(taxkey => 0, rate => 0.00);
  25
  26 my $dbh = SL::DB->client->dbh;
  27
  28 # example with chaining of add_chart_booking
  29 my $gl_transaction = SL::DB::GLTransaction->new(
  30   taxincluded => 1,
  31   reference   => 'bank/cash',
  32   description => 'bank/cash',
  33   transdate   => DateTime->today_local,
  34 )->add_chart_booking(
  35   chart  => $cash,
  36   credit => 100,
  37   tax_id => $tax_0->id,
  38 )->add_chart_booking(
  39   chart  => $bank,
  40   debit  => 100,
  41   tax_id => $tax_0->id,
  42 )->post;
  43
  44 # example where bookings is prepared separately as an arrayref
  45 my $gl_transaction_2 = SL::DB::GLTransaction->new(
  46   reference   => 'betriebsbedarf several rows',
  47   description => 'betriebsbedarf',
  48   taxincluded => 1,
  49   transdate   => DateTime->today_local,
  50 );
  51
  52 my $bookings = [
  53                 {
  54                   chart  => $betriebsbedarf,
  55                   memo   => 'foo 1',
  56                   source => 'foo 1',
  57                   debit  => 119,
  58                   tax_id => $tax_9->id,
  59                 },
  60                 {
  61                   chart  => $betriebsbedarf,
  62                   memo   => 'foo 2',
  63                   source => 'foo 2',
  64                   debit  => 119,
  65                   tax_id => $tax_9->id,
  66                 },
  67                 {
  68                   chart  => $cash,
  69                   credit => 238,
  70                   memo   => 'foo 1+2',
  71                   source => 'foo 1+2',
  72                   tax_id => $tax_0->id,
  73                 },
  74                ];
  75 $gl_transaction_2->add_chart_booking(%{$_}) foreach @{ $bookings };
  76 $gl_transaction_2->post;
  77
  78
  79 # example where add_chart_booking is called via a foreach
  80 my $gl_transaction_3 = SL::DB::GLTransaction->new(
  81   reference   => 'betriebsbedarf tax included',
  82   description => 'bar',
  83   taxincluded => 1,
  84   transdate   => DateTime->today_local,
  85 );
  86 $gl_transaction_3->add_chart_booking(%{$_}) foreach (
  87     {
  88       chart  => $betriebsbedarf,
  89       debit  => 119,
  90       tax_id => $tax_9->id,
  91     },
  92     {
  93       chart  => $betriebsbedarf,
  94       debit  => 107,
  95       tax_id => $tax_8->id,
  96     },
  97     {
  98       chart  => $betriebsbedarf,
  99       debit  => 100,
 100       tax_id => $tax_0->id,
 101     },
 102     {
 103       chart  => $cash,
 104       credit => 326,
 105       tax_id => $tax_0->id,
 106     },
 107 );
 108 $gl_transaction_3->post;
 109
 110 my $gl_transaction_4 = SL::DB::GLTransaction->new(
 111   reference   => 'betriebsbedarf tax not included',
 112   description => 'bar',
 113   taxincluded => 0,
 114   transdate   => DateTime->today_local,
 115 );
 116 $gl_transaction_4->add_chart_booking(%{$_}) foreach (
 117     {
 118       chart  => $betriebsbedarf,
 119       debit  => 100,
 120       tax_id => $tax_9->id,
 121     },
 122     {
 123       chart  => $betriebsbedarf,
 124       debit  => 100,
 125       tax_id => $tax_8->id,
 126     },
 127     {
 128       chart  => $betriebsbedarf,
 129       debit  => 100,
 130       tax_id => $tax_0->id,
 131     },
 132     {
 133       chart  => $cash,
 134       credit => 326,
 135       tax_id => $tax_0->id,
 136     },
 137 );
 138 $gl_transaction_4->post;
 139
 140 is(SL::DB::Manager::GLTransaction->get_all_count(), 4, "gl transactions created ok");
 141
 142 is_deeply(&get_account_balances,
 143           [
 144             {
 145               'accno' => '1000',
 146               'sum' => '990.00000'
 147             },
 148             {
 149               'accno' => '1200',
 150               'sum' => '-100.00000'
 151             },
 152             {
 153               'accno' => '1571',
 154               'sum' => '-14.00000'
 155             },
 156             {
 157               'accno' => '1576',
 158               'sum' => '-76.00000'
 159             },
 160             {
 161               'accno' => '4980',
 162               'sum' => '-800.00000'
 163             }
 164           ],
 165           "chart balances ok"
 166          );
 167
 168
 169 note('testing subcent');
 170
 171 my $gl_transaction_5_taxinc = SL::DB::GLTransaction->new(
 172   taxincluded => 1,
 173   reference   => 'subcent tax included',
 174   description => 'subcent tax included',
 175   transdate   => DateTime->today_local,
 176 )->add_chart_booking(
 177   chart  => $betriebsbedarf,
 178   debit  => 0.02,
 179   tax_id => $tax_9->id,
 180 )->add_chart_booking(
 181   chart  => $cash,
 182   credit => 0.02,
 183   tax_id => $tax_0->id,
 184 )->post;
 185
 186 my $gl_transaction_5_taxnoinc = SL::DB::GLTransaction->new(
 187   taxincluded => 0,
 188   reference   => 'subcent tax not included',
 189   description => 'subcent tax not included',
 190   transdate   => DateTime->today_local,
 191 )->add_chart_booking(
 192   chart  => $betriebsbedarf,
 193   debit  => 0.02,
 194   tax_id => $tax_9->id,
 195 )->add_chart_booking(
 196   chart  => $cash,
 197   credit => 0.02,
 198   tax_id => $tax_0->id,
 199 )->post;
 200
 201 my $gl_transaction_6_taxinc = SL::DB::GLTransaction->new(
 202   taxincluded => 1,
 203   reference   => 'cent tax included',
 204   description => 'cent tax included',
 205   transdate   => DateTime->today_local,
 206 )->add_chart_booking(
 207   chart  => $betriebsbedarf,
 208   debit  => 0.05,
 209   tax_id => $tax_9->id,
 210 )->add_chart_booking(
 211   chart  => $cash,
 212   credit => 0.05,
 213   tax_id => $tax_0->id,
 214 )->post;
 215
 216 my $gl_transaction_6_taxnoinc = SL::DB::GLTransaction->new(
 217   taxincluded => 0,
 218   reference   => 'cent tax included',
 219   description => 'cent tax included',
 220   transdate   => DateTime->today_local,
 221 )->add_chart_booking(
 222   chart  => $betriebsbedarf,
 223   debit  => 0.04,
 224   tax_id => $tax_9->id,
 225 )->add_chart_booking(
 226   chart  => $cash,
 227   credit => 0.05,
 228   tax_id => $tax_0->id,
 229 )->post;
 230
 231 is(SL::DB::Manager::GLTransaction->get_all_count(), 8, "gl transactions created ok");
 232
 233
 234 is_deeply(&get_account_balances,
 235           [
 236             {
 237               'accno' => '1000',
 238               'sum' => '990.14000'
 239             },
 240             {
 241               'accno' => '1200',
 242               'sum' => '-100.00000'
 243             },
 244             {
 245               'accno' => '1571',
 246               'sum' => '-14.00000'
 247             },
 248             {
 249               'accno' => '1576',
 250               'sum' => '-76.02000'
 251             },
 252             {
 253               'accno' => '4980',
 254               'sum' => '-800.12000'
 255             }
 256           ],
 257           "chart balances ok"
 258          );
 259
 260 note "testing automatic tax 19%";
 261
 262 my $gl_transaction_7 = SL::DB::GLTransaction->new(
 263   reference   => 'betriebsbedarf tax not included',
 264   description => 'bar',
 265   taxincluded => 0,
 266   transdate   => DateTime->new(year => 2019, month => 12, day => 30),
 267 );
 268
 269 $gl_transaction_7->add_chart_booking(%{$_}) foreach (
 270     {
 271       chart  => $betriebsbedarf,
 272       debit  => 100,
 273     },
 274     {
 275       chart  => $betriebsbedarf,
 276       debit  => 100,
 277     },
 278     {
 279       chart  => $betriebsbedarf,
 280       debit  => 100,
 281       tax_id => $tax_0->id,
 282     },
 283     {
 284       chart  => $cash,
 285       credit => 338,
 286     },
 287 );
 288 $gl_transaction_7->post;
 289
 290 is(SL::DB::Manager::GLTransaction->get_all_count(), 9, "gl transactions created ok");
 291 is_deeply(&get_account_balances,
 292           [
 293             {
 294               'accno' => '1000',
 295               'sum' => '1328.14000'
 296             },
 297             {
 298               'accno' => '1200',
 299               'sum' => '-100.00000'
 300             },
 301             {
 302               'accno' => '1571',
 303               'sum' => '-14.00000'
 304             },
 305             {
 306               'accno' => '1576',
 307               'sum' => '-114.02000'
 308             },
 309             {
 310               'accno' => '4980',
 311               'sum' => '-1100.12000'
 312             }
 313           ],
 314           "chart balances ok"
 315          );
 316
 317 note "testing automatic tax 16%";
 318
 319 my $gl_transaction_8 = SL::DB::GLTransaction->new(
 320   reference   => 'betriebsbedarf tax not included',
 321   description => 'bar',
 322   taxincluded => 0,
 323   transdate   => DateTime->new(year => 2020, month => 12, day => 31),
 324 );
 325
 326 $gl_transaction_8->add_chart_booking(%{$_}) foreach (
 327     {
 328       chart  => $betriebsbedarf,
 329       debit  => 100,
 330     },
 331     {
 332       chart  => $betriebsbedarf,
 333       debit  => 100,
 334     },
 335     {
 336       chart  => $betriebsbedarf,
 337       debit  => 100,
 338       tax_id => $tax_0->id,
 339     },
 340     {
 341       chart  => $cash,
 342       credit => 332,
 343     },
 344 );
 345 $gl_transaction_8->post;
 346
 347 is(SL::DB::Manager::GLTransaction->get_all_count(), 10, "gl transactions created ok");
 348 is_deeply(&get_account_balances,
 349           [
 350             {
 351               'accno' => '1000',
 352               'sum' => '1660.14000'
 353             },
 354             {
 355               'accno' => '1200',
 356               'sum' => '-100.00000'
 357             },
 358             {
 359               'accno' => '1571',
 360               'sum' => '-14.00000'
 361             },
 362             {
 363               'accno' => '1575',
 364               'sum' => '-32.00000'
 365             },
 366             {
 367               'accno' => '1576',
 368               'sum' => '-114.02000'
 369             },
 370             {
 371               'accno' => '4980',
 372               'sum' => '-1400.12000'
 373             }
 374           ],
 375           "chart balances ok"
 376          );
 377
 378 done_testing;
 379 clear_up();
 380
 381 1;
 382
 383 sub clear_up {
 384   "SL::DB::Manager::${_}"->delete_all(all => 1) for qw(
 385                                                        AccTransaction
 386                                                        GLTransaction
 387                                                       );
 388 };
 389
 390 sub get_account_balances {
 391   my $query = <<SQL;
 392   select c.accno,
 393          sum(a.amount)
 394     from acc_trans a
 395          left join chart c on (c.id = a.chart_id)
 396 group by c.accno
 397 order by c.accno;
 398 SQL
 399
 400   my $result = selectall_hashref_query($::form, $dbh, $query);
 401   return $result;
 402 };