客房管理系统VB+SQLWord文件下载.docx
《客房管理系统VB+SQLWord文件下载.docx》由会员分享,可在线阅读,更多相关《客房管理系统VB+SQLWord文件下载.docx(48页珍藏版)》请在冰点文库上搜索。
CREATEVIEWdbo.ViewAdminInfoASSELECTAid,Aname,AlimitFROMdbo.AdminInfo管理员信息视图
CREATEVIEWdbo.ViewAllCostASSELECTSUM(Ccost)ASAllCostFROMdbo.GuestInfo总收入视图
CREATEVIEWdbo.ViewBiaozhunCostASSELECTSUM(a.Ccost)ASBiaozhunCostFROMdbo.GuestInfoaINNERJOINdbo.RoomInfobONa.Rnum=b.RnumWHERE(b.Rtype='
标准'
)标准房收入视图
CREATEVIEWdbo.ViewCostASSELECTdbo.ViewAllCost.AllCost,dbo.ViewPutongCost.putongCost,dbo.ViewBiaozhunCost.BiaozhunCost,dbo.ViewHaohuaCost.HaohuaCostFROMdbo.ViewAllCostCROSSJOINdbo.ViewPutongCostCROSSJOINdbo.ViewBiaozhunCostCROSSJOINdbo.ViewHaohuaCost收入视图
CREATEVIEWdbo.ViewEmptyRoomInfoASSELECTRnum,Rtype,Rprice,RstatesFROMdbo.RoomInfoWHERE(Rstates='
空'
)空房信息视图
CREATEVIEWdbo.ViewFullRoomInfoASSELECTdbo.RoomInfo.*FROMdbo.RoomInfoWHERE(Rstates='
满'
)满房信息视图
CREATEVIEWdbo.ViewGuestInfoASSELECTdbo.GuestInfo.*FROMdbo.GuestInfo客户信息视图
CREATEVIEWdbo.ViewHaohuaCostASSELECTSUM(a.Ccost)ASHaohuaCostFROMdbo.GuestInfoaINNERJOINdbo.RoomInfobONa.Rnum=b.RnumWHERE(b.Rtype='
豪华'
)豪华房收入视图
CREATEVIEWdbo.ViewPutongCostASSELECTSUM(a.Ccost)ASPutongCostFROMdbo.GuestInfoaINNERJOINdbo.RoomInfobONa.Rnum=b.RnumWHERE(b.Rtype='
普通'
)普通房收入视图
CREATEVIEWdbo.ViewRoomInfoASSELECTdbo.RoomInfo.*FROMdbo.RoomInfo房间信息视图
5、创建触发器
在表GuestInfo中,创建一个触发器:
CREATETRIGGERinsert_triONdbo.GuestInfo
FORINSERT
AS
updateRoomInfo
setRstates='
Cid=(selectCidfrominserted)
whereRnum=(selectRnumfrominserted)
功能:
当向表GuestInfo中插入一个客户信息时,触发表RoomInfo的某房间的状态变为“满”
6、备份数据库
给所建立的数据库进行备份
六、界面设计与代码(用VB作为界面设计语言)
1、登陆界面
PublicSubCommand1_Click()‘确定
IfTrim(DataCombo1.Text)<
>
"
"
AndTrim(Text1.Text)<
Then
Adodc1.RecordSource="
select*fromAdminInfowhereAname='
+Trim(DataCombo1.Text)+"
'
andAmima='
+Trim(Text1.Text)+"
Adodc1.Refresh
IfAdodc1.Recordset.RecordCount=1Then
Adodc1.Recordset.Close
frmMain.Show
Me.Hide
Else
MsgBox"
错误的账号或密码!
vbInformation+vbOKOnly,"
错误信息"
Text1.Text="
DataCombo1.SetFocus
EndIf
ElseIfDataCombo1.Text="
账号不能为空!
Else
密码不能为空!
Text1.SetFocus
EndIf
EndSub
PrivateSubCommand2_Click()‘取消
End
PrivateSubText1_KeyPress(KeyAsciiAsInteger)‘在文本框按下输入的事件
IfKeyAscii=13Then
Command1_Click
PrivateSubdataCombo1_KeyPress(KeyAsciiAsInteger)‘在文本框按下输入的事件
2、客户登记界面
PrivateSubCommand3_Click()‘客户登记PrivateSubCommand4_Click()‘退房结算
frmGuestAdd.ShowfrmCheckOut.Show
EndSubEndSub
PrivateSubCommand1_Click()‘当前时间
txt6.Text=Trim(Now())
PrivateSubCommand2_Click()‘确定
Iftxt1.Text="
Ortxt2.Text="
Ortxt3.Text="
Ortxt4.Text="
Ortxt6.Text="
Ortxt8.Text="
OrDataCombo1.Text="
请将蓝色部分填写完整!
提示信息"
Iftxt1.Text="
txt1.SetFocus
ElseIftxt2.Text="
txt2.SetFocus
ElseIftxt3.Text="
txt3.SetFocus
ElseIftxt4.Text="
txt4.SetFocus
ElseIftxt6.Text="
txt6.SetFocus
ElseIftxt8.Text="
txt8.SetFocus
ElseIfDataCombo1.Text="
Adodc3.RecordSource="
selectAidfromViewAdminInfowhereAname='
&
frmLogin.DataCombo1.Text&
Adodc3.Refresh
DimaidAsInteger
aid=Adodc3.Recordset.Fields(0)
Adodc4.RecordSource="
select*fromGuestInfo"
Adodc4.Refresh
Adodc4.Recordset.AddNew
Adodc4.Recordset.Fields("
Cname"
)=txt1.Text
Csex"
)=txt2.Text
Cage"
)=txt3.Text
Cpnum"
)=txt4.Text
Iftxt6.Text<
Cadd"
)=txt5.Text
Cintime"
)=txt6.Text
Iftxt7.Text<
Cday"
)=txt7.Text
Cdeposit"
)=txt8.Text
Rnum"
)=DataCombo1.Text
Aid"
)=aid
Adodc4.Recordset.Update
frmMain.Adodc1.Refresh
frmMain.DataGrid1.Refresh
登记成功!
vbOKOnly+vbInformation,"
UnloadMe
PrivateSubCommand3_Click()‘取消
UnloadMe
PrivateSubDataCombo1_Change()‘选择房间
Adodc2.RecordSource="
select*fromViewEmptyRoomInfowhereRnum='
'
Adodc2.Refresh
SetDataGrid1.DataSource=Adodc2
DataGrid1.Refresh
PrivateSubCommand1_Click()‘查看信息
IfDataCombo1.Text<
IfCommand1.Caption="
查看信息"
select*fromViewGuestInfowhereRnum='
DataCombo1.Text&
andCouttimeisnull"
RichTextBox1.Text=vbCrLf+"
+"
编号:
+Trim(Adodc1.Recordset.Fields(0).Value)+"
姓名:
+Trim(Adodc1.Recordset.Fields
(1).Value)+"
性别:
+Trim(Adodc1.Recordset.Fields
(2).Value)+"
年龄:
+Trim(Adodc1.Recordset.Fields(3).Value)+vbCrLf+vbCrLf+"
身份证号:
+Trim(Adodc1.Recordset.Fields(4).Value)+vbCrLf+vbCrLf+"
入住时间:
+Trim(Adodc1.Recordset.Fields(6).Value)+vbCrLf+vbCrLf+"
押金:
+Trim(Adodc1.Recordset.Fields(10).Value)+"
元"
frame1.Caption="
显示信息-"
+DataCombo1.Text
DataCombo1.Enabled=False
Command1.Caption="
取消"
Command2.Enabled=True
ElseIfCommand1.Caption="
显示信息"
RichTextBox1.Text="
DataCombo1.Enabled=True
DataCombo1.Text="
Command2.Enabled=False
房间编号不能为空!
PrivateSubCommand2_Click()‘费用结算
Me.Hide
frmPrice.Show
PrivateSubCommand3_Click()‘退出
DimtimeAsDate
DimeAsInteger
PrivateSubCommand1_Click()‘退房
IfMsgBox("
确定退房结帐吗?
vbOKCancel+vbInformation,"
)=vbOKThen
selectCouttime,Ccost,RnumfromGuestInfowhereRnum='
frmCheckOut.DataCombo1.Text&
updataGuestInfosetCouttime="
time&
Ccost="
e&
DimrnumAsString
rnum=Adodc3.Recordset.Fields
(2)
Adodc3.Recordset.Fields(0)=time
Adodc3.Recordset.Fields
(1)=e
Adodc3.Recordset.Update
select*fromRoomInfowhereRnum='
rnum&
Adodc4.Recordset.Fields(3)="
空"
Adodc4.Recordset.Fields(4)=Null
frmMain.Adodc6.Refresh
退房成功!
UnloadfrmCheckOut
PrivateSubCommand2_Click()
UnloadfrmCheckOut
PrivateSubForm_Load()‘费用结算窗体加载事件
selectRpricefromViewRoomInfowhereRnum="
查询房间的价格
DimpriceAsInteger'
房间价格
price=CInt(Adodc2.Recordset.Fields(0))'
获取房间价格
Label2.Caption="
房间:
+frmCheckOut.DataCombo1.Text+"
单价:
+CStr(price)+"
元/天"
Adodc1.RecordSource="
查询客户信息
Adodc1.Refresh
frame1.Caption="
姓名:
性别:
年龄:
+Trim(Adodc1.Recordset.Fields(3).Value)
DimaAsDouble
DimbAsInteger
DimcAsDouble
DimdAsInteger
time=Now()
a=CDbl(time-Adodc1.Recordset.Fields(6))'
精确时间
b=CInt(time-Adodc1.Recordset.Fields(6))'
对时间取整
c=a-b'
计算出小于一天的时间
Ifc>
0Then
d=b+1'
不足一天算一天
d=b
e=d*price'
计算住宿费
RichTextBox1.Text=vbCrLf+"
当前时间:
+Trim(time)+vbCrLf+vbCrLf+"
共住"
+CStr(a)+"
天"
计"
+CStr(d)+"
+vbCrLf+vbCrLf+"
住宿费:
+CStr(e)+"
元"
应收:
+Trim(e-CInt(Adodc1.Recordset.Fields(10)))+"
元"
3、房间管理界面
PrivateSubCommand7_Click()‘增加房间
Command7.Enabled=False
Command8.Enabled=False
Command9.Enabled=False
Command10.Enabled=True
Command11.Enabled=True
frame1.Enabled=True
增加房间"
Text1.BackColor=&
H80000005
Text2.BackColor=&
Text3.BackColor=&
Text4.BackColor=&
Text2.Visible=False
Combo3.Visible=True
Text4.Enabled=False
Text4.Text="
Text1.Enabled=True
Text2.Enabled=True
Text3.Enabled=True
Text1.SetFocus
PrivateSubCommand8_Click()‘修改房间
修改房间"
Text1.Visible=False
DataCombo2.Visible=True
Text3.BackCol