[civicrm-core.git] / CRM / Logging / ReportSummary.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.4                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2013                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2013
 * $Id$
 *
 */
class CRM_Logging_ReportSummary extends CRM_Report_Form {
  protected $cid;

  protected $_logTables = array();

  protected $loggingDB;

  function __construct() {
    // don’t display the ‘Add these Contacts to Group’ button
    $this->_add2groupSupported = FALSE;

    $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
    $this->loggingDB = $dsn['database'];

    // used for redirect back to contact summary
    $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject);

    $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
    $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
    $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
    $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);

    $this->_logTables =
      array(
        'log_civicrm_contact' =>
        array(
          'fk' => 'id',
        ),
        'log_civicrm_email' =>
        array(
          'fk' => 'contact_id',
          'log_type' => 'Contact',
        ),
        'log_civicrm_phone' =>
        array(
          'fk' => 'contact_id',
          'log_type' => 'Contact',
        ),
        'log_civicrm_address' =>
        array(
          'fk' => 'contact_id',
          'log_type' => 'Contact',
        ),
        'log_civicrm_note' =>
        array(
          'fk' => 'entity_id',
          'entity_table' => TRUE,
          'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
        ),
        'log_civicrm_note_comment' =>
        array(
          'fk' => 'entity_id',
          'table_name' => 'log_civicrm_note',
          'joins' => array(
            'table' => 'log_civicrm_note',
            'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'"
          ),
          'entity_table' => TRUE,
          'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
        ),
        'log_civicrm_group_contact' =>
        array(
          'fk' => 'contact_id',
          'bracket_info' => array('entity_column' => 'group_id', 'table' => 'log_civicrm_group', 'column' => 'title'),
          'action_column' => 'status',
          'log_type' => 'Group',
        ),
        'log_civicrm_entity_tag' =>
        array(
          'fk' => 'entity_id',
          'bracket_info' => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'),
          'entity_table' => TRUE
        ),
        'log_civicrm_relationship' =>
        array(
          'fk' => 'contact_id_a',
          'bracket_info' => array(
            'entity_column' => 'relationship_type_id',
            'table' => 'log_civicrm_relationship_type',
            'column' => 'label_a_b'
          ),
        ),
        'log_civicrm_activity_for_target' =>
        array(
          'fk' => 'contact_id',
          'table_name' => 'log_civicrm_activity',
          'joins' => array(
            'table' => 'log_civicrm_activity_contact',
            'join' => "(entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$targetID})"
          ),
          'bracket_info' => array(
            'entity_column' => 'activity_type_id',
            'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
          ),
          'log_type' => 'Activity',
        ),
        'log_civicrm_activity_for_assignee' =>
        array(
          'fk' => 'contact_id',
          'table_name' => 'log_civicrm_activity',
          'joins' => array(
            'table' => 'log_civicrm_activity_contact',
            'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$assigneeID}"
          ),
          'bracket_info' => array(
            'entity_column' => 'activity_type_id',
            'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
          ),
          'log_type' => 'Activity',
        ),
        'log_civicrm_activity_for_source' =>
        array(
          'fk' => 'contact_id',
          'table_name' => 'log_civicrm_activity_contact',
          'joins' => array(
            'table' => 'log_civicrm_activity_contact',
            'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$sourceID}"
          ),
          'bracket_info' => array(
            'entity_column' => 'activity_type_id',
            'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
          ),
          'log_type' => 'Activity',
        ),
        'log_civicrm_case' =>
        array(
          'fk' => 'contact_id',
          'joins' => array(
            'table' => 'log_civicrm_case_contact',
            'join' => 'entity_log_civireport.id = fk_table.case_id'
          ),
          'bracket_info' => array(
            'entity_column' => 'case_type_id',
            'options' => CRM_Case_PseudoConstant::caseType('label', FALSE)
          ),
        ),
      );

    $logging = new CRM_Logging_Schema;

    // build _logTables for contact custom tables
    $customTables = $logging->entityCustomDataLogTables('Contact');
    foreach ($customTables as $table) {
      $this->_logTables[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
    }

    // build _logTables for address custom tables
    $customTables = $logging->entityCustomDataLogTables('Address');
    foreach ($customTables as $table) {
      $this->_logTables[$table] = 
        array(
          'fk' => 'contact_id',// for join of fk_table with contact table
          'joins' => array(
            'table' => 'log_civicrm_address', // fk_table
            'join'  => 'entity_log_civireport.entity_id = fk_table.id'
          ),
          'log_type' => 'Contact'
        );
    }

    // allow log tables to be extended via report hooks
    CRM_Report_BAO_Hook::singleton()->alterLogTables($this, $this->_logTables);

    parent::__construct();
  }

  function groupBy() {
    $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';
  }

  function select() {
    $select = array();
    $this->_columnHeaders = array();
    foreach ($this->_columns as $tableName => $table) {
      if (array_key_exists('fields', $table)) {
        foreach ($table['fields'] as $fieldName => $field) {
          if (CRM_Utils_Array::value('required', $field) or CRM_Utils_Array::value($fieldName, $this->_params['fields'])) {
            $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
            $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
            $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
            $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
          }
        }
      }
    }
    $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
  }

  function where() {
    parent::where();
    $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
  }

  function postProcess() {
    $this->beginPostProcess();
    $rows = array();

    $tempColumns = "id int(10)";
    if (CRM_Utils_Array::value('log_action', $this->_params['fields'])) {
      $tempColumns .= ", log_action varchar(64)";
    }
    $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
    if (CRM_Utils_Array::value('altered_contact', $this->_params['fields'])) {
      $tempColumns .= ", altered_contact varchar(128)";
    }
    $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
    if (CRM_Utils_Array::value('display_name', $this->_params['fields'])) {
      $tempColumns .= ", display_name varchar(128)";
    }

    // temp table to hold all altered contact-ids
    $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
    CRM_Core_DAO::executeQuery($sql);

    $logDateClause = $this->dateClause('log_date',
      CRM_Utils_Array::value("log_date_relative", $this->_params),
      CRM_Utils_Array::value("log_date_from", $this->_params),
      CRM_Utils_Array::value("log_date_to", $this->_params),
      CRM_Utils_Type::T_DATE,
      CRM_Utils_Array::value("log_date_from_time", $this->_params),
      CRM_Utils_Array::value("log_date_to_time", $this->_params));
    $logDateClause = $logDateClause ? "AND {$logDateClause}" : NULL;

    $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
    unset($this->_params['log_type_value']);
    if (empty($logTypes)) {
      foreach (array_keys($this->_logTables) as $table) {
        $type = $this->getLogType($table);
        $logTypes[$type] = $type;
      }
    }

    $logTypeTableClause = '(1)';
    if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) {
      $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'],
        $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL);
      unset($this->_params['log_type_table_value']);
    }

    foreach ($this->_logTables as $entity => $detail) {
      if ((in_array($this->getLogType($entity), $logTypes) &&
          CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
        (!in_array($this->getLogType($entity), $logTypes) &&
          CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')
      ) {
        $this->from($entity);
        $sql = $this->buildQuery(FALSE);
        $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
        $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
        CRM_Core_DAO::executeQuery($sql);
      }
    }

    // add computed log_type column so that we can do a group by after that, which will help
    // alterDisplay() counts sync with pager counts
    $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
    $dao = CRM_Core_DAO::executeQuery($sql);
    $replaceWith = array();
    while ($dao->fetch()) {
      $type = $this->getLogType($dao->log_type);
      if (!array_key_exists($type, $replaceWith)) {
        $replaceWith[$type] = array();
      }
      $replaceWith[$type][] = $dao->log_type;
    }
    foreach ($replaceWith as $type => $tables) {
      if (!empty($tables)) {
        $replaceWith[$type] = implode("','", $tables);
      }
    }

    $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
    CRM_Core_DAO::executeQuery($sql);
    foreach ($replaceWith as $type => $in) {
      $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
      CRM_Core_DAO::executeQuery($sql);
    }

    // note the group by columns are same as that used in alterDisplay as $newRows - $key
    $this->limit();
    $sql = "{$this->_select}
FROM civicrm_temp_civireport_logsummary entity_log_civireport
WHERE {$logTypeTableClause}
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
ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}";
    $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
    $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
    $sql = str_replace(array(
      'modified_contact_civireport.',
      'altered_by_contact_civireport.'
    ), 'entity_log_civireport.', $sql);
    $this->buildRows($sql, $rows);

    // format result set.
    $this->formatDisplay($rows);

    // assign variables to templates
    $this->doTemplateAssignment($rows);

    // do print / pdf / instance stuff if needed
    $this->endPostProcess($rows);
  }

  function getLogType($entity) {
    if (CRM_Utils_Array::value('log_type', $this->_logTables[$entity])) {
      return $this->_logTables[$entity]['log_type'];
    }
    $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
    return $logType;
  }

  function getEntityValue($id, $entity, $logDate) {
    if (CRM_Utils_Array::value('bracket_info', $this->_logTables[$entity])) {
      if (CRM_Utils_Array::value('entity_column', $this->_logTables[$entity]['bracket_info'])) {
        $logTable = CRM_Utils_Array::value('table_name', $this->_logTables[$entity]) ? $this->_logTables[$entity]['table_name'] : $entity;
        $sql = "
SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
  FROM `{$this->loggingDB}`.{$logTable}
 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
        $entityID = CRM_Core_DAO::singleValueQuery($sql, array(
          1 => array(
            CRM_Utils_Date::isoToMysql($logDate),
            'Timestamp'
          ),
          2 => array($id, 'Integer')
        ));
      }
      else {
        $entityID = $id;
      }

      // since case_type_id is a varchar field with separator
      if ($entity == 'log_civicrm_case') {
        $entityID = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR, $entityID);
        $entityID = CRM_Utils_Array::value(1, $entityID);
      }

      if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) {
        $sql = "
SELECT {$this->_logTables[$entity]['bracket_info']['column']}
FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
        return CRM_Core_DAO::singleValueQuery($sql, array(
          1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'),
          2 => array($entityID, 'Integer')
        ));
      }
      else {
        if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) {
          return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
        }
      }
    }
    return NULL;
  }

  function getEntityAction($id, $connId, $entity, $oldAction) {
    if (CRM_Utils_Array::value('action_column', $this->_logTables[$entity])) {
      $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
      $newAction = CRM_Core_DAO::singleValueQuery($sql, array(
        1 => array($id, 'Integer'),
        2 => array($connId, 'Integer')
      ));

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