Invoicing.pm 40.4 KB
Newer Older
1
# Invoicing functions
2
# Copyright (C) 2009-2014, AllWorldIT
3
# Copyright (C) 2008, LinuxRulz
Nigel Kukard's avatar
Nigel Kukard committed
4
# Copyright (C) 2007 Nigel Kukard  <nkukard@lbsd.net>
5
#
6
7
8
9
# 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.
10
#
11
12
13
14
# 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.
15
#
16
17
18
19
# 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.

20
21
22



Nigel Kukard's avatar
Nigel Kukard committed
23
package wiaflos::server::Invoicing;
24
25

use strict;
Nigel Kukard's avatar
Nigel Kukard committed
26
27
use warnings;

28

Nigel Kukard's avatar
Nigel Kukard committed
29
use wiaflos::version;
Nigel Kukard's avatar
Nigel Kukard committed
30
use wiaflos::constants;
Nigel Kukard's avatar
Nigel Kukard committed
31
32
use wiaflos::server::config;
use wiaflos::server::dblayer;
33
use wiaflos::server::templating;
Nigel Kukard's avatar
Nigel Kukard committed
34
use wiaflos::server::GL;
35
use wiaflos::server::Inventory;
Nigel Kukard's avatar
Nigel Kukard committed
36
use wiaflos::server::Clients;
Nigel Kukard's avatar
Nigel Kukard committed
37
use wiaflos::server::Tax;
38
use wiaflos::server::Tax;
39

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

44
45
use Date::Parse;
use DateTime;
46
47
use Crypt::GPG;
use MIME::Lite;
48
49
50
51
52
53
54
55
56
57


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

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

	# Set error
62
	$error = "$subroutine($line): $err";
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
}

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

	# Reset error
	$error = "";

	# Return error
	return $err;
}



80
81
82
83
# Backend function to build item hash
sub sanitizeRawItem
{
	my $rawData = shift;
84
85


86
	my $item;
87

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

Nigel Kukard's avatar
Nigel Kukard committed
90
91
	$item->{'ClientID'} = $rawData->{'ClientID'};
	$item->{'Number'} = "INV/".uc($rawData->{'Number'});
92

Nigel Kukard's avatar
Nigel Kukard committed
93
94
	$item->{'ShippingAddress'} = $rawData->{'ShippingAddress'};
	$item->{'TaxReference'} = $rawData->{'TaxReference'};
95

Nigel Kukard's avatar
Nigel Kukard committed
96
97
98
	$item->{'IssueDate'} = $rawData->{'IssueDate'};
	$item->{'DueDate'} = $rawData->{'DueDate'};
	$item->{'OrderNumber'} = $rawData->{'OrderNumber'};
99

Nigel Kukard's avatar
Nigel Kukard committed
100
101
102
103
	$item->{'DiscountTotal'} = $rawData->{'DiscountTotal'};
	$item->{'SubTotal'} = $rawData->{'SubTotal'};
	$item->{'TaxTotal'} = $rawData->{'TaxTotal'};
	$item->{'Total'} = $rawData->{'Total'};
104

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

Nigel Kukard's avatar
Nigel Kukard committed
107
	$item->{'GLTransactionID'} = $rawData->{'GLTransactionID'};
Nigel Kukard's avatar
Nigel Kukard committed
108
	$item->{'Posted'} = defined($rawData->{'GLTransactionID'}) ? 1 : 0;
109

Nigel Kukard's avatar
Nigel Kukard committed
110
	$item->{'Paid'} = $rawData->{'Paid'};
111
112
113
114
115
116
117

	return $item;
}


# Check if invoice exists
# Backend function, takes 1 parameter which is the invoice ID
Nigel Kukard's avatar
Nigel Kukard committed
118
sub invoiceIDExists
119
120
121
122
{
	my $invoiceID = shift;


123
124
125
	# Select invoice count
	my $rows = DBSelectNumResults("FROM invoices WHERE ID = ".DBQuote($invoiceID));
	if (!defined($rows)) {
Nigel Kukard's avatar
Nigel Kukard committed
126
127
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
128
129
	}

130
	return $rows > 0 ? 1 : 0;
131
132
133
}


