#=====================================================================
# 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) 2003
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Check and receipt printing payment module backend routines
# Number to text conversion routines are in
# locale/{countrycode}/Num2text
#
#======================================================================

package CP;


sub new {

	my ($type, $countrycode) = @_;

	$self = {};

	if ($countrycode) {

		if (-f "locale/$countrycode/Num2text") {
			require "locale/$countrycode/Num2text";
		} else {
			use LedgerSMB::Num2text;
		}

	} else {
		use LedgerSMB::Num2text;
	}

	bless $self, $type;

}


sub paymentaccounts {

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

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

	my $query = qq|SELECT accno, description, link
					 FROM chart
					WHERE link LIKE '%$form->{ARAP}%'
				 ORDER BY accno|;

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

	$form->{PR}{$form->{ARAP}} = ();
	$form->{PR}{"$form->{ARAP}_paid"} = ();

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

		foreach my $item (split /:/, $ref->{link}) {

			if ($item eq $form->{ARAP}) {
				push @{ $form->{PR}{$form->{ARAP}} }, $ref;
			}

			if ($item eq "$form->{ARAP}_paid") {
				push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
			}
		}
	}

	$sth->finish;

	# get currencies and closedto
	$query = qq|SELECT curr, closedto, current_date
				  FROM defaults|;

	($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);

	if ($form->{payment} eq 'payments') {
		# get language codes
		$query = qq|SELECT *
					  FROM language
				  ORDER BY 2|;

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

		$form->{all_language} = ();

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

		$sth->finish;

		$form->all_departments($myconfig, $dbh, $form->{vc});
	}

	$dbh->disconnect;

}


sub get_openvc {

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

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

	my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
	my $query = qq|SELECT count(*)
					 FROM $form->{vc} ct, $arap a
					WHERE a.$form->{vc}_id = ct.id
					  AND a.amount != a.paid|;

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

	my $sth;
	my $ref;
	my $i = 0;

	my $where = qq|WHERE a.$form->{vc}_id = ct.id
					 AND a.amount != a.paid|;

	if ($form->{$form->{vc}}) {
		my $var = $form->like(lc $form->{$form->{vc}});
		$where .= " AND lower(name) LIKE '$var'";
	}

	# build selection list
	$query = qq|SELECT DISTINCT ct.*
				  FROM $form->{vc} ct, $arap a
				$where
			  ORDER BY name|;

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

	while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
		$i++;
		push @{ $form->{name_list} }, $ref;
	}

	$sth->finish;

	$form->all_departments($myconfig, $dbh, $form->{vc});

	# get language codes
	$query = qq|SELECT *
				  FROM language
			  ORDER BY 2|;

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

	$form->{all_language} = ();

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

	$sth->finish;

	# get currency for first name
	if (@{ $form->{name_list} }) {
		$query = qq|SELECT curr 
					  FROM $form->{vc}
					 WHERE id = $form->{name_list}->[0]->{id}|;

		($form->{currency}) = $dbh->selectrow_array($query);
		$form->{currency} ||= $form->{defaultcurrency};
	}

	$dbh->disconnect;

	$i;
}


sub get_openinvoices {

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

	my $null;
	my $department_id;

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

	my $where = qq|WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"}
					 AND a.amount != a.paid|;

	$where .= qq| AND a.curr = '$form->{currency}'| if $form->{currency};

	my $sortorder = "transdate, invnumber";

	my ($buysell);

	if ($form->{vc} eq 'customer') {
		$buysell = "buy";
	} else {
		$buysell = "sell";
	}

	if ($form->{payment} eq 'payments') {

		$where = qq|WHERE a.amount != a.paid|;
		$where .= qq| AND a.curr = '$form->{currency}'| if $form->{currency};

		if ($form->{duedatefrom}) {
			$where .= qq| AND a.duedate >= '$form->{duedatefrom}'|;
		}

		if ($form->{duedateto}) {
			$where .= qq| AND a.duedate <= '$form->{duedateto}'|;
		}

		$sortorder = "name, transdate";
	}


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

	if ($department_id) {
		$where .= qq| AND a.department_id = $department_id|;
	}

	my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
						  a.curr, c.name, a.$form->{vc}_id, c.language_code
					 FROM $form->{arap} a
					 JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id)
				   $where
				 ORDER BY $sortorder|;

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

	$query = qq|SELECT s.spoolfile
				  FROM status s
				 WHERE s.formname = '$form->{formname}'
				   AND s.trans_id = ?|;

	my $vth = $dbh->prepare($query);

	my $spoolfile;

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

		# if this is a foreign currency transaction get exchangerate
		$ref->{exchangerate} = $form->get_exchangerate($dbh, $ref->{curr}, $ref->{transdate}, $buysell) if ($form->{currency} ne $form->{defaultcurrency});

		$vth->execute($ref->{id});
		$ref->{queue} = "";
	
		while (($spoolfile) = $vth->fetchrow_array) {
			$ref->{queued} .= "$form->{formname} $spoolfile ";
		}

		$vth->finish;
		$ref->{queued} =~ s/ +$//g;

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

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

}



