crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Logging/ReportSummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  */
  34 class CRM_Logging_ReportSummary extends CRM_Report_Form {
  35   protected $cid;
  36
  37   protected $_logTables = array();
  38
  39   protected $loggingDB;
  40
  41   /**
  42    * Class constructor.
  43    */
  44   public function __construct() {
  45     // don’t display the ‘Add these Contacts to Group’ button
  46     $this->_add2groupSupported = FALSE;
  47
  48     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
  49     $this->loggingDB = $dsn['database'];
  50
  51     // used for redirect back to contact summary
  52     $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject);
  53
  54     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
  55     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
  56     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
  57     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
  58
  59     $this->_logTables = array(
  60       'log_civicrm_contact' => array(
  61         'fk' => 'id',
  62       ),
  63       'log_civicrm_email' => array(
  64         'fk' => 'contact_id',
  65         'log_type' => 'Contact',
  66       ),
  67       'log_civicrm_phone' => array(
  68         'fk' => 'contact_id',
  69         'log_type' => 'Contact',
  70       ),
  71       'log_civicrm_address' => array(
  72         'fk' => 'contact_id',
  73         'log_type' => 'Contact',
  74       ),
  75       'log_civicrm_note' => array(
  76         'fk' => 'entity_id',
  77         'entity_table' => TRUE,
  78         'bracket_info' => array(
  79           'table' => 'log_civicrm_note',
  80           'column' => 'subject',
  81         ),
  82       ),
  83       'log_civicrm_note_comment' => array(
  84         'fk' => 'entity_id',
  85         'table_name' => 'log_civicrm_note',
  86         'joins' => array(
  87           'table' => 'log_civicrm_note',
  88           'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'",
  89         ),
  90         'entity_table' => TRUE,
  91         'bracket_info' => array(
  92           'table' => 'log_civicrm_note',
  93           'column' => 'subject',
  94         ),
  95       ),
  96       'log_civicrm_group_contact' => array(
  97         'fk' => 'contact_id',
  98         'bracket_info' => array(
  99           'entity_column' => 'group_id',
 100           'table' => 'log_civicrm_group',
 101           'column' => 'title',
 102         ),
 103         'action_column' => 'status',
 104         'log_type' => 'Group',
 105       ),
 106       'log_civicrm_entity_tag' => array(
 107         'fk' => 'entity_id',
 108         'bracket_info' => array(
 109           'entity_column' => 'tag_id',
 110           'table' => 'log_civicrm_tag',
 111           'column' => 'name',
 112         ),
 113         'entity_table' => TRUE,
 114       ),
 115       'log_civicrm_relationship' => array(
 116         'fk' => 'contact_id_a',
 117         'bracket_info' => array(
 118           'entity_column' => 'relationship_type_id',
 119           'table' => 'log_civicrm_relationship_type',
 120           'column' => 'label_a_b',
 121         ),
 122       ),
 123       'log_civicrm_activity_for_target' => array(
 124         'fk' => 'contact_id',
 125         'table_name' => 'log_civicrm_activity',
 126         'joins' => array(
 127           'table' => 'log_civicrm_activity_contact',
 128           'join' => "(entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$targetID})",
 129         ),
 130         'bracket_info' => array(
 131           'entity_column' => 'activity_type_id',
 132           'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 133         ),
 134         'log_type' => 'Activity',
 135       ),
 136       'log_civicrm_activity_for_assignee' => array(
 137         'fk' => 'contact_id',
 138         'table_name' => 'log_civicrm_activity',
 139         'joins' => array(
 140           'table' => 'log_civicrm_activity_contact',
 141           'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$assigneeID}",
 142         ),
 143         'bracket_info' => array(
 144           'entity_column' => 'activity_type_id',
 145           'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 146         ),
 147         'log_type' => 'Activity',
 148       ),
 149       'log_civicrm_activity_for_source' => array(
 150         'fk' => 'contact_id',
 151         'table_name' => 'log_civicrm_activity',
 152         'joins' => array(
 153           'table' => 'log_civicrm_activity_contact',
 154           'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$sourceID}",
 155         ),
 156         'bracket_info' => array(
 157           'entity_column' => 'activity_type_id',
 158           'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 159         ),
 160         'log_type' => 'Activity',
 161       ),
 162       'log_civicrm_case' => array(
 163         'fk' => 'contact_id',
 164         'joins' => array(
 165           'table' => 'log_civicrm_case_contact',
 166           'join' => 'entity_log_civireport.id = fk_table.case_id',
 167         ),
 168         'bracket_info' => array(
 169           'entity_column' => 'case_type_id',
 170           'options' => CRM_Case_PseudoConstant::caseType('title', FALSE),
 171         ),
 172       ),
 173     );
 174
 175     $logging = new CRM_Logging_Schema();
 176
 177     // build _logTables for contact custom tables
 178     $customTables = $logging->entityCustomDataLogTables('Contact');
 179     foreach ($customTables as $table) {
 180       $this->_logTables[$table] = array(
 181         'fk' => 'entity_id',
 182         'log_type' => 'Contact',
 183       );
 184     }
 185
 186     // build _logTables for address custom tables
 187     $customTables = $logging->entityCustomDataLogTables('Address');
 188     foreach ($customTables as $table) {
 189       $this->_logTables[$table] = array(
 190         // For join of fk_table with contact table.
 191         'fk' => 'contact_id',
 192         'joins' => array(
 193           // fk_table
 194           'table' => 'log_civicrm_address',
 195           'join' => 'entity_log_civireport.entity_id = fk_table.id',
 196         ),
 197         'log_type' => 'Contact',
 198       );
 199     }
 200
 201     // Allow log tables to be extended via report hooks.
 202     CRM_Report_BAO_Hook::singleton()->alterLogTables($this, $this->_logTables);
 203
 204     parent::__construct();
 205   }
 206
 207   public function groupBy() {
 208     $this->_groupBy = 'GROUP BY entity_log_civireport.log_conn_id, entity_log_civireport.log_user_id, EXTRACT(DAY_MICROSECOND FROM entity_log_civireport.log_date), entity_log_civireport.id';
 209   }
 210
 211   public function select() {
 212     $select = array();
 213     $this->_columnHeaders = array();
 214     foreach ($this->_columns as $tableName => $table) {
 215       if (array_key_exists('fields', $table)) {
 216         foreach ($table['fields'] as $fieldName => $field) {
 217           if (CRM_Utils_Array::value('required', $field) or
 218             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 219           ) {
 220             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 221             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 222             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 223             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 224           }
 225         }
 226       }
 227     }
 228     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 229   }
 230
 231   public function where() {
 232     // reset where clause as its called multiple times, every time insert sql is built.
 233     $this->_whereClauses = array();
 234
 235     parent::where();
 236     $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
 237   }
 238
 239   public function postProcess() {
 240     $this->beginPostProcess();
 241     $rows = array();
 242
 243     $tempColumns = "id int(10)";
 244     if (!empty($this->_params['fields']['log_action'])) {
 245       $tempColumns .= ", log_action varchar(64)";
 246     }
 247     $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
 248     if (!empty($this->_params['fields']['altered_contact'])) {
 249       $tempColumns .= ", altered_contact varchar(128)";
 250     }
 251     $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
 252     if (!empty($this->_params['fields']['display_name'])) {
 253       $tempColumns .= ", display_name varchar(128)";
 254     }
 255
 256     // temp table to hold all altered contact-ids
 257     $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
 258     CRM_Core_DAO::executeQuery($sql);
 259
 260     $logDateClause = $this->dateClause('log_date',
 261       CRM_Utils_Array::value("log_date_relative", $this->_params),
 262       CRM_Utils_Array::value("log_date_from", $this->_params),
 263       CRM_Utils_Array::value("log_date_to", $this->_params),
 264       CRM_Utils_Type::T_DATE,
 265       CRM_Utils_Array::value("log_date_from_time", $this->_params),
 266       CRM_Utils_Array::value("log_date_to_time", $this->_params));
 267     $logDateClause = $logDateClause ? "AND {$logDateClause}" : NULL;
 268
 269     $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
 270     unset($this->_params['log_type_value']);
 271     if (empty($logTypes)) {
 272       foreach (array_keys($this->_logTables) as $table) {
 273         $type = $this->getLogType($table);
 274         $logTypes[$type] = $type;
 275       }
 276     }
 277
 278     $logTypeTableClause = '(1)';
 279     if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) {
 280       $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'],
 281         $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL);
 282       unset($this->_params['log_type_table_value']);
 283     }
 284
 285     foreach ($this->_logTables as $entity => $detail) {
 286       if ((in_array($this->getLogType($entity), $logTypes) &&
 287           CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
 288         (!in_array($this->getLogType($entity), $logTypes) &&
 289           CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')
 290       ) {
 291         $this->from($entity);
 292         $sql = $this->buildQuery(FALSE);
 293         $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
 294         $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
 295         CRM_Core_DAO::executeQuery($sql);
 296       }
 297     }
 298
 299     // add computed log_type column so that we can do a group by after that, which will help
 300     // alterDisplay() counts sync with pager counts
 301     $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
 302     $dao = CRM_Core_DAO::executeQuery($sql);
 303     $replaceWith = array();
 304     while ($dao->fetch()) {
 305       $type = $this->getLogType($dao->log_type);
 306       if (!array_key_exists($type, $replaceWith)) {
 307         $replaceWith[$type] = array();
 308       }
 309       $replaceWith[$type][] = $dao->log_type;
 310     }
 311     foreach ($replaceWith as $type => $tables) {
 312       if (!empty($tables)) {
 313         $replaceWith[$type] = implode("','", $tables);
 314       }
 315     }
 316
 317     $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
 318     CRM_Core_DAO::executeQuery($sql);
 319     foreach ($replaceWith as $type => $in) {
 320       $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
 321       CRM_Core_DAO::executeQuery($sql);
 322     }
 323
 324     // note the group by columns are same as that used in alterDisplay as $newRows - $key
 325     $this->limit();
 326     $sql = "{$this->_select}
 327 FROM civicrm_temp_civireport_logsummary entity_log_civireport
 328 WHERE {$logTypeTableClause}
 329 GROUP BY log_civicrm_entity_log_date, log_civicrm_entity_log_type_label, log_civicrm_entity_log_conn_id, log_civicrm_entity_log_user_id, log_civicrm_entity_altered_contact_id
 330 ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}";
 331     $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
 332     $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
 333     $sql = str_replace(array(
 334       'modified_contact_civireport.',
 335       'altered_by_contact_civireport.',
 336     ), 'entity_log_civireport.', $sql);
 337     $this->buildRows($sql, $rows);
 338
 339     // format result set.
 340     $this->formatDisplay($rows);
 341
 342     // assign variables to templates
 343     $this->doTemplateAssignment($rows);
 344
 345     // do print / pdf / instance stuff if needed
 346     $this->endPostProcess($rows);
 347   }
 348
 349   /**
 350    * Get log type.
 351    *
 352    * @param string $entity
 353    *
 354    * @return string
 355    */
 356   public function getLogType($entity) {
 357     if (!empty($this->_logTables[$entity]['log_type'])) {
 358       return $this->_logTables[$entity]['log_type'];
 359     }
 360     $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
 361     return $logType;
 362   }
 363
 364   /**
 365    * Get entity value.
 366    *
 367    * @param int $id
 368    * @param $entity
 369    * @param $logDate
 370    *
 371    * @return mixed|null|string
 372    */
 373   public function getEntityValue($id, $entity, $logDate) {
 374     if (!empty($this->_logTables[$entity]['bracket_info'])) {
 375       if (!empty($this->_logTables[$entity]['bracket_info']['entity_column'])) {
 376         $logTable = !empty($this->_logTables[$entity]['table_name']) ? $this->_logTables[$entity]['table_name'] : $entity;
 377         $sql = "
 378 SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
 379   FROM `{$this->loggingDB}`.{$logTable}
 380  WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 381         $entityID = CRM_Core_DAO::singleValueQuery($sql, array(
 382           1 => array(
 383             CRM_Utils_Date::isoToMysql($logDate),
 384             'Timestamp',
 385           ),
 386           2 => array($id, 'Integer'),
 387         ));
 388       }
 389       else {
 390         $entityID = $id;
 391       }
 392
 393       if ($entityID && $logDate &&
 394         array_key_exists('table', $this->_logTables[$entity]['bracket_info'])
 395       ) {
 396         $sql = "
 397 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
 398 FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
 399 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 400         return CRM_Core_DAO::singleValueQuery($sql, array(
 401           1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'),
 402           2 => array($entityID, 'Integer'),
 403         ));
 404       }
 405       else {
 406         if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) &&
 407           $entityID
 408         ) {
 409           return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
 410         }
 411       }
 412     }
 413     return NULL;
 414   }
 415
 416   /**
 417    * Get entity action.
 418    *
 419    * @param int $id
 420    * @param int $connId
 421    * @param $entity
 422    * @param $oldAction
 423    *
 424    * @return null|string
 425    */
 426   public function getEntityAction($id, $connId, $entity, $oldAction) {
 427     if (!empty($this->_logTables[$entity]['action_column'])) {
 428       $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
 429       $newAction = CRM_Core_DAO::singleValueQuery($sql, array(
 430         1 => array($id, 'Integer'),
 431         2 => array($connId, 'Integer'),
 432       ));
 433
 434       switch ($entity) {
 435         case 'log_civicrm_group_contact':
 436           if ($oldAction !== 'Update') {
 437             $newAction = $oldAction;
 438           }
 439           if ($oldAction == 'Insert') {
 440             $newAction = 'Added';
 441           }
 442           break;
 443       }
 444       return $newAction;
 445     }
 446     return NULL;
 447   }
 448
 449 }