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;