134
135
136
# Check if invoice number exists
sub invoiceNumberExists
{
Nigel Kukard's avatar
Nigel Kukard committed
137
	my $number = shift;
138

Nigel Kukard's avatar
Nigel Kukard committed
139
140
141
	# Remove INV/
	$number = uc($number);
	$number =~ s#^INV/##;
142

143
144
145
	# Select invoice count
	my $rows = DBSelectNumResults("FROM invoices WHERE Number = ".DBQuote($number));
	if (!defined($rows)) {
Nigel Kukard's avatar
Nigel Kukard committed
146
147
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
148
149
150
151
152
153
	}

	return $rows > 0 ? 1 : 0;
}


Nigel Kukard's avatar
Nigel Kukard committed
154
155
# Return invoice ID from number
sub getInvoiceIDFromNumber
156
{
Nigel Kukard's avatar
Nigel Kukard committed
157
	my $number = shift;
158
159


Nigel Kukard's avatar
Nigel Kukard committed
160
161
162
163
	# Remove INV/
	$number = uc($number);
	$number =~ s#^INV/##;

164
165
	# Select invoice
	my $sth = DBSelect("
166
		SELECT
167
168
169
170
			ID
		FROM
			invoices
		WHERE
Nigel Kukard's avatar
Nigel Kukard committed
171
			Number = ".DBQuote($number)."
172
	");
Nigel Kukard's avatar
Nigel Kukard committed
173
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
174
175
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
176
177
	}

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

181
	# Check we got a result
182
	if (!defined($row)) {
183
		setError("Error finding client invoice '$number'");
Nigel Kukard's avatar
Nigel Kukard committed
184
		return ERR_NOTFOUND;
185
186
187
188
189
190
	}

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


191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
# 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;
}

226
227

# Return an array of client invoices
228
229
# Optional
#		Type - "unpaid", "all"
230
231
sub getInvoices
{
232
233
	my ($detail) = @_;
	my $type = defined($detail->{'Type'}) ? $detail->{'Type'} : "unpaid";
234
235
236
237
238
	my @invoices = ();


	# Return list of invoices
	my $sth = DBSelect("
239
240
		SELECT
			invoices.ID, invoices.ClientID, invoices.Number,
241
			invoices.IssueDate, invoices.DueDate, invoices.OrderNumber,
Nigel Kukard's avatar
Nigel Kukard committed
242
243
			invoices.DiscountTotal, invoices.SubTotal, invoices.TaxTotal, invoices.Total,
			invoices.GLTransactionID, invoices.Paid
244
245
246
		FROM
			invoices
	");
Nigel Kukard's avatar
Nigel Kukard committed
247
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
248
249
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
250
251
252
253
	}

	# Fetch rows
	while (my $row = $sth->fetchrow_hashref()) {
254
		# Check what kind of invoices we want
Nigel Kukard's avatar
Nigel Kukard committed
255
		if (($type eq "unpaid") && $row->{'Paid'} eq "0") {
Nigel Kukard's avatar
Nigel Kukard committed
256
			push(@invoices,sanitizeRawItem($row));
257
		} elsif ($type eq "all") {
Nigel Kukard's avatar
Nigel Kukard committed
258
			push(@invoices,sanitizeRawItem($row));
259
		}
260
261
262
263
264
265
266
267
268
	}

	DBFreeRes($sth);

	return \@invoices;
}


# Return a client invoice
Nigel Kukard's avatar
Nigel Kukard committed
269
# Optional:
270
271
#		ID		- Client invoice ID
#		Number	- Client invoice number
272
273
274
275
276
sub getInvoice
{
	my ($detail) = @_;


277
278
	# Check which 'mode' we operating in
	my $invoiceID;
Nigel Kukard's avatar
Nigel Kukard committed
279
280
281
282
283
	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;
284
285
286
		}

		# Check if invoice exists
