#=====================================================================
# LedgerSMB 
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
# 
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2001
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# backend code for reports
#
#======================================================================

package RP;

sub inventory_activity {
	my ($self, $myconfig, $form) = @_;
	($form->{fromdate}, $form->{todate}) = 
		$form->from_to($form->{fromyear}, $form->{frommonth}, 
		$form->{interval}) 
			if $form->{fromyear} && $form->{frommonth};

	my $dbh = $form->{dbh};

	unless ($form->{sort_col}){
		$form->{sort_col} = 'partnumber';
	}


	my $where = '';
	if ($form->{fromdate}){
		 $where .= "AND coalesce(ar.duedate, ap.duedate) >= ".
			$dbh->quote($form->{fromdate});
	}
	if ($form->{todate}){
		 $where .= "AND coalesce(ar.duedate, ap.duedate) < ".
			$dbh->quote($form->{todate}). " ";
	}
	if ($form->{partnumber}){
		$where .= qq| AND p.partnumber ILIKE |.
			$dbh->quote('%'."$form->{partnumber}%"); 
	}
	if ($form->{description}){
		$where .= q| AND p.description ILIKE |
			.$dbh->quote('%'."$form->{description}%");
	}
	$where =~ s/^\s?AND/WHERE/;

	my $query = qq|
		   SELECT min(p.description) AS description, 
		          min(p.partnumber) AS partnumber, sum(
		          CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END) AS sold, 
		          sum (CASE WHEN i.qty > 0 
		                    THEN i.sellprice * i.qty 
		                    ELSE 0 END) AS revenue, 
		          sum(CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END) 
		          AS received, sum(CASE WHEN i.qty < 0 
		                                THEN i.sellprice * i.qty * -1
		                                ELSE 0 END) as expenses, 
		          min(p.id) as id
		     FROM invoice i
		     JOIN parts p ON (i.parts_id = p.id)
		LEFT JOIN ar ON (ar.id = i.trans_id)
		LEFT JOIN ap ON (ap.id = i.trans_id)
		   $where
		 GROUP BY i.parts_id
		 ORDER BY $form->{sort_col}|;
	my $sth = $dbh->prepare($query) || $form->dberror($query);
	$sth->execute() || $form->dberror($query);
	@cols = qw(description sold revenue partnumber received expense);
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$ref->{net_income} = $ref->{revenue} - $ref->{expense}; 
		map {$ref->{$_} =~ s/^\s*//} @cols;
		map {$ref->{$_} =~ s/\s*$//} @cols;
		push @{$form->{TB}}, $ref;
	}
	$sth->finish;
	$dbh->commit;
    
}



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

	my $dbh = $form->{dbh};

	# if todate < existing yearends, delete GL and yearends
	my $query = qq|SELECT trans_id FROM yearend WHERE transdate >= ?|;
	my $sth = $dbh->prepare($query);
	$sth->execute($form->{todate}) || $form->dberror($query);
	
	my @trans_id = ();
	my $id;
	while (($id) = $sth->fetchrow_array) {
		push @trans_id, $id;
	}
	$sth->finish;

	$query = qq|DELETE FROM gl WHERE id = ?|;
	$sth = $dbh->prepare($query) || $form->dberror($query);

	$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
	my $ath = $dbh->prepare($query) || $form->dberror($query);
	      
	foreach $id (@trans_id) {
		$sth->execute($id);
		$ath->execute($id);

		$sth->finish;
		$ath->finish;
	}
  
  
	my $last_period = 0;
	my @categories = qw(I E);
	my $category;

	$form->{decimalplaces} *= 1;

	&get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, \@categories);
  
	$dbh->commit;


	# now we got $form->{I}{accno}{ }
	# and $form->{E}{accno}{  }
  
	my %account = ( 
		'I' => { 
			'label' => 'income',
			'labels' => 'income',
			'ml' => 1 },
		'E' => { 
			'label' => 'expense',
			'labels' => 'expenses',
			'ml' => -1 }
		);
  
	foreach $category (@categories) {
		foreach $key (sort keys %{ $form->{$category} }) {
			if ($form->{$category}{$key}{charttype} eq 'A') {
				$form->{"total_$account{$category}{labels}_this_period"} 
					+= $form->{$category}{$key}{this} 
					* $account{$category}{ml};
			}
		}
	}


	# totals for income and expenses
	$form->{total_income_this_period} = $form->round_amount(
		$form->{total_income_this_period}, $form->{decimalplaces});
	$form->{total_expenses_this_period} = $form->round_amount(
		$form->{total_expenses_this_period}, $form->{decimalplaces});

	# total for income/loss
	$form->{total_this_period} 
		= $form->{total_income_this_period} 
			- $form->{total_expenses_this_period};
  
}


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

	my $dbh = $form->{dbh};

	my $last_period = 0;
	my @categories = qw(I E);
	my $category;

	$form->{decimalplaces} *= 1;

	if (! ($form->{fromdate} || $form->{todate})) {
		if ($form->{fromyear} && $form->{frommonth}) {
			($form->{fromdate}, $form->{todate}) 
				= $form->from_to(
					$form->{fromyear}, 
					$form->{frommonth}, $form->{interval});
		}
	}
  
	&get_accounts(
		$dbh, $last_period, $form->{fromdate}, $form->{todate}, 
		$form, \@categories, 1);
  
	if (! ($form->{comparefromdate} || $form->{comparetodate})) {
		if ($form->{compareyear} && $form->{comparemonth}) {
			($form->{comparefromdate}, $form->{comparetodate}) 
				= $form->from_to(
					$form->{compareyear}, 
					$form->{comparemonth}, 
					$form->{interval});
		}
	}

	# if there are any compare dates
	if ($form->{comparefromdate} || $form->{comparetodate}) {
		$last_period = 1;

		&get_accounts(
			$dbh, $last_period, $form->{comparefromdate}, 
			$form->{comparetodate}, $form, \@categories, 1);
	}  

  
	$dbh->commit;


	# now we got $form->{I}{accno}{ }
	# and $form->{E}{accno}{  }
  
	my %account = ( 
		'I' => { 
			'label' => 'income',
			'labels' => 'income',
			'ml' => 1 },
		  'E' => { 
			'label' => 'expense',
			'labels' => 'expenses',
			'ml' => -1 }
		);
  
	my $str;
  
	foreach $category (@categories) {
    
		foreach $key (sort keys %{ $form->{$category} }) {
			# push description onto array
      
			$str = ($form->{l_heading}) ? $form->{padding} : "";
      
			if ($form->{$category}{$key}{charttype} eq "A") {
				$str .= 
					($form->{l_accno}) 
					? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" 
					: "$form->{$category}{$key}{description}";
			}
			if ($form->{$category}{$key}{charttype} eq "H") {
				if ($account{$category}{subtotal} 
						&& $form->{l_subtotal}) {

					$dash = "- ";
					push(@{$form->{"$account{$category}{label}_account"}}, 
						"$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");

					push(@{$form->{"$account{$category}{labels}_this_period"}}, 
						$form->format_amount(
							$myconfig, 
							$account{$category}{subthis} 
								* $account{$category}{ml}, 
							$form->{decimalplaces}, 
							$dash));
	  
					if ($last_period) {
						# Chris T:  Giving up on
						# Formatting this one :-(
						push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
					}
	  
				}
	
				$str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";

				$account{$category}{subthis} 
					= $form->{$category}{$key}{this};
				$account{$category}{sublast} 
					= $form->{$category}{$key}{last};
				$account{$category}{subdescription} 
					= $form->{$category}{$key}{description};
				$account{$category}{subtotal} = 1;

				$form->{$category}{$key}{this} = 0;
				$form->{$category}{$key}{last} = 0;

				next unless $form->{l_heading};

				$dash = " ";
			}
      
			push(@{$form->{"$account{$category}{label}_account"}}, 
				$str);
      
			if ($form->{$category}{$key}{charttype} eq 'A') {
					$form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};

					$dash = "- ";
			}
      
			push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
      
			# add amount or - for last period
			if ($last_period) {
				$form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};

				push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
			}
		}

		$str = ($form->{l_heading}) ? $form->{padding} : "";
		if ($account{$category}{subtotal} && $form->{l_subtotal}) {
			push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
			push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));

			if ($last_period) {
				push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
			}
		}
      
	}


	# totals for income and expenses
	$form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
	$form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});

	# total for income/loss
	$form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
  
	if ($last_period) {
		# total for income/loss
		$form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
    
		# totals for income and expenses for last_period
		$form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
  		$form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");

	}


	$form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
	$form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
	$form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");

}


