# file: correlsearch.html # Copyright (c) 1996-2005 Cuesta Technologies, LLC # All rights reserved. # This document contains confidential and/or proprietary information # beloging to Cuesta Technologies, LLC. # Package: # # Purpose: given State/Grade/Subject and optionally, Keyowrd(Description), find # all items in correlation db. # # Requires: # cw source mysql.tcl set pageID PG_155 redo_form_values if {[info exists cw_args(item)] && [info exists cw_args(pid)]} { ### coming from product page set qryStr "+item@$cw_args(item)+pid@$cw_args(pid)" MysqlConnectCorrelDB if {![info exists cw_val]} { set sdb(config2std) $cw_args(item) set sdb(record2std) $cw_args(pid) #set query "SELECT StdID FROM $mysqlCXReftbl WHERE PLOID LIKE '%$cw_args(item)%';" set query "SELECT StdID FROM $mysqlCXRefPLOtbl WHERE PLOID='$cw_args(item)';" set stdList [sql $query] set stdList [lindex [lindex $stdList 0] 0] cw log 0 "Find StdID for $cw_args(item): number of result [llength $stdList]" set stateList [MysqlGetListValue_In_Field $mysqlCorreltbl $stdList StdID Corre_State {} 100 No] #cw log 0 "Find state List:$stateList" set stateList [unique $stateList] regsub {\{\}} $stateList "" stateList set stateList [lsort $stateList] set sdb(stateList) $stateList #cw log 2 "stateList=$stateList" set gradeList [MysqlGetListValue_In_Field $mysqlCorreltbl $stdList StdID Corre_Grade {} 100 No] cw log 0 "Find Grade List" set gradeList [unique $gradeList] set gradeList [ReorderList $gradeList $cdb(correlGrades)] set sdb(gradeList) $gradeList set subjectList [MysqlGetListValue_In_Field $mysqlCorreltbl $stdList StdID Corre_Subject {} 100 No] cw log 0 "Find Subject List" set subjectList [unique $subjectList] set temp {} foreach subject $subjectList { regsub -all {[\{\}]} $subject "" subject if {[string length $subject]} { lappend temp $subject } } set subjectList [lsort $temp] set sdb(subjectList) $subjectList } else { set stateList $sdb(stateList) set gradeList $sdb(gradeList) set subjectList $sdb(subjectList) } sql selectdb $mysqlproddb set query "select Title from $mysqlProductTable where Record_ID='$cw_args(pid)';" set title [sql $query] set title [lindex [lindex $title 0] 0] set query "select Configuration from $mysqlConfigTable where Item_Number='$cw_args(item)';" set config [sql $query] set config [lindex [lindex $config 0] 0] set fromProd "$title: $config" MysqlDisconnect } else { ### not coming from product page set qryStr "" set sdb(config2std) "" set sdb(stateList) {} set sdb(gradeList) {} set subjectList {} set stateList $cdb(Corre_State) #set gradeList $cdb(Corre_Grade) ### 10.21.05 mes: use fixed cdb value for grades set gradeList $cdb(correlGrades) set subjectList $cdb(Corre_Subject) } set fields {Corre_State Corre_Grade Corre_Subject Corre_Description} if {![llength $sdb(fullNameStateList)]} { MysqlConnectCorrelDB set fullNameStateList {} foreach state $stateList { set query "SELECT StateFullName FROM $mysqlStatetbl WHERE State='$state';" set fullname [sql $query] set fullname [lindex [lindex $fullname 0] 0] lappend fullNameStateList $fullname } MysqlDisconnect set sdb(fullNameStateList) $fullNameStateList } #cw log 2 "sdb(fullNameStateList)=$sdb(fullNameStateList)" ###### coming back from correllist to modify search if {([info exists cw_args(from)] && [string match correllist $cw_args(from)]) || ([info exists cw_args(frompage)] && [string match correllist $cw_args(frompage)])} { foreach field $fields { set cw_vals($field) $sdb($field) set cw_val($field) $sdb($field) } } if {[info exists cw_val]} { 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 some special search pass to MySQLRecordSearch proc. set appendQueryString "" set substitutepairs { {Description "Standard Keyword"} {Corre_State State} {Corre_Grade Grade} {Corre_Subject Subject} {Corre_Description Keyword} {Corre_Title Title} } set omitlist {} set sdb(correl_result_list) "" set sdb(correlcriteria) "" set input 0 marked_complain Corre_State isblank "Please enter state." marked_complain Corre_Grade isblank "Please enter grade." marked_complain Corre_Subject isblank "Please enter subject." marked_complain Corre_Description isonechar "Please enter more than one character for standard keyword search." marked_complain Corre_Title isonechar "Please enter more than one character for product title search." foreach f $fields { if {[info exists cw_val($f)] && [string length $cw_val($f)]} { incr input } } FormToSdb {} submit if { $input == 0 } { lappend complaints "Please enter some search criteria." } if {![llength $complaints]} { ## create product search list based on product_table.t and cw_vals. set searchableList [GetSearchableList $cw_correl_tablepath $omitlist $fields] cw log 2 "0. no complaints, doing search..." ### Generate search criteria set listcriteria [GetSearchCriteria $searchableList $substitutepairs] cw log 2 "listcriteria=$listcriteria" set myfullname [mapelement $cw_val(Corre_State) $stateList $sdb(fullNameStateList)] regsub $cw_val(Corre_State) $listcriteria $myfullname listcriteria MysqlCleancw_val MysqlConnectCorrelDB ## do MysqlRecordSearch set results [MysqlRecordSearch $mysqlCorreltbl $searchableList StdID Corre_Sequence] cw log 2 "sdb(MYSQLqueryString)=$sdb(MYSQLqueryString)" set results [unique $results] cw log 2 "1. get search result [llength $results]" if {[info exists cw_val(Corre_Title)] && [string length $cw_val(Corre_Title)]} { set query "SELECT StdID FROM $mysqlCXReftbl WHERE PLOID LIKE '%$cw_args(item)%';" set itemresult [sql $query] set itemresult [unique $itemresult] set results [intersect $results $itemresult] cw log 2 "1.1 filter the Corre_Title: [llength $results]" append listcriteria "
   Product Title: $cw_val(correl_Title)
