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

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

  /**
   * This report has not been optimised for group filtering.
   *
   * The functionality for group filtering has been improved but not
   * all reports have been adjusted to take care of it. This report has not
   * and will run an inefficient query until fixed.
   *
   * CRM-19170
   *
   * @var bool
   */
  protected $groupFilterNotOptimised = TRUE;

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