[civicrm-core.git] / CRM / Report / Form / Contribute / TopDonor.php

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.4                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2013                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2013
 * $Id$
 *
 */
class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form {

  protected $_summary = NULL;

  public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');

  protected $_charts = array(
    '' => 'Tabular',
    'barChart' => 'Bar Chart',
    'pieChart' => 'Pie Chart',
  );

  function __construct() {
    $this->_columns = array(
      'civicrm_contact' =>
      array(
        'dao' => 'CRM_Contact_DAO_Contact',
        'fields' =>
        array(
          'id' =>
          array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'display_name' =>
          array('title' => ts('Contact Name'),
            'required' => TRUE,
            'no_repeat' => TRUE,
          ),
          'first_name' => array(
            'title' => ts('First Name'),
          ),
          'last_name' => array(
            'title' => ts('Last Name'),
          ),
          'contact_type' =>
          array(
            'title' => ts('Contact Type'),
          ),
          'contact_sub_type' =>
          array(
            'title' => ts('Contact SubType'),
          ),
        ),
      ),
    )
    + $this->getAddressColumns()
    + array(
      'civicrm_contribution' =>
      array(
        'dao' => 'CRM_Contribute_DAO_Contribution',
        'fields' =>
        array(
          'total_amount' =>
          array('title' => ts('Amount Statistics'),
            'required' => TRUE,
            'statistics' =>
            array('sum' => ts('Aggregate Amount'),
              'count' => ts('Donations'),
              'avg' => ts('Average'),
            ),
          ),
          'currency' =>
          array('required' => TRUE,
             'no_display' => TRUE,
          ),
        ),
        'filters' =>
        array(
          'receive_date' =>
          array(
            'default' => 'this.year',
            'operatorType' => CRM_Report_Form::OP_DATE,
          ),
          'currency' =>
          array('title' => 'Currency',
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
            'default' => NULL,
            'type' => CRM_Utils_Type::T_STRING,
            ),
          'total_range' =>
          array('title' => ts('Show no. of Top Donors'),
            'type' => CRM_Utils_Type::T_INT,
            'default_op' => 'eq',
          ),
          'financial_type_id' =>
          array('name' => 'financial_type_id',
            'title' => ts('Financial Type'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::financialType() ,
          ),
          'contribution_status_id' =>
          array('title' => ts('Donation Status'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
            'default' => array(1),
          ),
        ),
      ),
  'civicrm_email' =>
      array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' =>
        array(
          'email' =>
          array('title' => ts('Email'),
            'default' => TRUE,
            'no_repeat' => TRUE,
          ),
        ),
        'grouping' => 'email-fields',
      ),

      'civicrm_phone' =>
      array(
        'dao' => 'CRM_Core_DAO_Phone',
        'fields' =>
        array(
          'phone' =>
          array('title' => ts('Phone'),
            'default' => TRUE,
            'no_repeat' => TRUE,
          ),
        ),
        'grouping' => 'phone-fields',
      ),
      'civicrm_group' =>
      array(
        'dao' => 'CRM_Contact_DAO_GroupContact',
        'alias' => 'cgroup',
        'filters' =>
        array(
          'gid' =>
          array(
            'name' => 'group_id',
            'title' => ts('Group'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'group' => TRUE,
            'options' => CRM_Core_PseudoConstant::group(),
          ),
        ),
      ),
    );

    $this->_tagFilter = TRUE;
    $this->_currencyColumn = 'civicrm_contribution_currency';
    parent::__construct();
  }

  function preProcess() {
    parent::preProcess();
  }

  function select() {
    $select = array();
    $this->_columnHeaders = array();
    //Headers for Rank column
    $this->_columnHeaders["civicrm_donor_rank"]['title'] = ts('Rank');
    $this->_columnHeaders["civicrm_donor_rank"]['type'] = 1;
    //$select[] ="(@rank:=@rank+1)  as civicrm_donor_rank ";

    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('fields', $table)) {
        foreach ($table['fields'] as $fieldName => $field) {
          if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
            // only include statistics columns if set
            if (!empty($field['statistics'])) {
              foreach ($field['statistics'] as $stat => $label) {
                switch (strtolower($stat)) {
                  case 'sum':
                    $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                    break;

                  case 'count':
                    $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                    break;

                  case 'avg':
                    $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                    break;
                }
              }
            }
            else {
              $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
              // $field['type'] is not always set. Use string type as default if not set.
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = isset($field['type']) ? $field['type'] : 2;
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
            }
          }
        }
      }
    }

