static function getFilterValuesSearch(&$filter, &$view_join, &$view_where, &$filters_where)
 {
     //echo "<pre>"; debug_print_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); echo "</pre>";
     $db = JFactory::getDBO();
     $filter_where_curr = '';
     foreach ($filters_where as $filter_id => $filter_where) {
         if ($filter_id != $filter->id) {
             $filter_where_curr .= ' ' . $filter_where;
         }
     }
     $valuesselect = @$filter->filter_isindexed ? ' ai.value_id as value, ai.search_index as text ' : ' ai.search_index as value, ai.search_index as text';
     $faceted_filter = $filter->parameters->get('faceted_filter_s', 2);
     $display_filter_as = $filter->parameters->get('display_filter_as_s', 0);
     // Filter Type of Display
     $filter_as_range = in_array($display_filter_as, array(2, 3));
     $show_matching_items = $filter->parameters->get('show_matching_items_s', 1);
     $show_matches = $filter_as_range || !$faceted_filter ? 0 : $show_matching_items;
     static $item_ids_list = null;
     static $item_ids_sub_query = null;
     if ($faceted_filter) {
         // Find items belonging to current view
         if ($item_ids_list === null && empty($view_where)) {
             $item_ids_list = '';
         }
         if ($item_ids_list === null || $item_ids_sub_query === null) {
             $sub_query = 'SELECT DISTINCT ai.item_id ' . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' JOIN #__content i ON ai.item_id = i.id' . "\n" . $view_join . "\n" . $view_where . "\n";
             $db->setQuery($sub_query);
             global $fc_run_times, $fc_jprof, $fc_searchview;
             $start_microtime = microtime(true);
             $view_total = 1;
             //(int) @ $fc_searchview['view_total'];
             $use_item_list_below = 0;
             if ($view_total >= $use_item_list_below) {
                 // Use sub-query only if current view has more than 0 items
                 $item_ids_sub_query = $sub_query;
             } else {
                 // If current view has less than nnn items, then pre-calculate an item_id list ... ??? this is may or may not be benfitial ...
                 try {
                     // 1, try to bypass joomla SQL layer, including Falang (slow in large sites, not needed in this query ?) !!
                     $rows = flexicontent_db::directQuery($sub_query, false, true);
                     $item_ids = array();
                     foreach ($rows as $row) {
                         $item_ids[] = $row->id;
                     }
                 } catch (Exception $e) {
                     // 2, get items via normal joomla SQL layer
                     $db->setQuery($sub_query);
                     $item_ids = FLEXI_J16GE ? $db->loadColumn() : $db->loadResultArray();
                     if ($db->getErrorNum()) {
                         JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error');
                     }
                 }
                 $item_ids_list = implode(',', $item_ids);
                 unset($item_ids);
             }
             $fc_run_times['_create_filter_init'] = round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10;
         }
         // Get ALL records that have such values for the given field
         $query = 'SELECT ' . $valuesselect . ($faceted_filter && $show_matches ? ', COUNT(DISTINCT ai.item_id) as found ' : '') . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' WHERE 1 ' . "\n" . (!$item_ids_list ? '' : ' AND ai.item_id IN(' . $item_ids_list . ')' . "\n") . (!$item_ids_sub_query ? '' : ' AND ai.item_id IN(' . $item_ids_sub_query . ')' . "\n") . ' AND ai.field_id=' . (int) $filter->id . "\n" . str_replace('i.id', 'ai.item_id', $filter_where_curr) . "\n" . ' GROUP BY ai.search_index, ai.value_id' . "\n";
     } else {
         // Get ALL records that have such values for the given field
         $query = 'SELECT ' . $valuesselect . ($faceted_filter && $show_matches ? ', COUNT(DISTINCT i.id) as found ' : '') . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' JOIN #__content i ON ai.item_id = i.id' . "\n" . $view_join . "\n" . ' WHERE ' . "\n" . ($view_where ? $view_where . ' AND ' : '') . "\n" . ' ai.field_id=' . (int) $filter->id . "\n" . $filter_where_curr . ' GROUP BY ai.search_index, ai.value_id' . "\n";
         /*$query = 'SELECT DISTINCT '. $valuesselect ."\n"
         		.' FROM #__flexicontent_advsearch_index AS ai'."\n"
         		.' WHERE ai.field_id='.(int)$filter->id."\n"
         		//.' GROUP BY ai.search_index, ai.value_id'."\n"
         		;*/
     }
     $db->setQuery($query);
     $results = $db->loadObjectList('text');
     //echo "<br/>". count($results) ."<br/>";
     //echo nl2br($query) ."<br/><br/>";
     if ($db->getErrorNum()) {
         $filter->html = "Filter for : {$filter->label} cannot be displayed, error during db query :<br />" . $query . "<br/>" . __FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg());
         return array();
     }
     return $results;
 }
 /**
  * Search method
  *
  * The sql must return the following fields that are used in a common display routine:
  *
  *   href, title, section, created, text, browsernav
  *
  * @param string Target search string
  * @param string matching option, natural|natural_expanded|exact|any|all
  * @param string ordering option, newest|oldest|popular|alpha|category
  * @param mixed An array if restricted to areas, null if search all
  */
 function onContentSearch($text, $phrase = '', $ordering = '', $areas = null)
 {
     $app = JFactory::getApplication();
     $view = JRequest::getCMD('view');
     $app->setUserState('fc_view_total_' . $view, 0);
     $app->setUserState('fc_view_limit_max_' . $view, 0);
     // Check if not requested search areas, inside this search areas of this plugin
     if (is_array($areas) && !array_intersect($areas, array_keys($this->onContentSearchAreas()))) {
         return array();
     }
     // Initialize some variables
     $db = JFactory::getDBO();
     $user = JFactory::getUser();
     $menu = $app->getMenu()->getActive();
     // Get the COMPONENT only parameters and merge current menu item parameters
     $params = clone JComponentHelper::getParams('com_flexicontent');
     if ($menu) {
         $params->merge($menu->params);
     }
     // some parameter shortcuts for SQL query
     $show_noauth = $params->get('show_noauth', 0);
     $orderby_override = $params->get('orderby_override', 1);
     // Compatibility text search (LIKE %word%) for language without spaces
     $filter_word_like_any = $params->get('filter_word_like_any', 0);
     // ************************************************
     // some parameter shortcuts common with search view
     // ************************************************
     $canseltypes = $params->get('canseltypes', 1);
     $txtmode = $params->get('txtmode', 0);
     // 0: BASIC Index, 1: ADVANCED Index without search fields user selection, 2: ADVANCED Index with search fields user selection
     // Get if text searching according to specific (single) content type
     $show_txtfields = $params->get('show_txtfields', 1);
     //0:hide, 1:according to content, 2:use custom configuration
     $show_txtfields = $txtmode ? 0 : $show_txtfields;
     // disable this flag if using BASIC index for text search
     // Get if filtering according to specific (single) content type
     $show_filters = $params->get('show_filters', 1);
     //0:hide, 1:according to content, 2:use custom configuration
     // Force single type selection and showing the content type selector
     $type_based_search = $show_filters == 1 || $show_txtfields == 1;
     $canseltypes = $type_based_search ? 1 : $canseltypes;
     // ********************************************************************
     // Get Content Types allowed for user selection in the Search Form
     // Also retrieve their configuration, plus the currently selected types
     // ********************************************************************
     // Get them from configuration
     $contenttypes = $params->get('contenttypes', array());
     // Sanitize them
     $contenttypes = !is_array($contenttypes) ? array($contenttypes) : $contenttypes;
     $contenttypes = array_unique(array_map('intval', $contenttypes));
     // Make sure these are integers since we will be using them UNQUOTED
     // Force hidden content type selection if only 1 content type was initially configured
     $canseltypes = count($contenttypes) == 1 ? 0 : $canseltypes;
     // Type data and configuration (parameters), if no content types specified then all will be retrieved
     $typeData = flexicontent_db::getTypeData(implode(",", $contenttypes));
     $contenttypes = array();
     foreach ($typeData as $tdata) {
         $contenttypes[] = $tdata->id;
     }
     // Get Content Types to use either those currently selected in the Search Form, or those hard-configured in the search menu item
     if ($canseltypes) {
         $form_contenttypes = JRequest::getVar('contenttypes', array());
         // Sanitize them
         $form_contenttypes = !is_array($form_contenttypes) ? array($form_contenttypes) : $form_contenttypes;
         $form_contenttypes = array_unique(array_map('intval', $form_contenttypes));
         // Make sure these are integers since we will be using them UNQUOTED
         $_contenttypes = array_intersect($contenttypes, $form_contenttypes);
         if (!empty($_contenttypes)) {
             $contenttypes = $_contenttypes;
         }
         // catch empty case: no content types were given or not-allowed content types were passed
     }
     // Check for zero content type (can occur during sanitizing content ids to integers)
     if (!empty($contenttypes)) {
         foreach ($contenttypes as $i => $v) {
             if (!strlen($contenttypes[$i])) {
                 unset($contenttypes[$i]);
             }
         }
     }
     // Type based seach, get a single content type (first one, if more than 1 were given ...)
     if ($type_based_search && !empty($contenttypes)) {
         $single_contenttype = reset($contenttypes);
         $contenttypes = array($single_contenttype);
     } else {
         $single_contenttype = false;
     }
     // *************************************
     // Text Search Fields of the search form
     // *************************************
     if (!$txtmode) {
         $txtflds = array();
         $fields_text = array();
     } else {
         $txtflds = '';
         if ($show_txtfields) {
             if ($show_txtfields == 1) {
                 $txtflds = $single_contenttype ? $typeData[$single_contenttype]->params->get('searchable', '') : '';
             } else {
                 $txtflds = $params->get('txtflds', '');
             }
         }
         // Sanitize them
         $txtflds = preg_replace("/[\"'\\\\]/u", "", $txtflds);
         $txtflds = array_unique(preg_split("/\\s*,\\s*/u", $txtflds));
         if (!strlen($txtflds[0])) {
             unset($txtflds[0]);
         }
         // Create a comma list of them
         $txtflds_list = count($txtflds) ? "'" . implode("','", $txtflds) . "'" : '';
         // Retrieve field properties/parameters, verifying the support to be used as Text Search Fields
         // This will return all supported fields if field limiting list is empty
         $fields_text = FlexicontentFields::getSearchFields($key = 'id', $indexer = 'advanced', $txtflds_list, $contenttypes, $load_params = true, 0, 'search');
         if (empty($fields_text)) {
             // all entries of field limiting list were invalid , get ALL
             if (!empty($contenttypes)) {
                 $fields_text = FlexicontentFields::getSearchFields($key = 'id', $indexer = 'advanced', null, $contenttypes, $load_params = true, 0, 'search');
             } else {
                 $fields_text = array();
             }
         }
     }
     // ********************************
     // Filter Fields of the search form
     // ********************************
     // Get them from type configuration or from search menu item
     $filtflds = '';
     if ($show_filters) {
         if ($show_filters == 1) {
             $filtflds = $single_contenttype ? $typeData[$single_contenttype]->params->get('filters', '') : '';
         } else {
             $filtflds = $params->get('filtflds', '');
         }
     }
     // Sanitize them
     $filtflds = preg_replace("/[\"'\\\\]/u", "", $filtflds);
     $filtflds = array_unique(preg_split("/\\s*,\\s*/u", $filtflds));
     if (!strlen($filtflds[0])) {
         unset($filtflds[0]);
     }
     // Create a comma list of them
     $filtflds_list = count($filtflds) ? "'" . implode("','", $filtflds) . "'" : '';
     // Retrieve field properties/parameters, verifying the support to be used as Filter Fields
     // This will return all supported fields if field limiting list is empty
     if (count($filtflds)) {
         $filters_tmp = FlexicontentFields::getSearchFields($key = 'name', $indexer = 'advanced', $filtflds_list, $contenttypes, $load_params = true, 0, 'filter');
         // Use custom order
         $filters = array();
         if ($canseltypes && $show_filters) {
             foreach ($filtflds as $field_name) {
                 if (empty($filters_tmp[$field_name])) {
                     continue;
                 }
                 $filter_id = $filters_tmp[$field_name]->id;
                 $filters[$filter_id] = $filters_tmp[$field_name];
             }
         } else {
             foreach ($filters_tmp as $filter) {
                 $filters[$filter->id] = $filter;
                 // index by filter_id in this case too (for consistency, although we do not use the array index ?)
             }
         }
         unset($filters_tmp);
     }
     // If configured filters were not found/invalid for the current content type(s)
     // then retrieve all fields marked as filterable for the give content type(s) this is useful to list per content type filters automatically, even when not set or misconfigured
     if (empty($filters)) {
         if (!empty($contenttypes)) {
             $filters = FlexicontentFields::getSearchFields($key = 'id', $indexer = 'advanced', null, $contenttypes, $load_params = true, 0, 'filter');
         } else {
             $filters = array();
         }
     }
     // **********************
     // Load Plugin parameters
     // **********************
     $plugin = JPluginHelper::getPlugin('search', 'flexiadvsearch');
     $pluginParams = new JRegistry($plugin->params);
     // Shortcuts for plugin parameters
     $search_limit = $params->get('search_limit', $pluginParams->get('search_limit', 20));
     // Limits the returned results of this seach plugin
     $filter_lang = $params->get('filter_lang', $pluginParams->get('filter_lang', 1));
     // Language filtering enabled
     $search_archived = $params->get('search_archived', $pluginParams->get('search_archived', 1));
     // Include archive items into the search
     $browsernav = $params->get('browsernav', $pluginParams->get('browsernav', 2));
     // Open search in window (for value 1)
     // ***************************************************************************************************************
     // Varous other variable USED in the SQL query like (a) current frontend language and (b) -this- plugin specific ordering, (c) null / now dates, (d) etc
     // ***************************************************************************************************************
     // Get current frontend language (fronted user selected)
     $lang = flexicontent_html::getUserCurrentLang();
     // NULL and CURRENT dates,
     // NOTE: the current date needs to use built-in MYSQL function, otherwise filter caching can not work because the CURRENT DATETIME is continuously different !!!
     //$now = JFactory::getDate()->toSql();
     $_nowDate = 'UTC_TIMESTAMP()';
     //$db->Quote($now);
     $nullDate = $db->getNullDate();
     // Section name
     $searchFlexicontent = JText::_('FLEXICONTENT');
     // REMOVED / COMMENTED OUT this feature:
     // Require any OR all Filters ... this can be user selectable
     //$show_filtersop = $params->get('show_filtersop', 1);
     //$default_filtersop = $params->get('default_filtersop', 'all');
     //$FILTERSOP = !$show_filtersop ? $default_filtersop : JRequest::getVar('filtersop', $default_filtersop);
     // ****************************************
     // Create WHERE clause part for Text Search
     // ****************************************
     $si_tbl = !$txtmode ? 'flexicontent_items_ext' : 'flexicontent_advsearch_index';
     $search_prefix = JComponentHelper::getParams('com_flexicontent')->get('add_search_prefix') ? 'vvv' : '';
     // SEARCH WORD Prefix
     $text = preg_replace('/(\\b[^\\s,\\.]+\\b)/u', $search_prefix . '$0', trim($text));
     if (strlen($text)) {
         $ts = !$txtmode ? 'ie' : 'ts';
         $escaped_text = $db->escape($text, true);
         $quoted_text = $db->Quote($escaped_text, false);
         switch ($phrase) {
             case 'natural':
                 if ($filter_word_like_any) {
                     $_text_match = ' LOWER (' . $ts . '.search_index) LIKE ' . $db->Quote('%' . $escaped_text . '%', false);
                 } else {
                     $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ') ';
                 }
                 break;
             case 'natural_expanded':
                 $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ' WITH QUERY EXPANSION) ';
                 break;
             case 'exact':
                 $words = preg_split('/\\s\\s*/u', $text);
                 $stopwords = array();
                 $shortwords = array();
                 if (!$search_prefix) {
                     $words = flexicontent_db::removeInvalidWords($words, $stopwords, $shortwords, $si_tbl, 'search_index', $isprefix = 0);
                 }
                 if (empty($words)) {
                     // All words are stop-words or too short, we could try to execute a query that only contains a LIKE %...% , but it would be too slow
                     JRequest::setVar('ignoredwords', implode(' ', $stopwords));
                     JRequest::setVar('shortwords', implode(' ', $shortwords));
                     $_text_match = ' 0=1 ';
                 } else {
                     // speed optimization ... 2-level searching: first require ALL words, then require exact text
                     $newtext = '+' . implode(' +', $words);
                     $quoted_text = $db->escape($newtext, true);
                     $quoted_text = $db->Quote($quoted_text, false);
                     $exact_text = $db->Quote('%' . $escaped_text . '%', false);
                     $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ' IN BOOLEAN MODE) AND ' . $ts . '.search_index LIKE ' . $exact_text;
                 }
                 break;
             case 'all':
                 $words = preg_split('/\\s\\s*/u', $text);
                 $stopwords = array();
                 $shortwords = array();
                 if (!$search_prefix) {
                     $words = flexicontent_db::removeInvalidWords($words, $stopwords, $shortwords, $si_tbl, 'search_index', $isprefix = 1);
                 }
                 JRequest::setVar('ignoredwords', implode(' ', $stopwords));
                 JRequest::setVar('shortwords', implode(' ', $shortwords));
                 $newtext = '+' . implode('* +', $words) . '*';
                 $quoted_text = $db->escape($newtext, true);
                 $quoted_text = $db->Quote($quoted_text, false);
                 $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ' IN BOOLEAN MODE) ';
                 break;
             case 'any':
             default:
                 if ($filter_word_like_any) {
                     $_text_match = ' LOWER (' . $ts . '.search_index) LIKE ' . $db->Quote('%' . $escaped_text . '%', false);
                 } else {
                     $words = preg_split('/\\s\\s*/u', $text);
                     $stopwords = array();
                     $shortwords = array();
                     if (!$search_prefix) {
                         $words = flexicontent_db::removeInvalidWords($words, $stopwords, $shortwords, $si_tbl, 'search_index', $isprefix = 1);
                     }
                     JRequest::setVar('ignoredwords', implode(' ', $stopwords));
                     JRequest::setVar('shortwords', implode(' ', $shortwords));
                     $newtext = implode('* ', $words) . '*';
                     $quoted_text = $db->escape($newtext, true);
                     $quoted_text = $db->Quote($quoted_text, false);
                     $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ' IN BOOLEAN MODE) ';
                 }
                 break;
         }
         // Construct TEXT SEARCH limitation SUB-QUERY (contained in a AND-WHERE clause)
         $text_where = ' AND ' . $_text_match;
     } else {
         $text_where = '';
     }
     // *******************
     // Create ORDER clause
     // *******************
     // FLEXIcontent search view, use FLEXIcontent ordering
     $orderby_join = '';
     $orderby_col = '';
     if (JRequest::getVar('option') == 'com_flexicontent') {
         $order = '';
         $orderby = flexicontent_db::buildItemOrderBy($params, $order, $_request_var = 'orderby', $_config_param = 'orderby', $_item_tbl_alias = 'i', $_relcat_tbl_alias = 'rel', $_default_order = '', $_default_order_dir = '', $sfx = '', $support_2nd_lvl = false);
         // Create JOIN for ordering items by a custom field (Level 1)
         if ('field' == $order[1]) {
             $orderbycustomfieldid = (int) $params->get('orderbycustomfieldid', 0);
             $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f ON f.item_id = i.id AND f.field_id=' . $orderbycustomfieldid;
         }
         // Create JOIN for ordering items by a custom field (Level 2)
         if ('field' == $order[2]) {
             $orderbycustomfieldid_2nd = (int) $params->get('orderbycustomfieldid' . '_2nd', 0);
             $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f2 ON f2.item_id = i.id AND f2.field_id=' . $orderbycustomfieldid_2nd;
         }
         // Create JOIN for ordering items by author's name
         if (in_array('author', $order) || in_array('rauthor', $order)) {
             $orderby_col = '';
             $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by';
         }
         // Create JOIN for ordering items by a most commented
         if (in_array('commented', $order)) {
             $orderby_col = ', count(com.object_id) AS comments_total';
             $orderby_join .= ' LEFT JOIN #__jcomments AS com ON com.object_id = i.id';
         }
         // Create JOIN for ordering items by a most rated
         if (in_array('rated', $order)) {
             $orderby_col = ', (cr.rating_sum / cr.rating_count) * 20 AS votes';
             $orderby_join .= ' LEFT JOIN #__content_rating AS cr ON cr.content_id = i.id';
         }
         // Create JOIN for ordering items by their ordering attribute (in item's main category)
         if (in_array('order', $order)) {
             $orderby_join .= ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id AND rel.catid = i.catid';
         }
     } else {
         switch ($ordering) {
             //case 'relevance': $orderby = ' ORDER BY score DESC, i.title ASC'; break;
             case 'oldest':
                 $orderby = 'i.created ASC';
                 break;
             case 'popular':
                 $orderby = 'i.hits DESC';
                 break;
             case 'alpha':
                 $orderby = 'i.title ASC';
                 break;
             case 'category':
                 $orderby = 'c.title ASC, i.title ASC';
                 break;
             case 'newest':
                 $orderby = 'i.created DESC';
                 break;
             default:
                 $orderby = 'i.created DESC';
                 break;
         }
         $orderby = ' ORDER BY ' . $orderby;
     }
     // ****************************************************************************************
     // Create JOIN clause and WHERE clause part for filtering by current (viewing) access level
     // ****************************************************************************************
     $joinaccess = '';
     $andaccess = '';
     $select_access = '';
     // Extra access columns for main category and content type (item access will be added as 'access')
     $select_access .= ',  c.access as category_access, ty.access as type_access';
     if (!$show_noauth) {
         // User not allowed to LIST unauthorized items
         $aid_arr = JAccess::getAuthorisedViewLevels($user->id);
         $aid_list = implode(",", $aid_arr);
         $andaccess .= ' AND ty.access IN (0,' . $aid_list . ')';
         $andaccess .= ' AND  c.access IN (0,' . $aid_list . ')';
         $andaccess .= ' AND  i.access IN (0,' . $aid_list . ')';
         $select_access .= ', 1 AS has_access';
     } else {
         // Access Flags for: content type, main category, item
         $aid_arr = JAccess::getAuthorisedViewLevels($user->id);
         $aid_list = implode(",", $aid_arr);
         $select_access .= ', ' . ' CASE WHEN ' . '  ty.access IN (' . $aid_list . ') AND ' . '   c.access IN (' . $aid_list . ') AND ' . '   i.access IN (' . $aid_list . ') ' . ' THEN 1 ELSE 0 END AS has_access';
     }
     // **********************************************************************************************************************************************************
     // Create WHERE clause part for filtering by current active language, and current selected contend types ( !! although this is possible via a filter too ...)
     // **********************************************************************************************************************************************************
     $andlang = '';
     if ($app->isSite() && (FLEXI_FISH || FLEXI_J16GE && $app->getLanguageFilter()) && $filter_lang) {
         $andlang .= ' AND ( ie.language LIKE ' . $db->Quote($lang . '%') . (FLEXI_J16GE ? ' OR ie.language="*" ' : '') . ' ) ';
     }
     // Filter by currently selected content types
     $andcontenttypes = count($contenttypes) ? ' AND ie.type_id IN (' . implode(",", $contenttypes) . ') ' : '';
     // ***********************************************************************
     // Create the AND-WHERE clause parts for the currentl active Field Filters
     // ***********************************************************************
     $return_sql = 2;
     $filters_where = array();
     foreach ($filters as $field) {
         // Get value of current filter, and SKIP it if value is EMPTY
         $filtervalue = JRequest::getVar('filter_' . $field->id, '');
         $empty_filtervalue_array = is_array($filtervalue) && !strlen(trim(implode('', $filtervalue)));
         $empty_filtervalue_string = !is_array($filtervalue) && !strlen(trim($filtervalue));
         if ($empty_filtervalue_array || $empty_filtervalue_string) {
             continue;
         }
         // Call field filtering of advanced search to find items matching the field filter (an SQL SUB-QUERY is returned)
         $field_filename = $field->iscore ? 'core' : $field->field_type;
         $filtered = FLEXIUtilities::call_FC_Field_Func($field_filename, 'getFilteredSearch', array(&$field, &$filtervalue, &$return_sql));
         // An empty return value means no matching values were found
         $filtered = empty($filtered) ? ' AND 0 ' : $filtered;
         // A string mean a subquery was returned, while an array means that item ids we returned
         $filters_where[$field->id] = is_array($filtered) ? ' AND i.id IN (' . implode(',', $filtered) . ')' : $filtered;
         /*if ($filters_where[$field->id]) {
         			echo "\n<br/>Filter:". $field->name ." : ";   print_r($filtervalue);
         			echo "<br>".$filters_where[$field->id]."<br/>";
         		}*/
     }
     //echo "\n<br/><br/>Filters Active: ". count($filters_where)."<br/>";
     //echo "<pre>"; print_r($filters_where);
     //exit;
     // ******************************************************
     // Create Filters JOIN clauses and AND-WHERE clause parts
     // ******************************************************
     // JOIN clause - USED - to limit returned 'text' to the text of TEXT-SEARCHABLE only fields ... (NOT shared with filters)
     if (!$txtmode) {
         $onBasic_textsearch = $text_where;
         $onAdvanced_textsearch = '';
         $join_textsearch = '';
         $join_textfields = '';
     } else {
         $onBasic_textsearch = '';
         $onAdvanced_textsearch = $text_where;
         $join_textsearch = ' JOIN #__flexicontent_advsearch_index as ts ON ts.item_id = i.id ' . (count($fields_text) ? 'AND ts.field_id IN (' . implode(',', array_keys($fields_text)) . ')' : '');
         $join_textfields = ' JOIN #__flexicontent_fields as f ON f.id=ts.field_id';
     }
     // JOIN clauses ... (shared with filters)
     $join_clauses = '' . ' JOIN #__categories AS c ON c.id = i.catid' . ' JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id' . ' JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id';
     $join_clauses_with_text = '' . ' JOIN #__categories AS c ON c.id = i.catid' . ' JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id' . $onBasic_textsearch . ' JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id' . ($text_where ? $join_textsearch . $onAdvanced_textsearch . $join_textfields : '');
     // AND-WHERE sub-clauses ... (shared with filters)
     $where_conf = ' WHERE 1 ' . ' AND i.state IN (1,-5' . ($search_archived ? ',' . (FLEXI_J16GE ? 2 : -1) : '') . ') ' . ' AND c.published = 1 ' . ' AND ( i.publish_up = ' . $db->Quote($nullDate) . ' OR i.publish_up <= ' . $_nowDate . ' )' . ' AND ( i.publish_down = ' . $db->Quote($nullDate) . ' OR i.publish_down >= ' . $_nowDate . ' )' . $andaccess . $andlang . $andcontenttypes;
     // AND-WHERE sub-clauses for text search ... (shared with filters)
     $and_where_filters = count($filters_where) ? implode(" ", $filters_where) : '';
     // ************************************************
     // Set variables used by filters creation mechanism
     // ************************************************
     global $fc_searchview;
     $fc_searchview['join_clauses'] = $join_clauses;
     $fc_searchview['join_clauses_with_text'] = $join_clauses_with_text;
     $fc_searchview['where_conf_only'] = $where_conf;
     // WHERE of the view (mainly configuration dependent)
     $fc_searchview['filters_where'] = $filters_where;
     // WHERE of the filters
     $fc_searchview['search'] = $text_where;
     // WHERE for text search
     $fc_searchview['params'] = $params;
     // view's parameters
     // *****************************************************************************************************
     // Execute search query.  NOTE this is skipped it if (a) no text-search and no (b) no filters are active
     // *****************************************************************************************************
     // Do not check for 'contentypes' this are based on configuration and not on form submitted data,
     // considering contenttypes or other configuration based parameters, will return all items on initial search view display !
     if (!count($filters_where) && !strlen($text)) {
         return array();
     }
     $print_logging_info = $params->get('print_logging_info');
     if ($print_logging_info) {
         global $fc_run_times;
         $start_microtime = microtime(true);
     }
     // *****************************************
     // Overcome possible group concat limitation
     // *****************************************
     $query = "SET SESSION group_concat_max_len = 9999999";
     $db->setQuery($query);
     $db->execute();
     // *************
     // Get the items
     // *************
     $query = 'SELECT SQL_CALC_FOUND_ROWS i.id' . $orderby_col . ' FROM #__content AS i' . $join_clauses_with_text . $orderby_join . $joinaccess . $where_conf . $and_where_filters . ' GROUP BY i.id ' . $orderby;
     //echo "Adv search plugin main SQL query: ".nl2br($query)."<br/><br/>";
     // NOTE: The plugin will return a PRECONFIGURED limited number of results, the SEARCH VIEW to do the pagination, splicing (appropriately) the data returned by all search plugins
     try {
         // Get items, we use direct query because some extensions break the SQL_CALC_FOUND_ROWS, so let's bypass them (at this point it is OK)
         // *** Usage of FOUND_ROWS() will fail when (e.g.) Joom!Fish or Falang are installed, in this case we will be forced to re-execute the query ...
         // PLUS, we don't need Joom!Fish or Falang layer at --this-- STEP which may slow down the query considerably in large sites
         $query_limited = $query . ' LIMIT ' . $search_limit . ' OFFSET 0';
         $rows = flexicontent_db::directQuery($query_limited);
         $item_ids = array();
         foreach ($rows as $row) {
             $item_ids[] = $row->id;
         }
         // Get current items total for pagination
         $db->setQuery("SELECT FOUND_ROWS()");
         $fc_searchview['view_total'] = $db->loadResult();
         $app->setUserState('fc_view_total_' . $view, $fc_searchview['view_total']);
     } catch (Exception $e) {
         // Get items via normal joomla SQL layer
         $db->setQuery(str_replace('SQL_CALC_FOUND_ROWS', '', $query), 0, $search_limit);
         $item_ids = $db->loadColumn(0);
     }
     if (!count($item_ids)) {
         return array();
     }
     // No items found
     // *****************
     // Get the item data
     // *****************
     $query_data = 'SELECT i.id, i.title AS title, i.created, i.id AS fc_item_id, i.access, ie.type_id, i.language' . (!$txtmode ? ', ie.search_index AS text' : ', GROUP_CONCAT(ts.search_index ORDER BY f.ordering ASC SEPARATOR \' \') AS text') . ', CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(\':\', i.id, i.alias) ELSE i.id END as slug' . ', CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as categoryslug' . ', CONCAT_WS( " / ", ' . $db->Quote($searchFlexicontent) . ', c.title, i.title ) AS section' . $select_access . ' FROM #__content AS i' . $join_clauses . $join_textsearch . $join_textfields . ' WHERE i.id IN (' . implode(',', $item_ids) . ') ' . ' GROUP BY i.id ' . ' ORDER BY FIELD(i.id, ' . implode(',', $item_ids) . ')';
     //echo nl2br($query)."<br/><br/>";
     $db->setQuery($query_data);
     $list = $db->loadObjectList();
     if ($db->getErrorNum()) {
         echo $db->getErrorMsg();
     }
     if ($print_logging_info) {
         @($fc_run_times['search_query_runtime'] += round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10);
     }
     // *************************************
     // Create item links and other variables
     // *************************************
     //echo "<pre>"; print_r($list); echo "</pre>";
     if ($list) {
         if (count($list) >= $search_limit) {
             $app->setUserState('fc_view_limit_max_' . $view, $search_limit);
         }
         $item_cats = FlexicontentFields::_getCategories($list);
         foreach ($list as $key => $item) {
             $item->text = preg_replace('/\\b' . $search_prefix . '/', '', $item->text);
             $item->categories = isset($item_cats[$item->id]) ? $item_cats[$item->id] : array();
             // in case of item categories missing
             // If joomla article view is allowed allowed and then search view may optional create Joomla article links
             if ($typeData[$item->type_id]->params->get('allow_jview', 0) && $typeData[$item->type_id]->params->get('search_jlinks', 1)) {
                 $item->href = JRoute::_(ContentHelperRoute::getArticleRoute($item->slug, $item->categoryslug, $item->language));
             } else {
                 $item->href = JRoute::_(FlexicontentHelperRoute::getItemRoute($item->slug, $item->categoryslug, 0, $item));
             }
             $item->browsernav = $browsernav;
         }
     }
     return $list;
 }
