Invoicing.pm

# Invoicing functions
# Copyright (C) 2009-2014, AllWorldIT
# Copyright (C) 2008, LinuxRulz
# Copyright (C) 2007 Nigel Kukard  <nkukard@lbsd.net>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.




package wiaflos::server::Invoicing;

use strict;
use warnings;


use wiaflos::version;
use wiaflos::constants;
use wiaflos::server::config;
use wiaflos::server::dblayer;
use wiaflos::server::templating;
use wiaflos::server::GL;
use wiaflos::server::Inventory;
use wiaflos::server::Clients;
use wiaflos::server::Tax;
use wiaflos::server::Tax;

# Whole money transactions, precision is two
use Math::BigFloat;
Math::BigFloat::precision(-2);

use Date::Parse;
use DateTime;
use Crypt::GPG;
use MIME::Lite;


# Our current error message
my $error = "";

# Set current error message
# Args: error_message
sub setError
{
	my $err = shift;
	my ($package,$filename,$line) = caller;
	my (undef,undef,undef,$subroutine) = caller(1);

	# Set error
	$error = "$subroutine($line): $err";
}

# Return current error message
# Args: none
sub Error
{
	my $err = $error;

	# Reset error
	$error = "";

	# Return error
	return $err;
}



# Backend function to build item hash
sub sanitizeRawItem
{
	my $rawData = shift;


	my $item;

	$item->{'ID'} = $rawData->{'ID'};

	$item->{'ClientID'} = $rawData->{'ClientID'};
	$item->{'Number'} = "INV/".uc($rawData->{'Number'});

	$item->{'ShippingAddress'} = $rawData->{'ShippingAddress'};
	$item->{'TaxReference'} = $rawData->{'TaxReference'};

	$item->{'IssueDate'} = $rawData->{'IssueDate'};
	$item->{'DueDate'} = $rawData->{'DueDate'};
	$item->{'OrderNumber'} = $rawData->{'OrderNumber'};

	$item->{'DiscountTotal'} = $rawData->{'DiscountTotal'};
	$item->{'SubTotal'} = $rawData->{'SubTotal'};
	$item->{'TaxTotal'} = $rawData->{'TaxTotal'};
	$item->{'Total'} = $rawData->{'Total'};

	$item->{'Note'} = $rawData->{'Note'};

	$item->{'GLTransactionID'} = $rawData->{'GLTransactionID'};
	$item->{'Posted'} = defined($rawData->{'GLTransactionID'}) ? 1 : 0;

	$item->{'Paid'} = $rawData->{'Paid'};

	return $item;
}


# Check if invoice exists
# Backend function, takes 1 parameter which is the invoice ID
sub invoiceIDExists
{
	my $invoiceID = shift;


	# Select invoice count
	my $rows = DBSelectNumResults("FROM invoices WHERE ID = ".DBQuote($invoiceID));
	if (!defined($rows)) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	return $rows > 0 ? 1 : 0;
}


# Check if invoice number exists
sub invoiceNumberExists
{
	my $number = shift;

	# Remove INV/
	$number = uc($number);
	$number =~ s#^INV/##;

	# Select invoice count
	my $rows = DBSelectNumResults("FROM invoices WHERE Number = ".DBQuote($number));
	if (!defined($rows)) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	return $rows > 0 ? 1 : 0;
}


