局域网图书资料查询系统
§5.3 查询模块的实现
图5-3 查询窗口
该窗口(w_main)控件列表如下:
风 格 控件名 说 明
显示项 Group Box gb_1 ------
资料名称 Check Box cbx_1 Checked:true
作者 Check Box cbx_2 Checked:true
出版社 Check Box cbx_3 Checked:true
出版日期 Check Box cbx_4 Checked:false
类别 Check Box cbx_5 Checked:true
购买日期 Check Box cbx_6 Checked:false
ISBN Check Box cbx_7 Checked:false
保管人 Check Box cbx_8 Checked:true
备注 Check Box cbx_9 Checked:false
全选 Check Box cbx_10 选择全部显示项
检索 Picture Box pb_1 模糊查询检索
插入 Picture Box pb_2 插入一条记录
删除 Picture Box pb_3 删除一条记录
检索 Picture Box pb_4 组合查询检索
全部记录 Picture Box pb_5 检索全部记录
退出 Picture Box pb_6 退出查询界面
修改 Picture Box pb_7 进入编辑模式
插入 Picture Box pb_8 插入一条查询条件
删除 Picture Box pb_9 删除一条查询条件
存盘 Picture Box pb_10 修改后的数据存盘
打印 Picture Box pb_11 打印查询结果报表
存为默认显示项 Picture Box pb_12 将显示项存为当前用户默认
------ Single line edit sle_1 查询内容输入(字符型)
------ Edit mask em_1 查询内容输入(日期型)
------ Static text st_1 显示当前用户名
------ Picture P_1 修改按钮装饰
------ Picture P_2 Gb_1显示装饰
------ Picture P_3 显示当前用户头像
------ Drop down list box ddlb_1 查询项选择
------ Drop down list box ddlb_2 查询操作符选择
------ Tab control tab_1 共有三个tabpage頁tabpage1:模糊查询tabpage2:组合查询tabpage3:
更新打印
------ Datawindows control dw_1 对应数据窗口d_information图书信息检索
------ Datawindows control dw_2 对应数据窗口d_query_condition查询条件检索
Command button Cb_2 模糊查询操作符英-中转换
表5-2 查询模块控件信息表
控件:w_main 事件(event):open Script of open
string mode,p,xs,headboolean xs_item[10]integer idw_1.settransobject(sqlca)dw_1.
Retrieve()//打开窗口显示全部记录sj_check=0//为检索窗口排序变量赋初值st_1.text=user//
在查询窗口右上角显示当前用户名SELECT "keeper"."display_item", "keeper"."
head_picture" INTO :xs, :head FROM "keeper" WHERE "keeper"."name" = :user;
//从用户表中读出当前用户的头像值及显示像值并分别赋值给变量 file://有头像的用户则显示
在查询窗口右上角if isnull(head) thenp_3.visible=false else p_3.picturename=
headend if ////将当前用户查询显示项变量读出并赋值给每个查询显示控件for i=1 to 8 if
Mid (xs, i, 1)='0' then xs_item[i]=false else xs_item[i]=true end ifend for////给每
个对应的CHECKBOX赋值,确实是勾还是叉cbx_2.checked = xs_item[1]cbx_3.checked =
xs_item[2]cbx_4.checked = xs_item[3]cbx_5.checked = xs_item[4]cbx_6.checked =
xs_item[5] cbx_7.checked = xs_item[6] cbx_8.checked = xs_item[7]cbx_9.checked =
xs_item[8] ////对应的数据窗口显示情况dw_1.object.id.visible = falsedw_1.object.name
.visible = truedw_1.object.author.visible = cbx_2.checkeddw_1.object.
publishing_house.visible = cbx_3.checkeddw_1.object.publishing_date.visible = cbx_4.
checkeddw_1.object.sort.visible = cbx_5.checkeddw_1.object.buy_date.visible = cbx_6.
checkeddw_1.object.isbn.visible = cbx_7.checkeddw_1.object.keeper.visible = cbx_8.
checkeddw_1.object.memo.visible = cbx_9.checked////将查询数据窗口设为只读dw_1.Object
.DataWindow.ReadOnly="Yes"
控件:pb_12 “存为默认显示选项” 事件(event):clicked Script of clicked
boolean check[10]integer i,answerstring xs_item[10]string
xs_content,question_text,tempquestion_text="将当前显示项存为"+user+"用户的默认显示项
?"answer=messagebox('提示!',question_text,question!,YesNoCancel!)choose case answer
case 1 file://将当前对应的CHECEKBOX选项记录下来check[1]=cbx_2.checked check[2]
=cbx_3.checked check[3]=cbx_4.checked check[4]=cbx_5.checked check[5]
=cbx_6.checked check[6]=cbx_7.checked check[7]=cbx_8.checked check[8]
=cbx_9.checked// file://将显示项转化为01的显示,存入数组变量 xs_content=''
for i = 1 to 8 if check[i]=true then xs_item[i]='1' else xs_item[i]='0'
end if xs_content=xs_content+xs_item[i] end for// file://修改用户表中显示项
的值 UPDATE "keeper" SET "display_item" = :xs_content WHERE "
keeper"."name" = :user; choose case SQLCA.sqlcode case -1 messagebox("
错误!","error:"+& string(sqlca.sqldbcode)+& "information:"+sqlca.sqlerrtext)
messagebox('提示','修改成功!') commit; case 100
messagebox('!','修改失败!') end choose case 2 returncase 3 returnend choose
5.3.1 模糊(分类)查询的实现
图5-4 模糊查询窗口
控件:ddlb_1 事件(selectionchanged): Script of Selectionchanged
file://清空历史查询内容em_1.selecttext(1,len(em_1.text))em_1.clear()sle_1.selecttext
(1,len(sle_1.text))sle_1.clear()//运算符随查询内容的改变而改变if ddlb_1.text= '出版日
期' or ddlb_1.text='购买日期' then ddlb_2.deleteitem(ddlb_2.FindItem("包含", 2))
ddlb_2.deleteitem(ddlb_2.FindItem("不包含", 2)) ddlb_2.deleteitem(ddlb_2.FindItem("小
于等于", 2)) ddlb_2.deleteitem(ddlb_2.FindItem("大于等于", 2))ddlb_2.insertitem('小于
',3)ddlb_2.insertitem('大于',4)ddlb_2.insertitem('小于等于',5) ddlb_2.insertitem('大
于等于',6) em_1.TextColor = RGB(255,0,0) sle_1.visible = False em_1.SetMask(DateMask
!, 'mm/dd/yyyy')else ddlb_2.deleteitem(ddlb_2.FindItem("小于", 2)) ddlb_2.deleteitem
(ddlb_2.FindItem("大于", 2)) ddlb_2.deleteitem(ddlb_2.FindItem("小于等于", 2)) ddlb_
2.deleteitem(ddlb_2.FindItem("大于等于", 2)) ddlb_2.deleteitem(ddlb_2.FindItem("包含
", 2)) ddlb_2.deleteitem(ddlb_2.FindItem("不包含", 2)) ddlb_2.insertitem('包含',3)
ddlb_2.insertitem('不包含',4)sle_1.visible= Truesle_1.TextColor = RGB(255,0,0)end if
不可见控件:cb_2 “转换” 事件(event):Clicked Script of Clicked
file://将查询项转换为表info中字段名choose case ddlb_1.text case '资料名称'
query_item='name' case '作者' query_item='author' case '出版社' query_item=
'publishing_house' case '出版日期' query_item='publishing_date' case 'ISBN'
query_item='ISBN' case '资料类别' query_item='sort' case '购买日期' query_item=
'buy_date' case '保管人' query_item='keeper' case '备注' query_item='memo'end
choose//将操作符转换为中文显示choose case ddlb_2.text case '等于' query_operator='='
case '包含' query_operator='like' case '不等于' query_operator='<>' case '不包含'
query_operator='not like' case '小于等于' query_operator='<=' case '大于等于'
query_operator='>=' case '小于' query_operator='<' case '大于' query_operator=
'>'end choose
控件:pb_1 “检索” 事件(event):Clicked Script of Clicked
string rqdate dcb_2.TriggerEvent(clicked!)//将英文操作符转换为中文显示dw_1.setredraw
(true)old_sql = dw_1.getsqlselect()//得到原有数据窗口的SQL语句if ddlb_1.text='出版日
期' or ddlb_1.text='购买日期' then//查询项为日期类型 em_1.GetData(d)//取出输入的日期
数据 rq = String(d, "yyyy/mm/dd")//将输入的日期数据转换为字符型 choose case
query_operator//根据操作符的不同将新产生的WHERE条件与老的SQL语句拼接成新的SQL语句
case '=' new_sql = old_sql + " where " + query_item & + " "+query_operator
+ 'date' +'(' + "'" + rq + "'" +')' case '<>' new_sql = old_sql + " where " +
query_item & + " "+query_operator + 'date' +'(' + "'" + rq + "'" +')' &
+' or '+query_item+' is null ' case '<=' new_sql = old_sql + " where " +
query_item & + " "+query_operator + 'date' +'(' + "'" + rq + "'" +')' case
'>=' new_sql = old_sql + " where " + query_item & + " "+query_operator +
'date' +'(' + "'" + rq + "'" +')' end choose////查询项为字符型else sle_1.text=
Righttrim(sle_1.text)//将当前输入内容右面的空格去掉 choose case query_operator//根
据操作符的不同将新产生的WHERE条件与老的SQL语句拼接成新的SQL语句 case '='
new_sql = old_sql + " where " + query_item & + " "+query_operator + "'" +
parent.sle_1.text + "'" case '<>' new_sql = old_sql + " where " + query_item
& + " "+query_operator + "'" + parent.sle_1.text + "'"& +' or '+query_item+'
is null ' case 'like' new_sql = old_sql + " where " + query_item & + " "+
query_operator + "'" +'%'+ parent.sle_1.text +'%'+ "'" case 'not like' new_sql =
old_sql + " where " + query_item & + " "+query_operator + "'" +'%'+ parent.
sle_1.text +'%'+ "'"& + ' or '+query_item+' is null 'end choose//end if//对原有数据
窗口进行新的查询 if dw_1.setsqlselect(new_sql) = -1 then beep(3) messagebox("
警告", "检索失败",stopsign!) elsedw_1.settransobject(sqlca) dw_1.retrieve()
dw_1.setsqlselect(old_sql) end if
5.3.2 组合查询(条件查询)模块的实现: