客房管理系统VBSQL.doc

1、数据库课程设计报告题目：客房管理系统时间：2008-6-27客房管理系统一、 课程设计的目的和意义在当今经济和商务交往日益频繁的状况下,宾馆服务行业正面临客流量骤增的压力。越来越多的宾馆饭店都认识到传统的管理方法已经不能适合当今社会的需要,必须借助先进的计算机信息技术对宾馆服务进行管理。客房管理系统可以说是整个宾馆计算机信息系统的中心子系统,因为宾馆最主要的功能就是为旅客提供客房。设计客房管理这样一个系统,可以涉及到大多数SQL Server数据库的重要数据库对象、重要功能和特性,比如：视图、触发器和存储过程等。由此,通过这个课程设计可以加深对这些SQL Server数据库知识的学习、理解,积

2、累在实际工程应用中运用各种数据库对象的经验,使学生掌握使用应用软件开发工具开发数据库管理系统的基本方法。在实用性方面,客房管理系统是很普遍的一种应用,选择该系统作为课程设计也可以为学生以后可能遇到的实际开发提供借鉴。本课程设计的主要目的是学习和练习SQL Server数据库的实际应用,所以选择SQL Server 2000作为数据库服务器。二、 需求分析系统主要实现对客房的管理功能。主要有管理员登陆、客户登记、房间管理、客户查询、房间查询、收入统计、管理员信息管理。系统总体设计图如下：三、 概念结构设计E-R图四、 逻辑结构设计将E-R图转换成关系模式：管理员编号,账号,密码,权限房 间编号,

3、类型,价格,状态,入住客户编号客 户编号,性别,年龄,号,住址,入住时间,预计天数,房间编号,退房时间,押金,住宿费,登记人关系规化：均已达到第三式五、 数据实施与维护1、 建立数据库 打开SQL Server 2000企业管理器,建立数据库HotelRoom。2、 添加数据表在HotelRoom数据库中,添加表管理员信息表AdminInfo、房间信息表RoomInfo、客户信息表GuestInfo。3、 建立关系图4、 创建视图CREATE VIEW dbo.ViewAdminInfo AS SELECT Aid, Aname, Alimit FROM dbo.AdminInfo 管理员信息

4、视图CREATE VIEW dbo.ViewAllCostASSELECT SUM AS AllCostFROM dbo.GuestInfo 总收入视图CREATE VIEW dbo.ViewBiaozhunCostASSELECT SUM AS BiaozhunCostFROM dbo.GuestInfo a INNER JOINdbo.RoomInfo b ON a.Rnum = b.Rnum WHERE 标准房收入视图CREATE VIEW dbo.ViewCostASSELECT dbo.ViewAllCost.AllCost,dbo.ViewPutongCost.putongCost

5、 dbo.ViewBiaozhunCost.BiaozhunCost, dbo.ViewHaohuaCost.HaohuaCostFROM dbo.ViewAllCost CROSS JOIN dbo.ViewPutongCost CROSS JOINdbo.ViewBiaozhunCost CROSS JOINdbo.ViewHaohuaCost 收入视图CREATE VIEW dbo.ViewEmptyRoomInfoASSELECT Rnum, Rtype, Rprice, RstatesFROM dbo.RoomInfo WHERE 空房信息视图CREATE VIEW dbo.Vie

6、wFullRoomInfoASSELECT dbo.RoomInfo.*FROM dbo.RoomInfo WHERE 满房信息视图CREATE VIEW dbo.ViewGuestInfoASSELECT dbo.GuestInfo.*FROM dbo.GuestInfo 客户信息视图CREATE VIEW dbo.ViewHaohuaCostASSELECT SUM AS HaohuaCostFROM dbo.GuestInfo a INNER JOINdbo.RoomInfo b ON a.Rnum = b.Rnum WHERE 豪华房收入视图CREATE VIEW dbo.ViewPu

7、tongCostASSELECT SUM AS PutongCostFROM dbo.GuestInfo a INNER JOINdbo.RoomInfo b ON a.Rnum = b.Rnum WHERE 普通房收入视图CREATE VIEW dbo.ViewRoomInfoASSELECT dbo.RoomInfo.*FROM dbo.RoomInfo 房间信息视图5、 创建触发器在表GuestInfo中,创建一个触发器：CREATE TRIGGER insert_tri ON dbo.GuestInfoFOR INSERT ASupdate RoomInfo set Rstates=满

8、Cid=where Rnum=功能：当向表GuestInfo中插入一个客户信息时,触发表RoomInfo的某房间的状态变为满6、 备份数据库给所建立的数据库进行备份六、 界面设计与代码用VB作为界面设计语言1、 登陆界面Public Sub Command1_Click确定If Trim And Trim Then Adodc1.RecordSource = select * from AdminInfo where Aname= + Trim + and Amima= + Trim + Adodc1.Refresh If Adodc1.Recordset.RecordCount = 1 T

