/**
* @param Collection $collection
* @param array $collectionFilters
* @param array $accountIds
* @return array
*/
public static function getBalanceReportData(Xhb $xhb, array $collectionFilters, array $accountIds, $withGrandTotal = false)
{
$return = array('labels' => array(), 'datasets' => array());
$operationCollection = $xhb->getOperationCollection()->setFlag('skip_aggregated_fields', true);
$processedFilters = AccountOperation::applyFiltersOnCollection($operationCollection, $collectionFilters);
$firstOp = $operationCollection->getFirstItem();
if (!$firstOp) {
return $return;
}
$startDate = isset($processedFilters['start_date']) ? $processedFilters['start_date'] : $firstOp->getDateModel();
$endDate = isset($processedFilters['end_date']) ? $processedFilters['end_date'] : new \DateTime('last day of this month');
$datePeriod = self::getDatePeriod($xhb, $startDate, $endDate);
$rawBalanceData = array();
foreach ($accountIds as $accountId) {
$calculator = new Calculator($xhb, $accountId);
$rawBalanceData[$accountId] = $calculator->getBalanceByDate($datePeriod);
}
$now = new \DateTime();
$idx = 0;
$addLabels = true;
$grandTotal = array();
foreach ($rawBalanceData as $accountId => $accountBalanceData) {
$periodIdx = 0;
if (!isset($grandTotal[$idx])) {
$grandTotal[$idx] = array();
}
$return['datasets'][$idx] = array('label' => $xhb->getAccount($accountId)->getName(), 'strokeColor' => Output::rgbToCss(Chart::getColor($idx)), 'pointColor' => Output::rgbToCss(Chart::getColor($idx)), 'pointHighlightFill' => '#fff', 'pointHighlightStroke' => '#bbb', 'data' => array());
foreach ($accountBalanceData as $periodBalance) {
if ($addLabels) {
$return['labels'][] = I18n::instance()->date($periodBalance['date']);
}
if ($periodBalance['date'] < $now) {
$return['datasets'][$idx]['data'][$periodIdx] = array('x' => $periodBalance['date']->getTimestamp(), 'y' => $periodBalance['balance']);
$grandTotal[$periodIdx][] = array('x' => $periodBalance['date'], 'y' => $periodBalance['balance']);
} else {
$return['datasets'][$idx]['data'][$periodIdx] = array('x' => $periodBalance['date']->getTimestamp());
$grandTotal[$periodIdx][] = array('x' => $periodBalance['date']);
}
$periodIdx++;
}
$addLabels = false;
$idx++;
}
if ($withGrandTotal) {
$return['datasets'][$idx] = array('label' => I18n::instance()->tr('Grand Total'), 'strokeColor' => Output::rgbToCss(array(0, 0, 0)), 'pointColor' => Output::rgbToCss(array(0, 0, 0)), 'pointHighlightFill' => '#fff', 'pointHighlightStroke' => '#bbb', 'data' => array());
$periodIdx = 0;
foreach ($grandTotal as $periodAccountsBalance) {
$balance = array_sum(array_column($periodAccountsBalance, 'y'));
$date = current($periodAccountsBalance)['x'];
$balanceData = array('x' => $date->getTimestamp());
if ($date < $now) {
$balanceData['y'] = $balance;
}
$return['datasets'][$idx]['data'][$periodIdx] = $balanceData;
$periodIdx++;
}
}
return $return;
}
/**
* @param \Xhb\Model\Operation\Calculator $object
* @param string $type
* @param int $referenceTime
* @return float
*/
public function getCurrentBalance(\Xhb\Model\Operation\Calculator $object, $type, $referenceTime)
{
$txnTypeFilter = $object->getBalanceStatuses($type);
$operationCollection = $this->getOperationCollection($object)->addFieldToSelect(array('balance' => new Expression('SUM(amount)')))->addFieldToFilter('st', array('in' => $txnTypeFilter))->addFieldToFilter('date', array('le' => $referenceTime))->setLimit(1);
$item = $operationCollection->getFirstItem();
if ($item) {
return $item->getBalance();
}
return 0.0;
}
/**
* @param AbstractCollection $collection
* @param $filters
* @return array
*/
public static function applyFiltersOnCollection(AbstractCollection $collection, $filters)
{
$processedFilters = array();
$xhb = $collection->getXhb();
foreach ($filters as $name => $value) {
switch ($name) {
case 'period':
$periods = $xhb->getDateHelper()->getPredefinedTimePeriods();
$period = isset($periods[$filters['period']]) ? $periods[$filters['period']] : $periods[DateHelper::TIME_PERIOD_DEFAULT];
$ge = $period['start'];
$le = $period['end'];
$collection->addFieldToFilter('date', array('ge' => Date::dateToJd($ge)));
$processedFilters['start_date'] = $ge;
$collection->addFieldToFilter('date', array('le' => Date::dateToJd($le)));
$processedFilters['end_date'] = $le;
break;
case 'type':
switch ($value) {
case 'outcome':
$collection->addFieldToFilter('amount', array('lt' => 0));
$processedFilters['min_amount'] = 0;
break;
case 'income':
$collection->addFieldToFilter('amount', array('gt' => 0));
$processedFilters['max_amount'] = 0;
break;
case 'any_type':
default:
//no filter
break;
}
break;
case 'status':
switch ($value) {
case 'uncategorized':
$collection->addFieldToFilter('category', array('null' => true))->addFieldToFilter('scat', array('null' => true));
$processedFilters['categories'] = null;
break;
case 'unreconciled':
$collection->addFieldToFilter('st', array('in' => Operation\Calculator::getUnreconciliedStatuses()));
$processedFilters['status'] = implode(',', Operation\Calculator::getUnreconciliedStatuses());
break;
case 'uncleared':
$collection->addFieldToFilter('st', array('in' => Operation\Calculator::getUnclearedStatuses()));
$processedFilters['status'] = implode(',', Operation\Calculator::getUnclearedStatuses());
break;
case 'reconciled':
$collection->addFieldToFilter('st', array('in' => Operation\Calculator::getReconciliedStatuses()));
$processedFilters['status'] = implode(',', Operation\Calculator::getReconciliedStatuses());
break;
case 'cleared':
$collection->addFieldToFilter('st', array('in' => Operation\Calculator::getClearedStatuses()));
$processedFilters['status'] = implode(',', Operation\Calculator::getClearedStatuses());
break;
case 'any_status':
default:
//no filter
break;
}
break;
case 'search':
$value = trim($value);
if ($value) {
$collection->addFieldToFilter('aggregate_search', array('like' => "%{$value}%"));
$processedFilters['search'] = $value;
}
break;
}
}
return $processedFilters;
}