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