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

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.6                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2014                                |
 +--------------------------------------------------------------------+
 | 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-2014
 * $Id$
 *
 */
class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form {

  protected $_emailField = FALSE;
  protected $_emailFieldCredit = FALSE;
  protected $_phoneField = FALSE;
  protected $_phoneFieldCredit = FALSE;
  protected $_charts = array(
    '' => 'Tabular',
    'barChart' => 'Bar Chart',
    'pieChart' => 'Pie Chart',
  );
  public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');

  /**
   *
   */
  /**
   *
   */
  public function __construct() {

    // Check if CiviCampaign is a) enabled and b) has active campaigns
    $config = CRM_Core_Config::singleton();
    $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
    if ($campaignEnabled) {
      $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
      $this->activeCampaigns = $getCampaigns['campaigns'];
      asort($this->activeCampaigns);
    }

    $this->_columns = array(
      'civicrm_contact' => array(
        'dao' => 'CRM_Contact_DAO_Contact',
        'fields' => array(
          'display_name_creditor' => array(
            'title' => ts('Soft Credit Name'),
            'name' => 'sort_name',
            'alias' => 'contact_civireport',
            'required' => TRUE,
            'no_repeat' => TRUE,
          ),
          'id_creditor' => array(
            'title' => ts('Soft Credit Id'),
            'name' => 'id',
            'alias' => 'contact_civireport',
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'display_name_constituent' => array(
            'title' => ts('Contributor Name'),
            'name' => 'sort_name',
            'alias' => 'constituentname',
            'required' => TRUE,
          ),
          'id_constituent' => array(
            'title' => ts('Const Id'),
            'name' => 'id',
            'alias' => 'constituentname',
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'contact_type' => array(
            'title' => ts('Contact Type'),
          ),
          'contact_sub_type' => array(
            'title' => ts('Contact Subtype'),
          ),
        ),
        'filters' => array(
          'sort_name' => array(
            'name' => 'sort_name',
            'title' => ts('Soft Credit Name')
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_email' => array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' => array(
          'email_creditor' => array(
            'title' => ts('Soft Credit Email'),
            'name' => 'email',
            'alias' => 'emailcredit',
            'default' => TRUE,
            'no_repeat' => TRUE,
          ),
          'email_constituent' => array(
            'title' => ts('Contributor\'s Email'),
            'name' => 'email',
            'alias' => 'emailconst',
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_phone' => array(
        'dao' => 'CRM_Core_DAO_Phone',
        'fields' => array(
          'phone_creditor' => array(
            'title' => ts('Soft Credit Phone'),
            'name' => 'phone',
            'alias' => 'pcredit',
            'default' => TRUE,
          ),
          'phone_constituent' => array(
            'title' => ts('Contributor\'s Phone'),
            'name' => 'phone',
            'alias' => 'pconst',
            'no_repeat' => TRUE,
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_financial_type' => array(
        'dao' => 'CRM_Financial_DAO_FinancialType',
        'fields' => array('financial_type' => NULL,),
        'filters' => array(
          'id' => array(
            'name' => 'id',
            'title' => ts('Financial Type'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::financialType()
          ),
        ),
        'grouping' => 'softcredit-fields',
      ),
      'civicrm_contribution' => array(
        'dao' => 'CRM_Contribute_DAO_Contribution',
        'fields' => array(
          'contribution_source' => NULL,
          'currency' => array(
            'required' => TRUE,
            'no_display' => TRUE,
          ),
          'total_amount' => array(
            'title' => ts('Amount Statistics'),
            'default' => TRUE,
            'statistics' => array(
              'sum' => ts('Aggregate Amount'),
              'count' => ts('Contributions'),
              'avg' => ts('Average'),
            ),
          ),
        ),
        'grouping' => 'softcredit-fields',
        'filters' => array(
          'receive_date' => array('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,
          ),
          'contribution_status_id' => array(
            'title' => ts('Contribution Status'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
            'default' => array(1),
          ),
          'total_amount' => array(
            'title' => ts('Contribution Amount'),
          ),
        ),
      ),
      'civicrm_contribution_soft' => array(
        'dao' => 'CRM_Contribute_DAO_ContributionSoft',
        'fields' => array(
          'contribution_id' => array(
            'title' => ts('Contribution ID'),
            'no_display' => TRUE,
            'default' => TRUE,
          ),
          'id' => array(
            'default' => TRUE,
            'no_display' => TRUE,
          ),
          'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
        ),
        'filters' => array(
          'soft_credit_type_id' => array(
            'title' => 'Soft Credit Type',
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
            'default' => NULL,
            'type' => CRM_Utils_Type::T_STRING,
          ),
        ),
        'grouping' => 'softcredit-fields',
      ),
    );

    // If we have a campaign, build out the relevant elements
    if ($campaignEnabled && !empty($this->activeCampaigns)) {
      $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
        'title' => ts('Campaign'),
        'default' => 'false',
      );
      $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
        'title' => ts('Campaign'),
        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
        'options' => $this->activeCampaigns,
      );
    }

    $this->_groupFilter = TRUE;
    $this->_tagFilter = TRUE;

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

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

  public function select() {
    $select = array();
    $this->_columnHeaders = array();
    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])
          ) {

            // include email column if set
            if ($tableName == 'civicrm_email') {
              $this->_emailField = TRUE;
              $this->_emailFieldCredit = TRUE;
            }
            elseif ($tableName == 'civicrm_email_creditor') {
              $this->_emailFieldCredit = TRUE;
            }

            // include phone columns if set
            if ($tableName == 'civicrm_phone') {
              $this->_phoneField = TRUE;
              $this->_phoneFieldCredit = TRUE;
            }
            elseif ($tableName == 'civicrm_phone_creditor') {
              $this->_phoneFieldCredit = TRUE;
            }

            // 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}"]['type'] = CRM_Utils_Type::T_INT;
                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                    $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}";
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
            }
          }
        }
      }
    }

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

  /**
   * @param $fields
   * @param $files
   * @param $self
   *
   * @return array
   */
  public static function formRule($fields, $files, $self) {
    $errors = $grouping = array();
    return $errors;
  }

  public function from() {
    $alias_constituent = 'constituentname';
    $alias_creditor = 'contact_civireport';
    $this->_from = "
        FROM  civicrm_contribution {$this->_aliases['civicrm_contribution']}
              INNER JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
                         ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
                            {$this->_aliases['civicrm_contribution']}.id
              INNER JOIN civicrm_contact {$alias_constituent}
                         ON {$this->_aliases['civicrm_contribution']}.contact_id =
                            {$alias_constituent}.id
              LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
                         ON {$this->_aliases['civicrm_contribution']}.financial_type_id =
                            {$this->_aliases['civicrm_financial_type']}.id
              LEFT  JOIN civicrm_contact {$alias_creditor}
                         ON {$this->_aliases['civicrm_contribution_soft']}.contact_id =
                            {$alias_creditor}.id
              {$this->_aclFrom} ";

    // include Constituent email field if email column is to be included
    if ($this->_emailField) {
      $alias = 'emailconst';
      $this->_from .= "
            LEFT JOIN civicrm_email {$alias}
                      ON {$alias_constituent}.id =
                         {$alias}.contact_id   AND
                         {$alias}.is_primary = 1\n";
    }

    // include  Creditors email field if email column is to be included
    if ($this->_emailFieldCredit) {
      $alias = 'emailcredit';
      $this->_from .= "
            LEFT JOIN civicrm_email {$alias}
                      ON {$alias_creditor}.id =
                         {$alias}.contact_id  AND
                         {$alias}.is_primary = 1\n";
    }

    // include  Constituents phone field if email column is to be included
    if ($this->_phoneField) {
      $alias = 'pconst';
      $this->_from .= "
            LEFT JOIN civicrm_phone {$alias}
                      ON {$alias_constituent}.id =
                         {$alias}.contact_id  AND
                         {$alias}.is_primary = 1\n";
    }

    // include  Creditors phone field if email column is to be included
    if ($this->_phoneFieldCredit) {
      $alias = 'pcredit';
      $this->_from .= "
            LEFT JOIN civicrm_phone pcredit
                      ON {$alias_creditor}.id =
                         {$alias}.contact_id  AND
                         {$alias}.is_primary = 1\n";
    }
  }

  public function groupBy() {
    $this->_rollup = 'WITH ROLLUP';
    $this->_groupBy = "
GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
  }

  public function where() {
    parent::where();
    $this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
  }

  /**
   * @param $rows
   *
   * @return array
   */
  public function statistics(&$rows) {
    $statistics = parent::statistics($rows);

    $select = "
        SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
               SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
               ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
               {$this->_aliases['civicrm_contribution']}.currency as currency
        ";

    $sql = "{$select} {$this->_from} {$this->_where}
GROUP BY   {$this->_aliases['civicrm_contribution']}.currency
";

    $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 Contributions'),
      'value' => $count,
    );
    $statistics['counts']['avg'] = array(
      'title' => ts('Average'),
      'value' => implode(',  ', $average),
      'type' => CRM_Utils_Type::T_STRING,
    );

    return $statistics;
  }

  public function postProcess() {
    $this->beginPostProcess();

    $this->buildACLClause(array('constituentname', 'contact_civireport'));
    $sql = $this->buildQuery();

    $dao = CRM_Core_DAO::executeQuery($sql);
    $rows = $graphRows = array();
    $count = 0;
    while ($dao->fetch()) {
      $row = array();
      foreach ($this->_columnHeaders as $key => $value) {
        $row[$key] = $dao->$key;
      }
      $rows[] = $row;
    }
    $this->formatDisplay($rows);

    // to hide the contact ID field from getting displayed
    unset($this->_columnHeaders['civicrm_contact_id_constituent']);
    unset($this->_columnHeaders['civicrm_contact_id_creditor']);

    // assign variables to templates
    $this->doTemplateAssignment($rows);
    $this->endPostProcess($rows);
  }

  /**
   * @param $rows
   */
  public function alterDisplay(&$rows) {
    // custom code to alter rows

    $entryFound = FALSE;
    $dispname_flag = $phone_flag = $email_flag = 0;
    $prev_email = $prev_dispname = $prev_phone = NULL;

    foreach ($rows as $rowNum => $row) {
      // Link constituent (contributor) to contribution detail
      if (array_key_exists('civicrm_contact_display_name_constituent', $row) &&
        array_key_exists('civicrm_contact_id_constituent', $row)
      ) {

        $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
          'reset=1&force=1&id_op=eq&id_value=' .
          $row['civicrm_contact_id_constituent'],
          $this->_absoluteUrl, $this->_id, $this->_drilldownReport
        );
        $rows[$rowNum]['civicrm_contact_display_name_constituent_link'] = $url;
        $rows[$rowNum]['civicrm_contact_display_name_constituent_hover'] = ts('List all direct contribution(s) from this contact.');
        $entryFound = TRUE;
      }

      // convert soft credit contact name to link
      if (array_key_exists('civicrm_contact_display_name_creditor', $row) &&
        !empty($rows[$rowNum]['civicrm_contact_display_name_creditor']) &&
        array_key_exists('civicrm_contact_id_creditor', $row)
      ) {
        $url = CRM_Utils_System::url("civicrm/contact/view",
          'reset=1&cid=' . $row['civicrm_contact_id_creditor'],
          $this->_absoluteUrl
        );
        $rows[$rowNum]['civicrm_contact_display_name_creditor_link'] = $url;
        $rows[$rowNum]['civicrm_contact_display_name_creditor_hover'] = ts("view contact summary");
      }

      // make subtotals look nicer
      if (array_key_exists('civicrm_contact_id_constituent', $row) &&
        !$row['civicrm_contact_id_constituent']
      ) {
        $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
        $entryFound = TRUE;
      }

      // convert campaign_id to campaign title
      if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
        if ($value = $row['civicrm_contribution_campaign_id']) {
          $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
          $entryFound = TRUE;
        }
      }

      //convert soft_credit_type_id into label
      if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
        $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_OptionGroup::getLabel('soft_credit_type',
          $row['civicrm_contribution_soft_soft_credit_type_id']);
      }

