CRM/Report/Form/Member/Summary.php

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