crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Membership/Summary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  */
  34 class CRM_Report_Form_Membership_Summary extends CRM_Report_Form {
  35
  36   protected $_summary = NULL;
  37
  38   protected $_charts = array(
  39     '' => 'Tabular',
  40     'barChart' => 'Bar Chart',
  41     'pieChart' => 'Pie Chart',
  42   );
  43
  44   /**
  45    * Constructor function.
  46    */
  47   public function __construct() {
  48     // UI for selecting columns to appear in the report list
  49     // array containing the columns, group_bys and filters build and provided to Form
  50     $this->_columns = array(
  51       'civicrm_contact' => array(
  52         'dao' => 'CRM_Contact_DAO_Contact',
  53         'fields' => array(
  54           'sort_name' => array(
  55             'title' => ts('Member Name'),
  56             'no_repeat' => TRUE,
  57             'required' => TRUE,
  58           ),
  59           'id' => array(
  60             'no_display' => TRUE,
  61             'required' => TRUE,
  62           ),
  63         ),
  64         'group_bys' => array(
  65           'id' => array('title' => ts('Contact ID')),
  66           'display_name' => array(
  67             'title' => ts('Contact Name'),
  68           ),
  69         ),
  70         'grouping' => 'contact-fields',
  71       ),
  72       'civicrm_membership_type' => array(
  73         'dao' => 'CRM_Member_DAO_MembershipType',
  74         'grouping' => 'member-fields',
  75         'filters' => array(
  76           'gid' => array(
  77             'name' => 'id',
  78             'title' => ts('Membership Types'),
  79             'type' => CRM_Utils_Type::T_INT + CRM_Utils_Type::T_ENUM,
  80             'options' => CRM_Member_PseudoConstant::membershipType(),
  81           ),
  82         ),
  83       ),
  84       'civicrm_membership' => array(
  85         'dao' => 'CRM_Member_DAO_Membership',
  86         'grouping' => 'member-fields',
  87         'fields' => array(
  88           'membership_type_id' => array(
  89             'title' => 'Membership Type',
  90             'required' => TRUE,
  91           ),
  92           'join_date' => NULL,
  93           'start_date' => array(
  94             'title' => ts('Current Cycle Start Date'),
  95           ),
  96           'end_date' => array(
  97             'title' => ts('Current Cycle End Date'),
  98           ),
  99         ),
 100         'group_bys' => array(
 101           'membership_type_id' => array('title' => ts('Membership Type')),
 102         ),
 103         'filters' => array(
 104           'join_date' => array('type' => CRM_Utils_Type::T_DATE),
 105         ),
 106       ),
 107       'civicrm_address' => array(
 108         'dao' => 'CRM_Core_DAO_Address',
 109         'fields' => array(
 110           'street_address' => NULL,
 111           'city' => NULL,
 112           'postal_code' => NULL,
 113           'state_province_id' => array(
 114             'title' => ts('State/Province'),
 115           ),
 116           'country_id' => array(
 117             'title' => ts('Country'),
 118             'default' => TRUE,
 119           ),
 120         ),
 121         'grouping' => 'contact-fields',
 122       ),
 123       'civicrm_email' => array(
 124         'dao' => 'CRM_Core_DAO_Email',
 125         'fields' => array('email' => NULL),
 126         'grouping' => 'contact-fields',
 127       ),
 128       'civicrm_contribution' => array(
 129         'dao' => 'CRM_Contribute_DAO_Contribution',
 130         'filters' => array(
 131           'total_amount' => array(
 132             'title' => ts('Contribution Amount'),
 133           ),
 134         ),
 135       ),
 136     );
 137     parent::__construct();
 138   }
 139
 140   /**
 141    * Pre-process function.
 142    */
 143   public function preProcess() {
 144     $this->assign('reportTitle', ts('Membership Summary Report'));
 145     parent::preProcess();
 146   }
 147
 148   /**
 149    * Set default values.
 150    *
 151    * @return array
 152    *   Default values.
 153    */
 154   public function setDefaultValues() {
 155     return parent::setDefaultValues();
 156   }
 157
 158   /**
 159    * Generate select clause.
 160    */
 161   public function select() {
 162     $select = array();
 163     $this->_columnHeaders = array();
 164     foreach ($this->_columns as $tableName => $table) {
 165       if (array_key_exists('fields', $table)) {
 166         foreach ($table['fields'] as $fieldName => $field) {
 167           if (!empty($field['required']) ||
 168             !empty($this->_params['fields'][$fieldName])
 169           ) {
 170             // to include optional columns address and email, only if checked
 171             if ($tableName == 'civicrm_address') {
 172               $this->_addressField = TRUE;
 173               $this->_emailField = TRUE;
 174             }
 175             elseif ($tableName == 'civicrm_email') {
 176               $this->_emailField = TRUE;
 177             }
 178             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 179             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'];
 180             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 181           }
 182         }
 183       }
 184     }
 185     $this->_select = "SELECT " . implode(', ', $select) . " ";
 186   }
 187
 188   /**
 189    * Set form rules.
 190    *
 191    * @param $fields
 192    * @param $files
 193    * @param $self
 194    *
 195    * @return array
 196    */
 197   public static function formRule($fields, $files, $self) {
 198     $errors = $grouping = array();
 199     //check for searching combination of dispaly columns and
 200     //grouping criteria
 201
 202     return $errors;
 203   }
 204
 205   public function from() {
 206     $this->_from = NULL;
 207
 208     $this->_from = "
 209 FROM       civicrm_contact    {$this->_aliases['civicrm_contact']}
 210 INNER JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 211        ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_membership']}.contact_id
 212 LEFT  JOIN civicrm_membership_type  {$this->_aliases['civicrm_membership_type']}
 213        ON {$this->_aliases['civicrm_membership']}.membership_type_id = {$this->_aliases['civicrm_membership_type']}.id
 214 LEFT  JOIN civicrm_contribution  {$this->_aliases['civicrm_contribution']}
 215        ON {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contribution']}.contact_id
 216 ";
 217     // Include address table if address column is to be included.
 218     if ($this->_addressField) {
 219       $this->_from .= "LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 220     }
 221
 222     // Include email table if email column is to be included.
 223     if ($this->_emailField) {
 224       $this->_from .= "LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 225     }
 226   }
 227
 228   public function where() {
 229     $clauses = array();
 230     foreach ($this->_columns as $tableName => $table) {
 231       if (array_key_exists('filters', $table)) {
 232         foreach ($table['filters'] as $fieldName => $field) {
 233           $clause = NULL;
 234           if ($field['type'] & CRM_Utils_Type::T_DATE) {
 235             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 236             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 237             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 238
 239             if ($relative || $from || $to) {
 240               $clause = $this->dateClause($field['name'], $relative, $from, $to);
 241             }
 242           }
 243           else {
 244             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 245             if ($op) {
 246               $clause = $this->whereClause($field,
 247                 $op,
 248                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 249                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 250                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 251               );
 252             }
 253           }
 254
 255           if (!empty($clause)) {
 256             $clauses[] = $clause;
 257           }
 258         }
 259       }
 260     }
 261
 262     if (empty($clauses)) {
 263       $this->_where = "WHERE ( 1 ) ";
 264     }
 265     else {
 266       $this->_where = "WHERE " . implode(' AND ', $clauses);
 267     }
 268   }
 269
 270   /**
 271    * Generate statistics (bottom section of the report).
 272    *
 273    * @param array $rows
 274    *
 275    * @return array
 276    */
 277   public function statistics(&$rows) {
 278     $statistics = array();
 279     $statistics[] = array(
 280       'title' => ts('Row(s) Listed'),
 281       'value' => count($rows),
 282     );
 283     return $statistics;
 284   }
 285
 286   public function groupBy() {
 287     $this->_groupBy = "";
 288     if (is_array($this->_params['group_bys']) &&
 289       !empty($this->_params['group_bys'])
 290     ) {
 291       foreach ($this->_columns as $tableName => $table) {
 292         if (array_key_exists('group_bys', $table)) {
 293           foreach ($table['group_bys'] as $fieldName => $field) {
 294             if (!empty($this->_params['group_bys'][$fieldName])) {
 295               $this->_groupBy[] = $field['dbAlias'];
 296             }
 297           }
 298         }
 299       }
 300
 301       if (!empty($this->_statFields) &&
 302         (($append && count($this->_groupBy) <= 1) || (!$append))
 303       ) {
 304         $this->_rollup = " WITH ROLLUP";
 305       }
 306       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) .
 307         " {$this->_rollup} ";
 308     }
 309     else {
 310       $this->_groupBy = "GROUP BY contact.id";
 311     }
 312   }
 313
 314   public function postProcess() {
 315     $this->_params = $this->controller->exportValues($this->_name);
 316     if (empty($this->_params) &&
 317       $this->_force
 318     ) {
 319       $this->_params = $this->_formValues;
 320     }
 321     $this->_formValues = $this->_params;
 322
 323     $this->processReportMode();
 324
 325     $this->select();
 326
 327     $this->from();
 328
 329     $this->where();
 330
 331     $this->groupBy();
 332
 333     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy}";
 334
 335     $dao = CRM_Core_DAO::executeQuery($sql);
 336     $rows = $graphRows = array();
 337     $count = 0;
 338     while ($dao->fetch()) {
 339       $row = array();
 340       foreach ($this->_columnHeaders as $key => $value) {
 341         $row[$key] = $dao->$key;
 342       }
 343
 344       if (!empty($this->_params['charts']) &&
 345         $row['civicrm_contribution_receive_date_subtotal']
 346       ) {
 347         $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
 348         $graphRows[$this->_interval][] = $row['civicrm_contribution_receive_date_interval'];
 349         $graphRows['value'][] = $row['civicrm_contribution_total_amount_sum'];
 350         $count++;
 351       }
 352
 353       $rows[] = $row;
 354     }
 355     $this->formatDisplay($rows);
 356
 357     $this->assign_by_ref('columnHeaders', $this->_columnHeaders);
 358     $this->assign_by_ref('rows', $rows);
 359     $this->assign('statistics', $this->statistics($rows));
 360
 361     if (!empty($this->_params['charts'])) {
 362       foreach (array(
 363                  'receive_date',
 364                  $this->_interval,
 365                  'value',
 366                ) as $ignore) {
 367         unset($graphRows[$ignore][$count - 1]);
 368       }
 369
 370       // build chart.
 371       CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval);
 372     }
 373     parent::endPostProcess();
 374   }
 375
 376   /**
 377    * Alter display of rows.
 378    *
 379    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 380    * such as rendering contacts as links.
 381    *
 382    * @param array $rows
 383    *   Rows generated by SQL, with an array for each row.
 384    */
 385   public function alterDisplay(&$rows) {
 386     $entryFound = FALSE;
 387     $checkList = array();
 388
 389     foreach ($rows as $rowNum => $row) {
 390
 391       if (!empty($this->_noRepeats)) {
 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 (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::stateProvinceAbbreviation($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_Utils_System::url(
 438           'civicrm/report/member/detail',
 439           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 440           $this->_absoluteUrl
 441         );
 442         $rows[$rowNum]['civicrm_contact_sort_name']
 443           = "<a href='$url'>" . $row["civicrm_contact_sort_name"] . '</a>';
 444         $entryFound = TRUE;
 445       }
 446
 447       // skip looking further in rows, if first row itself doesn't
 448       // have the column we need
 449       if (!$entryFound) {
 450         break;
 451       }
 452     }
 453   }
 454
 455 }