Esempio n. 3
0
 /**
  * Method to get Data
  *
  * @access public
  * @return mixed
  */
 function getData()
 {
     $format = JRequest::getCmd('format', null);
     $cparams = $this->_params;
     $print_logging_info = $cparams->get('print_logging_info');
     if ($print_logging_info) {
         global $fc_run_times;
     }
     // Allow limit zero to achieve a category view without items
     $limit = (int) $this->getState('limit');
     $limitstart = (int) $this->getState('limitstart');
     if ($limit <= 0) {
         $this->_data = array();
     } else {
         if ($this->_data === null) {
             if ($print_logging_info) {
                 $start_microtime = microtime(true);
             }
             // Load the content if it doesn't already exist
             // 1, create full query: filter, ordered, limited
             $query = $this->_buildQuery();
             try {
                 // 2, get items, we use direct query because some extensions break the SQL_CALC_FOUND_ROWS, so let's bypass them (at this point it is OK)
                 // *** Usage of FOUND_ROWS() will fail when (e.g.) Joom!Fish or Falang are installed, in this case we will be forced to re-execute the query ...
                 // PLUS, we don't need Joom!Fish or Falang layer at --this-- STEP which may slow down the query considerably in large sites
                 $query_limited = $query . ' LIMIT ' . $limit . ' OFFSET ' . $limitstart;
                 $rows = flexicontent_db::directQuery($query_limited);
                 $query_ids = array();
                 foreach ($rows as $row) {
                     $query_ids[] = $row->id;
                 }
                 //$this->_db->setQuery($query, $limitstart, $limit);
                 //$query_ids = $this->_db->loadColumn();
                 // 3, get current items total for pagination
                 $this->_db->setQuery("SELECT FOUND_ROWS()");
                 $this->_total = $this->_db->loadResult();
             } catch (Exception $e) {
                 // 2, get items via normal joomla SQL layer
                 $this->_db->setQuery($query, $limitstart, $limit);
                 $query_ids = $this->_db->loadColumn();
                 if ($this->_db->getErrorNum()) {
                     JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($this->_db->getErrorMsg()), 'error');
                 }
                 // 3, get current items total for pagination
                 if (count($query_ids)) {
                     if (!$this->_total) {
                         $this->getTotal();
                     }
                 } else {
                     $this->_total = 0;
                 }
             }
             // Assign total number of items found this will be used to decide whether to do item counting per filter value
             global $fc_catview;
             $fc_catview['view_total'] = $this->_total;
             /*if ((int)$this->getState('limitstart') < (int)$this->_total) {
             			$this->_data = $this->_getList( $query, $limitstart, $limit );
             		} else {
             			$this->setState('limitstart',0);
             			$this->setState('start',0);
             			JRequest::setVar('start',0);
             			JRequest::setVar('limitstart',0);
             			$this->_data = $this->_getList( $query, 0, $limit );
             		}*/
             // 4, get item data
             if (count($query_ids)) {
                 $query = $this->_buildQuery($query_ids);
             }
             $_data = array();
             if (count($query_ids)) {
                 $this->_db->setQuery($query);
                 $_data = $this->_db->loadObjectList('id');
                 if ($this->_db->getErrorNum()) {
                     JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($this->_db->getErrorMsg()), 'error');
                 }
             }
             // 5, reorder items
             $this->_data = array();
             if ($_data) {
                 foreach ($query_ids as $item_id) {
                     $this->_data[] = $_data[$item_id];
                 }
             }
             // Get Original content ids for creating some untranslatable fields that have share data (like shared folders)
             flexicontent_db::getOriginalContentItemids($this->_data);
             if ($print_logging_info) {
                 @($fc_run_times['execute_main_query'] += round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10);
             }
         }
     }
     // maybe removed in the future, this is useful in places that item data need to be retrieved again because item object was not given
     global $fc_list_items;
     foreach ($this->_data as $_item) {
         $fc_list_items[$_item->id] = $_item;
     }
     return $this->_data;
 }
