public function create($period)
{
$balance = new GLBalance();
// Set the identifier field - this is used by bulk_insert
// to count the distinct rows that should be inserted
$balance->identifierField = "glcentre_id||'-'||glaccount_id";
$balance->orderby = '';
$balances = new GLBalanceCollection($balance);
$balances->setTablename('gl_year_to_date_summary');
$balances->orderby = '';
$sh = new SearchHandler($balances, false);
$fields = array($period->id . ' as glperiods_id');
$fields = array('glcentre_id', 'glaccount_id', 'glperiods_id', 'mth_actual', 'ytd_actual', 'usercompanyid', "'" . EGS_USERNAME . "' as createdby", "'" . EGS_USERNAME . "' as alteredby");
$sh->setFields($fields);
$sh->addConstraint(new Constraint('year', '=', $period->year));
$sh->addConstraint(new Constraint('period', '=', $period->period));
// Insert the year to date values
$result = $this->bulk_insert(array('glcentre_id', 'glaccount_id', 'glperiods_id', 'mth_actual', 'ytd_actual', 'usercompanyid', 'createdby', 'alteredby'), $sh);
return $result;
}
public function printtrialbalance()
{
$options = $this->reportOptions();
// build options array
$options['filename'] = 'TrialBalance_' . date('d-m-Y');
// we use status in other print functions, however here we base it on if ajax print is or isn't set
if (!$this->isPrinting()) {
return $options;
}
if (isset($this->_data['encoded_query_data'])) {
$original_data = $this->decode_original_form_data($this->_data['encoded_query_data']);
$this->_data['Search'] = $original_data['Search'];
}
// get the saved search
$this->setSearch('glbalancesSearch', 'trialBalance');
// get the gl periods value from the saved search
$glperiods_id = $this->search->getValue('glperiods_id');
// get current total
$balance = $this->_uses[$this->modeltype];
$current = $balance->getSum($glperiods_id);
// get ytd total
$period = DataObjectFactory::Factory('GLPeriod');
if ($period->load($glperiods_id)) {
$ytd_periods = $period->getIdsYTD($period->period, $period->year);
$ytd = $balance->getSum($ytd_periods);
} else {
$ytd_periods = array();
$ytd = 0;
}
// load collection
$collection = new GLBalanceCollection($balance);
$collection->getYTD($ytd_periods, TRUE, TRUE);
$col_arr = array();
// construct data array
foreach ($collection as $key => $value) {
$ytd_actual = $value->value;
$ytd_budget = $value->getYTDBudget($glperiods_id);
$month_actual = $value->getCurrent($glperiods_id);
$month_budget = $value->getCurrentBudget($glperiods_id);
$col_arr[] = array('id' => $value->id, 'centre' => $value->centre, 'glcentre_id' => $value->glcentre_id, 'account' => $value->account, 'glaccount_id' => $value->glaccount_id, 'period' => $period->getIdentifierValue(), 'ytd_actual' => sprintf("%01.2f", $ytd_actual), 'ytd_budget' => sprintf("%01.2f", $ytd_budget), 'ytd_variance' => sprintf("%01.2f", $ytd_budget - $ytd_actual), 'month_actual' => sprintf("%01.2f", $month_actual), 'month_budget' => sprintf("%01.2f", $month_budget), 'month_variance' => sprintf("%01.2f", $month_budget - $month_actual));
}
if ($this->_data['print']['printtype'] !== 'csv') {
// add totals to array
$col_arr[] = array('period' => 'Totals', 'ytd_actual' => $ytd, 'month_actual' => $current);
}
// construct headings array
$cols = array('account' => 'Account', 'centre' => 'Centre', 'period' => 'Period', 'ytd_actual' => 'YTD Actual', 'ytd_budget' => 'YTD Budget', 'ytd_variance' => 'YTD Variance', 'month_actual' => 'Month Actual', 'month_budget' => 'Month Budget', 'month_variance' => 'Month Variance');
if ($this->_data['print']['printtype'] === 'csv') {
$csv = $this->generate_csv($this->_data['print'], $col_arr, array_keys(current($col_arr)));
$options['csv_source'] = $csv;
} else {
// build XSL
$xsl = $this->build_custom_xsl($collection, 'PrintCollection', 'Trial Balance - ' . date('d/m/Y'), $cols, $this->_data['col_widths']);
// build XML
$xml = $this->build_custom_xml($col_arr, $cols);
// set XSL / XML to options
$options['xslSource'] = $xsl;
$options['xmlSource'] = $xml;
}
// execute the print output function, echo the returned json for jquery
echo $this->constructOutput($this->_data['print'], $options);
exit;
}
static function yearEnd($period, &$errors)
{
assetHandling::yearEnd($errors);
$period_ids = $period->getIdsYTD($period->period, $period->year);
$startperiod = $period->getNewStartPeriod($errors);
$result = $startperiod;
$glbalance = DataObjectFactory::Factory('GLBalance');
$newbalances = array();
$balances = new GLBalanceCollection($glbalance);
$balances->getYearEndBalances($period_ids, 'P');
$glparam = DataObjectFactory::Factory('GLParams');
$placcount_id = $glparam->retained_profits_account();
$plcentre_id = $glparam->balance_sheet_cost_centre();
$plbalance = 0;
foreach ($balances as $balance) {
if (count($errors) > 0) {
$result = false;
break;
}
$plbalance = $balance->value;
}
$balances = new GLBalanceCollection($glbalance);
$balances->getYearEndBalances($period_ids, 'B');
foreach ($balances as $balance) {
if (count($errors) > 0) {
$result = false;
break;
}
$data['glperiods_id'] = $startperiod->id;
$data['glaccount_id'] = $balance->glaccount_id;
$data['glcentre_id'] = $balance->glcentre_id;
$data['value'] = $balance->value;
if ($balance->glaccount_id == $placcount_id && $balance->glcentre_id == $plcentre_id) {
$data['value'] += $plbalance;
$plbalance = 0;
}
$newbalances[] = DataObject::Factory($data, $errors, $balance);
}
if ($result && count($newbalances) > 0) {
foreach ($newbalances as $balance) {
if ($result) {
$result = $balance->save();
}
}
}
if ($plbalance != 0) {
$data['glperiods_id'] = $startperiod->id;
$data['glaccount_id'] = $placcount_id;
$data['glcentre_id'] = $plcentre_id;
$data['value'] = $plbalance;
$balance = DataObject::Factory($data, $errors, 'GLBalance');
if ($balance) {
if (!$balance->save()) {
$errors[] = 'Failed to create P+L balance';
}
}
}
}