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