Receipting.pm 35 KB
Newer Older
1
# Receipting 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.

Nigel Kukard's avatar
Nigel Kukard committed
20
21
22



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

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

Nigel Kukard's avatar
Nigel Kukard committed
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
use wiaflos::server::core::config;
use awitpt::db::dblayer;
use awitpt::cache;
use wiaflos::server::core::templating;
use wiaflos::server::core::Clients;
use wiaflos::server::core::GL;
Nigel Kukard's avatar
Nigel Kukard committed
37
38
39
40
41
42

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


Nigel Kukard's avatar
Nigel Kukard committed
43
44
45
46
47
48
49
50
51
52


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

# Set current error message
# Args: error_message
sub setError
{
	my $err = shift;
53
54
	my ($package,$filename,$line) = caller;
	my (undef,undef,undef,$subroutine) = caller(1);
Nigel Kukard's avatar
Nigel Kukard committed
55
56

	# Set error
57
	$error = "$subroutine($line): $err";
Nigel Kukard's avatar
Nigel Kukard committed
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
}

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

	# Reset error
	$error = "";

	# Return error
	return $err;
}


74

Nigel Kukard's avatar
Nigel Kukard committed
75
76
# Check if receipt exists
# Backend function, takes 1 parameter which is the receipt ID
77
sub receiptIDExists
Nigel Kukard's avatar
Nigel Kukard committed
78
79
80
81
{
	my $receiptID = shift;


82
83
84
	# Select receipt count
	my $rows = DBSelectNumResults("FROM receipts WHERE ID = ".DBQuote($receiptID));
	if (!defined($rows)) {
85
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
86
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
87
88
	}

89
	return $rows > 0 ? 1 : 0;
Nigel Kukard's avatar
Nigel Kukard committed
90
91
92
}


93

94
# Check if receipt number exists
95
sub receiptNumberExists
Nigel Kukard's avatar
Nigel Kukard committed
96
{
Nigel Kukard's avatar
Nigel Kukard committed
97
	my $number = shift;
Nigel Kukard's avatar
Nigel Kukard committed
98
99


100
101
102
103
	# Sanitize
	$number = uc($number);
	$number =~ s#^RCT/##;

104
	# Select receipt count
Nigel Kukard's avatar
Nigel Kukard committed
105
	my $rows = DBSelectNumResults("FROM receipts WHERE Number = ".DBQuote($number));
106
	if (!defined($rows)) {
107
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
108
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
109
110
111
112
113
114
	}

	return $rows > 0 ? 1 : 0;
}


115

Nigel Kukard's avatar
Nigel Kukard committed
116
117
# Return receipt ID from number
sub getReceiptIDFromNumber
Nigel Kukard's avatar
Nigel Kukard committed
118
{
Nigel Kukard's avatar
Nigel Kukard committed
119
	my $number = shift;
Nigel Kukard's avatar
Nigel Kukard committed
120
121


122
123
124
125
	# Sanitize
	$number = uc($number);
	$number =~ s#^RCT/##;

126
127
128
	# Check cache
	my ($cache_res,$cache) = cacheGetKeyPair('Receipt/Number-to-ID',$number);
	if ($cache_res != RES_OK) {
129
		setError(awitpt::cache::Error());
130
131
132
133
		return $cache_res;
	}
	return $cache if (defined($cache));

Nigel Kukard's avatar
Nigel Kukard committed
134
135
	# Select receipt
	my $sth = DBSelect("
136
		SELECT
Nigel Kukard's avatar
Nigel Kukard committed
137
138
139
140
			ID
		FROM
			receipts
		WHERE
Nigel Kukard's avatar
Nigel Kukard committed
141
			Number = ".DBQuote($number)."
Nigel Kukard's avatar
Nigel Kukard committed
142
	");
143
	if (!$sth) {
144
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
145
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
146
147
	}

148
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID ));
149
150
	DBFreeRes($sth);

Nigel Kukard's avatar
Nigel Kukard committed
151
	# Check we got a result
152
	if (!defined($row)) {
153
		setError("Error finding receipt '$number'");
Nigel Kukard's avatar
Nigel Kukard committed
154
		return ERR_NOTFOUND;
Nigel Kukard's avatar
Nigel Kukard committed
155
	}
156

157
158
159
	# Cache this
	$cache_res = cacheStoreKeyPair('Receipt/Number-to-ID',$number,$row->{'ID'});
	if ($cache_res != RES_OK) {
160
		setError(awitpt::cache::Error());
161
162
163
		return $cache_res;
	}

Nigel Kukard's avatar
Nigel Kukard committed
164
165
166
167
168
169
170
171
172
173
	return $row->{'ID'};
}




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


Nigel Kukard's avatar
Nigel Kukard committed
176
	my $item;
177

Nigel Kukard's avatar
Nigel Kukard committed
178
179
180
181
182
	$item->{'ID'} = $rawData->{'ID'};

	my $data;

	# Pull in client data
Nigel Kukard's avatar
Nigel Kukard committed
183
	$item->{'ClientID'} = $rawData->{'ClientID'};
