CRM/Report/Form/Member/Summary.php

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