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

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

  /**
   * Class constructor.
   */
  public function __construct() {
    $this->_columns = array(
      'civicrm_contact' => array(
        'dao' => 'CRM_Contact_DAO_Contact',
        'order_bys' => array(
          'sort_name' => array(
            'title' => ts("Last name, First name"),
          ),
        ),
        'fields' => array(
          'sort_name' => array(
            'title' => ts('Contact Name'),
            'no_repeat' => TRUE,
            'default' => TRUE,
          ),
          'id' => array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
        ),
      ),
      'civicrm_email' => array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' => array(
          'email' => array(
            'title' => ts('Email'),
            'no_repeat' => TRUE,
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_phone' => array(
        'dao' => 'CRM_Core_DAO_Phone',
        'fields' => array(
          'phone' => array(
            'title' => ts('Phone'),
            'no_repeat' => TRUE,
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_contribution' => array(
        'dao' => 'CRM_Contribute_DAO_Contribution',
        'fields' => array(
          'id' => array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'total_amount' => array(
            'title' => ts('Amount Contributed to Date'),
            'statistics' => array(
              'sum' => ts("Total Amount Contributed"),
            ),
          ),
        ),
      ),
      'civicrm_contribution_recur' => array(
        'dao' => 'CRM_Contribute_DAO_ContributionRecur',
        'fields' => array(
          'id' => array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'currency' => array(
            'title' => ts("Currency"),
            'required' => TRUE,
            'no_display' => TRUE,
          ),
          'contribution_status_id' => array(
            'title' => ts('Contribution Status'),
          ),
          'frequency_interval' => array(
            'title' => ts('Frequency interval'),
            'default' => TRUE,
          ),
          'frequency_unit' => array(
            'title' => ts('Frequency unit'),
            'default' => TRUE,
          ),
          'amount' => array(
            'title' => ts('Installment Amount'),
            'default' => TRUE,
          ),
          'installments' => array(
            'title' => ts('Installments'),
            'default' => TRUE,
          ),
          'start_date' => array(
            'title' => ts('Start Date'),
          ),
          'create_date' => array(
            'title' => ts('Create Date'),
          ),
          'modified_date' => array(
            'title' => ts('Modified Date'),
          ),
          'cancel_date' => array(
            'title' => ts('Cancel Date'),
          ),
          'end_date' => array(
            'title' => ts('End Date'),
          ),
          'next_sched_contribution_date' => array(
            'title' => ts('Next Scheduled Contribution Date'),
          ),
          'failure_count' => array(
            'title' => ts('Failure Count'),
          ),
          'failure_retry_date' => array(
            'title' => ts('Failure Retry Date'),
          ),
        ),
        'filters' => array(
          'contribution_status_id' => array(
            'title' => ts('Contribution Status'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
            'default' => array(5),
            'type' => CRM_Utils_Type::T_INT,
          ),
          '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,
          ),
          'financial_type_id' => array(
            'title' => ts('Financial Type'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::financialType(),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'frequency_unit' => array(
            'title' => ts('Frequency Unit'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Core_OptionGroup::values('recur_frequency_units'),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'frequency_interval' => array(
            'title' => ts('Frequency Interval'),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'amount' => array(
            'title' => ts('Installment Amount'),
            'type' => CRM_Utils_Type::T_MONEY,
          ),
          'installments' => array(
            'title' => ts('Installments'),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'start_date' => array(
            'title' => ts('Start Date'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
          ),
          'next_sched_contribution_date' => array(
            'title' => ts('Next Scheduled Contribution Date'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
          ),
          'end_date' => array(
            'title' => ts('End Date'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
          ),
          'modified_date' => array(
            'title' => ts('Last Contribution Processed'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
          ),
          'calculated_end_date' => array(
            'title' => ts('Calculated end date (either end date or date all installments will be made)'),
            'description' => "does this work?",
            'operatorType' => CRM_Report_Form::OP_DATE,
            'pseudofield' => TRUE,
          ),
        ),
      ),
    );
    $this->_currencyColumn = 'civicrm_contribution_recur_currency';
    $this->_groupFilter = TRUE;
    parent::__construct();
  }

  /**
   * Get template file name.
   *
   * @return string
   */
  public function getTemplateName() {
    return 'CRM/Report/Form.tpl';
  }

  /**
   * Generate FROM SQL clause.
   */
  public function from() {
    $this->_from = "
      FROM civicrm_contact  {$this->_aliases['civicrm_contact']}
        INNER JOIN civicrm_contribution_recur   {$this->_aliases['civicrm_contribution_recur']}
          ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
    $this->_from .= "
      LEFT JOIN civicrm_contribution  {$this->_aliases['civicrm_contribution']}
        ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
    $this->_from .= "
      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)";
    $this->_from .= "
      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)";
  }

  public function groupBy() {
    $this->_groupBy = "GROUP BY " . $this->_aliases['civicrm_contribution_recur'] . ".id";
  }

  public function where() {
    parent::where();
    // Handle calculated end date. This can come from one of two sources:
    // Either there is a specified end date for the end_date field
    // Or, we calculate the end date based on the start date +
    // installments * intervals using the mysql date_add function, along
    // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
    $date_suffixes = array('relative', 'from', 'to');
    while (list(, $suffix) = each($date_suffixes)) {
      $isBreak = FALSE;
      // Check to see if the user wants to search by calculated date.
      if (!empty($this->_params['calculated_end_date_' . $suffix])) {
        // The calculated date field is in use - spring into action
        // Gather values
        $relative = CRM_Utils_Array::value("calculated_end_date_relative", $this->_params);
        $from = CRM_Utils_Array::value("calculated_end_date_from", $this->_params);
        $to = CRM_Utils_Array::value("calculated_end_date_to", $this->_params);
        $end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
        $end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type'];
        $start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type'];
        $frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
        $frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
        $installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
        $start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];

        // The end date clause is simple to construct
        $end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);

        // NOTE: For the calculation based on installment, there doesn't
        // seem to be a way to include the interval unit (e.g. month,
        // date, etc) as a field name - so we have to build a complex
        // OR statement instead.

        $this->_where .= 'AND (' .
          $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
            $relative, $from, $to, $start_date_type, NULL, NULL);
        $this->_where .= " AND $frequency_unit_db_alias = 'month' ) OR \n";

        $this->_where .= '(' .
          $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
            $relative, $from, $to, $start_date_type, NULL, NULL);
        $this->_where .= " AND $frequency_unit_db_alias = 'day' ) OR \n";

        $this->_where .= '(' .
          $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
            $relative, $from, $to, $start_date_type, NULL, NULL);
        $this->_where .= " AND $frequency_unit_db_alias = 'week' ) OR \n";

        $this->_where .= '(' .
          $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
            $relative, $from, $to, $start_date_type, NULL, NULL);
        $this->_where .= " AND $frequency_unit_db_alias = 'year' )
   AND (($end_date_db_alias IS NOT NULL AND $end_date_clause)
    OR ($installments_db_alias IS NOT NULL))
";
        $isBreak = TRUE;
      }
      if (!empty($this->_params['modified_date_' . $suffix])) {
        $this->_where .= " AND {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id = 1";
        $isBreak = TRUE;
      }
      if (!empty($isBreak)) {
        break;
      }
    }
  }


  /**
   * Alter display of rows.
   *
   * Iterate through the rows retrieved via SQL and make changes for display purposes,
   * such as rendering contacts as links.
   *
   * @param array $rows
   *   Rows generated by SQL, with an array for each row.
   */
  public function alterDisplay(&$rows) {
    $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
    foreach ($rows as $rowNum => $row) {
      // convert display name to links
      if (array_key_exists('civicrm_contact_sort_name', $row) &&
        CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
        array_key_exists('civicrm_contact_id', $row)
      ) {
        $url = CRM_Utils_System::url('civicrm/contact/view',
          'reset=1&cid=' . $row['civicrm_contact_id'],
          $this->_absoluteUrl
        );
        $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
        $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
      }

      // handle contribution status id
      if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_contribution_status_id', $row)) {
        $rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
      }

      if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) {
        $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
      }
    }
  }

}
Commit	Line	Data
c1413e28 DG	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
7e9e8871	4	\| CiviCRM version 4.7 \|
c1413e28	5	+--------------------------------------------------------------------+
e7112fa7	6	\| Copyright CiviCRM LLC (c) 2004-2015 \|
c1413e28 DG	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	+--------------------------------------------------------------------+
d25dd0ee	26	*/
c1413e28 DG	27
	28	/**
	29	*
	30	* @package CRM
e7112fa7	31	* @copyright CiviCRM LLC (c) 2004-2015
c1413e28 DG	32	* $Id$
	33	*
	34	*/
	35	class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form {
	36
ced9bfed EM	37	/**
	38	* Class constructor.
	39	*/
00be9182	40	public function __construct() {
c1413e28 DG	41	$this->_columns = array(
	42	'civicrm_contact' => array(
	43	'dao' => 'CRM_Contact_DAO_Contact',
	44	'order_bys' => array(
	45	'sort_name' => array(
	46	'title' => ts("Last name, First name"),
	47	),
	48	),
	49	'fields' => array(
	50	'sort_name' => array(
	51	'title' => ts('Contact Name'),
	52	'no_repeat' => TRUE,
	53	'default' => TRUE,
	54	),
	55	'id' => array(
	56	'no_display' => TRUE,
	57	'required' => TRUE,
	58	),
	59	),
	60	),
	61	'civicrm_email' => array(
	62	'dao' => 'CRM_Core_DAO_Email',
	63	'fields' => array(
	64	'email' => array(
	65	'title' => ts('Email'),
	66	'no_repeat' => TRUE,
	67	),
	68	),
	69	'grouping' => 'contact-fields',
	70	),
	71	'civicrm_phone' => array(
	72	'dao' => 'CRM_Core_DAO_Phone',
	73	'fields' => array(
	74	'phone' => array(
	75	'title' => ts('Phone'),
	76	'no_repeat' => TRUE,
	77	),
	78	),
	79	'grouping' => 'contact-fields',
	80	),
	81	'civicrm_contribution' => array(
	82	'dao' => 'CRM_Contribute_DAO_Contribution',
	83	'fields' => array(
	84	'id' => array(
	85	'no_display' => TRUE,
	86	'required' => TRUE,
	87	),
	88	'total_amount' => array(
	89	'title' => ts('Amount Contributed to Date'),
	90	'statistics' => array(
21dfd5f5	91	'sum' => ts("Total Amount Contributed"),
c1413e28 DG	92	),
	93	),
	94	),
	95	),
	96	'civicrm_contribution_recur' => array(
	97	'dao' => 'CRM_Contribute_DAO_ContributionRecur',
	98	'fields' => array(
	99	'id' => array(
	100	'no_display' => TRUE,
	101	'required' => TRUE,
	102	),
	103	'currency' => array(
	104	'title' => ts("Currency"),
	105	'required' => TRUE,
	106	'no_display' => TRUE,
	107	),
c1413e28	108	'contribution_status_id' => array(
be205937	109	'title' => ts('Contribution Status'),
c1413e28 DG	110	),
	111	'frequency_interval' => array(
	112	'title' => ts('Frequency interval'),
	113	'default' => TRUE,
	114	),
	115	'frequency_unit' => array(
	116	'title' => ts('Frequency unit'),
	117	'default' => TRUE,
	118	),
2006e4c4	119	'amount' => array(
979f1561	120	'title' => ts('Installment Amount'),
2006e4c4	121	'default' => TRUE,
979f1561	122	),
c1413e28 DG	123	'installments' => array(
	124	'title' => ts('Installments'),
	125	'default' => TRUE,
	126	),
	127	'start_date' => array(
	128	'title' => ts('Start Date'),
	129	),
	130	'create_date' => array(
	131	'title' => ts('Create Date'),
	132	),
	133	'modified_date' => array(
	134	'title' => ts('Modified Date'),
	135	),
	136	'cancel_date' => array(
	137	'title' => ts('Cancel Date'),
	138	),
	139	'end_date' => array(
	140	'title' => ts('End Date'),
	141	),
	142	'next_sched_contribution_date' => array(
	143	'title' => ts('Next Scheduled Contribution Date'),
	144	),
	145	'failure_count' => array(
	146	'title' => ts('Failure Count'),
	147	),
	148	'failure_retry_date' => array(
	149	'title' => ts('Failure Retry Date'),
	150	),
	151	),
	152	'filters' => array(
	153	'contribution_status_id' => array(
be205937	154	'title' => ts('Contribution Status'),
c1413e28 DG	155	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	156	'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
	157	'default' => array(5),
	158	'type' => CRM_Utils_Type::T_INT,
	159	),
	160	'currency' => array(
	161	'title' => 'Currency',
	162	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
	163	'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
	164	'default' => NULL,
	165	'type' => CRM_Utils_Type::T_STRING,
	166	),
	167	'financial_type_id' => array(
	168	'title' => ts('Financial Type'),
	169	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
353ffa53	170	'options' => CRM_Contribute_PseudoConstant::financialType(),
c1413e28 DG	171	'type' => CRM_Utils_Type::T_INT,
	172	),
	173	'frequency_unit' => array(
	174	'title' => ts('Frequency Unit'),
	175	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
84178120	176	'options' => CRM_Core_OptionGroup::values('recur_frequency_units'),
c1413e28 DG	177	'type' => CRM_Utils_Type::T_INT,
	178	),
	179	'frequency_interval' => array(
	180	'title' => ts('Frequency Interval'),
	181	'type' => CRM_Utils_Type::T_INT,
	182	),
2006e4c4	183	'amount' => array(
	184	'title' => ts('Installment Amount'),
	185	'type' => CRM_Utils_Type::T_MONEY,
	186	),
c1413e28 DG	187	'installments' => array(
	188	'title' => ts('Installments'),
	189	'type' => CRM_Utils_Type::T_INT,
	190	),
	191	'start_date' => array(
	192	'title' => ts('Start Date'),
	193	'operatorType' => CRM_Report_Form::OP_DATE,
8fea776b	194	'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
c1413e28 DG	195	),
	196	'next_sched_contribution_date' => array(
	197	'title' => ts('Next Scheduled Contribution Date'),
	198	'operatorType' => CRM_Report_Form::OP_DATE,
8fea776b	199	'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
c1413e28 DG	200	),
	201	'end_date' => array(
	202	'title' => ts('End Date'),
	203	'operatorType' => CRM_Report_Form::OP_DATE,
8fea776b	204	'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
c1413e28	205	),
8fea776b	206	'modified_date' => array(
979f1561	207	'title' => ts('Last Contribution Processed'),
979f1561	208	'operatorType' => CRM_Report_Form::OP_DATE,
8fea776b	209	'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
979f1561	210	),
c1413e28 DG	211	'calculated_end_date' => array(
	212	'title' => ts('Calculated end date (either end date or date all installments will be made)'),
	213	'description' => "does this work?",
	214	'operatorType' => CRM_Report_Form::OP_DATE,
21dfd5f5	215	'pseudofield' => TRUE,
c1413e28 DG	216	),
c1413e28 DG	217	),
21dfd5f5	218	),
c1413e28 DG	219	);
c1413e28 DG	220	$this->_currencyColumn = 'civicrm_contribution_recur_currency';
979f1561	221	$this->_groupFilter = TRUE;
c1413e28 DG	222	parent::__construct();
c1413e28 DG	223	}
353ffa53	224
ced9bfed EM	225	/**
	226	* Get template file name.
	227	*
	228	* @return string
	229	*/
00be9182	230	public function getTemplateName() {
84178120	231	return 'CRM/Report/Form.tpl';
c1413e28 DG	232	}
c1413e28 DG	233
ced9bfed EM	234	/**
	235	* Generate FROM SQL clause.
	236	*/
00be9182	237	public function from() {
c1413e28 DG	238	$this->_from = "
	239	FROM civicrm_contact {$this->_aliases['civicrm_contact']}
	240	INNER JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
	241	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
	242	$this->_from .= "
	243	LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
	244	ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
	245	$this->_from .= "
	246	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
14e99c8f KG	247	ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
14e99c8f KG	248	{$this->_aliases['civicrm_email']}.is_primary = 1)";
c1413e28 DG	249	$this->_from .= "
	250	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
	251	ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
14e99c8f	252	{$this->_aliases['civicrm_phone']}.is_primary = 1)";
c1413e28 DG	253	}
c1413e28 DG	254
00be9182	255	public function groupBy() {
c1413e28 DG	256	$this->_groupBy = "GROUP BY " . $this->_aliases['civicrm_contribution_recur'] . ".id";
	257	}
	258
