function statistics(&$rows)
{
$statistics = parent::statistics($rows);
if (!$this->_having) {
$select = "\n SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount ) as count,\n SUM({$this->_aliases['civicrm_pledge']}.amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg\n ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Pledged', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['count '] = array('value' => $dao->count, 'title' => 'Total No Pledges');
$statistics['counts']['avg '] = array('value' => $dao->avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
}
}
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$avg = null;
$select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,\n\t\t\t\t\t\t\t\t\tSUM( {$this->_aliases['civicrm_line_item']}.line_total )\t as amount\n\t\t\t\t\t\t";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
if ($dao->count && $dao->amount) {
$avg = $dao->amount / $dao->count;
}
$statistics['counts']['count'] = array('value' => $dao->count, 'title' => 'Total Participants', 'type' => CRM_Utils_Type::T_INT);
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Income', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['avg '] = array('value' => $avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
if (!empty($rows)) {
$select = "\n SELECT\n SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total LifeTime', 'type' => CRM_Utils_Type::T_MONEY);
}
}
return $statistics;
}
function statistics(&$rows)
{
return parent::statistics($rows);
}
/**
* Set statistics.
*
* @param array $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
$onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
$group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
$this->from('contribution');
$this->customDataFrom();
$contriQuery = "\nCOUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,\nSUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,\n{$this->_aliases['civicrm_contribution']}.currency as currency\n{$this->_from} {$this->_where}";
if ($softCredit) {
$this->from();
$select = "\nCOUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,\nSUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
$contriQuery = "{$select}, {$contriQuery}";
$softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency\n {$this->_from} {$this->_where} {$group} {$this->_having}";
}
$contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}";
$contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
$totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array();
$count = $softCount = 0;
while ($contriDAO->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) . " (" . $contriDAO->civicrm_contribution_total_amount_count . ")";
$average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency);
$count += $contriDAO->civicrm_contribution_total_amount_count;
}
$groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount";
$orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC";
$modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count,\n SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,\n currency\n FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount,\n {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency";
$mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
$medianSQL = "{$this->_from} {$this->_where}";
$median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, $this->_aliases['civicrm_contribution']);
if ($softCredit) {
$softDAO = CRM_Core_DAO::executeQuery($softSQL);
while ($softDAO->fetch()) {
$softTotalAmount[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) . " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")";
$softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency);
$softCount += $softDAO->civicrm_contribution_soft_soft_amount_count;
}
}
if (!$onlySoftCredit) {
$statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Total Contributions'), 'value' => $count);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['mode'] = array('title' => ts('Mode'), 'value' => implode(', ', $mode), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['median'] = array('title' => ts('Median'), 'value' => implode(', ', $median), 'type' => CRM_Utils_Type::T_STRING);
}
if ($softCredit) {
$statistics['counts']['soft_amount'] = array('title' => ts('Total Soft Credit Amount'), 'value' => implode(', ', $softTotalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['soft_count'] = array('title' => ts('Total Soft Credits'), 'value' => $softCount);
$statistics['counts']['soft_avg'] = array('title' => ts('Average Soft Credit'), 'value' => implode(', ', $softAverage), 'type' => CRM_Utils_Type::T_STRING);
}
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,\n {$this->_aliases['civicrm_contribution']}.currency,\n SUM(CASE\n WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL\n THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount\n ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount\n END) as amount\n";
$sql = "{$select} {$this->_from} {$this->_where}\n GROUP BY {$this->_aliases['civicrm_contribution']}.currency\n";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
$avg[] = CRM_Utils_Money::format(round($dao->amount / $dao->count, 2), $dao->currency);
}
$statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['avg'] = array('value' => implode(', ', $avg), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
//hack filter display for relationship type
$type = substr($this->_params['relationship_type_id_value'], -3);
foreach ($statistics['filters'] as $id => $value) {
if ($value['title'] == 'Relationship Type') {
$statistics['filters'][$id]['value'] = 'Is equal to ' . $this->relationTypes[$this->relationshipId . '_' . $type];
}
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "SELECT DISTINCT {$this->_aliases['civicrm_contribution']}.id";
$sql = "SELECT COUNT(cc.id) as count, SUM(cc.total_amount) as amount, ROUND(AVG(cc.total_amount), 2) as avg, cc.currency as currency\n FROM civicrm_contribution cc\n WHERE cc.id IN ({$select} {$this->_from} {$this->_where})\n GROUP BY cc.currency";
$dao = CRM_Core_DAO::executeQuery($sql);
$totalAmount = $average = array();
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
}
$statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
//replace Advisor id by name
if (CRM_Utils_Array::value('filters', $statistics)) {
foreach ($statistics['filters'] as $key => $fields) {
if (CRM_Utils_Array::value('title', $fields) == 'Advisor') {
$statistics['filters'][$key]['value'] = 'Is equal to ' . $this->teacherOptions[$this->_params['id_teacher_value']];
}
}
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "select COUNT( DISTINCT( {$this->_aliases['civicrm_address']}.country_id))";
$sql = "{$select} {$this->_from} {$this->_where}";
$countryCount = CRM_Core_DAO::singleValueQuery($sql);
$statistics['counts']['case'] = array('title' => ts('Total Number of Cases '), 'value' => isset($statistics['counts']['rowsFound']) ? $statistics['counts']['rowsFound']['value'] : count($rows));
$statistics['counts']['country'] = array('title' => ts('Total Number of Countries '), 'value' => $countryCount);
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$totalType = $totalActivity = $totalDuration = 0;
$query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id,\n {$this->_tempTableName}.civicrm_activity_id_count,\n {$this->_tempDurationSumTableName}.civicrm_activity_duration_total\n FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}\n ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
$actDAO = CRM_Core_DAO::executeQuery($query);
$activityTypesCount = array();
while ($actDAO->fetch()) {
if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) {
$activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id;
}
$totalActivity += $actDAO->civicrm_activity_id_count;
$totalDuration += $actDAO->civicrm_activity_duration_total;
}
$totalType = count($activityTypesCount);
$statistics['counts']['type'] = array('title' => ts('Total Types'), 'value' => $totalType);
$statistics['counts']['activities'] = array('title' => ts('Total Number of Activities'), 'value' => $totalActivity);
$statistics['counts']['duration'] = array('title' => ts('Total Duration (in Minutes)'), 'value' => $totalDuration);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "\n SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,\n IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,\n IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,\n COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount\n ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['count '] = array('value' => $dao->count, 'title' => 'Total Donations');
$statistics['counts']['memberCount'] = array('value' => $dao->memberCount, 'title' => 'Total Members');
$statistics['counts']['avg '] = array('value' => $dao->avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
if (!(int) $statistics['counts']['amount']['value']) {
//if total amount is zero then hide Chart Options
$this->assign('chartSupported', false);
}
}
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$totalAmount = array();
$count = 0;
$select = "\n SELECT \n SUM( cg.{$this->customFields['time_scheduled_minutes']['column_name']} ) AS scheduled,\n SUM( cg.{$this->customFields['time_completed_minutes']['column_name']} ) AS completed";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$scheduled = $dao->scheduled;
$completed = $dao->completed;
}
$statistics['counts']['scheduled'] = array('title' => ts('Total Time Scheduled in Minutes', array('domain' => 'org.civicrm.volunteer')), 'value' => $scheduled, 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['completed'] = array('title' => ts('Total Time Completed in Minutes', array('domain' => 'org.civicrm.volunteer')), 'value' => $completed, 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$totalAmount = $average = array();
$count = 0;
$select = "\n SELECT COUNT({$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount ) as count,\n SUM( {$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount), 2) as avg\n ";
$sql = "{$select}\n FROM cdntaxreceipts_log {$this->_aliases['civicrm_cdntaxreceipts_log']}\n {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, 'CAD');
$average[] = CRM_Utils_Money::format($dao->avg, 'CAD');
$count += $dao->count;
}
$statistics['counts']['amount'] = array('title' => ts('Total Amount Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Number Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => $count);
$statistics['counts']['avg'] = array('title' => ts('Average Amount Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$totalAmount = $average = array();
$count = 0;
$select = "\n SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,\n SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,\n {$this->_aliases['civicrm_contribution']}.currency as currency\n ";
$group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
$sql = "{$select} {$this->_from} {$this->_where} {$group}";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
$count += $dao->count;
}
$statistics['counts']['amount'] = array('title' => ts('Total Amount (Contributions)'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Total Contributions'), 'value' => $count);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
// Stats for soft credits
if ($this->_softFrom && CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) != 'contributions_only') {
$totalAmount = $average = array();
$count = 0;
$select = "\nSELECT COUNT(contribution_soft_civireport.amount ) as count,\n SUM(contribution_soft_civireport.amount ) as amount,\n ROUND(AVG(contribution_soft_civireport.amount), 2) as avg,\n {$this->_aliases['civicrm_contribution']}.currency as currency";
$sql = "\n{$select}\n{$this->_softFrom}\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
$count += $dao->count;
}
$statistics['counts']['softamount'] = array('title' => ts('Total Amount (Soft Credits)'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['softcount'] = array('title' => ts('Total Soft Credits'), 'value' => $count);
$statistics['counts']['softavg'] = array('title' => ts('Average (Soft Credits)'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
$onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
$totalAmount = $average = $softTotalAmount = $softAverage = array();
$group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
$this->from('contribution');
$contriSQL = "SELECT\nCOUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,\nSUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,\n{$this->_aliases['civicrm_contribution']}.currency as currency\n{$this->_from} {$this->_where} {$group} {$this->_having}";
if ($softCredit) {
$this->from();
$softSQL = "SELECT\nCOUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,\nSUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg,\n{$this->_aliases['civicrm_contribution']}.currency as currency\n{$this->_from} {$this->_where} {$group} {$this->_having}";
}
$contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
$totalAmount = $average = $softTotalAmount = $softAverage = array();
$count = $softCount = 0;
while ($contriDAO->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) . " (" . $contriDAO->civicrm_contribution_total_amount_count . ")";
$average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency);
$count += $contriDAO->civicrm_contribution_total_amount_count;
}
if ($softCredit) {
$softDAO = CRM_Core_DAO::executeQuery($softSQL);
while ($softDAO->fetch()) {
$softTotalAmount[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) . " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")";
$softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency);
$softCount += $softDAO->civicrm_contribution_soft_soft_amount_count;
}
}
if (!$onlySoftCredit) {
$statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Total Contributions'), 'value' => $count);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
}
if ($softCredit) {
$statistics['counts']['soft_amount'] = array('title' => ts('Total Soft Credit Amount'), 'value' => implode(', ', $softTotalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['soft_count'] = array('title' => ts('Total Soft Credits'), 'value' => $softCount);
$statistics['counts']['soft_avg'] = array('title' => ts('Average Soft Credit'), 'value' => implode(', ', $softAverage), 'type' => CRM_Utils_Type::T_STRING);
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "\n SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,\n IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,\n IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,\n COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount,\n {$this->_aliases['civicrm_contribution']}.currency as currency\n ";
$sql = "{$select} {$this->_from} {$this->_where}\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$totalAmount = $average = array();
$count = $memberCount = 0;
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
$count += $dao->count;
$memberCount += $dao->memberCount;
}
$statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Total Contributions'), 'value' => $count);
$statistics['counts']['memberCount'] = array('title' => ts('Total Members'), 'value' => $memberCount);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
if (!(int) $statistics['counts']['amount']['value']) {
//if total amount is zero then hide Chart Options
$this->assign('chartSupported', FALSE);
}
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "\n SELECT *\n ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
/*if ( $dao->fetch( ) ) {
$statistics['counts']['amount'] = array( 'value' => $dao->amount,
'title' => 'Total Amount',
'type' => CRM_Utils_Type::T_MONEY );
}*/
// Added by rajesh@millertech.co.uk on 1st Dec 2010
$batch_array = CRM_Utils_Array::value('batch_id_value', $_POST, '');
//print_r ($batch_array);exit;
$block_produce = FALSE;
if (!empty($batch_array)) {
$statistics['batch_array'] = array_reverse($batch_array);
$all_batch_ids = implode(',', $batch_array);
$sql = "SELECT * FROM civicrm_entity_batch eb WHERE eb.batch_id IN ({$all_batch_ids}) AND eb.entity_id IN (SELECT gf.entity_id FROM civicrm_entity_file gf)";
$dao = CRM_Core_DAO::executeQuery($sql);
$dao->fetch();
if (!empty($dao->entity_id)) {
$block_produce = TRUE;
}
} else {
$batch_sql = "SELECT MAX(id) as default_batch_id FROM civicrm_batch";
$batch_dao = CRM_Core_DAO::executeQuery($batch_sql);
$batch_dao->fetch();
$default_batch_id = $batch_dao->default_batch_id;
if (!empty($default_batch_id)) {
$batch_array = array(0 => $default_batch_id);
$statistics['batch_array'] = $batch_array;
$all_batch_ids = implode(',', $batch_array);
$sql = "SELECT * FROM civicrm_entity_batch eb WHERE eb.batch_id IN ({$all_batch_ids}) AND eb.entity_id IN (SELECT gf.entity_id FROM civicrm_entity_file gf)";
$dao = CRM_Core_DAO::executeQuery($sql);
$dao->fetch();
}
if (!empty($dao->entity_id)) {
$block_produce = TRUE;
}
}
if ($block_produce) {
$statistics['block_produce'] = 1;
} else {
$statistics['block_produce'] = 0;
}
if (!empty($batch_array)) {
$batch_id = $batch_array[0];
$batch_sql = "SELECT * FROM civicrm_batch b WHERE b.id = %1";
$batch_params = array(1 => array($batch_id, 'Integer'));
$batch_dao = CRM_Core_DAO::executeQuery($batch_sql, $batch_params);
$batch_dao->fetch();
$batch_file_name = "Batch_" . $batch_id . "_" . date('ymd', strtotime($batch_dao->created_date));
$csv_path = "sites/default/files/civicrm/custom";
$filePathName = "{$csv_path}/{$batch_file_name}.csv";
$statistics['csvFileName'] = $filePathName;
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
//regenerate the from field without extra left join on pledge payments
$this->_totalPaid = FALSE;
$this->from();
$this->customDataFrom();
if (!$this->_having) {
$totalAmount = $average = array();
$count = 0;
$select = "\n SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount ) as count,\n SUM({$this->_aliases['civicrm_pledge']}.amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg,\n {$this->_aliases['civicrm_pledge']}.currency as currency\n ";
$group = "GROUP BY {$this->_aliases['civicrm_pledge']}.currency";
$sql = "{$select} {$this->_from} {$this->_where} {$group}";
$dao = CRM_Core_DAO::executeQuery($sql);
$count = $index = $totalCount = 0;
// this will run once per currency
while ($dao->fetch()) {
$totalAmount = CRM_Utils_Money::format($dao->amount, $dao->currency);
$average = CRM_Utils_Money::format($dao->avg, $dao->currency);
$count = $dao->count;
$totalCount .= $count;
$statistics['counts']['amount' . $index] = array('title' => ts('Total Pledged') . ' (' . $dao->currency . ')', 'value' => $totalAmount, 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['avg' . $index] = array('title' => ts('Average') . ' (' . $dao->currency . ')', 'value' => $average, 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count' . $index] = array('title' => ts('Total No Pledges') . ' (' . $dao->currency . ')', 'value' => $count, 'type' => CRM_Utils_Type::T_INT);
$index++;
}
if ($totalCount > $count) {
$statistics['counts']['count' . $index] = array('title' => ts('Total No Pledges'), 'value' => $totalCount, 'type' => CRM_Utils_Type::T_INT);
}
}
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "\n SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,\n SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,\n {$this->_aliases['civicrm_contribution']}.currency as currency\n ";
$sql = "{$select} {$this->_from} {$this->_where}\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$count = 0;
$totalAmount = $average = array();
while ($dao->fetch()) {
$totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . '(' . $dao->count . ')';
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
$count += $dao->count;
}
$statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('title' => ts('Total Donations'), 'value' => $count);
$statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
// The parent class does something odd where it adds an extra row to the count for the grand total.
// Perhaps that works on some other report? But here it just seems odd.
$this->countStat($statistics, count($rows));
if (!empty($rows)) {
if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_last_year_total_amount'])) {
$statistics['counts']['civicrm_contribution_last_year_total_amount'] = array('value' => $this->rollupRow['civicrm_contribution_last_year_total_amount'], 'title' => $this->getLastYearColumnTitle(), 'type' => CRM_Utils_Type::T_MONEY);
}
if (!empty($this->rollupRow) && !empty($this->rollupRow['civicrm_contribution_civicrm_life_time_total'])) {
$statistics['counts']['civicrm_contribution_civicrm_life_time_total'] = array('value' => $this->rollupRow['civicrm_contribution_civicrm_life_time_total'], 'title' => ts('Total LifeTime'), 'type' => CRM_Utils_Type::T_MONEY);
} else {
$select = "SELECT SUM({$this->_aliases['civicrm_contribution']}.total_amount) as amount,\n SUM(IF( " . $this->whereClauseLastYear('contribution_civireport.receive_date') . ", contribution_civireport.total_amount, 0)) as last_year\n ";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => ts('Total LifeTime'), 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['last_year'] = array('value' => $dao->last_year, 'title' => $this->getLastYearColumnTitle(), 'type' => CRM_Utils_Type::T_MONEY);
}
}
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "select COUNT( DISTINCT( {$this->_aliases['civicrm_address']}.country_id))";
$sql = "{$select} {$this->_from} {$this->_where}";
$countryCount = CRM_Core_DAO::singleValueQuery($sql);
//CaseType statistics
if (array_key_exists('filters', $statistics)) {
foreach ($statistics['filters'] as $id => $value) {
if ($value['title'] == 'Case Type') {
$statistics['filters'][$id]['value'] = 'Is ' . $this->case_types[substr($statistics['filters'][$id]['value'], -3, -2)];
}
}
}
$statistics['counts']['case'] = array('title' => ts('Total Number of Cases '), 'value' => isset($statistics['counts']['rowsFound']) ? $statistics['counts']['rowsFound']['value'] : count($rows));
$statistics['counts']['country'] = array('title' => ts('Total Number of Countries '), 'value' => $countryCount);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$select = "\r\n SELECT SUM( value_gift_aid_submission_civireport.amount ) as amount,\r\n SUM( value_gift_aid_submission_civireport.gift_aid_amount ) as giftaid_amount";
$sql = "{$select} {$this->_from} {$this->_where}";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->fetch()) {
$statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_MONEY);
$statistics['counts']['giftaid'] = array('value' => $dao->giftaid_amount, 'title' => 'Total Gift Aid Amount', 'type' => CRM_Utils_Type::T_MONEY);
}
return $statistics;
}
function alterDisplay(&$rows)
{
$totalStatistics = $grantStatistics = array();
$totalStatistics = parent::statistics($rows);
$awardedGrantsAmount = $grantsReceived = $totalAmount = $awardedGrants = $grantReportsReceived = 0;
$grantStatistics = array();
$grantTypes = CRM_Core_PseudoConstant::get('CRM_Grant_DAO_Grant', 'grant_type_id');
$countries = CRM_Core_PseudoConstant::country();
$gender = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'gender_id');
$grantAmountTotal = "\nSELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,\n SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount\n {$this->_from} ";
if (!empty($this->_whereClause)) {
$grantAmountTotal .= " {$this->_whereClause}";
}
$result = CRM_Core_DAO::executeQuery($grantAmountTotal);
while ($result->fetch()) {
$grantsReceived = $result->count;
$totalAmount = $result->totalAmount;
}
if (!$grantsReceived) {
return;
}
$grantAmountAwarded = "\nSELECT COUNT({$this->_aliases['civicrm_grant']}.id) as count ,\n SUM({$this->_aliases['civicrm_grant']}.amount_granted) as grantedAmount,\n SUM({$this->_aliases['civicrm_grant']}.amount_total) as totalAmount\n {$this->_from} ";
if (!empty($this->_where)) {
$grantAmountAwarded .= " {$this->_where}";
}
$values = CRM_Core_DAO::executeQuery($grantAmountAwarded);
while ($values->fetch()) {
$awardedGrants = $values->count;
$awardedGrantsAmount = $values->totalAmount;
$amountGranted = $values->grantedAmount;
}
foreach ($rows as $key => $values) {
if (CRM_Utils_Array::value('civicrm_grant_grant_report_received', $values)) {
$grantReportsReceived++;
}
if (CRM_Utils_Array::value('civicrm_grant_grant_type_id', $values)) {
$grantType = CRM_Utils_Array::value($values['civicrm_grant_grant_type_id'], $grantTypes);
$grantStatistics['civicrm_grant_grant_type_id']['title'] = ts('By Grant Type');
self::getStatistics($grantStatistics['civicrm_grant_grant_type_id'], $grantType, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_world_region_name', $values)) {
$region = CRM_Utils_Array::value('civicrm_world_region_name', $values);
$region = $region ? $region : 'Unassigned';
$grantStatistics['civicrm_world_region_name']['title'] = ts('By Region');
self::getStatistics($grantStatistics['civicrm_world_region_name'], $region, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_address_country_id', $values)) {
$country = CRM_Utils_Array::value($values['civicrm_address_country_id'], $countries);
$country = $country ? $country : 'Unassigned';
$grantStatistics['civicrm_address_country_id']['title'] = ts('By Country');
self::getStatistics($grantStatistics['civicrm_address_country_id'], $country, $values, $awardedGrants, $awardedGrantsAmount);
}
if ($type = CRM_Utils_Array::value('civicrm_contact_contact_type', $values)) {
$grantStatistics['civicrm_contact_contact_type']['title'] = ts('By Contact Type');
$title = "Total Number of {$type}(s)";
self::getStatistics($grantStatistics['civicrm_contact_contact_type'], $title, $values, $awardedGrants, $awardedGrantsAmount);
}
if (array_key_exists('civicrm_contact_gender_id', $values)) {
$genderLabel = CRM_Utils_Array::value($values['civicrm_contact_gender_id'], $gender);
$genderLabel = $genderLabel ? $genderLabel : 'Unassigned';
$grantStatistics['civicrm_contact_gender_id']['title'] = ts('By Gender');
self::getStatistics($grantStatistics['civicrm_contact_gender_id'], $genderLabel, $values, $awardedGrants, $awardedGrantsAmount);
}
foreach ($values as $customField => $customValue) {
if (strstr($customField, 'civicrm_value_')) {
$customFieldTitle = CRM_Utils_Array::value('title', $this->_columnHeaders[$customField]);
$customGroupTitle = explode('_custom', strstr($customField, 'civicrm_value_'));
$customGroupTitle = $this->_columns[$customGroupTitle[0]]['group_title'];
$grantStatistics[$customGroupTitle]['title'] = ts('By %1', array(1 => $customGroupTitle));
$customData = $customValue ? FALSE : TRUE;
self::getStatistics($grantStatistics[$customGroupTitle], $customFieldTitle, $values, $awardedGrants, $awardedGrantsAmount, $customData);
}
}
}
$totalStatistics['total_statistics'] = array('grants_received' => array('title' => ts('Grant Requests Received'), 'count' => $grantsReceived, 'amount' => $totalAmount), 'grants_awarded' => array('title' => ts('Grants Awarded'), 'count' => $awardedGrants, 'amount' => $amountGranted), 'grants_report_received' => array('title' => ts('Grant Reports Received'), 'count' => $grantReportsReceived));
$this->assign('totalStatistics', $totalStatistics);
$this->assign('grantStatistics', $grantStatistics);
if ($this->_outputMode == 'csv' || $this->_outputMode == 'pdf') {
$row = array();
$this->_columnHeaders = array('civicrm_grant_total_grants' => array('title' => ts('Summary')), 'civicrm_grant_count' => array('title' => ts('Count')), 'civicrm_grant_amount' => array('title' => ts('Amount')));
foreach ($totalStatistics['total_statistics'] as $title => $value) {
$row[] = array('civicrm_grant_total_grants' => $value['title'], 'civicrm_grant_count' => $value['count'], 'civicrm_grant_amount' => $value['amount']);
}
if (!empty($grantStatistics)) {
foreach ($grantStatistics as $key => $value) {
$row[] = array('civicrm_grant_total_grants' => $value['title'], 'civicrm_grant_count' => ts('Number of Grants') . ' (%)', 'civicrm_grant_amount' => ts('Total Amount') . ' (%)');
foreach ($value['value'] as $field => $values) {
foreach ($values['currency'] as $currency => $amount) {
$totalAmount[$currency] = $currency . $amount['value'] . "({$values['percentage']}%)";
}
$totalAmt = implode(', ', $totalAmount);
$count = (bool) CRM_Utils_Array::value('count', $values, 0) ? $values['count'] . " ({$values['percentage']}%)" : '';
$row[] = array('civicrm_grant_total_grants' => $field, 'civicrm_grant_count' => $count, 'civicrm_grant_amount' => $totalAmt);
}
}
}
$rows = $row;
}
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$isStatusFilter = FALSE;
$relStatus = NULL;
if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') {
$relStatus = 'Is equal to Active';
} elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') {
$relStatus = 'Is equal to Inactive';
}
if (!empty($statistics['filters'])) {
foreach ($statistics['filters'] as $id => $value) {
//for displaying relationship type filter
if ($value['title'] == 'Relationship') {
$relTypes = CRM_Core_PseudoConstant::relationshipType();
$op = CRM_Utils_array::value('relationship_type_id_op', $this->_params) == 'in' ? ts('Is one of') . ' ' : ts('Is not one of') . ' ';
$relationshipTypes = array();
foreach ($this->_params['relationship_type_id_value'] as $relationship) {
$relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType];
}
$statistics['filters'][$id]['value'] = $op . implode(', ', $relationshipTypes);
}
//for displaying relationship status
if ($value['title'] == 'Relationship Status') {
$isStatusFilter = TRUE;
$statistics['filters'][$id]['value'] = $relStatus;
}
}
}
//for displaying relationship status
if (!$isStatusFilter && $relStatus) {
$statistics['filters'][] = array('title' => 'Relationship Status', 'value' => $relStatus);
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
$select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,\n CASE\n WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL\n THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount\n ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount\n END as amount\n";
$tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS\n {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
CRM_Core_DAO::executeQuery($tempQuery);
$sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency\n FROM {$tempTableName}\n GROUP BY currency";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $avg = array();
while ($dao->fetch()) {
$amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
$avg[] = CRM_Utils_Money::format(round($dao->amount / $dao->count, 2), $dao->currency);
}
$statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['avg'] = array('value' => implode(', ', $avg), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$count = 0;
foreach ($rows as $rownum => $row) {
if (is_numeric($rownum)) {
$count++;
}
}
$statistics['counts']['rowCount'] = array('title' => ts('Primary Contact(s) Listed'), 'value' => $count);
if ($this->_rowsFound && $this->_rowsFound > $count) {
$statistics['counts']['rowsFound'] = array('title' => ts('Total Primary Contact(s)'), 'value' => $this->_rowsFound);
}
return $statistics;
}
/**
* @param $rows
*
* @return array
*/
public function statistics(&$rows)
{
$statistics = parent::statistics($rows);
//fetch contributions for both date ranges from pre-existing temp tables
$sql = "\nCREATE TEMPORARY TABLE civicrm_temp_civireport_repeat3\nSELECT contact_id FROM civicrm_temp_civireport_repeat1 UNION SELECT contact_id FROM civicrm_temp_civireport_repeat2;";
$dao = CRM_Core_DAO::executeQuery($sql);
$sql = "\nSELECT civicrm_temp_civireport_repeat3.contact_id,\n civicrm_temp_civireport_repeat1.total_amount_sum as contribution1_total_amount_sum,\n civicrm_temp_civireport_repeat2.total_amount_sum as contribution2_total_amount_sum\nFROM civicrm_temp_civireport_repeat3\nLEFT JOIN civicrm_temp_civireport_repeat1\n ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat1.contact_id\nLEFT JOIN civicrm_temp_civireport_repeat2\n ON civicrm_temp_civireport_repeat3.contact_id = civicrm_temp_civireport_repeat2.contact_id";
$dao = CRM_Core_DAO::executeQuery($sql);
//store contributions in array 'contact_sums' for comparison
$contact_sums = array();
while ($dao->fetch()) {
$contact_sums[$dao->contact_id] = array('contribution1_total_amount_sum' => $dao->contribution1_total_amount_sum, 'contribution2_total_amount_sum' => $dao->contribution2_total_amount_sum);
}
$total_distinct_contacts = count($contact_sums);
$maintained = 0;
$upgraded = 0;
$downgraded = 0;
$new = 0;
$lapsed = 0;
foreach ($contact_sums as $uid => $row) {
if ($row['contribution1_total_amount_sum'] && $row['contribution2_total_amount_sum']) {
$change = $row['contribution1_total_amount_sum'] - $row['contribution2_total_amount_sum'];
if ($change == 0) {
$maintained += 1;
} elseif ($change > 0) {
$upgraded += 1;
} elseif ($change < 0) {
$downgraded += 1;
}
} elseif ($row['contribution1_total_amount_sum']) {
$new += 1;
} elseif ($row['contribution2_total_amount_sum']) {
$lapsed += 1;
}
}
//calculate percentages from numbers
if (!empty($total_distinct_contacts)) {
$maintained = $maintained / $total_distinct_contacts * 100;
$upgraded = $upgraded / $total_distinct_contacts * 100;
$downgraded = $downgraded / $total_distinct_contacts * 100;
$new = $new / $total_distinct_contacts * 100;
$lapsed = $lapsed / $total_distinct_contacts * 100;
}
//display percentages for new, lapsed, upgraded, downgraded, and maintained contributors
$statistics['counts']['count_new'] = array('value' => $new, 'title' => '% New Donors');
$statistics['counts']['count_lapsed'] = array('value' => $lapsed, 'title' => '% Lapsed Donors');
$statistics['counts']['count_upgraded'] = array('value' => $upgraded, 'title' => '% Upgraded Donors');
$statistics['counts']['count_downgraded'] = array('value' => $downgraded, 'title' => '% Downgraded Donors');
$statistics['counts']['count_maintained'] = array('value' => $maintained, 'title' => '% Maintained Donors');
$select = "\nSELECT COUNT({$this->_aliases['civicrm_contribution']}1.total_amount_count ) as count,\n SUM({$this->_aliases['civicrm_contribution']}1.total_amount_sum ) as amount,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}1.total_amount_sum), 2) as avg,\n COUNT({$this->_aliases['civicrm_contribution']}2.total_amount_count ) as count2,\n SUM({$this->_aliases['civicrm_contribution']}2.total_amount_sum ) as amount2,\n ROUND(AVG({$this->_aliases['civicrm_contribution']}2.total_amount_sum), 2) as avg2,\n currency";
$sql = "{$select} {$this->_from} {$this->_where}\nGROUP BY currency\n";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $average = $amount2 = $average2 = array();
$count = $count2 = 0;
while ($dao->fetch()) {
if ($dao->amount) {
$amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . $dao->count . ")";
$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
}
$count += $dao->count;
if ($dao->amount2) {
$amount2[] = CRM_Utils_Money::format($dao->amount2, $dao->currency) . "(" . $dao->count . ")";
$average2[] = CRM_Utils_Money::format($dao->avg2, $dao->currency);
}
$count2 += $dao->count2;
}
$statistics['counts']['range_one_title'] = array('title' => 'Initial Date Range:');
$statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count'] = array('value' => $count, 'title' => 'Total Donations');
$statistics['counts']['avg'] = array('value' => implode(', ', $average), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['range_two_title'] = array('title' => 'Second Date Range:');
$statistics['counts']['amount2'] = array('value' => implode(', ', $amount2), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
$statistics['counts']['count2'] = array('value' => $count2, 'title' => 'Total Donations');
$statistics['counts']['avg2'] = array('value' => implode(', ', $average2), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
return $statistics;
}
function statistics(&$rows)
{
$statistics = parent::statistics($rows);
$isStatusFilter = false;
$relStatus = null;
if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') {
$relStatus = 'Is equal to Active';
} elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') {
$relStatus = 'Is equal to Inactive';
}
if (CRM_Utils_Array::value('filters', $statistics)) {
foreach ($statistics['filters'] as $id => $value) {
//for displaying relationship type filter
if ($value['title'] == 'Relationship') {
$relTypes = CRM_Core_PseudoConstant::relationshipType();
$statistics['filters'][$id]['value'] = 'Is equal to ' . $relTypes[$this->_params['relationship_type_id_value']]['label_' . $this->relationType];
}
//for displaying relationship status
if ($value['title'] == 'Relationship Status') {
$isStatusFilter = true;
$statistics['filters'][$id]['value'] = $relStatus;
}
}
}
//for displaying relationship status
if (!$isStatusFilter && $relStatus) {
$statistics['filters'][] = array('title' => 'Relationship Status', 'value' => $relStatus);
}
return $statistics;
}
