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; }