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     );
 198
 199     // If we have a campaign, build out the relevant elements
 200     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 201       $this->_columns['civicrm_membership']['fields']['campaign_id'] = array(
 202         'title' => 'Campaign',
 203         'default' => 'false',
 204       );
 205       $this->_columns['civicrm_membership']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 206         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 207         'options' => $this->activeCampaigns,
 208       );
 209     }
 210
 211     $this->_groupFilter = TRUE;
 212     $this->_tagFilter = TRUE;
 213     parent::__construct();
 214   }
 215
 216   function preProcess() {
 217     parent::preProcess();
 218   }
 219
 220   function select() {
 221     $select = array();
 222     $this->_columnHeaders = array();
 223     foreach ($this->_columns as $tableName => $table) {
 224       if (array_key_exists('fields', $table)) {
 225         foreach ($table['fields'] as $fieldName => $field) {
 226           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 227             // to include optional columns address ,email and phone only if checked
 228             if ($tableName == 'civicrm_address') {
 229               $this->_addressField = TRUE;
 230             }
 231             elseif ($tableName == 'civicrm_email') {
 232               $this->_emailField = TRUE;
 233             }
 234             elseif ($tableName == 'civicrm_phone') {
 235               $this->_phoneField = TRUE;
 236             }
 237             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 238             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 239             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 240           }
 241         }
 242       }
 243     }
 244     $this->_select = "SELECT " . implode(', ', $select) . " ";
 245   }
 246
 247   /**
 248    * @param $fields
 249    * @param $files
 250    * @param $self
 251    *
 252    * @return array
 253    */
 254   static function formRule($fields, $files, $self) {
 255     $errors = $grouping = array();
 256     //check for searching combination of dispaly columns and
 257     //grouping criteria
 258
 259     return $errors;
 260   }
 261
 262   function from() {
 263     $this->_from = NULL;
 264
 265     $this->_from = "
 266         FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 267               INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 268                          ON {$this->_aliases['civicrm_contact']}.id =
 269                             {$this->_aliases['civicrm_membership']}.contact_id AND {$this->_aliases['civicrm_membership']}.is_test = 0
 270               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 271                          ON {$this->_aliases['civicrm_membership_status']}.id =
 272                             {$this->_aliases['civicrm_membership']}.status_id
 273               LEFT  JOIN civicrm_membership_type {$this->_aliases['civicrm_membership_type']}
 274                          ON {$this->_aliases['civicrm_membership']}.membership_type_id =
 275                             {$this->_aliases['civicrm_membership_type']}.id";
 276
 277     //  include address field if address column is to be included
 278     if ($this->_addressField) {
 279       $this->_from .= "
 280             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 281                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 282     }
 283
 284     // include email field if email column is to be included
 285     if ($this->_emailField) {
 286       $this->_from .= "
 287             LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 288                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 289     }
 290
 291     // include phone field if phone column is to be included
 292     if ($this->_phoneField) {
 293       $this->_from .= "
 294             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 295                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id
 296                      AND {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 297     }
 298   }
 299
 300   function where() {
 301     $clauses = array();
 302     foreach ($this->_columns as $tableName => $table) {
 303       if (array_key_exists('filters', $table)) {
 304         foreach ($table['filters'] as $fieldName => $field) {
 305           $clause = NULL;
 306
 307           if ($field['operatorType'] & CRM_Utils_Type::T_DATE) {
 308             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 309             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 310             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 311
 312             if ($relative || $from || $to) {
 313               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 314             }
 315           }
 316           else {
 317             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 318             if ($op) {
 319               $clause = $this->whereClause($field,
 320                 $op,
 321                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 322                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 323                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 324               );
 325             }
 326           }
 327           if (!empty($clause)) {
 328             $clauses[$fieldName] = $clause;
 329           }
 330         }
 331       }
 332     }
 333
 334     if (empty($clauses)) {
 335       $this->_where = "WHERE end_date < '" . date('Y-m-d') . "' AND {$this->_aliases['civicrm_membership_status']}.name = 'Expired'";
 336     }
 337     else {
 338       if (!array_key_exists('end_date', $clauses)) {
 339         $this->_where = "WHERE end_date < '" . date('Y-m-d') . "' AND " . implode(' AND ', $clauses);
 340       }
 341       else {
 342         $this->_where = "WHERE " . implode(' AND ', $clauses);
 343       }
 344     }
 345
 346     if ($this->_aclWhere) {
 347       $this->_where .= " AND {$this->_aclWhere} ";
 348     }
 349   }
 350
 351   function orderBy() {
 352     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_membership']}.membership_type_id";
 353   }
 354
 355   function postProcess() {
 356     $this->beginPostProcess();
 357
 358     // get the acl clauses built before we assemble the query
 359     $this->buildACLClause($this->_aliases['civicrm_contact']);
 360     $sql = $this->buildQuery(TRUE);
 361
 362     $dao   = CRM_Core_DAO::executeQuery($sql);
 363     $rows  = $graphRows = array();
 364     $count = 0;
 365     while ($dao->fetch()) {
 366       $row = array();
 367       foreach ($this->_columnHeaders as $key => $value) {
 368         $row[$key] = $dao->$key;
 369       }
 370
 371       $rows[] = $row;
 372     }
 373     $this->formatDisplay($rows);
 374
 375     // assign variables to templates
 376     $this->doTemplateAssignment($rows);
 377
 378     $this->endPostProcess($rows);
 379   }
 380
 381   /**
 382    * @param $rows
 383    */
 384   function alterDisplay(&$rows) {
 385     // custom code to alter rows
 386     $entryFound = FALSE;
 387     $checkList = array();
 388
 389     foreach ($rows as $rowNum => $row) {
 390
 391       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 392         // not repeat contact display names if it matches with the one
 393         // in previous row
 394
 395         $repeatFound = FALSE;
 396         foreach ($row as $colName => $colVal) {
 397           if (!empty($checkList[$colName]) &&
 398             is_array($checkList[$colName]) &&
 399             in_array($colVal, $checkList[$colName])
 400           ) {
 401             $rows[$rowNum][$colName] = "";
 402             $repeatFound = TRUE;
 403           }
 404           if (in_array($colName, $this->_noRepeats)) {
 405             $checkList[$colName][] = $colVal;
 406           }
 407         }
 408       }
 409
 410       //handle the Membership Type Ids
 411       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 412         if ($value = $row['civicrm_membership_membership_type_id']) {
 413           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 414         }
 415         $entryFound = TRUE;
 416       }
 417
 418       // handle state province
 419       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 420         if ($value = $row['civicrm_address_state_province_id']) {
 421           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 422         }
 423         $entryFound = TRUE;
 424       }
 425
 426       // handle country
 427       if (array_key_exists('civicrm_address_country_id', $row)) {
 428         if ($value = $row['civicrm_address_country_id']) {
 429           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 430         }
 431         $entryFound = TRUE;
 432       }
 433
 434       // convert display name to links
 435       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 436         array_key_exists('civicrm_contact_id', $row)
 437       ) {
 438         $url = CRM_Report_Utils_Report::getNextUrl('member/detail',
 439           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 440           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 441         );
 442         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 443         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Membership Detail for this Contact.");
 444       }
 445
 446       // If using campaigns, convert campaign_id to campaign title
 447       if (array_key_exists('civicrm_membership_campaign_id', $row)) {
 448         if ($value = $row['civicrm_membership_campaign_id']) {
 449           $rows[$rowNum]['civicrm_membership_campaign_id'] = $this->activeCampaigns[$value];
 450         }
 451         $entryFound = TRUE;
 452       }
 453
 454       // skip looking further in rows, if first row itself doesn't
 455       // have the column we need
 456       if (!$entryFound) {
 457         break;
 458       }
 459     }
 460   }
 461 }
 462