      // skip looking further in rows, if first row itself doesn't
      // have the column we need
      if (!$entryFound) {
        break;
      }
    }

    $this->removeDuplicates($rows);
  }
}
Commit	Line	Data
6a488035	1	<?php
6a488035 TO	2	/*
6a488035 TO	3	+--------------------------------------------------------------------+
39de6fd5	4	\| CiviCRM version 4.6 \|
6a488035	5	+--------------------------------------------------------------------+
06b69b18	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	7	+--------------------------------------------------------------------+
	8	\| This file is a part of CiviCRM. \|
	9	\| \|
	10	\| CiviCRM is free software; you can copy, modify, and distribute it \|
	11	\| under the terms of the GNU Affero General Public License \|
	12	\| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. \|
	13	\| \|
	14	\| CiviCRM is distributed in the hope that it will be useful, but \|
	15	\| WITHOUT ANY WARRANTY; without even the implied warranty of \|
	16	\| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. \|
	17	\| See the GNU Affero General Public License for more details. \|
	18	\| \|
	19	\| You should have received a copy of the GNU Affero General Public \|
	20	\| License and the CiviCRM Licensing Exception along \|
	21	\| with this program; if not, contact CiviCRM LLC \|
	22	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	23	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	24	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	25	+--------------------------------------------------------------------+
	26	*/
	27
	28	/**
	29	*
	30	* @package CRM
06b69b18	31	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	32	* $Id$
	33	*
	34	*/
	35	class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form {
	36
	37	protected $_emailField = FALSE;
	38	protected $_emailFieldCredit = FALSE;
	39	protected $_phoneField = FALSE;
	40	protected $_phoneFieldCredit = FALSE;
	41	protected $_charts = array(
	42	'' => 'Tabular',
	43	'barChart' => 'Bar Chart',
	44	'pieChart' => 'Pie Chart',
f813f78e	45	);
6a488035	46	public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
f813f78e	47
74cf4551 EM	48	/**
	49	*
	50	*/
	51	/**
	52	*
	53	*/
00be9182	54	public function __construct() {
d62fab33 RN	55
	56	// Check if CiviCampaign is a) enabled and b) has active campaigns
	57	$config = CRM_Core_Config::singleton();
	58	$campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
	59	if ($campaignEnabled) {
	60	$getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
	61	$this->activeCampaigns = $getCampaigns['campaigns'];
	62	asort($this->activeCampaigns);
f813f78e	63	}
d62fab33	64
6a488035	65	$this->_columns = array(
9d72cede	66	'civicrm_contact' => array(
6a488035	67	'dao' => 'CRM_Contact_DAO_Contact',
9d72cede EM	68	'fields' => array(
	69	'display_name_creditor' => array(
	70	'title' => ts('Soft Credit Name'),
6a488035 TO	71	'name' => 'sort_name',
	72	'alias' => 'contact_civireport',
	73	'required' => TRUE,
	74	'no_repeat' => TRUE,
	75	),
9d72cede EM	76	'id_creditor' => array(
9d72cede EM	77	'title' => ts('Soft Credit Id'),
6a488035 TO	78	'name' => 'id',
	79	'alias' => 'contact_civireport',
	80	'no_display' => TRUE,
	81	'required' => TRUE,
	82	),
9d72cede EM	83	'display_name_constituent' => array(
9d72cede EM	84	'title' => ts('Contributor Name'),
6a488035 TO	85	'name' => 'sort_name',
	86	'alias' => 'constituentname',
	87	'required' => TRUE,
	88	),
9d72cede EM	89	'id_constituent' => array(
9d72cede EM	90	'title' => ts('Const Id'),
6a488035 TO	91	'name' => 'id',
	92	'alias' => 'constituentname',
	93	'no_display' => TRUE,
	94	'required' => TRUE,
	95	),
9d72cede	96	'contact_type' => array(
30f85891 RN	97	'title' => ts('Contact Type'),
30f85891 RN	98	),
9d72cede	99	'contact_sub_type' => array(
b8f96eb8	100	'title' => ts('Contact Subtype'),
30f85891	101	),
6a488035	102	),
9d72cede EM	103	'filters' => array(
9d72cede EM	104	'sort_name' => array(
73f4acb4 S	105	'name' => 'sort_name',
	106	'title' => ts('Soft Credit Name')
	107	),
	108	),
6a488035 TO	109	'grouping' => 'contact-fields',
6a488035 TO	110	),
9d72cede	111	'civicrm_email' => array(
6a488035	112	'dao' => 'CRM_Core_DAO_Email',
9d72cede EM	113	'fields' => array(
	114	'email_creditor' => array(
	115	'title' => ts('Soft Credit Email'),
6a488035 TO	116	'name' => 'email',
	117	'alias' => 'emailcredit',
	118	'default' => TRUE,
	119	'no_repeat' => TRUE,
	120	),
9d72cede EM	121	'email_constituent' => array(
9d72cede EM	122	'title' => ts('Contributor\'s Email'),
6a488035 TO	123	'name' => 'email',
	124	'alias' => 'emailconst',
	125	),
	126	),
	127	'grouping' => 'contact-fields',
	128	),
9d72cede	129	'civicrm_phone' => array(
6a488035	130	'dao' => 'CRM_Core_DAO_Phone',
9d72cede EM	131	'fields' => array(
	132	'phone_creditor' => array(
	133	'title' => ts('Soft Credit Phone'),
6a488035 TO	134	'name' => 'phone',
	135	'alias' => 'pcredit',
	136	'default' => TRUE,
	137	),
9d72cede EM	138	'phone_constituent' => array(
9d72cede EM	139	'title' => ts('Contributor\'s Phone'),
6a488035 TO	140	'name' => 'phone',
	141	'alias' => 'pconst',
	142	'no_repeat' => TRUE,
	143	),
	144	),
	145	'grouping' => 'contact-fields',
	146	),
9d72cede EM	147	'civicrm_financial_type' => array(
	148	'dao' => 'CRM_Financial_DAO_FinancialType',
	149	'fields' => array('financial_type' => NULL,),
	150	'filters' => array(
	151	'id' => array(
6a488035	152	'name' => 'id',
b914f4e8	153	'title' => ts('Financial Type'),
6a488035	154	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
b914f4e8 PN	155	'options' => CRM_Contribute_PseudoConstant::financialType()
	156	),
	157	),
6a488035 TO	158	'grouping' => 'softcredit-fields',
6a488035 TO	159	),
9d72cede	160	'civicrm_contribution' => array(
6a488035	161	'dao' => 'CRM_Contribute_DAO_Contribution',
9d72cede	162	'fields' => array(
6a488035	163	'contribution_source' => NULL,
c6348f97	164	'currency' => array(
	165	'required' => TRUE,
	166	'no_display' => TRUE,
	167	),
9d72cede EM	168	'total_amount' => array(
9d72cede EM	169	'title' => ts('Amount Statistics'),
6a488035	170	'default' => TRUE,
9d72cede EM	171	'statistics' => array(
9d72cede EM	172	'sum' => ts('Aggregate Amount'),
be205937	173	'count' => ts('Contributions'),
6a488035 TO	174	'avg' => ts('Average'),
	175	),
	176	),
	177	),
	178	'grouping' => 'softcredit-fields',
9d72cede EM	179	'filters' => array(
	180	'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
	181	'currency' => array(
	182	'title' => 'Currency',
c6348f97	183	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
c6348f97	184	'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
d1b0931b	185	'default' => NULL,
c6348f97	186	'type' => CRM_Utils_Type::T_STRING,
c6348f97	187	),
9d72cede EM	188	'contribution_status_id' => array(
9d72cede EM	189	'title' => ts('Contribution Status'),
6a488035 TO	190	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	191	'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
	192	'default' => array(1),
	193	),
9d72cede EM	194	'total_amount' => array(
9d72cede EM	195	'title' => ts('Contribution Amount'),
6a488035 TO	196	),
	197	),
	198	),
9d72cede	199	'civicrm_contribution_soft' => array(
6a488035	200	'dao' => 'CRM_Contribute_DAO_ContributionSoft',
9d72cede EM	201	'fields' => array(
	202	'contribution_id' => array(
	203	'title' => ts('Contribution ID'),
6a488035 TO	204	'no_display' => TRUE,
	205	'default' => TRUE,
	206	),
9d72cede	207	'id' => array(
6a488035 TO	208	'default' => TRUE,
	209	'no_display' => TRUE,
	210	),
51fa20cb	211	'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
51fa20cb	212	),
9d72cede EM	213	'filters' => array(
	214	'soft_credit_type_id' => array(
	215	'title' => 'Soft Credit Type',
51fa20cb	216	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	217	'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
	218	'default' => NULL,
	219	'type' => CRM_Utils_Type::T_STRING,
	220	),
6a488035 TO	221	),
	222	'grouping' => 'softcredit-fields',
	223	),
6a488035 TO	224	);
6a488035 TO	225
d62fab33 RN	226	// If we have a campaign, build out the relevant elements
	227	if ($campaignEnabled && !empty($this->activeCampaigns)) {
	228	$this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
	229	'title' => ts('Campaign'),
	230	'default' => 'false',
	231	);
9d72cede EM	232	$this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
9d72cede EM	233	'title' => ts('Campaign'),
d62fab33 RN	234	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	235	'options' => $this->activeCampaigns,
	236	);
	237	}
	238
