function processInventory($upload_name) { global $coa, $db, $currencies, $messageStack; if (!$this->cyberParse($upload_name)) { return false; } $post_date = gen_specific_date(date('Y-m-d'), $day_offset = -1); $glEntry = new journal(); $sku_list = array(); $coa_list = array(); $affected_accounts = array(); for ($row_id = 0, $j = 2; $row_id < count($this->records); $row_id++, $j++) { $row = $this->records[$row_id]; $total_amount = $currencies->clean_value($row['total_amount']); $qty = $currencies->clean_value($row['quantity']); // check for errors and report/exit if error found if (!gen_validate_sku($row['sku'])) { $messageStack->add(GL_ERROR_UPDATING_INVENTORY_STATUS . $row['sku'] . GL_BEG_BAL_ERROR_0 . $j, 'error'); return false; } if (!in_array($row['inv_gl_acct'], $coa) || !in_array($row['gl_acct'], $coa)) { $messageStack->add(GL_BEG_BAL_ERROR_6 . $j, 'error'); return false; } if ($qty == 0) { $messageStack->add(GL_BEG_BAL_ERROR_7 . $j, 'caution'); } else { $affected_accounts[$row['inv_gl_acct']] = true; // need list of accounts to update history $affected_accounts[$row['gl_acct']] = true; // both credit and debit $sku_list[$row['sku']]['qty'] += $qty; // load quantity indexed by sku $sku_list[$row['sku']]['total'] += $total_amount; // load total_value indexed by sku $coa_list[$row['inv_gl_acct']] += $total_amount; // add to debit total by coa $coa_list[$row['gl_acct']] -= $total_amount; // add to credit total by coa } } if (is_array($sku_list)) { $glEntry->affected_accounts = $affected_accounts; // *************** START TRANSACTION ************************* $db->transStart(); // update inventory balances on hand foreach ($sku_list as $sku => $details) { $sql = "update " . TABLE_INVENTORY . " set quantity_on_hand = quantity_on_hand + {$details['qty']} where sku = '{$sku}'"; $result = $db->Execute($sql); if ($result->AffectedRows() != 1) { $messageStack->add(sprintf(GL_BEG_BAL_ERROR_8, $sku), 'error'); $db->transRollback(); return false; } $history_array = array('ref_id' => 0, 'sku' => $sku, 'qty' => $details['qty'], 'remaining' => $details['qty'], 'unit_cost' => $details['total'] / $details['qty'], 'avg_cost' => $details['total'] / $details['qty'], 'post_date' => $post_date); $result = db_perform(TABLE_INVENTORY_HISTORY, $history_array, 'insert'); } // update chart of account beginning balances for period 1 foreach ($coa_list as $account => $amount) { $sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " set beginning_balance = beginning_balance + {$amount}\n\t\t\twhere account_id = '{$account}' and period = 1"; $result = $db->Execute($sql); if ($result->AffectedRows() != 1) { $messageStack->add(sprintf(GL_BEG_BAL_ERROR_9, $account), 'error'); $db->transRollback(); return false; } } // update the chart of accounts history through the existing periods if (!$glEntry->update_chart_history_periods($period = 1)) { if (DEBUG) { $messageStack->write_debug(); } return $glEntry->fail_message(GL_ERROR_UPDATE_COA_HISTORY); } $db->transCommit(); // post the chart of account values if (DEBUG) { $messageStack->write_debug(); } // *************** END TRANSACTION ************************* } $this->line_count = $row_id; return true; }
// read and check history db values*/ } } if ($_REQUEST['action'] == 'coa_hist_fix' && sizeof($bad_accounts) > 0) { // *************** START TRANSACTION ************************* $db->transStart(); $glEntry = new journal(); foreach ($bad_accounts as $gl_acct => $acct_array) { $glEntry->affected_accounts[$gl_acct] = 1; foreach ($acct_array as $period => $sql_data_array) { db_perform(TABLE_CHART_OF_ACCOUNTS_HISTORY, $sql_data_array, 'update', "account_id='" . $gl_acct . "' and period=" . $period); } } $min_period = max($first_error_period, 2); // avoid a crash if min_period is the first period if ($glEntry->update_chart_history_periods($min_period - 1)) { // from prior period than the error account $db->transCommit(); $messageStack->add(GEN_ADM_TOOLS_REPAIR_COMPLETE, 'success'); gen_add_audit_log(GEN_ADM_TOOLS_REPAIR_LOG_ENTRY); } } if (sizeof($bad_accounts) == 0) { $messageStack->add(GEN_ADM_TOOLS_REPAIR_SUCCESS, 'success'); } else { $messageStack->add(GEN_ADM_TOOLS_REPAIR_ERROR, 'error'); } if (DEBUG) { $messageStack->write_debug(); } break;
function validate_gl_balances($action) { global $db, $currencies, $messageStack; $fiscal_years = array(); $sql = "select distinct fiscal_year, min(period) as first_period, max(period) as last_period\r\n\t from " . TABLE_ACCOUNTING_PERIODS . " group by fiscal_year order by fiscal_year ASC"; $result = $db->Execute($sql); while (!$result->EOF) { $fiscal_years[] = array('fiscal_year' => $result->fields['fiscal_year'], 'first_period' => $result->fields['first_period'], 'last_period' => $result->fields['last_period']); $result->MoveNext(); } $beg_bal = array(); $bad_accounts = array(); foreach ($fiscal_years as $fiscal_year) { $sql = "select account_id, period, beginning_balance, (beginning_balance + debit_amount - credit_amount) as next_beg_bal\r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\twhere period >= " . $fiscal_year['first_period'] . " and period <= " . $fiscal_year['last_period'] . " \r\n\t\torder by period, account_id"; $result = $db->Execute($sql); while (!$result->EOF) { $period = $result->fields['period']; $next_period = $period + 1; $gl_account = $result->fields['account_id']; $beg_balance = $currencies->format($result->fields['beginning_balance']); $next_beg_bal = $currencies->format($result->fields['next_beg_bal']); $beg_bal[$next_period][$gl_account] = $next_beg_bal; if ($period != 1 && $beg_bal[$period][$gl_account] != $beg_balance) { if ($action != 'coa_hist_fix') { $messageStack->add(sprintf(GEN_ADM_TOOLS_REPAIR_ERROR_MSG, $period, $gl_account, $beg_bal[$period][$gl_account], $beg_balance), 'caution'); } $bad_accounts[$period][$gl_account] = array('sync' => '1'); } // check posted transactions to account to see if they match $posted = $db->Execute("select sum(debit_amount) as debit, sum(credit_amount) as credit \r\n\t\t from " . TABLE_JOURNAL_MAIN . " m join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t\t where period = " . $period . " and gl_account = '" . $gl_account . "' \r\n\t\t and journal_id in (2, 6, 7, 12, 13, 14, 16, 18, 19, 20, 21)"); $posted_bal = $currencies->format($result->fields['beginning_balance'] + $posted->fields['debit'] - $posted->fields['credit']); if ($posted_bal != $next_beg_bal) { if ($action != 'coa_hist_fix') { $messageStack->add(sprintf(GEN_ADM_TOOLS_REPAIR_ERROR_MSG, $period, $gl_account, $posted_bal, $next_beg_bal), 'caution'); } $bad_accounts[$period][$gl_account] = array('sync' => '1', 'debit' => $posted->fields['debit'], 'credit' => $posted->fields['credit']); } $result->MoveNext(); } // roll the fiscal year balances $result = $db->Execute("select id from " . TABLE_CHART_OF_ACCOUNTS . " where account_type = 44"); $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) { $beg_bal[$next_period][$result->fields['id']] = 0; $acct_list[] = $result->fields['id']; $result->MoveNext(); } // fetch the totals for the closed accounts $sql = "select sum(beginning_balance + debit_amount - credit_amount) as retained_earnings \r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\twhere account_id in ('" . implode("','", $acct_list) . "') and period = " . $period; $result = $db->Execute($sql); $beg_bal[$next_period][$retained_earnings_acct] = $currencies->format($result->fields['retained_earnings']); } if ($action == 'coa_hist_fix') { // find the affected accounts if (sizeof($bad_accounts) > 0) { // *************** START TRANSACTION ************************* $db->transStart(); $glEntry = new journal(); $min_period = 999999; foreach ($bad_accounts as $period => $acct_array) { foreach ($acct_array as $gl_acct => $value) { $min_period = min($period, $min_period); // find first period that has an error $glEntry->affected_accounts[$gl_acct] = 1; if (isset($value['debit'])) { // the history doesn't match posted data, repair $db->Execute("update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\t\t set debit_amount = " . $value['debit'] . ", credit_amount = " . $value['credit'] . " \r\n\t\t\t where period = " . $period . " and account_id = '" . $gl_acct . "'"); } } } $debug = true; if ($glEntry->update_chart_history_periods($min_period - 1)) { // from prior period than the error account $db->transCommit(); $messageStack->add_session(GEN_ADM_TOOLS_REPAIR_COMPLETE, 'success'); gen_add_audit_log(GEN_ADM_TOOLS_REPAIR_LOG_ENTRY); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')) . 'action=coa_hist_test', 'SSL')); } } } if (sizeof($bad_accounts) == 0) { $messageStack->add(GEN_ADM_TOOLS_REPAIR_SUCCESS, 'success'); } else { $messageStack->add(GEN_ADM_TOOLS_REPAIR_ERROR, 'error'); } }
function processOrders($upload_name = 'file_name') { global $db, $messageStack; $period = gen_calculate_period(date('Y-m-d')); if (!defined('JOURNAL_ID')) { define('JOURNAL_ID', 12); } // load the amazon contact record info $result = $db->Execute("SELECT id FROM " . TABLE_CONTACTS . " WHERE short_name='" . MODULE_AMAZON_CUSTOMER_ID . "'"); $cID = $result->fields['id']; if (!$cID) { $messageStack->add("Contact could not be found in the Customer database. Please make sure the setting in the defaults.php file match your Customers value.", 'error'); return; } $result = $db->Execute("SELECT * FROM " . TABLE_ADDRESS_BOOK . " WHERE ref_id={$cID} AND type='cm'"); $commonMain = array('post_date' => date('Y-m-d'), 'period' => $period, 'journal_id' => JOURNAL_ID, 'currencies_code' => DEFAULT_CURRENCY, 'terminal_date' => date('Y-m-d'), 'store_id' => 0, 'admin_id' => $_SESSION['admin_id'], 'rep_id' => 0, 'gl_acct_id' => MODULE_AMAZON_DEFAULT_RECEIVABLES_GL_ACCT, 'bill_acct_id' => $result->fields['ref_id'], 'bill_address_id' => $result->fields['address_id'], 'bill_primary_name' => $result->fields['primary_name'], 'bill_contact' => $result->fields['contact'], 'bill_address1' => $result->fields['address1'], 'bill_address2' => $result->fields['address2'], 'bill_city_town' => $result->fields['city_town'], 'bill_state_province' => $result->fields['state_province'], 'bill_postal_code' => $result->fields['postal_code'], 'bill_country_code' => $result->fields['country_code'], 'bill_telephone1' => $result->fields['telephone1'], 'bill_email' => $result->fields['email'], 'drop_ship' => '1'); $bill_acct_id = $result->fields['ref_id']; // iterate through the map to set journal post variables, orders may be on more than 1 line // ***************************** START TRANSACTION ******************************* $db->transStart(); $itemCnt = 1; $items = array(); $totals = array(); $inStock = true; $orderCnt = 0; $skip = false; $runaway = 0; $rows = file($_FILES[$upload_name]['tmp_name']); $row = array_shift($rows); // heading $this->headings = explode("\t", $row); $row = array_shift($rows); // first order if (!$row) { $messageStack->add("There were no orders to process!", 'caution'); return; } $data = $this->processRow($row); while (true) { if (!$row) { break; } $main = $commonMain; $main['purch_order_id'] = $data['order-id']; $main['description'] = "Amazon Order # " . $data['order-id']; $main['shipper_code'] = MODULE_AMAZON_DEFAULT_SHIPPING_CARRIER; if (strlen($data['recipient-name']) > 32 || strlen($data['ship-address-1']) > 32 || strlen($data['ship-address-2']) > 32) { $messageStack->add(sprintf("Order # %s has a name or address that is too long for the PhreeBooks db and has been truncated: %s", $data['order-id'], $data['recipient-name']), 'caution'); } $main['ship_primary_name'] = $data['recipient-name']; $main['ship_address1'] = $data['ship-address-1']; $main['ship_address2'] = $data['ship-address-2']; $main['ship_contact'] = $data['ship-address-3']; $main['ship_city_town'] = $data['ship-city']; $main['ship_state_province'] = $data['ship-state']; $main['ship_postal_code'] = $data['ship-postal-code']; $main['ship_country_code'] = gen_get_country_iso_3_from_2($data['ship-country']); $main['ship_telephone1'] = $data['buyer-phone-number']; $main['ship_email'] = $data['buyer-email']; // build the item, check stock if auto_journal $inv = $db->Execute("SELECT * FROM " . TABLE_INVENTORY . " WHERE sku='{$data['sku']}'"); $messageStack->debug("\n Executing sql = " . "SELECT * FROM " . TABLE_INVENTORY . " WHERE sku='{$data['sku']}' resulting in:" . print_r($inv->fields, true)); if (!$inv->fields || sizeof($inv->fields) == 0) { $messageStack->add(sprintf("SKU: %s not found in the database, this import was skipped!", $data['sku'])); $skip = true; } else { if ($inv->fields['qty_stock'] < $data['quantity-purchased']) { $inStock = false; } } $items[] = array('item_cnt' => $itemCnt, 'gl_type' => 'sos', 'sku' => $data['sku'], 'qty' => $data['quantity-purchased'], 'description' => $data['product-name'], 'credit_amount' => $data['item-price'], 'gl_account' => $inv->fields['account_sales_income'] ? $inv->fields['account_sales_income'] : MODULE_AMAZON_DEFAULT_SALES_GL_ACCT, 'taxable' => 0, 'full_price' => $inv->fields['full_price'], 'post_date' => substr($data['purchase-date'], 0, 10)); // preset some totals to keep running balance if (!isset($totals['discount'])) { $totals['discount'] = 0; } if (!isset($totals['sales_tax'])) { $totals['sales_tax'] = 0; } if (!isset($totals['total_amount'])) { $totals['total_amount'] = 0; } if (!isset($totals['freight'])) { $totals['freight'] = 0; } // fill in order info $totals['discount'] += $data['item-promotion-discount'] + $data['ship-promotion-discount']; $totals['sales_tax'] += $data['item-tax']; $totals['total_amount'] += $data['item-price'] + $data['item-tax'] + $data['shipping-price'] + $data['shipping-tax']; // missing from file: $data['gift-wrap-price'] and $data['gift-wrap-tax'] $totals['freight'] += $data['shipping-price']; // check for continuation order $row = array_shift($rows); if ($runaway++ > 1000) { $messageStack->add("runaway reached, exiting!", 'error'); break; } if ($row) { // check for continuation order $nextData = $this->processRow($row); // $messageStack->debug("\nContinuing order check, Next order = {$nextData['order-id']} and this order = {$main['purch_order_id']}"); if ($nextData['order-id'] == $main['purch_order_id']) { $data = $nextData; $itemCnt++; continue; // more items for the same order } } // finish main and item to post $main['total_amount'] = $totals['total_amount']; // @todo add tax, shipping, gift wrap, and notes records (add to item array) $items[] = array('qty' => 1, 'gl_type' => 'frt', 'description' => "Shipping Amazon # " . $data['order-id'], 'credit_amount' => $totals['freight'], 'gl_account' => MODULE_AMAZON_DEFAULT_FREIGHT_GL_ACCT, 'taxable' => 0, 'post_date' => substr($data['purchase-date'], 0, 10)); $items[] = array('qty' => 1, 'gl_type' => 'ttl', 'description' => "Total Amazon # " . $data['order-id'], 'debit_amount' => $totals['total_amount'], 'gl_account' => MODULE_AMAZON_DEFAULT_RECEIVABLES_GL_ACCT, 'post_date' => substr($data['purchase-date'], 0, 10)); $dup = $db->Execute("SELECT id FROM " . TABLE_JOURNAL_MAIN . " WHERE purch_order_id='{$main['purch_order_id']}'"); if ($dup->fields['id']) { // $messageStack->debug("duplicate order id = ".$dup->fields['id']." and main = ".print_r($main, true)); $messageStack->add(sprintf("Order # %s has already been imported! It will be skipped.", $data['order-id']), 'caution'); continue; } $ledger = new journal(); $ledger->post_date = substr($data['purchase-date'], 0, 10); $ledger->period = $period; $ledger->closed = '0'; $ledger->journal_id = JOURNAL_ID; $ledger->bill_acct_id = $bill_acct_id; $ledger->journal_main_array = $main; $ledger->journal_rows = $items; if (!$skip) { if (!$ledger->validate_purchase_invoice_id()) { return false; } if (!$ledger->Post('insert')) { return; } $orderCnt++; } // prepare for next order. $data = $nextData; $itemCnt = 1; $items = array(); $totals = array(); $inStock = true; $skip = false; } if ($orderCnt) { if (!$ledger->update_chart_history_periods($period)) { return; } } $db->transCommit(); // finished successfully // ***************************** END TRANSACTION ******************************* $messageStack->add(sprintf("Successfully posted %s Amazon transactions.", $orderCnt), 'success'); if (DEBUG) { $messageStack->write_debug(); } return true; }
$total_amount += $entry; $index++; } // check to see if journal is still in balance $total_amount = $currencies->format($total_amount); if ($total_amount != 0) { $messageStack->add(GL_ERROR_NO_BALANCE, 'error'); break; } // *************** START TRANSACTION ************************* $db->transStart(); foreach ($glEntry->beg_bal as $account => $values) { $sql = "update " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\t\tset beginning_balance = " . $values['beg_bal'] . " \r\n\t\t\twhere period = 1 and account_id = '" . $account . "'"; $result = $db->Execute($sql); } if (!$glEntry->update_chart_history_periods($period = 1)) { // roll the beginning balances into chart history table $glEntry->fail_message(GL_ERROR_UPDATE_COA_HISTORY); } else { $db->transCommit(); // post the chart of account values gen_add_audit_log('Enter Beginning Balances'); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL')); // *************** END TRANSACTION ************************* } $messageStack->add(GL_ERROR_NO_POST, 'error'); break; default: } /***************** prepare to display templates *************************/ $include_header = true;
break; } $result = $db->Execute("select * from " . TABLE_ACCOUNTING_PERIODS . " where period = " . $highest_period); $next_fy = $result->fields['fiscal_year'] + 1; $next_period = $result->fields['period'] + 1; $next_start_date = date('Y-m-d', strtotime($result->fields['end_date']) + 60 * 60 * 24); $highest_period = validate_fiscal_year($next_fy, $next_period, $next_start_date); build_and_check_account_history_records(); // *************** roll account balances into next fiscal year ************************* $glEntry = new journal(); $result = $db->Execute("select id from " . TABLE_CHART_OF_ACCOUNTS); while (!$result->EOF) { $glEntry->affected_accounts[$result->fields['id']] = 1; $result->MoveNext(); } $glEntry->update_chart_history_periods(CURRENT_ACCOUNTING_PERIOD); // from current period through new fiscal year $fy = $next_fy; // set the pointer to open the fiscal year added gen_add_audit_log(GL_LOG_FY_UPDATE . TEXT_ADD); break; case "change": // retrieve the desired period and update the system default values. if ($security_level < 3) { $messageStack->add_session(ERROR_NO_PERMISSION, 'error'); gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL')); break; } $period = (int) db_prepare_input($_POST['period']); if ($period <= 0 || $period > $highest_period) { $messageStack->add(GL_ERROR_BAD_ACCT_PERIOD, 'error');