sub balance_sheet {
	my ($self, $myconfig, $form) = @_;
  
	my $dbh = $form->{dbh};

	my $last_period = 0;
	my @categories = qw(A L Q);

	my $null;
  
	if ($form->{asofdate}) {
		if ($form->{asofyear} && $form->{asofmonth}) {
			if ($form->{asofdate} !~ /\W/) {
				$form->{asofdate} 
					= "$form->{asofyear}$form->{asofmonth}$form->{asofdate}";
			}
		}
	} else {
		if ($form->{asofyear} && $form->{asofmonth}) {
			($null, $form->{asofdate}) 
				= $form->from_to(
					$form->{asofyear}, $form->{asofmonth});
		}
	}
  
	# if there are any dates construct a where
	if ($form->{asofdate}) {
    
		$form->{this_period} = "$form->{asofdate}";
		$form->{period} = "$form->{asofdate}";
    
	}

	$form->{decimalplaces} *= 1;

	&get_accounts(
		$dbh, $last_period, "", $form->{asofdate}, $form, 
		\@categories, 1);
  
	if ($form->{compareasofdate}) {
		if ($form->{compareasofyear} && $form->{compareasofmonth}) {
			if ($form->{compareasofdate} !~ /\W/) {
				$form->{compareasofdate} = "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}";
			}
		}
	} else {
		if ($form->{compareasofyear} && $form->{compareasofmonth}) {
			($null, $form->{compareasofdate}) = $form->from_to(
				$form->{compareasofyear}, 
				$form->{compareasofmonth});
		}
	}
  
	# if there are any compare dates
	if ($form->{compareasofdate}) {

		$last_period = 1;
		&get_accounts(
			$dbh, $last_period, "", $form->{compareasofdate}, 
			$form, \@categories, 1);
  
		$form->{last_period} = "$form->{compareasofdate}";

	}  

  
	$dbh->commit;


	# now we got $form->{A}{accno}{ }    assets
	# and $form->{L}{accno}{ }           liabilities
	# and $form->{Q}{accno}{ }           equity
	# build asset accounts
  
	my $str;
	my $key;
  
	my %account  = ( 
		'A' => {
			'label' => 'asset',
			'labels' => 'assets',
			'ml' => -1 },
		'L' => { 
			'label' => 'liability',
			'labels' => 'liabilities',
			'ml' => 1 },
		'Q' => { 
			'label' => 'equity',
			'labels' => 'equity',
			'ml' => 1 }
		);	    


	foreach $category (@categories) {			    

		foreach $key (sort keys %{ $form->{$category} }) {

			$str = ($form->{l_heading}) ? $form->{padding} : "";

			if ($form->{$category}{$key}{charttype} eq "A") {
				$str .= 
					($form->{l_accno}) 
					? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" 
					: "$form->{$category}{$key}{description}";
			}
			if ($form->{$category}{$key}{charttype} eq "H") {
				if ($account{$category}{subtotal} 
						&& $form->{l_subtotal}) {

					$dash = "- ";
					push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
					push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
	  
					if ($last_period) {
						push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
					}
				}

				$str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
	
				$account{$category}{subthis} = $form->{$category}{$key}{this};
				$account{$category}{sublast} = $form->{$category}{$key}{last};
				$account{$category}{subdescription} = $form->{$category}{$key}{description};
				$account{$category}{subtotal} = 1;
	
				$form->{$category}{$key}{this} = 0;
				$form->{$category}{$key}{last} = 0;

				next unless $form->{l_heading};

				$dash = " ";
			}
      
			# push description onto array
			push(@{$form->{"$account{$category}{label}_account"}}, 
				$str);
      
			if ($form->{$category}{$key}{charttype} eq 'A') {
				$form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
				$dash = "- ";
			}

			push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
      
			if ($last_period) {
				$form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};

				push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
			}
		}

		$str = ($form->{l_heading}) ? $form->{padding} : "";
		if ($account{$category}{subtotal} && $form->{l_subtotal}) {
			push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
			push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
      
			if ($last_period) {
				push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
			}
		}

	}

  
	# totals for assets, liabilities
	$form->{total_assets_this_period} = $form->round_amount(
		$form->{total_assets_this_period}, $form->{decimalplaces});
	$form->{total_liabilities_this_period} = $form->round_amount(
		$form->{total_liabilities_this_period}, 
		$form->{decimalplaces});
	$form->{total_equity_this_period} = $form->round_amount(
		$form->{total_equity_this_period}, $form->{decimalplaces});

	# calculate earnings
	$form->{earnings_this_period} = $form->{total_assets_this_period} 
		- $form->{total_liabilities_this_period} 
		- $form->{total_equity_this_period};

	push(@{$form->{equity_this_period}}, 
		$form->format_amount(
			$myconfig, $form->{earnings_this_period}, 
			$form->{decimalplaces}, "- "));
  
	$form->{total_equity_this_period} = $form->round_amount(
		$form->{total_equity_this_period} 
			+ $form->{earnings_this_period}, 
		$form->{decimalplaces});
  
	# add liability + equity
	$form->{total_this_period} = $form->format_amount(
		$myconfig, 
		$form->{total_liabilities_this_period} 
			+ $form->{total_equity_this_period}, 
		$form->{decimalplaces}, "- ");


	if ($last_period) {
		# totals for assets, liabilities
		$form->{total_assets_last_period} = $form->round_amount(
			$form->{total_assets_last_period}, 
			$form->{decimalplaces});
		$form->{total_liabilities_last_period} = $form->round_amount(
			$form->{total_liabilities_last_period}, 
			$form->{decimalplaces});
		$form->{total_equity_last_period} = $form->round_amount(
			$form->{total_equity_last_period}, 
			$form->{decimalplaces});

		# calculate retained earnings
		$form->{earnings_last_period}
			= $form->{total_assets_last_period} 
			- $form->{total_liabilities_last_period} 
			- $form->{total_equity_last_period};

		push(@{$form->{equity_last_period}}, 
			$form->format_amount(
				$myconfig,$form->{earnings_last_period}, 
				$form->{decimalplaces}, "- "));
    
		$form->{total_equity_last_period} = $form->round_amount(
			$form->{total_equity_last_period} 
				+ $form->{earnings_last_period}, 
			$form->{decimalplaces});

		# add liability + equity
		$form->{total_last_period} = $form->format_amount(
			$myconfig, 
			$form->{total_liabilities_last_period} 
				+ $form->{total_equity_last_period}, 
			$form->{decimalplaces}, "- ");

	}

  
	$form->{total_liabilities_last_period} = $form->format_amount(
		$myconfig, $form->{total_liabilities_last_period}, 
		$form->{decimalplaces}, "- ") 
			if ($form->{total_liabilities_last_period});
  
	$form->{total_equity_last_period} = $form->format_amount(
		$myconfig, $form->{total_equity_last_period}, 
		$form->{decimalplaces}, "- ") 
			if ($form->{total_equity_last_period});
  
	$form->{total_assets_last_period} = $form->format_amount(
		$myconfig, $form->{total_assets_last_period}, 
		$form->{decimalplaces}, "- ") 
			if ($form->{total_assets_last_period});
  
	$form->{total_assets_this_period} = $form->format_amount(
		$myconfig, $form->{total_assets_this_period}, 
		$form->{decimalplaces}, "- ");
  
	$form->{total_liabilities_this_period} = $form->format_amount(
		$myconfig, $form->{total_liabilities_this_period}, 
		$form->{decimalplaces}, "- ");
  
	$form->{total_equity_this_period} = $form->format_amount(
		$myconfig, $form->{total_equity_this_period}, 
		$form->{decimalplaces}, "- ");

}


