通用DW窗口查询功能

通用DW窗口查询功能

一 创建用户对象u_vst_search

二 初始化控件 uo_find.uf_init_dw( dw_1 )
三 重写ue_search() 和ue_all()事件用来处理过滤功能


如重写ue_search()按选择的查询条件进行过滤
long ll_count


dw_1.setredraw( false )


//先检索所有数据
ll_count = dw_1.retrieve( '1',"%" )
//再按条件过滤
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 )

forward
global type u_vst_search from userobject
end type
type st_txt from statictext within u_vst_search
end type
type cb_1 from commandbutton within u_vst_search
end type
type pb_find from commandbutton within u_vst_search
end type
type sle_xx from dropdownlistbox within u_vst_search
end type
type ddlb_condition from dropdownlistbox within u_vst_search
end type
type ddlb_col from dropdownlistbox within u_vst_search
end type
end forward

global type u_vst_search from userobject
integer width = 2318
integer height = 112
long backcolor = 67108864
string text = "none"
long tabtextcolor = 33554432
long picturemaskcolor = 536870912
event ue_key pbm_keydown
event ue_all ( )
event ue_search ( string as_condition )
st_txt st_txt
cb_1 cb_1
pb_find pb_find
sle_xx sle_xx
ddlb_condition ddlb_condition
ddlb_col ddlb_col
end type
global u_vst_search u_vst_search

type variables
DataWindow 		idw_1
String	  		is_CurrentCol = ''
string			is_title
string			is_datalist[]
string			is_condition
string			is_condtype	= 'F'		//查询方式:F(DW过滤);Q(SQL查询)
end variables

forward prototypes
public 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 prototypes

event ue_all();//点击“所有”操作，取消查询条件
//parent.event ue_all()
end event

event ue_search(string as_condition);//点击“查询”操作，as_condtion为查询条件
//parent.event ue_search(as_condition)
end event

public function string uf_explain_condition (string as_condition);String	ls_ret
Choose Case as_condition
	Case '等于'
		ls_ret = '='
	Case '不等于'
		ls_ret = '<>'
	Case '大于'
		ls_ret = '>'
	Case '大于等于'
		ls_ret = '>='
	Case '小于'
		ls_ret = '<'
	Case '小于等于'
		ls_ret = '<='
	Case '类似于'
		ls_ret = 'like'
	Case '包含'
		ls_ret = 'like'
	Case '不包含'
		ls_ret = 'not like'
	Case '在...之间'
		ls_ret = 'between'
	Case '并且'
		ls_ret = 'And'
	Case '或者'
		ls_ret = 'Or'
End Choose

return ls_ret
end function

public subroutine uf_set_condition (string as_type);ddlb_condition.Reset()
Choose Case lower(as_type)
	Case 'num'//数字型
		ddlb_condition.AddItem("等于")
		ddlb_condition.AddItem("不等于")
		ddlb_condition.AddItem("小于")
		ddlb_condition.AddItem("小于等于")
		ddlb_condition.AddItem("大于")
		ddlb_condition.AddItem("大于等于")
	Case 'str'//字符型
		ddlb_condition.AddItem("等于")
		ddlb_condition.AddItem("不等于")
		ddlb_condition.AddItem("小于")
		ddlb_condition.AddItem("小于等于")
		ddlb_condition.AddItem("大于")
		ddlb_condition.AddItem("大于等于")
		ddlb_condition.AddItem("类似于")
		ddlb_condition.AddItem("包含")
		ddlb_condition.AddItem("不包含")
	Case 'date'//日期型
		ddlb_condition.AddItem("等于")
		ddlb_condition.AddItem("不等于")
		ddlb_condition.AddItem("小于")
		ddlb_condition.AddItem("小于等于")
		ddlb_condition.AddItem("大于")
		ddlb_condition.AddItem("大于等于")
End Choose

end subroutine

public function long uf_check ();Int		li_id, li_pos,li_posleft,li_posright
string	ls_data

li_id = Integer(Mid(ddlb_col.text, 2, Pos(ddlb_col.Text, ']', 1) - 2))
if li_id = 0 then
	return 1
end if
ls_data = sle_xx.text