16e2e80c	239	$this->_groupFilter = TRUE;
6a488035	240	$this->_tagFilter = TRUE;
c6348f97	241
c6348f97	242	$this->_currencyColumn = 'civicrm_contribution_currency';
6a488035 TO	243	parent::__construct();
	244	}
	245
00be9182	246	public function preProcess() {
6a488035 TO	247	parent::preProcess();
	248	}
	249
00be9182	250	public function select() {
6a488035 TO	251	$select = array();
	252	$this->_columnHeaders = array();
	253	foreach ($this->_columns as $tableName => $table) {
	254	if (array_key_exists('fields', $table)) {
	255	foreach ($table['fields'] as $fieldName => $field) {
9d72cede EM	256	if (!empty($field['required']) \|\|
	257	!empty($this->_params['fields'][$fieldName])
	258	) {
6a488035 TO	259
	260	// include email column if set
	261	if ($tableName == 'civicrm_email') {
	262	$this->_emailField = TRUE;
	263	$this->_emailFieldCredit = TRUE;
	264	}
	265	elseif ($tableName == 'civicrm_email_creditor') {
	266	$this->_emailFieldCredit = TRUE;
	267	}
	268
	269	// include phone columns if set
	270	if ($tableName == 'civicrm_phone') {
	271	$this->_phoneField = TRUE;
	272	$this->_phoneFieldCredit = TRUE;
	273	}
	274	elseif ($tableName == 'civicrm_phone_creditor') {
	275	$this->_phoneFieldCredit = TRUE;
	276	}
	277
	278	// only include statistics columns if set
a7488080	279	if (!empty($field['statistics'])) {
6a488035 TO	280	foreach ($field['statistics'] as $stat => $label) {
	281	switch (strtolower($stat)) {
	282	case 'sum':
	283	$select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
	284	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	285	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
	286	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	287	break;
	288
	289	case 'count':
	290	$select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
	291	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
	292	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	293	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	294	break;
	295
	296	case 'avg':
	297	$select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
	298	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
	299	$this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
	300	$this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
	301	break;
	302	}
	303	}
	304	}
	305	else {
	306	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
	307	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
	308	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
	309	}
	310	}
	311	}
	312	}
	313	}
	314
c6348f97	315	$this->_select = 'SELECT ' . implode(', ', $select) . ' ';
6a488035 TO	316	}
6a488035 TO	317
74cf4551 EM	318	/**
	319	* @param $fields
	320	* @param $files
	321	* @param $self
	322	*
	323	* @return array
	324	*/
