/**
* Process all string values in an array with HTMLPurifier. Arrays may be of any depth. If a string is passed it will be purified and returned.
*
* @param array $pm_array The array or string to purify
* @param array $pa_options Array of options:
* purifier = HTMLPurifier instance to use for processing. If null a new instance will be used. [Default is null]
* @return array The purified array
*/
function caPurifyArray($pa_array, $pa_options = null)
{
if (!is_array($pa_array)) {
return array();
}
if (!($o_purifier = caGetOption('purifier', $pa_options, null)) instanceof HTMLPurifier) {
$o_purifier = new HTMLPurifier();
}
if (!is_array($pa_array)) {
return $o_purifier->purify($pa_array);
}
foreach ($pa_array as $vn_k => $vm_v) {
if (is_array($vm_v)) {
$pa_array[$vn_k] = caPurifyArray($vm_v, $pa_options);
} else {
if (!is_null($vm_v)) {
$pa_array[$vn_k] = $o_purifier->purify($vm_v);
}
}
}
return $pa_array;
}
/**
* Find row(s) with fields having values matching specific values.
* Results can be returned as model instances, numeric ids or search results (when possible).
*
* Exact matching is performed using values in $pa_values. Partial and pattern matching are not supported. Searches may include
* multiple fields with boolean AND and OR. For example, you can find ca_objects rows with idno = 2012.001 and access = 1 by passing the
* "boolean" option as "AND" and $pa_values set to array("idno" => "2012.001", "access" => 1).
* You could find all rows with either the idno or the access values by setting "boolean" to "OR"
*
* BaseModel::find() is not a replacement for the SearchEngine. It is intended as a quick and convenient way to programatically fetch rows using
* simple, clear cut criteria. If you need to fetch rows based upon an identifer or status value BaseModel::find() will be quicker and less code than
* using the SearchEngine. For full-text searches, searches on attributes, or searches that require transformations or complex boolean operations use
* the SearchEngine.
*
* @param array $pa_values An array of values to match. Keys are field names. This must be an array with at least one key-value pair where the key is a valid field name for the model. If you pass an integer instead of an array it will be used as the primary key value for the table; result will be returned as "firstModelInstance" unless the returnAs option is explicitly set.
* @param array $pa_options Options are:
* transaction = optional Transaction instance. If set then all database access is done within the context of the transaction
* returnAs = what to return; possible values are:
* searchResult = a search result instance (aka. a subclass of BaseSearchResult), when the calling subclass is searchable (ie. <classname>Search and <classname>SearchResult classes are defined)
* ids = an array of ids (aka. primary keys)
* modelInstances = an array of instances, one for each match. Each instance is the same class as the caller, a subclass of BaseModel
* firstId = the id (primary key) of the first match. This is the same as the first item in the array returned by 'ids'
* firstModelInstance = the instance of the first match. This is the same as the first instance in the array returned by 'modelInstances'
* count = the number of matches
*
* The default is ids
*
* limit = if searchResult, ids or modelInstances is set, limits number of returned matches. Default is no limit
* boolean = determines how multiple field values in $pa_values are combined to produce the final result. Possible values are:
* AND = find rows that match all criteria in $pa_values
* OR = find rows that match any criteria in $pa_values
*
* The default is AND
*
* sort = field to sort on. Must be in <table>.<field> or <field> format and be an intrinsic field in the primary table. Sort order can be set using the sortDirection option.
* sortDirection = the direction of the sort. Values are ASC (ascending) and DESC (descending). Default is ASC.
* allowWildcards = consider "%" as a wildcard when searching. Any term including a "%" character will be queried using the SQL LIKE operator. [Default is false]
* purify = process text with HTMLPurifier before search. Purifier encodes &, < and > characters, and performs other transformations that can cause searches on literal text to fail. If you are purifying all input (the default) then leave this set true. [Default is true]
* purifyWithFallback = executes the search with "purify" set and falls back to search with unpurified text if nothing is found. [Default is false]
* checkAccess = array of access values to filter results by; if defined only items with the specified access code(s) are returned. Only supported for <table_name>.hierarchy.preferred_labels and <table_name>.children.preferred_labels because these returns sets of items. For <table_name>.parent.preferred_labels, which returns a single row at most, you should do access checking yourself. (Everything here applies equally to nonpreferred_labels)
*
* @return mixed Depending upon the returnAs option setting, an array, subclass of BaseModel or integer may be returned.
*/
public static function find($pa_values, $pa_options = null)
{
$t_instance = null;
$vs_table = get_called_class();
if (!is_array($pa_values) && (int) $pa_values > 0) {
$t_instance = new $vs_table();
$pa_values = array($t_instance->primaryKey() => (int) $pa_values);
if (!isset($pa_options['returnAs'])) {
$pa_options['returnAs'] = 'firstModelInstance';
}
}
if (!is_array($pa_values) || sizeof($pa_values) == 0) {
return null;
}
$ps_return_as = caGetOption('returnAs', $pa_options, 'ids', array('forceLowercase' => true, 'validValues' => array('searchResult', 'ids', 'modelInstances', 'firstId', 'firstModelInstance', 'count')));
$ps_boolean = caGetOption('boolean', $pa_options, 'and', array('forceLowercase' => true, 'validValues' => array('and', 'or')));
$o_trans = caGetOption('transaction', $pa_options, null);
$pa_check_access = caGetOption('checkAccess', $pa_options, null);
if (!$t_instance) {
$t_instance = new $vs_table();
}
if ($o_trans) {
$t_instance->setTransaction($o_trans);
}
$va_sql_wheres = array();
$vb_purify_with_fallback = caGetOption('purifyWithFallback', $pa_options, false);
$vb_purify = $vb_purify_with_fallback ? true : caGetOption('purify', $pa_options, true);
if ($vb_purify) {
$pa_values = caPurifyArray($pa_values);
}
$va_sql_params = array();
//
// Convert type id
//
$vs_type_field_name = null;
if (method_exists($t_instance, "getTypeFieldName")) {
$vs_type_field_name = $t_instance->getTypeFieldName();
if (!is_array($pa_values[$vs_type_field_name]) && array_key_exists($vs_type_field_name, $pa_values)) {
$pa_values[$vs_type_field_name] = array($pa_values[$vs_type_field_name]);
}
if (is_array($pa_values[$vs_type_field_name])) {
foreach ($pa_values[$vs_type_field_name] as $vn_i => $vm_value) {
if (!is_numeric($vm_value)) {
if ($vn_id = ca_lists::getItemID($t_instance->getTypeListCode(), $vm_value)) {
$pa_values[$vs_type_field_name][$vn_i] = $vn_id;
}
}
}
}
}
//
// Convert other intrinsic list references
//
foreach ($pa_values as $vs_field => $vm_value) {
if ($vs_field == $vs_type_field_name) {
continue;
}
if ($vs_list_code = $t_instance->getFieldInfo($vs_field, 'LIST_CODE')) {
if (!is_array($vm_value)) {
$pa_values[$vs_field] = $vm_value = array($vm_value);
}
foreach ($vm_value as $vn_i => $vm_ivalue) {
if (is_numeric($vm_ivalue)) {
continue;
}
if ($vn_id = ca_lists::getItemID($vs_list_code, $vm_ivalue)) {
$pa_values[$vs_field][$vn_i] = $vn_id;
}
}
}
}
foreach ($pa_values as $vs_field => $vm_value) {
//if (is_array($vm_value)) { continue; }
# support case where fieldname is in format table.fieldname
if (preg_match("/([\\w_]+)\\.([\\w_]+)/", $vs_field, $va_matches)) {
if ($va_matches[1] != $vs_table) {
if ($t_instance->_DATAMODEL->tableExists($va_matches[1])) {
return false;
} else {
return false;
}
}
$vs_field = $va_matches[2];
# get field name alone
}
if (!$t_instance->hasField($vs_field)) {
return false;
}
if ($t_instance->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
if (is_array($vm_value)) {
foreach ($vm_value as $vn_i => $vm_ivalue) {
$vm_value[$vn_i] = intval($vm_ivalue);
}
} else {
if (!is_null($vm_value)) {
$vm_value = intval($vm_value);
}
}
}
} else {
if (is_array($vm_value) && sizeof($vm_value)) {
foreach ($vm_value as $vn_i => $vm_ivalue) {
$vm_value[$vn_i] = $t_instance->quote($vs_field, $vm_ivalue);
}
} else {
$vm_value = $t_instance->quote($vs_field, is_null($vm_value) ? '' : $vm_value);
}
}
if (is_null($vm_value)) {
$va_sql_wheres[] = "({$vs_field} IS NULL)";
} else {
if ($vm_value === '') {
continue;
}
if (is_array($vm_value)) {
if (!sizeof($vm_value)) {
continue;
}
$va_sql_wheres[] = "({$vs_field} IN (" . join(",", $vm_value) . "))";
} elseif (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$va_sql_wheres[] = "({$vs_field} LIKE {$vm_value})";
} else {
$va_sql_wheres[] = "({$vs_field} = {$vm_value})";
}
}
}
if (!sizeof($va_sql_wheres)) {
return null;
}
if (is_array($pa_check_access) && sizeof($pa_check_access) && $t_instance->hasField('access')) {
$va_sql_wheres[] = "({$vs_table}.access IN (?))";
$va_sql_params[] = $pa_check_access;
}
$vs_deleted_sql = $t_instance->hasField('deleted') ? '(deleted = 0) AND ' : '';
$vs_sql = "SELECT * FROM {$vs_table} WHERE {$vs_deleted_sql} (" . join(" {$ps_boolean} ", $va_sql_wheres) . ")";
$vs_orderby = '';
if ($vs_sort = caGetOption('sort', $pa_options, null)) {
$vs_sort_direction = caGetOption('sortDirection', $pa_options, 'ASC', array('validValues' => array('ASC', 'DESC')));
$va_tmp = explode(".", $vs_sort);
if (sizeof($va_tmp) > 0) {
switch ($va_tmp[0]) {
case $vs_table:
if ($t_instance->hasField($va_tmp[1])) {
$vs_orderby = " ORDER BY {$vs_sort} {$vs_sort_direction}";
}
break;
default:
if (sizeof($va_tmp) == 1) {
if ($t_instance->hasField($va_tmp[0])) {
$vs_orderby = " ORDER BY {$vs_sort} {$vs_sort_direction}";
}
}
break;
}
}
if ($vs_orderby) {
$vs_sql .= $vs_orderby;
}
}
if (isset($pa_options['transaction']) && $pa_options['transaction'] instanceof Transaction) {
$o_db = $pa_options['transaction']->getDb();
} else {
$o_db = new Db();
}
$vn_limit = isset($pa_options['limit']) && (int) $pa_options['limit'] > 0 ? (int) $pa_options['limit'] : null;
$qr_res = $o_db->query($vs_sql, $va_sql_params);
if ($vb_purify_with_fallback && $qr_res->numRows() == 0) {
return self::find($pa_values, array_merge($pa_options, ['purifyWithFallback' => false, 'purify' => false]));
}
$vn_c = 0;
$vs_pk = $t_instance->primaryKey();
switch ($ps_return_as) {
case 'firstmodelinstance':
while ($qr_res->nextRow()) {
$t_instance = new $vs_table();
if ($o_trans) {
$t_instance->setTransaction($o_trans);
}
if ($t_instance->load((int) $qr_res->get($vs_pk))) {
return $t_instance;
}
}
return null;
break;
case 'modelinstances':
$va_instances = array();
while ($qr_res->nextRow()) {
$t_instance = new $vs_table();
if ($o_trans) {
$t_instance->setTransaction($o_trans);
}
if ($t_instance->load($qr_res->get($vs_pk))) {
$va_instances[] = $t_instance;
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
}
return $va_instances;
break;
case 'firstid':
if ($qr_res->nextRow()) {
return $qr_res->get($vs_pk);
}
return null;
break;
case 'count':
return $qr_res->numRows();
break;
default:
case 'ids':
case 'searchresult':
$va_ids = array();
while ($qr_res->nextRow()) {
$va_ids[] = $qr_res->get($vs_pk);
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
if ($ps_return_as == 'searchresult') {
if (sizeof($va_ids) > 0) {
return $t_instance->makeSearchResult($t_instance->tableName(), $va_ids);
}
return null;
} else {
return $va_ids;
}
break;
}
return null;
}
/**
* Find row(s) with fields having values matching specific values.
* Results can be returned as model instances, numeric ids or search results (when possible).
*
* Exact matching is performed using values in $pa_values. Partial and pattern matching are not supported. Searches may include
* multiple fields with boolean AND and OR. For example, you can find ca_objects rows with idno = 2012.001 and access = 1 by passing the
* "boolean" option as "AND" and $pa_values set to array("idno" => "2012.001", "access" => 1).
* You could find all rows with either the idno or the access values by setting "boolean" to "OR"
*
* Keys in the $pa_values parameters must be valid fields in the table which the model sub-class represents. You may also search on preferred and
* non-preferred labels by specified keys and values for label table fields in "preferred_labels" and "nonpreferred_labels" sub-arrays. For example:
*
* array("idno" => 2012.001", "access" => 1, "preferred_labels" => array("name" => "Luna Park at Night"))
*
* will find rows with the idno, access and preferred label values.
*
* LabelableBaseModelWithAttributes::find() is not a replacement for the SearchEngine. It is intended as a quick and convenient way to programatically fetch rows using
* simple, clear cut criteria. If you need to fetch rows based upon an identifer or status value LabelableBaseModelWithAttributes::find() will be quicker and less code than
* using the SearchEngine. For full-text searches, searches on attributes, or searches that require transformations or complex boolean operations use
* the SearchEngine.
*
* @param array $pa_values An array of values to match. Keys are field names, metadata element codes or preferred_labels and /or nonpreferred_labels. This must be an array with at least one key-value pair where the key is a valid field name for the model. If you pass an integer instead of an array it will be used as the primary key value for the table; result will be returned as "firstModelInstance" unless the returnAs option is explicitly set.
* @param array $pa_options Options are:
* transaction = optional Transaction instance. If set then all database access is done within the context of the transaction
* returnAs = what to return; possible values are:
* searchResult = a search result instance (aka. a subclass of BaseSearchResult), when the calling subclass is searchable (ie. <classname>Search and <classname>SearchResult classes are defined)
* ids = an array of ids (aka. primary keys)
* modelInstances = an array of instances, one for each match. Each instance is the same class as the caller, a subclass of BaseModel
* firstId = the id (primary key) of the first match. This is the same as the first item in the array returned by 'ids'
* firstModelInstance = the instance of the first match. This is the same as the first instance in the array returned by 'modelInstances'
* count = the number of matches
*
* The default is ids
*
* limit = if searchResult, ids or modelInstances is set, limits number of returned matches. Default is no limit
* boolean = determines how multiple field values in $pa_values are combined to produce the final result. Possible values are:
* AND = find rows that match all criteria in $pa_values
* OR = find rows that match any criteria in $pa_values
*
* The default is AND
*
* labelBoolean = determines how multiple field values in $pa_values['preferred_labels'] and $pa_values['nonpreferred_labels'] are combined to produce the final result. Possible values are:
* AND = find rows that match all criteria in $pa_values['preferred_labels']/$pa_values['nonpreferred_labels']
* OR = find rows that match any criteria in $pa_values['preferred_labels']/$pa_values['nonpreferred_labels']
*
* The default is AND
*
* sort = field to sort on. Must be in <table>.<field> format and be an intrinsic field in either the primary table or the label table. Sort order can be set using the sortDirection option.
* sortDirection = the direction of the sort. Values are ASC (ascending) and DESC (descending). [Default is ASC]
* allowWildcards = consider "%" as a wildcard when searching. Any term including a "%" character will be queried using the SQL LIKE operator. [Default is false]
* purify = process text with HTMLPurifier before search. Purifier encodes &, < and > characters, and performs other transformations that can cause searches on literal text to fail. If you are purifying all input (the default) then leave this set true. [Default is true]
* purifyWithFallback = executes the search with "purify" set and falls back to search with unpurified text if nothing is found. [Default is false]
* checkAccess = array of access values to filter results by; if defined only items with the specified access code(s) are returned. Only supported for <table_name>.hierarchy.preferred_labels and <table_name>.children.preferred_labels because these returns sets of items. For <table_name>.parent.preferred_labels, which returns a single row at most, you should do access checking yourself. (Everything here applies equally to nonpreferred_labels)
* restrictToTypes = Restrict returned items to those of the specified types. An array of list item idnos and/or item_ids may be specified. [Default is null]
*
* @return mixed Depending upon the returnAs option setting, an array, subclass of LabelableBaseModelWithAttributes or integer may be returned.
*/
public static function find($pa_values, $pa_options = null)
{
$t_instance = null;
$vs_table = get_called_class();
if (!is_array($pa_values) && (int) $pa_values > 0) {
$t_instance = new $vs_table();
$pa_values = array($t_instance->primaryKey() => (int) $pa_values);
if (!isset($pa_options['returnAs'])) {
$pa_options['returnAs'] = 'firstModelInstance';
}
}
if (!is_array($pa_values) || sizeof($pa_values) == 0) {
return null;
}
$ps_return_as = caGetOption('returnAs', $pa_options, 'ids', array('forceLowercase' => true, 'validValues' => array('searchResult', 'ids', 'modelInstances', 'firstId', 'firstModelInstance', 'count')));
$ps_boolean = caGetOption('boolean', $pa_options, 'and', array('forceLowercase' => true, 'validValues' => array('and', 'or')));
$ps_label_boolean = caGetOption('labelBoolean', $pa_options, 'and', array('forceLowercase' => true, 'validValues' => array('and', 'or')));
$ps_sort = caGetOption('sort', $pa_options, null);
$pa_check_access = caGetOption('checkAccess', $pa_options, null);
if (!$t_instance) {
$t_instance = new $vs_table();
}
$vn_table_num = $t_instance->tableNum();
$vs_table_pk = $t_instance->primaryKey();
$va_sql_params = array();
$vs_type_restriction_sql = '';
if ($va_restrict_to_types = caGetOption('restrictToTypes', $pa_options, null)) {
if (is_array($va_restrict_to_types = caMakeTypeIDList($vs_table, $va_restrict_to_types)) && sizeof($va_restrict_to_types)) {
$vs_type_restriction_sql = " {$vs_table}." . $t_instance->getTypeFieldName() . " IN (?) AND ";
$va_sql_params[] = $va_restrict_to_types;
}
}
if (!($t_label = $t_instance->getLabelTableInstance())) {
if ($t_instance->ATTRIBUTE_TYPE_ID_FLD && is_array($va_restrict_to_types) && sizeof($va_restrict_to_types)) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] = $va_restrict_to_types;
}
return parent::find($pa_values, $pa_options);
}
$vs_label_table = $t_label->tableName();
$vs_label_table_pk = $t_label->primaryKey();
$vb_has_simple_fields = false;
foreach ($pa_values as $vs_field => $vm_value) {
if (!is_array($vm_value) && $t_instance->hasField($vs_field)) {
$vb_has_simple_fields = true;
break;
}
}
$vb_has_label_fields = false;
foreach ($pa_values as $vs_field => $vm_value) {
if (in_array($vs_field, array('preferred_labels', 'nonpreferred_labels')) && is_array($vm_value) && sizeof($vm_value)) {
$vb_has_label_fields = true;
break;
}
}
$vs_sort_proc = $ps_sort;
if (preg_match("!^{$vs_table}.preferred_labels[\\.]{0,1}(.*)!", $ps_sort, $va_matches) || preg_match("!^{$vs_table}.nonpreferred_labels[\\.]{0,1}(.*)!", $ps_sort, $va_matches)) {
$vs_sort_proc = $va_matches[1] && $t_label->hasField($va_matches[1]) ? "{$vs_label_table}." . $va_matches[1] : "{$vs_label_table}." . $t_label->getDisplayField();
$vb_has_label_fields = true;
}
$vb_has_attributes = false;
$va_element_codes = $t_instance->getApplicableElementCodes(null, true, false);
foreach ($pa_values as $vs_field => $vm_value) {
if (!is_array($vm_value) && in_array($vs_field, $va_element_codes)) {
$vb_has_attributes = true;
break;
}
}
$va_joins = array();
$vb_purify_with_fallback = caGetOption('purifyWithFallback', $pa_options, false);
$vb_purify = $vb_purify_with_fallback ? true : caGetOption('purify', $pa_options, true);
if ($vb_purify) {
$pa_values = caPurifyArray($pa_values);
}
if ($vb_has_simple_fields) {
//
// Convert type id
//
if ($t_instance->ATTRIBUTE_TYPE_LIST_CODE) {
if (isset($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD]) && !is_numeric($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD])) {
if (!is_array($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD])) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] = array($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD]);
}
foreach ($pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] as $vn_i => $vm_value) {
if (!is_numeric($vm_value)) {
if ($vn_id = ca_lists::getItemID($t_instance->ATTRIBUTE_TYPE_LIST_CODE, $vm_value)) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD][$vn_i] = $vn_id;
}
}
}
}
}
//
// Convert other intrinsic list references
//
foreach ($pa_values as $vs_field => $vm_value) {
if ($vs_field == $t_instance->ATTRIBUTE_TYPE_ID_FLD) {
continue;
}
if ($vs_list_code = $t_instance->getFieldInfo($vs_field, 'LIST_CODE')) {
if (!is_array($vm_value)) {
$pa_values[$vs_field] = $vm_value = array($vm_value);
}
foreach ($vm_value as $vn_i => $vm_ivalue) {
if (is_numeric($vm_ivalue)) {
continue;
}
if ($vn_id = ca_lists::getItemID($vs_list_code, $vm_ivalue)) {
$pa_values[$vs_field][$vn_i] = $vn_id;
}
}
}
}
}
$va_sql_wheres = array();
if ($vb_has_simple_fields && !$vb_has_attributes && !$vb_has_label_fields) {
if ($t_instance->ATTRIBUTE_TYPE_ID_FLD && is_array($va_restrict_to_types) && sizeof($va_restrict_to_types)) {
$pa_values[$t_instance->ATTRIBUTE_TYPE_ID_FLD] = $va_restrict_to_types;
}
return parent::find($pa_values, $pa_options);
}
$va_label_sql = array();
if ($vb_has_label_fields) {
$va_joins[] = " INNER JOIN {$vs_label_table} ON {$vs_label_table}.{$vs_table_pk} = {$vs_table}.{$vs_table_pk} ";
if (isset($pa_values['preferred_labels']) && is_array($pa_values['preferred_labels'])) {
$va_sql_wheres[] = "({$vs_label_table}.is_preferred = 1)";
foreach ($pa_values['preferred_labels'] as $vs_field => $vm_value) {
if (!$t_label->hasField($vs_field)) {
return false;
}
if ($t_label->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
$vm_value = intval($vm_value);
}
} else {
$vm_value = $t_label->quote($vs_field, is_null($vm_value) ? '' : $vm_value);
}
if (is_null($vm_value)) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} IS NULL)";
} elseif (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} LIKE {$vm_value})";
} else {
if ($vm_value === '') {
continue;
}
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} = {$vm_value})";
}
}
$va_label_sql[] = "(" . join(" {$ps_label_boolean} ", $va_sql_wheres) . ")";
$va_sql_wheres = array();
}
if (isset($pa_values['nonpreferred_labels']) && is_array($pa_values['nonpreferred_labels'])) {
$va_sql_wheres[] = "({$vs_label_table}.is_preferred = 0)";
foreach ($pa_values['nonpreferred_labels'] as $vs_field => $vm_value) {
if (!$t_label->hasField($vs_field)) {
return false;
}
if ($t_label->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
$vm_value = intval($vm_value);
}
} else {
$vm_value = $t_label->quote($vs_field, is_null($vm_value) ? '' : $vm_value);
}
if (is_null($vm_value)) {
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} IS NULL)";
} else {
if ($vm_value === '') {
continue;
}
$va_sql_wheres[] = "({$vs_label_table}.{$vs_field} = {$vm_value})";
}
}
$va_label_sql[] = "(" . join(" {$ps_label_boolean} ", $va_sql_wheres) . ")";
$va_sql_wheres = array();
}
}
if ($vb_has_simple_fields) {
foreach ($pa_values as $vs_field => $vm_value) {
//if (is_array($vm_value)) { continue; }
if (!$t_instance->hasField($vs_field)) {
continue;
}
if ($t_instance->_getFieldTypeType($vs_field) == 0) {
if (!is_numeric($vm_value) && !is_null($vm_value)) {
if (is_array($vm_value)) {
foreach ($vm_value as $vn_i => $vm_ivalue) {
$vm_value[$vn_i] = intval($vm_ivalue);
}
} else {
if (!is_null($vm_value)) {
$vm_value = intval($vm_value);
}
}
}
}
if (is_null($vm_value)) {
$va_label_sql[] = "({$vs_table}.{$vs_field} IS NULL)";
} elseif (caGetOption('allowWildcards', $pa_options, false) && !is_array($vm_value) && strpos($vm_value, '%') !== false) {
$va_label_sql[] = "({$vs_table}.{$vs_field} LIKE ?)";
$va_sql_params[] = $vm_value;
} else {
if ($vm_value === '') {
continue;
}
if (is_array($vm_value)) {
if (!sizeof($vm_value)) {
continue;
}
$va_label_sql[] = "({$vs_table}.{$vs_field} IN (?))";
} else {
$va_label_sql[] = "({$vs_table}.{$vs_field} = ?)";
}
$va_sql_params[] = $vm_value;
}
}
}
if ($vb_has_attributes) {
$va_joins[] = " INNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_table}.{$vs_table_pk} AND ca_attributes.table_num = {$vn_table_num} ";
$va_joins[] = " INNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id ";
foreach ($pa_values as $vs_field => $vm_value) {
if (($vn_element_id = array_search($vs_field, $va_element_codes)) !== false) {
$vs_q = " ca_attribute_values.element_id = {$vn_element_id} AND ";
switch ($vn_datatype = $t_instance->_getElementDatatype($vs_field)) {
case 0:
// continue
// continue
case 15:
// media
// media
case 16:
// file
// SKIP
continue 2;
break;
case 2:
// date
if (is_array($va_date = caDateToHistoricTimestamps($vm_value))) {
$vs_q .= "((ca_attribute_values.value_decimal1 BETWEEN ? AND ?) OR (ca_attribute_values.value_decimal2 BETWEEN ? AND ?))";
array_push($va_sql_params, $va_date['start'], $va_date['end'], $va_date['start'], $va_date['end']);
} else {
continue 2;
}
break;
case 3:
// list
if ($t_element = $t_instance->_getElementInstance($vs_field)) {
$vn_item_id = is_numeric($vm_value) ? (int) $vm_value : (int) caGetListItemID($t_element->get('list_id'), $vm_value);
$vs_q .= "(ca_attribute_values.item_id = ?)";
$va_sql_params[] = $vn_item_id;
}
break;
default:
if (!($vs_fld = Attribute::getSortFieldForDatatype($vn_datatype))) {
$vs_fld = 'value_longtext1';
}
if (caGetOption('allowWildcards', $pa_options, false) && strpos($vm_value, '%') !== false) {
$vs_q .= "(ca_attribute_values.{$vs_fld} LIKE ?)";
} else {
$vs_q .= "(ca_attribute_values.{$vs_fld} = ?)";
}
$va_sql_params[] = (string) $vm_value;
break;
}
$va_label_sql[] = "({$vs_q})";
}
}
}
if (!sizeof($va_label_sql)) {
return null;
}
if (is_array($pa_check_access) && sizeof($pa_check_access) && $t_instance->hasField('access')) {
$va_label_sql[] = "({$vs_table}.access IN (?))";
$va_sql_params[] = $pa_check_access;
}
$vs_deleted_sql = $t_instance->hasField('deleted') ? "({$vs_table}.deleted = 0) AND " : '';
$vs_sql = "SELECT * FROM {$vs_table}";
$vs_sql .= join("\n", $va_joins);
$vs_sql .= " WHERE {$vs_deleted_sql} {$vs_type_restriction_sql} (" . join(" {$ps_boolean} ", $va_label_sql) . ")";
$vs_orderby = '';
if ($vs_sort_proc) {
$vs_sort_direction = caGetOption('sortDirection', $pa_options, 'ASC', array('validValues' => array('ASC', 'DESC')));
$va_tmp = explode(".", $vs_sort_proc);
if (sizeof($va_tmp) == 2) {
switch ($va_tmp[0]) {
case $vs_table:
if ($t_instance->hasField($va_tmp[1])) {
$vs_orderby = " ORDER BY {$vs_sort_proc} {$vs_sort_direction}";
}
break;
case $vs_label_table:
if ($t_label->hasField($va_tmp[1])) {
$vs_orderby = " ORDER BY {$vs_sort_proc} {$vs_sort_direction}";
}
break;
}
}
if ($vs_orderby) {
$vs_sql .= $vs_orderby;
}
}
if (isset($pa_options['transaction']) && $pa_options['transaction'] instanceof Transaction) {
$o_db = $pa_options['transaction']->getDb();
} else {
$o_db = new Db();
}
$vn_limit = isset($pa_options['limit']) && (int) $pa_options['limit'] > 0 ? (int) $pa_options['limit'] : null;
$qr_res = $o_db->query($vs_sql, $va_sql_params);
if ($vb_purify_with_fallback && $qr_res->numRows() == 0) {
return self::find($pa_values, array_merge($pa_options, ['purifyWithFallback' => false, 'purify' => false]));
}
$vn_c = 0;
$vs_pk = $t_instance->primaryKey();
switch ($ps_return_as) {
case 'firstmodelinstance':
while ($qr_res->nextRow()) {
$o_instance = new $vs_table();
if ($o_instance->load($qr_res->get($vs_pk))) {
return $o_instance;
}
}
return null;
break;
case 'modelinstances':
$va_instances = array();
while ($qr_res->nextRow()) {
$o_instance = new $vs_table();
if ($o_instance->load($qr_res->get($vs_pk))) {
$va_instances[] = $o_instance;
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
}
return $va_instances;
break;
case 'firstid':
if ($qr_res->nextRow()) {
return $qr_res->get($vs_pk);
}
return null;
break;
case 'count':
return $qr_res->numRows();
break;
default:
case 'ids':
case 'searchresult':
$va_ids = array();
while ($qr_res->nextRow()) {
$va_ids[] = $qr_res->get($vs_pk);
$vn_c++;
if ($vn_limit && $vn_c >= $vn_limit) {
break;
}
}
if ($ps_return_as == 'searchresult') {
if (sizeof($va_ids) > 0) {
return $t_instance->makeSearchResult($t_instance->tableName(), $va_ids);
}
return null;
} else {
return $va_ids;
}
break;
}
}
