1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8// this script may only be included - so its better to die if called directly. 9if (strpos($_SERVER["SCRIPT_NAME"], basename(__FILE__)) !== false) { 10 header("location: index.php"); 11 exit; 12} 13 14$logslib = TikiLib::lib('logs'); 15 16/** 17 * Basic functions used by the accounting feature 18 * 19 * <p>This file contains all functions used by more than one file from the ccsg_accounting feature. 20 * This feature is a simple accounting/bookkeeping function.</p> 21 * 22 * @package accounting 23 * @author Joern Ott <white@ott-service.de> 24 * @version 1.2 25 * @date 2010-11-16 26 * @copyright LGPL 27 */ 28 29class AccountingLib extends LogsLib 30{ 31 /** 32 * 33 * Storing the book data if already requested once, this may save us a few queries 34 * @var array $_book array with the books structure 35 */ 36 private $_book = ''; 37 38 /** 39 * Lists all books available to a user 40 * @param string $order sorting order 41 * @return array list of books (complete table structure) 42 */ 43 function listBooks($order = 'bookId ASC') 44 { 45 $query = "SELECT * FROM tiki_acct_book ORDER BY $order"; 46 return $this->fetchAll($query, []); 47 } 48 49 /** 50 * 51 * Creates a new book and gives full permissions to the creator 52 * @param string $bookName descriptive name of the book 53 * @param string $bookStartDate first permitted date for the book 54 * @param string $bookEndDate last permitted date for the book 55 * @param string $bookCurrency up to 3 letter cuurency code 56 * @param int $bookCurrencyPos where should the currency symbol appear -1=before, 1=after 57 * @param int $bookDecimals number of decimal points 58 * @param string $bookDecPoint separator for the decimal point 59 * @param string $bookThousand separator for the thousands 60 * @param string $exportSeparator separator between fields when exporting CSV 61 * @param string $exportEOL end of line definition, either CR, LF or CRLF 62 * @param string $exportQuote Quote character to enclose strings in CSV 63 * @param string $bookClosed 'y' if the book is closed (no more changes), 'n' otherwise 64 * @param string $bookAutoTax 65 * @return int/string bookId on success, error message otherwise 66 */ 67 function createBook($bookName, $bookClosed = 'n', $bookStartDate, $bookEndDate, $bookCurrency, $bookCurrencyPos = -1, $bookDecimals, $bookDecPoint, $bookThousand, $exportSeparator, $exportEOL, $exportQuote, $bookAutoTax = 'y' 68 ) 69 { 70 global $user; 71 $userlib = TikiLib::lib('user'); 72 if (strlen($bookName) == 0) { 73 return "The book must have a name"; 74 } 75 if (strtotime($bookStartDate) === false) { 76 return "Invalid start date"; 77 } 78 if (strtotime($bookEndDate) === false) { 79 return "Invalid end date"; 80 } 81 $query = "INSERT INTO `tiki_acct_book`" . 82 " (`bookName`, `bookClosed`, `bookStartDate`, `bookEndDate`," . 83 " `bookCurrency`, `bookCurrencyPos`, `bookDecimals`, `bookDecPoint`, `bookThousand`," . 84 " `exportSeparator`, `exportEOL`, `exportQuote`, `bookAutoTax`)" . 85 " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; 86 87 $res = $this->query( 88 $query, 89 [ 90 $bookName, 91 $bookClosed, 92 $bookStartDate, 93 $bookEndDate, 94 $bookCurrency, 95 $bookCurrencyPos, 96 $bookDecimals, 97 $bookDecPoint, 98 $bookThousand, 99 $exportSeparator, 100 $exportEOL, 101 $exportQuote, 102 $bookAutoTax 103 ] 104 ); 105 $bookId = $this->lastInsertId(); 106 $this->createTax($bookId, tra('No automated tax'), 0, 'n'); 107 $groupId = $bookId; 108 109 do { 110 //make sure we don't have that group already 111 $groupname = "accounting_book_$groupId"; 112 $groupexists = $userlib->group_exists($groupname); 113 if ($groupexists) { 114 $groupId++; 115 } 116 } while ($groupexists); 117 118 if ($groupId != $bookId) { 119 $query = "UPDATE `tiki_acct_book` SET `bookId`=? WHERE `bookId`=?"; 120 $res = $this->query($query, [$groupId, $bookId]); 121 $bookId = $groupId; 122 } 123 124 $userlib->add_group($groupname); 125 $userlib->assign_user_to_group($user, $groupname); 126 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_view'); 127 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_book'); 128 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_manage_accounts'); 129 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_book_stack'); 130 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_book_import'); 131 $userlib->assign_object_permission($groupname, $bookId, 'accounting book', 'tiki_p_acct_manage_template'); 132 return $bookId; 133 } 134 135 /** 136 * 137 * Returns the details for a book with a given bookId 138 * @param int $bookId Id of the book to retrieve the data for 139 * @return array Array with book details 140 */ 141 function getBook($bookId) 142 { 143 if (! is_array($this->_book) or $this->_book['bookId'] != $bookId) { 144 $query = "SELECT * FROM `tiki_acct_book` WHERE `bookId`=?"; 145 $res = $this->query($query, [$bookId]); 146 $this->_book = $res->fetchRow(); 147 } 148 return $this->_book; 149 } 150 151 /** 152 * 153 * This function sets a books status to closed, so transactions can no longer be used 154 * @param int $bookId id of the book to close 155 * @return bool true on success 156 */ 157 function closeBook($bookId) 158 { 159 $book = $this->getBook($bookId); 160 if ($book['bookClosed'] == 'y') { 161 return false; 162 } 163 $query = "UPDATE `tiki_acct_book` SET `bookClosed`='y' WHERE `bookId`=?"; 164 $res = $this->query($query, [$bookId]); 165 if ($res === false) { 166 return false; 167 } 168 return true; 169 } 170 171 /** 172 * Returns the complete journal for a given account, if none is provided, the whole journal will be fetched 173 * 174 * @param int $bookId id of the current book 175 * @param int $accountId account for which we should display the journal, defaults to '%' (all accounts) 176 * @param string $order sorting order 177 * @param int $limit max number of records to fetch, defaults to 0 = all 178 * @return array|bool journal with all posts, false on errors 179 */ 180 function getJournal($bookId, $accountId = '%', $order = '`journalId` ASC', $limit = 0) 181 { 182 $journal = []; 183 184 if ($limit != 0) { 185 if ($limit < 0) { 186 $order = str_replace("ASC", "DESC", $order); 187 } 188 $order .= " LIMIT " . abs($limit); 189 } 190 191 if ($accountId == '%') { 192 $query = "SELECT `journalId`, `journalDate`, `journalDescription`, `journalCancelled`" . 193 " FROM `tiki_acct_journal`" . 194 " WHERE `journalBookId`=?" . 195 " ORDER BY $order"; 196 197 $res = $this->query($query, [$bookId]); 198 } else { 199 $query = "SELECT `journalId`, `journalDate`, `journalDescription`, `journalCancelled`" . 200 " FROM `tiki_acct_journal` INNER JOIN `tiki_acct_item`" . 201 " ON (`tiki_acct_journal`.`journalBookId`=`tiki_acct_item`.`itemBookId` AND" . 202 " `tiki_acct_journal`.`journalId`=`tiki_acct_item`.`itemJournalId`)" . 203 " WHERE `journalBookId`=? AND `itemAccountId` LIKE ?" . 204 " GROUP BY `journalId`, `journalDate`, `journalDescription`, `journalCancelled`" . 205 " ORDER BY $order"; 206 207 $res = $this->query($query, [$bookId, $accountId]); 208 } 209 if ($res === false) { 210 return false; 211 } 212 while ($row = $res->fetchRow()) { 213 $query = "SELECT * FROM `tiki_acct_item` WHERE `itemBookId`=? AND `itemJournalId`=? AND `itemType`=? ORDER BY `itemAccountId` ASC"; 214 $row['debit'] = $this->fetchAll($query, [$bookId, $row['journalId'], -1]); 215 $row['debitcount'] = count($row['debit']); 216 $row['credit'] = $this->fetchAll($query, [$bookId, $row['journalId'], 1]); 217 $row['creditcount'] = count($row['credit']); 218 $row['maxcount'] = max($row['creditcount'], $row['debitcount']); 219 $journal[] = $row; 220 } 221 return $journal; 222 } 223 224 /** 225 * Returns the totals for a given book and account 226 * 227 * @param int $bookId id of the current book 228 * @param int $accountId account for which we should fetch the totals, defaults to '%' (all accounts) 229 * @return array array with three elements debit, credit and the total (credit-debit) 230 */ 231 function getJournalTotals($bookId, $accountId = '%') 232 { 233 $journal = []; 234 235 $query = "SELECT `itemAccountId`, SUM(`itemAmount`*IF(`itemType`<0,1,0)) AS debit," . 236 " sum(`itemAmount`*IF(`itemType`>0,1,0)) AS credit" . 237 " FROM `tiki_acct_journal` INNER JOIN `tiki_acct_item`" . 238 " ON (`tiki_acct_journal`.`journalBookId`=`tiki_acct_item`.`itemBookId`" . 239 " AND `tiki_acct_journal`.`journalId`=`tiki_acct_item`.`itemJournalId`)" . 240 " WHERE `journalBookId`=? AND `itemAccountId` LIKE ?" . 241 " GROUP BY `itemAccountId`"; 242 243 $res = $this->query($query, [$bookId, $accountId]); 244 $totals = $res->fetchRow(); 245 $totals['total'] = $totals['credit'] - $totals['debit']; 246 return $totals; 247 } 248 249 /** 250 * Returns a list of accounts as defined in table tiki_acct_account 251 * 252 * @param int $bookId id of the book to retrieve the accounts for 253 * @param string $order order of items, defaults to accountId 254 * @param boolean $all true = fetch all accounts, false = fetch only unlocked accounts 255 * @return array list of accounts 256 */ 257 function getAccounts($bookId, $order = "`accountId` ASC", $all = false) 258 { 259 $query = 'SELECT * FROM `tiki_acct_account` WHERE `accountBookId`=? ' . 260 ($all ? '' : 'AND `accountLocked`=0 ') . 261 " ORDER BY $order"; 262 263 return $this->fetchAll($query, [$bookId]); 264 } //getAccounts 265 266 /** 267 * Returns an extended list of accounts with totals 268 * 269 * @param int $bookId id of the book to fetch the account list for 270 * @param bool $all true = fetch all accounts or false = only unlocked accounts, defaults to false 271 * @return array list of accounts 272 */ 273 function getExtendedAccounts($bookId, $all = false) 274 { 275 $allcond = $all ? '' : ' AND accountLocked=0'; 276 $query = "SELECT accountBookId, accountId, accountName, accountNotes, accountBudget, accountLocked, " . 277 " SUM(itemAmount*IF(itemType<0,1,0)) AS debit, SUM(itemAmount*IF(itemType>0,1,0)) AS credit" . 278 " FROM tiki_acct_account" . 279 " LEFT JOIN tiki_acct_journal ON tiki_acct_account.accountBookId=tiki_acct_journal.journalBookId" . 280 " LEFT JOIN tiki_acct_item ON tiki_acct_journal.journalId=tiki_acct_item.itemJournalId" . 281 " AND tiki_acct_account.accountId=tiki_acct_item.itemAccountId" . 282 " WHERE tiki_acct_account.accountBookId=? $allcond" . 283 " GROUP BY accountId, accountName, accountNotes, accountBudget, accountLocked, accountBookId"; 284 285 return $this->fetchAll($query, [$bookId]); 286 }//getExtendedAccounts 287 288 /** 289 * Returns an array with all data from the account 290 * 291 * @param int $bookId id of the current book 292 * @param int $accountId account id to retrieve 293 * @param boolean $checkChangeable perform check, if the account is changeable 294 * @return array account data or false on error 295 */ 296 function getAccount($bookId, $accountId, $checkChangeable = true) 297 { 298 $query = "SELECT * FROM `tiki_acct_account` WHERE `accountbookId`=? AND `accountId`=?"; 299 $res = $this->query($query, [$bookId, $accountId]); 300 $account = $res->fetchRow(); 301 if ($checkChangeable) { 302 $account['changeable'] = $this->accountChangeable($bookId, $accountId); 303 } 304 return $account; 305 } //getAccount 306 307 /** 308 * Checks if this accountId can be changed or the account can be deleted. 309 * This can only be done, if the account has not been used -> no posts exist for the account 310 * 311 * @param int $bookId id of the current book 312 * @param int $accountId account id to check 313 * @return boolean true, if the account can be changed/deleted 314 */ 315 function accountChangeable($bookId, $accountId) 316 { 317 $book = $this->getBook($bookId); 318 if ($book['bookClosed'] == 'y') { 319 return false; 320 } 321 $query = "SELECT Count(`itemAccountId`) AS posts" . 322 " FROM `tiki_acct_journal`" . 323 " INNER JOIN `tiki_acct_item` ON `tiki_acct_journal`.`journalId`=`tiki_acct_item`.`itemJournalId`" . 324 " WHERE `journalBookId`=? and `itemAccountId`=?"; 325 326 $res = $this->query($query, [$bookId, $accountId]); 327 $posts = $res->fetchRow(); 328 return ($posts['posts'] == 0); 329 } //accountChangeable 330 331 /** 332 * Creates an account with the given information 333 * 334 * @param int $bookId id of the current book 335 * @param int $accountId id of the account to create 336 * @param string $accountName name of the account to create 337 * @param string $accountNotes notes for this account 338 * @param float $accountBudget planned budget for the account 339 * @param boolean $accountLocked can this account be used, 0=unlocked, 1=locked 340 * @param int $accountTax taxId for tax automation 341 * @return array|bool list of errors or true on success 342 */ 343 function createAccount( 344 $bookId, 345 $accountId, 346 $accountName, 347 $accountNotes, 348 $accountBudget, 349 $accountLocked, 350 $accountTax = 0 351 ) { 352 353 $book = $this->getBook($bookId); 354 if ($book['bookClosed'] == 'y') { 355 $errors = [tra("This book has been closed. You can't create new accounts.")]; 356 return $errors; 357 } 358 359 $errors = $this->validateId('accountId', $accountId, 'tiki_acct_account', false, 'accountBookId', $bookId); 360 361 if ($accountName == '') { 362 $errors[] = tra('Account name must not be empty.'); 363 } 364 $cleanbudget = $this->cleanupAmount($bookId, $accountBudget); 365 366 if ($cleanbudget === '') { 367 $errors[] = tra('Budget is not a valid amount: ') . $accountBudget; 368 } 369 370 if ($accountLocked != 0 and $accountLocked != 1) { 371 $errors[] = tra('Locked must be either 0 or 1.'); 372 } 373 374 if ($accountTax != 0) { 375 $errors = array_merge($errors, $this->validateId('taxId', $accountTax, 'tiki_acct_tax', true, 'taxBookId', $bookId)); 376 } 377 378 if (! empty($errors)) { 379 return $errors; 380 } 381 382 $query = 'INSERT INTO tiki_acct_account' . 383 ' SET accountBookId=?, accountId=?, accountName=?,' . 384 ' accountNotes=?, accountBudget=?, accountLocked=?, accountTax=?'; 385 386 $res = $this->query( 387 $query, 388 [ 389 $bookId, 390 $accountId, 391 $accountName, 392 $accountNotes, 393 $cleanbudget, 394 $accountLocked, 395 $accountTax 396 ] 397 ); 398 if ($res === false) { 399 $errors[] = tra('Error creating account') & " $accountId: " . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 400 return $errors; 401 } 402 return true; 403 } //createAccount 404 405 /** 406 * Unlocks or locks an account which means it can not be used accidentally for booking 407 * 408 * @param int $bookId current book 409 * @param int $accountId account to lock 410 * @return bool true on success 411 */ 412 function changeAccountLock($bookId, $accountId) 413 { 414 $book = $this->getBook($bookId); 415 if ($book['bookClosed'] == 'y') { 416 return false; 417 } 418 $query = "UPDATE `tiki_acct_account` SET `accountLocked` = NOT `accountLocked` 419 WHERE `accountBookId`=? AND `accountId`=?"; 420 $res = $this->query($query, [$bookId, $accountId]); 421 if ($res === false) { 422 return false; 423 } 424 return true; 425 } //changeAccountLock 426 427 /** 428 * Updates an account with the given information 429 * 430 * @param int $bookId id of the current book 431 * @param int $accountId original id of the account 432 * @param int $newAccountId new id of the account (only if the account is changeable) 433 * @param string $accountName name of the account 434 * @param string $accountNotes notes for the account 435 * @param float $accountBudget planned yearly budget for the account 436 * @param boolean $accountLocked can this account be used 0=unlocked, 1=locked 437 * @param int $accountTax id of the auto tax type, defaults to 0 438 * @return array|bool list of errors, true on success 439 */ 440 function updateAccount( 441 $bookId, 442 $accountId, 443 $newAccountId, 444 $accountName, 445 $accountNotes, 446 $accountBudget, 447 $accountLocked, 448 $accountTax = 0 449 ) { 450 451 $book = $this->getBook($bookId); 452 if ($book['bookClosed'] == 'y') { 453 $errors = [tra("This book has been closed. You can't modify the account.")]; 454 return $errors; 455 } 456 457 $errors = $this->validateId('accountId', $newAccountId, 'tiki_acct_account', true, 'accountBookId', $bookId); 458 if ($accountId != $newAccountId) { 459 if (! $this->accountChangeable($bookId, $accountId)) { 460 $errors[] = tra('AccountId %0 is already in use and must not be changed. Please disable it if it is no longer needed.', $args = [$accountId]); 461 } 462 } 463 464 if ($accountName === '') { 465 $errors[] = tra('Account name must not be empty.'); 466 } 467 468 $cleanbudget = $this->cleanupAmount($bookId, $accountBudget); 469 470 if ($cleanbudget === '') { 471 $errors[] = tra('Budget is not a valid amount: ') . $cleanbudget; 472 } 473 474 if ($accountLocked != 0 and $accountLocked != 1) { 475 $errors[] = tra('Locked must be either 0 or 1.'); 476 } 477 478 if ($accountTax != 0) { 479 $errors = array_merge($errors, $this->validateId('taxId', $accountTax, 'tiki_acct_tax', true, 'taxBookId', $bookId)); 480 } 481 482 if (count($errors) != 0) { 483 return $errors; 484 } 485 486 $query = "UPDATE tiki_acct_account SET accountId=?, accountName=?, 487 accountNotes=?, accountBudget=?, accountLocked=?, accountTax=? 488 WHERE accountBookId=? AND accountId=?"; 489 490 $res = $this->query( 491 $query, 492 [ 493 $newAccountId, 494 $accountName, 495 $accountNotes, 496 $cleanbudget, 497 $accountLocked, 498 $accountTax, 499 $bookId, 500 $accountId 501 ] 502 ); 503 504 if ($res === false) { 505 $errors[] = tra('Error updating account') & " $accountId: " . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 506 return $errors; 507 } 508 509 return true; 510 } //updateAccount 511 512 /** 513 * Delete an account (if deleteable) 514 * 515 * @param int $bookId id of the current book 516 * @param int $accountId account id to delete 517 * @param bool $checkChangeable check, if the account is unused and can be deleted 518 * @return array|bool array with errors or true, if deletion was successful 519 */ 520 function deleteAccount($bookId, $accountId, $checkChangeable = true) 521 { 522 $book = $this->getBook($bookId); 523 if ($book['bookClosed'] == 'y') { 524 return [tra("This book has been closed. You can't delete the account.")]; 525 } 526 527 if (! $this->accountChangeable($bookId, $accountId)) { 528 return [tra('Account is already in use and must not be deleted. Please disable it, if it is no longer needed.')]; 529 } 530 531 $query = "DELETE FROM `tiki_acct_account` WHERE `accountBookId`=? AND `accountId`=?"; 532 $res = $this->query($query, [$bookId, $accountId]); 533 return true; 534 } //deleteAccount 535 536 /** 537 * 538 * Do a manual rollback, if the creation of a complete booking fails. 539 * This is a workaround for missing transaction support 540 * @param int $bookId id of the current book 541 * @param int $journalId id of the entry to roll back 542 * @return string Text messages stating the success/failure of the rollback 543 */ 544 function manualRollback($bookId, $journalId) 545 { 546 $errors = []; 547 $query = "DELETE FROM `tiki_acct_item` WHERE `itemBookId`=? AND `itemJournalId`=?"; 548 $res = $this->query($query, [$bookId, $journalId]); 549 $rollback = ($res !== false); 550 $query = "DELETE FROM `tiki_acct_journal` WHERE `journalBookId`=? AND `journalId`=?"; 551 $res = $this->query($query, [$bookId, $journalId]); 552 $rollback = $rollback and ($res !== false); 553 554 if (! $rollback) { 555 return tra('Rollback failed, inconsistent database: Cleanup needed for journalId %0 in book %1', [$journalId, $bookId]); 556 } else { 557 return tra('successfully rolled back #') . " $journalId"; 558 } 559 } //manualRollback 560 561 /** 562 * Checks if the book date is within the books limits 563 * 564 * @param array $book book array 565 * @param DateTime $Date 566 * @retun array|bool 567 */ 568 function checkBookDates($book, $Date) 569 { 570 $StartDate = new DateTime($book['bookStartDate']); 571 if ($Date < $StartDate) { 572 return [tra("The date of the transaction is before the start date of this book.")]; 573 } 574 575 $EndDate = new DateTime($book['bookEndDate']); 576 if ($Date > $EndDate) { 577 return [tra("The date of the transaction is after the end date of this book.")]; 578 } 579 580 return true; 581 } 582 583 /** 584 * books a simple transaction 585 * 586 * @param int $bookId id of the current book 587 * @param string $journalDate date of the transaction 588 * @param string $journalDescription description of this transaction 589 * @param int $debitAccount account to debit 590 * @param int $creditAccount account to credit 591 * @param double $amount amount to transfer between the accounts 592 * @param string $debitText text for the debit post, defaults to an empty string 593 * @param string $creditText text for the credit post, defaults to an empty string 594 * @return int|array list of errors or journalId on success 595 */ 596 function simpleBook( 597 $bookId, 598 $journalDate, 599 $journalDescription, 600 $debitAccount, 601 $creditAccount, 602 $amount, 603 $debitText = '', 604 $creditText = '' 605 ) { 606 607 608 $book = $this->getBook($bookId); 609 610 if ($book['bookClosed'] == 'y') { 611 return [tra("This book has been closed. Bookings can no longer be made in it.")]; 612 } try { 613 $date = new DateTime($journalDate); 614 } catch (Exception $e) { 615 return [tra("Invalid booking date.")]; 616 } 617 618 $errors = $this->checkBookDates($book, $date); 619 620 if (is_array($errors)) { 621 return $errors; 622 } 623 624 $errors = []; 625 626 $query = "INSERT INTO `tiki_acct_journal` (`journalBookId`, `journalDate`, `journalDescription`, 627 `journalCancelled`, `journalTs`) 628 VALUES (?,?,?,0,NOW())"; 629 $res = $this->query($query, [$bookId, $date->toString('Y-M-d'), $journalDescription]); 630 631 if ($res === false) { 632 $errors[] = tra('Booking error creating journal entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 633 $this->rollback(); 634 return $errors; 635 } 636 637 $journalId = $this->lastInsertId(); 638 639 $query = "INSERT INTO `tiki_acct_item` (`itemBookId`, `itemJournalId`, `itemAccountId`, `itemType`, 640 `itemAmount`, `itemText`, `itemTs`) 641 VALUES (?, ?, ?, ?, ?, ?, NOW())"; 642 643 $res = $this->query($query, [$bookId, $journalId, $debitAccount, -1, $amount, $debitText]); 644 645 if ($res === false) { 646 $errors[] = tra('Booking error creating debit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 647 $errors[] = $this->manualRollback($bookId, $journalId); 648 return $errors; 649 } 650 651 $res = $this->query($query, [$bookId, $journalId, $creditAccount, 1, $amount, $creditText]); 652 653 if ($res === false) { 654 $errors[] = tra('Booking error creating credit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 655 $errors[] = $this->manualRollback($bookId, $journalId); 656 return $errors; 657 } 658 // everything ok 659 return $journalId; 660 }// simplebook 661 662 /** 663 * books a complex transaction with multiple accounts on one side 664 * 665 * @param int $bookId id of the current book 666 * @param DateTime $journalDate date of the transaction 667 * @param string $journalDescription description of this transaction 668 * @param mixed $debitAccount account(s) to debit 669 * @param mixed $creditAccount account(s) to credit 670 * @param mixed $debitAmount amount(s) on debit side 671 * @param mixed $creditAmount amount(s) on credit side 672 * @param mixed $debitText text(s) for the debit post, defaults to an empty string 673 * @param mixed $creditText text(s) for the credit post, defaults to an empty string 674 * 675 * @return int|array journalID or list of errors 676 */ 677 function book( 678 $bookId, 679 $journalDate, 680 $journalDescription, 681 $debitAccount, 682 $creditAccount, 683 $debitAmount, 684 $creditAmount, 685 $debitText = [], 686 $creditText = [] 687 ) { 688 689 690 $book = $this->getBook($bookId); 691 692 if ($book['bookClosed'] == 'y') { 693 $errors[] = tra("This book has been closed. Bookings can no longer be made in it."); 694 } 695 if (! ($journalDate instanceof DateTime)) { 696 return [tra("Invalid booking date.")]; 697 } 698 699 $errors = $this->checkBookDates($book, $journalDate); 700 701 if (is_array($errors)) { 702 return $errors; 703 } 704 705 $errors = []; 706 707 if (! is_array($debitAccount)) { 708 $debitAccount = [$debitAccount]; 709 } 710 if (! is_array($creditAccount)) { 711 $creditAccount = [$creditAccount]; 712 } 713 if (! is_array($debitAmount)) { 714 $debitAmount = [$debitAmount]; 715 } 716 if (! is_array($creditAmount)) { 717 $creditAmount = [$creditAmount]; 718 } 719 if (! is_array($debitText)) { 720 $debitText = [$debitText]; 721 } 722 if (! is_array($creditText)) { 723 $creditText = [$creditText]; 724 } 725 726 if (count($debitAccount) != count($debitAmount) or count($debitAccount) != count($debitText)) { 727 $errors[] = tra('The number of debit entries differs: ') . count($debitAccount) . '/' . count($debitAmount) . '/' . count($debitText); 728 } 729 730 if (count($creditAccount) != count($creditAmount) or count($creditAccount) != count($creditText)) { 731 $errors[] = tra('The number of credit entries differs: ') . count($creditAccount) . '/' . count($creditAmount) . '/' . count($creditText); 732 } 733 734 if (count($debitAccount) > 1 and count($creditAccount) > 1) { 735 $errors[] = tra('Splitting is only allowed on one side.'); 736 } 737 738 $checkamount = 0; 739 for ($i = 0, $icount_debitAmount = count($debitAmount); $i < $icount_debitAmount; $i++) { 740 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 741 if (! is_numeric($a) or $a <= 0) { 742 $errors[] = tra('Invalid debit amount ') . $debitAmount[$i]; 743 } else { 744 $checkamount -= $a; 745 } 746 if (! is_numeric($debitAccount[$i])) { 747 $errors[] = tra('Invalid debit account number ') . $debitAccount[$i]; 748 } 749 } 750 751 for ($i = 0, $icount_creditAmount = count($creditAmount); $i < $icount_creditAmount; $i++) { 752 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 753 if (! is_numeric($a) or $a <= 0) { 754 $errors[] = tra('Invalid credit amount ') . $creditAmount[$i]; 755 } else { 756 $checkamount += $a; 757 } 758 759 if (! is_numeric($creditAccount[$i])) { 760 $errors[] = tra('Invalid credit account number ') . $creditAccount[$i]; 761 } 762 } 763 764 if ($checkamount != 0) { 765 $errors[] = tra('Difference between debit and credit amounts ') . $checkamount; 766 } 767 768 if (count($errors) > 0) { 769 return $errors; 770 } 771 772 $query = "INSERT INTO `tiki_acct_journal` (`journalBookId`, `journalDate`, `journalDescription`, 773 `journalCancelled`, `journalTs`) 774 VALUES (?,?,?,0,NOW())"; 775 $res = $this->query($query, [$bookId, date_format($journalDate, 'Y-m-d'), $journalDescription]); 776 777 if ($res === false) { 778 $errors[] = tra('Booking error creating journal entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 779 return $errors; 780 } 781 782 $journalId = $this->lastInsertId(); 783 784 $query = "INSERT INTO `tiki_acct_item` (`itemBookId`, `itemJournalId`, `itemAccountId`, `itemType`, 785 `itemAmount`, `itemText`, `itemTs`) 786 VALUES (?, ?, ?, ?, ?, ?, NOW())"; 787 788 for ($i = 0, $icount_debitAccount = count($debitAccount); $i < $icount_debitAccount; $i++) { 789 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 790 $res = $this->query($query, [$bookId, $journalId, $debitAccount[$i], -1, $a, $debitText[$i]]); 791 if ($res === false) { 792 $errors[] = tra('Booking error creating debit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 793 $errors[] = $this->manualRollback($bookId, $journalId); 794 return $errors; 795 } 796 } 797 798 for ($i = 0, $icount_creditAccount = count($creditAccount); $i < $icount_creditAccount; $i++) { 799 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 800 $res = $this->query($query, [$bookId, $journalId, $creditAccount[$i], 1, $a, $creditText[$i]]); 801 if ($res === false) { 802 $errors[] = tra('Booking error creating credit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 803 $errors[] = $this->manualRollback($bookId, $journalId); 804 return $errors; 805 } 806 } 807 return $journalId; 808 }// book 809 810 /** 811 * 812 * Retrieves one entry from the journal 813 * 814 * @param int $bookId id of the current book 815 * @param int $journalId id of the post in the journal 816 * @return array|bool array with post, false on error 817 */ 818 function getTransaction($bookId, $journalId) 819 { 820 $query = 'SELECT `journalId`, `journalDate`, `journalDescription`, `journalCancelled`' . 821 ' FROM `tiki_acct_journal`' . 822 ' WHERE `journalBookId`=? AND `journalId`=?' 823 ; 824 $res = $this->query($query, [$bookId, $journalId]); 825 if ($res === false) { 826 return false; 827 } 828 $entry = $res->fetchRow(); 829 $query = "SELECT * FROM `tiki_acct_item` WHERE `itemBookId`=? AND `itemJournalId`=? AND `itemType`=? ORDER BY `itemAccountId` ASC"; 830 $entry['debit'] = $this->fetchAll($query, [$bookId, $entry['journalId'], -1]); 831 $entry['debitcount'] = count($entry['debit']); 832 $entry['credit'] = $this->fetchAll($query, [$bookId, $entry['journalId'], 1]); 833 $entry['creditcount'] = count($entry['credit']); 834 $entry['maxcount'] = max($entry['creditcount'], $entry['debitcount']); 835 return $entry; 836 } //getTransaction 837 838 /** 839 * Declares a statement in the journal as cancelled. 840 * @param int $bookId id of the current book 841 * @param int $journalId journalId of the statement to cancel 842 */ 843 function cancelTransaction($bookId, $journalId) 844 { 845 $book = $this->getBook($bookId); 846 if ($book['bookClosed'] == 'y') { 847 $errors[] = tra("This book has been closed. Transactions can no longer be cancelled in it."); 848 } 849 $query = "UPDATE `tiki_acct_journal` SET `journalCancelled`=1 WHERE `journalBookId`=? and `journalId`=?"; 850 $res = $this->query($query, [$bookId, $journalId]); 851 return true; 852 } // cancelTransaction 853 854 /** 855 * Returns the complete stack 856 * 857 * @param int $bookId id of the current book 858 * @return array|bool stack with all posts, false on errors 859 */ 860 function getStack($bookId) 861 { 862 $stack = []; 863 $query = "SELECT * FROM `tiki_acct_stack` WHERE `stackBookId`=?"; 864 $res = $this->query($query, [$bookId]); 865 if ($res === false) { 866 return false; 867 } 868 869 while ($row = $res->fetchRow()) { 870 $query = "SELECT * FROM `tiki_acct_stackitem` WHERE `stackBookId`=? AND `stackItemStackId`=? AND `stackItemType`=? ORDER BY `stackItemAccountId` ASC"; 871 $row['debit'] = $this->fetchAll($query, [$bookId, $row['stackId'], -1]); 872 $row['debitcount'] = count($row['debit']); 873 $row['credit'] = $this->fetchAll($query, [$bookId, $row['stackId'], 1]); 874 $row['creditcount'] = count($row['credit']); 875 $row['maxcount'] = max($row['creditcount'], $row['debitcount']); 876 $stack[] = $row; 877 } 878 return $stack; 879 } 880 881 /** 882 * 883 * Do a manual rollback, if the creation of a complete booking fails. 884 * This is a workaround for missing transaction support 885 * @param int $bookId id of the current book 886 * @param int $stackId id of the entry to roll back 887 * @return string Text messages stating the success/failure of the rollback 888 */ 889 function stackManualRollback($bookId, $stackId) 890 { 891 $errors = []; 892 $query = "DELETE FROM `tiki_acct_stackitem` WHERE `stackitemBookId`=? AND `stackitemJournalId`=?"; 893 $res = $this->query($query, [$bookId, $stackId]); 894 $rollback = ($res !== false); 895 $query = "DELETE FROM `tiki_acct_stack` WHERE `stackBookId`=? AND `stackId`=?"; 896 $res = $this->query($query, [$bookId, $stackId]); 897 $rollback = $rollback and ($res !== false); 898 if (! $rollback) { 899 return tra('Rollback failed, inconsistent database: Cleanup needed for stackId %0 in book %1', [$stackId, $bookId]); 900 } else { 901 return tra('successfully rolled back #') . " $stackId"; 902 } 903 } //stackManualRollback 904 905 /** 906 * books a complex transaction with multiple accounts on one side into the stack 907 * 908 * @param int $bookId id of the current book 909 * @param DateTime $stackDate date of the transaction 910 * @param string $stackDescription description of this transaction 911 * @param mixed $debitAccount account(s) to debit 912 * @param mixed $creditAccount account(s) to credit 913 * @param mixed $debitAmount amount(s) on debit side 914 * @param mixed $creditAmount amount(s) on credit side 915 * @param mixed $debitText text(s) for the debit post, defaults to an empty string 916 * @param mixed $creditText text(s) for the credit post, defaults to an empty string 917 * 918 * @return int|array stackID or list of errors 919 */ 920 function stackBook( 921 $bookId, 922 $stackDate, 923 $stackDescription, 924 $debitAccount, 925 $creditAccount, 926 $debitAmount, 927 $creditAmount, 928 $debitText = [], 929 $creditText = [] 930 ) { 931 932 933 $book = $this->getBook($bookId); 934 if ($book['bookClosed'] == 'y') { 935 $errors[] = tra("This book has been closed. Bookings can no longer be made in it."); 936 } 937 938 $date = $stackDate; 939 $errors = $this->checkBookDates($book, $date); 940 if (is_array($errors)) { 941 return $errors; 942 } 943 $errors = []; 944 945 if (! is_array($debitAccount)) { 946 $debitAccount = [$debitAccount]; 947 } 948 if (! is_array($creditAccount)) { 949 $creditAccount = [$creditAccount]; 950 } 951 if (! is_array($debitAmount)) { 952 $debitAmount = [$debitAmount]; 953 } 954 if (! is_array($creditAmount)) { 955 $creditAmount = [$creditAmount]; 956 } 957 if (! is_array($debitText)) { 958 $debitText = [$debitText]; 959 } 960 if (! is_array($creditText)) { 961 $creditText = [$creditText]; 962 } 963 964 if (count($debitAccount) != count($debitAmount) or count($debitAccount) != count($debitText)) { 965 $errors[] = tra('The number of debit entries differs: ') . count($debitAccount) . '/' . count($debitAmount) . '/' . count($debitText); 966 } 967 968 if (count($creditAccount) != count($creditAmount) or count($creditAccount) != count($creditText)) { 969 $errors[] = tra('The number of credit entries differs: ') . count($creditAccount) . '/' . count($creditAmount) . '/' . count($creditText); 970 } 971 972 if (count($debitAccount) > 1 and count($creditAccount) > 1) { 973 $errors[] = tra('Splitting is only allowed on one side.'); 974 } 975 976 $checkamount = 0; 977 for ($i = 0, $icount_debitAmount = count($debitAmount); $i < $icount_debitAmount; $i++) { 978 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 979 if (! is_numeric($a) or $a <= 0) { 980 $errors[] = tra('Invalid debit amount ') . $debitAmount[$i]; 981 } else { 982 $checkamount -= $a; 983 } 984 if (! is_numeric($debitAccount[$i])) { 985 $errors[] = tra('Invalid debit account number ') . $debitAccount[$i]; 986 } 987 } 988 989 for ($i = 0, $icount_creditAmount = count($creditAmount); $i < $icount_creditAmount; $i++) { 990 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 991 if (! is_numeric($a) or $a <= 0) { 992 $errors[] = tra('Invalid credit amount ') . $creditAmount[$i]; 993 } else { 994 $checkamount += $a; 995 } 996 if (! is_numeric($creditAccount[$i])) { 997 $errors[] = tra('Invalid credit account number ') . $creditAccount[$i]; 998 } 999 } 1000 1001 if ($checkamount != 0) { 1002 $errors[] = tra('Difference between debit and credit amounts ') . $checkamount; 1003 } 1004 1005 if (count($errors) > 0) { 1006 return $errors; 1007 } 1008 1009 $query = "INSERT INTO `tiki_acct_stack` (`stackBookId`, `stackDate`, `stackDescription`) VALUES (?,?,?)"; 1010 $res = $this->query($query, [$bookId, date('Y-m-d', $date->getTimestamp()), $stackDescription]); 1011 1012 if ($res === false) { 1013 $errors[] = tra('Booking error creating stack entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1014 return $errors; 1015 } 1016 1017 $stackId = $this->lastInsertId(); 1018 1019 $query = "INSERT INTO `tiki_acct_stackitem` (`stackBookId`, `stackItemStackId`, `stackItemAccountId`, `stackItemType`, 1020 `stackItemAmount`, `stackItemText`) 1021 VALUES (?, ?, ?, ?, ?, ?)"; 1022 1023 for ($i = 0, $icount_debitAccount = count($debitAccount); $i < $icount_debitAccount; $i++) { 1024 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 1025 $res = $this->query($query, [$bookId, $stackId, $debitAccount[$i], -1, $a, $debitText[$i]]); 1026 if ($res === false) { 1027 $errors[] = tra('Booking error creating stack debit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1028 $errors[] = $this->stackManualRollback($bookId, $stackId); 1029 return $errors; 1030 } 1031 } 1032 1033 for ($i = 0, $icount_creditAccount = count($creditAccount); $i < $icount_creditAccount; $i++) { 1034 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 1035 $res = $this->query($query, [$bookId, $stackId, $creditAccount[$i], 1, $a, $creditText[$i]]); 1036 if ($res === false) { 1037 $errors[] = tra('Booking error creating stack credit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1038 $errors[] = $this->manualRollback($bookId, $stackId); 1039 return $errors; 1040 } 1041 } 1042 // everything ok 1043 return $stackId; 1044 }// stackBook 1045 1046 function stackUpdate( 1047 $bookId, 1048 $stackId, 1049 $stackDate, 1050 $stackDescription, 1051 $debitAccount, 1052 $creditAccount, 1053 $debitAmount, 1054 $creditAmount, 1055 $debitText = [], 1056 $creditText = [] 1057 ) { 1058 1059 1060 $book = $this->getBook($bookId); 1061 if ($book['bookClosed'] == 'y') { 1062 $errors[] = tra("This book has been closed. Bookings can no longer be made in it."); 1063 } 1064 1065 $date = $stackDate; 1066 $errors = $this->checkBookDates($book, $date); 1067 if (is_array($errors)) { 1068 return $errors; 1069 } 1070 $errors = []; 1071 1072 if (! is_array($debitAccount)) { 1073 $debitAccount = [$debitAccount]; 1074 } 1075 if (! is_array($creditAccount)) { 1076 $creditAccount = [$creditAccount]; 1077 } 1078 if (! is_array($debitAmount)) { 1079 $debitAmount = [$debitAmount]; 1080 } 1081 if (! is_array($creditAmount)) { 1082 $creditAmount = [$creditAmount]; 1083 } 1084 if (! is_array($debitText)) { 1085 $debitText = [$debitText]; 1086 } 1087 if (! is_array($creditText)) { 1088 $creditText = [$creditText]; 1089 } 1090 1091 if (count($debitAccount) != count($debitAmount) or count($debitAccount) != count($debitText)) { 1092 $errors[] = tra('The number of debit entries differs: ') . count($debitAccount) . '/' . count($debitAmount) . '/' . count($debitText); 1093 } 1094 1095 if (count($creditAccount) != count($creditAmount) or count($creditAccount) != count($creditText)) { 1096 $errors[] = tra('The number of credit entries differs: ') . count($creditAccount) . '/' . count($creditAmount) . '/' . count($creditText); 1097 } 1098 1099 if (count($debitAccount) > 1 and count($creditAccount) > 1) { 1100 $errors[] = tra('Splitting is only allowed on one side.'); 1101 } 1102 1103 $checkamount = 0; 1104 for ($i = 0, $icount_debitAmount = count($debitAmount); $i < $icount_debitAmount; $i++) { 1105 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 1106 if (! is_numeric($a) or $a <= 0) { 1107 $errors[] = tra('Invalid debit amount ') . $debitAmount[$i]; 1108 } else { 1109 $checkamount -= $a; 1110 } 1111 if (! is_numeric($debitAccount[$i])) { 1112 $errors[] = tra('Invalid debit account number ') . $debitAccount[$i]; 1113 } 1114 } 1115 1116 for ($i = 0, $icount_creditAmount = count($creditAmount); $i < $icount_creditAmount; $i++) { 1117 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 1118 if (! is_numeric($a) or $a <= 0) { 1119 $errors[] = tra('Invalid credit amount ') . $creditAmount[$i]; 1120 } else { 1121 $checkamount += $a; 1122 } 1123 if (! is_numeric($creditAccount[$i])) { 1124 $errors[] = tra('Invalid credit account number ') . $creditAccount[$i]; 1125 } 1126 } 1127 1128 if ($checkamount != 0) { 1129 $errors[] = tra('Difference between debit and credit amounts ') . $checkamount; 1130 } 1131 1132 if (count($errors) > 0) { 1133 return $errors; 1134 } 1135 1136 $query = "UPDATE `tiki_acct_stack` SET `stackDate`=?, `stackDescription`=? WHERE `stackBookId`=? AND `stackId`=?"; 1137 $res = $this->query($query, [date('Y-m-d', $date->getTimestamp()), $stackDescription, $bookId, $stackId]); 1138 if ($res === false) { 1139 $errors[] = tra('Booking error creating stack entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1140 return $errors; 1141 } 1142 1143 $query = "DELETE FROM `tiki_acct_stackitem` WHERE `stackBookId`=? AND `stackItemStackId`=?"; 1144 $res = $this->query($query, [$bookId, $stackId]); 1145 if ($res === false) { 1146 $errors[] = tra('Booking error creating stack entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1147 $errors[] = $this->stackManualRollback($bookId, $stackId); 1148 return $errors; 1149 } 1150 1151 $query = "INSERT INTO `tiki_acct_stackitem` (`stackBookId`, `stackItemStackId`, `stackItemAccountId`, `stackItemType`, 1152 `stackItemAmount`, `stackItemText`) 1153 VALUES (?, ?, ?, ?, ?, ?)"; 1154 1155 for ($i = 0, $icount_debitAccount = count($debitAccount); $i < $icount_debitAccount; $i++) { 1156 $a = $this->cleanupAmount($bookId, $debitAmount[$i]); 1157 $res = $this->query($query, [$bookId, $stackId, $debitAccount[$i], -1, $a, $debitText[$i]]); 1158 if ($res === false) { 1159 $errors[] = tra('Booking error creating stack debit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1160 $errors[] = $this->stackManualRollback($bookId, $stackId); 1161 return $errors; 1162 } 1163 } 1164 1165 for ($i = 0, $icount_creditAccount = count($creditAccount); $i < $icount_creditAccount; $i++) { 1166 $a = $this->cleanupAmount($bookId, $creditAmount[$i]); 1167 $res = $this->query($query, [$bookId, $stackId, $creditAccount[$i], 1, $a, $creditText[$i]]); 1168 if ($res === false) { 1169 $errors[] = tra('Booking error creating stack credit entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1170 $errors[] = $this->manualRollback($bookId, $stackId); 1171 return $errors; 1172 } 1173 } 1174 // everything ok 1175 return $stackId; 1176 } 1177 1178 /** 1179 * deletes an entry from the stack 1180 * @param int $bookId id of the current book 1181 * @param int $stackId id of the entry to delete 1182 * @return bool|array true on success, array of error messages otherwise 1183 */ 1184 function stackDelete($bookId, $stackId) 1185 { 1186 $errors = []; 1187 $query = "DELETE FROM `tiki_acct_stackitem` WHERE `stackBookId`=? AND `stackItemStackId`=?"; 1188 $res = $this->query($query, [$bookId, $stackId]); 1189 if ($res === false) { 1190 $errors[] = tra('Error deleting entry from stack') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1191 } 1192 $query = "DELETE FROM `tiki_acct_stack` WHERE `stackBookId`=? AND `stackId`=?"; 1193 $res = $this->query($query, [$bookId, $stackId]); 1194 if ($res === false) { 1195 $errors[] = tra('Error deleting entry from stack') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1196 } 1197 if (count($errors) != 0) { 1198 return $errors; 1199 } 1200 return true; 1201 } 1202 1203 /** 1204 * 1205 * Confirm a transaction and transfer it to the journal 1206 * @param int $bookId id of the current book 1207 * @param int $stackId id of the entry in the stack 1208 */ 1209 function stackConfirm($bookId, $stackId) 1210 { 1211 $query = "INSERT into `tiki_acct_journal` (`journalBookId`, `journalDate`, `journalDescription`, 1212 `journalCancelled`, `journalTs`) 1213 SELECT ?, `stackDate`, `stackDescription` , 0, NOW() FROM `tiki_acct_stack` WHERE `stackBookId`=? AND `stackId`=?"; 1214 $res = $this->query($query, [$bookId, $bookId, $stackId]); 1215 if ($res === false) { 1216 $errors[] = tra('Booking error confirming stack entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1217 return $errors; 1218 } 1219 $journalId = $this->lastInsertId(); 1220 $query = "INSERT INTO `tiki_acct_item` (`itemBookId`, `itemJournalId`, `itemAccountId`, `itemType`, 1221 `itemAmount`, `itemText`, `itemTs`) 1222 SELECT ?, ?, `stackItemAccountId`, `stackItemType`, `stackItemAmount`, `stackItemText`, NOW() 1223 FROM `tiki_acct_stackitem` WHERE `stackBookId`=? AND `stackItemStackId`=?"; 1224 $res = $this->query($query, [$bookId, $journalId, $bookId, $stackId]); 1225 if ($res === false) { 1226 $errors[] = tra('Booking error confirming stack entry') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1227 $errors[] = $this->manualRollback($bookId, $journalId); 1228 return $errors; 1229 } 1230 $this->stackDelete($bookId, $stackId); 1231 $query = "UPDATE `tiki_acct_statement` SET `statementJournalId`=? WHERE `statementBookId`=? AND `statementStackId`=?"; 1232 $res = $this->query($query, [$journalId, $bookId, $stackId]); 1233 return true; 1234 } 1235 1236 /** 1237 * 1238 * Retrieves one entry from the stack 1239 * 1240 * @param int $bookId id of the current book 1241 * @param int $journalId id of the post in the journal 1242 * @return array|bool array with post, false on error 1243 */ 1244 function getStackTransaction($bookId, $stackId) 1245 { 1246 $query = "SELECT * FROM `tiki_acct_stack` WHERE `stackBookId`=? AND `stackId`=?"; 1247 $res = $this->query($query, [$bookId, $stackId]); 1248 if ($res === false) { 1249 return false; 1250 } 1251 $entry = $res->fetchRow(); 1252 $query = "SELECT * FROM `tiki_acct_stackitem` WHERE `stackBookId`=? AND `stackItemStackId`=? AND `stackItemType`=? ORDER BY `stackItemAccountId` ASC"; 1253 $entry['debit'] = $this->fetchAll($query, [$bookId, $entry['stackId'], -1]); 1254 $entry['debitcount'] = count($entry['debit']); 1255 $entry['credit'] = $this->fetchAll($query, [$bookId, $entry['stackId'], 1]); 1256 $entry['creditcount'] = count($entry['credit']); 1257 $entry['maxcount'] = max($entry['creditcount'], $entry['debitcount']); 1258 return $entry; 1259 } //getTransaction 1260 1261 /** 1262 * Returns a list of bankaccounts which are related to internal accounts 1263 * @param int $bookId id if the current book 1264 * 1265 * @return array list of accounts 1266 */ 1267 function getBankAccounts($bookId) 1268 { 1269 $query = "SELECT * FROM `tiki_acct_bankaccount` INNER JOIN `tiki_acct_account` 1270 ON `tiki_acct_bankaccount`.`bankBookId` = `tiki_acct_account`.`accountBookId` AND 1271 `tiki_acct_bankaccount`.`bankAccountId`=`tiki_acct_account`.`accountId` 1272 WHERE `tiki_acct_bankaccount`.`bankBookId`=?"; 1273 return $this->fetchAll($query, [$bookId]); 1274 }//getBankAccounts 1275 1276 /** 1277 * Returns a list of bank statements which have been uploaded but not yet been processed 1278 * 1279 * @param int $bookId id of the current book 1280 * @param int $accountId id of the account to fetch the statements for 1281 * @return array|bool list of statements or false if an error occurred 1282 */ 1283 function getOpenStatements($bookId, $accountId) 1284 { 1285 $query = "SELECT * FROM `tiki_acct_statement` 1286 WHERE `statementJournalId`=0 AND `statementStackId`=0 1287 AND `statementBookId`=? AND `statementAccountId`=?"; 1288 return $this->fetchAll($query, [$bookId, $accountId]); 1289 }//getOpenStatements 1290 1291 /** 1292 * Returns the statement with the given Id from the list of statements 1293 * 1294 * @param int $statetmentId id of the statement to retrieve 1295 * @return array|bool statement data or false on error 1296 */ 1297 function getStatement($statementId) 1298 { 1299 $query = "SELECT * FROM `tiki_acct_statement` WHERE `statementId`=?"; 1300 $res = $this->query($query, [$statementId]); 1301 if ($res === false) { 1302 return $res; 1303 } 1304 return $res->fetchRow(); 1305 }//getStatement 1306 1307 /** 1308 * Returns the import specification for a given accountId 1309 * @param int $bookId id of the current book 1310 * @param int $accountId id of the account we want the specs for 1311 * @return array|bool list of statements or false 1312 */ 1313 function getBankAccount($bookId, $accountId) 1314 { 1315 $query = "SELECT * FROM `tiki_acct_bankaccount` WHERE bankBookId=? and bankAccountId=?"; 1316 $res = $this->query($query, [$bookId, $accountId]); 1317 if ($res === false) { 1318 return $res; 1319 } 1320 return $res->fetchRow(); 1321 }//getBankAccount 1322 1323 /** 1324 * Splits a header line into a matching array according to the specifications 1325 * 1326 * @param string $header line containing headers 1327 * @param array $defs file definitions 1328 * @return array list of statements 1329 */ 1330 function analyzeHeader($header, $defs) 1331 { 1332 $cols = explode($defs['bankDelimeter'], $header); 1333 $columns = []; 1334 1335 for ($i = 0, $isizeof_cols = count($cols); $i < $isizeof_cols; $i++) { 1336 switch ($cols[$i]) { 1337 case $defs['fieldNameAccount']: 1338 $columns['accountId'] = $i; 1339 break; 1340 case $defs['fieldNameBookingDate']: 1341 $columns['bookingDate'] = $i; 1342 break; 1343 case $defs['fieldNameValueDate']: 1344 $columns['valueDate'] = $i; 1345 break; 1346 case $defs['fieldNameBookingText']: 1347 $columns['bookingText'] = $i; 1348 break; 1349 case $defs['fieldNameReason']: 1350 $columns['reason'] = $i; 1351 break; 1352 case $defs['fieldNameCounterpartName']: 1353 $columns['counterpartName'] = $i; 1354 break; 1355 case $defs['fieldNameCounterpartAccount']: 1356 $columns['counterpartAccount'] = $i; 1357 break; 1358 case $defs['fieldNameCounterpartBankcode']: 1359 $columns['counterpartBankcode'] = $i; 1360 break; 1361 case $defs['fieldNameAmount']: 1362 $columns['amount'] = $i; 1363 break; 1364 case $defs['fieldNameAmountSign']: 1365 $columns['amountSign'] = $i; 1366 break; 1367 } 1368 } 1369 return $columns; 1370 }//analyzeHeader 1371 1372 /** 1373 * updates journalId in the given statement 1374 * 1375 * @param int $statementId id of the statement to update 1376 * @param int $journalId id of the entry in the journal which was caused by this statement 1377 * @return array|boolean list of errors, empty if no errors were found 1378 */ 1379 function updateStatement($statementId, $journalId) 1380 { 1381 $errors = []; 1382 1383 $query = "UPDATE `tiki_acct_statement` SET `statementJournalId`=? WHERE `statementId`=?"; 1384 $res = $this->query($query, [$journalId, $statementId]); 1385 if ($res === false) { 1386 $errors[] = tra('Error while updating statement:') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1387 return $errors; 1388 } 1389 return true; 1390 }//updateStatement 1391 1392 /** 1393 * updates journalId in the given statement 1394 * 1395 * @param int $statementId id of the statement to update 1396 * @param int $journalId id of the entry in the journal which was caused by this statement 1397 * @return array|bool list of errors, empty if no errors were found 1398 */ 1399 function updateStatementStack($statementId, $stackId) 1400 { 1401 $errors = []; 1402 1403 $query = "UPDATE `tiki_acct_statement` SET `statementStackId`=? WHERE `statementId`=?"; 1404 $res = $this->query($query, [$stackId, $statementId]); 1405 if ($res === false) { 1406 $errors[] = tra('Error while updating statement:') . $this->ErrorNo() . ": " . $this->ErrorMsg() . "<br /><pre>$query</pre>"; 1407 return $errors; 1408 } 1409 return true; 1410 }//updateStatementStack 1411 1412 /** 1413 * 1414 * Creates a tax setting for automated tax deduction/splitting 1415 * @param int $bookId 1416 * @param string $taxText 1417 * @param double $taxAmount 1418 * @param string $taxIsFix 1419 * @return int id of the newly created tax 1420 */ 1421 function createTax($bookId, $taxText, $taxAmount, $taxIsFix = 'n') 1422 { 1423 $query = "INSERT INTO `tiki_acct_tax` (`taxBookId`, `taxText`, `taxAmount`, `taxIsFix`) VALUES (?, ?, ?, ?)"; 1424 $res = $this->query($query, [$bookId, $taxText, $taxAmount, $taxIsFix]); 1425 return $this->lastInsertId(); 1426 } 1427 1428 /** 1429 * removes all unnecessary thousand markers and replaces local decimal characters with "." to enable handling as numbers. 1430 * 1431 * @param int $bookId id of the current book 1432 * @param string $amount date of the transaction 1433 * @return string/float Returns a float or an empty string if the source is not numeric 1434 */ 1435 function cleanupAmount($bookId, $amount) 1436 { 1437 $book = $this->getBook($bookId); 1438 $a = str_replace($book['bookDecPoint'], '.', str_replace($book['bookThousand'], '', $amount)); 1439 if (! is_numeric($a)) { 1440 return ''; 1441 } 1442 return (float)$a; 1443 }//cleanupAmount 1444 1445 /** 1446 * Checks the existence/non-existence of a numerical id in the given table 1447 * 1448 * @param string $idname name of the id field in the table 1449 * @param int $id the id to check 1450 * @param string $table the table to search 1451 * @param boolean $exists true if a record must exist, false if it must not 1452 * 1453 * @return array Returns aa array of errors (empty if none occurred) 1454 */ 1455 function validateId($idname, $id, $table, $exists = true, $bookIdName = '', $bookId = 0) 1456 { 1457 $errors = []; 1458 if (! is_numeric($id)) { 1459 $errors[] = htmlspecialchars($idname) . ' (' . htmlspecialchars($id) . ')' 1460 . tra('is not a number.'); 1461 } elseif ($id <= 0) { 1462 $errors[] = htmlspecialchars($idname) . ' ' . tra('must be greater than 0.'); 1463 } else { 1464 //static whitelist based on usage of the validateId function in accountinglib.php 1465 $tablesWhitelist = [ 1466 'tiki_acct_tax' => [ 1467 'idname' => 'taxId', 1468 'bookIdName' => 'taxBookId' 1469 ], 1470 'tiki_acct_account' => [ 1471 'idname' => 'accountId', 1472 'bookIdName' => 'accountBookId' 1473 ] 1474 ]; 1475 if (! array_key_exists($table, $tablesWhitelist)) { 1476 $errors[] = tra('Invalid transaction - please contact administrator.'); 1477 } elseif ($idname !== $tablesWhitelist[$table]['idname']) { 1478 $errors[] = tra('Invalid transaction - please contact administrator.'); 1479 } else { 1480 $query = "SELECT $idname FROM $table WHERE $idname = ?"; 1481 $bindvars = [$id]; 1482 if ($bookIdName === $tablesWhitelist[$table]['bookIdName']) { 1483 $query .= " AND $bookIdName = ?"; 1484 array_push($bindvars, $bookId); 1485 } 1486 1487 $res = $this->query($query, $bindvars); 1488 if ($res === false) { 1489 $errors[] = tra('Error checking') . htmlspecialchars($idname) . ': ' . $this->ErrorNo() . ': ' 1490 . $this->ErrorMsg() . '<br /><pre>' . htmlspecialchars($query) . '</pre>'; 1491 } else { 1492 if ($exists) { 1493 if ($res->numRows() == 0) { 1494 $errors[] = htmlspecialchars($idname) . ' ' . tra('does not exist.'); 1495 } 1496 } else { 1497 if ($res->numRows() > 0) { 1498 $errors[] = htmlspecialchars($idname) . ' ' . tra('already exists'); 1499 } 1500 } //existence 1501 } // query 1502 } 1503 } // numeric 1504 return $errors; 1505 } // validateId 1506} 1507