CRM/Report/Form/Member/Detail.php

   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           'currency' => array(
 209             'required' => TRUE,
 210             'no_display' => TRUE,
 211           ),
 212           'trxn_id' => NULL,
 213           'receive_date' => NULL,
 214           'receipt_date' => NULL,
 215           'fee_amount' => NULL,
 216           'net_amount' => NULL,
 217           'total_amount' => array('title' => ts('Payment Amount (most recent)'),
 218             'statistics' =>
 219             array('sum' => ts('Amount')),
 220           ),
 221         ),
 222         'filters' =>
 223         array(
 224           'receive_date' =>
 225           array('operatorType' => CRM_Report_Form::OP_DATE),
 226           'financial_type_id' =>
 227           array('title' => ts('Financial Type'),
 228             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 229             'options' => CRM_Contribute_PseudoConstant::financialType(),
 230             'type' => CRM_Utils_Type::T_INT,
 231           ),
 232           'payment_instrument_id' =>
 233           array('title' => ts('Payment Type'),
 234             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 235             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 236             'type' => CRM_Utils_Type::T_INT,
 237           ),
 238           'currency' =>
 239           array('title' => 'Currency',
 240             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 241             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 242             'default' => NULL,
 243             'type' => CRM_Utils_Type::T_STRING,
 244           ),
 245           'contribution_status_id' =>
 246           array('title' => ts('Contribution Status'),
 247             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 248             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 249             'type' => CRM_Utils_Type::T_INT,
 250           ),
 251           'total_amount' =>
 252           array('title' => ts('Contribution Amount')),
 253         ),
 254         'grouping' => 'contri-fields',
 255       ),
 256     );
 257     $this->_groupFilter = TRUE;
 258     $this->_tagFilter = TRUE;
 259
 260     $this->_currencyColumn = 'civicrm_contribution_currency';
 261     parent::__construct();
 262   }
 263
 264   function preProcess() {
 265     $this->assign('reportTitle', ts('Membership Detail Report'));
 266     parent::preProcess();
 267   }
 268
 269   function select() {
 270     $select = $this->_columnHeaders = array();
 271
 272     foreach ($this->_columns as $tableName => $table) {
 273       if (array_key_exists('fields', $table)) {
 274         foreach ($table['fields'] as $fieldName => $field) {
 275           if (CRM_Utils_Array::value('required', $field) ||
 276             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 277           ) {
 278             if ($tableName == 'civicrm_address') {
 279               $this->_addressField = TRUE;
 280             }
 281             elseif ($tableName == 'civicrm_email') {
 282               $this->_emailField = TRUE;
 283             }
 284             elseif ($tableName == 'civicrm_phone') {
 285               $this->_phoneField = TRUE;
 286             }
 287             elseif ($tableName == 'civicrm_contribution') {
 288               $this->_contribField = TRUE;
 289             }
 290             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 291             if (array_key_exists('title', $field)) {
 292               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 293             }
 294             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 295           }
 296         }
 297       }
 298     }
 299
 300     $this->_select = "SELECT " . implode(', ', $select) . " ";
 301   }
 302
 303   function from() {
 304     $this->_from = NULL;
 305
 306     $this->_from = "
 307          FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 308                INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 309                           ON {$this->_aliases['civicrm_contact']}.id =
 310                              {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 311                LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 312                           ON {$this->_aliases['civicrm_membership_status']}.id =
 313                              {$this->_aliases['civicrm_membership']}.status_id ";
 314
 315
 316     //used when address field is selected
 317     if ($this->_addressField) {
 318       $this->_from .= "
 319              LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 320                        ON {$this->_aliases['civicrm_contact']}.id =
 321                           {$this->_aliases['civicrm_address']}.contact_id AND
 322                           {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 323     }
 324     //used when email field is selected
 325     if ($this->_emailField) {
 326       $this->_from .= "
 327               LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 328                         ON {$this->_aliases['civicrm_contact']}.id =
 329                            {$this->_aliases['civicrm_email']}.contact_id AND
 330                            {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 331     }
 332     //used when phone field is selected
 333     if ($this->_phoneField) {
 334       $this->_from .= "
 335               LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 336                         ON {$this->_aliases['civicrm_contact']}.id =
 337                            {$this->_aliases['civicrm_phone']}.contact_id AND
 338                            {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 339     }
 340     //used when contribution field is selected
 341     if ($this->_contribField) {
 342       $this->_from .= "
 343               LEFT JOIN (
 344                   SELECT cc.*, cmp.membership_id as membership_id
 345                   FROM civicrm_membership_payment cmp
 346                     JOIN civicrm_contribution cc
 347                       ON cc.id = cmp.contribution_id
 348                   ORDER BY cc.receive_date DESC
 349                   ) {$this->_aliases['civicrm_contribution']}
 350                 ON {$this->_aliases['civicrm_membership']}.id =
 351                   {$this->_aliases['civicrm_contribution']}.membership_id\n";
 352     }
 353   }
 354
 355   function where() {
 356     $clauses = array();
 357     foreach ($this->_columns as $tableName => $table) {
 358       if (array_key_exists('filters', $table)) {
 359         foreach ($table['filters'] as $fieldName => $field) {
 360           $clause = NULL;
 361           if (CRM_Utils_Array::value('operatorType', $field) & CRM_Utils_Type::T_DATE) {
 362             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 363             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 364             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 365
 366             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 367           }
 368           else {
 369             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 370             if ($op) {
 371               $clause = $this->whereClause($field,
 372                 $op,
 373                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 374                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 375                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 376               );
 377             }
 378           }
 379
 380           if (!empty($clause)) {
 381             $clauses[] = $clause;
 382           }
 383         }
 384       }
 385     }
 386
 387     if (empty($clauses)) {
 388       $this->_where = "WHERE ( 1 ) ";
 389     }
 390     else {
 391       $this->_where = "WHERE " . implode(' AND ', $clauses);
 392     }
 393
 394     if ($this->_aclWhere) {
 395       $this->_where .= " AND {$this->_aclWhere} ";
 396     }
 397   }
 398
 399   function groupBy() {
 400     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 401   }
 402
 403   function orderBy() {
 404     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 405   }
 406
 407   function postProcess() {
 408
 409     $this->beginPostProcess();
 410
 411     // get the acl clauses built before we assemble the query
 412     $this->buildACLClause($this->_aliases['civicrm_contact']);
 413     $sql = $this->buildQuery(TRUE);
 414
 415     $rows = array();
 416     $this->buildRows($sql, $rows);
 417
 418     $this->formatDisplay($rows);
 419     $this->doTemplateAssignment($rows);
 420     $this->endPostProcess($rows);
 421   }
 422
 423   function alterDisplay(&$rows) {
 424     // custom code to alter rows
 425     $entryFound = FALSE;
 426     $checkList = array();
 427
 428     $contributionTypes  = CRM_Contribute_PseudoConstant::financialType();
 429     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 430     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 431
 432     foreach ($rows as $rowNum => $row) {
 433
 434       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 435         // not repeat contact display names if it matches with the one
 436         // in previous row
 437         $repeatFound = FALSE;
 438         foreach ($row as $colName => $colVal) {
 439           if (CRM_Utils_Array::value($colName, $checkList) &&
 440             is_array($checkList[$colName]) &&
 441             in_array($colVal, $checkList[$colName])
 442           ) {
 443             $rows[$rowNum][$colName] = "";
 444             $repeatFound = TRUE;
 445           }
 446           if (in_array($colName, $this->_noRepeats)) {
 447             $checkList[$colName][] = $colVal;
 448           }
 449         }
 450       }
 451
 452       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 453         if ($value = $row['civicrm_membership_membership_type_id']) {
 454           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 455         }
 456         $entryFound = TRUE;
 457       }
 458
 459       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 460         if ($value = $row['civicrm_address_state_province_id']) {
 461           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 462         }
 463         $entryFound = TRUE;
 464       }
 465
 466       if (array_key_exists('civicrm_address_country_id', $row)) {
 467         if ($value = $row['civicrm_address_country_id']) {
 468           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 469         }
 470         $entryFound = TRUE;
 471       }
 472
 473       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 474         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 475         array_key_exists('civicrm_contact_id', $row)
 476       ) {
 477         $url = CRM_Utils_System::url("civicrm/contact/view",
 478           'reset=1&cid=' . $row['civicrm_contact_id'],
 479           $this->_absoluteUrl
 480         );
 481         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 482         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 483         $entryFound = TRUE;
 484       }
 485
 486       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 487         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 488         $entryFound = TRUE;
 489       }
 490       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 491         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 492         $entryFound = TRUE;
 493       }
 494       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 495         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 496         $entryFound = TRUE;
 497       }
 498
 499       if (!$entryFound) {
 500         break;
 501       }
 502     }
 503   }
 504 }
 505