00be9182	259	public function where() {
c1413e28 DG	260	parent::where();
	261	// Handle calculated end date. This can come from one of two sources:
	262	// Either there is a specified end date for the end_date field
	263	// Or, we calculate the end date based on the start date +
	264	// installments * intervals using the mysql date_add function, along
	265	// with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
	266	$date_suffixes = array('relative', 'from', 'to');
22e263ad	267	while (list(, $suffix) = each($date_suffixes)) {
979f1561	268	$isBreak = FALSE;
c1413e28	269	// Check to see if the user wants to search by calculated date.
22e263ad	270	if (!empty($this->_params['calculated_end_date_' . $suffix])) {
c1413e28 DG	271	// The calculated date field is in use - spring into action
	272	// Gather values
	273	$relative = CRM_Utils_Array::value("calculated_end_date_relative", $this->_params);
	274	$from = CRM_Utils_Array::value("calculated_end_date_from", $this->_params);
	275	$to = CRM_Utils_Array::value("calculated_end_date_to", $this->_params);
	276	$end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
	277	$end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type'];
	278	$start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type'];
	279	$frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
	280	$frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
	281	$installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
	282	$start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
	283
	284	// The end date clause is simple to construct
353ffa53	285	$end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
c1413e28 DG	286
	287	// NOTE: For the calculation based on installment, there doesn't
	288	// seem to be a way to include the interval unit (e.g. month,
	289	// date, etc) as a field name - so we have to build a complex
	290	// OR statement instead.
	291
979f1561	292	$this->_where .= 'AND (' .
c1413e28	293	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
353ffa53	294	$relative, $from, $to, $start_date_type, NULL, NULL);
979f1561	295	$this->_where .= " AND $frequency_unit_db_alias = 'month' ) OR \n";
c1413e28	296
979f1561	297	$this->_where .= '(' .
c1413e28	298	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
353ffa53	299	$relative, $from, $to, $start_date_type, NULL, NULL);
979f1561	300	$this->_where .= " AND $frequency_unit_db_alias = 'day' ) OR \n";
c1413e28	301
979f1561	302	$this->_where .= '(' .
c1413e28	303	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
353ffa53	304	$relative, $from, $to, $start_date_type, NULL, NULL);
cc3360ca	305	$this->_where .= " AND $frequency_unit_db_alias = 'week' ) OR \n";
c1413e28	306
979f1561	307	$this->_where .= '(' .
c1413e28	308	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
353ffa53	309	$relative, $from, $to, $start_date_type, NULL, NULL);
979f1561	310	$this->_where .= " AND $frequency_unit_db_alias = 'year' )
	311	AND (($end_date_db_alias IS NOT NULL AND $end_date_clause)
	312	OR ($installments_db_alias IS NOT NULL))
	313	";
	314	$isBreak = TRUE;
	315	}
