/** * Method used to generate a where clause from the given list of conditions. * * @param array $reminder An array of reminder info. * @param array $conditions The list of conditions * @return string The where clause */ public function getWhereClause($reminder, $conditions) { $stmt = ' WHERE iss_prj_id=' . $reminder['rem_prj_id'] . "\n"; $requirement = self::getRequirements($reminder['rem_id']); if ($requirement['type'] == 'issue') { $stmt .= ' AND iss_id IN (' . implode(', ', $requirement['values']) . ")\n"; } else { if (CRM::hasCustomerIntegration($reminder['rem_prj_id'])) { $crm = CRM::getInstance($reminder['rem_prj_id']); if ($requirement['type'] == 'customer') { $stmt .= ' AND iss_customer_id IN (' . implode(', ', $requirement['values']) . ")\n"; } elseif ($requirement['type'] == 'support_level') { $customer_ids = $crm->getCustomerIDsBySupportLevel($requirement['values'], array(CRM_EXCLUDE_EXPIRED)); // break the query on purpose if no customers could be found if (count($customer_ids) == 0) { $customer_ids = array(-1); } $stmt .= ' AND iss_customer_id IN (' . implode(', ', $customer_ids) . ")\n"; } } } $priorities = self::getAssociatedPriorities($reminder['rem_id']); if (count($priorities) > 0) { $stmt .= ' AND iss_pri_id IN (' . implode(', ', $priorities) . ")\n"; } $products = self::getAssociatedProducts($reminder['rem_id']); if (count($products) > 0) { $stmt .= ' AND ipv_iss_id = iss_id AND ipv_pro_id IN (' . implode(', ', $products) . ")\n"; } $severities = self::getAssociatedSeverities($reminder['rem_id']); if (count($severities) > 0) { $stmt .= ' AND iss_sev_id IN (' . implode(', ', $severities) . ")\n"; } // now for the interesting stuff foreach ($conditions as &$cond) { if (empty($cond['rmf_sql_representation'])) { continue; } // check for fields that compare to other fields if (!empty($cond['rlc_comparison_rmf_id'])) { $sql_field = Reminder_Condition::getSQLField($cond['rlc_comparison_rmf_id']); $stmt .= sprintf(" AND %s %s %s\n", $cond['rmf_sql_field'], $cond['rmo_sql_representation'], $sql_field); } else { // date field values are always saved as number of hours, so let's calculate them now as seconds if (stristr($cond['rmf_title'], 'date')) { // support NULL as values for a date field if (strtoupper($cond['rlc_value']) == 'NULL') { $cond['rmf_sql_representation'] = $cond['rmf_sql_field']; } elseif (strtoupper($cond['rlc_value']) == 'NOW') { $cond['rmf_sql_representation'] = 'UNIX_TIMESTAMP(' . $cond['rmf_sql_field'] . ')'; $cond['rlc_value'] = 'UNIX_TIMESTAMP()'; } else { $cond['rlc_value'] = $cond['rlc_value'] * 60 * 60; if (@$reminder['rem_skip_weekend'] == 1) { $sql_field = Reminder_Condition::getSQLField($cond['rlc_rmf_id']); $cond['rmf_sql_representation'] = DB_Helper::getNoWeekendDateDiffSQL($sql_field); } } } $stmt .= sprintf(" AND %s %s %s\n", $cond['rmf_sql_representation'], $cond['rmo_sql_representation'], $cond['rlc_value']); } } return $stmt; }