# Return invoice ID from number
sub getInvoiceIDFromNumber
{
	my $number = shift;


	# Remove INV/
	$number = uc($number);
	$number =~ s#^INV/##;

	# Select invoice
	my $sth = DBSelect("
		SELECT
			ID
		FROM
			invoices
		WHERE
			Number = ".DBQuote($number)."
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	my $row = $sth->fetchrow_hashref();
	DBFreeRes($sth);

	# Check we got a result
	if (!defined($row)) {
		setError("Error finding client invoice '$number'");
		return ERR_NOTFOUND;
	}

	return $row->{'ID'};
}


# Backend function to parse an item description
sub parseInvItemDesc
{
	my ($desc,$invoice,$inventoryItem) = @_;


	# Parse in description
	$desc =~ s/%d/$inventoryItem->{'Description'}/g;

	# And dates...
	my $unixtime = str2time($invoice->{'IssueDate'});
	my $date = DateTime->from_epoch( epoch => $unixtime )->set_day(1);

	# This month
	my $thismonth = $date->ymd();
	$desc =~ s/%thismonth/$thismonth/g;

	# Last month
	$date->subtract( months => 1);
	my $lastmonth = $date->ymd();
	$desc =~ s/%lastmonth/$lastmonth/g;

	# Next month
	$date->add( months => 2);
	my $nextmonth = $date->ymd();
	$desc =~ s/%nextmonth/$nextmonth/g;

	# Next next month
	$date->add( months => 1);
	my $nextnextmonth = $date->ymd();
	$desc =~ s/%nextnextmonth/$nextnextmonth/g;

	return $desc;
}


# Return an array of client invoices
# Optional
#		Type - "unpaid", "all"
sub getInvoices
{
	my ($detail) = @_;
	my $type = defined($detail->{'Type'}) ? $detail->{'Type'} : "unpaid";
	my @invoices = ();


	# Return list of invoices
	my $sth = DBSelect("
		SELECT
			invoices.ID, invoices.ClientID, invoices.Number,
			invoices.IssueDate, invoices.DueDate, invoices.OrderNumber,
			invoices.DiscountTotal, invoices.SubTotal, invoices.TaxTotal, invoices.Total,
			invoices.GLTransactionID, invoices.Paid
		FROM
			invoices
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	# Fetch rows
	while (my $row = $sth->fetchrow_hashref()) {
		# Check what kind of invoices we want
		if (($type eq "unpaid") && $row->{'Paid'} eq "0") {
			push(@invoices,sanitizeRawItem($row));
		} elsif ($type eq "all") {
			push(@invoices,sanitizeRawItem($row));
		}
	}

	DBFreeRes($sth);

	return \@invoices;
}


# Return a client invoice
# Optional:
#		ID		- Client invoice ID
#		Number	- Client invoice number
sub getInvoice
{
	my ($detail) = @_;


	# Check which 'mode' we operating in
	my $invoiceID;
	if (!defined($detail->{'ID'}) || $detail->{'ID'} < 1) {
		# Verify invoice number
		if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
			setError("No (or invalid) invoice number provided");
			return ERR_PARAM;
		}

		# Check if invoice exists
		if (($invoiceID = getInvoiceIDFromNumber($detail->{'Number'})) < 1) {
			setError(Error());
			return $invoiceID;
		}
	} else {
		$invoiceID = $detail->{'ID'};
	}

	# Verify invoice ID
	if (!$invoiceID || $invoiceID < 1) {
		setError("No (or invalid) invoice number/id provided");
		return ERR_PARAM;
	}


	# Return invoice details
	my $sth = DBSelect("
		SELECT
			invoices.ID, invoices.ClientID, invoices.Number,
			invoices.ShippingAddress, invoices.TaxReference,
			invoices.IssueDate, invoices.DueDate, invoices.OrderNumber,
			invoices.DiscountTotal, invoices.SubTotal, invoices.TaxTotal, invoices.Total,
			invoices.Note,
			invoices.GLTransactionID, invoices.Paid
		FROM
			invoices
		WHERE
			invoices.ID = ".DBQuote($invoiceID)."
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	# Fetch row
	my $row = $sth->fetchrow_hashref();
	DBFreeRes($sth);

	return sanitizeRawItem($row);
}


# Return an array of invoice items
# Optional:
#		Number		- Client invoice number
#		ID			- Client invoice ID
sub getInvoiceItems
{
	my ($detail) = @_;

	my @items = ();


	# Grab invoice
	my $data;
	$data->{'Number'} = $detail->{'Number'};
	$data->{'ID'} = $detail->{'ID'};
	my $invoice = getInvoice($data);
	if (ref $invoice ne "HASH") {
		setError(Error());
		return $invoice;
	}

	# Return list of client invoice items
	my $sth = DBSelect("
		SELECT
			invoice_items.ID,
			invoice_items.SerialNumber,
			invoice_items.InventoryID, inventory.Code AS InventoryCode,
			invoice_items.Description, invoice_items.Quantity, invoice_items.Unit,
			invoice_items.UnitPrice, invoice_items.Price,
			invoice_items.Discount, invoice_items.DiscountAmount,
			invoice_items.TaxMode, invoice_items.TaxRate, invoice_items.TaxAmount
		FROM
			invoice_items, inventory
		WHERE
			invoice_items.InvoiceID = ".DBQuote($invoice->{'ID'})."
			AND inventory.ID = invoice_items.InventoryID
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	# Fetch rows
	while (my $row = $sth->fetchrow_hashref()) {
		my $item;

		$item->{'ID'} = $row->{'ID'};

		$item->{'InventoryID'} = $row->{'InventoryID'};
		$item->{'InventoryCode'} = $row->{'InventoryCode'};
		$item->{'Description'} = $row->{'Description'};
		$item->{'SerialNumber'} = $row->{'SerialNumber'};
		$item->{'Quantity'} = $row->{'Quantity'};
		$item->{'Unit'} = $row->{'Unit'};
		$item->{'UnitPrice'} = $row->{'UnitPrice'};
		$item->{'Price'} = $row->{'Price'};
		$item->{'Discount'} = $row->{'Discount'};
		$item->{'DiscountAmount'} = $row->{'DiscountAmount'};
		$item->{'TaxRate'} = $row->{'TaxRate'};
		$item->{'TaxAmount'} = $row->{'TaxAmount'};

		# Calculate final price
		my $totalPrice = Math::BigFloat->new($row->{'Price'});
		$totalPrice->precision(-2);
		if ($row->{'TaxMode'} eq "2") {
			$totalPrice->badd($row->{'TaxAmount'});
		}
		$item->{'TotalPrice'} = $totalPrice->bstr();


		push(@items,$item);
	}

	DBFreeRes($sth);

	return \@items;
}


# Create client invoice
# Parameters:
#		ClientCode		- Client code
#		Number			- Invoice number
#		IssueDate		- Issue date
#		DueDate			- Due date
# Optional:
#		ShippingAddress	- Shipping address
#		OrderNumber		- Order number
#		Note			- Notes
sub createInvoice
{
	my ($detail) = @_;


	my @extraCols = ();
	my @extraData = ();


	# Verify client code
	if (!defined($detail->{'ClientCode'}) || $detail->{'ClientCode'} eq "") {
		setError("No client code provided for client invoice");
		return ERR_PARAM;
	}

	# Verify invoice number
	if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
		setError("No invoice number provided for client invoice");
		return ERR_PARAM;
	}
	# Remove INV/
	(my $invNumber = uc($detail->{'Number'})) =~ s#^INV/##;

	# Verify issue date
	if (!defined($detail->{'IssueDate'}) || $detail->{'IssueDate'} eq "") {
		setError("No issue date provided for client invoice '$invNumber'");
		return ERR_PARAM;
	}

	# Verify due date
	if (!defined($detail->{'DueDate'}) || $detail->{'DueDate'} eq "") {
		setError("No due date provided for client invoice '$invNumber'");
		return ERR_PARAM;
	}

	# Check if client exists & pull
	my $data;
	$data->{'Code'} = $detail->{'ClientCode'};
	my $client = wiaflos::server::Clients::getClient($data);
	if (ref $client ne "HASH") {
		setError(wiaflos::server::Clients::Error());
		return $client;
	}

	# Check for conflicts
	if ((my $res = invoiceNumberExists($invNumber)) != 0) {
		# Err if already exists
		if ($res == 1) {
			setError("Client invoice number '$invNumber' already exists");
			return ERR_CONFLICT;
		} else {
			setError(Error());
		}
		# else err with result
		return $res;
	}

	# Add order number if exists
	if (defined($detail->{'OrderNumber'}) && $detail->{'OrderNumber'} ne "") {
		push(@extraCols,'OrderNumber');
		push(@extraData,DBQuote($detail->{'OrderNumber'}));
	}

	# Add note if it exists
	if (defined($detail->{'Note'}) && $detail->{'Note'} ne "") {
		push(@extraCols,'Note');
		push(@extraData,DBQuote($detail->{'Note'}));
	}

	# Get addresses
	my $shipAddr;
	if (defined($detail->{'ShippingAddress'}) && $detail->{'ShippingAddress'} ne "") {
		$shipAddr = $detail->{'ShippingAddress'};
	} else {
		my $data;
		$data->{'ID'} = $client->{'ID'};
		my $addresses = wiaflos::server::Clients::getClientAddresses($data);
		if (ref $addresses ne "ARRAY") {
			setError(wiaflos::server::Clients::Error());
			return $addresses;
		}

		my $billAddr;
		foreach my $address (@{$addresses}) {
			if ($address->{'Type'} eq "shipping") {
				$shipAddr = $address->{'Address'};
			} elsif ($address->{'Type'} eq "billing") {
				$billAddr = $address->{'Address'};
			}
		}

		# If we've not found a shipping address use the billing address
		if (!defined($shipAddr)) {
			$shipAddr = $billAddr;
		}
	}

	# If still not got a shipping address, panic
	if (!defined($shipAddr)) {
		setError("Could not determine shipping address for client invoice '$invNumber'");
		return ERR_NODETADDR;
	}


	# Pull in stuff we need
	if (defined($client->{'TaxReference'}) && $client->{'TaxReference'} ne "") {
		push(@extraCols,'TaxReference');
		push(@extraData,DBQuote($client->{'TaxReference'}));
	}

	# Pull in extra data
	my $extraCols = "";
	my $extraData = "";
	if (@extraCols > 0 && @extraData > 0) {
		$extraCols .= ',' . join(',',@extraCols);
		$extraData .= ',' . join(',',@extraData);
	}

	# Create client invoice
	my $sth = DBDo("
		INSERT INTO invoices
				(ClientID,Number,ShippingAddress,IssueDate,DueDate$extraCols)
			VALUES
				(
					".DBQuote($client->{'ID'}).",
					".DBQuote($invNumber).",
					".DBQuote($shipAddr).",
					".DBQuote($detail->{'IssueDate'}).",
					".DBQuote($detail->{'DueDate'})."
					$extraData
				)
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	# Grab last ID
	my $ID = DBLastInsertID("invoices","ID");

	return $ID;
}


# Link item to invoice
# Parameters:
#		Number				- Client invoice number
#		InventoryCode		- Inventory code
# Optional:
#		Description			- Description
#		Unit				- Unit
#		UnitPrice			- UnitPrice
#		Quantity			- Quantity
#		Discount			- Discount rate in %
#		TaxTypeID			- Tax type ID override
#		SerialNumber		- Used by tracked inventory products, this is normally the serial number of the item. Or in any other case
#							can be used a a batch number, or anything as a matter of fact.
sub linkInvoiceItem
{
	my ($detail) = @_;


	my @extraCols = ();
	my @extraData = ();


	# Verify client invoice number
	if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
		setError("No client invoice number provided");
		return ERR_PARAM;
	}

	# Verify inventory code
	if (!defined($detail->{'InventoryCode'}) || $detail->{'InventoryCode'} eq "") {
		setError("No inventory code provided for client invoice '".$detail->{'Number'}."'");
		return ERR_PARAM;
	}

	# Check if client invoice exists
	my $tmp;
	$tmp->{'Number'} = $detail->{'Number'};
	my $invoice = getInvoice($tmp);
	if (ref $invoice ne "HASH") {
		setError(Error());
		return $invoice;
	}

	# Get quantity. NOTE: We do this soo high up so we can get the stock item below.
	my $quantity = Math::BigFloat->new();
	$quantity->precision(-4);
	if (defined($detail->{'Quantity'})) {
		$quantity->badd($detail->{'Quantity'});
	} else {
		$quantity->badd(1);
	}

	# Grab inventory item, if exists
	$tmp = undef;
	$tmp->{'Code'} = $detail->{'InventoryCode'};
	$tmp->{'SerialNumber'} = $detail->{'SerialNumber'};
	$tmp->{'Quantity'} = $quantity->bstr();
	my $inventoryItem = wiaflos::server::Inventory::getInventoryStockItem($tmp);
	if (ref $inventoryItem ne "HASH") {
		setError(wiaflos::server::Inventory::Error());
		return $inventoryItem;
	}

	# Check discountability
	my $discount = Math::BigFloat->new();
	if (defined($detail->{'Discount'})) {
		$discount->badd($detail->{'Discount'});
		if ($inventoryItem->{'Discountable'} eq 'no' && !$discount->is_zero()) {
			setError("Discount given for non-discountable item '".$inventoryItem->{'Code'}."' on client invoice '".$invoice->{'Number'}."'");
			return ERR_DISCNDISC;
		}
	}


	# If we have an item serial number, add it aswell
	if (defined($detail->{'SerialNumber'}) && $detail->{'SerialNumber'} ne "") {
		push(@extraCols,'SerialNumber');
		push(@extraData,DBQuote($detail->{'SerialNumber'}));
	} else {
		# Tracked items must have a serial number
		if ($inventoryItem->{'Mode'} eq "track") {
			setError("Inventory item '".$inventoryItem->{'Code'}."' is tracked, a serial number MUST be provided on client invoice '".$invoice->{'Number'}."'");
			return ERR_USAGE;
		}
	}

	# Get description
	my $description;
	if (defined($detail->{'Description'}) && $detail->{'Description'} ne "") {
		$description = $detail->{'Description'};
	} else {
		$description = $inventoryItem->{'Description'};
	}

	# Check if tax type exists
	$tmp = undef;
	$tmp->{'TaxTypeID'} = defined($detail->{'TaxTypeID'}) ? $detail->{'TaxTypeID'} : $inventoryItem->{'TaxTypeID'};
	my $taxType = wiaflos::server::Tax::getTaxType($tmp);
	if (ref $taxType ne "HASH") {
		setError(wiaflos::server::Tax::Error());
		return $taxType;
	}

	# Pull in tax rate from the type we got above & set precision
	my $taxRate = Math::BigFloat->new($taxType->{'TaxRate'});

	# Get price
	my $sellPrice = Math::BigFloat->new();
	if (defined($detail->{'UnitPrice'})) {
		$sellPrice->badd($detail->{'UnitPrice'});
	} else {
		$sellPrice->badd($inventoryItem->{'SellPrice'});
	}

	# If we have an item unit, add it aswell
	if (defined($inventoryItem->{'Unit'}) && $inventoryItem->{'Unit'} ne "") {
		push(@extraCols,'Unit');
		push(@extraData,DBQuote($inventoryItem->{'Unit'}));
	}

	# Total up final price
	my $totalPrice = Math::BigFloat->new($sellPrice);
	$totalPrice->precision(-4);  # Increase precision for below calculations
	$totalPrice->bmul($quantity);

	# Calculate discount
	if (!$discount->is_zero()) {
		# Set totalprice
		my $discountAmount = Math::BigFloat->new();
		$discountAmount->precision(-4);
		$discountAmount->badd($totalPrice);

		# Get discount multiplier
		my $tmpDisc = $discount->copy();
		$tmpDisc->precision(-4);
		$tmpDisc->bdiv(100);

		# Get discount amount
		$discountAmount->bmul($tmpDisc);

		# Remove discount from total price
		$totalPrice->bsub($discountAmount);

		# Reduce precision
		$discountAmount->precision(-2);

		# All columns
		push(@extraCols,'Discount');
		push(@extraData,DBQuote($discount->bstr()));
		push(@extraCols,'DiscountAmount');
		push(@extraData,DBQuote($discountAmount->bstr()));
	}

	# Get ready for calculating tax
	my ($taxAmount,$taxMode) = wiaflos::server::Tax::getTaxAmount($inventoryItem->{'TaxMode'},$taxRate,$totalPrice);
	if (!defined($taxAmount) || !defined($taxMode)) {
		setError(wiaflos::server::Tax::Error());
		return ERR_USAGE;
	}

	# Reduce precision now
	$totalPrice->precision(-2);

	# Parse inventory item description
	my $itemDesc = parseInvItemDesc($description,$invoice,$inventoryItem);

	# Pull in extra data
	my $extraCols = "";
	my $extraData = "";
	if (@extraCols > 0 && @extraData > 0) {
		$extraCols .= ',' . join(',',@extraCols);
		$extraData .= ',' . join(',',@extraData);
	}

	# Link in invoice item
	my $sth = DBDo("
		INSERT INTO invoice_items
				(InvoiceID,InventoryID,Description,Quantity,UnitPrice,Price,TaxTypeID,TaxMode,TaxRate,TaxAmount$extraCols)
			VALUES
				(
					".DBQuote($invoice->{'ID'}).",
					".DBQuote($inventoryItem->{'ID'}).",
					".DBQuote($itemDesc).",
					".DBQuote($quantity->bstr()).",
					".DBQuote($sellPrice->bstr()).",
					".DBQuote($totalPrice->bstr()).",
					".DBQuote($taxType->{'ID'}).",
					".DBQuote($taxMode).",
					".DBQuote($taxRate->bstr()).",
					".DBQuote($taxAmount)."
					$extraData
				)
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	# Grab last ID
	my $ID = DBLastInsertID("invoice_items","ID");

	return $ID;
}


# Post invoice
# Parameters:
#		Number	- Client invoice number
sub postInvoice
{
	my ($detail) = @_;

	my $data;


	# Grab invoice
	$data = undef;
	$data->{'Number'} = $detail->{'Number'};
	my $invoice = getInvoice($data);
	if (ref $invoice ne "HASH") {
		setError(Error());
		return $invoice;
	}
	# Check if we already posted or not
	if ($invoice->{'GLTransactionID'}) {
		setError("Client invoice '".$invoice->{'Number'}."' already posted");
		return ERR_POSTED;
	}

	# Grab client
	$data = undef;
	$data->{'ID'} = $invoice->{'ClientID'};
	my $client = wiaflos::server::Clients::getClient($data);
	if (ref $client ne "HASH") {
		setError(wiaflos::server::Clients::Error());
		return $client;
	}

	# Return list of client invoice items
	my $sth = DBSelect("
		SELECT
			invoice_items.ID,
			invoice_items.InventoryID,
			invoice_items.SerialNumber,
			invoice_items.Quantity,
			invoice_items.DiscountAmount, invoice_items.UnitPrice, invoice_items.Price,
			invoice_items.TaxTypeID, invoice_items.TaxMode, invoice_items.TaxAmount,

			tax_types.GLAccountID
		FROM
			invoice_items, tax_types
		WHERE
			invoice_items.InvoiceID = ".DBQuote($invoice->{'ID'})."
			AND tax_types.ID = invoice_items.TaxTypeID
	");
	if (!$sth) {
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
	}

	DBBegin();


	# Create transaction
	my $transactionRef = sprintf('Invoice: %s',$invoice->{'Number'});
	$data = undef;
	$data->{'Date'} = $invoice->{'IssueDate'};
	$data->{'Reference'} = $transactionRef;
	my $GLTransActID = wiaflos::server::GL::createGLTransaction($data);
	if ($GLTransActID < 1) {
		setError(wiaflos::server::GL::Error());
		DBRollback();
		return $GLTransActID;
	}

	# These are the totals that will be posted to the GL
	my %taxEntries;
	my %stockEntries;
	my %expEntries;
	my %incEntries;
	my $discTotal = Math::BigFloat->new();
	my $subTotal = Math::BigFloat->new();
	my $invTotal = Math::BigFloat->new();

	# Fetch rows
	while (my $row = $sth->fetchrow_hashref()) {
		# Pull quantity as we do math below, else we wouldn't need to
		my $quantity = Math::BigFloat->new();
		$quantity->precision(-4);
		$quantity->badd($row->{'Quantity'});

		# Calculate final prices
		my $itemInclPrice = Math::BigFloat->new($row->{'Price'});
		$itemInclPrice->badd($row->{'TaxAmount'}) if ($row->{'TaxMode'} eq "2");

		my $itemExclPrice = Math::BigFloat->new($row->{'Price'});
		$itemExclPrice->bsub($row->{'TaxAmount'}) if ($row->{'TaxMode'} eq "1");

		# Pull in tax GL account and addup what we need to post
		if (defined($taxEntries{$row->{'GLAccountID'}})) {
			$taxEntries{$row->{'GLAccountID'}}->badd($row->{'TaxAmount'});
		} else {
			$taxEntries{$row->{'GLAccountID'}} = Math::BigFloat->new($row->{'TaxAmount'});
		}

		# Build query, and pull in item
		$data = undef;
		$data->{'ID'} = $row->{'InventoryID'};
		$data->{'SerialNumber'} = $row->{'SerialNumber'};  # Send serial number to ensure we get a cost price for tracked items
		$data->{'Quantity'} = $row->{'Quantity'};
		my $inventoryItem = wiaflos::server::Inventory::getInventoryStockItem($data);
		if (ref $inventoryItem ne 'HASH') {
			setError(wiaflos::server::Inventory::Error());
			DBRollback();
			return $inventoryItem;
		}

		# Work out cost price & stock list we require for adjustment
		my $costPrice = Math::BigFloat->new();
		my @stockAdjustmentList;
		# Check if this is a tracked item, if so add up all the stock we need
		foreach my $stockItem (@{$inventoryItem->{'StockRequired'}}) {
			# Get total cost of all items
			$costPrice->badd($stockItem->{'Cost'});

			# Create the stock list to adjust stock
			my $adjustItem;
			$adjustItem->{'InventoryTrackingID'} = $stockItem->{'InventoryTrackingID'};
			$adjustItem->{'Quantity'} = $stockItem->{'QuantityNeeded'};
			$adjustItem->{'Cost'} = $stockItem->{'Cost'};
			push(@stockAdjustmentList,$adjustItem);
		}

		# Pull in stock control account
		if (defined($inventoryItem->{'GLAssetAccountID'})) {
			if (defined($stockEntries{$inventoryItem->{'GLAssetAccountID'}})) {
				$stockEntries{$inventoryItem->{'GLAssetAccountID'}}->badd($costPrice);
			} else {
				$stockEntries{$inventoryItem->{'GLAssetAccountID'}} = $costPrice->copy();
			}
		}

		# Pull in expense account (cost of goods sold)
		if (defined($inventoryItem->{'GLExpenseAccountID'})) {
			if (defined($expEntries{$inventoryItem->{'GLExpenseAccountID'}})) {
				$expEntries{$inventoryItem->{'GLExpenseAccountID'}}->badd($costPrice);
			} else {
				$expEntries{$inventoryItem->{'GLExpenseAccountID'}} = $costPrice->copy();
			}
		}

		# Pull in income account, if amount is zero its probably discounted 100%
		if (defined($inventoryItem->{'GLIncomeAccountID'}) && !$itemExclPrice->is_zero()) {
			if (defined($incEntries{$inventoryItem->{'GLIncomeAccountID'}})) {
				$incEntries{$inventoryItem->{'GLIncomeAccountID'}}->badd($itemExclPrice);
			} else {
				$incEntries{$inventoryItem->{'GLIncomeAccountID'}} = $itemExclPrice->copy();
			}
		}

		# Adjust stock
		$quantity->bneg(); # Get negative
		$data = undef;
		$data->{'ID'} = $row->{'InventoryID'};
		$data->{'GLTransactionID'} = $GLTransActID;
		$data->{'QtyChange'} = $quantity->bstr();
		$data->{'SerialNumber'} = $row->{'SerialNumber'};
		$data->{'StockAdjustmentList'} = \@stockAdjustmentList;
		# Adjust stock and check for errors
		my $trackingInfo = wiaflos::server::Inventory::adjustInventoryStock($data);
		if (ref $trackingInfo ne "ARRAY") {
			setError(wiaflos::server::Inventory::Error());
			DBRollback();
			return $trackingInfo;
		}

		# Loop with tracking
		foreach my $trackingInfoItem (@{$trackingInfo}) {
			# Link in tracking information
			if ($trackingInfoItem->{'InventoryTrackingID'} > 0) {
				# Create inventory tracking
				my $sth2 = DBDo("
					INSERT INTO invoice_item_tracking
							(InvoiceItemID,InventoryTrackingID)
						VALUES
							(
								".DBQuote($row->{'ID'}).",
								".DBQuote($trackingInfoItem->{'InventoryTrackingID'})."
							)
				");
				if (!$sth2) {
					setError(wiaflos::server::dblayer::Error());
					DBRollback();
					return ERR_DB;
				}

				my $ID = DBLastInsertID("inventory_item_tracking","ID");
			}
		}

		# Addup inv total
		$discTotal->badd($row->{'DiscountAmount'}) if (defined($row->{'DiscountAmount'}));
		$subTotal->badd($itemExclPrice);
		$invTotal->badd($itemInclPrice);
	}

	DBFreeRes($sth);

	# Link invoice total to client GL account
	# Post transaction
	$data = undef;
	$data->{'ID'} = $GLTransActID;
	$data->{'GLAccountID'} = $client->{'GLAccountID'};
	$data->{'Amount'} = $invTotal->bstr();
	if ((my $res = wiaflos::server::GL::linkGLTransaction($data)) < <