X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FRC.pm;fp=sql-ledger%2FSL%2FRC.pm;h=0000000000000000000000000000000000000000;hp=2a8bf941012c46a891b1125399fa5a21e61c0bbd;hb=86b1b489a4ed2f9bc0cba6cafeab0d6eca5584dc;hpb=948b8acdd4b9b3864342062d0c397a11f57c5700

diff --git a/sql-ledger/SL/RC.pm b/sql-ledger/SL/RC.pm
deleted file mode 100644
index 2a8bf9410..000000000
--- a/sql-ledger/SL/RC.pm
+++ /dev/null
@@ -1,474 +0,0 @@
-#=====================================================================
-# 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;
-