Common DW window Query function

Source: Internet
Author: User
Tags rowcount

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.