Пример #1
0
 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++;
     }
 }
Пример #2
0
 public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
 {
     $va_solr_search_filters = array();
     $vn_i = 0;
     $va_old_signs = is_object($po_rewritten_query) ? $po_rewritten_query->getSigns() : array();
     $va_terms = $va_signs = array();
     if ($po_rewritten_query) {
         foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
             if (!$va_old_signs || !is_array($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';
             }
             // advanced search queries are for some reason nested 1-element boolean queries in boolean queries
             if (get_class($o_lucene_query_element) == 'Zend_Search_Lucene_Search_Query_Boolean') {
                 $va_subqueries = $o_lucene_query_element->getSubqueries();
                 if (sizeof($va_subqueries) == 1) {
                     $o_lucene_query_element = array_shift($va_subqueries);
                 }
             }
             switch ($vs_class = get_class($o_lucene_query_element)) {
                 case 'Zend_Search_Lucene_Search_Query_Term':
                 case 'Zend_Search_Lucene_Search_Query_MultiTerm':
                 case 'Zend_Search_Lucene_Search_Query_Phrase':
                     $vs_access_point = '';
                     if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
                         $va_raw_terms = 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;
                         }
                         $vs_term = join(" ", $va_raw_terms);
                     } else {
                         $vs_access_point = $o_lucene_query_element->getTerm()->field;
                         $vs_term = $o_lucene_query_element->getTerm()->text;
                     }
                     if ($vs_access_point) {
                         list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
                         if (in_array($vs_table, array('created', 'modified'))) {
                             if (!$this->opo_tep->parse($vs_term)) {
                                 break;
                             }
                             $va_range = $this->opo_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':
                                     if ($vn_user_id) {
                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
                                     } else {
                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
                                     }
                                     break;
                                 case 'modified':
                                     if ($vn_user_id) {
                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
                                     } else {
                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
                                     }
                                     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;
                                                 //
                                                 // 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_exact = $vs_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_term, 1);
                                                             if ($this->opo_tep->parse($vs_term)) {
                                                                 $va_dates = $this->opo_tep->getHistoricTimestamps();
                                                                 // TODO: fix date handling to reflect distinctions in ranges
                                                                 $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_table . '.' . $vs_fld_num));
                                                             }
                                                         } else {
                                                             if ($this->opo_tep->parse($vs_term)) {
                                                                 $va_dates = $this->opo_tep->getHistoricTimestamps();
                                                                 // TODO: fix date handling to reflect distinctions in ranges
                                                                 $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_table . '.' . $vs_fld_num));
                                                             }
                                                         }
                                                         break;
                                                     case 4:
                                                         // geocode
                                                         $t_geocode = new GeocodeAttributeValue();
                                                         if ($va_coords = caParseGISSearch($vs_term)) {
                                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_table . '.' . $vs_fld_num));
                                                         }
                                                         break;
                                                     case 6:
                                                         // currency
                                                         $t_cur = new CurrencyAttributeValue();
                                                         $va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                         $vn_amount = (double) $va_parsed_value['value_decimal1'];
                                                         $vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     case 8:
                                                         // length
                                                         $t_len = new LengthAttributeValue();
                                                         $va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                         $vn_len = (double) $va_parsed_value['value_decimal1'];
                                                         // this is always in meters so we can compare this value to the one in the database
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     case 9:
                                                         // weight
                                                         $t_weight = new WeightAttributeValue();
                                                         $va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                         $vn_weight = (double) $va_parsed_value['value_decimal1'];
                                                         // this is always in kilograms so we can compare this value to the one in the database
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     case 10:
                                                         // timecode
                                                         $t_timecode = new TimecodeAttributeValue();
                                                         $va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                         $vn_timecode = (double) $va_parsed_value['value_decimal1'];
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     case 11:
                                                         // integer
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     case 12:
                                                         // decimal
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_table . '.' . $vs_fld_num));
                                                         break;
                                                     default:
                                                         // everything else
                                                         $o_lucene_query_element->getTerm()->field = $vs_table . '.' . $vs_fld_num;
                                                         break;
                                                 }
                                             } else {
                                                 $vn_fld_num = false;
                                                 $vs_fld_num = $vs_field;
                                             }
                                         }
                                     }
                                 }
                             }
                         }
                     }
                     break;
             }
             $va_terms[] = $o_lucene_query_element;
             $va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
             $vn_i++;
         }
         $o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
         $ps_search_expression = $this->_queryToString($o_rewritten_query);
     }
     if (is_array($pa_filters) && sizeof($pa_filters) && ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters))) {
         $ps_search_expression = "({$ps_search_expression}) AND ({$vs_filter_query})";
     }
     if (preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):(\"[^\"]*\")!", $ps_search_expression, $va_matches) || preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):([^ ]*)!", $ps_search_expression, $va_matches)) {
         foreach ($va_matches[0] as $vn_i => $vs_element) {
             $vs_fld = $va_matches[1][$vn_i];
             if (!($vs_rel_type = trim($va_matches[2][$vn_i]))) {
                 continue;
             }
             $va_tmp = explode(".", $vs_fld);
             $vs_rel_table = caGetRelationshipTableName($pn_subject_tablenum, $va_tmp[0]);
             $va_rel_type_ids = $vs_rel_type && $vs_rel_table ? caMakeRelationshipTypeIDList($vs_rel_table, array($vs_rel_type)) : null;
             $va_new_elements = array();
             foreach ($va_rel_type_ids as $vn_rel_type_id) {
                 $va_new_elements[] = "(" . $va_matches[1][$vn_i] . "/" . $vn_rel_type_id . ":" . $va_matches[3][$vn_i] . ")";
             }
             $ps_search_expression = str_replace($vs_element, "(" . join(" OR ", $va_new_elements) . ")", $ps_search_expression);
         }
     }
     $ps_search_expression = str_replace("/", '\\/', $ps_search_expression);
     // escape forward slashes used to delimit relationship type qualifier
     Debug::msg("[SOLR] Running query {$ps_search_expression}");
     try {
         $vo_http_client = new Zend_Http_Client();
         $vo_http_client->setUri($this->ops_search_solr_url . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/select");
         $vo_http_client->setParameterGet($va_get = array('q' => utf8_decode($ps_search_expression), 'wt' => 'json', 'fl' => $this->opo_datamodel->getTablePrimaryKeyName($pn_subject_tablenum), 'start' => $this->getOption('start'), 'rows' => $this->getOption('limit')));
         $vo_http_response = $vo_http_client->request();
         $va_result = json_decode($vo_http_response->getBody(), true);
     } catch (Exception $e) {
         caLogEvent('ERR', _t('Could not connect to SOLR server: %1', $e->getMessage()), 'Solr->search()');
         $va_result["response"]["docs"] = array();
     }
     return new WLPlugSearchEngineSolrResult($va_result["response"]["docs"], $pn_subject_tablenum);
 }