Nigel Kukard's avatar
Nigel Kukard committed
287
		if (($invoiceID = getInvoiceIDFromNumber($detail->{'Number'})) < 1) {
288
			setError(Error());
289
290
291
			return $invoiceID;
		}
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
292
		$invoiceID = $detail->{'ID'};
293
	}
294

295
296
	# Verify invoice ID
	if (!$invoiceID || $invoiceID < 1) {
Nigel Kukard's avatar
Nigel Kukard committed
297
298
		setError("No (or invalid) invoice number/id provided");
		return ERR_PARAM;
299
300
301
302
303
	}


	# Return invoice details
	my $sth = DBSelect("
304
305
		SELECT
			invoices.ID, invoices.ClientID, invoices.Number,
306
			invoices.ShippingAddress, invoices.TaxReference,
307
			invoices.IssueDate, invoices.DueDate, invoices.OrderNumber,
Nigel Kukard's avatar
Nigel Kukard committed
308
			invoices.DiscountTotal, invoices.SubTotal, invoices.TaxTotal, invoices.Total,
309
			invoices.Note,
Nigel Kukard's avatar
Nigel Kukard committed
310
			invoices.GLTransactionID, invoices.Paid
311
312
313
		FROM
			invoices
		WHERE
314
			invoices.ID = ".DBQuote($invoiceID)."
315
	");
Nigel Kukard's avatar
Nigel Kukard committed
316
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
317
318
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
319
320
321
322
323
324
325
326
327
328
329
	}

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

	return sanitizeRawItem($row);
}


# Return an array of invoice items
330
# Optional:
Nigel Kukard's avatar
Nigel Kukard committed
331
332
#		Number		- Client invoice number
#		ID			- Client invoice ID
333
334
335
sub getInvoiceItems
{
	my ($detail) = @_;
336

337
338
339
340
	my @items = ();


	# Grab invoice
341
	my $data;
Nigel Kukard's avatar
Nigel Kukard committed
342
343
	$data->{'Number'} = $detail->{'Number'};
	$data->{'ID'} = $detail->{'ID'};
344
	my $invoice = getInvoice($data);
345
	if (ref $invoice ne "HASH") {
346
		setError(Error());
347
348
349
350
351
		return $invoice;
	}

	# Return list of client invoice items
	my $sth = DBSelect("
352
353
		SELECT
			invoice_items.ID,
Nigel Kukard's avatar
Nigel Kukard committed
354
355
			invoice_items.SerialNumber,
			invoice_items.InventoryID, inventory.Code AS InventoryCode,
356
357
			invoice_items.Description, invoice_items.Quantity, invoice_items.Unit,
			invoice_items.UnitPrice, invoice_items.Price,
358
			invoice_items.Discount, invoice_items.DiscountAmount,
359
360
			invoice_items.TaxMode, invoice_items.TaxRate, invoice_items.TaxAmount
		FROM
361
			invoice_items, inventory
362
		WHERE
363
			invoice_items.InvoiceID = ".DBQuote($invoice->{'ID'})."
364
			AND inventory.ID = invoice_items.InventoryID
365
	");
Nigel Kukard's avatar
Nigel Kukard committed
366
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
367
368
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
369
370
371
372
	}

	# Fetch rows
	while (my $row = $sth->fetchrow_hashref()) {
Nigel Kukard's avatar
Nigel Kukard committed
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
		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'};
389
390
391
392

		# Calculate final price
		my $totalPrice = Math::BigFloat->new($row->{'Price'});
		$totalPrice->precision(-2);
Nigel Kukard's avatar
Nigel Kukard committed
393
		if ($row->{'TaxMode'} eq "2") {
Nigel Kukard's avatar
Nigel Kukard committed
394
			$totalPrice->badd($row->{'TaxAmount'});
395
396
397
398
		}
		$item->{'TotalPrice'} = $totalPrice->bstr();


Nigel Kukard's avatar
Nigel Kukard committed
399
		push(@items,$item);
400
401
402
403
404
405
406
407
408
409
	}

	DBFreeRes($sth);

	return \@items;
}


