SF.net SVN: ledger-smb: [472] trunk/LedgerSMB/RP.pm

[..hidden..]

Revision: 472
          http://svn.sourceforge.net/ledger-smb/?rev=472&view=rev
Author:   einhverfr
Date:     2006-11-02 17:27:50 -0800 (Thu, 02 Nov 2006)

Log Message:
-----------
RP.pm progress

Modified Paths:
--------------
    trunk/LedgerSMB/RP.pm

Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm	2006-11-03 01:03:22 UTC (rev 471)
+++ trunk/LedgerSMB/RP.pm	2006-11-03 01:27:50 UTC (rev 472)
@@ -1716,169 +1716,161 @@
 
 
 sub tax_report {
-  my ($self, $myconfig, $form) = @_;
+	my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+	my $dbh = $form->{dbh};
 
-  my ($null, $department_id) = split /--/, $form->{department};
+	my ($null, $department_id) = split /--/, $form->{department};
   
-  # build WHERE
-  my $where = "1 = 1";
-  my $cashwhere = "";
-
-  if ($department_id) {
-    $where .= qq|
-                 AND a.department_id = $department_id
-		|;
-  }
+	# build WHERE
+	my $where = "1 = 1";
+	my $cashwhere = "";
+	
+	if ($department_id) {
+		$where .= qq|AND a.department_id = |.
+			$dbh->quote($department_id);
+	}
   
-  my $query;
-  my $sth;
-  my $accno;
+	my $query;
+	my $sth;
+	my $accno;
   
-  if ($form->{accno}) {
-    if ($form->{accno} =~ /^gifi_/) {
-      ($null, $accno) = split /_/, $form->{accno};
-      $accno = qq| AND ch.gifi_accno = '$accno'|;
-    } else {
-      $accno = $form->{accno};
-      $accno = qq| AND ch.accno = '$accno'|;
-    }
-  }
+	if ($form->{accno}) {
+		if ($form->{accno} =~ /^gifi_/) {
+			($null, $accno) = split /_/, $form->{accno};
+			$accno = $dbh->quote($accno);
+			$accno = qq| AND ch.gifi_accno = $accno|;
+		} else {
+			$accno = $dbh->quote($form->{accno});
+			$accno = qq| AND ch.accno = $accno|;
+		}
+	}
 
-  my $table;
-  my $ARAP;
+	my $table;
+	my $ARAP;
   
-  if ($form->{db} eq 'ar') {
-    $table = "customer";
-    $ARAP = "AR";
-  }
-  if ($form->{db} eq 'ap') {
-    $table = "vendor";
-    $ARAP = "AP";
-  }
+	if ($form->{db} eq 'ar') {
+		$table = "customer";
+		$ARAP = "AR";
+	}
+	if ($form->{db} eq 'ap') {
+		$table = "vendor";
+		$ARAP = "AP";
+	}
 
-  my $transdate = "a.transdate";
+	my $transdate = "a.transdate";
 
-  ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
+	($form->{fromdate}, $form->{todate}) = 
+		$form->from_to($form->{year},$form->{month}, $form->{interval})
+		if $form->{year} && $form->{month};
   
-  # if there are any dates construct a where
-  if ($form->{fromdate} || $form->{todate}) {
-    if ($form->{fromdate}) {
-      $where .= " AND $transdate >= '$form->{fromdate}'";
-    }
-    if ($form->{todate}) {
-      $where .= " AND $transdate <= '$form->{todate}'";
-    }
-  }
+	# if there are any dates construct a where
+	if ($form->{fromdate} || $form->{todate}) {
+		if ($form->{fromdate}) {
+			$where .= " AND $transdate >= '$form->{fromdate}'";
+		}
+		if ($form->{todate}) {
+			$where .= " AND $transdate <= '$form->{todate}'";
+		}
+	}
 
 
-  if ($form->{method} eq 'cash') {
-    $transdate = "a.datepaid";
+	if ($form->{method} eq 'cash') {
+		$transdate = "a.datepaid";
 
-    my $todate = $form->{todate};
-    if (! $todate) {
-      ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
-    }
+		my $todate = $form->{todate};
+		if (! $todate) {
+			($todate) = $dbh->selectrow_array(
+				qq|SELECT current_date|);
+		}
     
-    $cashwhere = qq|
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = chart.id)
-		     WHERE link LIKE '%${ARAP}_paid%'
-		     AND $transdate <= '$todate'
-		     AND a.paid = a.amount
-		   )
-		  |;
+		$cashwhere = qq|
+			AND ac.trans_id IN (
+			SELECT trans_id
+			  FROM acc_trans
+			  JOIN chart ON (chart_id = chart.id)
+			 WHERE link LIKE '%${ARAP}_paid%'
+			       AND $transdate <= |.$dbh->quote($todate).qq|
+			       AND a.paid = a.amount)|;
 
-  }
+	}
 
     
-  my $ml = ($form->{db} eq 'ar') ? 1 : -1;
+	my $ml = ($form->{db} eq 'ar') ? 1 : -1;
   
-  my %ordinal = ( 'transdate' => 3,
-                  'invnumber' => 4,
-		  'name' => 5
-		);
+	my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
   
-  my @a = qw(transdate invnumber name);
-  my $sortorder = $form->sort_order(..hidden.., \%ordinal);
+	my @a = qw(transdate invnumber name);
+	my $sortorder = $form->sort_order(..hidden.., \%ordinal);
 