Esempio n. 4
0
 /**
  * Method to load item data
  *
  * @access	private
  * @return	boolean	True on success
  * @since	1.0
  */
 function _loadItem($no_cache = false, $force_version = false)
 {
     if (!$this->_id) {
         return false;
     }
     // Only try to load existing item
     // Cache items retrieved, we can retrieve multiple items, for this purpose
     // (a) temporarily set JRequest variable -version- to specify loaded version (set to zero to use latest )
     // (b1) use member function function setId($id, $currcatid=0) to change primary key and then call getItem()
     // (b2) or call getItem($pk, $check_view_access=true) passing the item id and maybe also disabling read access checkings, to avoid unwanted messages/errors
     static $items = array();
     if ($no_cache) {
         // Clear item to make sure it is reloaded
         $this->_item = null;
     } else {
         if (isset($items[$this->_id])) {
             $this->_item =& $items[$this->_id];
             return (bool) $this->_item;
         }
     }
     static $unapproved_version_notice;
     $db = $this->_db;
     $app = JFactory::getApplication();
     $user = JFactory::getUser();
     $cparams = $this->_cparams;
     $task = JRequest::getVar('task', false);
     $layout = JRequest::getVar('layout', false);
     $view = JRequest::getVar('view', false);
     $option = JRequest::getVar('option', false);
     $use_versioning = $cparams->get('use_versioning', 1);
     $allow_current_version = true;
     $editjf_translations = $cparams->get('editjf_translations', 0);
     // *********************************************************************************************************
     // Retrieve item if not already retrieved, null indicates cleared item data, e.g. because of changed item id
     // *********************************************************************************************************
     if ($this->_item === null) {
         //*****************************************************
         // DECIDE VERSION and GENERATE VERSION RELATED MESSAGES
         //*****************************************************
         // Variables controlling the version loading logic
         $loadcurrent = JRequest::getVar('loadcurrent', false, 'request', 'boolean');
         // loadcurrent request flag, ignored if version specified
         $preview = JRequest::getVar('preview', false, 'request', 'boolean');
         // preview request flag for viewing latest (and possibly unapproved) version in frontend
         $version = JRequest::getVar('version', 0, 'request', 'int');
         // the item version to load
         // -- Decide the version to load: (a) the one specified by request or (b) the current one or (c) the latest one
         $current_version = FLEXIUtilities::getCurrentVersions($this->_id, true, $force = true);
         // Get current item version
         $last_version = FLEXIUtilities::getLastVersions($this->_id, true, $force = true);
         // Get last version (=latest one saved, highest version id),
         // NOTE: Setting version to zero indicates to load the current version from the normal tables and not the versioning table
         if (!$use_versioning) {
             // Force version to zero (load current version), when not using versioning mode
             $version = 0;
         } else {
             if ($force_version !== false) {
                 $version = $force_version == -1 ? $last_version : $force_version;
             } else {
                 if ($version == 0) {
                     // version request variable was NOT SET ... We need to decide to load current (version zero) or latest
                     if ($app->isAdmin() || $task == 'edit' && $option == 'com_flexicontent') {
                         // Catch cases (a) when we enable versioning mode after an item has been saved in unversioning mode, (b) loadcurrent flag is set
                         // in these case we will load CURRENT version instead of the default for the item edit form which is the LATEST (for backend/fontend)
                         $version = $current_version >= $last_version || $loadcurrent ? 0 : $last_version;
                     } else {
                         // In frontend item display the current version must be shown unless preview flag is set
                         $version = !$preview ? 0 : $last_version;
                     }
                 } else {
                     if ($version == $current_version) {
                         // Current version number given, the data from the versioning table should be the same as the data from normal tables
                         // we do not force $version to ZERO to allow testing the field data of current version from the versioning table
                         if (!$allow_current_version) {
                             $version = 0;
                         }
                         // Force zero to retrieve unversioned data
                     }
                 }
             }
         }
         // Check if not loading the current version while we are in edit form, and raise a notice to inform the user
         if ($version && $version != $current_version && $task == 'edit' && $option == 'com_flexicontent' && !$unapproved_version_notice) {
             $unapproved_version_notice = 1;
             if (!$app->isAdmin()) {
                 JError::raiseNotice(10, JText::_('FLEXI_LOADING_UNAPPROVED_VERSION_NOTICE'));
             } else {
                 JError::raiseNotice(10, JText::_('FLEXI_LOADING_UNAPPROVED_VERSION_NOTICE') . ' :: ' . JText::sprintf('FLEXI_LOADED_VERSION_INFO_NOTICE', $version, $current_version));
             }
         }
         try {
             if ($app->isAdmin()) {
                 // **********************
                 // Item Retrieval BACKEND
                 // **********************
                 $item = $this->getTable('flexicontent_items', '');
                 $result = $item->load($this->_id);
                 // try loading existing item data
                 if ($result === false) {
                     return false;
                 }
             } else {
                 // ***********************
                 // Item Retrieval FRONTEND
                 // ***********************
                 // Tables needed to be joined for calculating access
                 $joinaccess = '';
                 // Extra access columns for main category and content type (item access will be added as 'access')
                 $select_access = 'mc.access as category_access, ty.access as type_access';
                 // Access Flags for: content type, main category, item
                 if (FLEXI_J16GE) {
                     $aid_arr = JAccess::getAuthorisedViewLevels($user->id);
                     $aid_list = implode(",", $aid_arr);
                     $select_access .= ', CASE WHEN ty.access IN (0,' . $aid_list . ') THEN 1 ELSE 0 END AS has_type_access';
                     $select_access .= ', CASE WHEN mc.access IN (0,' . $aid_list . ') THEN 1 ELSE 0 END AS has_mcat_access';
                     $select_access .= ', CASE WHEN  i.access IN (0,' . $aid_list . ') THEN 1 ELSE 0 END AS has_item_access';
                 } else {
                     $aid = (int) $user->get('aid');
                     if (FLEXI_ACCESS) {
                         $joinaccess .= ' LEFT JOIN #__flexiaccess_acl AS gt ON ty.id = gt.axo AND gt.aco = "read" AND gt.axosection = "type"';
                         $joinaccess .= ' LEFT JOIN #__flexiaccess_acl AS gc ON mc.id = gc.axo AND gc.aco = "read" AND gc.axosection = "category"';
                         $joinaccess .= ' LEFT JOIN #__flexiaccess_acl AS gi ON  i.id = gi.axo AND gi.aco = "read" AND gi.axosection = "item"';
                         $select_access .= ', CASE WHEN (gt.aro IN ( ' . $user->gmid . ' ) OR ty.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_type_access';
                         $select_access .= ', CASE WHEN (gc.aro IN ( ' . $user->gmid . ' ) OR mc.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_mcat_access';
                         $select_access .= ', CASE WHEN (gi.aro IN ( ' . $user->gmid . ' ) OR  i.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_item_access';
                     } else {
                         $select_access .= ', CASE WHEN (ty.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_type_access';
                         $select_access .= ', CASE WHEN (mc.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_mcat_access';
                         $select_access .= ', CASE WHEN ( i.access <= ' . (int) $aid . ') THEN 1 ELSE 0 END AS has_item_access';
                     }
                     $select_access .= ', ';
                 }
                 // SQL date strings, current date and null date
                 $nowDate = $db->Quote(FLEXI_J16GE ? JFactory::getDate()->toSql() : JFactory::getDate()->toMySQL());
                 $nullDate = $db->Quote($db->getNullDate());
                 // Decide to limit to CURRENT CATEGORY
                 $limit_to_cid = $this->_cid ? ' AND rel.catid = ' . (int) $this->_cid : ' AND rel.catid = i.catid';
                 if (FLEXI_J16GE) {
                     // Initialize query
                     $query = $db->getQuery(true);
                     $query->select('i.*, ie.*');
                     // Item basic and extended data
                     $query->select($select_access);
                     // Access Columns and Access Flags for: content type, main category, item
                     if ($version) {
                         $query->select('ver.version_id');
                     }
                     // Versioned item viewing
                     $query->select('c.id AS catid, i.catid as maincatid');
                     // Current category id and Main category id
                     $query->select('c.title AS category_title, c.alias AS category_alias, c.lft,c.rgt');
                     // Current category data
                     $query->select('ty.name AS typename, ty.alias as typealias');
                     // Content Type data, and author data
                     $query->select('u.name AS author');
                     // Author data
                     // Rating count, Rating & Score
                     $query->select('v.rating_count as rating_count, ROUND( v.rating_sum / v.rating_count ) AS rating, ((v.rating_sum / v.rating_count)*20) as score');
                     // Item and Current Category slugs (for URL)
                     $query->select('CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(\':\', i.id, i.alias) ELSE i.id END as slug');
                     $query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as categoryslug');
                     // Publication Scheduled / Expired Flags
                     $query->select('CASE WHEN i.publish_up = ' . $nullDate . ' OR i.publish_up <= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_scheduled');
                     $query->select('CASE WHEN i.publish_down = ' . $nullDate . ' OR i.publish_down >= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_expired');
                     // From content table, and extended item table, content type table, user table, rating table, categories relation table
                     $query->from('#__content AS i');
                     $query->join('LEFT', '#__flexicontent_items_ext AS ie ON ie.item_id = i.id');
                     $query->join('LEFT', '#__flexicontent_types AS ty ON ie.type_id = ty.id');
                     $query->join('LEFT', '#__users AS u on u.id = i.created_by');
                     $query->join('LEFT', '#__content_rating AS v ON i.id = v.content_id');
                     $query->join('LEFT', '#__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id' . $limit_to_cid);
                     // Join twice on category table, once for current category and once for item's main category
                     $query->join('LEFT', '#__categories AS c on c.id = rel.catid');
                     // All item's categories
                     $query->join('LEFT', '#__categories AS mc on mc.id = i.catid');
                     // Item's main category
                     // HANDLE J1.6+ ancestor category being unpublished, when badcats.id is not null,
                     // then the item is inside in an unpublished ancestor category, thus inaccessible
                     /*$query->select('CASE WHEN badcats.id is null THEN 1 ELSE 0 END AS ancestor_cats_published');
                     		$subquery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ';
                     		$subquery .= 'ON cat.lft BETWEEN parent.lft AND parent.rgt ';
                     		$subquery .= 'WHERE parent.extension = ' . $db->Quote('com_content');
                     		$subquery .= ' AND parent.published <= 0 GROUP BY cat.id)';
                     		$query->join('LEFT', $subquery . ' AS badcats ON badcats.id = c.id');*/
                     if ($version) {
                         // NOTE: version_id is used by field helper file to load the specified version, the reason for left join here is to verify that the version exists
                         $query->join('LEFT', '#__flexicontent_versions AS ver ON ver.item_id = i.id AND ver.version_id = ' . $db->Quote($version));
                     }
                     // Join on contact table, to get contact data of author
                     //$query = 'SHOW TABLES LIKE "' . JFactory::getApplication()->getCfg('dbprefix') . 'contact_details"';
                     //$db->setQuery($query);
                     //$contact_details_tbl_exists = (boolean) count($db->loadObjectList());
                     //if ( $contact_details_tbl_exists) {
                     //	$query->select('contact.id as contactid' ) ;
                     //	$query->join('LEFT','#__contact_details AS contact on contact.user_id = i.created_by');
                     //}
                     // Join over the categories to get parent category titles
                     //$query->select('parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias');
                     //$query->join('LEFT', '#__categories as parent ON parent.id = c.parent_id');
                     $query->where('i.id = ' . (int) $this->_id);
                     //echo $db->replacePrefix($query);
                 } else {
                     // NOTE: version_id is used by field helper file to load the specified version, the reason for left join here is to verify that the version exists
                     $version_join = $version ? ' LEFT JOIN #__flexicontent_versions AS ver ON ver.item_id = i.id AND ver.version_id = ' . $db->Quote($version) : '';
                     $where = $this->_buildItemWhere();
                     $query = 'SELECT i.*, ie.*, ' . $select_access . ($version ? 'ver.version_id,' : '') . ' c.id AS catid, i.catid as maincatid,' . ' c.published AS catpublished,' . ' c.title AS category_title, c.alias AS category_alias,' . ' ty.name as typename, ty.alias as typealias,' . ' u.name AS author, u.usertype,' . ' v.rating_count as rating_count, ROUND( v.rating_sum / v.rating_count ) AS rating, ((v.rating_sum / v.rating_count)*20) as score,' . ' CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(\':\', i.id, i.alias) ELSE i.id END as slug,' . ' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as categoryslug,' . ' CASE WHEN i.publish_up = ' . $nullDate . ' OR i.publish_up <= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_scheduled,' . ' CASE WHEN i.publish_down = ' . $nullDate . ' OR i.publish_down >= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_expired' . ' FROM #__content AS i' . ' LEFT JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id' . ' LEFT JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id' . ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id' . $limit_to_cid . ' LEFT JOIN #__categories AS c ON c.id = rel.catid' . ' LEFT JOIN #__categories AS mc ON mc.id = i.catid' . ' LEFT JOIN #__users AS u ON u.id = i.created_by' . ' LEFT JOIN #__content_rating AS v ON i.id = v.content_id' . $joinaccess . $version_join . $where;
                 }
                 $db->setQuery($query);
                 // Try to execute query directly and load the data as an object
                 if (FLEXI_FISH && $task == 'edit' && $option == 'com_flexicontent' && in_array($app->getCfg('dbtype'), array('mysqli', 'mysql'))) {
                     $data = flexicontent_db::directQuery($query);
                     $data = @$data[0];
                     //$data = $db->loadObject(null, false);   // do not, translate, this is the JoomFish overridden method of Database extended Class
                 } else {
                     $data = $db->loadObject();
                 }
                 // Check for SQL error
                 if ($db->getErrorNum()) {
                     if (FLEXI_J16GE) {
                         throw new Exception($db->getErrorMsg(), 500);
                     } else {
                         JError::raiseError(500, $db->getErrorMsg());
                     }
                 }
                 //print_r($data); exit;
                 if (!$data) {
                     return false;
                 }
                 // item not found, return
                 if ($version && !$data->version_id) {
                     JError::raiseNotice(10, JText::sprintf('NOTICE: Requested item version %d was not found', $version));
                 }
                 $item =& $data;
             }
             // -- Create the description field called 'text' by appending introtext + readmore + fulltext
             $item->text = $item->introtext;
             $item->text .= JString::strlen(trim($item->fulltext)) ? '<hr id="system-readmore" />' . $item->fulltext : "";
             //echo "<br/>Current version (Frontend Active): " . $item->version;
             //echo "<br/>Version to load: ".$version;
             //echo "<br/><b> *** db title:</b> ".$item->title;
             //echo "<br/><b> *** db text:</b> ".$item->text;
             //echo "<pre>*** item data: "; print_r($item); echo "</pre>"; exit;
             // Set number of loaded version, IMPORTANT: zero means load unversioned data
             JRequest::setVar('version', $version);
             // *************************************************************************************************
             // -- Retrieve all active site languages, and create empty item translation objects for each of them
             // *************************************************************************************************
             $nn_content_tbl = FLEXI_J16GE ? 'falang_content' : 'jf_content';
             if (FLEXI_FISH) {
                 $site_languages = FLEXIUtilities::getlanguageslist();
                 $item_translations = new stdClass();
                 foreach ($site_languages as $lang_id => $lang_data) {
                     if (!$lang_id && $item->language != '*') {
                         continue;
                     }
                     $lang_data->fields = new stdClass();
                     $item_translations->{$lang_id} = $lang_data;
                 }
             }
             // **********************************
             // Retrieve and prepare JoomFish data
             // **********************************
             if (FLEXI_FISH && $task == 'edit' && $option == 'com_flexicontent') {
                 // -- Try to retrieve all joomfish data for the current item
                 $query = "SELECT jfc.language_id, jfc.reference_field, jfc.value, jfc.published " . " FROM #__" . $nn_content_tbl . " as jfc " . " WHERE jfc.reference_table='content' AND jfc.reference_id = {$this->_id} ";
                 $db->setQuery($query);
                 $translated_fields = $db->loadObjectList();
                 if ($editjf_translations == 0 && $translated_fields) {
                     // 1:disable without warning about found translations
                     $app->enqueueMessage("3rd party Joom!Fish/Falang translations detected for current item." . " You can either enable editing them or disable this message in FLEXIcontent component configuration", 'message');
                 } else {
                     if ($editjf_translations == 2) {
                         if ($db->getErrorNum()) {
                             JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error');
                         }
                         // -- Parse translation data according to their language
                         if ($translated_fields) {
                             // Add retrieved translated item properties
                             foreach ($translated_fields as $field_data) {
                                 $item_translations->{$field_data->language_id}->fields->{$field_data->reference_field} = new stdClass();
                                 $item_translations->{$field_data->language_id}->fields->{$field_data->reference_field}->value = $field_data->value;
                                 $found_languages[$field_data->language_id] = $item_translations->{$field_data->language_id}->name;
                             }
                             //echo "<br/>Joom!Fish translations found for: " . implode(",", $found_languages);
                         }
                         foreach ($item_translations as $lang_id => $translation_data) {
                             // Default title can be somewhat long, trim it to first word, so that it is more suitable for tabs
                             list($translation_data->name) = explode(' ', trim($translation_data->name));
                             // Create text field value for all languages
                             $translation_data->fields->text = new stdClass();
                             $translation_data->fields->text->value = @$translation_data->fields->introtext->value;
                             if (JString::strlen(trim(@$translation_data->fields->fulltext->value))) {
                                 $translation_data->fields->text->value .= '<hr id="system-readmore" />' . @$translation_data->fields->fulltext->value;
                             }
                         }
                         $item->item_translations =& $item_translations;
                     }
                 }
             }
             //echo "<pre>"; print_r($item->item_translations); exit;
             // *****************************************************
             // Overwrite item fields with the requested VERSION data
             // *****************************************************
             $item->current_version = $current_version;
             $item->last_version = $last_version;
             if ($use_versioning && $version) {
                 // Overcome possible group concat limitation
                 $query = "SET SESSION group_concat_max_len = 9999999";
                 $db->setQuery($query);
                 $db->query();
                 $query = "SELECT f.id, f.name, f.field_type, GROUP_CONCAT(iv.value SEPARATOR ',') as value, count(f.id) as valuecount, iv.field_id" . " FROM #__flexicontent_items_versions as iv " . " LEFT JOIN #__flexicontent_fields as f on f.id=iv.field_id" . " WHERE iv.version='" . $version . "' AND (f.iscore=1 OR iv.field_id=-1 OR iv.field_id=-2) AND iv.item_id='" . $this->_id . "'" . " GROUP BY f.id";
                 $db->setQuery($query);
                 $fields = $db->loadObjectList();
                 $fields = $fields ? $fields : array();
                 //echo "<br/>Overwritting fields with version: $version";
                 foreach ($fields as $f) {
                     //echo "<br/><b>{$f->field_id} : ". $f->name."</b> : "; print_r($f->value);
                     // Use versioned data, by overwriting the item data
                     $fieldname = $f->name;
                     if ($f->field_type == 'hits' || $f->field_type == 'state' || $f->field_type == 'voting') {
                         // skip fields that should not have been versioned: hits, state, voting
                         continue;
                     } else {
                         if ($f->field_type == 'version') {
                             // set version variable to indicate the loaded version
                             $item->version = $version;
                         } else {
                             if ($fieldname == 'categories' || $fieldname == 'tags') {
                                 // categories and tags must have been serialized but some earlier versions did not do it,
                                 // we will check before unserializing them, otherwise they were concatenated to a single string and use explode ...
                                 $item->{$fieldname} = ($array = @unserialize($f->value)) ? $array : explode(",", $f->value);
                             } else {
                                 if ($f->field_id == -1) {
                                     if (FLEXI_FISH) {
                                         $jfdata = unserialize($f->value);
                                         $item_lang = substr($item->language, 0, 2);
                                         foreach ($item_translations as $lang_id => $translation_data) {
                                             //echo "<br/>Adding values for: ".$translation_data->shortcode;
                                             if (empty($jfdata[$translation_data->shortcode])) {
                                                 continue;
                                             }
                                             foreach ($jfdata[$translation_data->shortcode] as $fieldname => $fieldvalue) {
                                                 //echo "<br/>".$translation_data->shortcode.": $fieldname => $fieldvalue";
                                                 if ($translation_data->shortcode != $item_lang) {
                                                     $translation_data->fields->{$fieldname} = new stdClass();
                                                     $translation_data->fields->{$fieldname}->value = $fieldvalue;
                                                 } else {
                                                     $item->{$fieldname} = $fieldvalue;
                                                 }
                                             }
                                         }
                                     }
                                 } else {
                                     if ($f->field_id == -2) {
                                         // Other item properties that were versioned, such as alias, catid, meta params, attribs
                                         $item_data = unserialize($f->value);
                                         //$item->bind($item_data);
                                         foreach ($item_data as $k => $v) {
                                             $item->{$k} = $v;
                                         }
                                     } else {
                                         if ($fieldname) {
                                             // Other fields (maybe serialized or not but we do not unserialized them, this is responsibility of the field itself)
                                             $item->{$fieldname} = $f->value;
                                         }
                                     }
                                 }
                             }
                         }
                     }
                 }
                 // The text field is stored in the db as to seperate fields: introtext & fulltext
                 // So we search for the {readmore} tag and split up the text field accordingly.
                 $pattern = '#<hr\\s+id=("|\')system-readmore("|\')\\s*\\/*>#i';
                 $tagPos = preg_match($pattern, $item->text);
                 if ($tagPos == 0) {
                     $item->introtext = $item->text;
                     $item->fulltext = '';
                 } else {
                     list($item->introtext, $item->fulltext) = preg_split($pattern, $item->text, 2);
                     $item->fulltext = JString::strlen(trim($item->fulltext)) ? $item->fulltext : '';
                 }
             }
             // -- Retrieve tags field value (if not using versioning)
             if ($use_versioning && $version) {
                 // Check version value was found
                 if (!isset($item->tags) || !is_array($item->tags)) {
                     $item->tags = array();
                 }
             } else {
                 // Retrieve unversioned value
                 $query = 'SELECT DISTINCT tid FROM #__flexicontent_tags_item_relations WHERE itemid = ' . (int) $this->_id;
                 $db->setQuery($query);
                 $item->tags = FLEXI_J16GE ? $db->loadColumn() : $db->loadResultArray();
             }
             // -- Retrieve categories field value (if not using versioning)
             if ($use_versioning && $version) {
                 // Check version value was found, and is valid (above code should have produced an array)
                 if (!isset($item->categories) || !is_array($item->categories)) {
                     $item->categories = array();
                 }
             } else {
                 $query = 'SELECT DISTINCT catid FROM #__flexicontent_cats_item_relations WHERE itemid = ' . (int) $this->_id;
                 $db->setQuery($query);
                 $item->categories = FLEXI_J16GE ? $db->loadColumn() : $db->loadResultArray();
             }
             // Make sure catid is in categories array
             if (!in_array($item->catid, $item->categories)) {
                 $item->categories[] = $item->catid;
             }
             // 'cats' is an alias of categories
             $item->cats =& $item->categories;
             // *********************************************************
             // Retrieve item properties not defined in the model's CLASS
             // *********************************************************
             if (FLEXI_J16GE) {
                 $query = 'SELECT title FROM #__viewlevels WHERE id = ' . (int) $item->access;
             } else {
                 $query = 'SELECT name FROM #__groups WHERE id = ' . (int) $item->access;
             }
             $db->setQuery($query);
             $item->access_level = $db->loadResult();
             // Category access is retrieved here for J1.6+, for J1.5 we use FLEXIaccess
             if (FLEXI_J16GE) {
                 // Get category access for the item's main category, used later to determine viewing of the item
                 $query = 'SELECT access FROM #__categories WHERE id = ' . (int) $item->catid;
                 $db->setQuery($query);
                 $item->category_access = $db->loadResult();
             }
             // Typecast some properties in case LEFT JOIN returned nulls
             if (!isset($item->type_access)) {
                 $public_acclevel = !FLEXI_J16GE ? 0 : 1;
                 $item->type_access = $public_acclevel;
             }
             if (!isset($item->rating_count)) {
                 // Get category access for the item's main category, used later to determine viewing of the item
                 $query = 'SELECT ' . ' v.rating_count as rating_count, ROUND( v.rating_sum / v.rating_count ) AS rating, ((v.rating_sum / v.rating_count)*20) as score' . ' FROM #__content_rating AS v WHERE v.content_id = ' . (int) $item->id;
                 $db->setQuery($query);
                 $rating_data = $db->loadObject();
                 $item->rating_count = !$rating_data ? 0 : $rating_data->rating_count;
                 $item->rating = !$rating_data ? 0 : $rating_data->rating_count;
                 $item->score = !$rating_data ? 0 : $rating_data->score;
             }
             $item->typename = (string) @$item->typename;
             $item->typealias = (string) @$item->typealias;
             // Retrieve Creator NAME and email (used to display the gravatar)
             $query = 'SELECT name, email FROM #__users WHERE id = ' . (int) $item->created_by;
             $db->setQuery($query);
             $creator_data = $db->loadObject();
             $item->creator = $creator_data ? $creator_data->name : '';
             $item->creatoremail = $creator_data ? $creator_data->email : '';
             // Retrieve Modifier NAME
             if ($item->created_by == $item->modified_by) {
                 $item->modifier = $item->creator;
             } else {
                 $query = 'SELECT name, email FROM #__users WHERE id = ' . (int) $item->modified_by;
                 $db->setQuery($query);
                 $modifier_data = $db->loadObject();
                 $item->modifier = $modifier_data ? $modifier_data->name : '';
                 $item->modifieremail = $modifier_data ? $modifier_data->email : '';
             }
             // Clear modified Date, if it is an invalid "null" date
             if ($item->modified == $db->getNulldate()) {
                 $item->modified = null;
             }
             // ********************************************************
             // Assign to the item data member variable and cache it too
             // ********************************************************
             $this->_item =& $item;
             $items[$this->_id] =& $this->_item;
             // ******************************************************************************************************
             // Detect if current version doesnot exist in version table and add it !!! e.g. after enabling versioning
             // ******************************************************************************************************
             if ($use_versioning && $current_version > $last_version) {
                 require_once JPATH_ADMINISTRATOR . DS . 'components' . DS . 'com_flexicontent' . DS . 'models' . DS . 'flexicontent.php';
                 $fleximodel = new FlexicontentModelFlexicontent();
                 $fleximodel->addCurrentVersionData($item->id);
             }
             // return true if item was loaded successfully
             return (bool) $this->_item;
         } catch (JException $e) {
             if ($e->getCode() == 404) {
                 // Need to go thru the error handler to allow Redirect to work.
                 $msg = $e->getMessage();
                 if (FLEXI_J16GE) {
                     throw new Exception($msg, 404);
                 } else {
                     JError::raiseError(404, $msg);
                 }
             } else {
                 $this->setError($e);
                 $this->_item = false;
             }
         }
     } else {
         $items[$this->_id] =& $this->_item;
     }
     /*$session = JFactory::getSession();
     		$postdata = $session->get('item_edit_postdata', array(), 'flexicontent');
     		if (count($postdata)) {
     			$session->set('item_edit_postdata', null, 'flexicontent');
     			// ...
     		}*/
     return true;
 }