public function testGets()
{
$vm_ret = $this->opt_object->get('ca_objects.type_id', array('convertCodesToDisplayText' => true));
$this->assertEquals('Moving Image', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.preferred_labels');
$this->assertEquals('My test moving image', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.duration');
$this->assertEquals('0:23:28', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.access');
$this->assertEquals('0', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.access', array('convertCodesToDisplayText' => true));
$this->assertEquals('not accessible to public', $vm_ret);
$o_tep = new TimeExpressionParser();
$vn_now = time();
$vm_ret = $this->opt_object->get('ca_objects.lastModified');
$this->assertTrue($o_tep->parse($vm_ret));
$va_modified_unix = $o_tep->getUnixTimestamps();
//$this->assertEquals($vn_now, $va_modified_unix['start'], 'lastModified timestamp cannot be more than 1 minute off', 60);
}
public function testGets()
{
$vm_ret = $this->opt_object->get('ca_objects.type_id', array('convertCodesToDisplayText' => true));
$this->assertEquals('Moving Image', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.preferred_labels');
$this->assertEquals('My test moving image', $vm_ret);
$vm_ret = $this->opt_object->get('ca_objects.duration');
$this->assertEquals('0:23:28', $vm_ret);
$vm_ret = $this->opt_object->get('ca_item_comments.comment');
$this->assertEquals('I like this very much.', $vm_ret);
$this->assertTrue(!is_numeric($this->opt_object->get('ca_item_comments.created_on')));
// should always be current date/time as text
$o_tep = new TimeExpressionParser();
$vn_now = time();
$vm_ret = $this->opt_object->get('ca_objects.lastModified');
$this->assertTrue($o_tep->parse($vm_ret));
$va_modified_unix = $o_tep->getUnixTimestamps();
//$this->assertEquals($vn_now, $va_modified_unix['start'], 'lastModified timestamp cannot be more than 1 minute off', 60);
}
/**
*
*/
public function search($ps_datetime_expression, $ps_code = null)
{
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($ps_datetime_expression)) {
list($vn_period_start, $vn_period_end) = $o_tep->getUnixTimestamps();
if ($vn_period_start && $vn_period_end) {
$o_db = new Db();
$qr_log = $o_db->query("\n\t\t\t\t\tSELECT sl.*, u.fname, u.lname, u.email, sfl.name form\n\t\t\t\t\tFROM ca_search_log sl\n\t\t\t\t\tLEFT JOIN ca_users AS u ON sl.user_id = u.user_id\n\t\t\t\t\tLEFT JOIN ca_search_form_labels AS sfl ON sl.form_id = sfl.form_id\n\t\t\t\t\tWHERE \n\t\t\t\t\t\t(log_datetime BETWEEN {$vn_period_start} AND {$vn_period_end})\n\t\t\t\t\tORDER BY log_datetime DESC\n\t\t\t\t");
$o_dm = Datamodel::load();
$va_rows = array();
while ($qr_log->nextRow()) {
$va_row = $qr_log->getRow();
$t_table = $o_dm->getInstanceByTableNum($va_row['table_num'], true);
$va_row['table_name'] = $t_table->getProperty('NAME_PLURAL');
$va_row['user_name'] = $va_row['fname'] . ' ' . $va_row['lname'];
$va_rows[$va_row['search_id']] = $va_row;
}
return $va_rows;
}
}
return null;
}
/**
* Set field value(s) for the table row represented by this object
*
* @param string|array string $pa_fields representation of a field name
* or array of string representations of field names
* @param mixed $pm_value value to set the given field(s) to
* @param array $pa_options associative array of options
* possible options (keys):
* when dealing with date/time fields:
* - SET_DIRECT_DATE
* - SET_DIRECT_TIME
* - SET_DIRECT_TIMES
*
* for media/files fields:
* - original_filename : (note that it is lower case) optional parameter which enables you to pass the original filename of a file, in addition to the representation in the temporary, global _FILES array;
*
* for text fields:
* - purify : if set then text input is run through HTML Purifier before being set
*
* for parent_id field:
* - treatParentIDAsIdno: force parent_id value to be used as idno lookup rather than a primary key value
*/
public function set($pa_fields, $pm_value = "", $pa_options = null)
{
$this->errors = array();
if (!is_array($pa_fields)) {
$pa_fields = array($pa_fields => $pm_value);
}
foreach ($pa_fields as $vs_field => $vm_value) {
if (array_key_exists($vs_field, $this->FIELDS)) {
$pa_fields_type = $this->getFieldInfo($vs_field, "FIELD_TYPE");
$pb_need_reload = false;
if (!$this->verifyFieldValue($vs_field, $vm_value, $pb_need_reload)) {
return false;
}
if ($pb_need_reload) {
return true;
}
// was set to default
if ($vs_field == $this->primaryKey()) {
$vm_value = preg_replace("/[\"']/", "", $vm_value);
}
$vs_cur_value = isset($this->_FIELD_VALUES[$vs_field]) ? $this->_FIELD_VALUES[$vs_field] : null;
switch ($pa_fields_type) {
case FT_NUMBER:
if ($vs_cur_value != $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
if ($vs_field == $this->HIERARCHY_PARENT_ID_FLD && (strlen($vm_value) > 0 && (!is_numeric($vm_value) || caGetOption('treatParentIDAsIdno', $pa_options, false)))) {
if (is_array($va_ids = call_user_func_array($this->tableName() . "::find", array(array('idno' => $vm_value, 'deleted' => 0), array('returnAs' => 'ids', 'transaction' => $this->getTransaction()))))) {
$vm_value = array_shift($va_ids);
}
}
if ($vm_value !== "" || $this->getFieldInfo($vs_field, "IS_NULL") && $vm_value == "") {
if ($vm_value) {
if (($vs_list_code = $this->getFieldInfo($vs_field, "LIST_CODE")) && !is_numeric($vm_value)) {
// translate ca_list_item idno's into item_ids if necessary
if ($vn_id = ca_lists::getItemID($vs_list_code, $vm_value)) {
$vm_value = $vn_id;
} else {
$this->postError(1103, _t('Value %1 is not in list %2', $vm_value, $vs_list_code), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
} else {
$vm_orig_value = $vm_value;
$vm_value = preg_replace("/[^\\d-.]+/", "", $vm_value);
# strip non-numeric characters
if (!preg_match("/^[\\-]{0,1}[\\d.]+\$/", $vm_value)) {
$this->postError(1100, _t("'%1' for %2 is not numeric", $vm_orig_value, $vs_field), "BaseModel->set()", $this->tableName() . '.' . $vs_field);
return false;
}
}
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
}
break;
case FT_BIT:
if ($vs_cur_value != $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value ? 1 : 0;
break;
case FT_DATETIME:
case FT_HISTORIC_DATETIME:
case FT_DATE:
case FT_HISTORIC_DATE:
if ($this->DIRECT_DATETIMES || $pa_options["SET_DIRECT_DATE"]) {
$this->_FIELD_VALUES[$vs_field] = $vm_value;
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = null;
} else {
$o_tep = new TimeExpressionParser();
if ($pa_fields_type == FT_DATE || $pa_fields_type == FT_HISTORIC_DATE) {
$va_timestamps = $o_tep->parseDate($vm_value);
} else {
$va_timestamps = $o_tep->parseDatetime($vm_value);
}
if (!$va_timestamps) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
if ($pa_fields_type == FT_HISTORIC_DATETIME || $pa_fields_type == FT_HISTORIC_DATE) {
if ($vs_cur_value != $va_timestamps["start"]) {
$this->_FIELD_VALUES[$vs_field] = $va_timestamps["start"];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
} else {
$va_timestamps = $o_tep->getUnixTimestamps();
if ($va_timestamps[0] == -1) {
$this->postError(1830, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
if ($vs_cur_value != $va_timestamps["start"]) {
$this->_FIELD_VALUES[$vs_field] = $va_timestamps["start"];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
}
}
break;
case FT_TIME:
if ($this->DIRECT_TIMES || $pa_options["SET_DIRECT_TIME"]) {
$this->_FIELD_VALUES[$vs_field] = $vm_value;
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseTime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
$va_times = $o_tep->getTimes();
if ($vs_cur_value != $va_times['start']) {
$this->_FIELD_VALUES[$vs_field] = $va_times['start'];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
}
break;
case FT_TIMESTAMP:
# can't set timestamp
break;
case FT_DATERANGE:
case FT_HISTORIC_DATERANGE:
$vs_start_field_name = $this->getFieldInfo($vs_field, "START");
$vs_end_field_name = $this->getFieldInfo($vs_field, "END");
$vn_start_date = isset($this->_FIELD_VALUES[$vs_start_field_name]) ? $this->_FIELD_VALUES[$vs_start_field_name] : null;
$vn_end_date = isset($this->_FIELD_VALUES[$vs_end_field_name]) ? $this->_FIELD_VALUES[$vs_end_field_name] : null;
if ($this->DIRECT_DATETIMES || $pa_options["SET_DIRECT_DATE"]) {
if (is_array($vm_value) && sizeof($vm_value) == 2 && $vm_value[0] <= $vm_value[1]) {
if ($vn_start_date != $vm_value[0]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $vm_value[0];
}
if ($vn_end_date != $vm_value[1]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $vm_value[1];
}
} else {
$this->postError(1100, _t("Invalid direct date values"), "BaseModel->set()", $this->tableName() . '.' . $vs_field);
}
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vn_start_date || $vn_end_date) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_start_field_name] = null;
$this->_FIELD_VALUES[$vs_end_field_name] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseDatetime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
if ($pa_fields_type == FT_HISTORIC_DATERANGE) {
$va_timestamps = $o_tep->getHistoricTimestamps();
} else {
$va_timestamps = $o_tep->getUnixTimestamps();
if ($va_timestamps[0] == -1) {
$this->postError(1830, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
}
if ($vn_start_date != $va_timestamps["start"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $va_timestamps["start"];
}
if ($vn_end_date != $va_timestamps["end"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $va_timestamps["end"];
}
}
}
break;
case FT_TIMERANGE:
$vs_start_field_name = $this->getFieldInfo($vs_field, "START");
$vs_end_field_name = $this->getFieldInfo($vs_field, "END");
if ($this->DIRECT_TIMES || $pa_options["SET_DIRECT_TIMES"]) {
if (is_array($vm_value) && sizeof($vm_value) == 2 && $vm_value[0] <= $vm_value[1]) {
if ($this->_FIELD_VALUES[$vs_start_field_name] != $vm_value[0]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $vm_value[0];
}
if ($this->_FIELD_VALUES[$vs_end_field_name] != $vm_value[1]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $vm_value[1];
}
} else {
$this->postError(1100, _t("Invalid direct time values"), "BaseModel->set()", $this->tableName() . '.' . $vs_field);
}
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($this->_FIELD_VALUES[$vs_start_field_name] || $this->_FIELD_VALUES[$vs_end_field_name]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_start_field_name] = null;
$this->_FIELD_VALUES[$vs_end_field_name] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseTime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()', $this->tableName() . '.' . $vs_field);
return false;
}
$va_timestamps = $o_tep->getTimes();
if ($this->_FIELD_VALUES[$vs_start_field_name] != $va_timestamps["start"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $va_timestamps["start"];
}
if ($this->_FIELD_VALUES[$vs_end_field_name] != $va_timestamps["end"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $va_timestamps["end"];
}
}
}
break;
case FT_TIMECODE:
$o_tp = new TimecodeParser();
if ($o_tp->parse($vm_value)) {
if ($o_tp->getParsedValueInSeconds() != $vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_field] = $o_tp->getParsedValueInSeconds();
}
}
break;
case FT_TEXT:
$vm_value = (string) $vm_value;
if (is_string($vm_value)) {
$vm_value = stripSlashes($vm_value);
}
if (isset($pa_options['purify']) && $pa_options['purify'] || (bool) $this->opb_purify_input || $this->getFieldInfo($vs_field, "PURIFY")) {
$vm_value = BaseModel::getPurifier()->purify((string) $vm_value);
}
if ($this->getFieldInfo($vs_field, "DISPLAY_TYPE") == DT_LIST_MULTIPLE) {
if (is_array($vm_value)) {
if (!($vs_list_multiple_delimiter = $this->getFieldInfo($vs_field, 'LIST_MULTIPLE_DELIMITER'))) {
$vs_list_multiple_delimiter = ';';
}
$vs_string_value = join($vs_list_multiple_delimiter, $vm_value);
$vs_string_value = str_replace("", '', $vs_string_value);
if ($vs_cur_value !== $vs_string_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_string_value;
}
} else {
$vm_value = str_replace("", '', $vm_value);
if ($this->getFieldInfo($vs_field, "ENTITY_ENCODE_INPUT")) {
$vs_value_entity_encoded = htmlentities(html_entity_decode($vm_value));
if ($vs_cur_value !== $vs_value_entity_encoded) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_value_entity_encoded;
} else {
if ($this->getFieldInfo($vs_field, "URL_ENCODE_INPUT")) {
$vs_value_url_encoded = urlencode($vm_value);
if ($vs_cur_value !== $vs_value_url_encoded) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_value_url_encoded;
} else {
if ($vs_cur_value !== $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
}
}
}
break;
case FT_PASSWORD:
if (isset($pa_options['purify']) && $pa_options['purify'] || (bool) $this->opb_purify_input || $this->getFieldInfo($vs_field, "PURIFY")) {
$vm_value = BaseModel::getPurifier()->purify((string) $vm_value);
}
if (!$vm_value) {
// store blank passwords as blank,
$this->_FIELD_VALUES[$vs_field] = "";
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
} else {
// leave the treatment of the password to the AuthAdapter, i.e. don't do hashing here
if ($vs_cur_value != $vm_value) {
$this->_FIELD_VALUES[$vs_field] = $vm_value;
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
break;
case FT_VARS:
if (md5(print_r($vs_cur_value, true)) != md5(print_r($vm_value, true))) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
break;
case FT_MEDIA:
case FT_FILE:
$vb_allow_fetching_of_urls = (bool) $this->_CONFIG->get('allow_fetching_of_media_from_remote_urls');
# if there's a tmp_name is the global _FILES array
# then we'll process it in insert()/update()...
$this->_SET_FILES[$vs_field]['options'] = $pa_options;
if (caGetOSFamily() == OS_WIN32) {
// fix for paths using backslashes on Windows failing in processing
$vm_value = str_replace('\\', '/', $vm_value);
}
if (isset($pa_options['purify']) && $pa_options['purify'] || (bool) $this->opb_purify_input || $this->getFieldInfo($vs_field, "PURIFY")) {
$pa_options["original_filename"] = BaseModel::getPurifier()->purify((string) $pa_options["original_filename"]);
$vm_value = BaseModel::getPurifier()->purify((string) $vm_value);
}
$va_matches = null;
if (is_string($vm_value) && (file_exists($vm_value) || $vb_allow_fetching_of_urls && isURL($vm_value) || preg_match("!^userMedia[\\d]+/!", $vm_value))) {
$this->_SET_FILES[$vs_field]['original_filename'] = $pa_options["original_filename"];
$this->_SET_FILES[$vs_field]['tmp_name'] = $vm_value;
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
} else {
# only return error when file name is not 'none'
# 'none' is PHP's stupid way of telling you there
# isn't a file...
if ($vm_value != "none" && $vm_value) {
//$this->postError(1500,_t("%1 does not exist", $vm_value),"BaseModel->set()", $this->tableName().'.'.$vs_field);
}
return false;
}
break;
default:
die("Invalid field type in BaseModel->set()");
break;
}
} else {
$this->postError(710, _t("'%1' does not exist in this object", $vs_field), "BaseModel->set()", $this->tableName() . '.' . $vs_field);
return false;
}
}
return true;
}
private function _doQueriesForSqlSearch($po_rewritten_query, $pn_subject_tablenum, $ps_dest_table, $pn_level = 0)
{
// query is always of type Zend_Search_Lucene_Search_Query_Boolean
$vn_i = 0;
$va_old_signs = $po_rewritten_query->getSigns();
foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
$vb_is_blank_search = false;
if (is_null($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
$va_direct_query_temp_tables = array();
// List of temporary tables created by direct search queries; tables listed here are dropped at the end of processing for the query element
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Boolean':
$this->_createTempTable('ca_sql_search_temp_' . $pn_level);
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, 'ca_sql_search_temp_' . $pn_level, $pn_level + 1);
// merge with current destination
switch ($vs_op) {
case 'AND':
// and
$this->_createTempTable($ps_dest_table . '_acc');
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT DISTINCT row_id, boost\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT mfs.row_id, SUM(mfs.boost)\n\t\t\t\t\t\t\t\t\tFROM {$ps_dest_table} mfs\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_temp_{$pn_level} AS ftmp1 ON ftmp1.row_id = mfs.row_id\n\t\t\t\t\t\t\t\t\tGROUP BY mfs.row_id\n\t\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
}
$this->_dropTempTable($ps_dest_table . '_acc');
break;
case 'NOT':
$vs_sql = "\n\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} WHERE row_id IN\n\t\t\t\t\t\t\t\t(SELECT row_id FROM ca_sql_search_temp_{$pn_level})\n\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
break;
default:
case 'OR':
// or
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT row_id, SUM(boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\tGROUP BY row_id\n\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
break;
}
$this->_dropTempTable('ca_sql_search_temp_' . $pn_level);
break;
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
case 'Zend_Search_Lucene_Search_Query_Phrase':
case 'Zend_Search_Lucene_Search_Query_Range':
$va_ft_terms = array();
$va_ft_like_terms = array();
$va_ft_stem_terms = array();
$vs_direct_sql_query = null;
$pa_direct_sql_query_params = null;
// set to array with values to use with direct SQL query placeholders or null to pass single standard table_num value as param (most queries just need this single value)
$va_tmp = array();
$vs_access_point = '';
$va_raw_terms = array();
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Range':
$va_lower_term = $o_lucene_query_element->getLowerTerm();
$va_upper_term = $o_lucene_query_element->getUpperTerm();
$va_element = $this->_getElementIDForAccessPoint($va_lower_term->field);
switch ($va_element['datatype']) {
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
$va_parsed_value = $t_geocode->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_lower_lat = $va_parsed_value['value_decimal1'];
$vs_lower_long = $va_parsed_value['value_decimal2'];
$va_parsed_value = $t_geocode->parseValue($va_upper_term->text, $va_element['element_info']);
$vs_upper_lat = $va_parsed_value['value_decimal1'];
$vs_upper_long = $va_parsed_value['value_decimal2'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vs_lower_lat) . " AND " . floatval($vs_upper_lat) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($vs_lower_long) . " AND " . floatval($vs_upper_long) . ")\t\n\t\t\t\t\t\t\t\t\t";
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_cur->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_timecode->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_len->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_weight->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 11:
// integer
$vn_lower_val = intval($va_lower_term->text);
$vn_upper_val = intval($va_upper_term->text);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case 12:
// decimal
$vn_lower_val = floatval($va_lower_term->text);
$vn_upper_val = floatval($va_upper_term->text);
break;
}
if (!$vs_direct_sql_query) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t";
}
break;
case 'Zend_Search_Lucene_Search_Query_Phrase':
$va_words = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_raw_terms[] = $vs_text = (string) $o_term->text;
if (strlen($vs_escaped_text = $this->opo_db->escape($vs_text))) {
$va_words[] = $vs_escaped_text;
}
}
if (!sizeof($va_words)) {
continue 3;
}
$va_ap_tmp = explode(".", $vs_access_point);
$vn_fld_table = $vn_fld_num = null;
if (sizeof($va_ap_tmp) == 2) {
$va_element = $this->_getElementIDForAccessPoint($vs_access_point);
if ($va_element) {
$vs_fld_num = $va_element['field_num'];
$vs_fld_table_num = $va_element['table_num'];
$vs_fld_limit_sql = " AND (swi.field_table_num = {$vs_fld_table_num} AND swi.field_num = '{$vs_fld_num}')";
}
}
$va_temp_tables = array();
$vn_w = 0;
foreach ($va_words as $vs_word) {
$vn_w++;
$vs_temp_table = 'ca_sql_search_phrase_' . md5($pn_subject_tablenum . "/" . $vs_word . "/" . $vn_w);
$this->_createTempTable($vs_temp_table);
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO {$vs_temp_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.index_id + 1, 1\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw \n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id \n\t\t\t\t\t\t\t\t\t" . (sizeof($va_temp_tables) ? " INNER JOIN " . $va_temp_tables[sizeof($va_temp_tables) - 1] . " AS tt ON swi.index_id = tt.row_id" : "") . "\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tsw.word = ? AND swi.table_num = ? {$vs_fld_limit_sql}\n \t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, $vs_word, (int) $pn_subject_tablenum);
$qr_count = $this->opo_db->query("SELECT count(*) c FROM {$vs_temp_table}");
if (!$qr_count->nextRow() || !(int) $qr_count->get('c')) {
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break 2;
}
$va_temp_tables[] = $vs_temp_table;
}
$vs_results_temp_table = array_pop($va_temp_tables);
$this->opo_db->query("UPDATE {$vs_results_temp_table} SET row_id = row_id - 1");
$va_direct_query_temp_tables[$vs_results_temp_table] = true;
$vs_direct_sql_query = "SELECT swi.row_id, ca.boost \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM {$vs_results_temp_table} ca\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON swi.index_id = ca.row_id \n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array();
// don't pass any params
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break;
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$va_ft_like_term_list = array();
foreach ($o_lucene_query_element->getTerms() as $o_term) {
$va_raw_terms[] = $vs_term = (string) (method_exists($o_term, "getTerm") ? $o_term->getTerm()->text : $o_term->text);
if (!$vs_access_point && ($vs_field = method_exists($o_term, "getTerm") ? $o_term->getTerm()->field : $o_term->field)) {
$vs_access_point = $vs_field;
}
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
$va_ft_like_terms[] = $vs_stripped_term . ($vb_had_wildcard ? '%' : '');
}
break;
default:
$vs_access_point = $o_lucene_query_element->getTerm()->field;
$vs_term = $o_lucene_query_element->getTerm()->text;
if ($vs_access_point && mb_strtoupper($vs_term) == _t('[BLANK]')) {
$vb_is_blank_search = true;
break;
}
$va_terms = $this->_tokenize($vs_term, true, $vn_i);
$vb_output_term = false;
foreach ($va_terms as $vs_term) {
if (in_array(trim(mb_strtolower($vs_term, 'UTF-8')), WLPlugSearchEngineSqlSearch::$s_stop_words)) {
continue;
}
if (get_class($o_lucene_query_element) != 'Zend_Search_Lucene_Search_Query_MultiTerm') {
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
// do stemming
if ($this->opb_do_stemming) {
$vs_to_stem = preg_replace('!\\*$!u', '', $vs_term);
if (!preg_match('!y$!u', $vs_to_stem) && !preg_match('![0-9]+!', $vs_to_stem)) {
// don't stem things ending in 'y' as that can cause problems (eg "Bowery" becomes "Boweri")
if (!($vs_stem = trim($this->opo_stemmer->stem($vs_to_stem)))) {
$vs_stem = (string) $vs_term;
}
$va_ft_stem_terms[] = "'" . $this->opo_db->escape($vs_stem) . "'";
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
$vb_output_term = true;
}
}
if ($vb_output_term) {
$va_raw_terms[] = $vs_term;
} else {
$vn_i--;
}
break;
}
$vs_fld_num = $vs_table_num = $t_table = null;
$vb_ft_bit_optimization = false;
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
$o_tep = new TimeExpressionParser();
$vs_date = join(' ', $va_raw_terms);
if (!$o_tep->parse($vs_date)) {
break;
}
$va_range = $o_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'I')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
case 'modified':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'U')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccls.subject_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_change_log_subjects AS ccls ON ccls.log_id = ccl.log_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccls.subject_table_num = {$pn_subject_tablenum})\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
}
} else {
if ($vs_table && $vs_field) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if ($t_table) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = 'I' . $vs_field;
$vn_fld_num = (int) $vs_field;
} else {
$vn_fld_num = $this->getFieldNum($vs_table, $vs_field);
$vs_fld_num = 'I' . $vn_fld_num;
if (!strlen($vn_fld_num)) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$vn_fld_num = $t_element->getPrimaryKey();
$vs_fld_num = 'A' . $vn_fld_num;
if (!$vb_is_blank_search) {
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case 2:
// dates
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 <= " . floatval($va_dates['start']) . " AND cav.value_decimal2 >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
}
break;
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
// If it looks like a lat/long pair that has been tokenized by Lucene
// into oblivion rehydrate it here.
if ($va_coords = caParseGISSearch(join(' ', $va_raw_terms))) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN {$va_coords['min_latitude']} AND {$va_coords['max_latitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN {$va_coords['min_longitude']} AND {$va_coords['max_longitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_amount = $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_amount) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue(array_shift($va_raw_terms), $t_element->getFieldValuesArray());
$vn_len = $va_parsed_value['value_decimal1'];
// this is always in meters so we can compare this value to the one in the database
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_len) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue(array_shift($va_raw_terms), $t_element->getFieldValuesArray());
$vn_weight = $va_parsed_value['value_decimal1'];
// this is always in kilograms so we can compare this value to the one in the database
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_weight) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_timecode = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_timecode) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 11:
// integer
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 = " . intval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 12:
// decimal
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
}
}
} else {
// neither table fields nor elements, i.e. 'virtual' fields like _count should
$vn_fld_num = false;
$vs_fld_num = $vs_field;
}
}
}
if ($t_table->getFieldInfo($t_table->fieldName($vn_fld_num), 'FIELD_TYPE') == FT_BIT) {
$vb_ft_bit_optimization = true;
}
}
}
}
}
//
// If we're querying on the fulltext index then we need to construct
// the query here... if we already have a direct SQL query to run then we can skip this
//
if ($vb_is_blank_search) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (swi.word_id = 0))";
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} elseif (!$vs_direct_sql_query) {
$va_sql_where = array();
if (sizeof($va_ft_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.word IN (" . join(',', $va_ft_terms) . ")))";
} else {
if (sizeof($va_ft_terms) == 1) {
$va_sql_where[] = "(sw.word = " . $va_ft_terms[0] . ")";
} else {
$va_sql_where[] = "(sw.word IN (" . join(',', $va_ft_terms) . "))";
}
}
}
if (sizeof($va_ft_like_terms)) {
$va_tmp = array();
foreach ($va_ft_like_terms as $vs_term) {
if ($vb_ft_bit_optimization) {
$va_tmp[] = '(sw.word = \' ' . $this->opo_db->escape(trim($vs_term)) . ' \')';
} else {
$va_tmp[] = '(sw.word LIKE \'' . $this->opo_db->escape(trim($vs_term)) . '%\')';
}
}
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (" . join(' AND ', $va_tmp) . "))";
} else {
$va_sql_where[] = "(" . join(' AND ', $va_tmp) . ")";
}
}
if (sizeof($va_ft_stem_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.stem IN (" . join(',', $va_ft_stem_terms) . ")))";
} else {
$va_sql_where[] = "(sw.stem IN (" . join(',', $va_ft_stem_terms) . "))";
}
}
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} else {
$va_ft_terms = $va_ft_like_terms = $va_ft_like_terms = array();
}
//print "OP=$vs_op<br>";
if ($vn_i == 0) {
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t" . (!$vb_is_blank_search ? "INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id" : '') . "\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\tGROUP BY swi.row_id \n\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1 && !$vs_direct_sql_query) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
if ($this->debug) {
print 'FIRST: ' . $vs_sql . " [{$pn_subject_tablenum}]<hr>\n";
}
//print $vs_sql;
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
} else {
switch ($vs_op) {
case 'AND':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = ca.row_id", $vs_direct_sql_query);
}
$this->_createTempTable($ps_dest_table . '_acc');
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table}_acc {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tINNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = swi.row_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
if ($this->debug) {
print 'AND:' . $vs_sql . "<hr>\n";
}
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
//$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_sql_search_temp_2");
$this->_dropTempTable($ps_dest_table . '_acc');
break;
case 'NOT':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t" . ($vs_sql_where ? "{$vs_sql_where} AND " : "") . " swi.table_num = ? \n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '');
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
$va_ids = $qr_res->getAllFieldValues("row_id");
$vs_sql = "\n\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\trow_id IN (?)\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
//print "$vs_sql<hr>";
break;
default:
case 'OR':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if ($this->debug) {
print 'OR' . $vs_sql . "<hr>\n";
}
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
break;
}
}
// Drop any temporary tables created by direct search queries
foreach (array_keys($va_direct_query_temp_tables) as $vs_temp_table_to_drop) {
$this->_dropTempTable($vs_temp_table_to_drop);
}
break;
default:
//print get_class($o_lucene_query_element);
break;
}
$vn_i++;
}
}
/**
* Perform the browse using currently applied criteria, calculating the result set and browse facets
* required for subsequent browse refinement. You need to call execute() after setting up your browse
* criteria and options to:
* • Get the result set reflecting the current browse state
* • Fetch browse facets that reflect the current browse state
*
* @param array $pa_options Options include:
* checkAccess = array of access values to filter facets that have an 'access' field by
* noCache = don't use cached browse results
* showDeleted = if set to true, related items that have been deleted are returned. Default is false.
* limitToModifiedOn = if set returned results will be limited to rows modified within the specified date range. The value should be a date/time expression parse-able by TimeExpressionParser
* user_id = If set item level access control is performed relative to specified user_id, otherwise defaults to logged in user
*
* @return bool True on success, null if the browse could not be executed (Eg. no settings), false no error
*/
public function execute($pa_options = null)
{
global $AUTH_CURRENT_USER_ID;
if (!is_array($this->opa_browse_settings)) {
return null;
}
if (!is_array($pa_options)) {
$pa_options = array();
}
$vn_user_id = caGetOption('user_id', $pa_options, $AUTH_CURRENT_USER_ID, array('castTo' => 'int'));
$vb_no_cache = caGetOption('noCache', $pa_options, caGetOption('no_cache', $pa_options, false, array('castTo' => 'bool')), array('castTo' => 'bool'));
$va_params = $this->opo_ca_browse_cache->getParameters();
$vb_need_to_cache_facets = false;
$vb_results_cached = false;
$vb_need_to_save_in_cache = false;
$vs_cache_key = $this->opo_ca_browse_cache->getCurrentCacheKey();
if ($this->opo_ca_browse_cache->load($vs_cache_key)) {
$vn_created_on = $this->opo_ca_browse_cache->getParameter('created_on');
//$t_new_browse->get('created_on', array('getDirectDate' => true));
$va_criteria = $this->getCriteria();
if (!$vb_no_cache && intval(time() - $vn_created_on) < $this->opo_ca_browse_config->get('cache_timeout')) {
$vb_results_cached = true;
$this->opo_ca_browse_cache->setParameter('created_on', time() + $this->opo_ca_browse_config->get('cache_timeout'));
$vb_need_to_save_in_cache = true;
Debug::msg("Cache hit for {$vs_cache_key}");
} else {
$va_criteria = $this->getCriteria();
//$this->opo_ca_browse_cache->remove();
//$this->opo_ca_browse_cache->setParameter('criteria', $va_criteria);
$vb_need_to_save_in_cache = true;
$vb_need_to_cache_facets = true;
Debug::msg("Cache expire for {$vs_cache_key}");
}
} else {
$va_criteria = $this->getCriteria();
$vb_need_to_save_in_cache = true;
Debug::msg("Cache miss for {$vs_cache_key}");
}
if (!$vb_results_cached) {
$this->opo_ca_browse_cache->setParameter('sort', null);
$this->opo_ca_browse_cache->setParameter('created_on', time());
$this->opo_ca_browse_cache->setParameter('table_num', $this->opn_browse_table_num);
$vb_need_to_cache_facets = true;
}
$this->opb_criteria_have_changed = false;
$t_item = $this->opo_datamodel->getInstanceByTableName($this->ops_browse_table_name, true);
$va_results = array();
if (is_array($va_criteria) && sizeof($va_criteria) > 0) {
if (!$vb_results_cached) {
$va_acc = array();
$vn_i = 0;
foreach ($va_criteria as $vs_facet_name => $va_row_ids) {
$vs_target_browse_table_name = $t_item->tableName();
$vs_target_browse_table_num = $t_item->tableNum();
$vs_target_browse_table_pk = $t_item->primaryKey();
$va_facet_info = $this->getInfoForFacet($vs_facet_name);
$va_row_ids = array_keys($va_row_ids);
$vs_relative_to_join = '';
switch ($va_facet_info['type']) {
# -----------------------------------------------------
case 'has':
$vs_rel_table_name = $va_facet_info['table'];
$va_joins = array();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_joins = array_merge($va_joins, $va_relative_execute_sql_data['relative_joins']);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
if ($va_facet_info['element_code']) {
$t_element = new ca_metadata_elements();
if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
break;
}
$vs_element_code = $va_facet_info['element_code'];
$vn_state = array_pop($va_row_ids);
if ($vn_state == 0) {
$va_wheres[] = $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " NOT IN (SELECT row_id FROM ca_attributes WHERE table_num = " . $t_item->tableNum() . " AND element_id = " . $t_element->getPrimaryKey() . ")";
} else {
$va_joins[] = "INNER JOIN ca_attributes AS caa ON caa.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND caa.table_num = " . $t_item->tableNum();
$va_wheres[] = "caa.element_id = " . $t_element->getPrimaryKey();
}
} else {
if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) {
$va_restrict_to_relationship_types = array();
}
$va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']);
if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) {
$va_exclude_relationship_types = array();
}
$va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']);
$vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name);
$vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num);
switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) {
case 3:
$t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true);
$vs_key = 'relation_id';
break;
case 2:
$t_item_rel = null;
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$vs_key = $t_rel_item->primaryKey();
break;
default:
// bad related table
return null;
break;
}
$vs_cur_table = array_shift($va_path);
$vn_state = array_pop($va_row_ids);
foreach ($va_path as $vs_join_table) {
$va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
$va_joins[] = ($vn_state ? 'INNER' : 'LEFT') . ' JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n";
$vs_cur_table = $vs_join_table;
}
$va_wheres = array();
if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel) && (bool) $vn_state) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))";
}
if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel) && (bool) $vn_state) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))";
}
if (!(bool) $vn_state) {
// no option
$va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL)";
if ($t_rel_item->hasField('deleted')) {
$va_wheres[] = "((" . $t_rel_item->tableName() . ".deleted = 0) OR (" . $t_rel_item->tableName() . ".deleted IS NULL))";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) {
$va_wheres[] = "((" . $t_rel_item->tableName() . ".access NOT IN (" . join(',', $pa_options['checkAccess']) . ")) OR ((" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL) AND (" . $t_rel_item->tableName() . ".access IS NULL)))";
}
} else {
// yes option
$va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NOT NULL)";
if ($t_rel_item->hasField('deleted')) {
$va_wheres[] = "(" . $t_rel_item->tableName() . ".deleted = 0)";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) {
$va_wheres[] = "(" . $t_rel_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
}
}
if ($t_item->hasField('deleted')) {
$va_wheres[] = "(" . $t_item->tableName() . ".deleted = 0)";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_wheres[] = "(" . $t_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
$vs_join_sql = join("\n", $va_joins);
$vs_where_sql = '';
if (sizeof($va_wheres) > 0) {
$vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres);
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
$qr_res = $this->opo_db->query($vs_sql);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'label':
if (!($t_label = $t_item->getLabelTableInstance())) {
break;
}
$vs_label_item_pk = $vs_item_pk = $t_item->primaryKey();
$vs_label_table_name = $t_label->tableName();
$vs_label_pk = $t_label->primaryKey();
$vs_label_display_field = $t_item->getLabelDisplayField();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
$t_target = $this->opo_datamodel->getInstanceByTableName($va_facet_info['relative_to'], true);
$t_target_label = $t_target->getLabelTableInstance();
$vs_item_pk = $t_target->primaryKey();
$vs_label_table_name = $t_target_label->tableName();
$vs_label_item_pk = $t_target_label->primaryKey();
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$va_relative_to_join[] = "INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}";
}
} else {
$va_relative_to_join = array("INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}");
}
$vs_relative_to_join = join("\n", $va_relative_to_join);
$va_labels = $t_item->getPreferredDisplayLabelsForIDs($va_row_ids);
foreach ($va_row_ids as $vn_row_id) {
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_label_table_name}.{$vs_label_display_field} = ?";
//print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, $va_labels[$vn_row_id]);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_label_table_name}.{$vs_label_display_field} = ?";
$qr_res = $this->opo_db->query($vs_sql, $va_labels[$vn_row_id]);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'field':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode(str_replace('/', '/', $vn_row_id));
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
$qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
$qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'attribute':
$t_element = new ca_metadata_elements();
if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
return array();
}
$vn_datatype = $t_element->get('datatype');
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
// TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) Text or Number attribute
// (do we support other types as well?)
$vn_element_id = $t_element->getPrimaryKey();
$o_attr = Attribute::getValueInstance($vn_datatype);
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
$vn_row_id = str_replace('/', '/', $vn_row_id);
$va_value = $o_attr->parseValue($vn_row_id, $t_element->getFieldValuesArray());
$va_attr_sql = array();
$va_attr_values = array(intval($vs_target_browse_table_num), $vn_element_id);
if (is_array($va_value)) {
foreach ($va_value as $vs_f => $vs_v) {
if ($vn_datatype == __CA_ATTRIBUTE_VALUE_LIST__) {
if ($vs_f != 'item_id') {
continue;
}
// Include sub-items
$t_list_item = new ca_list_items();
$va_item_ids = $t_list_item->getHierarchy((int) $vs_v, array('idsOnly' => true, 'includeSelf' => true));
$va_item_ids[] = (int) $vs_v;
$va_attr_sql[] = "(ca_attribute_values.{$vs_f} IN (?))";
$va_attr_values[] = $va_item_ids;
} else {
$va_attr_sql[] = "(ca_attribute_values.{$vs_f} " . (is_null($vs_v) ? " IS " : " = ") . " ?)";
$va_attr_values[] = $vs_v;
}
}
}
if ($vs_attr_sql = join(" AND ", $va_attr_sql)) {
$vs_attr_sql = " AND " . $vs_attr_sql;
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) {$vs_attr_sql}";
$qr_res = $this->opo_db->query($vs_sql, $va_attr_values);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) {$vs_attr_sql}";
$qr_res = $this->opo_db->query($vs_sql, $va_attr_values);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'normalizedDates':
$t_element = new ca_metadata_elements();
$vb_is_element = $vb_is_field = false;
if (!($vb_is_element = $t_element->load(array('element_code' => $va_facet_info['element_code']))) && !($vb_is_field = $t_item->hasField($va_facet_info['element_code']) && $t_item->getFieldInfo($va_facet_info['element_code'], 'FIELD_TYPE') === FT_HISTORIC_DATERANGE)) {
return array();
}
// TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute
$vs_normalization = $va_facet_info['normalization'];
$o_tep = new TimeExpressionParser();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
$vn_element_id = $vb_is_element ? $t_element->getPrimaryKey() : null;
$vs_browse_start_fld = $vs_browse_start_fld = null;
if ($vb_is_field) {
$vs_browse_start_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'START');
$vs_browse_end_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'END');
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
$va_dates = null;
if ($vn_row_id !== 'null') {
if (!$o_tep->parse($vn_row_id)) {
continue;
}
// invalid date?
$va_dates = $o_tep->getHistoricTimestamps();
}
if ($vb_is_element) {
if (is_null($va_dates)) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']);
}
} else {
// is intrinsic
if (is_null($va_dates)) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_start_fld} IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_end_fld} IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_start_fld} <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_end_fld} >= ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_start_fld} BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_end_fld} BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']);
}
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'normalizedLength':
$t_element = new ca_metadata_elements();
$vb_is_element = $vb_is_field = false;
if (!($vb_is_element = $t_element->load(array('element_code' => $va_facet_info['element_code']))) && !($vb_is_field = $t_item->hasField($va_facet_info['element_code']) && $t_item->getFieldInfo($va_facet_info['element_code'], 'FIELD_TYPE') === FT_HISTORIC_DATERANGE)) {
return array();
}
// TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute
$vs_normalization = $va_facet_info['normalization'];
$o_tep = new TimeExpressionParser();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
$vn_element_id = $vb_is_element ? $t_element->getPrimaryKey() : null;
$vs_browse_start_fld = $vs_browse_start_fld = null;
if ($vb_is_field) {
$vs_browse_start_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'START');
$vs_browse_end_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'END');
}
if (!($vs_output_units = caGetLengthUnitType($vs_units = caGetOption('units', $va_facet_info, 'm')))) {
$vs_output_units = Zend_Measure_Length::METER;
}
$vs_increment = caGetOption('increment', $va_facet_info, '1 m');
$vo_increment = caParseLengthDimension($vs_increment);
$vn_increment_in_current_units = (double) $vo_increment->convertTo($vs_output_units, 6, 'en_US');
foreach ($va_row_ids as $vn_row_id) {
$vn_start = urldecode($vn_row_id);
// is start dimension
// calculate end dimension
$vn_end = $vn_start + $vn_increment_in_current_units;
// convert to meters
$vo_start = new Zend_Measure_Length($vn_start, $vs_output_units, 'en_US');
$vo_end = new Zend_Measure_Length($vn_end, $vs_output_units, 'en_US');
$vn_start_in_meters = (double) $vo_start->convertTo(Zend_Measure_Length::METER, 6, 'en_US');
$vn_end_in_meters = (double) $vo_end->convertTo(Zend_Measure_Length::METER, 6, 'en_US');
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $vn_start_in_meters, $vn_end_in_meters);
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'authority':
$vs_rel_table_name = $va_facet_info['table'];
if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) {
$va_restrict_to_relationship_types = array();
}
$va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']);
if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) {
$va_exclude_relationship_types = array();
}
$va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']);
$vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name);
$vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num);
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) {
case 3:
$t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true);
$vs_key = 'relation_id';
break;
case 2:
$t_item_rel = null;
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$vs_key = $t_rel_item->primaryKey();
break;
default:
// bad related table
return null;
break;
}
$vs_cur_table = array_shift($va_path);
$va_joins = array();
foreach ($va_path as $vs_join_table) {
$va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
$va_joins[] = 'INNER JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n";
$vs_cur_table = $vs_join_table;
}
$vs_join_sql = join("\n", $va_joins);
$va_wheres = array();
if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))";
}
if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))";
}
$vs_where_sql = '';
if (sizeof($va_wheres) > 0) {
$vs_where_sql = ' AND ' . join(' AND ', $va_wheres);
}
if ((!isset($va_facet_info['dont_expand_hierarchically']) || !$va_facet_info['dont_expand_hierarchically']) && $t_rel_item->isHierarchical() && $t_rel_item->load((int) $vn_row_id)) {
$vs_hier_left_fld = $t_rel_item->getProperty('HIERARCHY_LEFT_INDEX_FLD');
$vs_hier_right_fld = $t_rel_item->getProperty('HIERARCHY_RIGHT_INDEX_FLD');
$vs_get_item_sql = "{$vs_rel_table_name}.{$vs_hier_left_fld} >= " . $t_rel_item->get($vs_hier_left_fld) . " AND {$vs_rel_table_name}.{$vs_hier_right_fld} <= " . $t_rel_item->get($vs_hier_right_fld);
if ($vn_hier_id_fld = $t_rel_item->getProperty('HIERARCHY_ID_FLD')) {
$vs_get_item_sql .= " AND {$vs_rel_table_name}.{$vn_hier_id_fld} = " . (int) $t_rel_item->get($vn_hier_id_fld);
}
$vs_get_item_sql = "({$vs_get_item_sql})";
} else {
$vs_get_item_sql = "({$vs_rel_table_name}.{$vs_rel_table_pk} = " . (int) $vn_row_id . ")";
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
$qr_res = $this->opo_db->query($vs_sql);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'location':
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
$va_row_tmp = explode(":", $vn_row_id);
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.current_loc_class = ?)" . (sizeof($va_row_tmp) > 1 ? " AND ({$this->ops_browse_table_name}.current_loc_subclass = ?)" : "") . (sizeof($va_row_tmp) > 2 ? " AND ({$this->ops_browse_table_name}.current_loc_id = ?)" : "");
$qr_res = $this->opo_db->query($vs_sql, $va_row_tmp);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.current_loc_class = ?)" . (sizeof($va_row_tmp) > 1 ? " AND ({$this->ops_browse_table_name}.current_loc_subclass = ?)" : "") . (sizeof($va_row_tmp) > 2 ? " AND ({$this->ops_browse_table_name}.current_loc_id = ?)" : "");
$qr_res = $this->opo_db->query($vs_sql, $va_row_tmp);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'fieldList':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'violations':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = " . $t_item->tableNum() . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rules ON ca_metadata_dictionary_rules.rule_id = ca_metadata_dictionary_rule_violations.rule_id\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_metadata_dictionary_rules.rule_code = ?)";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = " . $t_item->tableNum() . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rules ON ca_metadata_dictionary_rules.rule_id = ca_metadata_dictionary_rule_violations.rule_id\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_metadata_dictionary_rules.rule_code = ?)";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
}
$va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey());
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'checkouts':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
$vs_where = null;
$vn_current_time = time();
foreach ($va_row_ids as $vn_row_id) {
$vs_checkout_join_sql = "INNER JOIN ca_object_checkouts ON ca_object_checkouts.object_id = ca_objects.object_id";
$vs_status_code = isset($va_facet_info['status']) && $va_facet_info['status'] ? $va_facet_info['status'] : $vn_row_id;
switch ($vs_status_code) {
case 'overdue':
$vs_where = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL) AND (ca_object_checkouts.due_date <= {$vn_current_time}))";
break;
case 'reserved':
$vs_where = "((ca_object_checkouts.checkout_date IS NULL) AND (ca_object_checkouts.return_date IS NULL))";
break;
case 'available':
$vs_checkout_join_sql = '';
$vs_where = "(ca_objects.object_id NOT IN (SELECT object_id FROM ca_object_checkouts WHERE (ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL)))";
break;
case 'all':
$vs_where = "(ca_object_checkouts.checkout_date <= {$vn_current_time})";
break;
default:
case 'out':
$vs_where = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL))";
break;
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca_objects.object_id\n\t\t\t\t\t\t\t\t\t\tFROM ca_objects\n\t\t\t\t\t\t\t\t\t\t{$vs_checkout_join_sql}\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t{$vs_where}\n\t\t\t\t\t\t\t\t\t";
$vs_user_sql = null;
$va_params = array();
switch ($va_facet_info['mode']) {
case 'user':
foreach ($va_row_ids as $vn_index => $vn_row_id) {
$va_row_ids[$vn_index] = (int) $vn_row_id;
}
$va_params[] = $va_row_ids;
$vs_user_sql .= " AND (ca_object_checkouts.user_id IN (?))";
break;
case 'all':
default:
// noop
break;
}
$qr_res = $this->opo_db->query($vs_sql . $vs_user_sql, $va_params);
$va_acc[$vn_i] = $qr_res->getAllFieldValues('ca_objects.object_id');
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
default:
// handle "search" criteria - search engine queries that can be browsed
if ($vs_facet_name === '_search') {
if (!($o_search = caGetSearchInstance($this->ops_browse_table_name))) {
$this->postError(2900, _t("Invalid search type"), "BrowseEngine->execute()");
break;
}
$vs_pk = $t_item->primaryKey();
if (is_array($va_type_ids = $this->getTypeRestrictionList()) && sizeof($va_type_ids)) {
$o_search->setTypeRestrictions($va_type_ids);
}
if (is_array($va_source_ids = $this->getSourceRestrictionList()) && sizeof($va_source_ids)) {
$o_search->setSourceRestrictions($va_source_ids);
}
$va_options = $pa_options;
unset($va_options['sort']);
// browse engine takes care of sort so there is no reason to waste time having the search engine do so
$va_options['filterNonPrimaryRepresentations'] = true;
// filter out non-primary representations in ca_objects results to save (a bit) of time
$o_search->setOption('strictPhraseSearching', caGetOption('strictPhraseSearching', $va_options, true));
$qr_res = $o_search->search($va_row_ids[0], $va_options);
if ($qr_res->numHits() > 0) {
$va_acc[$vn_i] = $qr_res->getPrimaryKeyValues();
}
$vn_i++;
} else {
$this->postError(2900, _t("Invalid criteria type"), "BrowseEngine->execute()");
}
break;
# -----------------------------------------------------
}
}
foreach ($va_acc as $vn_i => $va_hits) {
$va_acc[$vn_i] = array_flip($va_hits);
}
$vn_smallest_list_index = null;
foreach ($va_acc as $vn_i => $va_hits) {
if (is_null($vn_smallest_list_index)) {
$vn_smallest_list_index = $vn_i;
continue;
}
if (sizeof($va_hits) < sizeof($va_acc[$vn_smallest_list_index])) {
$vn_smallest_list_index = $vn_i;
}
}
$va_res = array();
$va_acc_indices = array_keys($va_acc);
if (is_array($va_acc[$vn_smallest_list_index])) {
foreach ($va_acc[$vn_smallest_list_index] as $vn_row_id => $vb_dummy) {
foreach ($va_acc_indices as $vn_i) {
if ($vn_i == $vn_smallest_list_index) {
continue;
}
if (!isset($va_acc[$vn_i][$vn_row_id])) {
continue 2;
}
}
$va_res[$vn_row_id] = true;
}
}
if (sizeof($va_res)) {
$vs_filter_join_sql = $vs_filter_where_sql = '';
$va_wheres = array();
$va_joins = array();
$vs_sql_distinct = '';
if (sizeof($this->opa_result_filters)) {
$va_tmp = array();
foreach ($this->opa_result_filters as $va_filter) {
$vm_val = $this->_filterValueToQueryValue($va_filter);
$va_wheres[] = $this->ops_browse_table_name . '.' . $va_filter['field'] . " " . $va_filter['operator'] . " " . $vm_val;
}
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)";
}
if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['limitToModifiedOn'])) {
$va_range = $o_tep->getUnixTimestamps();
$va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . " AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum();
$va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id";
$va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))";
$vs_sql_distinct = 'DISTINCT';
// need to pull distinct rows since joining the change log can cause dupes
}
}
if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) {
$t_subject = $this->getSubjectInstance();
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . ')' . ($t_subject->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . " IS NULL)" : '') . ')';
}
if (is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) {
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IN (' . join(', ', $va_browse_source_ids) . ') OR (' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IS NULL))';
}
$vs_filter_where_sql = "WHERE (" . $this->ops_browse_table_name . "." . $t_item->primaryKey() . " IN (?)) ";
if (sizeof($va_wheres)) {
$vs_filter_where_sql .= ' AND (' . join(' AND ', $va_wheres) . ')';
}
if (sizeof($va_joins)) {
$vs_filter_join_sql = join("\n", $va_joins);
}
$qr_res = $this->opo_db->query("\n\t\t\t\t\t\t\tSELECT {$vs_sql_distinct} " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t\t", array(array_keys($va_res)));
$va_results = $qr_res->getAllFieldValues($t_item->primaryKey());
if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) {
$va_results = $this->filterHitsByACL($va_results, $this->opn_browse_table_num, $vn_user_id, __CA_ACL_READONLY_ACCESS__);
}
$this->opo_ca_browse_cache->setResults($va_results);
$vb_need_to_save_in_cache = true;
} else {
// No results for some reason - we're here because we don't want to throw a SQL error
$this->opo_ca_browse_cache->setResults($va_results = array());
$vb_need_to_save_in_cache = true;
}
}
} else {
// no criteria - don't try to find anything unless configured to do so
$va_settings = $this->opo_ca_browse_config->getAssoc($this->ops_browse_table_name);
if (isset($va_settings['show_all_for_no_criteria_browse']) && $va_settings['show_all_for_no_criteria_browse'] || isset($pa_options['showAllForNoCriteriaBrowse']) && $pa_options['showAllForNoCriteriaBrowse']) {
$va_wheres = $va_joins = array();
$vs_pk = $t_item->primaryKey();
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)";
}
if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['limitToModifiedOn'])) {
$va_range = $o_tep->getUnixTimestamps();
$va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . ".{$vs_pk} AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum();
$va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id";
$va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))";
$vs_sql_distinct = 'DISTINCT';
// need to pull distinct rows since joining the change log can cause dupes
}
}
$va_browse_type_ids = $this->getTypeRestrictionList();
$va_browse_source_ids = $this->getSourceRestrictionList();
if (is_array($va_browse_type_ids) && sizeof($va_browse_type_ids) || is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) {
$t_subject = $this->getSubjectInstance();
if (is_array($va_browse_type_ids) && sizeof($va_browse_type_ids)) {
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . ')' . ($t_subject->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . " IS NULL)" : '') . ')';
}
if (is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) {
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IN (' . join(', ', $va_browse_source_ids) . ') OR (' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IS NULL))';
}
}
if (sizeof($va_wheres)) {
$vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres);
}
if (sizeof($va_joins)) {
$vs_filter_join_sql = join("\n", $va_joins);
}
$qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_pk}\n\t\t\t\t\t\tFROM " . $t_item->tableName() . "\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t{$vs_pk}\n\t\t\t\t\t");
$va_results = $qr_res->getAllFieldValues($vs_pk);
if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) {
$va_results = array_keys($this->filterHitsByACL($va_results, $this->opn_browse_table_num, $vn_user_id, __CA_ACL_READONLY_ACCESS__));
}
$this->opo_ca_browse_cache->setResults($va_results);
} else {
$this->opo_ca_browse_cache->setResults(array());
}
$vb_need_to_save_in_cache = true;
}
if ($vb_need_to_cache_facets && !$pa_options['dontCheckFacetAvailability']) {
$this->loadFacetContent($pa_options);
}
if ($vb_need_to_save_in_cache) {
$this->opo_ca_browse_cache->save();
}
return true;
}
/**
* Parses natural language date and returns a Unix timestamp
*
* @param string $ps_date_expression A valid date/time expression as described in http://docs.collectiveaccess.org/wiki/Date_and_Time_Formats
* @return int A Unix timestamp for the date expression or null if expression cannot be parsed.
*/
function caDateToUnixTimestamp($ps_date_expression)
{
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($ps_date_expression)) {
$va_date = $o_tep->getUnixTimestamps();
return isset($va_date['start']) ? $va_date['start'] : null;
}
return null;
}
/**
* Return current date/time
*
* @param array $pa_options Options include:
* format = format of return value. Options are:
* unix = Unix-timestamp
* historic = Historic timestamp
* [Default is historic]
*
*/
public static function now($pa_options = null)
{
$ps_format = caGetOption('format', $pa_options, null, ['toLowerCase' => true]);
$o_tep = new TimeExpressionParser();
$o_tep->parse(__TEP_NOW__);
switch ($ps_format) {
case 'unix':
return array_shift($o_tep->getUnixTimestamps());
break;
case 'historic':
default:
return array_shift($o_tep->getHistoricTimestamps());
break;
}
return null;
}
/**
*
*/
public function search($ps_datetime_expression, $ps_code = null)
{
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($ps_datetime_expression)) {
list($vn_period_start, $vn_period_end) = $o_tep->getUnixTimestamps();
if ($vn_period_start && $vn_period_end) {
$o_db = new Db();
$qr_codes = $o_db->query("SELECT DISTINCT code FROM ca_eventlog ORDER BY code");
$va_codes = array();
while ($qr_codes->nextRow()) {
$va_codes[] = $qr_codes->get("code");
}
if (in_array($ps_code, $va_codes)) {
$qr_log = $o_db->query("\n\t\t\t\t\t\tSELECT *\n\t\t\t\t\t\tFROM ca_eventlog\n\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t(date_time BETWEEN {$vn_period_start} AND {$vn_period_end}) \n\t\t\t\t\t\t\tAND (code = ?)\n\t\t\t\t\t\tORDER BY date_time DESC\n\t\t\t\t\t", $ps_code);
} else {
$qr_log = $o_db->query("\n\t\t\t\t\t\tSELECT *\n\t\t\t\t\t\tFROM ca_eventlog\n\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t(date_time BETWEEN {$vn_period_start} AND {$vn_period_end})\n\t\t\t\t\t\tORDER BY date_time DESC\n\t\t\t\t\t");
}
return $qr_log->getAllRows();
}
}
return null;
}
/**
* Gets list of messages conforming to specified options.
* @param array $pa_options
* readOnly =
* unreadOnly =
* user_id =
* created_on =
* transaction_id =
* type =
* @return array
*/
public function getMessages($pn_user_id, $pa_options = null)
{
$o_db = $this->getDb();
$pb_read_only = (bool) (isset($pa_options['readOnly']) && $pa_options['readOnly']);
$pb_unread_only = (bool) (isset($pa_options['unreadOnly']) && $pa_options['unreadOnly']);
$pn_restrict_to_transaction_user_id = isset($pa_options['user_id']) && (int) $pa_options['user_id'] ? (int) $pa_options['user_id'] : null;
$ps_created_on = isset($pa_options['created_on']) && (string) $pa_options['created_on'] ? (string) $pa_options['created_on'] : null;
$va_sql_wheres = array();
$va_sql_params = array();
if ($pn_restrict_to_transaction_user_id) {
$va_sql_wheres[] = "tra.user_id = ?";
$va_sql_params[] = $pn_restrict_to_transaction_user_id;
}
if (is_array($pa_options) && array_key_exists('transaction_id', $pa_options)) {
$va_sql_wheres[] = "tra.transaction_id = ?";
$va_sql_params[] = (int) $pa_options['transaction_id'];
}
if ($ps_created_on) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($ps_created_on) && ($va_dates = $o_tep->getUnixTimestamps())) {
$va_sql_wheres[] = "(comm.created_on BETWEEN ? AND ?)";
$va_sql_params[] = $va_dates['start'];
$va_sql_params[] = $va_dates['end'];
}
}
if (isset($pa_options['type']) && in_array($pa_options['type'], array('O', 'L'))) {
$va_sql_wheres[] = "(comm.communication_type = ?)";
$va_sql_params[] = (string) $pa_options['type'];
}
if (isset($pa_options['search']) && strlen($pa_options['search'])) {
$o_search = new CommerceCommunicationSearch();
if ($qr_hits = $o_search->search($pa_options['search'])) {
$va_ids = array();
while ($qr_hits->nextHit()) {
$va_ids[] = $qr_hits->get('communication_id');
}
if (sizeof($va_ids)) {
$va_sql_wheres[] = "(comm.communication_id IN (?))";
$va_sql_params[] = $va_ids;
} else {
$va_sql_wheres[] = "(comm.communication_id = 0)";
}
}
}
if ($pb_read_only) {
$va_sql_wheres[] = "(comm.read_on IS NOT NULL) AND (comm.from_user_id <> " . (int) $pn_user_id . ")";
}
if ($pb_unread_only) {
$va_sql_wheres[] = "(comm.read_on IS NULL) AND (comm.from_user_id <> " . (int) $pn_user_id . ")";
}
$qr_res = $o_db->query($vs_sql = "\n\t \t\tSELECT comm.*, tra.short_description, tra.transaction_id, tra.created_on transaction_created_on, tra.set_id\n\t \t\tFROM ca_commerce_communications comm\n\t \t\tINNER JOIN ca_commerce_transactions AS tra ON tra.transaction_id = comm.transaction_id\n\t \t\t" . (sizeof($va_sql_wheres) ? " WHERE " . join(" AND ", $va_sql_wheres) : '') . "\n\t \t\tORDER BY\n\t \t\t\tcomm.created_on DESC\n\t \t", $va_sql_params);
//print $vs_sql;
$va_messages = array();
while ($qr_res->nextRow()) {
$va_messages[$qr_res->get('transaction_id')][] = $qr_res->getRow();
}
return $va_messages;
}
private function _doQueriesForSqlSearch($po_rewritten_query, $pn_subject_tablenum, $ps_dest_table, $pn_level = 0, $pa_options = null)
{
// query is always of type Zend_Search_Lucene_Search_Query_Boolean
$vn_i = 0;
switch (get_class($po_rewritten_query)) {
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$va_elements = $po_rewritten_query->getTerms();
break;
default:
$va_elements = $po_rewritten_query->getSubqueries();
break;
}
$o_base = new SearchBase();
$va_old_signs = $po_rewritten_query->getSigns();
foreach ($va_elements as $o_lucene_query_element) {
$vb_is_blank_search = false;
if (is_null($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
$va_direct_query_temp_tables = array();
// List of temporary tables created by direct search queries; tables listed here are dropped at the end of processing for the query element
$pa_direct_sql_query_params = null;
// set to array with values to use with direct SQL query placeholders or null to pass single standard table_num value as param (most queries just need this single value)
$vs_direct_sql_query = null;
$vn_direct_sql_target_table_num = $pn_subject_tablenum;
switch ($vs_class = get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Boolean':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$this->_createTempTable('ca_sql_search_temp_' . $pn_level);
if ($vs_op == 'AND' && $vn_i == 0) {
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, $ps_dest_table, $pn_level + 1);
} else {
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, 'ca_sql_search_temp_' . $pn_level, $pn_level + 1);
}
// merge with current destination
switch ($vs_op) {
case 'AND':
if ($vn_i > 0) {
$this->_createTempTable("{$ps_dest_table}_acc");
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT mfs.row_id, SUM(mfs.boost)\n\t\t\t\t\t\t\t\t\tFROM {$ps_dest_table} mfs\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_temp_{$pn_level} AS ftmp1 ON ftmp1.row_id = mfs.row_id\n\t\t\t\t\t\t\t\t\tGROUP BY mfs.row_id\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
$this->_dropTempTable("{$ps_dest_table}_acc");
}
break;
case 'NOT':
$qr_res = $this->opo_db->query("SELECT row_id FROM ca_sql_search_temp_{$pn_level}");
if (is_array($va_ids = $qr_res->getAllFieldValues()) && sizeof($va_ids)) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} WHERE row_id IN (?)\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
}
break;
default:
case 'OR':
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT row_id, SUM(boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\tGROUP BY row_id\n\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
break;
}
$vn_i++;
$this->_dropTempTable('ca_sql_search_temp_' . $pn_level);
break;
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Index_Term':
case 'Zend_Search_Lucene_Search_Query_Phrase':
case 'Zend_Search_Lucene_Search_Query_Range':
$va_ft_terms = array();
$va_ft_like_terms = array();
$va_ft_stem_terms = array();
$vs_access_point = '';
$va_raw_terms = array();
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Range':
$va_lower_term = $o_lucene_query_element->getLowerTerm();
$va_upper_term = $o_lucene_query_element->getUpperTerm();
$va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $va_lower_term->field);
$vn_direct_sql_target_table_num = $va_element['table_num'];
$va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num);
$vn_root_element_id = $va_element['element_info']['hier_element_id'];
if (!isset($va_indexed_fields['_ca_attribute_' . $va_element['element_id']]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) {
break 2;
}
// skip if not indexed
switch ($va_element['datatype']) {
case __CA_ATTRIBUTE_VALUE_GEOCODE__:
$t_geocode = new GeocodeAttributeValue();
$va_parsed_value = $t_geocode->parseValue('[' . $va_lower_term->text . ']', $va_element['element_info']);
$vs_lower_lat = $va_parsed_value['value_decimal1'];
$vs_lower_long = $va_parsed_value['value_decimal2'];
$va_parsed_value = $t_geocode->parseValue('[' . $va_upper_term->text . ']', $va_element['element_info']);
$vs_upper_lat = $va_parsed_value['value_decimal1'];
$vs_upper_long = $va_parsed_value['value_decimal2'];
// mysql BETWEEN always wants the lower value first ... BETWEEN 5 AND 3 wouldn't match 4 ... So we swap the values if necessary
if ($vs_upper_lat < $vs_lower_lat) {
$tmp = $vs_upper_lat;
$vs_upper_lat = $vs_lower_lat;
$vs_lower_lat = $tmp;
}
if ($vs_upper_long < $vs_lower_long) {
$tmp = $vs_upper_long;
$vs_upper_long = $vs_lower_long;
$vs_lower_long = $tmp;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vs_lower_lat) . " AND " . floatval($vs_upper_lat) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($vs_lower_long) . " AND " . floatval($vs_upper_long) . ")\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_CURRENCY__:
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_cur->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_TIMECODE__:
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_timecode->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_LENGTH__:
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_len->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_WEIGHT__:
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_weight->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_INTEGER__:
$vn_lower_val = intval($va_lower_term->text);
$vn_upper_val = intval($va_upper_term->text);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_NUMERIC__:
$vn_lower_val = floatval($va_lower_term->text);
$vn_upper_val = floatval($va_upper_term->text);
break;
}
if (!$vs_direct_sql_query) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t";
}
break;
case 'Zend_Search_Lucene_Search_Query_Phrase':
if ($this->getOption('strictPhraseSearching')) {
$vs_search_tokenizer_regex = $this->opo_search_config->get('search_tokenizer_regex');
$va_words = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_terms = preg_split("![{$vs_search_tokenizer_regex}]+!u", (string) $o_term->text);
$va_raw_terms[] = (string) $o_term->text;
foreach ($va_terms as $vs_term) {
if (strlen($vs_escaped_text = $this->opo_db->escape($vs_term))) {
$va_words[] = $vs_escaped_text;
}
}
}
if (!sizeof($va_words)) {
continue 3;
}
$va_ap_tmp = explode(".", $vs_access_point);
$vn_fld_table = $vn_fld_num = null;
if (sizeof($va_ap_tmp) >= 2) {
$va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $vs_access_point);
if ($va_element) {
$vs_fld_num = $va_element['field_num'];
$vs_fld_table_num = $va_element['table_num'];
$vs_fld_limit_sql = " AND (swi.field_table_num = {$vs_fld_table_num} AND swi.field_num = '{$vs_fld_num}')";
if (is_array($va_element['relationship_type_ids']) && sizeof($va_element['relationship_type_ids'])) {
$vs_fld_limit_sql .= " AND (swi.rel_type_id IN (" . join(",", $va_element['relationship_type_ids']) . "))";
}
}
}
$va_temp_tables = array();
$vn_w = 0;
foreach ($va_words as $vs_word) {
$vn_w++;
$vs_temp_table = 'ca_sql_search_phrase_' . md5($pn_subject_tablenum . "/" . $vs_word . "/" . $vn_w);
$this->_createTempTable($vs_temp_table);
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO {$vs_temp_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.index_id + 1, 1\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw \n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id \n\t\t\t\t\t\t\t\t\t" . (sizeof($va_temp_tables) ? " INNER JOIN " . $va_temp_tables[sizeof($va_temp_tables) - 1] . " AS tt ON swi.index_id = tt.row_id" : "") . "\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tsw.word = ? AND swi.table_num = ? {$vs_fld_limit_sql}\n \t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, $vs_word, (int) $pn_subject_tablenum);
$qr_count = $this->opo_db->query("SELECT count(*) c FROM {$vs_temp_table}");
if (!$qr_count->nextRow() || !(int) $qr_count->get('c')) {
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break 2;
}
$va_temp_tables[] = $vs_temp_table;
}
$vs_results_temp_table = array_pop($va_temp_tables);
$this->opo_db->query("UPDATE {$vs_results_temp_table} SET row_id = row_id - 1");
$va_direct_query_temp_tables[$vs_results_temp_table] = true;
$vs_direct_sql_query = "SELECT swi.row_id, ca.boost \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM {$vs_results_temp_table} ca\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON swi.index_id = ca.row_id \n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array();
// don't pass any params
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break;
}
default:
switch ($vs_class) {
case 'Zend_Search_Lucene_Search_Query_Phrase':
$va_term_objs = $o_lucene_query_element->getQueryTerms();
break;
case 'Zend_Search_Lucene_Index_Term':
$va_term_objs = array($o_lucene_query_element);
break;
default:
$va_term_objs = array($o_lucene_query_element->getTerm());
break;
}
foreach ($va_term_objs as $o_term) {
$va_access_point_info = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $o_term->field);
$vs_access_point = $va_access_point_info['access_point'];
$vs_term = $o_term->text;
if ($vs_access_point && mb_strtoupper($vs_term) == _t('[BLANK]')) {
$t_ap = $this->opo_datamodel->getInstanceByTableNum($va_access_point_info['table_num'], true);
if (is_a($t_ap, 'BaseLabel')) {
// labels have the literal text "[Blank]" indexed to "blank" to indicate blank-ness
$vb_is_blank_search = false;
$vs_term = _t('blank');
} else {
$vb_is_blank_search = true;
break;
}
}
$va_terms = array($vs_term);
//$this->_tokenize($vs_term, true, $vn_i);
$vb_has_wildcard = (bool) preg_match('!\\*$!', $vs_term);
$vb_output_term = false;
foreach ($va_terms as $vs_term) {
if ($vb_has_wildcard) {
$vs_term .= '*';
}
if (in_array(trim(mb_strtolower($vs_term, 'UTF-8')), WLPlugSearchEngineSqlSearch::$s_stop_words)) {
continue;
}
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
if ($vb_has_wildcard) {
$va_ft_like_terms[] = $vs_stripped_term;
} else {
// do stemming
$vb_do_stemming = $this->opb_do_stemming;
if (mb_substr($vs_term, -1) == '|') {
$vs_term = mb_substr($vs_term, 0, mb_strlen($vs_term) - 1);
$vb_do_stemming = false;
}
if ($vb_do_stemming) {
$vs_to_stem = preg_replace('!\\*$!u', '', $vs_term);
if (!preg_match('!y$!u', $vs_to_stem) && !preg_match('![0-9]+!', $vs_to_stem)) {
// don't stem things ending in 'y' as that can cause problems (eg "Bowery" becomes "Boweri")
if (!($vs_stem = trim($this->opo_stemmer->stem($vs_to_stem)))) {
$vs_stem = (string) $vs_term;
}
$va_ft_stem_terms[] = "'" . $this->opo_db->escape($vs_stem) . "'";
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
}
$vb_output_term = true;
}
if ($vb_output_term) {
$va_raw_terms[] = $vs_term;
}
}
break;
}
$vs_fld_num = $vs_table_num = $t_table = null;
$vb_ft_bit_optimization = false;
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
$o_tep = new TimeExpressionParser();
$vs_date = join(' ', $va_raw_terms);
if (!$o_tep->parse($vs_date)) {
break;
}
$va_range = $o_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'I')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
case 'modified':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'U')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccls.subject_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_change_log_subjects AS ccls ON ccls.log_id = ccl.log_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccls.subject_table_num = {$pn_subject_tablenum})\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
}
} else {
if ($vs_table && $vs_field && ($t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true))) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = 'I' . $vs_field;
$vn_fld_num = (int) $vs_field;
} else {
$vn_fld_num = $this->getFieldNum($vs_table, $vs_field);
$vs_fld_num = 'I' . $vn_fld_num;
$vn_direct_sql_target_table_num = $vs_table_num;
if (!strlen($vn_fld_num)) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num);
$vn_fld_num = $t_element->getPrimaryKey();
$vn_root_element_id = $t_element->get('hier_element_id');
if (!isset($va_indexed_fields['_ca_attribute_' . $vn_fld_num]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) {
break 2;
}
// skip if not indexed
$vs_fld_num = 'A' . $vn_fld_num;
if (!$vb_is_blank_search) {
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case __CA_ATTRIBUTE_VALUE_DATERANGE__:
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 <= " . floatval($va_dates['start']) . " AND cav.value_decimal2 >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
}
break;
case __CA_ATTRIBUTE_VALUE_GEOCODE__:
// At this point $va_raw_terms has been tokenized by Lucene into oblivion
// and is also dependent on the search_tokenizer_regex so we can't really do anything with it.
// We now build our own un-tokenized term array instead. caParseGISSearch() can handle it.
$va_gis_terms = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
$va_gis_terms[] = trim((string) $o_term->text);
}
if ($va_coords = caParseGISSearch(join(' ', $va_gis_terms))) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN {$va_coords['min_latitude']} AND {$va_coords['max_latitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN {$va_coords['min_longitude']} AND {$va_coords['max_longitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
break;
case __CA_ATTRIBUTE_VALUE_CURRENCY__:
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_amount = $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_amount) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_LENGTH__:
// If it looks like a dimension that has been tokenized by Lucene
// into oblivion rehydrate it here.
try {
switch (sizeof($va_raw_terms)) {
case 2:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1];
break;
case 3:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2];
break;
default:
$vs_dimension = join(' ', $va_raw_terms);
}
$vo_parsed_measurement = caParseLengthDimension($vs_dimension);
$vn_len = $vo_parsed_measurement->convertTo('METER', 6, 'en_US');
} catch (Exception $e) {
$vs_direct_sql_query = null;
break;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_len) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_WEIGHT__:
// If it looks like a weight that has been tokenized by Lucene
// into oblivion rehydrate it here.
try {
switch (sizeof($va_raw_terms)) {
case 2:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1];
break;
case 3:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2];
break;
default:
$vs_dimension = join(' ', $va_raw_terms);
}
$vo_parsed_measurement = caParseWeightDimension($vs_dimension);
$vn_weight = $vo_parsed_measurement->convertTo('KILOGRAM', 6, 'en_US');
} catch (Exception $e) {
$vs_direct_sql_query = null;
break;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_weight) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_TIMECODE__:
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_timecode = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_timecode) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_INTEGER__:
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 = " . intval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_NUMERIC__:
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
}
}
} else {
// neither table fields nor elements, i.e. 'virtual' fields like _count should
$vn_fld_num = false;
$vs_fld_num = $vs_field;
}
}
}
if (($vs_intrinsic_field_name = $t_table->fieldName($vn_fld_num)) && ($vn_intrinsic_type = $t_table->getFieldInfo($vs_intrinsic_field_name, 'FIELD_TYPE')) == FT_BIT) {
$vb_ft_bit_optimization = true;
} elseif ($vn_intrinsic_type == FT_HISTORIC_DATERANGE) {
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_date_start_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'START');
$vs_date_end_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'END');
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} <= " . floatval($va_dates['start']) . " AND {$vs_date_end_fld} >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t";
}
}
$pa_direct_sql_query_params = array();
}
}
}
}
//
// If we're querying on the fulltext index then we need to construct
// the query here... if we already have a direct SQL query to run then we can skip this
//
if ($vb_is_blank_search) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (swi.word_id = 0))";
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} elseif (!$vs_direct_sql_query) {
$va_sql_where = array();
if (sizeof($va_ft_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.word IN (" . join(',', $va_ft_terms) . ")))";
} else {
if (sizeof($va_ft_terms) == 1) {
$va_sql_where[] = "(sw.word = " . $va_ft_terms[0] . ")";
} else {
$va_sql_where[] = "(sw.word IN (" . join(',', $va_ft_terms) . "))";
}
}
}
if (sizeof($va_ft_like_terms)) {
$va_tmp = array();
foreach ($va_ft_like_terms as $vs_term) {
if ($vb_ft_bit_optimization) {
$va_tmp[] = '(sw.word = \' ' . $this->opo_db->escape(trim($vs_term)) . ' \')';
} else {
$va_tmp[] = '(sw.word LIKE \'' . $this->opo_db->escape(trim($vs_term)) . '%\')';
}
}
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (" . join(' AND ', $va_tmp) . "))";
} else {
$va_sql_where[] = "(" . join(' AND ', $va_tmp) . ")";
}
}
if (sizeof($va_ft_stem_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.stem IN (" . join(',', $va_ft_stem_terms) . ")))";
} else {
$va_sql_where[] = "(sw.stem IN (" . join(',', $va_ft_stem_terms) . "))";
}
}
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} else {
$va_ft_terms = $va_ft_like_terms = $va_ft_like_terms = array();
}
$vs_rel_type_id_sql = null;
if (is_array($va_access_point_info['relationship_type_ids']) && sizeof($va_access_point_info['relationship_type_ids'])) {
$vs_rel_type_id_sql = " AND (swi.rel_type_id IN (" . join(",", $va_access_point_info['relationship_type_ids']) . "))";
}
if (!$vs_fld_num && is_array($va_restrict_to_fields = caGetOption('restrictSearchToFields', $pa_options, null)) && sizeof($va_restrict_to_fields)) {
$va_field_restrict_sql = array();
foreach ($va_restrict_to_fields as $va_restrict) {
$va_field_restrict_sql[] = "((swi.field_table_num = " . intval($va_restrict['table_num']) . ") AND (swi.field_num = '" . $va_restrict['field_num'] . "'))";
}
$vs_sql_where .= " AND (" . join(" OR ", $va_field_restrict_sql) . ")";
}
$va_join = array();
if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) {
// We're doing direct queries on metadata in a related table, fun!
// Now let's rewrite the direct query to work...
if ($t_target = $this->opo_datamodel->getInstanceByTableNum($vn_direct_sql_target_table_num, true)) {
// First we create the join from the related table to our subject
$vs_target_table_name = $t_target->tableName();
$va_path = array_keys($this->opo_datamodel->getPath($vn_direct_sql_target_table_num, $pn_subject_tablenum));
$vs_left_table = array_shift($va_path);
$vn_cj = 0;
foreach ($va_path as $vs_right_table) {
if (sizeof($va_rels = $this->opo_datamodel->getRelationships($vs_left_table, $vs_right_table)) > 0) {
$va_join[] = "INNER JOIN {$vs_right_table} ON {$vs_right_table}." . $va_rels[$vs_left_table][$vs_right_table][0][1] . " = " . ($vn_cj == 0 ? 'ca.row_id' : "{$vs_left_table}." . $va_rels[$vs_left_table][$vs_right_table][0][0]);
}
$vs_left_table = $vs_right_table;
$vn_cj++;
}
// Next we rewrite the key we're pulling to be from our subject
$vs_direct_sql_query = str_replace("SELECT ca.row_id", "SELECT " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true), $vs_direct_sql_query);
// Finally we pray
}
}
if ($vn_i == 0) {
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$vs_sql = "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}";
if (strpos($vs_sql, '?') !== false && (!is_array($pa_direct_sql_query_params) || sizeof($pa_direct_sql_query_params) == 0)) {
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t" . (!$vb_is_blank_search ? "INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id" : '') . "\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array((int) $pn_subject_tablenum);
}
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1 && !$vs_direct_sql_query) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
$t = new Timer();
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array();
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
$vn_i++;
if ($this->debug) {
Debug::msg('FIRST: ' . $vs_sql . " [{$pn_subject_tablenum}] " . $t->GetTime(4));
}
} else {
switch ($vs_op) {
case 'AND':
if ($vs_direct_sql_query) {
if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) {
array_push($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true));
} else {
array_unshift($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = ca.row_id");
}
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = $vs_direct_sql_query ? "{$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
$t = new Timer();
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
if ($this->debug) {
Debug::msg('AND: ' . $vs_sql . ' ' . $t->GetTime(4) . ' ' . $qr_res->numRows());
}
if (is_array($va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id')) && sizeof($va_ids)) {
$vs_sql = "DELETE FROM {$ps_dest_table} WHERE row_id NOT IN (?)";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
if ($this->debug) {
Debug::msg('AND DELETE: ' . $vs_sql . ' ' . $t->GetTime(4));
}
} else {
// we don't have any results left, ie. our AND query should yield an empty result
$this->opo_db->query("DELETE FROM {$ps_dest_table}");
}
$vn_i++;
break;
case 'NOT':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t" . ($vs_sql_where ? "{$vs_sql_where} AND " : "") . " swi.table_num = ? \n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '');
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
$va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id');
if (sizeof($va_ids) > 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\trow_id IN (?)\n\t\t\t\t\t\t\t\t\t";
if ($this->debug) {
Debug::msg('NOT ' . $vs_sql);
}
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
}
$vn_i++;
break;
default:
case 'OR':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if ($this->debug) {
Debug::msg('OR ' . $vs_sql);
}
$vn_i++;
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
break;
}
}
// Drop any temporary tables created by direct search queries
foreach (array_keys($va_direct_query_temp_tables) as $vs_temp_table_to_drop) {
$this->_dropTempTable($vs_temp_table_to_drop);
}
break;
default:
//print get_class($o_lucene_query_element);
break;
}
}
}
public function testParseNowDate()
{
$o_tep = new TimeExpressionParser();
$vb_res = $o_tep->parse('now');
$this->assertEquals($vb_res, true);
$va_parse = $o_tep->getUnixTimestamps();
$this->assertEquals($va_parse['start'], $t = time());
$this->assertEquals($va_parse['end'], $t);
$this->assertEquals($va_parse[0], $t);
$this->assertEquals($va_parse[1], $t);
}
/**
* Get orders matching criteria specified by options
*
* @param array $pa_options An array of options:
* user_id =
* transaction_id =
* created_on = date range expression
* shipping_date =
* shipped_on_date =
* shipping_method =
* order_status =
* search =
* type =
* loan_checkout_date =
* loan_due_date =
* loan_return_date =
* is_overdue =
* is_outstanding =
* object_id =
* exclude = optional array of order_id's to omit from the returned list
*/
public function getOrders($pa_options = null)
{
$o_db = $this->getDb();
$vb_join_transactions = false;
$va_sql_wheres = $va_sql_values = array();
if (isset($pa_options['is_overdue']) && (bool) $pa_options['is_overdue']) {
$pa_options['type'] = 'L';
$va_sql_wheres[] = "(i.loan_due_date < ?)";
$va_sql_values[] = time();
$va_sql_wheres[] = "(i.loan_return_date IS NULL)";
}
if (isset($pa_options['exclude']) && is_array($pa_options['exclude'])) {
$va_sql_wheres[] = "(o.order_id NOT IN (?))";
$va_sql_values[] = $pa_options['exclude'];
}
if (isset($pa_options['is_outstanding']) && (bool) $pa_options['is_outstanding']) {
$pa_options['type'] = 'L';
$va_sql_wheres[] = "(i.loan_return_date IS NULL)";
}
if (!is_array($pa_options['order_status'])) {
if (isset($pa_options['order_status']) && strlen($pa_options['order_status'])) {
$pa_options['order_status'] = array((string) $pa_options['order_status']);
}
}
if (is_array($pa_options['order_status'])) {
foreach ($pa_options['order_status'] as $vn_i => $vs_s) {
if (!in_array($vs_s, $this->getFieldInfo('order_status', 'BOUNDS_CHOICE_LIST'))) {
unset($pa_options['order_status'][$vn_i]);
}
}
if (sizeof($pa_options['order_status'])) {
$va_sql_wheres[] = "(o.order_status IN (?))";
$va_sql_values[] = $pa_options['order_status'];
}
}
if (isset($pa_options['type']) && in_array($pa_options['type'], array('O', 'L'))) {
$va_sql_wheres[] = "(o.order_type = ?)";
$va_sql_values[] = (string) $pa_options['type'];
}
if (isset($pa_options['shipping_method']) && strlen($pa_options['shipping_method'])) {
$va_sql_wheres[] = "(o.shipping_method = ?)";
$va_sql_values[] = (string) $pa_options['shipping_method'];
}
if (isset($pa_options['user_id']) && strlen($pa_options['user_id'])) {
$va_sql_wheres[] = "(t.user_id = ?)";
$va_sql_values[] = (int) $pa_options['user_id'];
$vb_join_transactions = true;
}
if (isset($pa_options['transaction_id']) && strlen($pa_options['transaction_id'])) {
$va_sql_wheres[] = "(o.transaction_id = ?)";
$va_sql_values[] = (int) $pa_options['transaction_id'];
}
if (isset($pa_options['created_on']) && strlen($pa_options['created_on'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['created_on']))) {
$va_sql_wheres[] = "(o.created_on BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['object_id']) && strlen($pa_options['object_id'])) {
$va_sql_wheres[] = "(i.object_id = ?)";
$va_sql_values[] = (int) $pa_options['object_id'];
}
if (isset($pa_options['loan_checkout_date']) && strlen($pa_options['loan_checkout_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_checkout_date']))) {
$va_sql_wheres[] = "(i.loan_checkout_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['loan_due_date']) && strlen($pa_options['loan_due_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_due_date']))) {
$va_sql_wheres[] = "(i.loan_due_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['loan_return_date']) && strlen($pa_options['loan_return_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_return_date']))) {
$va_sql_wheres[] = "(i.loan_return_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['shipping_date']) && strlen($pa_options['shipping_date'])) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['shipping_date'])) {
$va_dates = $o_tep->getUnixTimestamps();
$va_sql_wheres[] = "(o.shipping_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['shipped_on_date']) && strlen($pa_options['shipped_on_date'])) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['shipped_on_date'])) {
$va_dates = $o_tep->getUnixTimestamps();
$va_sql_wheres[] = "(o.shipped_on_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['search']) && strlen($pa_options['search'])) {
$o_search = new CommerceOrderSearch();
if ($qr_hits = $o_search->search($pa_options['search'])) {
$va_ids = array();
while ($qr_hits->nextHit()) {
$va_ids[] = $qr_hits->get('order_id');
}
if (sizeof($va_ids)) {
$va_sql_wheres[] = "(o.order_id IN (?))";
$va_sql_values[] = $va_ids;
} else {
$va_sql_wheres[] = "(o.order_id = 0)";
}
}
}
$vs_sql_wheres = '';
if (sizeof($va_sql_wheres)) {
$vs_sql_wheres = " AND " . join(" AND ", $va_sql_wheres);
}
// Get item additional fees
$qr_res = $o_db->query($vs_sql = "\n\t \t\tSELECT \n\t \t\t\to.order_id, i.item_id, i.additional_fees\n\t \t\tFROM ca_commerce_orders o\n\t \t\tLEFT JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t \t\tWHERE\n\t \t\t\to.deleted = 0 {$vs_sql_wheres}\n\t \t\t\t\n\t \t", $va_sql_values);
$va_additional_fee_codes = $this->opo_client_services_config->getAssoc($this->get('order_type') == 'L' ? 'additional_loan_fees' : 'additional_order_item_fees');
$va_order_item_additional_fees = array();
while ($qr_res->nextRow()) {
$va_fees = caUnserializeForDatabase($qr_res->get('additional_fees'));
$vn_fee_total = 0;
foreach ($va_additional_fee_codes as $vs_code => $va_info) {
if (isset($va_fees[$vs_code])) {
$vn_fee_total += (double) $va_fees[$vs_code];
}
}
$va_order_item_additional_fees[$qr_res->get('order_id')] += $vn_fee_total;
}
// Get overdue items (only if type is set to [L]oan)
if (isset($pa_options['type']) && $pa_options['type'] == 'L') {
$qr_res = $o_db->query("\n\t\t\t\tSELECT \n\t\t\t\t\to.order_id, \n\t\t\t\t\tmin(i.loan_checkout_date) loan_checkout_date, min(i.loan_due_date) loan_due_date\n\t\t\t\tFROM ca_commerce_orders o\n\t\t\t\tINNER JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t\t\t\tWHERE\n\t\t\t\t\to.deleted = 0 AND i.loan_return_date IS NULL\n\t\t\t\t\t{$vs_sql_wheres}\n\t\t\t\tGROUP BY o.order_id\n\t\t\t\t\t\n\t\t\t", $va_sql_values);
$va_due_dates = $va_overdue_dates = array();
$vn_t = time();
while ($qr_res->nextRow()) {
$vn_due_date = $qr_res->get('loan_due_date');
if ($vn_due_date > $vn_t) {
$va_due_dates[$qr_res->get('order_id')] = caFormatInterval($vn_due_date - $vn_t, 2);
} else {
$va_overdue_dates[$qr_res->get('order_id')] = caFormatInterval($vn_t - $vn_due_date, 2);
}
}
}
// Get item totals
$qr_res = $o_db->query($vs_sql = "\n\t \t\tSELECT \n\t \t\t\to.*, \n\t \t\t\tsum(i.fee) order_total_item_fees, \n\t \t\t\tsum(i.tax) order_total_item_tax, \n\t \t\t\t((o.shipping_cost) + (i.shipping_cost)) order_total_shipping, \n\t \t\t\t((o.handling_cost) + (i.handling_cost)) order_total_handling, \n\t \t\t\tcount(*) num_items, \n\t \t\t\tmin(i.loan_checkout_date) loan_checkout_date_start, min(i.loan_due_date) loan_due_date_start, min(i.loan_return_date) loan_return_date_start,\n\t \t\t\tmax(i.loan_checkout_date) loan_checkout_date_end, max(i.loan_due_date) loan_due_date_end, max(i.loan_return_date) loan_return_date_end\n\t \t\tFROM ca_commerce_orders o\n\t \t\tLEFT JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t \t\tWHERE\n\t \t\t\to.deleted = 0 {$vs_sql_wheres}\n\t \t\tGROUP BY o.order_id\n\t \t\tORDER BY\n\t \t\t\to.created_on DESC\n\t \t\t\t\n\t \t", $va_sql_values);
//print $vs_sql."; ".print_r($va_sql_values, true);
$va_orders = array();
while ($qr_res->nextRow()) {
$va_order = $qr_res->getRow();
$va_order['order_number'] = date('mdY', $va_order['created_on']) . '-' . $va_order['order_id'];
// order additional fees
$vn_additional_order_fees = 0;
if (is_array($va_additional_fees = caUnserializeForDatabase($va_order['additional_fees']))) {
foreach ($va_additional_fees as $vs_code => $vn_fee) {
$vn_additional_order_fees += $vn_fee;
}
}
$va_order['order_total'] = $va_order['order_total_item_fees'] + $va_order['order_total_item_tax'] + $va_order['order_total_shipping'] + $va_order['order_total_handling'] + $vn_additional_order_fees + (double) $va_order_item_additional_fees[$qr_res->get('order_id')];
if (isset($va_overdue_dates[$va_order['order_id']])) {
$va_order['is_overdue'] = true;
$va_order['overdue_period'] = $va_overdue_dates[$va_order['order_id']];
} else {
if (isset($va_due_dates[$va_order['order_id']])) {
$va_order['is_overdue'] = false;
$va_order['due_period'] = $va_due_dates[$va_order['order_id']];
}
}
$va_orders[] = $va_order;
}
return $va_orders;
}
/**
* Set field value(s) for the table row represented by this object
*
* @param string|array string $pa_fields representation of a field name
* or array of string representations of field names
* @param mixed $pm_value value to set the given field(s) to
* @param array $pa_options associative array of options
* possible options (keys):
* when dealing with date/time fields:
* - SET_DIRECT_DATE
* - SET_DIRECT_TIME
* - SET_DIRECT_TIMES
*
* for media/files fields:
* - original_filename : (note that it is lower case) optional parameter which enables you to pass the original filename of a file, in addition to the representation in the temporary, global _FILES array;
*
* for text fields:
* - purify : if set then text input is run through HTML Purifier before being set
*/
public function set($pa_fields, $pm_value = "", $pa_options = null)
{
$this->errors = array();
if (!is_array($pa_fields)) {
$pa_fields = array($pa_fields => $pm_value);
}
foreach ($pa_fields as $vs_field => $vm_value) {
if (array_key_exists($vs_field, $this->FIELDS)) {
$pa_fields_type = $this->getFieldInfo($vs_field, "FIELD_TYPE");
$pb_need_reload = false;
if (!$this->verifyFieldValue($vs_field, $vm_value, $pb_need_reload)) {
return false;
}
if ($pb_need_reload) {
return true;
}
// was set to default
if ($vs_field == $this->primaryKey()) {
$vm_value = preg_replace("/[\"']/", "", $vm_value);
}
// what markup is supported for text fields?
$vs_markup_type = $this->getFieldInfo($vs_field, "MARKUP_TYPE");
// if markup is non-HTML then strip out HTML special chars for safety
if (!($vs_markup_type == __CA_MT_HTML__)) {
$vm_value = htmlspecialchars($vm_value, ENT_QUOTES, 'UTF-8');
}
$vs_cur_value = isset($this->_FIELD_VALUES[$vs_field]) ? $this->_FIELD_VALUES[$vs_field] : null;
switch ($pa_fields_type) {
case FT_NUMBER:
if ($vs_cur_value != $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
if ($vm_value !== "" || $this->getFieldInfo($vs_field, "IS_NULL") && $vm_value == "") {
if ($vm_value) {
if (($vs_list_code = $this->getFieldInfo($vs_field, "LIST_CODE")) && !is_numeric($vm_value)) {
// translate ca_list_item idno's into item_ids if necessary
if ($vn_id = ca_lists::getItemID($vs_list_code, $vm_value)) {
$vm_value = $vn_id;
}
} else {
$vm_orig_value = $vm_value;
$vm_value = preg_replace("/[^\\d-.]+/", "", $vm_value);
# strip non-numeric characters
if (!preg_match("/^[\\-]{0,1}[\\d.]+\$/", $vm_value)) {
$this->postError(1100, _t("'%1' for %2 is not numeric", $vm_orig_value, $vs_field), "BaseModel->set()");
return "";
}
}
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
}
break;
case FT_BIT:
if ($vs_cur_value != $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value ? 1 : 0;
break;
case FT_DATETIME:
case FT_HISTORIC_DATETIME:
case FT_DATE:
case FT_HISTORIC_DATE:
if ($this->DIRECT_DATETIMES || $pa_options["SET_DIRECT_DATE"]) {
$this->_FIELD_VALUES[$vs_field] = $vm_value;
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = null;
} else {
$o_tep = new TimeExpressionParser();
if ($pa_fields_type == FT_DATE || $pa_fields_type == FT_HISTORIC_DATE) {
$va_timestamps = $o_tep->parseDate($vm_value);
} else {
$va_timestamps = $o_tep->parseDatetime($vm_value);
}
if (!$va_timestamps) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
if ($pa_fields_type == FT_HISTORIC_DATETIME || $pa_fields_type == FT_HISTORIC_DATE) {
if ($vs_cur_value != $va_timestamps["start"]) {
$this->_FIELD_VALUES[$vs_field] = $va_timestamps["start"];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
} else {
$va_timestamps = $o_tep->getUnixTimestamps();
if ($va_timestamps[0] == -1) {
$this->postError(1830, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
if ($vs_cur_value != $va_timestamps["start"]) {
$this->_FIELD_VALUES[$vs_field] = $va_timestamps["start"];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
}
}
break;
case FT_TIME:
if ($this->DIRECT_TIMES || $pa_options["SET_DIRECT_TIME"]) {
$this->_FIELD_VALUES[$vs_field] = $vm_value;
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseTime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
$va_times = $o_tep->getTimes();
if ($vs_cur_value != $va_times['start']) {
$this->_FIELD_VALUES[$vs_field] = $va_times['start'];
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
}
break;
case FT_TIMESTAMP:
# can't set timestamp
break;
case FT_DATERANGE:
case FT_HISTORIC_DATERANGE:
$vs_start_field_name = $this->getFieldInfo($vs_field, "START");
$vs_end_field_name = $this->getFieldInfo($vs_field, "END");
$vn_start_date = isset($this->_FIELD_VALUES[$vs_start_field_name]) ? $this->_FIELD_VALUES[$vs_start_field_name] : null;
$vn_end_date = isset($this->_FIELD_VALUES[$vs_end_field_name]) ? $this->_FIELD_VALUES[$vs_end_field_name] : null;
if ($this->DIRECT_DATETIMES || $pa_options["SET_DIRECT_DATE"]) {
if (is_array($vm_value) && sizeof($vm_value) == 2 && $vm_value[0] <= $vm_value[1]) {
if ($vn_start_date != $vm_value[0]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $vm_value[0];
}
if ($vn_end_date != $vm_value[1]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $vm_value[1];
}
} else {
$this->postError(1100, _t("Invalid direct date values"), "BaseModel->set()");
}
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($vn_start_date || $vn_end_date) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_start_field_name] = null;
$this->_FIELD_VALUES[$vs_end_field_name] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseDatetime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
if ($pa_fields_type == FT_HISTORIC_DATERANGE) {
$va_timestamps = $o_tep->getHistoricTimestamps();
} else {
$va_timestamps = $o_tep->getUnixTimestamps();
if ($va_timestamps[0] == -1) {
$this->postError(1830, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
}
if ($vn_start_date != $va_timestamps["start"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $va_timestamps["start"];
}
if ($vn_end_date != $va_timestamps["end"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $va_timestamps["end"];
}
}
}
break;
case FT_TIMERANGE:
$vs_start_field_name = $this->getFieldInfo($vs_field, "START");
$vs_end_field_name = $this->getFieldInfo($vs_field, "END");
if ($this->DIRECT_TIMES || $pa_options["SET_DIRECT_TIMES"]) {
if (is_array($vm_value) && sizeof($vm_value) == 2 && $vm_value[0] <= $vm_value[1]) {
if ($this->_FIELD_VALUES[$vs_start_field_name] != $vm_value[0]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $vm_value[0];
}
if ($this->_FIELD_VALUES[$vs_end_field_name] != $vm_value[1]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $vm_value[1];
}
} else {
$this->postError(1100, _t("Invalid direct time values"), "BaseModel->set()");
}
} else {
if (!$vm_value && $this->FIELDS[$vs_field]["IS_NULL"]) {
if ($this->_FIELD_VALUES[$vs_start_field_name] || $this->_FIELD_VALUES[$vs_end_field_name]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_start_field_name] = null;
$this->_FIELD_VALUES[$vs_end_field_name] = null;
} else {
$o_tep = new TimeExpressionParser();
if (!$o_tep->parseTime($vm_value)) {
$this->postError(1805, $o_tep->getParseErrorMessage(), 'BaseModel->set()');
return false;
}
$va_timestamps = $o_tep->getTimes();
if ($this->_FIELD_VALUES[$vs_start_field_name] != $va_timestamps["start"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_start_field_name] = $va_timestamps["start"];
}
if ($this->_FIELD_VALUES[$vs_end_field_name] != $va_timestamps["end"]) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_end_field_name] = $va_timestamps["end"];
}
}
}
break;
case FT_TIMECODE:
$o_tp = new TimecodeParser();
if ($o_tp->parse($vm_value)) {
if ($o_tp->getParsedValueInSeconds() != $vs_cur_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
$this->_FIELD_VALUES[$vs_field] = $o_tp->getParsedValueInSeconds();
}
}
break;
case FT_TEXT:
$vm_value = (string) $vm_value;
if (is_string($vm_value)) {
$vm_value = stripSlashes($vm_value);
}
if (isset($pa_options['purify']) && $pa_options['purify'] || (bool) $this->opb_purify_input || $this->getFieldInfo($vs_field, "PURIFY") || (bool) $this->getAppConfig()->get('useHTMLPurifier')) {
if (!BaseModel::$html_purifier) {
BaseModel::$html_purifier = new HTMLPurifier();
}
$vm_value = BaseModel::$html_purifier->purify((string) $vm_value);
}
if ($this->getFieldInfo($vs_field, "DISPLAY_TYPE") == DT_LIST_MULTIPLE) {
if (is_array($vm_value)) {
if (!($vs_list_multiple_delimiter = $this->getFieldInfo($vs_field, 'LIST_MULTIPLE_DELIMITER'))) {
$vs_list_multiple_delimiter = ';';
}
$vs_string_value = join($vs_list_multiple_delimiter, $vm_value);
$vs_string_value = str_replace("", '', $vs_string_value);
if ($vs_cur_value !== $vs_string_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_string_value;
}
} else {
$vm_value = str_replace("", '', $vm_value);
if ($this->getFieldInfo($vs_field, "ENTITY_ENCODE_INPUT")) {
$vs_value_entity_encoded = htmlentities(html_entity_decode($vm_value));
if ($vs_cur_value !== $vs_value_entity_encoded) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_value_entity_encoded;
} else {
if ($this->getFieldInfo($vs_field, "URL_ENCODE_INPUT")) {
$vs_value_url_encoded = urlencode($vm_value);
if ($vs_cur_value !== $vs_value_url_encoded) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vs_value_url_encoded;
} else {
if ($vs_cur_value !== $vm_value) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
}
}
}
break;
case FT_PASSWORD:
if (!$vm_value) {
// store blank passwords as blank, not MD5 of blank
$this->_FIELD_VALUES[$vs_field] = $vs_crypt_pw = "";
} else {
if ($this->_CONFIG->get("use_old_style_passwords")) {
$vs_crypt_pw = crypt($vm_value, substr($vm_value, 0, 2));
} else {
$vs_crypt_pw = md5($vm_value);
}
if ($vs_cur_value != $vm_value && $vs_cur_value != $vs_crypt_pw) {
$this->_FIELD_VALUES[$vs_field] = $vs_crypt_pw;
}
if ($vs_cur_value != $vs_crypt_pw) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
}
break;
case FT_VARS:
if (md5(print_r($vs_cur_value, true)) != md5(print_r($vm_value, true))) {
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
}
$this->_FIELD_VALUES[$vs_field] = $vm_value;
break;
case FT_MEDIA:
case FT_FILE:
$vb_allow_fetching_of_urls = (bool) $this->_CONFIG->get('allow_fetching_of_media_from_remote_urls');
# if there's a tmp_name is the global _FILES array
# then we'll process it in insert()/update()...
$this->_SET_FILES[$vs_field]['options'] = $pa_options;
if (caGetOSFamily() == OS_WIN32) {
// fix for paths using backslashes on Windows failing in processing
$vm_value = str_replace('\\', '/', $vm_value);
}
$va_matches = null;
if (is_string($vm_value) && (file_exists($vm_value) || $vb_allow_fetching_of_urls && isURL($vm_value))) {
$this->_SET_FILES[$vs_field]['original_filename'] = $pa_options["original_filename"];
$this->_SET_FILES[$vs_field]['tmp_name'] = $vm_value;
$this->_FIELD_VALUE_CHANGED[$vs_field] = true;
} else {
# only return error when file name is not 'none'
# 'none' is PHP's stupid way of telling you there
# isn't a file...
if ($vm_value != "none" && $vm_value) {
//$this->postError(1500,_t("%1 does not exist", $vm_value),"BaseModel->set()");
}
return false;
}
break;
default:
die("Invalid field type in BaseModel->set()");
break;
}
} else {
$this->postError(710, _t("'%1' does not exist in this object", $vs_field), "BaseModel->set()");
return false;
}
}
return true;
}
/**
* Actually do the browse
*
* Options:
* checkAccess = array of access values to filter facets that have an 'access' field by
* no_cache = don't use cached browse results
* showDeleted = if set to true, related items that have been deleted are returned. Default is false.
* limitToModifiedOn = if set returned results will be limited to rows modified within the specified date range. The value should be a date/time expression parse-able by TimeExpressionParser
* user_id = If set item level access control is performed relative to specified user_id, otherwise defaults to logged in user
*/
public function execute($pa_options = null)
{
global $AUTH_CURRENT_USER_ID;
if (!is_array($pa_options)) {
$pa_options = array();
}
$vn_user_id = isset($pa_options['user_id']) && (int) $pa_options['user_id'] ? (int) $pa_options['user_id'] : (int) $AUTH_CURRENT_USER_ID;
if (!is_array($this->opa_browse_settings)) {
return null;
}
$va_params = $this->opo_ca_browse_cache->getParameters();
$vb_need_to_cache_facets = false;
$vb_results_cached = false;
$vb_need_to_save_in_cache = false;
$vs_cache_key = $this->opo_ca_browse_cache->getCurrentCacheKey();
if ($this->opo_ca_browse_cache->load($vs_cache_key)) {
$vn_created_on = $this->opo_ca_browse_cache->getParameter('created_on');
//$t_new_browse->get('created_on', array('GET_DIRECT_DATE' => true));
$va_criteria = $this->getCriteria();
if ((!isset($pa_options['no_cache']) || !$pa_options['no_cache']) && intval(time() - $vn_created_on) < $this->opo_ca_browse_config->get('cache_timeout')) {
$vb_results_cached = true;
//print "cache hit for [$vs_cache_key]<br>";
} else {
$va_criteria = $this->getCriteria();
$this->opo_ca_browse_cache->remove();
$this->opo_ca_browse_cache->setParameter('criteria', $va_criteria);
//print "cache expire for [$vs_cache_key]<br>";
$vb_need_to_save_in_cache = true;
$vb_need_to_cache_facets = true;
}
} else {
$va_criteria = $this->getCriteria();
//print "cache miss for [$vs_cache_key]<br>";
$vb_need_to_save_in_cache = true;
}
if (!$vb_results_cached) {
$this->opo_ca_browse_cache->setParameter('sort', null);
$this->opo_ca_browse_cache->setParameter('created_on', time());
$this->opo_ca_browse_cache->setParameter('table_num', $this->opn_browse_table_num);
$vb_need_to_cache_facets = true;
}
$this->opb_criteria_have_changed = false;
$t_item = $this->opo_datamodel->getInstanceByTableName($this->ops_browse_table_name, true);
$va_results = array();
if (is_array($va_criteria) && sizeof($va_criteria) > 0) {
if (!$vb_results_cached) {
// generate results
$this->_createTempTable('ca_browses_acc');
$this->_createTempTable('ca_browses_tmp');
$vn_i = 0;
foreach ($va_criteria as $vs_facet_name => $va_row_ids) {
$vs_target_browse_table_name = $t_item->tableName();
$vs_target_browse_table_num = $t_item->tableNum();
$vs_target_browse_table_pk = $t_item->primaryKey();
$va_facet_info = $this->getInfoForFacet($vs_facet_name);
$va_row_ids = array_keys($va_row_ids);
$vs_relative_to_join = '';
switch ($va_facet_info['type']) {
# -----------------------------------------------------
case 'has':
$vs_rel_table_name = $va_facet_info['table'];
$va_joins = array();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_joins = array_merge($va_joins, $va_relative_execute_sql_data['relative_joins']);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
if ($va_facet_info['element_code']) {
$t_element = new ca_metadata_elements();
if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
break;
}
$vs_element_code = $va_facet_info['element_code'];
$vn_state = array_pop($va_row_ids);
if ($vn_state == 0) {
$va_wheres[] = $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " NOT IN (SELECT row_id FROM ca_attributes WHERE table_num = " . $t_item->tableNum() . " AND element_id = " . $t_element->getPrimaryKey() . ")";
} else {
$va_joins[] = "INNER JOIN ca_attributes AS caa ON caa.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND caa.table_num = " . $t_item->tableNum();
$va_wheres[] = "caa.element_id = " . $t_element->getPrimaryKey();
}
} else {
if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) {
$va_restrict_to_relationship_types = array();
}
$va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']);
if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) {
$va_exclude_relationship_types = array();
}
$va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']);
$vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name);
$vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num);
switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) {
case 3:
$t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true);
$vs_key = 'relation_id';
break;
case 2:
$t_item_rel = null;
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$vs_key = $t_rel_item->primaryKey();
break;
default:
// bad related table
return null;
break;
}
$vs_cur_table = array_shift($va_path);
$vn_state = array_pop($va_row_ids);
foreach ($va_path as $vs_join_table) {
$va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
$va_joins[] = ($vn_state ? 'INNER' : 'LEFT') . ' JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n";
$vs_cur_table = $vs_join_table;
}
$va_wheres = array();
if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))";
}
if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))";
}
if (!(bool) $vn_state) {
// no option
$va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL)";
if ($t_rel_item->hasField('deleted')) {
$va_wheres[] = "((" . $t_rel_item->tableName() . ".deleted = 0) OR (" . $t_rel_item->tableName() . ".deleted IS NULL))";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) {
$va_wheres[] = "((" . $t_rel_item->tableName() . ".access NOT IN (" . join(',', $pa_options['checkAccess']) . ")) OR ((" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL) AND (" . $t_rel_item->tableName() . ".access IS NULL)))";
}
} else {
// yes option
$va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NOT NULL)";
if ($t_rel_item->hasField('deleted')) {
$va_wheres[] = "(" . $t_rel_item->tableName() . ".deleted = 0)";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) {
$va_wheres[] = "(" . $t_rel_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
}
}
if ($t_item->hasField('deleted')) {
$va_wheres[] = "(" . $t_item->tableName() . ".deleted = 0)";
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_wheres[] = "(" . $t_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
$vs_join_sql = join("\n", $va_joins);
$vs_where_sql = '';
if (sizeof($va_wheres) > 0) {
$vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres);
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'label':
if (!($t_label = $t_item->getLabelTableInstance())) {
break;
}
$vs_label_item_pk = $vs_item_pk = $t_item->primaryKey();
$vs_label_table_name = $t_label->tableName();
$vs_label_pk = $t_label->primaryKey();
$vs_label_display_field = $t_item->getLabelDisplayField();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
$t_target = $this->opo_datamodel->getInstanceByTableName($va_facet_info['relative_to'], true);
$t_target_label = $t_target->getLabelTableInstance();
$vs_item_pk = $t_target->primaryKey();
$vs_label_table_name = $t_target_label->tableName();
$vs_label_item_pk = $t_target_label->primaryKey();
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$va_relative_to_join[] = "INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}";
}
} else {
$va_relative_to_join = array("INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}");
}
$vs_relative_to_join = join("\n", $va_relative_to_join);
foreach ($va_row_ids as $vn_row_id) {
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_label_table_name}.{$vs_label_item_pk} = ?";
//print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_label_table_name}.{$vs_label_item_pk} = ?";
//print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'field':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
//print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
//print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'attribute':
$t_element = new ca_metadata_elements();
if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
return array();
}
$vn_datatype = $t_element->get('datatype');
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
// TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) Text or Number attribute
// (do we support other types as well?)
$vn_element_id = $t_element->getPrimaryKey();
$o_attr = Attribute::getValueInstance($t_element->get('datatype'));
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
$vn_row_id = str_replace('/', '/', $vn_row_id);
$va_value = $o_attr->parseValue($vn_row_id, $t_element->getFieldValuesArray());
$va_attr_sql = array();
$va_attr_values = array(intval($vs_target_browse_table_num), $vn_element_id);
if (is_array($va_value)) {
foreach ($va_value as $vs_f => $vs_v) {
if ($vn_datatype == 3) {
// list
$t_list_item = new ca_list_items((int) $vs_v);
// Include sub-items
$va_item_ids = $t_list_item->getHierarchy((int) $vs_v, array('idsOnly' => true, 'includeSelf' => true));
$va_item_ids[] = (int) $vs_v;
$va_attr_sql[] = "(ca_attribute_values.{$vs_f} IN (?))";
$va_attr_values[] = $va_item_ids;
} else {
$va_attr_sql[] = "(ca_attribute_values.{$vs_f} " . (is_null($vs_v) ? " IS " : " = ") . " ?)";
$va_attr_values[] = $vs_v;
}
}
}
if ($vs_attr_sql = join(" AND ", $va_attr_sql)) {
$vs_attr_sql = " AND " . $vs_attr_sql;
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) {$vs_attr_sql}";
//caDebug($vs_sql);
//caDebug(intval($vs_target_browse_table_num)."/".$vn_element_id."/".$vn_row_id);
//caDebug($va_attr_values);
$qr_res = $this->opo_db->query($vs_sql, $va_attr_values);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) {$vs_attr_sql}";
//print "$vs_sql [".intval($vs_target_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>";print_R($va_attr_values);
$qr_res = $this->opo_db->query($vs_sql, $va_attr_values);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'normalizedDates':
$t_element = new ca_metadata_elements();
if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
return array();
}
// TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute
$vs_normalization = $va_facet_info['normalization'];
$vn_element_id = $t_element->getPrimaryKey();
$o_tep = new TimeExpressionParser();
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
if (!$o_tep->parse($vn_row_id)) {
continue;
}
// invalid date?
$va_dates = $o_tep->getHistoricTimestamps();
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t";
//print $vs_sql;
$qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $t_item->tableName() . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t";
//print $vs_sql;
$qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'authority':
$vs_rel_table_name = $va_facet_info['table'];
if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) {
$va_restrict_to_relationship_types = array();
}
$va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']);
if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) {
$va_exclude_relationship_types = array();
}
$va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']);
$vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name);
$vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num);
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) {
case 3:
$t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true);
$vs_key = 'relation_id';
break;
case 2:
$t_item_rel = null;
$t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true);
$vs_key = $t_rel_item->primaryKey();
break;
default:
// bad related table
return null;
break;
}
$vs_cur_table = array_shift($va_path);
$va_joins = array();
foreach ($va_path as $vs_join_table) {
$va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
$va_joins[] = 'INNER JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n";
$vs_cur_table = $vs_join_table;
}
$vs_join_sql = join("\n", $va_joins);
$va_wheres = array();
if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))";
}
if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) {
$va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))";
}
$vs_where_sql = '';
if (sizeof($va_wheres) > 0) {
$vs_where_sql = ' AND ' . join(' AND ', $va_wheres);
}
if ((!isset($va_facet_info['dont_expand_hierarchically']) || !$va_facet_info['dont_expand_hierarchically']) && $t_rel_item->isHierarchical() && $t_rel_item->load((int) $vn_row_id)) {
$vs_hier_left_fld = $t_rel_item->getProperty('HIERARCHY_LEFT_INDEX_FLD');
$vs_hier_right_fld = $t_rel_item->getProperty('HIERARCHY_RIGHT_INDEX_FLD');
$vs_get_item_sql = "{$vs_rel_table_name}.{$vs_hier_left_fld} >= " . $t_rel_item->get($vs_hier_left_fld) . " AND {$vs_rel_table_name}.{$vs_hier_right_fld} <= " . $t_rel_item->get($vs_hier_right_fld);
if ($vn_hier_id_fld = $t_rel_item->getProperty('HIERARCHY_ID_FLD')) {
$vs_get_item_sql .= " AND {$vs_rel_table_name}.{$vn_hier_id_fld} = " . (int) $t_rel_item->get($vn_hier_id_fld);
}
$vs_get_item_sql = "({$vs_get_item_sql})";
} else {
$vs_get_item_sql = "({$vs_rel_table_name}.{$vs_rel_table_pk} = " . (int) $vn_row_id . ")";
}
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
case 'fieldList':
$vs_field_name = $va_facet_info['field'];
$vs_table_name = $this->ops_browse_table_name;
if ($va_facet_info['relative_to']) {
if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) {
$va_relative_to_join = $va_relative_execute_sql_data['relative_joins'];
$vs_relative_to_join = join("\n", $va_relative_to_join);
$vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name'];
$vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num'];
$vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk'];
}
}
foreach ($va_row_ids as $vn_row_id) {
$vn_row_id = urldecode($vn_row_id);
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
//print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_row_id."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
} else {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp");
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_table_name}.{$vs_field_name} = ?)";
//print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_row_id."]<hr>";
$qr_res = $this->opo_db->query($vs_sql, $vn_row_id);
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
$qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp");
}
$vn_i++;
}
break;
# -----------------------------------------------------
# -----------------------------------------------------
default:
// handle "search" criteria - search engine queries that can be browsed
if ($vs_facet_name === '_search') {
$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc");
if (!($o_search = caGetSearchInstance($this->ops_browse_table_name))) {
$this->postError(2900, _t("Invalid search type"), "BrowseEngine->execute()");
break;
}
$vs_pk = $t_item->primaryKey();
if (is_array($va_type_ids = $this->getTypeRestrictionList()) && sizeof($va_type_ids)) {
$o_search->setTypeRestrictions($va_type_ids);
}
$va_options = $pa_options;
unset($va_options['sort']);
// browse engine takes care of sort so there is no reason to waste time having the search engine do so
$va_options['filterNonPrimaryRepresentations'] = true;
// filter out non-primary representations in ca_objects results to save (a bit) of time
$qr_res = $o_search->search($va_row_ids[0], $va_options);
if ($qr_res->numHits() > 0) {
$va_ids = array();
$va_id_list = $qr_res->getPrimaryKeyValues();
foreach ($va_id_list as $vn_id) {
$va_ids[] = "({$vn_id})";
}
$this->opo_db->query("INSERT IGNORE INTO ca_browses_acc VALUES " . join(",", $va_ids));
$vn_i++;
}
} else {
$this->postError(2900, _t("Invalid criteria type"), "BrowseEngine->execute()");
}
break;
# -----------------------------------------------------
}
}
$vs_filter_join_sql = $vs_filter_where_sql = '';
$va_wheres = array();
$va_joins = array();
$vs_sql_distinct = '';
if (sizeof($this->opa_result_filters)) {
$va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id';
$va_tmp = array();
foreach ($this->opa_result_filters as $va_filter) {
$vm_val = $this->_filterValueToQueryValue($va_filter);
$va_wheres[] = $this->ops_browse_table_name . '.' . $va_filter['field'] . " " . $va_filter['operator'] . " " . $vm_val;
}
}
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id';
$va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) {
if (!isset($va_joins[$this->ops_browse_table_name])) {
$va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id';
}
$va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)";
}
if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['limitToModifiedOn'])) {
$va_range = $o_tep->getUnixTimestamps();
$va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = ca_browses_acc.row_id AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum();
$va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id";
$va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))";
$vs_sql_distinct = 'DISTINCT';
// need to pull distinct rows since joining the change log can cause dupes
}
}
if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) {
$t_subject = $this->getSubjectInstance();
$va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id';
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . '))';
}
if (sizeof($va_wheres)) {
$vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres);
}
if (sizeof($va_joins)) {
$vs_filter_join_sql = join("\n", $va_joins);
}
$qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_sql_distinct} row_id\n\t\t\t\t\t\tFROM ca_browses_acc\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t");
while ($qr_res->nextRow()) {
$va_results[] = $qr_res->get('row_id', array('binary' => true));
}
$this->_dropTempTable('ca_browses_acc');
$this->_dropTempTable('ca_browses_tmp');
if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) {
$va_results = array_keys($this->filterHitsByACL(array_flip($va_results), $vn_user_id, __CA_ACL_READONLY_ACCESS__));
}
$this->opo_ca_browse_cache->setResults($va_results);
$vb_need_to_save_in_cache = true;
}
} else {
// no criteria - don't try to find anything unless configured to do so
$va_settings = $this->opo_ca_browse_config->getAssoc($this->ops_browse_table_name);
if (isset($va_settings['show_all_for_no_criteria_browse']) && $va_settings['show_all_for_no_criteria_browse']) {
$va_wheres = $va_joins = array();
$vs_pk = $t_item->primaryKey();
if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
}
if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) {
$va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)";
}
if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['limitToModifiedOn'])) {
$va_range = $o_tep->getUnixTimestamps();
$va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . ".{$vs_pk} AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum();
$va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id";
$va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))";
$vs_sql_distinct = 'DISTINCT';
// need to pull distinct rows since joining the change log can cause dupes
}
}
if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) {
$t_subject = $this->getSubjectInstance();
$va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . '))';
}
if (sizeof($va_wheres)) {
$vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres);
}
if (sizeof($va_joins)) {
$vs_filter_join_sql = join("\n", $va_joins);
}
$qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_pk}\n\t\t\t\t\t\tFROM " . $t_item->tableName() . "\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t");
$va_results = $qr_res->getAllFieldValues($vs_pk);
if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) {
$va_results = array_keys($this->filterHitsByACL(array_flip($va_results), $vn_user_id, __CA_ACL_READONLY_ACCESS__));
}
$this->opo_ca_browse_cache->setResults($va_results);
} else {
$this->opo_ca_browse_cache->setResults(array());
}
$vb_need_to_save_in_cache = true;
}
if ($vb_need_to_cache_facets) {
if (!$pa_options['dontCheckFacetAvailability']) {
$this->loadFacetContent($pa_options);
}
}
if ($vb_need_to_save_in_cache) {
$this->opo_ca_browse_cache->save();
}
return true;
}
/**
*
*/
public function getDeletions()
{
//if (!is_object($t_entity = $this->_checkEntity())) { return $t_entity; }
//$vn_id = $t_entity->getPrimaryKey();
$t_object = new ca_objects();
//$va_data = array('id' => $vn_id);
$from = $this->opo_request->getParameter('from', pString);
$until = $this->opo_request->getParameter('until', pString);
if (!$until) {
$until = date('c');
}
$vs_range = $from && $until ? self::utcToDb($from) . ' to ' . self::utcToDb($until) : null;
$o_tep = new TimeExpressionParser();
$vb_parsed_date = false;
if ($vs_range) {
if ($vb_parsed_date = $o_tep->parse($vs_range)) {
$va_range = $o_tep->getUnixTimestamps();
}
}
$o_log = new ApplicationChangeLog();
// $va_log = $o_log->getChangeLogForRow($t_entity, array('range' => $va_range, 'changeType' => 'D'));
$va_log = $o_log->getDeletions('ca_objects', array('range' => $va_range));
$va_data['deletions'] = $va_log;
//
// foreach($va_log as $vs_key => $va_log_item) {
// foreach($va_log_item as $vn_i => $va_log) {
// if (!is_array($va_log['changes'])) { continue; }
// foreach($va_log['changes'] as $vn_j => $va_change) {
// $va_data[$va_change['table_name']][] = array(
// 'datetime' => $va_log['datetime'],
// 'row_id' => $va_change['row_id'],
// 'description' => $va_change['description'],
// 'idno' => $va_change['idno']
// );
// }
// }
// }
//
return $this->makeResponse($va_data);
}