From 5a14305b6dbd9353fc217e2c5f9373d161dab3fa Mon Sep 17 00:00:00 2001 From: eileen Date: Fri, 13 Jul 2018 16:46:32 +1200 Subject: [PATCH] Alter temp report creation to a) use new temp table mechanism and b) add sql to dev tab and c) make temp table cleanup easier in tests --- CRM/Report/Form.php | 21 ++++ CRM/Report/Form/Activity.php | 19 ++- tests/phpunit/api/v3/ReportTemplateTest.php | 125 ++++++++++++++++++++ 3 files changed, 154 insertions(+), 11 deletions(-) diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index d48fe6dac5..89f20ab751 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -1140,6 +1140,27 @@ class CRM_Report_Form extends CRM_Core_Form { } } + /** + * Create a temporary table. + * + * This function creates a table AND adds the details to the developer tab & $this->>temporary tables. + * + * @todo improve presentation on the developer tab since CREATE TEMPORARY is removed. + * + * @param string $identifier + * @param $sql + * @param bool $isTrueTemporary + * Is this a mysql temporary table or temporary in a less technical sense. + * + * @return string + */ + public function createTemporaryTable($identifier, $sql, $isTrueTemporary = TRUE) { + $this->addToDeveloperTab($sql); + $name = CRM_Utils_SQL_TempTable::build()->setUtf8(TRUE)->setDurable($isTrueTemporary)->createWithQuery($sql)->getName(); + $this->temporaryTables[$identifier] = ['temporary' => $isTrueTemporary, 'name' => $name]; + return $name; + } + /** * Add columns to report. */ diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index 5cb371d84d..1e7be33769 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -690,7 +690,7 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { $new_having = ' addtogroup_contact_id'; $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having); $query = "$select -FROM civireport_activity_temp_target tar +FROM {$this->temporaryTables['activity_temp_table']} tar GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $select = 'AS addtogroup_contact_id'; $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query); @@ -773,15 +773,12 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->from(); $this->customDataFrom(); $this->where('target'); - $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target {$this->_databaseAttributes} AS -{$this->_select} {$this->_from} {$this->_where} "; - $this->executeReportQuery($tempQuery); + $tempTableName = $this->createTemporaryTable('activity_temp_table', "{$this->_select} {$this->_from} {$this->_where}"); // 2. add new columns to hold assignee and source results // fixme: add when required $tempQuery = " - ALTER TABLE civireport_activity_temp_target + ALTER TABLE $tempTableName MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128), ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128), ADD COLUMN civicrm_contact_contact_source VARCHAR(128), @@ -801,7 +798,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->customDataFrom(); $this->where('assignee'); $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) + $tempQuery = "INSERT INTO $tempTableName ({$insertCols}) {$this->_select} {$this->_from} {$this->_where}"; $this->executeReportQuery($tempQuery); @@ -813,7 +810,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->customDataFrom(); $this->where('source'); $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols}) + $tempQuery = "INSERT INTO $tempTableName ({$insertCols}) {$this->_select} {$this->_from} {$this->_where}"; $this->executeReportQuery($tempQuery); @@ -828,7 +825,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->orderBy(); foreach ($this->_sections as $alias => $section) { if (!empty($section) && $section['name'] == 'activity_date_time') { - $this->alterSectionHeaderForDateTime('civireport_activity_temp_target', $section['tplField']); + $this->alterSectionHeaderForDateTime($tempTableName, $section['tplField']); } } @@ -845,7 +842,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; } $sql = "{$this->_select} - FROM civireport_activity_temp_target tar + FROM $tempTableName tar INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID} @@ -1079,7 +1076,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases); $query = $this->_select . - ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " . + ", count(DISTINCT civicrm_activity_id) as ct from {$this->temporaryTables['activity_temp_table']} group by " . implode(", ", $sectionAliases); // initialize array of total counts diff --git a/tests/phpunit/api/v3/ReportTemplateTest.php b/tests/phpunit/api/v3/ReportTemplateTest.php index 6f3d8c95fc..7bda266f73 100644 --- a/tests/phpunit/api/v3/ReportTemplateTest.php +++ b/tests/phpunit/api/v3/ReportTemplateTest.php @@ -35,6 +35,8 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase { protected $_apiversion = 3; + protected $contactIDs = []; + /** * Our group reports use an alter so transaction cleanup won't work. * @@ -775,4 +777,127 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase { $this->assertNumberOfContactsInResult(2, $rows, $template); } + /** + * Test activity summary report - requiring all current fields to be output. + */ + public function testActivitySummary() { + $this->createContactsWithActivities(); + $fields = [ + 'contact_source' => '1', + 'contact_assignee' => '1', + 'contact_target' => '1', + 'contact_source_email' => '1', + 'contact_assignee_email' => '1', + 'contact_target_email' => '1', + 'contact_source_phone' => '1', + 'contact_assignee_phone' => '1', + 'contact_target_phone' => '1', + 'activity_type_id' => '1', + 'activity_subject' => '1', + 'activity_date_time' => '1', + 'status_id' => '1', + 'duration' => '1', + 'location' => '1', + 'details' => '1', + 'priority_id' => '1', + 'result' => '1', + 'engagement_level' => '1', + 'address_name' => '1', + 'street_address' => '1', + 'supplemental_address_1' => '1', + 'supplemental_address_2' => '1', + 'supplemental_address_3' => '1', + 'street_number' => '1', + 'street_name' => '1', + 'street_unit' => '1', + 'city' => '1', + 'postal_code' => '1', + 'postal_code_suffix' => '1', + 'country_id' => '1', + 'state_province_id' => '1', + 'county_id' => '1', + ]; + $params = [ + 'fields' => $fields, + 'current_user_op' => 'eq', + 'current_user_value' => '0', + 'include_case_activities_op' => 'eq', + 'include_case_activities_value' => 0, + 'order_bys' => [1 => ['column' => 'activity_date_time', 'order' => 'ASC'], 2 => ['column' => 'activity_type_id', 'order' => 'ASC']], + ]; + + $params['report_id'] = 'Activity'; + + $rows = $this->callAPISuccess('report_template', 'getrows', $params)['values']; + $expected = [ + 'civicrm_contact_contact_source' => 'Łąchowski-Roberts, Anthony', + 'civicrm_contact_contact_assignee' => 'Łąchowski-Roberts, Anthony', + 'civicrm_contact_contact_target' => 'Brzęczysław, Anthony; Łąchowski-Roberts, Anthony', + 'civicrm_contact_contact_source_id' => $this->contactIDs[2], + 'civicrm_contact_contact_assignee_id' => $this->contactIDs[1], + 'civicrm_contact_contact_target_id' => $this->contactIDs[0] . ';' . $this->contactIDs[1], + 'civicrm_email_contact_source_email' => 'anthony_anderson@civicrm.org', + 'civicrm_email_contact_assignee_email' => 'anthony_anderson@civicrm.org', + 'civicrm_email_contact_target_email' => 'anthony_anderson@civicrm.org;anthony_anderson@civicrm.org', + 'civicrm_phone_contact_source_phone' => NULL, + 'civicrm_phone_contact_assignee_phone' => NULL, + 'civicrm_phone_contact_target_phone' => NULL, + 'civicrm_activity_id' => '1', + 'civicrm_activity_source_record_id' => NULL, + 'civicrm_activity_activity_type_id' => 'Meeting', + 'civicrm_activity_activity_subject' => 'Very secret meeting', + 'civicrm_activity_activity_date_time' => '2018-07-16 03:42:32', + 'civicrm_activity_status_id' => 'Scheduled', + 'civicrm_activity_duration' => '120', + 'civicrm_activity_location' => 'Pennsylvania', + 'civicrm_activity_details' => 'a test activity', + 'civicrm_activity_priority_id' => 'Normal', + 'civicrm_address_address_name' => NULL, + 'civicrm_address_street_address' => NULL, + 'civicrm_address_supplemental_address_1' => NULL, + 'civicrm_address_supplemental_address_2' => NULL, + 'civicrm_address_supplemental_address_3' => NULL, + 'civicrm_address_street_number' => NULL, + 'civicrm_address_street_name' => NULL, + 'civicrm_address_street_unit' => NULL, + 'civicrm_address_city' => NULL, + 'civicrm_address_postal_code' => NULL, + 'civicrm_address_postal_code_suffix' => NULL, + 'civicrm_address_country_id' => NULL, + 'civicrm_address_state_province_id' => NULL, + 'civicrm_address_county_id' => NULL, + 'civicrm_contact_contact_source_link' => '/index.php?q=civicrm/contact/view&reset=1&cid=' . $this->contactIDs[2], + 'civicrm_contact_contact_source_hover' => 'View Contact Summary for this Contact', + 'civicrm_activity_activity_type_id_hover' => 'View Activity Record', + 'class' => 'status-overdue', + ]; + $row = $rows[0]; + // This link is not relative - skip for now + unset($row['civicrm_activity_activity_type_id_link']); + + $this->assertEquals($expected, $row); + } + + /** + * Set up some activity data..... use some chars that challenge our utf handling. + */ + public function createContactsWithActivities() { + $this->contactIDs[] = $this->individualCreate(['last_name' => 'Brzęczysław']); + $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']); + $this->contactIDs[] = $this->individualCreate(['last_name' => 'Łąchowski-Roberts']); + + $this->callAPISuccess('Activity', 'create', [ + 'subject' => 'Very secret meeting', + 'activity_date_time' => '2018-07-16 03:42:32', + 'duration' => 120, + 'location' => 'Pennsylvania', + 'details' => 'a test activity', + 'status_id' => 1, + 'activity_type_id' => 'Meeting', + 'source_contact_id' => $this->contactIDs[2], + 'target_contact_id' => array($this->contactIDs[0], $this->contactIDs[1]), + 'assignee_contact_id' => $this->contactIDs[1], + ]); + } + } -- 2.25.1