[civicrm-core.git] / CRM / Report / Form / Contribute / Recur.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_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,
          ),
          'amount' => array(
            'title' => ts('Amount'),
            'default' => 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,
          ),
          '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,
          ),
          '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_TIME,
          ),
          'next_sched_contribution_date' => array(
            'title' => ts('Next Scheduled Contribution Date'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => CRM_Utils_Type::T_TIME,
          ),
          'end_date' => array(
            'title' => ts('End Date'),
            'operatorType' => CRM_Report_Form::OP_DATE,
            'type' => 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';
    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";
    $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)) {
      // 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.

        $installment_clause = '(' .
          $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);
        $installment_clause .= " AND $frequency_unit_db_alias = 'month' ) OR \n";

        $installment_clause .= '(' .
          $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);
        $installment_clause .= " AND $frequency_unit_db_alias = 'day' ) OR \n";

        $installment_clause .= '(' .
          $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);
        $installment_clause .= " AND $frequency_unit_db_alias = 'week' ) OR \n";

        $installment_clause .= '(' .
          $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);
        $installment_clause .= " AND $frequency_unit_db_alias = 'year' )\n";

        $this->_where .= " AND ";
        $this->_where .= "(";
        $this->_where .= "($end_date_db_alias IS NOT NULL AND $end_date_clause)\n";
        $this->_where .= " OR \n";
        $this->_where .= "($installments_db_alias IS NOT NULL AND ($installment_clause))\n";
        $this->_where .= ')';
        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	+--------------------------------------------------------------------+
39de6fd5	4	\| CiviCRM version 4.6 \|
c1413e28	5	+--------------------------------------------------------------------+
39de6fd5	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
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
39de6fd5	31	* @copyright CiviCRM LLC (c) 2004-2014
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	),
	108	'amount' => array(
	109	'title' => ts('Amount'),
	110	'default' => TRUE,
	111	),
	112	'contribution_status_id' => array(
be205937	113	'title' => ts('Contribution Status'),
c1413e28 DG	114	),
	115	'frequency_interval' => array(
	116	'title' => ts('Frequency interval'),
	117	'default' => TRUE,
	118	),
	119	'frequency_unit' => array(
	120	'title' => ts('Frequency unit'),
	121	'default' => TRUE,
	122	),
	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	),
	183	'installments' => array(
	184	'title' => ts('Installments'),
	185	'type' => CRM_Utils_Type::T_INT,
	186	),
	187	'start_date' => array(
	188	'title' => ts('Start Date'),
	189	'operatorType' => CRM_Report_Form::OP_DATE,
	190	'type' => CRM_Utils_Type::T_TIME,
	191	),
	192	'next_sched_contribution_date' => array(
	193	'title' => ts('Next Scheduled Contribution Date'),
	194	'operatorType' => CRM_Report_Form::OP_DATE,
	195	'type' => CRM_Utils_Type::T_TIME,
	196	),
	197	'end_date' => array(
	198	'title' => ts('End Date'),
	199	'operatorType' => CRM_Report_Form::OP_DATE,
	200	'type' => CRM_Utils_Type::T_TIME,
	201	),
	202	'calculated_end_date' => array(
	203	'title' => ts('Calculated end date (either end date or date all installments will be made)'),
	204	'description' => "does this work?",
	205	'operatorType' => CRM_Report_Form::OP_DATE,
21dfd5f5	206	'pseudofield' => TRUE,
c1413e28 DG	207	),
c1413e28 DG	208	),
21dfd5f5	209	),
c1413e28 DG	210	);
	211	$this->_currencyColumn = 'civicrm_contribution_recur_currency';
	212	parent::__construct();
	213	}
353ffa53	214
ced9bfed EM	215	/**
	216	* Get template file name.
	217	*
	218	* @return string
	219	*/
00be9182	220	public function getTemplateName() {
84178120	221	return 'CRM/Report/Form.tpl';
c1413e28 DG	222	}
c1413e28 DG	223
ced9bfed EM	224	/**
	225	* Generate FROM SQL clause.
	226	*/
00be9182	227	public function from() {
c1413e28 DG	228	$this->_from = "
	229	FROM civicrm_contact {$this->_aliases['civicrm_contact']}
	230	INNER JOIN civicrm_contribution_recur {$this->_aliases['civicrm_contribution_recur']}
	231	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution_recur']}.contact_id";
	232	$this->_from .= "
	233	LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
	234	ON {$this->_aliases['civicrm_contribution_recur']}.id = {$this->_aliases['civicrm_contribution']}.contribution_recur_id";
	235	$this->_from .= "
	236	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
	237	ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id";
	238	$this->_from .= "
	239	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
	240	ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
	241	{$this->_aliases['civicrm_phone']}.is_primary = 1)";
	242	}
	243
00be9182	244	public function groupBy() {
c1413e28 DG	245	$this->_groupBy = "GROUP BY " . $this->_aliases['civicrm_contribution_recur'] . ".id";
	246	}
	247
