From cbb9666edf94e69e5c0535140e0819cb7548085a Mon Sep 17 00:00:00 2001 From: yashodha Date: Mon, 29 Jul 2013 11:00:02 +0530 Subject: [PATCH] worked on CRM-12849 --- CRM/Logging/ReportSummary.php | 289 +++++++++++++++++++++------------- 1 file changed, 180 insertions(+), 109 deletions(-) diff --git a/CRM/Logging/ReportSummary.php b/CRM/Logging/ReportSummary.php index 251297967a..203f336b2e 100644 --- a/CRM/Logging/ReportSummary.php +++ b/CRM/Logging/ReportSummary.php @@ -40,78 +40,126 @@ class CRM_Logging_ReportSummary extends CRM_Report_Form { protected $loggingDB; function __construct() { + $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'), - ), - // FIXME: activity table structure has changed. Below activity code needs to be fixed based on new structure - CRM-12274 - /* 'log_civicrm_activity_for_target' => */ - /* array( 'fk' => 'target_contact_id', */ - /* 'table_name' => 'log_civicrm_activity', */ - /* 'joins' => array('table' => 'log_civicrm_activity_target', 'join' => 'entity_log_civireport.id = fk_table.activity_id'), */ - /* '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' => 'assignee_contact_id', */ - /* 'table_name' => 'log_civicrm_activity', */ - /* 'joins' => array('table' => 'log_civicrm_activity_assignment', 'join' => 'entity_log_civireport.id = fk_table.activity_id'), */ - /* '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' => 'source_contact_id', */ - /* 'table_name' => 'log_civicrm_activity', */ - /* '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)), - ), - ); + 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) + ), + ), + ); // don’t display the ‘Add these Contacts to Group’ button $this->_add2groupSupported = FALSE; @@ -180,18 +228,18 @@ class CRM_Logging_ReportSummary extends CRM_Report_Form { 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_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; + 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 ) { + if (empty($logTypes)) { + foreach (array_keys($this->_logTables) as $table) { $type = $this->getLogType($table); $logTypes[$type] = $type; } @@ -199,18 +247,19 @@ class CRM_Logging_ReportSummary extends CRM_Report_Form { $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); + $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 ) { + + foreach ($this->_logTables as $entity => $detail) { if ((in_array($this->getLogType($entity), $logTypes) && - CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') || + 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); + 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); @@ -222,10 +271,11 @@ class CRM_Logging_ReportSummary extends CRM_Report_Form { $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary"; $dao = CRM_Core_DAO::executeQuery($sql); $replaceWith = array(); - while($dao->fetch()){ + while ($dao->fetch()) { $type = $this->getLogType($dao->log_type); - if (!array_key_exists($type,$replaceWith)) + if (!array_key_exists($type, $replaceWith)) { $replaceWith[$type] = array(); + } $replaceWith[$type][] = $dao->log_type; } foreach ($replaceWith as $type => $tables) { @@ -248,9 +298,12 @@ 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); + $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. @@ -263,7 +316,7 @@ ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}"; $this->endPostProcess($rows); } - function getLogType( $entity ) { + function getLogType($entity) { if (CRM_Utils_Array::value('log_type', $this->_logTables[$entity])) { return $this->_logTables[$entity]['log_type']; } @@ -271,7 +324,7 @@ ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}"; return $logType; } - function getEntityValue( $id, $entity, $logDate ) { + 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; @@ -279,14 +332,21 @@ ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}"; 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 = 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 = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR, $entityID); $entityID = CRM_Utils_Array::value(1, $entityID); } @@ -295,29 +355,40 @@ SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']} 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 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; + return NULL; } - function getEntityAction( $id, $connId, $entity, $oldAction ) { + 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'))); + $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; + case 'log_civicrm_group_contact': + if ($oldAction !== 'Update') { + $newAction = $oldAction; + } + if ($oldAction == 'Insert') { + $newAction = 'Added'; + } + break; } return $newAction; } - return null; + return NULL; } } -- 2.25.1