function validate_fiscal_year($next_fy, $next_period, $next_start_date, $num_periods = 12)
{
global $db;
for ($i = 0; $i < $num_periods; $i++) {
$fy_array = array('period' => $next_period, 'fiscal_year' => $next_fy, 'start_date' => $next_start_date, 'end_date' => gen_specific_date($next_start_date, $day_offset = -1, $month_offset = 1), 'date_added' => date('Y-m-d'));
db_perform(TABLE_ACCOUNTING_PERIODS, $fy_array, 'insert');
$next_period++;
$next_start_date = gen_specific_date($next_start_date, $day_offset = 0, $month_offset = 1);
}
return $next_period--;
}
function add_item_journal_rows($debit_credit)
{
// read in line items and add to journal row array
if ($debit_credit == 'debit' || $debit_credit == 'credit') {
$total = 0;
for ($i = 0; $i < count($this->item_rows); $i++) {
switch ($this->journal_id) {
// determine to pick from the qty or pstd value
case 3:
case 4:
$qty_pstd = 'qty';
$terminal_date = gen_specific_date($this->post_date, $this->item_rows[$i]['lead_time']);
break;
case 9:
case 10:
$qty_pstd = 'qty';
$terminal_date = $this->terminal_date;
break;
case 6:
case 7:
$qty_pstd = 'pstd';
$terminal_date = $this->post_date;
break;
case 12:
case 13:
$qty_pstd = 'pstd';
$terminal_date = $this->terminal_date;
break;
default:
}
if ($this->item_rows[$i][$qty_pstd]) {
// make sure the quantity line is set and not zero
$this->journal_rows[] = array('id' => $this->item_rows[$i]['id'], 'item_cnt' => $this->item_rows[$i]['item_cnt'], 'so_po_item_ref_id' => $this->item_rows[$i]['so_po_item_ref_id'], 'gl_type' => $this->gl_type, 'sku' => $this->item_rows[$i]['sku'], 'qty' => $this->item_rows[$i][$qty_pstd], 'description' => $this->item_rows[$i]['desc'], $debit_credit . '_amount' => $this->item_rows[$i]['total'], 'full_price' => $this->item_rows[$i]['full'], 'gl_account' => $this->item_rows[$i]['acct'], 'taxable' => $this->item_rows[$i]['tax'], 'serialize_number' => $this->item_rows[$i]['serial'], 'project_id' => $this->item_rows[$i]['proj'], 'purch_package_quantity' => $this->item_rows[$i]['purch_package_quantity'], 'post_date' => $this->post_date, 'date_1' => $this->item_rows[$i]['date_1'] ? $this->item_rows[$i]['date_1'] : $terminal_date);
$total += $this->item_rows[$i]['total'];
}
}
return $total;
} else {
die('Bad parameter passed to add_item_journal_rows in class orders!');
}
}
function repost_journals($journals, $start_date, $end_date)
{
global $db, $messageStack;
if (sizeof($journals) > 0) {
$sql = "select id from " . TABLE_JOURNAL_MAIN . " \r\n\t\twhere journal_id in (" . implode(',', $journals) . ") \r\n\t\tand post_date >= '" . $start_date . "' and post_date < '" . gen_specific_date($end_date, 1) . "' \r\n\t\torder by id";
$result = $db->Execute($sql);
$cnt = 0;
$db->transStart();
while (!$result->EOF) {
$gl_entry = new journal($result->fields['id']);
$gl_entry->remove_cogs_rows();
// they will be regenerated during the re-post
if (!$gl_entry->Post('edit', true)) {
$db->transRollback();
$messageStack->add('<br /><br />Failed Re-posting the journals, try a smaller range. The record that failed was # ' . $gl_entry->id, 'error');
return false;
}
$cnt++;
$result->MoveNext();
}
$db->transCommit();
return $cnt;
}
}
case '3':
$month_offset = ($i + 1) * 1;
break;
// Monthly
// Monthly
case '4':
$month_offset = ($i + 1) * 3;
break;
// Quarterly
// Quarterly
case '5':
$year_offset = ($i + 1) * 1;
break;
// Yearly
}
$glEntry->post_date = gen_specific_date($post_date, $day_offset, $month_offset, $year_offset);
$glEntry->period = gen_calculate_period($glEntry->post_date, true);
if (!$glEntry->period && $i < $glEntry->recur_id - 1) {
// recur falls outside of available periods, ignore last calculation
$messageStack->add(ORD_PAST_LAST_PERIOD, 'error');
$error = true;
break;
}
$glEntry->journal_main_array['post_date'] = $glEntry->post_date;
$glEntry->journal_main_array['period'] = $glEntry->period;
$glEntry->purchase_invoice_id = string_increment($glEntry->journal_main_array['purchase_invoice_id']);
}
}
// restore the first values to continue with post process
$glEntry->id = $first_id;
$glEntry->journal_main_array['id'] = $first_id;
function gather_history()
{
global $db;
$dates = gen_get_dates();
$cur_month = $dates['ThisYear'] . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01';
$temp_year = $dates['ThisYear'];
$temp_month = $dates['ThisMonth'];
for ($i = 0; $i < 13; $i++) {
$index = substr($cur_month, 0, 7);
$this->purchases_history[$index] = array('post_date' => $cur_month, 'MonthName' => $dates['MonthName'], 'ThisYear' => $dates['ThisYear'], 'qty' => 0, 'total_amount' => 0);
$this->sales_history[$index] = array('post_date' => $cur_month, 'MonthName' => $dates['MonthName'], 'ThisYear' => $dates['ThisYear'], 'qty' => 0, 'usage' => 0, 'total_amount' => 0);
$cur_month = gen_specific_date($cur_month, 0, -1, 0);
$dates = gen_get_dates($cur_month);
}
$last_year = $temp_year - 1 . '-' . substr('0' . $temp_month, -2) . '-01';
// load the SO's and PO's and get order, expected del date
$sql = "select m.id, m.journal_id, m.store_id, m.purchase_invoice_id, i.qty, i.post_date, i.date_1,\ti.id as item_id \n\t \t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t \t where m.journal_id in (4, 10) and i.sku = '" . $this->sku . "' and m.closed = '0' \n\t \t order by i.date_1";
$result = $db->Execute($sql);
while (!$result->EOF) {
switch ($result->fields['journal_id']) {
case 4:
$gl_type = 'por';
$hist_type = 'open_po';
break;
case 10:
$gl_type = 'sos';
$hist_type = 'open_so';
break;
}
$sql = "select sum(qty) as qty from " . TABLE_JOURNAL_ITEM . " \n\t\t\t where gl_type = '" . $gl_type . "' and so_po_item_ref_id = " . $result->fields['item_id'];
$adj = $db->Execute($sql);
// this looks for partial received to make sure this item is still on order
if ($result->fields['qty'] > $adj->fields['qty']) {
$this->history[$hist_type][] = array('id' => $result->fields['id'], 'store_id' => $result->fields['store_id'], 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'post_date' => $result->fields['post_date'], 'qty' => $result->fields['qty'], 'date_1' => $result->fields['date_1']);
}
$result->MoveNext();
}
// load the units received and sold, assembled and adjusted
$sql = "select m.journal_id, m.post_date, i.qty, i.gl_type, i.credit_amount, i.debit_amount \n\t\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t\t where m.journal_id in (6, 12, 14, 16, 19, 21) and i.sku = '" . $this->sku . "' and m.post_date >= '" . $last_year . "' \n\t\t order by m.post_date DESC";
$result = $db->Execute($sql);
while (!$result->EOF) {
$month = substr($result->fields['post_date'], 0, 7);
switch ($result->fields['journal_id']) {
case 6:
case 21:
$this->purchases_history[$month]['qty'] += $result->fields['qty'];
$this->purchases_history[$month]['total_amount'] += $result->fields['debit_amount'];
break;
case 12:
case 19:
$this->sales_history[$month]['qty'] += $result->fields['qty'];
$this->sales_history[$month]['usage'] += $result->fields['qty'];
$this->sales_history[$month]['total_amount'] += $result->fields['credit_amount'];
break;
case 14:
if ($result->fields['gl_type'] == 'asi') {
// only if part of an assembly
$this->sales_history[$month]['usage'] -= $result->fields['qty'];
// need to negate quantity since assy.
}
break;
case 16:
$this->sales_history[$month]['usage'] += $result->fields['qty'];
break;
}
$result->MoveNext();
}
// calculate average usage
$cnt = 0;
foreach ($this->sales_history as $key => $value) {
if ($cnt == 0) {
$cnt++;
continue;
// skip current month since we probably don't have the full months worth
}
$this->history['averages']['12month'] += $this->sales_history[$key]['usage'];
if ($cnt < 7) {
$this->history['averages']['6month'] += $this->sales_history[$key]['usage'];
}
if ($cnt < 4) {
$this->history['averages']['3month'] += $this->sales_history[$key]['usage'];
}
if ($cnt < 2) {
$this->history['averages']['1month'] += $this->sales_history[$key]['usage'];
}
$cnt++;
}
$this->history['averages']['12month'] = round($this->history['averages']['12month'] / 12, 2);
$this->history['averages']['6month'] = round($this->history['averages']['6month'] / 6, 2);
$this->history['averages']['3month'] = round($this->history['averages']['3month'] / 3, 2);
}
function calculate_aging($id, $type = 'c', $special_terms = '0')
{
global $db;
$output = array();
if (!$id) {
return $output;
}
$today = date('Y-m-d');
$terms = explode(':', $special_terms);
$credit_limit = $terms[4] ? $terms[4] : constant(($type == 'v' ? 'AP' : 'AR') . '_CREDIT_LIMIT_AMOUNT');
$due_days = $terms[3] ? $terms[3] : constant(($type == 'v' ? 'AP' : 'AR') . '_NUM_DAYS_DUE');
$due_date = gen_specific_date($today, -$due_days);
$late_30 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_1 : -AR_AGING_PERIOD_1);
$late_60 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_2 : -AR_AGING_PERIOD_2);
$late_90 = gen_specific_date($today, $type == 'v' ? -AP_AGING_DATE_3 : -AR_AGING_PERIOD_3);
$output = array('balance_0' => '0', 'balance_30' => '0', 'balance_60' => '0', 'balance_90' => '0');
$inv_jid = $type == 'v' ? '6, 7' : '12, 13';
$pmt_jid = $type == 'v' ? '20' : '18';
$total_outstanding = 0;
$past_due = 0;
$sql = "select id from " . TABLE_JOURNAL_MAIN . " \n\t\twhere bill_acct_id = " . $id . " and journal_id in (" . $inv_jid . ") and closed = '0'";
$open_inv = $db->Execute($sql);
while (!$open_inv->EOF) {
$result = $db->Execute("select debit_amount, credit_amount from " . TABLE_JOURNAL_ITEM . " where gl_type = 'ttl' and ref_id = " . $open_inv->fields['id']);
$result2 = $db->Execute("select journal_id, post_date from " . TABLE_JOURNAL_MAIN . " where id = " . $open_inv->fields['id']);
$total_billed = $result->fields['debit_amount'] - $result->fields['credit_amount'];
$post_date = $result2->fields['post_date'];
$result = $db->Execute("select sum(debit_amount) as debits, sum(credit_amount) as credits \n\t from " . TABLE_JOURNAL_ITEM . " where so_po_item_ref_id = '" . $open_inv->fields['id'] . "' and gl_type in ('pmt', 'chk')");
$total_paid = $result->fields['credits'] - $result->fields['debits'];
$balance = $total_billed - $total_paid;
if ($type == 'v') {
$balance = -$balance;
}
// start the placement in aging array
if ($post_date < $due_date) {
$past_due += $balance;
}
if ($post_date < $late_90) {
$output['balance_90'] += $balance;
$total_outstanding += $balance;
} elseif ($post_date < $late_60) {
$output['balance_60'] += $balance;
$total_outstanding += $balance;
} elseif ($post_date < $late_30) {
$output['balance_30'] += $balance;
$total_outstanding += $balance;
} elseif ($post_date <= $today) {
$output['balance_0'] += $balance;
$total_outstanding += $balance;
}
// else it's in the future
$open_inv->MoveNext();
}
$output['total'] = $total_outstanding;
$output['past_due'] = $past_due;
$output['credit_limit'] = $credit_limit;
$output['terms_lang'] = gen_terms_to_language($special_terms, false, $type == 'v' ? 'AP' : 'AR');
return $output;
}
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;
}
function calculate_terms_due_dates($post_date, $terms_encoded, $type = 'AR')
{
$terms = explode(':', $terms_encoded);
$date_details = gen_get_dates($post_date);
$result = array();
switch ($terms[0]) {
default:
case '0':
// Default terms
$result['discount'] = constant($type . '_PREPAYMENT_DISCOUNT_PERCENT') / 100;
$result['net_date'] = gen_specific_date($post_date, constant($type . '_NUM_DAYS_DUE'));
if ($result['discount'] != 0) {
$result['early_date'] = gen_specific_date($post_date, constant($type . '_PREPAYMENT_DISCOUNT_DAYS'));
} else {
$result['early_date'] = gen_specific_date($post_date, 1000);
// move way out
}
break;
case '1':
// Cash on Delivery (COD)
// Cash on Delivery (COD)
case '2':
// Prepaid
$result['discount'] = 0;
$result['early_date'] = $post_date;
$result['net_date'] = $post_date;
break;
case '3':
// Special terms
$result['discount'] = $terms[1] / 100;
$result['early_date'] = gen_specific_date($post_date, $terms[2]);
$result['net_date'] = gen_specific_date($post_date, $terms[3]);
break;
case '4':
// Due on day of next month
$result['discount'] = $terms[1] / 100;
$result['early_date'] = gen_specific_date($post_date, $terms[2]);
$result['net_date'] = gen_db_date_short($terms[3]);
break;
case '5':
// Due at end of month
$result['discount'] = $terms[1] / 100;
$result['early_date'] = gen_specific_date($post_date, $terms[2]);
$result['net_date'] = date('Y-m-d', mktime(0, 0, 0, $date_details['ThisMonth'], $date_details['TotalDays'], $date_details['ThisYear']));
break;
}
return $result;
}
function gather_history($sku)
{
global $db, $messageStack;
$inv_history = array();
$dates = gen_get_dates();
$cur_month = $dates['ThisYear'] . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01';
for ($i = 0; $i < 13; $i++) {
$index = substr($cur_month, 0, 7);
$history['purchases'][$index] = array('post_date' => $cur_month, 'qty' => 0, 'total_amount' => 0);
$history['sales'][$index] = array('post_date' => $cur_month, 'qty' => 0, 'usage' => 0, 'total_amount' => 0);
$cur_month = gen_specific_date($cur_month, 0, -1, 0);
}
$last_year = $dates['ThisYear'] - 1 . '-' . substr('0' . $dates['ThisMonth'], -2) . '-01';
// load the SO's and PO's and get order, expected del date
$sql = "SELECT m.id, m.journal_id, m.store_id, m.purchase_invoice_id, i.qty, i.post_date, i.date_1,\n\ti.id AS item_id FROM " . TABLE_JOURNAL_MAIN . " m JOIN " . TABLE_JOURNAL_ITEM . " i ON m.id=i.ref_id\n\t WHERE m.journal_id IN (4, 10) AND i.sku='{$sku}' AND m.closed = '0' ORDER BY i.date_1";
$result = $db->Execute($sql);
while (!$result->EOF) {
switch ($result->fields['journal_id']) {
case 4:
$gl_type = 'por';
$hist_type = 'open_po';
break;
case 10:
$gl_type = 'sos';
$hist_type = 'open_so';
break;
}
$sql = "SELECT SUM(qty) AS qty from " . TABLE_JOURNAL_ITEM . "\n\t\tWHERE gl_type='{$gl_type}' AND so_po_item_ref_id=" . $result->fields['item_id'];
$adj = $db->Execute($sql);
// this looks for partial received to make sure this item is still on order
if ($result->fields['qty'] > $adj->fields['qty']) {
$history[$hist_type][] = array('id' => $result->fields['id'], 'store_id' => $result->fields['store_id'], 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'post_date' => $result->fields['post_date'], 'qty' => $result->fields['qty'], 'date_1' => $result->fields['date_1']);
}
$result->MoveNext();
}
// load the units received and sold, assembled and adjusted
$sql = "SELECT m.journal_id, m.post_date, i.qty, i.gl_type, i.credit_amount, i.debit_amount\n\t FROM " . TABLE_JOURNAL_MAIN . " m JOIN " . TABLE_JOURNAL_ITEM . " i ON m.id=i.ref_id\n\t WHERE m.journal_id IN (6, 12, 14, 16, 19, 21) AND i.sku='{$sku}' AND m.post_date >= '{$last_year}'\n\t ORDER BY m.post_date DESC";
$result = $db->Execute($sql);
while (!$result->EOF) {
$month = substr($result->fields['post_date'], 0, 7);
switch ($result->fields['journal_id']) {
case 6:
case 21:
$history['purchases'][$month]['qty'] += $result->fields['qty'];
$history['purchases'][$month]['total_amount'] += $result->fields['debit_amount'];
break;
case 12:
case 19:
$history['sales'][$month]['qty'] += $result->fields['qty'];
$history['sales'][$month]['usage'] += $result->fields['qty'];
$history['sales'][$month]['total_amount'] += $result->fields['credit_amount'];
break;
case 14:
if ($result->fields['gl_type'] == 'asi') {
// only if part of an assembly
$history['sales'][$month]['usage'] -= $result->fields['qty'];
// need to negate quantity since assy.
}
break;
case 16:
$history['sales'][$month]['usage'] += $result->fields['qty'];
break;
}
$result->MoveNext();
}
// calculate average usage
$percent_diff = 0.1;
// the percentage difference from current value to notify for adjustment
$months_of_data = 12;
// valid values are 1, 3, 6, or 12
$med_avg_diff = 0.25;
// the maximum percentage difference from the median and average, for large swings
$cnt = 0;
$sales = array();
$history['averages'] = array();
foreach ($history['sales'] as $key => $value) {
if ($cnt == 0) {
$cnt++;
continue;
}
// skip current month since we probably don't have the full months worth
$history['averages']['12month'] += $history['sales'][$key]['usage'];
if ($cnt < 7) {
$history['averages']['6month'] += $history['sales'][$key]['usage'];
}
if ($cnt < 4) {
$history['averages']['3month'] += $history['sales'][$key]['usage'];
}
if ($cnt < 2) {
$history['averages']['1month'] += $history['sales'][$key]['usage'];
}
if ($cnt <= $months_of_data) {
$sales[] = $value['usage'];
}
$cnt++;
}
$history['averages']['12month'] = round($history['averages']['12month'] / 12, 2);
$history['averages']['6month'] = round($history['averages']['6month'] / 6, 2);
$history['averages']['3month'] = round($history['averages']['3month'] / 3, 2);
sort($sales);
$sql = "SELECT minimum_stock_level, lead_time FROM " . TABLE_INVENTORY . " WHERE sku='{$sku}'";
$inv = $db->Execute($sql);
$idx = ceil(count($sales) / 2);
$median_sales = $sales[$idx];
$average_sales = ceil($history['averages'][$months_of_data . 'month']);
$new_min_stock = ceil($inv->fields['lead_time'] / 30) * $average_sales;
$high_band = $inv->fields['minimum_stock_level'] * (1 + $percent_diff);
$low_band = $inv->fields['minimum_stock_level'] * (1 - $percent_diff);
$high_avg = $average_sales * (1 + $med_avg_diff);
$low_avg = $average_sales * (1 - $med_avg_diff);
if ($new_min_stock > $high_band || $new_min_stock < $low_band) {
$messageStack->add(sprintf(INV_STOCK_LEVEL_ADJ, $new_min_stock), 'caution');
}
if ($median_sales > $high_avg || $median_sales < $low_avg) {
$messageStack->add(sprintf(INV_STOCK_MEDIAN, $median_sales, $average_sales), 'caution');
}
return $history;
}
function gen_build_sql_date($date_prefs, $df)
{
global $db;
$dates = gen_get_dates();
$DateArray = explode(':', $date_prefs);
$t = time();
$ds = '0000-00-00';
$de = '2199-00-00';
switch ($DateArray[0]) {
default:
case "a":
// All, skip the date addition to the where statement, all dates in db
$d = '';
$fildesc = '';
break;
case "b":
// Date Range
$d = '';
$fildesc = RW_RPT_DATERANGE;
if ($DateArray[1] != '') {
$ds = gen_db_date($DateArray[1]);
$d .= $df . " >= '" . $ds . "'";
$fildesc .= ' ' . TEXT_FROM . ' ' . $DateArray[1];
}
if ($DateArray[2] != '') {
// a value entered, check
if (strlen($d) > 0) {
$d .= ' and ';
}
$de = gen_specific_date(gen_db_date($DateArray[2]), 1);
$d .= $df . " < '" . $de . "'";
$fildesc .= ' ' . TEXT_TO . ' ' . $DateArray[2];
}
$fildesc .= '; ';
break;
case "c":
// Today (specify range for datetime type fields to match for time parts)
$ds = $dates['Today'];
$de = gen_specific_date($dates['Today'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' = ' . gen_locale_date($dates['Today']) . '; ';
break;
case "d":
// This Week
$ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t), $dates['ThisYear']));
$de = gen_specific_date(date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t) + 6, $dates['ThisYear'])), 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date(gen_specific_date($de, -1)) . '; ';
break;
case "e":
// This Week to Date
$ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], date('j', $t) - date('w', $t), $dates['ThisYear']));
$de = gen_specific_date($dates['Today'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; ';
break;
case "f":
// This Month
$ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], 1, $dates['ThisYear']));
$de = gen_specific_date(date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], $dates['TotalDays'], $dates['ThisYear'])), 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date(gen_specific_date($de, -1)) . '; ';
break;
case "g":
// This Month to Date
$ds = date('Y-m-d', mktime(0, 0, 0, $dates['ThisMonth'], 1, $dates['ThisYear']));
$de = gen_specific_date($dates['Today'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; ';
break;
case "h":
// This Quarter
$QtrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 3;
$temp = gen_calculate_fiscal_dates($QtrStrt);
$ds = $temp['start_date'];
$temp = gen_calculate_fiscal_dates($QtrStrt + 2);
$de = gen_specific_date($temp['end_date'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($temp['end_date']) . '; ';
break;
case "i":
// Quarter to Date
$QtrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 3;
$temp = gen_calculate_fiscal_dates($QtrStrt);
$ds = $temp['start_date'];
$de = gen_specific_date($dates['Today'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; ';
break;
case "j":
// This Year
$YrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 12;
$temp = gen_calculate_fiscal_dates($YrStrt);
$ds = $temp['start_date'];
$temp = gen_calculate_fiscal_dates($YrStrt + 11);
$de = gen_specific_date($temp['end_date'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($temp['end_date']) . '; ';
break;
case "k":
// Year to Date
$YrStrt = CURRENT_ACCOUNTING_PERIOD - (CURRENT_ACCOUNTING_PERIOD - 1) % 12;
$temp = gen_calculate_fiscal_dates($YrStrt);
$ds = $temp['start_date'];
$de = gen_specific_date($dates['Today'], 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = RW_RPT_DATERANGE . ' ' . TEXT_FROM . ' ' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($dates['Today']) . '; ';
break;
case "l":
// This Period
$ds = CURRENT_ACCOUNTING_PERIOD_START;
$de = gen_specific_date(CURRENT_ACCOUNTING_PERIOD_END, 1);
$d = $df . " >= '" . $ds . "' and " . $df . " < '" . $de . "'";
$fildesc = TEXT_PERIOD . ' ' . CURRENT_ACCOUNTING_PERIOD . ' (' . gen_locale_date(CURRENT_ACCOUNTING_PERIOD_START) . ' ' . TEXT_TO . ' ' . gen_locale_date(CURRENT_ACCOUNTING_PERIOD_END) . '); ';
break;
case "z":
// date by period
$temp = gen_calculate_fiscal_dates($DateArray[1]);
$ds = $temp['start_date'];
$de = $temp['end_date'];
$d = 'period = ' . $DateArray[1];
$fildesc = TEXT_PERIOD . ' ' . $DateArray[1] . ' (' . gen_locale_date($ds) . ' ' . TEXT_TO . ' ' . gen_locale_date($de) . '); ';
break;
}
$dates = array('sql' => $d, 'description' => $fildesc, 'start_date' => $ds, 'end_date' => $de);
return $dates;
}
function calulate_special_fields($id)
{
global $db;
$today = date('Y-m-d');
$new_data = array();
$result = $db->Execute("select debit_amount, credit_amount from " . TABLE_JOURNAL_ITEM . " where gl_type = 'ttl' and ref_id = " . $id);
$result2 = $db->Execute("select journal_id, post_date from " . TABLE_JOURNAL_MAIN . " where id = " . $id);
$post_date = $result2->fields['post_date'];
$negate = false;
if (in_array($result2->fields['journal_id'], array(6, 7))) {
$late_30 = gen_specific_date($today, -AP_AGING_DATE_1);
$late_60 = gen_specific_date($today, -AP_AGING_DATE_2);
$late_90 = gen_specific_date($today, -AP_AGING_DATE_3);
if ($result2->fields['journal_id'] != 7) {
$negate = true;
}
} else {
$late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1);
$late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2);
$late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3);
if ($result2->fields['journal_id'] != 12) {
$negate = true;
}
}
$result3 = $db->Execute("select sum(debit_amount) as debits, sum(credit_amount) as credits \n\t from " . TABLE_JOURNAL_ITEM . " where so_po_item_ref_id = '" . $id . "' and gl_type in ('pmt', 'chk')");
if ($negate) {
$total_billed = $result->fields['credit_amount'] - $result->fields['debit_amount'];
$total_paid = $result3->fields['debits'] - $result3->fields['credits'];
} else {
$total_billed = $result->fields['debit_amount'] - $result->fields['credit_amount'];
$total_paid = $result3->fields['credits'] - $result3->fields['debits'];
}
$balance = $total_billed - $total_paid;
$new_data['balance_due'] = $balance;
$new_data['balance_futre'] = 0;
$new_data['balance_0'] = 0;
$new_data['balance_30'] = 0;
$new_data['balance_60'] = 0;
$new_data['balance_90'] = 0;
if ($post_date < $late_90) {
$new_data['balance_90'] = $balance;
} elseif ($post_date < $late_60) {
$new_data['balance_60'] = $balance;
} elseif ($post_date < $late_30) {
$new_data['balance_30'] = $balance;
} elseif ($post_date > $today) {
$new_data['balance_futre'] = $balance;
} else {
$new_data['balance_0'] = $balance;
}
return $new_data;
}
$id = (int) $_GET['id'];
/*************** Act on the action request *************************/
// Load the customer status
$customer = $db->Execute("select c.type, c.inactive, c.special_terms, a.notes \r\n from " . TABLE_CONTACTS . " c inner join " . TABLE_ADDRESS_BOOK . " a on c.id = a.ref_id \r\n where c.id = " . $id . " and a.type like '%m'");
$notes = str_replace(chr(10), "<br />", $customer->fields['notes']);
$type = $customer->fields['type'] == 'v' ? 'AP' : 'AR';
$special_terms = gen_terms_to_language($customer->fields['special_terms'], $short = false, $type);
$terms = explode(':', $customer->fields['special_terms']);
$credit_limit = $terms[4] ? $terms[4] : constant($type . '_CREDIT_LIMIT_AMOUNT');
$due_days = $terms[3] ? $terms[3] : constant($type . '_NUM_DAYS_DUE');
$due_date = gen_specific_date($today, -$due_days);
// load the aged status
$today = date('Y-m-d');
$late_30 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_1 : -AR_AGING_PERIOD_1);
$late_60 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_2 : -AR_AGING_PERIOD_2);
$late_90 = gen_specific_date($today, $type == 'AP' ? -AP_AGING_DATE_3 : -AR_AGING_PERIOD_3);
$new_data = array('balance_0' => '0', 'balance_30' => '0', 'balance_60' => '0', 'balance_90' => '0');
$inv_jid = $type == 'AP' ? '6, 7' : '12, 13';
$pmt_jid = $type == 'AP' ? '20' : '18';
$total_outstanding = 0;
$past_due = 0;
$sql = "select id from " . TABLE_JOURNAL_MAIN . " \r\n\twhere bill_acct_id = " . $id . " and journal_id in (" . $inv_jid . ") and closed = '0'";
$open_inv = $db->Execute($sql);
//echo 'records = ' . $open_inv->RecordCount() . '<br />';
while (!$open_inv->EOF) {
$sql = "select m.post_date, sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n where m.id = " . $open_inv->fields['id'] . " and journal_id in (" . $inv_jid . ") and i.gl_type <> 'ttl' group by m.id";
$result = $db->Execute($sql);
$total_billed = $result->fields['credits'] - $result->fields['debits'];
$post_date = $result->fields['post_date'];
$sql = "select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n where i.so_po_item_ref_id = " . $open_inv->fields['id'] . " and m.journal_id = " . $pmt_jid . " and i.gl_type = 'pmt'";
$result = $db->Execute($sql);
$now->modify('-1 day');
}
$cal_order = array('name' => 'dateOrdered', 'form' => 'orders', 'fieldname' => 'post_date', 'imagename' => 'btn_date_1', 'default' => isset($order->post_date) ? gen_locale_date($order->post_date) : $now->format(DATE_FORMAT), 'params' => array('align' => 'left'));
$cal_terminal = array('name' => 'dateRequired', 'form' => 'orders', 'fieldname' => 'terminal_date', 'imagename' => 'btn_date_2', 'default' => isset($order->terminal_date) ? gen_locale_date($order->terminal_date) : $req_date, 'params' => array('align' => 'left'));
// build the display options based on JOURNAL_ID
$template_options = array();
switch (JOURNAL_ID) {
case 3:
case 4:
$req_date = gen_locale_date(gen_specific_date('', 0, 1, 0));
$template_options['terminal_date'] = true;
$template_options['terms'] = true;
$template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', ''));
break;
case 6:
$req_date = gen_locale_date(gen_specific_date('', 0, 1, 0));
$template_options['terms'] = true;
$template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', ''));
break;
case 7:
$req_date = date(DATE_FORMAT);
$template_options['terms'] = true;
$template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', ''));
break;
case 9:
case 10:
$template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', ''));
case 12:
$req_date = date(DATE_FORMAT);
$template_options['terminal_date'] = true;
$template_options['terms'] = true;
$db->Execute("delete from " . TABLE_PRICE_SHEETS . " where id = '{$id}'");
$db->Execute("delete from " . TABLE_INVENTORY_SPECIAL_PRICES . " where price_sheet_id = '{$id}'");
gen_add_audit_log(PRICE_SHEETS_LOG . TEXT_DELETE, $sheet_name);
gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('psID', 'action')) . '&type=' . $type, 'SSL'));
break;
case 'revise':
validate_security($security_level, 2);
$old_id = db_prepare_input($_GET['psID']);
$result = $db->Execute("select * from " . TABLE_PRICE_SHEETS . " where id = {$old_id}");
$old_rev = $result->fields['revision'];
$output_array = array('sheet_name' => $result->fields['sheet_name'], 'type' => $type, 'revision' => $result->fields['revision'] + 1, 'effective_date' => gen_specific_date($result->fields['effective_date'], 1), 'default_sheet' => $result->fields['default_sheet'], 'default_levels' => $result->fields['default_levels']);
db_perform(TABLE_PRICE_SHEETS, $output_array, 'insert');
$id = db_insert_id();
// this is used by the edit function later on.
// expire the old sheet
$db->Execute("UPDATE " . TABLE_PRICE_SHEETS . " SET expiration_date='" . gen_specific_date($result->fields['effective_date'], 1) . "' WHERE id={$old_id}");
// Copy special pricing information to new sheet
$levels = $db->Execute("select inventory_id, price_levels from " . TABLE_INVENTORY_SPECIAL_PRICES . " where price_sheet_id = {$old_id}");
while (!$levels->EOF) {
$db->Execute("insert into " . TABLE_INVENTORY_SPECIAL_PRICES . " set inventory_id = {$levels->fields}['inventory_id'],\n\t\t price_sheet_id = {$id}, price_levels = '{$levels->fields}['price_levels']'");
$levels->MoveNext();
}
gen_add_audit_log(PRICE_SHEETS_LOG . TEXT_REVISE, $result->fields['sheet_name'] . ' Rev. ' . $old_rev . ' => ' . ($old_rev + 1));
$_REQUEST['action'] = 'edit';
// continue with edit.
// continue with edit.
case 'edit':
if (!isset($id)) {
$id = db_prepare_input($_POST['rowSeq']);
}
$result = $db->Execute("select * from " . TABLE_PRICE_SHEETS . " where id = {$id}");
function trackPackages($track_date = '0000-00-00', $log_id = false)
{
global $db, $messageStack;
$result = array();
if (MODULE_SHIPPING_FEDEX_V7_TEST_MODE == 'Test') {
$messageStack->add('Tracking only works on the FedEx production server!', 'error');
return false;
} else {
$client = new SoapClient(PATH_TO_TRACK_WSDL, array('trace' => 1));
}
if ($log_id) {
$shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\t\t\tfrom " . TABLE_SHIPPING_LOG . " \n\t\t\t\twhere carrier = '" . $this->code . "' and id = '" . $log_id . "'");
} else {
$start_date = $track_date;
$end_date = gen_specific_date($track_date, $day_offset = 1);
$shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\t\t\tfrom " . TABLE_SHIPPING_LOG . " \n\t\t\t\twhere carrier = '" . $this->code . "' \n\t\t\t\t\tand ship_date >= '" . $start_date . "' and ship_date < '" . $end_date . "'");
}
while (!$shipments->EOF) {
$tracking_number = $shipments->fields['tracking_id'];
if ($shipments->fields['actual_date'] != '0000-00-00 00:00:00') {
// skip if already tracked
$shipments->MoveNext();
continue;
}
$request = $this->FormatFedExTrackRequest($shipments->fields['tracking_id']);
if (!$request) {
continue;
}
//echo 'request = '; print_r($request); echo '<br />';
try {
$response = $client->track($request);
//echo 'Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>';
//echo 'Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>';
if ($response->HighestSeverity != 'FAILURE' && $response->HighestSeverity != 'ERROR') {
$actual_date = str_replace('T', ' ', $response->TrackDetails->ActualDeliveryTimestamp);
$actual_date = substr($actual_date, 0, -6);
// see if the package was late, flag if so
$late = '0';
// no data
if ($shipments->fields['deliver_date'] < $actual_date) {
$late = 'L';
// $messageStack->add(SHIPPING_FEDEX_V7_TRACK_FAIL . $shipments->fields['ref_id'], 'error');
} elseif ($response->TrackDetails->StatusCode != 'DL') {
$late = 'T';
$messageStack->add(sprintf(SHIPPING_FEDEX_V7_TRACK_STATUS, $shipments->fields['ref_id'], $response->TrackDetails->StatusCode, $response->TrackDetails->StatusDescription), 'caution');
}
// update the log file with the actual delivery timestamp, append notes
$db->Execute("update " . TABLE_SHIPPING_LOG . " \n\t\t\t\t\t set actual_date = '" . $actual_date . "', deliver_late = '" . $late . "' where id = " . $shipments->fields['id']);
// $messageStack->add(SHIPPING_FEDEX_V7_TRACK_SUCCESS . $response->TrackDetails->ActualDeliveryTimestamp, 'success');
} else {
foreach ($response->Notifications as $notification) {
if (is_object($notification)) {
$message .= ' (' . $notification->Severity . ') ' . $notification->Message;
} else {
$message .= ' ' . $notification;
}
}
$messageStack->add(SHIPPING_FEDEX_V7_TRACK_ERROR . $message, 'error');
return false;
}
} catch (SoapFault $exception) {
//echo 'Error Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>';
//echo 'Error Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>';
$message = " ({$exception->faultcode}) {$exception->faultstring}";
$messageStack->add(SHIPPING_FEDEX_CURL_ERROR . $message, 'error');
return false;
}
$shipments->MoveNext();
}
return true;
}
$template_options = array();
switch (JOURNAL_ID) {
case 3:
$req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0));
$template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>');
$template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"'));
$template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', ''));
break;
case 4:
$req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0));
$template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>');
$template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"'));
$template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', ''));
break;
case 6:
$req_date = gen_spiffycal_db_date_short(gen_specific_date('', 0, 1, 0));
$template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"'));
$template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', ''));
break;
case 7:
$req_date = date(DATE_FORMAT, time());
$template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"'));
$template_options['waiting'] = array('title' => ORD_WAITING_FOR_INVOICE, 'field' => html_checkbox_field('waiting', '1', $order->waiting ? true : false, '', ''));
break;
case 9:
$req_date = date(DATE_FORMAT, time());
$template_options['terminal_date'] = array('title' => constant('ORD_TEXT_' . JOURNAL_ID . '_EXPIRES'), 'field' => '<script type="text/javascript">dateRequired.writeControl(); dateRequired.displayLeft=true; dateRequired.dateFormat="' . DATE_FORMAT_SPIFFYCAL . '";</script>');
$template_options['terms'] = array('title' => ACT_TERMS_DUE, 'field' => html_input_field('terms_text', gen_terms_to_language('0', true, 'ap'), 'readonly="readonly" size="25"') . ' ' . html_icon('apps/accessories-text-editor.png', ACT_TERMS_DUE, 'small', 'align="top" style="cursor:pointer" onclick="TermsList()"'));
$template_options['closed'] = array('title' => TEXT_CLOSE, 'field' => html_checkbox_field('closed', '1', $order->closed ? true : false, '', ''));
break;
case 10:
function calulate_special_fields($id)
{
global $db;
$today = date('Y-m-d');
$new_data = array();
$result = $db->Execute("select m.journal_id, m.post_date, sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t where m.id = " . $id . " and i.gl_type <> 'ttl' group by m.id");
if (in_array($result->fields['journal_id'], array(6, 7))) {
$total_billed = $result->fields['debits'] - $result->fields['credits'];
$late_30 = gen_specific_date($today, -AP_AGING_DATE_1);
$late_60 = gen_specific_date($today, -AP_AGING_DATE_2);
$late_90 = gen_specific_date($today, -AP_AGING_DATE_3);
$negate = true;
} else {
$total_billed = $result->fields['credits'] - $result->fields['debits'];
$late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1);
$late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2);
$late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3);
$negate = false;
}
$post_date = $result->fields['post_date'];
$result = $db->Execute("select sum(i.debit_amount) as debits, sum(i.credit_amount) as credits \r\n\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t where i.so_po_item_ref_id = " . $id . " and m.journal_id in (18, 20) and i.gl_type in ('pmt', 'chk')");
$total_paid = $result->fields['credits'] - $result->fields['debits'];
$balance = $total_billed - $total_paid;
$new_data['balance_0'] = 0;
$new_data['balance_30'] = 0;
$new_data['balance_60'] = 0;
$new_data['balance_90'] = 0;
if ($post_date < $late_90) {
$new_data['balance_90'] = $balance;
} elseif ($post_date < $late_60) {
$new_data['balance_60'] = $balance;
} elseif ($post_date < $late_30) {
$new_data['balance_30'] = $balance;
} else {
$new_data['balance_0'] = $balance;
}
return $new_data;
}
}
// Reset all other price sheet default flags if set to this price sheet
if ($default_sheet) {
$db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset default_sheet = '0' \r\n\t\t\twhere sheet_name <> '" . $sheet_name . "'");
}
$sql = $action == 'save' ? 'insert into ' : 'update ';
$sql .= TABLE_PRICE_SHEETS . " set \r\n\t\tsheet_name = '" . $sheet_name . "', \r\n\t\trevision = '" . $revision . "', \r\n\t\teffective_date = '" . $effective_date . "', \r\n\t\tdefault_sheet = '" . $default_sheet . "', \r\n\t\tdefault_levels = '" . $default_levels . "'";
$sql .= $action == 'save' ? '' : ' where id = ' . $id;
$result = $db->Execute($sql);
// Set all price sheets with this name to default
if ($default_sheet) {
$db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset default_sheet = '1' \r\n\t\t\twhere sheet_name = '" . $sheet_name . "'");
}
// set expiration date of previous rev if there is a older rev of this price sheet
if ($effective_date != '') {
$db->Execute("update " . TABLE_PRICE_SHEETS . " \r\n\t\t\tset expiration_date = '" . gen_specific_date($effective_date, -1) . "' \r\n\t\t\twhere sheet_name = '" . $sheet_name . "' and revision = " . ($revision - 1));
}
gen_add_audit_log(PRICE_SHEETS_LOG . ($action == 'save') ? TEXT_SAVE : TEXT_UPDATE, $sheet_name);
gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('psID', 'action')), 'SSL'));
break;
case 'delete':
if ($security_level < 4) {
$messageStack->add_session(ERROR_NO_PERMISSION, 'error');
gen_redirect(html_href_link(FILENAME_DEFAULT, gen_get_all_get_params(array('action')), 'SSL'));
break;
}
$id = (int) db_prepare_input($_GET['psID']);
$result = $db->Execute("select sheet_name from " . TABLE_PRICE_SHEETS . " where id = " . $id);
$sheet_name = $result->fields['sheet_name'];
// TBD check to see if this is the default if so, remind to pick another
$db->Execute("delete from " . TABLE_PRICE_SHEETS . " where id = '" . $id . "'");
function get_chart_data($operation, $data)
{
global $db, $currencies;
$output = array('type' => 'pie', 'width' => '600', 'height' => '400');
switch ($operation) {
case 'annual_sales':
$output['type'] = 'column';
$output['title'] = CONTACTS_CHART_SALES_TITLE;
$output['label_text'] = TEXT_DATE;
$output['value_text'] = TEXT_TOTAL;
$id = $data[0];
if (!$id) {
return false;
}
$dates = gen_get_dates(gen_specific_date(date(Y - m - d), 0, 0, -1));
$result = $db->Execute("SELECT month(post_date) as month, year(post_date) as year,\n sum(total_amount) as total from " . TABLE_JOURNAL_MAIN . " \n \t\t where bill_acct_id = {$id} and journal_id in (12,13) and post_date >= '" . $dates['ThisYear'] . '-' . $dates['ThisMonth'] . "-01' \n \t\t group by year, month limit 12");
for ($i = 0; $i < 12; $i++) {
if ($result->fields['year'] == $dates['ThisYear'] && $result->fields['month'] == $dates['ThisMonth']) {
$value = $result->fields['total'];
$result->MoveNext();
} else {
$value = 0;
}
$output['data'][] = array('label' => $dates['ThisYear'] . '-' . $dates['ThisMonth'], 'value' => $value);
$dates['ThisMonth']++;
if ($dates['ThisMonth'] == '13') {
$dates['ThisYear']++;
$dates['ThisMonth'] = '01';
}
}
break;
default:
return false;
}
return $output;
}
function load_report_data($report, $Seq, $sql = '', $GrpField = '')
{
global $db;
// find list of accounts within search filter
$today = date('Y-m-d');
$late_30 = gen_specific_date($today, -AR_AGING_PERIOD_1);
$late_60 = gen_specific_date($today, -AR_AGING_PERIOD_2);
$late_90 = gen_specific_date($today, -AR_AGING_PERIOD_3);
$sql_fields = substr($sql, strpos($sql, 'select ') + 7, strpos($sql, ' from ') - 7);
// prepare the sql by temporarily replacing calculated fields with real fields
$this->sql_field_array = explode(', ', $sql_fields);
for ($i = 0; $i < count($this->sql_field_array); $i++) {
$this->sql_field_karray['c' . $i] = substr($this->sql_field_array[$i], 0, strpos($this->sql_field_array[$i], ' '));
}
$temp_sql = str_replace(' FROM ', ', ' . TABLE_JOURNAL_MAIN . '.id, journal_id, post_date, total_amount, bill_acct_id FROM ', $sql);
$temp_sql = $this->replace_special_fields($temp_sql);
$result = $db->Execute($temp_sql);
if ($result->RecordCount() == 0) {
return false;
}
// No data so bail now
while (!$result->EOF) {
for ($i = 0; $i < sizeof($this->sql_field_karray); $i++) {
$this->accounts[$result->fields['bill_acct_id']]['c' . $i] = $result->fields['c' . $i];
}
$negate = in_array($result->fields['journal_id'], array(7, 13)) ? true : false;
$balance = $negate ? -$result->fields['total_amount'] : $result->fields['total_amount'];
$balance -= $this->fetch_paid_amounts($result->fields['id']);
if ($result->fields['post_date'] < $late_90) {
$this->accounts[$result->fields['bill_acct_id']]['balance_90'] += $balance;
} elseif ($result->fields['post_date'] < $late_60) {
$this->accounts[$result->fields['bill_acct_id']]['balance_60'] += $balance;
} elseif ($result->fields['post_date'] < $late_30) {
$this->accounts[$result->fields['bill_acct_id']]['balance_30'] += $balance;
} else {
$this->accounts[$result->fields['bill_acct_id']]['balance_0'] += $balance;
}
$result->MoveNext();
}
// Generate the output data array
$RowCnt = 0;
// Row counter for output data
$ColCnt = 1;
$GrpWorking = false;
foreach ($this->accounts as $myrow) {
// Check to see if a total row needs to be displayed
if (isset($GrpField)) {
// we're checking for group totals, see if this group is complete
if ($myrow[$GrpField] != $GrpWorking && $GrpWorking !== false) {
// it's a new group so print totals
$OutputArray[$RowCnt][0] = 'g:' . $GrpWorking;
foreach ($Seq as $offset => $TotalCtl) {
$OutputArray[$RowCnt][$offset + 1] = ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']);
$Seq[$offset]['grptotal'] = '';
// reset the total
}
$RowCnt++;
// go to next row
}
$GrpWorking = $myrow[$GrpField];
// set to new grouping value
}
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
//echo 'orig myrow = '; print_r($myrow); echo '<br /><br />';
$myrow = $this->replace_data_fields($myrow, $Seq);
//echo 'new myrow = '; print_r($myrow); echo '<br /><br />';
foreach ($Seq as $key => $TableCtl) {
//
// insert data into output array and set to next column
$OutputArray[$RowCnt][$ColCnt] = ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']);
$ColCnt++;
if ($TableCtl['total']) {
// add to the running total if need be
$Seq[$key]['grptotal'] += $myrow[$TableCtl['fieldname']];
$Seq[$key]['rpttotal'] += $myrow[$TableCtl['fieldname']];
}
}
$RowCnt++;
$ColCnt = 1;
}
if ($GrpWorking !== false) {
// if we collected group data show the final group total
$OutputArray[$RowCnt][0] = 'g:' . $GrpWorking;
foreach ($Seq as $TotalCtl) {
$OutputArray[$RowCnt][$ColCnt] = $TotalCtl['total'] == '1' ? ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']) : ' ';
$ColCnt++;
}
$RowCnt++;
$ColCnt = 1;
}
// see if we have a total to send
$ShowTotals = false;
foreach ($Seq as $TotalCtl) {
if ($TotalCtl['total'] == '1') {
$ShowTotals = true;
}
}
if ($ShowTotals) {
$OutputArray[$RowCnt][0] = 'r:' . $report->title;
foreach ($Seq as $TotalCtl) {
if ($TotalCtl['total']) {
$OutputArray[$RowCnt][$ColCnt] = ProcessData($TotalCtl['rpttotal'], $TotalCtl['processing']);
} else {
$OutputArray[$RowCnt][$ColCnt] = ' ';
}
$ColCnt++;
}
}
return $OutputArray;
}
function trackPackages($track_date = '0000-00-00', $log_id = false)
{
// single tracking # or date range
global $db, $messageStack;
// $result = array();
if ($log_id) {
$shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\tfrom " . TABLE_SHIPPING_LOG . " where carrier = '{$this->code}' and id = '{$log_id}'");
} else {
$start_date = $track_date;
$end_date = gen_specific_date($track_date, $day_offset = 1);
$shipments = $db->Execute("select id, ref_id, deliver_date, actual_date, tracking_id, notes \n\t\tfrom " . TABLE_SHIPPING_LOG . " where carrier = '{$this->code}' and ship_date >= '{$start_date}' and ship_date < '{$end_date}'");
}
if ($shipments->RecordCount() == 0) {
return 'No records were found!';
}
$xml = "<StatusRequest>\n";
$xml .= xmlEntry('AccountID', MODULE_SHIPPING_ENDICIA_ACCOUNT_NUMBER);
$xml .= xmlEntry('PassPhrase', MODULE_SHIPPING_ENDICIA_PASS_PHRASE);
$xml .= xmlEntry('Test', MODULE_SHIPPING_ENDICIA_TEST_MODE == 'Test' ? 'Y' : 'N');
$xml .= xmlEntry('FullStatus', 'N');
while (!$shipments->EOF) {
$xml .= " <StatusList>\n";
$xml .= xmlEntry('PICNumber', $shipments->fields['tracking_id']);
$xml .= " </StatusList>\n";
$shipments->MoveNext();
}
$xml .= "</StatusRequest>\n";
$client = new SoapClient(MODULE_SHIPPING_ENDICIA_ELS_URL, array('trace' => 1));
try {
$response = $client->StatusRequest($xml);
//echo 'Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>';
//echo 'Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>';
if ($response->StatusResponse->Status == 0) {
if (!is_array($response->StatusResponse->StatusList)) {
$response->StatusResponse->StatusList = array($response->StatusResponse->StatusList);
}
foreach ($response->StatusResponse->StatusList as $status) {
$description = $status->PICNumber->Status;
$status_code = $status->PICNumber->StatusCode;
$message = sprintf(SHIPPING_ENDICIA_TRACK_STATUS, $shipments->fields['ref_id'], $shipments->fields['tracking_id'], $description);
$messageStack->add($message, $status_code == 'D' ? 'success' : 'caution');
}
} else {
$message = TEXT_ERROR . ' (' . $response->StatusResponse->Status . ') ' . $response->StatusResponse->ErrorMsg;
$messageStack->add($message, 'error');
}
} catch (SoapFault $exception) {
//echo 'Error Request <pre>' . htmlspecialchars($client->__getLastRequest()) . '</pre>';
//echo 'Error Response <pre>' . htmlspecialchars($client->__getLastResponse()) . '</pre>';
$message = "SOAP Error ({$exception->faultcode}) {$exception->faultstring}";
$messageStack->add($message, 'error');
}
return $message;
}
