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