9、hen Adodc1.Recordset.Close frmMain.Show Me.Hide Else MsgBox 错误的账号或密码！, vbInformation + vbOKOnly, 错误信息 Text1.Text = DataCombo1.SetFocus End IfElseIf DataCombo1.Text = Then MsgBox 账号不能为空！, vbInformation + vbOKOnly, 错误信息 DataCombo1.SetFocusElse MsgBox 密码不能为空！, vbInformation + vbOKOnly, 错误信息 Text1.SetFo

10、cusEnd IfEnd SubPrivate Sub Command2_Click取消 EndEnd SubPrivate Sub Text1_KeyPress在文本框按下输入的事件 If KeyAscii = 13 Then Command1_Click End IfEnd SubPrivate Sub dataCombo1_KeyPress在文本框按下输入的事件 If KeyAscii = 13 Then Command1_Click End IfEnd Sub2、 客户登记界面Private Sub Command3_Click客户登记 Private Sub Command4_Cli

11、ck退房结算frmGuestAdd.Show frmCheckOut.ShowEnd Sub End SubPrivate Sub Command1_Click当前时间txt6.Text = TrimNowEnd SubPrivate Sub Command2_Click确定If txt1.Text = Or txt2.Text = Or txt3.Text = Or txt4.Text = Or txt6.Text = Or txt8.Text = Or DataCombo1.Text = Then MsgBox 请将蓝色部分填写完整！, vbInformation + vbOKOnly,

12、提示信息 If txt1.Text = Then txt1.SetFocus ElseIf txt2.Text = Then txt2.SetFocus ElseIf txt3.Text = Then txt3.SetFocus ElseIf txt4.Text = Then txt4.SetFocus ElseIf txt6.Text = Then txt6.SetFocus ElseIf txt8.Text = Then txt8.SetFocus ElseIf DataCombo1.Text = Then DataCombo1.SetFocus End IfElse Adodc3.Rec

13、ordSource = select Aid from ViewAdminInfo where Aname= & frmLogin.DataCombo1.Text & Adodc3.Refresh Dim aid As Integer aid = Adodc3.Recordset.Fields Adodc4.RecordSource = select * from GuestInfo Adodc4.Refresh Adodc4.Recordset.AddNew Adodc4.Recordset.Fields = txt1.Text Adodc4.Recordset.Fields = txt2.

14、Text Adodc4.Recordset.Fields = txt3.Text Adodc4.Recordset.Fields = txt4.Text If txt6.Text Then Adodc4.Recordset.Fields = txt5.Text End If Adodc4.Recordset.Fields = txt6.Text If txt7.Text Then Adodc4.Recordset.Fields = txt7.Text End If Adodc4.Recordset.Fields = txt8.Text Adodc4.Recordset.Fields = Dat

15、aCombo1.Text Adodc4.Recordset.Fields = aid Adodc4.Recordset.Update frmMain.Adodc1.Refresh frmMain.DataGrid1.Refresh MsgBox 登记成功！, vbOKOnly + vbInformation, 提示信息 Unload MeEnd IfEnd SubPrivate Sub Command3_Click取消Unload MeEnd SubPrivate Sub DataCombo1_Change选择房间Adodc2.RecordSource = select * from View

16、EmptyRoomInfo where Rnum= + Trim + Adodc2.RefreshSet DataGrid1.DataSource = Adodc2DataGrid1.RefreshEnd SubPrivate Sub Command1_Click查看信息If DataCombo1.Text Then If Command1.Caption = 查看信息 Then Adodc1.RecordSource = select * from ViewGuestInfo where Rnum= & DataCombo1.Text & and Couttime is null Adodc

17、1.Refresh RichTextBox1.Text = vbCrLf + + 编号： + TrimAdodc1.Recordset.Fields.Value + + ： + TrimAdodc1.Recordset.Fields.Value + + 性别： + TrimAdodc1.Recordset.Fields.Value + + 年龄： + TrimAdodc1.Recordset.Fields.Value + vbCrLf + vbCrLf + + 号： + TrimAdodc1.Recordset.Fields.Value + vbCrLf + vbCrLf + + 入住时间：

18、 TrimAdodc1.Recordset.Fields.Value + vbCrLf + vbCrLf + + 押金： + TrimAdodc1.Recordset.Fields.Value + 元 frame1.Caption = 显示信息- + DataCombo1.Text DataCombo1.Enabled = False Command1.Caption = 取消 Command2.Enabled = True ElseIf Command1.Caption = 取消 Then frame1.Caption = 显示信息 RichTextBox1.Text = DataComb

