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
  76     SELECT c.id,
  77       c.accno,
  78       c.description,
  79       c.charttype,
  80       c.gifi_accno,
  81       c.category,
  82       c.link,
  83       c.pos_bwa,
  84       c.pos_bilanz,
  85       c.pos_eur,
  86       c.valid_from,
  87       c.datevautomatik,
  88       ( SELECT comma(taxkey)
  89         FROM tax tx
  90         WHERE tx.id in (
  91           SELECT tk.tax_id from taxkeys tk
  92           WHERE tk.chart_id = c.id
  93         )
  94         ORDER BY c.accno
  95       ) AS taxkey,
  96
  97       ( SELECT comma(taxdescription || to_char (rate, '99V99' ) || '%')
  98         FROM tax tx
  99         WHERE tx.id in (
 100           SELECT tk.tax_id from taxkeys tk
 101           WHERE tk.chart_id = c.id
 102         )
 103         ORDER BY c.accno
 104       ) AS taxdescription,
 105
 106       ( SELECT comma(taxnumber)
 107         FROM tax tx
 108         WHERE tx.id in (
 109           SELECT tk.tax_id from taxkeys tk
 110           WHERE tk.chart_id = c.id
 111         )
 112         ORDER BY c.accno
 113       ) AS taxaccount,
 114
 115       ( SELECT comma(tk.pos_ustva)
 116         FROM taxkeys tk
 117         WHERE tk.chart_id = c.id
 118         ORDER BY c.id
 119       ) AS tk_ustva,
 120
 121       ( SELECT comma(startdate)
 122         FROM taxkeys tk
 123         WHERE tk.chart_id = c.id
 124         ORDER BY c.id
 125       ) AS startdate,
 126
 127       ( SELECT accno
 128         FROM chart c2
 129         WHERE c2.id = c.id
 130       ) AS new_account
 131     FROM chart c
 132     ORDER BY accno
 133   };
 134
 135   $sth = $dbh->prepare($query);
 136   $sth->execute || $form->dberror($query);
 137
 138   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
 139     $ca->{amount}           = $amount{ $ca->{accno} };
 140     $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
 141     if ($ca->{amount} < 0) {
 142       $ca->{debit} = $ca->{amount} * -1;
 143     } else {
 144       $ca->{credit} = $ca->{amount};
 145     }
 146     push @{ $form->{CA} }, $ca;
 147   }
 148
 149   $sth->finish;
 150   $dbh->disconnect;
 151
 152   $main::lxdebug->leave_sub();
 153 }
 154
 155 sub all_transactions {
 156   $main::lxdebug->enter_sub();
 157
 158   my ($self, $myconfig, $form) = @_;
 159
 160   # connect to database
 161   my $dbh = $form->dbconnect($myconfig);
 162
 163   # get chart_id
 164   my $query = qq|SELECT c.id FROM chart c
 165                  WHERE c.accno = '$form->{accno}'|;
 166   if ($form->{accounttype} eq 'gifi') {
 167     $query = qq|SELECT c.id FROM chart c
 168                 WHERE c.gifi_accno = '$form->{gifi_accno}'|;
 169   }
 170   my $sth = $dbh->prepare($query);
 171   $sth->execute || $form->dberror($query);
 172
 173   my @id = ();
 174   while (my ($id) = $sth->fetchrow_array) {
 175     push @id, $id;
 176   }
 177   $sth->finish;
 178
 179   my $fromdate_where;
 180   my $todate_where;
 181
 182   my $where = '1 = 1';
 183
 184   # build WHERE clause from dates if any
 185   #  if ($form->{fromdate}) {
 186   #    $where .= " AND ac.transdate >= '$form->{fromdate}'";
 187   #  }
 188   #  if ($form->{todate}) {
 189   #    $where .= " AND ac.transdate <= '$form->{todate}'";
 190   #  }
 191
 192   if ($form->{fromdate}) {
 193     $fromto = " AND ac.transdate >= '$form->{fromdate}'";
 194     $subwhere .= " AND transdate >= '$form->{fromdate}'";
 195     $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
 196   }
 197
 198   if ($form->{todate}) {
 199     $fromto   .= " AND ac.transdate <= '$form->{todate}'";
 200     $subwhere .= " AND transdate <= '$form->{todate}'";
 201     $glwhere .= " AND ac.transdate <= '$form->{todate}'";
 202   }
 203
 204
 205   $where .= $fromto;
 206   $AR_PAID = "";
 207   $AP_PAID = "";
 208   $glwhere = "";    # note! gl will be aliased as "a" later...
 209   my $sortorder = join ', ',
 210     $form->sort_columns(qw(transdate reference description));
 211   my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
 212
 213   # Oracle workaround, use ordinal positions
 214   my %ordinal = (transdate   => 4,
 215                  reference   => 2,
 216                  description => 3);
 217   map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
 218
 219   my ($null, $department_id) = split /--/, $form->{department};
 220   my $dpt_where;
 221   my $dpt_join;
 222   if ($department_id) {
 223     $dpt_join = qq|
 224                    JOIN department t ON (t.id = a.department_id)
 225                   |;
 226     $dpt_where = qq|
 227                    AND t.id = $department_id
 228                   |;
 229   }
 230
 231   my $project;
 232   if ($form->{project_id}) {
 233     $project = qq|
 234                  AND ac.project_id = $form->{project_id}
 235                  |;
 236   }
 237
 238   if ($form->{accno} || $form->{gifi_accno}) {
 239
 240     # get category for account
 241     $query = qq|SELECT c.category
 242                 FROM chart c
 243                 WHERE c.accno = '$form->{accno}'|;
 244
 245     if ($form->{accounttype} eq 'gifi') {
 246       $query = qq|SELECT c.category
 247                 FROM chart c
 248                 WHERE c.gifi_accno = '$form->{gifi_accno}'
 249                 AND c.charttype = 'A'|;
 250     }
 251
 252     $sth = $dbh->prepare($query);
 253
 254     $sth->execute || $form->dberror($query);
 255     ($form->{category}) = $sth->fetchrow_array;
 256     $sth->finish;
 257
 258     if ($form->{fromdate}) {
 259
 260       # get beginning balance
 261       $query = qq|SELECT SUM(ac.amount)
 262                   FROM acc_trans ac
 263                   JOIN chart c ON (ac.chart_id = c.id)
 264                   $dpt_join
 265                   WHERE c.accno = '$form->{accno}'
 266                   AND ac.transdate < '$form->{fromdate}'
 267                   $dpt_where
 268                   $project
 269                   |;
 270
 271       if ($form->{project_id}) {
 272
 273         $query .= qq|
 274
 275                UNION
 276
 277                   SELECT SUM(ac.qty * ac.sellprice)
 278                   FROM invoice ac
 279                   JOIN ar a ON (ac.trans_id = a.id)
 280                   JOIN parts p ON (ac.parts_id = p.id)
 281                   JOIN chart c ON (p.income_accno_id = c.id)
 282                   $dpt_join
 283                   WHERE c.accno = '$form->{accno}'
 284                   AND a.transdate < '$form->{fromdate}'
 285                   AND c.category = 'I'
 286                   $dpt_where
 287                   $project
 288
 289                UNION
 290
 291                   SELECT SUM(ac.qty * ac.sellprice)
 292                   FROM invoice ac
 293                   JOIN ap a ON (ac.trans_id = a.id)
 294                   JOIN parts p ON (ac.parts_id = p.id)
 295                   JOIN chart c ON (p.expense_accno_id = c.id)
 296                   $dpt_join
 297                   WHERE c.accno = '$form->{accno}'
 298                   AND a.transdate < '$form->{fromdate}'
 299                   AND c.category = 'E'
 300                   $dpt_where
 301                   $project
 302                   |;
 303
 304       }
 305
 306       if ($form->{accounttype} eq 'gifi') {
 307         $query = qq|SELECT SUM(ac.amount)
 308                   FROM acc_trans ac
 309                   JOIN chart c ON (ac.chart_id = c.id)
 310                   $dpt_join
 311                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 312                   AND ac.transdate < '$form->{fromdate}'
 313                   $dpt_where
 314                   $project
 315                   |;
 316
 317         if ($form->{project_id}) {
 318
 319           $query .= qq|
 320
 321                UNION
 322
 323                   SELECT SUM(ac.qty * ac.sellprice)
 324                   FROM invoice ac
 325                   JOIN ar a ON (ac.trans_id = a.id)
 326                   JOIN parts p ON (ac.parts_id = p.id)
 327                   JOIN chart c ON (p.income_accno_id = c.id)
 328                   $dpt_join
 329                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 330                   AND a.transdate < '$form->{fromdate}'
 331                   AND c.category = 'I'
 332                   $dpt_where
 333                   $project
 334
 335                UNION
 336
 337                   SELECT SUM(ac.qty * ac.sellprice)
 338                   FROM invoice ac
 339                   JOIN ap a ON (ac.trans_id = a.id)
 340                   JOIN parts p ON (ac.parts_id = p.id)
 341                   JOIN chart c ON (p.expense_accno_id = c.id)
 342                   $dpt_join
 343                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 344                   AND a.transdate < '$form->{fromdate}'
 345                   AND c.category = 'E'
 346                   $dpt_where
 347                   $project
 348                   |;
 349
 350         }
 351       }
 352
 353       $sth = $dbh->prepare($query);
 354
 355       $sth->execute || $form->dberror($query);
 356       ($form->{balance}) = $sth->fetchrow_array;
 357       $sth->finish;
 358     }
 359   }
 360
 361   $query = "";
 362   my $union = "";
 363
 364   foreach my $id (@id) {
 365
 366     # NOTE:
 367     #  Postgres is really picky about the order of implicit CROSS JOINs with ','
 368     #  if you alias the  tables and want to use the alias later in another JOIN.
 369     #  the alias you want to use has to be the most recent in the list, otherwise
 370     #  Postgres will overwrite the alias internally and complain.
 371     #  For this reason, in the next 3 SELECTs, the 'a' alias is last in the list.
 372     #  Don't change this, and if you do, substitute the ',' with CROSS JOIN
 373     #  ... that also works.
 374
 375     # get all transactions
 376     $query .= qq|$union
 377       SELECT a.id, a.reference, a.description, ac.transdate,
 378              $false AS invoice, ac.amount, 'gl' as module
 379                 FROM acc_trans ac, gl a $dpt_join
 380                 WHERE $where
 381                 $glwhere
 382                 $dpt_where
 383                 $project
 384                 AND ac.chart_id = $id
 385                 AND ac.trans_id = a.id
 386       UNION
 387       SELECT a.id, a.invnumber, c.name, ac.transdate,
 388              a.invoice, ac.amount, 'ar' as module
 389                 FROM acc_trans ac, customer c, ar a $dpt_join
 390                 WHERE $where
 391                 $dpt_where
 392                 $project
 393                 AND ac.chart_id = $id
 394                 AND ac.trans_id = a.id
 395                 $AR_PAID
 396                 AND a.customer_id = c.id
 397       UNION
 398       SELECT a.id, a.invnumber, v.name, ac.transdate,
 399              a.invoice, ac.amount, 'ap' as module
 400                 FROM acc_trans ac, vendor v, ap a $dpt_join
 401                 WHERE $where
 402                 $dpt_where
 403                 $project
 404                 AND ac.chart_id = $id
 405                 AND ac.trans_id = a.id
 406                 $AP_PAID
 407                 AND a.vendor_id = v.id
 408                 |;
 409     $union = qq|
 410       UNION ALL
 411       |;
 412
 413     if ($form->{project_id}) {
 414
 415       $fromdate_where =~ s/ac\./a\./;
 416       $todate_where   =~ s/ac\./a\./;
 417
 418       $query .= qq|
 419
 420              UNION ALL
 421
 422                  SELECT a.id, a.invnumber, c.name, a.transdate,
 423                  a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module
 424                  FROM ar a
 425                  JOIN invoice ac ON (ac.trans_id = a.id)
 426                  JOIN parts p ON (ac.parts_id = p.id)
 427                  JOIN customer c ON (a.customer_id = c.id)
 428                  $dpt_join
 429                  WHERE p.income_accno_id = $id
 430                  $fromdate_where
 431                  $todate_where
 432                  $dpt_where
 433                  $project
 434
 435              UNION ALL
 436
 437                  SELECT a.id, a.invnumber, v.name, a.transdate,
 438                  a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module
 439                  FROM ap a
 440                  JOIN invoice ac ON (ac.trans_id = a.id)
 441                  JOIN parts p ON (ac.parts_id = p.id)
 442                  JOIN vendor v ON (a.vendor_id = v.id)
 443                  $dpt_join
 444                  WHERE p.expense_accno_id = $id
 445                  $fromdate_where
 446                  $todate_where
 447                  $dpt_where
 448                  $project
 449                  |;
 450
 451       $fromdate_where =~ s/a\./ac\./;
 452       $todate_where   =~ s/a\./ac\./;
 453
 454     }
 455
 456     $union = qq|
 457              UNION ALL
 458                  |;
 459   }
 460
 461   $query .= qq|
 462       ORDER BY $sortorder|;
 463
 464   $sth = $dbh->prepare($query);
 465   $sth->execute || $form->dberror($query);
 466
 467   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
 468
 469     # gl
 470     if ($ca->{module} eq "gl") {
 471       $ca->{module} = "gl";
 472     }
 473
 474     # ap
 475     if ($ca->{module} eq "ap") {
 476       $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
 477     }
 478
 479     # ar
 480     if ($ca->{module} eq "ar") {
 481       $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
 482     }
 483
 484     if ($ca->{amount} < 0) {
 485       $ca->{debit}  = $ca->{amount} * -1;
 486       $ca->{credit} = 0;
 487     } else {
 488       $ca->{credit} = $ca->{amount};
 489       $ca->{debit}  = 0;
 490     }
 491
 492     push @{ $form->{CA} }, $ca;
 493
 494   }
 495
 496   $sth->finish;
 497   $dbh->disconnect;
 498
 499   $main::lxdebug->leave_sub();
 500 }
 501
 502 1;