sql-ledger/SL/RC.pm

   1 #=====================================================================
   2 # SQL-Ledger Accounting
   3 # Copyright (C) 2002
   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 # Account reconciliation routines
  26 #
  27 #======================================================================
  28
  29 package RC;
  30
  31
  32 sub paymentaccounts {
  33   my ($self, $myconfig, $form) = @_;
  34
  35   # connect to database
  36   my $dbh = $form->dbconnect($myconfig);
  37
  38   my $query = qq|SELECT accno, description
  39                  FROM chart
  40                  WHERE link LIKE '%_paid%'
  41                  AND (category = 'A' OR category = 'L')
  42                  ORDER BY accno|;
  43   my $sth = $dbh->prepare($query);
  44   $sth->execute || $form->dberror($query);
  45
  46   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  47     push @{ $form->{PR} }, $ref;
  48   }
  49   $sth->finish;
  50
  51   $form->all_years($dbh, $myconfig);
  52
  53   $dbh->disconnect;
  54
  55 }
  56
  57
  58 sub payment_transactions {
  59   my ($self, $myconfig, $form) = @_;
  60
  61   # connect to database, turn AutoCommit off
  62   my $dbh = $form->dbconnect_noauto($myconfig);
  63
  64   my $query;
  65   my $sth;
  66
  67   $query = qq|SELECT category FROM chart
  68               WHERE accno = '$form->{accno}'|;
  69   ($form->{category}) = $dbh->selectrow_array($query);
  70
  71   my $cleared;
  72
  73   ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  74
  75   my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;
  76
  77   if (! $form->{fromdate}) {
  78     $cleared = qq| AND ac.cleared = '1'|;
  79     $transdate = "";
  80   }
  81
  82   # get beginning balance
  83   $query = qq|SELECT sum(ac.amount)
  84               FROM acc_trans ac
  85               JOIN chart c ON (c.id = ac.chart_id)
  86               WHERE c.accno = '$form->{accno}'
  87               $transdate
  88               $cleared
  89               |;
  90   ($form->{beginningbalance}) = $dbh->selectrow_array($query);
  91
  92   # fx balance
  93   $query = qq|SELECT sum(ac.amount)
  94               FROM acc_trans ac
  95               JOIN chart c ON (c.id = ac.chart_id)
  96               WHERE c.accno = '$form->{accno}'
  97               AND ac.fx_transaction = '1'
  98               $transdate
  99               $cleared
 100               |;
 101   ($form->{fx_balance}) = $dbh->selectrow_array($query);
 102
 103
 104   $transdate = "";
 105   if ($form->{todate}) {
 106     $transdate = qq| AND ac.transdate <= date '$form->{todate}'|;
 107   }
 108
 109   # get statement balance
 110   $query = qq|SELECT sum(ac.amount)
 111               FROM acc_trans ac
 112               JOIN chart c ON (c.id = ac.chart_id)
 113               WHERE c.accno = '$form->{accno}'
 114               $transdate
 115               |;
 116   ($form->{endingbalance}) = $dbh->selectrow_array($query);
 117
 118   # fx balance
 119   $query = qq|SELECT sum(ac.amount)
 120               FROM acc_trans ac
 121               JOIN chart c ON (c.id = ac.chart_id)
 122               WHERE c.accno = '$form->{accno}'
 123               AND ac.fx_transaction = '1'
 124               $transdate
 125               |;
 126   ($form->{fx_endingbalance}) = $dbh->selectrow_array($query);
 127
 128
 129   $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
 130
 131   if ($form->{report}) {
 132     $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
 133     if ($form->{cleared}) {
 134       $cleared = qq| AND ac.cleared = '1'|;
 135     }
 136     if ($form->{outstanding}) {
 137       $cleared = ($form->{cleared}) ? "" : qq| AND ac.cleared = '0'|;
 138     }
 139     if (! $form->{fromdate}) {
 140       $form->{beginningbalance} = 0;
 141       $form->{fx_balance} = 0;
 142     }
 143   }
 144
 145
 146   if ($form->{summary}) {
 147     $query = qq|SELECT ac.transdate, ac.source,
 148                 sum(ac.amount) AS amount, ac.cleared
 149                 FROM acc_trans ac
 150                 JOIN chart ch ON (ac.chart_id = ch.id)
 151                 WHERE ch.accno = '$form->{accno}'
 152                 AND ac.amount >= 0
 153                 AND ac.fx_transaction = '0'
 154                 $cleared|;
 155     $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
 156     $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 157     $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
 158     $query .= qq|
 159                 UNION
 160                 SELECT ac.transdate, ac.source,
 161                 sum(ac.amount) AS amount, ac.cleared
 162                 FROM acc_trans ac
 163                 JOIN chart ch ON (ac.chart_id = ch.id)
 164                 WHERE ch.accno = '$form->{accno}'
 165                 AND ac.amount < 0
 166                 AND ac.fx_transaction = '0'
 167                 $cleared|;
 168     $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
 169     $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 170     $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
 171
 172     $query .= " ORDER BY 1,2";
 173
 174   } else {
 175
 176     $query = qq|SELECT ac.transdate, ac.source, ac.fx_transaction,
 177                 ac.amount, ac.cleared, g.id, g.description
 178                 FROM acc_trans ac
 179                 JOIN chart ch ON (ac.chart_id = ch.id)
 180                 JOIN gl g ON (g.id = ac.trans_id)
 181                 WHERE ch.accno = '$form->{accno}'
 182                 AND ac.fx_transaction = '0'
 183                 $cleared|;
 184     $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
 185     $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 186
 187     $query .= qq|
 188                 UNION
 189                 SELECT ac.transdate, ac.source, ac.fx_transaction,
 190                 ac.amount, ac.cleared, a.id, n.name
 191                 FROM acc_trans ac
 192                 JOIN chart ch ON (ac.chart_id = ch.id)
 193                 JOIN ar a ON (a.id = ac.trans_id)
 194                 JOIN customer n ON (n.id = a.customer_id)
 195                 WHERE ch.accno = '$form->{accno}'
 196                 AND ac.fx_transaction = '0'
 197                 $cleared|;
 198     $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
 199     $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 200
 201     $query .= qq|
 202                 UNION
 203                 SELECT ac.transdate, ac.source, ac.fx_transaction,
 204                 ac.amount, ac.cleared, a.id, n.name
 205                 FROM acc_trans ac
 206                 JOIN chart ch ON (ac.chart_id = ch.id)
 207                 JOIN ap a ON (a.id = ac.trans_id)
 208                 JOIN vendor n ON (n.id = a.vendor_id)
 209                 WHERE ch.accno = '$form->{accno}'
 210                 AND ac.fx_transaction = '0'
 211                 $cleared|;
 212     $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
 213     $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 214
 215     $query .= " ORDER BY 1,2,3";
 216   }
 217
 218   $sth = $dbh->prepare($query);
 219   $sth->execute || $form->dberror($query);
 220
 221   my $dr;
 222   my $cr;
 223   my $fxs;
 224
 225   if ($form->{summary}) {
 226     $query = qq|SELECT ac.amount, ac.cleared
 227                 FROM acc_trans ac
 228                 JOIN ar a ON (a.id = ac.trans_id)
 229                 JOIN customer n ON (n.id = a.customer_id)
 230                 WHERE ac.fx_transaction = '1'
 231                 AND n.name = ?
 232                 AND ac.transdate = ?
 233                 AND ac.trans_id IN (SELECT id FROM ar a
 234                                     JOIN acc_trans ac ON (a.id = ac.trans_id)
 235                                     WHERE ac.source = ?)
 236                 AND ac.cleared = ?
 237                 AND NOT
 238                    (ac.chart_id IN
 239                     (SELECT fxgain_accno_id FROM defaults
 240                      UNION
 241                      SELECT fxloss_accno_id FROM defaults))
 242                 |;
 243
 244     $query .= qq|
 245                 UNION
 246                 SELECT ac.amount, ac.cleared
 247                 FROM acc_trans ac
 248                 JOIN ap a ON (a.id = ac.trans_id)
 249                 JOIN vendor n ON (n.id = a.vendor_id)
 250                 WHERE ac.fx_transaction = '1'
 251                 AND n.name = ?
 252                 AND ac.transdate = ?
 253                 AND ac.trans_id IN (SELECT id FROM ap a
 254                                     JOIN acc_trans ac ON (a.id = ac.trans_id)
 255                                     WHERE ac.source = ?)
 256                 AND ac.cleared = ?
 257                 AND NOT
 258                    (ac.chart_id IN
 259                     (SELECT fxgain_accno_id FROM defaults
 260                      UNION
 261                      SELECT fxloss_accno_id FROM defaults))
 262                 |;
 263
 264   } else {
 265
 266     $query = qq|SELECT ac.amount, ac.cleared
 267                 FROM acc_trans ac
 268                 WHERE ac.trans_id = ?
 269                 AND ac.fx_transaction = '1'
 270                 $cleared
 271                 AND NOT
 272                    (ac.chart_id IN
 273                     (SELECT fxgain_accno_id FROM defaults
 274                      UNION
 275                      SELECT fxloss_accno_id FROM defaults))
 276                 |;
 277
 278   }
 279
 280   $fxs = $dbh->prepare($query);
 281
 282
 283   if ($form->{summary}) {
 284     $query = qq|SELECT c.name
 285                 FROM customer c
 286                 JOIN ar a ON (c.id = a.customer_id)
 287                 JOIN acc_trans ac ON (a.id = ac.trans_id)
 288                 WHERE ac.transdate = ?
 289                 AND ac.source = ?
 290                 AND ac.amount > 0
 291                 $cleared
 292         UNION
 293                 SELECT v.name
 294                 FROM vendor v
 295                 JOIN ap a ON (v.id = a.vendor_id)
 296                 JOIN acc_trans ac ON (a.id = ac.trans_id)
 297                 WHERE ac.transdate = ?
 298                 AND ac.source = ?
 299                 AND ac.amount > 0
 300                 $cleared
 301         UNION
 302                 SELECT g.description
 303                 FROM gl g
 304                 JOIN acc_trans ac ON (g.id = ac.trans_id)
 305                 WHERE ac.transdate = ?
 306                 AND ac.source = ?
 307                 AND ac.amount > 0
 308                 $cleared
 309                 |;
 310
 311     $query .= " ORDER BY 1";
 312     $dr = $dbh->prepare($query);
 313
 314
 315     $query = qq|SELECT c.name
 316                 FROM customer c
 317                 JOIN ar a ON (c.id = a.customer_id)
 318                 JOIN acc_trans ac ON (a.id = ac.trans_id)
 319                 WHERE ac.transdate = ?
 320                 AND ac.source = ?
 321                 AND ac.amount < 0
 322                 $cleared
 323         UNION
 324                 SELECT v.name
 325                 FROM vendor v
 326                 JOIN ap a ON (v.id = a.vendor_id)
 327                 JOIN acc_trans ac ON (a.id = ac.trans_id)
 328                 WHERE ac.transdate = ?
 329                 AND ac.source = ?
 330                 AND ac.amount < 0
 331                 $cleared
 332         UNION
 333                 SELECT g.description
 334                 FROM gl g
 335                 JOIN acc_trans ac ON (g.id = ac.trans_id)
 336                 WHERE ac.transdate = ?
 337                 AND ac.source = ?
 338                 AND ac.amount < 0
 339                 $cleared
 340                 |;
 341
 342     $query .= " ORDER BY 1";
 343     $cr = $dbh->prepare($query);
 344   }
 345
 346
 347   my $name;
 348   my $ref;
 349   my $xfref;
 350
 351   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
 352
 353     if ($form->{summary}) {
 354
 355       if ($ref->{amount} > 0) {
 356         $dr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
 357         $ref->{oldcleared} = $ref->{cleared};
 358         $ref->{name} = ();
 359         while (($name) = $dr->fetchrow_array) {
 360           push @{ $ref->{name} }, $name;
 361         }
 362         $dr->finish;
 363       } else {
 364
 365         $cr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
 366         $ref->{oldcleared} = $ref->{cleared};
 367         $ref->{name} = ();
 368         while (($name) = $cr->fetchrow_array) {
 369           push @{ $ref->{name} }, $name;
 370         }
 371         $cr->finish;
 372
 373       }
 374
 375     } else {
 376       push @{ $ref->{name} }, $ref->{description};
 377     }
 378
 379     push @{ $form->{PR} }, $ref;
 380
 381     # include fx transactions
 382     $amount = 0;
 383     $addfx = 0;
 384     $ref->{oldcleared} = $ref->{cleared};
 385     if ($form->{summary}) {
 386       foreach $name (@{ $ref->{name} }) {
 387         $fxs->execute($name, $ref->{transdate}, $ref->{source}, $ref->{cleared}, $name, $ref->{transdate}, $ref->{source}, $ref->{cleared});
 388         while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
 389           $addfx = 1;
 390           $amount += $fxref->{amount};
 391         }
 392         $fxs->finish;
 393       }
 394     } else {
 395       $fxs->execute($ref->{id});
 396       while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
 397         $addfx = 1;
 398         $amount += $fxref->{amount};
 399       }
 400       $fxs->finish;
 401     }
 402
 403     if ($addfx) {
 404       $fxref = ();
 405       map { $fxref->{$_} = $ref->{$_} } keys %$ref;
 406       $fxref->{fx_transaction} = 1;
 407       $fxref->{name} = ();
 408       $fxref->{source} = "";
 409       $fxref->{transdate} = "";
 410       $fxref->{amount} = $amount;
 411       push @{ $form->{PR} }, $fxref;
 412     }
 413
 414   }
 415   $sth->finish;
 416
 417   $dbh->disconnect;
 418
 419 }
 420
 421
 422 sub reconcile {
 423   my ($self, $myconfig, $form) = @_;
 424
 425   # connect to database
 426   my $dbh = $form->dbconnect($myconfig);
 427
 428   my $query = qq|SELECT id FROM chart
 429                  WHERE accno = '$form->{accno}'|;
 430   my ($chart_id) = $dbh->selectrow_array($query);
 431   $chart_id *= 1;
 432
 433   $query = qq|SELECT trans_id FROM acc_trans
 434               WHERE source = ?
 435               AND transdate = ?
 436               AND cleared = '0'|;
 437   my $sth = $dbh->prepare($query) || $form->dberror($query);
 438
 439   my $i;
 440   my $trans_id;
 441
 442   $query = qq|UPDATE acc_trans SET cleared = '1'
 443               WHERE cleared = '0'
 444               AND trans_id = ?
 445               AND transdate = ?
 446               AND chart_id = $chart_id|;
 447   my $tth = $dbh->prepare($query) || $form->dberror($query);
 448
 449   # clear flags
 450   for $i (1 .. $form->{rowcount}) {
 451     if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
 452       if ($form->{summary}) {
 453         $sth->execute($form->{"source_$i"}, $form->{"transdate_$i"}) || $form->dberror;
 454
 455         while (($trans_id) = $sth->fetchrow_array) {
 456           $tth->execute($trans_id, $form->{"transdate_$i"}) || $form->dberror;
 457           $tth->finish;
 458         }
 459         $sth->finish;
 460
 461       } else {
 462
 463         $tth->execute($form->{"id_$i"}, $form->{"transdate_$i"}) || $form->dberror;
 464         $tth->finish;
 465       }
 466     }
 467   }
 468
 469   $dbh->disconnect;
 470
 471 }
 472
 473 1;
 474