/** * @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); $dao->free(); } } else { if (CRM_Utils_Array::value('radio_ts', $values) == 'ts_sel') { // selected contacts only // need to perform action on only selected contacts $insertString = array(); foreach ($values 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 { $form->_contactIds[] = substr($name, CRM_Core_Form::CB_PREFIX_LEN); } } } if (!empty($insertString)) { $string = implode(',', $insertString); $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}"; CRM_Core_DAO::executeQuery($sql); } } } //contact type for pick up profiles as per selected contact types with subtypes //CRM-5521 if ($selectedTypes = CRM_Utils_Array::value('contact_type', $values)) { if (!is_array($selectedTypes)) { $selectedTypes = explode(" ", $selectedTypes); } foreach ($selectedTypes as $ct => $dontcare) { if (strpos($ct, CRM_Core_DAO::VALUE_SEPARATOR) === false) { $form->_contactTypes[] = $ct; } else { $separator = strpos($ct, CRM_Core_DAO::VALUE_SEPARATOR); $form->_contactTypes[] = substr($ct, $separator + 1); } } } if (!empty($form->_contactIds)) { $form->_componentClause = ' contact_a.id IN ( ' . implode(',', $form->_contactIds) . ' ) '; $form->assign('totalSelectedContacts', count($form->_contactIds)); $form->_componentIds = $form->_contactIds; } }
/** * @return bool */ public function createFinancialRecords() { $upgrade = new CRM_Upgrade_Form(); // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount $query = "\nUPDATE civicrm_entity_financial_trxn ceft\nLEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id\nSET ceft.amount = total_amount\nWHERE cft.net_amount IS NOT NULL\nAND ceft.entity_table = 'civicrm_contribution'\n"; CRM_Core_DAO::executeQuery($query); $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name'); $completedStatus = array_search('Completed', $contributionStatus); $pendingStatus = array_search('Pending', $contributionStatus); $cancelledStatus = array_search('Cancelled', $contributionStatus); $queryParams = array(1 => array($completedStatus, 'Integer'), 2 => array($pendingStatus, 'Integer'), 3 => array($cancelledStatus, 'Integer')); $accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' ")); $query = "\nSELECT id\nFROM civicrm_financial_account\nWHERE is_default = 1\nAND financial_account_type_id = {$accountType}\n"; $financialAccountId = CRM_Core_DAO::singleValueQuery($query); $accountRelationsips = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount', 'account_relationship', CRM_Core_DAO::$_nullArray, 'validate'); $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips); $incomeAccountIs = array_search('Income Account is', $accountRelationsips); $assetAccountIs = array_search('Asset Account is', $accountRelationsips); $expenseAccountIs = array_search('Expense Account is', $accountRelationsips); $financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id', CRM_Core_DAO::$_nullArray, 'validate'); $unpaidStatus = array_search('Unpaid', $financialItemStatus); $paidStatus = array_search('Paid', $financialItemStatus); $validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode(); $validCurrencyCodes = implode("','", $validCurrencyCodes); $config = CRM_Core_Config::singleton(); $defaultCurrency = $config->defaultCurrency; $now = date('YmdHis'); //adding financial_trxn records and entity_financial_trxn records related to contribution //Add temp column for easy entry in entity_financial_trxn $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL"; CRM_Core_DAO::executeQuery($sql); //pending pay later status handling $sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,\n check_number, to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT con.id as contribution_id, con.payment_instrument_id,\n IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,\n con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,\n con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,\n REPLACE(REPLACE(REPLACE(\n CASE\n WHEN con.receive_date IS NOT NULL THEN\n con.receive_date\n WHEN con.receipt_date IS NOT NULL THEN\n con.receipt_date\n ELSE\n {$now}\n END\n , '-', ''), ':', ''), ' ', '') as trxn_date\nFROM civicrm_contribution con\n LEFT JOIN civicrm_entity_financial_account efa\n ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'\n AND efa.account_relationship = {$accountsReceivableAccount})\nWHERE con.is_pay_later = 1\nAND con.contribution_status_id = {$pendingStatus}\n"; CRM_Core_DAO::executeQuery($sql); //create a temp table to hold financial account id related to payment instruments $tempTableName1 = CRM_Core_DAO::createTempTableName(); $sql = "\nCREATE TEMPORARY TABLE {$tempTableName1}\nSELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id\nFROM civicrm_entity_financial_account ceft\nINNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'\nINNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id\nWHERE cog.name = 'payment_instrument'\n"; CRM_Core_DAO::executeQuery($sql); //CRM-12141 $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));"; CRM_Core_DAO::executeQuery($sql); //create temp table to process completed / cancelled contribution $tempTableName2 = CRM_Core_DAO::createTempTableName(); $sql = "\nCREATE TEMPORARY TABLE {$tempTableName2}\nSELECT con.id as contribution_id, con.payment_instrument_id,\n IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,\n con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,\n con.check_number, NULL as from_financial_account_id,\n REPLACE(REPLACE(REPLACE(\n CASE\n WHEN con.receive_date IS NOT NULL THEN\n con.receive_date\n WHEN con.receipt_date IS NOT NULL THEN\n con.receipt_date\n ELSE\n {$now}\n END\n , '-', ''), ':', ''), ' ', '') as trxn_date,\n CASE\n WHEN con.payment_instrument_id IS NULL THEN\n {$financialAccountId}\n WHEN con.payment_instrument_id IS NOT NULL THEN\n tpi.financial_account_id\n END as to_financial_account_id,\n IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action\nFROM civicrm_contribution con\nLEFT JOIN civicrm_entity_financial_trxn eft\n ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)\nLEFT JOIN {$tempTableName1} tpi\n ON con.payment_instrument_id = tpi.instrument_id\nWHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})\n"; CRM_Core_DAO::executeQuery($sql); // CRM-12141 $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);"; CRM_Core_DAO::executeQuery($sql); //handling for completed contribution and cancelled contribution //insertion of new records $sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,\n to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,\n tempI.fee_amount, tempI.trxn_id, tempI.contribution_status_id, tempI.check_number,\n tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date\nFROM {$tempTableName2} tempI\nWHERE tempI.action = 'insert'\n"; CRM_Core_DAO::executeQuery($sql); //update of existing records $sql = "\nUPDATE civicrm_financial_trxn ft\n INNER JOIN {$tempTableName2} tempU\n ON (tempU.action != 'insert' AND ft.id = tempU.action)\nSET ft.from_financial_account_id = NULL,\n ft.to_financial_account_id = tempU.to_financial_account_id,\n ft.status_id = tempU.contribution_status_id,\n ft.payment_instrument_id = tempU.payment_instrument_id,\n ft.check_number = tempU.check_number,\n ft.contribution_id = tempU.contribution_id;"; CRM_Core_DAO::executeQuery($sql); //insert the -ve transaction rows for cancelled contributions $sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,\n check_number, to_financial_account_id, from_financial_account_id, trxn_date)\nSELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,\n ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date\nFROM civicrm_financial_trxn ft\nWHERE ft.status_id = {$cancelledStatus};"; CRM_Core_DAO::executeQuery($sql); //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses //this also handles +ve and -ve both transaction entries for a cancelled contribution $sql = "\nINSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount\nFROM civicrm_financial_trxn ft\nWHERE contribution_id IS NOT NULL AND\n ft.id NOT IN (SELECT financial_trxn_id\n FROM civicrm_entity_financial_trxn\n WHERE entity_table = 'civicrm_contribution'\n AND entity_id = ft.contribution_id)"; CRM_Core_DAO::executeQuery($sql); //end of adding financial_trxn records and entity_financial_trxn records related to contribution //update all linked line_item rows // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null // set line_item.financial_type_id = price_field_value.financial_type_id if its participant line item and line_item.price_field_value_id is set $updateLineItemSql = "\nUPDATE civicrm_line_item li\n LEFT JOIN civicrm_contribution con\n ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')\n LEFT JOIN civicrm_price_field_value cpfv\n ON li.price_field_value_id = cpfv.id\n LEFT JOIN civicrm_participant cp\n ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')\n LEFT JOIN civicrm_event ce\n ON ce.id = cp.event_id\nSET li.financial_type_id = CASE\n WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN\n con.financial_type_id\n WHEN (con.contribution_page_id IS NOT NULL AND cp.id IS NULL) || (cp.id IS NOT NULL AND li.price_field_value_id IS NOT NULL) THEN\n cpfv.financial_type_id\n WHEN cp.id IS NOT NULL AND li.price_field_value_id IS NULL THEN\n ce.financial_type_id\n END"; CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams); //add the financial_item entries //add a temp column so that inserting entity_financial_trxn entries gets easy $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL"; CRM_Core_DAO::executeQuery($sql); //add financial_item entries for contribution completed / pending pay later / cancelled $contributionlineItemSql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\n\nSELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,\n IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total, con.currency, 'civicrm_line_item',\n li.id as line_item_id, li.label as line_item_label,\n IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,\n ft.id as f_trxn_id\nFROM civicrm_line_item li\n INNER JOIN civicrm_contribution con\n ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')\n INNER JOIN civicrm_financial_trxn ft\n ON (con.id = ft.contribution_id)\n LEFT JOIN civicrm_entity_financial_account efa\n ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'\n AND efa.account_relationship = {$incomeAccountIs})\nWHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)"; CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams); //add financial_item entries for event $participantLineItemSql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\n\nSELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,\n IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,\n con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,\n IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,\n efa.financial_account_id as financial_account_id, ft.id as f_trxn_id\nFROM civicrm_line_item li\n INNER JOIN civicrm_participant par\n ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')\n INNER JOIN civicrm_participant_payment pp\n ON (pp.participant_id = par.id)\n INNER JOIN civicrm_contribution con\n ON (pp.contribution_id = con.id)\n INNER JOIN civicrm_financial_trxn ft\n ON (con.id = ft.contribution_id)\n LEFT JOIN civicrm_entity_financial_account efa\n ON (li.financial_type_id = efa.entity_id AND\n efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})\nWHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)"; CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams); //fee handling for contributions //insert fee entries in financial_trxn for contributions $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL"; CRM_Core_DAO::executeQuery($sql); $sql = "\nINSERT INTO civicrm_financial_trxn\n (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,\n to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)\n\nSELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,\n ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE\n WHEN efaPP.financial_account_id IS NOT NULL THEN\n efaPP.financial_account_id\n WHEN tpi.financial_account_id IS NOT NULL THEN\n tpi.financial_account_id\n ELSE\n {$financialAccountId}\n END as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee\nFROM civicrm_contribution con\n INNER JOIN civicrm_financial_trxn ft\n ON (ft.contribution_id = con.id)\n LEFT JOIN civicrm_entity_financial_account efaFT\n ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'\n AND efaFT.account_relationship = {$expenseAccountIs})\n LEFT JOIN civicrm_entity_financial_account efaPP\n ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'\n AND efaPP.account_relationship = {$assetAccountIs})\n LEFT JOIN {$tempTableName1} tpi\n ON ft.payment_instrument_id = tpi.instrument_id\nWHERE ft.fee_amount IS NOT NULL AND ft.fee_amount != 0 AND (con.contribution_status_id IN (%1, %3) OR (con.contribution_status_id =%2 AND con.is_pay_later = 1))\nGROUP BY con.id"; CRM_Core_DAO::executeQuery($sql, $queryParams); //link financial_trxn to contribution $sql = "\nINSERT INTO civicrm_entity_financial_trxn\n (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount\nFROM civicrm_financial_trxn ft\nWHERE ft.is_fee = 1"; CRM_Core_DAO::executeQuery($sql); //add fee related entries to financial item table $domainId = CRM_Core_Config::domainID(); $domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id'); $sql = "\nINSERT INTO civicrm_financial_item\n (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)\nSELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,\n 'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id\nFROM civicrm_financial_trxn ft\nWHERE ft.is_fee = 1;"; CRM_Core_DAO::executeQuery($sql); //add entries to entity_financial_trxn table $sql = "\nINSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)\nSELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount\nFROM civicrm_financial_item fi"; CRM_Core_DAO::executeQuery($sql); //drop the temparory columns $sql = "ALTER TABLE civicrm_financial_trxn\n DROP COLUMN contribution_id,\n DROP COLUMN is_fee;"; CRM_Core_DAO::executeQuery($sql); $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id"; CRM_Core_DAO::executeQuery($sql); return TRUE; }
/** * @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 { $form->_contactIds[] = substr($name, CRM_Core_Form::CB_PREFIX_LEN); } } } } else { // fetching selected contact ids of passed cache key $selectedCids = CRM_Core_BAO_PrevNextCache::getSelection($cacheKey); foreach ($selectedCids[$cacheKey] as $selectedCid => $ignore) { if ($useTable) { $insertString[] = " ( {$selectedCid} ) "; } else { $form->_contactIds[] = $selectedCid; } } } if (!empty($insertString)) { $string = implode(',', $insertString); $sql = "REPLACE INTO {$form->_componentTable} ( contact_id ) VALUES {$string}"; CRM_Core_DAO::executeQuery($sql); } } //contact type for pick up profiles as per selected contact types with subtypes //CRM-5521 if ($selectedTypes = CRM_Utils_Array::value('contact_type', self::$_searchFormValues)) { if (!is_array($selectedTypes)) { $selectedTypes = explode(' ', $selectedTypes); } foreach ($selectedTypes as $ct => $dontcare) { if (strpos($ct, CRM_Core_DAO::VALUE_SEPARATOR) === FALSE) { $form->_contactTypes[] = $ct; } else { $separator = strpos($ct, CRM_Core_DAO::VALUE_SEPARATOR); $form->_contactTypes[] = substr($ct, $separator + 1); } } } if (CRM_Utils_Array::value('radio_ts', self::$_searchFormValues) == 'ts_sel' && $form->_action != CRM_Core_Action::COPY) { $sel = CRM_Utils_Array::value('radio_ts', self::$_searchFormValues); $form->assign('searchtype', $sel); $result = CRM_Core_BAO_PrevNextCache::getSelectedContacts(); $form->assign("value", $result); } if (!empty($form->_contactIds)) { $form->_componentClause = ' contact_a.id IN ( ' . implode(',', $form->_contactIds) . ' ) '; $form->assign('totalSelectedContacts', count($form->_contactIds)); $form->_componentIds = $form->_contactIds; } }
/** * @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}"); }