00be9182	325	public static function formRule($fields, $files, $self) {
6a488035 TO	326	$errors = $grouping = array();
	327	return $errors;
	328	}
	329
00be9182	330	public function from() {
6a488035	331	$alias_constituent = 'constituentname';
9d72cede EM	332	$alias_creditor = 'contact_civireport';
9d72cede EM	333	$this->_from = "
6a488035	334	FROM civicrm_contribution {$this->_aliases['civicrm_contribution']}
f813f78e	335	INNER JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
f813f78e	336	ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
6a488035	337	{$this->_aliases['civicrm_contribution']}.id
f813f78e	338	INNER JOIN civicrm_contact {$alias_constituent}
f813f78e	339	ON {$this->_aliases['civicrm_contribution']}.contact_id =
6a488035	340	{$alias_constituent}.id
f813f78e	341	LEFT JOIN civicrm_financial_type {$this->_aliases['civicrm_financial_type']}
f813f78e	342	ON {$this->_aliases['civicrm_contribution']}.financial_type_id =
6a488035 TO	343	{$this->_aliases['civicrm_financial_type']}.id
6a488035 TO	344	LEFT JOIN civicrm_contact {$alias_creditor}
f813f78e	345	ON {$this->_aliases['civicrm_contribution_soft']}.contact_id =
f813f78e	346	{$alias_creditor}.id
6a488035 TO	347	{$this->_aclFrom} ";
	348
	349	// include Constituent email field if email column is to be included
	350	if ($this->_emailField) {
	351	$alias = 'emailconst';
	352	$this->_from .= "
f813f78e	353	LEFT JOIN civicrm_email {$alias}
	354	ON {$alias_constituent}.id =
	355	{$alias}.contact_id AND
6a488035 TO	356	{$alias}.is_primary = 1\n";
	357	}
	358
	359	// include Creditors email field if email column is to be included
	360	if ($this->_emailFieldCredit) {
	361	$alias = 'emailcredit';
	362	$this->_from .= "
f813f78e	363	LEFT JOIN civicrm_email {$alias}
	364	ON {$alias_creditor}.id =
	365	{$alias}.contact_id AND
6a488035 TO	366	{$alias}.is_primary = 1\n";
	367	}
	368
	369	// include Constituents phone field if email column is to be included
	370	if ($this->_phoneField) {
	371	$alias = 'pconst';
	372	$this->_from .= "
f813f78e	373	LEFT JOIN civicrm_phone {$alias}
f813f78e	374	ON {$alias_constituent}.id =
6a488035 TO	375	{$alias}.contact_id AND
	376	{$alias}.is_primary = 1\n";
	377	}
	378
	379	// include Creditors phone field if email column is to be included
	380	if ($this->_phoneFieldCredit) {
	381	$alias = 'pcredit';
	382	$this->_from .= "
	383	LEFT JOIN civicrm_phone pcredit
f813f78e	384	ON {$alias_creditor}.id =
f813f78e	385	{$alias}.contact_id AND
6a488035 TO	386	{$alias}.is_primary = 1\n";
	387	}
	388	}
	389