    $this->_select = " SELECT * FROM ( SELECT " . implode(', ', $select) . " ";
  }

  static function formRule($fields, $files, $self) {
    $errors = array();

    $op = CRM_Utils_Array::value('total_range_op', $fields);
    $val = CRM_Utils_Array::value('total_range_value', $fields);

    if (!in_array($op, array(
      'eq', 'lte'))) {
      $errors['total_range_op'] = ts("Please select 'Is equal to' OR 'Is Less than or equal to' operator");
    }

    if ($val && !CRM_Utils_Rule::positiveInteger($val)) {
      $errors['total_range_value'] = ts("Please enter positive number");
    }
    return $errors;
  }

  function from() {
    $this->_from = "
        FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
            INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
                ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0
             LEFT  JOIN civicrm_email  {$this->_aliases['civicrm_email']}
                         ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
                         AND {$this->_aliases['civicrm_email']}.is_primary = 1
             LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
                         ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
                            {$this->_aliases['civicrm_phone']}.is_primary = 1
  ";
    $this->addAddressFromClause();
  }

  function where() {
    $clauses = array();
    $this->_tempClause = $this->_outerCluase = '';
    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('filters', $table)) {
        foreach ($table['filters'] as $fieldName => $field) {
          $clause = NULL;
          if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
            $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
            $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
            $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);

            if ($relative || $from || $to) {
              $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
            }
          }
          else {
            $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
            if ($op) {
              $clause = $this->whereClause($field,
                $op,
                CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
                CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
              );
            }
          }

          if (!empty($clause)) {
            if ($fieldName == 'total_range') {
              $value = CRM_Utils_Array::value("total_range_value", $this->_params);
              $this->_outerCluase = " WHERE (( @rows := @rows + 1) <= {$value}) ";
            }
            else {
              $clauses[] = $clause;
            }
          }
        }
      }
    }
    if (empty($clauses)) {
      $this->_where = "WHERE ( 1 ) ";
    }
    else {
      $this->_where = "WHERE " . implode(' AND ', $clauses);
    }

    if ($this->_aclWhere) {
      $this->_where .= " AND {$this->_aclWhere} ";
    }
  }

  function groupBy() {
    $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.currency";
  }

  function postProcess() {

    $this->beginPostProcess();

    // get the acl clauses built before we assemble the query
    $this->buildACLClause($this->_aliases['civicrm_contact']);

    $this->select();

    $this->from();

    $this->where();

    $this->groupBy();

    $this->limit();


    //set the variable value rank, rows = 0
    $setVariable = " SET @rows:=0, @rank=0 ";
    CRM_Core_DAO::singleValueQuery($setVariable);

    $sql = " {$this->_select} {$this->_from}  {$this->_where} {$this->_groupBy}
                     ORDER BY civicrm_contribution_total_amount_sum DESC
                 ) as abc {$this->_outerCluase} $this->_limit
               ";

    $dao = CRM_Core_DAO::executeQuery($sql);

    while ($dao->fetch()) {
      $row = array();
      foreach ($this->_columnHeaders as $key => $value) {
        if (property_exists($dao, $key)) {
          $row[$key] = $dao->$key;
        }
      }
      $rows[] = $row;
    }
    $this->formatDisplay($rows);

    $this->doTemplateAssignment($rows);

    $this->endPostProcess($rows);
  }

  function add2group($groupID) {
    if (is_numeric($groupID)) {

      $sql = "
{$this->_select} {$this->_from}  {$this->_where} {$this->_groupBy}
ORDER BY civicrm_contribution_total_amount_sum DESC
) as abc {$this->_outerCluase}";
      $dao = CRM_Core_DAO::executeQuery($sql);

      $contact_ids = array();
      // Add resulting contacts to group
      while ($dao->fetch()) {
        $contact_ids[$dao->civicrm_contact_id] = $dao->civicrm_contact_id;
      }

      CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
      CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
    }
  }

  function limit($rowCount = CRM_Report_Form::ROW_COUNT_LIMIT) {
    // lets do the pager if in html mode
    $this->_limit = NULL;
    if ($this->_outputMode == 'html' || $this->_outputMode == 'group') {
      //replace only first occurence of SELECT
      $this->_select = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select, 1);
      $pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);

      if (!$pageId && !empty($_POST) && isset($_POST['crmPID_B'])) {
        if (!isset($_POST['PagerBottomButton'])) {
          unset($_POST['crmPID_B']);
        }
        else {
          $pageId = max((int)@$_POST['crmPID_B'], 1);
        }
      }

      $pageId = $pageId ? $pageId : 1;
      $this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
      $offset = ($pageId - 1) * $rowCount;

      $offset = CRM_Utils_Type::escape($offset, 'Int');
      $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');

      $this->_limit = " LIMIT $offset, " . $rowCount;
    }
  }

  function alterDisplay(&$rows) {
    // custom code to alter rows

    $entryFound = FALSE;
    $rank = 1;
    if (!empty($rows)) {
      foreach ($rows as $rowNum => $row) {

        $rows[$rowNum]['civicrm_donor_rank'] = $rank++;
        // convert display name to links
        if (array_key_exists('civicrm_contact_display_name', $row) &&
          array_key_exists('civicrm_contact_id', $row) && !empty($row['civicrm_contribution_currency'])) {
          $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
            'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'] . "&currency_value=" . $row['civicrm_contribution_currency'],
            $this->_absoluteUrl, $this->_id, $this->_drilldownReport
          );
          $rows[$rowNum]['civicrm_contact_display_name_link'] = $url;
          $entryFound = TRUE;
        }
        $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;

        // skip looking further in rows, if first row itself doesn't
        // have the column we need
        if (!$entryFound) {
          break;
        }
      }
    }
  }
}
Commit	Line	Data
6a488035	1	<?php
6a488035 TO	2
	3	/*
	4	+--------------------------------------------------------------------+
232624b1	5	\| CiviCRM version 4.4 \|
6a488035 TO	6	+--------------------------------------------------------------------+
	7	\| Copyright CiviCRM LLC (c) 2004-2013 \|
	8	+--------------------------------------------------------------------+
	9	\| This file is a part of CiviCRM. \|
	10	\| \|
	11	\| CiviCRM is free software; you can copy, modify, and distribute it \|
	12	\| under the terms of the GNU Affero General Public License \|
	13	\| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. \|
	14	\| \|
	15	\| CiviCRM is distributed in the hope that it will be useful, but \|
	16	\| WITHOUT ANY WARRANTY; without even the implied warranty of \|
	17	\| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. \|
	18	\| See the GNU Affero General Public License for more details. \|
	19	\| \|
	20	\| You should have received a copy of the GNU Affero General Public \|
	21	\| License and the CiviCRM Licensing Exception along \|
	22	\| with this program; if not, contact CiviCRM LLC \|
	23	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	24	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	25	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	26	+--------------------------------------------------------------------+
	27	*/
	28
	29	/**
	30	*
	31	* @package CRM
	32	* @copyright CiviCRM LLC (c) 2004-2013
	33	* $Id$
	34	*
	35	*/
	36	class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form {
	37
	38	protected $_summary = NULL;
	39
	40	public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
	41
	42	protected $_charts = array(
	43	'' => 'Tabular',
	44	'barChart' => 'Bar Chart',
	45	'pieChart' => 'Pie Chart',
	46	);
2f4c2f5d	47
6a488035 TO	48	function __construct() {
	49	$this->_columns = array(
	50	'civicrm_contact' =>
	51	array(
	52	'dao' => 'CRM_Contact_DAO_Contact',
	53	'fields' =>
	54	array(
	55	'id' =>
	56	array(
	57	'no_display' => TRUE,
	58	'required' => TRUE,
	59	),
	60	'display_name' =>
	61	array('title' => ts('Contact Name'),
	62	'required' => TRUE,
	63	'no_repeat' => TRUE,
	64	),
30f85891 RN	65	'first_name' => array(
30f85891 RN	66	'title' => ts('First Name'),
6a488035	67	),
30f85891 RN	68	'last_name' => array(
	69	'title' => ts('Last Name'),
	70	),
	71	'contact_type' =>
	72	array(
	73	'title' => ts('Contact Type'),
	74	),
	75	'contact_sub_type' =>
	76	array(
	77	'title' => ts('Contact SubType'),
6a488035 TO	78	),
	79	),
	80	),
21d62de7 DG	81	)
	82	+ $this->getAddressColumns()
	83	+ array(
6a488035 TO	84	'civicrm_contribution' =>
	85	array(
	86	'dao' => 'CRM_Contribute_DAO_Contribution',
	87	'fields' =>
	88	array(
	89	'total_amount' =>
	90	array('title' => ts('Amount Statistics'),
	91	'required' => TRUE,
	92	'statistics' =>
	93	array('sum' => ts('Aggregate Amount'),
	94	'count' => ts('Donations'),
	95	'avg' => ts('Average'),
	96	),
	97	),
2f4c2f5d	98	'currency' =>
7a961f19	99	array('required' => TRUE,
	100	'no_display' => TRUE,
	101	),
6a488035 TO	102	),
	103	'filters' =>
	104	array(
	105	'receive_date' =>
	106	array(
	107	'default' => 'this.year',
	108	'operatorType' => CRM_Report_Form::OP_DATE,
	109	),
7a961f19	110	'currency' =>
f3bc9ff5	111	array('title' => 'Currency',
f3bc9ff5	112	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
0637f1c4	113	'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
d1b0931b	114	'default' => NULL,
7a961f19	115	'type' => CRM_Utils_Type::T_STRING,
f3bc9ff5	116	),
6a488035 TO	117	'total_range' =>
	118	array('title' => ts('Show no. of Top Donors'),
	119	'type' => CRM_Utils_Type::T_INT,
	120	'default_op' => 'eq',
	121	),
f3bc9ff5	122	'financial_type_id' =>
b914f4e8 PN	123	array('name' => 'financial_type_id',
b914f4e8 PN	124	'title' => ts('Financial Type'),
6a488035	125	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
b914f4e8	126	'options' => CRM_Contribute_PseudoConstant::financialType() ,
6a488035 TO	127	),
	128	'contribution_status_id' =>
	129	array('title' => ts('Donation Status'),
	130	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	131	'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
	132	'default' => array(1),
	133	),
	134	),
	135	),
2f4c2f5d	136	'civicrm_email' =>
6a488035 TO	137	array(
	138	'dao' => 'CRM_Core_DAO_Email',
	139	'fields' =>
	140	array(
	141	'email' =>
	142	array('title' => ts('Email'),
	143	'default' => TRUE,
	144	'no_repeat' => TRUE,
	145	),
	146	),
	147	'grouping' => 'email-fields',
	148	),
2f4c2f5d	149
6a488035 TO	150	'civicrm_phone' =>
	151	array(
	152	'dao' => 'CRM_Core_DAO_Phone',
	153	'fields' =>
	154	array(
	155	'phone' =>
	156	array('title' => ts('Phone'),
	157	'default' => TRUE,
	158	'no_repeat' => TRUE,
	159	),
	160	),
	161	'grouping' => 'phone-fields',
	162	),
	163	'civicrm_group' =>
	164	array(
	165	'dao' => 'CRM_Contact_DAO_GroupContact',
	166	'alias' => 'cgroup',
	167	'filters' =>
	168	array(
	169	'gid' =>
	170	array(
	171	'name' => 'group_id',
	172	'title' => ts('Group'),
	173	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	174	'group' => TRUE,
	175	'options' => CRM_Core_PseudoConstant::group(),
	176	),
	177	),
	178	),
	179	);
	180
	181	$this->_tagFilter = TRUE;
7a961f19	182	$this->_currencyColumn = 'civicrm_contribution_currency';
6a488035 TO	183	parent::__construct();
	184	}
	185
	186	function preProcess() {
	187	parent::preProcess();
	188	}
	189
	190	function select() {
	191	$select = array();
	192	$this->_columnHeaders = array();
	193	//Headers for Rank column
	194	$this->_columnHeaders["civicrm_donor_rank"]['title'] = ts('Rank');
	195	$this->_columnHeaders["civicrm_donor_rank"]['type'] = 1;
	196	//$select[] ="(@rank:=@rank+1) as civicrm_donor_rank ";
	197
	198	foreach ($this->_columns as $tableName => $table) {
	199	if (array_key_exists('fields', $table)) {
	200	foreach ($table['fields'] as $fieldName => $field) {
8cc574cf	201	if (!empty($field['required']) \|\| !empty($this->_params['fields'][$fieldName])) {
6a488035	202	// only include statistics columns if set
a7488080	203	if (!empty($field['statistics'])) {
6a488035 TO	204	foreach ($field['statistics'] as $stat => $label) {
	205	switch (strtolower($stat)) {
	206	case 'sum':
	207	$select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
	208	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	209	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
	210	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	211	break;
	212
	213	case 'count':
	214	$select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
	215	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	216	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
	217	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	218	break;
	219
	220	case 'avg':
	221	$select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
	222	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
	223	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	224	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	225	break;
	226	}
	227	}
	228	}
	229	else {
	230	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
21d62de7 DG	231	// $field['type'] is not always set. Use string type as default if not set.
21d62de7 DG	232	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = isset($field['type']) ? $field['type'] : 2;
6a488035 TO	233	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
	234	}
	235	}
	236	}
	237	}
	238	}
65f9ed10	239
6a488035 TO	240	$this->_select = " SELECT * FROM ( SELECT " . implode(', ', $select) . " ";
	241	}
	242
	243	static function formRule($fields, $files, $self) {
	244	$errors = array();
	245
	246	$op = CRM_Utils_Array::value('total_range_op', $fields);
	247	$val = CRM_Utils_Array::value('total_range_value', $fields);
	248
	249	if (!in_array($op, array(
	250	'eq', 'lte'))) {
	251	$errors['total_range_op'] = ts("Please select 'Is equal to' OR 'Is Less than or equal to' operator");
	252	}
	253
	254	if ($val && !CRM_Utils_Rule::positiveInteger($val)) {
	255	$errors['total_range_value'] = ts("Please enter positive number");
	256	}
	257	return $errors;
	258	}
	259
	260	function from() {
	261	$this->_from = "
	262	FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
65f9ed10 E	263	INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
65f9ed10 E	264	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0
2f4c2f5d	265	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
2f4c2f5d	266	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id
6a488035	267	AND {$this->_aliases['civicrm_email']}.is_primary = 1
2f4c2f5d	268	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
6a488035	269	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
2f4c2f5d	270	{$this->_aliases['civicrm_phone']}.is_primary = 1
65f9ed10	271	";
21d62de7	272	$this->addAddressFromClause();
6a488035 TO	273	}
	274
	275	function where() {
	276	$clauses = array();
	277	$this->_tempClause = $this->_outerCluase = '';
	278	foreach ($this->_columns as $tableName => $table) {
	279	if (array_key_exists('filters', $table)) {
	280	foreach ($table['filters'] as $fieldName => $field) {
	281	$clause = NULL;
	282	if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
	283	$relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
	284	$from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
	285	$to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
	286
	287	if ($relative \|\| $from \|\| $to) {
	288	$clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
	289	}
	290	}
	291	else {
	292	$op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
	293	if ($op) {
	294	$clause = $this->whereClause($field,
	295	$op,
	296	CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
	297	CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
	298	CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
	299	);
	300	}
	301	}
	302
	303	if (!empty($clause)) {
	304	if ($fieldName == 'total_range') {
	305	$value = CRM_Utils_Array::value("total_range_value", $this->_params);
	306	$this->_outerCluase = " WHERE (( @rows := @rows + 1) <= {$value}) ";
	307	}
	308	else {
	309	$clauses[] = $clause;
	310	}
	311	}
	312	}
	313	}
	314	}
	315	if (empty($clauses)) {
	316	$this->_where = "WHERE ( 1 ) ";
	317	}
	318	else {
	319	$this->_where = "WHERE " . implode(' AND ', $clauses);
	320	}
	321
	322	if ($this->_aclWhere) {
	323	$this->_where .= " AND {$this->_aclWhere} ";
	324	}
	325	}
	326
	327	function groupBy() {
7a961f19	328	$this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.currency";
6a488035 TO	329	}
	330
	331	function postProcess() {
	332
	333	$this->beginPostProcess();
	334
	335	// get the acl clauses built before we assemble the query
	336	$this->buildACLClause($this->_aliases['civicrm_contact']);
	337
	338	$this->select();
	339
	340	$this->from();
	341
	342	$this->where();
	343
	344	$this->groupBy();
	345
	346	$this->limit();
	347
	348
	349	//set the variable value rank, rows = 0
	350	$setVariable = " SET @rows:=0, @rank=0 ";
	351	CRM_Core_DAO::singleValueQuery($setVariable);
	352
2f4c2f5d	353	$sql = " {$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}
6a488035 TO	354	ORDER BY civicrm_contribution_total_amount_sum DESC
	355	) as abc {$this->_outerCluase} $this->_limit
	356	";
	357
	358	$dao = CRM_Core_DAO::executeQuery($sql);
	359
	360	while ($dao->fetch()) {
	361	$row = array();
	362	foreach ($this->_columnHeaders as $key => $value) {
	363	if (property_exists($dao, $key)) {
	364	$row[$key] = $dao->$key;
	365	}
	366	}
	367	$rows[] = $row;
	368	}
	369	$this->formatDisplay($rows);
	370
	371	$this->doTemplateAssignment($rows);
	372
	373	$this->endPostProcess($rows);
	374	}
	375
	376	function add2group($groupID) {
	377	if (is_numeric($groupID)) {
	378
2f4c2f5d	379	$sql = "
2f4c2f5d	380	{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}
6a488035 TO	381	ORDER BY civicrm_contribution_total_amount_sum DESC
	382	) as abc {$this->_outerCluase}";
	383	$dao = CRM_Core_DAO::executeQuery($sql);
	384
	385	$contact_ids = array();
	386	// Add resulting contacts to group
	387	while ($dao->fetch()) {
	388	$contact_ids[$dao->civicrm_contact_id] = $dao->civicrm_contact_id;
	389	}
	390
	391	CRM_Contact_BAO_GroupContact::addContactsToGroup($contact_ids, $groupID);
	392	CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
	393	}
	394	}
	395
	396	function limit($rowCount = CRM_Report_Form::ROW_COUNT_LIMIT) {
	397	// lets do the pager if in html mode
	398	$this->_limit = NULL;
	399	if ($this->_outputMode == 'html' \|\| $this->_outputMode == 'group') {
	400	//replace only first occurence of SELECT
	401	$this->_select = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select, 1);
	402	$pageId = CRM_Utils_Request::retrieve('crmPID', 'Integer', CRM_Core_DAO::$_nullObject);
	403
	404	if (!$pageId && !empty($_POST) && isset($_POST['crmPID_B'])) {
	405	if (!isset($_POST['PagerBottomButton'])) {
	406	unset($_POST['crmPID_B']);
	407	}
	408	else {
	409	$pageId = max((int)@$_POST['crmPID_B'], 1);
	410	}
	411	}
	412
	413	$pageId = $pageId ? $pageId : 1;
	414	$this->set(CRM_Utils_Pager::PAGE_ID, $pageId);
	415	$offset = ($pageId - 1) * $rowCount;
	416
bf00d1b6 DL	417	$offset = CRM_Utils_Type::escape($offset, 'Int');
	418	$rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
	419
6a488035 TO	420	$this->_limit = " LIMIT $offset, " . $rowCount;
	421	}
	422	}
	423
	424	function alterDisplay(&$rows) {
	425	// custom code to alter rows
	426
	427	$entryFound = FALSE;
	428	$rank = 1;
	429	if (!empty($rows)) {
	430	foreach ($rows as $rowNum => $row) {
	431
	432	$rows[$rowNum]['civicrm_donor_rank'] = $rank++;
	433	// convert display name to links
	434	if (array_key_exists('civicrm_contact_display_name', $row) &&
8cc574cf	435	array_key_exists('civicrm_contact_id', $row) && !empty($row['civicrm_contribution_currency'])) {
6a488035	436	$url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
7a961f19	437	'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'] . "&currency_value=" . $row['civicrm_contribution_currency'],
6a488035 TO	438	$this->_absoluteUrl, $this->_id, $this->_drilldownReport
	439	);
	440	$rows[$rowNum]['civicrm_contact_display_name_link'] = $url;
	441	$entryFound = TRUE;
	442	}
21d62de7	443	$entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s)') ? TRUE : $entryFound;
6a488035 TO	444
	445	// skip looking further in rows, if first row itself doesn't
	446	// have the column we need
	447	if (!$entryFound) {
	448	break;
	449	}
	450	}
	451	}
	452	}
	453	}
	454