Choose Case uf_check_datatype(li_id)
		Case 'num'
			li_PosLeft = Pos(ls_data, "[")
			li_PosRight = Pos(ls_data, "]")
			if li_posright > li_posleft and li_posleft > 0 then
				ls_data = Mid(ls_data, li_PosLeft + 1, li_PosRight - li_PosLeft - 1)
			end if
			If Not IsNumber(ls_data) Then
				Goto Err
			End If
		Case 'date'
			IF uf_explain_condition(ddlb_condition.Text) = 'between' Then
				li_pos = Pos(ls_data, ',', 1) 
				If li_pos = 0 Then
					messagebox("查询", "请输入正确的数据格式，如: 2001-01-01,2003-01-01")
					Goto Err
				End If
				If Not IsDate(Left(ls_data, li_pos - 1))  Or Not IsDate(Right(ls_data, len(ls_data) - li_pos)) Then
					Goto Err
				End If
			Else
				If Not IsDate(ls_data) Then
					Goto Err
				End If
			End If
	End Choose
Return 1
Err:
   messagebox("查询", "数据的类型不匹配!")
	Return -1
	
end function

public function string uf_check_datatype (integer ai_id);String	 ls_datatype, ls_ret

ls_datatype = idw_1.Describe("#" + String(ai_id) + ".ColType")
If Pos(ls_datatype, '(', 1) > 0 Then
	ls_datatype = Mid(ls_datatype, 1, Pos(ls_datatype, "(", 1) - 1)
End If

Choose Case ls_datatype
	Case 'numeric', 'decimal', 'int', 'money', 'real', 'smallint', 'smallmoney', 'tinyint','long'
		ls_ret = 'num'
	Case 'datetime', 'timestamp'
		ls_ret = 'date'
	Case Else
		ls_ret = 'str'
End Choose

Return ls_ret
end function

public subroutine uf_init (datawindow adw_1);idw_1 = adw_1

String	ls_ColName, ls_Col, ls_itemName, ls_tag, ls_parent
Int		li_ColCount, i, m, li_visible

ddlb_col.reset( ) //重置列名

li_ColCount = Integer(idw_1.Describe("DataWindow.Column.Count"))

For i = 1 To li_ColCount
	ls_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 Continue
	If ls_ColName = '!' Then Continue
	If ls_tag = 'NOSEARCH' Then Continue
	If Pos(ls_Col, "item_no") > 0  Then 
		ls_itemName = ls_ColName
		m = i
	End If
	ddlb_col.AddItem("[" + String(i) + "]:" + ls_ColName)
Next
If m > 0 Then
	ddlb_col.Text = "["+ String(m) + "]:" + ls_itemName
	ddlb_col.TriggerEvent("SelectionChanged")
Else
	If i > 0 Then
		ddlb_col.SelectItem(1)
		ddlb_col.Event SelectionChanged(1)
	End If
End If

//接窗口增加模糊查询
ls_parent = lower(ClassName(This.GetParent()))
if ls_parent = '' then
	ddlb_col.InsertItem("[0]:模糊查询", 1)
	ddlb_col.SelectItem(1)
	ddlb_col.Event SelectionChanged(1)
end if
end subroutine

on u_vst_search.create
this.st_txt=create st_txt
this.cb_1=create cb_1
this.pb_find=create pb_find
this.sle_xx=create sle_xx
this.ddlb_condition=create ddlb_condition
this.ddlb_col=create ddlb_col
this.Control[]={this.st_txt,&
this.cb_1,&
this.pb_find,&
this.sle_xx,&
this.ddlb_condition,&
this.ddlb_col}
end on

on u_vst_search.destroy
destroy(this.st_txt)
destroy(this.cb_1)
destroy(this.pb_find)
destroy(this.sle_xx)
destroy(this.ddlb_condition)
destroy(this.ddlb_col)
end on

type st_txt from statictext within u_vst_search
integer x = 14
integer y = 32
integer width = 379
integer height = 52
integer textsize = -10
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
long textcolor = 134217751
long backcolor = 67108864
string text = "精确查询："
alignment alignment = right!
boolean focusrectangle = false
end type

