CRM/Logging/ReportSummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Logging_ReportSummary extends CRM_Report_Form {
  36   protected $cid;
  37
  38   protected $_logTables = array();
  39
  40   protected $loggingDB;
  41
  42   function __construct() {
  43     $this->_logTables =
  44     array(
  45       'log_civicrm_contact' =>
  46       array( 'fk'  => 'id',
  47       ),
  48       'log_civicrm_email' =>
  49       array( 'fk'  => 'contact_id',
  50         'log_type' => 'Contact',
  51       ),
  52       'log_civicrm_phone' =>
  53       array( 'fk'  => 'contact_id',
  54         'log_type' => 'Contact',
  55       ),
  56       'log_civicrm_address' =>
  57       array( 'fk'  => 'contact_id',
  58         'log_type' => 'Contact',
  59       ),
  60       'log_civicrm_note' =>
  61       array( 'fk'  => 'entity_id',
  62         'entity_table' => true,
  63         'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
  64       ),
  65       'log_civicrm_note_comment' =>
  66       array( 'fk'  => 'entity_id',
  67         'table_name'  => 'log_civicrm_note',
  68         'joins' => array('table' => 'log_civicrm_note',
  69                          'join'  => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'"),
  70         'entity_table' => true,
  71         'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
  72       ),
  73       'log_civicrm_group_contact' =>
  74       array( 'fk'  => 'contact_id',
  75         'bracket_info'  => array('entity_column' => 'group_id', 'table' => 'log_civicrm_group', 'column' => 'title'),
  76         'action_column' => 'status',
  77         'log_type'      => 'Group',
  78       ),
  79       'log_civicrm_entity_tag' =>
  80       array( 'fk'  => 'entity_id',
  81         'bracket_info'  => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'),
  82         'entity_table'  => true
  83       ),
  84       'log_civicrm_relationship' =>
  85       array( 'fk'  => 'contact_id_a',
  86         'bracket_info'  => array('entity_column' => 'relationship_type_id', 'table' => 'log_civicrm_relationship_type', 'column' => 'label_a_b'),
  87       ),
  88       // FIXME: activity table structure has changed. Below activity code needs to be fixed based on new structure - CRM-12274
  89       /* 'log_civicrm_activity_for_target' => */
  90       /* array( 'fk'  => 'target_contact_id', */
  91       /*   'table_name'  => 'log_civicrm_activity', */
  92       /*   'joins' => array('table' => 'log_civicrm_activity_target', 'join' => 'entity_log_civireport.id = fk_table.activity_id'), */
  93       /*   'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)), */
  94       /*   'log_type'      => 'Activity', */
  95       /* ), */
  96       /* 'log_civicrm_activity_for_assignee' => */
  97       /* array( 'fk'  => 'assignee_contact_id', */
  98       /*   'table_name'  => 'log_civicrm_activity', */
  99       /*   'joins' => array('table' => 'log_civicrm_activity_assignment', 'join' => 'entity_log_civireport.id = fk_table.activity_id'), */
 100       /*   'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)), */
 101       /*   'log_type'      => 'Activity', */
 102       /* ), */
 103       /* 'log_civicrm_activity_for_source' => */
 104       /* array( 'fk'  => 'source_contact_id', */
 105       /*   'table_name'  => 'log_civicrm_activity', */
 106       /*   'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)), */
 107       /*   'log_type'      => 'Activity', */
 108       /* ), */
 109       'log_civicrm_case' =>
 110       array( 'fk'  => 'contact_id',
 111         'joins' => array('table' => 'log_civicrm_case_contact', 'join' => 'entity_log_civireport.id = fk_table.case_id'),
 112         'bracket_info'  => array('entity_column' => 'case_type_id', 'options' => CRM_Case_PseudoConstant::caseType('label', FALSE)),
 113       ),
 114     );
 115
 116     // don’t display the ‘Add these Contacts to Group’ button
 117     $this->_add2groupSupported = FALSE;
 118
 119     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
 120     $this->loggingDB = $dsn['database'];
 121
 122     // used for redirect back to contact summary
 123     $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject);
 124
 125     $logging = new CRM_Logging_Schema;
 126     $customTables = $logging->customDataLogTables();
 127     foreach ($customTables as $table) {
 128       $this->_logTables[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
 129     }
 130
 131     parent::__construct();
 132   }
 133
 134   function groupBy() {
 135     $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';
 136   }
 137
 138   function select() {
 139     $select = array();
 140     $this->_columnHeaders = array();
 141     foreach ($this->_columns as $tableName => $table) {
 142       if (array_key_exists('fields', $table)) {
 143         foreach ($table['fields'] as $fieldName => $field) {
 144           if (CRM_Utils_Array::value('required', $field) or CRM_Utils_Array::value($fieldName, $this->_params['fields'])) {
 145             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 146             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 147             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 148             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 149           }
 150         }
 151       }
 152     }
 153     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 154   }
 155
 156   function where() {
 157     parent::where();
 158     $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
 159   }
 160
 161   function postProcess() {
 162     $this->beginPostProcess();
 163     $rows = array();
 164
 165     $tempColumns = "id int(10)";
 166     if (CRM_Utils_Array::value('log_action', $this->_params['fields'])) {
 167       $tempColumns .= ", log_action varchar(64)";
 168     }
 169     $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
 170     if (CRM_Utils_Array::value('altered_contact', $this->_params['fields'])) {
 171       $tempColumns .= ", altered_contact varchar(128)";
 172     }
 173     $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
 174     if (CRM_Utils_Array::value('display_name', $this->_params['fields'])) {
 175       $tempColumns .= ", display_name varchar(128)";
 176     }
 177
 178     // temp table to hold all altered contact-ids
 179     $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
 180     CRM_Core_DAO::executeQuery($sql);
 181
 182     $logDateClause = $this->dateClause('log_date',
 183       CRM_Utils_Array::value("log_date_relative",  $this->_params),
 184       CRM_Utils_Array::value("log_date_from",      $this->_params),
 185       CRM_Utils_Array::value("log_date_to",        $this->_params),
 186       CRM_Utils_Type::T_DATE,
 187       CRM_Utils_Array::value("log_date_from_time", $this->_params),
 188       CRM_Utils_Array::value("log_date_to_time",   $this->_params));
 189     $logDateClause = $logDateClause ? "AND {$logDateClause}" : null;
 190
 191     $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
 192     unset($this->_params['log_type_value']);
 193     if ( empty($logTypes) ) {
 194       foreach ( array_keys($this->_logTables) as  $table ) {
 195         $type = $this->getLogType($table);
 196         $logTypes[$type] = $type;
 197       }
 198     }
 199
 200     $logTypeTableClause = '(1)';
 201     if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) {
 202       $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'],
 203                             $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL);
 204       unset($this->_params['log_type_table_value']);
 205     }
 206
 207     foreach ( $this->_logTables as $entity => $detail ) {
 208       if ((in_array($this->getLogType($entity), $logTypes) &&
 209         CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
 210         (!in_array($this->getLogType($entity), $logTypes) &&
 211           CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')) {
 212         $this->from( $entity );
 213         $sql = $this->buildQuery(false);
 214         $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
 215         $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
 216         CRM_Core_DAO::executeQuery($sql);
 217       }
 218     }
 219
 220     // add computed log_type column so that we can do a group by after that, which will help
 221     // alterDisplay() counts sync with pager counts
 222     $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
 223     $dao = CRM_Core_DAO::executeQuery($sql);
 224     $replaceWith = array();
 225     while($dao->fetch()){
 226       $type = $this->getLogType($dao->log_type);
 227       if (!array_key_exists($type,$replaceWith))
 228         $replaceWith[$type] = array();
 229       $replaceWith[$type][] = $dao->log_type;
 230     }
 231     foreach ($replaceWith as $type => $tables) {
 232       if (!empty($tables)) {
 233         $replaceWith[$type] = implode("','", $tables);
 234       }
 235     }
 236
 237     $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
 238     CRM_Core_DAO::executeQuery($sql);
 239     foreach ($replaceWith as $type => $in) {
 240       $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
 241       CRM_Core_DAO::executeQuery($sql);
 242     }
 243
 244     // note the group by columns are same as that used in alterDisplay as $newRows - $key
 245     $this->limit();
 246     $sql = "{$this->_select}
 247 FROM civicrm_temp_civireport_logsummary entity_log_civireport
 248 WHERE {$logTypeTableClause}
 249 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
 250 ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}";
 251     $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact',   $sql);
 252     $sql = str_replace('modified_contact_civireport.id',           'entity_log_civireport.altered_contact_id', $sql);
 253     $sql = str_replace(array('modified_contact_civireport.', 'altered_by_contact_civireport.'), 'entity_log_civireport.', $sql);
 254     $this->buildRows($sql, $rows);
 255
 256     // format result set.
 257     $this->formatDisplay($rows);
 258
 259     // assign variables to templates
 260     $this->doTemplateAssignment($rows);
 261
 262     // do print / pdf / instance stuff if needed
 263     $this->endPostProcess($rows);
 264   }
 265
 266   function getLogType( $entity ) {
 267     if (CRM_Utils_Array::value('log_type', $this->_logTables[$entity])) {
 268       return $this->_logTables[$entity]['log_type'];
 269     }
 270     $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
 271     return $logType;
 272   }
 273
 274   function getEntityValue( $id, $entity, $logDate ) {
 275     if (CRM_Utils_Array::value('bracket_info', $this->_logTables[$entity])) {
 276       if (CRM_Utils_Array::value('entity_column', $this->_logTables[$entity]['bracket_info'])) {
 277         $logTable = CRM_Utils_Array::value('table_name', $this->_logTables[$entity]) ? $this->_logTables[$entity]['table_name'] : $entity;
 278         $sql = "
 279 SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
 280   FROM `{$this->loggingDB}`.{$logTable}
 281  WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 282         $entityID = CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($id, 'Integer')));
 283       } else {
 284         $entityID = $id;
 285       }
 286
 287       // since case_type_id is a varchar field with separator
 288       if ($entity == 'log_civicrm_case') {
 289         $entityID = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR,$entityID);
 290         $entityID = CRM_Utils_Array::value(1, $entityID);
 291       }
 292
 293       if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) {
 294         $sql = "
 295 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
 296 FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
 297 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 298         return CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($entityID, 'Integer')));
 299       } else if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) {
 300         return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
 301       }
 302     }
 303     return null;
 304   }
 305
 306   function getEntityAction( $id, $connId, $entity, $oldAction ) {
 307     if (CRM_Utils_Array::value('action_column', $this->_logTables[$entity])) {
 308       $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
 309       $newAction = CRM_Core_DAO::singleValueQuery($sql, array(1 => array($id, 'Integer'), 2 => array($connId, 'Integer')));
 310
 311       switch ($entity) {
 312       case 'log_civicrm_group_contact':
 313         if ($oldAction !== 'Update')
 314           $newAction = $oldAction;
 315         if ($oldAction == 'Insert')
 316           $newAction = 'Added';
 317         break;
 318       }
 319       return $newAction;
 320     }
 321     return null;
 322   }
 323 }