sub get_accounts {
	my  ($dbh, $last_period, $fromdate, $todate, $form, $categories, 
		$excludeyearend) = @_;

	my $department_id;
	my $project_id;
  
	($null, $department_id) = split /--/, $form->{department};
	($null, $project_id) = split /--/, $form->{projectnumber};

	my $query;
	my $dpt_where;
	my $dpt_join;
	my $project;
	my $where = "1 = 1";
	my $glwhere = "";
	my $subwhere = "";
	my $yearendwhere = "1 = 1";
	my $item;
 
	my $category = "AND (";
	foreach $item (@{ $categories }) {
		$category .= qq|c.category = |.$dbh->quote($item).qq| OR |;
	}
	$category =~ s/OR $/\)/;


	# get headings
	$query = qq|
		  SELECT accno, description, category
		    FROM chart c
		   WHERE c.charttype = 'H' $category
		ORDER BY c.accno|;

	if ($form->{accounttype} eq 'gifi'){
	  $query = qq|
		  SELECT g.accno, g.description, c.category
		    FROM gifi g
		    JOIN chart c ON (c.gifi_accno = g.accno)
		   WHERE c.charttype = 'H' $category
		ORDER BY g.accno|;
	}

	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);
  
	my @headingaccounts = ();
	while ($ref = $sth->fetchrow_hashref(NAME_lc)){
		$form->{$ref->{category}}{$ref->{accno}}{description} 
			= "$ref->{description}";

		$form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
		$form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
    
		push @headingaccounts, $ref->{accno};
	}

	$sth->finish;

	if ($form->{method} eq 'cash' && !$todate) {
		($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
	}

	if ($fromdate) {
		if ($form->{method} eq 'cash') {
			$subwhere .= " AND transdate >= ".
				$dbh->quote($fromdate);
			$glwhere = " AND ac.transdate >= ".
				$dbh->quote($fromdate);
		} else {
			$where .= " AND ac.transdate >= ".
				$dbh->quote($fromdate);
		}
	}

	if ($todate) {
		$where .= " AND ac.transdate <= ".$dbh->quote($todate);
		$subwhere .= " AND transdate <= ".$dbh->quote($todate);
		$yearendwhere = "ac.transdate < ".$dbh->quote($todate);
	}

	if ($excludeyearend) {
		$ywhere = "
			AND ac.trans_id NOT IN (SELECT trans_id FROM yearend)";
	       
		if ($todate) {
			$ywhere = " 
				AND ac.trans_id NOT IN 
				(SELECT trans_id FROM yearend
				  WHERE transdate <= ".dbh->quote($todate).")";
		}
       
		if ($fromdate) {
			$ywhere = "
				AND ac.trans_id NOT IN 
				(SELECT trans_id FROM yearend
				  WHERE transdate >= ".$dbh->quote($fromdate).
				")";
			if ($todate) {
				$ywhere = " 
					AND ac.trans_id NOT IN
					(SELECT trans_id FROM yearend
					WHERE transdate >= "
						.$dbh->quote($fromdate)."
					      AND transdate <= ".
						$dbh->quote($todate).")";
			}
		}
	}

	if ($department_id) {
		$dpt_join = qq|
			JOIN department t ON (a.department_id = t.id)|;
		$dpt_where = qq|
			AND t.id = $department_id|;
	}

	if ($project_id) {
		$project = qq|
			AND ac.project_id = $project_id|;
	}


	if ($form->{accounttype} eq 'gifi') {
    
 		if ($form->{method} eq 'cash') {

			$query = qq|
				  SELECT g.accno, sum(ac.amount) AS amount,
				         g.description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN ar a ON (a.id = ac.trans_id)
				    JOIN gifi g ON (g.accno = c.gifi_accno)
				    $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         AND ac.trans_id IN (
				         SELECT trans_id
				           FROM acc_trans
					   JOIN chart ON (chart_id = id)
				          WHERE link LIKE '%AR_paid%'
				                $subwhere)
				$project
				GROUP BY g.accno, g.description, c.category
		 
				UNION ALL

				  SELECT '' AS accno, SUM(ac.amount) AS amount,
				         '' AS description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN ar a ON (a.id = ac.trans_id)
				    $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         AND c.gifi_accno = '' AND 
				         ac.trans_id IN
				         (SELECT trans_id FROM acc_trans
				            JOIN chart ON (chart_id = id)
				           WHERE link LIKE '%AR_paid%'
				         $subwhere) $project
				GROUP BY c.category

				UNION ALL

				  SELECT g.accno, sum(ac.amount) AS amount,
				         g.description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN ap a ON (a.id = ac.trans_id)
				    JOIN gifi g ON (g.accno = c.gifi_accno)
				$dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         AND ac.trans_id IN
				         (SELECT trans_id FROM acc_trans
				            JOIN chart ON (chart_id = id)
				           WHERE link LIKE '%AP_paid%'
				                 $subwhere) $project
				GROUP BY g.accno, g.description, c.category
		 
				UNION ALL
       
				  SELECT '' AS accno, SUM(ac.amount) AS amount,
				         '' AS description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN ap a ON (a.id = ac.trans_id)
				 $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         AND c.gifi_accno = '' 
				         AND ac.trans_id IN
				         (SELECT trans_id FROM acc_trans
				            JOIN chart ON (chart_id = id)
				   WHERE link LIKE '%AP_paid%' $subwhere)
				         $project
				GROUP BY c.category

				UNION ALL

				  SELECT g.accno, sum(ac.amount) AS amount,
				         g.description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN gifi g ON (g.accno = c.gifi_accno)
				    JOIN gl a ON (a.id = ac.trans_id)
				$dpt_join
				   WHERE $where $ywhere $glwhere $dpt_where
				         $category AND NOT 
				         (c.link = 'AR' OR c.link = 'AP')
				         $project
				GROUP BY g.accno, g.description, c.category
		 
				UNION ALL

				  SELECT '' AS accno, SUM(ac.amount) AS amount,
				         '' AS description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN gl a ON (a.id = ac.trans_id)
				$dpt_join
				   WHERE $where $ywhere $glwhere $dpt_where
				         $category AND c.gifi_accno = ''
				         AND NOT 
				         (c.link = 'AR' OR c.link = 'AP')
				         $project
				GROUP BY c.category|;

			if ($excludeyearend) {


				$query .= qq|

					UNION ALL

					  SELECT g.accno, 
					         sum(ac.amount) AS amount,
					         g.description, c.category
					    FROM yearend y
					    JOIN gl a ON (a.id = y.trans_id)
					    JOIN acc_trans ac 
					         ON (ac.trans_id = y.trans_id)
					    JOIN chart c 
					         ON (c.id = ac.chart_id)
					    JOIN gifi g 
					         ON (g.accno = c.gifi_accno) 
					$dpt_join
					   WHERE $yearendwhere 
					         AND c.category = 'Q' 
					         $dpt_where $project
					GROUP BY g.accno, g.description, 
					         c.category|;
			}

		} else {

			if ($department_id) {
				$dpt_join = qq|
					JOIN dpt_trans t 
					     ON (t.trans_id = ac.trans_id)|;
				$dpt_where = qq|
					AND t.department_id = |.
						$dbh->quote($department_id);
			}

			$query = qq|
				  SELECT g.accno, SUM(ac.amount) AS amount,
				         g.description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				    JOIN gifi g ON (c.gifi_accno = g.accno)
				         $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         $project
				GROUP BY g.accno, g.description, c.category
	      
				UNION ALL
	   
				  SELECT '' AS accno, SUM(ac.amount) AS amount,
				         '' AS description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				         $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         AND c.gifi_accno = '' $project
				GROUP BY c.category|;

			if ($excludeyearend) {

				$query .= qq|

						UNION ALL

						  SELECT g.accno, 
						         sum(ac.amount) 
						         AS amount,
						         g.description, 
						         c.category
						    FROM yearend y
						    JOIN gl a 
						         ON (a.id = y.trans_id)
						    JOIN acc_trans ac 
						         ON (ac.trans_id = 
						         y.trans_id)
						    JOIN chart c 
						         ON 
						         (c.id = ac.chart_id)
						    JOIN gifi g 
						         ON (g.accno = 
						         c.gifi_accno)
						         $dpt_join
						   WHERE $yearendwhere
						         AND c.category = 'Q'
						         $dpt_where $project
						GROUP BY g.accno, 
						         g.description, 
						         c.category|;
			}
		}
    
	} else {    # standard account

		if ($form->{method} eq 'cash') {

  			$query = qq|
			  SELECT c.accno, sum(ac.amount) AS amount,
			         c.description, c.category
			    FROM acc_trans ac
			    JOIN chart c ON (c.id = ac.chart_id)
			    JOIN ar a ON (a.id = ac.trans_id) $dpt_join
			   WHERE $where $ywhere $dpt_where $category 
			         AND ac.trans_id IN (
			         SELECT trans_id FROM acc_trans
			           JOIN chart ON (chart_id = id)
			          WHERE link LIKE '%AR_paid%' $subwhere)
			         $project
			GROUP BY c.accno, c.description, c.category

			UNION ALL
	
			  SELECT c.accno, sum(ac.amount) AS amount,
			         c.description, c.category
			    FROM acc_trans ac
			    JOIN chart c ON (c.id = ac.chart_id)
			    JOIN ap a ON (a.id = ac.trans_id) $dpt_join
			   WHERE $where $ywhere $dpt_where $category
			         AND ac.trans_id IN (
			         SELECT trans_id FROM acc_trans
			           JOIN chart ON (chart_id = id)
			          WHERE link LIKE '%AP_paid%' $subwhere)
			         $project
			GROUP BY c.accno, c.description, c.category
		 
			UNION ALL

			  SELECT c.accno, sum(ac.amount) AS amount,
			         c.description, c.category
			    FROM acc_trans ac
			    JOIN chart c ON (c.id = ac.chart_id)
			    JOIN gl a ON (a.id = ac.trans_id) $dpt_join
			   WHERE $where $ywhere $glwhere $dpt_where $category
			         AND NOT (c.link = 'AR' OR c.link = 'AP')
			         $project
			GROUP BY c.accno, c.description, c.category|;

			if ($excludeyearend) {

        # this is for the yearend
	
				$query .= qq|

 					UNION ALL

					  SELECT c.accno, 
					         sum(ac.amount) AS amount,
					         c.description, c.category
					    FROM yearend y
					    JOIN gl a ON (a.id = y.trans_id)
					    JOIN acc_trans ac 
					         ON (ac.trans_id = y.trans_id)
					    JOIN chart c 
					         ON (c.id = ac.chart_id)
					         $dpt_join
					   WHERE $yearendwhere AND 
					         c.category = 'Q' $dpt_where
					         $project
					GROUP BY c.accno, c.description, 
					         c.category|;
			}

		} else {
     
			if ($department_id) {
				$dpt_join = qq|
					JOIN dpt_trans t 
					     ON (t.trans_id = ac.trans_id)|;
				$dpt_where = qq| AND t.department_id = |.
					$dbh->quote($department_id);
			}

	
			$query = qq|
				  SELECT c.accno, sum(ac.amount) AS amount,
				         c.description, c.category
				    FROM acc_trans ac
				    JOIN chart c ON (c.id = ac.chart_id)
				         $dpt_join
				   WHERE $where $ywhere $dpt_where $category
				         $project
				GROUP BY c.accno, c.description, c.category|;

			if ($excludeyearend) {

				$query .= qq|

					UNION ALL
       
					  SELECT c.accno, 
					         sum(ac.amount) AS amount,
					         c.description, c.category
					    FROM yearend y
					    JOIN gl a ON (a.id = y.trans_id)
					    JOIN acc_trans ac 
					         ON (ac.trans_id = y.trans_id)
					    JOIN chart c 
					         ON (c.id = ac.chart_id)
					         $dpt_join
					   WHERE $yearendwhere AND 
					         c.category = 'Q' $dpt_where
					         $project
					GROUP BY c.accno, c.description, 
					         c.category|;
			}
		}
	}

	my @accno;
	my $accno;
	my $ref;
  
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

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

		# get last heading account
		@accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
		$accno = pop @accno;
		if ($accno && ($accno ne $ref->{accno}) ) {
			if ($last_period) {
				$form->{$ref->{category}}{$accno}{last} 
					+= $ref->{amount};
			} else {
				$form->{$ref->{category}}{$accno}{this} 
					+= $ref->{amount};
			}
		}
    
		$form->{$ref->{category}}{$ref->{accno}}{accno} 
			= $ref->{accno};
		$form->{$ref->{category}}{$ref->{accno}}{description} 
			= $ref->{description};
		$form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
    
		if ($last_period) {
			$form->{$ref->{category}}{$ref->{accno}}{last} 
				+= $ref->{amount};
		} else {
			$form->{$ref->{category}}{$ref->{accno}}{this} 
				+= $ref->{amount};
		}
	}
	$sth->finish;

  
	# remove accounts with zero balance
	foreach $category (@{ $categories }) {
		foreach $accno (keys %{ $form->{$category} }) {
			$form->{$category}{$accno}{last} = $form->round_amount(
				$form->{$category}{$accno}{last}, 
				$form->{decimalplaces});
			$form->{$category}{$accno}{this} = $form->round_amount(
				$form->{$category}{$accno}{this}, 
				$form->{decimalplaces});

			delete $form->{$category}{$accno} 
				if ($form->{$category}{$accno}{this} == 0 
					&& $form->{$category}{$accno}{last} 
						== 0);
		}
	}

}



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

	my $dbh = $form->{dbh};

	my ($query, $sth, $ref);
	my %balance = ();
	my %trb = ();
	my $null;
	my $department_id;
	my $project_id;
	my @headingaccounts = ();
	my $dpt_where;
	my $dpt_join;
	my $project;

	my $where = "1 = 1";
	my $invwhere = $where;
  
	($null, $department_id) = split /--/, $form->{department};
	($null, $project_id) = split /--/, $form->{projectnumber};

	if ($department_id) {
		$dpt_join = qq|
			JOIN dpt_trans t ON (ac.trans_id = t.trans_id)|;
		$dpt_where = qq|
			AND t.department_id = |.$dbh->quote($department_id);
	}
  
  
	if ($project_id) {
		$project = qq|
			AND ac.project_id = |.$dbh->quote($project_id);
	}
  
	($form->{fromdate}, $form->{todate}) = $form->from_to(
		$form->{year}, $form->{month}, $form->{interval}) 
			if $form->{year} && $form->{month}; 
   
	# get beginning balances
	if ($form->{fromdate}) {

		if ($form->{accounttype} eq 'gifi') {
      
			$query = qq|
				  SELECT g.accno, c.category, 
				         SUM(ac.amount) AS amount,
				         g.description, c.contra
				    FROM acc_trans ac
				    JOIN chart c ON (ac.chart_id = c.id)
				    JOIN gifi g ON (c.gifi_accno = g.accno)
				         $dpt_join
				   WHERE ac.transdate < '$form->{fromdate}'
				         $dpt_where $project
				GROUP BY g.accno, c.category, g.description, 
				         c.contra|;
   
		} else {
      
			$query = qq|
				  SELECT c.accno, c.category, 
				         SUM(ac.amount) AS amount,
				         c.description, c.contra
				    FROM acc_trans ac
				    JOIN chart c ON (ac.chart_id = c.id)
				         $dpt_join
				   WHERE ac.transdate < '$form->{fromdate}'
				         $dpt_where $project
				GROUP BY c.accno, c.category, c.description, 
				         c.contra|;
		  
		}

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

		while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
			$ref->{amount} = $form->round_amount($ref->{amount}, 
				2);
			$balance{$ref->{accno}} = $ref->{amount};

			if ($form->{all_accounts}) {
				$trb{$ref->{accno}}{description} 
					= $ref->{description};
				$trb{$ref->{accno}}{charttype} 
					= 'A';
				$trb{$ref->{accno}}{category} 
					= $ref->{category};
				$trb{$ref->{accno}}{contra} 
					= $ref->{contra};
			}

		}
		$sth->finish;

	}
  

	# get headings
	$query = qq|
		  SELECT c.accno, c.description, c.category FROM chart c
		   WHERE c.charttype = 'H'
		ORDER by c.accno|;

	if ($form->{accounttype} eq 'gifi'){
		$query = qq|
			  SELECT g.accno, g.description, c.category, c.contra
			    FROM gifi g
			    JOIN chart c ON (c.gifi_accno = g.accno)
			   WHERE c.charttype = 'H'
			ORDER BY g.accno|;
	}

	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);
  
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$trb{$ref->{accno}}{description} = $ref->{description};
		$trb{$ref->{accno}}{charttype} = 'H';
		$trb{$ref->{accno}}{category} = $ref->{category};
		$trb{$ref->{accno}}{contra} = $ref->{contra};
   
		push @headingaccounts, $ref->{accno};
	}

	$sth->finish;


	if ($form->{fromdate} || $form->{todate}) {
		if ($form->{fromdate}) {
			$where .= " AND ac.transdate >= "
				.$dbh->quote($form->{fromdate});
			$invwhere .= " AND a.transdate >= ".
				$dbh->quote($form->{fromdate});
		}
		if ($form->{todate}) {
			$where .= " AND ac.transdate <= ".
				$dbh->quote($form->{todate});
			$invwhere .= " AND a.transdate <= "
				.$dbh->quote($form->{todate});
		}
	}


	if ($form->{accounttype} eq 'gifi') {

		$query = qq|
			  SELECT g.accno, g.description, c.category,
			         SUM(ac.amount) AS amount, c.contra
			    FROM acc_trans ac
			    JOIN chart c ON (c.id = ac.chart_id)
			    JOIN gifi g ON (c.gifi_accno = g.accno)
			         $dpt_join
			   WHERE $where $dpt_where $project
			GROUP BY g.accno, g.description, c.category, c.contra
			ORDER BY accno|;
    
	} else {

		$query = qq|
			  SELECT c.accno, c.description, c.category,
			         SUM(ac.amount) AS amount, c.contra
			    FROM acc_trans ac
			    JOIN chart c ON (c.id = ac.chart_id)
			         $dpt_join
			   WHERE $where $dpt_where $project
			GROUP BY c.accno, c.description, c.category, c.contra
			ORDER BY accno|;

	}

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

	# prepare query for each account
	$query = qq|
		SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac
		          JOIN chart c ON (c.id = ac.chart_id)
		               $dpt_join
		          WHERE $where $dpt_where $project AND ac.amount < 0
		                 AND c.accno = ?) AS debit,
		       (SELECT SUM(ac.amount) FROM acc_trans ac
		          JOIN chart c ON (c.id = ac.chart_id)
		               $dpt_join
		         WHERE $where $dpt_where $project AND ac.amount > 0
		               AND c.accno = ?) AS credit |;

	if ($form->{accounttype} eq 'gifi') {

  	$query = qq|
		SELECT (SELECT SUM(ac.amount) * -1
		          FROM acc_trans ac
		          JOIN chart c ON (c.id = ac.chart_id)
		               $dpt_join
		         WHERE $where $dpt_where $project AND ac.amount < 0
		               AND c.gifi_accno = ?) AS debit,
		
		       (SELECT SUM(ac.amount)
		          FROM acc_trans ac
		          JOIN chart c ON (c.id = ac.chart_id)
		               $dpt_join
		         WHERE $where $dpt_where $project AND ac.amount > 0
		               AND c.gifi_accno = ?) AS credit|;
  
	}
  
	$drcr = $dbh->prepare($query);

	# calculate debit and credit for the period
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$trb{$ref->{accno}}{description} = $ref->{description};
		$trb{$ref->{accno}}{charttype} = 'A';
		$trb{$ref->{accno}}{category} = $ref->{category};
		$trb{$ref->{accno}}{contra} = $ref->{contra};
		$trb{$ref->{accno}}{amount} += $ref->{amount};
	}
	$sth->finish;

	my ($debit, $credit);
  
	foreach my $accno (sort keys %trb) {
		$ref = ();
    
		$ref->{accno} = $accno;
		for (qw(description category contra charttype amount)) { 
			$ref->{$_} = $trb{$accno}{$_}; 
		}
    
		$ref->{balance} = $balance{$ref->{accno}};

		if ($trb{$accno}{charttype} eq 'A') {
			if ($project_id) {

				if ($ref->{amount} < 0) {
					$ref->{debit} = $ref->{amount} * -1;
				} else {
					$ref->{credit} = $ref->{amount};
				}
				next if $form->round_amount(
					$ref->{amount}, 2) == 0;

			} else {
	
				# get DR/CR
				$drcr->execute($ref->{accno}, $ref->{accno})
					|| $form->dberror($query);
	
				($debit, $credit) = (0,0);
				while (($debit, $credit) 
						= $drcr->fetchrow_array) {
					$ref->{debit} += $debit;
					$ref->{credit} += $credit;
				}
				$drcr->finish;

			}

			$ref->{debit} = $form->round_amount($ref->{debit}, 2);
			$ref->{credit} 
				= $form->round_amount($ref->{credit}, 2);
    
			if (!$form->{all_accounts}) {
				next 
					if $form->round_amount(
						$ref->{debit} + $ref->{credit},
						2) 
					== 0;
			}
		}

		# add subtotal
		@accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
		$accno = pop @accno;
		if ($accno) {
			$trb{$accno}{debit} += $ref->{debit};
			$trb{$accno}{credit} += $ref->{credit};
		}

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

	$dbh->commit;

	# debits and credits for headings
	foreach $accno (@headingaccounts) {
		foreach $ref (@{ $form->{TB} }) {
			if ($accno eq $ref->{accno}) {
				$ref->{debit} = $trb{$accno}{debit};
				$ref->{credit} = $trb{$accno}{credit};
			}
		}
	}

}


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

	my $dbh = $form->{dbh};
	my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';

	my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
	($form->{currencies}) = $dbh->selectrow_array($query);
  
	($null, $form->{todate}) 
		= $form->from_to($form->{year}, $form->{month}) 
		if $form->{year} && $form->{month};
  
	if (! $form->{todate}) {
		$query = qq|SELECT current_date|;
		($form->{todate}) = $dbh->selectrow_array($query);
	}
    
	my $where = "1 = 1";
	my $name;
	my $null;
	my $ref;
	my $transdate = ($form->{overdue}) ? "duedate" : "transdate";

	if ($form->{"$form->{ct}_id"}) {
		$where .= qq| AND ct.id = |.
			$dbh->quote($form->{"$form->{ct}_id"});
	} else {
		if ($form->{$form->{ct}} ne "") {
			$name = $dbh->quote($form->like(
				lc $form->{$form->{ct}}));
			$where .= qq| AND lower(ct.name) LIKE $name| 
				if $form->{$form->{ct}};
		}
	}

	if ($form->{department}) {
		($null, $department_id) = split /--/, $form->{department};
		$where .= qq| AND a.department_id = |.
			$dbh->quote($department_id);
	}
  
	# select outstanding vendors or customers, depends on $ct
	$query = qq|
		  SELECT DISTINCT ct.id, ct.name, ct.language_code
		    FROM $form->{ct} ct
		    JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id)
		   WHERE $where AND a.paid != a.amount 
		         AND (a.$transdate <= ?)
		ORDER BY ct.name|;
	my $sth = $dbh->prepare($query);
	$sth->execute($form->{todate}) || $form->dberror;
  
	my @ot = ();
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		push @ot, $ref;
	}
	$sth->finish;

	my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';

	my $todate = $dbh->quote($form->{todate});
	my %interval = ( 
		'c0' => "(date $todate - interval '0 days')",
		'c30' => "(date $todate - interval '30 days')",
		'c60' => "(date $todate - interval '60 days')",
		'c90' => "(date $todate - interval '90 days')" 
		);

  
		    
	# for each company that has some stuff outstanding
	$form->{currencies} ||= ":";
  
      
	$where = qq|a.paid != a.amount AND c.id = ? AND a.curr = ?|;
	    
	if ($department_id) {
		$where .= qq| AND a.department_id = |.
			$dbh->quote($department_id);
	}
  
	$query = "";
	my $union = "";

	if ($form->{c0}) {
		$query .= qq|
			SELECT c.id AS ctid, c.$form->{ct}number, c.name,
			       c.address1, c.address2, c.city, c.state, 
			       c.zipcode, c.country, c.contact, c.email,
		               c.phone as $form->{ct}phone, 
			       c.fax as $form->{ct}fax,
			       c.$form->{ct}number, 
			       c.taxnumber as $form->{ct}taxnumber,
		               a.invnumber, a.transdate, a.till, a.ordnumber, 
			       a.ponumber, a.notes, (a.amount - a.paid) as c0, 
			       0.00 as c30, 0.00 as c60, 0.00 as c90, 
			       a.duedate, a.invoice, a.id, a.curr,
			       (SELECT $buysell FROM exchangerate e
			         WHERE a.curr = e.curr
			              AND e.transdate = a.transdate) 
			       AS exchangerate
			  FROM $form->{arap} a
			  JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
			 WHERE $where AND ( a.$transdate <= $interval{c0}
			       AND a.$transdate >= $interval{c30} )|;

		$union = qq|UNION|;

	}
	  
	if ($form->{c30}) {

		$query .= qq|

			$union

			SELECT c.id AS ctid, c.$form->{ct}number, c.name,
			       c.address1, c.address2, c.city, c.state, 
			       c.zipcode, c.country, c.contact, c.email,
			       c.phone as $form->{ct}phone, 
			       c.fax as $form->{ct}fax, c.$form->{ct}number, 
			       c.taxnumber as $form->{ct}taxnumber,
			       a.invnumber, a.transdate, a.till, a.ordnumber, 
			       a.ponumber, a.notes, 0.00 as c0, 
			       (a.amount - a.paid) as c30, 0.00 as c60, 
			       0.00 as c90, a.duedate, a.invoice, a.id, a.curr,
			       (SELECT $buysell FROM exchangerate e
			         WHERE a.curr = e.curr
			               AND e.transdate = a.transdate) 
			       AS exchangerate
			  FROM $form->{arap} a
			  JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
			 WHERE $where AND (a.$transdate < $interval{c30}
			        AND a.$transdate >= $interval{c60})|;

		$union = qq|UNION|;

	}

	if ($form->{c60}) {

		$query .= qq|
			$union
    
			SELECT c.id AS ctid, c.$form->{ct}number, c.name,
			       c.address1, c.address2, c.city, c.state, 
			       c.zipcode, c.country, c.contact, c.email,
			       c.phone as $form->{ct}phone, 
			       c.fax as $form->{ct}fax, c.$form->{ct}number, 
			       c.taxnumber as $form->{ct}taxnumber, 
			       a.invnumber, a.transdate, a.till, a.ordnumber, 
			       a.ponumber, a.notes, 0.00 as c0, 0.00 as c30, 
			       (a.amount - a.paid) as c60, 0.00 as c90,
			       a.duedate, a.invoice, a.id, a.curr,
			       (SELECT $buysell FROM exchangerate e
			         WHERE a.curr = e.curr
			               AND e.transdate = a.transdate) 
			       AS exchangerate
			  FROM $form->{arap} a
			  JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
			 WHERE $where AND (a.$transdate < $interval{c60}
			       AND a.$transdate >= $interval{c90})|;

		$union = qq|UNION|;

	}

	if ($form->{c90}) {

		$query .= qq|
			$union
			SELECT c.id AS ctid, c.$form->{ct}number, c.name,
			       c.address1, c.address2, c.city, c.state, 
			       c.zipcode, c.country, c.contact, c.email,
			       c.phone as $form->{ct}phone, 
			       c.fax as $form->{ct}fax, c.$form->{ct}number, 
			       c.taxnumber as $form->{ct}taxnumber, 
			       a.invnumber, a.transdate, a.till, a.ordnumber, 
			       a.ponumber, a.notes, 0.00 as c0, 0.00 as c30, 
			       0.00 as c60, (a.amount - a.paid) as c90, 
			       a.duedate, a.invoice, a.id, a.curr,
			       (SELECT $buysell FROM exchangerate e
			         WHERE a.curr = e.curr
			               AND e.transdate = a.transdate) 
			       AS exchangerate
			  FROM $form->{arap} a
			  JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
			 WHERE $where
			       AND a.$transdate < $interval{c90}|;
	}
	$query .= qq| ORDER BY ctid, $transdate, invnumber|;
	$sth = $dbh->prepare($query) || $form->dberror($query);

	my @var = ();
  
	if ($form->{c0} + $form->{c30} + $form->{c60} + $form->{c90}) {
		foreach $curr (split /:/, $form->{currencies}) {
    
			foreach $item (@ot) {
    
				@var = ();
				for (qw(c0 c30 c60 c90)) { 
					push @var, ($item->{id}, $curr) 
					if $form->{$_} }
	
				$sth->execute(@var);

				while ($ref = $sth->fetchrow_hashref(NAME_lc)){
					$ref->{module} = 
						($ref->{invoice}) 
						? $invoice 
						: $form->{arap};
					$ref->{module} = 'ps' if $ref->{till};
					$ref->{exchangerate} = 1 
						unless $ref->{exchangerate};
					$ref->{language_code} 
						= $item->{language_code};
					push @{ $form->{AG} }, $ref;
				}
				$sth->finish;

			}
		}
	}

	# get language
	my $query = qq|SELECT * FROM language ORDER BY 2|;
	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

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

	$dbh->commit;

}


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

	my $dbh = $form->{dbh};

	my $query = qq|
		SELECT name, email, cc, bcc FROM $form->{ct} ct
		 WHERE ct.id = ?|;
	$sth = $dbh->prepare($query);
	$sth->execute($form->{"$form->{ct}_id"});
	($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) 
		= $sth->fetchrow_array();
  
	$dbh->commit;

}


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

	my $dbh = $form->{dbh};
	my $ARAP = uc $form->{db};
  
	# get tax accounts
	my $query = qq|
		  SELECT DISTINCT c.accno, c.description
		    FROM chart c
		    JOIN tax t ON (c.id = t.chart_id)
		   WHERE c.link LIKE '%${ARAP}_tax%'
                ORDER BY c.accno|;
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror;

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

	# get gifi tax accounts
	my $query = qq|
		  SELECT DISTINCT g.accno, g.description
		    FROM gifi g
		    JOIN chart c ON (c.gifi_accno= g.accno)
		    JOIN tax t ON (c.id = t.chart_id)
		   WHERE c.link LIKE '%${ARAP}_tax%'
		ORDER BY accno|;
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror;

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

	$dbh->commit;

}



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

	my $dbh = $form->{dbh};

	my ($null, $department_id) = split /--/, $form->{department};
  
	# 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;
  
	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;
  
	if ($form->{db} eq 'ar') {
		$table = "customer";
		$ARAP = "AR";
	}
	if ($form->{db} eq 'ap') {
		$table = "vendor";
		$ARAP = "AP";
	}

	my $transdate = "a.transdate";

	($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 ($form->{method} eq 'cash') {
		$transdate = "a.datepaid";

		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 <= |.$dbh->quote($todate).qq|
			       AND a.paid = a.amount)|;

	}

    
	my $ml = ($form->{db} eq 'ar') ? 1 : -1;
  
	my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
  
	my @a = qw(transdate invnumber name);
	my $sortorder = $form->sort_order(\@a, \%ordinal);

	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 |;

	  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
	      
			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) {
				 $query .= qq|
					UNION

					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 a.datepaid >= '$form->{fromdate}'
					       $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 a.datepaid >= '$form->{fromdate}'
					       $accno AND a.invoice = '1'
					       $cashwhere|;
			}
		}
	}


	if ($form->{report} =~ /nontaxable/) {
    
		if ($form->{summary}) {
			# only gather up non-taxable transactions
			$query = qq|
				SELECT DISTINCT a.id, a.invoice, 
				       $transdate AS transdate, a.invnumber, 
				       n.name, a.netamount, a.till
				  FROM acc_trans ac
				  JOIN $form->{db} a ON (a.id = ac.trans_id)
				  JOIN $table n ON (n.id = a.${table}_id)
				 WHERE $where AND a.netamount = a.amount
				       $cashwhere|;

			if ($form->{fromdate}) {
				if ($cashwhere) {
					 $query .= qq|
						UNION

						SELECT DISTINCT a.id, a.invoice,
						       $transdate AS transdate,
						       a.invnumber, n.name, 
						       a.netamount, a.till
						  FROM acc_trans ac
						  JOIN $form->{db} a 
						       ON (a.id = ac.trans_id)
						  JOIN $table n 
						       ON (n.id = a.${table}_id)
						 WHERE a.datepaid 
						       >= '$form->{fromdate}'
						       AND 
						       a.netamount = a.amount
						       $cashwhere|;
				}
			}
		  
		} else {

			# gather up details for non-taxable transactions
			$query = qq|
				  SELECT a.id, '0' AS invoice, 
				         $transdate AS transdate, a.invnumber, 
				         n.name, a.netamount, 
				         a.notes AS description, a.till
				    FROM acc_trans ac
				    JOIN $form->{db} a ON (a.id = ac.trans_id)
				    JOIN $table n ON (n.id = a.${table}_id)
				   WHERE $where AND a.invoice = '0'
				         AND a.netamount = a.amount $cashwhere
				GROUP BY a.id, $transdate, a.invnumber, n.name, 
				         a.netamount, a.notes, a.till
		
				UNION
		
				  SELECT a.id, '1' AS invoice, 
				         $transdate AS transdate, a.invnumber, 
				         n.name,  sum(ac.sellprice * ac.qty) 
				         * $ml AS netamount, ac.description, 
				         a.till
				    FROM invoice ac
				    JOIN $form->{db} a ON (a.id = ac.trans_id)
				    JOIN $table n ON (n.id = a.${table}_id)
				   WHERE $where AND a.invoice = '1' AND 
				         (a.${table}_id NOT IN 
				         (SELECT ${table}_id FROM ${table}tax t 
				         (${table}_id)
					 ) OR ac.parts_id NOT IN 
				         (SELECT parts_id FROM partstax p 
				         (parts_id))) $cashwhere
				GROUP BY a.id, a.invnumber, $transdate, n.name,
				         ac.description, a.till|;

 			if ($form->{fromdate}) {
				if ($cashwhere) {
					$query .= qq|
						UNION
						SELECT a.id, '0' AS invoice, 
						       $transdate AS transdate,
						       a.invnumber, n.name, 
						       a.netamount, 
						       a.notes AS description, 
						       a.till
						  FROM acc_trans ac
						  JOIN $form->{db} a 
						       ON (a.id = ac.trans_id)
						  JOIN $table n 
						       ON (n.id = a.${table}_id)
						 WHERE a.datepaid 
						       >= '$form->{fromdate}'
						       AND a.invoice = '0'
						       AND a.netamount 
						       = a.amount $cashwhere
						GROUP BY a.id, $transdate, 
						       a.invnumber, n.name, 
						       a.netamount, a.notes, 
						       a.till
		
						UNION
		
						SELECT a.id, '1' AS invoice, 
						       $transdate AS transdate,
						       a.invnumber, n.name,
						       sum(ac.sellprice 
						       * ac.qty) * $ml 
						       AS netamount,
						       ac.description, a.till
						  FROM invoice ac
						  JOIN $form->{db} a 
						       ON (a.id = ac.trans_id)
						  JOIN $table n 
						       ON (n.id = a.${table}_id)
						 WHERE a.datepaid 
						       >= '$form->{fromdate}'
						       AND a.invoice = '1' AND 
						       (a.${table}_id NOT IN 
						       (SELECT ${table}_id 
						          FROM ${table}tax t 
						       (${table}_id)) OR
						       ac.parts_id NOT IN
						       (SELECT parts_id 
						          FROM partstax p 
						       (parts_id)))
						       $cashwhere
						GROUP BY a.id, a.invnumber, 
						       $transdate, n.name,
						       ac.description, a.till|;
				}
			}

		}
	}

  
	$query .= qq| ORDER by $sortorder|;

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

	while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		$ref->{tax} = $form->round_amount($ref->{tax}, 2);
		if ($form->{report} =~ /nontaxable/) {
			push @{ $form->{TR} }, $ref if $ref->{netamount};
		} else {
			push @{ $form->{TR} }, $ref if $ref->{tax};
		}
	}

	$sth->finish;
	$dbh->commit;

}


