Reporting.pm 29.9 KB
Newer Older
Nigel Kukard's avatar
Nigel Kukard committed
1
# Reporting functions
2
# Copyright (C) 2009-2019, AllWorldIT
Nigel Kukard's avatar
Nigel Kukard committed
3
# Copyright (C) 2008, LinuxRulz
Nigel Kukard's avatar
Nigel Kukard committed
4
# Copyright (C) 2007 Nigel Kukard  <nkukard@lbsd.net>
5
#
Nigel Kukard's avatar
Nigel Kukard committed
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
#
Nigel Kukard's avatar
Nigel Kukard committed
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
#
Nigel Kukard's avatar
Nigel Kukard committed
16
17
18
19
20
21
22
# 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.




23
package wiaflos::server::core::Reporting;
Nigel Kukard's avatar
Nigel Kukard committed
24
25
26
27
28
29
30

use strict;
use warnings;


use wiaflos::version;
use wiaflos::constants;
31
32
33
34
35
use wiaflos::server::core::config;
use awitpt::db::dblayer;
use wiaflos::server::core::templating;
use wiaflos::server::core::GL;
use wiaflos::server::core::jobs;
Nigel Kukard's avatar
Nigel Kukard committed
36
37
38

use Math::BigFloat;

39
40
41
42
43
44
# Exporter stuff
require Exporter;
our (@ISA,@EXPORT,@EXPORT_OK);
@ISA = qw(Exporter);
@EXPORT = qw(
	REPORT_BALANCE_BF
Nigel Kukard's avatar
Nigel Kukard committed
45
	REPORT_INCLUDE_YEAREND
46
47
	REPORT_INCLUDE_AUDIT
	REPORT_INCLUDE_AUDIT_YEAREND
48
49
50
51
52
53
);
@EXPORT_OK = ();


use constant {
	REPORT_BALANCE_BF	=> 1,
Nigel Kukard's avatar
Nigel Kukard committed
54
	REPORT_INCLUDE_YEAREND	=> 2,
55
56
	REPORT_INCLUDE_AUDIT	=> 4,
	REPORT_INCLUDE_AUDIT_YEAREND	=> 8,
57
58
};

Nigel Kukard's avatar
Nigel Kukard committed
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90



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

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

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

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

	# Reset error
	$error = "";

	# Return error
	return $err;
}


