CRM/Report/Form/Contact/CurrentEmployer.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_Contact_CurrentEmployer extends CRM_Report_Form {
  18
  19   /**
  20    * This report has not been optimised for group filtering.
  21    *
  22    * The functionality for group filtering has been improved but not
  23    * all reports have been adjusted to take care of it. This report has not
  24    * and will run an inefficient query until fixed.
  25    *
  26    * @var bool
  27    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  28    */
  29   protected $groupFilterNotOptimised = TRUE;
  30
  31   protected $_summary = NULL;
  32
  33   protected $_customGroupExtends = [
  34     'Contact',
  35     'Individual',
  36   ];
  37
  38   public $_drilldownReport = ['contact/detail' => 'Link to Detail Report'];
  39
  40   /**
  41    * Class constructor.
  42    */
  43   public function __construct() {
  44
  45     $this->_columns = [
  46       'civicrm_employer' => [
  47         'dao' => 'CRM_Contact_DAO_Contact',
  48         'fields' => [
  49           'organization_name' => [
  50             'title' => ts('Employer Name'),
  51             'required' => TRUE,
  52             'no_repeat' => TRUE,
  53           ],
  54           'id' => [
  55             'no_display' => TRUE,
  56             'required' => TRUE,
  57           ],
  58         ],
  59         'filters' => [
  60           'organization_name' => [
  61             'title' => ts('Employer Name'),
  62             'operatorType' => CRM_Report_Form::OP_STRING,
  63           ],
  64         ],
  65       ],
  66       'civicrm_contact' => [
  67         'dao' => 'CRM_Contact_DAO_Contact',
  68         'fields' => [
  69           'sort_name' => [
  70             'title' => ts('Employee Name'),
  71             'required' => TRUE,
  72           ],
  73           'first_name' => [
  74             'title' => ts('First Name'),
  75           ],
  76           'middle_name' => [
  77             'title' => ts('Middle Name'),
  78           ],
  79           'last_name' => [
  80             'title' => ts('Last Name'),
  81           ],
  82           'id' => [
  83             'no_display' => TRUE,
  84             'required' => TRUE,
  85           ],
  86           'job_title' => [
  87             'title' => ts('Job Title'),
  88             'default' => TRUE,
  89           ],
  90           'gender_id' => [
  91             'title' => ts('Gender'),
  92           ],
  93           'birth_date' => [
  94             'title' => ts('Birth Date'),
  95           ],
  96           'age' => [
  97             'title' => ts('Age'),
  98             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  99           ],
 100           'contact_type' => [
 101             'title' => ts('Contact Type'),
 102           ],
 103           'contact_sub_type' => [
 104             'title' => ts('Contact Subtype'),
 105           ],
 106         ],
 107         'filters' => array_merge($this->getBasicContactFilters(), ['sort_name' => ['title' => ts('Employee Name')]]),
 108         'grouping' => 'contact-fields',
 109       ],
 110       'civicrm_relationship' => [
 111         'dao' => 'CRM_Contact_DAO_Relationship',
 112         'fields' => [
 113           'start_date' => [
 114             'title' => ts('Employee Since'),
 115             'default' => TRUE,
 116           ],
 117         ],
 118         'filters' => [
 119           'start_date' => [
 120             'title' => ts('Employee Since'),
 121             'operatorType' => CRM_Report_Form::OP_DATE,
 122             'type' => CRM_Utils_Type::T_DATE,
 123           ],
 124         ],
 125       ],
 126       'civicrm_phone' => [
 127         'dao' => 'CRM_Core_DAO_Phone',
 128         'grouping' => 'contact-fields',
 129         'fields' => [
 130           'phone' => [
 131             'title' => ts('Phone'),
 132             'default' => TRUE,
 133           ],
 134         ],
 135       ],
 136       'civicrm_email' => [
 137         'dao' => 'CRM_Core_DAO_Email',
 138         'grouping' => 'contact-fields',
 139         'fields' => [
 140           'email' => [
 141             'title' => ts('Email'),
 142             'default' => TRUE,
 143           ],
 144         ],
 145       ],
 146       'civicrm_address' => [
 147         'dao' => 'CRM_Core_DAO_Address',
 148         'grouping' => 'contact-fields',
 149         'fields' => [
 150           'street_address' => NULL,
 151           'city' => NULL,
 152           'postal_code' => NULL,
 153           'state_province_id' => [
 154             'title' => ts('State/Province'),
 155           ],
 156           'country_id' => [
 157             'title' => ts('Country'),
 158           ],
 159         ],
 160         'filters' => [
 161           'country_id' => [
 162             'title' => ts('Country'),
 163             'type' => CRM_Utils_Type::T_INT,
 164             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 165             'options' => CRM_Core_PseudoConstant::country(NULL, FALSE),
 166           ],
 167           'state_province_id' => [
 168             'title' => ts('State/Province'),
 169             'type' => CRM_Utils_Type::T_INT,
 170             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 171             'options' => CRM_Core_PseudoConstant::stateProvince(),
 172           ],
 173         ],
 174       ],
 175     ];
 176
 177     $this->_groupFilter = TRUE;
 178     $this->_tagFilter = TRUE;
 179     parent::__construct();
 180   }
 181
 182   public function preProcess() {
 183     parent::preProcess();
 184   }
 185
 186   public function select() {
 187
 188     $select = $this->_columnHeaders = [];
 189
 190     foreach ($this->_columns as $tableName => $table) {
 191       if (array_key_exists('fields', $table)) {
 192         foreach ($table['fields'] as $fieldName => $field) {
 193           if (!empty($field['required']) ||
 194             !empty($this->_params['fields'][$fieldName])
 195           ) {
 196
 197             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 198             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 199             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 200           }
 201         }
 202       }
 203     }
 204     $this->_selectClauses = $select;
 205
 206     $this->_select = "SELECT " . implode(', ', $select) . " ";
 207   }
 208
 209   public function from() {
 210     $relType = civicrm_api3('RelationshipType', 'getvalue', [
 211       'return' => "id",
 212       'name_a_b' => "Employee of",
 213       'name_b_a' => "Employer of",
 214     ]);
 215     $this->_from = "
 216 FROM civicrm_contact {$this->_aliases['civicrm_contact']}
 217
 218      LEFT JOIN civicrm_contact {$this->_aliases['civicrm_employer']}
 219           ON {$this->_aliases['civicrm_employer']}.id={$this->_aliases['civicrm_contact']}.employer_id
 220
 221      {$this->_aclFrom}
 222      LEFT JOIN civicrm_relationship {$this->_aliases['civicrm_relationship']}
 223           ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a={$this->_aliases['civicrm_contact']}.id
 224               AND {$this->_aliases['civicrm_relationship']}.contact_id_b={$this->_aliases['civicrm_contact']}.employer_id
 225               AND {$this->_aliases['civicrm_relationship']}.relationship_type_id={$relType})  ";
 226
 227     $this->joinAddressFromContact();
 228     $this->joinPhoneFromContact();
 229     $this->joinEmailFromContact();
 230   }
 231
 232   public function where() {
 233
 234     $clauses = [];
 235     foreach ($this->_columns as $tableName => $table) {
 236       if (array_key_exists('filters', $table)) {
 237         foreach ($table['filters'] as $fieldName => $field) {
 238           $clause = NULL;
 239           if (CRM_Utils_Array::value('operatorType', $field) & CRM_Report_Form::OP_DATE
 240           ) {
 241             $relative = $this->_params["{$fieldName}_relative"] ?? NULL;
 242             $from = $this->_params["{$fieldName}_from"] ?? NULL;
 243             $to = $this->_params["{$fieldName}_to"] ?? NULL;
 244
 245             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
 246           }
 247           else {
 248             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 249             if ($op) {
 250               $clause = $this->whereClause($field,
 251                 $op,
 252                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 253                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 254                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 255               );
 256             }
 257           }
 258
 259           if (!empty($clause)) {
 260             $clauses[$fieldName] = $clause;
 261           }
 262         }
 263       }
 264     }
 265
 266     if (empty($clauses)) {
 267       $this->_where = "WHERE {$this->_aliases['civicrm_contact']}.employer_id!='null' ";
 268     }
 269     else {
 270       $this->_where = "WHERE ({$this->_aliases['civicrm_contact']}.employer_id!='null') AND " . implode(' AND ', $clauses);
 271     }
 272
 273     if ($this->_aclWhere) {
 274       $this->_where .= " AND {$this->_aclWhere} ";
 275     }
 276   }
 277
 278   public function groupBy() {
 279     $groupBy = [
 280       "{$this->_aliases['civicrm_employer']}.id",
 281       "{$this->_aliases['civicrm_contact']}.id",
 282     ];
 283
 284     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 285   }
 286
 287   public function orderBy() {
 288     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_employer']}.organization_name, {$this->_aliases['civicrm_contact']}.display_name";
 289   }
 290
 291   public function postProcess() {
 292     // get the acl clauses built before we assemble the query
 293     $this->buildACLClause([
 294       $this->_aliases['civicrm_contact'],
 295       $this->_aliases['civicrm_employer'],
 296     ]);
 297     parent::postProcess();
 298   }
 299
 300   /**
 301    * Alter display of rows.
 302    *
 303    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 304    * such as rendering contacts as links.
 305    *
 306    * @param array $rows
 307    *   Rows generated by SQL, with an array for each row.
 308    */
 309   public function alterDisplay(&$rows) {
 310     $checkList = [];
 311     $entryFound = FALSE;
 312
 313     foreach ($rows as $rowNum => $row) {
 314
 315       // convert employer name to links
 316       if (array_key_exists('civicrm_employer_organization_name', $row) &&
 317         array_key_exists('civicrm_employer_id', $row)
 318       ) {
 319         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 320           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_employer_id'],
 321           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 322         );
 323         $rows[$rowNum]['civicrm_employer_organization_name_link'] = $url;
 324         $rows[$rowNum]['civicrm_employer_organization_name_hover'] = ts('View Contact Detail Report for this contact');
 325         $entryFound = TRUE;
 326       }
 327
 328       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 329         // not repeat contact display names if it matches with the one
 330         // in previous row
 331
 332         foreach ($row as $colName => $colVal) {
 333           if (!empty($checkList[$colName]) && is_array($checkList[$colName]) &&
 334             in_array($colVal, $checkList[$colName])
 335           ) {
 336             $rows[$rowNum][$colName] = "";
 337           }
 338           if (in_array($colName, $this->_noRepeats)) {
 339             $checkList[$colName][] = $colVal;
 340           }
 341         }
 342       }
 343
 344       // Handle ID to label conversion for contact fields
 345       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contact/currentEmployer', 'View Contact Detail') ? TRUE : $entryFound;
 346       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 347
 348       // convert employee name to links
 349       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 350         array_key_exists('civicrm_contact_id', $row)
 351       ) {
 352         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 353           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 354           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 355         );
 356         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 357         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Detail Report for this contact');
 358         $entryFound = TRUE;
 359       }
 360
 361       // skip looking further in rows, if first row itself doesn't
 362       // have the column we need
 363       if (!$entryFound) {
 364         break;
 365       }
 366     }
 367   }
 368
 369 }