CRM/Report/Form/Member/Detail.php

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