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