# file: search.html # Copyright (c) 1996-2009 Cuesta Technologies, LLC # All rights reserved. # This document contains confidential and/or proprietary information # beloging to Cuesta Technologies, LLC. # Package: sss # # Purpose: # # Requires: # set pageID SEC_0 cw source searchlimit.tcl cw source mysql.tcl ###### You can come from 3 types link #### # 1. from other pages (except search page itself) at this web site. # 2. from a direct query with query string set by somebody # 3. from this search pages. # for each case we must have special method to deal with it. If you make some change, # please consider the factors of other situations. # 1. go direct and display the html, please pay attention to the default values. # 2. change cw_args to cw_val and search. # 3. use cw_val or cw_vals to search. ###### ### Advanced Search: Title: (Title, Title_Search & ### General_Media_Text) ### Keyword: (Title, Title_Search, Description, ### General_Media_Text & General_Catalog_Number). # Check to see if the the clear button was pressed. redo_form_values #cw log 2 "sdb(searchCriteriaPairs)=$sdb(searchCriteriaPairs)" if {[info exists cw_args(fromlist)] && [string match resort $cw_args(fromlist)]} { foreach pair $sdb(searchCriteriaPairs) { set field [lindex $pair 0] regsub $field $pair {} value if {[string match New $field]} { if {[info exists cw_val(New)] && [string match Yes $cw_val(New)]} { set cw_val($field) $value set cw_vals($field) $value } } else { set cw_val($field) $value set cw_vals($field) $value } } } ######## transfer cw_args to cw_val ##### if {[info exists cw_args(Record_Type)]&&[string length $cw_args(Record_Type)]} { # New Record_Type Media Grade_Level Subject Special_Features set transList {New Record_Type Media Grade_Level Subject Special_Features CA_Standard} foreach field $transList { if {[info exists cw_args($field)] } { set cw_val($field) $cw_args($field) set cw_vals($field) {} lappend cw_vals($field) $cw_args($field) } } set transList2 {Keyword Title Description General_Catalog_Number General_Media_Text} foreach field $transList2 { if {[info exists cw_args($field)] } { set cw_val(Description) $cw_args($field) set cw_vals(Description) {} lappend cw_vals($field) $cw_args($field) break } } } ######## end of trans cw_args to cw_val #### if {[info exists cw_val] || ([info exists cw_args(where)] && [string match template $cw_args(where)])} { cw_val_trimSpace set listcriteria "" ## List of fileds that need to do tlob search (word And word) set tlobFieldList "" ## List of fileds that are mult fileds. set multFieldList "" ## extra query string for special search pass to MySQLRecordSearch proc. set appendQueryString "" set results {} set sdb(search_result_list) "" set sdb(search_rlen) "" set sdb(searchcriteria) "" set sdb(searchCriteriaPairs) "" ## The following 3 sdb variables need to be remembered in search.html ## and used in list.html for re-do the search. set doSepSearch {} set sdb(doSepSearch) "" ## save for the sort type. marked_complain Title isonechar "A title search for one charactor should be avoided." marked_complain Keyword isonechar "A Keyword search for one charactor should be avoided." if {[info exists cw_val(quicksearch)]} { #cw log 0 "cw_val(quicksearch)======$cw_val(quicksearch)" set sdb(quicksearch) $cw_val(quicksearch) marked_complain quicksearch contains "keyword" AND quicksearch contains "item #" "Please input keywords search criteria." set cw_val(Keyword) $cw_val(quicksearch) set cw_vals(Keyword) $cw_val(quicksearch) } else { ## to show current sdb(quicksarch) on quick search box. set sdb(quicksearch) "" } set transList3 {Media Grade_Level Subject Record_Type} set valno 0 foreach var $transList3 { if { [info exists cw_val($var)] && [string length $cw_val($var)] && ![regexp "All" $cw_val($var)] && ![regexp "Product" $cw_val($var)]} { incr valno } } set transList4 {New Special_Features Title Description Keyword} foreach var $transList4 { if {[info exists cw_val($var)] && [string length $cw_val($var)]} { incr valno } } if { $valno == 0 } { lappend complaints "We're sorry -- your session may have timed out due to inactivity, or you may not have entered any search criteria. Please try your search again." } if { ![llength $complaints]} { MysqlCleancw_val if {[info exists cw_val(Record_Type)] && ([string match "Products" $cw_val(Record_Type)] || [string match "Sample Lessons" $cw_val(Record_Type)] || [string match "Video Previews" $cw_val(Record_Type)])} { set sdb(fromcontent) 0 lappend sdb(searchCriteriaPairs) "Record_Type $cw_val(Record_Type)" } else { set sdb(fromcontent) 3 } set omitlist {Title Keyword Record_Type} if {[info exists cw_val(Grade_Level)] && [string match "All Grades" $cw_val(Grade_Level)]} { lappend omitlist Grade_Level } if {[info exists cw_val(Media)] && [string match "All Media" $cw_val(Media)]} { lappend omitlist Media } if {[info exists cw_val(Subject)] && [string match "All Subjects" $cw_val(Subject)]} { lappend omitlist Subject } if {[info exists cw_val(New)] && [string match "No" $cw_val(New)]} { lappend omitlist New } set substitutepairs {} ## create product search list based on product_table.t and cw_vals. set psearchableList [GetSearchableList $cw_product_tablepath $omitlist] foreach item $psearchableList { lappend sdb(searchCriteriaPairs) "$item $cw_vals($item)" } ### Generate search criteria set criteriaList $psearchableList set listcriteria [GetSearchCriteria $criteriaList $substitutepairs] if {[info exists cw_val(Record_Type)]} { if {[string match "Products" $cw_val(Record_Type)]} { set appendQueryString [SetAppendQueryString $appendQueryString "(Record_Type='Product' OR Record_Type='Series')"] } else { set appendQueryString [SetAppendQueryString $appendQueryString "(Record_Type='$cw_val(Record_Type)')"] } ### 3.12.13 mes: remove Resource Type per client # append listcriteria "
   Resource Type: $cw_val(Record_Type)" } if {![info exists cw_val(SortResult)]} { set cw_val(SortResult) Relevance } set sdb(SortResult) $cw_val(SortResult) if {[info exists cw_val(SortResult)] && [regexp {Media} $cw_val(SortResult)]} { set sortBy "Media" } else { set sortBy "Title_Alpha" } ## Nov. 11 2008:all products appear for sss and wc when user does search, but for new and notable, only sss and wc products appear. if {![regexp {^(SSSS|WRITINGCO)$} $sdb(catalog_id)]} { set cidCode [mapelement $sdb(catalog_id) $cdb(catalogIDList) $cdb(catalogIDCodeList)] set appendQueryString [SetAppendQueryString $appendQueryString "(Featured_Site LIKE '%{$sdb(catalog_id)}%' OR Featured_Site LIKE '%{$cidCode}%')"] } set appendQueryStringSave $appendQueryString ## Keyword Search contains all search fields of Title Search. ## So if there is Keyword search and Title Search, we can just ## do Keyword search. if {[info exists cw_val(Keyword)] && [string length $cw_val(Keyword)]} { lappend sdb(searchCriteriaPairs) "Keyword $cw_val(Keyword)" ### Ignore all characters after the first hyphen for ### Keyword search. set ind [string first "-" $cw_val(Keyword)] if {$ind > 0 } { set cw_val(Keyword) [string range $cw_val(Keyword) 0 [expr $ind - 1]] set cw_vals(Keyword) $cw_val(Keyword) } if {[regexp {^(\")[^\"]*(\")$} $cw_val(Keyword)]} { set keywordwhole [string trim $cw_val(Keyword) \"] } if {[info exists cw_val(SortResult)] && [regexp {Relevance} $cw_val(SortResult)]} { lappend doSepSearch Keyword } else { set keywordORList {} foreach item $cdb(Keyword_Search_Fields) { if {[info exists keywordwhole]} { lappend keywordORList "$item LIKE '%$keywordwhole%'" } elseif {[lsearch $tlobFieldList $item] > -1 } { #lappend keywordORList [CreateAndSearchQuery $item $cw_val(Keyword)] lappend keywordORList [CreateOrderedTlobSearchQuery $item $cw_val(Keyword)] } else { lappend keywordORList "$item LIKE '%$cw_val(Keyword)%'" } } set keywordString "([join $keywordORList " OR "])" set appendQueryString [SetAppendQueryString $appendQueryString $keywordString] } append listcriteria "
   Keyword: $cw_val(Keyword)" } if {[info exists cw_val(Title)] && [string length $cw_val(Title)]} { lappend sdb(searchCriteriaPairs) "Title $cw_val(Title)" if {[regexp {^(\")[^\"]*(\")$} $cw_val(Title)]} { set titlewhole [string trim $cw_val(Title) \"] } if {[info exists cw_val(SortResult)] && [regexp {Relevance} $cw_val(SortResult)]} { lappend doSepSearch Title } else { set titleORList {} foreach item $cdb(Title_Search_Fields) { if {[info exists titlewhole]} { lappend titleORList "$item LIKE '%$titlewhole%'" } else { ## all fields in $cdb(Title_Search_Fields) are tlob lappend titleORList [CreateOrderedTlobSearchQuery $item $cw_val(Title)] } } set titleString "([join $titleORList " OR "])" set appendQueryString [SetAppendQueryString $appendQueryString $titleString] } append listcriteria "
   Title: $cw_val(Title)" } MysqlConnectProdDB set tmpNum [llength $doSepSearch] if {$tmpNum > 0} { set sdb(doSepSearch) $doSepSearch set headerCnt 0 #cw log 0 "cw_val($doSepSearch)=$cw_val($doSepSearch)" if {$tmpNum == 2} { if {[info exists titlewhole]} { set newTStr $titlewhole } else { set wordList [split $cw_val(Title) " ,;."] set newTStr [join $wordList "%"] } set titleqryStr1 "Title LIKE '%$newTStr%'" set titleqryStr2 "Title_Search LIKE '%$newTStr%'" if {[info exists keywordwhole]} { set newKStr $keywordwhole } else { set wordList [split $cw_val(Keyword) " ,;."] set newKStr [join $wordList "%"] } set titleqryStr1 "Title LIKE '%$newTStr%'" set titleqryStr2 "Title_Search LIKE '%$newTStr%'" set keywordqryStr1 "Title LIKE '%$newKStr%'" set keywordqryStr2 "Title_Search LIKE '%$newKStr%'" set tmpappendStr1 [SetAppendQueryString $appendQueryString "(($titleqryStr1 OR $titleqryStr2) AND ($keywordqryStr1 OR $keywordqryStr2))"] set tmpresult1 [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $tmpappendStr1] cw log 2 "Title:sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" cw log 2 "tmpresult1=$tmpresult1" if {[llength $tmpresult1]} { set tmpresult1 [linsert $tmpresult1 0 Match_found_in_Title] incr headerCnt set results [concat $results $tmpresult1] } set tmpappendStr2 [SetAppendQueryString $appendQueryString "(General_Media_Text LIKE '%$cw_val(Title)%' AND General_Media_Text LIKE '%$cw_val(Keyword)%')"] set tmpresult2 [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $tmpappendStr2] cw log 2 "General_Media_Text:sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" cw log 2 "tmpresult2=$tmpresult2" if {[llength $tmpresult2]} { set tmpresult2 [linsert $tmpresult2 0 Match_found_in_General_Media_Text] incr headerCnt set results [concat $results $tmpresult2] } set tmpappendStr3 [SetAppendQueryString $appendQueryString "($titleqryStr1 OR $titleqryStr2 OR General_Media_Text LIKE '%$cw_val(Title)%') AND Description LIKE '%$cw_val(Keyword)%'"] set tmpresult3 [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $tmpappendStr3] cw log 2 "Description:sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" cw log 2 "tmpresult3: [llength $tmpresult3]" if {[llength $tmpresult3]} { set tmpresult3 [linsert $tmpresult3 0 Match_found_in_Description] incr headerCnt set results [concat $results $tmpresult3] } set tmpappendStr4 [SetAppendQueryString $appendQueryString "General_Catalog_Number LIKE '%$cw_val(Keyword)%'"] set tmpresult4 [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $tmpappendStr4] cw log 2 "General_Catalog_Number:sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" cw log 2 "tmpresult4=$tmpresult4" if {[llength $tmpresult4]} { set tmpresult4 [linsert $tmpresult4 0 Match_found_in_General_Catalog_Number] incr headerCnt set results [concat $results $tmpresult4] } } else { # tmpNum = 1 Either Title or Keyword cw log 2 "doSepSearch=$doSepSearch" set inputLen [llength $cw_val($doSepSearch)] foreach field $cdb($doSepSearch\_Search_Fields) { if {[string match Title_Search $field]} { ## Title_Search is combined with "OR" Title search. ## we don't need to do separate search for it. continue } if {[string match Title $field]} { ## need to search Title and Title_Search as well. if {[info exists whole]} { set newStr $whole } else { set wordList [split $cw_val($doSepSearch) " ,;."] set newStr [join $wordList "%"] } set titleqryStr1 "Title LIKE '%$newStr%'" set titleqryStr2 "Title_Search LIKE '%$newStr%'" set tmpappendStr [SetAppendQueryString $appendQueryString "($titleqryStr1 OR $titleqryStr2)"] set tmpresult [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $tmpappendStr] } else { set psearchableList [disjunct $psearchableList $cdb($doSepSearch\_Search_Fields) ] lappend psearchableList $field set cw_val($field) $cw_val($doSepSearch) set tmpresult [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $appendQueryString] } cw log 2 "$doSepSearch-$field:sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" cw log 2 "tmpresult:[llength $tmpresult]" if {[llength $tmpresult]} { set tmpresult [linsert $tmpresult 0 Match_found_in_$field] incr headerCnt set results [concat $results $tmpresult] } } } set results [unique_op $results] set len [llength $results] ## don't count the "Match found in" Group header. set rlen [expr ($len - $headerCnt)] if {$rlen == 0 && ![info exists whole] && [info exists inputLen] && $inputLen > 1} { #cw log 2 "2nd round of search for relavance search!!!" set tmpResult [UnorderedTlobRelevanceSearch $doSepSearch $cw_val($doSepSearch)] set rlen [lindex $tmpResult 0] set results [lindex $tmpResult 1] if {$rlen == 0} { ## 3rd round search for relevance ## successively remove one of the words, beginning ## with the right-most word, and perform additional ## searches with these "n - 1" terms. set k $inputLen set origInput $cw_val($doSepSearch) #cw log 2 "origInput=$origInput" while {$k > 0 && $rlen == 0} { set idx [expr ($k - 1)] set tmpSearch [lreplace $origInput $idx $idx ] #set tmpSearch [lrange $origInput 0 [expr ($k-2)] ] ## Jan 20. 2010 Cindy: lreplace & lrange operations ## change the value of origInput. ## Ex:cw_val(Keyword):"uncle tom\'s cabin" contrasted with Buckingham Hall ## origInput="uncle tom\'s cabin" contrasted with Buckingham Hall ## after we do lreplace or lrange, tmpSearch={uncle tom's cabin} contrasted with Buckingham ## So we need to remove ' again. regsub -all {[\]+\'} $tmpSearch {'} tmpSearch regsub -all ' $tmpSearch {\\'} tmpSearch #cw log 2 "3rd for relevance: $tmpSearch" set tmpResult [UnorderedTlobRelevanceSearch $doSepSearch $tmpSearch] set rlen [lindex $tmpResult 0] set results [lindex $tmpResult 1] incr k -1 } } } } else { set sdb(doSepSearch) "" set results [MysqlRecordSearch $mysqlProdtbl $psearchableList Record_ID $sortBy {} {} {} {} $appendQueryString] cw log 2 "sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" set rlen [llength $results] cw log 2 "2nd round search. No input order require" if {([info exists cw_val(Keyword)] && [string length $cw_val(Keyword)]) || ([info exists cw_val(Title)] && [string length $cw_val(Title)])} { if {$rlen == 0 && ![info exists whole]} { set keywordLen [llength $cw_val(Keyword)] if {$keywordLen > 1} { set results [UnorderedTlobRegularSearch Keyword $cw_val(Keyword)] } elseif {[info exists cw_val(Title)]} { set results [UnorderedTlobRegularSearch Title $cw_val(Title)] } set rlen [llength $results] if {$rlen == 0 } { ## We successively remove one of the words, beginning ## with the right-most word, and perform additional ## searches with these "n - 1" terms. " if {[info exists keywordLen] && $keywordLen > 1} { set m $keywordLen while {$m > 0 && $rlen ==0} { set idx [expr ($m -1)] set tmpSearch [lreplace $cw_val(Keyword) $idx $idx ] #cw log 2 "3rd round search Keyword: $tmpSearch" set results [UnorderedTlobRegularSearch Keyword $tmpSearch] set rlen [llength $results] incr m -1 } } elseif {[info exists titleLen] && $titleLen > 1} { set n $titleLen while {$n > 0 && $rlen ==0} { set idx [expr ($n -1)] set tmpSearch [lreplace $cw_val(Title) $idx $idx ] #cw log 2 "3rd round search Title: $tmpSearch" set results [UnorderedTlobRegularSearch Title $tmpSearch] set rlen [llength $results] incr n -1 } } } } } } MysqlDisconnect if {[info exists cw_args(where)]} { set listcriteria "Your search criteria:
