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