gen_ledger.php
<?php
// +-----------------------------------------------------------------+
// | PhreeBooks Open Source ERP |
// +-----------------------------------------------------------------+
// | Copyright (c) 2008 PhreeSoft, LLC |
// | http://www.PhreeSoft.com |
// +-----------------------------------------------------------------+
// | 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 3 of |
// | the License, or any later version. |
// | |
// | 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. |
// | |
// | The license that is bundled with this package is located in the |
// | file: /doc/manual/ch01-Introduction/license.html. |
// | If not, see http://www.gnu.org/licenses/ |
// +-----------------------------------------------------------------+
// Path: /modules/gen_ledger/classes/gen_ledger.php
//
class journal {
var $cog_item_types = array('si', 'sr', 'ms', 'mi', 'as'); // item types that are tracked in cost of goods sold
// class constructor
function journal($id = '') {
global $db, $messageStack;
$this->affected_accounts = array();
$this->repost_ids = array();
if ($id) {
$result = $db->Execute("select * from " . TABLE_JOURNAL_MAIN . " where id = " . (int)$id);
// make sure we have a record or die (there's a problem that needs to be fixed)
if ($result->RecordCount() == 0) return $this->fail_message(GL_ERROR_DIED_CREATING_RECORD . $id);
foreach ($result->fields as $key => $value) $this->$key = $value;
$this->journal_main_array = $this->build_journal_main_array(); // build ledger main record
$result = $db->Execute("select * from " . TABLE_JOURNAL_ITEM . " where ref_id = " . (int)$id);
$this->journal_rows = array();
$i = 0;
while (!$result->EOF) {
foreach ($result->fields as $key => $value) $this->journal_rows[$i][$key] = $value;
$i++;
$result->MoveNext();
}
}
}
/*******************************************************************************************************************/
// START Post Journal Function
/*******************************************************************************************************************/
function Post($action = 'insert', $skip_balance = false) {
global $messageStack;
$this->first_period = $this->period;
$this->repost_ids = array();
if ($action == 'edit') { // unPost the original entry and remove from db
if (DEBUG) $messageStack->debug("\n\n unPosting as part of edit journal main id = " . $this->id);
$old_gl_entry = new journal($this->id); // read in the original journal entry
$this->first_period = min($old_gl_entry->period, $this->first_period);
if (!$old_gl_entry->unPost('edit', true)) return false; // unpost it
$this->affected_accounts = gen_array_key_merge($this->affected_accounts, $old_gl_entry->affected_accounts);
if (sizeof($old_gl_entry->repost_ids) > 0) { // rePost any journal entries unPosted to rollback COGS calculation (if edit)
if (DEBUG) $messageStack->debug("\n First level unPost returned re-post_ids to be unPosted next = " . arr2string($old_gl_entry->repost_ids));
while (true) {
$id = array_shift($old_gl_entry->repost_ids);
if (DEBUG) $messageStack->debug("\n\n unPosting re-post Journal main id = " . $id);
if(!$id) break; // no more to unPost, exit loop
if (in_array($id, $this->repost_ids)) continue; // already has been unposted, skip
$this->repost_ids[$id] = $id;
$this->unPost_entry[$id] = new journal($id);
if (!$this->unPost_entry[$id]->unPost('edit', true)) return false;
// add the new repost_ids to the arrays, one for now, one for re-post loop later
$old_gl_entry->repost_ids += $this->unPost_entry[$id]->repost_ids;
if (DEBUG) $messageStack->debug("\n\n unPosting array now looks like = " . arr2string($old_gl_entry->repost_ids));
if (DEBUG) $messageStack->debug("\n re-Posting array will re-post = " . arr2string($this->repost_ids));
$this->unPost_entry[$id]->repost_ids = array(); // clear nested unPost to zero, so it doesn't re-post
}
}
}
// post journal main record
if (DEBUG) $messageStack->debug("\n\nPosting Journal main ... id = " . $this->id . " and action = " . $action . " and journal_id = " . $this->journal_id);
if (DEBUG) $messageStack->debug("\n main_array = " . arr2string($this->journal_main_array));
db_perform(TABLE_JOURNAL_MAIN, $this->journal_main_array, 'insert');
$this->id = db_insert_id();
// post journal rows
if (DEBUG) $messageStack->debug("\n\nPosting Journal rows ...");
for ($i = 0; $i < count($this->journal_rows); $i++) {
if (DEBUG) $messageStack->debug("\n journal_rows = " . arr2string($this->journal_rows[$i]));
$this->journal_rows[$i]['ref_id'] = $this->id; // link the rows to the journal main id
db_perform(TABLE_JOURNAL_ITEM, $this->journal_rows[$i], 'insert');
$this->journal_rows[$i]['id'] = db_insert_id();
}
if (DEBUG) $messageStack->debug("\n\nStarting auxilliary post functions ...");
// Inventory needs to be posted first because function may add additional journal rows for COGS
if (!$this->Post_inventory()) return false;
if (!$this->Post_chart_balances()) return false; // post the chart of account values
if (!$this->Post_account_sales_purchases()) return false;
if (sizeof($this->repost_ids) > 0) { // rePost any journal entries unPosted to rollback COGS calculation (if edit)
if (DEBUG) $messageStack->debug("\nStarting to Post re-post_ids to be Posted = " . arr2string($this->repost_ids));
while ($id = array_shift($this->repost_ids)) {
if (DEBUG) $messageStack->debug("\n\nRe-posting as part of Post - Journal main id = " . $id);
$gl_entry = $this->unPost_entry[$id];
if (!is_object($gl_entry)) { // for this case, the affected journal objects have not been created
$gl_entry = new journal($id);
$gl_entry->remove_cogs_rows(); // they will be regenerated during the re-post
if (!$gl_entry->Post('edit', true)) return false;
} else {
$gl_entry->remove_cogs_rows(); // they will be regenerated during the re-post
if (!$gl_entry->Post('insert', true)) return false;
}
$this->affected_accounts = gen_array_key_merge($this->affected_accounts, $gl_entry->affected_accounts);
$this->first_period = min($gl_entry->period, $this->first_period);
}
}
if (!$skip_balance) {
if (!$this->update_chart_history_periods($this->first_period)) return false;
}
if (!$this->check_for_closed_po_so('Post')) return false;
if (DEBUG) $messageStack->debug("\n*************** end Posting Journal ******************* id = " . $this->id . "\n");
return true;
}
function unPost($action = 'delete', $skip_balance = false) {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n\nunPosting Journal... id = " . $this->id . " and action = " . $action . " and journal_id = " . $this->journal_id);
if (!$this->check_for_re_post()) return false; // check for dependent records that will need to be re-posted
if (!$this->unPost_account_sales_purchases()) return false; // unPost the customer/vendor history
// unPost_chart_balances needs to be unPosted before inventory because inventory may remove journal rows (COGS)
if (!$this->unPost_chart_balances()) return false; // unPost the chart of account values
if (!$this->unPost_inventory()) return false;
if (DEBUG) $messageStack->debug("\n Deleting Journal main and rows as part of unPost ...");
$result = $db->Execute("delete from " . TABLE_JOURNAL_MAIN . " where id = " . $this->id);
if ($result->AffectedRows() <> 1) return $this->fail_message(GL_ERROR_CANNOT_DELETE_MAIN);
$result = $db->Execute("delete from " . TABLE_JOURNAL_ITEM . " where ref_id = " . $this->id);
if ($result->AffectedRows() == 0 ) return $this->fail_message(printf(GL_ERROR_CANNOT_DELETE_ITEM, $id));
if ($action <> 'edit') { // re-post affected entries unless edited (which is after the entry is reposted)
if (is_array($this->repost_ids)) { // rePost any journal entries unPosted to rollback COGS calculation
while ($id = array_shift($this->repost_ids)) {
if (DEBUG) $messageStack->debug("\n\nRe-posting as part of unPost - Journal main id = " . $id);
$gl_entry = $this->unPost_entry[$id];
if (!is_object($gl_entry)) { // for the delete case, the affected journal objects have not been created
$gl_entry = new journal($id);
$gl_entry->remove_cogs_rows(); // they will be regenerated during the re-post
if (!$gl_entry->Post('edit', true)) return false;
} else {
$gl_entry->remove_cogs_rows(); // they will be regenerated during the re-post
if (!$gl_entry->Post('insert', true)) return false;
}
$this->affected_accounts = gen_array_key_merge($this->affected_accounts, $gl_entry->affected_accounts);
$this->first_period = min($gl_entry->period, $this->first_period);
}
}
}
if (!$skip_balance) {
if (!$this->update_chart_history_periods($this->period)) return false;
}
if (!$this->check_for_closed_po_so('unPost')) return false; // check to re-open predecessor entry
if (DEBUG) $messageStack->debug("\nend unPosting Journal.\n");
return true;
}
/*******************************************************************************************************************/
// END Post Journal Function
/*******************************************************************************************************************/
// START re-post Functions
/*******************************************************************************************************************/
function check_for_re_post() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Checking for re-post records ... ");
$gl_type = NULL;
switch ($this->journal_id) {
case 6: // Purchase/Receive Journal
case 7: // Purchase Credit Memo Journal
// case 21: // Inventory Direct Purchase Journal
case 12: // Sales/Invoice Journal
case 13: // Sales Credit Memo Journal
// case 19: // POS Journal
// Check for payments or receipts made to this record that will need to be re-posted.
$sql = "select ref_id from " . TABLE_JOURNAL_ITEM . "
where so_po_item_ref_id = " . $this->id . " and gl_type in ('chk', 'pmt')";
$result = $db->Execute($sql);
while(!$result->EOF) {
if (DEBUG) $messageStack->debug("\n check_for_report is queing re-post id = " . $result->fields['ref_id']);
$this->repost_ids[$result->fields['ref_id']] = $result->fields['ref_id'];
$result->MoveNext();
}
if (DEBUG) $messageStack->debug(" end Checking for Re-post.");
break;
case 2: // General Journal
case 3: // Purchase Quote Journal
case 4: // Purchase Order Journal
case 9: // Sales Quote Journal
case 10: // Sales Order Journal
case 14: // Inventory Assembly Journal
case 16: // Inventory Adjustment Journal
case 18: // Cash Receipts Journal
case 20: // Cash Distribution Journal
default:
if (DEBUG) $messageStack->debug(" end check for Re-post with no action.");
}
return true;
}
/*******************************************************************************************************************/
// START Chart of Accout Functions
/*******************************************************************************************************************/
function Post_chart_balances() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Posting Chart Balances...");
switch ($this->journal_id) {
case 2: // General Journal
case 6: // Purchase/Receive Journal
case 7: // Purchase Credit Memo Journal
case 12: // Sales/Invoice Journal
case 13: // Sales Credit Memo Journal
case 14: // Inventory Assembly Journal
case 16: // Inventory Adjustment Journal
case 18: // Cash Receipts Journal
// case 19: // POS Journal
case 20: // Cash Distribution Journal
// case 21: // Inventory Direct Purchase Journal
for ($i=0; $i<count($this->journal_rows); $i++) {
$credit_amount = ($this->journal_rows[$i]['credit_amount']) ? $this->journal_rows[$i]['credit_amount'] : '0';
$debit_amount = ($this->journal_rows[$i]['debit_amount']) ? $this->journal_rows[$i]['debit_amount'] : '0';
if ($credit_amount <> 0 || $debit_amount <> 0) {
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " set
credit_amount = credit_amount + " . $credit_amount . ",
debit_amount = debit_amount + " . $debit_amount . ",
last_update = '" . $this->post_date . "'
where account_id = '" . $this->journal_rows[$i]['gl_account'] . "' and period = " . $this->period;
if (DEBUG) $messageStack->debug("\n Post chart balances: credit_amount = " . $credit_amount . ", debit_amount = " . $debit_amount . ", acct = " . $this->journal_rows[$i]['gl_account']);
$result = $db->Execute($sql);
if ($result->AffectedRows() <> 1) return $this->fail_message(GL_ERROR_POSTING_CHART_BALANCES . $this->journal_rows[$i]['gl_account']);
$this->affected_accounts[$this->journal_rows[$i]['gl_account']] = 1;
}
}
if (DEBUG) $messageStack->debug("\n end Posting Chart Balances.");
break;
case 3: // Purchase Quote Journal
case 4: // Purchase Order Journal
case 9: // Sales Quote Journal
case 10: // Sales Order Journal
default:
if (DEBUG) $messageStack->debug(" end Posting Chart Balances with no action.");
}
return true;
}
function unPost_chart_balances() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n unPosting Chart Balances...");
switch ($this->journal_id) {
case 2: // General Journal
case 6: // Purchase/Receive Journal
case 7: // Purchase Credit Memo Journal
case 12: // Sales/Invoice Journal
case 13: // Sales Credit Memo Journal
case 14: // Inventory Assembly Journal
case 16: // Inventory Adjustment Journal
case 18: // Cash Receipts Journal
// case 19: // POS Journal
case 20: // Cash Distribution Journal
// case 21: // Inventory Direct Purchase Journal
for ($i=0; $i<count($this->journal_rows); $i++) {
// Update chart of accounts history
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " set
credit_amount = credit_amount - " . $this->journal_rows[$i]['credit_amount'] . ",
debit_amount = debit_amount - " . $this->journal_rows[$i]['debit_amount'] . "
where account_id = '" . $this->journal_rows[$i]['gl_account'] . "' and period = " . $this->period;
if (DEBUG) $messageStack->debug("\n unPost chart balances: credit_amount = " . $this->journal_rows[$i]['credit_amount'] . ", debit_amount += " . $this->journal_rows[$i]['debit_amount'] . ", acct = " . $this->journal_rows[$i]['gl_account']);
$coa_update = $db->Execute($sql);
$this->affected_accounts[$this->journal_rows[$i]['gl_account']] = 1;
}
if (DEBUG) $messageStack->debug(" ... end unPosting Chart Balances.");
break;
case 3: // Purchase Quote Journal
case 4: // Purchase Order Journal
case 9: // Sales Quote Journal
case 10: // Sales Order Journal
default:
if (DEBUG) $messageStack->debug(" end unPosting Chart Balances with no action.");
}
return true;
}
// ********* chart of account support functions **********
function update_chart_history_periods($period = CURRENT_ACCOUNTING_PERIOD) {
global $db, $messageStack;
// first find out the last period with data in the system from the current_status table
$sql = "select fiscal_year from " . TABLE_ACCOUNTING_PERIODS . " where period = " . $period;
$result = $db->Execute($sql);
if ($result->EOF) return $this->fail_message(GL_ERROR_BAD_ACCT_PERIOD);
$fiscal_year = $result->fields['fiscal_year'];
if (DEBUG) $messageStack->debug("\n Updating chart history for fiscal year: " . $fiscal_year . " and period: " . $period);
$sql = "select max(period) as period from " . TABLE_ACCOUNTING_PERIODS . " where fiscal_year = " . $fiscal_year;
$result = $db->Execute($sql);
$max_period = $result->fields['period'];
$affected_acct_string = (is_array($this->affected_accounts)) ? implode("', '", array_keys($this->affected_accounts)) : '';
for ($i = $period; $i <= $max_period; $i++) {
if (!$this->validate_balance($i)) return false;
// update future months
$sql = "select account_id, beginning_balance + debit_amount - credit_amount as beginning_balance
from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
where account_id in ('" . $affected_acct_string . "') and period = " . $i;
$result = $db->Execute($sql);
while (!$result->EOF) {
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
set beginning_balance = " . $result->fields['beginning_balance'] . "
where period = " . ($i + 1) . " and account_id = '" . $result->fields['account_id'] . "'";
$db->Execute($sql);
$result->MoveNext();
}
}
// see if there is another fiscal year to roll into
$sql = "select fiscal_year from " . TABLE_ACCOUNTING_PERIODS . " where period = " . ($max_period + 1);
$result = $db->Execute($sql);
if ($result->RecordCount() > 0) { // close balances for end of this fiscal year and roll post into next fiscal year
// select retained earnings account
$sql = "select id from " . TABLE_CHART_OF_ACCOUNTS . " where account_type = 44";
$result = $db->Execute($sql);
if ($result->RecordCount() <> 1) $this->fail_message(GL_ERROR_NO_RETAINED_EARNINGS_ACCOUNT);
$retained_earnings_acct = $result->fields['id'];
// select list of accounts that need to be closed, adjusted
$sql = "select id from " . TABLE_CHART_OF_ACCOUNTS . " where account_type in (30, 32, 34, 42, 44)";
$result = $db->Execute($sql);
$acct_list = array();
while(!$result->EOF) {
$acct_list[] = $result->fields['id'];
$result->MoveNext();
}
$acct_string = implode("','",$acct_list);
// fetch the totals for the closed accounts
$sql = "select sum(beginning_balance + debit_amount - credit_amount) as retained_earnings
from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
where account_id in ('" . $acct_string . "') and period = " . $max_period;
$result = $db->Execute($sql);
$retained_earnings = $result->fields['retained_earnings'];
// clear out the expense, sales, cogs, and other year end accounts that need to be closed
// needs to be before writing retained earnings account, since retained earnings is part of acct_string
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
set beginning_balance = 0
where account_id in ('" . $acct_string . "') and period = " . ($max_period + 1);
$result = $db->Execute($sql);
// update the retained earnings account
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
set beginning_balance = " . $retained_earnings . "
where account_id = '" . $retained_earnings_acct . "' and period = " . ($max_period + 1);
$result = $db->Execute($sql);
// now continue rolling in current post into next fiscal year
if (!$this->update_chart_history_periods($max_period + 1)) return false;
}
// all historical chart of account balances from period on should be OK at this point.
if (DEBUG) $messageStack->debug("\n end Updating chart history periods. Fiscal Year: " . $fiscal_year);;
return true;
}
function validate_balance($period = CURRENT_ACCOUNTING_PERIOD) {
global $db, $currencies, $messageStack;
if (DEBUG) $messageStack->debug("\n Validating trial balance for period: " . $period . " ... ");
$sql = "select sum(debit_amount) as debit, sum(credit_amount) as credit
from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " where period = " . $period;
$result = $db->Execute($sql);
// check to see if we are still in balance, round debits and credits and compare
if (DEBUG) $messageStack->debug(" debits = " . $result->fields['debit'] . " and credits = " . $result->fields['credit']);
$debit_total = round($result->fields['debit'], $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
$credit_total = round($result->fields['credit'], $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']);
if ($debit_total <> $credit_total) { // Trouble in paradise, fraction of cents adjustment next
$tolerance = 2 * (1 / pow(10, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places'])); // i.e. 2 cents in USD
$adjustment = $result->fields['credit'] - $result->fields['debit'];
if (abs($adjustment) > $tolerance) {
return $this->fail_message(GL_ERROR_OUT_OF_BALANCE_A . $result->fields['debit'] . GL_ERROR_OUT_OF_BALANCE_B . $result->fields['credit'] . GL_ERROR_OUT_OF_BALANCE_C . $period);
}
// find the adjustment account
if (!defined('ROUNDING_GL_ACCOUNT') || ROUNDING_GL_ACCOUNT == '') {
$result = $db->Execute("select id from " . TABLE_CHART_OF_ACCOUNTS . " where account_type = 44 limit 1");
if ($result->RecordCount() == 0) {
return $this->fail_message('Failed trying to locate retained earnings account to make rounding adjustment. There must be one and only one Retained Earnings account in the chart of accounts!');
}
$adj_gl_account = $result->fields['id'];
} else {
$adj_gl_account = ROUNDING_GL_ACCOUNT;
}
if (DEBUG) $messageStack->debug("\n Adjusting balance, adjustment = " . $adjustment . " and gl account = " . $adj_gl_account);
$sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . "
set debit_amount = debit_amount + " . $adjustment . "
where period = " . $period . " and account_id = '" . $adj_gl_account . "'";
$result = $db->Execute($sql);
}
if (DEBUG) $messageStack->debug(" ... End Validating trial balance.");
return true;
}
/*******************************************************************************************************************/
// END Chart of Accout Functions
/*******************************************************************************************************************/
// START Customer/Vendor Account Functions
/*******************************************************************************************************************/
// Post the customers/vendors sales/purchases values for the given period
function Post_account_sales_purchases() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Posting account sales and purchases ...");
switch ($this->journal_id) {
// case 19: if (!$this->bill_acct_id) return true; // no sales history in POS if no bill account id, else continue
case 6:
case 7:
case 12:
case 13:
case 18:
case 20:
// case 21:
if (!$this->bill_acct_id) return $this->fail_message(GL_ERROR_NO_GL_ACCT_NUMBER . 'post_account_sales_purchases.');
$history_array = array(
'ref_id' => $this->id,
'so_po_ref_id' => $this->so_po_ref_id,
'acct_id' => $this->bill_acct_id,
'journal_id' => $this->journal_id,
'purchase_invoice_id' => $this->purchase_invoice_id,
'amount' => $this->total_amount,
'post_date' => $this->post_date);
$result = db_perform(TABLE_ACCOUNTS_HISTORY, $history_array, 'insert');
if ($result->AffectedRows() <> 1 ) return $this->fail_message(GL_ERROR_UPDATING_ACCOUNT_HISTORY);
if (DEBUG) $messageStack->debug(" end Posting account sales and purchases.");
break;
case 2:
case 3:
case 4:
case 9:
case 10:
case 14:
case 16:
default: // nothing required to do
if (DEBUG) $messageStack->debug(" end Posting account sales and purchases with no action.");
}
return true;
}
function unPost_account_sales_purchases() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n unPosting account sales and purchases ...");
switch ($this->journal_id) {
// case 19: if (!$this->bill_acct_id) return true; // no sales history in POS if no bill account id, else continue
case 6:
case 7:
case 12:
case 13:
case 18:
case 20:
// case 21:
if (!$this->bill_acct_id) return $this->fail_message(GL_ERROR_NO_GL_ACCT_NUMBER . 'unPost_account_sales_purchases.');
$result = $db->Execute("delete from " . TABLE_ACCOUNTS_HISTORY . " where ref_id = " . $this->id);
if ($result->AffectedRows() <> 1) return $this->fail_message(GL_ERROR_DELETING_ACCOUNT_HISTORY);
if (DEBUG) $messageStack->debug(" end unPosting account sales and purchases.");
break;
case 2:
case 3:
case 4:
case 9:
case 10:
case 14:
case 16:
default: // nothing required to do
if (DEBUG) $messageStack->debug(" end unPosting account sales and purchases with no action.");
}
return true;
}
/*******************************************************************************************************************/
// END Customer/Vendor Account Functions
/*******************************************************************************************************************/
// START Inventory Functions
/*******************************************************************************************************************/
function Post_inventory() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Posting Inventory ...");
switch ($this->journal_id) { // Pre-posting particulars that are journal dependent
case 4:
$str_field = 'quantity_on_order';
$item_array = $this->load_so_po_balance($this->id);
break;
case 6:
$str_field = 'quantity_on_hand';
$so_po_str_field = 'quantity_on_order';
$item_array = $this->load_so_po_balance($this->so_po_ref_id, $this->id);
break;
case 10:
$str_field = 'quantity_on_sales_order';
$item_array = $this->load_so_po_balance($this->id);
break;
case 12:
// case 19:
$str_field = 'quantity_on_hand';
$so_po_str_field = 'quantity_on_sales_order';
$item_array = $this->load_so_po_balance($this->so_po_ref_id, $this->id);
break;
case 7:
case 13:
case 14:
case 16:
// case 21:
$str_field = 'quantity_on_hand';
break;
case 2:
case 3:
case 9:
case 18:
case 20:
default:
if (DEBUG) $messageStack->debug(" end Posting Inventory not requiring any action.");
return true;
}
// adjust inventory stock status levels (also fills inv_list array)
$item_rows_to_process = count($this->journal_rows); // NOTE: variable needs to be here because journal_rows may grow within for loop (COGS)
for($i = 0; $i < $item_rows_to_process; $i++) {
if ($this->journal_rows[$i]['sku']) {
if ($this->journal_rows[$i]['debit_amount']) $price = $this->journal_rows[$i]['debit_amount'] / $this->journal_rows[$i]['qty'];
if ($this->journal_rows[$i]['credit_amount']) $price = $this->journal_rows[$i]['credit_amount'] / $this->journal_rows[$i]['qty'];
$inv_list = array(
'id' => $this->journal_rows[$i]['id'],
'gl_type' => $this->journal_rows[$i]['gl_type'],
'so_po_item_ref_id' => $this->journal_rows[$i]['so_po_item_ref_id'],
'sku' => $this->journal_rows[$i]['sku'],
'description' => $this->journal_rows[$i]['description'],
'serialize_number' => $this->journal_rows[$i]['serialize_number'],
'qty' => $this->journal_rows[$i]['qty'],
'price' => $price,
'store_id' => $this->store_id,
'post_date' => $this->post_date);
switch ($this->journal_id) {
case 4:
case 10:
if ($this->closed) { // special case if closing an order and a balance remains
$bal_before_post = $item_array[$inv_list['id']]['ordered'] - $item_array[$inv_list['id']]['processed'];
if ($bal_before_post > 0) {
if(!$this->update_inventory_status($inv_list['sku'], $str_field, -$bal_before_post)) return false;
}
}
break;
case 12: // a sale so make quantity negative (pulling from inventory) and continue
// case 19:
$inv_list['qty'] = -$inv_list['qty'];
case 6:
// case 21:
if (!$this->calculate_COGS($inv_list)) return false;
if ($inv_list['so_po_item_ref_id']) { // check for reference to po/so to adjust qty on order/sales order
// do not allow qty on order to go below zero.
$bal_before_post = $item_array[$inv_list['so_po_item_ref_id']]['ordered'] - $item_array[$inv_list['so_po_item_ref_id']]['processed'] + $this->journal_rows[$i]['qty'];
$adjustment = -(min($this->journal_rows[$i]['qty'], $bal_before_post));
if(!$this->update_inventory_status($inv_list['sku'], $so_po_str_field, $adjustment)) return false;
}
break;
case 14:
$assy_cost = $this->calculate_assembly_list($inv_list); // for assembly parts list
if ($assy_cost === false) return false; // there was an error
break;
case 7: // a vendor credit memo, negate the quantity and process same as customer credit memo
$inv_list['qty'] = -$inv_list['qty'];
case 13: // a customer credit memo, qty stays positive
case 16:
if (!$this->calculate_COGS($inv_list)) return false;
break;
default: // nothing
}
}
}
// update inventory status
for($i = 0; $i < count($this->journal_rows); $i++) {
$post_qty = $this->journal_rows[$i]['qty'];
$item_cost = 0;
$full_price = 0;
switch ($this->journal_id) {
case 6:
case 13: // update the last item_cost for purchases
// case 21:
/* The item_cost calculation is being left out pending further review
When using price sheets, the item cost can vary all over the board and will affect prices if
they are based on item_cost. This can be a good or bad thing depending on how often the price
can change. item_cost can be set in the individual inventory item page or through bulk price
loads in price manager. TBD - this probably should be a user configurable true-false constant.
if ($this->journal_rows[$i]['qty']) {
$item_cost = $this->journal_rows[$i]['debit_amount'] / $this->journal_rows[$i]['qty'];
}
*/
break;
case 12:
if ($this->journal_rows[$i]['qty']) {
$full_price = $this->journal_rows[$i]['credit_amount'] / $this->journal_rows[$i]['qty'];
}
case 7:
// case 19:
$post_qty = -$post_qty;
break;
case 14:
if ($i == 0 && $this->journal_rows[$i]['qty'] > 0) { // only for the item being assembled
$item_cost = $this->journal_rows[$i]['debit_amount'] / $this->journal_rows[$i]['qty'];
}
break;
default:
}
if(!$this->update_inventory_status($this->journal_rows[$i]['sku'], $str_field, $post_qty, $item_cost, $this->journal_rows[$i]['description'], $full_price)) return false;
}
if (DEBUG) $messageStack->debug("\n end Posting Inventory.");
return true;
}
function unPost_inventory() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n unPosting Inventory ...");
// if remaining <> qty then some items have been sold; reduce qty and remaining by original qty (qty will be 0)
// and keep record. Quantity may go negative because it was used in a COGS calculation but will be corrected when
// new inventory has been received and the associated cost applied. If the quantity is changed, the new remaining
// value will be calculated when the updated purchase/receive is posted.
switch ($this->journal_id) { // journals that don't affect inventory, return now
case 2:
case 3:
case 9:
case 18:
case 20:
if (DEBUG) $messageStack->debug(" end unPosting Inventory with no action.");
return true;
case 6:
case 7:
case 12:
case 13:
case 14:
case 16:
// case 19:
// case 21:
// Delete all owed cogs entries (will be re-added during post)
$db->Execute("delete from " . TABLE_INVENTORY_COGS_OWED . " where journal_main_id = " . $this->id);
if (!$this->rollback_COGS($this->journal_rows[$i]['serialize_number'])) return false;
break;
default: // continue to unPost inventory
}
// prepare some variables
switch ($this->journal_id) {
case 4:
case 6:
// case 21:
case 7:
$db_field = 'quantity_on_order';
break;
default:
$db_field = 'quantity_on_sales_order';
}
for ($i = 0; $i < count($this->journal_rows); $i++) if ($this->journal_rows[$i]['sku']) {
switch ($this->journal_id) {
case 4:
case 10:
if ($this->closed) { // special case if closing an order and a balance remains, reinstate balance
$item_array = $this->load_so_po_balance($this->id, '', false);
$bal_before_post = $item_array[$this->journal_rows[$i]['id']]['ordered'] - $item_array[$this->journal_rows[$i]['id']]['processed'];
if ($bal_before_post > 0) {
if(!$this->update_inventory_status($this->journal_rows[$i]['sku'], $db_field, $bal_before_post)) return false;
}
}
if(!$this->update_inventory_status($this->journal_rows[$i]['sku'], $db_field, -$this->journal_rows[$i]['qty'])) return false;
break;
case 6:
case 7:
case 12:
case 13:
case 14:
case 16:
// case 19:
// case 21:
// check to see if any future postings relied on this record, queue to re-post if so.
$sql = "select id from " . TABLE_INVENTORY_HISTORY . "
where ref_id = " . $this->id . " and sku = '" . $this->journal_rows[$i]['sku'] . "'";
$result = $db->Execute($sql);
if ($result->RecordCount() > 0) {
$sql = "select journal_main_id from " . TABLE_INVENTORY_COGS_USAGE . " where inventory_history_id = " . $result->fields['id'];
$result = $db->Execute($sql);
while (!$result->EOF) {
if ($result->fields['journal_main_id'] <> $this->id) {
if (DEBUG) $messageStack->debug("\nunPost Inventory is queing ID: " . $result->fields['journal_main_id'] . " to re-post.");
$this->repost_ids[$result->fields['journal_main_id']] = $result->fields['journal_main_id'];
}
$result->MoveNext();
}
}
switch ($this->journal_id) {
case 7: // vendor credit memo - negate qty
case 12: // customer sales - negate quantity
// case 19: // customer POS - negate quantity
$qty = -$this->journal_rows[$i]['qty'];
break;
default:
$qty = $this->journal_rows[$i]['qty'];
}
if(!$this->update_inventory_status($this->journal_rows[$i]['sku'], 'quantity_on_hand', -$qty)) return false;
// adjust po/so inventory, if necessary, based on min of qty on ordered and qty shipped/received
if ($this->journal_rows[$i]['so_po_item_ref_id']) {
$item_array = $this->load_so_po_balance($this->so_po_ref_id, $this->id, false);
$bal_before_post = $item_array[$this->journal_rows[$i]['so_po_item_ref_id']]['ordered'] - $item_array[$this->journal_rows[$i]['so_po_item_ref_id']]['processed'];
// do not allow qty on order to go below zero.
$adjustment = min($this->journal_rows[$i]['qty'], $bal_before_post);
if(!$this->update_inventory_status($this->journal_rows[$i]['sku'], $db_field, $adjustment)) return false;
}
break;
default:
}
}
// remove the inventory history records
$db->Execute("delete from " . TABLE_INVENTORY_HISTORY . " where ref_id = " . $this->id);
$db->Execute("delete from " . TABLE_INVENTORY_COGS_USAGE . " where journal_main_id = " . $this->id);
// remove cost of goods sold records (will be re-calculated if re-posting)
$this->remove_journal_COGS_entries();
if (DEBUG) $messageStack->debug("\n end unPosting Inventory.");
return true;
}
// ********* inventory support functions **********
function update_inventory_status($sku, $field, $adjustment, $item_cost = 0, $desc = '', $full_price = 0) {
global $db, $messageStack;
if (!$sku) return true;
if (DEBUG) $messageStack->debug("\n update_inventory_status, SKU = " . $sku . ", field = " . $field . ", adjustment = " . $adjustment . ", and item_cost = " . $item_cost);
// catch sku's that are not in the inventory database but have been requested to post
$result = $db->Execute("select id, inventory_type from " . TABLE_INVENTORY . " where sku = '" . $sku . "'");
if ($result->RecordCount() == 0) {
if (!INVENTORY_AUTO_ADD) {
return $this->fail_message(GL_ERROR_UPDATING_INVENTORY_STATUS . $sku);
} else {
$id = $this->inventory_auto_add($sku, $desc, $item_cost, $full_price);
$result->fields['inventory_type'] = 'si';
}
}
$type = $result->fields['inventory_type'];
// only update items that are to be tracked in inventory (non-stock are tracked for PO/SO only)
if (in_array($type, $this->cog_item_types) || ($type == 'ns' && $field <> 'quantity_on_hand')) {
$sql = "update " . TABLE_INVENTORY . " set " . $field . " = " . $field . " + " . $adjustment . ", ";
if ($item_cost) $sql .= "item_cost = " . $item_cost . ", ";
$sql .= "last_journal_date = now() where sku = '" . $sku . "'";
$result = $db->Execute($sql);
}
return true;
}
function calculate_COGS($item, $return_cogs = false) {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Calculating COGS, SKU = " . $item['sku']);
$cogs = 0;
// fetch the additional inventory item fields we need
$sql = "select inactive, inventory_type, account_inventory_wage, account_cost_of_sales, item_cost, cost_method, quantity_on_hand, serialize
from " . TABLE_INVENTORY . " where sku = '" . $item['sku'] . "'";
$result = $db->Execute($sql);
// catch sku's that are not in the inventory database but have been requested to post, error
if ($result->RecordCount() == 0) {
if (!INVENTORY_AUTO_ADD) {
return $this->fail_message(GL_ERROR_CALCULATING_COGS);
} else {
$item_cost = 0;
$full_price = 0;
switch ($this->journal_id) {
case 6:
case 7:
$item_cost = $item['price']; break;
case 12:
case 13:
$full_price = $item['price']; break;
default:
return $this->fail_message(GL_ERROR_CALCULATING_COGS);
}
$id = $this->inventory_auto_add($item['sku'], $item['description'], $item_cost, $full_price);
$result = $db->Execute($sql); // re-load now that item was created
}
}
// only calculate cogs for certain inventory_types
if (!in_array($result->fields['inventory_type'], $this->cog_item_types)) {
if (DEBUG) $messageStack->debug(". Exiting COGS, no work to be done with this SKU.");
return true;
}
$defaults = $result->fields;
if (ENABLE_MULTI_BRANCH) $defaults['quantity_on_hand'] = $this->branch_qty_on_hand($item['sku'], $defaults['quantity_on_hand']);
// catch sku's that are serialized and the quantity is not one, error
if ($defaults['serialize'] && abs($item['qty']) <> 1) return $this->fail_message(GL_ERROR_SERIALIZE_QUANTITY);
if ($defaults['serialize'] && !$item['serialize_number']) return $this->fail_message(GL_ERROR_SERIALIZE_EMPTY);
if ($item['qty'] > 0) { // for positive quantities, inventory received, customer credit memos, unbuild assembly
// if insert, enter SYSTEM ENTRY COGS cost only if inv on hand is negative
// update will never happen because the entries are removed during the unpost operation.
switch ($this->journal_id) {
case 13: // for customer credit memos, the price needs to be the last unit_cost, not the credit memo price (customers price)
$item['price'] = $defaults['item_cost'];
$credit_cogs = true; // force the restoration (credit) of cogs
break;
case 14: // for un-build assemblies, force the cogs calculation
$credit_cogs = true; // force the restoration (credit) of cogs
break;
default: // for all other journals, use the cost as entered to calculate added inventory
$credit_cogs = false;
}
if ($defaults['quantity_on_hand'] >= 0) { // no COGS except credit memo, add to inventory
if ($credit_cogs) $cogs = $item['qty'] * $item['price']; // only credit memo affects cogs in this case
} else {
// adjust remaining quantity and calculate cogs since initial balance was less than zero (cogs owed)
// this will never happen with serialized items since they cannot be sold before they arrive (serial number unknown)
// find adjustments/sales that caused the inventory to go negative and queue to re-post to calculate cogs
$sql = "select id, journal_main_id, qty from " . TABLE_INVENTORY_COGS_OWED . "
where sku = '" . $item['sku'] . "'";
if (ENABLE_MULTI_BRANCH) $sql .= " and store_id = " . $this->store_id;
$sql .= " order by post_date";
$result = $db->Execute($sql);
$working_qty = $item['qty'];
while (!$result->EOF) {
$working_qty -= $result->fields['qty'];
if ($working_qty >= 0) { // repost this journal entry and remove the owed record since we will repost all the negative quantities necessary
if (DEBUG) $messageStack->debug("\nCOGS calculation is queing ID: " . $result->fields['journal_main_id'] . " to re-post.");
$this->repost_ids[$result->fields['journal_main_id']] = $result->fields['journal_main_id'];
$db->Execute("delete from " . TABLE_INVENTORY_COGS_OWED . " where id = " . $result->fields['id']);
}
if ($working_qty <= 0) break; // we are finished listing all records that will be affected by this inv receipt
$result->MoveNext();
}
}
if ($credit_cogs) $cogs = -$cogs; // credit memo subtracts from cogs
// adjust remaining quantities for inventory history since stock was negative
$history_array = array(
'sku' => $item['sku'],
'qty' => $item['qty'],
'unit_cost' => $item['price'],
'post_date' => $this->post_date,
'remaining' => $item['qty'],
'ref_id' => $this->id,
'store_id' => $this->store_id);
if ($defaults['serialize']) $history_array['serialize_number'] = $item['serialize_number'];
$result = db_perform(TABLE_INVENTORY_HISTORY, $history_array, 'insert');
if ($result->AffectedRows() <> 1) return $this->fail_message(GL_ERROR_POSTING_INV_HISTORY);
} else { // for negative quantities, i.e. sales, negative inv adjustments, assemblies, vendor credit memos
// if insert, calculate COGS pulling from one or more history records (inv may go negative)
// update should never happen because COGS is backed out during the unPost inventory function
$working_qty = -$item['qty']; // quantity needs to be positive
$history_ids = array(); // the id's used to calculated cogs from the inventory history table
switch ($defaults['cost_method']) {
case 'a': // Average
case 'f': // First-In First-Out
$disp_order = 'post_date'; break;
case 'l': // Last-In First-Out
$disp_order = 'post_date DESC'; break;
default: return $this->fail_message('Bad cost_method in classes/gen_ledger function calculate_COGS');
}
if ($defaults['serialize']) { // there should only be one record with one remaining quantity
$sql = "select id, remaining, remaining as total_remain, unit_cost, unit_cost as total_cost
from " . TABLE_INVENTORY_HISTORY . "
where sku = '" . $item['sku'] . "' and remaining > 0 and serialize_number = '" . $item['serialize_number'] . "'";
$result = $db->Execute($sql);
if ($result->RecordCount() <> 1) return $this->fail_message(GL_ERROR_SERIALIZE_COGS);
} else {
$sql = "select id, remaining, sum(remaining) as total_remain, unit_cost, sum(unit_cost) as total_cost
from " . TABLE_INVENTORY_HISTORY . " where sku = '" . $item['sku'] . "' and remaining > 0";
if (ENABLE_MULTI_BRANCH) $sql .= " and store_id = " . $this->store_id;
$sql .= " group by id order by $disp_order";
$result = $db->Execute($sql);
}
while (!$result->EOF) { // loops until either qty is zero and/or inventory history is exhausted
if ($defaults['cost_method'] == 'a') { // Average cost
switch ($this->journal_id) {
case 7: // vendor credit memo, just need the difference in return price from average price
case 14: // assembly, just need the difference in assemble price from piece price
$cost = ($result->fields['total_cost'] / $result->fields['total_remain']) - $item['price'];
break;
default:
$cost = $result->fields['total_cost'] / $result->fields['total_remain'];
}
} else { // FIFO, LIFO
switch ($this->journal_id) {
case 7: // vendor credit memo, just need the difference in return price from purchase price
case 14: // assembly, just need the difference in assemble price from piece price
$cost = $result->fields['unit_cost'] - $item['price'];
break;
default:
$cost = $result->fields['unit_cost']; // for the specific history record
}
}
// Calculate COGS and adjust remaining levels based on costing method and history
// there are two possibilities, inventory is in stock (deduct from inventory history)
// or inventory is out of stock (balance goes negative, COGS to be calculated later)
if ($working_qty <= $result->fields['remaining']) { // this history record has enough to fill request
$cost_qty = $working_qty;
$working_qty = 0;
$exit_loop = true;
} else { // qty will span more than one history record, just calculate for this record
$cost_qty = $result->fields['remaining'];
$working_qty -= $result->fields['remaining'];
$exit_loop = false;
}
// save the history record id used along with the quantity for roll-back purposes
$history_ids[] = array('id' => $result->fields['id'], 'qty' => $cost_qty); // how many from what id
$cogs += $cost * $cost_qty;
$sql = "update " . TABLE_INVENTORY_HISTORY . " set remaining = remaining - " . $cost_qty . " where id = " . $result->fields['id'];
$db->Execute($sql);
if ($exit_loop) break;
$result->MoveNext();
}
for ($i = 0; $i < count($history_ids); $i++) {
$sql_data_array = array(
'inventory_history_id' => $history_ids[$i]['id'],
'qty' => $history_ids[$i]['qty'],
'journal_main_id' => $this->id,
);
db_perform(TABLE_INVENTORY_COGS_USAGE, $sql_data_array, 'insert');
}
// see if there is quantity left to account for but nothing left in inventory (less than zero inv balance)
if ($working_qty > 0) {
// for now, estimate the cost based on the unit_price of the item, will be re-posted (corrected) when product arrives
$result = $db->Execute("select item_cost from " . TABLE_INVENTORY . " where sku = '" . $item['sku'] . "'");
switch ($this->journal_id) {
case 7: // vendor credit memo, just need the difference in return price from purchase price
case 14: // assembly, just need the difference in assemble price from piece price
$cost = $result->fields['item_cost'] - $item['price'];
break;
default:
$cost = $result->fields['item_cost']; // for the specific history record
}
$cogs += $cost * $working_qty;
// queue the journal_main_id to be re-posted later after inventory is received
$sql_data_array = array(
'journal_main_id' => $this->id,
'sku' => $item['sku'],
'qty' => $working_qty,
'post_date' => $this->post_date,
'store_id' => $this->store_id,
);
$result = db_perform(TABLE_INVENTORY_COGS_OWED, $sql_data_array, 'insert');
}
}
if ($return_cogs) return $cogs; // just calculate cogs and adjust inv history
// fill the journal array with the cogs sold entry and inventory entry, update this->object
if ($cogs) {
if (DEBUG) $messageStack->debug("\n Adding COGS to journal, calculated value = " . $cogs);
$temp_array = array(
'ref_id' => $this->id,
'gl_type' => 'cog', // code for cost of goods charges
'description' => GL_JOURNAL_ENTRY_COGS,
'gl_account' => $defaults['account_inventory_wage'],
'post_date' => $this->post_date);
if ($cogs >= 0 ) {
$temp_array['credit_amount'] = $cogs;
} else {
$temp_array['debit_amount'] = -$cogs;
}
db_perform(TABLE_JOURNAL_ITEM, $temp_array, 'insert');
$temp_array['id'] = db_insert_id();
$this->journal_rows[] = $temp_array;
$temp_array = array(
'ref_id' => $this->id,
'gl_type' => 'cog', // code for cost of goods charges
'description' => GL_JOURNAL_ENTRY_COGS,
'gl_account' => (($this->override_cogs_acct) ? $this->override_cogs_acct : $defaults['account_cost_of_sales']),
'post_date' => $this->post_date);
if ($cogs >= 0 ) {
$temp_array['debit_amount'] = $cogs;
} else {
$temp_array['credit_amount'] = -$cogs;
}
db_perform(TABLE_JOURNAL_ITEM, $temp_array, 'insert');
$temp_array['id'] = db_insert_id();
$this->journal_rows[] = $temp_array;
}
if (DEBUG) $messageStack->debug(" ... Finished calculating COGS.");
return true;
}
// Rolling back cost of goods sold required to unpost an entry involves only re-setting the inventory history.
// The cogs records and costing is reversed in the unPost_chart_balances function.
function rollback_COGS($serial_number = '') {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Rolling back COGS ... ");
// only calculate cogs for certain inventory_types
$sql = "select id, qty, inventory_history_id from " . TABLE_INVENTORY_COGS_USAGE . " where journal_main_id = " . $this->id;
$result = $db->Execute($sql);
if ($result->EOF) {
if (DEBUG) $messageStack->debug(" ...Exiting COGS, no work to be done.");
return true;
}
while(!$result->EOF) {
$sql = "update " . TABLE_INVENTORY_HISTORY . "
set remaining = remaining + " . $result->fields['qty'] . "
where id = " . $result->fields['inventory_history_id'];
$db->Execute($sql);
$result->MoveNext();
}
if (DEBUG) $messageStack->debug(" ... Finished rolling back COGS");
return true;
}
function load_so_po_balance($ref_id, $id = '', $post = true) {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Starting to load SO/PO balances ...");
$item_array = array();
if ($ref_id) {
switch ($this->journal_id) {
case 4:
case 6:
case 7: $gl_type = 'poo'; $proc_type = 'por'; break;
// case 21: $gl_type = 'poo'; $proc_type = 'por'; break;
case 10:
case 12:
case 13: $gl_type = 'soo'; $proc_type = 'sos'; break;
// case 19: $gl_type = 'soo'; $proc_type = 'sos'; break;
default: return $this->fail_message('Error in classes/gen_ledger, function load_so_po_balance. Bad $journal_id for this function.');
}
// start by retrieving the po/so item list
$sql = "select id, sku, qty from " . TABLE_JOURNAL_ITEM . "
where ref_id = " . $ref_id . " and gl_type = '" . $gl_type . "'";
$result = $db->Execute($sql);
while(!$result->EOF) {
if ($result->fields['sku']) $item_array[$result->fields['id']]['ordered'] = $result->fields['qty'];
$result->MoveNext();
}
// retrieve the total number of units processed (received/shipped) less this order (may be multiple sales/purchases)
$sql = "select i.so_po_item_ref_id as id, i.sku, i.qty
from " . TABLE_JOURNAL_MAIN . " m left join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id
where m.so_po_ref_id = " . $ref_id . " and i.gl_type = '" . $proc_type . "'";
if (!$post && $id) $sql .= " and m.id <> " . $id; // unposting so don't include current id (journal_id = 6 or 12)
$result = $db->Execute($sql);
while(!$result->EOF) {
if ($result->fields['sku']) $item_array[$result->fields['id']]['processed'] += $result->fields['qty'];
$result->MoveNext();
}
}
$this->so_po_balance_array = $item_array;
if (DEBUG) { $messageStack->debug(" Finished loading SO/PO balances = "); $messageStack->debug(arr2string($item_array)); }
return $item_array;
}
function remove_journal_COGS_entries() {
$temp_array = $this->journal_rows;
$this->journal_rows = array();
for ($i=0; $i<count($temp_array); $i++) {
if ($temp_array[$i]['gl_type'] == 'cog') continue; // skip row - they are re-calculated later
if ($temp_array[$i]['gl_type'] == 'asi') continue; // skip row - they are re-calculated later
$this->journal_rows[] = $temp_array[$i];
}
}
function calculate_assembly_list($inv_list) {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Calculating Assembly item list, SKU = " . $inv_list['sku']);
$sku = $inv_list['sku'];
$qty = $inv_list['qty'];
$result = $db->Execute("select id from " . TABLE_INVENTORY . " where sku = '" . $sku . "'");
if ($result->RecordCount() == 0) return $this->fail_message(GL_ERROR_BAD_SKU_ENTERED);
$sku_id = $result->fields['id'];
$sql = "select a.sku, a.description, a.qty, i.quantity_on_hand, i.account_inventory_wage, i.item_cost as price
from " . TABLE_INVENTORY_ASSY_LIST . " a inner join " . TABLE_INVENTORY . " i on a.sku = i.sku
where a.ref_id = " . $sku_id;
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) return $this->fail_message(GL_ERROR_SKU_NOT_ASSY . $sku);
$assy_cost = 0;
while (!$result->EOF) {
if ($result->fields['quantity_on_hand'] < ($qty * $result->fields['qty'])) {
if (DEBUG) $messageStack->debug("\n Not enough of SKU = " . $result->fields['sku'] . " needed " . ($qty * $result->fields['qty']) . " and had " . $result->fields['quantity_on_hand']);
return $this->fail_message(GL_ERROR_NOT_ENOUGH_PARTS . $result->fields['sku']);
}
$result->fields['qty'] = -($qty * $result->fields['qty']);
$result->fields['id'] = $this->journal_rows[0]['id']; // placeholder ref_id
if ($qty > 0) $result->fields['price'] = 0; // remove unit_price for builds, leave for unbuilds (to calc delta COGS)
$item_cost = $this->calculate_COGS($result->fields, $return_cogs = true);
if ($item_cost === false) return false; // error in cogs calculation
$assy_cost += $item_cost;
// generate inventory assembly part record and insert into db
$temp_array = array(
'ref_id' => $this->id,
'gl_type' => 'asi', // assembly item code
'sku' => $result->fields['sku'],
'qty' => $result->fields['qty'],
'description' => $result->fields['description'],
'gl_account' => $result->fields['account_inventory_wage'],
'post_date' => $this->post_date);
if ($qty < 0) {
$temp_array['debit_amount'] = -$item_cost;
} else {
$temp_array['credit_amount'] = $item_cost;
}
db_perform(TABLE_JOURNAL_ITEM, $temp_array, 'insert');
$temp_array['id'] = db_insert_id();
$this->journal_rows[] = $temp_array;
if ($qty < 0) { // unbuild assy, update ref_id pointer in inventory history record of newly added item (just like a receive)
$db->Execute("update " . TABLE_INVENTORY_HISTORY . " set ref_id = " . $temp_array['id'] . "
where sku = '" . $temp_array['sku'] . "' and ref_id = " . $result->fields['id']);
}
$result->MoveNext();
}
// update assembled item with total cost
$id = $this->journal_rows[0]['id'];
if ($qty < 0) { // the item to assemble should be the first item record
$this->journal_rows[0]['credit_amount'] = -$assy_cost;
$fields = array('credit_amount' => -$assy_cost);
} else {
$this->journal_rows[0]['debit_amount'] = $assy_cost;
$fields = array('debit_amount' => $assy_cost);
}
$result = db_perform(TABLE_JOURNAL_ITEM, $fields, 'update', "id = " . (int)$id);
$inv_list['price'] = $assy_cost / $qty; // insert the assembly cost of materials - unit price
// Adjust inventory levels for assembly, if unbuild, also calcuate COGS differences
if ($this->calculate_COGS($inv_list, $return_cogs = ($qty < 0) ? false : true) === false) return false;
return true;
}
function branch_qty_on_hand($sku, $current_qty_in_stock = 0) {
global $db;
$sql = "select sum(remaining) as remaining from " . TABLE_INVENTORY_HISTORY . "
where store_id = " . $this->store_id . " and sku = '" . $sku . "'";
$result = $db->Execute($sql);
$store_bal = $result->fields['remaining'];
$sql = "select sum(qty) as qty from " . TABLE_INVENTORY_COGS_OWED . "
where store_id = " . $this->store_id . " and sku = '" . $sku . "'";
$result = $db->Execute($sql);
$qty_owed = $result->fields['qty'];
return ($store_bal - $qty_owed);
}
function inventory_auto_add($sku, $desc, $item_cost = 0, $full_price = 0) {
$sql_array = array(
'sku' => $sku,
'inventory_type' => 'si',
'description_short' => $desc,
'description_purchase' => $desc,
'account_sales_income' => INV_STOCK_DEFAULT_SALES,
'account_inventory_wage' => INV_STOCK_DEFAULT_INVENTORY,
'account_cost_of_sales' => INV_STOCK_DEFAULT_COS,
'cost_method' => INV_STOCK_DEFAULT_COSTING,
'item_cost' => $item_cost,
'full_price' => $full_price,
'creation_date' => date('Y-m-d h:i:s', time()),
);
$result = db_perform(TABLE_INVENTORY, $sql_array, 'insert');
return db_insert_id();
}
/*******************************************************************************************************************/
// END Inventory Functions
/*******************************************************************************************************************/
// START General Functions
/*******************************************************************************************************************/
function build_journal_main_array() { // maps/prepares the fields to the journal_main fields
$main_record = array();
if (isset($this->id)) if ($this->id) $main_record['id'] = $this->id; // retain id if known for re-post references
if (isset($this->period)) $main_record['period'] = $this->period;
if (isset($this->journal_id)) $main_record['journal_id'] = $this->journal_id;
if (isset($this->post_date)) $main_record['post_date'] = $this->post_date;
if (isset($this->store_id)) $main_record['store_id'] = $this->store_id;
$main_record['description'] = (isset($this->description)) ? $this->description : constant('GENERAL_JOURNAL_' . $this->journal_id . '_DESC');
if (isset($this->closed)) $main_record['closed'] = $this->closed;
if (isset($this->freight)) $main_record['freight'] = $this->freight;
if (isset($this->shipper_code)) $main_record['shipper_code'] = $this->shipper_code;
if (isset($this->terms)) $main_record['terms'] = $this->terms;
if (isset($this->sales_tax)) $main_record['sales_tax'] = $this->sales_tax;
// if (isset($this->sales_tax_auths)) $main_record['tax_auths'] = $this->sales_tax_auths;
if (isset($this->total_amount)) $main_record['total_amount'] = $this->total_amount;
if (isset($this->currencies_code)) $main_record['currencies_code'] = $this->currencies_code;
if (isset($this->currencies_value)) $main_record['currencies_value'] = $this->currencies_value;
if (isset($this->so_po_ref_id)) $main_record['so_po_ref_id'] = $this->so_po_ref_id;
if (isset($this->purchase_invoice_id)) $main_record['purchase_invoice_id'] = $this->purchase_invoice_id;
if (isset($this->purch_order_id)) $main_record['purch_order_id'] = $this->purch_order_id;
if (isset($this->dept_rep_id)) $main_record['dept_rep_id'] = $this->dept_rep_id;
if (isset($this->waiting)) $main_record['waiting'] = $this->waiting;
if (isset($this->gl_acct_id)) $main_record['gl_acct_id'] = $this->gl_acct_id;
if (isset($this->bill_acct_id)) $main_record['bill_acct_id'] = $this->bill_acct_id;
if (isset($this->bill_address_id)) $main_record['bill_address_id'] = $this->bill_address_id;
if (isset($this->bill_primary_name)) $main_record['bill_primary_name'] = $this->bill_primary_name;
if (isset($this->bill_contact)) $main_record['bill_contact'] = $this->bill_contact;
if (isset($this->bill_address1)) $main_record['bill_address1'] = $this->bill_address1;
if (isset($this->bill_address2)) $main_record['bill_address2'] = $this->bill_address2;
if (isset($this->bill_city_town)) $main_record['bill_city_town'] = $this->bill_city_town;
if (isset($this->bill_state_province)) $main_record['bill_state_province'] = $this->bill_state_province;
if (isset($this->bill_postal_code)) $main_record['bill_postal_code'] = $this->bill_postal_code;
if (isset($this->bill_country_code)) $main_record['bill_country_code'] = $this->bill_country_code;
if (isset($this->bill_telephone1)) $main_record['bill_telephone1'] = $this->bill_telephone1;
if (isset($this->bill_email)) $main_record['bill_email'] = $this->bill_email;
if (isset($this->ship_acct_id)) $main_record['ship_acct_id'] = $this->ship_acct_id;
if (isset($this->ship_address_id)) $main_record['ship_address_id'] = $this->ship_address_id;
if (isset($this->ship_primary_name)) $main_record['ship_primary_name'] = $this->ship_primary_name;
if (isset($this->ship_contact)) $main_record['ship_contact'] = $this->ship_contact;
if (isset($this->ship_address1)) $main_record['ship_address1'] = $this->ship_address1;
if (isset($this->ship_address2)) $main_record['ship_address2'] = $this->ship_address2;
if (isset($this->ship_city_town)) $main_record['ship_city_town'] = $this->ship_city_town;
if (isset($this->ship_state_province)) $main_record['ship_state_province'] = $this->ship_state_province;
if (isset($this->ship_postal_code)) $main_record['ship_postal_code'] = $this->ship_postal_code;
if (isset($this->ship_country_code)) $main_record['ship_country_code'] = $this->ship_country_code;
if (isset($this->ship_telephone1)) $main_record['ship_telephone1'] = $this->ship_telephone1;
if (isset($this->ship_email)) $main_record['ship_email'] = $this->ship_email;
if (isset($this->terminal_date)) $main_record['terminal_date'] = $this->terminal_date;
if (isset($this->drop_ship)) $main_record['drop_ship'] = $this->drop_ship;
if (isset($this->recur_id)) $main_record['recur_id'] = $this->recur_id;
return $main_record;
}
function remove_cogs_rows() {
global $messageStack;
if (DEBUG) $messageStack->debug("\n Removing system generated gl rows. Started with " . count($this->journal_rows) . " rows ");
// remove these types of rows since they are regenerated as part of the Post
$removal_gl_types = array('cog', 'asi');
$temp_rows = array();
foreach ($this->journal_rows as $key => $value) {
if (!in_array($value['gl_type'], $removal_gl_types)) $temp_rows[] = $value;
}
$this->journal_rows = $temp_rows;
if (DEBUG) $messageStack->debug(" and ended with " . count($this->journal_rows) . " rows.");
}
function check_for_closed_po_so($action = 'Post') {
global $db, $currencies, $messageStack;
// closed can occur many ways including:
// forced closure through so/po form (from so/po journal - adjust qty on so/po)
// all quantities are reduced to zero (from so/po journal - should be deleted instead but it's possible)
// editing quantities on po/so to match the number received (from po/so journal)
// receiving all (or more) po/so items through one or more purchases/sales (from purchase/sales journal)
if (DEBUG) $messageStack->debug("\n Checking for closed entry. action = " . $action);
switch ($this->journal_id) {
case 4: $gl_type = 'poo';
// continue like sales order
case 10: if (!$gl_type) $gl_type = 'soo';
// determine if shipped/received items are still outstanding
$ordr_diff = false;
if (is_array($this->so_po_balance_array)) {
foreach($this->so_po_balance_array as $counts) {
if ($counts['ordered'] > $counts['processed']) $ordr_diff = true;
}
}
// determine if all items quantities have been entered as zero
$item_rows_all_zero = true;
for ($i = 0; $i < count($this->journal_rows); $i++) {
if ($this->journal_rows[$i]['qty'] && $this->journal_rows[$i]['gl_type'] == $gl_type) $item_rows_all_zero = false; // at least one qty is non-zero
}
// also close if the 'Close' box was checked
if (!$ordr_diff || $item_rows_all_zero || $this->closed) $this->close_so_po($this->id, true);
break;
case 6:
case 12:
// case 19:
// case 21:
if ($this->so_po_ref_id) { // make sure there is a reference po/so to check
$ordr_diff = false;
if (is_array($this->so_po_balance_array)) {
foreach($this->so_po_balance_array as $key => $counts) {
if ($counts['ordered'] > $counts['processed']) $ordr_diff = true;
}
} else {
$ordr_diff = true; // force open since balance array is empty
}
if ($ordr_diff) { // open it, there are still items to be processed
$this->close_so_po($this->so_po_ref_id, false);
} else { // close the order
$this->close_so_po($this->so_po_ref_id, true);
}
}
// close if the invoice/inv receipt total is zero
if (round($this->total_amount, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']) == 0) {
$this->close_so_po($this->id, true);
}
break;
case 18: //$gl_type = 'pmt';
// continue like payment
case 20: //if (!$gl_type) $gl_type = 'chk';
if ($action == 'Post') {
$temp = array();
for ($i = 0; $i < count($this->journal_rows); $i++) { // fetch the list of paid invoices
if ($this->journal_rows[$i]['so_po_item_ref_id']) {
$temp[$this->journal_rows[$i]['so_po_item_ref_id']] = true;
}
}
$invoices = array_keys($temp);
for ($i = 0; $i < count($invoices); $i++) {
$result = $db->Execute("select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits
from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id
where m.id = " . $invoices[$i] . " and i.gl_type <> 'ttl'");
$total_billed = $result->fields['credits'] - $result->fields['debits'];
$result = $db->Execute("select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits
from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id
where i.so_po_item_ref_id = " . $invoices[$i] . " and i.gl_type in ('pmt', 'chk')");
// where i.so_po_item_ref_id = " . $invoices[$i] . " and i.gl_type = '" . $gl_type . "' and m.journal_id = " . $this->journal_id);
$total_paid = $result->fields['credits'] - $result->fields['debits'];
if (round($total_billed - $total_paid, $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']) == 0) {
$this->close_so_po($invoices[$i], true);
}
}
} else { // unpost - re-open the purchase/invoices affected
for ($i = 0; $i < count($this->journal_rows); $i++) {
if ($this->journal_rows[$i]['so_po_item_ref_id']) {
$this->close_so_po($this->journal_rows[$i]['so_po_item_ref_id'], false);
}
}
}
break;
case 2:
case 3:
case 7:
case 9:
case 13:
case 14:
case 16:
default:
}
return true;
}
function close_so_po($id, $closed) {
global $db, $messageStack;
$sql = "update " . TABLE_JOURNAL_MAIN . " set closed = '" . (($closed) ? '1' : '0') . "' where id = " . $id;
$result = $db->Execute($sql);
if (DEBUG) $messageStack->debug("\n Record ID: " . $this->id . " " . (($closed) ? "Closed Record ID: " : "Opened Record ID: ") . $id);
return;
}
function validate_purchase_invoice_id() {
global $db, $messageStack;
if (DEBUG) $messageStack->debug("\n Start validating purchase_invoice_id ... ");
if ($this->purchase_invoice_id <> '') { // entered a so/po/invoice value, check for dups
switch ($this->journal_id) { // allow for duplicates in the following journals
case 18:
if (DEBUG) $messageStack->debug(" specified ID and dups allowed, returning OK.");
return true; // allow for duplicate deposit ticket ID's
default: // continue
}
$sql = "select purchase_invoice_id from " . TABLE_JOURNAL_MAIN . "
where purchase_invoice_id = '" . $this->purchase_invoice_id . "' and journal_id = '" . $this->journal_id . "'";
if ($this->id) $sql .= " and id <> " . $this->id;
$result = $db->Execute($sql);
if ($result->RecordCount() > 0) return $this->fail_message(constant('GENERAL_JOURNAL_' . $this->journal_id . '_ERROR_2'));
$this->journal_main_array['purchase_invoice_id'] = $this->purchase_invoice_id;
if (DEBUG) $messageStack->debug(" specified ID but no dups, returning OK. ");
} else { // generate a new order/invoice value
switch ($this->journal_id) { // select the field to fetch the next number
case 3: $str_field = 'next_ap_quote_num'; break;
case 4: $str_field = 'next_po_num'; break;
case 6:
case 7: $str_field = ''; break; // not applicable
// case 21: $str_field = ''; break; // not applicable
case 9: $str_field = 'next_ar_quote_num'; break;
case 10: $str_field = 'next_so_num'; break;
case 12: $str_field = 'next_inv_num'; break;
// case 19: $str_field = 'next_inv_num'; break;
case 13: $str_field = 'next_cm_num'; break;
case 18: $str_field = 'next_deposit_num'; break;
case 20: $str_field = 'next_check_num'; break;
}
if ($str_field) {
$result = $db->Execute("select " . $str_field . " from " . TABLE_CURRENT_STATUS . " limit 1");
if (!$result) return $this->fail_message(GL_ERROR_CANNOT_FIND_NEXT_ID);
$this->journal_main_array['purchase_invoice_id'] = $result->fields[$str_field];
} else {
$this->journal_main_array['purchase_invoice_id'] = '';
}
if (DEBUG) $messageStack->debug(" generated ID, returning ID# " . $this->journal_main_array['purchase_invoice_id']);
}
return true;
}
function increment_purchase_invoice_id($force = false) {
global $db;
if ($this->purchase_invoice_id == '' || $force) { // increment the po/so/invoice number
switch ($this->journal_id) { // select the field to increment the number
case 3: $str_field = 'next_ap_quote_num'; break;
case 4: $str_field = 'next_po_num'; break;
case 6:
case 7: $str_field = ''; break; // not applicable
case 9: $str_field = 'next_ar_quote_num'; break;
case 10: $str_field = 'next_so_num'; break;
case 12: $str_field = 'next_inv_num'; break;
case 13: $str_field = 'next_cm_num'; break;
case 18: $str_field = 'next_deposit_num'; break;
// case 19: $str_field = 'next_inv_num'; break;
case 20: $str_field = 'next_check_num'; break;
// case 21: $str_field = 'next_check_num'; break;
}
if ($str_field) {
$next_id = $this->string_increment($this->journal_main_array['purchase_invoice_id']);
$sql = "update " . TABLE_CURRENT_STATUS . " set " . $str_field . " = '" . $next_id . "'";
if (!$force) $sql .= " where " . $str_field . " = '" . $this->journal_main_array['purchase_invoice_id'] . "'";
$result = $db->Execute($sql);
if ($result->AffectedRows() <> 1) return $this->fail_message(constant('GENERAL_JOURNAL_' . $this->journal_id . '_ERROR_5'));
}
}
$this->purchase_invoice_id = $this->journal_main_array['purchase_invoice_id'];
return true;
}
function add_account($type, $acct_id = 0, $address_id = 0, $allow_overwrite = false) {
global $db;
$acct_type = substr($type, 0, 1);
switch (substr($type, 1, 1)) {
case 'b':
case 'm': $add_type = 'bill'; break;
case 's': $add_type = 'ship'; break;
default: return $this->fail_message('Bad account type: ' . $type . ' passed to gen_ledger/classes/gen_ledger.php (add_account)');
}
if ($add_type == 'bill' || $this->drop_ship) { // update or insert new account record, else skip to add address
$short_name = ($add_type == 'bill') ? $this->short_name : $this->ship_short_name;
if (!$short_name) return $this->fail_message(ACT_ERROR_NO_ACCOUNT_ID);
// it id exists, fetch the data, else check for duplicates
$sql = "select id, store_id, dept_rep_id from " . TABLE_CONTACTS . " where ";
$sql .= ($acct_id) ? ("id = " . (int)$acct_id) : ("short_name = '" . $short_name . "' and type = '" . $acct_type . "'");
$result = $db->Execute($sql);
if (!$acct_id && $result->RecordCount() > 0 && !$allow_overwrite) { // duplicate ID w/o allow_overwrite
return $this->fail_message(ACT_ERROR_DUPLICATE_ACCOUNT);
}
$acct_id = $result->fields['id']; // will only change if no id was passed and allow_overwrite is true
$sql_data_array = array();
$sql_data_array['last_update'] = 'now()';
$sql_data_array['store_id'] = isset($this->store_id) ? $this->store_id : $result->fields['store_id'];
$sql_data_array['dept_rep_id'] = isset($this->dept_rep_id) ? $this->dept_rep_id : $result->fields['dept_rep_id'];
if ($result->RecordCount() == 0) { // new account
$sql_data_array['type'] = $acct_type;
$sql_data_array['short_name'] = $short_name;
$sql_data_array['gl_type_account'] = DEF_INV_GL_ACCT;
$sql_data_array['first_date'] = 'now()';
db_perform(TABLE_CONTACTS, $sql_data_array, 'insert');
$acct_id = db_insert_id();
$force_mail_address = true;
} else { // duplicate ID with allow_overwrite
db_perform(TABLE_CONTACTS, $sql_data_array, 'update', 'id = ' . (int)$acct_id);
$force_mail_address = false;
}
}
// address book fields
$sql_data_array = array();
if (!$address_id) { // check for the address already there using criteria_fields to match
$criteria_fields = array('primary_name', 'address1', 'postal_code');
$sql = "select address_id from " . TABLE_ADDRESS_BOOK . " where ";
foreach ($criteria_fields as $name) {
$field_to_test = $add_type . '_' . $name;
$sql .= $name . " = '" . db_input($this->$field_to_test) . "' and ";
}
$sql .= "ref_id = " . $acct_id;
$result = $db->Execute($sql);
$address_id = ($result->RecordCount() > 0) ? $result->fields['address_id'] : '';
}
$add_fields = array('primary_name', 'contact', 'address1', 'address2', 'city_town',
'state_province', 'postal_code', 'country_code', 'telephone1', 'telephone2',
'telephone3', 'telephone4', 'email', 'website');
foreach ($add_fields as $name) {
$field_to_test = $add_type . '_' . $name;
$sql_data_array[$name] = $this->$field_to_test;
}
$sql_data_array['ref_id'] = $acct_id;
if (!$address_id) { // create new address
$sql_data_array['type'] = ($force_mail_address) ? ($acct_type . 'm') : $type;
db_perform(TABLE_ADDRESS_BOOK, $sql_data_array, 'insert');
$address_id = db_insert_id();
} else { // then update address
db_perform(TABLE_ADDRESS_BOOK, $sql_data_array, 'update', 'address_id = ' . (int)$address_id);
}
// update the journal_main array since we could have new id's
switch ($add_type) {
case 'mail':
case 'bill':
$this->journal_main_array['bill_acct_id'] = $acct_id;
$this->journal_main_array['bill_address_id'] = $address_id;
break;
case 'ship':
$this->journal_main_array['ship_acct_id'] = $acct_id;
$this->journal_main_array['ship_address_id'] = $address_id;
break;
default:
}
return $acct_id; // should be either passed id or new id if record was created
}
function get_recur_ids($recur_id) {
global $db;
$output = array();
$result = $db->Execute("select id, post_date, purchase_invoice_id from " . TABLE_JOURNAL_MAIN . "
where recur_id = " . $recur_id . " and post_date >= '" . $this->post_date . "' order by post_date");
while (!$result->EOF) {
$output[] = array('id' => $result->fields['id'],
'post_date' => $result->fields['post_date'],
'purchase_invoice_id' => $result->fields['purchase_invoice_id']);
$result->MoveNext();
}
return $output;
}
function string_increment($string, $increment = 1) {
ereg("(_?)([0-9]+)$", $string, $regs);
$value = empty($regs) ? '' : $regs[2];
$new_value = (int)$value + $increment;
$string = ereg_replace((($value == '') ? "$" : $regs[0] . "$"), (string)$regs[1] . (string)$new_value, $string);
return $string;
}
function fail_message($message) {
global $db, $messageStack;
$db->transRollback();
$messageStack->add($message, 'error');
return false;
}
function session_message($message, $level = 'error') {
global $messageStack;
$messageStack->add_session($message, $level);
}
} // end class journal
?>