public function loadTypeSpecificValueFromRow($pa_value_array)
 {
     global $g_ui_locale;
     global $g_ui_units_pref;
     if ($pa_value_array['value_decimal1'] === '' || is_null($pa_value_array['value_decimal1'])) {
         $this->ops_text_value = '';
         return;
     }
     try {
         $vo_measurement = new Zend_Measure_Length((double) $pa_value_array['value_decimal1'], 'METER', $g_ui_locale);
         switch ($g_ui_units_pref) {
             case 'metric':
                 $this->ops_text_value = $vo_measurement->convertTo(Zend_Measure_Length::METER, 4);
                 break;
             case 'english':
                 $this->ops_text_value = $vo_measurement->convertTo(Zend_Measure_Length::FEET, 4);
                 break;
             case 'fractions':
                 $this->ops_text_value = caLengthToFractions($vo_measurement->convertTo(Zend_Measure_Length::INCH, 8), 16);
                 break;
             default:
                 // show value in unit entered, but adjusted for the UI locale
                 $this->ops_text_value = $vo_measurement->convertTo($pa_value_array['value_longtext2'], 4);
                 break;
         }
     } catch (Exception $e) {
         // derp
         $this->ops_text_value = $pa_value_array['value_longtext1'];
     }
     $this->opn_decimal_value = $pa_value_array['value_decimal1'];
 }
 public function loadTypeSpecificValueFromRow($pa_value_array)
 {
     global $g_ui_locale;
     global $g_ui_units_pref;
     if ($pa_value_array['value_decimal1'] === '') {
         $this->ops_text_value = '';
         return;
     }
     switch ($g_ui_units_pref) {
         case 'metric':
             $vo_measurement = new Zend_Measure_Length((double) $pa_value_array['value_decimal1'], 'METER', $g_ui_locale);
             $this->ops_text_value = $vo_measurement->convertTo(Zend_Measure_Length::METER, 4);
             break;
         case 'english':
             $vo_measurement = new Zend_Measure_Length((double) $pa_value_array['value_decimal1'], 'METER', $g_ui_locale);
             $this->ops_text_value = $vo_measurement->convertTo(Zend_Measure_Length::FEET, 4);
             break;
         default:
             // show as-is
             $this->ops_text_value = $pa_value_array['value_longtext1'];
             break;
     }
     $this->opn_decimal_value = $pa_value_array['value_decimal1'];
 }
Example #3
0
/**
 * 
 */
