/**
* generate a transaction message for the given mandate/creditor
*
* @return a SEPA compliant transaction message
*/
static function getTransactionMessage($mandate, $creditor)
{
// get tx message from settings
$transaction_message = self::getSetting('custom_txmsg', $creditor['id']);
// run hook for further customisation
CRM_Utils_SepaCustomisationHooks::modify_txmessage($transaction_message, $mandate, $creditor);
// fallback is "Thanks."
if (empty($transaction_message)) {
$transaction_message = ts("Thank you");
}
// make sure that it doesn't contain any special characters
$transaction_message = preg_replace("#[^a-zA-Z0-9\\/\\-\\:\\(\\)\\'\\+ \\.\\*]#", '?', $transaction_message);
return $transaction_message;
}
/**
* @param array $params (reference ) an assoc array of name/value pairs
*
* @return object CRM_Core_BAO_SEPAMandate object on success, null otherwise
* @access public
* @static (I do apologize, I don't want to)
*/
static function add(&$params)
{
// handle 'normal' creation process inlcuding hooks
$hook = empty($params['id']) ? 'create' : 'edit';
CRM_Utils_Hook::pre($hook, 'SepaMandate', CRM_Utils_Array::value('id', $params), $params);
// set default date to today
if (!array_key_exists("date", $params)) {
$params["date"] = date("YmdHis");
}
if (empty($params['id'])) {
CRM_Utils_SepaCustomisationHooks::create_mandate($params);
if (empty($params['reference'])) {
// If no mandate reference was supplied by the caller nor the customisation hook, create a nice default one.
$creditor = civicrm_api3('SepaCreditor', 'getsingle', array('id' => $params['creditor_id'], 'return' => 'mandate_prefix'));
$dao = new CRM_Core_DAO();
$database = $dao->database();
$next_id = CRM_Core_DAO::singleValueQuery("SELECT auto_increment FROM information_schema.tables WHERE table_schema='{$database}' and table_name='civicrm_sdd_mandate';");
$params['reference'] = $creditor['mandate_prefix'] . '-' . $params['creditor_id'] . '-' . $params['type'] . '-' . date("Y") . '-' . $next_id;
}
}
// validate IBAN / BIC
if (!empty($params['iban'])) {
$params['iban'] = strtoupper($params['iban']);
// create uppercase string
$params['iban'] = str_replace(' ', '', $params['iban']);
// strip spaces
$iban_error = CRM_Sepa_Logic_Verification::verifyIBAN($params['iban']);
if ($iban_error) {
throw new CRM_Exception($iban_error . ':' . $params['iban']);
}
}
if (!empty($params['bic'])) {
$bic_error = CRM_Sepa_Logic_Verification::verifyBIC($params['bic']);
if ($bic_error) {
throw new CRM_Exception($bic_error . ':' . $params['bic']);
}
}
// create the DAO object
$dao = new CRM_Sepa_DAO_SEPAMandate();
$dao->copyValues($params);
if (self::is_active(CRM_Utils_Array::value('status', $params))) {
if ($dao->validation_date == NULL) {
$dao->validation_date = date("YmdHis");
}
}
$dao->save();
CRM_Utils_Hook::post($hook, 'SepaMandate', $dao->id, $dao);
return $dao;
}
/**
* subroutine to create the group/contribution structure as calculated
*/
protected static function syncGroups($calculated_groups, $existing_groups, $mode, $type, $notice, $creditor_id)
{
$group_status_id_open = (int) CRM_Core_OptionGroup::getValue('batch_status', 'Open', 'name');
foreach ($calculated_groups as $collection_date => $mandates) {
// check if we need to defer the collection date (e.g. due to bank holidays)
$exclude_weekends = CRM_Core_BAO_Setting::getItem('SEPA Direct Debit Preferences', 'exclude_weekends');
if ($exclude_weekends) {
// skip (western) week ends, if the option is activated.
$day_of_week = date('N', strtotime($collection_date));
if ($day_of_week > 5) {
// this is a weekend -> skip to Monday
$defer_days = 8 - $day_of_week;
$collection_date = date('Y-m-d', strtotime("+{$defer_days} day", strtotime($collection_date)));
}
}
// also run the hook, in case somebody has a
CRM_Utils_SepaCustomisationHooks::defer_collection_date($collection_date, $creditor_id);
if (!isset($existing_groups[$collection_date])) {
// this group does not yet exist -> create
// find unused reference
$reference = "TXG-{$creditor_id}-{$mode}-{$collection_date}";
$counter = 0;
while (self::referenceExists($reference)) {
$counter += 1;
$reference = "TXG-{$creditor_id}-{$mode}-{$collection_date}--" . $counter;
}
$group = civicrm_api('SepaTransactionGroup', 'create', array('version' => 3, 'reference' => $reference, 'type' => $mode, 'collection_date' => $collection_date, 'latest_submission_date' => date('Y-m-d', strtotime("-{$notice} days", strtotime($collection_date))), 'created_date' => date('Y-m-d'), 'status_id' => $group_status_id_open, 'sdd_creditor_id' => $creditor_id));
if (!empty($group['is_error'])) {
// TODO: Error handling
error_log("org.project60.sepa: batching:syncGroups/createGroup " . $group['error_message']);
}
} else {
$group = civicrm_api('SepaTransactionGroup', 'getsingle', array('version' => 3, 'id' => $existing_groups[$collection_date], 'status_id' => $group_status_id_open));
if (!empty($group['is_error'])) {
// TODO: Error handling
error_log("org.project60.sepa: batching:syncGroups/getGroup " . $group['error_message']);
}
unset($existing_groups[$collection_date]);
}
// now we have the right group. Prepare some parameters...
$group_id = $group['id'];
$entity_ids = array();
foreach ($mandates as $mandate) {
// remark: "mandate_entity_id" in this case means the contribution ID
if (empty($mandate['mandate_entity_id'])) {
// this shouldn't happen
error_log("org.project60.sepa: batching:syncGroups mandate with bad mandate_entity_id ignored:" . $mandate['mandate_id']);
} else {
array_push($entity_ids, $mandate['mandate_entity_id']);
}
}
if (count($entity_ids) <= 0) {
continue;
}
// now, filter out the entity_ids that are are already in a non-open group
// (DO NOT CHANGE CLOSED GROUPS!)
$entity_ids_list = implode(',', $entity_ids);
$already_sent_contributions = CRM_Core_DAO::executeQuery("\n SELECT contribution_id \n FROM civicrm_sdd_contribution_txgroup \n LEFT JOIN civicrm_sdd_txgroup ON civicrm_sdd_contribution_txgroup.txgroup_id = civicrm_sdd_txgroup.id\n WHERE contribution_id IN ({$entity_ids_list})\n AND civicrm_sdd_txgroup.status_id <> {$group_status_id_open};");
while ($already_sent_contributions->fetch()) {
$index = array_search($already_sent_contributions->contribution_id, $entity_ids);
if ($index !== false) {
unset($entity_ids[$index]);
}
}
if (count($entity_ids) <= 0) {
continue;
}
// remove all the unwanted entries from our group
$entity_ids_list = implode(',', $entity_ids);
CRM_Core_DAO::executeQuery("DELETE FROM civicrm_sdd_contribution_txgroup WHERE txgroup_id={$group_id} AND contribution_id NOT IN ({$entity_ids_list});");
// remove all our entries from other groups, if necessary
CRM_Core_DAO::executeQuery("DELETE FROM civicrm_sdd_contribution_txgroup WHERE txgroup_id!={$group_id} AND contribution_id IN ({$entity_ids_list});");
// now check which ones are already in our group...
$existing = CRM_Core_DAO::executeQuery("SELECT * FROM civicrm_sdd_contribution_txgroup WHERE txgroup_id={$group_id} AND contribution_id IN ({$entity_ids_list});");
while ($existing->fetch()) {
// remove from entity ids, if in there:
if (($key = array_search($existing->contribution_id, $entity_ids)) !== false) {
unset($entity_ids[$key]);
}
}
// the remaining must be added
foreach ($entity_ids as $entity_id) {
CRM_Core_DAO::executeQuery("INSERT INTO civicrm_sdd_contribution_txgroup (txgroup_id, contribution_id) VALUES ({$group_id}, {$entity_id});");
}
}
// CLEANUP: remove nonexisting contributions from groups
CRM_Core_DAO::executeQuery("\n DELETE FROM civicrm_sdd_contribution_txgroup \n WHERE contribution_id NOT IN (SELECT id FROM civicrm_contribution);");
// CLEANUP: delete empty groups
$empty_group_query = CRM_Core_DAO::executeQuery("\n SELECT id, sdd_file_id \n FROM civicrm_sdd_txgroup \n WHERE type = '{$mode}'\n AND status_id = {$group_status_id_open}\n AND id NOT IN (SELECT txgroup_id FROM civicrm_sdd_contribution_txgroup);");
while ($empty_group_query->fetch()) {
if (!in_array($empty_group_query->id, $existing_groups)) {
array_push($existing_groups, $empty_group_query->id);
}
if ($empty_group_query->sdd_file_id) {
error_log("org.project60.sepa: WARNING: txgroup " . $empty_group_query->id . " should be deleted, but already has a file. Trouble ahead.");
}
}
// now, use the API to delete all these groups
foreach ($existing_groups as $group_id) {
CRM_Core_DAO::executeQuery("DELETE FROM civicrm_sdd_contribution_txgroup WHERE txgroup_id={$group_id};");
$result = civicrm_api('SepaTransactionGroup', 'delete', array('version' => 3, 'id' => $group_id));
if (isset($result['is_error']) && $result['is_error']) {
error_log("org.project60.sepa: Cannot delete txgroup " . $group_id . ". Error was " . $result['error_message']);
}
}
}
