t/gl/gl.t

   1 use strict;
   2 use Test::More tests => 4;
   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 done_testing;
 261 clear_up();
 262
 263 1;
 264
 265 sub clear_up {
 266   "SL::DB::Manager::${_}"->delete_all(all => 1) for qw(
 267                                                        AccTransaction
 268                                                        GLTransaction
 269                                                       );
 270 };
 271
 272 sub get_account_balances {
 273   my $query = <<SQL;
 274   select c.accno,
 275          sum(a.amount)
 276     from acc_trans a
 277          left join chart c on (c.id = a.chart_id)
 278 group by c.accno
 279 order by c.accno;
 280 SQL
 281
 282   my $result = selectall_hashref_query($::form, $dbh, $query);
 283   return $result;
 284 };