# Create client invoice
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
410
411
#		ClientCode		- Client code
#		Number			- Invoice number
412
413
414
#		IssueDate		- Issue date
#		DueDate			- Due date
# Optional:
415
#		ShippingAddress	- Shipping address
416
417
418
419
420
421
422
423
424
425
426
#		OrderNumber		- Order number
#		Note			- Notes
sub createInvoice
{
	my ($detail) = @_;


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


Nigel Kukard's avatar
Nigel Kukard committed
427
428
	# Verify client code
	if (!defined($detail->{'ClientCode'}) || $detail->{'ClientCode'} eq "") {
429
		setError("No client code provided for client invoice");
Nigel Kukard's avatar
Nigel Kukard committed
430
		return ERR_PARAM;
431
432
	}

Nigel Kukard's avatar
Nigel Kukard committed
433
434
	# Verify invoice number
	if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
435
		setError("No invoice number provided for client invoice");
Nigel Kukard's avatar
Nigel Kukard committed
436
		return ERR_PARAM;
437
	}
Nigel Kukard's avatar
Nigel Kukard committed
438
439
	# Remove INV/
	(my $invNumber = uc($detail->{'Number'})) =~ s#^INV/##;
440
441
442

	# Verify issue date
	if (!defined($detail->{'IssueDate'}) || $detail->{'IssueDate'} eq "") {
443
		setError("No issue date provided for client invoice '$invNumber'");
Nigel Kukard's avatar
Nigel Kukard committed
444
		return ERR_PARAM;
445
	}
446

447
448
	# Verify due date
	if (!defined($detail->{'DueDate'}) || $detail->{'DueDate'} eq "") {
449
		setError("No due date provided for client invoice '$invNumber'");
Nigel Kukard's avatar
Nigel Kukard committed
450
		return ERR_PARAM;
451
	}
452

453
	# Check if client exists & pull
454
	my $data;
Nigel Kukard's avatar
Nigel Kukard committed
455
	$data->{'Code'} = $detail->{'ClientCode'};
Nigel Kukard's avatar
Nigel Kukard committed
456
	my $client = wiaflos::server::Clients::getClient($data);
457
	if (ref $client ne "HASH") {
Nigel Kukard's avatar
Nigel Kukard committed
458
		setError(wiaflos::server::Clients::Error());
459
		return $client;
460
461
	}

462
	# Check for conflicts
Nigel Kukard's avatar
Nigel Kukard committed
463
	if ((my $res = invoiceNumberExists($invNumber)) != 0) {
464
465
		# Err if already exists
		if ($res == 1) {
466
			setError("Client invoice number '$invNumber' already exists");
467
			return ERR_CONFLICT;
468
469
		} else {
			setError(Error());
470
471
472
		}
		# else err with result
		return $res;
473
474
	}

475
476
477
478
479
480
481
482
483
484
485
486
	# 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'}));
	}

487
488
489
490
491
492
	# Get addresses
	my $shipAddr;
	if (defined($detail->{'ShippingAddress'}) && $detail->{'ShippingAddress'} ne "") {
		$shipAddr = $detail->{'ShippingAddress'};
	} else {
		my $data;
Nigel Kukard's avatar
Nigel Kukard committed
493
494
		$data->{'ID'} = $client->{'ID'};
		my $addresses = wiaflos::server::Clients::getClientAddresses($data);
495
		if (ref $addresses ne "ARRAY") {
Nigel Kukard's avatar
Nigel Kukard committed
496
			setError(wiaflos::server::Clients::Error());
497
498
			return $addresses;
		}
499

500
501
		my $billAddr;
		foreach my $address (@{$addresses}) {
502
			if ($address->{'Type'} eq "shipping") {
503
				$shipAddr = $address->{'Address'};
504
			} elsif ($address->{'Type'} eq "billing") {
505
506
507
508
509
510
511
512
513
514
515
516
				$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)) {
517
		setError("Could not determine shipping address for client invoice '$invNumber'");
Nigel Kukard's avatar
Nigel Kukard committed
518
		return ERR_NODETADDR;
519
520
521
522
523
524
525
526
527
	}


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

528
529
530
531
532
533
534
535
536
537
	# 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("
538
539
		INSERT INTO invoices
				(ClientID,Number,ShippingAddress,IssueDate,DueDate$extraCols)
540
541
			VALUES
				(
542
					".DBQuote($client->{'ID'}).",
Nigel Kukard's avatar
Nigel Kukard committed
543
					".DBQuote($invNumber).",
544
					".DBQuote($shipAddr).",
545
546
547
548
549
					".DBQuote($detail->{'IssueDate'}).",
					".DBQuote($detail->{'DueDate'})."
					$extraData
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
550
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
551
552
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
553
554
555
556
557
558
559
560
561
562
563
	}

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

	return $ID;
}


