path: root/sql-ledger/SL/RC.pm

#=====================================================================
# SQL-Ledger Accounting
# Copyright (C) 2002
#
#  Author: Dieter Simader
#   Email: dsimader@sql-ledger.org
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#======================================================================
#
# Account reconciliation routines
#
#======================================================================

package RC;


sub paymentaccounts {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  my $query = qq|SELECT accno, description
                 FROM chart
		 WHERE link LIKE '%_paid%'
		 AND (category = 'A' OR category = 'L')
		 ORDER BY accno|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{PR} }, $ref;
  }
  $sth->finish;

  $form->all_years($dbh, $myconfig);

  $dbh->disconnect;

}


sub payment_transactions {
  my ($self, $myconfig, $form) = @_;

  # connect to database, turn AutoCommit off
  my $dbh = $form->dbconnect_noauto($myconfig);

  my $query;
  my $sth;

  $query = qq|SELECT category FROM chart
              WHERE accno = '$form->{accno}'|;
  ($form->{category}) = $dbh->selectrow_array($query);
  
  my $cleared;

  ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};

  my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;

  if (! $form->{fromdate}) {
    $cleared = qq| AND ac.cleared = '1'|;
    $transdate = "";
  }
    
  # get beginning balance
  $query = qq|SELECT sum(ac.amount)
	      FROM acc_trans ac
	      JOIN chart c ON (c.id = ac.chart_id)
	      WHERE c.accno = '$form->{accno}'
	      $transdate
	      $cleared
	      |;
  ($form->{beginningbalance}) = $dbh->selectrow_array($query);

  # fx balance
  $query = qq|SELECT sum(ac.amount)
	      FROM acc_trans ac
	      JOIN chart c ON (c.id = ac.chart_id)
	      WHERE c.accno = '$form->{accno}'
	      AND ac.fx_transaction = '1'
	      $transdate
	      $cleared
	      |;
  ($form->{fx_balance}) = $dbh->selectrow_array($query);
  

  $transdate = "";
  if ($form->{todate}) {
    $transdate = qq| AND ac.transdate <= date '$form->{todate}'|;
  }
 
  # get statement balance
  $query = qq|SELECT sum(ac.amount)
	      FROM acc_trans ac
	      JOIN chart c ON (c.id = ac.chart_id)
	      WHERE c.accno = '$form->{accno}'
	      $transdate
	      |;
  ($form->{endingbalance}) = $dbh->selectrow_array($query);

  # fx balance
  $query = qq|SELECT sum(ac.amount)
	      FROM acc_trans ac
	      JOIN chart c ON (c.id = ac.chart_id)
	      WHERE c.accno = '$form->{accno}'
	      AND ac.fx_transaction = '1'
	      $transdate
	      |;
  ($form->{fx_endingbalance}) = $dbh->selectrow_array($query);


  $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
  
  if ($form->{report}) {
    $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
    if ($form->{cleared}) {
      $cleared = qq| AND ac.cleared = '1'|;
    }
    if ($form->{outstanding}) {
      $cleared = ($form->{cleared}) ? "" : qq| AND ac.cleared = '0'|;
    }
    if (! $form->{fromdate}) {
      $form->{beginningbalance} = 0;
      $form->{fx_balance} = 0;
    }
  }
  
  
  if ($form->{summary}) {
    $query = qq|SELECT ac.transdate, ac.source,
		sum(ac.amount) AS amount, ac.cleared
		FROM acc_trans ac
		JOIN chart ch ON (ac.chart_id = ch.id)
		WHERE ch.accno = '$form->{accno}'
		AND ac.amount >= 0
		AND ac.fx_transaction = '0'
		$cleared|;
    $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
    $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
    $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
    $query .= qq|
                UNION
		SELECT ac.transdate, ac.source,
		sum(ac.amount) AS amount, ac.cleared
		FROM acc_trans ac
		JOIN chart ch ON (ac.chart_id = ch.id)
		WHERE ch.accno = '$form->{accno}'
		AND ac.amount < 0
		AND ac.fx_transaction = '0'
		$cleared|;
    $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
    $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
    $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";

    $query .= " ORDER BY 1,2";
    
  } else {
    
    $query = qq|SELECT ac.transdate, ac.source, ac.fx_transaction,
		ac.amount, ac.cleared, g.id, g.description
		FROM acc_trans ac
		JOIN chart ch ON (ac.chart_id = ch.id)
		JOIN gl g ON (g.id = ac.trans_id)
		WHERE ch.accno = '$form->{accno}'
		AND ac.fx_transaction = '0'
		$cleared|;
    $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
    $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
    
    $query .= qq|
                UNION
		SELECT ac.transdate, ac.source, ac.fx_transaction,
		ac.amount, ac.cleared, a.id, n.name
		FROM acc_trans ac
		JOIN chart ch ON (ac.chart_id = ch.id)
		JOIN ar a ON (a.id = ac.trans_id)
		JOIN customer n ON (n.id = a.customer_id)
		WHERE ch.accno = '$form->{accno}'
		AND ac.fx_transaction = '0'
		$cleared|;
    $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
    $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
    
    $query .= qq|
                UNION
		SELECT ac.transdate, ac.source, ac.fx_transaction,
		ac.amount, ac.cleared, a.id, n.name
		FROM acc_trans ac
		JOIN chart ch ON (ac.chart_id = ch.id)
		JOIN ap a ON (a.id = ac.trans_id)
		JOIN vendor n ON (n.id = a.vendor_id)
		WHERE ch.accno = '$form->{accno}'
		AND ac.fx_transaction = '0'
		$cleared|;
    $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
    $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
    
    $query .= " ORDER BY 1,2,3";
  }

  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  my $dr;
  my $cr;
  my $fxs;
  
  if ($form->{summary}) {
    $query = qq|SELECT ac.amount, ac.cleared
		FROM acc_trans ac
		JOIN ar a ON (a.id = ac.trans_id)
		JOIN customer n ON (n.id = a.customer_id)
		WHERE ac.fx_transaction = '1'
		AND n.name = ?
		AND ac.transdate = ?
		AND ac.trans_id IN (SELECT id FROM ar a
				    JOIN acc_trans ac ON (a.id = ac.trans_id)
				    WHERE ac.source = ?)
		AND ac.cleared = ?
		AND NOT
		   (ac.chart_id IN
		    (SELECT fxgain_accno_id FROM defaults
		     UNION
		     SELECT fxloss_accno_id FROM defaults))
		|;
		
    $query .= qq|
		UNION
		SELECT ac.amount, ac.cleared
		FROM acc_trans ac
		JOIN ap a ON (a.id = ac.trans_id)
		JOIN vendor n ON (n.id = a.vendor_id)
		WHERE ac.fx_transaction = '1'
		AND n.name = ?
		AND ac.transdate = ?
		AND ac.trans_id IN (SELECT id FROM ap a
				    JOIN acc_trans ac ON (a.id = ac.trans_id)
				    WHERE ac.source = ?)
		AND ac.cleared = ?
		AND NOT
		   (ac.chart_id IN
		    (SELECT fxgain_accno_id FROM defaults
		     UNION
		     SELECT fxloss_accno_id FROM defaults))
		|;

  } else {

    $query = qq|SELECT ac.amount, ac.cleared
		FROM acc_trans ac
		WHERE ac.trans_id = ?
		AND ac.fx_transaction = '1'
		$cleared
		AND NOT
		   (ac.chart_id IN
		    (SELECT fxgain_accno_id FROM defaults
		     UNION
		     SELECT fxloss_accno_id FROM defaults))
		|;
	
  }

  $fxs = $dbh->prepare($query);


  if ($form->{summary}) {
    $query = qq|SELECT c.name
		FROM customer c
		JOIN ar a ON (c.id = a.customer_id)
		JOIN acc_trans ac ON (a.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount > 0
		$cleared
	UNION
		SELECT v.name
		FROM vendor v
		JOIN ap a ON (v.id = a.vendor_id)
		JOIN acc_trans ac ON (a.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount > 0
		$cleared
	UNION
		SELECT g.description
		FROM gl g
		JOIN acc_trans ac ON (g.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount > 0
		$cleared
		|;
    
    $query .= " ORDER BY 1";
    $dr = $dbh->prepare($query);


    $query = qq|SELECT c.name
		FROM customer c
		JOIN ar a ON (c.id = a.customer_id)
		JOIN acc_trans ac ON (a.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount < 0
		$cleared
	UNION
		SELECT v.name
		FROM vendor v
		JOIN ap a ON (v.id = a.vendor_id)
		JOIN acc_trans ac ON (a.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount < 0
		$cleared
	UNION
		SELECT g.description
		FROM gl g
		JOIN acc_trans ac ON (g.id = ac.trans_id)
		WHERE ac.transdate = ?
		AND ac.source = ?
		AND ac.amount < 0
		$cleared
		|;
		
    $query .= " ORDER BY 1";
    $cr = $dbh->prepare($query);
  }
 

  my $name;
  my $ref;
  my $xfref;

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

    if ($form->{summary}) {

      if ($ref->{amount} > 0) {
	$dr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
	$ref->{oldcleared} = $ref->{cleared};
	$ref->{name} = ();
	while (($name) = $dr->fetchrow_array) {
	  push @{ $ref->{name} }, $name;
	}
	$dr->finish;
      } else {
      
	$cr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
	$ref->{oldcleared} = $ref->{cleared};
	$ref->{name} = ();
	while (($name) = $cr->fetchrow_array) {
	  push @{ $ref->{name} }, $name;
	}
	$cr->finish;
	
      }

    } else {
      push @{ $ref->{name} }, $ref->{description};
    }

    push @{ $form->{PR} }, $ref;

    # include fx transactions
    $amount = 0;
    $addfx = 0;
    $ref->{oldcleared} = $ref->{cleared};
    if ($form->{summary}) {
      foreach $name (@{ $ref->{name} }) {
	$fxs->execute($name, $ref->{transdate}, $ref->{source}, $ref->{cleared}, $name, $ref->{transdate}, $ref->{source}, $ref->{cleared});
	while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
	  $addfx = 1;
	  $amount += $fxref->{amount};
	}
	$fxs->finish;
      }
    } else {
      $fxs->execute($ref->{id});
      while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
	$addfx = 1;
	$amount += $fxref->{amount};
      }
      $fxs->finish;
    }
    
    if ($addfx) {
      $fxref = ();
      map { $fxref->{$_} = $ref->{$_} } keys %$ref;
      $fxref->{fx_transaction} = 1;
      $fxref->{name} = ();
      $fxref->{source} = "";
      $fxref->{transdate} = "";
      $fxref->{amount} = $amount;
      push @{ $form->{PR} }, $fxref;
    }
 
  }
  $sth->finish;

  $dbh->disconnect;
  
}


sub reconcile {
  my ($self, $myconfig, $form) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  my $query = qq|SELECT id FROM chart
                 WHERE accno = '$form->{accno}'|;
  my ($chart_id) = $dbh->selectrow_array($query);
  $chart_id *= 1;
  
  $query = qq|SELECT trans_id FROM acc_trans
              WHERE source = ?
	      AND transdate = ?
	      AND cleared = '0'|;
  my $sth = $dbh->prepare($query) || $form->dberror($query);
    
  my $i;
  my $trans_id;

  $query = qq|UPDATE acc_trans SET cleared = '1'
              WHERE cleared = '0'
	      AND trans_id = ? 
	      AND transdate = ?
	      AND chart_id = $chart_id|;
  my $tth = $dbh->prepare($query) || $form->dberror($query);
  
  # clear flags
  for $i (1 .. $form->{rowcount}) {
    if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
      if ($form->{summary}) {
	$sth->execute($form->{"source_$i"}, $form->{"transdate_$i"}) || $form->dberror;
      
	while (($trans_id) = $sth->fetchrow_array) {
	  $tth->execute($trans_id, $form->{"transdate_$i"}) || $form->dberror;
	  $tth->finish;
	}
	$sth->finish;
	
      } else {

	$tth->execute($form->{"id_$i"}, $form->{"transdate_$i"}) || $form->dberror;
	$tth->finish;
      }
    }
  }

  $dbh->disconnect;

}

1;