function load_report_data($Prefs, $Seq)
{
global $db;
$bank_list = array();
$dep_in_transit = 0;
$chk_in_transit = 0;
$period = substr($Prefs['datedefault'], 2);
$fiscal_dates = gen_calculate_fiscal_dates($period);
$gl_account = $Prefs['fromvalue1'];
// assumes that the gl account is the first criteria
if (!$gl_account) {
return false;
}
// No gl account so bail now
//Load open Journal Items
$sql = "SELECT m.id, m.post_date, i.debit_amount, i.credit_amount, m.purchase_invoice_id, i.description " . "FROM " . TABLE_JOURNAL_MAIN . " m " . "INNER JOIN " . TABLE_JOURNAL_ITEM . " i " . "ON m.id = i.ref_id " . "WHERE i.gl_account = '" . $gl_account . "' " . "AND i.reconciled = 0 " . "AND m.post_date <= '" . $fiscal_dates['end_date'] . "' " . "ORDER BY post_date";
$result = $db->Execute($sql);
while (!$result->EOF) {
$new_total = $result->fields['debit_amount'] - $result->fields['credit_amount'];
if ($new_total < 0) {
$dep_amount = '';
$pmt_amount = -$new_total;
$payment = 1;
} else {
$dep_amount = $new_total;
$pmt_amount = '';
$payment = 0;
}
$dep_in_transit += $dep_amount;
$chk_in_transit += $pmt_amount;
$bank_list[$result->fields['id']] = array('post_date' => $result->fields['post_date'], 'reference' => $result->fields['purchase_invoice_id'], 'description' => $result->fields['description'], 'dep_amount' => $dep_amount, 'pmt_amount' => $pmt_amount);
$result->MoveNext();
}
// load the gl account end of period balance
$sql = "select beginning_balance, debit_amount, credit_amount from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\t\twhere account_id = '" . $gl_account . "' and period = " . $period;
$result = $db->Execute($sql);
$gl_init_bal = $result->fields['beginning_balance'];
$cash_receipts = $result->fields['debit_amount'];
$cash_payments = $result->fields['credit_amount'];
$end_gl_bal = $gl_init_bal + $cash_receipts - $cash_payments;
// Check this next line - end_gl_bal_1 or just end_gl_bal?
$unrecon_diff = $end_gl_bal - $dep_in_transit + $chk_in_transit;
$this->bal_sheet_data = array();
$this->bal_sheet_data[] = array('d', RW_RECON_BB, '', '', '', ProcessData($gl_init_bal, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_CR, '', '', '', ProcessData($cash_receipts, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_CD, '', '', '', ProcessData(-$cash_payments, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_EB, '', '', '', ProcessData($end_gl_bal, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_ADD_BACK, '', '', '', '');
foreach ($bank_list as $value) {
if ($value['dep_amount']) {
$this->bal_sheet_data[] = array('d', '', ProcessData($value['post_date'], 'date'), $value['reference'], ProcessData($value['dep_amount'], 'null_pcur'), '');
}
}
$this->bal_sheet_data[] = array('d', RW_RECON_DIT, '', '', '', ProcessData($dep_in_transit, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_LOP, '', '', '', '');
foreach ($bank_list as $value) {
if ($value['pmt_amount']) {
$this->bal_sheet_data[] = array('d', '', ProcessData($value['post_date'], 'date'), $value['reference'], ProcessData(-$value['pmt_amount'], 'null_pcur'), '');
}
}
$this->bal_sheet_data[] = array('d', RW_RECON_TOP, '', '', '', ProcessData(-$chk_in_transit, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_DIFF, '', '', '', ProcessData($unrecon_diff, 'null_pcur'));
$this->bal_sheet_data[] = array('d', RW_RECON_EB, '', '', '', ProcessData($end_gl_bal, 'null_pcur'));
//Load closed Journal Items
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_CLEARED, '', '', '', '');
$sql = "SELECT m.id, m.post_date, i.debit_amount, i.credit_amount, m.purchase_invoice_id, i.description " . "FROM " . TABLE_JOURNAL_MAIN . " m " . "INNER JOIN " . TABLE_JOURNAL_ITEM . " i " . "ON m.id = i.ref_id " . "WHERE i.gl_account = '" . $gl_account . "' " . "AND i.reconciled = 1 " . "AND m.post_date <= '" . $fiscal_dates['end_date'] . "' " . "ORDER BY post_date";
$result = $db->Execute($sql);
unset($new_total, $bank_list);
while (!$result->EOF) {
$new_total = $result->fields['debit_amount'] - $result->fields['credit_amount'];
if ($new_total < 0) {
$dep_amount = '';
$pmt_amount = -$new_total;
$payment = 1;
} else {
$dep_amount = $new_total;
$pmt_amount = '';
$payment = 0;
}
$dep_cleared += $dep_amount;
$chk_cleared += $pmt_amount;
$bank_list[$result->fields['id']] = array('post_date' => $result->fields['post_date'], 'reference' => $result->fields['purchase_invoice_id'], 'description' => $result->fields['description'], 'dep_amount' => $dep_amount, 'pmt_amount' => $pmt_amount);
$result->MoveNext();
}
$this->bal_sheet_data[] = array('d', RW_RECON_DCLEARED, '', '', '', '');
foreach ($bank_list as $value) {
if ($value['dep_amount']) {
$this->bal_sheet_data[] = array('d', '', ProcessData($value['post_date'], 'date'), $value['reference'], ProcessData($value['dep_amount'], 'null_pcur'), '');
}
}
$this->bal_sheet_data[] = array('d', RW_RECON_TDC, '', '', '', ProcessData($dep_cleared, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_PCLEARED, '', '', '', '');
foreach ($bank_list as $value) {
if ($value['pmt_amount']) {
$this->bal_sheet_data[] = array('d', '', ProcessData($value['post_date'], 'date'), $value['reference'], ProcessData(-$value['pmt_amount'], 'null_pcur'), '');
}
}
$this->bal_sheet_data[] = array('d', RW_RECON_TPC, '', '', '', ProcessData($chk_cleared, 'null_pcur'));
$this->bal_sheet_data[] = array('d', '', '', '', '', '');
$this->bal_sheet_data[] = array('d', RW_RECON_NCLEARED, '', '', '', ProcessData($dep_cleared - $chk_cleared, 'null_pcur'));
return $this->bal_sheet_data;
}
// set closed flag to '0' for all records that were unchecked
if (count($uncleared_items)) {
$sql = "update " . TABLE_JOURNAL_MAIN . " set closed = '0' where id in (" . implode(',', $uncleared_items) . ")";
// $result = $db->Execute($sql);
}
$messageStack->add(GL_RECON_POST_SUCCESS, 'success');
gen_add_audit_log(GL_LOG_ACCT_RECON . $period, $gl_account);
break;
default:
}
/***************** prepare to display templates *************************/
//
$bank_list = array();
$statement_balance = $currencies->format(0);
// load the payments and deposits that are open
$fiscal_dates = gen_calculate_fiscal_dates($period);
$end_date = $fiscal_dates['end_date'];
$sql = "select i.id, m.post_date, i.debit_amount, i.credit_amount, m.purchase_invoice_id, m.bill_primary_name \r\n\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\twhere i.gl_account = '" . $gl_account . "' and i.reconciled = '0' and m.post_date <= '" . $fiscal_dates['end_date'] . "'";
$result = $db->Execute($sql);
while (!$result->EOF) {
$bank_list[$result->fields['id']] = array('post_date' => $result->fields['post_date'], 'reference' => $result->fields['purchase_invoice_id'], 'dep_amount' => $result->fields['debit_amount'], 'pmt_amount' => $result->fields['credit_amount'], 'payment' => $result->fields['debit_amount'] ? 0 : 1, 'name' => $result->fields['bill_primary_name'], 'cleared' => 0);
$result->MoveNext();
}
// check to see if in partial reconciliation, if so add closed items
$sql = "select statement_balance, cleared_items from " . TABLE_RECONCILIATION . " where period = " . $period . " and gl_account = '" . $gl_account . "'";
$result = $db->Execute($sql);
if ($result->RecordCount() != 0) {
// there are current cleared items in the present accounting period (edit)
$statement_balance = $currencies->format($result->fields['statement_balance']);
$cleared_items = unserialize($result->fields['cleared_items']);
// load information from general journal
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;
}
