8cc0f6afd9cb5621fe5c803cab1532e86f3a7ab3
[kivitendo-erp.git] / 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 };