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