Loeblich, Doku wollte ich schon immer haben.
[kivitendo-erp.git] / SL / CA.pm
1 #=====================================================================
2 # LX-Office ERP
3 # Copyright (C) 2004
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
6 #
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 2001
10 #
11 #  Author: Dieter Simader
12 #   Email: dsimader@sql-ledger.org
13 #     Web: http://www.sql-ledger.org
14 #
15 #  Contributors:
16 #
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
21 #
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
30 # chart of accounts
31 #
32 # CHANGE LOG:
33 #   DS. 2000-07-04  Created
34 #
35 #======================================================================
36
37 package CA;
38 use Data::Dumper;
39
40 sub all_accounts {
41   $main::lxdebug->enter_sub();
42
43   my ($self, $myconfig, $form) = @_;
44
45   my %amount;
46
47   # connect to database
48   my $dbh = $form->dbconnect($myconfig);
49
50   my $query = qq|SELECT c.accno,
51                  SUM(a.amount) AS amount
52                  FROM chart c, acc_trans a
53                  WHERE c.id = a.chart_id
54                  GROUP BY c.accno|;
55   my $sth = $dbh->prepare($query);
56   $sth->execute || $form->dberror($query);
57
58   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
59     $amount{ $ref->{accno} } = $ref->{amount};
60   }
61   $sth->finish;
62
63   $query = qq|SELECT accno, description
64               FROM gifi|;
65   $sth = $dbh->prepare($query);
66   $sth->execute || $form->dberror($query);
67
68   my %gifi;
69   while (my ($accno, $description) = $sth->fetchrow_array) {
70     $gifi{$accno} = $description;
71   }
72   $sth->finish;
73
74   $query = qq{
75     SELECT 
76       c.accno,
77       c.id,
78       c.description,
79       c.charttype,
80       c.category,
81       c.link,
82       c.pos_bwa,
83       c.pos_bilanz,
84       c.pos_eur,
85       c.valid_from,
86       c.datevautomatik,
87       comma(tk.startdate) AS startdate,
88       comma(tk.taxkey_id) AS taxkey,
89       comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
90       comma(tx.taxnumber) AS taxaccount,
91       comma(tk.pos_ustva) AS tk_ustva,
92       ( SELECT accno
93       FROM chart c2
94       WHERE c2.id = c.id
95       ) AS new_account
96     FROM chart c
97     LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
98     LEFT JOIN tax tx ON (tk.tax_id = tx.id)
99     GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,
100       c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,      
101       c.datevautomatik
102     ORDER BY c.accno
103   };
104
105   $sth = $dbh->prepare($query);
106   $sth->execute || $form->dberror($query);
107
108   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
109     $ca->{amount}           = $amount{ $ca->{accno} };
110     $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
111     if ($ca->{amount} < 0) {
112       $ca->{debit} = $ca->{amount} * -1;
113     } else {
114       $ca->{credit} = $ca->{amount};
115     }
116     push @{ $form->{CA} }, $ca;
117   }
118
119   $sth->finish;
120   $dbh->disconnect;
121
122   $main::lxdebug->leave_sub();
123 }
124
125 sub all_transactions {
126   $main::lxdebug->enter_sub();
127
128   my ($self, $myconfig, $form) = @_;
129
130   # connect to database
131   my $dbh = $form->dbconnect($myconfig);
132
133   # get chart_id
134   my $query = qq|SELECT c.id FROM chart c
135                  WHERE c.accno = '$form->{accno}'|;
136   if ($form->{accounttype} eq 'gifi') {
137     $query = qq|SELECT c.id FROM chart c
138                 WHERE c.gifi_accno = '$form->{gifi_accno}'|;
139   }
140   my $sth = $dbh->prepare($query);
141   $sth->execute || $form->dberror($query);
142
143   my @id = ();
144   while (my ($id) = $sth->fetchrow_array) {
145     push @id, $id;
146   }
147   $sth->finish;
148
149   my $fromdate_where;
150   my $todate_where;
151
152   my $where = '1 = 1';
153
154   # build WHERE clause from dates if any
155   #  if ($form->{fromdate}) {
156   #    $where .= " AND ac.transdate >= '$form->{fromdate}'";
157   #  }
158   #  if ($form->{todate}) {
159   #    $where .= " AND ac.transdate <= '$form->{todate}'";
160   #  }
161
162   if ($form->{fromdate}) {
163     $fromto = " AND ac.transdate >= '$form->{fromdate}'";
164     $subwhere .= " AND transdate >= '$form->{fromdate}'";
165     $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
166   }
167
168   if ($form->{todate}) {
169     $fromto   .= " AND ac.transdate <= '$form->{todate}'";
170     $subwhere .= " AND transdate <= '$form->{todate}'";
171     $glwhere .= " AND ac.transdate <= '$form->{todate}'";
172   }
173
174
175   $where .= $fromto;
176   $AR_PAID = "";
177   $AP_PAID = "";
178   $glwhere = "";    # note! gl will be aliased as "a" later...
179   my $sortorder = join ', ',
180     $form->sort_columns(qw(transdate reference description));
181   my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
182
183   # Oracle workaround, use ordinal positions
184   my %ordinal = (transdate   => 4,
185                  reference   => 2,
186                  description => 3);
187   map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
188
189   my ($null, $department_id) = split /--/, $form->{department};
190   my $dpt_where;
191   my $dpt_join;
192   if ($department_id) {
193     $dpt_join = qq|
194                    JOIN department t ON (t.id = a.department_id)
195                   |;
196     $dpt_where = qq|
197                    AND t.id = $department_id
198                   |;
199   }
200
201   my $project;
202   if ($form->{project_id}) {
203     $project = qq|
204                  AND ac.project_id = $form->{project_id}
205                  |;
206   }
207
208   if ($form->{accno} || $form->{gifi_accno}) {
209
210     # get category for account
211     $query = qq|SELECT c.category
212                 FROM chart c
213                 WHERE c.accno = '$form->{accno}'|;
214
215     if ($form->{accounttype} eq 'gifi') {
216       $query = qq|SELECT c.category
217                 FROM chart c
218                 WHERE c.gifi_accno = '$form->{gifi_accno}'
219                 AND c.charttype = 'A'|;
220     }
221
222     $sth = $dbh->prepare($query);
223
224     $sth->execute || $form->dberror($query);
225     ($form->{category}) = $sth->fetchrow_array;
226     $sth->finish;
227
228     if ($form->{fromdate}) {
229
230       # get beginning balance
231       $query = qq|SELECT SUM(ac.amount)
232                   FROM acc_trans ac
233                   JOIN chart c ON (ac.chart_id = c.id)
234                   $dpt_join
235                   WHERE c.accno = '$form->{accno}'
236                   AND ac.transdate < '$form->{fromdate}'
237                   $dpt_where
238                   $project
239                   |;
240
241       if ($form->{project_id}) {
242
243         $query .= qq|
244
245                UNION
246
247                   SELECT SUM(ac.qty * ac.sellprice)
248                   FROM invoice ac
249                   JOIN ar a ON (ac.trans_id = a.id)
250                   JOIN parts p ON (ac.parts_id = p.id)
251                   JOIN chart c ON (p.income_accno_id = c.id)
252                   $dpt_join
253                   WHERE c.accno = '$form->{accno}'
254                   AND a.transdate < '$form->{fromdate}'
255                   AND c.category = 'I'
256                   $dpt_where
257                   $project
258
259                UNION
260
261                   SELECT SUM(ac.qty * ac.sellprice)
262                   FROM invoice ac
263                   JOIN ap a ON (ac.trans_id = a.id)
264                   JOIN parts p ON (ac.parts_id = p.id)
265                   JOIN chart c ON (p.expense_accno_id = c.id)
266                   $dpt_join
267                   WHERE c.accno = '$form->{accno}'
268                   AND a.transdate < '$form->{fromdate}'
269                   AND c.category = 'E'
270                   $dpt_where
271                   $project
272                   |;
273
274       }
275
276       if ($form->{accounttype} eq 'gifi') {
277         $query = qq|SELECT SUM(ac.amount)
278                   FROM acc_trans ac
279                   JOIN chart c ON (ac.chart_id = c.id)
280                   $dpt_join
281                   WHERE c.gifi_accno = '$form->{gifi_accno}'
282                   AND ac.transdate < '$form->{fromdate}'
283                   $dpt_where
284                   $project
285                   |;
286
287         if ($form->{project_id}) {
288
289           $query .= qq|
290
291                UNION
292
293                   SELECT SUM(ac.qty * ac.sellprice)
294                   FROM invoice ac
295                   JOIN ar a ON (ac.trans_id = a.id)
296                   JOIN parts p ON (ac.parts_id = p.id)
297                   JOIN chart c ON (p.income_accno_id = c.id)
298                   $dpt_join
299                   WHERE c.gifi_accno = '$form->{gifi_accno}'
300                   AND a.transdate < '$form->{fromdate}'
301                   AND c.category = 'I'
302                   $dpt_where
303                   $project
304
305                UNION
306
307                   SELECT SUM(ac.qty * ac.sellprice)
308                   FROM invoice ac
309                   JOIN ap a ON (ac.trans_id = a.id)
310                   JOIN parts p ON (ac.parts_id = p.id)
311                   JOIN chart c ON (p.expense_accno_id = c.id)
312                   $dpt_join
313                   WHERE c.gifi_accno = '$form->{gifi_accno}'
314                   AND a.transdate < '$form->{fromdate}'
315                   AND c.category = 'E'
316                   $dpt_where
317                   $project
318                   |;
319
320         }
321       }
322
323       $sth = $dbh->prepare($query);
324
325       $sth->execute || $form->dberror($query);
326       ($form->{balance}) = $sth->fetchrow_array;
327       $sth->finish;
328     }
329   }
330
331   $query = "";
332   my $union = "";
333
334   foreach my $id (@id) {
335
336     # NOTE:
337     #  Postgres is really picky about the order of implicit CROSS JOINs with ','
338     #  if you alias the  tables and want to use the alias later in another JOIN.
339     #  the alias you want to use has to be the most recent in the list, otherwise
340     #  Postgres will overwrite the alias internally and complain.
341     #  For this reason, in the next 3 SELECTs, the 'a' alias is last in the list.
342     #  Don't change this, and if you do, substitute the ',' with CROSS JOIN
343     #  ... that also works.
344
345     # get all transactions
346     $query .= qq|$union
347       SELECT a.id, a.reference, a.description, ac.transdate,
348              $false AS invoice, ac.amount, 'gl' as module
349                 FROM acc_trans ac, gl a $dpt_join
350                 WHERE $where
351                 $glwhere
352                 $dpt_where
353                 $project
354                 AND ac.chart_id = $id
355                 AND ac.trans_id = a.id
356       UNION
357       SELECT a.id, a.invnumber, c.name, ac.transdate,
358              a.invoice, ac.amount, 'ar' as module
359                 FROM acc_trans ac, customer c, ar a $dpt_join
360                 WHERE $where
361                 $dpt_where
362                 $project
363                 AND ac.chart_id = $id
364                 AND ac.trans_id = a.id
365                 $AR_PAID
366                 AND a.customer_id = c.id
367       UNION
368       SELECT a.id, a.invnumber, v.name, ac.transdate,
369              a.invoice, ac.amount, 'ap' as module
370                 FROM acc_trans ac, vendor v, ap a $dpt_join
371                 WHERE $where
372                 $dpt_where
373                 $project
374                 AND ac.chart_id = $id
375                 AND ac.trans_id = a.id
376                 $AP_PAID
377                 AND a.vendor_id = v.id
378                 |;
379     $union = qq|
380       UNION ALL
381       |;
382
383     if ($form->{project_id}) {
384
385       $fromdate_where =~ s/ac\./a\./;
386       $todate_where   =~ s/ac\./a\./;
387
388       $query .= qq|
389
390              UNION ALL
391
392                  SELECT a.id, a.invnumber, c.name, a.transdate,
393                  a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module
394                  FROM ar a
395                  JOIN invoice ac ON (ac.trans_id = a.id)
396                  JOIN parts p ON (ac.parts_id = p.id)
397                  JOIN customer c ON (a.customer_id = c.id)
398                  $dpt_join
399                  WHERE p.income_accno_id = $id
400                  $fromdate_where
401                  $todate_where
402                  $dpt_where
403                  $project
404
405              UNION ALL
406
407                  SELECT a.id, a.invnumber, v.name, a.transdate,
408                  a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module
409                  FROM ap a
410                  JOIN invoice ac ON (ac.trans_id = a.id)
411                  JOIN parts p ON (ac.parts_id = p.id)
412                  JOIN vendor v ON (a.vendor_id = v.id)
413                  $dpt_join
414                  WHERE p.expense_accno_id = $id
415                  $fromdate_where
416                  $todate_where
417                  $dpt_where
418                  $project
419                  |;
420
421       $fromdate_where =~ s/a\./ac\./;
422       $todate_where   =~ s/a\./ac\./;
423
424     }
425
426     $union = qq|
427              UNION ALL
428                  |;
429   }
430
431   $query .= qq|
432       ORDER BY $sortorder|;
433
434   $sth = $dbh->prepare($query);
435   $sth->execute || $form->dberror($query);
436
437   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
438
439     # gl
440     if ($ca->{module} eq "gl") {
441       $ca->{module} = "gl";
442     }
443
444     # ap
445     if ($ca->{module} eq "ap") {
446       $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
447     }
448
449     # ar
450     if ($ca->{module} eq "ar") {
451       $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
452     }
453
454     if ($ca->{amount} < 0) {
455       $ca->{debit}  = $ca->{amount} * -1;
456       $ca->{credit} = 0;
457     } else {
458       $ca->{credit} = $ca->{amount};
459       $ca->{debit}  = 0;
460     }
461
462     push @{ $form->{CA} }, $ca;
463
464   }
465
466   $sth->finish;
467   $dbh->disconnect;
468
469   $main::lxdebug->leave_sub();
470 }
471
472 1;