[civicrm-core.git] / CRM / Report / Form / Member / Detail.php

<?php
// $Id$

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

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

  protected $_addressField = FALSE;

  protected $_emailField = FALSE;

  protected $_phoneField = FALSE;
  
  protected $_contribField = FALSE;

  protected $_summary = NULL;

  protected $_customGroupExtends = array('Membership', 'Contribution');
  protected $_customGroupGroupBy = FALSE; 
  
  function __construct() {
    $this->_columns = array(
      'civicrm_contact' =>
      array(
        'dao' => 'CRM_Contact_DAO_Contact',
        'fields' =>
        array(
          'sort_name' =>
          array('title' => ts('Contact Name'),
            'required' => TRUE,
            'default' => TRUE,
            'no_repeat' => TRUE,
          ),
          'id' =>
          array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'first_name' =>
          array('title' => ts('First Name'),
            'no_repeat' => TRUE,
          ),
          'id' =>
          array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'last_name' =>
          array('title' => ts('Last Name'),
            'no_repeat' => TRUE,
          ),
          'id' =>
          array(
            'no_display' => TRUE,
            'required' => TRUE,
          ),
        ),
        'filters' =>
        array(
          'sort_name' =>
          array('title' => ts('Contact Name'),
            'operator' => 'like',
          ),
          'id' =>
          array('no_display' => TRUE),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_membership' =>
      array(
        'dao' => 'CRM_Member_DAO_Membership',
        'fields' =>
        array(
          'membership_type_id' => array(
            'title' => 'Membership Type',
            'required' => TRUE,
            'no_repeat' => TRUE,
          ),
          'membership_start_date' => array('title' => ts('Start Date'),
            'default' => TRUE,
          ),
          'membership_end_date' => array('title' => ts('End Date'),
            'default' => TRUE,
          ),
          'join_date' => array('title' => ts('Join Date'),
            'default' => TRUE,
          ),
          'source' => array('title' => 'Source'),
        ),
        'filters' => array(
          'join_date' =>
          array('operatorType' => CRM_Report_Form::OP_DATE),
          'membership_start_date' =>
          array('operatorType' => CRM_Report_Form::OP_DATE),
          'membership_end_date' =>
          array('operatorType' => CRM_Report_Form::OP_DATE),
          'owner_membership_id' =>
          array('title' => ts('Membership Owner ID'),
            'operatorType' => CRM_Report_Form::OP_INT,
          ),
          'tid' =>
          array(
            'name' => 'membership_type_id',
            'title' => ts('Membership Types'),
            'type' => CRM_Utils_Type::T_INT,
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Member_PseudoConstant::membershipType(),
          ),
        ),
        'grouping' => 'member-fields',
      ),
      'civicrm_membership_status' =>
      array(
        'dao' => 'CRM_Member_DAO_MembershipStatus',
        'alias' => 'mem_status',
        'fields' =>
        array('name' => array('title' => ts('Status'),
            'default' => TRUE,
          ),
        ),
        'filters' => array(
          'sid' =>
          array(
            'name' => 'id',
            'title' => ts('Status'),
            'type' => CRM_Utils_Type::T_INT,
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
          ),
        ),
        'grouping' => 'member-fields',
      ),
      'civicrm_address' =>
      array(
        'dao' => 'CRM_Core_DAO_Address',
        'fields' =>
        array(
          'street_address' => NULL,
          'city' => NULL,
          'postal_code' => NULL,
          'state_province_id' =>
          array('title' => ts('State/Province'),
          ),
          'country_id' =>
          array('title' => ts('Country'),
          ),
        ),
        'grouping' => 'contact-fields',
      ),
      'civicrm_email' =>
      array(
        'dao' => 'CRM_Core_DAO_Email',
        'fields' =>
        array('email' => NULL),
        'grouping' => 'contact-fields',
      ),
      'civicrm_phone' =>
      array(
        'dao' => 'CRM_Core_DAO_Phone',
        'fields' =>
        array('phone' => NULL),
        'grouping' => 'contact-fields',
      ),
      'civicrm_contribution' =>
      array(
        'dao' => 'CRM_Contribute_DAO_Contribution',
        'fields' =>
        array(
          'contribution_id' => array(
            'name' => 'id',
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'financial_type_id' => array('title' => ts('Financial Type')),
          'contribution_status_id' => array('title' => ts('Contribution Status')),
          'payment_instrument_id' => array('title' => ts('Payment Type')),
          'trxn_id' => NULL,
          'receive_date' => NULL,
          'receipt_date' => NULL,
          'fee_amount' => NULL,
          'net_amount' => NULL,
          'total_amount' => array('title' => ts('Payment Amount (most recent)'),
            'statistics' =>
            array('sum' => ts('Amount')),
          ),
        ),
        'filters' =>
        array(
          'receive_date' =>
          array('operatorType' => CRM_Report_Form::OP_DATE),
          '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,
          ),
          'payment_instrument_id' =>
          array('title' => ts('Payment Type'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'contribution_status_id' =>
          array('title' => ts('Contribution Status'),
            'operatorType' => CRM_Report_Form::OP_MULTISELECT,
            'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
            'type' => CRM_Utils_Type::T_INT,
          ),
          'total_amount' =>
          array('title' => ts('Contribution Amount')),
        ),
        'grouping' => 'contri-fields',
      ),
    );
    $this->_groupFilter = TRUE;
    $this->_tagFilter = TRUE;
    parent::__construct();
  }

  function preProcess() {
    $this->assign('reportTitle', ts('Membership Detail Report'));
    parent::preProcess();
  }

  function select() {
    $select = $this->_columnHeaders = array();

    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('fields', $table)) {
        foreach ($table['fields'] as $fieldName => $field) {
          if (CRM_Utils_Array::value('required', $field) ||
            CRM_Utils_Array::value($fieldName, $this->_params['fields'])
          ) {
            if ($tableName == 'civicrm_address') {
              $this->_addressField = TRUE;
            }
            elseif ($tableName == 'civicrm_email') {
              $this->_emailField = TRUE;
            }
            elseif ($tableName == 'civicrm_phone') {
              $this->_phoneField = TRUE;
            }
            elseif ($tableName == 'civicrm_contribution') {
              $this->_contribField = TRUE;
            }
            $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
            if (array_key_exists('title', $field)) {
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
            }
            $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
          }
        }
      }
    }

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

  function from() {
    $this->_from = NULL;

    $this->_from = "
         FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
               INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']} 
                          ON {$this->_aliases['civicrm_contact']}.id = 
                             {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
                          ON {$this->_aliases['civicrm_membership_status']}.id = 
                             {$this->_aliases['civicrm_membership']}.status_id ";


    //used when address field is selected
    if ($this->_addressField) {
      $this->_from .= "
             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} 
                       ON {$this->_aliases['civicrm_contact']}.id = 
                          {$this->_aliases['civicrm_address']}.contact_id AND 
                          {$this->_aliases['civicrm_address']}.is_primary = 1\n";
    }
    //used when email field is selected
    if ($this->_emailField) {
      $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\n";
    }
    //used when phone field is selected
    if ($this->_phoneField) {
      $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\n";
    }
    //used when contribution field is selected
    if ($this->_contribField) {
      $this->_from .= "
              LEFT JOIN (
                  SELECT cc.*, cmp.membership_id as membership_id 
                  FROM civicrm_membership_payment cmp
                    JOIN civicrm_contribution cc 
                      ON cc.id = cmp.contribution_id 
                  ORDER BY cc.receive_date DESC
                  ) {$this->_aliases['civicrm_contribution']} 
                ON {$this->_aliases['civicrm_membership']}.id = 
                  {$this->_aliases['civicrm_contribution']}.membership_id\n";
    }
  }

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

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

          if (!empty($clause)) {
            $clauses[] = $clause;
          }
        }
      }
    }

    if (empty($clauses)) {
      $this->_where = "WHERE ( 1 ) ";
    }
    else {
      $this->_where = "WHERE " . implode(' AND ', $clauses);
    }

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

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

  function orderBy() {
    $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
  }

  function postProcess() {

    $this->beginPostProcess();

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

    $rows = array();
    $this->buildRows($sql, $rows);

    $this->formatDisplay($rows);
    $this->doTemplateAssignment($rows);
    $this->endPostProcess($rows);
  }

  function alterDisplay(&$rows) {
    // custom code to alter rows
    $entryFound = FALSE;
    $checkList = array();
    
    $contributionTypes  = CRM_Contribute_PseudoConstant::financialType();
    $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
    $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
    
    foreach ($rows as $rowNum => $row) {

      if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
        // not repeat contact display names if it matches with the one
        // in previous row
        $repeatFound = FALSE;
        foreach ($row as $colName => $colVal) {
          if (CRM_Utils_Array::value($colName, $checkList) &&
            is_array($checkList[$colName]) &&
            in_array($colVal, $checkList[$colName])
          ) {
            $rows[$rowNum][$colName] = "";
            $repeatFound = TRUE;
          }
          if (in_array($colName, $this->_noRepeats)) {
            $checkList[$colName][] = $colVal;
          }
        }
      }

      if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
        if ($value = $row['civicrm_membership_membership_type_id']) {
          $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
        }
        $entryFound = TRUE;
      }

      if (array_key_exists('civicrm_address_state_province_id', $row)) {
        if ($value = $row['civicrm_address_state_province_id']) {
          $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
        }
        $entryFound = TRUE;
      }

      if (array_key_exists('civicrm_address_country_id', $row)) {
        if ($value = $row['civicrm_address_country_id']) {
          $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
        }
        $entryFound = TRUE;
      }

      if (array_key_exists('civicrm_contact_sort_name', $row) &&
        $rows[$rowNum]['civicrm_contact_sort_name'] &&
        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.");
        $entryFound = TRUE;
      }
      
      if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
        $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
        $entryFound = TRUE;
      }
      if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
        $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
        $entryFound = TRUE;
      }
      if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
        $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
        $entryFound = TRUE;
      }

      if (!$entryFound) {
        break;
      }
    }
  }
}
Commit	Line	Data
6a488035 TO	1	<?php
	2	// $Id$
	3
	4	/*
	5	+--------------------------------------------------------------------+
	6	\| CiviCRM version 4.3 \|
	7	+--------------------------------------------------------------------+
	8	\| Copyright CiviCRM LLC (c) 2004-2013 \|
	9	+--------------------------------------------------------------------+
	10	\| This file is a part of CiviCRM. \|
	11	\| \|
	12	\| CiviCRM is free software; you can copy, modify, and distribute it \|
	13	\| under the terms of the GNU Affero General Public License \|
	14	\| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. \|
	15	\| \|
	16	\| CiviCRM is distributed in the hope that it will be useful, but \|
	17	\| WITHOUT ANY WARRANTY; without even the implied warranty of \|
	18	\| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. \|
	19	\| See the GNU Affero General Public License for more details. \|
	20	\| \|
	21	\| You should have received a copy of the GNU Affero General Public \|
	22	\| License and the CiviCRM Licensing Exception along \|
	23	\| with this program; if not, contact CiviCRM LLC \|
	24	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	25	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	26	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	27	+--------------------------------------------------------------------+
	28	*/
	29
	30	/**
	31	*
	32	* @package CRM
	33	* @copyright CiviCRM LLC (c) 2004-2013
	34	* $Id$
	35	*
	36	*/
	37	class CRM_Report_Form_Member_Detail extends CRM_Report_Form {
	38
	39	protected $_addressField = FALSE;
	40
	41	protected $_emailField = FALSE;
	42
	43	protected $_phoneField = FALSE;
	44
	45	protected $_contribField = FALSE;
	46
	47	protected $_summary = NULL;
	48
	49	protected $_customGroupExtends = array('Membership', 'Contribution');
	50	protected $_customGroupGroupBy = FALSE;
	51
	52	function __construct() {
	53	$this->_columns = array(
	54	'civicrm_contact' =>
	55	array(
	56	'dao' => 'CRM_Contact_DAO_Contact',
	57	'fields' =>
	58	array(
	59	'sort_name' =>
	60	array('title' => ts('Contact Name'),
	61	'required' => TRUE,
	62	'default' => TRUE,
	63	'no_repeat' => TRUE,
	64	),
65	'id' =>
66	array(
67	'no_display' => TRUE,
68	'required' => TRUE,
69	),
70	'first_name' =>
71	array('title' => ts('First Name'),
72	'no_repeat' => TRUE,
73	),
74	'id' =>
75	array(
76	'no_display' => TRUE,
77	'required' => TRUE,
78	),
79	'last_name' =>
80	array('title' => ts('Last Name'),
81	'no_repeat' => TRUE,
82	),
83	'id' =>
84	array(
85	'no_display' => TRUE,
86	'required' => TRUE,
87	),
88	),
89	'filters' =>
90	array(
91	'sort_name' =>
92	array('title' => ts('Contact Name'),
93	'operator' => 'like',
94	),
95	'id' =>
96	array('no_display' => TRUE),
97	),
98	'grouping' => 'contact-fields',
99	),
100	'civicrm_membership' =>
101	array(
102	'dao' => 'CRM_Member_DAO_Membership',
103	'fields' =>
104	array(
105	'membership_type_id' => array(
106	'title' => 'Membership Type',
107	'required' => TRUE,
108	'no_repeat' => TRUE,
109	),
110	'membership_start_date' => array('title' => ts('Start Date'),
111	'default' => TRUE,
112	),
113	'membership_end_date' => array('title' => ts('End Date'),
114	'default' => TRUE,
115	),
116	'join_date' => array('title' => ts('Join Date'),
117	'default' => TRUE,
118	),
119	'source' => array('title' => 'Source'),
120	),
121	'filters' => array(
122	'join_date' =>
123	array('operatorType' => CRM_Report_Form::OP_DATE),
124	'membership_start_date' =>
125	array('operatorType' => CRM_Report_Form::OP_DATE),
126	'membership_end_date' =>
127	array('operatorType' => CRM_Report_Form::OP_DATE),
128	'owner_membership_id' =>
129	array('title' => ts('Membership Owner ID'),
130	'operatorType' => CRM_Report_Form::OP_INT,
131	),
132	'tid' =>
133	array(
134	'name' => 'membership_type_id',
135	'title' => ts('Membership Types'),
136	'type' => CRM_Utils_Type::T_INT,
137	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
138	'options' => CRM_Member_PseudoConstant::membershipType(),
139	),
140	),
141	'grouping' => 'member-fields',
142	),
143	'civicrm_membership_status' =>
144	array(
145	'dao' => 'CRM_Member_DAO_MembershipStatus',
146	'alias' => 'mem_status',
147	'fields' =>
148	array('name' => array('title' => ts('Status'),
149	'default' => TRUE,
150	),
151	),
152	'filters' => array(
153	'sid' =>
154	array(
155	'name' => 'id',
156	'title' => ts('Status'),
157	'type' => CRM_Utils_Type::T_INT,
158	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
159	'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
160	),
161	),
162	'grouping' => 'member-fields',
163	),
164	'civicrm_address' =>
165	array(
166	'dao' => 'CRM_Core_DAO_Address',
167	'fields' =>
168	array(
169	'street_address' => NULL,
170	'city' => NULL,
171	'postal_code' => NULL,
172	'state_province_id' =>
173	array('title' => ts('State/Province'),
174	),
175	'country_id' =>
176	array('title' => ts('Country'),
177	),
178	),
179	'grouping' => 'contact-fields',
180	),
181	'civicrm_email' =>
182	array(
183	'dao' => 'CRM_Core_DAO_Email',
184	'fields' =>
185	array('email' => NULL),
186	'grouping' => 'contact-fields',
187	),
188	'civicrm_phone' =>
189	array(
190	'dao' => 'CRM_Core_DAO_Phone',
191	'fields' =>
192	array('phone' => NULL),
193	'grouping' => 'contact-fields',
194	),
195	'civicrm_contribution' =>
196	array(
197	'dao' => 'CRM_Contribute_DAO_Contribution',
198	'fields' =>
199	array(
200	'contribution_id' => array(
201	'name' => 'id',
202	'no_display' => TRUE,
203	'required' => TRUE,
204	),
205	'financial_type_id' => array('title' => ts('Financial Type')),
206	'contribution_status_id' => array('title' => ts('Contribution Status')),
207	'payment_instrument_id' => array('title' => ts('Payment Type')),
208	'trxn_id' => NULL,
209	'receive_date' => NULL,
210	'receipt_date' => NULL,
211	'fee_amount' => NULL,
212	'net_amount' => NULL,
213	'total_amount' => array('title' => ts('Payment Amount (most recent)'),
214	'statistics' =>
215	array('sum' => ts('Amount')),
216	),
217	),
218	'filters' =>
219	array(
220	'receive_date' =>
221	array('operatorType' => CRM_Report_Form::OP_DATE),
222	'financial_type_id' =>
223	array('title' => ts('Financial Type'),
224	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
225	'options' => CRM_Contribute_PseudoConstant::financialType(),
226	'type' => CRM_Utils_Type::T_INT,
227	),
228	'payment_instrument_id' =>
229	array('title' => ts('Payment Type'),
230	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
231	'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
232	'type' => CRM_Utils_Type::T_INT,
233	),
234	'contribution_status_id' =>
235	array('title' => ts('Contribution Status'),
236	'operatorType' => CRM_Report_Form::OP_MULTISELECT,
237	'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
238	'type' => CRM_Utils_Type::T_INT,
239	),
240	'total_amount' =>
241	array('title' => ts('Contribution Amount')),
242	),
243	'grouping' => 'contri-fields',
244	),
245	);
246	$this->_groupFilter = TRUE;
247	$this->_tagFilter = TRUE;
248	parent::__construct();
249	}
250
251	function preProcess() {
252	$this->assign('reportTitle', ts('Membership Detail Report'));
253	parent::preProcess();
254	}
255
256	function select() {
257	$select = $this->_columnHeaders = array();
258
259	foreach ($this->_columns as $tableName => $table) {
260	if (array_key_exists('fields', $table)) {
261	foreach ($table['fields'] as $fieldName => $field) {
262	if (CRM_Utils_Array::value('required', $field) \|\|
263	CRM_Utils_Array::value($fieldName, $this->_params['fields'])
264	) {
265	if ($tableName == 'civicrm_address') {
266	$this->_addressField = TRUE;
267	}
268	elseif ($tableName == 'civicrm_email') {
269	$this->_emailField = TRUE;
270	}
271	elseif ($tableName == 'civicrm_phone') {
272	$this->_phoneField = TRUE;
273	}
274	elseif ($tableName == 'civicrm_contribution') {
275	$this->_contribField = TRUE;
276	}
277	$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
278	if (array_key_exists('title', $field)) {
279	$this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
280	}
281	$this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
282	}
283	}
284	}
285	}
286
287	$this->_select = "SELECT " . implode(', ', $select) . " ";
288	}
289
290	function from() {
291	$this->_from = NULL;
292
293	$this->_from = "
294	FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
295	INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
296	ON {$this->_aliases['civicrm_contact']}.id =
297	{$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
298	LEFT JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
299	ON {$this->_aliases['civicrm_membership_status']}.id =
300	{$this->_aliases['civicrm_membership']}.status_id ";
301
302
303	//used when address field is selected
304	if ($this->_addressField) {
305	$this->_from .= "
306	LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
307	ON {$this->_aliases['civicrm_contact']}.id =
308	{$this->_aliases['civicrm_address']}.contact_id AND
309	{$this->_aliases['civicrm_address']}.is_primary = 1\n";
310	}
311	//used when email field is selected
312	if ($this->_emailField) {
313	$this->_from .= "
314	LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
315	ON {$this->_aliases['civicrm_contact']}.id =
316	{$this->_aliases['civicrm_email']}.contact_id AND
317	{$this->_aliases['civicrm_email']}.is_primary = 1\n";
318	}
319	//used when phone field is selected
320	if ($this->_phoneField) {
321	$this->_from .= "
322	LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
323	ON {$this->_aliases['civicrm_contact']}.id =
324	{$this->_aliases['civicrm_phone']}.contact_id AND
325	{$this->_aliases['civicrm_phone']}.is_primary = 1\n";
326	}
327	//used when contribution field is selected
328	if ($this->_contribField) {
329	$this->_from .= "
330	LEFT JOIN (
331	SELECT cc.*, cmp.membership_id as membership_id
332	FROM civicrm_membership_payment cmp
333	JOIN civicrm_contribution cc
334	ON cc.id = cmp.contribution_id
335	ORDER BY cc.receive_date DESC
336	) {$this->_aliases['civicrm_contribution']}
337	ON {$this->_aliases['civicrm_membership']}.id =
338	{$this->_aliases['civicrm_contribution']}.membership_id\n";
339	}
340	}
341
342	function where() {
343	$clauses = array();
344	foreach ($this->_columns as $tableName => $table) {
345	if (array_key_exists('filters', $table)) {
346	foreach ($table['filters'] as $fieldName => $field) {
347	$clause = NULL;
348	if (CRM_Utils_Array::value('operatorType', $field) & CRM_Utils_Type::T_DATE) {
349	$relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
350	$from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
351	$to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
352
353	$clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
354	}
355	else {
356	$op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
357	if ($op) {
358	$clause = $this->whereClause($field,
359	$op,
360	CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
361	CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
362	CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
363	);
364	}
365	}
366
367	if (!empty($clause)) {
368	$clauses[] = $clause;
369	}
370	}
371	}
372	}
373
374	if (empty($clauses)) {
375	$this->_where = "WHERE ( 1 ) ";
376	}
377	else {
378	$this->_where = "WHERE " . implode(' AND ', $clauses);
379	}
380
381	if ($this->_aclWhere) {
382	$this->_where .= " AND {$this->_aclWhere} ";
383	}
384	}
385
386	function groupBy() {
387	$this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
388	}
389
390	function orderBy() {
391	$this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
392	}
393
394	function postProcess() {
395
396	$this->beginPostProcess();
397
398	// get the acl clauses built before we assemble the query
399	$this->buildACLClause($this->_aliases['civicrm_contact']);
400	$sql = $this->buildQuery(TRUE);
401
402	$rows = array();
403	$this->buildRows($sql, $rows);
404
405	$this->formatDisplay($rows);
406	$this->doTemplateAssignment($rows);
407	$this->endPostProcess($rows);
408	}
409
410	function alterDisplay(&$rows) {
411	// custom code to alter rows
412	$entryFound = FALSE;
413	$checkList = array();
414
415	$contributionTypes = CRM_Contribute_PseudoConstant::financialType();
416	$contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
417	$paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
418
419	foreach ($rows as $rowNum => $row) {
420
421	if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
422	// not repeat contact display names if it matches with the one
423	// in previous row
424	$repeatFound = FALSE;
425	foreach ($row as $colName => $colVal) {
426	if (CRM_Utils_Array::value($colName, $checkList) &&
427	is_array($checkList[$colName]) &&
428	in_array($colVal, $checkList[$colName])
429	) {
430	$rows[$rowNum][$colName] = "";
431	$repeatFound = TRUE;
432	}
433	if (in_array($colName, $this->_noRepeats)) {
434	$checkList[$colName][] = $colVal;
435	}
436	}
437	}
438
439	if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
440	if ($value = $row['civicrm_membership_membership_type_id']) {
441	$rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
442	}
443	$entryFound = TRUE;
444	}
445
446	if (array_key_exists('civicrm_address_state_province_id', $row)) {
447	if ($value = $row['civicrm_address_state_province_id']) {
448	$rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
449	}
450	$entryFound = TRUE;
451	}
452
453	if (array_key_exists('civicrm_address_country_id', $row)) {
454	if ($value = $row['civicrm_address_country_id']) {
455	$rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
456	}
457	$entryFound = TRUE;
458	}
459
460	if (array_key_exists('civicrm_contact_sort_name', $row) &&
461	$rows[$rowNum]['civicrm_contact_sort_name'] &&
462	array_key_exists('civicrm_contact_id', $row)
463	) {
464	$url = CRM_Utils_System::url("civicrm/contact/view",
465	'reset=1&cid=' . $row['civicrm_contact_id'],
466	$this->_absoluteUrl
467	);
468	$rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
469	$rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
470	$entryFound = TRUE;
471	}
472
473	if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
474	$rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
475	$entryFound = TRUE;
476	}
477	if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
478	$rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
479	$entryFound = TRUE;
480	}
481	if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
482	$rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
483	$entryFound = TRUE;
484	}
485
486	if (!$entryFound) {
487	break;
488	}
489	}
490	}
491	}
492