00be9182	248	public function where() {
c1413e28 DG	249	parent::where();
	250	// Handle calculated end date. This can come from one of two sources:
	251	// Either there is a specified end date for the end_date field
	252	// Or, we calculate the end date based on the start date +
	253	// installments * intervals using the mysql date_add function, along
	254	// with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH)
	255	$date_suffixes = array('relative', 'from', 'to');
22e263ad	256	while (list(, $suffix) = each($date_suffixes)) {
c1413e28	257	// Check to see if the user wants to search by calculated date.
22e263ad	258	if (!empty($this->_params['calculated_end_date_' . $suffix])) {
c1413e28 DG	259	// The calculated date field is in use - spring into action
	260	// Gather values
	261	$relative = CRM_Utils_Array::value("calculated_end_date_relative", $this->_params);
	262	$from = CRM_Utils_Array::value("calculated_end_date_from", $this->_params);
	263	$to = CRM_Utils_Array::value("calculated_end_date_to", $this->_params);
	264	$end_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['dbAlias'];
	265	$end_date_type = $this->_columns['civicrm_contribution_recur']['filters']['end_date']['type'];
	266	$start_date_type = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['type'];
	267	$frequency_unit_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_unit']['dbAlias'];
	268	$frequency_interval_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['frequency_interval']['dbAlias'];
	269	$installments_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['installments']['dbAlias'];
	270	$start_date_db_alias = $this->_columns['civicrm_contribution_recur']['filters']['start_date']['dbAlias'];
	271
	272	// The end date clause is simple to construct
353ffa53	273	$end_date_clause = $this->dateClause($end_date_db_alias, $relative, $from, $to, $end_date_type, NULL, NULL);
c1413e28 DG	274
	275	// NOTE: For the calculation based on installment, there doesn't
	276	// seem to be a way to include the interval unit (e.g. month,
	277	// date, etc) as a field name - so we have to build a complex
	278	// OR statement instead.
	279
	280	$installment_clause = '(' .
	281	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)",
353ffa53	282	$relative, $from, $to, $start_date_type, NULL, NULL);
c1413e28 DG	283	$installment_clause .= " AND $frequency_unit_db_alias = 'month' ) OR \n";
	284
	285	$installment_clause .= '(' .
	286	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)",
353ffa53	287	$relative, $from, $to, $start_date_type, NULL, NULL);
c1413e28 DG	288	$installment_clause .= " AND $frequency_unit_db_alias = 'day' ) OR \n";
	289
	290	$installment_clause .= '(' .
	291	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)",
353ffa53	292	$relative, $from, $to, $start_date_type, NULL, NULL);
c1413e28 DG	293	$installment_clause .= " AND $frequency_unit_db_alias = 'week' ) OR \n";
	294
	295	$installment_clause .= '(' .
	296	$this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)",
353ffa53	297	$relative, $from, $to, $start_date_type, NULL, NULL);
c1413e28 DG	298	$installment_clause .= " AND $frequency_unit_db_alias = 'year' )\n";
	299
	300	$this->_where .= " AND ";
	301	$this->_where .= "(";
	302	$this->_where .= "($end_date_db_alias IS NOT NULL AND $end_date_clause)\n";
	303	$this->_where .= " OR \n";
	304	$this->_where .= "($installments_db_alias IS NOT NULL AND ($installment_clause))\n";
	305	$this->_where .= ')';
	306	break;
	307	}
	308	}
	309	}
	310
	311
ced9bfed EM	312	/**
	313	* Alter display of rows.
	314	*
	315	* Iterate through the rows retrieved via SQL and make changes for display purposes,
	316	* such as rendering contacts as links.
	317	*
	318	* @param array $rows
	319	* Rows generated by SQL, with an array for each row.
	320	*/
00be9182	321	public function alterDisplay(&$rows) {
c1413e28 DG	322	$contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
	323	foreach ($rows as $rowNum => $row) {
	324	// convert display name to links
	325	if (array_key_exists('civicrm_contact_sort_name', $row) &&
	326	CRM_Utils_Array::value('civicrm_contact_sort_name', $rows[$rowNum]) &&
	327	array_key_exists('civicrm_contact_id', $row)
	328	) {
	329	$url = CRM_Utils_System::url('civicrm/contact/view',
	330	'reset=1&cid=' . $row['civicrm_contact_id'],
	331	$this->_absoluteUrl
	332	);
	333	$rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
	334	$rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
	335	}
	336
	337	// handle contribution status id
	338	if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_contribution_status_id', $row)) {
	339	$rows[$rowNum]['civicrm_contribution_recur_contribution_status_id'] = $contributionStatus[$value];
	340	}
84178120	341
c1413e28	342	if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) {
84178120	343	$rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']);
c1413e28	344	}
84178120	345
c1413e28 DG	346	}
c1413e28 DG	347	}
96025800	348
ef10e0b5	349	}