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