Пример #3
0
 /**
  * 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('&#47;', '/', $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('&#47;', '/', $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;
 }
 /**
  * Options:
  * 		rawDate - if true, returns date as an array of start and end historic timestames
  *		sortable - if true a language-independent sortable representation is returned.
  *		getDirectDate - get underlying historic timestamp (floatval)
  */
 public function getDisplayValue($pa_options = null)
 {
     if (!is_array($pa_options)) {
         $pa_options = array();
     }
     if (isset($pa_options['rawDate']) && $pa_options['rawDate']) {
         return array(0 => $this->opn_start_date, 1 => $this->opn_end_date, 'start' => $this->opn_start_date, 'end' => $this->opn_end_date);
     }
     if (isset($pa_options['GET_DIRECT_DATE']) && $pa_options['GET_DIRECT_DATE'] || isset($pa_options['getDirectDate']) && $pa_options['getDirectDate']) {
         return $this->opn_start_date;
     }
     if (isset($pa_options['sortable']) && $pa_options['sortable']) {
         if (!$this->opn_start_date || !$this->opn_end_date) {
             return null;
         }
         return $this->opn_start_date . '/' . $this->opn_end_date;
     }
     $o_config = Configuration::load();
     $o_date_config = Configuration::load($o_config->get('datetime_config'));
     if ($o_date_config->get('dateFormat') == 'original') {
         return $this->ops_text_value;
     } else {
         $t_element = new ca_metadata_elements($this->getElementID());
         $va_settings = $this->getSettingValuesFromElementArray($t_element->getFieldValuesArray(), array('isLifespan'));
         $o_tep = new TimeExpressionParser();
         $o_tep->setHistoricTimestamps($this->opn_start_date, $this->opn_end_date);
         return $o_tep->getText(array_merge(array('isLifespan' => $va_settings['isLifespan']), $pa_options));
         //$this->ops_text_value;
     }
 }