19、o1.Enabled = True DataCombo1.Text = Command1.Caption = 查看信息 Command2.Enabled = False End If Else MsgBox 房间编号不能为空！, vbOKOnly + vbInformation, 错误信息 DataCombo1.SetFocus End IfEnd SubPrivate Sub Command2_Click费用结算Me.HidefrmPrice.ShowEnd SubPrivate Sub Command3_Click退出Unload MeEnd SubDim time As DateDim

20、e As IntegerPrivate Sub Command1_Click退房If MsgBox = vbOK Then Adodc3.RecordSource = select Couttime,Ccost,Rnum from GuestInfo where Rnum= & frmCheckOut.DataCombo1.Text & and Couttime is null updata GuestInfo set Couttime= & time & ,Ccost= & e & Adodc3.Refresh Dim rnum As String rnum = Adodc3.Records

21、et.Fields Adodc3.Recordset.Fields = time Adodc3.Recordset.Fields = e Adodc3.Recordset.Update Adodc4.RecordSource = select * from RoomInfo where Rnum= & rnum & Adodc4.Refresh Adodc4.Recordset.Fields = 空 Adodc4.Recordset.Fields = Null Adodc4.Recordset.Update frmMain.Adodc1.Refresh frmMain.Adodc6.Refre

22、sh frmMain.DataGrid1.Refresh MsgBox 退房成功！, vbOKOnly + vbInformation, 提示信息 Unload frmCheckOut Unload MeElse Unload frmCheckOut Unload MeEnd IfEnd SubPrivate Sub Command2_ClickUnload frmCheckOutUnload MeEnd SubPrivate Sub Form_Load费用结算窗体加载事件Adodc2.RecordSource = select Rprice from ViewRoomInfo where R

23、num= & frmCheckOut.DataCombo1.Text & 查询房间的价格Adodc2.RefreshDim price As Integer 房间价格price = CIntAdodc2.Recordset.Fields 获取房间价格Label2.Caption = 房间： + frmCheckOut.DataCombo1.Text + + 单价： + CStr + 元/天Adodc1.RecordSource = select * from ViewGuestInfo where Rnum= & frmCheckOut.DataCombo1.Text & and Coutti

24、me is null 查询客户信息Adodc1.Refreshframe1.Caption = 编号： + TrimAdodc1.Recordset.Fields.Value + ： + TrimAdodc1.Recordset.Fields.Value + 性别： + TrimAdodc1.Recordset.Fields.Value + 年龄： + TrimAdodc1.Recordset.Fields.ValueDim a As DoubleDim b As IntegerDim c As DoubleDim d As Integertime = Nowa = CDbltime - Ad

25、odc1.Recordset.Fields 精确时间b = CInttime - Adodc1.Recordset.Fields 对时间取整c = a - b 计算出小于一天的时间If c 0 Thend = b + 1 不足一天算一天Elsed = bEnd Ife = d * price 计算住宿费RichTextBox1.Text = vbCrLf + + 入住时间： + TrimAdodc1.Recordset.Fields.Value + vbCrLf + vbCrLf + + 当前时间： + Trim + vbCrLf + vbCrLf + + 共住 + CStr + 天 + +

26、计 + CStr + 天 + vbCrLf + vbCrLf + + 住宿费： + CStr + 元 + 押金： + TrimAdodc1.Recordset.Fields.Value + 元 + 应收： + Trime - CIntAdodc1.Recordset.Fields + 元End Sub3、 房间管理界面54 / 54Private Sub Command7_Click增加房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand

27、11.Enabled = Trueframe1.Enabled = Trueframe1.Caption = 增加房间Text1.BackColor = &H80000005Text2.BackColor = &H80000005Text3.BackColor = &H80000005Text4.BackColor = &H80000005Text2.Visible = FalseCombo3.Visible = TrueText4.Enabled = FalseText4.Text = 空Text1.Enabled = TrueText2.Enabled = TrueText3.Enable

28、d = TrueText1.SetFocusEnd SubPrivate Sub Command8_Click修改房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand11.Enabled = Trueframe1.Enabled = Trueframe1.Caption = 修改房间Text1.Visible = FalseDataCombo2.Visible = TrueText2.BackColor = &H80000005Text3

29、BackColor = &H80000005Text4.BackColor = &H80000005Text4.Enabled = FalseText2.Enabled = FalseText3.Enabled = FalseDataCombo2.SetFocusEnd SubPrivate Sub Command9_Click删除房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand11.Enabled = Trueframe1.Ena