type cb_1 from commandbutton within u_vst_search
string tag = "检索出所有数据"
integer x = 2075
integer y = 4
integer width = 219
integer height = 96
string text = "所有"
end type

event clicked;call super::clicked;is_condition = ""
event ue_all()
end event

type pb_find from commandbutton within u_vst_search
string tag = "查询"
integer x = 1902
integer y = 4
integer width = 165
integer height = 96
string text = "查询"
end type

event clicked;call super::clicked;
String  ls_colname,ls_coldbname,ls_data,ls_datatype,ls_condition,ls_where    
Integer li_pos,li_posleft,li_posright
Int		li_id,li_ret

If Not IsValid(idw_1) Then
	Return
end if
If ddlb_col.Text = 'none' Then 
	Return 
end if
If uf_check() = -1 Then 
	Return
end if

li_id = Integer(Mid(ddlb_col.Text, 2, pos(ddlb_col.text, ']', 1) - 2))
if is_condtype = 'F' then
	//按DW的列名生成过滤条件 
	ls_ColName = idw_1.Describe("#" + String(li_id) + ".Name")		
else
	//按表的字段名生成SQL查询条件
	ls_ColName = idw_1.Describe("#" + String(li_id) + ".dbName")
end if
ls_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_PosLeft < li_PosRight Then
	ls_data = Mid(ls_data, li_PosLeft + 1, li_PosRight - li_PosLeft - 1)
End If
If IsNull(ls_data)  or ls_data = '' then
	Messagebox("提示","请输入查询条件!")
	return
end if

Choose Case ls_datatype 
	Case 'num'
		ls_where = "(" + ls_ColName + ' ' + ls_condition + " " + ls_data + ")"
	
	Case 'str'
		If ls_condition = 'like' Or ls_condition = 'not like' Then
			If Pos(ddlb_condition.text, "包含") > 0 Then
				ls_where = "(" + ls_ColName + ' ' + ls_condition + " '%" + ls_data + "%')"
			Else
				ls_where = "(" + ls_ColName + ' ' + ls_condition + " '" + ls_data + "%')"
			End If
		Else
			If ls_condition = 'between' Then
				li_pos = Pos(ls_data, ",", 1)
				ls_where = "(" + ls_colName + ' ' + ls_condition + " '" + &
				Trim(Left(ls_data, li_pos - 1)) + "' and '" + &
				Trim(Right(ls_data, Len(ls_data) - li_pos)) + "')"
			Else
				ls_where = "(" + ls_ColName + ' ' + ls_condition + " '" + ls_data + "')"
			End If
		End If
					
	Case 'date'
		If ls_condition = '=' Then
			ls_data = string(date(ls_data), "yyyy-mm-dd")
			ls_where = "(" + ls_ColName + " >= ('" + ls_data +"') and "+ls_colname +" <= ('" + ls_data + " 23:59:59.9'))"
		Else
			ls_where = "(" + ls_ColName + ' ' + ls_condition + " '" + ls_data + "')"
		End If
End Choose

is_condition = ls_where
event ue_search(ls_where)



end event

type sle_xx from dropdownlistbox within u_vst_search
event key pbm_keydown
integer x = 1339
integer y = 8
integer width = 553
integer height = 544
integer taborder = 10
integer textsize = -9
integer weight = 400
fontcharset fontcharset = ansi!
fontpitch fontpitch = variable!
fontfamily fontfamily = swiss!
string facename = "Arial"
long textcolor = 33554432
boolean allowedit = true
boolean autohscroll = true
boolean sorted = false
boolean hscrollbar = true
boolean vscrollbar = true
borderstyle borderstyle = stylelowered!
end type

event key;if key = KeyEnter! then
	IF Len ( This.Text ) > 0 THEN
		pb_find.TriggerEvent ( Clicked! )
		This.SelectText ( 1, Len ( This.Text ) )
	END IF
end if

end event

event modified;string	ls_data
integer 	li_i


ls_data =this.text
//处理下拉模糊查询
if sle_xx.totalitems( ) > 0 then
	if sle_xx.finditem(ls_data,0) < 0 then
		for li_i = 1 to upperbound(is_datalist)
			if pos(is_datalist[li_i],ls_data) > 0 then
				sle_xx.selectitem( li_i )
				exit
			end if
		next
	end if
