GL.pm 37 KB
Newer Older
1
# General ledger functions
Nigel Kukard's avatar
Nigel Kukard committed
2
# Copyright (C) 2009-2020, AllWorldIT
Nigel Kukard's avatar
Nigel Kukard committed
3
# Copyright (C) 2008, LinuxRulz
Nigel Kukard's avatar
Nigel Kukard committed
4
# Copyright (C) 2006-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::GL;
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::constants;
30
31
use awitpt::db::dblayer;
use awitpt::cache;
Nigel Kukard's avatar
Nigel Kukard committed
32

33
34
use Math::BigFloat;

35
36
37
38
39
40
41
42
43
44
45
46
# Exporter stuff
require Exporter;
our (@ISA,@EXPORT,@EXPORT_OK);
@ISA = qw(Exporter);
@EXPORT = qw(
	GL_TRANSTYPE_NORMAL
	GL_TRANSTYPE_YEAREND
	GL_TRANSTYPE_AUDIT
	GL_TRANSTYPE_AUDIT_YEAREND
);
@EXPORT_OK = ();

47

48
49
use constant {
	GL_TRANSTYPE_NORMAL	=>	1,
50
51
52
	GL_TRANSTYPE_YEAREND	=>	2,
	GL_TRANSTYPE_AUDIT	=>	4,
	GL_TRANSTYPE_AUDIT_YEAREND	=>	8
53
54
};

Nigel Kukard's avatar
Nigel Kukard committed
55
56
57
58

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

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

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

Nigel Kukard's avatar
Nigel Kukard committed
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# Return current error message
# Args: none
sub Error
{
	my $err = $error;

	# Reset error
	$error = "";

	# Return error
	return $err;
}


85
86
87
88
# Backend function to build account item hash
sub sanitizeRawGLAccountItem
{
	my $rawData = shift;
89
90


91
	my $item;
Nigel Kukard's avatar
Nigel Kukard committed
92
	$item->{'ID'} = $rawData->{'ID'};
93

Nigel Kukard's avatar
Nigel Kukard committed
94
	$item->{'Number'} = getGLAccountNumberFromID($rawData->{'ID'});
95

Nigel Kukard's avatar
Nigel Kukard committed
96
	$item->{'ParentGLAccountID'} = $rawData->{'ParentGLAccountID'};
Nigel Kukard's avatar
Nigel Kukard committed
97
98
99
100
101
	$item->{'Name'} = $rawData->{'Name'};
	$item->{'FinCatCode'} = $rawData->{'FinCatCode'};
	$item->{'FinCatDescription'} = $rawData->{'FinCatDescription'};
	$item->{'RwCatCode'} = $rawData->{'RwCatCode'};
	$item->{'RwCatDescription'} = $rawData->{'RwCatDescription'};
102
103
104
105
106
107
108
109
110
111

	return $item;
}



# Backend function to build GL transaction item hash
sub sanitizeRawGLTransactionItem
{
	my $rawData = shift;
112
113


114
115
	my $item;

Nigel Kukard's avatar
Nigel Kukard committed
116
117
118
	$item->{'ID'} = $rawData->{'ID'};
	$item->{'TransactionDate'} = $rawData->{'TransactionDate'};
	$item->{'Reference'} = $rawData->{'Reference'};
119
	$item->{'Type'} = $rawData->{'Type'};
Nigel Kukard's avatar
Nigel Kukard committed
120
	$item->{'Posted'} = $rawData->{'Posted'};
121
122
123
124
125
126

	return $item;
}



Nigel Kukard's avatar
Nigel Kukard committed
127
# Check if GL account ID exists
128
# Backend function, takes 1 parameter which is the GL account ID
Nigel Kukard's avatar
Nigel Kukard committed
129
sub GLAccountIDExists
130
131
132
133
{
	my $GLAccID = shift;


134
135
136
	# Select account count
	my $rows = DBSelectNumResults("FROM gl_accounts WHERE ID = ".DBQuote($GLAccID));
	if (!defined($rows)) {
137
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
138
		return ERR_DB;
139
140
141
	}

	# Check we got a result
142
	if ($rows < 1) {
143
		setError("Error finding GL account '$GLAccID'");
Nigel Kukard's avatar
Nigel Kukard committed
144
		return ERR_NOTFOUND;
145
146
147
148
149
150
	}

	return 1;
}


Nigel Kukard's avatar
Nigel Kukard committed
151
152
# Check if a GL account code exists
sub GLAccountCodeExists
153
{
Nigel Kukard's avatar
Nigel Kukard committed
154
	my ($parentID,$code) = @_;
155
156
	my $extra_sql = "";

157

158
159
	# If we have a parent use it
	if (defined($parentID) && $parentID ne "") {
Nigel Kukard's avatar
Nigel Kukard committed
160
		$extra_sql .= " AND ParentGLAccountID = ".DBQuote($parentID);
161
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
162
		$extra_sql .= " AND ParentGLAccountID IS NULL";
163
	}
164

165
166
167
	# Select account count
	my $rows = DBSelectNumResults("FROM gl_accounts WHERE Code = ".DBQuote($code)." $extra_sql");
	if (!defined($rows)) {
168
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
169
		return ERR_DB;
170
171
172
173
174
175
	}

	return $rows > 0 ? 1 : 0;
}


