X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FRC.pm;h=2a8bf941012c46a891b1125399fa5a21e61c0bbd;hp=9957d7349102dfed1662e4ac431f4d0805126a2c;hb=32306b5f8ffe4ce594409aa6e89626740b225a39;hpb=52072fcd26f2faf57923f598c358e7f47c4e2643

diff --git a/sql-ledger/SL/RC.pm b/sql-ledger/SL/RC.pm
index 9957d7349..2a8bf9410 100644
--- a/sql-ledger/SL/RC.pm
+++ b/sql-ledger/SL/RC.pm
@@ -38,7 +38,7 @@ sub paymentaccounts {
   my $query = qq|SELECT accno, description
                  FROM chart
 		 WHERE link LIKE '%_paid%'
-		 AND category = 'A'
+		 AND (category = 'A' OR category = 'L')
 		 ORDER BY accno|;
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
@@ -47,6 +47,9 @@ sub paymentaccounts {
     push @{ $form->{PR} }, $ref;
   }
   $sth->finish;
+
+  $form->all_years($dbh, $myconfig);
+
   $dbh->disconnect;
 
 }
@@ -58,92 +61,356 @@ sub payment_transactions {
   # connect to database, turn AutoCommit off
   my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my ($query, $sth);
-  
-  # get cleared balance
-  if ($form->{fromdate}) {
-    $query = qq|SELECT sum(a.amount)
-		FROM acc_trans a, chart c
-		WHERE a.transdate < date '$form->{fromdate}'
-		AND a.cleared = '1'
-		AND c.id = a.chart_id
-		AND c.accno = '$form->{accno}'
-		|;
-  } else {
-    $query = qq|SELECT sum(a.amount)
-		FROM acc_trans a, chart c
-		WHERE a.cleared = '1'
-		AND c.id = a.chart_id
-		AND c.accno = '$form->{accno}'
-		|;
-  }
+  my $query;
+  my $sth;
+
+  $query = qq|SELECT category FROM chart
+              WHERE accno = '$form->{accno}'|;
+  ($form->{category}) = $dbh->selectrow_array($query);
   
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my $cleared;
 
-  ($form->{beginningbalance}) = $sth->fetchrow_array;
+  ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
 
-  $sth->finish;
+  my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;
 
-  my %oid = ( 'Pg'      => 'ac.oid',
-              'Oracle'  => 'ac.rowid');
-
-  $query = qq|SELECT c.name, ac.source, ac.transdate, ac.cleared,
-	      ac.fx_transaction, ac.amount, a.id,
-	      $oid{$myconfig->{dbdriver}} AS oid
-	      FROM customer c, acc_trans ac, ar a, chart ch
-	      WHERE c.id = a.customer_id
-	      AND ac.cleared = '0'
-	      AND ac.trans_id = a.id
-	      AND ac.chart_id = ch.id
-	      AND ch.accno = '$form->{accno}'
+  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
 	      |;
-	      
-  $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
-  $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
-
-  $query .= qq|
-  
-      UNION
-              SELECT v.name, ac.source, ac.transdate, ac.cleared,
-	      ac.fx_transaction, ac.amount, a.id,
-	      $oid{$myconfig->{dbdriver}} AS oid
-	      FROM vendor v, acc_trans ac, ap a, chart ch
-	      WHERE v.id = a.vendor_id
-	      AND ac.cleared = '0'
-	      AND ac.trans_id = a.id
-	      AND ac.chart_id = ch.id
-	      AND ch.accno = '$form->{accno}'
-	     |;
-	      
-  $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
-  $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
-  $query .= qq|
+  ($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);
   
-      UNION
-	      SELECT g.description, ac.source, ac.transdate, ac.cleared,
-	      ac.fx_transaction, ac.amount, g.id,
-	      $oid{$myconfig->{dbdriver}} AS oid
-	      FROM gl g, acc_trans ac, chart ch
-	      WHERE g.id = ac.trans_id
-	      AND ac.cleared = '0'
-	      AND ac.trans_id = g.id
-	      AND ac.chart_id = ch.id
-	      AND ch.accno = '$form->{accno}'
+
+  $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);
 
-  $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
-  $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
 
-  $query .= " ORDER BY 3,7,8";
+  $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);
 
-  while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{PR} }, $pr;
+  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;
 
@@ -158,22 +425,43 @@ sub reconcile {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my ($query, $i);
-  my %oid = ( 'Pg'      => 'oid',
-              'Oracle'  => 'rowid');
+  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"}) {
-      $query = qq|UPDATE acc_trans SET cleared = '1'
-                  WHERE $oid{$myconfig->{dbdriver}} = $form->{"oid_$i"}|;
-      $dbh->do($query) || $form->dberror($query);
-
-      # clear fx_transaction
-      if ($form->{"fxoid_$i"}) {
-	$query = qq|UPDATE acc_trans SET cleared = '1'
-		    WHERE $oid{$myconfig->{dbdriver}} = $form->{"fxoid_$i"}|;
-	$dbh->do($query) || $form->dberror($query);
+    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;
       }
     }
   }