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