/**
* @param string $name
* @param $type
*
* @return mixed|null
*/
public static function getTypedValue($name, $type)
{
$value = CRM_Utils_Array::value($name, $_GET);
if ($value === NULL) {
return NULL;
}
return CRM_Utils_Type::escape($value, CRM_Utils_Type::typeToString($type), FALSE);
}
static function getTypedValue($name, $type)
{
$value = CRM_Utils_Array::value($name, $_GET);
if ($value === null) {
return null;
}
return CRM_Utils_Type::escape($value, CRM_Utils_Type::typeToString($type), false);
}
/**
* Generate where clause for any parameters not already handled.
*
* @param array $values
*
* @throws Exception
*/
public function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$this->_where[$grouping][] = self::buildClause($where, $op, $value);
list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
$this->_qill[$grouping][] = ts("State %1 %2", array(1 => $qillop, 2 => $qillVal));
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, 'CRM_Contact_DAO_Contact', $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country' || substr($name, 0, 6) === 'county') {
$name = substr($name, 0, 7) === 'country' ? "country_id" : "county_id";
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.{$name}";
} else {
$where = "civicrm_address.{$name}";
}
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
if ($lType) {
$field['title'] .= " ({$lType})";
}
list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
} elseif ($name === 'world_region') {
$this->optionValueQuery($name, $op, $value, $grouping, NULL, $field, ts('World Region'), 'Positive', TRUE);
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'created_date' || $name === 'modified_date' || $name === 'deceased_date' || $name === 'birth_date') {
$appendDateTime = TRUE;
if ($name === 'deceased_date' || $name === 'birth_date') {
$appendDateTime = FALSE;
self::$_openedPanes[ts('Demographics')] = TRUE;
}
$this->dateQueryBuilder($values, 'contact_a', $name, $name, $field['title'], $appendDateTime);
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
} elseif (!empty($field['where'])) {
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$fieldName = "LOWER(`{$tName}`.{$fldName})";
// we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
$this->_tables[$tName] = $this->_whereTables[$tName] = 1;
} else {
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
}
list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, $field['title'], $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE ? $qillVal : "'{$qillVal}'"));
if (is_array($value)) {
// traditionally an array being passed has been a fatal error. We can take advantage of this to add support
// for api style operators for functions that hit this point without worrying about regression
// (the previous comments indicated the condition for hitting this point were unknown
// per CRM-14743 we are adding modified_date & created_date operator support
$operations = array_keys($value);
foreach ($operations as $operator) {
if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
//Via Contact get api value is not in array(operator => array(values)) format ONLY for IN/NOT IN operators
//so this condition will satisfy the search for now
if (strpos($op, 'IN') !== FALSE) {
$value = array($op => $value);
} else {
CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $operator)));
}
}
}
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
} else {
if (!strpos($op, 'IN')) {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
/**
* global validation rules for the form
*
* @param array $fields posted values of the form
*
* @return array list of errors to be posted back to the form
* @static
* @access public
*/
static function formRule($values, $files, $self)
{
if (CRM_Utils_Array::value('addMore', $values) || CRM_Utils_Array::value('addBlock', $values)) {
return TRUE;
}
$fields = self::fields();
$fld = CRM_Core_BAO_Mapping::formattedFields($values, TRUE);
$errorMsg = array();
foreach ($fld as $k => $v) {
if (!$v[1]) {
$errorMsg["operator[{$v['3']}][{$v['4']}]"] = ts("Please enter the operator.");
} else {
// CRM-10338
$v[2] = self::checkArrayKeyEmpty($v[2]);
if (in_array($v[1], array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY')) && !empty($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts('Please clear your value if you want to use %1 operator.', array(1 => $v[1]));
} elseif (($v[0] == 'group' || $v[0] == 'tag') && !empty($v[2])) {
$grpId = array_keys($v[2]);
if (!key($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a value.");
}
if (count($grpId) > 1) {
if ($v[1] != 'IN' && $v[1] != 'NOT IN') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a valid value.");
}
foreach ($grpId as $val) {
$error = CRM_Utils_Type::validate($val, 'Integer', FALSE);
if ($error != $val) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter valid value.");
break;
}
}
} else {
$error = CRM_Utils_Type::validate($grpId[0], 'Integer', FALSE);
if ($error != $grpId[0]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts('Please enter valid %1 id.', array(1 => $v[0]));
}
}
} elseif (substr($v[0], 0, 7) === 'do_not_' or substr($v[0], 0, 3) === 'is_') {
if (isset($v[2])) {
$v2 = array($v[2]);
if (!isset($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a value.");
}
$error = CRM_Utils_Type::validate($v2[0], 'Integer', FALSE);
if ($error != $v2[0]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a valid value.");
}
} else {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a value.");
}
} else {
if (substr($v[0], 0, 7) == 'custom_') {
// Get rid of appended location type id
list($fieldKey) = explode('-', $v[0]);
$type = $fields[$fieldKey]['data_type'];
// hack to handle custom data of type state and country
if (in_array($type, array('Country', 'StateProvince'))) {
$type = "Integer";
}
} else {
$fldName = $v[0];
// FIXME: no idea at this point what to do with this,
// FIXME: but definitely needs fixing.
if (substr($v[0], 0, 13) == 'contribution_') {
$fldName = substr($v[0], 13);
}
$fldValue = CRM_Utils_Array::value($fldName, $fields);
$fldType = CRM_Utils_Array::value('type', $fldValue);
$type = CRM_Utils_Type::typeToString($fldType);
// Check Empty values for Integer Or Boolean Or Date type For operators other than IS NULL and IS NOT NULL.
if (!in_array($v[1], array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
if (($type == 'Int' || $type == 'Boolean') && !trim($v[2]) && $v[2] != '0') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a value.");
} elseif ($type == 'Date' && !trim($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a value.");
}
}
}
if ($type && empty($errorMsg)) {
// check for valid format while using IN Operator
if ($v[1] == 'IN') {
$inVal = trim($v[2]);
//checking for format to avoid db errors
if ($type == 'Int') {
if (!preg_match('/^[(]([A-Za-z0-9\\,]+)[)]$/', $inVal)) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter correct Data (in valid format).");
}
} else {
if (!(substr($inVal, 0, 1) == '(' && substr($inVal, -1, 1) == ')') && !preg_match('/^[(]([A-Za-z0-9åäöÅÄÖüÜœŒæÆøØ\\,\\s]+)[)]$/', $inVal)) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter correct Data (in valid format).");
}
}
// Validate each value in parenthesis to avoid db errors
if (empty($errorMsg)) {
$parenValues = array();
$parenValues = explode(',', trim($inVal, "(..)"));
foreach ($parenValues as $val) {
$val = trim($val);
if (!$val && $val != '0') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the values correctly.");
}
if (empty($errorMsg)) {
$error = CRM_Utils_Type::validate($val, $type, FALSE);
if ($error != $val) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a valid value.");
}
}
}
}
} elseif (trim($v[2])) {
//else check value for rest of the Operators
$error = CRM_Utils_Type::validate($v[2], $type, FALSE);
if ($error != $v[2]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter a valid value.");
}
}
}
}
}
}
if (!empty($errorMsg)) {
$self->set('showSearchForm', TRUE);
$self->assign('rows', NULL);
return $errorMsg;
}
return TRUE;
}
/**
* Execute a weight-related query
*
* @param string $queryType SELECT, UPDATE, DELETE
* @param string $daoName full name of the DAO
* @param array $fieldValues field => value to be used in the WHERE
* @param string $queryData data to be used, dependent on the query type
* @param string $orderBy optional ORDER BY field
* @return Object CRM_Core_DAO objet that holds the results of the query
*/
static function &query($queryType, $daoName, $fieldValues = null, $queryData, $additionalWhere = null, $orderBy = null, $groupBy = null)
{
require_once 'CRM/Utils/Type.php';
require_once str_replace('_', DIRECTORY_SEPARATOR, $daoName) . ".php";
$dao =& new $daoName();
$table = $dao->getTablename();
$fields =& $dao->fields();
$fieldlist = array_keys($fields);
$whereConditions = array();
if ($additionalWhere) {
$whereConditions[] = $additionalWhere;
}
$params = array();
$fieldNum = 0;
if (is_array($fieldValues)) {
foreach ($fieldValues as $fieldName => $value) {
if (!in_array($fieldName, $fieldlist)) {
// invalid field specified. abort.
return false;
}
$fieldNum++;
$whereConditions[] = "{$fieldName} = %{$fieldNum}";
$fieldType = $fields[$fieldName]['type'];
$params[$fieldNum] = array($value, CRM_Utils_Type::typeToString($fieldType));
}
}
$where = implode(' AND ', $whereConditions);
switch ($queryType) {
case 'SELECT':
$query = "SELECT {$queryData} FROM {$table}";
if ($where) {
$query .= " WHERE {$where}";
}
if ($groupBy) {
$query .= " GROUP BY {$groupBy}";
}
if ($orderBy) {
$query .= " ORDER BY {$orderBy}";
}
break;
case 'UPDATE':
$query = "UPDATE {$table} SET {$queryData}";
if ($where) {
$query .= " WHERE {$where}";
}
break;
case 'DELETE':
$query = "DELETE FROM {$table} WHERE {$where} AND {$queryData}";
break;
default:
return false;
}
$resultDAO = CRM_Core_DAO::executeQuery($query, $params);
return $resultDAO;
}
/**
* Get a list of Assignments matching the params, where each param key is:
* 1. the key of a field in civicrm_activity
* except for activity_type_id and activity_duration
* 2. the key of a custom field on the activity
* (volunteer_need_id, time_scheduled, time_completed)
* 3. the key of a field in civicrm_contact
* 4. project_id
*
* @param array $params
* @return array of CRM_Volunteer_BAO_Project objects
*/
static function retrieve(array $params)
{
$activity_fields = CRM_Activity_DAO_Activity::fields();
$contact_fields = CRM_Contact_DAO_Contact::fields();
$custom_fields = self::getCustomFields();
$foreign_fields = array('project_id', 'target_contact_id');
// This is the "real" id
$activity_fields['id'] = $activity_fields['activity_id'];
unset($activity_fields['activity_id']);
// enforce restrictions on parameters
$allowed_params = array_flip(array_merge(array_keys($activity_fields), array_keys($contact_fields), array_keys($custom_fields), $foreign_fields));
unset($allowed_params['activity_type_id']);
unset($allowed_params['activity_duration']);
$filtered_params = array_intersect_key($params, $allowed_params);
$custom_group = self::getCustomGroup();
$customTableName = $custom_group['table_name'];
foreach ($custom_fields as $name => $field) {
$selectClause[] = "{$customTableName}.{$field['column_name']} AS {$name}";
}
$customSelect = implode(', ', $selectClause);
$activityContactTypes = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContactTypes);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContactTypes);
$volunteerStatus = CRM_Activity_BAO_Activity::buildOptions('status_id', 'validate');
$available = CRM_Utils_Array::key('Available', $volunteerStatus);
$scheduled = CRM_Utils_Array::key('Scheduled', $volunteerStatus);
$placeholders = array(1 => array($assigneeID, 'Integer'), 2 => array(self::volunteerActivityTypeId(), 'Integer'), 3 => array($scheduled, 'Integer'), 4 => array($available, 'Integer'), 5 => array($targetID, 'Integer'));
$i = count($placeholders) + 1;
$where = array();
$whereClause = NULL;
foreach ($filtered_params as $key => $value) {
if (CRM_Utils_Array::value($key, $activity_fields)) {
$dataType = CRM_Utils_Type::typeToString($activity_fields[$key]['type']);
$fieldName = $activity_fields[$key]['name'];
$tableName = CRM_Activity_DAO_Activity::$_tableName;
} elseif (CRM_Utils_Array::value($key, $contact_fields)) {
$dataType = CRM_Utils_Type::typeToString($contact_fields[$key]['type']);
$fieldName = $contact_fields[$key]['name'];
$tableName = CRM_Contact_DAO_Contact::$_tableName;
} elseif (CRM_Utils_Array::value($key, $custom_fields)) {
$dataType = $custom_fields[$key]['data_type'];
$fieldName = $custom_fields[$key]['column_name'];
$tableName = $customTableName;
} elseif ($key == 'project_id') {
$dataType = 'Int';
$fieldName = 'id';
$tableName = CRM_Volunteer_DAO_Project::$_tableName;
} elseif ($key == 'target_contact_id') {
$dataType = 'Int';
$fieldName = 'contact_id';
$tableName = 'tgt';
// this is an alias for civicrm_activity_contact
}
$where[] = "{$tableName}.{$fieldName} = %{$i}";
$placeholders[$i] = array($value, $dataType);
$i++;
}
if (count($where)) {
$whereClause = 'AND ' . implode("\nAND ", $where);
}
$query = "\n SELECT\n civicrm_activity.*,\n assignee.contact_id AS assignee_contact_id,\n {$customSelect},\n civicrm_volunteer_need.start_time,\n civicrm_volunteer_need.is_flexible,\n civicrm_volunteer_need.role_id,\n assignee_contact.sort_name AS assignee_sort_name,\n assignee_contact.display_name AS assignee_display_name,\n assignee_phone.phone AS assignee_phone,\n assignee_phone.phone_ext AS assignee_phone_ext,\n assignee_email.email AS assignee_email,\n -- begin target contact fields\n tgt.contact_id AS target_contact_id,\n tgt_contact.sort_name AS target_sort_name,\n tgt_contact.display_name AS target_display_name,\n tgt_phone.phone AS target_phone,\n tgt_phone.phone_ext AS target_phone_ext,\n tgt_email.email AS target_email\n -- end target contact fields\n FROM civicrm_activity\n INNER JOIN civicrm_activity_contact assignee\n ON (\n assignee.activity_id = civicrm_activity.id\n AND assignee.record_type_id = %1\n )\n INNER JOIN civicrm_contact assignee_contact\n ON assignee.contact_id = assignee_contact.id\n LEFT JOIN civicrm_email assignee_email\n ON assignee_email.contact_id = assignee_contact.id AND assignee_email.is_primary = 1\n LEFT JOIN civicrm_phone assignee_phone\n ON assignee_phone.contact_id = assignee_contact.id AND assignee_phone.is_primary = 1\n -- begin target contact joins\n LEFT JOIN civicrm_activity_contact tgt\n ON (\n tgt.activity_id = civicrm_activity.id\n AND tgt.record_type_id = %5\n )\n LEFT JOIN civicrm_contact tgt_contact\n ON tgt.contact_id = tgt_contact.id\n LEFT JOIN civicrm_email tgt_email\n ON tgt_email.contact_id = tgt_contact.id AND tgt_email.is_primary = 1\n LEFT JOIN civicrm_phone tgt_phone\n ON tgt_phone.contact_id = tgt_contact.id AND tgt_phone.is_primary = 1\n -- end target contact joins\n INNER JOIN {$customTableName}\n ON ({$customTableName}.entity_id = civicrm_activity.id)\n INNER JOIN civicrm_volunteer_need\n ON (civicrm_volunteer_need.id = {$customTableName}.{$custom_fields['volunteer_need_id']['column_name']})\n INNER JOIN civicrm_volunteer_project\n ON (civicrm_volunteer_project.id = civicrm_volunteer_need.project_id)\n WHERE civicrm_activity.activity_type_id = %2\n AND civicrm_activity.status_id IN (%3, %4 )\n {$whereClause}\n ";
$dao = CRM_Core_DAO::executeQuery($query, $placeholders);
$rows = array();
while ($dao->fetch()) {
$rows[$dao->id] = $dao->toArray();
}
/*
* For clarity we want the fields associated with each contact prefixed with
* the contact type (e.g., target_phone). For backwards compatibility,
* however, we want the fields associated with each assignee contact to be
* accessible sans prefix. Eventually we should deprecate the non-prefixed
* field names.
*/
foreach ($rows as $id => $fields) {
foreach ($fields as $key => $value) {
if (substr($key, 0, 9) == 'assignee_') {
$rows[$id][substr($key, 9)] = $value;
}
}
}
return $rows;
}
/**
* make a shallow copy of an object.
* and all the fields in the object
*
* @param string $daoName
* Name of the dao.
* @param array $criteria
* Array of all the fields & values.
* on which basis to copy
* @param array $newData
* Array of all the fields & values.
* to be copied besides the other fields
* @param string $fieldsFix
* Array of fields that you want to prefix/suffix/replace.
* @param string $blockCopyOfDependencies
* Fields that you want to block from.
* getting copied
*
*
* @return CRM_Core_DAO
* the newly created copy of the object
*/
public static function ©Generic($daoName, $criteria, $newData = NULL, $fieldsFix = NULL, $blockCopyOfDependencies = NULL)
{
$object = new $daoName();
if (!$newData) {
$object->id = $criteria['id'];
} else {
foreach ($criteria as $key => $value) {
$object->{$key} = $value;
}
}
$object->find();
while ($object->fetch()) {
// all the objects except with $blockCopyOfDependencies set
// be copied - addresses #CRM-1962
if ($blockCopyOfDependencies && $object->{$blockCopyOfDependencies}) {
break;
}
$newObject = new $daoName();
$fields =& $object->fields();
if (!is_array($fieldsFix)) {
$fieldsToPrefix = array();
$fieldsToSuffix = array();
$fieldsToReplace = array();
}
if (!empty($fieldsFix['prefix'])) {
$fieldsToPrefix = $fieldsFix['prefix'];
}
if (!empty($fieldsFix['suffix'])) {
$fieldsToSuffix = $fieldsFix['suffix'];
}
if (!empty($fieldsFix['replace'])) {
$fieldsToReplace = $fieldsFix['replace'];
}
foreach ($fields as $name => $value) {
if ($name == 'id' || $value['name'] == 'id') {
// copy everything but the id!
continue;
}
$dbName = $value['name'];
$type = CRM_Utils_Type::typeToString($value['type']);
$newObject->{$dbName} = $object->{$dbName};
if (isset($fieldsToPrefix[$dbName])) {
$newObject->{$dbName} = $fieldsToPrefix[$dbName] . $newObject->{$dbName};
}
if (isset($fieldsToSuffix[$dbName])) {
$newObject->{$dbName} .= $fieldsToSuffix[$dbName];
}
if (isset($fieldsToReplace[$dbName])) {
$newObject->{$dbName} = $fieldsToReplace[$dbName];
}
if ($type == 'Timestamp' || $type == 'Date') {
$newObject->{$dbName} = CRM_Utils_Date::isoToMysql($newObject->{$dbName});
}
if ($newData) {
foreach ($newData as $k => $v) {
$newObject->{$k} = $v;
}
}
}
$newObject->save();
}
return $newObject;
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
unset($query->_where[$grouping][$key]);
}
}
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
unset($query->_where[$grouping][$key]);
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
if (!empty($allEventIds)) {
$op = "IN";
$value = "(" . implode(",", $allEventIds) . ")";
}
}
$query->_where[$grouping][] = "civicrm_event.id {$op} {$value}";
$query->_qill[$grouping][] = ts('Include Repeating Events');
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'participant_is_test':
$key = array_search('civicrm_participant.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'participant_test':
// We dont want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts("Participant is a Test");
}
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
}
return;
case 'participant_fee_id':
$feeLabel = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $value, 'label');
$feeLabel = CRM_Core_DAO::escapeString(trim($feeLabel));
if ($value) {
$query->_where[$grouping][] = "civicrm_participant.fee_level LIKE '%{$feeLabel}%'";
$query->_qill[$grouping][] = ts("Fee level") . " contains {$feeLabel}";
}
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_amount_high':
case 'participant_fee_amount_low':
$query->numberRangeBuilder($values, 'civicrm_participant', 'participant_fee_amount', 'fee_amount', 'Fee Amount');
return;
case 'participant_status_id':
case 'participant_role_id':
if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
$op = 'IN';
}
case 'participant_status':
case 'participant_role':
case 'participant_source':
case 'participant_id':
case 'participant_contact_id':
case 'participant_is_pay_later':
case 'participant_fee_amount':
case 'participant_fee_level':
$qillName = $name;
if (in_array($name, array('participant_status_id', 'participant_role_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later'))) {
$name = str_replace('participant_', '', $name);
if ($name == 'is_pay_later') {
$qillName = $name;
}
if ($name == 'role_id') {
$qillName = 'participant_role';
$query->_where[$grouping][] = " civicrm_participant.{$name} REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', (array) $value) . "[[:>:]]' ";
}
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
if (in_array($name, array('participant_status', 'participant_role'))) {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$name}.label", $op, $value, $dataType);
} elseif ($name != 'role_id') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $op, $value, $dataType);
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_register_date':
$query->dateQueryBuilder($values, 'civicrm_participant', 'participant_register_date', 'register_date', 'Register Date');
return;
case 'event_id':
case 'participant_event_id':
$name = str_replace('participant_', '', $name);
case 'event_is_public':
case 'event_type_id':
case 'event_title':
$qillName = $name;
if (in_array($name, array('event_id', 'event_title', 'event_is_public'))) {
$name = str_replace('event_', '', $name);
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.{$name}", $op, $value, $dataType);
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
if (!array_key_exists($qillName, $fields)) {
break;
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Event', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
return;
case 'participant_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_participant');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$quoteValue = NULL;
$fields = array_merge(CRM_Contribute_BAO_Contribution::fields(), self::getFields());
if (!empty($value) && !is_array($value)) {
$quoteValue = "\"{$value}\"";
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
foreach (self::getRecurringFields() as $dateField => $dateFieldTitle) {
if (self::buildDateWhere($values, $query, $name, $dateField, $dateFieldTitle)) {
return;
}
}
switch ($name) {
case 'contribution_date':
case 'contribution_date_low':
case 'contribution_date_low_time':
case 'contribution_date_high':
case 'contribution_date_high_time':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_contribution', 'contribution_date', 'receive_date', 'Contribution Date');
return;
case 'contribution_amount':
case 'contribution_amount_low':
case 'contribution_amount_high':
// process min/max amount
$query->numberRangeBuilder($values, 'civicrm_contribution', 'contribution_amount', 'total_amount', 'Contribution Amount', NULL);
return;
case 'contribution_thankyou_date_is_not_null':
if ($value) {
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Contribution Thank-you Sent');
} else {
$op = "IS NULL";
$query->_qill[$grouping][] = ts('Contribution Thank-you Not Sent');
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.thankyou_date", $op);
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_receipt_date_is_not_null':
if ($value) {
$op = "IS NOT NULL";
$query->_qill[$grouping][] = ts('Contribution Receipt Sent');
} else {
$op = "IS NULL";
$query->_qill[$grouping][] = ts('Contribution Receipt Not Sent');
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.receipt_date", $op);
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'financial_type':
case 'contribution_page':
case 'payment_instrument':
case 'contribution_payment_instrument':
case 'contribution_status':
$name .= '_id';
case 'financial_type_id':
case 'payment_instrument_id':
case 'contribution_payment_instrument_id':
case 'contribution_page_id':
case 'contribution_status_id':
case 'contribution_id':
case 'contribution_currency_type':
case 'contribution_currency':
case 'contribution_source':
case 'contribution_trxn_id':
case 'contribution_check_number':
case 'contribution_contact_id':
case strpos($name, '_amount') !== FALSE:
case strpos($name, '_date') !== FALSE:
$qillName = $name;
$pseudoExtraParam = NULL;
if (strpos($name, '_amount') !== FALSE || strpos($name, '_date') !== FALSE || in_array($name, array('contribution_id', 'contribution_currency', 'contribution_source', 'contribution_trxn_id', 'contribution_check_number', 'contribution_payment_instrument_id', 'contribution_contact_id'))) {
$name = str_replace('contribution_', '', $name);
if (!in_array($name, array('source', 'id', 'contact_id'))) {
$qillName = str_replace('contribution_', '', $qillName);
}
}
if (in_array($name, array('contribution_currency', 'contribution_currency_type'))) {
$qillName = $name = 'currency';
$pseudoExtraParam = array('labelColumn' => 'name');
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', $name, $value, $op, $pseudoExtraParam);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_pcp_made_through_id':
case 'contribution_soft_credit_type_id':
$qillName = $name;
if ($name == 'contribution_pcp_made_through_id') {
$qillName = $name = 'pcp_id';
$fields[$name] = array('title' => ts('Personal Campaign Page'), 'type' => 2);
}
if ($name == 'contribution_soft_credit_type_id') {
$qillName = str_replace('_id', '', $qillName);
$fields[$qillName]['type'] = $fields[$qillName]['data_type'];
$name = str_replace('contribution_', '', $name);
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution_soft.{$name}", $op, $value, CRM_Utils_Type::typeToString($fields[$qillName]['type']));
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_ContributionSoft', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_or_softcredits':
if ($value == 'only_scredits') {
$query->_where[$grouping][] = "contribution_search_scredit_combined.scredit_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits Only');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
} elseif ($value == 'both_related') {
$query->_where[$grouping][] = "contribution_search_scredit_combined.filter_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Soft Credits with related Hard Credit');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
} elseif ($value == 'both') {
$query->_qill[$grouping][] = ts('Contributions OR Soft Credits? - Both');
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
}
// default option: $value == 'only_contribs'
return;
case 'contribution_is_test':
// By default is Contribution Search form we choose is_test = 0 in otherwords always show active contribution
// so in case if any one choose any Yes/No avoid the default clause otherwise it will be conflict in whereClause
$key = array_search('civicrm_contribution.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'contribution_test':
// We dont want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts("Only Display Test Contributions");
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
}
return;
case 'contribution_is_pay_later':
case 'contribution_pay_later':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.is_pay_later", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts("Find Pay Later Contributions");
} else {
$query->_qill[$grouping][] = ts("Exclude Pay Later Contributions");
}
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_recurring':
if ($value) {
$query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NOT NULL";
$query->_qill[$grouping][] = ts("Find Recurring Contributions");
$query->_tables['civicrm_contribution_recur'] = $query->_whereTables['civicrm_contribution_recur'] = 1;
} else {
$query->_where[$grouping][] = "civicrm_contribution.contribution_recur_id IS NULL";
$query->_qill[$grouping][] = ts("Exclude Recurring Contributions");
}
return;
case 'contribution_recur_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_contribution.contribution_recur_id", $op, $value, "Integer");
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
return;
case 'contribution_note':
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(civicrm_note.note)" : "civicrm_note.note";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, "String");
$query->_qill[$grouping][] = ts('Contribution Note %1 %2', array(1 => $op, 2 => $quoteValue));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = $query->_whereTables['contribution_note'] = 1;
return;
case 'contribution_membership_id':
$query->_where[$grouping][] = " civicrm_membership.id {$op} {$value}";
$query->_tables['contribution_membership'] = $query->_whereTables['contribution_membership'] = 1;
return;
case 'contribution_participant_id':
$query->_where[$grouping][] = " civicrm_participant.id {$op} {$value}";
$query->_tables['contribution_participant'] = $query->_whereTables['contribution_participant'] = 1;
return;
case 'contribution_pcp_display_in_roll':
$query->_where[$grouping][] = " civicrm_contribution_soft.pcp_display_in_roll {$op} '{$value}'";
if ($value) {
$query->_qill[$grouping][] = ts("Personal Campaign Page Honor Roll");
} else {
$query->_qill[$grouping][] = ts("NOT Personal Campaign Page Honor Roll");
}
$query->_tables['civicrm_contribution_soft'] = $query->_whereTables['civicrm_contribution_soft'] = 1;
return;
case 'contribution_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_contribution');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
case 'contribution_batch_id':
$batches = CRM_Contribute_PseudoConstant::batch();
$query->_where[$grouping][] = " civicrm_entity_batch.batch_id {$op} {$value}";
$query->_qill[$grouping][] = ts('Batch Name %1 %2', array(1 => $op, 2 => $batches[$value]));
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
$query->_tables['contribution_batch'] = $query->_whereTables['contribution_batch'] = 1;
return;
default:
//all other elements are handle in this case
$fldName = substr($name, 13);
if (!isset($fields[$fldName])) {
// CRM-12597
CRM_Core_Session::setStatus(ts('We did not recognize the search field: %1. Please check and fix your contribution related smart groups.', array(1 => $fldName)));
return;
}
$whereTable = $fields[$fldName];
$value = trim($value);
$dataType = "String";
if (!empty($whereTable['type'])) {
$dataType = CRM_Utils_Type::typeToString($whereTable['type']);
}
$wc = $op != 'LIKE' && $dataType != 'Date' ? "LOWER({$whereTable['where']})" : "{$whereTable['where']}";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($wc, $op, $value, $dataType);
$query->_qill[$grouping][] = "{$whereTable['title']} {$op} {$quoteValue}";
list($tableName, $fieldName) = explode('.', $whereTable['where'], 2);
$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
if ($tableName == 'civicrm_contribution_product') {
$query->_tables['civicrm_product'] = $query->_whereTables['civicrm_product'] = 1;
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
} else {
$query->_tables['civicrm_contribution'] = $query->_whereTables['civicrm_contribution'] = 1;
}
}
}
/**
* global validation rules for the form
*
* @param array $fields posted values of the form
*
* @return array list of errors to be posted back to the form
* @static
* @access public
*/
static function formRule(&$values)
{
//CRM_Core_Error::debug('s', $values);
if (CRM_Utils_Array::value('addMore', $values) || CRM_Utils_Array::value('addBlock', $values)) {
return true;
}
require_once 'CRM/Contact/BAO/Contact.php';
$fields = array();
$fields = CRM_Contact_BAO_Contact::exportableFields('All', false, true);
require_once 'CRM/Core/Component.php';
$compomentFields =& CRM_Core_Component::getQueryFields();
$fields = array_merge($fields, $compomentFields);
$fld = array();
$fld = CRM_Core_BAO_Mapping::formattedFields($values, true);
require_once 'CRM/Utils/Type.php';
$errorMsg = array();
foreach ($fld as $k => $v) {
if (!$v[1]) {
$errorMsg["operator[{$v['3']}][{$v['4']}]"] = ts("Please enter the operator.");
} else {
if (in_array($v[1], array('IS NULL', 'IS NOT NULL')) && $v[2]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts('Please clear your value if you want to use %1 operator.', array(1 => $v[1]));
} else {
if ($v[0] == 'group' || $v[0] == 'tag') {
$grpId = array_keys($v[2]);
if (!key($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
}
if (count($grpId) > 1) {
if ($v[1] != 'IN' && $v[1] != 'NOT IN') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the valid value.");
}
foreach ($grpId as $val) {
$error = CRM_Utils_Type::validate($val, 'Integer', false);
if ($error != $val) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter valid value.");
break;
}
}
} else {
$error = CRM_Utils_Type::validate($grpId[0], 'Integer', false);
if ($error != $grpId[0]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts('Please enter valid %1 id.', array(1 => $v[0]));
}
}
} else {
if (substr($v[0], 0, 7) === 'do_not_' or substr($v[0], 0, 3) === 'is_') {
if ($v[2]) {
$v2 = array($v[2]);
if (!isset($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
}
$error = CRM_Utils_Type::validate($v2[0], 'Integer', false);
if ($error != $v2[0]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter valid value.");
}
} else {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
}
} else {
if ($v[0] === 'sort_name' || $v[0] === 'display_name') {
$v2 = trim($v[2]);
if (empty($v2)) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
}
} else {
if (substr($v[0], 0, 7) == 'custom_') {
$type = $fields[$v[0]]['data_type'];
// hack to handle custom data of type state and country
if (in_array($type, array('Country', 'StateProvince'))) {
$type = "Integer";
}
} else {
$fldName = $v[0];
// FIXME: no idea at this point what to do with this,
// FIXME: but definitely needs fixing.
if (substr($v[0], 0, 13) == 'contribution_') {
$fldName = substr($v[0], 13);
}
$fldType = CRM_Utils_Array::value('type', $fields[$fldName]);
$type = CRM_Utils_Type::typeToString($fldType);
// Check Empty values for Integer Or Boolean Or Date type For operators other than IS NULL and IS NOT NULL.
if (!in_array($v[1], array('IS NULL', 'IS NOT NULL'))) {
if (($type == 'Int' || $type == 'Boolean') && !trim($v[2]) && $v[2] != '0') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
} else {
if ($type == 'Date' && !trim($v[2])) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the value.");
}
}
}
}
if ($type && empty($errorMsg)) {
// check for valid format while using IN Operator
if ($v[1] == 'IN') {
$inVal = trim($v[2]);
//checking for format to avoid db errors
if (!preg_match('/^[(]([A-Za-z0-9\'\\,]+)[)]$/', $inVal)) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter correct Data ( in valid format ).");
}
// Validate each value in parenthesis to avoid db errors
if (empty($errorMsg)) {
$parenValues = array();
$parenValues = explode(',', trim($inVal, "(..)"));
foreach ($parenValues as $val) {
if (!$val && $val != '0') {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter the values correctly.");
}
if (empty($errorMsg)) {
$error = CRM_Utils_Type::validate($val, $type, false);
if ($error != $val) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter valid value.");
}
}
}
}
} else {
if (trim($v[2])) {
//else check value for rest of the Operators
$error = CRM_Utils_Type::validate($v[2], $type, false);
if ($error != $v[2]) {
$errorMsg["value[{$v['3']}][{$v['4']}]"] = ts("Please enter valid value.");
}
}
}
}
}
}
}
}
}
}
if (!empty($errorMsg)) {
return $errorMsg;
}
return true;
}
/**
* Get a list of Commendations matching the params, where each param key is:
* 1. the key of a field in civicrm_activity, except for activity_type_id
* 2. the key of a custom field on the activity (volunteer_project_id)
* 3. the key of a field in civicrm_contact
*
* @param array $params
* @return array of CRM_Volunteer_BAO_Project objects
*/
public static function retrieve(array $params)
{
$activity_fields = CRM_Activity_DAO_Activity::fields();
$contact_fields = CRM_Contact_DAO_Contact::fields();
$custom_fields = self::getCustomFields();
// This is the "real" id
$activity_fields['id'] = $activity_fields['activity_id'];
unset($activity_fields['activity_id']);
// enforce restrictions on parameters
$allowed_params = array_flip(array_merge(array_keys($activity_fields), array_keys($contact_fields), array_keys($custom_fields)));
unset($allowed_params['activity_type_id']);
$filtered_params = array_intersect_key($params, $allowed_params);
$custom_group = self::getCustomGroup();
$customTableName = $custom_group['table_name'];
foreach ($custom_fields as $name => $field) {
$selectClause[] = "{$customTableName}.{$field['column_name']} AS {$name}";
}
$customSelect = implode(', ', $selectClause);
$activityContactTypes = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContactTypes);
$placeholders = array(1 => array($targetID, 'Integer'), 2 => array(self::getActivityTypeId(), 'Integer'));
$i = count($placeholders) + 1;
$where = array();
$whereClause = NULL;
foreach ($filtered_params as $key => $value) {
if (CRM_Utils_Array::value($key, $activity_fields)) {
$dataType = CRM_Utils_Type::typeToString($activity_fields[$key]['type']);
$fieldName = $activity_fields[$key]['name'];
$tableName = CRM_Activity_DAO_Activity::$_tableName;
} elseif (CRM_Utils_Array::value($key, $contact_fields)) {
$dataType = CRM_Utils_Type::typeToString($contact_fields[$key]['type']);
$fieldName = $contact_fields[$key]['name'];
$tableName = CRM_Contact_DAO_Contact::$_tableName;
} elseif (CRM_Utils_Array::value($key, $custom_fields)) {
$dataType = $custom_fields[$key]['data_type'];
$fieldName = $custom_fields[$key]['column_name'];
$tableName = $customTableName;
}
$where[] = "{$tableName}.{$fieldName} = %{$i}";
$placeholders[$i] = array($value, $dataType);
$i++;
}
if (count($where)) {
$whereClause = 'AND ' . implode("\nAND ", $where);
}
$query = "\n SELECT\n civicrm_activity.*,\n {$customSelect},\n activityContact.contact_id AS volunteer_contact_id,\n volunteer_contact.sort_name AS volunteer_sort_name,\n volunteer_contact.display_name AS volunteer_display_name\n FROM civicrm_activity\n INNER JOIN civicrm_activity_contact activityContact\n ON (\n activityContact.activity_id = civicrm_activity.id\n AND activityContact.record_type_id = %1\n )\n INNER JOIN civicrm_contact volunteer_contact\n ON activityContact.contact_id = volunteer_contact.id\n INNER JOIN {$customTableName}\n ON ({$customTableName}.entity_id = civicrm_activity.id)\n WHERE civicrm_activity.activity_type_id = %2\n {$whereClause}\n ";
$dao = CRM_Core_DAO::executeQuery($query, $placeholders);
$rows = array();
while ($dao->fetch()) {
$rows[$dao->id] = $dao->toArray();
}
return $rows;
}
/**
* @param $values
*
* @throws Exception
*/
function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$states = CRM_Core_PseudoConstant::stateProvince();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $states[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $states);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']), $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.country_id";
} else {
$where = "civicrm_address.country_id";
}
$countries = CRM_Core_PseudoConstant::country();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $countries[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $countries);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.county_id";
} else {
$where = "civicrm_address.county_id";
}
$counties = CRM_Core_PseudoConstant::county();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $counties[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $counties);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$field['where'] = 'civicrm_worldregion.id';
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::worldRegion(), $field, ts('World Region'), 'Positive', TRUE);
} elseif ($name === 'birth_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'deceased_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if (is_array($value)) {
// traditionally an array being passed has been a fatal error. We can take advantage of this to add support
// for api style operators for functions that hit this point without worrying about regression
// (the previous comments indicated the condition for hitting this point were unknown
// per CRM-14743 we are adding modified_date & created_date operator support
$operations = array_keys($value);
foreach ($operations as $operator) {
if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
// we don't know when this might happen
CRM_Core_Error::fatal();
}
}
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($name, $value, NULL);
//since this is not currently being called by the form layer we can skip worrying about the 'qill' for now
return;
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
// we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
$this->_tables[$tName] = $this->_whereTables[$tName] = 1;
$this->_qill[$grouping][] = "{$field['title']} {$op} '{$value}'";
} else {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
function whereClause(&$field, $op, $value, $min, $max)
{
$type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
$clause = null;
switch ($op) {
case 'bw':
case 'nbw':
if ($min !== null && strlen($min) > 0 || $max !== null && strlen($max) > 0) {
$min = CRM_Utils_Type::escape($min, $type);
$max = CRM_Utils_Type::escape($max, $type);
$clauses = array();
if ($min) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} >= {$min} )";
} else {
$clauses[] = "( {$field['dbAlias']} < {$min} )";
}
}
if ($max) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} <= {$max} )";
} else {
$clauses[] = "( {$field['dbAlias']} > {$max} )";
}
}
if (!empty($clauses)) {
if ($op == 'bw') {
$clause = implode(' AND ', $clauses);
} else {
$clause = implode(' OR ', $clauses);
}
}
}
break;
case 'has':
case 'nhas':
if ($value !== null && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === false) {
$value = "'%{$value}%'";
} else {
$value = "'{$value}'";
}
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'in':
if ($value !== null && is_array($value) && count($value) > 0) {
$sqlOP = self::getSQLOperator($op);
if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
$clause = "( {$field['dbAlias']} {$sqlOP} ( '" . implode("' , '", $value) . "') )";
} else {
// for numerical values
$clause = "( {$field['dbAlias']} {$sqlOP} (" . implode(', ', $value) . ") )";
}
}
break;
case 'mhas':
// mhas == multiple has
if ($value !== null && count($value) > 0) {
$sqlOP = self::getSQLOperator($op);
$clause = "{$field['dbAlias']} REGEXP '[[:<:]]" . implode('|', $value) . "[[:>:]]'";
}
break;
case 'sw':
case 'ew':
if ($value !== null && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === false) {
if ($op == 'sw') {
$value = "'{$value}%'";
} else {
$value = "'%{$value}'";
}
} else {
$value = "'{$value}'";
}
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'nll':
case 'nnll':
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} )";
break;
default:
if ($value !== null && strlen($value) > 0) {
if (isset($field['clause'])) {
// FIXME: we not doing escape here. Better solution is to use two
// different types - data-type and filter-type
eval("\$clause = \"{$field['clause']}\";");
} else {
$value = CRM_Utils_Type::escape($value, $type);
$sqlOP = self::getSQLOperator($op);
if ($field['type'] == CRM_Utils_Type::T_STRING) {
$value = "'{$value}'";
}
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
}
break;
}
if (CRM_Utils_Array::value('group', $field) && $clause) {
$clause = $this->whereGroupClause($clause);
} elseif (CRM_Utils_Array::value('tag', $field) && $clause) {
// not using left join in query because if any contact
// belongs to more than one tag, results duplicate
// entries.
$clause = $this->whereTagClause($clause);
}
return $clause;
}
static function whereClause(&$field, $op, $value, $min, $max)
{
$type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
$clause = null;
switch ($op) {
case 'bw':
case 'nbw':
if ($min !== null && strlen($min) > 0 || $max !== null && strlen($max) > 0) {
$min = CRM_Utils_Type::escape($min, $type);
$max = CRM_Utils_Type::escape($max, $type);
$clauses = array();
if ($min) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} >= {$min} )";
} else {
$clauses[] = "( {$field['dbAlias']} < {$min} )";
}
}
if ($max) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} <= {$max} )";
} else {
$clauses[] = "( {$field['dbAlias']} > {$max} )";
}
}
if (!empty($clauses)) {
if ($op == 'bw') {
$clause = implode(' AND ', $clauses);
} else {
$clause = implode(' OR ', $clauses);
}
}
}
break;
case 'has':
case 'nhas':
if ($value !== null && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === false) {
$value = "'%{$value}%'";
} else {
$value = "'{$value}'";
}
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'in':
if ($value !== null && count($value) > 0) {
$sqlOP = self::getSQLOperator($op);
if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
$clause = "( {$field['dbAlias']} {$sqlOP} ( '" . implode("' , '", $value) . "') )";
} else {
$clause = "( {$field['dbAlias']} {$sqlOP} (" . implode(', ', $value) . ") )";
}
}
break;
case 'sw':
case 'ew':
if ($value !== null && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === false) {
if ($op == 'sw') {
$value = "'{$value}%'";
} else {
$value = "'%{$value}'";
}
} else {
$value = "'{$value}'";
}
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'nll':
$sqlOP = self::getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} )";
break;
default:
if ($value !== null && strlen($value) > 0) {
if (isset($field['clause'])) {
// FIXME: we not doing escape here. Better solution is to use two
// different types - data-type and filter-type
eval("\$clause = \"{$field['clause']}\";");
} else {
$value = CRM_Utils_Type::escape($value, $type);
$sqlOP = self::getSQLOperator($op);
if ($field['type'] == CRM_Utils_Type::T_STRING) {
$value = "'{$value}'";
}
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
}
break;
}
return $clause;
}
/**
* Generate where clause.
*
* This can be overridden in reports for special treatment of a field
*
* @param array $field Field specifications
* @param string $op Query operator (not an exact match to sql)
* @param mixed $value
* @param float $min
* @param float $max
*
* @return null|string
*/
public function whereClause(&$field, $op, $value, $min, $max)
{
$type = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
$clause = NULL;
switch ($op) {
case 'bw':
case 'nbw':
if ($min !== NULL && strlen($min) > 0 || $max !== NULL && strlen($max) > 0) {
$min = CRM_Utils_Type::escape($min, $type);
$max = CRM_Utils_Type::escape($max, $type);
$clauses = array();
if ($min) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} >= {$min} )";
} else {
$clauses[] = "( {$field['dbAlias']} < {$min} )";
}
}
if ($max) {
if ($op == 'bw') {
$clauses[] = "( {$field['dbAlias']} <= {$max} )";
} else {
$clauses[] = "( {$field['dbAlias']} > {$max} )";
}
}
if (!empty($clauses)) {
if ($op == 'bw') {
$clause = implode(' AND ', $clauses);
} else {
$clause = implode(' OR ', $clauses);
}
}
}
break;
case 'has':
case 'nhas':
if ($value !== NULL && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === FALSE) {
$value = "'%{$value}%'";
} else {
$value = "'{$value}'";
}
$sqlOP = $this->getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'in':
case 'notin':
if (is_string($value) && strlen($value)) {
$value = explode(',', $value);
}
if ($value !== NULL && is_array($value) && count($value) > 0) {
$sqlOP = $this->getSQLOperator($op);
if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
//cycle through selections and escape values
foreach ($value as $key => $selection) {
$value[$key] = CRM_Utils_Type::escape($selection, $type);
}
$clause = "( {$field['dbAlias']} {$sqlOP} ( '" . implode("' , '", $value) . "') )";
} else {
// for numerical values
$clause = "{$field['dbAlias']} {$sqlOP} (" . implode(', ', $value) . ")";
}
if ($op == 'notin') {
$clause = "( " . $clause . " OR {$field['dbAlias']} IS NULL )";
} else {
$clause = "( " . $clause . " )";
}
}
break;
case 'mhas':
case 'mnot':
// multiple has or multiple not
if ($value !== NULL && count($value) > 0) {
$value = CRM_Utils_Type::escapeAll($value, $type);
$operator = $op == 'mnot' ? 'NOT' : '';
$regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
$clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
}
break;
case 'sw':
case 'ew':
if ($value !== NULL && strlen($value) > 0) {
$value = CRM_Utils_Type::escape($value, $type);
if (strpos($value, '%') === FALSE) {
if ($op == 'sw') {
$value = "'{$value}%'";
} else {
$value = "'%{$value}'";
}
} else {
$value = "'{$value}'";
}
$sqlOP = $this->getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
break;
case 'nll':
case 'nnll':
$sqlOP = $this->getSQLOperator($op);
$clause = "( {$field['dbAlias']} {$sqlOP} )";
break;
default:
if ($value !== NULL && strlen($value) > 0) {
if (isset($field['clause'])) {
// FIXME: we not doing escape here. Better solution is to use two
// different types - data-type and filter-type
$clause = $field['clause'];
} else {
$value = CRM_Utils_Type::escape($value, $type);
$sqlOP = $this->getSQLOperator($op);
if ($field['type'] == CRM_Utils_Type::T_STRING) {
$value = "'{$value}'";
}
$clause = "( {$field['dbAlias']} {$sqlOP} {$value} )";
}
}
break;
}
if (!empty($field['group']) && $clause) {
$clause = $this->whereGroupClause($field, $value, $op);
} elseif (!empty($field['tag']) && $clause) {
// not using left join in query because if any contact
// belongs to more than one tag, results duplicate
// entries.
$clause = $this->whereTagClause($field, $value, $op);
} elseif (!empty($field['membership_org']) && $clause) {
$clause = $this->whereMembershipOrgClause($value, $op);
} elseif (!empty($field['membership_type']) && $clause) {
$clause = $this->whereMembershipTypeClause($value, $op);
}
return $clause;
}
/**
* Where clause for a single field.
*
* @param $values
* @param $query
* @return void
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping) = $values;
$fields = CRM_Activity_BAO_Activity::exportableFields();
$query->_tables['civicrm_activity'] = $query->_whereTables['civicrm_activity'] = 1;
if ($query->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY) {
$query->_skipDeleteClause = TRUE;
}
switch ($name) {
case 'activity_type_id':
case 'activity_status_id':
case 'activity_engagement_level':
case 'activity_subject':
case 'activity_id':
$qillName = $name;
if (in_array($name, array('activity_engagement_level', 'activity_id'))) {
$name = $qillName = str_replace('activity_', '', $name);
}
if (in_array($name, array('activity_status_id', 'activity_subject'))) {
$name = str_replace('activity_', '', $name);
$qillName = str_replace('_id', '', $qillName);
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
break;
case 'activity_type':
case 'activity_status':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$name}.label", $op, $value, 'String');
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$name]['title'], 2 => $op, 3 => $value));
$query->_tables[$name] = $query->_whereTables[$name] = 1;
break;
case 'activity_survey_id':
if (!$value) {
break;
}
$value = CRM_Utils_Type::escape($value, 'Integer');
$query->_where[$grouping][] = " civicrm_activity.source_record_id = {$value}";
$query->_qill[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title');
break;
case 'activity_role':
CRM_Contact_BAO_Query::$_activityRole = $values[2];
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
if ($values[2]) {
$query->_tables['civicrm_activity_contact'] = $query->_whereTables['civicrm_activity_contact'] = 1;
if ($values[2] == 1) {
$query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$sourceID}";
$query->_qill[$grouping][] = ts('Activity created by');
} elseif ($values[2] == 2) {
$query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$assigneeID}";
$query->_qill[$grouping][] = ts('Activity assigned to');
} elseif ($values[2] == 3) {
$query->_where[$grouping][] = " civicrm_activity_contact.record_type_id = {$targetID}";
$query->_qill[$grouping][] = ts('Activity targeted to');
}
}
break;
case 'activity_test':
// We don't want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_activity.is_test", $op, $value, "Boolean");
if ($value) {
$query->_qill[$grouping][] = ts('Activity is a Test');
}
}
break;
case 'activity_date':
case 'activity_date_low':
case 'activity_date_high':
$query->dateQueryBuilder($values, 'civicrm_activity', 'activity_date', 'activity_date_time', ts('Activity Date'));
break;
case 'activity_taglist':
$taglist = $value;
$value = array();
foreach ($taglist as $val) {
if ($val) {
$val = explode(',', $val);
foreach ($val as $tId) {
if (is_numeric($tId)) {
$value[$tId] = 1;
}
}
}
}
case 'activity_tags':
$value = array_keys($value);
$activityTags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
$names = array();
if (is_array($value)) {
foreach ($value as $k => $v) {
$names[] = $activityTags[$v];
}
}
$query->_where[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
$query->_qill[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names);
$query->_tables['civicrm_activity_tag'] = $query->_whereTables['civicrm_activity_tag'] = 1;
break;
case 'activity_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_activity');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
break;
case 'activity_result':
if (is_array($value)) {
$safe = NULL;
while (list(, $k) = each($value)) {
$safe[] = "'" . CRM_Utils_Type::escape($k, 'String') . "'";
}
$query->_where[$grouping][] = "civicrm_activity.result IN (" . implode(',', $safe) . ")";
$query->_qill[$grouping][] = ts("Activity Result - %1", array(1 => implode(' or ', $safe)));
}
break;
case 'parent_id':
if ($value == 1) {
$query->_where[$grouping][] = "parent_id.parent_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Activities which have Followup Activities');
} elseif ($value == 2) {
$query->_where[$grouping][] = "parent_id.parent_id IS NULL";
$query->_qill[$grouping][] = ts('Activities without Followup Activities');
}
break;
case 'followup_parent_id':
if ($value == 1) {
$query->_where[$grouping][] = "civicrm_activity.parent_id IS NOT NULL";
$query->_qill[$grouping][] = ts('Activities which are Followup Activities');
} elseif ($value == 2) {
$query->_where[$grouping][] = "civicrm_activity.parent_id IS NULL";
$query->_qill[$grouping][] = ts('Activities which are not Followup Activities');
}
break;
}
}
function restWhere(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if (!CRM_Utils_Array::value($grouping, $this->_where)) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = array();
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$states = CRM_Core_PseudoConstant::stateProvince();
$value = $states[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$countries = CRM_Core_PseudoConstant::country();
$value = $countries[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$counties = CRM_Core_PseudoConstant::county();
$value = $counties[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$worldRegions = CRM_Core_PseudoConstant::worldRegion();
if (is_numeric($value)) {
$value = $worldRegions[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('World Region') . " {$op} '{$value}'";
} elseif ($name === 'individual_prefix') {
$individualPrefixs = CRM_Core_PseudoConstant::individualPrefix();
if (is_numeric($value)) {
$value = $individualPrefixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Prefix') . " {$op} '{$value}'";
} elseif ($name === 'individual_suffix') {
$individualSuffixs = CRM_Core_PseudoConstant::individualsuffix();
if (is_numeric($value)) {
$value = $individualSuffixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Suffix') . " {$op} '{$value}'";
} elseif ($name === 'gender') {
$genders = CRM_Core_PseudoConstant::gender();
if (is_numeric($value)) {
$value = $genders[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Gender') . " {$op} '{$value}'";
self::$_openedPanes['Demographics'] = TRUE;
} elseif ($name === 'birth_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes['Demographics'] = TRUE;
} elseif ($name === 'deceased_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes['Demographics'] = TRUE;
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes['Demographics'] = TRUE;
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}' AND contact_a.contact_type ='Individual'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$emailGreetings = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $emailGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Email Greeting') . " {$op} '{$value}'";
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$postalGreetings = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $postalGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Postal Greeting') . " {$op} '{$value}'";
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$addressee = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $addressee[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Addressee') . " {$op} '{$value}'";
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
// sometime the value is an array, need to investigate and fix
if (is_array($value)) {
CRM_Core_Error::fatal();
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
}
if ($wildcard) {
$value = "'%{$value}%'";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
$this->_whereTables[$tName] = $this->_tables[$tName];
$this->_qill[$grouping][] = "{$field['title']} {$op} '{$value}'";
} else {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
$type = NULL;
if (CRM_Utils_Array::value('type', $field)) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
if ($setTables) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
/**
* Returns an array of property objects for the requested class.
*
* @param String $class_name 'class_name' (string) A valid class name.
* @param Striing $filter filter' (string) Limits properties returned ("core", "custom", "default", "all).
*
* @return $property_object Array of property objects containing the properties like id ,name ,data_type, description;
*
* @access public
*/
function crm_get_class_properties($class_name = 'Individual', $filter = 'all')
{
_crm_initialize();
$property_object = array();
require_once "CRM/Contact/DAO/{$class_name}.php";
$error = eval('$fields = CRM_Contact_DAO_' . $class_name . '::fields( );');
if ($error) {
return _crm_error($error);
}
$id = -1;
foreach ($fields as $key => $values) {
$property_object[] = array("id" => $id, "name" => $key, "data_type" => CRM_Utils_Type::typeToString($values['type']), "description" => $values['title']);
}
if ($class_name == 'Individual' || $class_name == 'Organization' || $class_name == 'Household') {
eval('$fields = CRM_Contact_DAO_Contact::fields( );');
foreach ($fields as $key => $values) {
$property_object[] = array("id" => $id, "name" => $key, "data_type" => CRM_Utils_Type::typeToString($values['type']), "description" => $values['title']);
}
$fields = "";
}
if ($filter == 'custom' || $filter == 'all') {
require_once 'CRM/Core/BAO/CustomGroup.php';
$groupTree = CRM_Core_BAO_CustomGroup::getTree($class_name, null, -1);
foreach ($groupTree as $node) {
$fields = $node["fields"];
foreach ($fields as $key => $values) {
$property_object[] = array("id" => $values['id'], "name" => $values['name'], "data_type" => $values['data_type'], "description" => $values['help_post']);
}
}
}
return $property_object;
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
$where = $query->_where[$grouping][$key];
} else {
if (strstr($val, 'civicrm_event.id IN')) {
//extract the first event id if multiple events are selected
preg_match('/civicrm_event.id IN \\(\\"(\\d+)/', $val, $matches);
$value = $matches[1];
$where = $query->_where[$grouping][$key];
}
}
}
if ($exEventId) {
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
} else {
if (!empty($matches[1])) {
$value = $matches[1];
}
}
$where = $query->_where[$grouping][$key];
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
if (!empty($allEventIds)) {
$op = "IN";
$value = "(" . implode(",", $allEventIds) . ")";
}
}
$query->_where[$grouping][] = "{$where} OR civicrm_event.id {$op} {$value}";
$query->_qill[$grouping][] = ts('Include Repeating Events');
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'participant_is_test':
$key = array_search('civicrm_participant.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'participant_test':
// We dont want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_test", $op, $value, "Boolean");
$isTest = $value ? 'a Test' : 'not a Test';
$query->_qill[$grouping][] = ts("Participant is %1", array(1 => $isTest));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
}
return;
case 'participant_fee_id':
foreach ($value as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $value);
$query->_where[$grouping][] = "civicrm_participant.fee_level REGEXP '{$feeLabel}'";
$query->_qill[$grouping][] = ts("Fee level") . " IN " . implode(', ', $value);
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_amount_high':
case 'participant_fee_amount_low':
$query->numberRangeBuilder($values, 'civicrm_participant', 'participant_fee_amount', 'fee_amount', 'Fee Amount');
return;
case 'participant_status_id':
if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
$op = 'IN';
}
case 'participant_status':
case 'participant_source':
case 'participant_id':
case 'participant_contact_id':
case 'participant_is_pay_later':
case 'participant_fee_amount':
case 'participant_fee_level':
case 'participant_campaign_id':
$qillName = $name;
if (in_array($name, array('participant_status_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later', 'participant_campaign_id'))) {
$name = str_replace('participant_', '', $name);
if ($name == 'is_pay_later') {
$qillName = $name;
}
} elseif ($name == 'participant_status') {
$name = 'status_id';
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$tableName = empty($tableName) ? 'civicrm_participant' : $tableName;
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_role':
case 'participant_role_id':
$qillName = $name;
$name = 'role_id';
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
if (!strstr($op, 'NULL') && !strstr($op, 'EMPTY') && !strstr($op, 'LIKE')) {
$regexOp = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT REGEXP' : 'REGEXP';
$regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $regexOp, $regexp, 'String');
} else {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_register_date':
case 'participant_register_date_high':
case 'participant_register_date_low':
$query->dateQueryBuilder($values, 'civicrm_participant', 'participant_register_date', 'register_date', 'Register Date');
return;
case 'event_id':
case 'participant_event_id':
$name = str_replace('participant_', '', $name);
case 'event_is_public':
case 'event_type_id':
case 'event_title':
$qillName = $name;
if (in_array($name, array('event_id', 'event_title', 'event_is_public'))) {
$name = str_replace('event_', '', $name);
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.{$name}", $op, $value, $dataType);
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
if (!array_key_exists($qillName, $fields)) {
break;
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Event', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
return;
}
}
function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$states = CRM_Core_PseudoConstant::stateProvince();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $states[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $states);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']), $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.country_id";
} else {
$where = "civicrm_address.country_id";
}
$countries = CRM_Core_PseudoConstant::country();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $countries[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $countries);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.county_id";
} else {
$where = "civicrm_address.county_id";
}
$counties = CRM_Core_PseudoConstant::county();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $counties[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $counties);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$field['where'] = 'civicrm_worldregion.id';
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::worldRegion(), $field, ts('World Region'));
} elseif ($name === 'birth_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'deceased_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
// sometime the value is an array, need to investigate and fix
if (is_array($value)) {
CRM_Core_Error::fatal();
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
$this->_whereTables[$tName] = $this->_tables[$tName];
$this->_qill[$grouping][] = "{$field['title']} {$op} '{$value}'";
} else {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