-  if ($form->{summary}) {
+	if ($form->{summary}) {
     
-    $query = qq|SELECT a.id, a.invoice, $transdate AS transdate,
-		a.invnumber, n.name, a.netamount,
-		ac.amount * $ml AS tax,
-		a.till
-		FROM acc_trans ac
-	      JOIN $form->{db} a ON (a.id = ac.trans_id)
-	      JOIN chart ch ON (ch.id = ac.chart_id)
-	      JOIN $table n ON (n.id = a.${table}_id)
-		WHERE $where
-		$accno
-		$cashwhere
-		|;
+		$query = qq|
+			SELECT a.id, a.invoice, $transdate AS transdate,
+			       a.invnumber, n.name, a.netamount,
+			       ac.amount * $ml AS tax, a.till
+			  FROM acc_trans ac
+			  JOIN $form->{db} a ON (a.id = ac.trans_id)
+			  JOIN chart ch ON (ch.id = ac.chart_id)
+			  JOIN $table n ON (n.id = a.${table}_id)
+			 WHERE $where $accno $cashwhere |;
 
-      if ($form->{fromdate}) {
-	# include open transactions from previous period
-	if ($cashwhere) {
-	  $query .= qq|
-              UNION
-	      
-                SELECT a.id, a.invoice, $transdate AS transdate,
-		a.invnumber, n.name, a.netamount,
-		ac.amount * $ml AS tax,
-		a.till
-		FROM acc_trans ac
-	      JOIN $form->{db} a ON (a.id = ac.trans_id)
-	      JOIN chart ch ON (ch.id = ac.chart_id)
-	      JOIN $table n ON (n.id = a.${table}_id)
-		WHERE a.datepaid >= '$form->{fromdate}'
-		$accno
-		$cashwhere
-		|;
-	}
-      }
+	  if ($form->{fromdate}) {
+		# include open transactions from previous period
+		if ($cashwhere) {
+			$query .= qq|
+				UNION
  
+				SELECT a.id, a.invoice, 
+				       $transdate AS transdate, a.invnumber, 
+				       n.name, a.netamount, ac.
+				       amount * $ml AS tax, a.till
+				  FROM acc_trans ac
+				  JOIN $form->{db} a ON (a.id = ac.trans_id)
+				  JOIN chart ch ON (ch.id = ac.chart_id)
+				  JOIN $table n ON (n.id = a.${table}_id)
+				 WHERE a.datepaid >= '$form->{fromdate}' 
+				       $accno $cashwhere|;
+		}
+	  }
+ 
 		
-    } else {
-      
-     $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
-		a.invnumber, n.name, a.netamount,
-		ac.amount * $ml AS tax,
-		a.notes AS description, a.till
-		FROM acc_trans ac
-	      JOIN $form->{db} a ON (a.id = ac.trans_id)
-	      JOIN chart ch ON (ch.id = ac.chart_id)
-	      JOIN $table n ON (n.id = a.${table}_id)
-		WHERE $where
-		$accno
-		AND a.invoice = '0'
-		$cashwhere
-		
-	      UNION
+	} else {
+
+		$query = qq|
+			SELECT a.id, '0' AS invoice, $transdate AS transdate,
+			       a.invnumber, n.name, a.netamount, 
+			       ac.amount * $ml AS tax, a.notes AS description, 
+			       a.till
+			  FROM acc_trans ac
+			  JOIN $form->{db} a ON (a.id = ac.trans_id)
+			  JOIN chart ch ON (ch.id = ac.chart_id)
+			  JOIN $table n ON (n.id = a.${table}_id)
+			 WHERE $where $accno AND a.invoice = '0' $cashwhere
+
+			UNION
 	      
-		SELECT a.id, '1' AS invoice, $transdate AS transdate,
-		a.invnumber, n.name,
-		i.sellprice * i.qty * $ml AS netamount,
-		i.sellprice * i.qty * $ml *
-		(SELECT tx.rate FROM tax tx WHERE tx.chart_id = ch.id AND (tx.validto > $transdate OR tx.validto IS NULL) ORDER BY validto LIMIT 1) AS tax,
-		i.description, a.till
-		FROM acc_trans ac
-	      JOIN $form->{db} a ON (a.id = ac.trans_id)
-	      JOIN chart ch ON (ch.id = ac.chart_id)
-	      JOIN $table n ON (n.id = a.${table}_id)
-	      JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
-	      JOIN invoice i ON (i.trans_id = a.id)
-	      JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
-		WHERE $where
-		$accno
-		AND a.invoice = '1'
-		$cashwhere
-		|;
+			SELECT a.id, '1' AS invoice, $transdate AS transdate,
+			       a.invnumber, n.name, 
+			       i.sellprice * i.qty * $ml AS netamount,
+			       i.sellprice * i.qty * $ml *
+			        (SELECT tx.rate FROM tax tx 
+			          WHERE tx.chart_id = ch.id 
+			                AND (tx.validto > $transdate 
+			                OR tx.validto IS NULL) 
+			       ORDER BY validto LIMIT 1) 
+			       AS tax, i.description, a.till
+			  FROM acc_trans ac
+			  JOIN $form->{db} a ON (a.id = ac.trans_id)
+			  JOIN chart ch ON (ch.id = ac.chart_id)
+			  JOIN $table n ON (n.id = a.${table}_id)
+			  JOIN ${table}tax t 
+			       ON (t.${table}_id = n.id AND t.chart_id = ch.id)
+			  JOIN invoice i ON (i.trans_id = a.id)
+			  JOIN partstax pt 
+			       ON (pt.parts_id = i.parts_id 
+			       AND pt.chart_id = ch.id)
+			 WHERE $where $accno AND a.invoice = '1' $cashwhere|;
 
       if ($form->{fromdate}) {
 	if ($cashwhere) {


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.