Index: trunk/kernel/include/advsearch.php =================================================================== diff -u -N -r4231 -r6093 --- trunk/kernel/include/advsearch.php (.../advsearch.php) (revision 4231) +++ trunk/kernel/include/advsearch.php (.../advsearch.php) (revision 6093) @@ -8,7 +8,7 @@ var $SourceTable; var $Relationships; var $Ignored_Words; - var $CatClause; + var $CatClause; var $Keywords; var $Phrase = ""; var $SearchType; @@ -18,7 +18,7 @@ var $PctRating; var $SearchLogRecord = Array(); - + function clsAdvancedSearchResults($SearchSource,$DataClass, $ItemType = 0) { global $objConfig, $objSearchConfig; @@ -40,9 +40,9 @@ if(!is_object($objSearchConfig)) $objSearchConfig = new clsSearchConfigList(); $objSearchConfig->Clear(); - + if ($ItemType == 0) { - $objSearchConfig->Query_Item("SELECT * FROM ".$objSearchConfig->SourceTable." WHERE TableName='$SearchSource' AND AdvancedSearch=1 ORDER BY DisplayOrder"); + $objSearchConfig->Query_Item("SELECT * FROM ".$objSearchConfig->SourceTable." WHERE TableName='$SearchSource' AND AdvancedSearch=1 ORDER BY DisplayOrder"); } else { $tables = Array(1 => 'Category', 2 => 'News', @@ -69,159 +69,162 @@ { $limit = "LIMIT $Start,$PerPage"; } - $sql = "SELECT * FROM ".$this->ResultTable." ".$limit; + $sql = "SELECT * FROM ".$this->ResultTable." ".$limit; $this->Clear(); $rs = $this->adodbConnection->Execute($sql); return $this->Query_Item($sql); } - + function SetCategoryClause($whereclause) { $this->CatClause=$whereclause; } function AddRelationship($JoinTable,$JoinExpression=NULL) { - $this->Relationships[$JoinTable]=$JoinExpression; + $this->Relationships[$JoinTable]=$JoinExpression; } function SetKeywords($keywords) { - $this->Phrase=$keywords; + $this->Phrase=$keywords; $this->keywords = GetKeywords($keywords); } function AddAdvancedField($TableName,$FieldName,$Verb,$Value,$conj, $main_prefix) { global $objSearchConfig, $objConfig; - + static $x = 0, $customs_joined = Array(); - -// echo 'Adding ADVANCED SearchField: ['.$TableName.']; ['.$FieldName.']; ['.$Verb.']; ['.$Value.']; ['.$conj.']
'; - + + // echo 'Adding ADVANCED SearchField: ['.$TableName.']; ['.$FieldName.']; ['.$Verb.']; ['.$Value.']; ['.$conj.']
'; + $ml_formatter =& $this->Application->recallObject('kMultiLanguage'); if(!is_object($objSearchConfig)) - $objSearchConfig = new clsSeachConfigList(); - - if ($TableName == GetTablePrefix().'CustomField') { - // stupid custom fields already have prefix in SearchConfig table (not for in-commerce module :D) - $prefix = ''; - } - else { - $prefix = GetTablePrefix(); - } - $f = $prefix.$TableName.".".$FieldName; - $FieldConfig = $objSearchConfig->GetItemByName($TableName,$FieldName); - if(!is_object($FieldConfig)) { - return false; - } - - if ($FieldConfig->Get('ForeignField')) { - $x++; - $parts = explode(".",$FieldConfig->Get("ForeignField")); - $Table = $parts[0]; - $pseudo = 'c'.$x; - $Table = AddTablePrefix($Table); - $Field = $parts[1]; - $join = $FieldConfig->Get("JoinClause"); - $join = str_replace($Table, $pseudo, $join); - $join = str_replace("{ForeignTable}", $pseudo, $join); - $join = str_replace("{LocalTable}", $TableName, $join); - $join = str_replace("{Table}", AddTablePrefix($this->SourceTable), $join); - $join = '('.$join.' AND '.$pseudo.'.CustomFieldId='.$FieldConfig->Get('CustomFieldId').')'; - $this->AddRelationship($Table.' AS '.$pseudo, $join); + $objSearchConfig = new clsSeachConfigList(); - // for items that does not have appropriate records in CustomMetaData table - if ($Verb == 'notcontains' || $Verb == 'isnot') { - $or_not_null = ' OR '.$pseudo.'.'.$Field.' IS NULL'; - } - else { - $or_not_null = ''; - } + if ($TableName == GetTablePrefix().'CustomField') { + // stupid custom fields already have prefix in SearchConfig table (not for in-commerce module :D) + $prefix = ''; + } + else { + $prefix = GetTablePrefix(); + } + $f = $prefix.$TableName.".".$FieldName; + $FieldConfig = $objSearchConfig->GetItemByName($TableName,$FieldName); + if(!is_object($FieldConfig)) { + return false; + } - $FieldItem["where"] = str_replace('CustomMetaData', $pseudo, $FieldConfig->GetWhereClause($Verb,$Value)).$or_not_null; - $FieldItem["Field"] = $FieldConfig->Get("ForeignField"); - $FieldItem["Table"] = $pseudo; - $FieldItem["conjuction"] = $conj; - $FieldItem["weight"] = $FieldConfig->Get("Priority"); - } - else { - $classes = Array( 'Link' => '_clsLinkList', - 'News' => '_clsNewsList', - 'Topic' => '_clsTopicList', - 'Category' => 'clsCatList'); - $list_class = getArrayValue($classes, $TableName); - if ($list_class) { - $objList =& new $list_class(); - } - - if ($FieldConfig->Get('CustomFieldId')) { - $ml_formatter =& $this->Application->recallObject('kMultiLanguage'); - $FieldConfig->Set('FieldName', $ml_formatter->LangFieldName('cust_'.$FieldConfig->Get('CustomFieldId'))); - } - - switch ($FieldName) { - case 'HotItem': - $cutoff = $objList->GetHotValue(); - $Value == 1 ? $not = '' : $not = 'NOT '; - $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); - $where_clause .= ' OR ('.AddTablePrefix($TableName).'.HotItem=2 AND '. - $not.'('.AddTablePrefix($TableName). - '.Hits>='.$cutoff.'))'; - break; - case 'PopItem': - $cutoff = $objList->GetPopValue(); - $Value == 1 ? $not = '' : $not = 'NOT '; - $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); - $where_clause .= ' OR ('.AddTablePrefix($TableName).'.PopItem=2 AND '. - $not.'('.AddTablePrefix($TableName). - '.CachedRating>='.$cutoff.'))'; - break; - case 'NewItem': - $NewTime = $objConfig->Get($TableName.'_NewDays')*3600*24; - $Value == 1 ? $not = '' : $not = 'NOT '; - $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); - $where_clause .= ' OR ('.AddTablePrefix($TableName).'.NewItem=2 AND '. - $not.'('.adodb_mktime().'-'.AddTablePrefix($TableName). - '.CreatedOn<'.$NewTime.'))'; - break; - default: - $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); - break; - } + if ($FieldConfig->Get('ForeignField')) { + $x++; + $parts = explode(".",$FieldConfig->Get("ForeignField")); + $Table = $parts[0]; + $pseudo = 'c'.$x; + $Table = AddTablePrefix($Table); + $Field = $parts[1]; + $join = $FieldConfig->Get("JoinClause"); + $join = str_replace($Table, $pseudo, $join); + $join = str_replace("{ForeignTable}", $pseudo, $join); + $join = str_replace("{LocalTable}", $TableName, $join); + $join = str_replace("{Table}", AddTablePrefix($this->SourceTable), $join); + $join = '('.$join.' AND '.$pseudo.'.CustomFieldId='.$FieldConfig->Get('CustomFieldId').')'; + $this->AddRelationship($Table.' AS '.$pseudo, $join); - $FieldItem = Array(); - if ($FieldConfig->Get('CustomFieldId')) { - if (!getArrayValue($customs_joined, $main_prefix)) { - $item_table = $this->Application->getUnitOption($main_prefix, 'TableName'); - $custom_table = $this->Application->getUnitOption($main_prefix.'-cdata', 'TableName'); - $this->AddRelationship($custom_table.' AS '.$main_prefix.'_custom_data', $main_prefix.'_custom_data.ResourceId = '.$item_table.'.ResourceId'); - $customs_joined[$main_prefix] = true; - } - - $FieldItem['Table'] = $main_prefix.'_custom_data'; - $FieldItem['Field'] = $FieldConfig->Get('FieldName'); - } - else { - $FieldItem['Table'] = AddTablePrefix($TableName); - $FieldItem['Field'] = $FieldName; - } - - $FieldItem['where'] = $FieldItem['Table'].'.'.$where_clause; - $FieldItem['conjuction'] = $conj; - $FieldItem['weight'] = $FieldConfig->Get('Priority'); + // for items that does not have appropriate records in CustomMetaData table + if ($Verb == 'notcontains' || $Verb == 'isnot') { + $or_not_null = ' OR '.$pseudo.'.'.$Field.' IS NULL'; + } + else { + $or_not_null = ''; + } + $FieldItem["where"] = str_replace('CustomMetaData', $pseudo, $FieldConfig->GetWhereClause($Verb,$Value)).$or_not_null; + $FieldItem["Field"] = $FieldConfig->Get("ForeignField"); + $FieldItem["Table"] = $pseudo; + $FieldItem["conjuction"] = $conj; + $FieldItem["weight"] = $FieldConfig->Get("Priority"); + } + else { + $classes = Array( + 'Link' => '_clsLinkList', + 'News' => '_clsNewsList', + 'Topic' => '_clsTopicList', + 'Category' => 'clsCatList'); + $list_class = getArrayValue($classes, $TableName); + if ($list_class) { + $objList =& new $list_class(); + } - } - $this->FieldList[] = $FieldItem; + $prefix_fields = $this->Application->getUnitOption($main_prefix, 'Fields'); + if (isset($prefix_fields[$FieldName]) && $prefix_fields[$FieldName]['formatter'] == 'kMultiLanguage') { + $FieldConfig->Set('FieldName', $ml_formatter->LangFieldName($FieldName)); + } - - // create search record - $this->SearchLogRecord[] = language('la_Field').' "'.$FieldConfig->getHumanField().'" '.$FieldConfig->getHumanVerb($Verb).' '.language('la_Value').' "'.$FieldConfig->getHumanValue($Value).'" '.language('lu_'.$conj); - - unset($FieldItem); - + if ($FieldConfig->Get('CustomFieldId')) { + $FieldConfig->Set('FieldName', $ml_formatter->LangFieldName('cust_'.$FieldConfig->Get('CustomFieldId'))); + } + + switch ($FieldName) { + case 'HotItem': + $cutoff = $objList->GetHotValue(); + $Value == 1 ? $not = '' : $not = 'NOT '; + $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); + $where_clause .= ' OR ('.AddTablePrefix($TableName).'.HotItem=2 AND '. + $not.'('.AddTablePrefix($TableName). + '.Hits>='.$cutoff.'))'; + break; + case 'PopItem': + $cutoff = $objList->GetPopValue(); + $Value == 1 ? $not = '' : $not = 'NOT '; + $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); + $where_clause .= ' OR ('.AddTablePrefix($TableName).'.PopItem=2 AND '. + $not.'('.AddTablePrefix($TableName). + '.CachedRating>='.$cutoff.'))'; + break; + case 'NewItem': + $NewTime = $objConfig->Get($TableName.'_NewDays')*3600*24; + $Value == 1 ? $not = '' : $not = 'NOT '; + $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); + $where_clause .= ' OR ('.AddTablePrefix($TableName).'.NewItem=2 AND '. + $not.'('.adodb_mktime().'-'.AddTablePrefix($TableName). + '.CreatedOn<'.$NewTime.'))'; + break; + default: + $where_clause = $FieldConfig->GetWhereClause($Verb, $Value); + break; + } + + $FieldItem = Array(); + if ($FieldConfig->Get('CustomFieldId')) { + if (!getArrayValue($customs_joined, $main_prefix)) { + $item_table = $this->Application->getUnitOption($main_prefix, 'TableName'); + $custom_table = $this->Application->getUnitOption($main_prefix.'-cdata', 'TableName'); + $this->AddRelationship($custom_table.' AS '.$main_prefix.'_custom_data', $main_prefix.'_custom_data.ResourceId = '.$item_table.'.ResourceId'); + $customs_joined[$main_prefix] = true; + } + + $FieldItem['Table'] = $main_prefix.'_custom_data'; + $FieldItem['Field'] = $FieldConfig->Get('FieldName'); + } + else { + $FieldItem['Table'] = AddTablePrefix($TableName); + $FieldItem['Field'] = $FieldName; + } + + $FieldItem['where'] = $FieldItem['Table'].'.'.$where_clause; + $FieldItem['conjuction'] = $conj; + $FieldItem['weight'] = $FieldConfig->Get('Priority'); + + + } + $this->FieldList[] = $FieldItem; + + // create search record + $this->SearchLogRecord[] = language('la_Field').' "'.$FieldConfig->getHumanField().'" '.$FieldConfig->getHumanVerb($Verb).' '.language('la_Value').' "'.$FieldConfig->getHumanValue($Value).'" '.language('lu_'.$conj); + + unset($FieldItem); } - + function getKeywords(&$FieldConfig) { $ret = implode('
', $this->SearchLogRecord); @@ -232,38 +235,38 @@ { $sql = "SELECT * FROM ".GetTablePrefix()."SearchConfig WHERE TableName='".$this->SourceTable."' AND AdvancedSearch=1 AND CustomFieldId>0"; //echo $sql; - foreach($this->Relationships as $Table=>$clause) + foreach($this->Relationships as $Table=>$clause) { if(strlen($Table)>0 && $Table != "Category") $sql .= " OR TableName='".$Table."'"; } $ctable = GetTablePrefix()."CustomMetaData"; $rs = $this->adodbConnection->Execute($sql); - $CustomJoined = FALSE; + $CustomJoined = FALSE; while($rs && !$rs->EOF) { $x = $rs->fields["CustomFieldId"]; $t = $ctable." as c".$x; - $join = "(c$x.ResourceId=".GetTablePrefix().$this->SourceTable.".ResourceId AND c$x.CustomFieldId=".$rs->fields["CustomFieldId"].")"; + $join = "(c$x.ResourceId=".GetTablePrefix().$this->SourceTable.".ResourceId AND c$x.CustomFieldId=".$rs->fields["CustomFieldId"].")"; $this->AddRelationship($t,$join); - $f = "c".$x.".Value "; + $f = "c".$x.".Value "; $this->FieldList[] = $f; $this->FieldWeight[$f] = $rs->fields["Priority"]; $rs->MoveNext(); } - } + } function AddAdvancedFields() { $sql = "SELECT * FROM ".GetTablePrefix()."SearchConfig WHERE TableName='".$this->SourceTable."' AND AdvancedSearch=1 AND CustomFieldId=0"; //echo $sql; - foreach($this->Relationships as $Table=>$clause) + foreach($this->Relationships as $Table=>$clause) { if(strlen($Table)>0 && $Table != "Category") $sql .= " OR TableName='".$Table."'"; } $rs = $this->adodbConnection->Execute($sql); - + while($rs && !$rs->EOF) { $f = GetTablePrefix().$rs->fields["TableName"].".".$rs->fields["FieldName"]; @@ -273,58 +276,58 @@ } $this->AddAdvancedCustomFields(); }*/ - + /* ready to write the code to build the query and go */ function BuildWhereClause() { - $where = ""; + $where = ""; foreach($this->FieldList as $f) $where .= " (".$f["where"].") ".$f["conjuction"]; - + $cut_length = substr($where, -2) == 'OR' ? 2 : substr($where, -3) == 'AND' ? 3 : 0; $where = substr($where, 0, strlen($where) - $cut_length); return $where; } - + function PerformSearch($ItemType,$OrderBy=NULL,$InitTable=FALSE, $idlist=NULL) { static $SelectSQL, $OldItemType; global $objSession, $objItemTypes; - + // add recrod to log $objSearchList = new clsSearchLogList(); $objSearchList->UpdateKeyword($this->getKeywords(), 1); - + $ctype = $objItemTypes->GetItem($ItemType); $idField = $ctype->Get("SourceTable")."Id"; $this->SourceTable = AddTablePrefix($ctype->Get("SourceTable")); $result=0; $PopField = $ctype->Get("PopField"); - $RateField = $ctype->Get("RateField"); + $RateField = $ctype->Get("RateField"); $typestr = str_pad($ItemType,2,"0",STR_PAD_LEFT); $SelectSQL = "SELECT ".$this->SourceTable.".".$idField." as ItemId, ". $this->SourceTable.".Priority as Relevance, ".$this->SourceTable.".ResourceId, CONCAT($typestr) as ItemType, EditorsPick as EdPick FROM ".$this->SourceTable." "; - + foreach($this->Relationships as $JoinTable=>$OnClause) - { + { $SelectSQL .= "LEFT JOIN $JoinTable ON ($OnClause) "; } $first=1; $WhereClause = $this->BuildWhereClause(); if(strlen($WhereClause)>0) { $SelectSQL .= " WHERE ".$WhereClause; - } + } $SelectSQL .= " GROUP BY $idField "; //echo $SelectSQL; $this->adodbConnection->Execute("DROP TABLE IF EXISTS ".$this->ResultTable); - $full_sql = "CREATE TABLE ".$this->ResultTable." ".$indexSQL.$SelectSQL; + $full_sql = "CREATE TABLE ".$this->ResultTable." ".$indexSQL.$SelectSQL; $this->adodbConnection->Execute($full_sql); // if advanced seaech used, then delete simple search keywords $objSession->SetVariable('Search_Keywords', ''); //print_pre($full_sql); } - + function BuildIndexes() { $sql = "ALTER TABLE ".$this->ResultTable." ADD INDEX (Relevance), ";