酒店管理系统实验报告-2

酒店管理系统实验报告06808班6号朱伟数据字典表英文名：guests表中文名：客人信息表序号属性名含义关键字类型长度阈值说明输入方式1Roomcode房间号varchar4参照room表客户登记2Name客户名varchar20客户登记3Sex性别varchar1M:男；F:女客户登记4Age年龄Int31~200客户登记5certclassno证件编码varchar2参照编码表客户登记6Certificiate证件名varchar20客户登记7countryno国家编码varchar2参照国籍表客户登记8arridate到达日期Smalldatetime客户登记9leftdate离开日期Smalldatetimeleftdate>arridate客户登记10remarks备注varchar20客户登记表英文名：roomstate表中文名：房间状态表1Roomcode房间号房间状态表4参照room表数据库录入2Roomstate房间状态房间状态表2r:可租z:整理o:故障l:住人数据库录入、修改表英文名：roomtype表中文名：房间类型表1typeno分类号varchar2数据库录入2typename分类名varchar101:豪华套,2:总统套,3:标准间,4:单人间5:三人间数据库录入3beds床位数Int1数据库录入4price定价Float数据库录入5remarks备注varchar20数据库录入表英文名：room表中文名：房间配置表1roomcode房间号码varchar4数据库录入2typeno房间分类号varchar2参照roomtype表数据库录入表英文名：countrycodes表中文名：国籍编码表1countryno国籍编码varchar2两位数字数据库录入2country国籍varchar20数据库录入表英文名：certclasses表中文名：证件编码表1Certno证件编码varchar2数据库录入2certclass证件名称varchar20数据库录入二、功能说明本实验设计的酒店管理系统的主要完成客户的入店信息登记、资料修改、房间基本查询等项目的基本功能1、Fjcx窗体主要完成窗体主要完成客户在登记时的房间选择DataCombo1用于显示房间类型Datalist1数据框用于显示处于当前选择状态的房间号PrivateSubDataCombo1_Change()IfOption1.Value=TrueThen'查询出可租房间str2="selectroom.roomcodefromroom,roomstatewhereroomstate.roomcode=room.roomcodeandroomstate.roomstate='r'andtypeno='"&DataCombo1.BoundText&"'"SetrsRoom=NewADODB.RecordsetrsRoom.Openstr2,cnn,adOpenKeyset,adLockOptimisticSetDataList1.RowSource=rsRoomDataList1.ListField="roomcode"ElseIfOption2.Value=TrueThen'查询出已住人的房间str2="selectroom.roomcodefromroom,roomstatewhereroomstate.roomcode=room.roomcodeandroomstate.roomstate='l'andtypeno='"&DataCombo1.BoundText&"'"Setrst2=NewADODB.Recordsetrst2.Openstr2,cnn,adOpenKeyset,adLockOptimisticSetDataList1.RowSource=rst2DataList1.ListField="roomcode"ElseIfOption3.Value=TrueThen'查询出故障房间str2="selectroom.roomcodefromroom,roomstatewhereroomstate.roomcode=room.roomcodeandroomstate.roomstate='o'andtypeno='"&DataCombo1.BoundText&"'"Setrst2=NewADODB.Recordsetrst2.Openstr2,cnn,adOpenKeyset,adLockOptimisticSetDataList1.RowSource=rst2DataList1.ListField="roomcode"Else'查询出整理房间str2="selectroom.roomcodefromroom,roomstatewhereroomstate.roomcode=room.roomcodeandroomstate.roomstate='z'andtypeno='"&DataCombo1.BoundText&"'"Setrst2=NewADODB.Recordsetrst2.Openstr2,cnn,adOpenKeyset,adLockOptimisticSetDataList1.RowSource=rst2DataList1.ListField="roomcode"EndIfEndSub2、Fjsz房间设置窗体用于修改房间所处的状态修改所需主要函数：PrivateSubCommand1_Click()DimstateAsStringDimrsAsNewADODB.RecordsetDimcnnAsNewADODB.ConnectionIfOption1.Value=TrueThenstate="r"ElseIfOption2.Value=TrueThenstate="z"ElseIfOption3.Value=TrueThenstate="o"ElseIfOption4.Value=TrueThenstate="r"EndIfcnn.Open"DSN=hotelodbc"rs.Open"select*fromroomstatewhereroomcode='"+DataCombo1+"'",cnn,adOpenKeyset,adLockOptimisticIfrs.RecordCount>0Thenrs.Fields("roomstate")=staters.Updatei=MsgBox("修改状态成功")EndIfEndSub3.fjzt房间状态窗体显示当前的所有房间的状态主要由adodc控件来完成与数据库的连接查询语句：selectroom.roomcode,typename,beds,price,roomstate,room.typeno,remarksfromroomtype,roomstate,roomwhereroom.typeno=roomtype.typenoandroomstate.roomcode=room.roomcode4、krdj客人登记窗体两个adodc控件选择2-adcmdtable方式分别连接了国籍与证件类型datacombo控件。窗体语句：PrivateSubCommand1_Click()Dimsqlstr1AsNewADODB.RecordsetDimsqlstr2AsNewADODB.RecordsetDimsqlno1Dimsqlno2DimsextypeAsStringDimrsAsNewADODB.RecordsetDimcnnAsNewADODB.ConnectionIfLabel7.Caption=""ThenMsgBox"请输入房间号！",48,"警告"ExitSubEndIfIfText1.Text=""ThenMsgBox"请输入姓名！",48,"警告"ExitSubEndIfIfCombo2.Text="请选择"ThenMsgBox"请选择性别！",48,"警告"ExitSubEndIfIf(Val(Text2.Text)>200)Or(Val(Text2.Text)<1)ThenMsgBox"输入年龄必须在1—200之间！",48,"提示"EndIfIfText3.Text=""ThenMsgBox"请输入证件编码！",48,"警告"ExitSubEndIfIfDataCombo1.Text="请选择"ThenMsgBox"请选择证件种类！",48,"警告"ExitSubEndIfIfDataCombo2.Text="请选择"ThenMsgBox"请选择国藉！",48,"警告"ExitSubEndIfIfDTPicker1.Value>=DTPicker2.ValueThenMsgBox"日期有误，请更正！",48,"警告"ExitSubEndIfIfrs.state=1Thenrs.CloseEndIfcnn.Open"DSN=hotelodbc"sqlstr1.Open"selectcertnoasnofromcertclasseswherecertclass='"+DataCombo1.Text+"'",cnn,adOpenForwardOnly,adLockReadOnlysqlno1=sqlstr1!no'Ifrs.state=1Then'rs.Close'EndIfsqlstr2.Open"selectcountrynoasnofromcountrycodeswherecountry='"+DataCombo2.Text+"'",cnn,adOpenForwardOnly,adLockReadOnlysqlno2=sqlstr2!noIfrs.state=1Thenrs.CloseEndIf'cnn.Open"DSN=hotelodbc"rs.Open"select*fromroomstatewhereroomcode='"+Label7.Caption+"'androomstate='r'",cnn,adOpenKeyset,adLockOptimisticIfrs.RecordCount>0ThenIfrs.state=1Thenrs.CloseEndIfrs.Open"select*fromguests"rs.AddNewrs.Fields("roomcode")=Label7.Captionrs.Fields("name")=Text1.Textrs.Fields("age")=Text2.Textrs.Fields("certclassno")=sqlno1rs.Fields("countryno")=sqlno2rs.Fields("arridate")=DTPicker1.Valuers.Fields("leftdate")=DTPicker2.Valuers.Fields("remarks")=Text6.Textrs.Fields("sex")=Combo2.Textrs.UpdateBatchadAffectCurrenti=MsgBox("登记成功")EndIfIfrs.state=1Thenrs.CloseEndIf'cnn.Open"DSN=hotelodbc"rs.Open"select*fromroomstatewhereroomcode='"+Label7.Caption+"'",cnn,adOpenKeyset,adLockOptimisticIfrs.RecordCount>0Thenrs.Fields("roomstate")="l"rs.UpdateEndIfUnloadMeMain.ShowEndSubPrivateSubCommand2_Click()Text1.Text=""Text2.Text=""Combo2.Text=""DataCombo1.Text=""Text3.Text=""DataCombo2.Text=""Text6.Text=""UnloadMeFjcx.Show1EndSubPrivateSubCommand4_Click()UnloadMeMain.ShowEndSubPrivateSubCommand5_Click()UnloadMeFjcx.Show1EndSubPrivateSubForm_Load()DataCombo1.Text="请选择"DataCombo2.Text="请选择"Text6.Text=""EndSub5、krts客人退房与查询窗体使用datacombo控件来实现已入住房间的查找工作；初始语句为：PrivateSubForm_Load()Setcnn=NewADODB.Connection'建立数据库连接对象DimconnectstringAsStringconnectstring="DSN=hotelodbc"'使用的DSNcnn.Openconnectstring'建立数据库连接Setrsroomcode=NewADODB.Recordsetstr="select*fromguests"rsroomcode.Openstr,cnn,adOpenKeyset,adLockReadOnlySetDataCombo1.RowSource=rsroomcodeDataCombo1.ListField="roomcode"DataCombo1.BoundText="roomcode"rsroomcode.MoveFirstDataCombo1.Text=rsroomcode.Fields("roomcode")EndSub窗体语句：DimrsroomcodeAsADODB.Recordset'存储房间号DimrsguestsAsADODB.RecordsetDimrst1AsADODB.RecordsetDimstrAsStringPrivateSubCommand1_Click()Setcnn=NewADODB.Connection'建立数据库连接对象DimconnectstringAsStringconnectstring="DSN=hotelodbc"'使用的DSNcnn.Openconnectstring'建立数据库连接DimrstAsADODB.RecordsetDimXAsIntegerX=MsgBox("确定退房？",vbYesNo,"确定退房")IfX=6ThenOnErrorGoToerrcnn.BeginTransstr2="deletefromguestswhereroomcode='"&DataCombo1.Text&"'"Debug.Printstr2cnn.Executestr2str2="updateroomstatesetroomstate='r'whereroomcode='"&DataCombo1.Text&"'"Debug.Printstr2cnn.Executestr2Debug.Printstr2cnn.Executestr2MsgBox"退房成功",vbOKOnly+vbInformation,"提示"UnloadMecnn.CommitTransExitSuberr:cnn.RollbackTransMsgBox"退房失败",vbOKOnly+vbInformation,"提示"EndIfUnloadMeEndSubPrivateSubCommand2_Click()UnloadMeMain.Show1EndSubPrivateSubCommand3_Click()DimrstAsADODB.RecordsetSetcnn=NewADODB.Connection'建立数据库连接对象DimconnectstringAsStringconnectstring="DSN=hotelodbc"'使用的DSNcnn.Openconnectstring'建立数据库连接IfText1.Text=""ThenMsgBox"请输入客人姓名",vbOKOnly+vbInformation,"提示"ExitSubElseSetrsguests=NewADODB.Recordsetstr="selectguests.roomcode房间号码,name姓名,sex性别,certclass证件,country国籍,arridate入住日期,leftdate拟走日期,remarks备注fromguests,certclasses,countrycodeswhereguests.certclassno=certclasses.certnoandguests.countryno=countrycodes.countrynoandnamelike'"&Text1.Text&"%'"rsguests.Openstr,cnn,adOpenKeyset,adLockReadOnlySetDataGrid1.DataSource=rsguestsEndIfEndSubPrivateSubCommand4_Cl