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