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       'log_civicrm_activity_for_target' =>
  89       array( 'fk'  => 'target_contact_id',
  90         'table_name'  => 'log_civicrm_activity',
  91         'joins' => array('table' => 'log_civicrm_activity_target', 'join' => 'entity_log_civireport.id = fk_table.activity_id'),
  92         'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
  93         'log_type'      => 'Activity',
  94       ),
  95       'log_civicrm_activity_for_assignee' =>
  96       array( 'fk'  => 'assignee_contact_id',
  97         'table_name'  => 'log_civicrm_activity',
  98         'joins' => array('table' => 'log_civicrm_activity_assignment', 'join' => 'entity_log_civireport.id = fk_table.activity_id'),
  99         'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
 100         'log_type'      => 'Activity',
 101       ),
 102       'log_civicrm_activity_for_source' =>
 103       array( 'fk'  => 'source_contact_id',
 104         'table_name'  => 'log_civicrm_activity',
 105         'bracket_info'  => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
 106         'log_type'      => 'Activity',
 107       ),
 108       'log_civicrm_case' =>
 109       array( 'fk'  => 'contact_id',
 110         'joins' => array('table' => 'log_civicrm_case_contact', 'join' => 'entity_log_civireport.id = fk_table.case_id'),
 111         'bracket_info'  => array('entity_column' => 'case_type_id', 'options' => CRM_Case_PseudoConstant::caseType('label', FALSE)),
 112       ),
 113     );
 114
 115     // don’t display the ‘Add these Contacts to Group’ button
 116     $this->_add2groupSupported = FALSE;
 117
 118     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
 119     $this->loggingDB = $dsn['database'];
 120
 121     // used for redirect back to contact summary
 122     $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject);
 123
 124     $logging = new CRM_Logging_Schema;
 125     $customTables = $logging->customDataLogTables();
 126     foreach ($customTables as $table) {
 127       $this->_logTables[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
 128     }
 129
 130     parent::__construct();
 131   }
 132
 133   function groupBy() {
 134     $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';
 135   }
 136
 137   function select() {
 138     $select = array();
 139     $this->_columnHeaders = array();
 140     foreach ($this->_columns as $tableName => $table) {
 141       if (array_key_exists('fields', $table)) {
 142         foreach ($table['fields'] as $fieldName => $field) {
 143           if (CRM_Utils_Array::value('required', $field) or CRM_Utils_Array::value($fieldName, $this->_params['fields'])) {
 144             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 145             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 146             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 147             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 148           }
 149         }
 150       }
 151     }
 152     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 153   }
 154
 155   function where() {
 156     parent::where();
 157     $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
 158   }
 159
 160   function postProcess() {
 161     $this->beginPostProcess();
 162     $rows = array();
 163
 164     $tempColumns = "id int(10)";
 165     if (CRM_Utils_Array::value('log_action', $this->_params['fields'])) {
 166       $tempColumns .= ", log_action varchar(64)";
 167     }
 168     $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
 169     if (CRM_Utils_Array::value('altered_contact', $this->_params['fields'])) {
 170       $tempColumns .= ", altered_contact varchar(128)";
 171     }
 172     $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
 173     if (CRM_Utils_Array::value('display_name', $this->_params['fields'])) {
 174       $tempColumns .= ", display_name varchar(128)";
 175     }
 176
 177     // temp table to hold all altered contact-ids
 178     $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
 179     CRM_Core_DAO::executeQuery($sql);
 180
 181     $logDateClause = $this->dateClause('log_date',
 182       CRM_Utils_Array::value("log_date_relative",  $this->_params),
 183       CRM_Utils_Array::value("log_date_from",      $this->_params),
 184       CRM_Utils_Array::value("log_date_to",        $this->_params),
 185       CRM_Utils_Type::T_DATE,
 186       CRM_Utils_Array::value("log_date_from_time", $this->_params),
 187       CRM_Utils_Array::value("log_date_to_time",   $this->_params));
 188     $logDateClause = $logDateClause ? "AND {$logDateClause}" : null;
 189
 190     $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
 191     unset($this->_params['log_type_value']);
 192     if ( empty($logTypes) ) {
 193       foreach ( array_keys($this->_logTables) as  $table ) {
 194         $type = $this->getLogType($table);
 195         $logTypes[$type] = $type;
 196       }
 197     }
 198
 199     foreach ( $this->_logTables as $entity => $detail ) {
 200       if ((in_array($this->getLogType($entity), $logTypes) &&
 201         CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
 202         (!in_array($this->getLogType($entity), $logTypes) &&
 203           CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')) {
 204         $this->from( $entity );
 205         $sql = $this->buildQuery(false);
 206         $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
 207         $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
 208         CRM_Core_DAO::executeQuery($sql);
 209       }
 210     }
 211
 212     $this->limit();
 213     $sql = "{$this->_select}
 214 FROM civicrm_temp_civireport_logsummary entity_log_civireport
 215 ORDER BY entity_log_civireport.log_date DESC {$this->_limit}";
 216     $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact',   $sql);
 217     $sql = str_replace('modified_contact_civireport.id',           'entity_log_civireport.altered_contact_id', $sql);
 218     $sql = str_replace(array('modified_contact_civireport.', 'altered_by_contact_civireport.'), 'entity_log_civireport.', $sql);
 219     $this->buildRows($sql, $rows);
 220
 221     // format result set.
 222     $this->formatDisplay($rows);
 223
 224     // assign variables to templates
 225     $this->doTemplateAssignment($rows);
 226
 227     // do print / pdf / instance stuff if needed
 228     $this->endPostProcess($rows);
 229   }
 230
 231   function getLogType( $entity ) {
 232     if (CRM_Utils_Array::value('log_type', $this->_logTables[$entity])) {
 233       return $this->_logTables[$entity]['log_type'];
 234     }
 235     $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
 236     return $logType;
 237   }
 238
 239   function getEntityValue( $id, $entity, $logDate ) {
 240     if (CRM_Utils_Array::value('bracket_info', $this->_logTables[$entity])) {
 241       if (CRM_Utils_Array::value('entity_column', $this->_logTables[$entity]['bracket_info'])) {
 242         $logTable = CRM_Utils_Array::value('table_name', $this->_logTables[$entity]) ? $this->_logTables[$entity]['table_name'] : $entity;
 243         $sql = "
 244 SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
 245   FROM `{$this->loggingDB}`.{$logTable}
 246  WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 247         $entityID = CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($id, 'Integer')));
 248       } else {
 249         $entityID = $id;
 250       }
 251
 252       // since case_type_id is a varchar field with separator
 253       if ($entity == 'log_civicrm_case') {
 254         $entityID = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR,$entityID);
 255         $entityID = CRM_Utils_Array::value(1, $entityID);
 256       }
 257
 258       if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) {
 259         $sql = "
 260 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
 261 FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
 262 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 263         return CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($entityID, 'Integer')));
 264       } else if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) {
 265         return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
 266       }
 267     }
 268     return null;
 269   }
 270
 271   function getEntityAction( $id, $connId, $entity, $oldAction ) {
 272     if (CRM_Utils_Array::value('action_column', $this->_logTables[$entity])) {
 273       $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
 274       $newAction = CRM_Core_DAO::singleValueQuery($sql, array(1 => array($id, 'Integer'), 2 => array($connId, 'Integer')));
 275
 276       switch ($entity) {
 277       case 'log_civicrm_group_contact':
 278         if ($oldAction !== 'Update')
 279           $newAction = $oldAction;
 280         if ($oldAction == 'Insert')
 281           $newAction = 'Added';
 282         break;
 283       }
 284       return $newAction;
 285     }
 286     return null;
 287   }
 288 }