CRM/Report/Form/Member/Summary.php

   1 <?php
   2 // $Id$
   3
   4 /*
   5  +--------------------------------------------------------------------+
   6  | CiviCRM version 4.3                                                |
   7  +--------------------------------------------------------------------+
   8  | Copyright CiviCRM LLC (c) 2004-2013                                |
   9  +--------------------------------------------------------------------+
  10  | This file is a part of CiviCRM.                                    |
  11  |                                                                    |
  12  | CiviCRM is free software; you can copy, modify, and distribute it  |
  13  | under the terms of the GNU Affero General Public License           |
  14  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  15  |                                                                    |
  16  | CiviCRM is distributed in the hope that it will be useful, but     |
  17  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  18  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  19  | See the GNU Affero General Public License for more details.        |
  20  |                                                                    |
  21  | You should have received a copy of the GNU Affero General Public   |
  22  | License and the CiviCRM Licensing Exception along                  |
  23  | with this program; if not, contact CiviCRM LLC                     |
  24  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  25  | GNU Affero General Public License or the licensing of CiviCRM,     |
  26  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  27  +--------------------------------------------------------------------+
  28 */
  29
  30 /**
  31  *
  32  * @package CRM
  33  * @copyright CiviCRM LLC (c) 2004-2013
  34  * $Id$
  35  *
  36  */
  37 class CRM_Report_Form_Member_Summary extends CRM_Report_Form {
  38
  39   protected $_summary = NULL;
  40   protected $_interval = NULL;
  41   protected $_charts = array(
  42     '' => 'Tabular',
  43     'barChart' => 'Bar Chart',
  44     'pieChart' => 'Pie Chart',
  45   );
  46   protected $_add2groupSupported = FALSE;
  47
  48   protected $_customGroupExtends = array('Membership');
  49   protected $_customGroupGroupBy = FALSE;
  50   public $_drilldownReport = array('member/detail' => 'Link to Detail Report');
  51
  52   function __construct() {
  53     // UI for selecting columns to appear in the report list
  54     // array conatining the columns, group_bys and filters build and provided to Form
  55
  56     $this->_columns = array(
  57       'civicrm_membership' =>
  58       array(
  59         'dao' => 'CRM_Member_DAO_MembershipType',
  60         'grouping' => 'member-fields',
  61         'fields' =>
  62         array(
  63           'membership_type_id' =>
  64           array(
  65             'title' => 'Membership Type',
  66             'required' => TRUE,
  67           ),
  68         ),
  69         'filters' =>
  70         array(
  71           'join_date' =>
  72           array('title' => ts('Member Since'),
  73             'type' => CRM_Utils_Type::T_DATE,
  74             'operatorType' => CRM_Report_Form::OP_DATE,
  75           ),
  76           'membership_start_date' =>
  77           array(
  78             'name' => 'start_date',
  79             'title' => ts('Membership Start Date'),
  80             'type' => CRM_Utils_Type::T_DATE,
  81             'operatorType' => CRM_Report_Form::OP_DATE,
  82           ),
  83           'membership_end_date' =>
  84           array(
  85             'name' => 'end_date',
  86             'title' => ts('Membership End Date'),
  87             'type' => CRM_Utils_Type::T_DATE,
  88             'operatorType' => CRM_Report_Form::OP_DATE,
  89           ),
  90           'membership_type_id' =>
  91           array('title' => ts('Membership Type'),
  92             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  93             'options' => CRM_Member_PseudoConstant::membershipType(),
  94           ),
  95           'status_id' =>
  96           array('title' => ts('Membership Status'),
  97             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  98             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
  99           ),
 100         ),
 101         'group_bys' =>
 102         array(
 103           'join_date' =>
 104           array('title' => ts('Member Since'),
 105             'default' => TRUE,
 106             'frequency' => TRUE,
 107             'chart' => TRUE,
 108             'type' => 12,
 109           ),
 110           'membership_type_id' =>
 111           array(
 112             'title' => 'Membership Type',
 113             'default' => TRUE,
 114             'chart' => TRUE,
 115           ),
 116         ),
 117       ),
 118       'civicrm_contact' =>
 119       array(
 120         'dao' => 'CRM_Contact_DAO_Contact',
 121         'fields' =>
 122         array(
 123           'contact_id' =>
 124           array(
 125             'no_display' => TRUE,
 126           ),
 127         ),
 128       ),
 129       'civicrm_contribution' =>
 130       array(
 131         'dao' => 'CRM_Contribute_DAO_Contribution',
 132         'fields' =>
 133         array(
 134           'currency' =>
 135           array('required' => TRUE,
 136             'no_display' => TRUE,
 137           ),
 138           'total_amount' =>
 139           array('title' => ts('Amount Statistics'),
 140             'required' => TRUE,
 141             'statistics' =>
 142             array('sum' => ts('Total Payments Made'),
 143               'count' => ts('Contribution Count'),
 144               'avg' => ts('Average'),
 145             ),
 146           ),
 147         ),
 148         'filters' =>
 149         array(
 150           'currency' =>
 151           array('title' => 'Currency',
 152             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 153             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 154             'type' => CRM_Utils_Type::T_STRING,
 155           ),
 156           'contribution_status_id' =>
 157           array('title' => ts('Contribution Status'),
 158             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 159             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 160           ),
 161         ),
 162         'grouping' => 'member-fields',
 163       ),
 164     );
 165     $this->_tagFilter = TRUE;
 166     $this->_groupFilter = TRUE;
 167     $this->_currencyColumn = 'civicrm_contribution_currency';
 168     parent::__construct();
 169   }
 170
 171   function select() {
 172     $select = array();
 173     $groupBys = FALSE;
 174     $this->_columnHeaders = array();
 175     $select[] = " COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as civicrm_membership_member_count";
 176     $select['joinDate'] = " {$this->_aliases['civicrm_membership']}.join_date  as civicrm_membership_member_join_date";
 177     $this->_columnHeaders["civicrm_membership_member_join_date"] = array('title' => ts('Member Since'),
 178       'type' => CRM_Utils_Type::T_DATE,
 179     );
 180     foreach ($this->_columns as $tableName => $table) {
 181       if (array_key_exists('group_bys', $table)) {
 182         foreach ($table['group_bys'] as $fieldName => $field) {
 183           if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
 184
 185             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 186               case 'YEARWEEK':
 187                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 188
 189                 $select[]       = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 190                 $select[]       = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 191                 $field['title'] = 'Week';
 192                 break;
 193
 194               case 'YEAR':
 195                 $select[]       = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 196                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 197                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 198                 $field['title'] = 'Year';
 199                 break;
 200
 201               case 'MONTH':
 202                 $select[]       = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 203                 $select[]       = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 204                 $select[]       = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 205                 $field['title'] = 'Month';
 206                 break;
 207
 208               case 'QUARTER':
 209                 $select[]       = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 210                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 211                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 212                 $field['title'] = 'Quarter';
 213                 break;
 214             }
 215             if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 216               $this->_interval = $field['title'];
 217               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 218               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 219               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 220
 221               // just to make sure these values are transfered to rows.
 222               // since we need that for calculation purpose,
 223               // e.g making subtotals look nicer or graphs
 224               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 225               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 226             }
 227             $groupBys = TRUE;
 228           }
 229         }
 230       }
 231       // end of select
 232
 233       if (array_key_exists('fields', $table)) {
 234         foreach ($table['fields'] as $fieldName => $field) {
 235           if (CRM_Utils_Array::value('required', $field) ||
 236             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 237           ) {
 238
 239             // only include statistics columns if set
 240             if (CRM_Utils_Array::value('statistics', $field)) {
 241               $this->_statFields[] = 'civicrm_membership_member_count';
 242               foreach ($field['statistics'] as $stat => $label) {
 243                 switch (strtolower($stat)) {
 244                   case 'sum':
 245                     $select[] = "IFNULL(SUM({$field['dbAlias']}), 0) as {$tableName}_{$fieldName}_{$stat}";
 246                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 247                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 248                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 249                     break;
 250
 251                   case 'count':
 252                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 253                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 254                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 255                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 256                     break;
 257
 258                   case 'avg':
 259                     $select[] = "IFNULL(ROUND(AVG({$field['dbAlias']}),2), 0) as {$tableName}_{$fieldName}_{$stat}";
 260                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 261                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 262                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 263                     break;
 264                 }
 265               }
 266             }
 267             elseif ($fieldName == 'membership_type_id') {
 268               if (!CRM_Utils_Array::value('membership_type_id', $this->_params['group_bys']) &&
 269                 CRM_Utils_Array::value('join_date', $this->_params['group_bys'])
 270               ) {
 271                 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']}  ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
 272               }
 273               else {
 274                 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 275               }
 276               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 277               $this->_columnHeaders["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array::value('operatorType', $field);
 278             }
 279             else {
 280               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 281               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 282               $this->_columnHeaders["{$tableName}_{$fieldName}"]['operatorType'] = CRM_Utils_Array::value('operatorType', $field);
 283             }
 284           }
 285         }
 286       }
 287       $this->_columnHeaders["civicrm_membership_member_count"] = array('title' => ts('Member Count'),
 288         'type' => CRM_Utils_Type::T_INT,
 289       );
 290     }
 291     //If grouping is availabled then remove join date from field
 292     if ($groupBys) {
 293       unset($select['joinDate']);
 294       unset($this->_columnHeaders["civicrm_membership_member_join_date"]);
 295     }
 296     $this->_select = "SELECT " . implode(', ', $select) . " ";
 297   }
 298
 299   function from() {
 300     $this->_from = "
 301         FROM  civicrm_membership {$this->_aliases['civicrm_membership']}
 302
 303               LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON ( {$this->_aliases['civicrm_membership']}.contact_id = {$this->_aliases['civicrm_contact']}.id )
 304
 305               LEFT JOIN civicrm_membership_status
 306                         ON ({$this->_aliases['civicrm_membership']}.status_id = civicrm_membership_status.id  )
 307               LEFT JOIN civicrm_membership_payment payment
 308                         ON ( {$this->_aliases['civicrm_membership']}.id = payment.membership_id )
 309               LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 310                          ON payment.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 311   }
 312   // end of from
 313
 314   function where() {
 315     $clauses = array();
 316     foreach ($this->_columns as $tableName => $table) {
 317       if (array_key_exists('filters', $table)) {
 318         foreach ($table['filters'] as $fieldName => $field) {
 319           $clause = NULL;
 320
 321           if ($field['operatorType'] & CRM_Utils_Type::T_DATE) {
 322             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 323             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 324             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 325
 326             if ($relative || $from || $to) {
 327               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 328             }
 329           }
 330           else {
 331             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 332             if ($op) {
 333               $clause = $this->whereClause($field,
 334                 $op,
 335                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 336                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 337                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 338               );
 339             }
 340           }
 341           if (!empty($clause)) {
 342             $clauses[$fieldName] = $clause;
 343           }
 344         }
 345       }
 346     }
 347
 348     if (!empty($clauses)) {
 349       $this->_where = "WHERE {$this->_aliases['civicrm_membership']}.is_test = 0 AND " . implode(' AND ', $clauses);
 350     }
 351     else {
 352       $this->_where = "WHERE {$this->_aliases['civicrm_membership']}.is_test = 0";
 353     }
 354   }
 355
 356   function groupBy() {
 357     $this->_groupBy = "";
 358     if (is_array($this->_params['group_bys']) &&
 359       !empty($this->_params['group_bys'])
 360     ) {
 361       foreach ($this->_columns as $tableName => $table) {
 362         if (array_key_exists('group_bys', $table)) {
 363           foreach ($table['group_bys'] as $fieldName => $field) {
 364             if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
 365               if (CRM_Utils_Array::value('chart', $field)) {
 366                 $this->assign('chartSupported', TRUE);
 367               }
 368               if (CRM_Utils_Array::value('frequency', $table['group_bys'][$fieldName]) &&
 369                 CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])
 370               ) {
 371
 372                 $append = "YEAR({$field['dbAlias']}),";
 373                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 374                     array('year')
 375                   )) {
 376                   $append = '';
 377                 }
 378                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 379                 $append = TRUE;
 380               }
 381               else {
 382                 $this->_groupBy[] = $field['dbAlias'];
 383               }
 384             }
 385           }
 386         }
 387       }
 388
 389       $this->_rollup = ' WITH ROLLUP';
 390       $this->_groupBy = 'GROUP BY ' . implode(', ', $this->_groupBy) . " {$this->_rollup} ";
 391     }
 392     else {
 393       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_membership']}.join_date";
 394     }
 395   }
 396
 397   function statistics(&$rows) {
 398     $statistics = parent::statistics($rows);
 399     $select = "
 400         SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
 401                IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,
 402                IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,
 403                COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount,
 404                {$this->_aliases['civicrm_contribution']}.currency as currency
 405         ";
 406
 407     $sql = "{$select} {$this->_from} {$this->_where}
 408 GROUP BY    {$this->_aliases['civicrm_contribution']}.currency
 409 ";
 410
 411     $dao = CRM_Core_DAO::executeQuery($sql);
 412
 413     $totalAmount = $average = array();
 414     $count = $memberCount = 0;
 415     while ($dao->fetch()) {
 416       $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency)."(".$dao->count.")";
 417       $average[] =   CRM_Utils_Money::format($dao->avg, $dao->currency);
 418       $count += $dao->count;
 419       $memberCount += $dao->memberCount;
 420     }
 421     $statistics['counts']['amount'] = array(
 422       'title' => ts('Total Amount'),
 423       'value' => implode(',  ', $totalAmount),
 424       'type' => CRM_Utils_Type::T_STRING,
 425     );
 426     $statistics['counts']['count'] = array(
 427       'title' => ts('Total Donations'),
 428       'value' => $count,
 429     );
 430     $statistics['counts']['memberCount'] = array(
 431       'title' => ts('Total Members'),
 432       'value' => $memberCount,
 433     );
 434     $statistics['counts']['avg'] = array(
 435       'title' => ts('Average'),
 436       'value' => implode(',  ', $average),
 437       'type' => CRM_Utils_Type::T_STRING,
 438     );
 439
 440     if (!(int)$statistics['counts']['amount']['value']) {
 441       //if total amount is zero then hide Chart Options
 442       $this->assign('chartSupported', FALSE);
 443     }
 444
 445     return $statistics;
 446   }
 447
 448   function postProcess() {
 449     parent::postProcess();
 450   }
 451
 452   function buildChart(&$rows) {
 453     $graphRows = array();
 454     $count = 0;
 455     $membershipTypeValues = CRM_Member_PseudoConstant::membershipType();
 456     $isMembershipType = CRM_Utils_Array::value('membership_type_id', $this->_params['group_bys']);
 457     $isJoiningDate = CRM_Utils_Array::value('join_date', $this->_params['group_bys']);
 458     if (CRM_Utils_Array::value('charts', $this->_params)) {
 459       foreach ($rows as $key => $row) {
 460         if (!($row['civicrm_membership_join_date_subtotal'] &&
 461             $row['civicrm_membership_membership_type_id']
 462           )) {
 463           continue;
 464         }
 465         if ($isMembershipType) {
 466           $join_date = CRM_Utils_Array::value('civicrm_membership_join_date_start', $row);
 467           $displayInterval = CRM_Utils_Array::value('civicrm_membership_join_date_interval', $row);
 468           if ($join_date) {
 469             list($year, $month) = explode('-', $join_date);
 470           }
 471           if (CRM_Utils_Array::value('civicrm_membership_join_date_subtotal', $row)) {
 472
 473             switch ($this->_interval) {
 474               case 'Month':
 475                 $displayRange = $displayInterval . ' ' . $year;
 476                 break;
 477
 478               case 'Quarter':
 479                 $displayRange = 'Quarter ' . $displayInterval . ' of ' . $year;
 480                 break;
 481
 482               case 'Week':
 483                 $displayRange = 'Week ' . $displayInterval . ' of ' . $year;
 484                 break;
 485
 486               case 'Year':
 487                 $displayRange = $year;
 488                 break;
 489             }
 490             $membershipType = $displayRange . "-" . $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
 491           }
 492           else {
 493
 494             $membershipType = $membershipTypeValues[$row['civicrm_membership_membership_type_id']];
 495           }
 496
 497           $interval[$membershipType] = $membershipType;
 498           $display[$membershipType] = $row['civicrm_contribution_total_amount_sum'];
 499         }
 500         else {
 501           $graphRows['receive_date'][] = CRM_Utils_Array::value('civicrm_membership_join_date_start', $row);
 502           $graphRows[$this->_interval][] = CRM_Utils_Array::value('civicrm_membership_join_date_interval', $row);
 503           $graphRows['value'][] = $row['civicrm_contribution_total_amount_sum'];
 504           $count++;
 505         }
 506       }
 507
 508       // build chart.
 509       if ($isMembershipType) {
 510         $graphRows['value'] = $display;
 511         $chartInfo = array(
 512           'legend' => 'Membership Summary',
 513           'xname' => 'Member Since / Member Type',
 514           'yname' => 'Fees',
 515         );
 516         CRM_Utils_OpenFlashChart::reportChart($graphRows, $this->_params['charts'], $interval, $chartInfo);
 517       }
 518       else {
 519         CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval);
 520       }
 521     }
 522     $this->assign('chartType', $this->_params['charts']);
 523   }
 524
 525   function alterDisplay(&$rows) {
 526     // custom code to alter rows
 527     $entryFound = FALSE;
 528     foreach ($rows as $rowNum => $row) {
 529       // make count columns point to detail report
 530       if (CRM_Utils_Array::value('join_date', $this->_params['group_bys']) &&
 531         CRM_Utils_Array::value('civicrm_membership_join_date_start', $row) &&
 532         $row['civicrm_membership_join_date_start'] &&
 533         $row['civicrm_membership_join_date_subtotal']
 534       ) {
 535
 536         $dateStart = CRM_Utils_Date::customFormat($row['civicrm_membership_join_date_start'], '%Y%m%d');
 537         $endDate   = new DateTime($dateStart);
 538         $dateEnd   = array();
 539
 540         list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
 541
 542         switch (strtolower($this->_params['group_bys_freq']['join_date'])) {
 543           case 'month':
 544             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 1,
 545                 $dateEnd['d'] - 1, $dateEnd['Y']
 546               ));
 547             break;
 548
 549           case 'year':
 550             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 551                 $dateEnd['d'] - 1, $dateEnd['Y'] + 1
 552               ));
 553             break;
 554
 555           case 'yearweek':
 556             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 557                 $dateEnd['d'] + 6, $dateEnd['Y']
 558               ));
 559             break;
 560
 561           case 'quarter':
 562             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 3,
 563                 $dateEnd['d'] - 1, $dateEnd['Y']
 564               ));
 565             break;
 566         }
 567         $typeUrl = '';
 568         if (CRM_Utils_Array::value('membership_type_id', $this->_params['group_bys']) &&
 569           $typeID = $row['civicrm_membership_membership_type_id']
 570         ) {
 571           $typeUrl = "&tid_op=in&tid_value={$typeID}";
 572         }
 573         $statusUrl = '';
 574         if (!empty($this->_params['status_id_value'])) {
 575           $statusUrl = "&sid_op=in&sid_value=" . implode(",", $this->_params['status_id_value']);
 576         }
 577         $url = CRM_Report_Utils_Report::getNextUrl('member/detail',
 578           "reset=1&force=1&join_date_from={$dateStart}&join_date_to={$dateEnd}{$typeUrl}{$statusUrl}",
 579           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 580         );
 581         $row['civicrm_membership_join_date_start'] = CRM_Utils_Date::format($row['civicrm_membership_join_date_start']);
 582         $rows[$rowNum]['civicrm_membership_join_date_start_link'] = $url;
 583         $rows[$rowNum]['civicrm_membership_join_date_start_hover'] = ts("Lists Summary of Memberships for this date unit.");
 584
 585         $entryFound = TRUE;
 586       }
 587
 588       // handle Membership Types
 589       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 590         if ($value = $row['civicrm_membership_membership_type_id']) {
 591           $value = explode(',', $value);
 592           foreach ($value as $key => $id) {
 593             $value[$key] = CRM_Member_PseudoConstant::membershipType($id, FALSE);
 594           }
 595           $rows[$rowNum]['civicrm_membership_membership_type_id'] = implode(' , ', $value);
 596         }
 597         $entryFound = TRUE;
 598       }
 599
 600       // make subtotals look nicer
 601       if (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
 602         !$row['civicrm_membership_join_date_subtotal']
 603       ) {
 604         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 605         $entryFound = TRUE;
 606       }
 607       elseif (array_key_exists('civicrm_membership_join_date_subtotal', $row) &&
 608         $row['civicrm_membership_join_date_subtotal'] &&
 609         !$row['civicrm_membership_membership_type_id']
 610       ) {
 611         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields, FALSE);
 612         $rows[$rowNum]['civicrm_membership_membership_type_id'] = '<b>SubTotal</b>';
 613         $entryFound = TRUE;
 614       }
 615
 616       // skip looking further in rows, if first row itself doesn't
 617       // have the column we need
 618       if (!$entryFound) {
 619         break;
 620       }
 621     }
 622   }
 623 }
 624