Common DW window Query function
Create a User Object U_vst_search
Binary initialization Control UO_FIND.UF_INIT_DW (DW_1)
Three rewrite Ue_search () and Ue_all () events to handle filtering functions
such as rewrite Ue_search () filter by selected query criteria
Long Ll_count
Dw_1.setredraw (False)
Retrieve all data first
Ll_count = Dw_1.retrieve (' 1 ', "%")
Filter again by condition
Dw_1.setfilter (as_condition)
Dw_1.filter ()
Ll_count = Dw_1.rowcount ()
If Ll_count <= 0 Then
Event Zfc_new ()
End If
Dw_1.setredraw (True)
Forwardglobal type U_vst_search from Userobjectend typetype st_txt from Statictext within U_vst_searchend Typetype cb_1 fr Om CommandButton within U_vst_searchend Typetype Pb_find from CommandButton within U_vst_searchend Typetype sle_xx from Dr Opdownlistbox within U_vst_searchend Typetype ddlb_condition from Dropdownlistbox within U_vst_searchend Typetype ddlb_ Col from Dropdownlistbox within u_vst_searchend typeend forwardglobal type u_vst_search from userobjectinteger width = 231 8integer height = 112long BackColor = 67108864string Text = "None" long Tabtextcolor = 33554432long Picturemaskcolor = 5368 70912event ue_key pbm_keydownevent Ue_all () event Ue_search (string as_condition) st_txt st_txtcb_1 cb_1pb_find pb_finds Le_xx sle_xxddlb_condition ddlb_conditionddlb_col ddlb_colend typeglobal u_vst_search u_vst_searchtype Variablesdatawindow idw_1string is_currentcol = "Stringis_titlestringis_datalist[]stringis_conditionstringis_ Condtype= ' F '//Query method: F (DW filter); Q (SQL query) end VariablesforwardPrototypespublic function String uf_explain_condition (string as_condition) Public subroutine Uf_set_condition (string As_type) Public Function Long Uf_check () public function string Uf_check_datatype (integer ai_id) public subroutine Uf_init (DataWindow adw_1) End prototypesevent Ue_all ();//click on "All" operation, cancel the query condition//parent.event ue_all () End Eventevent Ue_search ( string as_condition);//Click the "Query" action, as_condtion for the query condition//parent.event ue_search (as_condition) End Eventpublic function String Uf_explain_condition (string as_condition); Stringls_retchoose case as_conditioncase ' equals ' ls_ret = ' = ' case ' is not equal to ' Ls_ret = ' <> ' case ' greater than ' Ls_ret = ' > ' case ' greater than or equal ' l S_ret = ' >= ' case ' is less than ' Ls_ret = ' < ' case ' less than equals ' Ls_ret = ' <= ' case ' similar to ' Ls_ret = ' like ' case ' contains ' Ls_ret = ' case ' not Contains ' Ls_ret = ' not a ' case ' in ... Between ' Ls_ret = ' between ' case ' and ' Ls_ret = ' and ' case ' or ' Ls_ret = ' or ' End choosereturn ls_retend Functionpublic subroutine uf_ Set_condition (String as_type);dd lb_condition. Reset () Choose case Lower (as_type) case ' num 'Digital type ddlb_condition. AddItem ("equals") ddlb_condition. AddItem ("Not Equal to") ddlb_condition. AddItem ("less than") ddlb_condition. AddItem ("Less than equals") ddlb_condition. AddItem ("Greater than") ddlb_condition. AddItem ("Greater than equals") case ' str '//character type ddlb_condition. AddItem ("equals") ddlb_condition. AddItem ("Not Equal to") ddlb_condition. AddItem ("less than") ddlb_condition. AddItem ("Less than equals") ddlb_condition. AddItem ("Greater than") ddlb_condition. AddItem ("Greater than equals") ddlb_condition. AddItem ("similar to") ddlb_condition. AddItem ("contains") ddlb_condition. AddItem ("not included") Case ' date '//date type ddlb_condition. AddItem ("equals") ddlb_condition. AddItem ("Not Equal to") ddlb_condition. AddItem ("less than") ddlb_condition. AddItem ("Less than equals") ddlb_condition. AddItem ("Greater than") ddlb_condition. AddItem ("Greater than equals") End chooseend subroutinepublic function Long Uf_check (); intli_id, Li_pos,li_posleft,li_ posrightstringls_datali_id = Integer (Mid (Ddlb_col.text, 2, Pos (Ddlb_col). Text, '] ', 1)-2)) If li_id = 0 Thenreturn 1end ifls_data = sle_xx.textchoose case Uf_check_datatype (li_id) case ' num ' Li_po Sleft = pos (Ls_data, "[") Li_posright = pos (Ls_data, "]") if Li_posright > LI_posleft and li_posleft > 0 thenls_data = Mid (ls_data, Li_posleft + 1, li_posright-li_posleft-1) end IfIf Not IsNum ber (ls_data) thengoto errend ifcase ' Date ' IF uf_explain_condition (ddlb_condition. Text) = ' between ' Thenli_pos = pos (Ls_data, ', ', 1) if Li_pos = 0 thenmessagebox ("Query", "Please enter the correct data format, such as: 2001-01-01,2003-01-0 1 ") Goto errend IfIf Not IsDate (left (Ls_data, li_pos-1)) Or not IsDate (right (Ls_data, Len (ls_data)-Li_pos)) Thengoto E Rrend Ifelseif not IsDate (ls_data) thengoto errend IfEnd IfEnd Choosereturn 1err:messagebox ("Query", "type of data mismatch!") Return-1end functionpublic function String uf_check_datatype (integer ai_id); String Ls_datatype, Ls_retls_datatype = Idw_1.describe ("#" + String (ai_id) + ". Coltype ") If Pos (Ls_datatype, ' (', 1) > 0 thenls_datatype = Mid (Ls_datatype, 1, POS (Ls_datatype," (", 1)-1) End Ifchoos E case ls_datatypecase ' numeric ', ' decimal ', ' int. ', ' money ', ' real ', ' smallint ', ' smallmoney ', ' tinyint ', ' long ' Ls_ret = ' n Um ' case ' datetime ', ' timestamp ' Ls_ret = ' Date ' case elsels_ret = ' str ' End choosereturn ls_retend Functionpublic subroutine Uf_init (DataWindow adw_1); idw_1 = Adw_ 1stringls_colname, Ls_col, Ls_itemname, Ls_tag, Ls_parentintli_colcount, I, M, Li_visibleddlb_col.reset ()//Reset column name Li_ ColCount = Integer (Idw_1.describe ("DataWindow.Column.Count")) for i = 1 to Li_colcountls_col = Idw_1.describe ("#" + String (i) + ". Name ") Ls_colname = Idw_1.describe (Ls_col +" _t.text ") li_visible = Integer (Idw_1.describe (Ls_col +). Visible ")) Ls_tag = Idw_1.describe (" # "+ String (i) +"). Tag ") If li_visible = 0 Then Continueif ls_colname = '! ' Then Continueif ls_tag = ' Nosearch ' then Continueif Pos (Ls_col, "Item_no") > 0 Then ls_itemname = Ls_colnamem = Iend I Fddlb_col. AddItem ("[" + String (i) + "]:" + ls_colname) NextIf m > 0 thenddlb_col. Text = "[" + String (m) + "]:" + ls_itemnameddlb_col. Triggerevent ("selectionchanged") ElseIf i > 0 thenddlb_col. SelectItem (1) ddlb_col. Event selectionchanged (1) End ifend if//window add fuzzy query ls_parent = lower (ClassName (this.getParent ())) If ls_parent = ' Thenddlb_col. InsertItem ("[0]: Fuzzy query", 1) ddlb_col. SelectItem (1) ddlb_col. Event selectionchanged (1) End IfEnd Subroutineon u_vst_search.createthis.st_txt=create st_txtthis.cb_1=create cb_ 1this.pb_find=create pb_findthis.sle_xx=create sle_xxthis.ddlb_condition=create ddlb_conditionthis.ddlb_col= Create Ddlb_colthis. control[]={this.st_txt,&this.cb_1,&this.pb_find,&this.sle_xx,&this.ddlb_condition,& This.ddlb_col}end onon U_vst_search.destroydestroy (This.st_txt) Destroy (This.cb_1) Destroy (This.pb_find) Destroy ( THIS.SLE_XX) Destroy (This.ddlb_condition) destroy (This.ddlb_col) end Ontype st_txt from Statictext within U_vst_ Searchinteger x = 14integer y = 32integer width = 379integer height = 52integer textsize = -10integer weight = 400fontchar Set Fontcharset = Gb2312charset!fontpitch Fontpitch = variable!string facename = "Arial" long TextColor = 134217751long Backco Lor = 67108864string Text = "Exact query:" Alignment alignment = Right!boolean Focusrectangle = FalseEndTypetype cb_1 from CommandButton within u_vst_searchstring tag = "Retrieve all data" integer x = 2075integer y = 4integer width = 219 Integer height = 96string Text = "All" End typeevent clicked;call super::clicked;is_condition = "" Event Ue_all () End Eventtyp E Pb_find from CommandButton within u_vst_searchstring tag = "Query" integer x = 1902integer y = 4integer width = 165integer h eight = 96string Text = "Query" End typeevent Clicked;call super::clicked; String Ls_colname,ls_coldbname,ls_data,ls_datatype,ls_condition,ls_where Integer Li_pos,li_posleft,li_ Posrightintli_id,li_retif not IsValid (idw_1) thenreturnend ifif Ddlb_col. Text = ' None ' then Return end IfIf Uf_check () =-1 then returnend ifli_id = Integer (Mid (Ddlb_col. Text, 2, POS (Ddlb_col.text, '] ', 1)-2)) If Is_condtype = ' F ' then//generates a filter condition by the column name of the DW Ls_colname = Idw_1.describe ("#" + String ( li_id) + ". Name ") else//the SQL query condition generated by the field name of the table Ls_colname = Idw_1.describe (" # "+ String (li_id) +". DbName ") End ifls_datatype = Uf_check_ DataType (li_id) ls_condition = Uf_explaIn_condition (ddlb_condition. Text) Ls_data = Trim (sle_xx. Text) Li_posleft = pos (Ls_data, "[") Li_posright = pos (Ls_data, "]") If li_posleft > 0 and li_posright > 0 and Li_posle FT < Li_posright thenls_data = Mid (ls_data, Li_posleft + 1, li_posright-li_posleft-1) End ifif IsNull (ls_data) or L S_data = ' Thenmessagebox ("Prompt", "Please enter query criteria!") Returnend ifchoose case ls_datatype case ' num ' ls_where = "(" + ls_colname + "+ ls_condition +" "+ Ls_data +") "Case" str ' If ls_condition = ' like ' Or ls_condition = ' Don't like ' thenif Pos (Ddlb_condition.text, "contains") > 0 thenls_where = "(" + ls_colname + "+ ls_condition +" '% "+ ls_data +"% ') "Elsels_where =" ("+ ls_colname +" + ls_condition + "'" + L S_data + "% ')" End ifelseif ls_condition = ' between ' Thenli_pos = pos (Ls_data, ",", 1) ls_where = "(" + ls_colname + "+ L S_condition + "'" + &trim (left (Ls_data, li_pos-1) + "' and '" + &trim (right (Ls_data, Len (ls_data)-Li_pos)) + "')" Elsels_where = "(" + ls_colname + "+ Ls_Condition + "'" + Ls_data + "')" End ifend ifcase ' Date ' If ls_condition = ' = ' Thenls_data = string (date (Ls_data), "yyyy-mm -dd ") Ls_where =" ("+ Ls_colname +" >= (' "+ Ls_data +" ') and "+ls_colname +" <= (' "+ Ls_data +" 23:59:59.9 "))" El Sels_where = "(" + ls_colname + "+ ls_condition +" ' "+ Ls_data +" ') "End ifend Chooseis_condition = ls_whereevent Ue_ Search (ls_where) end EventType sle_xx from Dropdownlistbox within u_vst_searchevent key Pbm_keydowninteger x = 1339integer y = 8integer width = 553integer height = 544integer TabOrder = 10integer Textsize = -9integer weight = 400fontcharset fon Tcharset = Ansi!fontpitch Fontpitch = variable!fontfamily fontfamily = swiss!string facename = "Arial" Long TextColor = 335 54432boolean AllowEdit = Trueboolean Autohscroll = Trueboolean sorted = Falseboolean HScrollBar = Trueboolean VScrollBar = Trueborderstyle BorderStyle = stylelowered!end typeevent key;if key = keyenter! ThenIf Len (This.text) > 0 thenpb_find. Triggerevent (Clicked! ) This.selecttext (1, Len (this.text)) END IfEnd IfEnd eventevent modified;stringls_datainteger li_ils_data =this.text// Processing drop-down blur query if Sle_xx.totalitems () > 0 thenif sle_xx.finditem (ls_data,0) < 0 thenfor li_i = 1 to Upperbound (is_datalist ) if POS (is_datalist[li_i],ls_data) > 0 thensle_xx.selectitem (li_i) exitend ifnextend ifend IfIf keydown (keyenter!) th En IF Len (this.text) > 0 thenpb_find. Triggerevent (clicked!) This.selecttext (1, Len (this.text)) END IfEnd IfEnd EventType ddlb_condition from Dropdownlistbox within U_vst_searchi Nteger x = 942integer y = 8integer width = 393integer height = 512integer textsize = -9integer weight = 400fontcharset fon Tcharset = Ansi!fontpitch Fontpitch = variable!fontfamily fontfamily = swiss!string facename = "Arial" Long TextColor = 335 54432string Text = "None" boolean sorted = Falseboolean VScrollBar = trueborderstyle BorderStyle = Stylelowered!end Typetyp e Ddlb_col from Dropdownlistbox within u_vst_searchinteger x = 398integer y = 8integer width = 539integer height = 676integer textsize = -9integer Weight = 400fontcharset Fontcharset = Ansi!fontpitch Fontpitch = variable!fontfamily FontFamily = swiss!string facename = "Arial" Long textcolor = 33554432string Text = "None" Boolean sorted = Falseboolean VScrollBar = trueborderstyle BorderStyle = Stylelowered!end typeevent selectionchanged; Stringls_datatype, Ls_colnameintegerli_id,li_istringls_empty[]for li_i = Sle_xx.totalitems () to 1 step-1sle_ Xx.deleteitem (li_i) nextis_datalist = ls_emptyli_id = Integer (Mid (This.text, 2, Pos (This.text, '] ', 1)-2)) If li_id = 0 Thenddlb_condition. Reset () ddlb_condition. AddItem ("contains") ddlb_condition. SelectItem ("contains", 1) return End ifls_colname = Idw_1.describe ("#" + String (li_id) + ". Name ") Is_currentcol = Ls_colnamels_datatype = Uf_check_datatype (li_id) Choose case ls_datatypecase ' num ' If not isnumber ( Sle_xx.text) Thensle_xx.text = ' End Ifcase ' date ' If not IsDate (sle_xx.text) Thensle_xx.text = ' End IfEnd Chooseparent.uf _set_conditioN (ls_datatype) Ddlb_condition.text = ' equals ' Stringls_displaycol, Ls_datacolstringls_values, Ls_work, Ls_pData, Ls_dData, Ls_datalongll_pid, Ll_did, Row, n//handling of columns with DDDW datawindowchildldwc_1if idw_1.getchild (ls_colname, Ldwc_1) <>-1 ThenIf Ldwc_1.rowcount () > 0 thendatastorelds_1lds_1 = Create datastorelds_1.dataobject = idw_1.describe (Ls_ColName + ". Dddw. Name ") ldwc_1.rowscopy (1, Ldwc_1.rowcount (), primary!, Lds_1, 1, primary!) Ls_displaycol = Trim (Idw_1.describe (Ls_colname +). Dddw. DisplayColumn ")) Ls_datacol = Trim (Idw_1.describe (Ls_colname +). Dddw. DataColumn ")) Ll_pid = Long (ldwc_1.describe (Ls_displaycol +"). Id ")) Ll_did = Long (ldwc_1.describe (Ls_datacol +"). Id ")) for Row = 1 to Ldwc_1.rowcount () Ls_pdata = string (Lds_1.object.data.primary.current[row, Ll_pid]) Ls_ddata= string ( Lds_1.object.data.primary.current[row, Ll_did]) If ls_pdata <> "New ..." Thenchoose case ls_datatype case ' num ' ls_data = "[" + String (ls_ddata) + "]" + ls_pdatacase elsels_data = "[" + Ls_ddata + "]" + LS_pdataend choosesle_xx. AddItem (Ls_data) is_datalist[upperbound (is_datalist) + 1] = ls_dataend ifnextend ifend if//handling of columns with ddlb n = 1ls_values = Tri M (Idw_1.describe (Ls_colname + ". Values")) If ls_values = "?" Then returnls_work = Gf_string_xsub (Ls_values, "/", 1) does while Len (ls_work) > 0If Pos (ls_work, "~t", 1) > 0 thenls_p Data = Gf_string_xsub (ls_work, "~t", 1) ls_ddata = Gf_string_xsub (ls_work, "~t", 2) If ls_pdata <> "New ..." Thenchoose case ls_datatype case ' num ' ls_data = "[" + String (ls_ddata) + "]" + ls_pdatacase elsels_data = "[" + Ls_ddata + "]" + ls_pdataend choosesle_xx. AddItem (Ls_data) is_datalist[upperbound (is_datalist) + 1] = ls_dataend IfEnd Ifn++ls_work = gf_string_xsub (Ls_values, "/ ", N) loopend event
Common DW window Query function