sql-ledger/SL/CA.pm

   1 #=====================================================================
   2 # SQL-Ledger Accounting
   3 # Copyright (C) 2001
   4 #
   5 #  Author: Dieter Simader
   6 #   Email: dsimader@sql-ledger.org
   7 #     Web: http://www.sql-ledger.org
   8 #
   9 #  Contributors:
  10 #
  11 # This program is free software; you can redistribute it and/or modify
  12 # it under the terms of the GNU General Public License as published by
  13 # the Free Software Foundation; either version 2 of the License, or
  14 # (at your option) any later version.
  15 #
  16 # This program is distributed in the hope that it will be useful,
  17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19 # GNU General Public License for more details.
  20 # You should have received a copy of the GNU General Public License
  21 # along with this program; if not, write to the Free Software
  22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  23 #======================================================================
  24 #
  25 # chart of accounts
  26 #
  27 #======================================================================
  28
  29
  30 package CA;
  31
  32
  33 sub all_accounts {
  34   my ($self, $myconfig, $form) = @_;
  35
  36   my $amount = ();
  37   # connect to database
  38   my $dbh = $form->dbconnect($myconfig);
  39
  40   my $query = qq|SELECT accno,
  41                  SUM(acc_trans.amount) AS amount
  42                  FROM chart, acc_trans
  43                  WHERE chart.id = acc_trans.chart_id
  44                  GROUP BY accno|;
  45   my $sth = $dbh->prepare($query);
  46   $sth->execute || $form->dberror($query);
  47
  48   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  49     $amount{$ref->{accno}} = $ref->{amount}
  50   }
  51   $sth->finish;
  52
  53   $query = qq|SELECT accno, description
  54               FROM gifi|;
  55   $sth = $dbh->prepare($query);
  56   $sth->execute || $form->dberror($query);
  57
  58   my $gifi = ();
  59   while (my ($accno, $description) = $sth->fetchrow_array) {
  60     $gifi{$accno} = $description;
  61   }
  62   $sth->finish;
  63
  64   $query = qq|SELECT c.id, c.accno, c.description, c.charttype, c.gifi_accno,
  65               c.category, c.link
  66               FROM chart c
  67               ORDER BY accno|;
  68   $sth = $dbh->prepare($query);
  69   $sth->execute || $form->dberror($query);
  70
  71   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
  72     $ca->{amount} = $amount{$ca->{accno}};
  73     $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
  74     if ($ca->{amount} < 0) {
  75       $ca->{debit} = $ca->{amount} * -1;
  76     } else {
  77       $ca->{credit} = $ca->{amount};
  78     }
  79     push @{ $form->{CA} }, $ca;
  80   }
  81
  82   $sth->finish;
  83   $dbh->disconnect;
  84
  85 }
  86
  87
  88 sub all_transactions {
  89   my ($self, $myconfig, $form) = @_;
  90
  91   # connect to database
  92   my $dbh = $form->dbconnect($myconfig);
  93
  94   # get chart_id
  95   my $query = qq|SELECT id FROM chart
  96                  WHERE accno = '$form->{accno}'|;
  97   if ($form->{accounttype} eq 'gifi') {
  98     $query = qq|SELECT id FROM chart
  99                 WHERE gifi_accno = '$form->{gifi_accno}'|;
 100   }
 101   my $sth = $dbh->prepare($query);
 102   $sth->execute || $form->dberror($query);
 103
 104   my @id = ();
 105   while (my ($id) = $sth->fetchrow_array) {
 106     push @id, $id;
 107   }
 108   $sth->finish;
 109
 110   my $fromdate_where;
 111   my $todate_where;
 112
 113   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 114
 115   if ($form->{fromdate}) {
 116     $fromdate_where = qq|
 117                  AND ac.transdate >= '$form->{fromdate}'
 118                 |;
 119   }
 120   if ($form->{todate}) {
 121     $todate_where .= qq|
 122                  AND ac.transdate <= '$form->{todate}'
 123                 |;
 124   }
 125
 126
 127   my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
 128
 129   # Oracle workaround, use ordinal positions
 130   my %ordinal = ( transdate => 4,
 131                   reference => 2,
 132                   description => 3 );
 133
 134   my @a = qw(transdate reference description);
 135   my $sortorder = $form->sort_order(\@a, \%ordinal);
 136
 137   my $null;
 138   my $department_id;
 139   my $dpt_where;
 140   my $dpt_join;
 141
 142   ($null, $department_id) = split /--/, $form->{department};
 143
 144   if ($department_id) {
 145     $dpt_join = qq|
 146                    JOIN department t ON (t.id = a.department_id)
 147                   |;
 148     $dpt_where = qq|
 149                    AND t.id = $department_id
 150                   |;
 151   }
 152
 153   my $project;
 154   my $project_id;
 155   if ($form->{projectnumber}) {
 156     ($null, $project_id) = split /--/, $form->{projectnumber};
 157     $project = qq|
 158                  AND ac.project_id = $project_id
 159                  |;
 160   }
 161
 162   if ($form->{accno} || $form->{gifi_accno}) {
 163     # get category for account
 164     $query = qq|SELECT category, link
 165                 FROM chart
 166                 WHERE accno = '$form->{accno}'|;
 167
 168     if ($form->{accounttype} eq 'gifi') {
 169       $query = qq|SELECT category, link
 170                 FROM chart
 171                 WHERE gifi_accno = '$form->{gifi_accno}'
 172                 AND charttype = 'A'|;
 173     }
 174
 175     $sth = $dbh->prepare($query);
 176
 177     $sth->execute || $form->dberror($query);
 178     ($form->{category}, $form->{link}) = $sth->fetchrow_array;
 179     $sth->finish;
 180
 181     if ($form->{fromdate}) {
 182
 183       # get beginning balance
 184       $query = qq|SELECT SUM(ac.amount)
 185                   FROM acc_trans ac
 186                   JOIN chart c ON (ac.chart_id = c.id)
 187                   $dpt_join
 188                   WHERE c.accno = '$form->{accno}'
 189                   AND ac.transdate < '$form->{fromdate}'
 190                   $dpt_where
 191                   $project
 192                   |;
 193
 194       if ($project_id) {
 195
 196         $query .= qq|
 197
 198                UNION
 199
 200                   SELECT SUM(ac.sellprice * ac.qty)
 201                   FROM invoice ac
 202                   JOIN ar a ON (ac.trans_id = a.id)
 203                   JOIN parts p ON (ac.parts_id = p.id)
 204                   JOIN chart c ON (p.income_accno_id = c.id)
 205                   $dpt_join
 206                   WHERE c.accno = '$form->{accno}'
 207                   AND a.transdate < '$form->{fromdate}'
 208                   AND c.category = 'I'
 209                   $dpt_where
 210                   $project
 211
 212                UNION
 213
 214                   SELECT SUM(ac.sellprice * ac.qty)
 215                   FROM invoice ac
 216                   JOIN ap a ON (ac.trans_id = a.id)
 217                   JOIN parts p ON (ac.parts_id = p.id)
 218                   JOIN chart c ON (p.expense_accno_id = c.id)
 219                   $dpt_join
 220                   WHERE c.accno = '$form->{accno}'
 221                   AND p.inventory_accno_id IS NULL
 222                   AND p.assembly = '0'
 223                   AND a.transdate < '$form->{fromdate}'
 224                   AND c.category = 'E'
 225                   $dpt_where
 226                   $project
 227
 228                UNION
 229
 230                   SELECT SUM(ac.sellprice * ac.allocated) * -1
 231                   FROM invoice ac
 232                   JOIN ap a ON (ac.trans_id = a.id)
 233                   JOIN parts p ON (ac.parts_id = p.id)
 234                   JOIN chart c ON (p.expense_accno_id = c.id)
 235                   $dpt_join
 236                   WHERE c.accno = '$form->{accno}'
 237                   AND ac.assemblyitem = '0'
 238                   AND a.transdate < '$form->{fromdate}'
 239                   AND c.category = 'E'
 240                   $dpt_where
 241                   $project
 242                   |;
 243
 244       }
 245
 246       if ($form->{accounttype} eq 'gifi') {
 247         $query = qq|SELECT SUM(ac.amount)
 248                   FROM acc_trans ac
 249                   JOIN chart c ON (ac.chart_id = c.id)
 250                   $dpt_join
 251                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 252                   AND ac.transdate < '$form->{fromdate}'
 253                   $dpt_where
 254                   $project
 255                   |;
 256
 257         if ($project_id) {
 258
 259           $query .= qq|
 260
 261                UNION
 262
 263                   SELECT SUM(ac.sellprice * ac.qty)
 264                   FROM invoice ac
 265                   JOIN ar a ON (ac.trans_id = a.id)
 266                   JOIN parts p ON (ac.parts_id = p.id)
 267                   JOIN chart c ON (p.income_accno_id = c.id)
 268                   $dpt_join
 269                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 270                   AND a.transdate < '$form->{fromdate}'
 271                   AND c.category = 'I'
 272                   $dpt_where
 273                   $project
 274
 275                UNION
 276
 277                   SELECT SUM(ac.sellprice * ac.qty)
 278                   FROM invoice ac
 279                   JOIN ap a ON (ac.trans_id = a.id)
 280                   JOIN parts p ON (ac.parts_id = p.id)
 281                   JOIN chart c ON (p.expense_accno_id = c.id)
 282                   $dpt_join
 283                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 284                   AND p.inventory_accno_id IS NULL
 285                   AND p.assembly = '0'
 286                   AND a.transdate < '$form->{fromdate}'
 287                   AND c.category = 'E'
 288                   $dpt_where
 289                   $project
 290
 291                UNION
 292
 293                   SELECT SUM(ac.sellprice * ac.allocated) * -1
 294                   FROM invoice ac
 295                   JOIN ap a ON (ac.trans_id = a.id)
 296                   JOIN parts p ON (ac.parts_id = p.id)
 297                   JOIN chart c ON (p.expense_accno_id = c.id)
 298                   $dpt_join
 299                   WHERE c.gifi_accno = '$form->{gifi_accno}'
 300                   AND ac.assemblyitem = '0'
 301                   AND a.transdate < '$form->{fromdate}'
 302                   AND c.category = 'E'
 303                   $dpt_where
 304                   $project
 305                   |;
 306
 307         }
 308       }
 309
 310       ($form->{balance}) = $dbh->selectrow_array($query);
 311
 312     }
 313   }
 314
 315   $query = "";
 316   my $union = "";
 317
 318   foreach my $id (@id) {
 319
 320     # get all transactions
 321     $query .= qq|$union
 322                  SELECT a.id, a.reference, a.description, ac.transdate,
 323                  $false AS invoice, ac.amount, 'gl' as module, ac.cleared,
 324                  '' AS till
 325                  FROM gl a
 326                  JOIN acc_trans ac ON (ac.trans_id = a.id)
 327                  $dpt_join
 328                  WHERE ac.chart_id = $id
 329                  $fromdate_where
 330                  $todate_where
 331                  $dpt_where
 332                  $project
 333
 334              UNION ALL
 335
 336                  SELECT a.id, a.invnumber, c.name, ac.transdate,
 337                  a.invoice, ac.amount, 'ar' as module, ac.cleared,
 338                  a.till
 339                  FROM ar a
 340                  JOIN acc_trans ac ON (ac.trans_id = a.id)
 341                  JOIN customer c ON (a.customer_id = c.id)
 342                  $dpt_join
 343                  WHERE ac.chart_id = $id
 344                  $fromdate_where
 345                  $todate_where
 346                  $dpt_where
 347                  $project
 348
 349              UNION ALL
 350
 351                  SELECT a.id, a.invnumber, v.name, ac.transdate,
 352                  a.invoice, ac.amount, 'ap' as module, ac.cleared,
 353                  a.till
 354                  FROM ap a
 355                  JOIN acc_trans ac ON (ac.trans_id = a.id)
 356                  JOIN vendor v ON (a.vendor_id = v.id)
 357                  $dpt_join
 358                  WHERE ac.chart_id = $id
 359                  $fromdate_where
 360                  $todate_where
 361                  $dpt_where
 362                  $project
 363                  |;
 364
 365     if ($project_id) {
 366
 367       $fromdate_where =~ s/ac\./a\./;
 368       $todate_where =~ s/ac\./a\./;
 369
 370       $query .= qq|
 371
 372              UNION ALL
 373
 374              -- sold items
 375
 376                  SELECT a.id, a.invnumber, c.name, a.transdate,
 377                  a.invoice, ac.sellprice * ac.qty, 'ar' as module, '0' AS cleared,
 378                  a.till
 379                  FROM ar a
 380                  JOIN invoice ac ON (ac.trans_id = a.id)
 381                  JOIN parts p ON (ac.parts_id = p.id)
 382                  JOIN customer c ON (a.customer_id = c.id)
 383                  $dpt_join
 384                  WHERE p.income_accno_id = $id
 385                  $fromdate_where
 386                  $todate_where
 387                  $dpt_where
 388                  $project
 389
 390              UNION ALL
 391
 392              -- bought services
 393
 394                  SELECT a.id, a.invnumber, v.name, a.transdate,
 395                  a.invoice, ac.sellprice * ac.qty, 'ap' as module, '0' AS cleared,
 396                  a.till
 397                  FROM ap a
 398                  JOIN invoice ac ON (ac.trans_id = a.id)
 399                  JOIN parts p ON (ac.parts_id = p.id)
 400                  JOIN vendor v ON (a.vendor_id = v.id)
 401                  $dpt_join
 402                  WHERE p.expense_accno_id = $id
 403                  AND p.inventory_accno_id IS NULL
 404                  AND p.assembly = '0'
 405                  $fromdate_where
 406                  $todate_where
 407                  $dpt_where
 408                  $project
 409
 410              UNION ALL
 411
 412              -- cogs
 413
 414                  SELECT a.id, a.invnumber, v.name, a.transdate,
 415                  a.invoice, ac.sellprice * ac.allocated * -1, 'ap' as module, '0' AS cleared,
 416                  a.till
 417                  FROM ap a
 418                  JOIN invoice ac ON (ac.trans_id = a.id)
 419                  JOIN parts p ON (ac.parts_id = p.id)
 420                  JOIN vendor v ON (a.vendor_id = v.id)
 421                  $dpt_join
 422                  WHERE p.expense_accno_id = $id
 423                  AND ac.assemblyitem = '0'
 424                  $fromdate_where
 425                  $todate_where
 426                  $dpt_where
 427                  $project
 428
 429                  |;
 430
 431       $fromdate_where =~ s/a\./ac\./;
 432       $todate_where =~ s/a\./ac\./;
 433
 434     }
 435
 436       $union = qq|
 437              UNION ALL
 438                  |;
 439   }
 440
 441   $query .= qq|
 442       ORDER BY $sortorder|;
 443
 444   $sth = $dbh->prepare($query);
 445   $sth->execute || $form->dberror($query);
 446
 447   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
 448
 449     # gl
 450     if ($ca->{module} eq "gl") {
 451       $ca->{module} = "gl";
 452     }
 453
 454     # ap
 455     if ($ca->{module} eq "ap") {
 456       $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
 457       $ca->{module} = 'ps' if $ca->{till};
 458     }
 459
 460     # ar
 461     if ($ca->{module} eq "ar") {
 462       $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
 463       $ca->{module} = 'ps' if $ca->{till};
 464     }
 465
 466     if ($ca->{amount}) {
 467       if ($ca->{amount} < 0) {
 468         $ca->{debit} = $ca->{amount} * -1;
 469         $ca->{credit} = 0;
 470       } else {
 471         $ca->{credit} = $ca->{amount};
 472         $ca->{debit} = 0;
 473       }
 474
 475       push @{ $form->{CA} }, $ca;
 476     }
 477
 478   }
 479
 480   $sth->finish;
 481   $dbh->disconnect;
 482
 483 }
 484
 485 1;
 486