function getQuerySorted($text, $phrase = '', $order = '', $areas = null, $categories = null, $fieldsfilter = null, $limit = 50, $tmpfieldid = "", $valuefieldid = "", $tmplistids = "", $rules = "", $fieldOrderid = "")
{
// echo "RRRULE: ".$rules;
$rulesrray = explode(",", $rules);
//echo "FIFIFIF:".$fieldsfilter."<br>";
$db = JFactory::getDbo();
$app = JFactory::getApplication();
$query = $db->getQuery(true);
$tag = JFactory::getLanguage()->getTag();
$query->select('a.id, a.title AS title, a.metadesc, a.metakey, a.created AS created, a.catid, a.publish_up , ' . 'CONCAT(a.introtext, a.fulltext) AS text, a.introtext, c.title AS section, ' . 'CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, ' . 'CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug' . ',d.fieldsid,d.value
');
//.'"2" AS browsernav');
$query->from('#__content AS a');
$query->innerJoin('#__categories AS c ON c.id=a.catid');
$query->innerJoin('#__fieldsattach_values AS d ON d.articleid=a.id');
$query->innerJoin('#__fieldsattach AS e ON d.fieldsid=e.id');
/*$query->where('('. $where .')' . 'AND a.catid IN(10,11) AND a.state=1 AND c.published = 1 AND a.access IN ('.$groups.') '
.'AND c.access IN ('.$groups.') '
.'AND (a.publish_up = '.$db->Quote($nullDate).' OR a.publish_up <= '.$db->Quote($now).') '
.'AND (a.publish_down = '.$db->Quote($nullDate).' OR a.publish_down >= '.$db->Quote($now).')' );
*/
$searchword = JRequest::getVar("searchword");
$where_fieldattach = "";
//echo "".$phrase;
if ($phrase == "any") {
$cont = 0;
$tmp = explode(" ", $searchword);
//echo "count:".count($tmp). " -- ".($tmp[0]);
if (count($tmp) > 0) {
if (!empty($tmp[0])) {
$where_fieldattach = " (";
foreach ($tmp as $obj) {
$obj = $db->Quote('%' . $db->getEscaped($obj, true) . '%', false);
$where = "(d.value LIKE " . $obj . ' OR ' . "a.title LIKE " . $obj . ' OR ' . "a.introtext LIKE " . $obj . ")";
$where_fieldattach .= $where;
if (count($tmp) > $cont + 1) {
$where_fieldattach .= ' AND ';
}
$cont++;
}
$where_fieldattach .= " )";
}
}
} else {
$text = $db->Quote('%' . $db->getEscaped($searchword, true) . '%', false);
$where_fieldattach = "(d.value LIKE " . $text . ' OR ' . "a.title LIKE " . $text . ' OR ' . "a.introtext LIKE " . $text . ")";
}
//Extra fields
$whereextra = "";
if (count($fieldsfilter) > 0 && empty($tmplistids)) {
$whereextra = "";
$cont_field = 0;
$arrayfieldsid = explode(",", $fieldsfilter);
//$arrayfieldsid = $fieldsfilter;
foreach ($arrayfieldsid as $fieldsid) {
$tmp1 = explode("_", $fieldsid);
$tmpfieldid = $tmp1[0];
$valuefieldid = "";
if (count($tmp1) > 1) {
$valuefieldid = $tmp1[1];
}
//echo "<br>BBB1:: ".$valuefieldid."<br>";
$valuefieldid = plgAdvancedsearchfieldsattachment::getValue($tmpfieldid, $valuefieldid);
//echo "<br>BBB2:: ".$valuefieldid."<br>";
//$where .= " ( d.fieldsid = ". $fieldsid. ' AND d.value LIKE '.$text .')';
//echo "<br />AAA".count($arrayfieldsid).">".($cont_field+1);
//if(!empty($tmpfieldid) && !empty( $tmp1[1])){
if (!empty($tmpfieldid) && !empty($valuefieldid)) {
//if(!empty($whereextra)) { $whereextra .= ' OR ';}
if (!empty($whereextra)) {
$whereextra .= ' OR ';
}
//LIKE
if ($rulesrray[$cont_field] == "LIKE") {
//echo "<br>Ssssad sdasd1:::: ".$tmpfieldid." --- ".$valuefieldid." END<br>";
$valuefieldid = $db->Quote('%' . $valuefieldid . '%', false);
//echo "<br>Ssssad sdasd2:::: ".$tmpfieldid." --- ".$valuefieldid." END<br>";
$whereextra .= " ( d.fieldsid = " . $tmpfieldid . ' AND d.value LIKE ' . $valuefieldid . ')';
//echo "<br>Ssssad sdasd3:::: ".$whereextra." END<br>";
}
//EQUAL
if ($rulesrray[$cont_field] == "EQUAL") {
$valuefieldid = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = " . $tmpfieldid . ' AND d.value = ' . $valuefieldid . ')';
}
//NOT EQUAL
if ($rulesrray[$cont_field] == "NOTEQUAL") {
$valuefieldid = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = " . $tmpfieldid . ' AND d.value != ' . $valuefieldid . ')';
}
//HIGHER
if ($rulesrray[$cont_field] == "HIGHER") {
$valuefieldid = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = " . $tmpfieldid . ' AND d.value > ' . $valuefieldid . ')';
}
//LOWER
if ($rulesrray[$cont_field] == "LOWER") {
$valuefieldid = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = " . $tmpfieldid . ' AND d.value < ' . $valuefieldid . ')';
}
//BETWEEN
if ($rulesrray[$cont_field] == "BETWEEN") {
$tmp = explode("|", $valuefieldid);
$valuefieldid_1 = $tmp[0];
$valuefieldid_2 = "";
if (count($tmp) > 1) {
$valuefieldid_2 = $tmp[1];
}
if (!empty($valuefieldid_1) && !empty($valuefieldid_2)) {
//Transform mydsql format
$type = plgAdvancedsearchfieldsattachment::getType($tmpfieldid);
if ($type == "date") {
$valuefieldid_1 = strtotime($valuefieldid_1);
$valuefieldid_1 = date("Y-m-d", $valuefieldid_1);
$valuefieldid_2 = strtotime($valuefieldid_2);
$valuefieldid_2 = date("Y-m-d", $valuefieldid_2);
}
$valuefieldid_1 = $db->Quote($db->getEscaped($valuefieldid_1, true), false);
$valuefieldid_2 = $db->Quote($db->getEscaped($valuefieldid_2, true), false);
$whereextra .= " (d.fieldsid = " . $tmpfieldid . " AND d.value BETWEEN " . $valuefieldid_1 . " AND " . $valuefieldid_2 . ") ";
}
}
}
$cont_field++;
}
$whereextra .= "";
}
/*$whereextra = "";
if(!empty($tmpfieldid) && !empty($valuefieldid))
{
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. ' AND d.value LIKE '.$valuefieldid .')';
}
*/
if (!empty($where_fieldattach)) {
$where_fieldattach .= " AND ";
}
if (!empty($whereextra)) {
$whereextra .= " AND ";
}
// $query->where( $whereextra. $where_fieldattach .' a.state=1 AND c.published = 1 AND a.access IN ('.$groups.') ');
$query->where($whereextra . $where_fieldattach . ' a.state=1 AND c.published = 1 ');
$query->where("e.published=1");
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('a.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ')');
$query->where('c.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ')');
}
$tmpcat = explode(",", $categories);
if (!empty($categories)) {
$query->where('c.id in (' . $categories . ')');
}
if (!empty($tmplistids)) {
$query->where('a.id in (' . $tmplistids . ')');
}
if (!empty($fieldOrderid) && is_numeric($fieldOrderid) && count($fieldsfilter) > 0) {
$query->where('d.fieldsid = ' . $fieldOrderid);
}
$query->order($order);
//$query->group('a.id');
//echo $query;
return $query;
}
function getQuery($text, $phrase='', $order='', $areas=null, $categories =null, $fieldsfilter=null, $limit=50, $tmpfieldid="", $valuefieldid="", $tmplistids="" )
{
$db = JFactory::getDbo();
$app = JFactory::getApplication();
$query = $db->getQuery(true);
$tag = JFactory::getLanguage()->getTag();
$query->select('a.id, a.title AS title, a.metadesc, a.metakey, a.created AS created, a.catid, a.publish_up , '
.'CONCAT(a.introtext, a.fulltext) AS text, a.introtext, c.title AS section, '
.'CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, '
.'CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug ');
//.'"2" AS browsernav');
$query->from('#__content AS a');
$query->innerJoin('#__categories AS c ON c.id=a.catid');
$query->innerJoin('#__fieldsattach_values AS d ON d.articleid=a.id');
/*$query->where('('. $where .')' . 'AND a.catid IN(10,11) AND a.state=1 AND c.published = 1 AND a.access IN ('.$groups.') '
.'AND c.access IN ('.$groups.') '
.'AND (a.publish_up = '.$db->Quote($nullDate).' OR a.publish_up <= '.$db->Quote($now).') '
.'AND (a.publish_down = '.$db->Quote($nullDate).' OR a.publish_down >= '.$db->Quote($now).')' );
*/
$searchword = JRequest::getVar("searchword");
$where_fieldattach="";
//echo "".$phrase;
if($phrase=="any"){
$cont=0;
$tmp = explode(" ", $searchword);
//echo "count:".count($tmp). " -- ".($tmp[0]);
if(count($tmp)>0)
{
if(!empty($tmp[0])){
$where_fieldattach =" (";
foreach($tmp as $obj){
$obj = $db->Quote('%'.$db->getEscaped($obj, true).'%', false);
$where = "(d.value LIKE ".$obj .' OR '."a.title LIKE ".$obj. ' OR ' . "a.introtext LIKE ".$obj .")";
$where_fieldattach .= $where;
if(count($tmp)>($cont+1)) $where_fieldattach .= ' OR ';
$cont++;
}
$where_fieldattach .=" )";
}
}
}else{
$text = $db->Quote('%'.$db->getEscaped($searchword, true).'%', false);
$where_fieldattach = "(d.value LIKE ".$text .' OR '."a.title LIKE ".$text. ' OR ' . "a.introtext LIKE ".$text .")";
}
//Extra fields
$whereextra = "";
if(count($fieldsfilter) && empty($tmplistids))
{
$whereextra = "";
$cont_field=0;
//echo "<br />FIELD:: ".$cont_field;
$arrayfieldsid = explode(",",$fieldsfilter);
foreach ($arrayfieldsid as $fieldsid)
{
$tmp1= explode("_",$fieldsid);
$tmpfieldid= $tmp1[0];
$valuefieldid="";
if(count($tmp1)>1) {
$valuefieldid = $tmp1[1];
}
$valuefieldid = plgAdvancedsearchfieldsattachment::getValue($tmpfieldid, $valuefieldid);
//$where .= " ( d.fieldsid = ". $fieldsid. ' AND d.value LIKE '.$text .')';
$valuefieldid = $db->Quote('%'.$db->getEscaped($valuefieldid, true).'%', false);
//echo "<br />AAA".count($arrayfieldsid).">".($cont_field+1);
if(!empty($tmpfieldid) && !empty( $tmp1[1])){
if(!empty($whereextra)) { $whereextra .= ' OR ';}
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. ' AND d.value LIKE '.$valuefieldid .')';
}
$cont_field++;
}
$whereextra .= "";
}
/*$whereextra = "";
if(!empty($tmpfieldid) && !empty($valuefieldid))
{
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. ' AND d.value LIKE '.$valuefieldid .')';
}
*/
if(!empty($where_fieldattach)) $where_fieldattach .= " AND ";
if(!empty($whereextra)) { $whereextra .= " AND "; }
// $query->where( $whereextra. $where_fieldattach .' a.state=1 AND c.published = 1 AND a.access IN ('.$groups.') ');
$query->where( $whereextra. $where_fieldattach .' a.state=1 AND c.published = 1 ');
//$query->group('a.id');
$query->order($order);
$query->order("a.id");
// $where .= ' AND a.publish_up >= "'.$year.'-'.$month.'-1" AND a.publish_up <= "'.$year.'-'.$month.'-31"' ;
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('a.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ')');
$query->where('c.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ')');
}
$tmpcat = explode(",",$categories);
if(!empty($categories)){
$query->where('c.id in (' . $categories . ')');
}
if(!empty($tmplistids)){
$query->where('a.id in (' . $tmplistids . ')');
}
/*
$fields = $this->params->get('fields');
if(!empty($fields))
{
$query->where('d.fieldsid in (' . $fields . ')');
}*/
return $query;
}
function getQuerySorted($text, $phrase = '', $order = '', $areas = null, $categories = null, $fieldsfilter = null, $limit = 50, $tmpfieldid = "", $valuefieldid = "", $tmplistids = "", $rules = "", $fieldOrderid = "")
{
// echo "RRRULE: ".$rules;
$rulesrray = explode(",", $rules);
//echo "FIFIFIF:".$fieldsfilter."<br>";
$db = JFactory::getDbo();
$app = JFactory::getApplication();
$query = $db->getQuery(true);
$tag = JFactory::getLanguage()->getTag();
$whereextra = '';
$arrayfieldsid = explode(",", $fieldsfilter);
//First get a fieldsattach rows
$fieldsrows = array();
foreach ($arrayfieldsid as $fieldsid) {
$tmp1 = explode("_", $fieldsid);
$tmpfieldid = $tmp1[0];
$tmp2 = plgAdvancedsearchfieldsattachment::getNameField($tmpfieldid);
//echo "<br>-->".$tmp2;
$fieldsrows[] = $this->toAscii($tmp2);
}
$str_query = "";
//Select global
$str_query = 'SELECT id, title AS title, metadesc, metakey, created AS created, catid, publish_up , ';
$str_query .= ' text, introtext, section, ';
$str_query .= 'slug, ';
$str_query .= 'catslug ';
//Fieldsattach rows
//echo "<br>". implode(",", $fieldsrows)."<br>";
//$build = ','. implode(",", $fieldsrows). ' ';
$tmpcont = 0;
$strtmp = '';
foreach ($fieldsrows as $fieldtitle) {
$strtmp .= ', tmp_' . $fieldtitle;
$tmpcont++;
}
$str_query .= $strtmp;
$str_query .= ' FROM ( ';
//Select global
$str_query .= 'SELECT id, title AS title, metadesc, metakey, created AS created, catid, publish_up , ';
$str_query .= ' text, introtext, section, ';
$str_query .= 'slug, ';
$str_query .= 'catslug ';
//$tmp_query .=',d.fieldsid,d.value ';
//Fieldsattach rows
//echo "<br>". implode(",", $fieldsrows)."<br>";
//$build = ','. implode(",", $fieldsrows). ' ';
$tmpcont = 0;
$strtmp = '';
foreach ($fieldsrows as $fieldtitle) {
$strtmp .= ', max(tmp_' . $fieldtitle . ') as tmp_' . $fieldtitle . ' ';
$tmpcont++;
}
$str_query .= $strtmp;
$str_query .= ' FROM ( ';
//END select global
if (count($fieldsfilter) > 0 && empty($tmplistids)) {
$whereextra = "";
$cont_field = 0;
//$arrayfieldsid = $fieldsfilter;
$conttotal = 0;
foreach ($arrayfieldsid as $fieldsid) {
$tmp_query = 'SELECT a.id, a.title AS title, a.metadesc, a.metakey, a.created AS created, a.catid, a.publish_up , ';
$tmp_query .= 'CONCAT(a.introtext, a.fulltext) AS text, a.introtext, c.title AS section, ';
$tmp_query .= 'CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, ';
$tmp_query .= 'CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug ';
//$tmp_query .=',d.fieldsid,d.value ';
//Fieldsattach rows
//echo "<br>". implode(",", $fieldsrows)."<br>";
//$build = ','. implode(",", $fieldsrows). ' ';
$tmpcont = 0;
$strtmp = '';
foreach ($fieldsrows as $fieldtitle) {
if ($conttotal == $tmpcont) {
$strtmp .= ', d.value as tmp_' . $fieldtitle . ' ';
} else {
$strtmp .= ', "" as tmp_' . $fieldtitle . ' ';
}
$tmpcont++;
}
$tmp_query .= $strtmp;
//TABLES
$tmp_query .= 'FROM #__content AS a ';
$tmp_query .= 'INNER JOIN #__categories AS c ON c.id=a.catid ';
$tmp_query .= 'INNER JOIN #__fieldsattach_values AS d ON d.articleid=a.id ';
$tmp_query .= 'INNER JOIN #__fieldsattach AS e ON d.fieldsid=e.id ';
//CONDITIONS
$searchword = JRequest::getVar("searchword");
$where_fieldattach = "";
//echo "".$phrase;
if ($phrase == "any") {
$cont = 0;
$tmp = explode(" ", $searchword);
//echo "count:".count($tmp). " -- ".($tmp[0]);
if (count($tmp) > 0) {
if (!empty($tmp[0])) {
$where_fieldattach = " (";
foreach ($tmp as $obj) {
$obj = $db->Quote('%' . $db->getEscaped($obj, true) . '%', false);
$where = "(d.value LIKE " . $obj . ' OR ' . "a.title LIKE " . $obj . ' OR ' . "a.introtext LIKE " . $obj . ")";
$where_fieldattach .= $where;
if (count($tmp) > $cont + 1) {
$where_fieldattach .= ' AND ';
}
$cont++;
}
$where_fieldattach .= " )";
}
}
} else {
$text = $db->Quote('%' . $db->getEscaped($searchword, true) . '%', false);
$where_fieldattach = "(d.value LIKE " . $text . ' OR ' . "a.title LIKE " . $text . ' OR ' . "a.introtext LIKE " . $text . ")";
}
//Extra fields
$tmp1 = explode("_", $fieldsid);
$tmpfieldid = $tmp1[0];
$valuefieldid = "";
if (count($tmp1) > 1) {
$valuefieldid = $tmp1[1];
}
//echo "<br>BBB1:: ".$valuefieldid."<br>";
$valuefieldid = plgAdvancedsearchfieldsattachment::getValue($tmpfieldid, $valuefieldid);
//echo "<br>BBB2:: ".$valuefieldid."<br>";
//$where .= " ( d.fieldsid = ". $fieldsid. ' AND d.value LIKE '.$text .')';
//$whereextra = '';
//echo "<br />AAA".count($arrayfieldsid).">".($cont_field)." VALUE::---".empty($valuefieldid);
//if(!empty($tmpfieldid) && !empty( $tmp1[1])){
/*if(!empty($tmpfieldid) && !empty($valuefieldid))
{*/
//LIKE
/*
if($rulesrray[$cont_field]=="LIKE"){
//echo "<br>Ssssad sdasd1:::: ".$tmpfieldid." --- ".$valuefieldid." END<br>";
$valuefieldidtmp = $db->Quote('%'.$valuefieldid.'%', false);
//echo "<br>Ssssad sdasd2:::: ".$tmpfieldid." --- ".$valuefieldid." END<br>";
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. "";
if(!empty($valuefieldid)) $whereextra .= " AND d.value LIKE ".$valuefieldidtmp ;
$whereextra .= " ) ";
//echo "<br>Ssssad sdasd3:::: ".$whereextra." END<br>";
}
//echo "<br>RULE sdasd3:::: ".$rulesrray[$cont_field]." cont:".$cont_field." END<br>";
//EQUAL
if($rulesrray[$cont_field]=="EQUAL"){
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. "";
if(!empty($valuefieldid)) $whereextra .= " AND d.value = ".$valuefieldidtmp ;
$whereextra .= " ) ";
}
//NOT EQUAL
if($rulesrray[$cont_field]=="NOTEQUAL"){
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. "";
if(!empty($valuefieldid)) $whereextra .= "AND d.value != ".$valuefieldidtmp ;
$whereextra .= " ) ";
}
//HIGHER
if($rulesrray[$cont_field]=="HIGHER"){
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. "";
if(!empty($valuefieldid)) $whereextra .= "AND d.value > ".$valuefieldidtmp .")";
$whereextra .= " ) ";
}
//LOWER
if($rulesrray[$cont_field]=="LOWER"){
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
$whereextra .= " ( d.fieldsid = ". $tmpfieldid. "";
if(!empty($valuefieldid)) $whereextra .= " AND d.value < ".$valuefieldidtmp .")";
$whereextra .= " ) ";
}
//BETWEEN
if($rulesrray[$cont_field]=="BETWEEN"){
$tmp = explode("|", $valuefieldid );
$valuefieldid_1 = $tmp[0];
$valuefieldid_2="";
if(count($tmp)>1) $valuefieldid_2 = $tmp[1];
if(!empty($valuefieldid_1) && !empty($valuefieldid_2)){
//Transform mydsql format
$type = plgAdvancedsearchfieldsattachment::getType($tmpfieldid);
if($type == "date")
{
$valuefieldid_1 = strtotime( $valuefieldid_1 );
$valuefieldid_1 = date("Y-m-d",$valuefieldid_1);
$valuefieldid_2 = strtotime( $valuefieldid_2 );
$valuefieldid_2 = date("Y-m-d",$valuefieldid_2);
}
$valuefieldid_1 = $db->Quote($db->getEscaped($valuefieldid_1, true), false);
$valuefieldid_2 = $db->Quote($db->getEscaped($valuefieldid_2, true), false);
$whereextra .= " (d.fieldsid = ". $tmpfieldid. " AND d.value BETWEEN ".$valuefieldid_1 ." AND ".$valuefieldid_2 .") ";
}
}
*/
//echo "<br>SISISIS<br>".$whereextra."<br>";
//}
$whereextra = " ( d.fieldsid = " . $tmpfieldid . ")";
$cont_field++;
if (!empty($where_fieldattach)) {
$where_fieldattach .= " AND ";
}
if (!empty($whereextra)) {
$whereextra .= " AND ";
}
// $query->where( $whereextra. $where_fieldattach .' a.state=1 AND c.published = 1 AND a.access IN ('.$groups.') ');
$tmp_query .= ' WHERE ' . $whereextra . ' ' . $where_fieldattach . ' a.state=1 ';
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$tmp_query .= ' AND a.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ') ';
$tmp_query .= ' AND c.language in (' . $db->Quote($tag) . ',' . $db->Quote('*') . ') ';
}
$tmpcat = explode(",", $categories);
//Remove ,
$tmpcat = trim($tmpcat, ',');
if (!empty($categories)) {
$tmp_query .= ' AND c.id in (' . $categories . ')';
}
if (!empty($tmplistids)) {
$tmp_query .= ' AND a.id in (' . $tmplistids . ')';
}
if (!empty($fieldOrderid) && is_numeric($fieldOrderid) && count($fieldsfilter) > 0) {
$tmp_query .= ' AND d.fieldsid = ' . $fieldOrderid;
}
//$tmp_query .= ' ORDER BY ' . $order;
$str_query .= $tmp_query . " ";
if (count($arrayfieldsid) - 1 > $conttotal) {
$str_query .= " UNION ";
}
$conttotal++;
}
//For
}
//
$str_query .= ' ) as T';
//WHERE FULL
$tmpcont = 0;
$first = true;
$whereextra = "";
foreach ($fieldsrows as $fieldtitle) {
$fieldsid = $arrayfieldsid[$tmpcont];
$tmp1 = explode("_", $fieldsid);
$tmpfieldid = $tmp1[0];
$valuefieldid = "";
if (count($tmp1) > 1) {
$valuefieldid = $tmp1[1];
}
//echo "<br>BBB1:: ".$valuefieldid."<br>";
$valuefieldid = plgAdvancedsearchfieldsattachment::getValue($tmpfieldid, $valuefieldid);
//echo "<br>RULE:: ".$rulesrray[$cont_field]." LLL:".$tmpcont."<br>";
$tmpwhere = '';
$tmpand = '';
//Remove space
$valuefieldid = trim($valuefieldid);
if (!empty($valuefieldid)) {
$tmpand .= ' AND ';
}
//LIKE
if ($rulesrray[$tmpcont] == "LIKE") {
$type = plgAdvancedsearchfieldsattachment::getType($tmpfieldid);
switch ($type) {
case "selecttree":
$this->SelectTreeChild($valuefieldid);
$valuefieldid = $valuefieldid . $this->tmp;
//$valuefieldidtmp = $db->Quote(''.$valuefieldid.'', false);
if (!empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' in (' . $valuefieldid . ')';
}
break;
case "select":
//AND VALUE
$valorselects = fieldattach::getValueSelect($tmpfieldid, $valuefieldid);
$valuefieldidtmp = $db->Quote('%' . $valuefieldid . '%', false);
if (!empty($valuefieldid) && !empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' LIKE ' . $valuefieldidtmp;
}
break;
default:
//echo "<br>Ssssad sdasd1:::: ".$tmpfieldid." --- ".$valuefieldid." END<br>";
$valuefieldidtmp = $db->Quote('%' . $valuefieldid . '%', false);
if (!empty($valuefieldid) && !empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' LIKE ' . $valuefieldidtmp;
}
}
//echo "<br>Ssssad sdasd3:::: ".$whereextra." END<br>";
}
//EQUAL
if ($rulesrray[$tmpcont] == "EQUAL") {
$valuefieldidtmp = $db->Quote($valuefieldid, false);
if (!empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' = ' . $valuefieldidtmp;
}
}
//NOT EQUAL
if ($rulesrray[$tmpcont] == "NOTEQUAL") {
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
if (!empty($valuefieldid)) {
$tmpwhere = 'tmp_' . $fieldtitle . '!= ' . $valuefieldidtmp;
}
}
//HIGHER
if ($rulesrray[$tmpcont] == "HIGHER") {
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
if (!empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' > ' . $valuefieldidtmp;
}
}
//LOWER
if ($rulesrray[$tmpcont] == "LOWER") {
$valuefieldidtmp = $db->Quote($db->getEscaped($valuefieldid, true), false);
if (!empty($valuefieldid)) {
$tmpwhere = ' tmp_' . $fieldtitle . ' < ' . $valuefieldidtmp;
}
}
//BETWEEN
if ($rulesrray[$tmpcont] == "BETWEEN") {
$tmp = explode("|", $valuefieldid);
$valuefieldid_1 = $tmp[0];
$valuefieldid_2 = "";
if (count($tmp) > 1) {
$valuefieldid_2 = $tmp[1];
}
if (!empty($valuefieldid_1) && !empty($valuefieldid_2)) {
//Transform mydsql format
$type = plgAdvancedsearchfieldsattachment::getType($tmpfieldid);
if ($type == "date") {
$valuefieldid_1 = strtotime($valuefieldid_1);
$valuefieldid_1 = date("Y-m-d", $valuefieldid_1);
$valuefieldid_2 = strtotime($valuefieldid_2);
$valuefieldid_2 = date("Y-m-d", $valuefieldid_2);
}
//$valuefieldid_1 = $db->Quote($db->getEscaped($valuefieldid_1, true), false);
//$valuefieldid_2 = $db->Quote($db->getEscaped($valuefieldid_2, true), false);
$tmpwhere = ' ( tmp_' . $fieldtitle . ' BETWEEN ' . $valuefieldid_1 . ' AND ' . $valuefieldid_2 . ') ';
}
}
//$strtmp .= ' tmp_'.$fieldtitle.' tmp_'.$fieldtitle.' ';
if (!empty($whereextra)) {
$whereextra .= $tmpand;
}
$whereextra .= $tmpwhere;
$tmpcont++;
}
$str_query .= ' GROUP BY id';
$str_query .= ') as W';
if (!empty($whereextra)) {
$str_query .= " WHERE " . $whereextra;
}
$str_query .= ' ORDER BY ' . $order;
//$query->group('a.id');
//echo $str_query;
return $str_query;
}