# Link item to invoice
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
564
#		Number				- Client invoice number
565
#		InventoryCode		- Inventory code
566
567
# Optional:
#		Description			- Description
568
#		Unit				- Unit
569
570
#		UnitPrice			- UnitPrice
#		Quantity			- Quantity
571
#		Discount			- Discount rate in %
572
#		TaxTypeID			- Tax type ID override
Nigel Kukard's avatar
Nigel Kukard committed
573
#		SerialNumber		- Used by tracked inventory products, this is normally the serial number of the item. Or in any other case
574
#							can be used a a batch number, or anything as a matter of fact.
575
sub linkInvoiceItem
576
577
578
579
580
581
582
583
{
	my ($detail) = @_;


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


Nigel Kukard's avatar
Nigel Kukard committed
584
585
586
587
	# Verify client invoice number
	if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
		setError("No client invoice number provided");
		return ERR_PARAM;
588
589
	}

Nigel Kukard's avatar
Nigel Kukard committed
590
591
	# Verify inventory code
	if (!defined($detail->{'InventoryCode'}) || $detail->{'InventoryCode'} eq "") {
592
		setError("No inventory code provided for client invoice '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
593
		return ERR_PARAM;
594
595
596
	}

	# Check if client invoice exists
597
	my $tmp;
Nigel Kukard's avatar
Nigel Kukard committed
598
	$tmp->{'Number'} = $detail->{'Number'};
599
600
	my $invoice = getInvoice($tmp);
	if (ref $invoice ne "HASH") {
601
		setError(Error());
602
		return $invoice;
603
604
	}

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

614
	# Grab inventory item, if exists
615
	$tmp = undef;
Nigel Kukard's avatar
Nigel Kukard committed
616
	$tmp->{'Code'} = $detail->{'InventoryCode'};
617
	$tmp->{'SerialNumber'} = $detail->{'SerialNumber'};
618
	$tmp->{'Quantity'} = $quantity->bstr();
619
	my $inventoryItem = wiaflos::server::Inventory::getInventoryStockItem($tmp);
620
	if (ref $inventoryItem ne "HASH") {
621
		setError(wiaflos::server::Inventory::Error());
622
623
624
		return $inventoryItem;
	}

625
	# Check discountability
626
	my $discount = Math::BigFloat->new();
627
628
629
	if (defined($detail->{'Discount'})) {
		$discount->badd($detail->{'Discount'});
		if ($inventoryItem->{'Discountable'} eq 'no' && !$discount->is_zero()) {
630
			setError("Discount given for non-discountable item '".$inventoryItem->{'Code'}."' on client invoice '".$invoice->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
631
			return ERR_DISCNDISC;
632
633
634
635
		}
	}


Nigel Kukard's avatar
Nigel Kukard committed
636
637
638
639
	# 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'}));
640
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
641
		# Tracked items must have a serial number
642
		if ($inventoryItem->{'Mode'} eq "track") {
643
			setError("Inventory item '".$inventoryItem->{'Code'}."' is tracked, a serial number MUST be provided on client invoice '".$invoice->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
644
			return ERR_USAGE;
645
646
647
648
649
650
651
652
653
654
655
		}
	}

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

656
	# Check if tax type exists
657
	$tmp = undef;
658
	$tmp->{'TaxTypeID'} = defined($detail->{'TaxTypeID'}) ? $detail->{'TaxTypeID'} : $inventoryItem->{'TaxTypeID'};
Nigel Kukard's avatar
Nigel Kukard committed
659
	my $taxType = wiaflos::server::Tax::getTaxType($tmp);
660
	if (ref $taxType ne "HASH") {
661
		setError(wiaflos::server::Tax::Error());
662
663
		return $taxType;
	}
664

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

	# Get price
669
	my $sellPrice = Math::BigFloat->new();
670
671
	if (defined($detail->{'UnitPrice'})) {
		$sellPrice->badd($detail->{'UnitPrice'});
672
	} else {
673
		$sellPrice->badd($inventoryItem->{'SellPrice'});
674
675
	}

Nigel Kukard's avatar
Nigel Kukard committed
676
	# If we have an item unit, add it aswell
677
678
679
680
681
	if (defined($inventoryItem->{'Unit'}) && $inventoryItem->{'Unit'} ne "") {
		push(@extraCols,'Unit');
		push(@extraData,DBQuote($inventoryItem->{'Unit'}));
	}

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

687
688
689
	# Calculate discount
	if (!$discount->is_zero()) {
		# Set totalprice
690
		my $discountAmount = Math::BigFloat->new();
691
692
693
694
695
696
697
698
699
700
		$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);
701

702
703
704
705
706
707
708
709
710
711
712
713
714
		# 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()));
	}

715
	# Get ready for calculating tax
716
	my ($taxAmount,$taxMode) = wiaflos::server::Tax::getTaxAmount($inventoryItem->{'TaxMode'},$taxRate,$totalPrice);
717
718
	if (!defined($taxAmount) || !defined($taxMode)) {
		setError(wiaflos::server::Tax::Error());
Nigel Kukard's avatar
Nigel Kukard committed
719
		return ERR_USAGE;
720
	}
721

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

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

728
729
730
731
732
733
734
735
	# Pull in extra data
	my $extraCols = "";
	my $extraData = "";
	if (@extraCols > 0 && @extraData > 0) {
		$extraCols .= ',' . join(',',@extraCols);
		$extraData .= ',' . join(',',@extraData);
	}

Nigel Kukard's avatar
Nigel Kukard committed
736
	# Link in invoice item
737
	my $sth = DBDo("
738
739
		INSERT INTO invoice_items
				(InvoiceID,InventoryID,Description,Quantity,UnitPrice,Price,TaxTypeID,TaxMode,TaxRate,TaxAmount$extraCols)
740
741
			VALUES
				(
742
					".DBQuote($invoice->{'ID'}).",
743
					".DBQuote($inventoryItem->{'ID'}).",
744
					".DBQuote($itemDesc).",
745
					".DBQuote($quantity->bstr()).",
746
					".DBQuote($sellPrice->bstr()).",
747
					".DBQuote($totalPrice->bstr()).",
748
					".DBQuote($taxType->{'ID'}).",
749
750
751
752
753
754
					".DBQuote($taxMode).",
					".DBQuote($taxRate->bstr()).",
					".DBQuote($taxAmount)."
					$extraData
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
755
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
756
757
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
758
759
760
761
762
763
764
765
766
767
768
	}

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

	return $ID;
}


# Post invoice
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
769
#		Number	- Client invoice number
770
771
772
sub postInvoice
{
	my ($detail) = @_;
773

774
775
776
777
778
	my $data;


	# Grab invoice
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
779
	$data->{'Number'} = $detail->{'Number'};
780
781
	my $invoice = getInvoice($data);
	if (ref $invoice ne "HASH") {
782
		setError(Error());
783
784
		return $invoice;
	}
785
786
	# Check if we already posted or not
	if ($invoice->{'GLTransactionID'}) {
787
		setError("Client invoice '".$invoice->{'Number'}."' already posted");
788
789
		return ERR_POSTED;
	}
790
791
792

	# Grab client
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
793
	$data->{'ID'} = $invoice->{'ClientID'};
Nigel Kukard's avatar
Nigel Kukard committed
794
	my $client = wiaflos::server::Clients::getClient($data);
795
	if (ref $client ne "HASH") {
Nigel Kukard's avatar
Nigel Kukard committed
796
		setError(wiaflos::server::Clients::Error());
797
798
799
800
801
		return $client;
	}

	# Return list of client invoice items
	my $sth = DBSelect("
802
803
		SELECT
			invoice_items.ID,
804
			invoice_items.InventoryID,
Nigel Kukard's avatar
Nigel Kukard committed
805
			invoice_items.SerialNumber,
806
			invoice_items.Quantity,
807
			invoice_items.DiscountAmount, invoice_items.UnitPrice, invoice_items.Price,
808
809
			invoice_items.TaxTypeID, invoice_items.TaxMode, invoice_items.TaxAmount,

Nigel Kukard's avatar
Nigel Kukard committed
810
			tax_types.GLAccountID
811
812
813
		FROM
			invoice_items, tax_types
		WHERE
814
			invoice_items.InvoiceID = ".DBQuote($invoice->{'ID'})."
815
816
			AND tax_types.ID = invoice_items.TaxTypeID
	");
Nigel Kukard's avatar
Nigel Kukard committed
817
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
818
819
		setError(wiaflos::server::dblayer::Error());
		return ERR_DB;
820
821
822
823
824
825
	}

	DBBegin();


	# Create transaction
Nigel Kukard's avatar
Nigel Kukard committed
826
	my $transactionRef = sprintf('Invoice: %s',$invoice->{'Number'});
827
828
	$data = undef;
	$data->{'Date'} = $invoice->{'IssueDate'};
Nigel Kukard's avatar
Nigel Kukard committed
829
830
	$data->{'Reference'} = $transactionRef;
	my $GLTransActID = wiaflos::server::GL::createGLTransaction($data);
831
	if ($GLTransActID < 1) {
Nigel Kukard's avatar
Nigel Kukard committed
832
		setError(wiaflos::server::GL::Error());
833
		DBRollback();
Nigel Kukard's avatar
Nigel Kukard committed
834
		return $GLTransActID;
835
836
837
838
839
840
	}

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

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

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

857
		my $itemExclPrice = Math::BigFloat->new($row->{'Price'});
Nigel Kukard's avatar
Nigel Kukard committed
858
		$itemExclPrice->bsub($row->{'TaxAmount'}) if ($row->{'TaxMode'} eq "1");
859
860

		# Pull in tax GL account and addup what we need to post
Nigel Kukard's avatar
Nigel Kukard committed
861
862
		if (defined($taxEntries{$row->{'GLAccountID'}})) {
			$taxEntries{$row->{'GLAccountID'}}->badd($row->{'TaxAmount'});
863
		} else {
Nigel Kukard's avatar
Nigel Kukard committed
864
			$taxEntries{$row->{'GLAccountID'}} = Math::BigFloat->new($row->{'TaxAmount'});
865
866
		}

867
868
		# Build query, and pull in item
		$data = undef;
869
		$data->{'ID'} = $row->{'InventoryID'};
870
		$data->{'SerialNumber'} = $row->{'SerialNumber'};  # Send serial number to ensure we get a cost price for tracked items
871
		$data->{'Quantity'} = $row->{'Quantity'};
872
		my $inventoryItem = wiaflos::server::Inventory::getInventoryStockItem($data);
873
		if (ref $inventoryItem ne 'HASH') {
Nigel Kukard's avatar
Nigel Kukard committed
874
			setError(wiaflos::server::Inventory::Error());
875
876
877
878
			DBRollback();
			return $inventoryItem;
		}

879
		# Work out cost price & stock list we require for adjustment
880
		my $costPrice = Math::BigFloat->new();
881
882
883
884
		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
Nigel Kukard's avatar
Nigel Kukard committed
885
			$costPrice->badd($stockItem->{'Cost'});
886
887
888
889
890

			# Create the stock list to adjust stock
			my $adjustItem;
			$adjustItem->{'InventoryTrackingID'} = $stockItem->{'InventoryTrackingID'};
			$adjustItem->{'Quantity'} = $stockItem->{'QuantityNeeded'};
Nigel Kukard's avatar
Nigel Kukard committed
891
			$adjustItem->{'Cost'} = $stockItem->{'Cost'};
892
893
			push(@stockAdjustmentList,$adjustItem);
		}
894

895
		# Pull in stock control account
Nigel Kukard's avatar
Nigel Kukard committed
896
897
898
		if (defined($inventoryItem->{'GLAssetAccountID'})) {
			if (defined($stockEntries{$inventoryItem->{'GLAssetAccountID'}})) {
				$stockEntries{$inventoryItem->{'GLAssetAccountID'}}->badd($costPrice);
899
			} else {
Nigel Kukard's avatar
Nigel Kukard committed
900
				$stockEntries{$inventoryItem->{'GLAssetAccountID'}} = $costPrice->copy();
901
902
			}
		}
903

904
		# Pull in expense account (cost of goods sold)
Nigel Kukard's avatar
Nigel Kukard committed
905
906
907
		if (defined($inventoryItem->{'GLExpenseAccountID'})) {
			if (defined($expEntries{$inventoryItem->{'GLExpenseAccountID'}})) {
				$expEntries{$inventoryItem->{'GLExpenseAccountID'}}->badd($costPrice);
908
			} else {
Nigel Kukard's avatar
Nigel Kukard committed
909
				$expEntries{$inventoryItem->{'GLExpenseAccountID'}} = $costPrice->copy();
910
			}
911
		}
912

913
		# Pull in income account, if amount is zero its probably discounted 100%
Nigel Kukard's avatar
Nigel Kukard committed
914
915
916
		if (defined($inventoryItem->{'GLIncomeAccountID'}) && !$itemExclPrice->is_zero()) {
			if (defined($incEntries{$inventoryItem->{'GLIncomeAccountID'}})) {
				$incEntries{$inventoryItem->{'GLIncomeAccountID'}}->badd($itemExclPrice);
917
			} else {
Nigel Kukard's avatar
Nigel Kukard committed
918
				$incEntries{$inventoryItem->{'GLIncomeAccountID'}} = $itemExclPrice->copy();
919
920
921
			}
		}

922
		# Adjust stock
923
		$quantity->bneg(); # Get negative
924
		$data = undef;
925
		$data->{'ID'} = $row->{'InventoryID'};
Nigel Kukard's avatar
Nigel Kukard committed
926
		$data->{'GLTransactionID'} = $GLTransActID;
927
		$data->{'QtyChange'} = $quantity->bstr();
928
		$data->{'SerialNumber'} = $row->{'SerialNumber'};
929
		$data->{'StockAdjustmentList'} = \@stockAdjustmentList;
930
		# Adjust stock and check for errors
931
		my $trackingInfo = wiaflos::server::Inventory::adjustInventoryStock($data);
932
		if (ref $trackingInfo ne "ARRAY") {
Nigel Kukard's avatar
Nigel Kukard committed
933
			setError(wiaflos::server::Inventory::Error());
934
			DBRollback();
935
			return $trackingInfo;
936
		}
937

938
939
940
941
942
943
944
		# 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
945
							(InvoiceItemID,InventoryTrackingID)
946
947
						VALUES
							(
948
								".DBQuote($row->{'ID'}).",
949
950
951
952
								".DBQuote($trackingInfoItem->{'InventoryTrackingID'})."
							)
				");
				if (!$sth2) {
Nigel Kukard's avatar
Nigel Kukard committed
953
					setError(wiaflos::server::dblayer::Error());
954
					DBRollback();
Nigel Kukard's avatar
Nigel Kukard committed
955
					return ERR_DB;
956
				}
957

958
				my $ID = DBLastInsertID("inventory_item_tracking","ID");
959
960
961
			}
		}

962
		# Addup inv total
963
		$discTotal->badd($row->{'DiscountAmount'}) if (defined($row->{'DiscountAmount'}));
964
		$subTotal->badd($itemExclPrice);
965
966
		$invTotal->badd($itemInclPrice);
	}
967

968
969
970
971
972
	DBFreeRes($sth);

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