CRM/Report/Form/Member/Summary.php

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