30、bled = Trueframe1.Caption = 删除房间Text1.Visible = FalseDataCombo2.Visible = TrueText2.BackColor = &H80000005Text3.BackColor = &H80000005Text4.BackColor = &H80000005Text2.Enabled = FalseText3.Enabled = FalseText4.Enabled = FalseDataCombo2.SetFocusEnd SubPrivate Sub DataCombo2_Change Adodc5.RecordSource

31、 = select * from ViewEmptyRoomInfo where Rnum= + Trim + Adodc5.Refresh Set Text2.DataSource = Adodc5 Set Text3.DataSource = Adodc5 Set Text4.DataSource = Adodc5 Text2.Refresh Text3.Refresh Text4.Refresh If frame1.Caption = 修改房间 Then Set Combo3.DataSource = Adodc5 Combo3.Refresh Combo3.Enabled = True

32、 Combo3.Visible = True Text2.Visible = False Text3.Enabled = True End IfEnd SubPrivate Sub Command10_Click确定If frame1.Caption = 增加房间 Then If Text1.Text And Combo3.Text And Text3.Text Then If MsgBox = vbOK Then Adodc5.RecordSource = select * from ViewRoomInfo where Rnum= & Text1.Text & Adodc5.Refresh

33、 If Adodc5.Recordset.RecordCount = 1 Then MsgBox 你输入的房间编号 & Text1.Text & 重复！请重新输入。, vbInformation + vbOKOnly, 提示信息 Text1.Text = Text2.Text = Text3.Text = Text1.SetFocus Else Adodc5mandType = adCmdText Adodc5.RecordSource = select * from RoomInfo Adodc5.Recordset.AddNew Adodc5.Recordset.Fields = Text

34、1.Text Adodc5.Recordset.Fields = Combo3.Text Adodc5.Recordset.Fields = Text3.Text Adodc5.Recordset.Fields = Text4.Text Adodc5.Recordset.Update Adodc2.Refresh Set DataGrid2.DataSource = Adodc2 DataCombo2.Refresh Adodc6.Refresh MsgBox 增加房间 & Text1.Text & 成功！, vbInformation + vbOKOnly, 提示信息 Command11_C

35、lick End If Else Command11_Click End If Else MsgBox 房间信息填写不完整,请继续填写！, vbOKOnly + vbInformation, 错误提示 End IfElseIf frame1.Caption = 修改房间 Then If DataCombo2.Text Then If MsgBox = vbOK Then Adodc5mandType = adCmdText Adodc5.RecordSource = updata RoomInfo set Rtype= & Text2.Text & ,Rprice= & Text3.Text

36、 where Rnum= & DataCombo2.Text & Adodc5.Recordset.Update Adodc2.Refresh Set DataGrid2.DataSource = Adodc2DataCombo2.Refresh Adodc6.Refresh MsgBox 房间信息修改成功！, vbOKOnly + vbInformation, 提示信息 Command11_Click Else Command11_Click End If Else MsgBox 请选择要修改的房间！, vbInformation + vbOKOnly, 提示信息 DataCombo2.S

37、etFocus End IfElseIf frame1.Caption = 删除房间 Then If DataCombo2.Text Then If MsgBox = vbOK Then Adodc5mandType = adCmdText Adodc5.RecordSource = delete from RoomInfo where Rnum= & DataCombo2.Text & Adodc5.Recordset.Delete Adodc5.Recordset.Update Adodc2.Refresh Set DataGrid2.DataSource = Adodc2 DataGri

38、d2.Refresh Adodc6.Refresh MsgBox 删除房间 & DataCombo2.Text & 成功！, vbInformation + vbOKOnly, 提示信息 Command11_Click Else Command11_Click End If Else MsgBox 请选择要删除的房间！, vbOKOnly + vbInformation, 提示信息 DataCombo2.SetFocus End IfEnd IfEnd SubPrivate Sub Command11_Click取消Text1.Text = Text2.Text = Text3.Text =

39、Text4.Text = Combo3.Text = DataCombo2.Text = frame1.Enabled = FalseCommand7.Enabled = TrueCommand8.Enabled = TrueCommand9.Enabled = TrueCommand10.Enabled = FalseCommand11.Enabled = Falseframe1.Caption = DataCombo2.Visible = FalseText1.Visible = TrueCombo3.Visible = FalseText2.Visible = TrueText1.Bac

40、kColor = &H8000000FText2.BackColor = &H8000000FText3.BackColor = &H8000000FText4.BackColor = &H8000000FText4.Enabled = TrueSet Combo3.DataSource = NothingSet Text2.DataSource = NothingSet Text3.DataSource = NothingSet Text4.DataSource = NothingCombo3.RefreshText2.RefreshText3.RefreshText4.RefreshEnd Sub