176
177
# Check if transaction exists
# Backend function, takes 1 parameter which is the transaction ID
Nigel Kukard's avatar
Nigel Kukard committed
178
sub GLTransactionIDExists
179
180
181
182
{
	my $transActID = shift;


183
184
185
	# Select transaction count
	my $rows = DBSelectNumResults("FROM gl_transactions WHERE ID = ".DBQuote($transActID));
	if (!defined($rows)) {
186
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
187
		return ERR_DB;
188
189
	}

190
	return $rows > 0 ? 1 : 0;
191
192
193
}


Nigel Kukard's avatar
Nigel Kukard committed
194
195
196
# Get GL account ID from GL account number
# Backend function, takes a GL account number and returns the GL account ID
sub getGLAccountIDFromNumber
197
{
Nigel Kukard's avatar
Nigel Kukard committed
198
	my $GLAccNumber = shift;
199
200


201
202
203
	# Check cache
	my ($cache_res,$cache) = cacheGetKeyPair('GL/Number-to-AccountID',$GLAccNumber);
	if ($cache_res != RES_OK) {
204
		setError(awitpt::cache::Error());
205
206
207
208
209
		return $cache_res;
	}
	return $cache if (defined($cache));


210
211
	# Pull list of GL accounts
	my $sth = DBSelect("
212
		SELECT
Nigel Kukard's avatar
Nigel Kukard committed
213
			ID, Code
214
215
216
		FROM
			gl_accounts
	");
Nigel Kukard's avatar
Nigel Kukard committed
217
	if (!$sth) {
218
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
219
		return ERR_DB;
220
221
222
	}

	# Fetch rows, while we not found anything
223
	my $GLAccID;
224
	while ((my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID Code ))) && !defined($GLAccID)) {
Nigel Kukard's avatar
Nigel Kukard committed
225
226
		my $tmpGLAccNumber = getGLAccountNumberFromID($row->{'ID'});
		# If not defined b0rk out
Nigel Kukard's avatar
Nigel Kukard committed
227
		if (!defined($tmpGLAccNumber)) {
228
			DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
229
			return ERR_UNKNOWN;
230
231
232
		}

		# Check if we found it
Nigel Kukard's avatar
Nigel Kukard committed
233
		if ($GLAccNumber eq $tmpGLAccNumber) {
234
235
236
237
238
239
			$GLAccID = $row->{'ID'};
		}
	}

	DBFreeRes($sth);

240
	if (!defined($GLAccID)) {
241
		setError("Error finding GL account '$GLAccNumber'");
242
243
		return ERR_NOTFOUND;
	}
244

245
246
247
	# Cache this
	$cache_res = cacheStoreKeyPair('GL/Number-to-AccountID',$GLAccNumber,$GLAccID);
	if ($cache_res != RES_OK) {
248
		setError(awitpt::cache::Error());
249
250
251
		return $cache_res;
	}

252
253
254
255
	return $GLAccID;
}


Nigel Kukard's avatar
Nigel Kukard committed
256
257
# Return financial category ID from code
sub getGLFinCatIDFromCode
258
{
Nigel Kukard's avatar
Nigel Kukard committed
259
	my $finCatCode = shift;
260
261
262
263


	# Select financial category
	my $sth = DBSelect("
264
		SELECT
265
266
			ID
		FROM
267
			gl_financial_categories
268
		WHERE
Nigel Kukard's avatar
Nigel Kukard committed
269
			Code = ".DBQuote($finCatCode)."
270
	");
Nigel Kukard's avatar
Nigel Kukard committed
271
	if (!$sth) {
272
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
273
		return ERR_DB;
274
275
	}

276
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID ));
277
278
	DBFreeRes($sth);

279
	# Check we got a result
280
	if (!defined($row)) {
281
		setError("Error finding financial category '$finCatCode'");
Nigel Kukard's avatar
Nigel Kukard committed
282
		return ERR_NOTFOUND;
283
284
285
286
287
288
	}

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


Nigel Kukard's avatar
Nigel Kukard committed
289
290
# Return reporting category ID from code
sub getGLRwCatIDFromCode
291
{
Nigel Kukard's avatar
Nigel Kukard committed
292
	my $rwCatCode = shift;
293
294
295
296


	# Select reporting category
	my $sth = DBSelect("
297
		SELECT
298
299
			ID
		FROM
300
			gl_reportwriter_categories
301
		WHERE
Nigel Kukard's avatar
Nigel Kukard committed
302
			Code = ".DBQuote($rwCatCode)."
303
	");
Nigel Kukard's avatar
Nigel Kukard committed
304
	if (!$sth) {
305
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
306
		return ERR_DB;
307
308
	}

309
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID ));
310
311
	DBFreeRes($sth);

