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