Keyword or Item#: $cw_val(Keyword)
" cw stats QUICKSEARCH $cw_val(Keyword) } else { cw stats SEARCHPAGE search } if {[info exists cw_val(Subject)] && ![regexp "All Subjects" $cw_val(Subject)]} { cw_STATS PF-SUBJECT $cw_vals(Subject) } if {[info exists cw_vals(Media)] && ![regexp "All Media" $cw_vals(Media)] } { cw_STATS PF-MEDIA $cw_vals(Media) } if {[info exists cw_vals(Description)]} { cw_STATS PF-DESCRIPTION $cw_vals(Description) } if {[info exists cw_val(Title)] && [string length $cw_val(Title)]} { cw_STATS PF-TITLE $cw_val(Title) } if {[info exists cw_val(Keyword)] && [string length $cw_val(Keyword)]} { cw_STATS PF-KEYWORD $cw_val(Keyword) } if {[info exists cw_vals(Grade_Level)] && ![regexp "All Grades" $cw_vals(Grade_Level)] } { cw_STATS PF-GRADE $cw_vals(Grade_Level) } if {[info exists cw_vals(General_Media_Text)]} { cw_STATS PF-GENERAL_MEDIA_TEXT $cw_vals(General_Media_Text) } if {[info exists cw_vals(General_Catalog_Number)]} { cw_STATS PF-GENERAL_CATALOG_NUBBER $cw_vals(General_Catalog_Number) } if {[info exists cw_vals(Record_Type)]} { cw_STATS PF-RECORD $cw_vals(Record_Type) } if {[info exists cw_vals(New)]} { set sdb(New) $cw_vals(New) cw_STATS PF-NEW $cw_vals(New) } else { set sdb(New) "" } if {[info exists cw_vals(Special_Features)]} { cw_STATS PF-SPECIAL_FEATURES $cw_vals(Special_Features) } cw_STATS PF-SEARCH "Search " if {[info exists cw_val(searchLimit)]} { set sdb(searchLimit) $cw_val(searchLimit) } else { set sdb(searchLimit) $cdb(searchLimit) } if {[info exists cw_val(List_Type)]} { set sdb(List_Type) $cw_val(List_Type) } else { set sdb(List_Type) "Title with Brief Description" } set sdb(listcriteria) $listcriteria if { $rlen == 0 } { set noresult 1 } elseif { $rlen == 1 } { foreach item $results { if {[regexp Match_found_in $item]} { continue } set rec $item } set sdb(search_result_list) $rec cw redirect product.html$nocache+record@$rec } elseif {$rlen > $cdb(SearchMax) } { set toomanyresult 1 } else { set sdb(search_result_list) $results set sdb(search_rlen) $rlen cw redirect list.html$nocache+curList@0 } } } cw source $cw_templatePath/templateTop.tcl ######################## # Add your own code here ######################## if {[info exists noresult] && [string match 1 $noresult]} { puts "There are no items that meet your selected criteria. Please try again.