312
	# Check we got a result
313
	if (!defined($row)) {
314
		setError("Error finding reporting category '$rwCatCode'");
Nigel Kukard's avatar
Nigel Kukard committed
315
		return ERR_NOTFOUND;
316
317
318
319
320
321
	}

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


322
323
324
325
326
# Return report writer categories
sub getGLRwCats
{
	# Select reporting category
	my $sth = DBSelect("
327
		SELECT
328
329
330
331
332
			ID, Code, Description
		FROM
			gl_reportwriter_categories
	");
	if (!$sth) {
333
		setError(awitpt::db::dblayer::Error());
334
335
336
337
338
		return ERR_DB;
	}

	# Fetch rows
	my @entries;
339
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID Code Description ))) {
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
		my $entry;

		$entry->{'ID'} = $row->{'ID'};
		$entry->{'Code'} = $row->{'Code'};
		$entry->{'Description'} = $row->{'Description'};

		push(@entries,$entry);
	}

	DBFreeRes($sth);

	return \@entries;
}


355
# Resolve the full GL account reference
Nigel Kukard's avatar
Nigel Kukard committed
356
sub getGLAccountNumberFromID
Nigel Kukard's avatar
Nigel Kukard committed
357
358
359
{
	my $accID = shift;

360

361
362
363
	# Check cache
	my ($cache_res,$cache) = cacheGetKeyPair('GL/AccountID-to-Number',$accID);
	if ($cache_res != RES_OK) {
364
		setError(awitpt::cache::Error());
365
366
367
368
369
		return $cache_res;
	}
	return $cache if (defined($cache));


Nigel Kukard's avatar
Nigel Kukard committed
370
371
	# Return account ref & parent
	my $sth = DBSelect("
372
		SELECT
373
			Code, ParentGLAccountID
Nigel Kukard's avatar
Nigel Kukard committed
374
375
376
		FROM
			gl_accounts
		WHERE
377
			ID = ".DBQuote($accID)."
Nigel Kukard's avatar
Nigel Kukard committed
378
	");
Nigel Kukard's avatar
Nigel Kukard committed
379
	if (!$sth) {
380
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
381
		return undef;
Nigel Kukard's avatar
Nigel Kukard committed
382
383
	}

384
	# Grab row & free
385
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( Code ParentGLAccountID ));
386
	DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
387

Nigel Kukard's avatar
Nigel Kukard committed
388
389
	# Get parent acc number ... or if we the parent, return our acc number
	my $accnum = "";
Nigel Kukard's avatar
Nigel Kukard committed
390
	# If we have parent, get its ref
391
	if (defined($row->{'ParentGLAccountID'})) {
Nigel Kukard's avatar
Nigel Kukard committed
392
		# Check return value
393
		if (my $ret = getGLAccountNumberFromID($row->{'ParentGLAccountID'})) {
Nigel Kukard's avatar
Nigel Kukard committed
394
			# And add ref to our ref
Nigel Kukard's avatar
Nigel Kukard committed
395
			$accnum = "$ret:".$row->{'Code'};
Nigel Kukard's avatar
Nigel Kukard committed
396
397
398
		} else {
			return undef;
		}
Nigel Kukard's avatar
Nigel Kukard committed
399
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
400
		$accnum = $row->{'Code'};
Nigel Kukard's avatar
Nigel Kukard committed
401
402
	}

403
404
405
	# Cache this
	$cache_res = cacheStoreKeyPair('GL/AccountID-to-Number',$accID,$accnum);
	if ($cache_res != RES_OK) {
406
		setError(awitpt::cache::Error());
407
408
409
410
		return $cache_res;
	}


Nigel Kukard's avatar
Nigel Kukard committed
411
	return $accnum;
Nigel Kukard's avatar
Nigel Kukard committed
412
413
414
}


415
416
417
418
# Check GL account financial category
# Returns:
#	1	- match
#	0	- no match
Nigel Kukard's avatar
Nigel Kukard committed
419
sub checkGLAccountFinCat
420
{
Nigel Kukard's avatar
Nigel Kukard committed
421
	my ($GLAccID,$finCatCode) = @_;
422
423
424
425


	# Check and return GL account financial category
	my $sth = DBSelect("
426
		SELECT
427
			gl_financial_categories.Code
428
		FROM
429
			gl_financial_categories, gl_accounts
430
431
		WHERE
			gl_accounts.ID = ".DBQuote($GLAccID)."
432
			AND gl_financial_categories.ID = gl_accounts.FinCatID
433
	");
Nigel Kukard's avatar
Nigel Kukard committed
434
	if (!$sth) {
435
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
436
		return ERR_DB;
437
438
439
	}

	# Grab row
440
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( Code ));
441
442
	DBFreeRes($sth);

443
444
	# Check we got a result
	if (!defined($row)) {
445
		setError("Error finding GL account '$GLAccID'");
446
447
448
		return ERR_NOTFOUND;
	}

Nigel Kukard's avatar
Nigel Kukard committed
449
	return $row->{'Code'} eq $finCatCode ? 1 : 0;
450
451
452
453
}



