CRM/Report/Form/Contribute/HouseholdSummary.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_Contribute_HouseholdSummary extends CRM_Report_Form {
  34
  35   public $_drilldownReport = ['contribute/detail' => 'Link to Detail Report'];
  36
  37   protected $_summary = NULL;
  38
  39   /**
  40    * Class constructor.
  41    */
  42   public function __construct() {
  43     self::validRelationships();
  44
  45     $this->_columns = [
  46       'civicrm_contact_household' => [
  47         'dao' => 'CRM_Contact_DAO_Contact',
  48         'fields' => [
  49           'household_name' => [
  50             'title' => ts('Household Name'),
  51             'required' => TRUE,
  52           ],
  53           'id' => [
  54             'no_display' => TRUE,
  55             'required' => TRUE,
  56           ],
  57           'contact_type' => [
  58             'title' => ts('Contact Type'),
  59           ],
  60           'contact_sub_type' => [
  61             'title' => ts('Contact Subtype'),
  62           ],
  63         ],
  64         'filters' => [
  65           'household_name' => [
  66             'title' => ts('Household Name'),
  67           ],
  68           'is_deleted' => [
  69             'default' => 0,
  70             'type' => CRM_Utils_Type::T_BOOLEAN,
  71           ],
  72         ],
  73         'grouping' => 'household-fields',
  74       ],
  75       'civicrm_line_item' => [
  76         'dao' => 'CRM_Price_DAO_LineItem',
  77       ],
  78       'civicrm_relationship' => [
  79         'dao' => 'CRM_Contact_DAO_Relationship',
  80         'fields' => [
  81           'relationship_type_id' => [
  82             'title' => ts('Relationship Type'),
  83           ],
  84         ],
  85         'filters' => [
  86           'relationship_type_id' => [
  87             'title' => ts('Relationship Type'),
  88             'type' => CRM_Utils_Type::T_INT,
  89             'operatorType' => CRM_Report_Form::OP_SELECT,
  90             'options' => $this->relationTypes,
  91             'default' => key($this->relationTypes),
  92           ],
  93         ],
  94         'grouping' => 'household-fields',
  95       ],
  96       'civicrm_contact' => [
  97         'dao' => 'CRM_Contact_DAO_Contact',
  98         'fields' => [
  99           'sort_name' => [
 100             'title' => ts('Contact Name'),
 101             'required' => TRUE,
 102           ],
 103           'id' => [
 104             'no_display' => TRUE,
 105             'required' => TRUE,
 106           ],
 107         ],
 108         'grouping' => 'contact-fields',
 109       ],
 110       'civicrm_contribution' => [
 111         'dao' => 'CRM_Contribute_DAO_Contribution',
 112         'fields' => [
 113           'total_amount' => [
 114             'title' => ts('Amount'),
 115             'required' => TRUE,
 116           ],
 117           'id' => [
 118             'no_display' => TRUE,
 119             'required' => TRUE,
 120           ],
 121           'contribution_status_id' => [
 122             'title' => ts('Contribution Status'),
 123             'default' => TRUE,
 124           ],
 125           'check_number' => [
 126             'title' => ts('Check Number'),
 127           ],
 128           'currency' => [
 129             'required' => TRUE,
 130             'no_display' => TRUE,
 131           ],
 132           'financial_type_id' => [
 133             'title' => ts('Financial Type'),
 134           ],
 135           'trxn_id' => NULL,
 136           'receive_date' => ['default' => TRUE],
 137           'receipt_date' => NULL,
 138         ],
 139         'filters' => [
 140           'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
 141           'total_amount' => ['title' => ts('Amount Between')],
 142           'currency' => [
 143             'title' => ts('Currency'),
 144             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 145             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 146             'default' => NULL,
 147             'type' => CRM_Utils_Type::T_STRING,
 148           ],
 149           'contribution_status_id' => [
 150             'title' => ts('Contribution Status'),
 151             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 152             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 153             'default' => [1],
 154           ],
 155           'financial_type_id' => [
 156             'title' => ts('Financial Type'),
 157             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 158             'options' => CRM_Contribute_PseudoConstant::financialType(),
 159           ],
 160         ],
 161         'grouping' => 'contri-fields',
 162       ],
 163       'civicrm_financial_trxn' => [
 164         'dao' => 'CRM_Financial_DAO_FinancialTrxn',
 165         'fields' => [
 166           'card_type_id' => [
 167             'title' => ts('Credit Card Type'),
 168             'dbAlias' => 'GROUP_CONCAT(financial_trxn_civireport.card_type_id SEPARATOR ",")',
 169           ],
 170         ],
 171         'filters' => [
 172           'card_type_id' => [
 173             'title' => ts('Credit Card Type'),
 174             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 175             'options' => CRM_Financial_DAO_FinancialTrxn::buildOptions('card_type_id'),
 176             'default' => NULL,
 177             'type' => CRM_Utils_Type::T_STRING,
 178           ],
 179         ],
 180       ],
 181       'civicrm_address' => [
 182         'dao' => 'CRM_Core_DAO_Address',
 183         'fields' => [
 184           'street_address' => NULL,
 185           'city' => NULL,
 186           'postal_code' => NULL,
 187           'state_province_id' => [
 188             'title' => ts('State/Province'),
 189           ],
 190           'country_id' => [
 191             'title' => ts('Country'),
 192           ],
 193         ],
 194         'grouping' => 'contact-fields',
 195       ],
 196       'civicrm_email' => [
 197         'dao' => 'CRM_Core_DAO_Email',
 198         'fields' => [
 199           'email' => NULL,
 200         ],
 201         'grouping' => 'contact-fields',
 202       ],
 203     ];
 204
 205     // If we have a campaign, build out the relevant elements
 206     $this->addCampaignFields('civicrm_contribution');
 207
 208     $this->_currencyColumn = 'civicrm_contribution_currency';
 209     parent::__construct();
 210   }
 211
 212   public function select() {
 213     // @todo remove this & use parent select.
 214     $this->_columnHeaders = $select = [];
 215     foreach ($this->_columns as $tableName => $table) {
 216       if (array_key_exists('fields', $table)) {
 217         foreach ($table['fields'] as $fieldName => $field) {
 218           if (!empty($field['required']) ||
 219             !empty($this->_params['fields'][$fieldName])
 220           ) {
 221
 222             if (!empty($field['statistics'])) {
 223               foreach ($field['statistics'] as $stat => $label) {
 224                 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}_{$stat}";
 225                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 226                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 227                 $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 228               }
 229             }
 230             else {
 231               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 232
 233               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 234               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 235             }
 236           }
 237         }
 238       }
 239     }
 240     $this->_selectClauses = $select;
 241     $this->_select = "SELECT " . implode(', ', $select) . " ";
 242   }
 243
 244   public function from() {
 245     $this->_from = "
 246         FROM  civicrm_relationship {$this->_aliases['civicrm_relationship']}
 247             LEFT  JOIN civicrm_contact {$this->_aliases['civicrm_contact_household']} ON
 248                       ({$this->_aliases['civicrm_contact_household']}.id = {$this->_aliases['civicrm_relationship']}.$this->householdContact AND {$this->_aliases['civicrm_contact_household']}.contact_type='Household')
 249             LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']} ON
 250                       ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_relationship']}.$this->otherContact )
 251             {$this->_aclFrom}
 252             INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON
 253                       ({$this->_aliases['civicrm_contribution']}.contact_id = {$this->_aliases['civicrm_relationship']}.$this->otherContact ) AND {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
 254
 255     $this->joinAddressFromContact();
 256     $this->joinEmailFromContact();
 257
 258     // for credit card type
 259     $this->addFinancialTrxnFromClause();
 260   }
 261
 262   public function where() {
 263     $clauses = [];
 264     foreach ($this->_columns as $tableName => $table) {
 265       if (array_key_exists('filters', $table)) {
 266         foreach ($table['filters'] as $fieldName => $field) {
 267           $clause = NULL;
 268           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 269             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 270             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 271             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 272             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 273           }
 274           else {
 275             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 276             if ($op) {
 277               if ($fieldName == 'relationship_type_id') {
 278                 $clause = "{$this->_aliases['civicrm_relationship']}.relationship_type_id=" . $this->relationshipId;
 279               }
 280               else {
 281                 $clause = $this->whereClause($field,
 282                   $op,
 283                   CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 284                   CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 285                   CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 286                 );
 287               }
 288             }
 289           }
 290
 291           if (!empty($clause)) {
 292             $clauses[] = $clause;
 293           }
 294         }
 295       }
 296     }
 297
 298     if (empty($clauses)) {
 299       $this->_where = "WHERE ( 1 )";
 300     }
 301     else {
 302       $this->_where = "WHERE " . implode(' AND ', $clauses);
 303     }
 304
 305     if ($this->_aclWhere) {
 306       $this->_where .= " AND {$this->_aclWhere} ";
 307     }
 308   }
 309
 310   public function groupBy() {
 311     $groupBy = [
 312       "{$this->_aliases['civicrm_relationship']}.$this->householdContact",
 313       "{$this->_aliases['civicrm_relationship']}.$this->otherContact",
 314       "{$this->_aliases['civicrm_contribution']}.id",
 315       "{$this->_aliases['civicrm_relationship']}.relationship_type_id",
 316     ];
 317     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 318   }
 319
 320   public function orderBy() {
 321     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact_household']}.household_name, {$this->_aliases['civicrm_relationship']}.$this->householdContact, {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_relationship']}.$this->otherContact";
 322   }
 323
 324   /**
 325    * @param $rows
 326    *
 327    * @return array
 328    */
 329   public function statistics(&$rows) {
 330     $statistics = parent::statistics($rows);
 331
 332     //hack filter display for relationship type
 333     $type = substr($this->_params['relationship_type_id_value'], -3);
 334     foreach ($statistics['filters'] as $id => $value) {
 335       if (
 336         $value['title'] == 'Relationship Type' &&
 337         isset($this->relationTypes[$this->relationshipId . '_' . $type])
 338       ) {
 339         $statistics['filters'][$id]['value'] = 'Is equal to ' .
 340           $this->relationTypes[$this->relationshipId . '_' . $type];
 341       }
 342     }
 343     return $statistics;
 344   }
 345
 346   public function postProcess() {
 347
 348     $this->beginPostProcess();
 349     $this->buildACLClause([
 350       $this->_aliases['civicrm_contact'],
 351       $this->_aliases['civicrm_contact_household'],
 352     ]);
 353     $sql = $this->buildQuery(TRUE);
 354     $rows = [];
 355
 356     $this->buildRows($sql, $rows);
 357     $this->formatDisplay($rows);
 358     $this->doTemplateAssignment($rows);
 359     $this->endPostProcess($rows);
 360   }
 361
 362   /**
 363    * Set variables to be accessed by API and form layer in processing.
 364    */
 365   public function beginPostProcessCommon() {
 366     $getRelationship = $this->_params['relationship_type_id_value'];
 367     $type = substr($getRelationship, -3);
 368     $this->relationshipId = intval((substr($getRelationship, 0, strpos($getRelationship, '_'))));
 369     if ($type == 'b_a') {
 370       $this->householdContact = 'contact_id_b';
 371       $this->otherContact = 'contact_id_a';
 372     }
 373     else {
 374       $this->householdContact = 'contact_id_a';
 375       $this->otherContact = 'contact_id_b';
 376     }
 377   }
 378
 379   public function validRelationships() {
 380     $this->relationTypes = $relationTypes = [];
 381
 382     $params = ['contact_type_b' => 'Household', 'version' => 3];
 383     $typesA = civicrm_api('relationship_type', 'get', $params);
 384     if (empty($typesA['is_error'])) {
 385       foreach ($typesA['values'] as $rel) {
 386         $relationTypes[$rel['id']][$rel['id'] . '_b_a'] = $rel['label_b_a'];
 387       }
 388     }
 389     $params = ['contact_type_a' => 'Household', 'version' => 3];
 390     $typesB = civicrm_api('relationship_type', 'get', $params);
 391     if (empty($typesB['is_error'])) {
 392       foreach ($typesB['values'] as $rel) {
 393         $relationTypes[$rel['id']][$rel['id'] . '_a_b'] = $rel['label_a_b'];
 394         //$this->relationTypes[$rel['id'].'_a_b'] = $rel['label_a_b'];
 395       }
 396     }
 397
 398     ksort($relationTypes);
 399     foreach ($relationTypes as $relationship) {
 400       foreach ($relationship as $index => $label) {
 401         $this->relationTypes[$index] = $label;
 402       }
 403     }
 404   }
 405
 406   /**
 407    * Alter display of rows.
 408    *
 409    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 410    * such as rendering contacts as links.
 411    *
 412    * @param array $rows
 413    *   Rows generated by SQL, with an array for each row.
 414    */
 415   public function alterDisplay(&$rows) {
 416     $type = substr($this->_params['relationship_type_id_value'], -3);
 417
 418     $entryFound = FALSE;
 419     $flagHousehold = $flagContact = 0;
 420     foreach ($rows as $rowNum => $row) {
 421
 422       //replace retionship id by relationship name
 423       if (array_key_exists('civicrm_relationship_relationship_type_id', $row)) {
 424         if ($value = $row['civicrm_relationship_relationship_type_id']) {
 425           $rows[$rowNum]['civicrm_relationship_relationship_type_id'] = $this->relationTypes[$value . '_' . $type];
 426           $entryFound = TRUE;
 427         }
 428       }
 429
 430       //remove duplicate Organization names
 431       if (array_key_exists('civicrm_contact_household_household_name', $row) &&
 432         $this->_outputMode != 'csv'
 433       ) {
 434         if ($value = $row['civicrm_contact_household_household_name']) {
 435           if ($rowNum == 0) {
 436             $priviousHousehold = $value;
 437           }
 438           else {
 439             if ($priviousHousehold == $value) {
 440               $flagHousehold = 1;
 441               $priviousHousehold = $value;
 442             }
 443             else {
 444               $flagHousehold = 0;
 445               $priviousHousehold = $value;
 446             }
 447           }
 448
 449           if ($flagHousehold == 1) {
 450             $rows[$rowNum]['civicrm_contact_household_household_name'] = "";
 451           }
 452           else {
 453             $url = CRM_Utils_System::url('civicrm/contact/view',
 454               'reset=1&cid=' . $rows[$rowNum]['civicrm_contact_household_id'],
 455               $this->_absoluteUrl
 456             );
 457
 458             $rows[$rowNum]['civicrm_contact_household_household_name'] = "<a href='$url' title='" . ts('View contact summary for this househould') . "'>" . $value . '</a>';
 459           }
 460           $entryFound = TRUE;
 461         }
 462       }
 463
 464       //remove duplicate Contact names and relationship type
 465       if (array_key_exists('civicrm_contact_id', $row) &&
 466         $this->_outputMode != 'csv'
 467       ) {
 468         if ($value = $row['civicrm_contact_id']) {
 469           if ($rowNum == 0) {
 470             $priviousContact = $value;
 471           }
 472           else {
 473             if ($priviousContact == $value) {
 474               $flagContact = 1;
 475               $priviousContact = $value;
 476             }
 477             else {
 478               $flagContact = 0;
 479               $priviousContact = $value;
 480             }
 481           }
 482
 483           if ($flagContact == 1 && $flagHousehold == 1) {
 484             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 485             $rows[$rowNum]['civicrm_relationship_relationship_type_id'] = "";
 486           }
 487
 488           $entryFound = TRUE;
 489         }
 490       }
 491
 492       if (array_key_exists('civicrm_contribution_contribution_status_id', $row)) {
 493         if ($value = $row['civicrm_contribution_contribution_status_id']) {
 494           $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($value);
 495         }
 496       }
 497
 498       if (array_key_exists('civicrm_contribution_financial_type_id', $row)) {
 499         if ($value = $row['civicrm_contribution_financial_type_id']) {
 500           $rows[$rowNum]['civicrm_contribution_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($value);
 501         }
 502       }
 503
 504       // convert display name to links
 505       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 506         $rows[$rowNum]['civicrm_contact_sort_name'] &&
 507         array_key_exists('civicrm_contact_id', $row)
 508       ) {
 509         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 510           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 511           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 512         );
 513         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 514         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View contribution details for this individual');
 515
 516         $entryFound = TRUE;
 517       }
 518
 519       if (!empty($row['civicrm_contribution_total_amount'])) {
 520         $row['civicrm_contribution_total_amount'] = CRM_Utils_Money::format($row['civicrm_contribution_total_amount'], $row['civicrm_contribution_currency']);
 521       }
 522
 523       if (!empty($row['civicrm_financial_trxn_card_type_id'])) {
 524         $rows[$rowNum]['civicrm_financial_trxn_card_type_id'] = $this->getLabels($row['civicrm_financial_trxn_card_type_id'], 'CRM_Financial_DAO_FinancialTrxn', 'card_type_id');
 525         $entryFound = TRUE;
 526       }
 527
 528       // Contribution amount links to view contribution
 529       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount', $row)) &&
 530         CRM_Core_Permission::check('access CiviContribute')
 531       ) {
 532         $url = CRM_Utils_System::url("civicrm/contact/view/contribution",
 533           "reset=1&id=" . $row['civicrm_contribution_id'] . "&cid=" .
 534           $row['civicrm_contact_id'] .
 535           "&action=view&context=contribution&selectedChild=contribute",
 536           $this->_absoluteUrl
 537         );
 538         $rows[$rowNum]['civicrm_contribution_total_amount_link'] = $url;
 539         $rows[$rowNum]['civicrm_contribution_total_amount_hover'] = ts("View this contribution.");
 540         $entryFound = TRUE;
 541       }
 542
 543       // convert campaign_id to campaign title
 544       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 545         if ($value = $row['civicrm_contribution_campaign_id']) {
 546           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->campaigns[$value];
 547           $entryFound = TRUE;
 548         }
 549       }
 550
 551       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 552
 553       // skip looking further in rows, if first row itself doesn't
 554       if (!$entryFound) {
 555         break;
 556       }
 557       $lastKey = $rowNum;
 558     }
 559   }
 560
 561 }