end if

if keydown(keyenter!) then 
	IF Len ( This.Text ) > 0 THEN
		pb_find.TriggerEvent ( Clicked! )
		This.SelectText ( 1, Len ( This.Text ) )
	END IF
end if


end event

type ddlb_condition from dropdownlistbox within u_vst_search
integer x = 942
integer y = 8
integer width = 393
integer height = 512
integer textsize = -9
integer weight = 400
fontcharset fontcharset = ansi!
fontpitch fontpitch = variable!
fontfamily fontfamily = swiss!
string facename = "Arial"
long textcolor = 33554432
string text = "none"
boolean sorted = false
boolean vscrollbar = true
borderstyle borderstyle = stylelowered!
end type

type ddlb_col from dropdownlistbox within u_vst_search
integer x = 398
integer y = 8
integer width = 539
integer height = 676
integer textsize = -9
integer weight = 400
fontcharset fontcharset = ansi!
fontpitch fontpitch = variable!
fontfamily fontfamily = swiss!
string facename = "Arial"
long textcolor = 33554432
string text = "none"
boolean sorted = false
boolean vscrollbar = true
borderstyle borderstyle = stylelowered!
end type

event selectionchanged;String	ls_datatype, ls_ColName
Integer	li_id,li_i
string	ls_empty[]

for li_i = sle_xx.totalitems( ) to 1 step -1
	sle_xx.deleteitem( li_i )
next
is_datalist = ls_empty

li_id = Integer(Mid(This.Text, 2, Pos(This.Text, ']', 1) - 2))
if li_id = 0 then
	ddlb_condition.Reset()
	ddlb_condition.AddItem("包含")
	ddlb_condition.SelectItem("包含", 1)
	return 
end if

ls_ColName = idw_1.Describe("#" + String(li_id) + ".Name")
is_CurrentCol = ls_ColName
ls_datatype = uf_check_datatype(li_id)
Choose Case ls_datatype
	Case 'num'
		If Not IsNumber(sle_xx.text) Then
			sle_xx.text = ''
		End If
	Case 'date'
		If Not IsDate(sle_xx.text) Then
			sle_xx.text = ''
		End If
End Choose
Parent.uf_set_condition(ls_datatype)
ddlb_condition.text = '等于'

String				ls_DisplayCol, ls_DataCol
String				ls_values, ls_work, ls_pData, ls_dData, ls_data
Long					ll_pId, ll_dId, Row, n

//具有DDDW的列的处理
DataWindowChild	ldwc_1

If idw_1.GetChild(ls_ColName, ldwc_1) <> -1 Then
	If ldwc_1.RowCount() > 0 Then
		DataStore			lds_1
		lds_1 = Create DataStore
		lds_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 <> "新增..." Then
				choose case ls_datatype 
					case 'num'
						ls_data = "[" + string(ls_dData) + "]" + ls_pData
					case else
						ls_data = "[" + ls_dData + "]" + ls_pData
				end choose
				sle_xx.AddItem(ls_data)
				is_datalist[upperbound(is_datalist) + 1] = ls_data
			End If
		Next
	End If
End If

//具有ddlb的列的处理
n = 1
ls_values = Trim(idw_1.Describe(ls_ColName + ".values"))
If ls_values =  "?" Then Return
ls_work = gf_string_xsub(ls_values, "/", 1)
Do While Len(ls_work) > 0
	If Pos(ls_work, "~t", 1) > 0 Then
		ls_PData = gf_string_xsub(ls_work, "~t", 1)
		ls_dData = gf_string_xsub(ls_work, "~t", 2)
		If ls_PData <> "新增..."  Then
			choose case ls_datatype 
				case 'num'
					ls_data = "[" + string(ls_dData) + "]" + ls_pData
				case else
					ls_data = "[" + ls_dData + "]" + ls_pData
			end choose
			sle_xx.AddItem(ls_data)
			is_datalist[upperbound(is_datalist) + 1] = ls_data		
	
		End If
	End If
	n++
	ls_work = gf_string_xsub(ls_values, "/", n)
Loop

	


end event