/** * Will get a variable as defined by the "variables" tag in the config * * @param $context CRM_Banking_Matcher_Context instance, will be used for caching * @return the variables length */ function getVariable($name) { if (isset($this->_plugin_config->variables->{$name})) { $variable_spec = $this->_plugin_config->variables->{$name}; if (!empty($variable_spec->cached)) { // check the cache $value = CRM_Utils_StaticCache::getCachedEntry('var_' . $name); if ($value != NULL) { return $value; } } // get value if ($variable_spec->type == 'SQL') { $value = array(); $querySQL = "SELECT " . $variable_spec->query . ";"; try { $query = CRM_Core_DAO::executeQuery($querySQL); while ($query->fetch()) { $value[] = $query->value; } } catch (Exception $e) { error_log("org.project60.banking.matcher: there was an error with SQL statement '{$querySQL}'"); } if (isset($variable_spec->glue)) { $value = implode($variable_spec->glue, $value); } } else { error_log("org.project60.banking.matcher: unknown variable type '{$variable_spec->type}'."); } if (!empty($variable_spec->cached)) { // set cache value CRM_Utils_StaticCache::setCachedEntry('var_' . $name, $value); } return $value; } return NULL; }
/** * This function will generate an SQL statement to * find all relevant memberships. It should also * provide all values necessary to rate the membership * for probability * * the query itself is derived from the plugin's configuration * and will be cached. * * @return SQL string */ protected function createSQLQuery($contact_ids, $amount, $context) { $cache_key = "matcher_membership_" . $this->_plugin_id . "_query"; $query = CRM_Utils_StaticCache::getCachedEntry($cache_key); if ($query == NULL) { // NOT CACHED, build query $config = $this->_plugin_config; $base_query = "\n SELECT \n civicrm_membership.id AS id,\n civicrm_membership.contact_id AS contact_id,\n civicrm_membership.membership_type_id AS membership_type_id,\n civicrm_membership.start_date AS membership_start_date,\n civicrm_membership_type.duration_unit AS membership_duration_unit,\n civicrm_membership_type.duration_interval AS membership_duration_interval,\n civicrm_membership_type.period_type AS membership_period_type,\n civicrm_membership_type.minimum_fee AS membership_minimum_fee,\n MAX(civicrm_contribution.id) AS last_fee_id,\n AVG(civicrm_contribution.total_amount) AS last_fee_amount,\n civicrm_contribution.receive_date AS last_fee_date\n FROM\n civicrm_membership\n LEFT JOIN\n civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id\n LEFT JOIN\n civicrm_contribution ON civicrm_contribution.id = (%s)\n WHERE\n civicrm_membership.contact_id IN (CONTACT_IDS)\n AND civicrm_membership.membership_type_id IN (%s)\n AND (%s)\n GROUP BY\n civicrm_membership.id;\n "; // LATEST CONTRIBUTION CRITERIA: $contribution_subquery = "\n SELECT last_contribution.id \n FROM civicrm_contribution AS last_contribution\n LEFT JOIN civicrm_membership_payment\n ON civicrm_membership_payment.contribution_id = last_contribution.id\n WHERE last_contribution.contribution_status_id = 1\n AND last_contribution.is_test = 0\n AND civicrm_membership_payment.membership_id = civicrm_membership.id\n ORDER BY receive_date DESC\n LIMIT 1"; // load all membership types $membership_types = array(); $_membership_types = civicrm_api3('MembershipType', 'get', array('option.limit' => 99999)); foreach ($_membership_types['values'] as $membership_type) { $membership_types[$membership_type['id']] = $membership_type; } // get $membership_type_id_list $membership_type_ids = array(); if (isset($config->general_options->membership_type_ids)) { // if there is a given list, we'll take it $membership_type_ids_option = explode(',', $config->general_options->membership_type_ids); foreach ($membership_type_ids_option as $membership_type_id) { if ((int) $membership_type_id) { $membership_type_ids[] = (int) $membership_type_id; } } } else { // if there is no given list, we'll take all active type foreach ($membership_types as $membership_type_id => $membership_type) { if ($membership_type['is_active']) { $membership_type_ids[] = (int) $membership_type_id; } } } //if (empty($membership_type_ids)) throw Exception("matcher_membership: No active membership types found."); // compile $membership_type_clauses $membership_type_clauses = array(); foreach ($membership_type_ids as $membership_type_id) { $amount_range = $this->getMembershipAmountRange($membership_types[$membership_type_id], $context); $membership_type_clauses[] = "(\n (civicrm_membership.membership_type_id = {$membership_type_id})\n AND\n (BTX_AMOUNT >= {$amount_range[0]})\n AND\n (BTX_AMOUNT <= {$amount_range[1]})\n )"; } // compile final query: $membership_type_id_list = implode(',', $membership_type_ids); $membership_type_clauses_sql = implode(' OR ', $membership_type_clauses); $query = sprintf($base_query, $contribution_subquery, $membership_type_id_list, $membership_type_clauses_sql); // normalize query (remove extra whitespaces) $query = preg_replace('/\\s+/', ' ', $query); // and cache the result CRM_Utils_StaticCache::setCachedEntry($cache_key, $query); } // insert the contact IDs $contact_id_list = implode(',', $contact_ids); $final_sql = str_replace('CONTACT_IDS', $contact_id_list, $query); $final_sql = str_replace('BTX_AMOUNT', $amount, $final_sql); //error_log($final_sql); return $final_sql; }