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; }