Пример #1
0
 /**
  * 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;
 }