sub post_payment {

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

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

	my $sth;

	my ($paymentaccno) = split /--/, $form->{account};

	# if currency ne defaultcurrency update exchangerate
	if ($form->{currency} ne $form->{defaultcurrency}) {

		$form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});

		if ($form->{vc} eq 'customer') {
			$form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
		} else {
			$form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
		}

	} else {
		$form->{exchangerate} = 1;
	}

	my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
					 FROM defaults|;

	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);

	my ($buysell);

	if ($form->{vc} eq 'customer') {
		$buysell = "buy";
	} else {
		$buysell = "sell";
	}

	my $ml;
	my $where;

	if ($form->{ARAP} eq 'AR') {

		$ml = 1;
		$where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;

	} else {

		$ml = -1;
		$where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;

	}

	my $paymentamount = $form->parse_amount($myconfig, $form->{amount});

	# query to retrieve paid amount
	$query = qq|SELECT paid 
				  FROM $form->{arap}
				 WHERE id = ?
			FOR UPDATE|;

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

	my %audittrail;

	# go through line by line
	for my $i (1 .. $form->{rowcount}) {

		$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
		$form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});

		if ($form->{"checked_$i"} && $form->{"paid_$i"}) {

			$paymentamount -= $form->{"paid_$i"};

			# get exchangerate for original 
			$query = qq|SELECT $buysell
						  FROM exchangerate e
						  JOIN $form->{arap} a ON (a.transdate = e.transdate)
						 WHERE e.curr = '$form->{currency}'
						   AND a.id = $form->{"id_$i"}|;

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

			$exchangerate = 1 unless $exchangerate;

			$query = qq|SELECT c.id
						  FROM chart c
						  JOIN acc_trans a ON (a.chart_id = c.id)
						 WHERE $where
						   AND a.trans_id = $form->{"id_$i"}|;

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

			$amount = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);

			# add AR/AP
			$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount)
						VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}', $amount * $ml)|;

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

			# add payment
			$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
												amount, source, memo)
						VALUES ($form->{"id_$i"}, (SELECT id 
													 FROM chart
													WHERE accno = '$paymentaccno'),
								'$form->{datepaid}', $form->{"paid_$i"} * $ml * -1, |
								.$dbh->quote($form->{source}).qq|, |
								.$dbh->quote($form->{memo}).qq|)|;

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

			# add exchangerate difference if currency ne defaultcurrency
			$amount = $form->round_amount($form->{"paid_$i"} * ($form->{exchangerate} - 1), 2);

			if ($amount) {
				# exchangerate difference
				$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
													amount, cleared, fx_transaction, source)
							VALUES ($form->{"id_$i"}, (SELECT id 
														 FROM chart
														WHERE accno = '$paymentaccno'),
									'$form->{datepaid}', $amount * $ml * -1, '0', '1', |
									.$dbh->quote($form->{source}).qq|)|;

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

				# gain/loss
				$amount = ($form->round_amount($form->{"paid_$i"} * $exchangerate,2) - $form->round_amount($form->{"paid_$i"} * $form->{exchangerate},2)) * $ml * -1;

				if ($amount) {

					my $accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;

					$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
														amount, cleared, fx_transaction)
								VALUES ($form->{"id_$i"}, $accno_id,
										'$form->{datepaid}', $amount, '0', '1')|;

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

			$form->{"paid_$i"} = $form->round_amount($form->{"paid_$i"} * $exchangerate, 2);

			$pth->execute($form->{"id_$i"}) || $form->dberror;
			($amount) = $pth->fetchrow_array;
			$pth->finish;

			$amount += $form->{"paid_$i"};

			# update AR/AP transaction
			$query = qq|UPDATE $form->{arap} 
						   SET paid = $amount,
							   datepaid = '$form->{datepaid}'
						 WHERE id = $form->{"id_$i"}|;

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

			%audittrail = ( tablename  => $form->{arap},
							reference  => $form->{source},
							formname   => $form->{formname},
							action     => 'posted',
							id         => $form->{"id_$i"} );

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

		}
	}


	# record a AR/AP with a payment
	if ($form->round_amount($paymentamount, 2)) {
		$form->{invnumber} = "";
		OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
	}

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

	$rc;

}