Пример #5
0
 public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
 {
     $vn_i = 0;
     $va_old_signs = $po_rewritten_query->getSigns();
     $va_terms = $va_signs = array();
     foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
         switch ($vs_class = get_class($o_lucene_query_element)) {
             case 'Zend_Search_Lucene_Search_Query_Term':
             case 'Zend_Search_Lucene_Search_Query_MultiTerm':
             case 'Zend_Search_Lucene_Search_Query_Phrase':
                 $vs_access_point = null;
                 if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
                     $va_raw_terms = 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;
                     }
                     $vs_term = join(" ", $va_raw_terms);
                 } else {
                     $vs_access_point = $o_lucene_query_element->getTerm()->field;
                     $vs_term = $o_lucene_query_element->getTerm()->text;
                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('"' . $vs_term . '"', $vs_access_point));
                 }
                 if ($vs_access_point) {
                     list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
                     if (in_array($vs_table, array('created', 'modified'))) {
                         if (!$this->opo_tep->parse($vs_term)) {
                             break;
                         }
                         $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;
                             }
                         }
                         switch ($vs_table) {
                             case 'created':
                                 if ($vn_user_id) {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
                                 } else {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
                                 }
                                 break;
                             case 'modified':
                                 if ($vn_user_id) {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
                                 } else {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
                                 }
                                 break;
                         }
                     } else {
                         if ($vs_table && $vs_field) {
                             $t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
                             if (!$t_table) {
                                 break;
                             }
                             $vs_fld_num = $this->opo_datamodel->getFieldNum($vs_table, $vs_field);
                             if (!$vs_fld_num) {
                                 // probably attribute
                                 $t_element = new ca_metadata_elements();
                                 if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
                                     //
                                     // 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_exact = $vs_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) {
                                                 if ($this->opo_tep->parse($vs_term)) {
                                                     // TODO: fix date handling to reflect distinctions in ranges
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
                                                 }
                                             } else {
                                                 if ($this->opo_tep->parse($vs_term)) {
                                                     // TODO: fix date handling to reflect distinctions in ranges
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
                                                 }
                                             }
                                             break;
                                         case 4:
                                             // geocode
                                             $t_geocode = new GeocodeAttributeValue();
                                             if ($va_coords = caParseGISSearch($vs_term)) {
                                                 $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_access_point));
                                             }
                                             break;
                                         case 6:
                                             // currency
                                             $t_cur = new CurrencyAttributeValue();
                                             $va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
                                             $vn_amount = (double) $va_parsed_value['value_decimal1'];
                                             $vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_access_point));
                                             break;
                                         case 8:
                                             // length
                                             $t_len = new LengthAttributeValue();
                                             $va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
                                             $vn_len = (double) $va_parsed_value['value_decimal1'];
                                             // this is always in meters so we can compare this value to the one in the database
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_access_point));
                                             break;
                                         case 9:
                                             // weight
                                             $t_weight = new WeightAttributeValue();
                                             $va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
                                             $vn_weight = (double) $va_parsed_value['value_decimal1'];
                                             // this is always in kilograms so we can compare this value to the one in the database
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_access_point));
                                             break;
                                         case 10:
                                             // timecode
                                             $t_timecode = new TimecodeAttributeValue();
                                             $va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
                                             $vn_timecode = (double) $va_parsed_value['value_decimal1'];
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_access_point));
                                             break;
                                         case 11:
                                             // integer
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
                                             break;
                                         case 12:
                                             // decimal
                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
                                             break;
                                     }
                                 }
                             }
                         }
                     }
                 }
                 break;
         }
         $va_terms[] = $o_lucene_query_element;
         $va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
         $vn_i++;
     }
     $o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
     $ps_search_expression = $this->_queryToString($o_rewritten_query);
     if ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters)) {
         $ps_search_expression = "({$ps_search_expression}) AND ({$vs_filter_query})";
     }
     $vo_http_client = new Zend_Http_Client();
     $vo_http_client->setUri($this->ops_elasticsearch_base_url . "/" . $this->ops_elasticsearch_index_name . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/" . "_search");
     if (preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):(\"[^\"]*\")!", $ps_search_expression, $va_matches) || preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):([^ ]*)!", $ps_search_expression, $va_matches)) {
         foreach ($va_matches[0] as $vn_i => $vs_element) {
             $vs_fld = $va_matches[1][$vn_i];
             if (!($vs_rel_type = trim($va_matches[2][$vn_i]))) {
                 continue;
             }
             $va_tmp = explode(".", $vs_fld);
             $vs_rel_table = caGetRelationshipTableName($pn_subject_tablenum, $va_tmp[0]);
             $va_rel_type_ids = $vs_rel_type && $vs_rel_table ? caMakeRelationshipTypeIDList($vs_rel_table, array($vs_rel_type)) : null;
             $va_new_elements = array();
             foreach ($va_rel_type_ids as $vn_rel_type_id) {
                 $va_new_elements[] = "(" . $va_matches[1][$vn_i] . "/" . $vn_rel_type_id . ":" . $va_matches[3][$vn_i] . ")";
             }
             $ps_search_expression = str_replace($vs_element, "(" . join(" OR ", $va_new_elements) . ")", $ps_search_expression);
         }
     }
     $ps_search_expression = str_replace("/", '\\/', $ps_search_expression);
     // escape forward slashes used to delimit relationship type qualifier
     Debug::msg("[ElasticSearch] Running query {$ps_search_expression}");
     $vo_http_client->setParameterGet(array('size' => intval($this->opa_options["limit"]), 'q' => $ps_search_expression, 'fields' => ''));
     $vo_http_response = $vo_http_client->request();
     $va_result = json_decode($vo_http_response->getBody(), true);
     return new WLPlugSearchEngineElasticSearchResult($va_result["hits"]["hits"], $pn_subject_tablenum);
 }