function caProcessBottomLineTemplate($po_request, $pa_placement, $pr_res, $pa_options = null)
{
    global $g_ui_units_pref, $g_ui_locale;
    if (!isset($pa_placement['settings']['bottom_line']) || !$pa_placement['settings']['bottom_line']) {
        return null;
    }
    if (!$pr_res) {
        return null;
    }
    $vs_template = $pa_placement['settings']['bottom_line'];
    $vs_bundle_name = $pa_placement['bundle_name'];
    $pn_page_start = caGetOption('pageStart', $pa_options, 0);
    $pn_page_end = caGetOption('pageEnd', $pa_options, $pr_res->numHits());
    if (($vn_current_index = $pr_res->currentIndex()) < 0) {
        $vn_current_index = 0;
    }
    $pr_res->seek(0);
    $o_dm = Datamodel::load();
    $va_tmp = explode(".", $vs_bundle_name);
    if (!($t_instance = $o_dm->getInstanceByTableName($va_tmp[0], true))) {
        return null;
    }
    if (!method_exists($t_instance, "_getElementDatatype") || is_null($vn_datatype = $t_instance->_getElementDatatype($va_tmp[1]))) {
        return null;
    }
    if (!($vs_user_currency = $po_request->user ? $po_request->user->getPreference('currency') : 'USD')) {
        $vs_user_currency = 'USD';
    }
    // Parse out tags and optional sub-elements from template
    //		we have to pull each sub-element separately
    //
    //		Ex. 	^SUM:valuation = sum of "valuation" sub-element
    //				^SUM = sum of primary value in non-container element
    if (!preg_match("!(\\^[A-Z]+[\\:]{0,1}[A-Za-z0-9\\_\\-]*)!", $vs_template, $va_tags)) {
        return $vs_template;
    }
    $va_tags_to_process = array();
    $va_subelements_to_process = array();
    if ($vn_datatype == 0) {
        // container
        foreach ($va_tags as $vs_raw_tag) {
            $va_tmp = explode(":", $vs_raw_tag);
            $vs_tag = $va_tmp[0];
            if (sizeof($va_tmp) == 2) {
                $vs_subelement = $va_tmp[1];
            } else {
                continue;
            }
            $va_tags_to_process[$vs_raw_tag] = true;
            $va_subelements_to_process["{$vs_bundle_name}.{$vs_subelement}"] = $t_instance->_getElementDatatype($vs_subelement);
        }
    } else {
        $va_tmp = explode(".", $vs_bundle_name);
        if (sizeof($va_tmp) == 2) {
            $vs_bundle_name .= "." . array_pop($va_tmp);
        }
        $va_subelements_to_process = array($vs_bundle_name => $vn_datatype);
    }
    $vn_c = 0;
    $vn_page_len = 0;
    $vb_has_timecode = false;
    $vn_min = $vn_max = null;
    $vn_page_min = $vn_page_max = null;
    $va_tag_values = array();
    while ($pr_res->nextHit()) {
        foreach ($va_subelements_to_process as $vs_subelement => $vn_subelement_datatype) {
            if (!is_array($va_tag_values[$vs_subelement])) {
                $va_tag_values[$vs_subelement]['SUM'] = 0;
                $va_tag_values[$vs_subelement]['PAGESUM'] = 0;
                $va_tag_values[$vs_subelement]['MIN'] = null;
                $va_tag_values[$vs_subelement]['PAGEMIN'] = null;
                $va_tag_values[$vs_subelement]['MAX'] = null;
                $va_tag_values[$vs_subelement]['PAGEMAX'] = null;
                $va_tag_values[$vs_subelement]['AVG'] = 0;
                $va_tag_values[$vs_subelement]['PAGEAVG'] = 0;
            }
            switch ($vn_subelement_datatype) {
                case 2:
                    // date range
                    $vs_value = $pr_res->get($vs_subelement);
                    break;
                case 6:
                    // currency
                    $va_values = $pr_res->get($vs_subelement, array('returnAsDecimalWithCurrencySpecifier' => true, 'returnAsArray' => true));
                    if (is_array($va_values)) {
                        foreach ($va_values as $vs_value) {
                            $vn_value = (double) caConvertCurrencyValue($vs_value, $vs_user_currency, array('numericValue' => true));
                            $va_tag_values[$vs_subelement]['SUM'] += $vn_value;
                            if (is_null($va_tag_values[$vs_subelement]['MIN']) || $vn_value < $va_tag_values[$vs_subelement]['MIN']) {
                                $va_tag_values[$vs_subelement]['MIN'] = $vn_value;
                            }
                            if (is_null($va_tag_values[$vs_subelement]['MAX']) || $vn_value > $va_tag_values[$vs_subelement]['MAX']) {
                                $va_tag_values[$vs_subelement]['MAX'] = $vn_value;
                            }
                            if ($vn_c >= $pn_page_start && $vn_c <= $pn_page_end) {
                                $va_tag_values[$vs_subelement]['PAGESUM'] += $vn_value;
                                if (is_null($va_tag_values[$vs_subelement]['PAGEMIN']) || $vn_value < $va_tag_values[$vs_subelement]['PAGEMIN']) {
                                    $va_tag_values[$vs_subelement]['PAGEMIN'] = $vn_value;
                                }
                                if (is_null($va_tag_values[$vs_subelement]['PAGEMAX']) || $vn_value > $va_tag_values[$vs_subelement]['PAGEMAX']) {
                                    $va_tag_values[$vs_subelement]['PAGEMAX'] = $vn_value;
                                }
                                $vn_page_len++;
                            }
                        }
                    }
                    break;
                case 8:
                    // length
                // length
                case 9:
                    // weight
                    $va_values = $pr_res->get($vs_subelement, array('returnAsDecimalMetric' => true, 'returnAsArray' => true));
                    if (is_array($va_values)) {
                        foreach ($va_values as $vs_value) {
                            $vn_value = (double) $vs_value;
                            $va_tag_values[$vs_subelement]['SUM'] += $vn_value;
                            if (is_null($va_tag_values[$vs_subelement]['MIN']) || $vn_value < $va_tag_values[$vs_subelement]['MIN']) {
                                $va_tag_values[$vs_subelement]['MIN'] = $vn_value;
                            }
                            if (is_null($va_tag_values[$vs_subelement]['MAX']) || $vn_value > $va_tag_values[$vs_subelement]['MAX']) {
                                $va_tag_values[$vs_subelement]['MAX'] = $vn_value;
                            }
                            if ($vn_c >= $pn_page_start && $vn_c <= $pn_page_end) {
                                $va_tag_values[$vs_subelement]['PAGESUM'] += $vn_value;
                                if (is_null($va_tag_values[$vs_subelement]['PAGEMIN']) || $vn_value < $va_tag_values[$vs_subelement]['PAGEMIN']) {
                                    $va_tag_values[$vs_subelement]['PAGEMIN'] = $vn_value;
                                }
                                if (is_null($va_tag_values[$vs_subelement]['PAGEMAX']) || $vn_value > $va_tag_values[$vs_subelement]['PAGEMAX']) {
                                    $va_tag_values[$vs_subelement]['PAGEMAX'] = $vn_value;
                                }
                                $vn_page_len++;
                            }
                        }
                    }
                    break;
                case 10:
                    // timecode
                    $va_values = $pr_res->get($vs_subelement, array('returnAsDecimal' => true, 'returnAsArray' => true));
                    if (is_array($va_values)) {
                        foreach ($va_values as $vn_value) {
                            $va_tag_values[$vs_subelement]['SUM'] += $vn_value;
                            if (is_null($vn_min) || $vn_value < $vn_min) {
                                $vn_min = $vn_value;
                            }
                            if (is_null($vn_max) || $vn_value > $vn_max) {
                                $vn_max = $vn_value;
                            }
                            if ($vn_c >= $pn_page_start && $vn_c <= $pn_page_end) {
                                $va_tag_values[$vs_subelement]['PAGESUM'] += $vn_value;
                                if (is_null($vn_page_min) || $vn_value < $vn_page_min) {
                                    $vn_page_min = $vn_value;
                                }
                                if (is_null($vn_page_max) || $vn_value > $vn_page_max) {
                                    $vn_page_max = $vn_value;
                                }
                                $vn_page_len++;
                            }
                        }
                    }
                    $vb_has_timecode = true;
                    break;
                case 11:
                    // integer
                // integer
                case 12:
                    // numeric (decimal)
                // numeric (decimal)
                default:
                    $va_values = $pr_res->get($vs_subelement, array('returnAsArray' => true));
                    if (is_array($va_values)) {
                        foreach ($va_values as $vs_value) {
                            $vn_value = (double) $vs_value;
                            $va_tag_values[$vs_subelement]['SUM'] += $vn_value;
                            if (is_null($vn_min) || $vn_value < $vn_min) {
                                $vn_min = $vn_value;
                            }
                            if (is_null($vn_max) || $vn_value > $vn_max) {
                                $vn_max = $vn_value;
                            }
                            if ($vn_c >= $pn_page_start && $vn_c <= $pn_page_end) {
                                $va_tag_values[$vs_subelement]['PAGESUM'] += $vn_value;
                                if (is_null($vn_page_min) || $vn_value < $vn_page_min) {
                                    $vn_page_min = $vn_value;
                                }
                                if (is_null($vn_page_max) || $vn_value > $vn_page_max) {
                                    $vn_page_max = $vn_value;
                                }
                                $vn_page_len++;
                            }
                        }
                    }
                    break;
                default:
                    break 2;
            }
        }
        $vn_c++;
    }
    if ($vb_has_timecode) {
        $o_tcp = new TimecodeParser();
        $o_config = Configuration::load();
        if (!($vs_timecode_format = $o_config->get('timecode_output_format'))) {
            $vs_timecode_format = 'HOURS_MINUTES_SECONDS';
        }
    }
    // Post processing
    foreach ($va_subelements_to_process as $vs_subelement => $vn_subelement_datatype) {
        switch ($vn_subelement_datatype) {
            case 6:
                // currency
                $va_tag_values[$vs_subelement]['PAGEAVG'] = $vn_page_len > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['PAGESUM'] / $vn_page_len) : 0;
                $va_tag_values[$vs_subelement]['AVG'] = $vn_c > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['SUM'] / $vn_c) : "0.00";
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    $va_tag_values[$vs_subelement][$vs_tag] = "{$vs_user_currency} " . $va_tag_values[$vs_subelement][$vs_tag];
                }
                break;
            case 8:
                // length
                $va_tag_values[$vs_subelement]['PAGEAVG'] = $vn_page_len > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['PAGESUM'] / $vn_page_len) : 0;
                $va_tag_values[$vs_subelement]['AVG'] = $vn_c > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['SUM'] / $vn_c) : "0.00";
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    $vo_measurement = new Zend_Measure_Length((double) $vn_val, 'METER', $g_ui_locale);
                    $va_tag_values[$vs_subelement][$vs_tag] = $vo_measurement->convertTo($g_ui_units_pref == 'metric' ? Zend_Measure_Length::METER : Zend_Measure_Length::FEET, 4);
                }
                break;
            case 9:
                // weight
                $va_tag_values[$vs_subelement]['PAGEAVG'] = $vn_page_len > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['PAGESUM'] / $vn_page_len) : 0;
                $va_tag_values[$vs_subelement]['AVG'] = $vn_c > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['SUM'] / $vn_c) : "0.00";
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    $vo_measurement = new Zend_Measure_Length((double) $vn_val, 'KILOGRAM', $g_ui_locale);
                    $va_tag_values[$vs_subelement][$vs_tag] = $vo_measurement->convertTo($g_ui_units_pref == 'metric' ? Zend_Measure_Weight::KILOGRAM : Zend_Measure_Weight::POUND, 4);
                }
                break;
            case 10:
                // timecode
                $va_tag_values[$vs_subelement]['PAGEAVG'] = $vn_page_len > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['PAGESUM'] / $vn_page_len) : 0;
                $va_tag_values[$vs_subelement]['AVG'] = $vn_c > 0 ? sprintf("%1.2f", $va_tag_values[$vs_subelement]['SUM'] / $vn_c) : 0;
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    if (!$vb_has_timecode) {
                        $va_tag_values[$vs_subelement][$vs_tag] = '';
                        continue;
                    }
                    $o_tcp->setParsedValueInSeconds($vn_val);
                    $va_tag_values[$vs_subelement][$vs_tag] = $o_tcp->getText($vs_timecode_format);
                }
                break;
            case 11:
                // integer
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    $va_tag_values[$vs_subelement][$vs_tag] = (int) $va_tag_values[$vs_subelement][$vs_tag];
                }
                break;
            case 12:
                // numeric (decimal)
                foreach ($va_tag_values[$vs_subelement] as $vs_tag => $vn_val) {
                    $va_tag_values[$vs_subelement][$vs_tag] = (double) $va_tag_values[$vs_subelement][$vs_tag];
                }
                break;
        }
    }
    // Restore current position of search result
    $pr_res->seek($vn_current_index);
    foreach ($va_tag_values as $vs_subelement => $va_tag_data) {
        foreach ($va_tag_data as $vs_tag => $vs_tag_value) {
            if ($vs_subelement == $vs_bundle_name) {
                $vs_template = str_replace("^{$vs_tag}", $vs_tag_value, $vs_template);
            } else {
                $va_tmp = explode(".", $vs_subelement);
                $vs_template = str_replace("^{$vs_tag}:" . array_pop($va_tmp), $vs_tag_value, $vs_template);
            }
        }
    }
    return $vs_template;
}
Example #4
0
 /**
  * Return list of items from the specified table that are related to the current browse set. This is the method that actually
  * pulls the facet content, regardless of whether the facet is cached yet or not. If you want to use the facet cache, call
  * BrowseEngine::getFacet()
  *
  * @see BrowseEngine::getFacet()
  * Options:
  *		checkAccess = array of access values to filter facets that have an 'access' field by
  *		checkAvailabilityOnly = if true then content is not actually fetch - only the availablility of content is verified
  *		user_id = If set item level access control is performed relative to specified user_id, otherwise defaults to logged in user
  */
 public function getFacetContent($ps_facet_name, $pa_options = null)
 {
     global $AUTH_CURRENT_USER_ID;
     $vs_browse_table_name = $this->ops_browse_table_name;
     $vs_browse_table_num = $this->opn_browse_table_num;
     $vn_user_id = isset($pa_options['user_id']) && (int) $pa_options['user_id'] ? (int) $pa_options['user_id'] : (int) $AUTH_CURRENT_USER_ID;
     $vb_show_if_no_acl = (bool) ($this->opo_config->get('default_item_access_level') > __CA_ACL_NO_ACCESS__);
     $t_user = new ca_users($vn_user_id);
     if (is_array($va_groups = $t_user->getUserGroups()) && sizeof($va_groups)) {
         $va_group_ids = array_keys($va_groups);
     } else {
         $va_group_ids = array();
     }
     if (!is_array($this->opa_browse_settings)) {
         return null;
     }
     if (!isset($this->opa_browse_settings['facets'][$ps_facet_name])) {
         return null;
     }
     if (!is_array($pa_options)) {
         $pa_options = array();
     }
     $vb_check_availability_only = isset($pa_options['checkAvailabilityOnly']) ? (bool) $pa_options['checkAvailabilityOnly'] : false;
     $va_all_criteria = $this->getCriteria();
     $va_criteria = $this->getCriteria($ps_facet_name);
     $va_facet_info = $this->opa_browse_settings['facets'][$ps_facet_name];
     $t_subject = $this->getSubjectInstance();
     if ($va_facet_info['relative_to']) {
         $vs_browse_table_name = $va_facet_info['relative_to'];
         $vs_browse_table_num = $this->opo_datamodel->getTableNum($vs_browse_table_name);
     }
     $vs_browse_type_limit_sql = '';
     if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) {
         // type restrictions
         $vs_browse_type_limit_sql = '(' . $t_subject->tableName() . '.' . $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_facet_info['type_restrictions'])) {
             // facet type restrictions bind a facet to specific types; we check them here
             $va_restrict_to_types = $this->_convertTypeCodesToIDs($va_facet_info['type_restrictions']);
             $vb_is_ok_to_browse = false;
             foreach ($va_browse_type_ids as $vn_type_id) {
                 if (in_array($vn_type_id, $va_restrict_to_types)) {
                     $vb_is_ok_to_browse = true;
                     break;
                 }
             }
             if (!$vb_is_ok_to_browse) {
                 return array();
             }
         }
     }
     // Values to exclude from list attributes and authorities; can be idnos or ids
     $va_exclude_values = caGetOption('exclude_values', $va_facet_info, array(), array('castTo' => 'array'));
     $va_results = $this->opo_ca_browse_cache->getResults();
     $vb_single_value_is_present = false;
     $vs_single_value = isset($va_facet_info['single_value']) ? $va_facet_info['single_value'] : null;
     $va_wheres = array();
     switch ($va_facet_info['type']) {
         # -----------------------------------------------------
         case 'has':
             $vn_state = null;
             if (isset($va_all_criteria[$ps_facet_name])) {
                 break;
             }
             // only one instance of this facet allowed per browse
             if (!($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true))) {
                 break;
             }
             $vs_yes_text = isset($va_facet_info['label_yes']) && $va_facet_info['label_yes'] ? $va_facet_info['label_yes'] : _t('Yes');
             $vs_no_text = isset($va_facet_info['label_no']) && $va_facet_info['label_no'] ? $va_facet_info['label_no'] : _t('No');
             $va_facet_values = array('yes' => array('id' => 1, 'label' => $vs_yes_text), 'no' => array('id' => 0, 'label' => $vs_no_text));
             // Actually check that both yes and no values will result in something
             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'];
                 $va_facet = array();
                 $va_counts = array();
                 foreach ($va_facet_values as $vs_state_name => $va_state_info) {
                     $va_wheres = array();
                     $va_joins = array();
                     if (!(bool) $va_state_info['id']) {
                         // no option
                         $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 {
                         // yes option
                         $va_joins[] = "LEFT JOIN ca_attributes AS caa ON  " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " = caa.row_id AND " . $t_item->tableNum() . " = caa.table_num";
                         $va_wheres[] = "caa.element_id = " . $t_element->getPrimaryKey();
                     }
                     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[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
                     }
                     if (sizeof($va_results)) {
                         $va_wheres[] = $vs_browse_table_name . "." . $t_item->primaryKey() . " IN (" . join(",", $va_results) . ")";
                     }
                     if ($va_facet_info['relative_to']) {
                         if ($t_subject->hasField('deleted')) {
                             $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                         }
                         if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                             $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                             $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                         }
                     }
                     if ($this->opo_config->get('perform_item_level_access_checking')) {
                         if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                             // Join to limit what browse table items are used to generate facet
                             $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                             $va_wheres[] = "(\n\t\t\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t\t\t)";
                         }
                     }
                     $vs_join_sql = join("\n", $va_joins);
                     $vs_where_sql = '';
                     if (sizeof($va_wheres) > 0) {
                         $vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres);
                     }
                     if ($vb_check_availability_only) {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\tLIMIT 2\n\t\t\t\t\t\t\t\t";
                         //print "$vs_sql<hr>";
                         $qr_res = $this->opo_db->query($vs_sql);
                         if ($qr_res->nextRow()) {
                             $va_counts[$vs_state_name] = (int) $qr_res->numRows();
                         }
                     } else {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT " . $vs_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t";
                         //print "$vs_sql<hr>";
                         $qr_res = $this->opo_db->query($vs_sql);
                         if ($qr_res->numRows() > 0) {
                             $va_facet[$vs_state_name] = $va_state_info;
                         } else {
                             return array();
                             // if either option in a "has" facet fails then don't show the facet
                         }
                     }
                 }
             } else {
                 $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);
                 switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_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_init = array();
                 foreach ($va_path as $vs_join_table) {
                     $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
                     $va_joins_init[] = ($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_facet = array();
                 $va_counts = array();
                 foreach ($va_facet_values as $vs_state_name => $va_state_info) {
                     $va_wheres = array();
                     $va_joins = $va_joins_init;
                     if (!(bool) $va_state_info['id']) {
                         // 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() . ".access IS NULL))";
                         }
                         if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) {
                             $va_wheres[] = "((" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_restrict_to_relationship_types) . ")) OR (" . $t_item_rel->tableName() . ".type_id IS NULL))";
                         }
                         if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) {
                             $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_exclude_relationship_types) . "))";
                         }
                     } 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 (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 ($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[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
                     }
                     if (sizeof($va_results)) {
                         $va_wheres[] = $vs_browse_table_name . "." . $t_item->primaryKey() . " IN (" . join(",", $va_results) . ")";
                     }
                     if ($va_facet_info['relative_to']) {
                         if ($t_subject->hasField('deleted')) {
                             $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                         }
                         if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                             $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                             $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                         }
                     }
                     if ($this->opo_config->get('perform_item_level_access_checking')) {
                         if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                             // Join to limit what browse table items are used to generate facet
                             $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                             $va_wheres[] = "(\n\t\t\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t\t\t)";
                         }
                     }
                     $vs_join_sql = join("\n", $va_joins);
                     $vs_where_sql = '';
                     if (sizeof($va_wheres) > 0) {
                         $vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres);
                     }
                     if ($vb_check_availability_only) {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\tLIMIT 2\n\t\t\t\t\t\t\t\t";
                         //print "$vs_sql<hr>";
                         $qr_res = $this->opo_db->query($vs_sql);
                         if ($qr_res->nextRow()) {
                             $va_counts[$vs_state_name] = (int) $qr_res->numRows();
                         }
                     } else {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT " . $vs_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t";
                         //print "$vs_sql<hr>";
                         $qr_res = $this->opo_db->query($vs_sql);
                         if ($qr_res->numRows() > 0) {
                             $va_facet[$vs_state_name] = $va_state_info;
                         } else {
                             return array();
                             // if either option in a "has" facet fails then don't show the facet
                         }
                     }
                 }
             }
             if ($vb_check_availability_only) {
                 return sizeof($va_counts) > 1 ? true : false;
             }
             return $va_facet;
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'label':
             if (!($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true))) {
                 break;
             }
             if (!($t_label = $t_item->getLabelTableInstance())) {
                 break;
             }
             if (!is_array($va_restrict_to_types = $va_facet_info['restrict_to_types'])) {
                 $va_restrict_to_types = array();
             }
             $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();
             $vs_label_sort_field = $t_item->getLabelSortField();
             $vs_where_sql = $vs_join_sql = '';
             $vb_needs_join = false;
             $va_where_sql = array();
             $va_joins = array();
             if ($vs_browse_type_limit_sql) {
                 $va_where_sql[] = $vs_browse_type_limit_sql;
             }
             if (isset($va_facet_info['preferred_labels_only']) && $va_facet_info['preferred_labels_only'] && $t_label->hasField('is_preferred')) {
                 $va_where_sql[] = "l.is_preferred = 1";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_where_sql[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($t_item->hasField('deleted')) {
                 $va_where_sql[] = "(" . $vs_browse_table_name . ".deleted = 0)";
                 $vb_needs_join = true;
             }
             if (sizeof($va_restrict_to_types)) {
                 $va_restrict_to_type_ids = caMakeTypeIDList($vs_browse_table_name, $va_restrict_to_types, array('dont_include_subtypes_in_type_restriction' => true));
                 if (sizeof($va_restrict_to_type_ids)) {
                     $va_where_sql[] = "(" . $vs_browse_table_name . "." . $t_item->getTypeFieldName() . " IN (" . join(", ", $va_restrict_to_type_ids) . ")" . ($t_item->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $vs_browse_table_name . '.' . $t_item->getTypeFieldName() . " IS NULL)" : '') . ")";
                     $vb_needs_join = true;
                 }
             }
             if (sizeof($va_exclude_types)) {
                 $va_exclude_type_ids = caMakeTypeIDList($vs_browse_table_name, $va_exclude_types, array('dont_include_subtypes_in_type_restriction' => true));
                 if (sizeof($va_exclude_type_ids)) {
                     $va_where_sql[] = "(" . $vs_browse_table_name . "." . $t_item->getTypeFieldName() . " NOT IN (" . join(", ", $va_exclude_type_ids) . ")" . ($t_item->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $vs_browse_table_name . '.' . $t_item->getTypeFieldName() . " IS NULL)" : '') . ")";
                     $vb_needs_join = true;
                 }
             }
             if ($vb_needs_join) {
                 $va_joins[] = "INNER JOIN " . $vs_browse_table_name . " ON " . $vs_browse_table_name . "." . $t_item->primaryKey() . " = l." . $t_item->primaryKey();
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_where_sql[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_where_sql = array_merge($va_where_sql, $va_relative_sql_data['wheres']);
                 }
             }
             if (sizeof($va_results)) {
                 if ($va_facet_info['relative_to']) {
                     $va_where_sql[] = $this->ops_browse_table_name . "." . $t_subject->primaryKey() . " IN (" . join(",", $va_results) . ")";
                 } else {
                     $va_where_sql[] = "l.{$vs_item_pk} IN (" . join(",", $va_results) . ")";
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_where_sql[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (sizeof($va_where_sql)) {
                 $vs_where_sql = "WHERE " . join(" AND ", $va_where_sql);
             }
             if ($vb_check_availability_only) {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM {$vs_label_table_name} l\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\tLIMIT 1\n\t\t\t\t\t\t";
                 $qr_res = $this->opo_db->query($vs_sql);
                 return (int) $qr_res->numRows() > 0 ? true : false;
             } else {
                 $vs_parent_fld = $t_item->getProperty('HIERARCHY_PARENT_ID_FLD');
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT  l.* " . ($vs_parent_fld ? ", " . $vs_browse_table_name . "." . $vs_parent_fld : '') . " \n\t\t\t\t\t\t\tFROM {$vs_label_table_name} l\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t";
                 $qr_res = $this->opo_db->query($vs_sql);
                 $va_values = array();
                 $va_child_counts = array();
                 $vn_parent_id = null;
                 while ($qr_res->nextRow()) {
                     $vn_id = $qr_res->get($t_item->primaryKey());
                     if ($vs_parent_fld) {
                         $vn_parent_id = $qr_res->get($vs_parent_fld);
                         if ($vn_parent_id) {
                             $va_child_counts[$vn_parent_id]++;
                         }
                     }
                     $va_values[$vn_id][$qr_res->get('locale_id')] = array_merge($qr_res->getRow(), array('id' => $vn_id, 'parent_id' => $vn_parent_id, 'label' => $qr_res->get($vs_label_display_field)));
                     if (!is_null($vs_single_value) && $vn_id == $vs_single_value) {
                         $vb_single_value_is_present = true;
                     }
                 }
                 if ($vs_parent_fld) {
                     foreach ($va_values as $vn_id => $va_values_by_locale) {
                         foreach ($va_values_by_locale as $vn_locale_id => $va_value) {
                             $va_values[$vn_id][$vn_locale_id]['child_count'] = (int) $va_child_counts[$vn_id];
                         }
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 $va_values = caExtractValuesByUserLocale($va_values);
                 return $va_values;
             }
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'attribute':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $t_element = new ca_metadata_elements();
             if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) {
                 return array();
             }
             $vn_element_type = $t_element->get('datatype');
             $vn_element_id = $t_element->getPrimaryKey();
             $va_joins = array('INNER JOIN ca_attribute_values ON ca_attributes.attribute_id = ca_attribute_values.attribute_id', 'INNER JOIN ' . $vs_browse_table_name . ' ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_attributes.row_id AND ca_attributes.table_num = ' . intval($vs_browse_table_num));
             $va_wheres = array();
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = ' AND (' . $vs_where_sql . ')';
             }
             if ($vb_check_availability_only) {
                 // exclude criteria values
                 $vs_criteria_exclude_sql = '';
                 if (is_array($va_criteria) && sizeof($va_criteria)) {
                     $vs_criteria_exclude_sql = ' AND (ca_attribute_values.value_longtext1 NOT IN (' . join(", ", caQuoteList(array_keys($va_criteria))) . ')) ';
                 }
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t(ca_attribute_values.element_id = ?) {$vs_criteria_exclude_sql} {$vs_where_sql}\n\t\t\t\t\t\t\tLIMIT 2";
                 //print $vs_sql;
                 $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                 return (int) $qr_res->numRows() > 1 ? true : false;
             } else {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT value_longtext1, value_decimal1, value_longtext2, value_integer1\n\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? {$vs_where_sql}";
                 $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                 $va_values = array();
                 $va_list_items = null;
                 $va_suppress_values = null;
                 if ($va_facet_info['suppress'] && !is_array($va_facet_info['suppress'])) {
                     $va_facet_info['suppress'] = array($va_facet_info['suppress']);
                 }
                 if (!is_array($va_suppress_values = caGetOption('suppress', $va_facet_info, null))) {
                     $va_suppress_values = caGetOption('exclude_values', $va_facet_info, null);
                 }
                 switch ($vn_element_type) {
                     case __CA_ATTRIBUTE_VALUE_LIST__:
                         $va_values = $qr_res->getAllFieldValues('value_longtext1');
                         $qr_res->seek(0);
                         $t_list_item = new ca_list_items();
                         $va_list_item_cache = $t_list_item->getFieldValuesForIDs($va_values, array('idno', 'item_value', 'parent_id', 'access'));
                         $va_list_child_count_cache = array();
                         if (is_array($va_list_item_cache)) {
                             foreach ($va_list_item_cache as $vn_id => $va_item) {
                                 if (!($vn_parent_id = $va_item['parent_id'])) {
                                     continue;
                                 }
                                 if (is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && !in_array($va_item['access'], $pa_options['checkAccess'])) {
                                     continue;
                                 }
                                 $va_list_child_count_cache[$vn_parent_id]++;
                             }
                         }
                         $va_list_label_cache = $t_list_item->getPreferredDisplayLabelsForIDs($va_values);
                         // Translate value idnos to ids
                         if (is_array($va_suppress_values)) {
                             $va_suppress_values = ca_lists::getItemIDsFromList($t_element->get('list_id'), $va_suppress_values);
                         }
                         $va_facet_list = array();
                         foreach ($va_values as $vn_val) {
                             if (!$vn_val) {
                                 continue;
                             }
                             if (is_array($va_suppress_values) && in_array($vn_val, $va_suppress_values)) {
                                 continue;
                             }
                             if (is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && !in_array($va_item['access'], $pa_options['checkAccess'])) {
                                 continue;
                             }
                             if ($va_criteria[$vn_val]) {
                                 continue;
                             }
                             // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                             $vn_child_count = isset($va_list_child_count_cache[$vn_val]) ? $va_list_child_count_cache[$vn_val] : 0;
                             $va_facet_list[$vn_val] = array('id' => $vn_val, 'label' => html_entity_decode($va_list_label_cache[$vn_val]), 'parent_id' => isset($va_list_item_cache[$vn_val]['parent_id']) ? $va_list_item_cache[$vn_val]['parent_id'] : null, 'child_count' => $vn_child_count);
                         }
                         // preserve order of list
                         $va_values_sorted_by_list_order = array();
                         if (is_array($va_list_item_cache)) {
                             foreach ($va_list_item_cache as $vn_item_id => $va_item) {
                                 if (isset($va_facet_list[$vn_item_id])) {
                                     $va_values_sorted_by_list_order[$vn_item_id] = $va_facet_list[$vn_item_id];
                                 }
                             }
                         }
                         return caSortArrayByKeyInValue($va_values_sorted_by_list_order, array('label'));
                         break;
                     case __CA_ATTRIBUTE_VALUE_OBJECTS__:
                     case __CA_ATTRIBUTE_VALUE_ENTITIES__:
                     case __CA_ATTRIBUTE_VALUE_PLACES__:
                     case __CA_ATTRIBUTE_VALUE_OCCURRENCES__:
                     case __CA_ATTRIBUTE_VALUE_COLLECTIONS__:
                     case __CA_ATTRIBUTE_VALUE_LOANS__:
                     case __CA_ATTRIBUTE_VALUE_MOVEMENTS__:
                     case __CA_ATTRIBUTE_VALUE_STORAGELOCATIONS__:
                     case __CA_ATTRIBUTE_VALUE_OBJECTLOTS__:
                         if ($t_rel_item = AuthorityAttributeValue::elementTypeToInstance($vn_element_type)) {
                             $va_ids = $qr_res->getAllFieldValues('value_integer1');
                             $va_auth_items = $t_rel_item->getPreferredDisplayLabelsForIDs($va_ids);
                             $qr_res->seek(0);
                         }
                         break;
                     default:
                         if (isset($va_facet_info['suppress']) && is_array($va_facet_info['suppress'])) {
                             $va_suppress_values = $va_facet_info['suppress'];
                         }
                         break;
                 }
                 while ($qr_res->nextRow()) {
                     $o_attr = Attribute::getValueInstance($vn_element_type, $qr_res->getRow(), true);
                     if (!($vs_val = trim($o_attr->getDisplayValue()))) {
                         continue;
                     }
                     if (is_array($va_suppress_values) && in_array($vs_val, $va_suppress_values)) {
                         continue;
                     }
                     if ($va_criteria[$vs_val]) {
                         continue;
                     }
                     // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                     switch ($vn_element_type) {
                         case __CA_ATTRIBUTE_VALUE_LIST__:
                             $vn_child_count = 0;
                             if ($va_list_parent_ids[$vs_val]) {
                                 $vn_child_count++;
                             }
                             $va_values[$vs_val] = array('id' => str_replace('/', '&#47;', $vs_val), 'label' => html_entity_decode($va_list_items[$vs_val]['name_plural'] ? $va_list_items[$vs_val]['name_plural'] : $va_list_items[$vs_val]['item_value']), 'parent_id' => $va_list_items[$vs_val]['parent_id'], 'child_count' => $vn_child_count);
                             break;
                         case __CA_ATTRIBUTE_VALUE_OBJECTS__:
                         case __CA_ATTRIBUTE_VALUE_ENTITIES__:
                         case __CA_ATTRIBUTE_VALUE_PLACES__:
                         case __CA_ATTRIBUTE_VALUE_OCCURRENCES__:
                         case __CA_ATTRIBUTE_VALUE_COLLECTIONS__:
                         case __CA_ATTRIBUTE_VALUE_LOANS__:
                         case __CA_ATTRIBUTE_VALUE_MOVEMENTS__:
                         case __CA_ATTRIBUTE_VALUE_STORAGELOCATIONS__:
                         case __CA_ATTRIBUTE_VALUE_OBJECTLOTS__:
                             $va_values[$vs_val] = array('id' => $vn_id, 'label' => html_entity_decode($va_auth_items[$vn_id] ? $va_auth_items[$vn_id] : $vs_val));
                             break;
                         case __CA_ATTRIBUTE_VALUE_CURRENCY__:
                             $va_values[sprintf("%014.2f", preg_replace("![\\D]+!", "", $vs_val))] = array('id' => str_replace('/', '&#47;', $vs_val), 'label' => $vs_val);
                             break;
                         default:
                             $va_values[$vs_val] = array('id' => str_replace('/', '&#47;', $vs_val), 'label' => $vs_val);
                             break;
                     }
                     if (!is_null($vs_single_value) && $vs_val == $vs_single_value) {
                         $vb_single_value_is_present = true;
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 ksort($va_values);
                 return $va_values;
             }
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'location':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $vs_sort_field = null;
             if ($t_item->getProperty('ID_NUMBERING_ID_FIELD') == $vs_field_name) {
                 $vs_sort_field = $t_item->getProperty('ID_NUMBERING_SORT_FIELD');
             }
             $va_joins = array();
             $va_wheres = array();
             $vs_where_sql = '';
             $va_wheres[] = "({$vs_browse_table_name}.current_loc_class IS NOT NULL)";
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = '(' . $vs_where_sql . ')';
             }
             if ($vb_check_availability_only) {
                 if (sizeof($va_criteria) > 0) {
                     return false;
                 }
                 // only one current location criteria allowed
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\tLIMIT 2";
                 $qr_res = $this->opo_db->query($vs_sql);
                 if ($qr_res->nextRow()) {
                     return (int) $qr_res->numRows() > 0 ? true : false;
                 }
                 return false;
             } else {
                 if (sizeof($va_criteria) > 0) {
                     return array();
                 }
                 // only one current location criteria allowed
                 $vs_pk = $t_item->primaryKey();
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT {$vs_browse_table_name}.current_loc_class, {$vs_browse_table_name}.current_loc_subclass, {$vs_browse_table_name}.current_loc_id\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}";
                 if ($vs_sort_field) {
                     $vs_sql .= " ORDER BY {$vs_sort_field}";
                 }
                 $qr_res = $this->opo_db->query($vs_sql);
                 $va_collapse_map = $this->getCollapseMapForLocationFacet($va_facet_info);
                 $va_values = $va_values_by_table = array();
                 while ($qr_res->nextRow()) {
                     if (!($vs_loc_class = trim($qr_res->get('current_loc_class')))) {
                         continue;
                     }
                     if (!($vs_loc_subclass = trim($qr_res->get('current_loc_subclass')))) {
                         continue;
                     }
                     if (!($vs_loc_id = trim($qr_res->get('current_loc_id')))) {
                         continue;
                     }
                     $vs_val = "{$vs_loc_class}:{$vs_loc_subclass}:{$vs_loc_id}";
                     if ($va_criteria[$vs_val]) {
                         continue;
                     }
                     // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                     $va_values_by_table[$vs_loc_class][$vs_loc_subclass][$vs_loc_id] = true;
                 }
                 foreach ($va_values_by_table as $vs_loc_class => $va_loc_id_by_subclass) {
                     foreach ($va_loc_id_by_subclass as $vs_loc_subclass => $va_loc_ids) {
                         if (sizeof($va_tmp = array_keys($va_loc_ids))) {
                             $vs_loc_table_name = $this->opo_datamodel->getTableName($vs_loc_class);
                             if (($vs_table_name = $vs_loc_table_name) == 'ca_objects_x_storage_locations') {
                                 $vs_table_name = 'ca_storage_locations';
                             }
                             $qr_res = caMakeSearchResult($vs_table_name, $va_tmp);
                             if (isset($va_collapse_map[$vs_table_name]) && isset($va_collapse_map[$vs_table_name]['*']) && $va_collapse_map[$vs_table_name]['*']) {
                                 $va_values[$vs_id = "{$vs_loc_class}"] = array('id' => $vs_id, 'label' => $va_collapse_map[$vs_table_name]['*']);
                                 continue;
                             }
                             while ($qr_res->nextHit()) {
                                 $vn_id = $qr_res->getPrimaryKey();
                                 $va_config = ca_objects::getConfigurationForCurrentLocationType($vs_table_name, $vs_loc_subclass, array('facet' => isset($va_facet_info['display']) ? $va_facet_info['display'] : null));
                                 $vs_template = isset($va_config['template']) ? $va_config['template'] : "^{$vs_table_name}.preferred_labels";
                                 if (isset($va_collapse_map[$vs_table_name]) && isset($va_collapse_map[$vs_table_name][$vs_loc_subclass]) && $va_collapse_map[$vs_table_name][$vs_loc_subclass]) {
                                     $va_values[$vs_id = "{$vs_loc_class}:{$vs_loc_subclass}"] = array('id' => $vs_id, 'label' => $va_collapse_map[$vs_table_name][$vs_loc_subclass]);
                                     continue;
                                 }
                                 $va_values[$vs_id = "{$vs_loc_class}:{$vs_loc_subclass}:{$vn_id}"] = array('id' => $vs_id, 'label' => $qr_res->getWithTemplate($vs_template));
                             }
                         }
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 return caSortArrayByKeyInValue($va_values, array('label'));
             }
             return array();
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'fieldList':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $vs_field_name = $va_facet_info['field'];
             $va_field_info = $t_item->getFieldInfo($vs_field_name);
             $t_list = new ca_lists();
             $t_list_item = new ca_list_items();
             $va_joins = array();
             $va_wheres = array();
             $vs_where_sql = '';
             if (isset($va_field_info['LIST_CODE']) && ($vs_list_name = $va_field_info['LIST_CODE'])) {
                 // Handle fields containing ca_list_item.item_id's
                 $va_joins = array('INNER JOIN ' . $vs_browse_table_name . ' ON ' . $vs_browse_table_name . '.' . $vs_field_name . ' = li.item_id', 'INNER JOIN ca_lists ON ca_lists.list_id = li.list_id');
                 if (sizeof($va_results) && $this->numCriteria() > 0) {
                     $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
                 }
                 if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                     $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
                     $va_wheres[] = "(li.access IN (" . join(',', $pa_options['checkAccess']) . "))";
                 }
                 if ($vs_browse_type_limit_sql) {
                     $va_wheres[] = $vs_browse_type_limit_sql;
                 }
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_facet_info['relative_to']) {
                     if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                         $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                         $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                     }
                 }
                 if (is_array($va_criteria) && sizeof($va_criteria)) {
                     $va_wheres[] = "(li.item_id NOT IN (" . join(",", array_keys($va_criteria)) . "))";
                 }
                 if ($this->opo_config->get('perform_item_level_access_checking')) {
                     if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                         // Join to limit what browse table items are used to generate facet
                         $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                         $va_wheres[] = "(\n\t\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t\t)";
                     }
                 }
                 $vs_join_sql = join("\n", $va_joins);
                 if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                     $vs_where_sql = ' AND (' . $vs_where_sql . ')';
                 }
                 if ($vb_check_availability_only) {
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\tFROM ca_list_items li\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tca_lists.list_code = ? {$vs_where_sql}\n\t\t\t\t\t\t\t\tLIMIT 2";
                     $qr_res = $this->opo_db->query($vs_sql, $vs_list_name);
                     return (int) $qr_res->numRows() > 1 ? true : false;
                 } else {
                     // Get label ordering fields
                     $va_ordering_fields_to_fetch = isset($va_facet_info['order_by_label_fields']) && is_array($va_facet_info['order_by_label_fields']) ? $va_facet_info['order_by_label_fields'] : array();
                     $va_orderbys = array();
                     $t_rel_item_label = new ca_list_item_labels();
                     foreach ($va_ordering_fields_to_fetch as $vs_sort_by_field) {
                         if (!$t_rel_item_label->hasField($vs_sort_by_field)) {
                             continue;
                         }
                         $va_orderbys[] = $va_label_selects[] = 'lil.' . $vs_sort_by_field;
                     }
                     $vs_order_by = sizeof($va_orderbys) ? "ORDER BY " . join(', ', $va_orderbys) : '';
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT DISTINCT lil.item_id, lil.name_singular, lil.name_plural, lil.locale_id\n\t\t\t\t\t\t\t\tFROM ca_list_items li\n\t\t\t\t\t\t\t\tINNER JOIN ca_list_item_labels AS lil ON lil.item_id = li.item_id\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tca_lists.list_code = ?  AND lil.is_preferred = 1 {$vs_where_sql} {$vs_order_by}";
                     //print $vs_sql." [$vs_list_name]";
                     $qr_res = $this->opo_db->query($vs_sql, $vs_list_name);
                     $va_values = array();
                     while ($qr_res->nextRow()) {
                         $vn_id = $qr_res->get('item_id');
                         if ($va_criteria[$vn_id]) {
                             continue;
                         }
                         // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                         $va_values[$vn_id][$qr_res->get('locale_id')] = array('id' => $vn_id, 'label' => $qr_res->get('name_plural'));
                         if (!is_null($vs_single_value) && $vn_id == $vs_single_value) {
                             $vb_single_value_is_present = true;
                         }
                     }
                     if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                         return array();
                     }
                     return caExtractValuesByUserLocale($va_values);
                 }
             } else {
                 if ($vs_list_name = $va_field_info['LIST']) {
                     $va_list_items_by_value = array();
                     // fields with values set according to ca_list_items (not a foreign key ref)
                     if ($va_list_items = caExtractValuesByUserLocale($t_list->getItemsForList($vs_list_name))) {
                         foreach ($va_list_items as $vn_id => $va_list_item) {
                             $va_list_items_by_value[$va_list_item['item_value']] = $va_list_item['name_plural'];
                         }
                     } else {
                         foreach ($va_field_info['BOUNDS_CHOICE_LIST'] as $vs_val => $vn_id) {
                             $va_list_items_by_value[$vn_id] = $vs_val;
                         }
                     }
                     if (sizeof($va_results) && $this->numCriteria() > 0) {
                         $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
                     }
                     if ($vs_browse_type_limit_sql) {
                         $va_wheres[] = $vs_browse_type_limit_sql;
                     }
                     if ($t_subject->hasField('deleted')) {
                         $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                     }
                     if ($va_facet_info['relative_to']) {
                         if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                             $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                             $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                         }
                     }
                     if ($this->opo_config->get('perform_item_level_access_checking')) {
                         if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                             // Join to limit what browse table items are used to generate facet
                             $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                             $va_wheres[] = "(\n\t\t\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t\t\t)";
                         }
                     }
                     if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                         $vs_where_sql = '(' . $vs_where_sql . ')';
                     }
                     $vs_join_sql = join("\n", $va_joins);
                     if ($vb_check_availability_only) {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t" . ($vs_where_sql ? 'WHERE' : '') . "\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\tLIMIT 2";
                         $qr_res = $this->opo_db->query($vs_sql);
                         return (int) $qr_res->numRows() > 1 ? true : false;
                     } else {
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT DISTINCT " . $vs_browse_table_name . '.' . $vs_field_name . "\n\t\t\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t" . ($vs_where_sql ? 'WHERE' : '') . "\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
                         //print $vs_sql." [$vs_list_name]";
                         $qr_res = $this->opo_db->query($vs_sql);
                         $va_values = array();
                         while ($qr_res->nextRow()) {
                             $vn_id = $qr_res->get($vs_field_name);
                             if ($va_criteria[$vn_id]) {
                                 continue;
                             }
                             // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                             if (isset($va_list_items_by_value[$vn_id])) {
                                 $va_values[$vn_id] = array('id' => $vn_id, 'label' => $va_list_items_by_value[$vn_id]);
                                 if (!is_null($vs_single_value) && $vn_id == $vs_single_value) {
                                     $vb_single_value_is_present = true;
                                 }
                             }
                         }
                         if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                             return array();
                         }
                         return $va_values;
                     }
                 } else {
                     if ($t_browse_table = $this->opo_datamodel->getInstanceByTableName($vs_facet_table = $va_facet_info['table'], true)) {
                         // Handle fields containing ca_list_item.item_id's
                         $va_joins = array('INNER JOIN ' . $vs_browse_table_name . ' ON ' . $vs_browse_table_name . '.' . $vs_field_name . ' = ' . $vs_facet_table . '.' . $t_browse_table->primaryKey());
                         $vs_display_field_name = null;
                         if (method_exists($t_browse_table, 'getLabelTableInstance')) {
                             $t_label_instance = $t_browse_table->getLabelTableInstance();
                             $vs_display_field_name = isset($va_facet_info['display']) && $va_facet_info['display'] ? $va_facet_info['display'] : $t_label_instance->getDisplayField();
                             $va_joins[] = 'INNER JOIN ' . $t_label_instance->tableName() . " AS lab ON lab." . $t_browse_table->primaryKey() . ' = ' . $t_browse_table->tableName() . '.' . $t_browse_table->primaryKey();
                         }
                         if (sizeof($va_results) && $this->numCriteria() > 0) {
                             $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
                         }
                         if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                             $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
                         }
                         if ($vs_browse_type_limit_sql) {
                             $va_wheres[] = $vs_browse_type_limit_sql;
                         }
                         if ($va_facet_info['relative_to']) {
                             if ($t_subject->hasField('deleted')) {
                                 $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                             }
                             if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                                 $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                                 $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                             }
                         }
                         if ($this->opo_config->get('perform_item_level_access_checking')) {
                             if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                                 // Join to limit what browse table items are used to generate facet
                                 $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                                 $va_wheres[] = "(\n\t\t\t\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\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(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t\t\t\t)";
                             }
                         }
                         $vs_join_sql = join("\n", $va_joins);
                         if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                             $vs_where_sql = 'WHERE (' . $vs_where_sql . ')';
                         }
                         if ($vb_check_availability_only) {
                             $vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\t\tFROM {$vs_facet_table}\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\t\tLIMIT 1";
                             $qr_res = $this->opo_db->query($vs_sql);
                             return (int) $qr_res->numRows() > 0 ? true : false;
                         } else {
                             $vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT DISTINCT *\n\t\t\t\t\t\t\t\t\t\tFROM {$vs_facet_table}\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}";
                             //print $vs_sql;
                             $qr_res = $this->opo_db->query($vs_sql);
                             $va_values = array();
                             $vs_pk = $t_browse_table->primaryKey();
                             while ($qr_res->nextRow()) {
                                 $vn_id = $qr_res->get($vs_pk);
                                 if ($va_criteria[$vn_id]) {
                                     continue;
                                 }
                                 // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                                 $va_values[$vn_id][$qr_res->get('locale_id')] = array('id' => $vn_id, 'label' => $qr_res->get($vs_display_field_name));
                                 if (!is_null($vs_single_value) && $vn_id == $vs_single_value) {
                                     $vb_single_value_is_present = true;
                                 }
                             }
                             if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                                 return array();
                             }
                             return caExtractValuesByUserLocale($va_values);
                         }
                     }
                 }
             }
             return array();
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'field':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             if (!is_array($va_restrict_to_types = $va_facet_info['restrict_to_types'])) {
                 $va_restrict_to_types = array();
             }
             if (!is_array($va_restrict_to_types = $this->_convertTypeCodesToIDs($va_restrict_to_types, array('instance' => $t_item, 'dontExpandHierarchically' => true)))) {
                 $va_restrict_to_types = array();
             }
             $va_restrict_to_types_expanded = $this->_convertTypeCodesToIDs($va_restrict_to_types, array('instance' => $t_item));
             $vs_field_name = $va_facet_info['field'];
             $va_field_info = $t_item->getFieldInfo($vs_field_name);
             $vs_sort_field = null;
             if ($t_item->getProperty('ID_NUMBERING_ID_FIELD') == $vs_field_name) {
                 $vs_sort_field = $vs_browse_table_name . '.' . $t_item->getProperty('ID_NUMBERING_SORT_FIELD');
             }
             $t_list = new ca_lists();
             $t_list_item = new ca_list_items();
             $va_joins = array();
             $va_wheres = array();
             $vs_where_sql = '';
             $va_facet_values = null;
             if ($vb_is_bit = $va_field_info['FIELD_TYPE'] == FT_BIT) {
                 $vs_yes_text = caGetOption('label_yes', $va_facet_info, _t('Yes'));
                 $vs_no_text = caGetOption('label_no', $va_facet_info, _t('No'));
                 $va_facet_values = array(1 => array('id' => 1, 'label' => $vs_yes_text), 0 => array('id' => 0, 'label' => $vs_no_text));
             }
             if (is_array($va_restrict_to_types) && sizeof($va_restrict_to_types) > 0 && method_exists($t_rel_item, "getTypeList")) {
                 $va_wheres[] = "{$va_restrict_to_types_expanded}.type_id IN (" . join(',', caGetOption('dont_include_subtypes', $va_facet_info, false) ? $va_restrict_to_types : $va_restrict_to_types_expanded) . ")";
                 $va_selects[] = "{$va_restrict_to_types_expanded}.type_id";
             }
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = '(' . $vs_where_sql . ')';
             }
             if ($vb_check_availability_only) {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT {$vs_browse_table_name}.{$vs_field_name}\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\tLIMIT 2";
                 $qr_res = $this->opo_db->query($vs_sql);
                 if ($qr_res->numRows() > 1) {
                     return true;
                 }
                 return false;
             } else {
                 $vs_pk = $t_item->primaryKey();
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT {$vs_browse_table_name}.{$vs_field_name}\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}";
                 if ($vs_sort_field) {
                     $vs_sql .= " ORDER BY {$vs_sort_field}";
                 }
                 $qr_res = $this->opo_db->query($vs_sql);
                 $va_values = array();
                 while ($qr_res->nextRow()) {
                     if (!($vs_val = trim($qr_res->get($vs_field_name))) && !$vb_is_bit) {
                         continue;
                     }
                     if ($va_criteria[$vs_val]) {
                         continue;
                     }
                     // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                     if ($vb_is_bit && isset($va_facet_values[$vs_val])) {
                         $va_values[$vs_val] = $va_facet_values[$vs_val];
                     } else {
                         $va_values[$vs_val] = array('id' => str_replace('/', '&#47;', $vs_val), 'label' => $vs_val);
                     }
                     if (!is_null($vs_single_value) && $vs_val == $vs_single_value) {
                         $vb_single_value_is_present = true;
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 return $va_values;
             }
             return array();
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'violations':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $vs_field_name = $va_facet_info['field'];
             $va_field_info = $t_item->getFieldInfo($vs_field_name);
             $va_joins = array();
             $va_wheres = array();
             $vs_where_sql = '';
             $va_facet_values = null;
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = '(' . $vs_where_sql . ')';
             }
             if ($vb_check_availability_only) {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = {$vs_browse_table_name}." . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = {$vs_browse_table_num}\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\tLIMIT 2";
                 $qr_res = $this->opo_db->query($vs_sql);
                 if ($qr_res->nextRow()) {
                     return (int) $qr_res->numRows() > 0 ? true : false;
                 }
                 return false;
             } else {
                 $vs_pk = $t_item->primaryKey();
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT ca_metadata_dictionary_rules.rule_id\n\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = {$vs_browse_table_name}." . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = {$vs_browse_table_num}\n\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{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_where_sql}";
                 $qr_res = $this->opo_db->query($vs_sql);
                 $va_values = array();
                 $t_rule = new ca_metadata_dictionary_rules();
                 while ($qr_res->nextRow()) {
                     if ($t_rule->load($qr_res->get('rule_id'))) {
                         if (!($vs_val = trim($t_rule->getSetting('label')))) {
                             continue;
                         }
                         $vs_code = $t_rule->get('rule_code');
                         if ($va_criteria[$vs_val]) {
                             continue;
                         }
                         // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                         if (isset($va_facet_values[$vs_code])) {
                             $va_values[$vs_code] = $va_facet_values[$vs_code];
                         } else {
                             $va_values[$vs_code] = array('id' => $vs_code, 'label' => $vs_val);
                         }
                         if (!is_null($vs_single_value) && $vs_code == $vs_single_value) {
                             $vb_single_value_is_present = true;
                         }
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 return $va_values;
             }
             return array();
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'checkouts':
             if ($vs_browse_table_name != 'ca_objects') {
                 return array();
             }
             $t_item = new ca_objects();
             $va_joins = array();
             $va_wheres = array();
             $vs_where_sql = '';
             $va_facet_values = null;
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             $vs_checkout_join_sql = "INNER JOIN ca_object_checkouts ON ca_object_checkouts.object_id = ca_objects.object_id";
             $vn_current_time = time();
             switch ($va_facet_info['status']) {
                 case 'overdue':
                     $va_wheres[] = "((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':
                     $va_wheres[] = "((ca_object_checkouts.checkout_date IS NULL) AND (ca_object_checkouts.return_date IS NULL))";
                     break;
                 case 'available':
                     $vs_checkout_join_sql = '';
                     $va_wheres[] = "(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;
                 default:
                 case 'out':
                     $va_wheres[] = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL))";
                     break;
             }
             if ($vs_checkout_join_sql) {
                 $va_joins[] = $vs_checkout_join_sql;
                 $va_joins[] = "INNER JOIN ca_users ON ca_object_checkouts.user_id = ca_users.user_id";
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = '(' . $vs_where_sql . ')';
             }
             if ($vb_check_availability_only) {
                 switch ($va_facet_info['mode']) {
                     case 'user':
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\tFROM ca_objects\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql} AND ca_objects.deleted = 0\n\t\t\t\t\t\t\t\t\tLIMIT 2";
                         break;
                     default:
                     case 'all':
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\t\tFROM ca_objects\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tca_objects.deleted = 0 " . (sizeof($va_results) ? "AND (" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tLIMIT 2";
                         break;
                 }
                 $qr_res = $this->opo_db->query($vs_sql);
                 if ($qr_res->nextRow()) {
                     return (int) $qr_res->numRows() > 0 ? true : false;
                 }
                 return false;
             } else {
                 $va_values = array();
                 $vs_pk = $t_item->primaryKey();
                 switch ($va_facet_info['mode']) {
                     case 'user':
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT DISTINCT ca_object_checkouts.user_id, ca_users.fname, ca_users.lname, ca_users.email\n\t\t\t\t\t\t\t\t\tFROM ca_objects\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql} " . (sizeof($va_results) ? " AND (" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))" : "");
                         $qr_res = $this->opo_db->query($vs_sql);
                         while ($qr_res->nextRow()) {
                             $vn_user_id = $qr_res->get('user_id');
                             $vs_val = $qr_res->get('fname') . ' ' . $qr_res->get('lname') . (($vs_email = $qr_res->get('email')) ? "({$vs_email})" : '');
                             if ($va_criteria[$vs_val]) {
                                 continue;
                             }
                             // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                             if (isset($va_facet_values[$vn_user_id])) {
                                 $va_values[$vn_user_id] = $va_facet_values[$vn_user_id];
                             } else {
                                 $va_values[$vn_user_id] = array('id' => $vn_user_id, 'label' => $vs_val);
                             }
                             if (!is_null($vs_single_value) && $vn_user_id == $vs_single_value) {
                                 $vb_single_value_is_present = true;
                             }
                         }
                         break;
                     case 'all':
                     default:
                         foreach (array(_t('Available') => 'available', _t('Out') => 'out', _t('Reserved') => 'reserved', _t('Overdue') => 'overdue') as $vs_status_text => $vs_status) {
                             $vs_join_sql = "INNER JOIN ca_object_checkouts ON ca_object_checkouts.object_id = ca_objects.object_id";
                             switch ($vs_status) {
                                 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_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;
                                 default:
                                 case 'out':
                                     $vs_where = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL))";
                                     break;
                             }
                             if (sizeof($va_results) && $this->numCriteria() > 0) {
                                 $vs_where .= " AND (" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
                             }
                             $vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT count(*) c\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_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";
                             $qr_res = $this->opo_db->query($vs_sql);
                             $qr_res->nextRow();
                             if (!$qr_res->get('c')) {
                                 continue;
                             }
                             $va_values[$vs_status] = array('id' => $vs_status, 'label' => $vs_status_text);
                             if (!is_null($vs_single_value) && $vs_status == $vs_single_value) {
                                 $vb_single_value_is_present = true;
                             }
                         }
                         break;
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 return $va_values;
             }
             return array();
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'normalizedDates':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $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();
             }
             if ($vb_is_element) {
                 $va_joins = array('INNER JOIN ca_attribute_values ON ca_attributes.attribute_id = ca_attribute_values.attribute_id', 'INNER JOIN ' . $vs_browse_table_name . ' ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_attributes.row_id AND ca_attributes.table_num = ' . intval($vs_browse_table_num));
             } else {
                 $va_joins = array();
             }
             $va_wheres = array();
             $vs_normalization = $va_facet_info['normalization'];
             // how do we construct the date ranges presented to uses. In other words - how do we want to allow users to browse dates? By year, decade, century?
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_where_sql = '';
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = ' AND (' . $vs_where_sql . ')';
             }
             $vs_join_sql = join("\n", $va_joins);
             if ($vb_is_element) {
                 $vn_element_id = $t_element->getPrimaryKey();
                 $vs_dir = strtoupper($va_facet_info['sort']) === 'DESC' ? "DESC" : "ASC";
                 $o_tep = new TimeExpressionParser();
                 $vn_min_date = $vn_max_date = null;
                 $vs_min_sql = $vs_max_sql = '';
                 if (isset($va_facet_info['minimum_date'])) {
                     if ($o_tep->parse($va_facet_info['minimum_date'])) {
                         $va_tmp = $o_tep->getHistoricTimestamps();
                         $vn_min_date = (double) $va_tmp['start'];
                         $vs_min_sql = " AND (ca_attribute_values.value_decimal1 >= {$vn_min_date})";
                     }
                 }
                 if (isset($va_facet_info['maximum_date'])) {
                     if ($o_tep->parse($va_facet_info['maximum_date'])) {
                         $va_tmp = $o_tep->getHistoricTimestamps();
                         $vn_max_date = (double) $va_tmp['end'];
                         $vs_max_sql = " AND (ca_attribute_values.value_decimal2 <= {$vn_max_date})";
                     }
                 }
                 if ($vb_check_availability_only) {
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? \n\t\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\tLIMIT 1";
                     //print $vs_sql;
                     $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                     return (int) $qr_res->numRows() > 0 ? true : false;
                 } else {
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT DISTINCT ca_attribute_values.value_decimal1, ca_attribute_values.value_decimal2\n\t\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? \n\t\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t";
                     //print $vs_sql;
                     $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                     $vn_current_year = (int) date("Y");
                     $va_values = array();
                     $vb_include_unknown = (bool) caGetOption('include_unknown', $va_facet_info, false);
                     $vb_unknown_is_set = false;
                     while ($qr_res->nextRow()) {
                         $vn_start = $qr_res->get('value_decimal1');
                         $vn_end = $qr_res->get('value_decimal2');
                         if (!($vn_start && $vn_end)) {
                             if ($vb_include_unknown) {
                                 $vb_unknown_is_set = true;
                             }
                             continue;
                         }
                         if ($vn_end > $vn_current_year + 50) {
                             continue;
                         }
                         // bad years can make for large facets that cause timeouts so cut it off 50 years into the future
                         $va_normalized_values = $o_tep->normalizeDateRange($vn_start, $vn_end, $vs_normalization);
                         foreach ($va_normalized_values as $vn_sort_value => $vs_normalized_value) {
                             if ($va_criteria[$vs_normalized_value]) {
                                 continue;
                             }
                             // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                             if (is_numeric($vs_normalized_value) && (int) $vs_normalized_value === 0) {
                                 continue;
                             }
                             // don't include year=0
                             $va_values[$vn_sort_value][$vs_normalized_value] = array('id' => $vs_normalized_value, 'label' => $vs_normalized_value);
                             if (!is_null($vs_single_value) && $vs_normalized_value == $vs_single_value) {
                                 $vb_single_value_is_present = true;
                             }
                         }
                     }
                     if ($vb_include_unknown && !$vb_unknown_is_set) {
                         // Check for rows where no data is set at all as opposed to null dates
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT DISTINCT ca_attributes.row_id\n\t\t\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? \n\t\t\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t";
                         //print $vs_sql;
                         $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                         if ($qr_res->numRows() < sizeof($va_results)) {
                             $vb_unknown_is_set = true;
                         }
                     }
                     if ($vb_unknown_is_set && sizeof($va_values) > 0) {
                         $va_values['999999999'][_t('Date unknown')] = array('id' => 'null', 'label' => _t('Date unknown'));
                     }
                     if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                         return array();
                     }
                     ksort($va_values);
                     if ($vs_dir == 'DESC') {
                         $va_values = array_reverse($va_values);
                     }
                     $va_sorted_values = array();
                     foreach ($va_values as $vn_sort_value => $va_values_for_sort_value) {
                         $va_sorted_values = array_merge($va_sorted_values, $va_values_for_sort_value);
                     }
                     return $va_sorted_values;
                 }
             } else {
                 // is intrinsic
                 $vs_dir = strtoupper($va_facet_info['sort']) === 'DESC' ? "DESC" : "ASC";
                 $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');
                 $o_tep = new TimeExpressionParser();
                 $vn_min_date = $vn_max_date = null;
                 $vs_min_sql = $vs_max_sql = '';
                 if (isset($va_facet_info['minimum_date'])) {
                     if ($o_tep->parse($va_facet_info['minimum_date'])) {
                         $va_tmp = $o_tep->getHistoricTimestamps();
                         $vn_min_date = (double) $va_tmp['start'];
                         $vs_min_sql = " AND ({$vs_browse_table_name}.{$vs_browse_start_fld} >= {$vn_min_date})";
                     }
                 }
                 if (isset($va_facet_info['maximum_date'])) {
                     if ($o_tep->parse($va_facet_info['maximum_date'])) {
                         $va_tmp = $o_tep->getHistoricTimestamps();
                         $vn_max_date = (double) $va_tmp['end'];
                         $vs_max_sql = " AND ({$vs_browse_table_name}.{$vs_browse_end_fld} <= {$vn_max_date})";
                     }
                 }
                 if ($vb_check_availability_only) {
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t1 = 1\n\t\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\tLIMIT 1";
                     //print $vs_sql;
                     $qr_res = $this->opo_db->query($vs_sql);
                     return (int) $qr_res->numRows() > 0 ? true : false;
                 } else {
                     $vs_sql = "\n\t\t\t\t\t\t\t\tSELECT DISTINCT {$vs_browse_table_name}.{$vs_browse_start_fld}, {$vs_browse_table_name}.{$vs_browse_end_fld}\n\t\t\t\t\t\t\t\tFROM {$vs_browse_table_name}\n\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t1 = 1\n\t\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t";
                     //print $vs_sql;
                     $qr_res = $this->opo_db->query($vs_sql);
                     $va_values = array();
                     while ($qr_res->nextRow()) {
                         $vn_start = $qr_res->get($vs_browse_start_fld);
                         $vn_end = $qr_res->get($vs_browse_end_fld);
                         if (!($vn_start && $vn_end)) {
                             continue;
                         }
                         $va_normalized_values = $o_tep->normalizeDateRange($vn_start, $vn_end, $vs_normalization);
                         foreach ($va_normalized_values as $vn_sort_value => $vs_normalized_value) {
                             if ($va_criteria[$vs_normalized_value]) {
                                 continue;
                             }
                             // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                             if (is_numeric($vs_normalized_value) && (int) $vs_normalized_value === 0) {
                                 continue;
                             }
                             // don't include year=0
                             $va_values[$vn_sort_value][$vs_normalized_value] = array('id' => $vs_normalized_value, 'label' => $vs_normalized_value);
                             if (!is_null($vs_single_value) && $vs_normalized_value == $vs_single_value) {
                                 $vb_single_value_is_present = true;
                             }
                         }
                     }
                     if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                         return array();
                     }
                     ksort($va_values);
                     if ($vs_dir == 'DESC') {
                         $va_values = array_reverse($va_values);
                     }
                     $va_sorted_values = array();
                     foreach ($va_values as $vn_sort_value => $va_values_for_sort_value) {
                         $va_sorted_values = array_merge($va_sorted_values, $va_values_for_sort_value);
                     }
                     return $va_sorted_values;
                 }
             }
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'normalizedLength':
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             $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();
             }
             if ($vb_is_element) {
                 $va_joins = array('INNER JOIN ca_attribute_values ON ca_attributes.attribute_id = ca_attribute_values.attribute_id', 'INNER JOIN ' . $vs_browse_table_name . ' ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_attributes.row_id AND ca_attributes.table_num = ' . intval($vs_browse_table_num));
             } else {
                 $va_joins = array();
             }
             $va_wheres = array();
             $vs_normalization = $va_facet_info['normalization'];
             // how do we construct the dimensions ranges presented to users. In other words - what increments do we can to use to  browse measurments?
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
             }
             if ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($t_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $vs_browse_table_name . ".deleted = 0)";
             }
             if ($va_facet_info['relative_to']) {
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_where_sql = '';
             if (is_array($va_wheres) && sizeof($va_wheres) && ($vs_where_sql = join(' AND ', $va_wheres))) {
                 $vs_where_sql = ' AND (' . $vs_where_sql . ')';
             }
             $vs_join_sql = join("\n", $va_joins);
             $vn_element_id = $t_element->getPrimaryKey();
             $vs_dir = strtoupper($va_facet_info['sort']) === 'DESC' ? "DESC" : "ASC";
             $vs_min_sql = $vs_max_sql = '';
             $vo_minimum_dimension = caParseLengthDimension(caGetOption('minimum_dimension', $va_facet_info, "0 in"));
             $vo_maximum_dimension = caParseLengthDimension(caGetOption('maximum_dimension', $va_facet_info, "0 in"));
             if ($vo_minimum_dimension) {
                 $vn_tmp = (double) $vo_minimum_dimension->convertTo('METER', 6, 'en_US');
                 $vs_min_sql = " AND (ca_attribute_values.value_decimal1 >= {$vn_tmp})";
             }
             if (caGetOption('maximum_dimension', $va_facet_info, null) && $vo_maximum_dimension) {
                 $vn_tmp = (double) $vo_maximum_dimension->convertTo('METER', 6, 'en_US');
                 $vs_max_sql = " AND (ca_attribute_values.value_decimal1 <= {$vn_tmp})";
             }
             if ($vb_check_availability_only) {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? \n\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\tLIMIT 1";
                 //print $vs_sql;
                 $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                 return (int) $qr_res->numRows() > 0 ? true : false;
             } else {
                 $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT ca_attribute_values.value_decimal1, ca_attribute_values.value_decimal2, ca_attribute_values.value_longtext1, ca_attribute_values.value_longtext2\n\t\t\t\t\t\t\tFROM ca_attributes\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tca_attribute_values.element_id = ? \n\t\t\t\t\t\t\t\t{$vs_min_sql}\n\t\t\t\t\t\t\t\t{$vs_max_sql}\n\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t";
                 //print $vs_sql;
                 $qr_res = $this->opo_db->query($vs_sql, $vn_element_id);
                 $va_values = array();
                 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');
                 while ($qr_res->nextRow()) {
                     $vn_meters = $qr_res->get('value_decimal1');
                     // measurement in meters
                     // convert to target dimensions
                     // normalize
                     $vo_dim = new Zend_Measure_Length($vn_meters, Zend_Measure_Length::METER, 'en_US');
                     $vs_dim = $vo_dim->convertTo($vs_output_units, 6, 'en_US');
                     $vn_dim = (double) $vs_dim;
                     $vn_normalized = floor($vn_dim / $vn_increment_in_current_units) * $vn_increment_in_current_units;
                     if (isset($va_criteria[$vn_normalized])) {
                         continue;
                     }
                     $vs_normalized_range_with_units = "{$vn_normalized} {$vs_units} - " . ($vn_normalized + $vn_increment_in_current_units) . " {$vs_units}";
                     $va_values[$vn_normalized][$vn_normalized] = array('id' => $vn_normalized, 'label' => $vs_normalized_range_with_units);
                     if (!is_null($vs_single_value) && $vn_normalized == $vs_single_value) {
                         $vb_single_value_is_present = true;
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 ksort($va_values);
                 if ($vs_dir == 'DESC') {
                     $va_values = array_reverse($va_values);
                 }
                 $va_sorted_values = array();
                 foreach ($va_values as $vn_sort_value => $va_values_for_sort_value) {
                     $va_sorted_values = array_merge($va_sorted_values, $va_values_for_sort_value);
                 }
                 return $va_sorted_values;
             }
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         case 'authority':
             $vs_rel_table_name = $va_facet_info['table'];
             $va_params = $this->opo_ca_browse_cache->getParameters();
             // Make sure we honor type restrictions for the related authority
             $va_user_type_restrictions = caGetTypeRestrictionsForUser($vs_rel_table_name);
             $va_restrict_to_types = $va_facet_info['restrict_to_types'];
             if (is_array($va_user_type_restrictions)) {
                 if (!is_array($va_restrict_to_types)) {
                     $va_restrict_to_types = $va_user_type_restrictions;
                 } else {
                     $va_restrict_to_types = array_intersect($va_restrict_to_types, $va_user_type_restrictions);
                 }
             }
             if (!is_array($va_exclude_types = $va_facet_info['exclude_types'])) {
                 $va_exclude_types = array();
             }
             if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) {
                 $va_restrict_to_relationship_types = array();
             }
             if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) {
                 $va_exclude_relationship_types = array();
             }
             $t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true);
             if ($vs_browse_table_name == $vs_rel_table_name) {
                 // browsing on self-relations not supported
                 break;
             } else {
                 switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_browse_table_name, $vs_rel_table_name)))) {
                     case __CA_ATTRIBUTE_VALUE_LIST__:
                         $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 __CA_ATTRIBUTE_VALUE_DATERANGE__:
                         $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;
                 }
             }
             $vb_rel_is_hierarchical = (bool) $t_rel_item->isHierarchical();
             //
             // Convert related item type_code specs in restrict_to_types and exclude_types lists to numeric type_ids we need for the query
             //
             if (!is_array($va_restrict_to_types = $this->_convertTypeCodesToIDs($va_restrict_to_types, array('instance' => $t_rel_item, 'dontExpandHierarchically' => true)))) {
                 $va_restrict_to_types = array();
             }
             if (!is_array($va_exclude_types = $this->_convertTypeCodesToIDs($va_exclude_types, array('instance' => $t_rel_item, 'dontExpandHierarchically' => true)))) {
                 $va_exclude_types = array();
             }
             $va_restrict_to_types_expanded = $this->_convertTypeCodesToIDs($va_restrict_to_types, array('instance' => $t_rel_item));
             $va_exclude_types_expanded = $this->_convertTypeCodesToIDs($va_exclude_types, array('instance' => $t_rel_item));
             // look up relationship type restrictions
             $va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']);
             $va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']);
             $va_joins = array();
             $va_selects = array();
             $va_wheres = array();
             $va_orderbys = array();
             if (!$va_facet_info['show_all_when_first_facet'] || $this->numCriteria() > 0) {
                 $vs_cur_table = array_shift($va_path);
                 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;
                 }
             } else {
                 if ($va_facet_info['show_all_when_first_facet']) {
                     $va_path = array_reverse($va_path);
                     // in "show_all" mode we turn the browse on it's head and grab records by the "subject" table, rather than the browse table
                     $vs_cur_table = array_shift($va_path);
                     $vs_join_table = $va_path[0];
                     $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table);
                     $va_joins[] = '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";
                 }
             }
             if (sizeof($va_results) && $this->numCriteria() > 0) {
                 $va_wheres[] = "(" . $t_subject->tableName() . '.' . $t_subject->primaryKey() . " IN (" . join(',', $va_results) . "))";
             }
             if (!is_array($va_restrict_to_lists = $va_facet_info['restrict_to_lists'])) {
                 $va_restrict_to_lists = array();
             }
             if (is_array($va_restrict_to_lists) && sizeof($va_restrict_to_lists) > 0 && $t_rel_item->tableName() == 'ca_list_items') {
                 $va_list_ids = array();
                 foreach ($va_restrict_to_lists as $vm_list) {
                     if (is_numeric($vm_list)) {
                         $vn_list_id = (int) $vm_list;
                     } else {
                         $vn_list_id = (int) ca_lists::getListID($vm_list);
                     }
                     if ($vn_list_id) {
                         $va_list_ids[] = $vn_list_id;
                     }
                 }
                 if (sizeof($va_list_ids) > 0) {
                     $va_wheres[] = "{$vs_rel_table_name}.list_id IN (" . join(',', $va_list_ids) . ")";
                 }
             }
             if (is_array($va_restrict_to_types) && sizeof($va_restrict_to_types) > 0 && method_exists($t_rel_item, "getTypeList")) {
                 $va_wheres[] = "{$vs_rel_table_name}.type_id IN (" . join(',', caGetOption('dont_include_subtypes', $va_facet_info, false) ? $va_restrict_to_types : $va_restrict_to_types_expanded) . ")" . ($t_rel_item->getFieldInfo('type_id', 'IS_NULL') ? " OR ({$vs_rel_table_name}.type_id IS NULL)" : '');
                 $va_selects[] = "{$vs_rel_table_name}.type_id";
             }
             if (is_array($va_exclude_types) && sizeof($va_exclude_types) > 0 && method_exists($t_rel_item, "getTypeList")) {
                 $va_wheres[] = "{$vs_rel_table_name}.type_id NOT IN (" . join(',', caGetOption('dont_include_subtypes', $va_facet_info, false) ? $va_exclude_types : $va_exclude_types_expanded) . ")";
             }
             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']) . "))";
                 // exclude non-accessible authority items
                 if (!$va_facet_info['show_all_when_first_facet'] || $this->numCriteria() > 0) {
                     $va_wheres[] = "(" . $vs_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))";
                     // exclude non-accessible browse items
                 }
             }
             if ($t_item->hasField('deleted') && !$va_facet_info['show_all_when_first_facet']) {
                 $va_wheres[] = "(" . $t_item->tableName() . ".deleted = 0)";
             }
             if ($t_rel_item->hasField('deleted')) {
                 $va_wheres[] = "(" . $t_rel_item->tableName() . ".deleted = 0)";
             }
             $vs_rel_pk = $t_rel_item->primaryKey();
             $va_rel_attr_elements = $t_rel_item->getApplicableElementCodes(null, true, false);
             $va_attrs_to_fetch = array();
             if (!$va_facet_info['show_all_when_first_facet'] || $this->numCriteria() > 0) {
                 //$va_selects[] = $t_item->tableName().'.'.$t_item->primaryKey();			// get primary key of subject
             }
             $va_selects[] = $t_rel_item->tableName() . '.' . $vs_rel_pk;
             // get primary key of related
             $vs_hier_parent_id_fld = $vs_hier_id_fld = null;
             if ($vb_rel_is_hierarchical) {
                 $vs_hier_parent_id_fld = $t_rel_item->getProperty('HIERARCHY_PARENT_ID_FLD');
                 $va_selects[] = $t_rel_item->tableName() . '.' . $vs_hier_parent_id_fld;
                 if ($vs_hier_id_fld = $t_rel_item->getProperty('HIERARCHY_ID_FLD')) {
                     $va_selects[] = $t_rel_item->tableName() . '.' . $vs_hier_id_fld;
                 }
             }
             // analyze group_fields (if defined) and add them to the query
             $va_groupings_to_fetch = array();
             if (isset($va_facet_info['groupings']) && is_array($va_facet_info['groupings']) && sizeof($va_facet_info['groupings'])) {
                 foreach ($va_facet_info['groupings'] as $vs_grouping => $vs_grouping_name) {
                     // is grouping type_id?
                     if ($vs_grouping === 'type' && $t_rel_item->hasField('type_id')) {
                         $va_selects[] = $t_rel_item->tableName() . '.type_id';
                         $va_groupings_to_fetch[] = 'type_id';
                     }
                     // is group field a relationship type?
                     if ($vs_grouping === 'relationship_types') {
                         $va_selects[] = $va_facet_info['relationship_table'] . '.type_id rel_type_id';
                         $va_groupings_to_fetch[] = 'rel_type_id';
                     }
                     // is group field an attribute?
                     if (preg_match('!^ca_attribute_([^:]*)!', $vs_grouping, $va_matches)) {
                         if ($vn_element_id = array_search($va_matches[1], $va_rel_attr_elements)) {
                             $va_attrs_to_fetch[] = $vn_element_id;
                         }
                     }
                 }
             }
             if ($va_facet_info['relative_to']) {
                 // TODO: do this everywhere
                 $va_restrict_to_relationship_types = array();
                 $vs_browse_type_limit_sql = '';
                 if ($t_subject->hasField('deleted')) {
                     $va_wheres[] = "(" . $t_subject->tableName() . ".deleted = 0)";
                 }
                 if ($va_relative_sql_data = $this->_getRelativeFacetSQLData($va_facet_info['relative_to'], $pa_options)) {
                     $va_joins = array_merge($va_joins, $va_relative_sql_data['joins']);
                     $va_wheres = array_merge($va_wheres, $va_relative_sql_data['wheres']);
                 }
             }
             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 ($vs_browse_type_limit_sql) {
                 $va_wheres[] = $vs_browse_type_limit_sql;
             }
             if ($this->opo_config->get('perform_item_level_access_checking')) {
                 if ($t_item = $this->opo_datamodel->getInstanceByTableName($vs_browse_table_name, true)) {
                     // Join to limit what browse table items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl ON ' . $vs_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_acl.row_id AND ca_acl.table_num = ' . $t_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(ca_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(ca_acl.user_id IS NULL and ca_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND ca_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR ca_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                     // Join to limit what related items are used to generate facet
                     $va_joins[] = 'LEFT JOIN ca_acl AS rel_acl ON ' . $t_rel_item->tableName() . '.' . $t_rel_item->primaryKey() . ' = rel_acl.row_id AND rel_acl.table_num = ' . $t_rel_item->tableNum() . "\n";
                     $va_wheres[] = "(\n\t\t\t\t\t\t\t\t((\n\t\t\t\t\t\t\t\t\t(rel_acl.user_id = " . (int) $vn_user_id . ")\n\t\t\t\t\t\t\t\t\t" . (sizeof($va_group_ids) > 0 ? "OR\n\t\t\t\t\t\t\t\t\t(rel_acl.group_id IN (" . join(",", $va_group_ids) . "))" : "") . "\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(rel_acl.user_id IS NULL and rel_acl.group_id IS NULL)\n\t\t\t\t\t\t\t\t) AND rel_acl.access >= " . __CA_ACL_READONLY_ACCESS__ . ")\n\t\t\t\t\t\t\t\t" . ($vb_show_if_no_acl ? "OR rel_acl.acl_id IS NULL" : "") . "\n\t\t\t\t\t\t\t)";
                 }
             }
             $vs_join_sql = join("\n", $va_joins);
             if ($vb_check_availability_only) {
                 if (!$va_facet_info['show_all_when_first_facet'] || $this->numCriteria() > 0) {
                     $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t" . (sizeof($va_wheres) ? ' WHERE ' : '') . join(" AND ", $va_wheres) . " LIMIT 1";
                 } else {
                     $vs_sql = "\n\t\t\t\t\t\t\tSELECT 1\n\t\t\t\t\t\t\tFROM " . $t_rel_item->tableName() . "\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t" . (sizeof($va_wheres) ? ' WHERE ' : '') . join(" AND ", $va_wheres) . " LIMIT 1";
                 }
                 $qr_res = $this->opo_db->query($vs_sql);
                 //print "<hr>$vs_sql<hr>\n";
                 return (int) $qr_res->numRows() > 0 ? true : false;
             } else {
                 if (!$va_facet_info['show_all_when_first_facet'] || $this->numCriteria() > 0) {
                     $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT " . join(', ', $va_selects) . "\n\t\t\t\t\t\t\tFROM " . $vs_browse_table_name . "\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t" . (sizeof($va_wheres) ? ' WHERE ' : '') . join(" AND ", $va_wheres) . "\n\t\t\t\t\t\t\t\t" . (sizeof($va_orderbys) ? "ORDER BY " . join(', ', $va_orderbys) : '');
                 } else {
                     $vs_sql = "\n\t\t\t\t\t\t\tSELECT DISTINCT " . join(', ', $va_selects) . "\n\t\t\t\t\t\t\tFROM " . $t_rel_item->tableName() . "\n\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t" . (sizeof($va_wheres) ? ' WHERE ' : '') . join(" AND ", $va_wheres) . "\n\t\t\t\t\t\t\t\t" . (sizeof($va_orderbys) ? "ORDER BY " . join(', ', $va_orderbys) : '');
                 }
                 //print "<hr>$vs_sql<hr>\n";
                 $qr_res = $this->opo_db->query($vs_sql);
                 $va_facet = $va_facet_items = array();
                 $vs_rel_pk = $t_rel_item->primaryKey();
                 // First get related ids with type and relationship type values
                 // (You could get all of the data we need for the facet in a single query but it turns out to be faster for very large facets to
                 // do it in separate queries, one for the primary ids and another for the labels; a third is done if attributes need to be fetched.
                 // There appears to be a significant [~10%] performance for smaller facets and a larger one [~20-25%] for very large facets)
                 $vn_max_level = caGetOption('maximum_levels', $va_facet_info, null);
                 while ($qr_res->nextRow()) {
                     $va_fetched_row = $qr_res->getRow();
                     $vn_id = $va_fetched_row[$vs_rel_pk];
                     //if (isset($va_facet_items[$vn_id])) { continue; } --- we can't do this as then we don't detect items that have multiple rel_type_ids... argh.
                     if (isset($va_criteria[$vn_id])) {
                         continue;
                     }
                     // skip items that are used as browse critera - don't want to browse on something you're already browsing on
                     if (!$va_facet_items[$va_fetched_row[$vs_rel_pk]]) {
                         // if(!is_null($vn_max_level)) {
                         // 									if (sizeof($va_ancestors) + 1 > $vn_max_level) {
                         // 										if ($va_tmp = $va_ancestors[sizeof($va_ancestors) - $vn_max_level]) {
                         // 											$va_ancestors = array();
                         // 											$va_fetched_row = $va_tmp['NODE'];
                         // 										}
                         // 									}
                         // 								}
                         if (is_array($va_restrict_to_types) && sizeof($va_restrict_to_types) && $va_fetched_row['type_id'] && !in_array($va_fetched_row['type_id'], $va_restrict_to_types)) {
                             continue;
                         }
                         $va_facet_items[$va_fetched_row[$vs_rel_pk]] = array('id' => $va_fetched_row[$vs_rel_pk], 'type_id' => array(), 'parent_id' => $vb_rel_is_hierarchical ? $va_fetched_row[$vs_hier_parent_id_fld] : null, 'hierarchy_id' => $vb_rel_is_hierarchical ? $va_fetched_row[$vs_hier_id_fld] : null, 'rel_type_id' => array(), 'child_count' => 0);
                         if (!is_null($vs_single_value) && $va_fetched_row[$vs_rel_pk] == $vs_single_value) {
                             $vb_single_value_is_present = true;
                         }
                     }
                     if ($va_fetched_row['type_id']) {
                         $va_facet_items[$va_fetched_row[$vs_rel_pk]]['type_id'][] = $va_fetched_row['type_id'];
                     }
                     if ($va_fetched_row['rel_type_id']) {
                         $va_facet_items[$va_fetched_row[$vs_rel_pk]]['rel_type_id'][] = $va_fetched_row['rel_type_id'];
                     }
                 }
                 if (!isset($va_facet_info['dont_expand_hierarchically']) || !$va_facet_info['dont_expand_hierarchically']) {
                     $qr_res->seek(0);
                     $va_ids = $qr_res->getAllFieldValues($vs_rel_pk);
                     $qr_ancestors = call_user_func($t_rel_item->tableName() . '::getHierarchyAncestorsForIDs', $va_ids, array('returnAs' => 'SearchResult'));
                     $vs_rel_table = $t_rel_item->tableName();
                     $vs_rel_pk = $t_rel_item->primaryKey();
                     $vb_check_ancestor_access = (bool) (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access'));
                     if ($qr_ancestors) {
                         while ($qr_ancestors->nextHit()) {
                             $vn_parent_type_id = $qr_ancestors->get('type_id');
                             if (sizeof($va_exclude_types) > 0 && in_array($vn_parent_type_id, $va_exclude_types)) {
                                 continue;
                             }
                             if (sizeof($va_restrict_to_types) > 0 && !in_array($vn_parent_type_id, $va_restrict_to_types)) {
                                 continue;
                             }
                             if ($vb_check_ancestor_access && !in_array($qr_ancestors->get('access'), $pa_options['checkAccess'])) {
                                 continue;
                             }
                             $va_facet_items[$vn_ancestor_id = (int) $qr_ancestors->get("{$vs_rel_pk}")] = array('id' => $vn_ancestor_id, 'type_id' => array(), 'parent_id' => $vb_rel_is_hierarchical ? $qr_ancestors->get("{$vs_hier_parent_id_fld}") : null, 'hierarchy_id' => $vb_rel_is_hierarchical && $vs_hier_id_fld ? $qr_ancestors->get($vs_hier_id_fld) : null, 'rel_type_id' => array(), 'child_count' => 0);
                         }
                     }
                 }
                 // Set child counts
                 foreach ($va_facet_items as $vn_i => $va_item) {
                     if ($va_item['parent_id'] && isset($va_facet_items[$va_item['parent_id']])) {
                         $va_facet_items[$va_item['parent_id']]['child_count']++;
                     }
                 }
                 // Get labels for facet items
                 if (sizeof($va_row_ids = array_keys($va_facet_items))) {
                     if ($vs_label_table_name = $t_rel_item->getLabelTableName()) {
                         $t_rel_item_label = $this->opo_datamodel->getInstanceByTableName($vs_label_table_name, true);
                         $vs_label_display_field = $t_rel_item_label->getDisplayField();
                         $vs_rel_pk = $t_rel_item->primaryKey();
                         $va_label_wheres = array();
                         if ($t_rel_item_label->hasField('is_preferred')) {
                             $va_label_wheres[] = "({$vs_label_table_name}.is_preferred = 1)";
                         }
                         $va_label_wheres[] = "({$vs_label_table_name}.{$vs_rel_pk} IN (" . join(",", $va_row_ids) . "))";
                         $va_label_selects[] = "{$vs_label_table_name}.{$vs_rel_pk}";
                         $va_label_selects[] = "{$vs_label_table_name}.locale_id";
                         $va_label_fields = $t_rel_item->getLabelUIFields();
                         foreach ($va_label_fields as $vs_label_field) {
                             $va_label_selects[] = "{$vs_label_table_name}.{$vs_label_field}";
                         }
                         // Get label ordering fields
                         $va_ordering_fields_to_fetch = isset($va_facet_info['order_by_label_fields']) && is_array($va_facet_info['order_by_label_fields']) ? $va_facet_info['order_by_label_fields'] : array();
                         $va_orderbys = array();
                         foreach ($va_ordering_fields_to_fetch as $vs_sort_by_field) {
                             if (!$t_rel_item_label->hasField($vs_sort_by_field)) {
                                 continue;
                             }
                             $va_orderbys[] = $va_label_selects[] = $vs_label_table_name . '.' . $vs_sort_by_field;
                         }
                         // get labels
                         $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT " . join(', ', $va_label_selects) . "\n\t\t\t\t\t\t\t\t\tFROM " . $vs_label_table_name . "\n\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_label_wheres) ? ' WHERE ' : '') . join(" AND ", $va_label_wheres) . "\n\t\t\t\t\t\t\t\t\t\t" . (sizeof($va_orderbys) ? "ORDER BY " . join(', ', $va_orderbys) : '') . "";
                         //print $vs_sql;
                         $qr_labels = $this->opo_db->query($vs_sql);
                         while ($qr_labels->nextRow()) {
                             $va_fetched_row = $qr_labels->getRow();
                             $va_facet_item = array_merge($va_facet_items[$va_fetched_row[$vs_rel_pk]], array('label' => $va_fetched_row[$vs_label_display_field]));
                             foreach ($va_ordering_fields_to_fetch as $vs_to_fetch) {
                                 $va_facet_item[$vs_to_fetch] = $va_fetched_row[$vs_to_fetch];
                             }
                             $va_facet[$va_fetched_row[$vs_rel_pk]][$va_fetched_row['locale_id']] = $va_facet_item;
                         }
                     }
                     // get attributes for facet items
                     if (sizeof($va_attrs_to_fetch)) {
                         $qr_attrs = $this->opo_db->query("\n\t\t\t\t\t\t\t\t\tSELECT c_av.*, c_a.locale_id, c_a.row_id\n\t\t\t\t\t\t\t\t\tFROM ca_attributes c_a\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values c_av ON c_a.attribute_id = c_av.attribute_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tc_av.element_id IN (" . join(',', $va_attrs_to_fetch) . ")\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tc_a.table_num = ? \n\t\t\t\t\t\t\t\t\t\tAND \n\t\t\t\t\t\t\t\t\t\tc_a.row_id IN (" . join(',', $va_row_ids) . ")\n\t\t\t\t\t\t\t\t", $t_rel_item->tableNum());
                         while ($qr_attrs->nextRow()) {
                             $va_fetched_row = $qr_attrs->getRow();
                             $vn_id = $va_fetched_row['row_id'];
                             // if no locale is set for the attribute default it to whatever the locale for the item is
                             if (!($vn_locale_id = $va_fetched_row['locale_id'])) {
                                 $va_tmp = array_keys($va_facet[$vn_id]);
                                 $vn_locale_id = $va_tmp[0];
                             }
                             $va_facet[$vn_id][$vn_locale_id]['ca_attribute_' . $va_fetched_row['element_id']][] = $va_fetched_row;
                         }
                     }
                 }
                 if (!is_null($vs_single_value) && !$vb_single_value_is_present) {
                     return array();
                 }
                 return caExtractValuesByUserLocale($va_facet);
             }
             break;
             # -----------------------------------------------------
         # -----------------------------------------------------
         default:
             return null;
             break;
             # -----------------------------------------------------
     }
 }