Nigel Kukard's avatar
Nigel Kukard committed
454
455
456
457
458
459
460
461
462
463
## @fn getGLAccountTree
# Resolve the set of accounts into a tree
#
# @returns Array ref of hash refs, @see getGLAccounts with the additional items...
# @li Children Array ref of hash refs, children of this account
# @li Level Depth level of this account
sub getGLAccountTree
{
	# Grab account list
	my $accounts = getGLAccounts();
Nigel Kukard's avatar
Nigel Kukard committed
464
	if (ref($accounts) ne "ARRAY") {
Nigel Kukard's avatar
Nigel Kukard committed
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
		return $accounts;
	}

	my %accountsByID;
	my @paccounts; # Parent accounts
	foreach my $account (@{$accounts}) {
		# Generate our rows by ID table
		$accountsByID{$account->{'ID'}} = $account;
		# Check if we're a parent account
		push(@paccounts,$account) if (!defined($account->{'ParentGLAccountID'}));
	}
	# Resolve children
	foreach my $account (@{$accounts}) {
		# Make sure we're a child and not a parent
		if (defined($account->{'ParentGLAccountID'})) {
			# This is the account parent
			my $parent = $accountsByID{$account->{'ParentGLAccountID'}};
			# Attach us as a child
			push(@{$parent->{'Children'}}, $account);
		}
	}
	# Resolve levels
	foreach my $parent (@paccounts) {
		# Resolve the account level
		sub resolveLevel {
			my ($raccount,$plevel) = @_;

			# We 1 level down
			$raccount->{'Level'} = $plevel + 1;
			# Loop with children
			foreach my $caccount (@{$raccount->{'Children'}}) {
				resolveLevel($caccount,$raccount->{'Level'});
			}
		}
		# Parents are level 0, so we set this to -1
		resolveLevel($parent,-1);
501
	}
Nigel Kukard's avatar
Nigel Kukard committed
502
503
504
505
506

	return \@paccounts;
}


Nigel Kukard's avatar
Nigel Kukard committed
507
# Return an array of general ledger accounts
Nigel Kukard's avatar
Nigel Kukard committed
508
sub getGLAccounts
Nigel Kukard's avatar
Nigel Kukard committed
509
510
511
512
513
{
	my @accounts = ();

	# Return list of GL accounts
	my $sth = DBSelect("
514
		SELECT
Nigel Kukard's avatar
Nigel Kukard committed
515
			gl_accounts.ID, gl_accounts.ParentGLAccountID, gl_accounts.Code, gl_accounts.Name,
516

517
			gl_financial_categories.Code AS FinCatCode, gl_financial_categories.Description AS FinCatDescription,
518

519
			gl_reportwriter_categories.Code AS RwCatCode, gl_reportwriter_categories.Description AS RwCatDescription
520

Nigel Kukard's avatar
Nigel Kukard committed
521
		FROM
522
			gl_accounts, gl_financial_categories, gl_reportwriter_categories
523

Nigel Kukard's avatar
Nigel Kukard committed
524
		WHERE
525
			gl_financial_categories.ID = gl_accounts.FinCatID
526
			AND gl_reportwriter_categories.ID = gl_accounts.RwCatID
Nigel Kukard's avatar
Nigel Kukard committed
527
	");
Nigel Kukard's avatar
Nigel Kukard committed
528
	if (!$sth) {
529
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
530
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
531
532
533
	}

	# Fetch rows
534
535
536
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ParentGLAccountID Code Name FinCatCode FinCatDescription RwCatCode RwCatDescription )
	)) {
Nigel Kukard's avatar
Nigel Kukard committed
537
		my $account = sanitizeRawGLAccountItem($row);
Nigel Kukard's avatar
Nigel Kukard committed
538

Nigel Kukard's avatar
Nigel Kukard committed
539
		push(@accounts,$account);
540
	}
Nigel Kukard's avatar
Nigel Kukard committed
541

542
	DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
543

Nigel Kukard's avatar
Nigel Kukard committed
544
545
546
	# Sort account listing
	@accounts = sort { $a->{'Number'} cmp $b->{'Number'} } @accounts;

547
548
	return \@accounts;
}
Nigel Kukard's avatar
Nigel Kukard committed
549

550
551
552

