static function createItemsListSQL(&$params, &$_item_data = null, $isform = 0, $reverse_field = 0, &$parentfield, &$parentitem) { $db = JFactory::getDBO(); $sfx = $isform ? '_form' : ''; // Get data like aliases and published state $publish_where = ''; if ($params->get('use_publish_dates', 1)) { // Date-Times are stored as UTC, we should use current UTC time to compare and not user time (requestTime), // thus the items are published globally at the time the author specified in his/her local clock //$app = JFactory::getApplication(); //$now = FLEXI_J16GE ? $app->requestTime : $app->get('requestTime'); // NOT correct behavior it should be UTC (below) //$date = JFactory::getDate(); //$now = FLEXI_J16GE ? $date->toSql() : $date->toMySQL(); // NOT good if string passed to function that will be cached, because string continuesly different $_nowDate = 'UTC_TIMESTAMP()'; //$db->Quote($now); $nullDate = $db->getNullDate(); $publish_where = ' AND ( i.publish_up = ' . $db->Quote($nullDate) . ' OR i.publish_up <= ' . $_nowDate . ' )'; $publish_where .= ' AND ( i.publish_down = ' . $db->Quote($nullDate) . ' OR i.publish_down >= ' . $_nowDate . ' )'; } // item IDs via reversing a relation field if ($reverse_field) { $item_join = ' JOIN #__flexicontent_fields_item_relations AS fi_rel' . ' ON i.id=fi_rel.item_id AND fi_rel.field_id=' . $reverse_field . ' AND CAST(fi_rel.value AS UNSIGNED)=' . $parentitem->id; } else { $item_where = ' AND i.id IN (' . implode(",", array_keys($_item_data)) . ')'; } // Get orderby SQL CLAUSE ('ordering' is passed by reference but no frontend user override is used (we give empty 'request_var') $order = $params->get('orderby' . $sfx, 'alpha'); $orderby = flexicontent_db::buildItemOrderBy($params, $order, $request_var = '', $config_param = '', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', '', '', $sfx, $support_2nd_lvl = true); $orderby_join = ''; // Create JOIN for ordering items by a custom field (use SFC) if ('field' == $order[1]) { $orderbycustomfieldid = (int) $params->get('orderbycustomfieldid' . $sfx, 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 ($sfx == '' && '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 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 author name if (in_array('author', $order) || in_array('rauthor', $order)) { $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by'; } // Because query includes specific items it should be fast $query = 'SELECT i.*, ext.type_id,' . ' GROUP_CONCAT(c.id SEPARATOR ",") AS catidlist, ' . ' GROUP_CONCAT(c.alias SEPARATOR ",") AS cataliaslist ' . @$orderby_col . ' FROM #__content AS i ' . ' LEFT JOIN #__flexicontent_items_ext AS ext ON i.id=ext.item_id ' . @$item_join . @$orderby_join . ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON i.id=rel.itemid ' . ' LEFT JOIN #__categories AS c ON c.id=rel.catid ' . ' WHERE 1 ' . @$item_where . $publish_where . ' GROUP BY i.id ' . $orderby; //echo "<pre>".$query."</pre>"; return $query; }
/** * Build the order clause for item listing * * @access private * @return string */ function _buildItemOrderBy(&$order = '') { $request_var = $this->_params->get('orderby_override') ? 'orderby' : ''; $default_order = $this->getState('filter_order'); $default_order_dir = $this->getState('filter_order_Dir'); // Precedence: $request_var ==> $order ==> $config_param ==> $default_order return flexicontent_db::buildItemOrderBy($this->_params, $order, $request_var, $config_param = 'orderby', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order, $default_order_dir, $sfx = '', $support_2nd_lvl = true); }
public static function getItems(&$params, $ordering) { global $dump, $globalcats; global $modfc_jprof, $mod_fc_run_times; $app = JFactory::getApplication(); // For specific cache issues if (empty($globalcats)) { if (FLEXI_SECTION || FLEXI_CAT_EXTENSION) { JPluginHelper::importPlugin('system', 'flexisystem'); if (FLEXI_CACHE) { // add the category tree to categories cache $catscache = JFactory::getCache('com_flexicontent_cats'); $catscache->setCaching(1); //force cache $catscache->setLifeTime(84600); //set expiry to one day $globalcats = $catscache->call(array('plgSystemFlexisystem', 'getCategoriesTree')); } else { $globalcats = plgSystemFlexisystem::getCategoriesTree(); } } } // Initialize variables $db = JFactory::getDBO(); $user = JFactory::getUser(); $view = JRequest::getVar('view'); $option = JRequest::getVar('option'); $fparams = $app->getParams('com_flexicontent'); $show_noauth = $fparams->get('show_noauth', 0); // Date-Times are stored as UTC, we should use current UTC time to compare and not user time (requestTime), // thus the items are published globally at the time the author specified in his/her local clock //$now = $app->get('requestTime'); $now = JFactory::getDate()->toSql(); $nullDate = $db->getNullDate(); // $display_category_data $apply_config_per_category = (int) $params->get('apply_config_per_category', 0); // *** METHODS that their 'ALL' value is 0, (these do not use current item information) // current item scope parameters $method_curitem = (int) $params->get('method_curitem', 0); // current language scope parameters $method_curlang = (int) $params->get('method_curlang', 0); // current item scope parameters $method_curuserfavs = (int) $params->get('method_curuserfavs', 0); // featured items scope parameters $method_featured = (int) $params->get('method_featured', 0); // featured items scope parameters $method_states = (int) $params->get('method_states', 0); $item_states = $params->get('item_states'); $show_nocontent_msg = (int) $params->get('show_nocontent_msg', 1); // *** METHODS that their 'ALL' value is 1, that also have behaviour variable (most of them) // categories scope parameters $method_cat = (int) $params->get('method_cat', 1); $catids = $params->get('catids', array()); $behaviour_cat = $params->get('behaviour_cat', 0); $treeinclude = $params->get('treeinclude'); // types scope parameters $method_types = (int) $params->get('method_types', 1); $types = $params->get('types'); $behaviour_types = $params->get('behaviour_types', 0); // authors scope parameters $method_auth = (int) $params->get('method_auth', 1); $authors = trim($params->get('authors')); $behaviour_auth = $params->get('behaviour_auth'); // items scope parameters $method_items = (int) $params->get('method_items', 1); $items = trim($params->get('items')); $behaviour_items = $params->get('behaviour_items', 0); $excluded_tags = $params->get('excluded_tags', array()); $excluded_tags = !is_array($excluded_tags) ? array($excluded_tags) : $excluded_tags; $relitems_fields = $params->get('relitems_fields', array()); $relitems_fields = !is_array($relitems_fields) ? array($relitems_fields) : $relitems_fields; // tags scope parameters $method_tags = (int) $params->get('method_tags', 1); $tag_ids = $params->get('tag_ids', array()); $tag_combine = $params->get('tag_combine', 0); // date scope parameters $method_dates = (int) $params->get('method_dates', 1); // parameter added later, maybe not to break compatibility this should be INCLUDE=3 by default ? $date_type = (int) $params->get('date_type', 0); $nulldates = (int) $params->get('nulldates', 0); $bdate = $params->get('bdate', ''); $edate = $params->get('edate', ''); $raw_bdate = $params->get('raw_bdate', 0); $raw_edate = $params->get('raw_edate', 0); $behaviour_dates = $params->get('behaviour_dates', 0); $date_compare = $params->get('date_compare', 0); $datecomp_field = (int) $params->get('datecomp_field', 0); // Server date $sdate = explode(' ', $now); $cdate = $sdate[0] . ' 00:00:00'; // Set date comparators if ($date_type == 0) { // created $comp = 'i.created'; } else { if ($date_type == 1) { // modified $comp = 'i.modified'; } else { if ($date_type == 2) { // publish up $comp = 'i.publish_up'; } else { if ($date_type == 4) { // publish down $comp = 'i.publish_down'; } else { // $date_type == 3 $comp = 'dfrel.value'; } } } } // custom field scope $method_filt = (int) $params->get('method_filt', 1); // parameter added later, maybe not to break compatibility this should be INCLUDE=3 by default ? $behaviour_filt = (int) $params->get('behaviour_filt', 0); $static_filters = $params->get('static_filters', ''); $dynamic_filters = $params->get('dynamic_filters', ''); // get module fetching parameters if ($params->get('skip_items', 0)) { $count = (int) $params->get('maxskipcount', 50); } else { $count = (int) $params->get('count', 5); } // get module display parameters $mod_image = $params->get('mod_image'); // ************************************************************************************ // filter by publication state, (except for item state which is a special scope, below) // ************************************************************************************ $where = ' WHERE c.published = 1'; $where .= FLEXI_J16GE ? '' : ' AND i.sectionid = ' . FLEXI_SECTION; $ignore_up_down_dates = $params->get('ignore_up_down_dates', 0); // 1: ignore publish_up, 2: ignore publish_donw, 3: ignore both $ignoreState = $params->get('use_list_items_in_any_state_acl', 0) && $user->authorise('flexicontent.ignoreviewstate', 'com_flexicontent'); if (!$ignoreState && $ignore_up_down_dates != 3 && $ignore_up_down_dates != 1) { $where .= ' AND ( i.publish_up = ' . $db->Quote($nullDate) . ' OR i.publish_up <= ' . $db->Quote($now) . ' )'; } if (!$ignoreState && $ignore_up_down_dates != 3 && $ignore_up_down_dates != 2) { $where .= ' AND ( i.publish_down = ' . $db->Quote($nullDate) . ' OR i.publish_down >= ' . $db->Quote($now) . ' )'; } // ********************* // filter by permissions // ********************* $joinaccess = ''; if (!$show_noauth) { $aid_arr = JAccess::getAuthorisedViewLevels($user->id); $aid_list = implode(",", $aid_arr); $where .= ' AND ty.access IN (0,' . $aid_list . ')'; $where .= ' AND mc.access IN (0,' . $aid_list . ')'; $where .= ' AND i.access IN (0,' . $aid_list . ')'; } // ******************************************************* // NON-STATIC behaviors that need current item information // ******************************************************* $isflexi_itemview = $option == 'com_flexicontent' && $view == FLEXI_ITEMVIEW && JRequest::getInt('id'); $isflexi_catview = $option == 'com_flexicontent' && $view == 'category' && (JRequest::getInt('cid') || JRequest::getVar('cids')); $curritem_date_field_needed = $behaviour_dates && $date_compare && $date_type == 3 && $datecomp_field; // Date field selected if (($behaviour_cat || $behaviour_types || $behaviour_auth || $behaviour_items || $curritem_date_field_needed || $behaviour_filt) && $isflexi_itemview) { // initialize variables $cid = JRequest::getInt('cid'); $id = JRequest::getInt('id'); $Itemid = JRequest::getInt('Itemid'); // Check for new item nothing to retrieve, // NOTE: aborting execution if current view is not item view, but item view is required // and also proper usage of current item, both of these will be handled by SCOPEs $sel_date = ''; $join_date = ''; if ($curritem_date_field_needed) { $sel_date = ', dfrel.value as custom_date'; $join_date = ' LEFT JOIN #__flexicontent_fields_item_relations AS dfrel' . ' ON ( i.id = dfrel.item_id AND dfrel.valueorder = 1 AND dfrel.field_id = ' . $datecomp_field . ' )'; } if ($id) { $query = 'SELECT i.*, ie.*, GROUP_CONCAT(ci.catid SEPARATOR ",") as itemcats' . $sel_date . ' FROM #__content as i' . ' LEFT JOIN #__flexicontent_items_ext AS ie on ie.item_id = i.id' . ' LEFT JOIN #__flexicontent_cats_item_relations AS ci on ci.itemid = i.id' . $join_date . ' WHERE i.id = ' . $id . ' GROUP BY ci.itemid'; $db->setQuery($query); $curitem = $db->loadObject(); // Get item dates $idate = null; if ($date_type == 0) { // created $idate = $curitem->created; } else { if ($date_type == 1) { // modified $idate = $curitem->modified; } else { if ($date_type == 2) { // publish up $idate = $curitem->publish_up; } else { if (isset($curitem->custom_date)) { // $date_type == 3 $idate = $curitem->custom_date; } } } } if ($idate) { $idate = explode(' ', $idate); $cdate = $idate[0] . ' 00:00:00'; } $curritemcats = explode(',', $curitem->itemcats); } } // ****************** // current item scope // ****************** $currid = JRequest::getInt('id'); if ($method_curitem == 1) { // exclude method --- exclude current item $where .= ' AND i.id <> ' . $currid; } else { if ($method_curitem == 2) { // include method --- include current item ONLY $where .= ' AND i.id = ' . $currid; } else { // All Items including current } } // ********************** // current language scope // ********************** $lang = flexicontent_html::getUserCurrentLang(); if ($method_curlang == 1) { // exclude method --- exclude items of current language $where .= ' AND ie.language NOT LIKE ' . $db->Quote($lang . '%'); } else { if ($method_curlang == 2) { // include method --- include items of current language ONLY $where .= ' AND ( ie.language LIKE ' . $db->Quote($lang . '%') . (FLEXI_J16GE ? ' OR ie.language="*" ' : '') . ' ) '; } else { // Items of any language } } // ***************************** // current user favourites scope // ***************************** $curruserid = (int) $user->get('id'); if ($method_curuserfavs == 1) { // exclude method --- exclude currently logged user favourites $join_favs = ' LEFT OUTER JOIN #__flexicontent_favourites AS fav ON fav.itemid = i.id AND fav.userid = ' . $curruserid; $where .= ' AND fav.itemid IS NULL'; } else { if ($method_curuserfavs == 2) { // include method --- include currently logged user favourites $join_favs = ' LEFT JOIN #__flexicontent_favourites AS fav ON fav.itemid = i.id'; $where .= ' AND fav.userid = ' . $curruserid; } else { // All Items regardless of being favoured by current user $join_favs = ''; } } // ****************************** // joomla featured property scope // ****************************** if ($method_featured == 1) { // exclude method --- exclude currently logged user favourites $where .= ' AND i.featured=0'; } else { if ($method_featured == 2) { // include method --- include currently logged user favourites $where .= ' AND i.featured=1'; } else { // All Items regardless of being featured or not } } // ***************** // item states scope // ***************** $item_states = is_array($item_states) ? implode(',', $item_states) : $item_states; if ($method_states == 0) { if (!$ignoreState) { // method normal: Published item states $where .= ' AND i.state IN ( 1, -5 )'; } } else { // exclude trashed $where .= ' AND i.state <> -2'; if ($item_states) { if ($method_states == 1) { // exclude method --- exclude specified item states $where .= ' AND i.state NOT IN (' . $item_states . ')'; } else { if ($method_states == 2) { // include method --- include specified item states $where .= ' AND i.state IN (' . $item_states . ')'; } } } else { if ($method_states == 2) { // misconfiguration, when using include method with no state selected ... echo "<b>WARNING:</b> Misconfigured item states scope, select at least one state or set states scope to Normal <small>(Published)</small><br/>"; return; } } } // **************** // categories scope // **************** // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do if (!$behaviour_cat && $method_cat == 1) { if ($apply_config_per_category) { echo "<b>WARNING:</b> Misconfiguration warning, APPLY CONFIGURATION PER CATEGORY is possible only if CATEGORY SCOPE is set to either (a) INCLUDE(static selection of categories) or (b) items in same category as current item / or current category of category view<br/>"; return; } } else { if (!$behaviour_cat) { // Check for empty statically selected records, and abort with error message if (empty($catids)) { echo "<b>WARNING:</b> Misconfigured category scope, select at least one category or set category scope to ALL<br/>"; return; } // Make sure categories is an array $catids = is_array($catids) ? $catids : array($catids); // Retrieve extra categories, such children or parent categories $catids_arr = flexicontent_cats::getExtraCats($catids, $treeinclude, array()); if (empty($catids_arr)) { if ($show_nocontent_msg) { echo JText::_("No viewable content in Current View for your Access Level"); } return; } if ($method_cat == 2) { // exclude method if ($apply_config_per_category) { echo "<b>WARNING:</b> Misconfiguration warning, APPLY CONFIGURATION PER CATEGORY is possible only if CATEGORY SCOPE is set to either (a) INCLUDE(static selection of categories) or (b) items in same category as current item / or current category of category view<br/>"; return; } $where .= ' AND c.id NOT IN (' . implode(',', $catids_arr) . ')'; } else { if ($method_cat == 3) { // include method if (!$apply_config_per_category) { $where .= ' AND c.id IN (' . implode(',', $catids_arr) . ')'; } else { // *** Applying configuration per category *** foreach ($catids_arr as $catid) { // The items retrieval query will be executed ... once per EVERY category $multiquery_cats[$catid] = ' AND c.id = ' . $catid; } $params->set('dynamic_catids', serialize($catids_arr)); // Set dynamic catids to be used by the getCategoryData } } } } else { if (($behaviour_cat == 2 || $behaviour_cat == 4) && $apply_config_per_category) { echo "<b>WARNING:</b> Misconfiguration warning, APPLY CONFIGURATION PER CATEGORY is possible only if CATEGORY SCOPE is set to either (a) INCLUDE(static selection of categories) or (b) items in same category as current item / or current category of category view<br/>"; return; } $currcat_valid_case = $behaviour_cat == 1 && $isflexi_itemview || $behaviour_cat == 3 && $isflexi_catview; if (!$currcat_valid_case) { return; // current view is not item OR category view ... , nothing to display } // IF $cid is not set then use the main category id of the (current) item if ($isflexi_itemview) { $cid = $cid ? $cid : $curitem->catid; // Retrieve extra categories, such children or parent categories $catids_arr = flexicontent_cats::getExtraCats(array($cid), $treeinclude, $curritemcats); } else { if ($isflexi_catview) { $cid = JRequest::getInt('cid', 0); if (!$cid) { $_cids = JRequest::getVar('cids', ''); if (!is_array($_cids)) { $_cids = preg_replace('/[^0-9,]/i', '', (string) $_cids); $_cids = explode(',', $_cids); } // make sure given data are integers ... !! $cids = array(); foreach ($_cids as $i => $_id) { if ((int) $_id) { $cids[] = (int) $_id; } } // Retrieve extra categories, such children or parent categories $catids_arr = flexicontent_cats::getExtraCats(array($cid), $treeinclude, array()); } } else { return; // nothing to display } } // Retrieve extra categories, such children or parent categories $catids_arr = flexicontent_cats::getExtraCats(array($cid), $treeinclude, $isflexi_itemview ? $curritemcats : array()); if (empty($catids_arr)) { if ($show_nocontent_msg) { echo JText::_("No viewable content in Current View for your Access Level"); } return; } if ($behaviour_cat == 1 || $behaviour_cat == 3) { if (!$apply_config_per_category) { $where .= ' AND c.id IN (' . implode(',', $catids_arr) . ')'; } else { // *** Applying configuration per category *** foreach ($catids_arr as $catid) { // The items retrieval query will be executed ... once per EVERY category $multiquery_cats[$catid] = ' AND c.id = ' . $catid; } $params->set('dynamic_catids', serialize($catids_arr)); // Set dynamic catids to be used by the getCategoryData } } else { $where .= ' AND c.id NOT IN (' . implode(',', $catids_arr) . ')'; } } } // Now check if no items need to be retrieved if ($count == 0) { return; } // *********** // types scope // *********** // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do if (!$behaviour_types && $method_types == 1) { } else { if (!$behaviour_types) { // Check for empty statically selected records, and abort with error message if (empty($types)) { echo "<b>WARNING:</b> Misconfigured types scope, select at least one item type or set types scope to ALL<br/>"; return; } // Make types a comma separated string of ids $types = is_array($types) ? implode(',', $types) : $types; if ($method_types == 2) { // exclude method $where .= ' AND ie.type_id NOT IN (' . $types . ')'; } else { if ($method_types == 3) { // include method $where .= ' AND ie.type_id IN (' . $types . ')'; } } } else { if (!$isflexi_itemview) { return; // current view is not item view ... , nothing to display } if ($behaviour_types == 1) { $where .= ' AND ie.type_id = ' . (int) $curitem->type_id; } else { $where .= ' AND ie.type_id <> ' . (int) $curitem->type_id; } } } // ************ // author scope // ************ // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do if (!$behaviour_auth && $method_auth == 1) { } else { if (!$behaviour_auth) { // Check for empty statically selected records, and abort with error message if (empty($authors)) { echo "<b>WARNING:</b> Misconfigured author scope, select at least one author or set author scope to ALL<br/>"; return; } if ($method_auth == 2) { // exclude method $where .= ' AND i.created_by NOT IN (' . $authors . ')'; } else { if ($method_auth == 3) { // include method $where .= ' AND i.created_by IN (' . $authors . ')'; } } } else { if (!$isflexi_itemview && $behaviour_auth < 3) { // Behaviour 3 is current user thus not related to current item return; // current view is not item view ... , nothing to display } if ($behaviour_auth == 1) { $where .= ' AND i.created_by = ' . (int) $curitem->created_by; } else { if ($behaviour_auth == 2) { $where .= ' AND i.created_by <> ' . (int) $curitem->created_by; } else { // $behaviour_auth == 3 $where .= ' AND i.created_by = ' . (int) $user->id; } } } } // *********** // items scope // *********** // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do if (!$behaviour_items && $method_items == 1) { } else { if (!$behaviour_items) { // Check for empty statically selected records, and abort with error message if (empty($items)) { echo "<b>WARNING:</b> Misconfigured items scope, select at least one item or set items scope to ALL<br/>"; return; } if ($method_items == 2) { // exclude method $where .= ' AND i.id NOT IN (' . $items . ')'; } else { if ($method_items == 3) { // include method $where .= ' AND i.id IN (' . $items . ')'; } } } else { if ($behaviour_items == 2 || $behaviour_items == 3) { if (!$isflexi_itemview) { return; // current view is not item view ... , nothing to display } unset($related); // make sure this is no set ... if (count($relitems_fields)) { $where2 = count($relitems_fields) > 1 ? ' AND field_id IN (' . implode(',', $relitems_fields) . ')' : ' AND field_id = ' . $relitems_fields[0]; // select the item ids related to current item via the relation fields $query2 = 'SELECT DISTINCT ' . ($behaviour_items == 2 ? 'value' : 'item_id') . ' FROM #__flexicontent_fields_item_relations' . ' WHERE ' . ($behaviour_items == 2 ? 'item_id' : 'value') . ' = ' . (int) $id . $where2; $db->setQuery($query2); $related = $db->loadColumn(); $related = is_array($related) ? array_map('intval', $related) : $related; } if (isset($related) && count($related)) { $where .= count($related) > 1 ? ' AND i.id IN (' . implode(',', $related) . ')' : ' AND i.id = ' . $related[0]; } else { // No related items were found return; } } else { if ($behaviour_items == 1) { if (!$isflexi_itemview) { return; // current view is not item view ... , nothing to display } // select the tags associated to the item $query2 = 'SELECT tid' . ' FROM #__flexicontent_tags_item_relations' . ' WHERE itemid = ' . (int) $id; $db->setQuery($query2); $tags = $db->loadColumn(); $tags = array_diff($tags, $excluded_tags); unset($related); if ($tags) { $where2 = count($tags) > 1 ? ' AND tid IN (' . implode(',', $tags) . ')' : ' AND tid = ' . $tags[0]; // select the item ids related to current item via common tags $query2 = 'SELECT DISTINCT itemid' . ' FROM #__flexicontent_tags_item_relations' . ' WHERE itemid <> ' . (int) $id . $where2; $db->setQuery($query2); $related = $db->loadColumn(); } if (isset($related) && count($related)) { $where .= count($related) > 1 ? ' AND i.id IN (' . implode(',', $related) . ')' : ' AND i.id = ' . $related[0]; } else { // No related items were found return; } } } } } // ********** // tags scope // ********** if ($method_tags > 1) { // Check for empty statically selected records, and abort with error message if (empty($tag_ids)) { echo "<b>WARNING:</b> Misconfigured tags scope, select at least one tag or set tags scope to ALL<br/>"; return; } // Make sure tag_ids is an array $tag_ids = !is_array($tag_ids) ? array($tag_ids) : $tag_ids; // Create query to match item ids using the selected tags $query2 = 'SELECT ' . ($tag_combine ? 'itemid' : 'DISTINCT itemid') . ' FROM #__flexicontent_tags_item_relations' . ' WHERE tid IN (' . implode(',', $tag_ids) . ')' . ($tag_combine ? ' GROUP by itemid HAVING COUNT(*) >= ' . count($tag_ids) : ''); if ($method_tags == 2) { // exclude method $where .= ' AND i.id NOT IN (' . $query2 . ')'; } else { if ($method_tags == 3) { // include method $where .= ' AND i.id IN (' . $query2 . ')'; } } } // ********** // date scope // ********** // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do // NOTE: currently we only have ALL, INCLUDE methods if (!$behaviour_dates && $method_dates == 1) { } else { if (!$behaviour_dates) { $negate_op = $method_dates == 2 ? 'NOT' : ''; if (!$raw_edate && $edate && !FLEXIUtilities::isSqlValidDate($edate)) { echo "<b>WARNING:</b> Misconfigured date scope, you have entered invalid -END- date:<br>(a) Enter a valid date via callendar OR <br>(b) leave blank OR <br>(c) choose (non-static behavior 'custom offset') and enter custom offset e.g. five days ago (be careful with space character): -5 d<br/>"; return; } else { if ($edate) { $where .= ' AND ( ' . $negate_op . ' ( ' . $comp . ' <= ' . (!$raw_edate ? $db->Quote($edate) : $edate) . ' )' . ($nulldates ? ' OR ' . $comp . ' IS NULL OR ' . $comp . '="" ' : '') . ' )'; } } if (!$raw_bdate && $bdate && !FLEXIUtilities::isSqlValidDate($bdate)) { echo "<b>WARNING:</b> Misconfigured date scope, you have entered invalid -BEGIN- date:<br>(a) Enter a valid date via callendar OR <br>(b) leave blank OR <br>(c) choose (non-static behavior 'custom offset') and enter custom offset e.g. five days ago (be careful with space character): -5 d<br/>"; return; } else { if ($bdate) { $where .= ' AND ( ' . $negate_op . ' ( ' . $comp . ' >= ' . (!$raw_bdate ? $db->Quote($bdate) : $bdate) . ' )' . ($nulldates ? ' OR ' . $comp . ' IS NULL OR ' . $comp . '="" ' : '') . ' )'; } } } else { if (!$isflexi_itemview && $date_compare == 1) { return; // date_compare == 1 means compare to current item, but current view is not an item view so we terminate } // FOR date_compare==0, $cdate is SERVER DATE // FOR date_compare==1, $cdate is CURRENT ITEM DATE of type created or modified or publish_up or CUSTOM date field switch ($behaviour_dates) { case '1': // custom offset if ($edate) { $edate = array(0 => preg_replace("/[^-+0-9\\s]/", "", $edate), 1 => preg_replace("/[0-9-+\\s]/", "", $edate)); if (empty($edate[1])) { echo "<b>WARNING:</b> Misconfigured date scope, you have entered invalid -END- date:Custom offset is invalid e.g. in order to enter five days ago (be careful with space character) use: -5 d (DO NOT FORGET the space between e.g. '-5 d')<br/>"; return; } else { $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, $edate[0], $edate[1])) . ($nulldates ? ' OR ' . $comp . ' IS NULL OR ' . $comp . '="" ' : '') . ' )'; } } if ($bdate) { $bdate = array(0 => preg_replace("/[^-+0-9]/", "", $bdate), 1 => preg_replace("/[0-9-+]/", "", $bdate)); if (empty($bdate[1])) { echo "<b>WARNING:</b> Misconfigured date scope, you have entered invalid -BEGIN- date: Custom offset is invalid e.g. in order to enter five days ago (be careful with space character) use: -5 d (DO NOT FORGET the space between e.g. '-5 d')<br/>"; return; } else { $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, $bdate[0], $bdate[1])) . ($nulldates ? ' OR ' . $comp . ' IS NULL OR ' . $comp . '="" ' : '') . ' )'; } } break; case '8': // same day $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-' . $cdate[2] . ' 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 1, 'd')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote($cdate) . ' )'; break; case '2': // same month $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 1, 'm')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote($cdate) . ' )'; break; case '3': // same year $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-01-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 1, 'Y')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote($cdate) . ' )'; break; case '9': // previous day $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-' . $cdate[2] . ' 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote($cdate) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, -1, 'd')) . ' )'; break; case '4': // previous month $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote($cdate) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, -1, 'm')) . ' )'; break; case '5': // previous year $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-01-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote($cdate) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, -1, 'Y')) . ' )'; break; case '10': // next day $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-' . $cdate[2] . ' 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 2, 'd')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, 1, 'd')) . ' )'; break; case '6': // next month $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-' . $cdate[1] . '-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 2, 'm')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, 1, 'm')) . ' )'; break; case '7': // next year $cdate = explode(' ', $cdate); $cdate = explode('-', $cdate[0]); $cdate = $cdate[0] . '-01-01 00:00:00'; $where .= ' AND ( ' . $comp . ' < ' . $db->Quote(date_time::shift_dates($cdate, 2, 'Y')) . ' )'; $where .= ' AND ( ' . $comp . ' >= ' . $db->Quote(date_time::shift_dates($cdate, 1, 'Y')) . ' )'; break; case '11': // same day of month, ignore year $where .= ' AND ( DAYOFMONTH(' . $comp . ') = ' . 'DAYOFMONTH(' . $db->Quote($cdate) . ') AND MONTH(' . $comp . ') = ' . 'MONTH(' . $db->Quote($cdate) . ') )'; break; case '12': // [-3d,+3d] days of month, IGNORE YEAR $where .= ' AND ((DAYOFMONTH(' . $db->Quote($cdate) . ')-3) <= DAYOFMONTH(' . $comp . ') AND DAYOFMONTH(' . $comp . ') <= (DAYOFMONTH(' . $db->Quote($cdate) . ')+4) AND MONTH(' . $comp . ') = ' . 'MONTH(' . $db->Quote($cdate) . ') )'; break; case '13': // same week of month, IGNORE YEAR $week_start = (int) $params->get('week_start', 0); // 0 is sunday, 5 is monday $week_of_month = '(WEEK(%s,5) - WEEK(DATE_SUB(%s, INTERVAL DAYOFMONTH(%s)-1 DAY),5)+1)'; $where .= ' AND (' . str_replace('%s', $comp, $week_of_month) . ' = ' . str_replace('%s', $db->Quote($cdate), $week_of_month) . ' AND ( MONTH(' . $comp . ') = ' . 'MONTH(' . $db->Quote($cdate) . ') ) )'; break; case '14': // same week of year, IGNORE YEAR $week_start = (int) $params->get('week_start', 0); // 0 is sunday, 5 is monday $where .= ' AND ( WEEK(' . $comp . ') = ' . 'WEEK(' . $db->Quote($cdate) . ',' . $week_start . ') )'; break; case '15': // same month of year, IGNORE YEAR $where .= ' AND ( MONTH(' . $comp . ') = ' . 'MONTH(' . $db->Quote($cdate) . ') )'; break; case '16': // same day of month, IGNORE MONTH, YEAR $where .= ' AND ( DAYOFMONTH(' . $comp . ') = ' . 'DAYOFMONTH(' . $db->Quote($cdate) . ') )'; break; case '17': // [-3d,+3d] days of month, IGNORE MONTH, YEAR $where .= ' AND ((DAYOFMONTH(' . $db->Quote($cdate) . ')-3) <= DAYOFMONTH(' . $comp . ') AND DAYOFMONTH(' . $comp . ') <= (DAYOFMONTH(' . $db->Quote($cdate) . ')+4) )'; break; case '18': // same week of month, IGNORE MONTH, YEAR $week_start = (int) $params->get('week_start', 0); // 0 is sunday, 5 is monday $week_of_month = '(WEEK(%s,5) - WEEK(DATE_SUB(%s, INTERVAL DAYOFMONTH(%s)-1 DAY),5)+1)'; $where .= ' AND (' . str_replace('%s', $comp, $week_of_month) . ' = ' . str_replace('%s', $db->Quote($cdate), $week_of_month) . ' )'; break; } } } // ***************************** // EXTRA joins for special cases // ***************************** // EXTRA joins when comparing to custom date field $join_date = ''; if ($behaviour_dates || $method_dates != 1) { // using date SCOPE: dynamic behaviour, or static behavior with (static) method != ALL(=1) if (($bdate || $edate || $behaviour_dates) && $date_type == 3) { if ($datecomp_field) { $join_date = ' LEFT JOIN #__flexicontent_fields_item_relations AS dfrel' . ' ON ( i.id = dfrel.item_id AND dfrel.field_id = ' . $datecomp_field . ' )'; } else { echo "<b>WARNING:</b> Misconfigured date scope, you have set DATE TYPE as CUSTOM DATE Field, but have not select any specific DATE Field to be used<br/>"; //$join_date = ''; return; } } } // ***************************************************************************************************************************** // Get orderby SQL CLAUSE ('ordering' is passed by reference but no frontend user override is used (we give empty 'request_var') // ***************************************************************************************************************************** $orderby = flexicontent_db::buildItemOrderBy($params, $ordering, $request_var = '', $config_param = 'ordering', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order = '', $default_order_dir = '', $sfx = '', $support_2nd_lvl = true); //echo "<br/>" . print_r($ordering, true) ."<br/>"; // EXTRA join of field used in custom ordering // NOTE: if (1st/2nd level) custom field id is not set, THEN 'field' ordering was changed to level's default, by the ORDER CLAUSE creating function $orderby_join = ''; // Create JOIN for ordering items by a custom field (Level 1) if ('field' == $ordering[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' == $ordering[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', $ordering) || in_array('rauthor', $ordering)) { $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by'; } // ***************************************************** // Decide Select Sub-Clause and Join-Clause for comments // ***************************************************** $display_comments = $params->get('display_comments'); $display_comments_feat = $params->get('display_comments_feat'); // Check (when needed) if jcomments are installed, and also clear 'commented' ordering if they jcomments is missing if ($display_comments_feat || $display_comments || in_array('commented', $ordering)) { // Handle jcomments integratio. No need to reset 'commented' ordering if jcomments not installed, // and neither print message, the ORDER CLAUSE creating function should have done this already if (!file_exists(JPATH_SITE . DS . 'components' . DS . 'com_jcomments' . DS . 'jcomments.php')) { //echo "jcomments not installed, you need jcomments to use 'Most commented' ordering OR display comments information.<br>\n"; $jcomments_exist = false; } else { $jcomments_exist = true; } } // Decide to JOIN (or not) with comments TABLE, needed when displaying comments and/or when ordering by comments $add_comments = ($display_comments_feat || $display_comments || in_array('commented', $ordering)) && $jcomments_exist; // Additional select and joins for comments $select_comments = $add_comments ? ', COUNT(DISTINCT com.id) AS comments_total' : ''; $join_comments_type = $ordering[1] == 'commented' ? ' INNER JOIN' : ' LEFT JOIN'; // Do not require most commented for 2nd level ordering $join_comments = $add_comments ? $join_comments_type . ' #__jcomments AS com ON com.object_id = i.id AND com.object_group="com_flexicontent" AND com.published="1"' : ''; // ********************************************************** // Decide Select Sub-Clause and Join-Clause for voting/rating // ********************************************************** $display_voting = $params->get('display_voting'); $display_voting_feat = $params->get('display_voting_feat'); // Decide to JOIN (or not) with rating TABLE, needed when displaying ratings and/or when ordering by ratings $add_rated = $display_voting_feat || $display_voting || in_array('rated', $ordering); // Additional select and joins for ratings $select_rated = in_array('rated', $ordering) ? ', (cr.rating_sum / cr.rating_count) * 20 AS votes' : ''; $select_rated .= $add_rated ? ', cr.rating_sum as rating_sum, cr.rating_count as rating_count' : ''; $join_rated_type = in_array('rated', $ordering) ? ' INNER JOIN' : ' LEFT JOIN'; $join_rated = $add_rated ? $join_rated_type . ' #__content_rating AS cr ON cr.content_id = i.id' : ''; // *********************************************************** // Finally put together the query to retrieve the listed items // *********************************************************** // ****************** // Custom FIELD scope // ****************** $where_field_filters = ''; $join_field_filters = ''; // ZERO 'behaviour' means statically selected records, but METHOD 1 is ALL records ... so NOTHING to do if (!$behaviour_filt && $method_filt == 1) { } else { if ($behaviour_filt == 0 || $behaviour_filt == 2) { $negate_op = $method_filt == 2 ? 'NOT' : ''; // These field filters apply a STATIC filtering, regardless of current item being displayed. // Static Field Filters (These are a string that MAPs filter ID TO filter VALUES) $static_filters_data = FlexicontentFields::setFilterValues($params, 'static_filters', $is_persistent = 1, $set_method = "array"); // Dynamic Field Filters (THIS is filter IDs list) // These field filters apply a DYNAMIC filtering, that depend on current item being displayed. The items that have same value as currently displayed item will be included in the list. //$dynamic_filters = FlexicontentFields::setFilterValues( $params, 'dynamic_filters', $is_persistent=0); foreach ($static_filters_data as $filter_id => $filter_values) { // Handle single-valued filter as multi-valued if (!is_array($filter_values)) { $filter_values = array(0 => $filter_values); } // Single or Multi valued filter if (isset($filter_values[0])) { $in_values = array(); foreach ($filter_values as $val) { $in_values[] = $db->Quote($val); } // Quote in case they are strings !! $where_field_filters .= ' AND ' . $negate_op . ' (rel' . $filter_id . '.value IN (' . implode(',', $in_values) . ') ) '; } else { // Special case only one part of range provided ... must MATCH/INCLUDE empty values or NULL values ... $value_empty = !strlen(@$filter_values[1]) && strlen(@$filter_values[2]) ? ' OR rel' . $filter_id . '.value="" OR rel' . $filter_id . '.value IS NULL ' : ''; if (strlen(@$filter_values[1]) || strlen(@$filter_values[2])) { $where_field_filters .= ' AND ' . $negate_op . ' ( 1 '; if (strlen(@$filter_values[1])) { $where_field_filters .= ' AND (rel' . $filter_id . '.value >=' . $filter_values[1] . ') '; } if (strlen(@$filter_values[2])) { $where_field_filters .= ' AND (rel' . $filter_id . '.value <=' . $filter_values[2] . $value_empty . ') '; } $where_field_filters .= ' )'; } } $join_field_filters .= ' JOIN #__flexicontent_fields_item_relations AS rel' . $filter_id . ' ON rel' . $filter_id . '.item_id=i.id AND rel' . $filter_id . '.field_id = ' . $filter_id; } } } if ($behaviour_filt == 1 || $behaviour_filt == 2) { if (!$isflexi_itemview) { return; // current view is not item view ... , nothing to display } // 1. Get ids of dynamic filters //$dynamic_filter_ids = preg_split("/[\s]*,[\s]*/", $dynamic_filters); $dynamic_filter_ids = FLEXIUtilities::paramToArray($dynamic_filters, "/[\\s]*,[\\s]*/", "intval"); if (empty($dynamic_filter_ids)) { echo "Please enter at least 1 field in Custom field filtering SCOPE, or set behaviour to static"; } else { // 2. Get values of dynamic filters $where2 = count($dynamic_filter_ids) > 1 ? ' AND field_id IN (' . implode(',', $dynamic_filter_ids) . ')' : ' AND field_id = ' . $dynamic_filter_ids[0]; // select the item ids related to current item via the relation fields $query2 = 'SELECT DISTINCT value, field_id' . ' FROM #__flexicontent_fields_item_relations' . ' WHERE item_id = ' . (int) $id . $where2; $db->setQuery($query2); $curritem_vals = $db->loadObjectList(); //echo "<pre>"; print_r($curritem_vals); echo "</pre>"; // 3. Group values by field $_vals = array(); foreach ($curritem_vals as $v) { $_vals[$v->field_id][] = $v->value; } foreach ($dynamic_filter_ids as $filter_id) { // Handle non-existent value by requiring that matching item do not have a value for this field either if (!isset($_vals[$filter_id])) { $where_field_filters .= ' AND reldyn' . $filter_id . '.value IS NULL'; } else { $in_values = array(); foreach ($_vals[$filter_id] as $v) { $in_values[] = $db->Quote($v); } $where_field_filters .= ' AND reldyn' . $filter_id . '.value IN (' . implode(',', $in_values) . ') ' . "\n"; } $join_field_filters .= ' JOIN #__flexicontent_fields_item_relations AS reldyn' . $filter_id . ' ON reldyn' . $filter_id . '.item_id=i.id AND reldyn' . $filter_id . '.field_id = ' . $filter_id . "\n"; } //echo "<pre>"."\n\n".$join_field_filters ."\n\n".$where_field_filters."</pre>"; } } if (empty($items_query)) { // If a custom query has not been set above then use the default one ... $items_query = 'SELECT ' . ' i.id ' . (in_array('commented', $ordering) ? $select_comments : '') . (in_array('rated', $ordering) ? $select_rated : '') . ' FROM #__flexicontent_items_tmp AS i' . ' JOIN #__flexicontent_items_ext AS ie on ie.item_id = i.id' . ' JOIN #__flexicontent_types AS ty on ie.type_id = ty.id' . ' JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id' . ' JOIN #__categories AS c ON c.id = rel.catid' . ' JOIN #__categories AS mc ON mc.id = i.catid' . $joinaccess . $join_favs . $join_date . (in_array('commented', $ordering) ? $join_comments : '') . (in_array('rated', $ordering) ? $join_rated : '') . $orderby_join . $join_field_filters . $where . ' ' . ($apply_config_per_category ? '__CID_WHERE__' : '') . $where_field_filters . ' GROUP BY i.id' . $orderby; // if using CATEGORY SCOPE INCLUDE ... then link though them ... otherwise via main category $_cl = !$behaviour_cat && $method_cat == 3 ? 'c' : 'mc'; $items_query_data = 'SELECT ' . ' i.*, ie.*, ty.name AS typename' . $select_comments . $select_rated . ', mc.title AS maincat_title, mc.alias AS maincat_alias' . ', 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(\':\', ' . $_cl . '.id, ' . $_cl . '.alias) ELSE ' . $_cl . '.id END as categoryslug' . ', GROUP_CONCAT(rel.catid SEPARATOR ",") as itemcats' . ' FROM #__content AS i' . ' JOIN #__flexicontent_items_ext AS ie on ie.item_id = i.id' . ' JOIN #__flexicontent_types AS ty on ie.type_id = ty.id' . ' JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id' . ' JOIN #__categories AS c ON c.id = rel.catid' . ' JOIN #__categories AS mc ON mc.id = i.catid' . $joinaccess . $join_favs . $join_date . $join_comments . $join_rated . $orderby_join . ' WHERE i.id IN (__content__)' . ' GROUP BY i.id'; } // ********************************** // Execute query once OR per category // ********************************** if (!isset($multiquery_cats)) { $multiquery_cats = array(0 => ""); } foreach ($multiquery_cats as $catid => $cat_where) { $_microtime = $modfc_jprof->getmicrotime(); // Get content list per given category $per_cat_query = str_replace('__CID_WHERE__', $cat_where, $items_query); $db->setQuery($per_cat_query, 0, $count); $content = $db->loadColumn(0); if ($db->getErrorNum()) { JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error'); } @($mod_fc_run_times['query_items'] += $modfc_jprof->getmicrotime() - $_microtime); // Check for no content found for given category if (empty($content)) { $cat_items_arr[$catid] = array(); continue; } $_microtime = $modfc_jprof->getmicrotime(); // Get content list data per given category $per_cat_query = str_replace('__content__', implode(',', $content), $items_query_data); $db->setQuery($per_cat_query, 0, $count); $_rows = $db->loadObjectList('item_id'); if ($db->getErrorNum()) { JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error'); } @($mod_fc_run_times['query_items_sec'] += $modfc_jprof->getmicrotime() - $_microtime); // Secondary content list ordering and assign content list per category $rows = array(); foreach ($content as $_id) { $rows[] = $_rows[$_id]; } $cat_items_arr[$catid] = $rows; // Get Original content ids for creating some untranslatable fields that have share data (like shared folders) flexicontent_db::getOriginalContentItemids($cat_items_arr[$catid]); } // ************************************************************************************************ // Return items indexed per category id OR via empty string if not apply configuration per category // ************************************************************************************************ return $cat_items_arr; }
/** * 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; }
function onDisplayField(&$field, &$item) { if (!in_array($field->field_type, self::$field_types)) { return; } $field->label = JText::_($field->label); // Get some api objects $db = JFactory::getDBO(); $user = JFactory::getUser(); $document = JFactory::getDocument(); $field->html = ''; $ri_field_name = str_replace('-', '_', $field->name); $fieldname = FLEXI_J16GE ? 'custom[' . $ri_field_name . '][]' : $ri_field_name . '[]'; // Case of autorelated item $autorelation_itemid = JRequest::getInt('autorelation_' . $field->id); if ($autorelation_itemid) { // automatically related item $query = 'SELECT title, id, catid, state, alias ' . ' FROM #__content ' . ' WHERE id =' . $autorelation_itemid; $db->setQuery($query); $rel_item = $db->loadObject(); if (!$rel_item) { $field->html = 'auto relating item id: ' . $autorelation_itemid . ' : item not found '; return; } $field->html = '<input id="' . $ri_field_name . '" name="' . $fieldname . '" type="hidden" value="' . $rel_item->id . ':' . $rel_item->catid . '" />'; $field->html .= $rel_item->title; return; } // ************************************************************************ // Initialise values and split them into: (a) item ids and (b) category ids // ************************************************************************ $default_values = ''; if ($item->version == 0 && $default_values) { $field->value = explode(",", $default_values); } else { if (!$field->value) { $field->value = array(); } else { // Compatibility with old values, we no longer serialize all values to one, this way the field can be reversed more easily !!! $field->value = ($field_data = @unserialize($field->value[0])) ? $field_data : $field->value; } } $_itemids_catids = array(); foreach ($field->value as $i => $val) { list($itemid, $catid) = explode(":", $val); $itemid = (int) $itemid; $catid = (int) $catid; $_itemids_catids[$itemid] = new stdClass(); $_itemids_catids[$itemid]->itemid = $itemid; $_itemids_catids[$itemid]->catid = $catid; $_itemids_catids[$itemid]->value = $val; } $auto_relate_curritem = $field->parameters->get('auto_relate_curritem', 0); if ($auto_relate_curritem && !empty($_itemids_catids) && !FlexicontentHelperPerm::getPerm()->SuperAdmin) { $query = 'SELECT title, id, catid, state, alias ' . ' FROM #__content ' . ' WHERE id IN (' . implode(array_keys($_itemids_catids), ',') . ')'; $db->setQuery($query); $rel_items = $db->loadObjectList(); $i = 0; foreach ($rel_items as $rel_item) { $field->html .= '<input id="' . $ri_field_name . $i . '" name="' . $fieldname . '" type="hidden" value="' . $rel_item->id . ':' . $rel_item->catid . '" />'; $field->html .= $rel_item->title . " <br/> \n"; $i++; } return; } // ****************** // SCOPE PARAMETERS // ****************** // categories scope parameters $method_cat = $field->parameters->get('method_cat', 1); $usesubcats = $field->parameters->get('usesubcats', 0); $catids = $field->parameters->get('catids'); if (empty($catids)) { $catids = array(); } else { if (!is_array($catids)) { $catids = !FLEXI_J16GE ? array($catids) : explode("|", $catids); } } // types scope parameters $method_types = $field->parameters->get('method_types', 1); $types = $field->parameters->get('types'); if (empty($types)) { $types = array(); } else { if (!is_array($types)) { $types = !FLEXI_J16GE ? array($types) : explode("|", $types); } } // other limits of scope parameters $samelangonly = $field->parameters->get('samelangonly', 1); $onlypublished = $field->parameters->get('onlypublished', 1); $ownedbyuser = $field->parameters->get('ownedbyuser', 0); // ****************** // EDITING PARAMETERS // ****************** // some parameters shortcuts $size = $field->parameters->get('size', 12); $size = $size ? ' size="' . $size . '"' : ''; $prepend_item_state = $field->parameters->get('prepend_item_state', 1); $maxtitlechars = $field->parameters->get('maxtitlechars', 40); $title_filter = $field->parameters->get('title_filter', 1); $required = $field->parameters->get('required', 0); $required = $required ? ' required' : ''; $select_items_prompt = $field->parameters->get('select_items_prompt', 'FLEXI_RIFLD_SELECT_ITEMS_PROMPT'); $selected_items_label = $field->parameters->get('selected_items_label', 'FLEXI_RIFLD_SELECTED_ITEMS_LABEL'); $display_cat_filter_label = $field->parameters->get('display_cat_filter_label', 1); $display_title_filter_label = $field->parameters->get('display_title_filter_label', 1); $default_value_title_filter = $field->parameters->get('default_value_title_filter', ''); // *********************************************** // Get & check Global category related permissions // *********************************************** require_once JPATH_ROOT . DS . 'components' . DS . 'com_flexicontent' . DS . 'helpers' . DS . 'permission.php'; $viewallcats = FlexicontentHelperPerm::getPerm()->ViewAllCats; $viewtree = FlexicontentHelperPerm::getPerm()->ViewTree; if (!$viewtree) { $field->html = '<div class="alert alert-info fc-small fc-iblock">' . JText::_('FLEXI_NO_ACCESS_LEVEL_TO_VIEW_CATEGORY_TREE') . '</div><div class="clear"></div>'; return; } // **************************************************** // Calculate categories to use for retrieving the items // **************************************************** $allowed_cats = $disallowed_cats = false; // Get user allowed categories $usercats = FLEXI_J16GE || FLEXI_ACCESS ? FlexicontentHelperPerm::getAllowedCats($user, $actions_allowed = array('core.create', 'core.edit', 'core.edit.own'), $require_all = false, $check_published = true) : FlexicontentHelperPerm::returnAllCats($check_published = true, $specific_catids = null); // Find (if configured) , descendants of the categories if ($usesubcats) { global $globalcats; $_catids = array(); foreach ($catids as $catid) { $subcats = $globalcats[$catid]->descendantsarray; foreach ($subcats as $subcat) { $_catids[(int) $subcat] = 1; } } $catids = array_keys($_catids); } // ... TODO: retrieve items via AJAX // ********************************************* // Item retrieving query ... CREATE WHERE CLAUSE // ********************************************* $where = array(); // ************** // CATEGORY SCOPE // ************** // Include method if ($method_cat == 3) { $allowed_cats = $viewallcats ? $catids : array_intersect($usercats, $catids); if (!empty($allowed_cats)) { $where[] = " rel.catid IN (" . implode(',', $allowed_cats) . ") "; } else { $field->html = JText::_('FLEXI_CANNOT_EDIT_FIELD') . ': <br/> ' . JText::_('FLEXI_NO_ACCESS_TO_USE_CONFIGURED_CATEGORIES'); return; } } else { if ($method_cat == 2) { $disallowed_cats = $viewallcats ? $catids : array_diff($usercats, $catids); if (!empty($disallowed_cats)) { $where[] = " rel.catid NOT IN (" . implode(',', $disallowed_cats) . ") "; } } else { if (!$viewallcats) { $allowed_cats = $usercats; if (!empty($allowed_cats)) { $where[] = " rel.catid IN (" . implode(',', $allowed_cats) . ") "; } else { $field->html = JText::_('FLEXI_CANNOT_EDIT_FIELD') . ': <br/> ' . JText::_('FLEXI_NO_ACCESS_TO_USE_ANY_CATEGORIES'); return; } } } } // TYPE SCOPE if (($method_types == 2 || $method_types == 3) && (!count($types) || empty($types[0]))) { $field->html = 'Content Type scope is set to include/exclude but no Types are selected in field configuration, please set to "ALL" or select types to include/exclude'; return; } if ($method_types == 2) { $where[] = ' ie.type_id NOT IN (' . implode(',', $types) . ')'; } else { if ($method_types == 3) { $where[] = ' ie.type_id IN (' . implode(',', $types) . ')'; } } // include method // OTHER SCOPE LIMITS if ($samelangonly) { $where[] = $item->language == '*' ? " ie.language='*' " : " (ie.language='{$item->language}' OR ie.language='*') "; } if ($onlypublished) { $where[] = " i.state IN (1, -5) "; } if ($ownedbyuser == 1) { $where[] = " i.created_by = " . $user->id; } else { if ($ownedbyuser == 2) { $where[] = " i.created_by = " . $item->created_by; } } $where = !count($where) ? "" : " WHERE " . implode(" AND ", $where); // *********************************************** // Item retrieving query ... CREATE ORDERBY CLAUSE // *********************************************** $order = $field->parameters->get('orderby_form', 'alpha'); // TODO: add more orderings: commented, rated $orderby = flexicontent_db::buildItemOrderBy($field->parameters, $order, $request_var = '', $config_param = '', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order = '', $default_order_dir = '', $sfx = '_form', $support_2nd_lvl = false); // Create JOIN for ordering items by a most rated if (in_array('author', $order) || in_array('rauthor', $order)) { $orderby_join = ' LEFT JOIN #__users AS u ON u.id = i.created_by'; } // ***************************************************** // Item retrieving query ... put together and execute it // ***************************************************** $query = 'SELECT i.title, i.id, i.catid, i.state, i.alias' . ", GROUP_CONCAT(rel.catid SEPARATOR ',') as catlist" . ' FROM #__content AS i ' . ($samelangonly || $method_types > 1 ? " LEFT JOIN #__flexicontent_items_ext AS ie on i.id=ie.item_id " : "") . ' JOIN #__flexicontent_cats_item_relations AS rel on i.id=rel.itemid ' . @$orderby_join . $where . " GROUP BY rel.itemid " . $orderby; $db->setQuery($query); $items_arr = $db->loadObjectList(); if ($db->getErrorNum()) { echo $db->getErrorMsg(); $field->html = ''; return false; } // ******************************************************* // Create category tree to use for selecting related items // ******************************************************* require_once JPATH_ROOT . DS . "components" . DS . "com_flexicontent" . DS . "classes" . DS . "flexicontent.categories.php"; $tree = flexicontent_cats::getCategoriesTree(); // Get categories without filtering if ($allowed_cats) { foreach ($allowed_cats as $catid) { $allowedtree[$catid] = $tree[$catid]; } } if ($disallowed_cats) { foreach ($disallowed_cats as $catid) { unset($tree[$catid]); } $allowedtree =& $tree; } if (!$allowed_cats && !$disallowed_cats) { $allowedtree =& $tree; } // ***************************************** // Create field's HTML display for item form // ***************************************** static $common_css_js_added = false; if (!$common_css_js_added) { $common_css_js_added = true; flexicontent_html::loadFramework('select2'); $css = '' . '.fcrelation_field_used_items, .fcrelation_field_unused_items, .fcrelation_field_controls { display:inline-block; float:left !important; margin: 0 0 8px 0; }' . '.fcrelation_field_used_items.fc_vertical, .fcrelation_field_unused_items.fc_vertical { min-width: 100%; }' . '.fcrelation_field_used_items.fc_horizontal, .fcrelation_field_unused_items.fc_horizontal { margin: 8px 0%; }' . '.fcrelation_field_controls.fc_vertical { min-width: 100%; }' . '.fcrelation_field_controls.fc_horizontal { max-width:6%; margin: 48px 1% 0 1%; width: auto; }' . '.fcrelation_field_controls.fc_horizontal span.fcrelation_btn { float: left !important; clear: both !important; }' . '.fcfield-placement-h.fc_horizontal { display: none !important; }' . '.fcfield-placement-v.fc_vertical { display: none !important; }' . '.fcrelation_field_filters { display:inline-block; float:left !important; }' . '.fcrelation_field_filters span.label { min-width: 140px; }' . '.fcrelation_field_used_items select, .fcrelation_field_unused_items select { min-width: 100%; margin:0px; }'; if ($css) { $document->addStyleDeclaration($css); } } // The split up the items $items_options = ''; $items_options_select = ''; $items_options_unused = ''; $state_shortname = array(1 => 'P', 0 => 'U', -1 => 'A', -3 => 'PE', -4 => 'OQ', -5 => 'IP'); foreach ($items_arr as $itemdata) { $itemtitle = mb_strlen($itemdata->title) > $maxtitlechars ? mb_substr($itemdata->title, 0, $maxtitlechars) . "..." : $itemdata->title; if ($prepend_item_state) { $statestr = "[" . @$state_shortname[$itemdata->state] . "] "; $itemtitle = $statestr . $itemtitle . " "; //.$itemdata->catlist; } $itemcat_arr = explode(",", $itemdata->catlist); $classes_str = ""; $itemid = $itemdata->id; foreach ($itemcat_arr as $catid) { $classes_str .= " " . "cat_" . $catid; } if (isset($_itemids_catids[$itemid])) { $items_options .= '<option class="' . $classes_str . '" value="' . $_itemids_catids[$itemid]->value . '" >' . $itemtitle . '</option>' . "\n"; $items_options_select .= '<option selected="selected" class="' . $classes_str . '" value="' . $_itemids_catids[$itemid]->value . '" >' . $itemtitle . '</option>' . "\n"; } else { $items_options_unused .= '<option class="' . $classes_str . '" value="' . $itemid . '" >' . $itemtitle . '</option>' . "\n"; } } $cat_selected = count($allowedtree) == 1 ? reset($allowedtree) : ''; $cat_selecor_box_style = count($allowedtree) == 1 ? 'style="display:none;" ' : ''; $_cat_selector = flexicontent_cats::buildcatselect($allowedtree, $ri_field_name . '_fccats', $catvals = $cat_selected ? $cat_selected->id : '', $top = 2, ' class="use_select2_lib ' . $ri_field_name . '_fccats" ', $check_published = true, $check_perms = true, $actions_allowed = array('core.create', 'core.edit', 'core.edit.own'), $require_all = false, $skip_subtrees = array(), $disable_subtrees = array(), $custom_options = array('__ALL__' => 'FLEXI_RIFLD_FILTER_LIST_ALL')); if ($title_filter) { $document->addScript(JURI::root(true) . '/components/com_flexicontent/assets/js/filterlist.js'); $_title_filtering = '' . '<input class="fcfield_textval" id="' . $ri_field_name . '_regexp" name="' . $ri_field_name . '_regexp" onKeyUp="' . $ri_field_name . '_titlefilter.set(this.value)" size="30" onfocus="if (this.value==\'' . $default_value_title_filter . '\') this.value=\'\';" onblur="if (this.value==\'\') this.value=\'' . $default_value_title_filter . '\';" value="' . $default_value_title_filter . '" />' . '<input class="fcfield-button" type="button" onclick="' . $ri_field_name . '_titlefilter.reset();this.form.' . $ri_field_name . '_regexp.value=\'\'" value="' . JText::_('FLEXI_RIFLD_RESET') . '" />'; } $field->html .= ' <div class="fcfieldval_container valuebox fcfieldval_container_' . $field->id . '"> <span class="fcrelation_field_filters"> <span class="fcrelation_field_filter_by_cat nowrap_box" ' . $cat_selecor_box_style . '> ' . ($display_cat_filter_label ? '<span class="label">' . JText::_('FLEXI_RIFLD_FILTER_BY_CAT') . '</span>' : '') . ' ' . $_cat_selector . ' </span> ' . ($title_filter ? ' <span class="fcrelation_field_filter_by_title nowrap_box"> ' . ($display_title_filter_label ? '<span class="label">' . JText::_('FLEXI_RIFLD_FILTER_BY_TITLE') . '</span>' : '') . ' ' . $_title_filtering . ' </span> ' : '') . ' </span> <div class="fcclear"></div> '; $initial_placement = $field->parameters->get('initial_placement', 'h'); $placement_class = $initial_placement == 'h' ? ' fc_horizontal' : ' fc_vertical'; $field->html .= ' <span class="fcrelation_field_unused_items' . $placement_class . '"> <span class="label">' . JText::_($select_items_prompt) . '</span><br/> <select id="' . $ri_field_name . '_visitems" name="' . $ri_field_name . '_visitems[]" multiple="multiple" class="fcfield_selectmulval" ' . $size . ' > </select> </span> <span class="fcrelation_field_controls' . $placement_class . '"> <span id="btn-add_' . $ri_field_name . '" class="fcrelation_btn fcfield-list-add ' . $placement_class . '" title="' . JText::_('FLEXI_ADD') . '"></span> <span id="btn-remove_' . $ri_field_name . '" class="fcrelation_btn fcfield-list-del ' . $placement_class . '" title="' . JText::_('FLEXI_REMOVE') . '"></span> <span id="btn-toggle_horizontal_' . $ri_field_name . '" class="fcrelation_btn fcfield-placement-h fc_toggle ' . $placement_class . '" onclick="jQuery(this).closest(\'.valuebox\').find(\'.fc_vertical\').removeClass(\'fc_vertical\').addClass(\'fc_horizontal\');" title="' . JText::_('FLEXI_HORIZONTAL') . '"></span> <span id="btn-toggle_vertical_' . $ri_field_name . '" class="fcrelation_btn fcfield-placement-v fc_toggle ' . $placement_class . '" onclick="jQuery(this).closest(\'.valuebox\').find(\'.fc_horizontal\').removeClass(\'fc_horizontal\').addClass(\'fc_vertical\');" title="' . JText::_('FLEXI_VERTICAL') . '"></span> </span> <span class="fcrelation_field_used_items' . $placement_class . '"> <span class="label">' . JText::_($selected_items_label) . '</span><br/> <select id="' . $ri_field_name . '" name="' . $fieldname . '" multiple="multiple" class="' . $required . '" style="display:none;" ' . $size . ' > ' . $items_options_select . ' </select> <select id="' . $ri_field_name . '_selitems" name="' . $ri_field_name . '_selitems[]" multiple="multiple" class="fcfield_selectmulval" ' . $size . ' > ' . $items_options . ' </select> <select id="' . $ri_field_name . '_hiditems" name="' . $ri_field_name . '_hiditems" style="display:none;" > ' . $items_options_unused . ' </select> </span> </div> '; $js = ($title_filter ? ' var filteredfield, ' . $ri_field_name . '_titlefilter;' : '') . "\n\njQuery(document).ready(function() {\n\n" . ($title_filter ? ' filteredfield = document.getElementById("' . $ri_field_name . '_visitems"); ' . $ri_field_name . '_titlefilter = new filterlist( filteredfield ); ' : '') . "\n\n jQuery('#btn-add_" . $ri_field_name . "').click(function(){\n jQuery('#" . $ri_field_name . "_visitems option:selected').each( function() {\n jQuery('#" . $ri_field_name . "_selitems').append(\"<option class='\"+jQuery(this).attr('class')+\"' value='\"+jQuery(this).val()+\"'>\"+jQuery(this).text()+\"</option>\");\n jQuery('#" . $ri_field_name . "').append(\"<option selected='selected' class='\"+jQuery(this).attr('class')+\"' value='\"+jQuery(this).val()+\"'>\"+jQuery(this).text()+\"</option>\");\n jQuery(this).remove();\n });\n });\n jQuery('#btn-remove_" . $ri_field_name . "').click(function(){\n jQuery('#" . $ri_field_name . "_selitems option:selected').each( function() {\n jQuery('#" . $ri_field_name . "_visitems').append(\"<option class='\"+jQuery(this).attr('class')+\"' value='\"+jQuery(this).val()+\"'>\"+jQuery(this).text()+\"</option>\");\n jQuery(\"#" . $ri_field_name . " option[value='\"+jQuery(this).val()+\"']\").remove();\n jQuery(this).remove();\n });\n });\n\n});\n\njQuery(document).ready(function() {\n\t\n\tjQuery('#" . $ri_field_name . "_fccats').change(function() {\n\t\t\n\t\tvar " . $ri_field_name . "_fccats_val = jQuery('#" . $ri_field_name . "_fccats').val();\n\t\t\n\t\t" . ($title_filter ? $ri_field_name . "_titlefilter.reset(); this.form." . $ri_field_name . "_regexp.value='';" : "") . "\n\t\t\n\t jQuery('#" . $ri_field_name . "_visitems option').each( function() {\n\t \tvar data = jQuery(this).val().split(':'); \n\t \tvar itemid = data[0];\n\t \tjQuery('#" . $ri_field_name . "_hiditems').append(\"<option class='\"+jQuery(this).attr('class')+\"' value='\"+itemid+\"'>\"+jQuery(this).text()+\"</option>\");\n\t \tjQuery(this).remove();\n\t\t});\n\t\t\n\t jQuery('#" . $ri_field_name . "_hiditems option').each( function() {\n\t \tif ( " . $ri_field_name . "_fccats_val == '__ALL__' || jQuery(this).hasClass('cat_' + " . $ri_field_name . "_fccats_val ) ) {\n\t\t\t jQuery('#" . $ri_field_name . "_visitems').append(\"<option class='\"+jQuery(this).attr('class')+\"'value='\"+jQuery(this).val()+\":\"+ " . $ri_field_name . "_fccats_val+\"'>\"+jQuery(this).text()+\"</option>\");\n\t\t\t\tjQuery(this).remove();\n\t \t}\n\t\t});\n\t\t\n\t\t" . ($title_filter ? $ri_field_name . "_titlefilter.init();" : "") . "\n\t});\n\t" . (count($allowedtree) == 1 ? "jQuery('#" . $ri_field_name . "_fccats').trigger('change');" : "") . "\n\t\n});"; $document->addScriptDeclaration($js); }