CRM/Report/Form/Contribute/HouseholdSummary.php

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