# Return a hash containing account details
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
553
#		AccountID		- GL account ID
Nigel Kukard's avatar
Nigel Kukard committed
554
555
#		AccountNumber	- GL account reference
sub getGLAccount
556
557
558
559
560
{
	my ($data) = @_;


	my $GLAccID;
Nigel Kukard's avatar
Nigel Kukard committed
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577

	# Check which 'mode' we operating in
	if (!defined($data->{'AccountID'}) || $data->{'AccountID'} < 1) {
		# Verify GL account ref
		if (!defined($data->{'AccountNumber'}) || $data->{'AccountNumber'} eq "") {
			setError("No (or invalid) account number provided");
			return ERR_PARAM;
		}

		# Check if account number exists
		if (($GLAccID = getGLAccountIDFromNumber($data->{'AccountNumber'})) < 1) {
			setError(Error());
			return $GLAccID;
		}
	} else {
		$GLAccID = $data->{'AccountID'};
	}
578

Nigel Kukard's avatar
Nigel Kukard committed
579
580
581
582
	# Verify account ID
	if (!$GLAccID || $GLAccID < 1) {
		setError("No (or invalid) account number or ID provided");
		return ERR_PARAM;
583
584
585
	}

	my $sth = DBSelect("
586
		SELECT
Nigel Kukard's avatar
Nigel Kukard committed
587
			gl_accounts.ID, gl_accounts.ParentGLAccountID, gl_accounts.Code, gl_accounts.Name,
588

589
			gl_financial_categories.Code AS FinCatCode, gl_financial_categories.Description AS FinCatDescription,
590

591
			gl_reportwriter_categories.Code AS RwCatCode, gl_reportwriter_categories.Description AS RwCatDescription
592

593
		FROM
594
			gl_accounts, gl_financial_categories, gl_reportwriter_categories
595

596
597
		WHERE
			gl_accounts.ID = ".DBQuote($GLAccID)."
598
			AND gl_financial_categories.ID = gl_accounts.FinCatID
599
			AND gl_reportwriter_categories.ID = gl_accounts.RwCatID
600
	");
Nigel Kukard's avatar
Nigel Kukard committed
601
	if (!$sth) {
602
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
603
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
604
605
	}

606
	# Fetch row
607
608
609
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw( ID ParentGLAccountID Code Name FinCatCode FinCatDescription RwCatCode RwCatDescription )
	);
610
611
	DBFreeRes($sth);

612
	# Check we got a result
613
	if (!defined($row)) {
614
		setError("Error finding account '$GLAccID'");
Nigel Kukard's avatar
Nigel Kukard committed
615
		return ERR_NOTFOUND;
616
617
	}

Nigel Kukard's avatar
Nigel Kukard committed
618

Nigel Kukard's avatar
Nigel Kukard committed
619
	return sanitizeRawGLAccountItem($row);
620
621
622
}


Nigel Kukard's avatar
Nigel Kukard committed
623
# Return the next sub account code
624
# Parameters:
Nigel Kukard's avatar
Nigel Kukard committed
625
626
#		AccountNumber	- GL account reference
sub getNextGLSubAccountCode
627
628
629
630
{
	my ($data) = @_;


Nigel Kukard's avatar
Nigel Kukard committed
631
632
633
634
	# Verify GL account number
	if (!defined($data->{'AccountNumber'}) || $data->{'AccountNumber'} eq "") {
		setError("No (or invalid) GL account number provided");
		return ERR_PARAM;
635
636
637
638
	}

	# Check GL account exists
	my $GLAccID;
Nigel Kukard's avatar
Nigel Kukard committed
639
	if (($GLAccID = getGLAccountIDFromNumber($data->{'AccountNumber'})) < 1) {
640
		setError(Error());
641
642
643
644
645
		return $GLAccID;
	}

	# Select last account
	my $sth = DBSelect("
646
		SELECT
Nigel Kukard's avatar
Nigel Kukard committed
647
			Code
648
649
650
		FROM
			gl_accounts
		WHERE
651
			ParentGLAccountID = ".DBQuote($GLAccID)."
Nigel Kukard's avatar
Nigel Kukard committed
652
		ORDER BY Code DESC
653
654
		LIMIT 1
	");
Nigel Kukard's avatar
Nigel Kukard committed
655
	if (!$sth) {
656
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
657
		return ERR_DB;
658
659
660
	}

	# Fetch row
661
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw( Code ));
662
663
	DBFreeRes($sth);

664
665
666
667
668
	# Check we got a result, if not return 1
	if (!defined($row)) {
		return 1;
	}

Nigel Kukard's avatar
Nigel Kukard committed
669
	return $row->{'Code'} + 1;
Nigel Kukard's avatar
Nigel Kukard committed
670
671
672
}


