function ShowTableRow($Params, $myrow, $FillThisRow, $FC, $Heading = false)
{
$MaxBoxY = $Params->ordinate + $Params->height;
// figure the max y position on page
$fillReq = $Heading ? $Params->hfillshow : $Params->fillshow;
if ($FillThisRow && $fillReq) {
$this->SetFillColor($FC[0], $FC[1], $FC[2]);
} else {
$this->SetFillColor(255);
}
$this->Cell($Params->width, $MaxBoxY - $this->y0, '', 0, 0, 'L', 1);
// sets background proper color
$maxY = $this->y0;
// set to current top of row
$Col = 0;
$NextXPos = $Params->abscissa;
foreach ($myrow as $key => $value) {
if (substr($key, 0, 1) == 'r') {
$key = substr($key, 1);
}
$font = $Heading && $Params->hfont != '' ? $Params->hfont : $Params->boxfield[$key]->font;
$size = $Heading && $Params->hsize != '' ? $Params->hsize : $Params->boxfield[$key]->size;
$color = $Heading && $Params->hcolor != '' ? $Params->hcolor : $Params->boxfield[$key]->color;
$align = $Heading && $Params->halign != '' ? $Params->halign : $Params->boxfield[$key]->align;
$this->SetLeftMargin($NextXPos);
$this->SetXY($NextXPos, $this->y0);
$this->SetFont($font, '', $size);
$TC = explode(':', $color);
$this->SetTextColor($TC[0], $TC[1], $TC[2]);
$CellHeight = ($size + PF_DEFAULT_ROWSPACE) * 0.35;
// if ($trunc) $value=$this->TruncData($value, $value->width);
// special code for heading and data
if ($Heading) {
if ($align == 'A') {
$align = $Params->boxfield[$key]->align;
}
// auto align
} else {
if (isset($Params->boxfield[$key]->processing)) {
$value = ProcessData($value, $Params->boxfield[$key]->processing);
}
}
$this->MultiCell($Params->boxfield[$key]->width, $CellHeight, $value, 0, $align, $fillReq ? true : false);
if ($this->GetY() > $maxY) {
$maxY = $this->GetY();
}
$NextXPos += $Params->boxfield[$key]->width;
$Col++;
}
$ThisRowHt = $maxY - $this->y0;
// seee how tall this row was
if ($ThisRowHt > $MaxRowHt) {
$MaxRowHt = $ThisRowHt;
}
// keep that largest row so far to track pagination
$this->y0 = $maxY;
// set y position to largest value for next row
if ($Heading && $Params->hbordershow) {
// then it's the heading draw a line after if fill is set
$this->Line($Params->abscissa, $maxY, $Params->abscissa + $Params->width, $maxY);
$this->y0 = $this->y0 + $Params->hsize * 0.35;
}
return $MaxRowHt;
}
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 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;
}
function build_data_fields($data, $level)
{
foreach ($data as $value) {
if (is_array($value['child'])) {
$this->build_data_fields($value['child'], $level + 1);
}
$disp_level = max(1, $this->max_num_levels - $level + 1);
$bal_level = max(1, $this->max_num_levels - $level);
$data = array('d', $value['description']);
for ($i = 1; $i <= $this->max_num_levels; $i++) {
if ($i == $bal_level) {
$data[] = ProcessData($value['balance'], 'null_dcur');
} else {
if ($i == $disp_level) {
$data[] = ProcessData($value['total'], 'null_dcur');
} else {
$data[] = '';
}
}
}
$this->bal_sheet_data[] = $data;
}
}
function BuildSeq($ReportID, $Prefs, $delivery_method = 'D')
{
// for forms only - Sequential mode
global $db, $messageStack;
global $FieldListings, $FieldValues, $posted_currencies;
$output = array();
// first fetch all the fields we need to display
$FieldListings = '';
$sql = "select seqnum, params from " . TABLE_REPORT_FIELDS . " \r\n\t\twhere reportid = " . $ReportID . " and entrytype = 'fieldlist' and visible = 1\r\n\t\torder by seqnum";
$result = $db->Execute($sql);
while (!$result->EOF) {
$result->fields['params'] = unserialize($result->fields['params']);
$FieldListings[] = $result->fields;
$result->MoveNext();
}
// check for at least one field selected to show
if (!$FieldListings) {
// No fields are checked to show, that's bad
$messageStack->add(RW_RPT_NOROWS, 'caution');
return;
}
// Let's build the sql field list for the general data fields (not totals, blocks or tables)
$strField = '';
$index = 0;
// index each field to allow one field to be used multiple times since $db->Execute returns assoc array
foreach ($FieldListings as $OneField) {
// check for a data field and build sql field list
if ($OneField['params']['index'] == 'Data' || $OneField['params']['index'] == 'BarCode') {
// then it's data field make sure it's not empty
if ($OneField['params']['DataField'] != '') {
$strField .= $OneField['params']['DataField'] . ' as d' . $index . ', ';
$index++;
} else {
// the field is empty, bad news, error and exit
$messageStack->add(RW_RPT_EMPTYFIELD . $OneField['seqnum'], 'error');
return;
}
}
}
$strField = substr($strField, 0, -2);
// strip the extra comma, space and continue
// fetch the sort order and add to group by string to finish ORDER BY string
$strSort = $strGroup;
if (is_array($Prefs['SortListings'])) {
while ($FieldValues = array_shift($Prefs['SortListings'])) {
if ($FieldValues['params']['default'] == '1') {
// then it's the sort by field match
if ($strSort == '') {
$strSort .= $FieldValues['fieldname'];
} else {
$strSort .= ', ' . $FieldValues['fieldname'];
}
$Prefs['filterdesc'] .= RW_RPT_SORTBY . ' ' . $FieldValues['displaydesc'] . '; ';
break;
}
}
}
// fetch date filter info (skip if criteria was passed to generate function)
$strDate = '';
if (!isset($Prefs['PassedCrit'])) {
$dates = gen_build_sql_date($Prefs['datedefault'], $Prefs['datefield']);
$strDate = $dates['sql'];
}
// Fetch the Criteria
$criteria = build_criteria($Prefs['CritListings']);
$strCrit = $criteria['sql'];
if (isset($Prefs['PassedCrit'])) {
// add the passed criteria to the default criteria
for ($i = 0; $i < count($Prefs['PassedCrit']); $i++) {
$temp = explode(':', $Prefs['PassedCrit'][$i]);
switch (count($temp)) {
case 2:
// single value passed (assume equal to)
if ($strCrit) {
$strCrit .= ' and ';
}
$strCrit .= $temp[0] . " = '" . $temp[1] . "'";
break;
case 3:
// range passed (assume between inclusive)
if ($strCrit) {
$strCrit .= ' and ';
}
$strCrit .= $temp[0] . " >= '" . $temp[1] . "' and " . $temp[0] . " <= '" . $temp[2] . "'";
break;
default:
// error in passed parameters
}
}
}
// fetch the tables to query
$tables = array($Prefs['table1'], $Prefs['table2'], $Prefs['table3'], $Prefs['table4'], $Prefs['table5'], $Prefs['table6']);
$sqlTable = DB_PREFIX . $Prefs['table1'];
if ($Prefs['table2']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table2'] . ' on ' . $Prefs['table2criteria'];
}
if ($Prefs['table3']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table3'] . ' on ' . $Prefs['table3criteria'];
}
if ($Prefs['table4']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table4'] . ' on ' . $Prefs['table4criteria'];
}
if ($Prefs['table5']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table5'] . ' on ' . $Prefs['table5criteria'];
}
if ($Prefs['table6']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table6'] . ' on ' . $Prefs['table6criteria'];
}
// Build query string and execute
$sqlCrit = '';
if ($strCrit && $strDate) {
$sqlCrit .= $strDate . ' and ' . $strCrit;
}
if (!$strCrit && $strDate) {
$sqlCrit .= $strDate;
}
if ($strCrit && !$strDate) {
$sqlCrit .= $strCrit;
}
// We now have the sql, find out how many groups in the query (to determine the number of forms)
$PageBreakField = $Prefs['formbreakfield'];
$form_field_list = $Prefs['filenamesource'] == '' ? $PageBreakField : $PageBreakField . ', ' . $Prefs['filenamesource'];
$sql = 'select ' . $form_field_list . ' from ' . $sqlTable;
if ($sqlCrit) {
$sql .= ' where ' . $sqlCrit;
}
$sql .= ' group by ' . $PageBreakField;
if ($strSort) {
$sql .= ' order by ' . $strSort;
}
// replace table aliases (needed for DB_PREFIX values <> '')
$sql = GenReplaceTables($sql, $tables);
// execute sql to see if we have data
//echo 'sql = ' . $sql . '<br />';
$result = $db->Execute($sql);
if (!$result->RecordCount()) {
$messageStack->add(RW_RPT_NOROWS, 'caution');
return;
}
// set the filename for download or email
if ($Prefs['filenameprefix'] || $Prefs['filenamesource']) {
$output['filename'] = $Prefs['filenameprefix'] . $result->fields[strip_tablename($Prefs['filenamesource'])] . '.pdf';
} else {
$output['filename'] = ReplaceNonAllowedCharacters($Prefs['description']) . '.pdf';
}
// create an array for each form
while (!$result->EOF) {
$FormPageID[] = $result->fields[strip_tablename($PageBreakField)];
$result->MoveNext();
}
// retrieve the company information
foreach ($FieldListings as $key => $SingleObject) {
if ($SingleObject['params']['index'] == 'CDta') {
$FieldListings[$key]['params']['TextField'] = constant($SingleObject['params']['DataField']);
}
if ($SingleObject['params']['index'] == 'CBlk') {
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
$TextField = '';
foreach ($SingleObject['params']['Seq'] as $OneField) {
$TextField .= AddSep(constant($OneField['TblField']), $OneField['Processing']);
}
$FieldListings[$key]['params']['TextField'] = $TextField;
}
}
// patch for special_reports (forms) where the data file is generated externally from the standard class
if ($Prefs['special_report']) {
$temp = explode(':', $Prefs['special_report']);
$form_class = $temp[1];
if (file_exists(DIR_FS_MY_FILES . 'custom/reportwriter/classes/' . $form_class . '.php')) {
$success = (include DIR_FS_MY_FILES . 'custom/reportwriter/classes/' . $form_class . '.php');
} elseif (file_exists(DIR_FS_MODULES . 'reportwriter/classes/' . $form_class . '.php')) {
$success = (include DIR_FS_MODULES . 'reportwriter/classes/' . $form_class . '.php');
} else {
$success = false;
}
if (!$success) {
$messageStack->add('Special form class: ' . $form_class . ' was called but could not be found!', 'error');
return;
}
}
// Generate a form for each group element
$output = NULL;
foreach ($FormPageID as $formNum => $Fvalue) {
// find the single line data from the query for the current form page
$TrailingSQL = " from " . $sqlTable . " where " . ($sqlCrit ? $sqlCrit . " AND " : '') . $PageBreakField . " = '" . $Fvalue . "'";
if ($Prefs['special_report']) {
$special_form = new $form_class();
$FieldValues = $special_form->load_query_results($PageBreakField, $Fvalue);
} else {
$sql = "select " . $strField . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
// replace table aliases (needed for DB_PREFIX values <> '')
$result = $db->Execute($sql);
$FieldValues = $result->fields;
}
// load the posted currency values
$posted_currencies = array();
if (ENABLE_MULTI_CURRENCY && strpos($sqlTable, TABLE_JOURNAL_MAIN) !== false) {
$sql = "select currencies_code, currencies_value " . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$posted_currencies = array('currencies_code' => $result->fields['currencies_code'], 'currencies_value' => $result->fields['currencies_value']);
} else {
$posted_currencies = array('currencies_code' => DEFAULT_CURRENCY, 'currencies_value' => 1);
}
foreach ($FieldListings as $key => $SingleObject) {
switch ($SingleObject['params']['index']) {
default:
$value = ProcessData($SingleObject['params']['TextField'], $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
case 'Data':
$value = ProcessData(array_shift($FieldValues), $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
case 'TBlk':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
if ($Prefs['special_report']) {
$TextField = $special_form->load_text_block_data($SingleObject['params']['Seq']);
} else {
$strTxtBlk = '';
// Build the fieldlist
foreach ($SingleObject['params']['Seq'] as $OneField) {
$strTxtBlk .= $OneField['TblField'] . ', ';
}
$strTxtBlk = substr($strTxtBlk, 0, -2);
$sql = "select " . $strTxtBlk . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$TxtBlkValues = $result->fields;
$TextField = '';
$t = 0;
foreach ($TxtBlkValues as $Temp) {
$TextField .= AddSep($Temp, $SingleObject['params']['Seq'][$t]['Processing']);
$t++;
// mapping counter to separator directive
}
}
$FieldListings[$key]['params']['TextField'] = $TextField;
$output .= $FieldListings[$key]['params']['TextField'] . "\n";
break;
case 'Tbl':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
// Build the sql
$tblField = '';
$tblHeading = array();
foreach ($SingleObject['params']['Seq'] as $TableField) {
$tblHeading[] = $TableField['TblDesc'];
}
if ($Prefs['special_report']) {
$SingleObject['params']['Data'] = $special_form->load_table_data($SingleObject['params']['Seq']);
} else {
foreach ($SingleObject['params']['Seq'] as $TableField) {
$tblField .= $TableField['TblField'] . ', ';
}
$tblField = substr($tblField, 0, -2);
// remove the last two chars (comma and space)
$sql = "select " . $tblField . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
while (!$result->EOF) {
$SingleObject['params']['Data'][] = $result->fields;
$result->MoveNext();
}
}
// array_unshift($SingleObject['params']['Data'], $tblHeading); // set the first data element to the headings
$StoredTable = $SingleObject['params'];
foreach ($SingleObject['params']['Data'] as $key => $value) {
$temp = array();
$Col = 0;
foreach ($value as $data_key => $data_element) {
// if ($Params['Seq'][$Col]['TblShow']) {
$value = ProcessData($data_element, $SingleObject['params']['Seq'][$data_key]['Processing']);
$temp[] .= formatReceipt($value, $SingleObject['params']['Seq'][$data_key]['TblColWidth'], $SingleObject['params']['Seq'][$data_key]['FontAlign'], true);
// }
$Col++;
}
$output .= implode("", $temp) . "\n";
}
break;
case 'TDup':
if (!$StoredTable) {
$messageStack->add(RW_RPT_EMPTYTABLE . $SingleObject['seqnum'], 'error');
return;
}
// TBD NEED TO FINISH THIS
break;
case 'Ttl':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
if ($Prefs['special_report']) {
$FieldValues = $special_form->load_total_results($SingleObject['params']);
} else {
$ttlField = '';
foreach ($SingleObject['params']['Seq'] as $Temp) {
$ttlField .= $Temp . '+';
}
$sql = "select sum(" . substr($ttlField, 0, -1) . ") as form_total" . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$FieldValues = $result->fields['form_total'];
}
$value = ProcessData($FieldValues, $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
}
}
// set the printed flag field if provided
if ($Prefs['setprintedflag']) {
$tmp = GenReplaceTables($PageBreakField, $tables);
$id_field = $tmp;
$tmp = GenReplaceTables($Prefs['setprintedflag'], $tables);
$temp = explode('.', $tmp);
if (sizeof($temp) == 2) {
// need the table name and field name
$sql = "update " . $temp[0] . " set " . $temp[1] . " = " . $temp[1] . " + 1 where " . $id_field . " = '" . $Fvalue . "'";
$db->Execute($sql);
}
}
$output .= "\n" . "\n";
// page break
}
$FileSize = strlen($output);
header("Content-type: application/text");
header("Content-disposition: attachment; filename=" . $Prefs['description'] . ".txt; size=" . $FileSize);
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
header('Connection: close');
header('Expires: ' . date('r', time() + 60 * 60));
header('Last-Modified: ' . date('r', time()));
print $output;
exit;
}
function load_text_block_data($Params)
{
$TextField = '';
foreach ($Params as $Temp) {
$fieldname = $Temp->fieldname;
$temp = $Temp->formatting ? ProcessData($this->{$fieldname}, $Temp->formatting) : $this->{$fieldname};
$TextField .= AddSep($temp, $Temp->processing);
}
return $TextField;
}
function load_report_data($report, $Seq, $sql = '', $GrpField = '')
{
global $db;
// prepare the sql by temporarily replacing calculated fields with real fields
$sql_fields = substr($sql, strpos($sql, 'select ') + 7, strpos($sql, ' from ') - 7);
$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], ' '));
}
$sql = $this->replace_special_fields($sql);
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) {
return false;
}
// No data so bail now
// Generate the output data array
$RowCnt = 0;
// Row counter for output data
$ColCnt = 1;
$GrpWorking = false;
while (!$result->EOF) {
$myrow = $result->fields;
// 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
}
//echo 'orig myrow = '; print_r($myrow); echo '<br /><br />';
$myrow = $this->replace_data_fields($myrow, $Seq);
// if myrow is returned false, the sales order line has been filled.
if (!$myrow) {
$ColCnt = 1;
$result->MoveNext();
continue;
// skip the row, order has been filled
}
//echo 'new myrow = '; print_r($myrow); echo '<br /><br />';
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
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;
$result->MoveNext();
}
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++;
}
}
// echo 'output array = '; print_r($OutputArray); echo '<br />'; exit();
return $OutputArray;
}
function ShowTableRow($Params, $myrow, $FillThisRow, $FC, $Heading)
{
$MaxBoxY = $Params['LineYStrt'] + $Params['BoxHeight'];
// figure the max y position on page
$fillReq = $Heading ? $Params['hFill'] : $Params['Fill'];
if ($FillThisRow && $fillReq) {
$this->SetFillColor($FC[0], $FC[1], $FC[2]);
} else {
$this->SetFillColor(255);
}
$this->Cell($Params['BoxWidth'], $MaxBoxY - $this->y0, '', 0, 0, 'L', 1);
$maxY = $this->y0;
// set to current top of row
$Col = 0;
$NextXPos = $Params['LineXStrt'];
foreach ($myrow as $key => $value) {
if ($Params['Seq'][$Col]['TblShow']) {
$font = $Heading && $Params['hFont'] != '' ? $Params['hFont'] : $Params['Seq'][$Col]['Font'];
$size = $Heading && $Params['hFontSize'] != '' ? $Params['hFontSize'] : $Params['Seq'][$Col]['FontSize'];
$color = $Heading && $Params['hFontColor'] != '' ? $Params['hFontColor'] : $Params['Seq'][$Col]['FontColor'];
$align = $Heading && $Params['hFontAlign'] != '' ? $Params['hFontAlign'] : $Params['Seq'][$Col]['FontAlign'];
$this->SetLeftMargin($NextXPos);
$this->SetXY($NextXPos, $this->y0);
$this->SetFont($font, '', $size);
$TC = explode(':', $color);
$this->SetTextColor($TC[0], $TC[1], $TC[2]);
$CellHeight = ($size + RowSpace) * 0.35;
// if ($trunc) $value=$this->TruncData($value, $Params['Seq'][$Col]['TblColWidth']);
// special code for heading and data
if ($Heading) {
if (!$Params['hFontAlign']) {
$align = 'C';
}
// for legacy Pre R1.6 center headings
if ($align == 'A') {
$align = $Params['Seq'][$Col]['FontAlign'];
}
// auto align
} else {
if (isset($Params['Seq'][$Col]['Processing'])) {
$value = ProcessData($value, $Params['Seq'][$Col]['Processing']);
}
}
$this->MultiCell($Params['Seq'][$Col]['TblColWidth'], $CellHeight, $value, 0, $align);
if ($this->GetY() > $maxY) {
$maxY = $this->GetY();
}
$NextXPos += $Params['Seq'][$Col]['TblColWidth'];
}
$Col++;
}
$ThisRowHt = $maxY - $this->y0;
// seee how tall this row was
if ($ThisRowHt > $MaxRowHt) {
$MaxRowHt = $ThisRowHt;
}
// keep that largest row so far to track pagination
$this->y0 = $maxY;
// set y position to largest value for next row
if ($Heading && $Params['hLine']) {
// then it's the heading draw a line after if fill is set
$this->Line($Params['LineXStrt'], $maxY, $Params['LineXStrt'] + $Params['BoxWidth'], $maxY);
$this->y0 = $this->y0 + $Params['hLineSize'] * 0.35;
}
return $MaxRowHt;
}
function add_income_stmt_data($type, $negate = false)
{
global $db, $Prefs;
$account_array = array();
// current period
$sql = "select c.id, c.description, h.debit_amount - h.credit_amount as balance, budget \r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\r\n\t\twhere h.period = " . $this->period . " and c.account_type = " . $type . " \r\n\t\torder by c.id";
$cur_period = $db->Execute($sql);
$sql = "select (sum(h.debit_amount) - sum(h.credit_amount)) as balance, sum(budget) as budget \r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\r\n\t\twhere h.period >= " . $this->first_period . " and h.period <= " . $this->period . " and c.account_type = " . $type . " \r\n\t\tgroup by h.account_id order by c.id";
$ytd_period = $db->Execute($sql);
// last year to date
$sql = "select c.id, c.description, h.debit_amount - h.credit_amount as balance, budget \r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\r\n\t\twhere h.period = " . $this->ly_period . " and c.account_type = " . $type . " \r\n\t\torder by c.id";
$lcur_period = $db->Execute($sql);
$sql = "select (sum(h.debit_amount) - sum(h.credit_amount)) as balance, sum(budget) as budget \r\n\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\r\n\t\twhere h.period >= " . $this->ly_first_period . " and h.period <= " . $this->ly_period . " and c.account_type = " . $type . " \r\n\t\tgroup by h.account_id order by c.id";
$lytd_period = $db->Execute($sql);
/*
// beginning balances (not needed for income statement since these account types start the year at 0)
$sql = "select beginning_balance
from " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id
where h.period = " . $this->first_period . " and c.account_type = " . $type . "
group by h.account_id order by c.id";
$beg_balance = $db->Execute($sql);
*/
$cur_total_1 = 0;
$ytd_total_1 = 0;
$ly_cur_total_1 = 0;
$ly_ytd_total_1 = 0;
$bgt_total_1 = 0;
$bgt_ytd_total_1 = 0;
$temp = array();
$total = array('description' => TEXT_TOTAL . ' ' . $this->coa_types[$type]['text']);
while (!$cur_period->EOF) {
$factor = $negate ? -1 : 1;
$temp[$cur_period->fields['id']]['description'] = $cur_period->fields['description'];
$temp[$cur_period->fields['id']]['current'] = $factor * $cur_period->fields['balance'];
$temp[$cur_period->fields['id']]['current_ytd'] = $factor * $ytd_period->fields['balance'];
$temp[$lcur_period->fields['id']]['ly_current'] = $factor * $lcur_period->fields['balance'];
$temp[$lcur_period->fields['id']]['ly_ytd'] = $factor * $lytd_period->fields['balance'];
$temp[$cur_period->fields['id']]['budget_cur'] = $factor * $cur_period->fields['budget'];
$temp[$cur_period->fields['id']]['budget_ytd'] = $factor * $ytd_period->fields['budget'];
$total['current'] += $factor * $cur_period->fields['balance'];
$total['current_ytd'] += $factor * $ytd_period->fields['balance'];
$total['ly_current'] += $factor * $lcur_period->fields['balance'];
$total['ly_ytd'] += $factor * $lytd_period->fields['balance'];
$total['budget_cur'] += $factor * $cur_period->fields['budget'];
$total['budget_ytd'] += $factor * $ytd_period->fields['budget'];
$cur_period->MoveNext();
$ytd_period->MoveNext();
$lcur_period->MoveNext();
$lytd_period->MoveNext();
// $beg_balance->MoveNext();
}
foreach ($temp as $acct) {
$line = array(0 => 'd');
foreach ($Prefs['FieldListings'] as $value) {
$line[] = ProcessData($acct[$value['fieldname']], $value['params']['processing']);
}
$this->inc_stmt_data[] = $line;
}
$this->add_heading_line();
$line = array(0 => 'd');
foreach ($Prefs['FieldListings'] as $value) {
$line[] = ProcessData($total[$value['fieldname']], $value['params']['processing']);
}
$this->inc_stmt_data[] = $line;
return $total;
}
function add_income_stmt_data($type, $first_period, $period, $negate = false)
{
global $db, $Seq;
$account_array = array();
$sql = "select c.id, c.description, h.debit_amount - h.credit_amount as balance, budget \n\t\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\n\t\t\twhere h.period = " . $period . " and c.account_type = " . $type . " \n\t\t\torder by c.id";
$cur_period = $db->Execute($sql);
$sql = "select (sum(h.debit_amount) - sum(h.credit_amount)) as balance, sum(budget) as budget \n\t\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\n\t\t\twhere h.period >= " . $first_period . " and h.period <= " . $period . " and c.account_type = " . $type . " \n\t\t\tgroup by h.account_id order by c.id";
$ytd_period = $db->Execute($sql);
$sql = "select beginning_balance \n\t\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\n\t\t\twhere h.period = " . $first_period . " and c.account_type = " . $type . " \n\t\t\tgroup by h.account_id order by c.id";
$beg_balance = $db->Execute($sql);
$cur_total_1 = 0;
$ytd_total_1 = 0;
$bgt_total_1 = 0;
while (!$cur_period->EOF) {
if ($negate) {
$cur_total_1 += -$cur_period->fields['balance'];
$ytd_total_1 += -$beg_balance->fields['beginning_balance'] - $ytd_period->fields['balance'];
$bgt_total_1 += -$ytd_period->fields['budget'];
$cur_temp = ProcessData(-$cur_period->fields['balance'], $Seq[1]['processing']);
$ytd_temp = ProcessData(-$beg_balance->fields['beginning_balance'] - $ytd_period->fields['balance'], $Seq[2]['processing']);
$bgt_temp = ProcessData(-$ytd_period->fields['budget'], $Seq[2]['processing']);
} else {
$cur_total_1 += $cur_period->fields['balance'];
$ytd_total_1 += $beg_balance->fields['beginning_balance'] + $ytd_period->fields['balance'];
$bgt_total_1 += $ytd_period->fields['budget'];
$cur_temp = ProcessData($cur_period->fields['balance'], $Seq[1]['processing']);
$ytd_temp = ProcessData($beg_balance->fields['beginning_balance'] + $ytd_period->fields['balance'], $Seq[2]['processing']);
$bgt_temp = ProcessData($ytd_period->fields['budget'], $Seq[2]['processing']);
}
$account_array[$cur_period->fields['id']] = array($cur_period->fields['description'], $cur_temp, $ytd_temp);
$cur_period->MoveNext();
$ytd_period->MoveNext();
$beg_balance->MoveNext();
}
$this->total_2 = $cur_total_1;
$this->total_3 = $ytd_total_1;
return $account_array;
}
function add_bal_sheet_data($the_list, $negate_array, $period)
{
global $db, $Seq;
foreach ($the_list as $key => $account_type) {
$sql = "select h.beginning_balance + h.debit_amount - h.credit_amount as balance, c.description \r\n\t\t\t\tfrom " . TABLE_CHART_OF_ACCOUNTS . " c inner join " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " h on c.id = h.account_id\r\n\t\t\t\twhere h.period = " . $period . " and c.account_type = " . $account_type;
$result = $db->Execute($sql);
$total_1 = 0;
while (!$result->EOF) {
if ($negate_array[$key]) {
$total_1 -= $result->fields['balance'];
$temp = ProcessData(-$result->fields['balance'], $Seq[1]['processing']);
} else {
$total_1 += $result->fields['balance'];
$temp = ProcessData($result->fields['balance'], $Seq[1]['processing']);
}
$this->bal_sheet_data[] = array('d', $result->fields['description'], $temp, '', '');
$result->MoveNext();
}
$this->bal_tot_2 += $total_1;
$total_1 = ProcessData($total_1, $Seq[1]['processing']);
$this->bal_sheet_data[] = array('d', TEXT_TOTAL . ' ' . $this->coa_types[$account_type]['text'], '', $total_1, '');
}
$this->bal_tot_3 += $this->bal_tot_2;
}
function BuildDataArray($sql, $report)
{
// for reports only
global $db, $Heading, $Seq, $posted_currencies, $messageStack, $currencies;
$posted_currencies = array('currencies_code' => DEFAULT_CURRENCY, 'currencies_value' => 1);
// use default currency
// See if we need to group, fetch the group fieldname
$GrpFieldName = '';
if (is_array($report->grouplist)) {
while ($Temp = array_shift($report->grouplist)) {
if ($Temp->default) {
$GrpFieldName = $Temp->fieldname;
$GrpFieldProcessing = $Temp->processing;
break;
}
}
}
// Build the sequence map of retrieved fields, order is as user wants it
$i = 0;
$GrpField = '';
foreach ($report->fieldlist as $DataFields) {
if ($DataFields->visible) {
if ($DataFields->fieldname == $GrpFieldName) {
$GrpField = 'c' . $i;
}
$Heading[] = $DataFields->description;
$Seq[$i]['break'] = $DataFields->columnbreak;
$Seq[$i]['fieldname'] = 'c' . $i;
$Seq[$i]['total'] = $DataFields->total;
$Seq[$i]['processing'] = $DataFields->processing;
$Seq[$i]['align'] = $DataFields->align;
$Seq[$i]['grptotal'] = '';
$Seq[$i]['rpttotal'] = '';
$i++;
}
}
// patch for special_reports where the data file is generated externally from the standard function
if ($report->special_class) {
if (!($path = find_special_class($report->special_class))) {
return false;
}
load_special_language($path, $report->special_class);
require_once $path . '/classes/' . $report->special_class . '.php';
$sp_report = new $report->special_class();
return $sp_report->load_report_data($report, $Seq, $sql, $GrpField);
// the special report formats all of the data, we're done
}
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) {
return false;
}
// No data so bail now
// Generate the output data array
$RowCnt = 0;
$ColCnt = 1;
$GrpWorking = false;
while (!$result->EOF) {
$myrow = $result->fields;
// 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:' . ProcessData($GrpWorking, $GrpFieldProcessing);
foreach ($Seq as $offset => $TotalCtl) {
$OutputArray[$RowCnt][$offset + 1] = $TotalCtl['total'] == '1' ? $TotalCtl['grptotal'] : ' ';
$Seq[$offset]['grptotal'] = '';
// reset the total
}
$RowCnt++;
// go to next row
}
$GrpWorking = $myrow[$GrpField];
// set to new grouping value
}
foreach ($Seq as $key => $TableCtl) {
//
if ($report->totalonly != '1') {
// insert data into output array and set to next column
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
$OutputArray[$RowCnt][$ColCnt] = ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']);
}
$ColCnt++;
if ($TableCtl['total']) {
// add to the running total if need be
$Seq[$key]['grptotal'] += $currencies->clean_value(ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']));
$Seq[$key]['rpttotal'] += $currencies->clean_value(ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']));
}
}
$RowCnt++;
$ColCnt = 1;
$result->MoveNext();
}
if ($GrpWorking !== false) {
// if we collected group data show the final group total
$OutputArray[$RowCnt][0] = 'g:' . ProcessData($GrpWorking, $GrpFieldProcessing);
foreach ($Seq as $TotalCtl) {
$OutputArray[$RowCnt][$ColCnt] = $TotalCtl['total'] == '1' ? $TotalCtl['grptotal'] : ' ';
$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] = $TotalCtl['rpttotal'];
} else {
$OutputArray[$RowCnt][$ColCnt] = ' ';
}
$ColCnt++;
}
}
//echo 'output array = '; print_r($OutputArray); echo '<br />'; exit();
return $OutputArray;
}
function load_report_data($Prefs, $Seq, $sql = '', $GrpField = '')
{
global $db;
// prepare the sql by temporarily replacing calculated fields with real fields
$sql_fields = substr($sql, strpos($sql, 'select ') + 7, strpos($sql, ' from ') - 7);
$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], ' '));
}
$sql = $this->replace_special_fields($sql);
//echo 'sql = ' . $sql . '<br />'; exit;
//echo 'Prefs = '; print_r($Prefs); echo '<br />';
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) {
return false;
}
// No data so bail now
// Generate the output data array
/*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/
$this->gl_account = $Prefs['fromvalue1'];
// assumes that the gl account is the first criteria, equal to
if (!$this->gl_account) {
return false;
}
// No gl account so bail now
$dates = gen_build_sql_date($Prefs['datedefault'], $Prefs['datefield']);
$post_date = $dates['start_date'];
$period = gen_calculate_period($post_date, $hide_error = true);
if (!$period) {
$period = 1;
}
$temp = $db->Execute("select beginning_balance from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\twhere account_id = '" . $this->gl_account . "' and period = " . $period);
$beginning_balance = $temp->fields['beginning_balance'];
// load the payments and deposits for the current period
$sql = "select sum(i.debit_amount) as deposit, sum(i.credit_amount) as withdrawal\r\n\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t\twhere m.period = " . $period . " and m.post_date < '" . $post_date . "' and i.gl_account = '" . $this->gl_account . "'";
$temp = $db->Execute($sql);
$this->beginning_balance = $beginning_balance + $temp->fields['deposit'] - $temp->fields['withdrawal'];
$this->current_balance = $this->beginning_balance;
/*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/
$RowCnt = 0;
// Row counter for output data
$ColCnt = 1;
$GrpWorking = false;
while (!$result->EOF) {
$myrow = $result->fields;
// 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 />';
// build the new balance
foreach ($Seq as $key => $TableCtl) {
// calculate the new balance
//echo 'key = ' . $key . ' and fieldname = '; print_r($Prefs['FieldListings'][$key+1]['fieldname']); echo '<br />';
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == '[table2].debit_amount') {
$this->beginning_balance += $myrow[$TableCtl['fieldname']];
}
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == '[table2].credit_amount') {
$this->beginning_balance -= $myrow[$TableCtl['fieldname']];
}
}
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;
$result->MoveNext();
}
// add an extra line at the bottom with the newest balance
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
foreach ($Seq as $key => $TableCtl) {
// calculate the new balance
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == 'beg_balance') {
$OutputArray[$RowCnt][$ColCnt] = ProcessData($this->beginning_balance, $TableCtl['processing']);
} else {
$OutputArray[$RowCnt][$ColCnt] = '';
}
$ColCnt++;
}
$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:' . $Prefs['description'];
foreach ($Seq as $TotalCtl) {
if ($TotalCtl['total']) {
$OutputArray[$RowCnt][$ColCnt] = ProcessData($TotalCtl['rpttotal'], $TotalCtl['processing']);
} else {
$OutputArray[$RowCnt][$ColCnt] = ' ';
}
$ColCnt++;
}
}
// echo 'output array = '; print_r($OutputArray); echo '<br />'; exit();
return $OutputArray;
}
