/**
* @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}");
}