91
## @fn getAccountBalances
Nigel Kukard's avatar
Nigel Kukard committed
92
93
# Function to return the chart of accounts with balances
#
94
95
# @param flags Flags of what we returned, options are below, use || (OR)
# @li REPORT_BALANCE_BF - Return balance brought forward for each account
Nigel Kukard's avatar
Nigel Kukard committed
96
# @li REPORT_INCLUDE_YEAREND - Get balances and include yearend transactions
97
98
# @li REPORT_INCLUDE_AUDIT - Get balances and include audit adjustment transactions
# @li REPORT_INCLUDE_AUDIT_YEAREND - Get balances and include audited yearend adjustment transactions
99
100
101
102
103
#
# @param data Parameter hash ref
# @li StartDate - Optional statement start date
# @li EndDate - Optional statement end date
# @li Levels - Optional depth to go into on each parent account
Nigel Kukard's avatar
Nigel Kukard committed
104
105
#
# @returns Array ref of hash refs containing the accounts and their balances
106
107
108
109
110
111
112
113
114
115
116
# @li ID - Account ID
# @li Number - Account number
# @li Name - Name of account
#
# @li Balance - Account balance
# Balance of account, can either be - (credit) or + (debit)
#
# @li DebitBalance - Optional, this is the total of all debits
# @li CreditBalance - Optional, this is the total of all credits
#
# @li BalanceBroughtForward - Optional, returned when REPORT_BALANCE_BF is specified
117
# This is the balance of the account for the period up until this reporting period
118
sub getAccountBalances
Nigel Kukard's avatar
Nigel Kukard committed
119
{
120
121
	our $flags = shift;
	my $data = shift;
122

Nigel Kukard's avatar
Nigel Kukard committed
123
124

	# Get our account tree
125
	my $res = wiaflos::server::core::GL::getGLAccountTree();
126
	if (ref($res) ne "ARRAY") {
127
		setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
128
129
130
		return $res;
	}

Nigel Kukard's avatar
Nigel Kukard committed
131
132
133
134
135
	# GL search criteria
	my $search;
	$search->{'StartDate'} = $data->{'StartDate'};
	$search->{'EndDate'} = $data->{'EndDate'};
	$search->{'Levels'} = $data->{'Levels'};
136
	$search->{'Type'} = GL_TRANSTYPE_NORMAL;
Nigel Kukard's avatar
Nigel Kukard committed
137
138
139

	# Check if we must include yearend transactions
	if (($flags & REPORT_INCLUDE_YEAREND) == REPORT_INCLUDE_YEAREND) {
140
141
142
143
144
145
146
147
148
149
150
		$search->{'Type'} |= GL_TRANSTYPE_YEAREND;
	}

	# Check if we must include audit adjustment transactions
	if (($flags & REPORT_INCLUDE_AUDIT) == REPORT_INCLUDE_AUDIT) {
		$search->{'Type'} |= GL_TRANSTYPE_AUDIT;
	}

	# Check if we must include audit yearend adjustment transactions
	if (($flags & REPORT_INCLUDE_AUDIT_YEAREND) == REPORT_INCLUDE_AUDIT_YEAREND) {
		$search->{'Type'} |= GL_TRANSTYPE_AUDIT_YEAREND;
Nigel Kukard's avatar
Nigel Kukard committed
151
	}
Nigel Kukard's avatar
Nigel Kukard committed
152

153
	# Loop with the parent accounts, remember they're top level
Nigel Kukard's avatar
Nigel Kukard committed
154
155
156
157
158
159
160
	foreach my $account (@{$res}) {

		# Process child accounts within parent
		sub processChildren
		{
			my ($paccount,$search) = @_;

161
			# This is our final balances
162
163
164
165
			my $creditBalance = Math::BigFloat->new(0);
			my $openingCreditBalance = Math::BigFloat->new(0);
			my $debitBalance = Math::BigFloat->new(0);
			my $openingDebitBalance = Math::BigFloat->new(0);
166
			$creditBalance->precision(-2);
167
			$openingCreditBalance->precision(-2);
168
			$debitBalance->precision(-2);
169
			$openingDebitBalance->precision(-2);
170

Nigel Kukard's avatar
Nigel Kukard committed
171
172
173
174
175
			# Loop with children
			foreach my $caccount (@{$paccount->{'Children'}}) {
				# Process child
				my $pcres = processChildren($caccount,$search);
				if ($pcres) {
176
					setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
177
178
179
					return $pcres;
				}
				# Add up balance of child...
180
181
				$creditBalance->badd($caccount->{'CreditBalance'});
				$debitBalance->badd($caccount->{'DebitBalance'});
182
				# Check if we should add up opening balances
183
				if (($flags & REPORT_BALANCE_BF) == REPORT_BALANCE_BF) {
184
185
186
					$openingCreditBalance->badd($caccount->{'OpeningCreditBalance'});
					$openingDebitBalance->badd($caccount->{'OpeningDebitBalance'});
				}
Nigel Kukard's avatar
Nigel Kukard committed
187
			}
188

Nigel Kukard's avatar
Nigel Kukard committed
189
			# Build param we need to pass to get this accounts balance
190
191
192
			my $info;
			$info->{'StartDate'} = $search->{'StartDate'};
			$info->{'EndDate'} = $search->{'EndDate'};
Nigel Kukard's avatar
Nigel Kukard committed
193
			$info->{'Type'} = $search->{'Type'};
Nigel Kukard's avatar
Nigel Kukard committed
194
195
			$info->{'AccountID'} = $paccount->{'ID'};
			# Get our own account balance
196
			my $res = wiaflos::server::core::GL::getGLAccountBalance($info);
197
			if (ref($res) ne "HASH") {
198
				setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
199
200
				return $res;
			}
201
			# Pull in balances...
202
203
			$creditBalance->badd($res->{'CreditBalance'});
			$debitBalance->badd($res->{'DebitBalance'});
204
			my $balance = $debitBalance->copy()->bsub($creditBalance);
205
			# Set parent account balances
206
207
			$paccount->{'CreditBalance'} = $creditBalance->bstr();
			$paccount->{'DebitBalance'} = $debitBalance->bstr();
208
209
210
			$paccount->{'Balance'} = $balance->bstr();

			# Check if we need to pull in balance brought forward
211
			if (($flags & REPORT_BALANCE_BF) == REPORT_BALANCE_BF) {
212
213
214
				# We can only do this if we have a start date, else its pointless?
				if ($search->{'StartDate'}) {
					my $bfinfo;
215
					$bfinfo->{'EndDate'} = $search->{'StartDate'};
Nigel Kukard's avatar
Nigel Kukard committed
216
					$bfinfo->{'EndDateExcl'} = 1;
Nigel Kukard's avatar
Nigel Kukard committed
217
					$bfinfo->{'Type'} = $search->{'Type'};
218
					$bfinfo->{'AccountID'} = $paccount->{'ID'};
219
					# Pull in balance brought forward
220
					$res = wiaflos::server::core::GL::getGLAccountBalance($bfinfo);
221
					if (ref($res) ne "HASH") {
222
						setError(wiaflos::server::core::GL::Error());
223
224
						return $res;
					}
225
					# Total up
226
227
228
229
230
231
232
233
234
235
					$openingCreditBalance->badd($res->{'CreditBalance'});
					$openingDebitBalance->badd($res->{'DebitBalance'});
				}
				# Set opening blalance
				my $openingBalance = $openingDebitBalance->copy()->bsub($openingCreditBalance);
				# Set parent account balances
				$paccount->{'OpeningCreditBalance'} = $openingCreditBalance->bstr();
				$paccount->{'OpeningDebitBalance'} = $openingDebitBalance->bstr();
				$paccount->{'OpeningBalance'} = $openingBalance->bstr();
				# Set closing balance
236
				$paccount->{'ClosingBalance'} = $openingBalance->copy()->badd($balance)->bstr();
237
			}
Nigel Kukard's avatar
Nigel Kukard committed
238
239
240
241
242
243
244

			return 0;
		}

		# Process this parent account
		my $pcres = processChildren($account,$search);
		if ($pcres) {
245
			setError(wiaflos::server::core::GL::Error());
Nigel Kukard's avatar
Nigel Kukard committed
246
247
248
249
250
251
252
253
			return $pcres;
		}
	}

	return $res;
}


