CRM/Report/Form/Member/Summary.php

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