PHP CRM_Core_DAO::createTempTableName方法代码示例

 /**
  * @param $sqlColumns
  *
  * @return string
  */
 public static function createTempTable(&$sqlColumns)
 {
     //creating a temporary table for the search result that need be exported
     $exportTempTable = CRM_Core_DAO::createTempTableName('civicrm_export', TRUE);
     // also create the sql table
     $sql = "DROP TABLE IF EXISTS {$exportTempTable}";
     CRM_Core_DAO::executeQuery($sql);
     $sql = "\nCREATE TABLE {$exportTempTable} (\n     id int unsigned NOT NULL AUTO_INCREMENT,\n";
     $sql .= implode(",\n", array_values($sqlColumns));
     $sql .= ",\n  PRIMARY KEY ( id )\n";
     // add indexes for street_address and household_name if present
     $addIndices = array('street_address', 'household_name', 'civicrm_primary_id');
     foreach ($addIndices as $index) {
         if (isset($sqlColumns[$index])) {
             $sql .= ",\n  INDEX index_{$index}( {$index} )\n";
         }
     }
     $sql .= "\n) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci\n";
     CRM_Core_DAO::executeQuery($sql);
     return $exportTempTable;
 }

 function filterRelatedContacts(&$from, &$where, &$having)
 {
     static $_rTypeProcessed = NULL;
     static $_rTypeFrom = NULL;
     static $_rTypeWhere = NULL;
     if (!$_rTypeProcessed) {
         $_rTypeProcessed = TRUE;
         // create temp table with contact ids
         $tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
         $sql = "CREATE TEMPORARY TABLE {$tableName} ( contact_id int primary key) ENGINE=HEAP";
         CRM_Core_DAO::executeQuery($sql);
         $sql = "\nREPLACE INTO {$tableName} ( contact_id )\nSELECT contact_a.id\n       {$from}\n       {$where}\n       {$having}\n";
         CRM_Core_DAO::executeQuery($sql);
         $qillMessage = ts('Contacts with a Relationship Type of: ');
         $rTypes = CRM_Core_PseudoConstant::relationshipType();
         if (is_numeric($this->_displayRelationshipType)) {
             $relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
             $_rTypeFrom = "\nINNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )\nINNER JOIN {$tableName} transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )\n";
             $_rTypeWhere = "\nWHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}\nAND   displayRelType.is_active = 1\n";
         } else {
             list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
             if ($dirOne == 'a') {
                 $relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
                 $_rTypeFrom .= "\nINNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )\nINNER JOIN {$tableName} transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )\n";
             } else {
                 $relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
                 $_rTypeFrom .= "\nINNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )\nINNER JOIN {$tableName} transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )\n";
             }
             $_rTypeWhere = "\nWHERE displayRelType.relationship_type_id = {$relType}\nAND   displayRelType.is_active = 1\n";
         }
         $this->_qill[0][] = $qillMessage . "'" . $relationshipTypeLabel . "'";
     }
     if (strpos($from, $_rTypeFrom) === FALSE) {
         // lets replace all the INNER JOIN's in the $from so we dont exclude other data
         // this happens when we have an event_type in the quert (CRM-7969)
         $from = str_replace("INNER JOIN", "LEFT JOIN", $from);
         $from .= $_rTypeFrom;
         $where = $_rTypeWhere;
     }
     $having = NULL;
 }

 /**
  * @param $rows
  *
  * @return array
  */
 public function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
     $select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,\n               CASE\n                 WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL\n                 THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount\n                 ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount\n               END as amount\n";
     $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS\n                  {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
     CRM_Core_DAO::executeQuery($tempQuery);
     $sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency\n            FROM {$tempTableName}\n            GROUP BY currency";
     $dao = CRM_Core_DAO::executeQuery($sql);
     $amount = $avg = array();
     while ($dao->fetch()) {
         $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
         $avg[] = CRM_Utils_Money::format(round($dao->amount / $dao->count, 2), $dao->currency);
     }
     $statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
     $statistics['counts']['avg'] = array('value' => implode(', ', $avg), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
     return $statistics;
 }

 static function preProcessCommon(&$form, $useTable = false)
 {
     $form->_contactIds = array();
     $form->_contactTypes = array();
     // get the submitted values of the search form
     // we'll need to get fv from either search or adv search in the future
     $fragment = 'search';
     if ($form->_action == CRM_Core_Action::ADVANCED) {
         $values = $form->controller->exportValues('Advanced');
         $fragment .= '/advanced';
     } else {
         if ($form->_action == CRM_Core_Action::PROFILE) {
             $values = $form->controller->exportValues('Builder');
             $fragment .= '/builder';
         } else {
             if ($form->_action == CRM_Core_Action::COPY) {
                 $values = $form->controller->exportValues('Custom');
                 $fragment .= '/custom';
             } else {
                 $values = $form->controller->exportValues('Basic');
             }
         }
     }
     //set the user context for redirection of task actions
     $qfKey = CRM_Utils_Request::retrieve('qfKey', 'String', $form);
     require_once 'CRM/Utils/Rule.php';
     $urlParams = 'force=1';
     if (CRM_Utils_Rule::qfKey($qfKey)) {
         $urlParams .= "&qfKey={$qfKey}";
     }
     $url = CRM_Utils_System::url('civicrm/contact/' . $fragment, $urlParams);
     $session = CRM_Core_Session::singleton();
     $session->replaceUserContext($url);
     require_once 'CRM/Contact/Task.php';
     $form->_task = CRM_Utils_Array::value('task', $values);
     $crmContactTaskTasks = CRM_Contact_Task::taskTitles();
     $form->assign('taskName', CRM_Utils_Array::value($form->_task, $crmContactTaskTasks));
     if ($useTable) {
         $form->_componentTable = CRM_Core_DAO::createTempTableName('civicrm_task_action', false);
         $sql = " DROP TABLE IF EXISTS {$form->_componentTable}";
         CRM_Core_DAO::executeQuery($sql);
         $sql = "CREATE TABLE {$form->_componentTable} ( contact_id int primary key) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
         CRM_Core_DAO::executeQuery($sql);
     }
     // all contacts or action = save a search
     if (CRM_Utils_Array::value('radio_ts', $values) == 'ts_all' || $form->_task == CRM_Contact_Task::SAVE_SEARCH) {
         // need to perform action on all contacts
         // fire the query again and get the contact id's + display name
         $sortID = null;
         if ($form->get(CRM_Utils_Sort::SORT_ID)) {
             $sortID = CRM_Utils_Sort::sortIDValue($form->get(CRM_Utils_Sort::SORT_ID), $form->get(CRM_Utils_Sort::SORT_DIRECTION));
         }
         $selectorName = $form->controller->selectorName();
         require_once str_replace('_', DIRECTORY_SEPARATOR, $selectorName) . '.php';
         $fv = $form->get('formValues');
         $customClass = $form->get('customSearchClass');
         require_once "CRM/Core/BAO/Mapping.php";
         $returnProperties = CRM_Core_BAO_Mapping::returnProperties($values);
         eval('$selector   = new ' . $selectorName . '( $customClass, $fv, null, $returnProperties ); ');
         $params = $form->get('queryParams');
         // fix for CRM-5165
         $sortByCharacter = $form->get('sortByCharacter');
         if ($sortByCharacter && $sortByCharacter != 1) {
             $params[] = array('sortByCharacter', '=', $sortByCharacter, 0, 0);
         }
         $dao =& $selector->contactIDQuery($params, $form->_action, $sortID);
         $form->_contactIds = array();
         if ($useTable) {
             $count = 0;
             $insertString = array();
             while ($dao->fetch()) {
                 $count++;
                 $insertString[] = " ( {$dao->contact_id} ) ";
                 if ($count % 200 == 0) {
                     $string = implode(',', $insertString);
                     $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}";
                     CRM_Core_DAO::executeQuery($sql);
                     $insertString = array();
                 }
             }
             if (!empty($insertString)) {
                 $string = implode(',', $insertString);
                 $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}";
                 CRM_Core_DAO::executeQuery($sql);
             }
             $dao->free();
         } else {
             // filter duplicates here
             // CRM-7058
             // might be better to do this in the query, but that logic is a bit complex
             // and it decides when to use distinct based on input criteria, which needs
             // to be fixed and optimized.
             $alreadySeen = array();
             while ($dao->fetch()) {
                 if (!array_key_exists($dao->contact_id, $alreadySeen)) {
                     $form->_contactIds[] = $dao->contact_id;
                     $alreadySeen[$dao->contact_id] = 1;
                 }
             }
             unset($alreadySeen);
//.........这里部分代码省略.........

 /**
  * @return array
  */
 public static function deleteInvalidPairs()
 {
     require_once 'CRM/Member/PseudoConstant.php';
     require_once 'CRM/Contribute/PseudoConstant.php';
     $processedRecords = array();
     $tempTableName1 = CRM_Core_DAO::createTempTableName();
     // 1. collect all duplicates
     $sql = "\n  CREATE TEMPORARY TABLE {$tempTableName1} SELECT mp.id as payment_id, mp.contribution_id, mp.membership_id, mem.membership_type_id, mem.start_date, mem.end_date, mem.status_id, mem.contact_id, con.contribution_status_id\n  FROM civicrm_membership_payment mp\n  INNER JOIN ( SELECT cmp.contribution_id\n                FROM civicrm_membership_payment cmp\n                LEFT JOIN civicrm_line_item cli ON cmp.contribution_id=cli.entity_id and cli.entity_table = 'civicrm_contribution'\n                WHERE cli.entity_id IS NULL\n                GROUP BY cmp.contribution_id\n                HAVING COUNT(cmp.membership_id) > 1) submp ON submp.contribution_id = mp.contribution_id\n  INNER JOIN civicrm_membership mem ON mem.id = mp.membership_id\n  INNER JOIN civicrm_contribution con ON con.id = mp.contribution_id\n  ORDER BY mp.contribution_id, mp.membership_id";
     $dao = CRM_Core_DAO::executeQuery($sql);
     $tempTableName2 = CRM_Core_DAO::createTempTableName();
     // 2. collect all records that are going to be retained
     $sql = "\n  CREATE TEMPORARY TABLE {$tempTableName2}\n  SELECT MAX(payment_id) as payment_id FROM {$tempTableName1} GROUP BY contribution_id HAVING COUNT(*) > 1";
     CRM_Core_DAO::executeQuery($sql);
     // 3. do the un-linking
     $sql = "\n  DELETE cmp.*\n  FROM   civicrm_membership_payment cmp\n  INNER JOIN {$tempTableName1} temp1 ON temp1.payment_id = cmp.id\n  LEFT JOIN  {$tempTableName2} temp2 ON temp1.payment_id = temp2.payment_id\n  WHERE temp2.payment_id IS NULL";
     CRM_Core_DAO::executeQuery($sql);
     // 4. show all records that were Processed, i.e Retained vs Un-linked
     $sql = "\n  SELECT temp1.contact_id, temp1.contribution_id, temp1.contribution_status_id, temp1.membership_id, temp1.membership_type_id, temp1.start_date, temp1.end_date, temp1.status_id, temp2.payment_id as retain_id\n  FROM {$tempTableName1} temp1\n  LEFT JOIN  {$tempTableName2} temp2 ON temp1.payment_id = temp2.payment_id";
     $dao = CRM_Core_DAO::executeQuery($sql);
     if ($dao->N) {
         $membershipType = CRM_Member_PseudoConstant::membershipType();
         $membershipStatus = CRM_Member_PseudoConstant::membershipStatus();
         $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
         while ($dao->fetch()) {
             $status = $dao->retain_id ? 'Retained' : 'Un-linked';
             $memType = CRM_Utils_Array::value($dao->membership_type_id, $membershipType);
             $memStatus = CRM_Utils_Array::value($dao->status_id, $membershipStatus);
             $contribStatus = CRM_Utils_Array::value($dao->contribution_status_id, $contributionStatus);
             $processedRecords[] = array($dao->contact_id, $dao->contribution_id, $contribStatus, $dao->membership_id, $memType, $dao->start_date, $dao->end_date, $memStatus, $status);
         }
     }
     if (!empty($processedRecords)) {
         CRM_Core_Error::debug_log_message("deleteInvalidPairs() - The following records have been processed. Membership records with action:");
         CRM_Core_Error::debug_log_message("Contact ID, ContributionID, Contribution Status, MembershipID, Membership Type, Start Date, End Date, Membership Status, Action");
         foreach ($processedRecords as $record) {
             CRM_Core_Error::debug_log_message(implode(', ', $record));
         }
     } else {
         CRM_Core_Error::debug_log_message("deleteInvalidPairs() - Could not find any records to process.");
     }
     return $processedRecords;
 }

 /**
  * Common pre-processing function.
  *
  * @param CRM_Core_Form $form
  * @param bool $useTable
  */
 public static function preProcessCommon(&$form, $useTable = FALSE)
 {
     $form->_contactIds = array();
     $form->_contactTypes = array();
     // get the submitted values of the search form
     // we'll need to get fv from either search or adv search in the future
     $fragment = 'search';
     if ($form->_action == CRM_Core_Action::ADVANCED) {
         self::$_searchFormValues = $form->controller->exportValues('Advanced');
         $fragment .= '/advanced';
     } elseif ($form->_action == CRM_Core_Action::PROFILE) {
         self::$_searchFormValues = $form->controller->exportValues('Builder');
         $fragment .= '/builder';
     } elseif ($form->_action == CRM_Core_Action::COPY) {
         self::$_searchFormValues = $form->controller->exportValues('Custom');
         $fragment .= '/custom';
     } else {
         self::$_searchFormValues = $form->controller->exportValues('Basic');
     }
     //set the user context for redirection of task actions
     $qfKey = CRM_Utils_Request::retrieve('qfKey', 'String', $form);
     $urlParams = 'force=1';
     if (CRM_Utils_Rule::qfKey($qfKey)) {
         $urlParams .= "&qfKey={$qfKey}";
     }
     $cacheKey = "civicrm search {$qfKey}";
     $url = CRM_Utils_System::url('civicrm/contact/' . $fragment, $urlParams);
     $session = CRM_Core_Session::singleton();
     $session->replaceUserContext($url);
     $form->_task = CRM_Utils_Array::value('task', self::$_searchFormValues);
     $crmContactTaskTasks = CRM_Contact_Task::taskTitles();
     $form->assign('taskName', CRM_Utils_Array::value($form->_task, $crmContactTaskTasks));
     if ($useTable) {
         $form->_componentTable = CRM_Core_DAO::createTempTableName('civicrm_task_action', TRUE, $qfKey);
         $sql = " DROP TABLE IF EXISTS {$form->_componentTable}";
         CRM_Core_DAO::executeQuery($sql);
         $sql = "CREATE TABLE {$form->_componentTable} ( contact_id int primary key) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
         CRM_Core_DAO::executeQuery($sql);
     }
     // all contacts or action = save a search
     if (CRM_Utils_Array::value('radio_ts', self::$_searchFormValues) == 'ts_all' || $form->_task == CRM_Contact_Task::SAVE_SEARCH) {
         $sortByCharacter = $form->get('sortByCharacter');
         $cacheKey = $sortByCharacter && $sortByCharacter != 'all' ? "{$cacheKey}_alphabet" : $cacheKey;
         // since we don't store all contacts in prevnextcache, when user selects "all" use query to retrieve contacts
         // rather than prevnext cache table for most of the task actions except export where we rebuild query to fetch
         // final result set
         if ($useTable) {
             $allCids = CRM_Core_BAO_PrevNextCache::getSelection($cacheKey, "getall");
         } else {
             $allCids[$cacheKey] = $form->getContactIds();
         }
         $form->_contactIds = array();
         if ($useTable) {
             $count = 0;
             $insertString = array();
             foreach ($allCids[$cacheKey] as $cid => $ignore) {
                 $count++;
                 $insertString[] = " ( {$cid} ) ";
                 if ($count % 200 == 0) {
                     $string = implode(',', $insertString);
                     $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}";
                     CRM_Core_DAO::executeQuery($sql);
                     $insertString = array();
                 }
             }
             if (!empty($insertString)) {
                 $string = implode(',', $insertString);
                 $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}";
                 CRM_Core_DAO::executeQuery($sql);
             }
         } else {
             // filter duplicates here
             // CRM-7058
             // might be better to do this in the query, but that logic is a bit complex
             // and it decides when to use distinct based on input criteria, which needs
             // to be fixed and optimized.
             foreach ($allCids[$cacheKey] as $cid => $ignore) {
                 $form->_contactIds[] = $cid;
             }
         }
     } elseif (CRM_Utils_Array::value('radio_ts', self::$_searchFormValues) == 'ts_sel') {
         // selected contacts only
         // need to perform action on only selected contacts
         $insertString = array();
         // refire sql in case of custom seach
         if ($form->_action == CRM_Core_Action::COPY) {
             // selected contacts only
             // need to perform action on only selected contacts
             foreach (self::$_searchFormValues as $name => $value) {
                 if (substr($name, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) {
                     $contactID = substr($name, CRM_Core_Form::CB_PREFIX_LEN);
                     if ($useTable) {
                         $insertString[] = " ( {$contactID} ) ";
                     } else {
//.........这里部分代码省略.........

 /**
  * @param array $params
  *
  * @return array
  */
 public static function voterClause($params)
 {
     $voterClause = array();
     $fromClause = $whereClause = NULL;
     if (!is_array($params) || empty($params)) {
         return $voterClause;
     }
     $surveyId = CRM_Utils_Array::value('campaign_survey_id', $params);
     $searchVoterFor = CRM_Utils_Array::value('campaign_search_voter_for', $params);
     //get the survey activities.
     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
     $status = array('Scheduled');
     if ($searchVoterFor == 'reserve') {
         $status[] = 'Completed';
     }
     $completedStatusId = NULL;
     foreach ($status as $name) {
         if ($statusId = array_search($name, $activityStatus)) {
             $statusIds[] = $statusId;
             if ($name == 'Completed') {
                 $completedStatusId = $statusId;
             }
         }
     }
     $voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);
     if (!empty($voterActValues)) {
         $operator = 'IN';
         $voterIds = array_keys($voterActValues);
         if ($searchVoterFor == 'reserve') {
             $operator = 'NOT IN';
             //filter out recontact survey contacts.
             $recontactInterval = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $surveyId, 'recontact_interval');
             $recontactInterval = unserialize($recontactInterval);
             if ($surveyId && is_array($recontactInterval) && !empty($recontactInterval)) {
                 $voterIds = array();
                 foreach ($voterActValues as $values) {
                     $numOfDays = CRM_Utils_Array::value($values['result'], $recontactInterval);
                     if ($numOfDays && $values['status_id'] == $completedStatusId) {
                         $recontactIntSeconds = $numOfDays * 24 * 3600;
                         $actDateTimeSeconds = CRM_Utils_Date::unixTime($values['activity_date_time']);
                         $totalSeconds = $recontactIntSeconds + $actDateTimeSeconds;
                         //don't consider completed survey activity
                         //unless it fulfill recontact interval criteria.
                         if ($totalSeconds <= time()) {
                             continue;
                         }
                     }
                     $voterIds[$values['voter_id']] = $values['voter_id'];
                 }
             }
         }
         //lets dump these ids in tmp table and
         //use appropriate join depend on operator.
         if (!empty($voterIds)) {
             $voterIdCount = count($voterIds);
             //create temporary table to store voter ids.
             $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_survey_respondent');
             CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
             $query = "\n     CREATE TEMPORARY TABLE {$tempTableName} (\n            id int unsigned NOT NULL AUTO_INCREMENT,\n            survey_contact_id int unsigned NOT NULL,\n  PRIMARY KEY ( id )\n);\n";
             CRM_Core_DAO::executeQuery($query);
             $batch = 100;
             $insertedCount = 0;
             do {
                 $processIds = $voterIds;
                 $insertIds = array_splice($processIds, $insertedCount, $batch);
                 if (!empty($insertIds)) {
                     $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )\n                     VALUES (" . implode('),(', $insertIds) . ');';
                     CRM_Core_DAO::executeQuery($insertSQL);
                 }
                 $insertedCount += $batch;
             } while ($insertedCount < $voterIdCount);
             if ($operator == 'IN') {
                 $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
             } else {
                 $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
                 $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
             }
         }
     }
     $voterClause = array('fromClause' => $fromClause, 'whereClause' => $whereClause);
     return $voterClause;
 }

 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} (\n      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)) . " )\n{$sql}";
     CRM_Core_DAO::executeQuery($insertQuery);
     // now build the query for duration sum
     $this->from(TRUE);
     $this->where(TRUE);
     $this->groupBy(FALSE);
     // 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} (\n      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)\n    {$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\n    FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}\n      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);
     //reset the sql building to default, which is used / called during other actions like "add to group"
     // now build the query for duration sum
     $this->from();
     $this->where();
     // do print / pdf / instance stuff if needed
     $this->endPostProcess($rows);
 }

 public function filterVoterIds()
 {
     //do the cleanup later on.
     if (!is_array($this->_contactIds)) {
         return;
     }
     $profileId = CRM_Campaign_BAO_Survey::getSurveyProfileId($this->_surveyId);
     if ($profileId) {
         $profileType = CRM_Core_BAO_UFField::getProfileType($profileId);
         if (in_array($profileType, CRM_Contact_BAO_ContactType::basicTypes())) {
             $voterIdCount = count($this->_contactIds);
             //create temporary table to store voter ids.
             $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_survey_respondent');
             CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
             $query = "\n     CREATE TEMPORARY TABLE {$tempTableName} (\n            id int unsigned NOT NULL AUTO_INCREMENT,\n            survey_contact_id int unsigned NOT NULL,\n  PRIMARY KEY ( id )\n);\n";
             CRM_Core_DAO::executeQuery($query);
             $batch = 100;
             $insertedCount = 0;
             do {
                 $processIds = $this->_contactIds;
                 $insertIds = array_splice($processIds, $insertedCount, $batch);
                 if (!empty($insertIds)) {
                     $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )\n                     VALUES (" . implode('),(', $insertIds) . ');';
                     CRM_Core_DAO::executeQuery($insertSQL);
                 }
                 $insertedCount += $batch;
             } while ($insertedCount < $voterIdCount);
             $query = "\n    SELECT  contact.id as id\n      FROM  civicrm_contact contact\nINNER JOIN  {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact.id )\n     WHERE  contact.contact_type != %1";
             $removeContact = CRM_Core_DAO::executeQuery($query, array(1 => array($profileType, 'String')));
             while ($removeContact->fetch()) {
                 unset($this->_contactIds[$removeContact->id]);
             }
         }
     }
 }

/**
 * Generate a temporary table with just fields from either the contribution
 * or participant triggers. This function is used when populating the initial
 * data after changing fields, etc.
 */
function sumfields_create_temporary_table($trigger_table)
{
    $name = CRM_Core_DAO::createTempTableName();
    // These are the actual field names as created in this instance
    $custom_fields = _sumfields_get_custom_field_parameters();
    // Load the field and group definitions because we need to know
    // which fields are triggered on which tables
    $custom_field_definitions = sumfields_get_custom_field_definitions();
    $definitions = $custom_field_definitions['fields'];
    $create_fields = array();
    // Initialize with a field to hold the entity_id
    $create_fields[] = "`contact_id` INT";
    // Iterate over the actual instantiated summary fields
    while (list($field_name, $values) = each($custom_fields)) {
        // Avoid error - make sure we have a definition for this field.
        if (array_key_exists($field_name, $definitions)) {
            $field_definition = $definitions[$field_name];
            if ($field_definition['trigger_table'] == $trigger_table) {
                $data_type = $field_definition['data_type'];
                if ($data_type == 'Money') {
                    $data_type = "DECIMAL(10,2)";
                } elseif ($data_type == 'Date') {
                    $data_type = 'datetime';
                } elseif ($data_type == 'String') {
                    $data_type = 'varchar(128)';
                }
                $create_fields[] = "`{$field_name}` {$data_type}";
            }
        }
    }
    $sql = "CREATE TABLE `{$name}` ( " . implode($create_fields, ',') . ')';
    CRM_Core_DAO::executeQuery($sql);
    return $name;
}

 /**
  * Test the function designed to find myIsam tables.
  */
 public function testMyISAMCheck()
 {
     // Cleanup previous, failed tests.
     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_my_isam');
     // A manually created MyISAM table should raise a redflag.
     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
     CRM_Core_DAO::executeQuery('CREATE TABLE civicrm_my_isam (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM');
     $this->assertEquals(1, CRM_Core_DAO::isDBMyISAM());
     CRM_Core_DAO::executeQuery('DROP TABLE civicrm_my_isam');
     // A temp table should not raise flag (static naming).
     $tempName = CRM_Core_DAO::createTempTableName('civicrm', FALSE);
     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
     CRM_Core_DAO::executeQuery("CREATE TABLE {$tempName} (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
     // Ignore temp tables
     CRM_Core_DAO::executeQuery("DROP TABLE {$tempName}");
     // A temp table should not raise flag (randomized naming).
     $tempName = CRM_Core_DAO::createTempTableName('civicrm', TRUE);
     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
     CRM_Core_DAO::executeQuery("CREATE TABLE {$tempName} (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
     // Ignore temp tables
     CRM_Core_DAO::executeQuery("DROP TABLE {$tempName}");
 }