CRM/Report/Form/Member/Lapse.php

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