" } if {[info exists toomanyresult] && [string match 1 $toomanyresult]} { puts "There are too many titles that meet your selected criteria. Please refine your search.

" } puts "

" # #" puts " " if {[info exists cdb(Special_Features)] && [llength $cdb(Special_Features)]} { puts "" } puts "
Title" complain_mark Title "*" puts "" cw_text Title {} 50 puts "
Keyword" complain_mark Keyword "*" puts "" cw_text Keyword {} 50 puts "
Resource Type" #cw_popupE Record_Type $cdb(Resource_Type) #puts "
Media " set MediaList [linsert $cdb(Media) 0 "All Media"] set MediaList [lsort -dictionary $MediaList] cw_popup Media $MediaList {} {} {} "All Media" puts "
Grade Level" set GradeLevelList [linsert $cdb(Grade_Level) 0 "All Grades"] cw_popupE Grade_Level $GradeLevelList {} {} {} "All Grades" puts "
Subjects" set SubjectList [linsert $cdb(Subject) 0 "All Subjects"] cw_popup Subject $SubjectList {} {} {} "All Subjects" puts "
Only new titles? " #cw_checkboxes2 New {Yes No} 2 "" "" "" $sdb(New) cw_radios2 New {Yes No} 2 "" "" No $sdb(New) puts "
Special Features " cw_checkboxes2 Special_Features $cdb(Special_Features) 2 "" "" "" $sdb(Special_Features) {} h {} nowrap puts "
Limit search results to" cw_popupE searchLimit {20 50 100} {} {} {} {} 20 puts "items per page.

" basic_form_buttons Search {Clear Changes} {Redo Form} puts "

" include_file search_directions.txt cw source $cw_templatePath/templateBottom.tcl