Invoicing.pm 41.1 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



23
package wiaflos::server::core::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;
31
32
33
34
35
36
37
38
use wiaflos::server::core::config;
use awitpt::db::dblayer;
use wiaflos::server::core::templating;
use wiaflos::server::core::GL;
use wiaflos::server::core::Inventory;
use wiaflos::server::core::Clients;
use wiaflos::server::core::Tax;
use wiaflos::server::core::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)) {
126
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
127
		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)) {
146
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
147
		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) {
174
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
175
		return ERR_DB;
176
177
	}

178
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID ));
179
180
	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
		SELECT
240
241
			ID, ClientID, Number, IssueDate, DueDate, OrderNumber, DiscountTotal, SubTotal, TaxTotal, Total,
			GLTransactionID, Paid
242
243
244
		FROM
			invoices
	");
Nigel Kukard's avatar
Nigel Kukard committed
245
	if (!$sth) {
246
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
247
		return ERR_DB;
248
249
250
	}

	# Fetch rows
251
252
253
254
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ClientID Number IssueDate DueDate OrderNumber DiscountTotal SubTotal TaxTotal Total
				GLTransactionID Paid )
	)) {
255
		# Check what kind of invoices we want
Nigel Kukard's avatar
Nigel Kukard committed
256
		if (($type eq "unpaid") && $row->{'Paid'} eq "0") {
Nigel Kukard's avatar
Nigel Kukard committed
257
			push(@invoices,sanitizeRawItem($row));
258
		} elsif ($type eq "all") {
Nigel Kukard's avatar
Nigel Kukard committed
259
			push(@invoices,sanitizeRawItem($row));
260
		}
261
262
263
264
265
266
267
268
269
	}

	DBFreeRes($sth);

	return \@invoices;
}


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


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

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

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


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

	# Fetch row
319
320
321
322
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ClientID Number ShippingAddress TaxReference IssueDate DueDate OrderNumber DiscountTotal
				SubTotal TaxTotal Total Note GLTransactionID Paid )
	);
323
324
325
326
327
328
329
	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
		SELECT
353
354
355
356
357
358
			invoice_items.ID, invoice_items.SerialNumber, invoice_items.InventoryID, 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

			inventory.Code AS InventoryCode,
359
		FROM
360
			invoice_items, inventory
361
		WHERE
362
			invoice_items.InvoiceID = ".DBQuote($invoice->{'ID'})."
363
			AND inventory.ID = invoice_items.InventoryID
364
	");
Nigel Kukard's avatar
Nigel Kukard committed
365
	if (!$sth) {
366
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
367
		return ERR_DB;
368
369
370
	}

	# Fetch rows
371
372
373
374
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID SerialNumber InventoryID Description Quantity Unit UnitPrice Price Discount DiscountAmount TaxMode
				TaxRate TaxAmount InventoryCode )
	)) {
Nigel Kukard's avatar
Nigel Kukard committed
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
		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'};
391
392
393
394

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


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

	DBFreeRes($sth);

	return \@items;
}


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


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


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

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

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

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

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

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

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

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

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


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

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

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

	return $ID;
}


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


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


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

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

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

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

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

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


Nigel Kukard's avatar
Nigel Kukard committed
638
639
640
641
	# 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'}));
642
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
643
		# Tracked items must have a serial number
644
		if ($inventoryItem->{'Mode'} eq "track") {
645
			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
646
			return ERR_USAGE;
647
648
649
650
651
652
653
654
655
656
657
		}
	}

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

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

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

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

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

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

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

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

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

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

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

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

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

	return $ID;
}


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

776
777
778
779
780
	my $data;


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

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

	# Return list of client invoice items
	my $sth = DBSelect("
804
		SELECT
805
806
807
808
			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,

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

	DBBegin();


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

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

	# Fetch rows
846
847
848
849
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID InventoryID SerialNumber Quantity DiscountAmount UnitPrice Price TaxTypeID TaxMode TaxAmount
				TaxGLAccountID )
	)) {
850
		# Pull quantity as we do math below, else we wouldn't need to
851
		my $quantity = Math::BigFloat->new();
852
853
		$quantity->precision(-4);
		$quantity->badd($row->{'Quantity'});
854
855
856

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

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

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

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

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

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

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

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

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

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

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

960
				my $ID = DBLastInsertID("inventory_item_tracking","ID");
961
962
963
			}
		}