/**
* Will execute any dedupe/merge requests specified via the REQUEST params
*/
function executeRequests($duplicates)
{
$refs_fixed = 0;
$accounts_fixed = 0;
$errors_ecountered = 0;
$account_reflist = array();
$reflist = array();
// =========================
// MERGE DUPLICATE ACCOUNTS
// =========================
if (!empty($_REQUEST['fixdupe'])) {
if ($_REQUEST['fixdupe'] == 'all') {
foreach ($duplicates['account'] as $reference => $info) {
if ((int) $info['reference_id']) {
$account_reflist[] = (int) $info['reference_id'];
}
}
} else {
$parm_list = explode(',', $_REQUEST['fixdupe']);
foreach ($parm_list as $reference_id) {
if ((int) $reference_id) {
$account_reflist[] = (int) $reference_id;
}
}
}
// perform the changes
foreach ($account_reflist as $reference_id) {
// MERGE ACCOUNTS
// first, find the account IDs to merge
$bank_account_ids = array();
$sql = "SELECT ba_id \n FROM civicrm_bank_account_reference \n WHERE reference = (SELECT reference FROM civicrm_bank_account_reference WHERE id={$reference_id})\n ORDER BY ba_id ASC;";
// use the oldest (lowest ID) as merge target
$bank_account_query = CRM_Core_DAO::executeQuery($sql);
while ($bank_account_query->fetch()) {
$bank_account_ids[] = $bank_account_query->ba_id;
}
if (count($bank_account_ids) < 2) {
// we need at least two bank accounts to merge...
$errors_ecountered += 1;
continue;
}
// now: load the first bank account...
$main_ba = new CRM_Banking_BAO_BankAccount();
$main_ba->get('id', $bank_account_ids[0]);
$main_data_parsed = $main_ba->getDataParsed();
// ...and try to merge the others into it
$merge_failed = FALSE;
for ($i = 1; $i < count($bank_account_ids); $i++) {
$merge_ba = new CRM_Banking_BAO_BankAccount();
$merge_ba->get('id', $bank_account_ids[$i]);
// merge created_date
if (isset($merge_ba->created_date) && $merge_ba->created_date < $main_ba->created_date) {
$main_ba->created_date = $merge_ba->created_date;
}
// merge description/data_raw
$replace_attributes = array('description', 'data_raw');
foreach ($replace_attributes as $attribute) {
if (!empty($merge_ba->{$attribute})) {
if (empty($main_ba->{$attribute})) {
// main_ba.$attribute not set, just overwrite
$main_ba->{$attribute} = $merge_ba->{$attribute};
} else {
// main_ba.$attribute set, check if identical
if ($main_ba->{$attribute} != $merge_ba->{$attribute}) {
if (CIVIBANKING_MERGE_ACCOUNTS_CHECK_STRICT) {
$merge_failed = TRUE;
break;
}
}
}
}
}
if ($merge_failed) {
break;
}
// merge data_parsed
$merge_data_parsed = $merge_ba->getDataParsed();
foreach ($merge_data_parsed as $key => $value) {
if (empty($main_data_parsed[$key])) {
$main_data_parsed[$key] = $value;
} else {
if ($main_data_parsed[$key] != $merge_data_parsed[$key]) {
if (CIVIBANKING_MERGE_ACCOUNTS_CHECK_STRICT) {
$merge_failed = TRUE;
break;
}
}
}
}
if ($merge_failed) {
break;
}
}
// MERGE NEXT ACCOUNT (for same target)
if ($merge_failed) {
$errors_ecountered += 1;
} else {
// SAVE THE MERGED OBJECT
$main_ba->setDataParsed($main_data_parsed);
$main_ba->modified_date = date('YmdHis');
$main_ba->save();
// DELETE THE OTHER, NOW OBSOLETE ACCOUNTS
$target_id = $bank_account_ids[0];
unset($bank_account_ids[0]);
$delete_ids = implode(',', $bank_account_ids);
CRM_Core_DAO::singleValueQuery("UPDATE civicrm_bank_account_reference SET ba_id={$target_id} WHERE ba_id IN ({$delete_ids});");
CRM_Core_DAO::singleValueQuery("UPDATE civicrm_bank_tx SET ba_id={$target_id} WHERE ba_id IN ({$delete_ids});");
CRM_Core_DAO::singleValueQuery("UPDATE civicrm_bank_tx SET party_ba_id={$target_id} WHERE party_ba_id IN ({$delete_ids});");
CRM_Core_DAO::singleValueQuery("DELETE FROM civicrm_bank_account WHERE id IN ({$delete_ids});");
$accounts_fixed += 1;
// finally, add the reference_ids to duplicate reference list,
// in order to delete the resulting duplicate references
$reflist[] = $reference_id;
}
}
if ($errors_ecountered) {
CRM_Core_Session::setStatus(ts("%1 errors were encountered when trying to merge duplicate bank accounts, %2/%3 bank accounts were successfully merged.", array(1 => $errors_ecountered, 2 => $accounts_fixed, 3 => $errors_ecountered + $accounts_fixed)), ts('Errors encountered'), 'warn');
$errors_ecountered = 0;
} else {
CRM_Core_Session::setStatus(ts("%1 duplicate bank accounts successfully merged.", array(1 => $accounts_fixed)), ts('Success'), 'info');
}
}
// ============================
// DELETE DUPLICATE REFERENCES.
// ============================
if (!empty($_REQUEST['fixref']) || !empty($reflist)) {
// They should be identical and can be safely removed
if (!empty($_REQUEST['fixref'])) {
if ($_REQUEST['fixref'] == 'all') {
foreach ($duplicates['reference'] as $reference => $info) {
if ((int) $info['reference_id']) {
$reflist[] = (int) $info['reference_id'];
}
}
} else {
$parm_list = explode(',', $_REQUEST['fixref']);
foreach ($parm_list as $reference_id) {
if ((int) $reference_id) {
$reflist[] = (int) $reference_id;
}
}
}
}
// perform the changes
foreach ($reflist as $reference_id) {
$sql = "SELECT ref_delete.id AS reference_id\n FROM civicrm_bank_account_reference ref_delete\n LEFT JOIN civicrm_bank_account_reference ref_keep ON ref_keep.id={$reference_id}\n WHERE ref_keep.reference = ref_delete.reference\n AND ref_keep.ba_id = ref_delete.ba_id\n AND ref_keep.reference_type_id = ref_delete.reference_type_id\n AND ref_keep.id != ref_delete.id;";
$reference_to_delete = CRM_Core_DAO::executeQuery($sql);
while ($reference_to_delete->fetch()) {
$result = civicrm_api('BankingAccountReference', 'delete', array('id' => $reference_to_delete->reference_id, 'version' => 3));
if (empty($result['is_error'])) {
$refs_fixed += 1;
} else {
error_log("org.project60.banking.dedupe: Error while deleting dupe reference: " . $result['error_message']);
$errors_ecountered += 1;
}
}
}
if ($errors_ecountered) {
CRM_Core_Session::setStatus(ts("%1 errors were encountered when trying to delete duplicate references. %2/%3 references were successfully deleted.", array(1 => $errors_ecountered, 2 => $refs_fixed, 3 => $errors_ecountered + $refs_fixed)), ts('Errors encountered'), 'warn');
$errors_ecountered = 0;
} else {
CRM_Core_Session::setStatus(ts("%1 duplicate references successfully deleted.", array(1 => $refs_fixed)), ts('Success'), 'info');
}
}
return $refs_fixed + $accounts_fixed;
}
/**
* Save presets and create/update account/reference
*/
function postProcess()
{
$values = $this->exportValues();
$was_created = FALSE;
// save presets
if (!empty($values['reference_type'])) {
CRM_Core_BAO_Setting::setItem($values['reference_type'], 'CiviBanking', 'account.default_reference_id');
}
if (!empty($values['country'])) {
CRM_Core_BAO_Setting::setItem($values['country'], 'CiviBanking', 'account.default_country');
}
// create bank account
$ba_id = $values['ba_id'];
if (empty($ba_id)) {
$bank_account = civicrm_api3('BankingAccount', 'create', array('contact_id' => $values['contact_id'], 'data_parsed' => '{}'));
$was_created = TRUE;
$ba_id = $bank_account['id'];
}
// update bank account data
$bank_data_attributes = array('bic' => 'BIC', 'bank_name' => 'name', 'country' => 'country');
$bank_bao = new CRM_Banking_BAO_BankAccount();
$bank_bao->get('id', $ba_id);
$bank_data = $bank_bao->getDataParsed();
foreach ($bank_data_attributes as $form_attribute => $bank_data_attribute) {
if (empty($values[$form_attribute])) {
unset($bank_data[$bank_data_attribute]);
} else {
$bank_data[$bank_data_attribute] = $values[$form_attribute];
}
}
$bank_bao->setDataParsed($bank_data);
$bank_bao->save();
// update/create bank reference
$reference_update = array('reference' => $values['reference'], 'reference_type_id' => $values['reference_type'], 'ba_id' => $ba_id);
if (!empty($values['reference_id'])) {
$reference_update['id'] = $values['reference_id'];
}
civicrm_api3('BankingAccountReference', 'create', $reference_update);
if ($was_created) {
CRM_Core_Session::setStatus(ts("Bank account '%1' was created.", $values['reference']), ts('Success'));
} else {
CRM_Core_Session::setStatus(ts("Bank account '%1' was updated.", $values['reference']), ts('Success'));
}
// return to accounts tab
CRM_Utils_System::redirect(CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid=2&selectedChild=bank_accounts"));
parent::postProcess();
}
function run()
{
CRM_Utils_System::setTitle(ts('CiviBanking Dashboard'));
$now = strtotime("now");
$week_count = 7;
$oldest_week = date('YW', strtotime("now -{$week_count} weeks"));
$payment_states = banking_helper_optiongroup_id_name_mapping('civicrm_banking.bank_tx_status');
$account_names = array();
// get the week based data
$account_week_data = array();
for ($i = $week_count; $i >= 0; $i--) {
$weeks[] = date('YW', strtotime("now -{$i} weeks"));
}
$account_based_data_sql = "\n SELECT\n COUNT(*) AS count,\n YEARWEEK(value_date,3) AS year_week,\n ba_id AS bank_account_id,\n status_id AS status_id\n FROM\n civicrm_bank_tx\n GROUP BY\n ba_id, year_week, status_id;\n ";
$results = CRM_Core_DAO::executeQuery($account_based_data_sql);
while ($results->fetch()) {
// get the account
if (empty($results->bank_account_id)) {
$account_id = 0;
} else {
$account_id = $results->bank_account_id;
}
if (!isset($account_week_data[$account_id])) {
$account_week_data[$account_id] = array();
}
// get the week
$week = $results->year_week;
if ($week < $oldest_week) {
$week = 'before';
}
if (!isset($account_week_data[$account_id][$week])) {
$account_week_data[$account_id][$week] = array();
}
// get the status
$status_id = $results->status_id;
if ($results->status_id == $payment_states['processed']['id'] || $results->status_id == $payment_states['ignored']['id']) {
if (!isset($account_week_data[$account_id][$week]['done'])) {
$account_week_data[$account_id][$week]['done'] = 0;
}
$account_week_data[$account_id][$week]['done'] += $results->count;
}
//if (!isset($account_week_data[$account_id][$week][$status_id])) $account_week_data[$account_id][$week][$status_id] = 0;
//$account_week_data[$account_id][$week][$status_id] += 1;
if (!isset($account_week_data[$account_id][$week]['sum'])) {
$account_week_data[$account_id][$week]['sum'] = 0;
}
$account_week_data[$account_id][$week]['sum'] += $results->count;
}
// fill empty weeks
foreach ($account_week_data as $account_id => $account_data) {
for ($i = $week_count; $i >= 0; $i--) {
$week = date('YW', strtotime("now -{$i} weeks"));
if (!isset($account_data[$week])) {
$account_week_data[$account_id][$week] = array('sum' => 0);
}
}
if (!isset($account_data['before'])) {
$account_week_data[$account_id]['before'] = array('sum' => 0);
}
// look up account name
if ($account_id == 0) {
$account_names[0] = ts('Unknown');
} else {
$btx_bao = new CRM_Banking_BAO_BankAccount();
$btx_bao->get('id', $account_id);
$data_parsed = $btx_bao->getDataParsed();
if (isset($data_parsed['name'])) {
$account_names[$account_id] = $data_parsed['name'];
} else {
$account_names[$account_id] = ts('account') . " [{$account_id}]";
}
}
}
$this->assign('account_week_data', $account_week_data);
$this->assign('account_names', $account_names);
$this->assign('weeks', $weeks);
// get statistics data
$statistics = array();
$statistics[] = $this->calculateStats(ts("Payments") . " (" . ts("current year") . ")", "YEAR(value_date) = '" . date('Y') . "'", $payment_states);
$statistics[] = $this->calculateStats(ts("Payments") . " (" . ts("last year") . ")", "YEAR(value_date) = '" . date('Y', strtotime("-1 year")) . "'", $payment_states);
$statistics[] = $this->calculateStats(ts("Payments") . " (" . ts("all times") . ")", "1", $payment_states);
$this->assign('statistics', $statistics);
parent::run();
}