Nigel Kukard's avatar
Nigel Kukard committed
184
185

	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
186
	$data->{'ID'} = $rawData->{'ClientID'};
187
	my $client = wiaflos::server::core::Clients::getClient($data);
Nigel Kukard's avatar
Nigel Kukard committed
188
	$item->{'ClientCode'} = $client->{'Code'};
189

Nigel Kukard's avatar
Nigel Kukard committed
190
191

	# Pull in GL account info
Nigel Kukard's avatar
Nigel Kukard committed
192
	$item->{'GLAccountID'} = $rawData->{'GLAccountID'};
193
	$item->{'GLAccountNumber'} = wiaflos::server::core::GL::getGLAccountNumberFromID($rawData->{'GLAccountID'});
Nigel Kukard's avatar
Nigel Kukard committed
194

Nigel Kukard's avatar
Nigel Kukard committed
195
	$item->{'Number'} = "RCT/".uc($rawData->{'Number'});
Nigel Kukard's avatar
Nigel Kukard committed
196

Nigel Kukard's avatar
Nigel Kukard committed
197
198
199
	$item->{'TransactionDate'} = $rawData->{'TransactionDate'};
	$item->{'Reference'} = $rawData->{'Reference'};
	$item->{'Amount'} = $rawData->{'Amount'};
200

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

204
	$item->{'Closed'} = $rawData->{'Closed'};
Nigel Kukard's avatar
Nigel Kukard committed
205
206
207
208
209

	return $item;
}


210

Nigel Kukard's avatar
Nigel Kukard committed
211
212
213
214
# Backend function to build item hash
sub sanitizeRawAllocationItem
{
	my $rawData = shift;
215
216


Nigel Kukard's avatar
Nigel Kukard committed
217
	my $item;
218

Nigel Kukard's avatar
Nigel Kukard committed
219
220
221
222
	$item->{'ID'} = $rawData->{'ID'};


   	# Pull in invoice data
223
224
	if (defined($rawData->{'InvoiceID'})) {
		$item->{'InvoiceID'} = $rawData->{'InvoiceID'};
225

226
227
		my $data;
		$data->{'ID'} = $rawData->{'InvoiceID'};
228
		my $invoice = wiaflos::server::core::Invoicing::getInvoice($data);
229
230
231
232
233
		$item->{'InvoiceNumber'} = $invoice->{'Number'};

	# Pull in transaction data
	} elsif (defined($rawData->{'AccountTransactionID'})) {
		$item->{'AccountTransactionID'} = $rawData->{'AccountTransactionID'};
234

235
236
		my $data;
		$data->{'ID'} = $rawData->{'AccountTransactionID'};
237
		my $transaction = wiaflos::server::core::Clients::getAccountTransaction($data);
238
239
		$item->{'AccountTransactionNumber'} = $transaction->{'Number'};
	}
240

241
242
	# And the receipt
	$item->{'ReceiptID'} = $rawData->{'ReceiptID'};
243

244
	my $data;
245
246
247
248
	$data->{'ID'} = $rawData->{'ReceiptID'};
	my $receipt = getReceipt($data);
	$item->{'ReceiptNumber'} = $receipt->{'Number'};

Nigel Kukard's avatar
Nigel Kukard committed
249

Nigel Kukard's avatar
Nigel Kukard committed
250
	$item->{'Amount'} = $rawData->{'Amount'};
251

252
	$item->{'Posted'} = (defined($rawData->{'InvoiceTransactionID'}) || defined($rawData->{'AccountTransactionAllocationID'})) ? 1 : 0;
Nigel Kukard's avatar
Nigel Kukard committed
253
254
255
256
257

	return $item;
}


258

Nigel Kukard's avatar
Nigel Kukard committed
259
260
# Create receipt
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
261
#		ClientCode	- Client code
Nigel Kukard's avatar
Nigel Kukard committed
262
263
#		GLAccountNumber	- GL account where money was paid from
#		Number		- Number for this receipt
Nigel Kukard's avatar
Nigel Kukard committed
264
#		Date		- Date of receipt
Nigel Kukard's avatar
Nigel Kukard committed
265
#		Reference		- GL account entry reference (bank statement reference for example)
Nigel Kukard's avatar
Nigel Kukard committed
266
267
268
269
270
271
#		Amount		- Amount
sub createReceipt
{
	my ($detail) = @_;


272
273
274
275
276
277
278
	# Verify receipt number
	if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
		setError("No (or invalid) receipt number provided");
		return ERR_PARAM;
	}
	(my $receiptNumber = uc($detail->{'Number'})) =~ s#^RCT/##;

279
	# Verify client code