254

Nigel Kukard's avatar
Nigel Kukard committed
255
## @fn sendReport($detail)
256
257
258
259
# Function to send a report
#
# @param detail Parameter hash ref
# @li Template - Tempalte to use
Nigel Kukard's avatar
Nigel Kukard committed
260
# @li GLAccountNumber - Optional GL account number(s) used for some reports
261
262
263
264
# @li SendTo - Send to,  email:  ,  file:  , return
# @li Subject - Optional Subject of the report
# @li StartDate - Optional statement start date
# @li EndDate - Optional statement end date
265
# @li Background - Optional, allow this report to be sent in the background
266
267
#
# @returns RES_OK on success, < 0 otherwise
268
269
sub sendReport
{
270
271
	our ($detail) = @_;

Nigel Kukard's avatar
Nigel Kukard committed
272

273
	# Variables used in the API functions
Nigel Kukard's avatar
Nigel Kukard committed
274
275
276
277
278
279
280
	our %variables = ();
	# Constants used in API functions
	use constant {
		API_FMT_REVERSE => 1,
		API_FMT_NEGBRACKET => 2,
	};

281
282
283
	# Reporting API function to retrieve account balances
	sub api_account_balances
	{
284
285
286
287
288
289
290
291
		our $flags = shift;


		# Set flags to 0 if we don't have any
		if (!defined($flags) || $flags eq "") {
			$flags = 0;
		}

292
293
294
295
		# GL search criteria
		my $search;
		$search->{'StartDate'} = $detail->{'StartDate'};
		$search->{'EndDate'} = $detail->{'EndDate'};
296

297
		my $entries = getAccountBalances($flags,$search);
298
		if (ref($entries) ne "ARRAY") {
299
			wiaflos::server::core::templating::abort('api_account_balances',$entries,Error());
300
		}
Nigel Kukard's avatar
Nigel Kukard committed
301
302
303
304
305

		our @accountList = ();
		if (defined($detail->{'GLAccountNumber'})) {
			@accountList = split(/[,;]/,$detail->{'GLAccountNumber'});
		}
306

307
		# Result to return
Nigel Kukard's avatar
Nigel Kukard committed
308
		our $resdata = undef;
309

310
311
		# Load line items for GL
		foreach my $item (@{$entries}) {
312

313
314
315
316
			# Process item
			sub processItem
			{
				my $account = shift;
317
318


319
				my $i;
320

321
322
323
				# Fix some stuff up
				$i->{'Number'} = $account->{'Number'};
				$i->{'Name'} = $account->{'Name'};
324

325
326
327
328
329
				# Pull in the normal balances
				$i->{'DebitBalance'} = $account->{'DebitBalance'};
				$i->{'CreditBalance'} = $account->{'CreditBalance'};
				$i->{'Balance'} = $account->{'Balance'};
				# Check if we need the opening balances
330
331
332
333
334
335
336
				if (($flags & REPORT_BALANCE_BF) == REPORT_BALANCE_BF) {
					$i->{'OpeningDebitBalance'} = $account->{'OpeningDebitBalance'};
					$i->{'OpeningCreditBalance'} = $account->{'OpeningCreditBalance'};
					$i->{'OpeningBalance'} = $account->{'OpeningBalance'};
					# And closing balance
					$i->{'ClosingBalance'} = $account->{'ClosingBalance'};
				}
337

338
				# Check balance
339
				my $balance = Math::BigFloat->new(0);
340
341
342
343
344
345
346
347
348
				$balance->precision(-2);
				$balance->badd($account->{'Balance'});
				# Check if its negative or positive
				if ($balance->is_neg()) {
					$balance->babs();
					$i->{'CreditAmount'} = sprintf('%.2f',$balance->bstr());
				} else {
					$i->{'DebitAmount'} = sprintf('%.2f',$balance->bstr());
				}
349

350
				$i->{'ReportWriterCategoryCode'} = $account->{'RwCatCode'};
351
352

				# If we have children, process them
353
354
355
356
357
358
359
360
361
				if ($account->{'Children'}) {
					# Loop with child accounts
					foreach my $caccount (@{$account->{'Children'}}) {
						# Process
						my $citem = processItem($caccount);
						# Add to child list
						push(@{$i->{'Children'}},$citem);
					}
				}
362

Nigel Kukard's avatar
Nigel Kukard committed
363
364
365
366
367
368
369
370
371
372
373
374
375
376
				my $add = 0;
				# Check if we have a limit list on the accounts to return
				if (@accountList > 0) {
					# Try find a match
					foreach my $j (@accountList) {
						if ($i->{'Number'} =~ /^$j:/) {
							$add = 1;
							last;
						}
					}
				} else {
					$add = 1;
				}

377
				# File balance under account number
Nigel Kukard's avatar
Nigel Kukard committed
378
379
380
 				if ($add) {
					$resdata->{'AccountBalances'}{$i->{'Number'}} = $i;
				}
381

382
383
				return $i;
			}
384

385
386
			# Process top level item
			my $pitem = processItem($item);
387

Nigel Kukard's avatar
Nigel Kukard committed
388
389
390
391
392
393
394
395
396
397
398
399
400
401
			my $add = 0;
			# Check if we have a limit list on the accounts to return
			if (@accountList > 0) {
				# Try find a match
				foreach my $j (@accountList) {
					if ($pitem->{'Number'} =~ /^$j:/) {
						$add = 1;
						last;
					}
				}
			} else {
				$add = 1;
			}

402
			# File under report write category code
Nigel Kukard's avatar
Nigel Kukard committed
403
404
405
			if ($add) {
				push(@{$resdata->{'ReportWriterCategoryToAccounts'}{$pitem->{'ReportWriterCategoryCode'}}},$pitem);
			}
406
		}
407

408
		# Load report write categories
409
		$entries = wiaflos::server::core::GL::getGLRwCats();
410
		if (ref($entries) ne "ARRAY") {
411
			setError(wiaflos::server::core::GL::Error());
412
413
414
415
416
			return $entries;
		}
		foreach my $item (@{$entries}) {
			$resdata->{'ReportWriterCategories'}{$item->{'Code'}} = $item;
		}
417

418
419
420
		return $resdata;
	}

421

422
423
424
	# Reporting API function to retrieve GL transactions
	sub api_gl_transactions
	{
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
		our $flags = shift;


		# Set flags to 0 if we don't have any
		if (!defined($flags) || $flags eq "") {
			$flags = 0;
		}

		my $types = GL_TRANSTYPE_NORMAL;

		# Check if we must include yearend transactions
		if (($flags & REPORT_INCLUDE_YEAREND) == REPORT_INCLUDE_YEAREND) {
			$types |= GL_TRANSTYPE_YEAREND;
		}

		# Check if we must include audit adjustment transactions
		if (($flags & REPORT_INCLUDE_AUDIT) == REPORT_INCLUDE_AUDIT) {
			$types |= GL_TRANSTYPE_AUDIT;
		}

		# Check if we must include audit yearend adjustment transactions
		if (($flags & REPORT_INCLUDE_AUDIT_YEAREND) == REPORT_INCLUDE_AUDIT_YEAREND) {
			$types |= GL_TRANSTYPE_AUDIT_YEAREND;
		}

450
		my $resdata = ();
451

452
		# Get account data
453
		my $accounts = wiaflos::server::core::GL::getGLAccounts();
454
		if (ref($accounts) ne "ARRAY") {
455
			wiaflos::server::core::templating::abort('api_gl_transactions',$accounts,wiaflos::server::core::GL::Error());
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
		}
		# Sort based on account number
		@{$accounts} = sort { $a->{'Number'} cmp $b->{'Number'} } @{$accounts};
		foreach my $account (@{$accounts}) {

			my $aitem;
			$aitem->{'ID'} = $account->{'ID'};
			$aitem->{'Number'} = $account->{'Number'};
			$aitem->{'Name'} = $account->{'Name'};

			# Grab transactions
			my $tmp;
			$tmp->{'AccountID'} = $account->{'ID'};
			$tmp->{'StartDate'} = $detail->{'StartDate'};
			$tmp->{'EndDate'} = $detail->{'EndDate'};
471
			$tmp->{'Type'} = $types;
472
			my $transactions = wiaflos::server::core::GL::getGLTransactions($tmp);
473
			if (ref($transactions) ne "ARRAY") {
474
				wiaflos::server::core::templating::abort('api_gl_transactions',$transactions,wiaflos::server::core::GL::Error());
475
			}
476
477
478
479
480
481
482
			# Sort based on transaction date
			@{$transactions} = sort { $a->{'TransactionDate'} cmp $b->{'TransactionDate'} } @{$transactions};
			# Loop and process
			foreach my $transaction (@{$transactions}) {
				# Only report on posted transactions
				next if (!$transaction->{'Posted'});

483
				# Setup our transaction item
484
485
486
487
				my $titem;
				$titem->{'ID'} = $transaction->{'ID'};
				$titem->{'TransactionDate'} = $transaction->{'TransactionDate'};
				$titem->{'Reference'} = $transaction->{'Reference'};
488
				$titem->{'Type'} = $transaction->{'Type'};
489
490
491
492

				# Pull in transaction entries
				$tmp = undef;
				$tmp->{'ID'} = $transaction->{'ID'};
493
				my $transactionEntries = wiaflos::server::core::GL::getGLTransactionEntries($tmp);
494
495
496
				# Loop and process
				foreach my $transactionEntry (@{$transactionEntries}) {
					my $eitem;
497

498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
					# Start creating our transaction entry
					$eitem->{'ID'} = $transactionEntry->{'ID'};
					$eitem->{'GLAccountID'} = $transactionEntry->{'GLAccountID'};
					$eitem->{'GLAccountName'} = $transactionEntry->{'GLAccountName'};
					$eitem->{'GLAccountNumber'} = $transactionEntry->{'GLAccountNumber'};

					# Clean up amount
					my $cleanAmount = Math::BigFloat->new($transactionEntry->{'Amount'});
					$cleanAmount->precision(-2);
					$eitem->{'Amount'} = $cleanAmount->bstr();

					$eitem->{'Reference'} = $transactionEntry->{'Reference'};

					# Add entry to transaction
					push(@{$titem->{'Entries'}},$eitem);
				}
				# Add transaction to account
515
				push(@{$aitem->{'Transactions'}},$titem);
516
517
518
519
520
521
522
523
524
			}
			# Add account to return data
			push(@{$resdata},$aitem);
		}

		return $resdata;
	}


525
526
527
528
	# Reporting API function to retrieve account entries
	sub api_account_entries
	{
		my $resdata;
529

530
531
532
		# Get account data
		my $tmp;
		$tmp->{'AccountNumber'} = $detail->{'GLAccountNumber'};
533
		my $account = wiaflos::server::core::GL::getGLAccount($tmp);
534
		if (ref($account) ne "HASH") {
535
			wiaflos::server::core::templating::abort('api_account_entries',$account,wiaflos::server::core::GL::Error());
536
537
		}
		$resdata->{'Account'} = $account;
538
539

		$tmp = undef;
540
541
542
543
		$tmp->{'AccountID'} = $account->{'ID'};
		$tmp->{'StartDate'} = $detail->{'StartDate'};
		$tmp->{'EndDate'} = $detail->{'EndDate'};
		$tmp->{'BalanceBroughtForward'} = 1;
544
		my $res = wiaflos::server::core::GL::getGLAccountEntries($tmp);
545
		if (ref($res) ne "ARRAY") {
546
			wiaflos::server::core::templating::abort('api_account_entries',$res,wiaflos::server::core::GL::Error());
547
548
		}

549
		# Sort so our balance makes sense
550
551
552
		@{$res} = sort { $a->{'TransactionDate'} cmp $b->{'TransactionDate'} } @{$res};

		# Work out closing balance
553
		my $closingBalance = Math::BigFloat->new(0);
554
555
556
557
558
		$closingBalance->precision(-2);

		# Fetch items
		foreach my $item (@{$res}) {
			my $entry;
559

560
561
562
563
			$entry->{'ID'} = $item->{'ID'};
			$entry->{'GLTransactionID'} = $item->{'GLTransactionID'};
			$entry->{'Reference'} = defined($item->{'Reference'}) ? $item->{'Reference'} : $item->{'TransactionReference'};
			$entry->{'Amount'} = $item->{'Amount'};
564
			# Calculate balance so far
565
566
567
			$closingBalance->badd($entry->{'Amount'});
			# Make it pretty
			$entry->{'Balance'} = sprintf('%.2f',$closingBalance->bstr());
568

569
570
			$entry->{'TransactionDate'} = $item->{'TransactionDate'};
			$entry->{'TransactionReference'} = $item->{'TransactionReference'};
571

572
573
574
575
576
			push(@{$resdata->{'Entries'}},$entry);
		}
		# Generate closing balance entry
		my $entry;
		$entry->{'ID'} = -99;
Nigel Kukard's avatar
Nigel Kukard committed
577
		$entry->{'GLTransactionID'} = -99;
578
579
580
581
		$entry->{'Reference'} = "* Closing Balance *";
		$entry->{'Balance'} = sprintf('%.2f',$closingBalance->bstr());
		$entry->{'TransactionDate'} = $detail->{'EndDate'};
		push(@{$resdata->{'Entries'}},$entry);
582

583
584
585
586
587
588
589
		# Sort results
		@{$resdata->{'Entries'}} = sort { $a->{'TransactionDate'} cmp $b->{'TransactionDate'} } @{$resdata->{'Entries'}};

		return $resdata;
	}


Nigel Kukard's avatar
Nigel Kukard committed
590
591
592
593
	# Reporting API function to retrieve inventory stock balances
	sub api_inventory_stock_balances
	{
		my $resdata;
594

Nigel Kukard's avatar
Nigel Kukard committed
595
596
597
		# Get inventory data
		my $tmp;
		$tmp->{'EndDate'} = $detail->{'EndDate'};
598
		my $res = wiaflos::server::core::Inventory::getInventoryStockBalance($tmp);
Nigel Kukard's avatar
Nigel Kukard committed
599
		if (ref($res) ne "HASH") {
600
			wiaflos::server::core::templating::abort('api_inventory_stock_balances',$res,wiaflos::server::core::Inventory::Error());
Nigel Kukard's avatar
Nigel Kukard committed
601
602
603
		}

		# Work out closing balance
604
		my $totalValue = Math::BigFloat->new(0);
Nigel Kukard's avatar
Nigel Kukard committed
605
606
607
608
609
610
		$totalValue->precision(-2);

		# Loop with stock codes
		foreach my $itemCode (keys %{$res}) {
			my $entry;

611
			$entry->{'TotalQuantity'} = Math::BigFloat->new(0);
Nigel Kukard's avatar
Nigel Kukard committed
612
			$entry->{'TotalQuantity'}->precision(-4);
613
			$entry->{'TotalValue'} = Math::BigFloat->new(0);
Nigel Kukard's avatar
Nigel Kukard committed
614
615
616
617
618
619
620
621
622
623
624
625
626

			# Loop with stock items
			foreach my $itemSerial (keys %{$res->{$itemCode}}) {
				my $stockItem = $res->{$itemCode}->{$itemSerial};

				$entry->{'TotalQuantity'}->badd($stockItem->{'Quantity'});
				$entry->{'TotalValue'}->badd($stockItem->{'Value'});

				$totalValue->badd($stockItem->{'Value'});

				# If its zero, we don't want it
				if (!$stockItem->{'Quantity'}->is_zero() && !$stockItem->{'Value'}->is_zero()) {
					my $sentry;
627
628
					$sentry->{'TotalQuantity'} = sprintf('%.4f',$stockItem->{'Quantity'}->bstr());
					$sentry->{'TotalValue'} = sprintf('%.4f',$stockItem->{'Value'}->bstr());
629
					$sentry->{'GLTransactions'} = $stockItem->{'GLTransactions'};
Nigel Kukard's avatar
Nigel Kukard committed
630
631
632
633
634
635
636
637
638
639
640
641
					$resdata->{'StockItemBalances'}->{$itemCode}->{$itemSerial} = $sentry;
				}
			}
			# If its zero, we don't need it
			if (!$entry->{'TotalQuantity'}->is_zero() && !$entry->{'TotalValue'}->is_zero()) {
				$entry->{'TotalQuantity'} = sprintf('%.4f',$entry->{'TotalQuantity'}->bstr());
				$entry->{'TotalValue'} = sprintf('%.2f',$entry->{'TotalValue'}->bstr());
				$resdata->{'StockBalances'}->{$itemCode} = $entry;
			}
		}

		$resdata->{'TotalValue'} = sprintf('%.2f',$totalValue->bstr());
642

Nigel Kukard's avatar
Nigel Kukard committed
643
644
645
646
		return $resdata;
	}


Nigel Kukard's avatar
Nigel Kukard committed
647
648
649
650
651
652
653
654
	# Reporting API function to create a variable
	sub api_variable_new
	{
		my ($item,$type) = @_;

		# Check what type we are
		if (lc($type) eq "float") {
			$variables{$item}{'type'} = "float";
Nigel Kukard's avatar
Nigel Kukard committed
655
			$variables{$item}{'value'} = new Math::BigFloat(0);
Nigel Kukard's avatar
Nigel Kukard committed
656
657
			$variables{$item}{'value'}->precision(-2);
		}
658
		# We shouldn't return results
Nigel Kukard's avatar
Nigel Kukard committed
659
660
		return undef;
	}
661

Nigel Kukard's avatar
Nigel Kukard committed
662
663
664
665
666
667
668
669
670
671
672

	# Reporting API function to add something to a variable
	sub api_variable_add
	{
		my ($item,$value) = @_;


		# If we have a balance, add to it
		if (defined($variables{$item})) {
			# If its a float
			if ($variables{$item}{'type'} eq "float") {
Nigel Kukard's avatar
Nigel Kukard committed
673
674
675
676
				# Cannot add an undefined value
				if (defined($value) && $value ne "") {
					$variables{$item}{'value'}->badd($value);
				}
Nigel Kukard's avatar
Nigel Kukard committed
677
			} else {
678
				wiaflos::server::core::templating::abort('api_variable_add','Variable type not supported: "'.defined($value) ? $value : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
679
680
			}
		} else {
681
			wiaflos::server::core::templating::abort('api_variable_add','Variable not defined: "'.defined($item) ? $item : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
682
		}
683
684

		# We shouldn't return results
Nigel Kukard's avatar
Nigel Kukard committed
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
		return undef;
	}


	# Reporting API function to subtract something to a variable
	sub api_variable_subtract
	{
		my ($item,$value) = @_;


		# If we have a balance, subtract from it
		if (defined($variables{$item})) {
			# If its a float
			if ($variables{$item}{'type'} eq "float") {
				$variables{$item}{'value'}->bsub($value);
			} else {
701
				wiaflos::server::core::templating::abort('api_variable_subtract','Variable type not supported: "'.defined($value) ? $value : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
702
703
			}
		} else {
704
			wiaflos::server::core::templating::abort('api_variable_subtract','Variable not defined: "'.defined($item) ? $item : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
705
		}
706
		# We shouldn't return results
Nigel Kukard's avatar
Nigel Kukard committed
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
		return undef;
	}


	# Reporting API function to get variable
	sub api_variable_get
	{
		my $item = shift;


		my $res;

		# If we have a balance, return it
		if (defined($variables{$item})) {
			# If its a float
			if ($variables{$item}{'type'} eq "float") {
				$res = sprintf('%.2f',$variables{$item}{'value'}->bstr());
			} else {
725
				wiaflos::server::core::templating::abort('api_variable_get','Variable type not supported: "'.
Nigel Kukard's avatar
Nigel Kukard committed
726
727
728
					defined($variables{$item}{'type'}) ? $variables{$item}{'type'} : ''.'"');
			}
		} else {
729
			wiaflos::server::core::templating::abort('api_variable_get','Variable not defined: "'.defined($item) ? $item : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
		}
		return $res;
	}


	# Reporting API function to get raw variable value
	sub api_variable_getraw
	{
		my $item = shift;


		my $res;

		# If we have a balance, return it
		if (defined($variables{$item})) {
			$res = $variables{$item}{'value'};
		} else {
747
			wiaflos::server::core::templating::abort('api_variable_get','Variable not defined: "'.defined($item) ? $item : ''.'"');
Nigel Kukard's avatar
Nigel Kukard committed
748
749
750
751
752
		}

		return $res;
	}

753

754
755
	# Reporting API function to logical OR all variables
	sub api_bitwise_or
Nigel Kukard's avatar
Nigel Kukard committed
756
757
758
	{
		my @vars = @_;

759
		# OR up all the values
Nigel Kukard's avatar
Nigel Kukard committed
760
761
762
763
764
765
766
767
		my $res = 0;
		foreach my $val (@vars) {
			$res |= $val;
		}

		return $res;
	}

768

Nigel Kukard's avatar
Nigel Kukard committed
769
770
771
772
773
774
775
776
777
	# Dump contents of parameters on server
	sub api_format_amount
	{
		my ($pamount,$options) = @_;

		my $prefix = "";
		my $suffix = "";

		# Pull in value
Nigel Kukard's avatar
Nigel Kukard committed
778
		my $amount = new Math::BigFloat(0);
Nigel Kukard's avatar
Nigel Kukard committed
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
		$amount->precision(-2);
		$amount->badd($pamount);

		# Check options
		if (($options & API_FMT_REVERSE) == API_FMT_REVERSE) {
			$amount->bmul(-1);
		}

		if (($options & API_FMT_NEGBRACKET) == API_FMT_NEGBRACKET) {
			# Check if neg, if it is, use brackets
			if ($amount->is_neg()) {
				$prefix = "(";
				$suffix = ")";
				$amount->babs();
			}
		}

		return $prefix . sprintf('%.2f',$amount->bstr()) . $suffix;
	}

	# Dump contents of parameters on server
	sub api_dump
	{
		use Data::Dumper;
		print(STDERR Dumper(\@_));
	}

806
807
808
809
810
811
812
813
814
815
816
817
818
	# Verify Template
	if (!defined($detail->{'Template'}) || $detail->{'Template'} eq "") {
		setError("Template was not provided");
		return ERR_PARAM;
	}

	# Verify SendTo
	if (!defined($detail->{'SendTo'}) || $detail->{'SendTo'} eq "") {
		setError("SendTo was not provided");
		return ERR_PARAM;
	}

	# Pull in config
819
	my $config = wiaflos::server::core::config::getConfig();
820
821

	# Check if subject was overridden
822
	my $subject = (defined($detail->{'Subject'}) && $detail->{'Subject'} ne "") ? $detail->{'Subject'} : "Report";
823
824

	# Build array of stuff we can use
825
	my $vars = {
826
827
		'WiaflosString' => $GENSTRING,

828
829
		# API
		'api_account_balances' => \&api_account_balances,
830
		'API_RPT_BALANCE_BF' => REPORT_BALANCE_BF,
Nigel Kukard's avatar
Nigel Kukard committed
831
		'API_RPT_INCLUDE_YEAREND' => REPORT_INCLUDE_YEAREND,
832
833
		'API_RPT_INCLUDE_AUDIT' => REPORT_INCLUDE_AUDIT,
		'API_RPT_INCLUDE_AUDIT_YEAREND' => REPORT_INCLUDE_AUDIT_YEAREND,
834

835
		'api_account_entries' => \&api_account_entries,
836

Nigel Kukard's avatar
Nigel Kukard committed
837
		'api_inventory_stock_balances' => \&api_inventory_stock_balances,
838
		'api_gl_transactions' => \&api_gl_transactions,
839

Nigel Kukard's avatar
Nigel Kukard committed
840
841
842
843
844
		'api_variable_new' => \&api_variable_new,
		'api_variable_add' => \&api_variable_add,
		'api_variable_subtract' => \&api_variable_subtract,
		'api_variable_get' => \&api_variable_get,
		'api_variable_getraw' => \&api_variable_getraw,
845

846
		'api_bitwise_or' => \&api_bitwise_or,
847

Nigel Kukard's avatar
Nigel Kukard committed
848
849
850
		'api_format_amount' => \&api_format_amount,
		'API_FMT_REVERSE' => API_FMT_REVERSE,
		'API_FMT_NEGBRACKET' => API_FMT_NEGBRACKET,
851

Nigel Kukard's avatar
Nigel Kukard committed
852
853
		'api_dump' => \&api_dump,

854
855
856
		# Client
		'StartDate' => defined($detail->{'StartDate'}) ? $detail->{'StartDate'} : "-",
		'EndDate' => defined($detail->{'EndDate'}) ? $detail->{'EndDate'} : "CURRENT",
857

858
859
	};

860
861
	# Should this be backgrounded?
	my $background = $detail->{'Background'};
862
	if ($background) {
863
		my $job = wiaflos::server::core::jobs::createJob();
864
865
866
867
868
869
870
871
		if ($job < RES_OK) {
			setError(wiaflos::jobs::Error());
			return $job;
		} elsif ($job > RES_OK) {
			# Parent job
			return RES_OK;
		}
	}
872

873
874
875
876
877
878
879
	# Set template to use
	my $template = $detail->{'Template'};

	# Check where report must go
	if ($detail->{'SendTo'} =~ /^file:(\S+)/i) {
		my $filename = $1;

880
		wiaflos::server::core::jobs::setStatus("Loading report template '$template'...");
881
882
883
		# Load template
		my $res = loadTemplate($template,$vars,$filename);
		if (!$res) {
884
			if (!$background) {
885
				setError("Failed to load template '$template': ".wiaflos::server::core::templating::Error());
886
887
				return ERR_SRVTEMPLATE;
			} else {
888
				wiaflos::server::core::jobs::setStatus("Failed to load template '$template': ".wiaflos::server::core::templating::Error());
889
890
				exit 0;
			}
891
892
893
894
895
896
897
898
899
		}

	# Write out using email
	} elsif ($detail->{'SendTo'} =~ /^email(?:\:(\S+))?/i) {
		# Pull in email address user specified
		my $emailAddy = $1;

		# Check if we have a email addy
		if (!defined($emailAddy) || $emailAddy eq "") {
900
901
902
903
			if (!$background) {
				setError("No email address defined to send reports to");
				return ERR_PARAM;
			} else {
904
				wiaflos::server::core::jobs::setStatus("No email address defined to send reports to");
905
906
				exit 0;
			}
907
908
909
910
911
912
913
914
915
916
917
918
		}

		# Report filename
		(my $reportFilename = "report.html") =~ s,/,-,g;
		# Report signature filename
		my $rprtSignFilename = $reportFilename . ".asc";


		# If we must, pull in email body
		my $message_template = $config->{'reports'}{'email_message_template'};
		my $emailBody = "";
		if (defined($message_template) && $message_template ne "") {
919
			wiaflos::server::core::jobs::setStatus("Loading message template '$message_template'...");
920
921
922
923
924
925
926
927
			# Variables for our template
			my $vars2 = {
				'ReportFilename' => $reportFilename,
				'ReportSignatureFilename' => $rprtSignFilename,
			};
			# Load template
			my $res = loadTemplate($message_template,$vars2,\$emailBody);
			if (!$res) {
928
				if (!$background) {
929
					setError("Failed to load template '$message_template': ".wiaflos::server::core::templating::Error());
930
931
					return ERR_SRVTEMPLATE;
				} else {
932
					wiaflos::server::core::jobs::setStatus("Failed to load template '$message_template': ".wiaflos::server::core::templating::Error());
933
934
					exit 0;
				}
935
936
937
938
939
			}

			$emailBody =~ s/(?<!\r)\n/\r\n/sg; # Sanitize eol for crypt-gpg
		}

940
		wiaflos::server::core::jobs::setStatus("Loading report template '$template'...");
941

942
943
944
945
		# This is our entire report
		my $reportData = "";
		my $res = loadTemplate($template,$vars,\$reportData);
		if (!$res) {
946
			if (!$background) {
947
				setError("Failed to load template '$template': ".wiaflos::server::core::templating::Error());
948
949
				return ERR_SRVTEMPLATE;
			} else {
950
				wiaflos::server::core::jobs::setStatus("Failed to load template '$template': ".wiaflos::server::core::templating::Error());
951
952
				exit 0;
			}
953
954
955
956
957
958
959
		}
		$reportData =~ s/(?<!\r)\n/\r\n/sg; # Sanitize eol, needed to fix bug in crypt-gpg where it mangles \n

		# See if we must use GPG
		my $use_gpg_key = $config->{'reports'}{'use_gpg_key'};
		my $sign;
		if (defined($use_gpg_key) && $use_gpg_key ne "") {
960
			wiaflos::server::core::jobs::setStatus("Signing report...");
961
962
963
964
965
966
967
968
			# Setup GnuPG
			my $gpg = new Crypt::GPG;
			$gpg->gpgbin('/usr/bin/gpg');
			$gpg->secretkey($use_gpg_key);
			$gpg->armor(1);
			# Sign report
			$sign = $gpg->sign($reportData);
			if (!defined($sign)) {
969
970
971
972
				if (!$background) {
					setError("Failed to sign report");
					return ERR_SRVEXEC;
				} else {
973
					wiaflos::server::core::jobs::setStatus("Failed to sign report");
974
975
					exit 0;
				}
976
977
978
			}
		}

979
		wiaflos::server::core::jobs::setStatus("Composing mail...");
980
981
982
983
984
985
986
987
988
989
990
991
		# Create message
		my $msg = MIME::Lite->new(
				From	=> $config->{'reports'}{'email_from'},
				To		=> $emailAddy,
				Bcc		=> $config->{'reports'}{'email_bcc'},
				Subject	=> $subject,
				Type	=> 'multipart/mixed'
		);

		# Attach body
		$msg->attach(
				Type	=> 'TEXT',
992
				Encoding 	=> '8bit',
993
994
995
996
997
998
				Data	=> $emailBody,
		);

		# Attach report
		$msg->attach(
				Type	=> 'text/html',
999
				Encoding	=> 'base64',
1000
				Data	=> $reportData,