CRM/Report/Form/Contribute/OrganizationSummary.php

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