00be9182	390	public function groupBy() {
9d72cede EM	391	$this->_rollup = 'WITH ROLLUP';
9d72cede EM	392	$this->_groupBy = "
6a488035 TO	393	GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}";
	394	}
	395
00be9182	396	public function where() {
6a488035 TO	397	parent::where();
	398	$this->_where .= " AND {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
	399	}
	400
74cf4551 EM	401	/**
	402	* @param $rows
	403	*
	404	* @return array
	405	*/
00be9182	406	public function statistics(&$rows) {
6a488035 TO	407	$statistics = parent::statistics($rows);
	408
	409	$select = "
	410	SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
	411	SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
c6348f97	412	ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
c6348f97	413	{$this->_aliases['civicrm_contribution']}.currency as currency
6a488035 TO	414	";
6a488035 TO	415
c6348f97	416	$sql = "{$select} {$this->_from} {$this->_where}
	417	GROUP BY {$this->_aliases['civicrm_contribution']}.currency
	418	";
6a488035	419
c6348f97	420	$dao = CRM_Core_DAO::executeQuery($sql);
c6348f97	421	$count = 0;
51fa20cb	422	$totalAmount = $average = array();
c6348f97	423	while ($dao->fetch()) {
9d72cede EM	424	$totalAmount[] =
	425	CRM_Utils_Money::format($dao->amount, $dao->currency) . '(' .
	426	$dao->count . ')';
	427	$average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
c6348f97	428	$count += $dao->count;
6a488035	429	}
c6348f97	430	$statistics['counts']['amount'] = array(
	431	'title' => ts('Total Amount'),
	432	'value' => implode(', ', $totalAmount),
	433	'type' => CRM_Utils_Type::T_STRING,
	434	);
	435	$statistics['counts']['count'] = array(
be205937	436	'title' => ts('Total Contributions'),
c6348f97	437	'value' => $count,
	438	);
	439	$statistics['counts']['avg'] = array(
	440	'title' => ts('Average'),
	441	'value' => implode(', ', $average),
	442	'type' => CRM_Utils_Type::T_STRING,
	443	);
6a488035 TO	444
	445	return $statistics;
	446	}
	447
