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