sub post_payments {

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

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

	my $sth;

	my ($paymentaccno) = split /--/, $form->{account};

	# if currency ne defaultcurrency update exchangerate
	if ($form->{currency} ne $form->{defaultcurrency}) {
		$form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});

		if ($form->{vc} eq 'customer') {
			$form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
		} else {
			$form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
		}

	} else {
		$form->{exchangerate} = 1;
	}

	my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
					 FROM defaults|;

	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);

	my ($buysell);

	if ($form->{vc} eq 'customer') {
		$buysell = "buy";
	} else {
		$buysell = "sell";
	}

	my $ml;
	my $where;

	if ($form->{ARAP} eq 'AR') {

		$ml = 1;
		$where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;

	} else {

		$ml = -1;
		$where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;

	}

	# get AR/AP account
	$query = qq|SELECT c.accno 
				  FROM chart c
				  JOIN acc_trans ac ON (ac.chart_id = c.id)
				 WHERE trans_id = ?
				   AND $where|;

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

	# query to retrieve paid amount
	$query = qq|SELECT paid 
				  FROM $form->{arap}
				 WHERE id = ?
			FOR UPDATE|;

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

	my %audittrail;

	my $overpayment = 0;
	my $accno_id;

	# go through line by line
	for my $i (1 .. $form->{rowcount}) {

		$ath->execute($form->{"id_$i"});
		($form->{$form->{ARAP}}) = $ath->fetchrow_array;
		$ath->finish;

		$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
		$form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});

		if ($form->{"$form->{vc}_id_$i"} ne $sameid) {
			# record a AR/AP with a payment
			if ($overpayment > 0 && $form->{$form->{ARAP}}) {
				$form->{invnumber} = "";
				OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
			}

			$overpayment = 0;
			$form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
			for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
		}

		if ($form->{"checked_$i"} && $form->{"paid_$i"}) {

			$overpayment += ($form->{"paid_$i"} - $form->{"due_$i"});

			# get exchangerate for original 
			$query = qq|SELECT $buysell
						  FROM exchangerate e
						  JOIN $form->{arap} a ON (a.transdate = e.transdate)
						 WHERE e.curr = '$form->{currency}'
						   AND a.id = $form->{"id_$i"}|;

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

			$exchangerate ||= 1;

			$query = qq|SELECT c.id
						  FROM chart c
						  JOIN acc_trans a ON (a.chart_id = c.id)
						 WHERE $where
						   AND a.trans_id = $form->{"id_$i"}|;

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

			$paid = ($form->{"paid_$i"} > $form->{"due_$i"}) ? $form->{"due_$i"} : $form->{"paid_$i"};
			$amount = $form->round_amount($paid * $exchangerate, 2);

			# add AR/AP
			$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount)
						VALUES ($form->{"id_$i"}, $id, '$form->{datepaid}',
								$amount * $ml)|;

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

			$query = qq|SELECT id
						  FROM chart
						 WHERE accno = '$paymentaccno'|;

			($accno_id) = $dbh->selectrow_array($query);

			# add payment
			$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
												amount, source, memo)
						VALUES ($form->{"id_$i"}, $accno_id, '$form->{datepaid}',
								$paid * $ml * -1, |
								.$dbh->quote($form->{source}).qq|, |
								.$dbh->quote($form->{memo}).qq|)|;

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

			# add exchangerate difference if currency ne defaultcurrency
			$amount = $form->round_amount($paid * ($form->{exchangerate} - 1) * $ml * -1, 2);

			if ($amount) {
				# exchangerate difference
				$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
													amount, source)
							VALUES ($form->{"id_$i"}, $accno_id, '$form->{datepaid}',
									$amount, |
									.$dbh->quote($form->{source}).qq|)|;

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

				# gain/loss
				$amount = ($form->round_amount($paid * $exchangerate,2) - $form->round_amount($paid * $form->{exchangerate},2)) * $ml * -1;

				if ($amount) {
					$accno_id = ($amount > 0) ? $fxgain_accno_id : $fxloss_accno_id;

					$query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate,
														amount, fx_transaction)
								VALUES ($form->{"id_$i"}, $accno_id,
										'$form->{datepaid}', $amount, '1')|;

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

			$paid = $form->round_amount($paid * $exchangerate, 2);

			$pth->execute($form->{"id_$i"}) || $form->dberror;
			($amount) = $pth->fetchrow_array;
			$pth->finish;

			$amount += $paid;

			# update AR/AP transaction
			$query = qq|UPDATE $form->{arap} 
						   SET paid = $amount,
							   datepaid = '$form->{datepaid}'
						 WHERE id = $form->{"id_$i"}|;

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

			%audittrail = ( tablename  => $form->{arap},
							reference  => $form->{source},
							formname   => $form->{formname},
							action     => 'posted',
							id         => $form->{"id_$i"} );

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

		}

		$sameid = $form->{"$form->{vc}_id_$i"};

	}

	# record a AR/AP with a payment
	if ($overpayment > 0 && $form->{$form->{ARAP}}) {
		$form->{invnumber} = "";
		OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
	}

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

	$rc;

}


1;