CRM/Report/Form/Member/Lapse.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   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_Lapse extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38   protected $_addressField = FALSE;
  39   protected $_emailField = FALSE;
  40   protected $_phoneField = FALSE;
  41   protected $_charts = array('' => 'Tabular');
  42   protected $_customGroupExtends = array(
  43     'Membership');
  44   public $_drilldownReport = array('member/detail' => 'Link to Detail Report');
  45
  46   /**
  47    *
  48    */
  49   /**
  50    *
  51    */
  52   function __construct() {
  53
  54     // Check if CiviCampaign is a) enabled and b) has active campaigns
  55     $config = CRM_Core_Config::singleton();
  56     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  57     if ($campaignEnabled) {
  58       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  59       $this->activeCampaigns = $getCampaigns['campaigns'];
  60       asort($this->activeCampaigns);
  61     }
  62
  63     // UI for selecting columns to appear in the report list
  64     // array conatining the columns, group_bys and filters build and provided to Form
  65     $this->_columns = array(
  66       'civicrm_contact' =>
  67       array(
  68         'dao' => 'CRM_Contact_DAO_Contact',
  69         'fields' =>
  70         array(
  71           'sort_name' =>
  72           array('title' => ts('Member Name'),
  73             'no_repeat' => TRUE,
  74             'required' => TRUE,
  75           ),
  76           'id' =>
  77           array(
  78             'no_display' => TRUE,
  79             'required' => TRUE,
  80           ),
  81           'first_name' =>
  82           array('title' => ts('First Name'),
  83             'no_repeat' => TRUE,
  84           ),
  85           'id' =>
  86           array(
  87             'no_display' => TRUE,
  88             'required' => TRUE,
  89           ),
  90           'last_name' =>
  91           array('title' => ts('Last Name'),
  92             'no_repeat' => TRUE,
  93           ),
  94           'id' =>
  95           array(
  96             'no_display' => TRUE,
  97             'required' => TRUE,
  98           ),
  99           'contact_type' =>
 100           array(
 101             'title' => ts('Contact Type'),
 102           ),
 103           'contact_sub_type' =>
 104           array(
 105             'title' => ts('Contact SubType'),
 106           ),
 107         ),
 108         'grouping' => 'contact-fields',
 109       ),
 110       'civicrm_membership_type' =>
 111       array(
 112         'dao' => 'CRM_Member_DAO_MembershipType',
 113         'grouping' => 'member-fields',
 114         'filters' =>
 115         array(
 116           'tid' =>
 117           array(
 118             'name' => 'id',
 119             'title' => ts('Membership Types'),
 120             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 121             'options' => CRM_Member_PseudoConstant::membershipType(),
 122           ),
 123         ),
 124       ),
 125       'civicrm_membership' =>
 126       array(
 127         'dao' => 'CRM_Member_DAO_Membership',
 128         'grouping' => 'member-fields',
 129         'fields' =>
 130         array(
 131           'membership_type_id' =>
 132           array(
 133             'title' => 'Membership Type',
 134             'required' => TRUE,
 135             'type' => CRM_Utils_Type::T_STRING,
 136           ),
 137           'membership_start_date' => array('title' => ts('Current Cycle Start Date'),
 138           ),
 139           'membership_end_date' => array('title' => ts('Membership Lapse Date'),
 140             'required' => TRUE,
 141           ),
 142         ),
 143         'filters' =>
 144         array(
 145           'membership_end_date' =>
 146           array(
 147             'title' => 'Lapsed Memberships',
 148             'operatorType' => CRM_Report_Form::OP_DATE,
 149           ),
 150         ),
 151       ),
 152       'civicrm_membership_status' =>
 153       array(
 154         'dao' => 'CRM_Member_DAO_MembershipStatus',
 155         'alias' => 'mem_status',
 156         'fields' =>
 157         array(
 158           'name' => array('title' => ts('Current Status'),
 159             'required' => TRUE,
 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             'default' => TRUE,
 178           ),
 179         ),
 180         'grouping' => 'contact-fields',
 181       ),
 182       'civicrm_phone' =>
 183       array(
 184         'dao' => 'CRM_Core_DAO_Phone',
 185         'alias' => 'phone',
 186         'fields' =>
 187         array('phone' => NULL),
 188         'grouping' => 'contact-fields',
 189       ),
 190       'civicrm_email' =>
 191       array(
 192         'dao' => 'CRM_Core_DAO_Email',
 193         'fields' =>
 194         array('email' => NULL),
 195         'grouping' => 'contact-fields',
 196       ),
 197       'civicrm_group' =>
 198       array(
 199         'dao' => 'CRM_Contact_DAO_GroupContact',
 200         'alias' => 'cgroup',
 201         'filters' =>
 202         array(
 203           'gid' =>
 204           array(
 205             'name' => 'group_id',
 206             'title' => ts('Group'),
 207             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 208             'group' => TRUE,
 209             'options' => CRM_Core_PseudoConstant::group(),
 210           ),
 211         ),
 212       ),
 213     );
 214
 215     // If we have a campaign, build out the relevant elements
 216     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 217       $this->_columns['civicrm_membership']['fields']['campaign_id'] = array(
 218         'title' => 'Campaign',
 219         'default' => 'false',
 220       );
 221       $this->_columns['civicrm_membership']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 222         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 223         'options' => $this->activeCampaigns,
 224       );
 225     }
 226
 227     $this->_tagFilter = TRUE;
 228     parent::__construct();
 229   }
 230
 231   function preProcess() {
 232     parent::preProcess();
 233   }
 234
 235   function select() {
 236     $select = array();
 237     $this->_columnHeaders = array();
 238     foreach ($this->_columns as $tableName => $table) {
 239       if (array_key_exists('fields', $table)) {
 240         foreach ($table['fields'] as $fieldName => $field) {
 241           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 242             // to include optional columns address ,email and phone only if checked
 243             if ($tableName == 'civicrm_address') {
 244               $this->_addressField = TRUE;
 245             }
 246             elseif ($tableName == 'civicrm_email') {
 247               $this->_emailField = TRUE;
 248             }
 249             elseif ($tableName == 'civicrm_phone') {
 250               $this->_phoneField = TRUE;
 251             }
 252             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 253             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 254             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 255           }
 256         }
 257       }
 258     }
 259     $this->_select = "SELECT " . implode(', ', $select) . " ";
 260   }
 261
 262   /**
 263    * @param $fields
 264    * @param $files
 265    * @param $self
 266    *
 267    * @return array
 268    */
 269   static function formRule($fields, $files, $self) {
 270     $errors = $grouping = array();
 271     //check for searching combination of dispaly columns and
 272     //grouping criteria
 273
 274     return $errors;
 275   }
 276
 277   function from() {
 278     $this->_from = NULL;
 279
 280     $this->_from = "
 281         FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 282               INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 283                          ON {$this->_aliases['civicrm_contact']}.id =
 284                             {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 285               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 286                          ON {$this->_aliases['civicrm_membership_status']}.id =
 287                             {$this->_aliases['civicrm_membership']}.status_id
 288               LEFT  JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']}
 289                          ON {$this->_aliases['civicrm_membership']}.membership_type_id =
 290                             {$this->_aliases['civicrm_membership_type']}.id";
 291
 292     //  include address field if address column is to be included
 293     if ($this->_addressField) {
 294       $this->_from .= "
 295             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 296                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 297     }
 298
 299     // include email field if email column is to be included
 300     if ($this->_emailField) {
 301       $this->_from .= "
 302             LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 303                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 304     }
 305
 306     // include phone field if phone column is to be included
 307     if ($this->_phoneField) {
 308       $this->_from .= "
 309             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 310                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id
 311                      AND {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 312     }
 313   }
 314
 315   function where() {
 316     $clauses = array();
 317     foreach ($this->_columns as $tableName => $table) {
 318       if (array_key_exists('filters', $table)) {
 319         foreach ($table['filters'] as $fieldName => $field) {
 320           $clause = NULL;
 321
 322           if ($field['operatorType'] & CRM_Utils_Type::T_DATE) {
 323             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 324             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 325             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 326
 327             if ($relative || $from || $to) {
 328               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 329             }
 330           }
 331           else {
 332             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 333             if ($op) {
 334               $clause = $this->whereClause($field,
 335                 $op,
 336                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 337                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 338                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 339               );
 340             }
 341           }
 342           if (!empty($clause)) {
 343             $clauses[$fieldName] = $clause;
 344           }
 345         }
 346       }
 347     }
 348
 349     if (empty($clauses)) {
 350       $this->_where = "WHERE end_date < '" . date('Y-m-d') . "' AND {$this->_aliases['civicrm_membership_status']}.name = 'Expired'";
 351     }
 352     else {
 353       if (!array_key_exists('end_date', $clauses)) {
 354         $this->_where = "WHERE end_date < '" . date('Y-m-d') . "' AND " . implode(' AND ', $clauses);
 355       }
 356       else {
 357         $this->_where = "WHERE " . implode(' AND ', $clauses);
 358       }
 359     }
 360
 361     if ($this->_aclWhere) {
 362       $this->_where .= " AND {$this->_aclWhere} ";
 363     }
 364   }
 365
 366   function orderBy() {
 367     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 368   }
 369
 370   function postProcess() {
 371     $this->beginPostProcess();
 372
 373     // get the acl clauses built before we assemble the query
 374     $this->buildACLClause($this->_aliases['civicrm_contact']);
 375     $sql = $this->buildQuery(TRUE);
 376
 377     $dao   = CRM_Core_DAO::executeQuery($sql);
 378     $rows  = $graphRows = array();
 379     $count = 0;
 380     while ($dao->fetch()) {
 381       $row = array();
 382       foreach ($this->_columnHeaders as $key => $value) {
 383         $row[$key] = $dao->$key;
 384       }
 385
 386       $rows[] = $row;
 387     }
 388     $this->formatDisplay($rows);
 389
 390     // assign variables to templates
 391     $this->doTemplateAssignment($rows);
 392
 393     $this->endPostProcess($rows);
 394   }
 395
 396   /**
 397    * @param $rows
 398    */
 399   function alterDisplay(&$rows) {
 400     // custom code to alter rows
 401     $entryFound = FALSE;
 402     $checkList = array();
 403
 404     foreach ($rows as $rowNum => $row) {
 405
 406       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 407         // not repeat contact display names if it matches with the one
 408         // in previous row
 409
 410         $repeatFound = FALSE;
 411         foreach ($row as $colName => $colVal) {
 412           if (!empty($checkList[$colName]) &&
 413             is_array($checkList[$colName]) &&
 414             in_array($colVal, $checkList[$colName])
 415           ) {
 416             $rows[$rowNum][$colName] = "";
 417             $repeatFound = TRUE;
 418           }
 419           if (in_array($colName, $this->_noRepeats)) {
 420             $checkList[$colName][] = $colVal;
 421           }
 422         }
 423       }
 424
 425       //handle the Membership Type Ids
 426       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 427         if ($value = $row['civicrm_membership_membership_type_id']) {
 428           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 429         }
 430         $entryFound = TRUE;
 431       }
 432
 433       // handle state province
 434       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 435         if ($value = $row['civicrm_address_state_province_id']) {
 436           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 437         }
 438         $entryFound = TRUE;
 439       }
 440
 441       // handle country
 442       if (array_key_exists('civicrm_address_country_id', $row)) {
 443         if ($value = $row['civicrm_address_country_id']) {
 444           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 445         }
 446         $entryFound = TRUE;
 447       }
 448
 449       // convert display name to links
 450       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 451         array_key_exists('civicrm_contact_id', $row)
 452       ) {
 453         $url = CRM_Report_Utils_Report::getNextUrl('member/detail',
 454           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 455           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 456         );
 457         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 458         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Membership Detail for this Contact.");
 459       }
 460
 461       // If using campaigns, convert campaign_id to campaign title
 462       if (array_key_exists('civicrm_membership_campaign_id', $row)) {
 463         if ($value = $row['civicrm_membership_campaign_id']) {
 464           $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns[$value];
 465         }
 466         $entryFound = TRUE;
 467       }
 468
 469       // skip looking further in rows, if first row itself doesn't
 470       // have the column we need
 471       if (!$entryFound) {
 472         break;
 473       }
 474     }
 475   }
 476 }
 477