/** * will scan the database for reference duplicates * and returns the findings in three lists */ function findReferences() { // look up reference types $reference_type_group_id = banking_helper_optiongroupid_by_name('civicrm_banking.reference_types'); $reference_types = civicrm_api3('OptionValue', 'get', array('option_group_id' => $reference_type_group_id)); $reference_types = $reference_types['values']; // then, identify the duplicates $duplicate_references = array(); $duplicate_accounts = array(); $account_conflicts = array(); $sql = "SELECT * \n FROM (SELECT \n civicrm_bank_account_reference.id reference_id,\n COUNT(civicrm_bank_account_reference.id) dupe_count, \n COUNT(DISTINCT(ba_id)) ba_count,\n COUNT(DISTINCT(contact_id)) contact_count,\n MAX(modified_date) last_change,\n reference,\n reference_type_id\n FROM civicrm_bank_account_reference \n LEFT JOIN civicrm_bank_account ON ba_id = civicrm_bank_account.id\n GROUP BY reference, reference_type_id\n ORDER BY last_change DESC\n ) AS dupequery\n WHERE dupequery.dupe_count > 1;"; $duplicate = CRM_Core_DAO::executeQuery($sql); while ($duplicate->fetch()) { $info = array('reference' => $duplicate->reference, 'reference_id' => $duplicate->reference_id, 'dupe_count' => $duplicate->dupe_count, 'reference_type_id' => $duplicate->reference_type_id, 'reference_type' => $reference_types[$duplicate->reference_type_id]); if ($duplicate->ba_count == 1) { $duplicate_references[$duplicate->reference] = $info; } elseif ($duplicate->contact_count == 1) { $duplicate_accounts[$duplicate->reference] = $info; } else { $account_conflicts[$duplicate->reference] = $info; } } // add information foreach ($duplicate_references as &$duplicate_reference) { $this->addContactInformation($duplicate_reference); } foreach ($duplicate_accounts as &$duplicate_account) { $this->addContactInformation($duplicate_account); } foreach ($account_conflicts as &$account_conflict) { $this->addContactInformation($account_conflict); } return array('reference' => $duplicate_references, 'account' => $duplicate_accounts, 'conflict' => $account_conflicts); }
/** * creates an id/name => object mapping for the given option group * * the implementation is probably not optimal, but it'll do for the moment * * @package org.project60.banking * @copyright GNU Affero General Public License * $Id$ * */ function banking_helper_optiongroup_id_name_mapping($group_name) { $group_id = banking_helper_optiongroupid_by_name($group_name); if ($group_id) { $result = civicrm_api('OptionValue', 'get', array('version' => 3, 'option_group_id' => $group_id)); if (isset($result['is_error']) && $result['is_error']) { CRM_Core_Error::fatal(sprintf(ts("Error while looking up option values for group '%s'!"), $group_id)); return array(); } $mapping = array(); foreach ($result['values'] as $entry) { $mapping[$entry['id']] = $entry; $mapping[$entry['name']] = $entry; } // inject 'new' value as id 0 for convenience $mapping[0] = $mapping['new']; return $mapping; } else { return array(); } }
/** * Get a id => value matching of all reference types */ protected function getReferenceTypes($context) { $types = $context->getCachedEntry('analyser_account.reference_types'); if ($types === NULL) { $group_id = banking_helper_optiongroupid_by_name('civicrm_banking.reference_types'); $types = CRM_Core_OptionGroup::valuesByID($group_id, $flip = TRUE, $grouping = FALSE, $localize = FALSE, $labelColumnName = 'id', $onlyActive = TRUE, $fresh = FALSE); $context->setCachedEntry('analyser_account.reference_types', $types); } return $types; }