sub paymentaccounts {
	my ($self, $myconfig, $form) = @_;
 
	my $dbh = $form->{dbh};

	my $ARAP = uc $form->{db};
  
	# get A(R|P)_paid accounts
	my $query = qq|
		SELECT accno, description FROM chart
		 WHERE link LIKE '%${ARAP}_paid%'
		 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($myconfig, $dbh);
  
	$dbh->{dbh};

}

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

	my $dbh = $form->{dbh};

	my $ml = 1;
	if ($form->{db} eq 'ar') {
		$table = 'customer';
		$ml = -1;
	}
	if ($form->{db} eq 'ap') {
		$table = 'vendor';
	}
     

	my $query;
	my $sth;
	my $dpt_join;
	my $where;
	my $var;

	if ($form->{department_id}) {
		$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id)|;

		$where = qq| AND t.department_id = |.
			$dbh->quote($form->{department_id});
	}

	($form->{fromdate}, $form->{todate}) = $form->from_to(
		$form->{year}, $form->{month}, $form->{interval}) 
			if $form->{year} && $form->{month};
  
	if ($form->{fromdate}) {
		$where .= " AND ac.transdate >= "
			.$dbh->quote($form->{fromdate});
	}
	if ($form->{todate}) {
		$where .= " AND ac.transdate <= ".$dbh->quote($form->{todate});
	}
	if (!$form->{fx_transaction}) {
		$where .= " AND ac.fx_transaction = '0'";
	}
  
	if ($form->{description} ne "") {
		$var = $dbh->quote($form->like(lc $form->{description}));
		$where .= " AND lower(c.name) LIKE $var";
	}
	if ($form->{source} ne "") {
		$var = $dbh->quote($form->like(lc $form->{source}));
		$where .= " AND lower(ac.source) LIKE $var";
	}
	if ($form->{memo} ne "") {
		$var = $dbh->quote($form->like(lc $form->{memo}));
		$where .= " AND lower(ac.memo) LIKE $var";
	}
 
	my %ordinal = ( 
		'name' => 1,
		'transdate' => 2,
		'source' => 4,
		'employee' => 6,
		'till' => 7
		);

	my @a = qw(name transdate employee);
	my $sortorder = $form->sort_order(\@a, \%ordinal);
  
	my $glwhere = $where;
	$glwhere =~ s/\(c.name\)/\(g.description\)/;

	# cycle through each id
	foreach my $accno (split(/ /, $form->{paymentaccounts})) {

		$query = qq|
			SELECT id, accno, description
			  FROM chart
			 WHERE accno = ?|;
		$sth = $dbh->prepare($query);
		$sth->execute($accno) || $form->dberror($query);

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

		$query = qq|
			   SELECT c.name, ac.transdate, 
			          sum(ac.amount) * $ml AS paid, ac.source, 
			          ac.memo, e.name AS employee, a.till, a.curr
			     FROM acc_trans ac
			     JOIN $form->{db} a ON (ac.trans_id = a.id)
			     JOIN $table c ON (c.id = a.${table}_id)
			LEFT JOIN employee e ON (a.employee_id = e.id)
			          $dpt_join
			    WHERE ac.chart_id = $ref->{id} $where|;

		if ($form->{till} ne "") {
 			$query .= " AND a.invoice = '1' AND NOT a.till IS NULL";
      
			if ($myconfig->{role} eq 'user') {
				$query .= " AND e.login = '$form->{login}'";
			}
		}

		$query .= qq|
			GROUP BY c.name, ac.transdate, ac.source, ac.memo,
			         e.name, a.till, a.curr|;
		
		if ($form->{till} eq "") {
      
			$query .= qq|
 				UNION
				SELECT g.description, ac.transdate, 
				       sum(ac.amount) * $ml AS paid, ac.source,
				       ac.memo, e.name AS employee, '' AS till,
				       '' AS curr
				  FROM acc_trans ac
				  JOIN gl g ON (g.id = ac.trans_id)
				  LEFT 
				  JOIN employee e ON (g.employee_id = e.id)
				       $dpt_join
				 WHERE ac.chart_id = $ref->{id} $glwhere
				       AND (ac.amount * $ml) > 0
				 GROUP BY g.description, ac.transdate, 
			               ac.source, ac.memo, e.name|;

		}

		$query .= qq| ORDER BY $sortorder|;

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

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

	}
  
	$dbh->commit;
  
}


1;