#=====================================================================
# 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 NOT 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};


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

  my $dbh = $form->dbconnect($myconfig) || $form->dberror();

  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 '%|.$form->{partnumber}.qq|%' |; 
  }
  if ($form->{description}){
    $where .= q|AND p.description ILIKE '%|.$form->{description}.q|%' |;
  }
  $where =~ 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
	INNER 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->disconnect;
    
}



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

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

  # if todate < existing yearends, delete GL and yearends
  my $query = qq|SELECT trans_id FROM yearend
                 WHERE transdate >= '$form->{todate}'|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $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);
  
  # disconnect
  $dbh->disconnect;


  # 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) = @_;

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

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

  
  # disconnect
  $dbh->disconnect;


  # 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) {
	    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) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);

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

  }  

  
  # disconnect
  $dbh->disconnect;


  # 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 = '$item' 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 FROM defaults|);
  }

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

  if ($todate) {
    $where .= " AND ac.transdate <= '$todate'";
    $subwhere .= " AND transdate <= '$todate'";
    $yearendwhere = "ac.transdate < '$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 <= '$todate')";
    }
       
    if ($fromdate) {
      $ywhere = " AND ac.trans_id NOT IN
		 (SELECT trans_id FROM yearend
		  WHERE transdate >= '$fromdate')";
      if ($todate) {
	$ywhere = " AND ac.trans_id NOT IN
		   (SELECT trans_id FROM yearend
		    WHERE transdate >= '$fromdate'
		    AND transdate <= '$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

-- add gl
	
	         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) {

         # this is for the yearend

	 $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 = $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) {

	  # this is for the yearend

	  $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 = $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) {

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

  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->dbconnect($myconfig);

  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 = $department_id
		|;
  }
  
  
  if ($project_id) {
    $project = qq|
                AND ac.project_id = $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 >= '$form->{fromdate}'";
      $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
    }
    if ($form->{todate}) {
      $where .= " AND ac.transdate <= '$form->{todate}'";
      $invwhere .= " AND a.transdate <= '$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});
	
	($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->disconnect;

  # 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) = @_;

  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';

  my $query = qq|SELECT curr FROM defaults|;
  ($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 FROM defaults|;
    ($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 = $form->{"$form->{ct}_id"}|;
  } else {
    if ($form->{$form->{ct}} ne "") {
      $name = $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 = $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 <= '$form->{todate}')
              ORDER BY ct.name|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $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 %interval = ( 'Pg' => {
                        'c0' => "(date '$form->{todate}' - interval '0 days')",
			'c30' => "(date '$form->{todate}' - interval '30 days')",
			'c60' => "(date '$form->{todate}' - interval '60 days')",
			'c90' => "(date '$form->{todate}' - interval '90 days')" },
		  'DB2' => {
		        'c0' => "(date ('$form->{todate}') - 0 days)",
			'c30' => "(date ('$form->{todate}') - 30 days)",
			'c60' => "(date ('$form->{todate}') - 60 days)",
			'c90' => "(date ('$form->{todate}') - 90 days)" }
		);

  $interval{Oracle} = $interval{PgPP} = $interval{Pg};
  
		    
  # 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 = $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{$myconfig->{dbdriver}}{c0}
		  AND a.$transdate >= $interval{$myconfig->{dbdriver}}{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{$myconfig->{dbdriver}}{c30}
		  AND a.$transdate >= $interval{$myconfig->{dbdriver}}{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{$myconfig->{dbdriver}}{c60}
		  AND a.$transdate >= $interval{$myconfig->{dbdriver}}{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{$myconfig->{dbdriver}}{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;

  # disconnect
  $dbh->disconnect;

}


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

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

  my $query = qq|SELECT name, email, cc, bcc
                 FROM $form->{ct} ct
		 WHERE ct.id = $form->{"$form->{ct}_id"}|;
  ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query);
  
  $dbh->disconnect;

}


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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  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->disconnect;

}



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

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

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

  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 FROM defaults|);
    }
    
    $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
		   )
		  |;

  }

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

}


sub paymentaccounts {
  my ($self, $myconfig, $form) = @_;
 
  # connect to database, turn AutoCommit off
  my $dbh = $form->dbconnect_noauto($myconfig);

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

}

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

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

  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 = $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 >= '$form->{fromdate}'";
  }
  if ($form->{todate}) {
    $where .= " AND ac.transdate <= '$form->{todate}'";
  }
  if (!$form->{fx_transaction}) {
    $where .= " AND ac.fx_transaction = '0'";
  }
  
  if ($form->{description} ne "") {
    $var = $form->like(lc $form->{description});
    $where .= " AND lower(c.name) LIKE '$var'";
  }
  if ($form->{source} ne "") {
    $var = $form->like(lc $form->{source});
    $where .= " AND lower(ac.source) LIKE '$var'";
  }
  if ($form->{memo} ne "") {
    $var = $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 = '$accno'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $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 "") {
# don't need gl for a till
      
      $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->disconnect;
  
}


1;