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