8fea776b	316	if (!empty($this->_params['modified_date_' . $suffix])) {
8fea776b	317	$this->_where .= " AND {$this->_aliases['civicrm_contribution_recur']}.contribution_status_id = 1";
979f1561	318	$isBreak = TRUE;
	319	}
	320	if (!empty($isBreak)) {
c1413e28 DG	321	break;
	322	}
	323	}
	324	}
	325
	326
ced9bfed EM	327	/**
	328	* Alter display of rows.
	329	*
	330	* Iterate through the rows retrieved via SQL and make changes for display purposes,
	331	* such as rendering contacts as links.
	332	*
	333	* @param array $rows
	334	* Rows generated by SQL, with an array for each row.
	335	*/
00be9182	336	public function alterDisplay(&$rows) {
c1413e28 DG	337	$contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
	338	foreach ($rows as $rowNum => $row) {
	339	// convert display name to links
	340	if (array_key_exists('civicrm_contact_sort_name', $row) &&
	341	CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
	342	array_key_exists('civicrm_contact_id', $row)
	343	) {
	344	$url = CRM_Utils_System::url('civicrm/contact/view',
	345	'reset=1&cid=' . $row['civicrm_contact_id'],
	346	$this->_absoluteUrl
	347	);
	348	$rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
	349	$rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
	350	}
	351
	352	// handle contribution status id
	353	if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_contribution_status_id', $row)) {
	354	$rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
	355	}
84178120	356
c1413e28	357	if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) {
84178120	358	$rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
44fd0292	359	}
c1413e28 DG	360	}
c1413e28 DG	361	}
96025800	362
ef10e0b5	363	}