Example #5
0
 /**
  * Extract relevant data from webservice response.
  *
  * @param string $xml
  * @return array
  * @throws Dhl_Account_Exception
  */
 protected function parsePackstationFinderResponse($xml)
 {
     $element = new SimpleXMLElement($xml, LIBXML_NOCDATA);
     // invalid xml exception
     if (!$element) {
         throw new Dhl_Account_Exception('An error occured while parsing the packstation response.');
     }
     // empty xml exception, optionally with error info
     if (!$element->data->children()->count()) {
         $errorMessage = $this->parseResponseSessionInfo($element->session, 'No data transmitted');
         throw new Dhl_Account_Exception($errorMessage);
     }
     // no or too many packstations found exception, optionally with error info
     if (!$element->data->result->automats || !$element->data->result->automats->children()->count()) {
         $errorMessage = $this->parseResponseSessionInfo($element->session, 'Address is ambigious');
         throw new Dhl_Account_Exception($errorMessage);
     }
     $automats = array();
     foreach ($element->data->result->automats->children() as $automat) {
         $distance = trim(current($automat->location->distance));
         $measure = new Zend_Measure_Length($distance, Zend_Measure_Length::METER, Mage::app()->getLocale()->getLocale());
         if ($distance > 1000) {
             $measure->convertTo(Zend_Measure_Length::KILOMETER);
         }
         $automats[] = array('packstationnumber' => substr(trim(current($automat['objectId'])), -3), 'city' => trim($automat->address->city), 'zip' => trim($automat->address->zip), 'street' => trim($automat->address->street), 'streetno' => trim($automat->address->streetno), 'errors' => '', 'distance' => $measure->toString());
     }
     return $automats;
 }
 /**
  * @param $attributeType
  * @param $value
  *
  * @return int|string
  */
 public function getAttributesMappingUnitConversion($attributeType, $value)
 {
     $this->_getConfigAttributeMapped($attributeType);
     if ($attributeType == 'weight') {
         //check if needs conversion
         if ($this->_mapping[$attributeType]['unit'] != self::ME_WEIGHT_UNIT) {
             $unit = new \Zend_Measure_Weight((double) $value);
             $unit->convertTo(\Zend_Measure_Weight::GRAM);
             return $unit->getValue();
         }
     } elseif ($this->_mapping[$attributeType]['unit'] != self::ME_LENGTH_UNIT) {
         $unit = new \Zend_Measure_Length((double) $value);
         $unit->convertTo(\Zend_Measure_Length::CENTIMETER);
         return $unit->getValue();
     }
     return $value;
 }