From 028df0a9a511774b1fe2d1a98f1c3595fca5cabd Mon Sep 17 00:00:00 2001 From: Kurund Jalmi Date: Sat, 22 Aug 2015 19:22:48 +0530 Subject: [PATCH] CRM-16719: fixed duration total --- CRM/Report/Form/ActivitySummary.php | 231 ++++++++++++++++++++-------- 1 file changed, 166 insertions(+), 65 deletions(-) diff --git a/CRM/Report/Form/ActivitySummary.php b/CRM/Report/Form/ActivitySummary.php index 8256629892..b9e0ead547 100644 --- a/CRM/Report/Form/ActivitySummary.php +++ b/CRM/Report/Form/ActivitySummary.php @@ -110,11 +110,11 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { 'type' => CRM_Utils_Type::T_STRING, ), 'duration' => array( - 'title' => 'Duration', + 'title' => ts('Duration'), 'default' => TRUE, ), 'id' => array( - 'title' => 'Total Activities', + 'title' => ts('Total Activities'), 'required' => TRUE, 'statistics' => array( 'count' => ts('Count'), @@ -275,62 +275,72 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $this->_select = "SELECT " . implode(', ', $select) . " "; } - public function from() { + public function from($durationMode = FALSE) { $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts); $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts); $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts); - $this->_from = " - FROM civicrm_activity {$this->_aliases['civicrm_activity']} - - LEFT JOIN civicrm_activity_contact target_activity - ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND - target_activity.record_type_id = {$targetID} - LEFT JOIN civicrm_activity_contact assignment_activity - ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND - assignment_activity.record_type_id = {$assigneeID} - LEFT JOIN civicrm_activity_contact source_activity - ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND - source_activity.record_type_id = {$sourceID} - LEFT JOIN civicrm_contact contact_civireport - ON target_activity.contact_id = contact_civireport.id - LEFT JOIN civicrm_contact civicrm_contact_assignee - ON assignment_activity.contact_id = civicrm_contact_assignee.id - LEFT JOIN civicrm_contact civicrm_contact_source - ON source_activity.contact_id = civicrm_contact_source.id - {$this->_aclFrom} - LEFT JOIN civicrm_option_value - ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value ) - LEFT JOIN civicrm_option_group - ON civicrm_option_group.id = civicrm_option_value.option_group_id - LEFT JOIN civicrm_case_activity - ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id - LEFT JOIN civicrm_case - ON civicrm_case_activity.case_id = civicrm_case.id - LEFT JOIN civicrm_case_contact - ON civicrm_case_contact.case_id = civicrm_case.id "; - - if ($this->_emailField) { - $this->_from .= " - LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND - {$this->_aliases['civicrm_email']}.is_primary = 1 "; - } + if (!$durationMode) { + $this->_from = " + FROM civicrm_activity {$this->_aliases['civicrm_activity']} + + LEFT JOIN civicrm_activity_contact target_activity + ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND + target_activity.record_type_id = {$targetID} + LEFT JOIN civicrm_activity_contact assignment_activity + ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND + assignment_activity.record_type_id = {$assigneeID} + LEFT JOIN civicrm_activity_contact source_activity + ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND + source_activity.record_type_id = {$sourceID} + LEFT JOIN civicrm_contact contact_civireport + ON target_activity.contact_id = contact_civireport.id + LEFT JOIN civicrm_contact civicrm_contact_assignee + ON assignment_activity.contact_id = civicrm_contact_assignee.id + LEFT JOIN civicrm_contact civicrm_contact_source + ON source_activity.contact_id = civicrm_contact_source.id + {$this->_aclFrom} + LEFT JOIN civicrm_option_value + ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value ) + LEFT JOIN civicrm_option_group + ON civicrm_option_group.id = civicrm_option_value.option_group_id + LEFT JOIN civicrm_case_activity + ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id + LEFT JOIN civicrm_case + ON civicrm_case_activity.case_id = civicrm_case.id + LEFT JOIN civicrm_case_contact + ON civicrm_case_contact.case_id = civicrm_case.id "; + + if ($this->_emailField) { + $this->_from .= " + LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND + {$this->_aliases['civicrm_email']}.is_primary = 1 "; + } - if ($this->_phoneField) { - $this->_from .= " - LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} - ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND - {$this->_aliases['civicrm_phone']}.is_primary = 1 "; + if ($this->_phoneField) { + $this->_from .= " + LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} + ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND + {$this->_aliases['civicrm_phone']}.is_primary = 1 "; + } + } + else { + $this->_from = " + FROM civicrm_activity {$this->_aliases['civicrm_activity']} {$this->_aclFrom} "; } } - public function where() { - $this->_where = " WHERE civicrm_option_group.name = 'activity_type' AND - {$this->_aliases['civicrm_activity']}.is_test = 0 AND - {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND - {$this->_aliases['civicrm_activity']}.is_current_revision = 1"; + public function where($durationMode = FALSE) { + $optionGroupClause = ''; + if (!$durationMode) { + $optionGroupClause = 'civicrm_option_group.name = "activity_type" AND '; + } + $this->_where = " WHERE {$optionGroupClause} + {$this->_aliases['civicrm_activity']}.is_test = 0 AND + {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND + {$this->_aliases['civicrm_activity']}.is_current_revision = 1"; $clauses = array(); foreach ($this->_columns as $tableName => $table) { @@ -371,7 +381,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $this->_where .= " AND " . implode(' AND ', $clauses); } - if ($this->_aclWhere) { + if ($this->_aclWhere && !$durationMode) { $this->_where .= " AND ({$this->_aclWhere} OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0)"; } } @@ -446,7 +456,96 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { public function postProcess() { // get the acl clauses built before we assemble the query $this->buildACLClause($this->_aliases['civicrm_contact']); - parent::postProcess(); + + // get ready with post process params + $this->beginPostProcess(); + + // build query + $sql = $this->buildQuery(); + + // main sql statement + $this->select(); + $this->from(); + $this->customDataFrom(); + $this->where(); + $this->groupBy(); + $this->orderBy(); + + // order_by columns not selected for display need to be included in SELECT + $unselectedSectionColumns = $this->unselectedSectionColumns(); + foreach ($unselectedSectionColumns as $alias => $section) { + $this->_select .= ", {$section['dbAlias']} as {$alias}"; + } + + if (!empty($applyLimit) && empty($this->_params['charts'])) { + $this->limit(); + } + CRM_Utils_Hook::alterReportVar('sql', $this, $this); + + // store the duration count in temp table + $this->_tempTableName = CRM_Core_DAO::createTempTableName('civicrm_activity'); + + // build temporary table column names base on column headers of result + $dbColumns = array(); + foreach ($this->_columnHeaders as $fieldName => $dontCare) { + $dbColumns[] = $fieldName . ' VARCHAR(128)'; + } + + // create temp table to store main result + //$tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} ( " . implode(',', $dbColumns) . ')'; + $tempQuery = "CREATE TABLE {$this->_tempTableName} ( + id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id))'; + CRM_Core_DAO::executeQuery($tempQuery); + + // build main report query + $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; + + // store the result in temporary table + $insertCols = ''; + $insertQuery = "INSERT INTO {$this->_tempTableName} ( " . implode(',', array_keys($this->_columnHeaders)) . " ) +{$sql}"; + CRM_Core_DAO::executeQuery($insertQuery); + + // now build the query for duration sum + $this->from(TRUE); + $this->customDataFrom(); + $this->where(TRUE); + + // build the query to calulate duration sum + $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; + + // create temp table to store duration + //$tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} ( " . implode(',', $dbColumns) . ')'; + $this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity'); + $tempQuery = "CREATE TABLE {$this->_tempDurationSumTableName} ( + id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id))"; + CRM_Core_DAO::executeQuery($tempQuery); + + // store the result in temporary table + $insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total) + {$sql}"; + CRM_Core_DAO::executeQuery($insertQuery); + + // build array of result based on column headers. This method also allows + // modifying column headers before using it to build result set i.e $rows. + $rows = array(); + $query = "SELECT {$this->_tempTableName}.*, {$this->_tempDurationSumTableName}.civicrm_activity_duration_total + FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName} + ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)"; + + // finally add duration total to column headers + $this->_columnHeaders['civicrm_activity_duration_total'] = array('no_display' => 1); + + $this->buildRows($query, $rows); + + // format result set. + $this->formatDisplay($rows); + + // assign variables to templates + $this->doTemplateAssignment($rows); + + // do print / pdf / instance stuff if needed + $this->endPostProcess($rows); } /** @@ -458,24 +557,26 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $statistics = parent::statistics($rows); $totalType = $totalActivity = $totalDuration = 0; - $query = "SELECT SUM(activity_civireport.duration) - FROM civicrm_activity activity_civireport - {$this->_where}"; - - $actSQL = "SELECT - COUNT(DISTINCT {$this->_aliases['civicrm_activity']}.activity_type_id ) as civicrm_activity_activity_type_id_count, - COUNT(DISTINCT {$this->_aliases['civicrm_activity']}.id ) as civicrm_activity_activity_id_count, - ($query) as civicrm_activity_activity_duration - {$this->_from} {$this->_where} {$this->_groupBy}"; + $query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id, + {$this->_tempTableName}.civicrm_activity_id_count, + {$this->_tempDurationSumTableName}.civicrm_activity_duration_total + FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName} + ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)"; - $actDAO = CRM_Core_DAO::executeQuery($actSQL); + $actDAO = CRM_Core_DAO::executeQuery($query); + $activityTypesCount = array(); while ($actDAO->fetch()) { - $totalType += $actDAO->civicrm_activity_activity_type_id_count; - $totalActivity += $actDAO->civicrm_activity_activity_id_count; - $totalDuration += $actDAO->civicrm_activity_activity_duration; + if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) { + $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id; + } + + $totalActivity += $actDAO->civicrm_activity_id_count; + $totalDuration += $actDAO->civicrm_activity_duration_total; } + $totalType = count($activityTypesCount); + $statistics['counts']['type'] = array( 'title' => ts('Total Types'), 'value' => $totalType, @@ -612,7 +713,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { if (array_key_exists('civicrm_activity_duration', $row)) { if ($value = $row['civicrm_activity_duration']) { - $rows[$rowNum]['civicrm_activity_duration'] += $rows[$rowNum]['civicrm_activity_duration']; + $rows[$rowNum]['civicrm_activity_duration'] = $rows[$rowNum]['civicrm_activity_duration_total']; $entryFound = TRUE; } } -- 2.25.1