673
## @fn getGLTransactions($data)
Nigel Kukard's avatar
Nigel Kukard committed
674
# Return an array of general ledger transactions
675
676
677
678
679
#
# @param data Parameter hash ref
# @li AccountID Limit transactions to those relating to this account
# @li StartDate	Optional start date
# @li EndDate Optional end date
680
# @li Type Optional transaction type
681
682
683
684
685
686
#
# @returns Array ref of hash refs
# @li ID GL entry ID
# @li TransactionDate Transaction date
# @li Reference GL entry reference
# @li Posted 0 if unposted, 1 if posted
687
# @li Type Transaction type
Nigel Kukard's avatar
Nigel Kukard committed
688
sub getGLTransactions
Nigel Kukard's avatar
Nigel Kukard committed
689
{
690
691
	my $data = shift;

Nigel Kukard's avatar
Nigel Kukard committed
692
693
	my @transactions = ();

694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
	# Extra SQL we may need
	my $extraSQL = "";
	my $extraTables = "";
	my $extraEndSQL = "";

	# Check if we have an account ID
	if (defined($data->{'AccountID'}) && $data->{'AccountID'} > 0) {
		$extraTables .= ", gl_entries";
		$extraSQL .= "AND gl_entries.GLAccountID = ".DBQuote($data->{'AccountID'})." ";
		$extraSQL .= "AND gl_entries.GLTransactionID = gl_transactions.ID ";
		$extraEndSQL .= "GROUP BY gl_transactions.ID ";
	}

	# Check if we must use the start date
	if (defined($data->{'StartDate'}) && $data->{'StartDate'} ne "") {
		$extraSQL .= "AND gl_transactions.TransactionDate >= ".DBQuote($data->{'StartDate'})." ";
	}
	# Check if we must use the end date
	if (defined($data->{'EndDate'}) && $data->{'EndDate'} ne "") {
		$extraSQL .= "AND gl_transactions.TransactionDate <= ".DBQuote($data->{'EndDate'})." ";
	}

716
	# Check if we're filtering on type
717
	my $typeFilter = GL_TRANSTYPE_NORMAL;
718
719
720
721
	if (defined($data->{'Type'}) && $data->{'Type'} ne "") {
		$typeFilter = $data->{'Type'};
	}

Nigel Kukard's avatar
Nigel Kukard committed
722
723
	# Return list of GL transactions
	my $sth = DBSelect("
724
		SELECT
725
			gl_transactions.ID AS ID, TransactionDate, gl_transactions.Reference AS Reference, Type, Posted
Nigel Kukard's avatar
Nigel Kukard committed
726
		FROM
727
728
729
730
731
			gl_transactions $extraTables
		WHERE
			1 = 1
			$extraSQL
		$extraEndSQL
Nigel Kukard's avatar
Nigel Kukard committed
732
	");
Nigel Kukard's avatar
Nigel Kukard committed
733
	if (!$sth) {
734
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
735
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
736
737
738
	}

	# Fetch rows
739
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw( ID TransactionDate Reference Type Posted ))) {
740
		# Check filter, if this is not one of our items, continue
741
		if (! ($typeFilter & $row->{'Type'})) {
742
743
744
			next;
		}

Nigel Kukard's avatar
Nigel Kukard committed
745
		my $transaction = sanitizeRawGLTransactionItem($row);
Nigel Kukard's avatar
Nigel Kukard committed
746

Nigel Kukard's avatar
Nigel Kukard committed
747
		push(@transactions,$transaction);
Nigel Kukard's avatar
Nigel Kukard committed
748
749
750
751
752
753
754
755
	}

	DBFreeRes($sth);

	return \@transactions;
}


756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
# Return a hash containing a GL transaction
# Parameters:
#		ID	- Transaction ID
sub getGLTransaction
{
	my ($detail) = @_;


	# Verify params
	if (!defined($detail->{'ID'}) || $detail->{'ID'} < 1) {
		setError("Transaction ID not provided");
		return ERR_PARAM;
	}

	# Return list of GL transactions
	my $sth = DBSelect("
772
		SELECT
773
774
775
776
777
778
779
			ID, TransactionDate, Reference, Posted
		FROM
			gl_transactions
		WHERE
			ID = ".DBQuote($detail->{'ID'})."
	");
	if (!$sth) {
780
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
781
		return ERR_DB;
782
783
784
	}

	# Fetch rows
785
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( ID TransactionDate Reference Posted ));
786
787
788
789
790
791
792
793
794
795
796
797
	DBFreeRes($sth);

	# Check we got a result
	if (!$row) {
		setError("GL transaction '".$detail->{'ID'}."' not found");
		return ERR_NOTFOUND;
	}

	return sanitizeRawGLTransactionItem($row);
}