Пример #6
0
 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;
         }
     }
 }
Пример #7
0
 public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
 {
     $t = new Timer();
     $va_solr_search_filters = array();
     $vn_i = 0;
     $va_old_signs = $po_rewritten_query->getSigns();
     $va_terms = $va_signs = array();
     foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
         if (!$va_old_signs || !is_array($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';
         }
         switch ($vs_class = get_class($o_lucene_query_element)) {
             case 'Zend_Search_Lucene_Search_Query_Term':
             case 'Zend_Search_Lucene_Search_Query_MultiTerm':
             case 'Zend_Search_Lucene_Search_Query_Phrase':
                 if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
                     $va_raw_terms = 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;
                     }
                     $vs_term = join(" ", $va_raw_terms);
                 } else {
                     $vs_access_point = $o_lucene_query_element->getTerm()->field;
                     $vs_term = $o_lucene_query_element->getTerm()->text;
                 }
                 if ($vs_access_point) {
                     list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
                     if (in_array($vs_table, array('created', 'modified'))) {
                         if (!$this->opo_tep->parse($vs_term)) {
                             break;
                         }
                         $va_range = $this->opo_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':
                                 if ($vn_user_id) {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
                                 } else {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
                                 }
                                 break;
                             case 'modified':
                                 if ($vn_user_id) {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
                                 } else {
                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
                                 }
                                 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 = $vs_field;
                                 } else {
                                     $vs_fld_num = $this->opo_datamodel->getFieldNum($vs_table, $vs_field);
                                     if (!$vs_fld_num) {
                                         $t_element = new ca_metadata_elements();
                                         if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
                                             $vs_fld_num = $t_element->getPrimaryKey();
                                             //
                                             // 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_exact = $vs_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_term, 1);
                                                         if ($this->opo_tep->parse($vs_term)) {
                                                             $va_dates = $this->opo_tep->getHistoricTimestamps();
                                                             // TODO: fix date handling to reflect distinctions in ranges
                                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
                                                         }
                                                     } else {
                                                         if ($this->opo_tep->parse($vs_term)) {
                                                             $va_dates = $this->opo_tep->getHistoricTimestamps();
                                                             // TODO: fix date handling to reflect distinctions in ranges
                                                             $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
                                                         }
                                                     }
                                                     break;
                                                 case 4:
                                                     // geocode
                                                     $t_geocode = new GeocodeAttributeValue();
                                                     if ($va_coords = caParseGISSearch($vs_term)) {
                                                         $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_access_point));
                                                     }
                                                     break;
                                                 case 6:
                                                     // currency
                                                     $t_cur = new CurrencyAttributeValue();
                                                     $va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                     $vn_amount = (double) $va_parsed_value['value_decimal1'];
                                                     $vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_access_point));
                                                     break;
                                                 case 8:
                                                     // length
                                                     $t_len = new LengthAttributeValue();
                                                     $va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                     $vn_len = (double) $va_parsed_value['value_decimal1'];
                                                     // this is always in meters so we can compare this value to the one in the database
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_access_point));
                                                     break;
                                                 case 9:
                                                     // weight
                                                     $t_weight = new WeightAttributeValue();
                                                     $va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                     $vn_weight = (double) $va_parsed_value['value_decimal1'];
                                                     // this is always in kilograms so we can compare this value to the one in the database
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_access_point));
                                                     break;
                                                 case 10:
                                                     // timecode
                                                     $t_timecode = new TimecodeAttributeValue();
                                                     $va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
                                                     $vn_timecode = (double) $va_parsed_value['value_decimal1'];
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_access_point));
                                                     break;
                                                 case 11:
                                                     // integer
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
                                                     break;
                                                 case 12:
                                                     // decimal
                                                     $o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
                                                     break;
                                             }
                                         }
                                     }
                                 }
                             }
                         }
                     }
                 }
                 break;
         }
         $va_terms[] = $o_lucene_query_element;
         $va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
         $vn_i++;
     }
     $o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
     $ps_search_expression = $this->_queryToString($o_rewritten_query);
     if ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters)) {
         $ps_search_expression .= ' AND (' . $vs_filter_query . ')';
     }
     $vo_http_client = new Zend_Http_Client();
     $vo_http_client->setUri($this->opo_search_config->get('search_elasticsearch_base_url') . "/" . $this->opo_search_config->get('search_elasticsearch_index_name') . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/" . "_search");
     $vo_http_client->setParameterGet(array('size' => intval($this->opa_options["limit"]), 'q' => $ps_search_expression));
     $vo_http_response = $vo_http_client->request();
     $va_result = json_decode($vo_http_response->getBody(), true);
     return new WLPlugSearchEngineElasticSearchResult($va_result["hits"]["hits"], $pn_subject_tablenum);
 }
Пример #8
0
 /**
  * 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('&#47;', '/', $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;
 }