Nigel Kukard's avatar
Nigel Kukard committed
280
	if (!defined($detail->{'ClientCode'}) || $detail->{'ClientCode'} eq "") {
281
		setError("No (or invalid) client code provided for receipt '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
282
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
283
284
285
	}

	# Verify GL account
Nigel Kukard's avatar
Nigel Kukard committed
286
	if (!defined($detail->{'GLAccountNumber'}) || $detail->{'GLAccountNumber'} eq "") {
287
		setError("No (or invalid) GL account provided for receipt '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
288
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
289
290
291
292
	}

	# Verify date
	if (!defined($detail->{'Date'}) || $detail->{'Date'} eq "") {
293
		setError("No (or invalid) date provided for receipt '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
294
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
295
296
	}

Nigel Kukard's avatar
Nigel Kukard committed
297
298
	# Verify reference
	if (!defined($detail->{'Reference'}) || $detail->{'Reference'} eq "") {
299
		setError("No (or invalid) reference provided for receipt '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
300
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
301
302
303
304
	}

	# Amount
	if (!defined($detail->{'Amount'}) || $detail->{'Amount'} eq "") {
305
		setError("No (or invalid) amount account provided for receipt '".$detail->{'Number'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
306
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
307
308
309
	}

	# Check if client exists
310
	my $clientID  = wiaflos::server::core::Clients::getClientIDFromCode($detail->{'ClientCode'});
Nigel Kukard's avatar
Nigel Kukard committed
311
	if ($clientID < 1) {
312
		setError(wiaflos::server::core::Clients::Error());
Nigel Kukard's avatar
Nigel Kukard committed
313
314
315
316
		return $clientID;
	}

	# Check GL account exists
317
	my $GLAccountID = wiaflos::server::core::GL::getGLAccountIDFromNumber($detail->{'GLAccountNumber'});
Nigel Kukard's avatar
Nigel Kukard committed
318
	if ($GLAccountID < 1) {
319
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
320
		return $GLAccountID;
Nigel Kukard's avatar
Nigel Kukard committed
321
322
323
	}

	# Check for conflicts
324
325
	if (receiptNumberExists($receiptNumber)) {
		setError("Receipt number '$receiptNumber' already exists");
Nigel Kukard's avatar
Nigel Kukard committed
326
		return ERR_CONFLICT;
Nigel Kukard's avatar
Nigel Kukard committed
327
328
329
330
	}

	# Create receipt
	my $sth = DBDo("
331
		INSERT INTO receipts
332
				(ClientID,GLAccountID,Number,TransactionDate,Reference,Amount,Closed)
Nigel Kukard's avatar
Nigel Kukard committed
333
334
335
			VALUES
				(
					".DBQuote($clientID).",
Nigel Kukard's avatar
Nigel Kukard committed
336
					".DBQuote($GLAccountID).",
337
					".DBQuote($receiptNumber).",
Nigel Kukard's avatar
Nigel Kukard committed
338
					".DBQuote($detail->{'Date'}).",
Nigel Kukard's avatar
Nigel Kukard committed
339
					".DBQuote($detail->{'Reference'}).",
340
341
					".DBQuote($detail->{'Amount'}).",
					0
Nigel Kukard's avatar
Nigel Kukard committed
342
343
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
344
	if (!$sth) {
345
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
346
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
347
348
349
350
351
352
353
354
355
	}

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

	return $ID;
}


356

Nigel Kukard's avatar
Nigel Kukard committed
357
358
359
# Return an array of receipts
sub getReceipts
{
360
361
	my ($detail) = @_;
	my $type = defined($detail->{'Type'}) ? $detail->{'Type'} : "open";
Nigel Kukard's avatar
Nigel Kukard committed
362
363
364
365
366
	my @receipts = ();


	# Return list of receipts
	my $sth = DBSelect("
367
		SELECT
368
369
			ID, ClientID, GLAccountID, Number, TransactionDate,
			Reference, Amount, Closed
Nigel Kukard's avatar
Nigel Kukard committed
370
371
372
		FROM
			receipts
	");
Nigel Kukard's avatar
Nigel Kukard committed
373
	if (!$sth) {
374
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
375
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
376
377
378
	}

	# Fetch rows
379
380
381
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ClientID GLAccountID Number TransactionDate Reference Amount Closed )
	)) {
382
		# Check what kind of receipts do we want
Nigel Kukard's avatar
Nigel Kukard committed
383
		if (($type eq "open") && $row->{'Closed'} eq "0") {
Nigel Kukard's avatar
Nigel Kukard committed
384
			push(@receipts,sanitizeRawItem($row));
385
		} elsif ($type eq "all") {
Nigel Kukard's avatar
Nigel Kukard committed
386
			push(@receipts,sanitizeRawItem($row));
387
		}
Nigel Kukard's avatar
Nigel Kukard committed
388
389
390
391
392
393
394
395
	}

	DBFreeRes($sth);

	return \@receipts;
}


396

Nigel Kukard's avatar
Nigel Kukard committed
397
398
# Return an receipt hash
# Optional:
Nigel Kukard's avatar
Nigel Kukard committed
399
400
#		Number	- Receipt number
#		ID		- Receipt ID
Nigel Kukard's avatar
Nigel Kukard committed
401
402
403
404
405
406
407
408
sub getReceipt
{
	my ($detail) = @_;


	my $receiptID;

	# Check which 'mode' we operating in
Nigel Kukard's avatar
Nigel Kukard committed
409
	if (!defined($detail->{'ID'}) || $detail->{'ID'} < 1) {
Nigel Kukard's avatar
Nigel Kukard committed
410
		# Verify receipt number
Nigel Kukard's avatar
Nigel Kukard committed
411
412
413
		if (!defined($detail->{'Number'}) || $detail->{'Number'} eq "") {
			setError("No (or invalid) receipt number provided");
			return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
414
415
416
		}

		# Check if receipt exists
Nigel Kukard's avatar
Nigel Kukard committed
417
		if (($receiptID = getReceiptIDFromNumber($detail->{'Number'})) < 1) {
418
			setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
419
420
421
			return $receiptID;
		}
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
422
		$receiptID = $detail->{'ID'};
Nigel Kukard's avatar
Nigel Kukard committed
423
	}
424

Nigel Kukard's avatar
Nigel Kukard committed
425
426
	# Verify receipt ID
	if (!$receiptID || $receiptID < 1) {
Nigel Kukard's avatar
Nigel Kukard committed
427
428
		setError("No (or invalid) receipt number/id provided");
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
429
430
431
432
	}

	# Return list of receipts
	my $sth = DBSelect("
433
		SELECT
434
			ID, ClientID, GLAccountID, Number, TransactionDate, Reference, Amount, GLTransactionID, Closed
Nigel Kukard's avatar
Nigel Kukard committed
435
436
437
		FROM
			receipts
		WHERE
438
			ID = ".DBQuote($receiptID)."
Nigel Kukard's avatar
Nigel Kukard committed
439
	");
Nigel Kukard's avatar
Nigel Kukard committed
440
	if (!$sth) {
441
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
442
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
443
444
445
	}

	# Fetch rows
446
447
448
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ClientID GLAccountID Number TransactionDate Reference Amount GLTransactionID Closed )
	);
Nigel Kukard's avatar
Nigel Kukard committed
449
450
451
452
453
454
	DBFreeRes($sth);

	return sanitizeRawItem($row);
}


455

Nigel Kukard's avatar
Nigel Kukard committed
456
# One can now post the receipt, which sets GLTransactionID
Nigel Kukard's avatar
Nigel Kukard committed
457
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
458
#		Number	- Receipt number
Nigel Kukard's avatar
Nigel Kukard committed
459
460
461
462
463
464
sub postReceipt
{
	my ($detail) = @_;


	my $data;
465

Nigel Kukard's avatar
Nigel Kukard committed
466
	# Grab receipt
Nigel Kukard's avatar
Nigel Kukard committed
467
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
468
	$data->{'Number'} = $detail->{'Number'};
Nigel Kukard's avatar
Nigel Kukard committed
469
470
	my $receipt = getReceipt($data);
	if (ref $receipt ne "HASH") {
471
		setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
472
473
474
475
476
		return $receipt;
	}

	# Make sure receipt is not posted
	if ($receipt->{'Posted'} eq "1") {
477
		setError("Receipt '".$receipt->{'Number'}."' already posted");
Nigel Kukard's avatar
Nigel Kukard committed
478
		return ERR_POSTED;
Nigel Kukard's avatar
Nigel Kukard committed
479
480
481
482
	}

	# Pull in client
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
483
	$data->{'ID'} = $receipt->{'ClientID'};
484
	my $client = wiaflos::server::core::Clients::getClient($data);
Nigel Kukard's avatar
Nigel Kukard committed
485
	if (ref $client ne "HASH") {
486
		setError(wiaflos::server::core::Clients::Error());
Nigel Kukard's avatar
Nigel Kukard committed
487
488
489
490
491
492
		return $client;
	}

	DBBegin();

	# Create transaction
493
	my $transactionRef = sprintf('Receipt: %s',$receipt->{'Number'});
Nigel Kukard's avatar
Nigel Kukard committed
494
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
495
496
	$data->{'Date'} = $receipt->{'TransactionDate'};
	$data->{'Reference'} = $transactionRef;
497
	my $GLTransactionID = wiaflos::server::core::GL::createGLTransaction($data);
Nigel Kukard's avatar
Nigel Kukard committed
498
	if ($GLTransactionID < 1) {
499
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
500
		DBRollback();
Nigel Kukard's avatar
Nigel Kukard committed
501
		return $GLTransactionID;
Nigel Kukard's avatar
Nigel Kukard committed
502
503
504
505
506
	}

	# Pull in amount
	my $transValue = Math::BigFloat->new($receipt->{'Amount'});

Nigel Kukard's avatar
Nigel Kukard committed
507
	# Link from GL
Nigel Kukard's avatar
Nigel Kukard committed
508
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
509
	$data->{'ID'} = $GLTransactionID;
510
	$data->{'Reference'} = $receipt->{'Reference'};
Nigel Kukard's avatar
Nigel Kukard committed
511
	$data->{'GLAccountID'} = $receipt->{'GLAccountID'};
Nigel Kukard's avatar
Nigel Kukard committed
512
	$data->{'Amount'} = $transValue->bstr();
513
514
	if ((my $res = wiaflos::server::core::GL::linkGLTransaction($data)) < 1) {
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
515
516
517
518
519
		DBRollback();
		return $res;
	}

	# Negate for other side
520
	$transValue->bneg();
Nigel Kukard's avatar
Nigel Kukard committed
521

Nigel Kukard's avatar
Nigel Kukard committed
522
	# Link to client GL account
Nigel Kukard's avatar
Nigel Kukard committed
523
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
524
525
	$data->{'ID'} = $GLTransactionID;
	$data->{'GLAccountID'} = $client->{'GLAccountID'};
Nigel Kukard's avatar
Nigel Kukard committed
526
	$data->{'Amount'} = $transValue->bstr();
527
528
	if ((my $res = wiaflos::server::core::GL::linkGLTransaction($data)) < 1) {
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
529
530
531
532
533
534
535
536
		DBRollback();
		return $res;
	}

	# Post receipt
	my $sth = DBDo("
		UPDATE receipts
		SET
Nigel Kukard's avatar
Nigel Kukard committed
537
			GLTransactionID = ".DBQuote($GLTransactionID)."
Nigel Kukard's avatar
Nigel Kukard committed
538
539
540
		WHERE
			ID = ".DBQuote($receipt->{'ID'})."
	");
Nigel Kukard's avatar
Nigel Kukard committed
541
	if (!$sth) {
542
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
543
		DBRollback();
Nigel Kukard's avatar
Nigel Kukard committed
544
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
545
546
547
548
	}

	# Post transaction
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
549
	$data->{'ID'} = $GLTransactionID;
550
551
	if ((my $res = wiaflos::server::core::GL::postGLTransaction($data)) != 0) {
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
552
553
554
555
556
557
		DBRollback();
		return $res;
	}

	DBCommit();

Nigel Kukard's avatar
Nigel Kukard committed
558
	return $GLTransactionID;
Nigel Kukard's avatar
Nigel Kukard committed
559
560
561
}


562
563
564
565
566
567
568
569
570
571
# @fn createReceiptAllocation($data)
# Create a receipt allocation
#
# @param data Hash ref with the below elements
# @li ReceiptNumber - Receipt number to create allocation for
# @li InvoiceNumber - Invoice number to allocate to
# @li TransactionNumber - Transaction number to allocate to
# @li Amount - Amount to allocate
#
# @return Receipt allocation ID on success < 1 on error
Nigel Kukard's avatar
Nigel Kukard committed
572
sub createReceiptAllocation
Nigel Kukard's avatar
Nigel Kukard committed
573
574
575
576
577
{
	my ($detail) = @_;


	# Verify receipt number
Nigel Kukard's avatar
Nigel Kukard committed
578
	if (!defined($detail->{'ReceiptNumber'}) || $detail->{'ReceiptNumber'} eq "") {
579
		setError("No (or invalid) receipt number provided for receipt allocation");
Nigel Kukard's avatar
Nigel Kukard committed
580
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
581
582
583
	}

	# Verify invoice number
584
585
586
587
588
589
590
591
592
593
594
595
596
	if (defined($detail->{'InvoiceNumber'})) {
		if ($detail->{'InvoiceNumber'} eq "") {
			setError("No (or invalid) invoice number provided for receipt allocation");
			return ERR_PARAM;
		}
	# or transaction number
	} elsif (defined($detail->{'TransactionNumber'})) {
		if ($detail->{'TransactionNumber'} eq "") {
			setError("No (or invalid) transaction number provided for receipt allocation");
			return ERR_PARAM;
		}
	} else {
		setError("No invoice or transaction number provided for receipt allocation");
Nigel Kukard's avatar
Nigel Kukard committed
597
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
598
599
600
601
	}

	# Amount
	if (!defined($detail->{'Amount'}) || $detail->{'Amount'} eq "") {
602
		setError("No (or invalid) amount account provided for receipt allocation");
Nigel Kukard's avatar
Nigel Kukard committed
603
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
604
605
606
	}
	my $allocAmount = Math::BigFloat->new($detail->{'Amount'});
	if ($allocAmount->is_zero()) {
607
		setError("Receipt allocation amount cannot be zero");
Nigel Kukard's avatar
Nigel Kukard committed
608
		return ERR_AMTZERO;
Nigel Kukard's avatar
Nigel Kukard committed
609
610
611
612
613
614
	}

	my $data;

	# Check if receipt exists & pull in
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
615
	$data->{'Number'} = $detail->{'ReceiptNumber'};
Nigel Kukard's avatar
Nigel Kukard committed
616
617
	my $receipt = getReceipt($data);
	if (ref $receipt ne "HASH" ) {
618
		setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
619
620
621
622
623
		return $receipt;
	}

	# Make sure receipt is posted
	if ($receipt->{'Posted'} ne "1") {
624
		setError("Receipt '".$receipt->{'Number'}."' must be posted before an allocation can be made");
625
		return ERR_POSTED;
Nigel Kukard's avatar
Nigel Kukard committed
626
627
628
	}

	$data = undef;
629
630
	my @extraCols;
	my @extraData;
Nigel Kukard's avatar
Nigel Kukard committed
631

632
633
634
	# Grab invoice
	if ($detail->{'InvoiceNumber'}) {
		$data->{'Number'} = $detail->{'InvoiceNumber'};
635
		my $invoice = wiaflos::server::core::Invoicing::getInvoice($data);
636
		if (ref($invoice) ne "HASH") {
637
			setError(wiaflos::server::core::Invoicing::Error());
638
639
640
641
642
643
644
			return $invoice;
		}

		# Check if invoice is posted or not
		if ($invoice->{'Posted'} ne "1") {
			setError("Cannot allocate an amount from receipt '".$receipt->{'Number'}."' to invoice '".$invoice->{'Number'}.
					"' as the invoice is not posted");
645
			return ERR_POSTED;
646
647
648
649
650
651
652
		}
		push(@extraCols,'InvoiceID');
		push(@extraData,DBQuote($invoice->{'ID'}));

	# Grab transaction
	} elsif ($detail->{'TransactionNumber'}) {
		$data->{'Number'} = $detail->{'TransactionNumber'};
653
		my $transaction = wiaflos::server::core::Clients::getAccountTransaction($data);
654
		if (ref($transaction) ne "HASH") {
655
			setError(wiaflos::server::core::Clients::Error());
656
657
658
659
660
661
662
			return $transaction;
		}

		# Check if transaction is posted or not
		if ($transaction->{'Posted'} ne "1") {
			setError("Cannot allocate an amount from receipt '".$receipt->{'Number'}."' to transaction '".$transaction->{'Number'}.
					"' as the transaction is not posted");
663
			return ERR_POSTED;
664
665
666
667
668
669
		}

		# Check if transaction is closed or not
		if ($transaction->{'Closed'} eq "1") {
			setError("Cannot allocate an amount from receipt '".$receipt->{'Number'}."' to transaction '".$transaction->{'Number'}.
					"' as the transaction is closed");
670
			return ERR_POSTED;
671
		}
672

673
674
		push(@extraCols,'AccountTransactionID');
		push(@extraData,DBQuote($transaction->{'ID'}));
675
676
677
	}


Nigel Kukard's avatar
Nigel Kukard committed
678
679
	# Grab allocations
	$data = undef;
680
	$data->{'ReceiptID'} = $receipt->{'ID'};
Nigel Kukard's avatar
Nigel Kukard committed
681
	my $allocs = getReceiptAllocations($data);
Nigel Kukard's avatar
Nigel Kukard committed
682
	if (ref $allocs ne "ARRAY") {
683
		setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
684
685
686
687
688
689
690
691
692
693
		return $allocs;
	}

	# Check if we either balance to 0 or have left over
	my $receiptBalance = Math::BigFloat->new($receipt->{'Amount'});
	foreach my $alloc (@{$allocs}) {
		$receiptBalance->bsub($alloc->{'Amount'});
	}
	$receiptBalance->bsub($detail->{'Amount'});
	if ($receiptBalance->is_neg()) {
694
		setError("Creating this allocation will exceed receipt '".$receipt->{'Number'}."' amount by ".$receiptBalance->bstr());
Nigel Kukard's avatar
Nigel Kukard committed
695
		return ERR_OVERALLOC;
Nigel Kukard's avatar
Nigel Kukard committed
696
697
	}

698
699
700
701
	# Pull in extra data
	my $extraCols = ',' . join(',',@extraCols);
	my $extraData = ',' . join(',',@extraData);

Nigel Kukard's avatar
Nigel Kukard committed
702
703
	# Create receipt allocation
	my $sth = DBDo("
704
		INSERT INTO receipt_allocations
705
				(ReceiptID,Amount$extraCols)
Nigel Kukard's avatar
Nigel Kukard committed
706
707
708
709
			VALUES
				(
					".DBQuote($receipt->{'ID'}).",
					".DBQuote($detail->{'Amount'})."
710
					$extraData
Nigel Kukard's avatar
Nigel Kukard committed
711
712
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
713
	if (!$sth) {
714
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
715
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
716
717
718
	}

	# Grab last ID
719
	my $ID = DBLastInsertID("receipt_allocations","ID");
Nigel Kukard's avatar
Nigel Kukard committed
720
721
722
723
724

	return $ID;
}


725

Nigel Kukard's avatar
Nigel Kukard committed
726
727
# Return an array of receipt allocations
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
728
#		ReceiptNumber	- Receipt number
Nigel Kukard's avatar
Nigel Kukard committed
729
#		ReceiptID	- Receipt ID
730
#
Nigel Kukard's avatar
Nigel Kukard committed
731
#		InvoiceID	-  invoice ID
732
733
734
#
#		AccountTransactionNumber - Transaction number
#		AccountTransactionID - Transaction ID
Nigel Kukard's avatar
Nigel Kukard committed
735
sub getReceiptAllocations
Nigel Kukard's avatar
Nigel Kukard committed
736
737
{
	my ($detail) = @_;
738

Nigel Kukard's avatar
Nigel Kukard committed
739
740
741
742
743
744
745
746
747
748
	my @allocations = ();


	# SQL query string to use
	my $query = "";

	# ID mode
	if (defined($detail->{'ReceiptID'}) && $detail->{'ReceiptID'} > 0) {
		$query .= "ReceiptID = ".$detail->{'ReceiptID'};

Nigel Kukard's avatar
Nigel Kukard committed
749
750
	# Receipt number mode
	} elsif (defined($detail->{'ReceiptNumber'}) && $detail->{'ReceiptNumber'} ne "") {
Nigel Kukard's avatar
Nigel Kukard committed
751
752
753
		my $receiptID;

		# Check if receipt exists
Nigel Kukard's avatar
Nigel Kukard committed
754
		if (($receiptID = getReceiptIDFromNumber($detail->{'ReceiptNumber'})) < 1) {
755
			setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
756
757
			return $receiptID;
		}
758

Nigel Kukard's avatar
Nigel Kukard committed
759
760
761
762
763
764
		$query .= "ReceiptID = ".DBQuote($receiptID);

	# Invoice ID mode
	} elsif (defined($detail->{'InvoiceID'}) && $detail->{'InvoiceID'} ne "") {
		$query .= "InvoiceID = ".DBQuote($detail->{'InvoiceID'});

765
766
767
768
769
770
771
772
773
	# Transaction ID mode
	} elsif (defined($detail->{'AccountTransactionID'}) && $detail->{'AccountTransactionID'} ne "") {
		$query .= "AccountTransactionID = ".DBQuote($detail->{'AccountTransactionID'});

	# Transaction number mode
	} elsif (defined($detail->{'AccountTransactionNumber'}) && $detail->{'AccountTransactionNumber'} ne "") {
		my $transactionID;

		# Check if transaction exists
774
		if (($transactionID = wiaflos::server::core::Clients::getTransactionIDFromNumber($detail->{'TransactionNumber'})) < 1) {
775
776
777
			setError(Error());
			return $transactionID;
		}
778

779
780
		$query .= "AccountTransactionID = ".DBQuote($transactionID);

Nigel Kukard's avatar
Nigel Kukard committed
781
	} else {
782
		setError("No acceptable parameters provided to return receipt allocations");
Nigel Kukard's avatar
Nigel Kukard committed
783
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
784
	}
785

Nigel Kukard's avatar
Nigel Kukard committed
786
787
	# Return list of receipt allocations
	my $sth = DBSelect("
788
		SELECT
789
			ID, ReceiptID, Amount, InvoiceID, InvoiceTransactionID, AccountTransactionID, AccountTransactionAllocationID
Nigel Kukard's avatar
Nigel Kukard committed
790
		FROM
791
			receipt_allocations
Nigel Kukard's avatar
Nigel Kukard committed
792
793
794
		WHERE
			$query
	");
Nigel Kukard's avatar
Nigel Kukard committed
795
	if (!$sth) {
796
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
797
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
798
799
800
	}

	# Fetch rows
801
802
803
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ReceiptID Amount InvoiceID InvoiceTransactionID AccountTransactionID AccountTransactionAllocationID )
	)) {
Nigel Kukard's avatar
Nigel Kukard committed
804
		push(@allocations,sanitizeRawAllocationItem($row));
Nigel Kukard's avatar
Nigel Kukard committed
805
806
807
808
809
810
811
812
	}

	DBFreeRes($sth);

	return \@allocations;
}


813

Nigel Kukard's avatar
Nigel Kukard committed
814
815
# Return an hash containing a allocation
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
816
817
#		ID	- Allocation ID
sub getReceiptAllocation
Nigel Kukard's avatar
Nigel Kukard committed
818
819
{
	my ($detail) = @_;
820

Nigel Kukard's avatar
Nigel Kukard committed
821
822

	# Verify allocation id
Nigel Kukard's avatar
Nigel Kukard committed
823
	if (!defined($detail->{'ID'}) || $detail->{'ID'} eq "") {
Nigel Kukard's avatar
Nigel Kukard committed
824
		setError("No (or invalid) allocation ID provided");
Nigel Kukard's avatar
Nigel Kukard committed
825
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
826
827
828
829
	}

	# Return allocation
	my $sth = DBSelect("
830
		SELECT
831
			ID, ReceiptID, Amount, InvoiceID, InvoiceTransactionID, AccountTransactionID, AccountTransactionAllocationID
Nigel Kukard's avatar
Nigel Kukard committed
832
		FROM
833
			receipt_allocations
Nigel Kukard's avatar
Nigel Kukard committed
834
		WHERE
Nigel Kukard's avatar
Nigel Kukard committed
835
			ID = ".DBQuote($detail->{'ID'})."
Nigel Kukard's avatar
Nigel Kukard committed
836
837
	");
	if (!$sth) {
838
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
839
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
840
841
842
	}

	# Fetch rows
843
844
845
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ReceiptID Amount InvoiceID invoiceTransactionID AccountTransactionID AccountTransactionAllocationID )
	);
Nigel Kukard's avatar
Nigel Kukard committed
846
847
848
849
850
851
	DBFreeRes($sth);

	return sanitizeRawAllocationItem($row);
}


852

Nigel Kukard's avatar
Nigel Kukard committed
853
854
# One can now post the allocation, which checks the invoice if its totally paid
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
855
856
#		ID	- Receipt allocation ID
sub postReceiptAllocation
Nigel Kukard's avatar
Nigel Kukard committed
857
858
859
860
861
{
	my ($detail) = @_;


	# Verify allocation ID
Nigel Kukard's avatar
Nigel Kukard committed
862
	if (!defined($detail->{'ID'}) || $detail->{'ID'} eq "") {
Nigel Kukard's avatar
Nigel Kukard committed
863
		setError("No (or invalid) allocation ID provided");
Nigel Kukard's avatar
Nigel Kukard committed
864
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
865
866
867
	}

	my $data;
868

Nigel Kukard's avatar
Nigel Kukard committed
869
870
	# Check if allocation exists & pull in
	$data = undef;
Nigel Kukard's avatar
Nigel Kukard committed
871
872
	$data->{'ID'} = $detail->{'ID'};
	my $allocation = getReceiptAllocation($data);
Nigel Kukard's avatar
Nigel Kukard committed
873
	if (ref $allocation ne "HASH") {
874
		setError(Error());
Nigel Kukard's avatar
Nigel Kukard committed
875
876
877
878
879
		return $allocation;
	}

	# Make sure allocation is not posted
	if ($allocation->{'Posted'} ne "0") {
880
		setError("Receipt allocation '".$allocation->{'ID'}."' already posted");
Nigel Kukard's avatar
Nigel Kukard committed
881
		return ERR_POSTED;
Nigel Kukard's avatar
Nigel Kukard committed
882
883
	}

884
885
886
887
888
889
890
891
892
	# Get corrosponding receipt
	$data = undef;
	$data->{'ID'} = $allocation->{'ReceiptID'};
	my $receipt = getReceipt($data);
	if (ref $receipt ne "HASH") {
		setError(Error());
		return $receipt;
	}

893
894
895
896
897
898
899
900
	# We use this at the end of the function to contain the ID of the thing we're posting the allocation against
	my $allocationAgainstID;

	# Check if its an invoice
	if (defined($allocation->{'InvoiceID'})) {
		# Grab invoice
		$data = undef;
		$data->{'ID'} = $allocation->{'InvoiceID'};
901
		my $invoice = wiaflos::server::core::Invoicing::getInvoice($data);
902
		if (ref $invoice ne "HASH") {
903
			setError(wiaflos::server::core::Invoicing::Error());
904
905
			return $invoice;
		}
Nigel Kukard's avatar
Nigel Kukard committed
906

907
908
909
910
911
		# Make sure invoice is not paid
		if ($invoice->{'Paid'} ne "0") {
			setError("Invoice '".$invoice->{'Number'}."' already paid");
			return ERR_PAID;
		}
Nigel Kukard's avatar
Nigel Kukard committed
912

913
914
915
916
917
		# Make sure invoice is posted
		if ($invoice->{'Posted'} ne "1") {
			setError("Invoice '".$invoice->{'Number'}."' not posted");
			return ERR_POSTED;
		}
918

919
920
921
922
923
924
925
		# Grab invoice allocations
		$data = undef;
		$data->{'InvoiceID'} = $allocation->{'InvoiceID'};
		my $invoiceAllocations = getReceiptAllocations($data);
		if (ref $invoiceAllocations ne "ARRAY") {
			setError(Error());
			return $invoiceAllocations;
926
		}
927
928
929
930
931
932
933
934
935
936
937
938
939
		# Add up invoice balance
		my $invoiceBalance = Math::BigFloat->new($invoice->{'Total'});
		foreach my $alloc (@{$invoiceAllocations}) {
			if ($alloc->{'Posted'} eq "1") {
				$invoiceBalance->bsub($alloc->{'Amount'});
			}
		}
		# Check invoice balance if its negative, this is if we've overallocated
		$invoiceBalance->bsub($allocation->{'Amount'});
		if ($invoiceBalance->is_neg()) {
			setError("Posting the allocation will end up in a balance of '".$invoiceBalance->bstr()."' on invoice '".$invoice->{'Number'}."'");
			return ERR_OVERALLOC;
		}
940

941
942
943
944
945
946
947
		$allocationAgainstID = $invoice->{'ID'};

	# Or if its a account transaction
	} elsif (defined($allocation->{'AccountTransactionID'})) {
		# Grab account transaction
		$data = undef;
		$data->{'ID'} = $allocation->{'AccountTransactionID'};
948
		my $transaction = wiaflos::server::core::Clients::getAccountTransaction($data);
949
		if (ref($transaction) ne "HASH") {
950
			setError(wiaflos::server::core::Invoicing::Error());
951
952
953
954
955
956
957
958
			return $transaction;
		}

		# Make sure transaction is posted
		if ($transaction->{'Posted'} ne "1") {
			setError("Account transaction '".$transaction->{'Number'}."' not posted");
			return ERR_POSTED;
		}
959

960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
		# Make sure transaction is not closed
		if ($transaction->{'Closed'} eq "1") {
			setError("Account transaction '".$transaction->{'Number'}."' already closed");
			return ERR_PAID;
		}

		# Grab transaction allocations
		$data = undef;
		$data