798
## @fn createGLTransaction($data)
Nigel Kukard's avatar
Nigel Kukard committed
799
# Create GL transaction
800
801
802
803
804
805
806
#
# @param data Parameter hash ref
# @li Date Date of transaction
# @li Reference Reference of transaction
# @li Type Optional transaction type
#
# @returns Transaction ID
Nigel Kukard's avatar
Nigel Kukard committed
807
sub createGLTransaction
Nigel Kukard's avatar
Nigel Kukard committed
808
{
809
	my ($detail) = @_;
Nigel Kukard's avatar
Nigel Kukard committed
810

Nigel Kukard's avatar
Nigel Kukard committed
811

812
813
814
815
	# Extra SQL we may need
	my $extraColumns = "";
	my $extraValues = "";

Nigel Kukard's avatar
Nigel Kukard committed
816
	# Verify date
817
	if (!defined($detail->{'Date'}) || $detail->{'Date'} eq "") {
818
		setError("No date provided for GL transaction");
Nigel Kukard's avatar
Nigel Kukard committed
819
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
820
821
822
	}

	# Verify reference
Nigel Kukard's avatar
Nigel Kukard committed
823
	if (!defined($detail->{'Reference'}) || $detail->{'Reference'} eq "") {
824
		setError("No reference provided for GL transaction");
Nigel Kukard's avatar
Nigel Kukard committed
825
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
826
	}
827

828
829
830
831
832
833
	# Verify type
	if (defined($detail->{'Type'}) && $detail->{'Type'} ne "") {
		$extraColumns .= ",Type";
		$extraValues .= ",".DBQuote($detail->{'Type'});
	}

Nigel Kukard's avatar
Nigel Kukard committed
834
835
	# Create GL transaction
	my $sth = DBDo("
836
		INSERT INTO gl_transactions
837
				(TransactionDate,Reference$extraColumns)
Nigel Kukard's avatar
Nigel Kukard committed
838
839
			VALUES
				(
840
					".DBQuote($detail->{'Date'}).",
Nigel Kukard's avatar
Nigel Kukard committed
841
					".DBQuote($detail->{'Reference'})."
842
					$extraValues
Nigel Kukard's avatar
Nigel Kukard committed
843
844
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
845
	if (!$sth) {
846
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
847
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
848
849
850
851
852
853
854
855
856
	}

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

	return $ID;
}


Nigel Kukard's avatar
Nigel Kukard committed
857
# Link GL transaction using account number instead of ID
858
# Parameters:
859
#		ID				- Transaction ID
Nigel Kukard's avatar
Nigel Kukard committed
860
#		GLAccountNumber	- GL account number
861
#		Amount			- Amount to post
Nigel Kukard's avatar
Nigel Kukard committed
862
863
#		Reference		- Transaction reference
sub linkGLTransactionByAccountNumber
864
865
866
867
868
869
{
	my ($data) = @_;


	# Check GL account exists
	my $GLAccID;
Nigel Kukard's avatar
Nigel Kukard committed
870
	if (($GLAccID = getGLAccountIDFromNumber($data->{'GLAccountNumber'})) < 1) {
871
		setError(Error());
872
873
		return $GLAccID;
	}
874

875
	# Add ID
Nigel Kukard's avatar
Nigel Kukard committed
876
	$data->{'GLAccountID'} = $GLAccID;
877
878

	# And Link
Nigel Kukard's avatar
Nigel Kukard committed
879
	return linkGLTransaction($data);
880
881
882
}


Nigel Kukard's avatar
Nigel Kukard committed
883
# Link GL transaction
884
# Parameters:
885
#		ID				- Transaction ID
Nigel Kukard's avatar
Nigel Kukard committed
886
#		GLAccountID		- GL account ID
887
#		Amount			- Amount to post
Nigel Kukard's avatar
Nigel Kukard committed
888
889
#		Reference		- Transaction reference
sub linkGLTransaction
Nigel Kukard's avatar
Nigel Kukard committed
890
{
891
	my ($data) = @_;
Nigel Kukard's avatar
Nigel Kukard committed
892

Nigel Kukard's avatar
Nigel Kukard committed
893
894

	# Verify params
895
	if (!defined($data->{'ID'}) || $data->{'ID'} < 1) {
896
		setError("Transaction ID not provided");
Nigel Kukard's avatar
Nigel Kukard committed
897
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
898
899
	}

Nigel Kukard's avatar
Nigel Kukard committed
900
	if (!defined($data->{'GLAccountID'}) || $data->{'GLAccountID'} < 1) {
901
		setError("Account ID not provided for transaction '".$data->{'ID'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
902
		return ERR_PARAM;
903
	}
Nigel Kukard's avatar
Nigel Kukard committed
904

905
906
907
908
909
910
911
912
913
914
	if (!defined($data->{'Amount'}) && !defined($data->{'Credit'}) && !defined($data->{'Debit'})) {
		setError("Amount or Credit or Debit must be specified");
		return ERR_PARAM;
	}
	if (defined($data->{'Credit'}) && defined($data->{'Debit'})) {
		setError("Parameter 'Credit' and 'Debit' cannot be specified for the same transcation");
		return ERR_PARAM;
	}
	if ((defined($data->{'Credit'}) && defined($data->{'Amount'})) || (defined($data->{'Debit'}) && defined($data->{'Amount'}))) {
		setError("Parameter 'Credit'/'Debit' is incompatible with 'Amount'");
Nigel Kukard's avatar
Nigel Kukard committed
915
		return ERR_PARAM;
Nigel Kukard's avatar
Nigel Kukard committed
916
917
	}

918
919
920
921
922
923
924
925
	# Lets get some account info
	my $params;
	$params->{'AccountID'} = $data->{'GLAccountID'};
	my $account = getGLAccount($params);
	if (ref $account ne "HASH") {
		return $account;
	}

926
	# Pull in amount
927
	my $cleanAmount = Math::BigFloat->new(0);
Nigel Kukard's avatar
Nigel Kukard committed
928
	$cleanAmount->precision(-2);
929
930
931
932
933
934

	# Lets see what we going to pull in...
	# We use ABS to get positive value no matte what
	# We use bneg() to negate this in case of a debit
	if (defined($data->{'Credit'})) {
		$cleanAmount->badd($data->{'Credit'})->babs();
935
936
937
938
939
940
941
942
		# NK: Credit vs. Debit based on account type, are we going to reverse this?
		foreach my $finCatCode ("A01", "B01", "D01", "E01") {
			# Check for match
			if ($account->{'FinCatCode'} eq $finCatCode) {
				$cleanAmount->bneg();
				last;
			}
		}
943
	} elsif (defined($data->{'Debit'})) {
944
945
946
947
948
949
950
951
952
953
		$cleanAmount->bsub($data->{'Debit'})->babs();

		# NK: Credit vs. Debit based on account type, are we going to reverse this?
		foreach my $finCatCode ("B01","C01","D01") {
			# Check for match
			if ($account->{'FinCatCode'} eq $finCatCode) {
				last;
			}
		}

954
955
956
957
	} elsif (defined($data->{'Amount'})) {
		$cleanAmount->badd($data->{'Amount'});
	}

Nigel Kukard's avatar
Nigel Kukard committed
958
	#NK - wtf .... should we or shouldn't we check?
959
	# still not sure, its disabled for a reason I guess ... ??
960
961
962
963
#	if ($cleanAmount->is_zero()) {
#		setError("Amount cannot be zero");
#		return ERR_AMTZERO;
#	}
964

Nigel Kukard's avatar
Nigel Kukard committed
965
	# Decide what to do with ref
Nigel Kukard's avatar
Nigel Kukard committed
966
	my $ref = defined($data->{'Reference'}) ? DBQuote($data->{'Reference'}) : "NULL";
Nigel Kukard's avatar
Nigel Kukard committed
967

Nigel Kukard's avatar
Nigel Kukard committed
968
969
	# Return list of GL transactions
	my $sth = DBSelect("
970
		SELECT
971
			Posted
Nigel Kukard's avatar
Nigel Kukard committed
972
973
974
		FROM
			gl_transactions
		WHERE
975
			ID = ".DBQuote($data->{'ID'})."
Nigel Kukard's avatar
Nigel Kukard committed
976
	");
Nigel Kukard's avatar
Nigel Kukard committed
977
	if (!$sth) {
978
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
979
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
980
981
	}

982
	my $row = hashifyLCtoMC($sth->fetchrow_hashref(),qw( Posted ));
983
984
	DBFreeRes($sth);

Nigel Kukard's avatar
Nigel Kukard committed
985
	# Check we got a result
986
	if (!defined($row)) {
987
		setError("Error finding transaction '".$data->{'ID'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
988
		return ERR_NOTFOUND;
Nigel Kukard's avatar
Nigel Kukard committed
989
990
991
992
	}

	# Check if we not posted
	if ($row->{'Posted'} == '1') {
993
		setError("Cannot link to a posted transaction '".$data->{'ID'}."'");
Nigel Kukard's avatar
Nigel Kukard committed
994
		return ERR_POSTED;
Nigel Kukard's avatar
Nigel Kukard committed
995
996
	}

Nigel Kukard's avatar
Nigel Kukard committed
997
	# Create GL entry
Nigel Kukard's avatar
Nigel Kukard committed
998
	$sth = DBDo("
999
1000
		INSERT INTO gl_entries
				(GLTransactionID,GLAccountID,Reference,Amount)
Nigel Kukard's avatar
Nigel Kukard committed
1001
1002
			VALUES
				(
1003
					".DBQuote($data->{'ID'}).",
Nigel Kukard's avatar
Nigel Kukard committed
1004
					".DBQuote($data->{'GLAccountID'}).",
Nigel Kukard's avatar
Nigel Kukard committed
1005
					$ref,
1006
					".DBQuote($cleanAmount->bstr())."
Nigel Kukard's avatar
Nigel Kukard committed
1007
1008
				)
	");
Nigel Kukard's avatar
Nigel Kukard committed
1009
	if (!$sth) {
1010
		setError(awitpt::db::dblayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
1011
		return ERR_DB;
Nigel Kukard's avatar
Nigel Kukard committed
1012
1013
1014
1015
1016
1017
1018
1019
1020
	}

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

	return $ID;
}


Nigel Kukard's avatar
Nigel Kukard committed
1021
# Post GL transaction
1022
# Parameters:
1023
#		ID		- Transaction ID