#=====================================================================
# 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) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors: Jim Rawlings <jim@your-dba.com>
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Administration module
#    Chart of Accounts
#    template routines
#    preferences
#
#======================================================================

package AM;


sub get_account {

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

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

	my $query = qq|SELECT accno, description, charttype, gifi_accno,
						  category, link, contra
					 FROM chart
					WHERE id = $form->{id}|;

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

	my $ref = $sth->fetchrow_hashref(NAME_lc);
	for (keys %$ref) { $form->{$_} = $ref->{$_} }
	$sth->finish;

	# get default accounts
	$query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id,
					   fxgain_accno_id, fxloss_accno_id
				  FROM defaults|;

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

	$ref = $sth->fetchrow_hashref(NAME_lc);
	for (keys %$ref) { $form->{$_} = $ref->{$_} }
	$sth->finish;

	# check if we have any transactions
	$query = qq|SELECT trans_id FROM acc_trans
				 WHERE chart_id = $form->{id}|;

	($form->{orphaned}) = $dbh->selectrow_array($query);
	$form->{orphaned} = !$form->{orphaned};

	$dbh->disconnect;

}


sub save_account {

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

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

	$form->{link} = "";
	foreach my $item ($form->{AR},
					  $form->{AR_amount},
					  $form->{AR_tax},
					  $form->{AR_paid},
					  $form->{AP},
					  $form->{AP_amount},
					  $form->{AP_tax},
					  $form->{AP_paid},
					  $form->{IC},
					  $form->{IC_income},
					  $form->{IC_sale},
					  $form->{IC_expense},
					  $form->{IC_cogs},
					  $form->{IC_taxpart},
					  $form->{IC_taxservice}) {
		$form->{link} .= "${item}:" if ($item);
	}

	chop $form->{link};

	# strip blanks from accno
	for (qw(accno gifi_accno)) { $form->{$_} =~ s/( |')//g }

	foreach my $item (qw(accno gifi_accno description)) {
		$form->{$item} =~ s/-(-+)/-/g;
		$form->{$item} =~ s/ ( )+/ /g;
	}

	my $query;
	my $sth;

	$form->{contra} *= 1;

	# if we have an id then replace the old record
	if ($form->{id}) {
		$query = qq|UPDATE chart SET accno = '$form->{accno}',
									 description = |.$dbh->quote($form->{description}).qq|,
									 charttype = '$form->{charttype}',
									 gifi_accno = '$form->{gifi_accno}',
									 category = '$form->{category}',
									 link = '$form->{link}',
									 contra = '$form->{contra}'
							   WHERE id = $form->{id}|;
	} else {
		$query = qq|INSERT INTO chart (accno, description, charttype, 
									   gifi_accno, category, link, contra)
						 VALUES ('$form->{accno}',|
								 .$dbh->quote($form->{description}).qq|,
								 '$form->{charttype}', '$form->{gifi_accno}',
								 '$form->{category}', '$form->{link}', '$form->{contra}')|;
	}

	$dbh->do($query) || $form->dberror($query);


	$chart_id = $form->{id};

	if (! $form->{id}) {
		# get id from chart
		$query = qq|SELECT id
					  FROM chart
					 WHERE accno = '$form->{accno}'|;

		($chart_id) = $dbh->selectrow_array($query);
	}

	if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{AR_tax} || $form->{AP_tax}) {

		# add account if it doesn't exist in tax
		$query = qq|SELECT chart_id
					  FROM tax
					 WHERE chart_id = $chart_id|;

		my ($tax_id) = $dbh->selectrow_array($query);

		# add tax if it doesn't exist
		unless ($tax_id) {
			$query = qq|INSERT INTO tax (chart_id, rate)
							 VALUES ($chart_id, 0)|;

			$dbh->do($query) || $form->dberror($query);
		}

	} else {

		# remove tax
		if ($form->{id}) {
			$query = qq|DELETE FROM tax
							  WHERE chart_id = $form->{id}|;

			$dbh->do($query) || $form->dberror($query);
		}
	}

	# commit
	my $rc = $dbh->commit;
	$dbh->disconnect;

	$rc;
}



sub delete_account {

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

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

	## needs fixing (SELECT *...)
	my $query = qq|SELECT * 
					 FROM acc_trans
					WHERE chart_id = $form->{id}|;

	if ($dbh->selectrow_array($query)) {
		$dbh->disconnect;
		return;
	}


	# delete chart of account record
	$query = qq|DELETE FROM chart
					  WHERE id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);

	# set inventory_accno_id, income_accno_id, expense_accno_id to defaults
	$query = qq|UPDATE parts
				   SET inventory_accno_id = (SELECT inventory_accno_id 
											   FROM defaults)
				 WHERE inventory_accno_id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);

	$query = qq|UPDATE parts
				   SET income_accno_id = (SELECT income_accno_id 
											FROM defaults)
				 WHERE income_accno_id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);

	$query = qq|UPDATE parts
				   SET expense_accno_id = (SELECT expense_accno_id 
											 FROM defaults)
				 WHERE expense_accno_id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);

	foreach my $table (qw(partstax customertax vendortax tax)) {
		$query = qq|DELETE FROM $table
						  WHERE chart_id = $form->{id}|;

		$dbh->do($query) || $form->dberror($query);
	}

	# commit and redirect
	my $rc = $dbh->commit;
	$dbh->disconnect;

	$rc;
}