" } if {[info exists cw_args(item)] && [info exists cw_args(pid)]} { cw log 2 "2. filter with $cw_args(item)...." set query "SELECT StdID FROM $mysqlCXReftbl WHERE PLOID LIKE '%$cw_args(item)%';" set itemresult [sql $query] set itemresult [unique $itemresult] set results [intersect $results $itemresult] append listcriteria "
   Product: $fromProd
" cw stats ITEMCORREL $cw_args(item) } if {[llength $results]} { ## filter out standard id without config records cw log 2 "3. save to configAry ..." set tmp {} foreach id $results { set query "SELECT PLOID FROM $mysqlCXReftbl WHERE StdID='$id';" set cxref [unique [lindex [lindex [sql $query] 0] 0]] if {[info exists cw_args(item)]} { set cxref [intersect $sdb(config2std) $cxref] } set cxlen [llength $cxref] #cw log 2 "id=$id; cxlen=$cxlen" ## Save number of config in array if {$cxlen} { lappend tmp $id set configAry($id) $cxlen ## Save config list to sdb set sdb($id) $cxref } } set results $tmp } if {[llength $results]} { ##find the ancestor standards of each standard in the results list. cw log 2 "4. get ancList...." set ancList [FindAncestor $results] set ancList [unique $ancList] set results [unique [concat $results $ancList]] #cw log 2 "resuslts([llength $results])=$results" cw log 2 "5. sort the list ..." sql selectdb $mysqlcorreldb set sortedList [MysqlSortListByColumn $mysqlCorreltbl StdID $results Corre_Sequence] set results $sortedList } MysqlDisconnect ##Added the HTML end tags to search criteria append listcriteria [GetSearchCriteriaEnd] set sdb(correl_result_list) $results set sdb(correlcriteria) $listcriteria DoStats $fields if {[llength $results] } { ## save pagelist based on configAry in sdb(corre_pagelist). cw log 2 "SetupCorreResultLimit" set sdb(corre_pagelist) [SetupCorreResultLimit $results configAry] if { ![info exists cw_args(from)] } { cw redirect correllist$webPgExtn$nocache+curList@0+frompage@correlsearch force } } else { set noresult 1 } } } cw source $new_cw_templatePath/templateSetup.tcl cw source $new_cw_templatePath/templateTop.tcl ######################## # Add your own code here ######################## puts "

" if {[info exists noresult] || ([info exists cw_args(frompage)] && [string match correllist $cw_args(frompage)])} { ### 5.18.05 mes: changed text: puts "

  • There are no correlation results. Please adjust your selections below.

  • " } ### 5.18.05 mes: moved name of item here: if {[info exists cw_args(item)] && [info exists cw_args(pid)]} { puts "Find correlations for $fromProd
    " } puts "
    " if {[info exists cdb(Corre_State)]} { puts "" } if {[info exists cdb(Corre_Grade)]} { puts "" } if {[info exists cdb(Corre_Subject)]} { puts "" } puts "
    State: " complain_mark Corre_State * puts "" #cw_popupE Corre_State $stateList Select... cw_popupE Corre_State $sdb(fullNameStateList) Select... {} {} {} {} {} $stateList puts "
    Grade: " complain_mark Corre_Grade * puts "" cw_popupE Corre_Grade $gradeList Select... puts "
    Subject: " complain_mark Corre_Subject * puts "" cw_popupE Corre_Subject $subjectList Select... puts "
    Standards Keyword:  " complain_mark Corre_Description * cw_text Corre_Description {} 30 puts "(optional)
    " basic_form_buttons Search {Clear Changes} puts "
    " cw source $new_cw_templatePath/templateBottom.tcl