00be9182	448	public function postProcess() {
6a488035 TO	449	$this->beginPostProcess();
	450
	451	$this->buildACLClause(array('constituentname', 'contact_civireport'));
	452	$sql = $this->buildQuery();
	453
9d72cede EM	454	$dao = CRM_Core_DAO::executeQuery($sql);
9d72cede EM	455	$rows = $graphRows = array();
6a488035 TO	456	$count = 0;
	457	while ($dao->fetch()) {
	458	$row = array();
	459	foreach ($this->_columnHeaders as $key => $value) {
	460	$row[$key] = $dao->$key;
	461	}
	462	$rows[] = $row;
	463	}
	464	$this->formatDisplay($rows);
	465
	466	// to hide the contact ID field from getting displayed
	467	unset($this->_columnHeaders['civicrm_contact_id_constituent']);
	468	unset($this->_columnHeaders['civicrm_contact_id_creditor']);
	469
	470	// assign variables to templates
	471	$this->doTemplateAssignment($rows);
	472	$this->endPostProcess($rows);
	473	}
	474
74cf4551 EM	475	/**
	476	* @param $rows
	477	*/
00be9182	478	public function alterDisplay(&$rows) {
6a488035 TO	479	// custom code to alter rows
6a488035 TO	480
9d72cede	481	$entryFound = FALSE;
6a488035	482	$dispname_flag = $phone_flag = $email_flag = 0;
9d72cede	483	$prev_email = $prev_dispname = $prev_phone = NULL;
6a488035 TO	484
	485	foreach ($rows as $rowNum => $row) {
	486	// Link constituent (contributor) to contribution detail
	487	if (array_key_exists('civicrm_contact_display_name_constituent', $row) &&
	488	array_key_exists('civicrm_contact_id_constituent', $row)
	489	) {
	490
	491	$url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
9d72cede EM	492	'reset=1&force=1&id_op=eq&id_value=' .
9d72cede EM	493	$row['civicrm_contact_id_constituent'],
6a488035 TO	494	$this->_absoluteUrl, $this->_id, $this->_drilldownReport
	495	);
	496	$rows[$rowNum]['civicrm_contact_display_name_constituent_link'] = $url;
c6348f97	497	$rows[$rowNum]['civicrm_contact_display_name_constituent_hover'] = ts('List all direct contribution(s) from this contact.');
6a488035 TO	498	$entryFound = TRUE;
	499	}
	500
e1ea3ee3	501	// convert soft credit contact name to link
9d72cede EM	502	if (array_key_exists('civicrm_contact_display_name_creditor', $row) &&
9d72cede EM	503	!empty($rows[$rowNum]['civicrm_contact_display_name_creditor']) &&
e1ea3ee3 DG	504	array_key_exists('civicrm_contact_id_creditor', $row)
	505	) {
	506	$url = CRM_Utils_System::url("civicrm/contact/view",
	507	'reset=1&cid=' . $row['civicrm_contact_id_creditor'],
	508	$this->_absoluteUrl
	509	);
	510	$rows[$rowNum]['civicrm_contact_display_name_creditor_link'] = $url;
	511	$rows[$rowNum]['civicrm_contact_display_name_creditor_hover'] = ts("view contact summary");
	512	}
	513
6a488035 TO	514	// make subtotals look nicer
	515	if (array_key_exists('civicrm_contact_id_constituent', $row) &&
	516	!$row['civicrm_contact_id_constituent']
	517	) {
	518	$this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
	519	$entryFound = TRUE;
	520	}
	521
d62fab33 RN	522	// convert campaign_id to campaign title
	523	if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
	524	if ($value = $row['civicrm_contribution_campaign_id']) {
	525	$rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
	526	$entryFound = TRUE;
	527	}
	528	}
	529
51fa20cb	530	//convert soft_credit_type_id into label
	531	if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
	532	$rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_OptionGroup::getLabel('soft_credit_type',
	533	$row['civicrm_contribution_soft_soft_credit_type_id']);
	534	}
	535
6a488035 TO	536	// skip looking further in rows, if first row itself doesn't
	537	// have the column we need
	538	if (!$entryFound) {
	539	break;
	540	}
	541	}
	542
	543	$this->removeDuplicates($rows);
	544	}
	545	}