sub gifi_accounts {

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

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

	my $query = qq|SELECT accno, description
					 FROM gifi
				 ORDER BY accno|;

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

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

	$sth->finish;

	$dbh->disconnect;
}



sub get_gifi {

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

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

	my $query = qq|SELECT accno, description
					 FROM gifi
					WHERE accno = '$form->{accno}'|;

	($form->{accno}, $form->{description}) = $dbh->selectrow_array($query);

	# check for transactions ## needs fixing (SELECT *...)
	$query = qq|SELECT * 
				  FROM acc_trans a
				  JOIN chart c ON (a.chart_id = c.id)
				  JOIN gifi g ON (c.gifi_accno = g.accno)
				 WHERE g.accno = '$form->{accno}'|;

	($form->{orphaned}) = $dbh->selectrow_array($query);
	$form->{orphaned} = !$form->{orphaned};

	$dbh->disconnect;

}


sub save_gifi {

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

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

	$form->{accno} =~ s/( |')//g;

	foreach my $item (qw(accno description)) {
		$form->{$item} =~ s/-(-+)/-/g;
		$form->{$item} =~ s/ ( )+/ /g;
	}

	# id is the old account number!
	if ($form->{id}) {
		$query = qq|UPDATE gifi 
					   SET accno = '$form->{accno}',
						   description = |.$dbh->quote($form->{description}).qq|
					 WHERE accno = '$form->{id}'|;

	} else {
		$query = qq|INSERT INTO gifi (accno, description)
						 VALUES ('$form->{accno}',|
								.$dbh->quote($form->{description}).qq|)|;
	}

	$dbh->do($query) || $form->dberror; 
	$dbh->disconnect;

}


sub delete_gifi {

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

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

	# id is the old account number!
	$query = qq|DELETE FROM gifi
					  WHERE accno = '$form->{id}'|;

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub warehouses {

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

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

	$form->sort_order();
	my $query = qq|SELECT id, description
					 FROM warehouse
				 ORDER BY description $form->{direction}|;

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

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

	$sth->finish;
	$dbh->disconnect;

}


sub get_warehouse {

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

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

	my $query = qq|SELECT description
					 FROM warehouse
					WHERE id = $form->{id}|;

	($form->{description}) = $dbh->selectrow_array($query);

	# see if it is in use
	$query = qq|SELECT * FROM inventory
				 WHERE warehouse_id = $form->{id}|;

	($form->{orphaned}) = $dbh->selectrow_array($query);
	$form->{orphaned} = !$form->{orphaned};

	$dbh->disconnect;
}


sub save_warehouse {

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

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

	$form->{description} =~ s/-(-)+/-/g;
	$form->{description} =~ s/ ( )+/ /g;

	if ($form->{id}) {
		$query = qq|UPDATE warehouse 
					   SET description = |.$dbh->quote($form->{description}).qq|
					 WHERE id = $form->{id}|;
	} else {
		$query = qq|INSERT INTO warehouse (description)
						 VALUES (|.$dbh->quote($form->{description}).qq|)|;
	}

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub delete_warehouse {

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

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

	$query = qq|DELETE FROM warehouse
					  WHERE id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}



sub departments {

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

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

	$form->sort_order();
	my $query = qq|SELECT id, description, role
					 FROM department
				 ORDER BY description $form->{direction}|;

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

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

	$sth->finish;
	$dbh->disconnect;

}



sub get_department {

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

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

	my $query = qq|SELECT description, role
					 FROM department
					WHERE id = $form->{id}|;

	($form->{description}, $form->{role}) = $dbh->selectrow_array($query);

	for (keys %$ref) { $form->{$_} = $ref->{$_} }

	# see if it is in use ## needs fixing (SELECT * ...)
	$query = qq|SELECT * 
				  FROM dpt_trans
				 WHERE department_id = $form->{id}|;

	($form->{orphaned}) = $dbh->selectrow_array($query);
	$form->{orphaned} = !$form->{orphaned};

	$dbh->disconnect;
}


sub save_department {

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

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

	$form->{description} =~ s/-(-)+/-/g;
	$form->{description} =~ s/ ( )+/ /g;

	if ($form->{id}) {
		$query = qq|UPDATE department 
					   SET description = |.$dbh->quote($form->{description}).qq|,
						   role = '$form->{role}'
					 WHERE id = $form->{id}|;

	} else {
		$query = qq|INSERT INTO department (description, role)
						 VALUES (| .$dbh->quote($form->{description}).qq|, '$form->{role}')|;
	}

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub delete_department {

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

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

	$query = qq|DELETE FROM department
					  WHERE id = $form->{id}|;

	$dbh->do($query);
	$dbh->disconnect;

}


sub business {

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

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

	$form->sort_order();
	my $query = qq|SELECT id, description, discount
					 FROM business
				 ORDER BY description $form->{direction}|;

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

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

	$sth->finish;
	$dbh->disconnect;

}


sub get_business {

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

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

	my $query = qq|SELECT description, discount
					 FROM business
					WHERE id = $form->{id}|;

	($form->{description}, $form->{discount}) = $dbh->selectrow_array($query);
	$dbh->disconnect;

}


sub save_business {

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

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

	$form->{description} =~ s/-(-)+/-/g;
	$form->{description} =~ s/ ( )+/ /g;
	$form->{discount} /= 100;

	if ($form->{id}) {
		$query = qq|UPDATE business 
					   SET description = |.$dbh->quote($form->{description}).qq|,
						   discount = $form->{discount}
					 WHERE id = $form->{id}|;

	} else {
		$query = qq|INSERT INTO business (description, discount)
						 VALUES (| .$dbh->quote($form->{description}).qq|, $form->{discount})|;
	}

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


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

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

	$query = qq|DELETE FROM business
					  WHERE id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub sic {

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

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

	$form->{sort} = "code" unless $form->{sort};
	my @a = qw(code description);

	my %ordinal = ( code		=> 1,
					description	=> 3 );

	my $sortorder = $form->sort_order(\@a, \%ordinal);

	my $query = qq|SELECT code, sictype, description
					 FROM sic
				 ORDER BY $sortorder|;

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

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

	$sth->finish;
	$dbh->disconnect;

}


sub get_sic {

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

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

	my $query = qq|SELECT code, sictype, description
					 FROM sic
					WHERE code = |.$dbh->quote($form->{code});

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

	my $ref = $sth->fetchrow_hashref(NAME_lc);
	for (keys %$ref) { $form->{$_} = $ref->{$_} }

	$sth->finish;
	$dbh->disconnect;

}


sub save_sic {

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

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

	foreach my $item (qw(code description)) {
		$form->{$item} =~ s/-(-)+/-/g;
	}

	# if there is an id
	if ($form->{id}) {
		$query = qq|UPDATE sic 
					   SET code = |.$dbh->quote($form->{code}).qq|,
						   sictype = '$form->{sictype}',
						   description = |.$dbh->quote($form->{description}).qq|
					 WHERE code = |.$dbh->quote($form->{id});

	} else {
		$query = qq|INSERT INTO sic (code, sictype, description)
						 VALUES (|.$dbh->quote($form->{code}).qq|,
								 '$form->{sictype}',|
								 .$dbh->quote($form->{description}).qq|)|;

	}

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub delete_sic {

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

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

	$query = qq|DELETE FROM sic
					  WHERE code = |.$dbh->quote($form->{code});

	$dbh->do($query);
	$dbh->disconnect;

}


sub language {

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

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

	$form->{sort} = "code" unless $form->{sort};
	my @a = qw(code description);

	my %ordinal = ( code		=> 1,
					description	=> 2 );

	my $sortorder = $form->sort_order(\@a, \%ordinal);

	my $query = qq|SELECT code, description
					 FROM language
				 ORDER BY $sortorder|;

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

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

	$sth->finish;
	$dbh->disconnect;

}


sub get_language {

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

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

	## needs fixing (SELECT *...)
	my $query = qq|SELECT *
					 FROM language
					WHERE code = |.$dbh->quote($form->{code});

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

	my $ref = $sth->fetchrow_hashref(NAME_lc);

	for (keys %$ref) { $form->{$_} = $ref->{$_} }

	$sth->finish;
	$dbh->disconnect;

}


sub save_language {

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

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

	$form->{code} =~ s/ //g;

	foreach my $item (qw(code description)) {
		$form->{$item} =~ s/-(-)+/-/g;
		$form->{$item} =~ s/ ( )+/-/g;
	}

	# if there is an id
	if ($form->{id}) {
		$query = qq|UPDATE language 
					   SET code = |.$dbh->quote($form->{code}).qq|,
						   description = |.$dbh->quote($form->{description}).qq|
					 WHERE code = |.$dbh->quote($form->{id});

	} else {
		$query = qq|INSERT INTO language (code, description)
						 VALUES (|.$dbh->quote($form->{code}).qq|,|
								  .$dbh->quote($form->{description}).qq|)|;
	}

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub delete_language {

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

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

	$query = qq|DELETE FROM language
					  WHERE code = |.$dbh->quote($form->{code});

	$dbh->do($query) || $form->dberror($query);
	$dbh->disconnect;

}


sub recurring_transactions {

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

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

	my $query = qq|SELECT curr FROM defaults|;

	my ($defaultcurrency) = $dbh->selectrow_array($query);
	$defaultcurrency =~ s/:.*//g;

	$form->{sort} ||= "nextdate";
	my @a = ($form->{sort});
	my $sortorder = $form->sort_order(\@a);

	$query = qq|SELECT 'ar' AS module, 'ar' AS transaction, a.invoice,
						n.name AS description, a.amount,
						s.*, se.formname AS recurringemail,
						sp.formname AS recurringprint,
						s.nextdate - current_date AS overdue, 'customer' AS vc,
						ex.buy AS exchangerate, a.curr,
						(s.nextdate IS NULL OR s.nextdate > s.enddate) AS expired
				   FROM recurring s
				   JOIN ar a ON (a.id = s.id)
				   JOIN customer n ON (n.id = a.customer_id)
			  LEFT JOIN recurringemail se ON (se.id = s.id)
			  LEFT JOIN recurringprint sp ON (sp.id = s.id)
			  LEFT JOIN exchangerate ex ON (ex.curr = a.curr AND a.transdate = ex.transdate)

				  UNION

				 SELECT 'ap' AS module, 'ap' AS transaction, a.invoice,
						 n.name AS description, a.amount,
						 s.*, se.formname AS recurringemail,
						 sp.formname AS recurringprint,
						 s.nextdate - current_date AS overdue, 'vendor' AS vc,
						 ex.sell AS exchangerate, a.curr,
						 (s.nextdate IS NULL OR s.nextdate > s.enddate) AS expired
				   FROM recurring s
				   JOIN ap a ON (a.id = s.id)
				   JOIN vendor n ON (n.id = a.vendor_id)
			  LEFT JOIN recurringemail se ON (se.id = s.id)
			  LEFT JOIN recurringprint sp ON (sp.id = s.id)
			  LEFT JOIN exchangerate ex ON (ex.curr = a.curr AND a.transdate = ex.transdate)

				  UNION

				 SELECT 'gl' AS module, 'gl' AS transaction, FALSE AS invoice,
						a.description, (SELECT SUM(ac.amount) 
										  FROM acc_trans ac 
										 WHERE ac.trans_id = a.id 
										   AND ac.amount > 0) AS amount,
						s.*, se.formname AS recurringemail,
						sp.formname AS recurringprint,
						s.nextdate - current_date AS overdue, '' AS vc,
						'1' AS exchangerate, '$defaultcurrency' AS curr,
						(s.nextdate IS NULL OR s.nextdate > s.enddate) AS expired
				   FROM recurring s
				   JOIN gl a ON (a.id = s.id)
			  LEFT JOIN recurringemail se ON (se.id = s.id)
			  LEFT JOIN recurringprint sp ON (sp.id = s.id)

				  UNION

				 SELECT 'oe' AS module, 'so' AS transaction, FALSE AS invoice,
						n.name AS description, a.amount,
						s.*, se.formname AS recurringemail,
						sp.formname AS recurringprint,
						s.nextdate - current_date AS overdue, 'customer' AS vc,
						ex.buy AS exchangerate, a.curr,
						(s.nextdate IS NULL OR s.nextdate > s.enddate) AS expired
				   FROM recurring s
				   JOIN oe a ON (a.id = s.id)
				   JOIN customer n ON (n.id = a.customer_id)
			  LEFT JOIN recurringemail se ON (se.id = s.id)
			  LEFT JOIN recurringprint sp ON (sp.id = s.id)
			  LEFT JOIN exchangerate ex ON (ex.curr = a.curr AND a.transdate = ex.transdate)
				  WHERE a.quotation = '0'

				  UNION

				 SELECT 'oe' AS module, 'po' AS transaction, FALSE AS invoice,
						n.name AS description, a.amount,
						s.*, se.formname AS recurringemail,
						sp.formname AS recurringprint,
						s.nextdate - current_date AS overdue, 'vendor' AS vc,
						ex.sell AS exchangerate, a.curr,
						(s.nextdate IS NULL OR s.nextdate > s.enddate) AS expired
				   FROM recurring s
				   JOIN oe a ON (a.id = s.id)
				   JOIN vendor n ON (n.id = a.vendor_id)
			  LEFT JOIN recurringemail se ON (se.id = s.id)
			  LEFT JOIN recurringprint sp ON (sp.id = s.id)
			  LEFT JOIN exchangerate ex ON (ex.curr = a.curr AND a.transdate = ex.transdate)
				  WHERE a.quotation = '0'

			   ORDER BY $sortorder|;

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

	my $id;
	my $transaction;
	my %e = ();
	my %p = ();

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

		$ref->{exchangerate} ||= 1;

		if ($ref->{id} != $id) {

			if (%e) {
				$form->{transactions}{$transaction}->[$i]->{recurringemail} = "";
				for (keys %e) { $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:" }
				chop $form->{transactions}{$transaction}->[$i]->{recurringemail};
			}

			if (%p) {
				$form->{transactions}{$transaction}->[$i]->{recurringprint} = "";
				for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" }
				chop $form->{transactions}{$transaction}->[$i]->{recurringprint};
			}

			%e = ();
			%p = ();

			push @{ $form->{transactions}{$ref->{transaction}} }, $ref;

			$id = $ref->{id};
			$i = $#{ $form->{transactions}{$ref->{transaction}} };

		}

		$transaction = $ref->{transaction};

		$e{$ref->{recurringemail}} = 1 if $ref->{recurringemail};
		$p{$ref->{recurringprint}} = 1 if $ref->{recurringprint};

	}

	$sth->finish;

	# this is for the last row
	if (%e) {
		$form->{transactions}{$transaction}->[$i]->{recurringemail} = "";
		for (keys %e) { $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:" }
		chop $form->{transactions}{$transaction}->[$i]->{recurringemail};
	}

	if (%p) {
		$form->{transactions}{$transaction}->[$i]->{recurringprint} = "";
		for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" }
		chop $form->{transactions}{$transaction}->[$i]->{recurringprint};
	}


	$dbh->disconnect;

}

sub recurring_details {

	my ($self, $myconfig, $form, $id) = @_;

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

	my $query = qq|SELECT s.*, ar.id AS arid, ar.invoice AS arinvoice,
						  ap.id AS apid, ap.invoice AS apinvoice,
						  ar.duedate - ar.transdate AS overdue,
						  ar.datepaid - ar.transdate AS paid,
						  oe.reqdate - oe.transdate AS req,
						  oe.id AS oeid, oe.customer_id, oe.vendor_id
					 FROM recurring s
				LEFT JOIN ar ON (ar.id = s.id)
				LEFT JOIN ap ON (ap.id = s.id)
				LEFT JOIN oe ON (oe.id = s.id)
					WHERE s.id = $id|;

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

	my $ref = $sth->fetchrow_hashref(NAME_lc);
	$form->{vc} = "customer" if $ref->{customer_id};
	$form->{vc} = "vendor" if $ref->{vendor_id};
	for (keys %$ref) { $form->{$_} = $ref->{$_} }
	$sth->finish;

	$form->{invoice} = ($form->{arid} && $form->{arinvoice});
	$form->{invoice} = ($form->{apid} && $form->{apinvoice}) unless $form->{invoice};

	$query = qq|SELECT * 
				  FROM recurringemail
				 WHERE id = $id|;

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

	$form->{recurringemail} = "";

	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$form->{recurringemail} .= "$ref->{formname}:$ref->{format}:";
		$form->{message} = $ref->{message};
	}

	$sth->finish;

	$query = qq|SELECT * 
				  FROM recurringprint
				 WHERE id = $id|;

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

	$form->{recurringprint} = "";
		while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$form->{recurringprint} .= "$ref->{formname}:$ref->{format}:$ref->{printer}:";
	}

	$sth->finish;

	chop $form->{recurringemail};
	chop $form->{recurringprint};

	for (qw(arinvoice apinvoice)) { delete $form->{$_} }

	$dbh->disconnect;

}


sub update_recurring {

	my ($self, $myconfig, $form, $id) = @_;

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

	my $query = qq|SELECT nextdate, repeat, unit
					 FROM recurring
					WHERE id = $id|;

	my ($nextdate, $repeat, $unit) = $dbh->selectrow_array($query);

	my %advance = ( 'Pg'  => "(date '$nextdate' + interval '$repeat $unit')",
					'DB2' => qq|(date ('$nextdate') + "$repeat $unit")|,);

	$interval{Oracle} = $interval{PgPP} = $interval{Pg};

	# check if it is the last date
	$query = qq|SELECT $advance{$myconfig->{dbdriver}} > enddate
				  FROM recurring
				 WHERE id = $id|;

	my ($last_repeat) = $dbh->selectrow_array($query);
	if ($last_repeat) {
		$advance{$myconfig->{dbdriver}} = "NULL";
	}

	$query = qq|UPDATE recurring 
				   SET nextdate = $advance{$myconfig->{dbdriver}}
				 WHERE id = $id|;

	$dbh->do($query) || $form->dberror($query);

	$dbh->disconnect;

}


sub load_template {

	my ($self, $form) = @_;

	open(TEMPLATE, "$form->{file}") or $form->error("$form->{file} : $!");

	while (<TEMPLATE>) {
		$form->{body} .= $_;
	}

	close(TEMPLATE);

}


sub save_template {

	my ($self, $form) = @_;

	open(TEMPLATE, ">$form->{file}") or $form->error("$form->{file} : $!");

	# strip 
	$form->{body} =~ s/\r//g;
	print TEMPLATE $form->{body};

	close(TEMPLATE);

}


sub save_preferences {

	my ($self, $myconfig, $form, $memberfile, $userspath) = @_;

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

	# update name
	my $query = qq|UPDATE employee
					  SET name = |.$dbh->quote($form->{name}).qq|,
						  role = '$form->{role}'
					WHERE login = '$form->{login}'|;

	$dbh->do($query) || $form->dberror($query);

	# get default currency
	$query = qq|SELECT curr, businessnumber
				  FROM defaults|;

	($form->{currency}, $form->{businessnumber}) = $dbh->selectrow_array($query);
	$form->{currency} =~ s/:.*//;

	$dbh->disconnect;

	my $myconfig = new User "$memberfile", "$form->{login}";

	foreach my $item (keys %$form) {
		$myconfig->{$item} = $form->{$item};
	}

	$myconfig->{password} = $form->{new_password} if ($form->{old_password} ne $form->{new_password});

	$myconfig->save_member($memberfile, $userspath);

	1;

}


sub save_defaults {

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

	for (qw(IC IC_income IC_expense FX_gain FX_loss)) { ($form->{$_}) = split /--/, $form->{$_} }

	my @a;
	$form->{curr} =~ s/ //g;
	for (split /:/, $form->{curr}) { push(@a, uc pack "A3", $_) if $_ }
	$form->{curr} = join ':', @a;

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

	# save defaults
	my $query = qq|UPDATE defaults 
					  SET inventory_accno_id = (SELECT id 
												  FROM chart
												 WHERE accno = '$form->{IC}'),
						  income_accno_id = (SELECT id 
											   FROM chart
											  WHERE accno = '$form->{IC_income}'),
						  expense_accno_id = (SELECT id 
												FROM chart
											   WHERE accno = '$form->{IC_expense}'),
						  fxgain_accno_id = (SELECT id 
											   FROM chart
											  WHERE accno = '$form->{FX_gain}'),
						  fxloss_accno_id = (SELECT id 
											   FROM chart
											  WHERE accno = '$form->{FX_loss}'),
						  glnumber = '$form->{glnumber}',
						  sinumber = '$form->{sinumber}',
						  vinumber = '$form->{vinumber}',
						  sonumber = '$form->{sonumber}',
						  ponumber = '$form->{ponumber}',
						  sqnumber = '$form->{sqnumber}',
						  rfqnumber = '$form->{rfqnumber}',
						  partnumber = '$form->{partnumber}',
						  employeenumber = '$form->{employeenumber}',
						  customernumber = '$form->{customernumber}',
						  vendornumber = '$form->{vendornumber}',
						  projectnumber = '$form->{projectnumber}',
						  yearend = '$form->{yearend}',
						  curr = '$form->{curr}',
						  weightunit = |.$dbh->quote($form->{weightunit}).qq|,
						  businessnumber = |.$dbh->quote($form->{businessnumber});

	$dbh->do($query) || $form->dberror($query);

	my $rc = $dbh->commit;
	$dbh->disconnect;

	$rc;

}


sub defaultaccounts {

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

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

	# get defaults from defaults table
	my $query = qq|SELECT * FROM defaults|;
	my $sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);

	my $ref = $sth->fetchrow_hashref(NAME_lc);
	for (keys %$ref) { $form->{$_} = $ref->{$_} }

	$form->{defaults}{IC} = $form->{inventory_accno_id};
	$form->{defaults}{IC_income} = $form->{income_accno_id};
	$form->{defaults}{IC_sale} = $form->{income_accno_id};
	$form->{defaults}{IC_expense} = $form->{expense_accno_id};
	$form->{defaults}{IC_cogs} = $form->{expense_accno_id};
	$form->{defaults}{FX_gain} = $form->{fxgain_accno_id};
	$form->{defaults}{FX_loss} = $form->{fxloss_accno_id};

	$sth->finish;

	$query = qq|SELECT id, accno, description, link
				  FROM chart
				 WHERE link LIKE '%IC%'
			  ORDER BY accno|;

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

	my $nkey;
	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		foreach my $key (split(/:/, $ref->{link})) {
			if ($key =~ /IC/) {
				$nkey = $key;

					if ($key =~ /cogs/) {
						$nkey = "IC_expense";
					}

					if ($key =~ /sale/) {
						$nkey = "IC_income";
					}

				%{ $form->{accno}{$nkey}{$ref->{accno}} } = ( id => $ref->{id},
															description => $ref->{description} );
			}
		}
	}

	$sth->finish;


	$query = qq|SELECT id, accno, description
				  FROM chart
				 WHERE (category = 'I' OR category = 'E')
				   AND charttype = 'A'
			  ORDER BY accno|;

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

	while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
		%{ $form->{accno}{FX_gain}{$ref->{accno}} } = ( id => $ref->{id},
														description => $ref->{description} );

		%{ $form->{accno}{FX_loss}{$ref->{accno}} } = ( id => $ref->{id},
														description => $ref->{description} );
	}

	$sth->finish;

	$dbh->disconnect;

}


sub taxes {

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

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

	my $query = qq|SELECT c.id, c.accno, c.description,
						  t.rate * 100 AS rate, t.taxnumber, t.validto
					 FROM chart c
					 JOIN tax t ON (c.id = t.chart_id)
				 ORDER BY 3, 6|;

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

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

	$sth->finish;

	$dbh->disconnect;

}


sub save_taxes {

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

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

	my $query = qq|DELETE FROM tax|;
	$dbh->do($query) || $form->dberror($query);

	foreach my $item (split / /, $form->{taxaccounts}) {
		my ($chart_id, $i) = split /_/, $item;
		my $rate = $form->parse_amount($myconfig, $form->{"taxrate_$i"}) / 100;
		
		$query = qq|INSERT INTO tax (chart_id, rate, taxnumber, validto)
						 VALUES ($chart_id, $rate, |
								.$dbh->quote($form->{"taxnumber_$i"}).qq|, |
								.$form->dbquote($form->{"validto_$i"}, SQL_DATE)
								.qq|)|;

		$dbh->do($query) || $form->dberror($query);
	}

	my $rc = $dbh->commit;
	$dbh->disconnect;

	$rc;

}


sub backup {

	my ($self, $myconfig, $form, $userspath, $gzip) = @_;

	my $mail;
	my $err;

	my @t = localtime(time);
	$t[4]++;
	$t[5] += 1900;
	$t[3] = substr("0$t[3]", -2);
	$t[4] = substr("0$t[4]", -2);

	my $boundary = time;
	my $tmpfile = "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql";
	my $out = $form->{OUT};
	$form->{OUT} = ">$tmpfile";

	open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!");

	# get sequences, functions and triggers

	my $today = scalar localtime;

	$myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost};

	$ENV{PGPASSWD} = $myconfig->{dbpasswd};
	# drop tables and sequences

	# compress backup if gzip defined
	my $suffix = "";

	if ($form->{media} eq 'email') {
		if ($gzip){
			print OUT `pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} $myconfig->{dbname} | $gzip`;
		} else {
			print OUT `pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} $myconfig->{dbname}`;
		}
		close OUT;
		use LedgerSMB::Mailer;
		$mail = new Mailer;

		$mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
		$mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
		$mail->{subject} = "LedgerSMB Backup / $myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix";
		@{ $mail->{attachments} } = ($tmpfile);
		$mail->{version} = $form->{version};
		$mail->{fileid} = "$boundary.";

		$myconfig->{signature} =~ s/\\n/\n/g;
		$mail->{message} = "-- \n$myconfig->{signature}";

		$err = $mail->send($out);
	}

	if ($form->{media} eq 'file') {

		open(IN, "$tmpfile") or $form->error("$tmpfile : $!");
		open(OUT, ">-") or $form->error("STDOUT : $!");

		print OUT qq|Content-Type: application/file;\n| .
		qq|Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix"\n\n|;
		if ($gzip){
			print OUT `pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} $myconfig->{dbname} | $gzip`;
		} else {
			print OUT `pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} $myconfig->{dbname}`;
		}

	}

	unlink "$tmpfile";

}


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

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

	my $query = qq|SELECT closedto, revtrans, audittrail
					 FROM defaults|;

	($form->{closedto}, $form->{revtrans}, $form->{audittrail}) = $dbh->selectrow_array($query);

	$dbh->disconnect;

}


sub closebooks {

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

	my $dbh = $form->dbconnect_noauto($myconfig);
	my $query = qq|UPDATE defaults SET|;

	if ($form->{revtrans}) {
		$query .= qq| revtrans = '1'|;
	} else {
		$query .= qq| revtrans = '0'|;
	}

	$query .= qq|, closedto = |.$form->dbquote($form->{closedto}, SQL_DATE);

	if ($form->{audittrail}) {
		$query .= qq|, audittrail = '1'|;
	} else {
		$query .= qq|, audittrail = '0'|;
	}

	# set close in defaults
	$dbh->do($query) || $form->dberror($query);

	if ($form->{removeaudittrail}) {
		$query = qq|DELETE FROM audittrail
						  WHERE transdate < '$form->{removeaudittrail}'|;

		$dbh->do($query) || $form->dberror($query);
	}

	$dbh->commit;
	$dbh->disconnect;

}


sub earningsaccounts {

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

	my ($query, $sth, $ref);

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

	# get chart of accounts
	$query = qq|SELECT accno,description
				  FROM chart
				 WHERE charttype = 'A'
				   AND category = 'Q'
			  ORDER BY accno|;

	$sth = $dbh->prepare($query);
	$sth->execute || $form->dberror($query);
	$form->{chart} = "";

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


sub post_yearend {

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

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

	my $query;
	my $uid = localtime;
	$uid .= "$$";

	$query = qq|INSERT INTO gl (reference, employee_id)
					 VALUES ('$uid', (SELECT id FROM employee
					  WHERE login = '$form->{login}'))|;

	$dbh->do($query) || $form->dberror($query);

	$query = qq|SELECT id 
				  FROM gl
				 WHERE reference = '$uid'|;

	($form->{id}) = $dbh->selectrow_array($query);

	$query = qq|UPDATE gl 
				   SET reference = |.$dbh->quote($form->{reference}).qq|,
					   description = |.$dbh->quote($form->{description}).qq|,
					   notes = |.$dbh->quote($form->{notes}).qq|,
					   transdate = '$form->{transdate}',
					   department_id = 0
				 WHERE id = $form->{id}|;

	$dbh->do($query) || $form->dberror($query);

	my $amount;
	my $accno;

	# insert acc_trans transactions
	for my $i (1 .. $form->{rowcount}) {
		# extract accno
		($accno) = split(/--/, $form->{"accno_$i"});
		$amount = 0;

		if ($form->{"credit_$i"}) {
			$amount = $form->{"credit_$i"};
		}

		if ($form->{"debit_$i"}) {
			$amount = $form->{"debit_$i"} * -1;
		}


		# if there is an amount, add the record
		if ($amount) {
			$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source)
							 VALUES ($form->{id}, (SELECT id
													 FROM chart
													WHERE accno = '$accno'),
									 $amount, '$form->{transdate}', |
									.$dbh->quote($form->{reference}).qq|)|;

		$dbh->do($query) || $form->dberror($query);
		}
	}

	$query = qq|INSERT INTO yearend (trans_id, transdate)
					 VALUES ($form->{id}, '$form->{transdate}')|;

	$dbh->do($query) || $form->dberror($query);

	my %audittrail = ( tablename	=> 'gl',
					   reference	=> $form->{reference},
					    formname	=> 'yearend',
					      action	=> 'posted',
							  id	=> $form->{id} );

	$form->audittrail($dbh, "", \%audittrail);

	# commit and redirect
	my $rc = $dbh->commit;
	$dbh->disconnect;

	$rc;

}


1;