/** This function buid the query to retrive content items
* based on assigned tags
* @param cpContentElement object $ce cp Custom Properties Object
* @param array &$search_pars array search_field=>search_value
* @param integer/array $tags tag id, if set, the search by tag is performed. If tags is an array, content items with all tag will be retrieved.
* @param integer $exclude_id article id to be excluded from results. Used for related items, for not showing the article itself.
* @param string $ordering ordering of the dataset
* @param object &$params component parameters
* @param integer $limit limit returned rows
* @return object returns the relevant rows or false if an error occurs
*/
function getRows($ce, &$search_pars, $tags = null, $exclude_id = null, $ordering, $limit = 0, $params)
{
global $mainframe;
$user = JFactory::getUser();
$aid = $user->get('aid', 0);
$database = JFactory::getDBO();
$language = $mainframe->getCfg('language');
$searchword = "";
$result = array();
$nullDate = $database->getNullDate();
$jnow =& JFactory::getDate();
$now = $jnow->toMySQL();
if (is_array($exclude_id)) {
$exclude_id = join($exclude_id, ',');
}
$got_JoomFish = $params->get('use_joomfish');
$selstr = array();
$fromstr = array();
$wherestr = array();
$orderstr = array();
$conditions = array();
/* WARNING : contains MOAQ (mother of all queries) *
* now approaching spaghetti complexity ;) */
$href_task = $ce->href_task ? "&task=" . $ce->href_task : "";
$href_view = $ce->href_view ? "&view=" . $ce->href_view : "";
$href_link = "";
if ($ce->slug_links) {
if ($ce->title_alias) {
$href_link = "&" . $ce->href_id . "=', c." . $ce->id . ",':',c." . $ce->title_alias;
} else {
$href_link = "&" . $ce->href_id . "=', c." . $ce->id . ",':',c." . $ce->title;
}
} else {
$href_link = "&" . $ce->href_id . "=', c." . $ce->id;
}
$selstr[] = "c." . $ce->id . " AS id";
$selstr[] = "c." . $ce->title . " AS title";
$selstr[] = ($ce->title_alias ? "c." . $ce->title_alias : "''") . " AS title_alias";
$selstr[] = "c." . $ce->introtext . " AS introtext";
$selstr[] = "CONCAT('index.php?option=" . $ce->href_option . $href_view . $href_task . $href_link . ") AS href";
$selstr[] = ($ce->created ? "c." . $ce->created : "''") . " AS created";
$selstr[] = ($ce->fulltext ? "c." . $ce->fulltext : "''") . " AS `fulltext`";
$selstr[] = ($ce->images ? "c." . $ce->images : "''") . " AS images";
if ($ce->sec_table) {
$selstr[] = "sec." . $ce->sec_table_id . " AS secid ";
$selstr[] = "sec." . $ce->sec_table_title . " AS section";
} else {
$selstr[] = "'" . $ce->cat_section_filter . "' AS section";
}
$fromstr[] = "#__" . $ce->table . " AS c";
if ($ce->cat_table) {
$fromstr[] = "LEFT JOIN #__" . $ce->cat_table . " AS cat ON(c." . $ce->catid . " = cat." . $ce->cat_table_id . ") ";
}
if ($ce->sec_table) {
$fromstr[] = "LEFT JOIN #__" . $ce->sec_table . " AS sec ON(c." . $ce->sectionid . " = sec." . $ce->sec_table_id . ") ";
}
if ($ce->state) {
if ($params->get('search_archived')) {
$wherestr[] = "c." . $ce->state . " <> '0'";
} else {
$wherestr[] = "c." . $ce->state . " = '1'";
}
}
if ($ce->cat_table && $ce->cat_table_published) {
$wherestr[] = "(cat." . $ce->cat_table_published . " = '1' OR cat." . $ce->cat_table_published . " IS NULL)";
}
if ($ce->sec_table && $ce->sec_table_published) {
$wherestr[] = "(sec." . $ce->sec_table_published . " = '1' OR sec." . $ce->sec_table_published . " IS NULL)";
}
if ($ce->publish_up) {
$wherestr[] = "( c." . $ce->publish_up . " = " . $database->Quote($nullDate) . " OR c." . $ce->publish_up . " <= " . $database->Quote($now) . " )";
}
if ($ce->publish_down) {
$wherestr[] = "( c." . $ce->publish_down . " = " . $database->Quote($nullDate) . " OR c." . $ce->publish_down . " >= " . $database->Quote($now) . " )";
}
// showing only one section , if navigating from "result_summary"
$section_filter = "";
if (($show_section = JRequest::getVar('show_section', '')) && $ce->sec_table_id) {
$show_section = $database->getEscaped($show_section);
$wherestr[] = "sec." . $ce->sec_table_id . " = '{$show_section}' ";
}
if (!$params->get('search_unauthorized') && $ce->access) {
$wherestr[] = "c." . $ce->access . " <= '{$aid}' ";
}
switch ($params->get('ordering_field')) {
case '1':
if ($ce->title_alias) {
$ordering_field = $ce->title_alias;
}
break;
case '0':
default:
$ordering_field = $ce->title;
break;
}
switch ($ordering) {
case 'category':
if (!$got_JoomFish) {
if ($ce->sec_table_title) {
$orderstr[] = "sec." . $ce->sec_table_title;
}
if ($ce->cat_table_title) {
$orderstr[] = "cat." . $ce->cat_table_title;
}
}
break;
case 'alpha':
if (!$got_JoomFish) {
$orderstr[] = "c.{$ordering_field}";
}
break;
// TODO rating sorting
/*
case 'rating':
$orderstr = "ORDER BY r.rating_sum / r.rating_count DESC, r.rating_sum DESC ";
break;
*/
// TODO rating sorting
/*
case 'rating':
$orderstr = "ORDER BY r.rating_sum / r.rating_count DESC, r.rating_sum DESC ";
break;
*/
case 'oldest':
$orderstr[] = "c." . $ce->created . " ASC";
break;
case 'newest':
default:
$orderstr[] = "c." . $ce->created . " DESC";
break;
}
if (!empty($tags) && !is_array($tags)) {
// TAG SEARCH
$search_pars['tagId'] = $tags;
$fromstr[] = "INNER JOIN #__custom_properties AS cp\n\t\t\t\tON(c." . $ce->id . " = cp.content_id)\n\t\t\t\tINNER JOIN #__custom_properties_values AS v\n\t\t\t\tON (cp.value_id = v.id )";
$wherestr[] = "v.id = '{$tags}' ";
$wherestr[] = "cp.ref_table = '" . $ce->table . "' ";
} elseif (!empty($tags) && is_array($tags)) {
$search_pars['tagId'] = $tags;
$i = 0;
foreach ($tags as $tag) {
$fromstr[] = "INNER JOIN #__custom_properties AS cp{$i}\n\t\t\t\t\tON(c." . $ce->id . " = cp{$i}.content_id)\n\t\t\t\t\tINNER JOIN #__custom_properties_values AS v{$i}\n\t\t\t\t\tON (cp{$i}.value_id = v{$i}.id ) ";
$wherestr[] = "v{$i}.id = '{$tag}' ";
$wherestr[] = "cp{$i}.ref_table = '" . $ce->table . "' ";
$i++;
}
if ($exclude_id) {
$wherestr[] = "c." . $ce->id . " not in ({$exclude_id}) ";
}
} else {
// STANDARD SEARCH
$i = 0;
foreach ($_REQUEST as $key => $field_values) {
if (strpos($key, 'cp_') === 0) {
$field_name = $database->getEscaped(substr($key, 3));
if (($field_type = $this->_getFieldType($field_name, $aid)) && $field_values != "") {
$search_pars[$key] = $field_values;
$wherestr[] = "cp{$i}.ref_table = '" . $ce->table . "'";
$fromstr[] = "INNER JOIN #__custom_properties AS cp{$i}\n\t\t\t\t\t\t\tON(c." . $ce->id . " = cp{$i}.content_id)\n\t\t\t\t\t\t\tINNER JOIN #__custom_properties_values AS v{$i}\n\t\t\t\t\t\t\tON (cp{$i}.value_id = v{$i}.id )\n\t\t\t\t\t\t\tINNER JOIN #__custom_properties_fields AS f{$i}\n\t\t\t\t\t\t\tON (v{$i}.field_id = f{$i}.id ) ";
$conditions = array();
if ($field_type == 'checkbox') {
foreach ($field_values as $value) {
$value = $database->getEscaped($value);
$conditions[] = "(f{$i}.name = '{$field_name}' AND v{$i}.name = '{$value}' AND f{$i}.published='1' AND f{$i}.access <= '{$aid}' )";
}
$wherestr[] = "(" . implode(" OR ", $conditions) . ")";
} elseif ($field_type == 'select') {
$field_values = $database->getEscaped($field_values);
$wherestr[] = "(f{$i}.name = '{$field_name}' AND v{$i}.name = '{$field_values}' AND f{$i}.published='1' AND f{$i}.access <= '{$aid}' ) ";
} elseif ($field_type == 'text') {
// 1st find the tags that match the text
if ($tags = $this->_getTagsByText($field_name, $field_values, $aid, $got_JoomFish)) {
$j = 0;
$conditions = array();
foreach ($tags as $tag) {
$fromstr[] = "INNER JOIN #__custom_properties AS cp_" . $i . "_" . $j . " ON (c." . $ce->id . " = cp_" . $i . "_" . $j . ".content_id) ";
$conditions[] = "cp_" . $i . "_" . $j . ".value_id = '{$tag}' ";
$j++;
}
$wherestr[] = "(" . implode(" OR ", $conditions) . ")";
} else {
array_push($this->errors, "no match");
return false;
}
}
} elseif ($field_name == "text_search" && $field_values != "") {
$field_values = JFilterInput::clean($field_values);
if (strlen(trim($field_values)) < 3) {
array_push($this->errors, "text short");
return false;
}
$searchword = $field_values;
$search_pars[$key] = $searchword;
$this->searchword = $searchword;
$searchword = $database->getEscaped($searchword);
$fulltext = $ce->fulltext ? ", c." . $ce->fulltext : '';
// translation of search text with JoomFish
if ($got_JoomFish) {
$fromstr[] = "LEFT JOIN #__jf_content as jfc ON jfc.reference_id = c." . $ce->id . " LEFT JOIN #__languages as jfl ON jfc.language_id = jfl.id ";
$wherestr[] = "( (jfc.value like '%{$searchword}%' AND jfl.code = '{$language}') \n" . "\n OR CONCAT(c." . $ce->title . ",' ', c." . $ce->introtext . ",' '" . $fulltext . ") LIKE '%{$searchword}%' ) \n";
} else {
$wherestr[] = "\n CONCAT(c." . $ce->title . ",' ', c." . $ce->introtext . ",' '" . $fulltext . ") LIKE '%{$searchword}%' \n";
}
} else {
continue;
}
$i++;
}
}
}
// Filter by section
// component section scope
if ($ce->sec_table) {
$global_sections = array();
if (($search_sections = $params->get('search_sections')) != "") {
$global_sections = explode(',', $search_sections);
}
// module section scope
$module_sections = array();
if (($search_sections = preg_replace('/[^0-9\\,]/', '', JRequest::getVar("bind_to_section"))) != "") {
$module_sections = explode(',', $search_sections);
}
if (count($global_sections) and !count($module_sections)) {
$section_scope = implode(',', $global_sections);
} elseif (!count($global_sections) and count($module_sections)) {
$section_scope = implode(',', $module_sections);
} elseif (count($global_sections) and count($module_sections)) {
$section_scope = implode(',', array_intersect($global_sections, $module_sections));
// if no intersection , global rules
if (empty($section_scope)) {
$section_scope = implode(',', $global_sections);
}
}
if (!empty($section_scope)) {
$wherestr[] = "sec." . $ce->sec_table_id . " IN ({$section_scope}) ";
}
}
/* TODO rating
if($ordering == 'rating'){
$fromstr .= "LEFT JOIN #__content_rating AS r on(c.id = r.content_id) ";
}
*/
$limitstr = "";
if ($limit > 0) {
$limitstr = " LIMIT {$limit} ";
}
$selstr = join($selstr, ",");
$fromstr = join($fromstr, " \n");
$wherestr = join($wherestr, "\n AND ");
$orderstr = join($orderstr, ",");
$this->fromstr = $fromstr;
$this->wherestr = $wherestr;
// eventually putting the query together ...
$query = "SELECT DISTINCT " . $selstr . "\n" . " FROM " . $fromstr . "\n" . " WHERE " . $wherestr . "\n" . (!empty($orderstr) ? " ORDER BY " . $orderstr : "") . $limitstr;
if (!empty($tags) && !is_array($tags) && $params->get('show_tags')) {
$this->tagname = getTagNameById($tags, $params);
$search_pars['tagId'] = $tags;
}
if (!count($search_pars) > 0) {
array_push($this->errors, "no pars");
return false;
}
if (!empty($section_scope)) {
$search_pars['bind_to_section'] = $section_scope;
}
//TODO cleanup the cose print "<pre><tt>$query</tt></pre>";
$database->setQuery("{$query}");
$rows = $database->loadObjectList();
if ($rows === null) {
array_push($this->errors, "db error");
return false;
}
/* Tricky matter: sorting alpha with joomFish.
* Unreliable with SQL only, because you hardly get to know
* which table you are reading from.
* In this case we sort with Joomla API instead.
*/
if ($got_JoomFish && $ordering == 'alpha') {
JArrayHelper::sortObjects($rows, $ordering_field);
} elseif ($got_JoomFish && $ordering == 'category') {
JArrayHelper::sortObjects($rows, 'section');
}
return $rows;
}
}
if (isset($_GET['file'])) {
$CUR_FILE = $_GET['file'];
}
if (isset($_GET['img_id'])) {
$CUR_IMG_ID = round($_GET['img_id']);
if (isset($_GET['delTag'])) {
echo 'delTag ' . $_GET['delTag'] . '<br>';
removeTagFromBdd($CUR_IMG_ID, $_GET['delTag']);
}
}
if (isset($_POST['attachTag'])) {
addTagToImage($_POST['tagName'], $_POST['imgId']);
}
if (isset($_GET['addTagToFile'])) {
$tagName = getTagNameById($_GET['tagId']);
if ($tagName !== false) {
addTagToImage($tagName, $_GET['img_id']);
}
header('Location: image.php?img_id=' . $_GET['img_id']);
exit;
}
if (isset($_GET['remTagToFile'])) {
removeTagFromBdd(round($_GET['img_id']), round($_GET['tagId']));
header('Location: image.php?img_id=' . $_GET['img_id']);
exit;
}
if (isset($_GET['folder_id'])) {
$CUR_FOLDER_ID = $_GET['folder_id'];
}
if (isset($_GET['tag_id'])) {