From d5c61232323421d1f4ef9e2884e0934befa6931a Mon Sep 17 00:00:00 2001 From: Kurund Jalmi Date: Tue, 1 Sep 2015 20:30:17 -0700 Subject: [PATCH] added form rule to allow add to group action only if contact fields are selected --- CRM/Report/Form/ActivitySummary.php | 253 ++++++++++------------------ 1 file changed, 85 insertions(+), 168 deletions(-) diff --git a/CRM/Report/Form/ActivitySummary.php b/CRM/Report/Form/ActivitySummary.php index 9c2c705c6b..dc76938495 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' => ts('Duration'), + 'title' => 'Duration', 'default' => TRUE, ), 'id' => array( - 'title' => ts('Total Activities'), + 'title' => 'Total Activities', 'required' => TRUE, 'statistics' => array( 'count' => ts('Count'), @@ -127,7 +127,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { ), 'activity_type_id' => array( 'title' => ts('Activity Type'), - 'default' => 0, + 'default' => FALSE, 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE), ), @@ -275,72 +275,62 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $this->_select = "SELECT " . implode(', ', $select) . " "; } - public function from($durationMode = FALSE) { + public function from() { $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); - 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 "; - } + $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 "; } - else { - $this->_from = " - FROM civicrm_activity {$this->_aliases['civicrm_activity']} {$this->_aclFrom} "; + + 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 "; } } - 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"; + 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"; $clauses = array(); foreach ($this->_columns as $tableName => $table) { @@ -381,15 +371,15 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $this->_where .= " AND " . implode(' AND ', $clauses); } - if ($this->_aclWhere && !$durationMode) { + if ($this->_aclWhere) { $this->_where .= " AND ({$this->_aclWhere} OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0)"; } } public function groupBy() { $this->_groupBy = array(); - if (!empty($this->_params['group_bys']) && - is_array($this->_params['group_bys'])) { + if (!empty($this->_params['group_bys'] && + is_array($this->_params['group_bys']))) { foreach ($this->_columns as $tableName => $table) { if (array_key_exists('group_bys', $table)) { foreach ($table['group_bys'] as $fieldName => $field) { @@ -421,7 +411,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy); } else { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id "; + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id "; } } @@ -450,100 +440,29 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } } } + + // don't allow add to group action unless contact fields are selected. + if (isset($fields['_qf_ActivitySummary_submit_group'])) { + $contactFieldSelected = FALSE; + foreach ($fields['fields'] as $fieldName => $val) { + if (in_array($fieldName, $contactFields)) { + $contactFieldSelected = TRUE; + break; + } + } + + if (!$contactFieldSelected) { + $errors['fields'] = ts('You cannot use "Add Contacts to Group" action unless contacts fields are selected.'); + } + } + return $errors; } public function postProcess() { // get the acl clauses built before we assemble the query $this->buildACLClause($this->_aliases['civicrm_contact']); - - // 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} ( - 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 - $this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity'); - $tempQuery = "CREATE TEMPORARY 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); + parent::postProcess(); } /** @@ -555,26 +474,24 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $statistics = parent::statistics($rows); $totalType = $totalActivity = $totalDuration = 0; - $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)"; + $query = "SELECT SUM(activity_civireport.duration) + FROM civicrm_activity activity_civireport + {$this->_where}"; - $actDAO = CRM_Core_DAO::executeQuery($query); + $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}"; - $activityTypesCount = array(); - while ($actDAO->fetch()) { - if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) { - $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id; - } + $actDAO = CRM_Core_DAO::executeQuery($actSQL); - $totalActivity += $actDAO->civicrm_activity_id_count; - $totalDuration += $actDAO->civicrm_activity_duration_total; + while ($actDAO->fetch()) { + $totalType += $actDAO->civicrm_activity_activity_type_id_count; + $totalActivity += $actDAO->civicrm_activity_activity_id_count; + $totalDuration += $actDAO->civicrm_activity_activity_duration; } - $totalType = count($activityTypesCount); - $statistics['counts']['type'] = array( 'title' => ts('Total Types'), 'value' => $totalType, @@ -711,7 +628,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_total']; + $rows[$rowNum]['civicrm_activity_duration'] += $rows[$rowNum]['civicrm_activity_duration']; $entryFound = TRUE; } } -- 2.25.1