数据库课程设计酒店管理信息系统

酒店管理信息系统系统平台：Windows2000数据库服务器：MicrosoftSQLServer2000开发工具：VisualBasic6.0系统开发人员：⒈编写目地酒店在正常地运营中需要对客房资源、顾客信息、结算信息进行管理,利用宾馆管理信息系统及时了解各个环节中信息地变更,有利于提高管理效率.b5E2RGbCAP⒉系统功能分析系统开发地总体任务是实现宾馆各种信息地系统化、规范化和自动化 .主要完成功能：●有关客房标准地制定、标准信息地输入,包括标准编号、标准名称、房间面积、床位数量、住房单价、是否有空调、电视机、电话以及单独卫生间等.p1EanqFDPw●客房标准信息地修改、查询等 .●客房基本信息地输入,包括客房编号、客房类型、客房位置、客房单价、备注等.●客房基本信息地查询、修改,包括客房编号、客房类型、客房位置、客房单价、备注等.●剩余客房信息地查询等.●订房信息地输入,包括客房编号、客房种类、客房位置、客房单价、顾客姓名、顾客身份证号码、入住日期、折扣、备注信息等.DXDiTa9E3d●订房信息地修改和查询 ,包括客房编号、客房种类、客房位置、客房单价、顾客姓名、顾客身份证号码、入住日期、折扣、备注信息等.RTCrpUDGiT●结算信息地输入,包括客房编号、客房种类、位置、客房单价、顾客姓名、顾客身份证号码、入住日期、折扣、结算日期、备注信息等.5PCzVD7HxA●结算信息地修改和查询,包括客房编号、客房种类、客房位置、客房单价、顾客姓名、顾客身份证号码、入住日期、折扣、结日期、备注信息等.jLBHrnAILg⒊系统功能模块设计按结构化程序设计思想,分析得出如下系统功能模块图宾馆管理信息系统结订客算房房系信信信统息息息管管管管理理理理剩结结结订订订余设设算算算房房房客置置密用信信信信信信房客客码户息息息息息息信房房管管查修添查修添息信标理理询改加询改加查息准询客客客客客房房房房房信信信标标息息息准准查修添修添询改加改加xHAQX74J0X图Ⅰ系统功能模块图⒋数据库设计数据库设计步骤：● 数据库需求分析● 数据库概念结构设计● 数据库逻辑结构设计①数据库需求分析分析调查有关宾馆管理信息需求地基础上得本系统所处理地数据流程客房标准设置客房登记基本信息输入基本信息录入客房信息管理顾客登记订房信息管理订房信息管理 客房信息返回结算信息输入结算信息管理LDAYtRyKfE图Ⅱ宾馆管理信息系统数据流程图针对一般宾馆管理信息系统地需求 ,通过对宾馆管理过程地内容和流程分析 ,设计如下面所示地数据项和数据结构 ：Zzz6ZB2Ltk● 客房标准信息,包括地数据项有：标准编号、标准名称、房间面积、床位数量、住房单价、是否有空调、电视、电话、单独卫生间等.dvzfvkwMI1●客房信息,包括地数据项有：客房编号、客房种类、客房位置、客房单价、备注等.● 订房信息,包括地数据项有：订房编号、客房编号、客房种类、客房位置、客房备注信息、顾客姓名、顾客身份证号码、入住时间、折扣、备注等.rqyn14ZNXI● 结算信息,包括地数据项有：订房编号、客房编号、客房种类、客房位置、客房备注信息、顾客姓名、顾客身份证号码、入住时间、折扣、结算时间、备注等.EmxvxOtOco②数据库概念结构设计本系统根据以上地设计规划出地实体有：客房标准信息实体、客房信息实体、订房信息实体、结算信息实体 .各个实体具体地 描述Ｅ-Ｒ图如下：SixE2yXPq5客房标准信息实体标准编号

客房单价标准名称 床位数量客房标准信息实体Ｅ-Ｒ图客房信息实体客房标准 客房单价客房编号 客房位置客房信息实体Ｅ-Ｒ图订房信息实体客房信息 折扣顾客信息入住时间订房信息实体Ｅ-Ｒ图结算信息实体客房信息 结算金额顾客信息 结算时间结算信息实体Ｅ-Ｒ图客房标准信息客房登记客房信息管理订房 结算信息管理订房信息管理 客房结算实体之间关系Ｅ-Ｒ图③数据库逻辑结构设计首先将宾馆管理信息系统地数据库概念结构转化为SQL2000数据库系统所支持地实际数据模型,即：数据库地逻辑结构.其各个表格地设计结果如下6ewMyirQFL列名数据类型可否为空说明TypeidVarcharNOTNULL标准编号TypenameVarcharNOTNULL标准名称AreaNumericNULL房间面积BednumNumericNULL床位数量HairconditionVarcharNULL是否有空调HtelephoneVarcharNULL是否有电话HtelevisionVarcharNULL是否有电视HtoiletVarcharNULL是否有单独卫生间PriceNumericNULL单价Roomtype客房标准信息表列名数据类型可否为空说明roomNOVarcharNOTNULL客房编号RoomtypeVarcharNOTNULL客房种类RoompositionVarcharNULL客房位置RoompriceNumericNULL单价PutupVarcharNOTNULL是否被预定RoommemoTextNULL备注Rooms客房信息表列名数据类型可否为空说明BooknoVarcharNOTNULL订房编号CustomnameVarcharNOTNULL顾客姓名CustomIDVarcharNOTNULL身份证号码RoomnoVarcharNOTNULL客房编号IndateDatetimeNULL入住日期DiscountNumericNULL折扣InmenoTextNULL备注CheckdateDatetimeNULL结算日期AmountNumericNULL金额Bookin订房信息表⒌数据库结构地实现利用SQL2000数据库系统中地查询分析实现数据库地逻辑结构,其表格如下：创建系统用户表格user_InfoCREATETABLE[dbo].[user_Info1]([user_ID][char](10>COLLATEChinese_PRC_CI_ASNOTNULL,kavU42VRUs[user_PWD][char](10>COLLATEChinese_PRC_CI_ASNULL,y6v3ALoS89[user_Des][char](10>COLLATEChinese_PRC_CI_ASNULLM2ub6vSTnP>ON[PRIMARY]创建客房标准信息表格 roomtypeCREATETABLE[dbo].[roomtype]([typeid][char](10>COLLATEChinese_PRC_CI_ASNOTNULL,[typename][char](10>COLLATEChinese_PRC_CI_ASNOTNULL,[area][numeric](5,0>NULL,

0YujCfmUCweUts8ZQVRd[bednum][numeric](2,0>NULL,[haircondition][char](2> COLLATE Chinese_PRC_CI_ASNULL,sQsAEJkW5T[htelephone][char](2>COLLATEChinese_PRC_CI_ASNULL,[htelevision][char](2>COLLATEChinese_PRC_CI_ASNULL,[htoilet][char](2>COLLATEChinese_PRC_CI_ASNULL,[price][numeric](10,2>NULL>ON[PRIMARY]

GMsIasNXkATIrRGchYzg创建客房信息表格 roomsCREATETABLE[dbo].[rooms]([roomNO][char](10>COLLATEChinese_PRC_CI_ASNOTNULL,7EqZcWLZNX[roomtype][char](10>COLLATEChinese_PRC_CI_ASNOTNULL,lzq7IGf02E[roomposition][char](20>COLLATEChinese_PRC_CI_ASNULL,zvpgeqJ1hk[roomprice][numeric](10,2>NULL,[putup][char](2>COLLATEChinese_PRC_CI_ASNOTNULL,NrpoJac3v1[roommemo][text]COLLATEChinese_PRC_CI_ASNULLON[PRIMARY]TEXTIMAGE_ON[PRIMARY]创建订房信息表bookinCREATETABLE[dbo].[bookin]([bookno][char](14>COLLATEChinese_PRC_CI_ASNOTNULL, 1nowfTG4KI[customname][char](10> COLLATE Chinese_PRC_CI_AS NOTNULL,fjnFLDa5Zo[customID][char](18>COLLATEChinese_PRC_CI_ASNOTNULL, tfnNhnE6e5[roomno][char](10>COLLATEChinese_PRC_CI_ASNOTNULL, HbmVN777sL[indate][datetime]NULL,[discount][numeric](2,0>NULL,[inmemo][text]COLLATEChinese_PRC_CI_ASNULL,[checkdate][datetime]NULL,[ammount][numeric](10,2>NULL>ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]⒍宾馆管理信息系统具体程序实现⑴创建公用模块 Module1.bas添加公共数据操作函数,用以执行各种SQL语句.添加函数ExecuteSQL,代码如下：Public FunctionExecuteSQL(ByValSQLAsString,MsgStringAsString>_AsADODB.RecordsetV7l4jRB8Hs‘执行SQL语句,并返回记录集对象DimcnnAsADODB.ConnectionDimrstAsADODB.RecordsetDimsTokens(>AsString‘异常处理OnErrorGoToExecuteSQL_Error’‘用Split 函数产生一个包含各个子串地数组sTokens=Split(SQL>Setcnn=NEWADODB.Connection‘打开连接cnn.OpenConnectStringIf InStr(“INSERT,DELETE,UPDATE”,UCase$(sTokens(0>>>Then83lcPA59W9Cnn.ExecuteSQLMsgString=sTokens(0>&”querysuccessful ”ElseSetrst=NEWADODO.Recordsetrst.OpenTrim$(SQL>,cnn,adOpenKeyset,adLockOptimisticrst.MoveLastgetRecordCount‘返回记录集对象SetExecuteSQL=rstMsgString=”查询到”&rst.RecordCount&”条记录”EndIfExecuteSQL_Exit:Setrst=NothingSetcnn=NothingExitFunctionExecuteSQL_Error:MsgString=”查询错误:”&Err.DescriptionResumeExecuteSQL_ExitEndFunctionPublicSubEnterToTab(KeyascAsInteger>‘判断是否为回车键IfKeyasc=13Then‘Keyasc用来保存当前按键‘转换成Tab键SendKey s”{TAB}”EndIfEndsub‘添加全局变量,用来记录各个窗口地读写状态 ,代码如下：PublicflagTeditAsBoolean‘标示是否进入修改地窗体PublicflagReditAsBoolean

mZkklkzaaPPublicflagBeditAsBooleanPublicflagCeditAsBooleanPublicgintCmodeAsIntegerPublicgintTmodeAsInteger ’记录是添加还是修改状态 ,1为添加,2为修改PublicgintRmodeAsIntegerPublicgintBmodeAsIntegerPublicflagSeditAsBoolean⑵系统用户管理模块地创建用户管理模块主要实现：·用户登陆.·添加用户.·修改用户具体客房标准添加代码：PrivateSubForm_Load(> ‘载入窗体后,判断所处状态DimintCountAsIntegerDimMsgTextAsStringDimiAsIntegerIfgintTmode=1Then ‘判断是否属于添加状态Me.Caption=Me.Caption&”添加”Fori=0To3Combo1(i>.AddItem ”否”Combo1 (i>.AddItem ”是”Combo1(i>.ListIndex=0NextiElseIfgintTmode=2Then‘判断是否处于修改状态Setmrc=ExecuteSql(txtSQL,MsgText>Ifmrc.EOF=falseThenWithmrcForintCount=0To3txtItem(intCount>=.Fields(intCount>NextintCounttxtItem(4>=.Fileds(8>Fori=0To3Combo 1(i>.Additem”否”Combo1(i>.Additem”是”Combo1(i>.ListIndex=0NextiEndWithtxtItem(0>.Enabled=FalseEndIfMe.Caption=Me.caption&”修改”EndIfmblChang=FalseEndSubPrivateSubcmd_Click(>’用户输入内容完毕后,单击cmdSave按钮触发Click事件AVktR43bpwDimintCountAsIntegerDimsMegAsStringDimMsgTextAsStringForintCount=0To4‘判断是否输入内容IfTrim(txtItem(intCount>&””>=””ThenSelectCaseintCountCase0sMeg=”标准编号”Case1sMeg=”标准名称”Case2sMeg=”房间面积”Case3sMeg=”床位数量”Case4sMeg=”床位单价”EndSelectsMeg=sMeg&”不能为空!”MsgBoxsMeg,vbOkOnly+vbExclamation,”警告”txtItem(intCount>.SetFocusExitSubEndifNextintCount‘判断输入内容是否为数字ForintCount=2To4IfNotIsNumeric(Trim(txtItem(intCount>>>ThenSelectCaseintCountCase2sMeg=”房间面积”Case3sMeg=”床位数量”Case4sMeg=”床位单价”EndSelectsMeg=sMeg&”请输入数字!”MsgBoxsMeg,vbOKOnly+vbExclamation,”警告”txtItem(intCount>.SeFocusExitSubEndIfNextintCountIfgintTmode=1Then

‘判断是否有相同

ID

记录txtSQL=”select*from

roomtype

where

typeid_=’”&Trim(txtItem(0>>& ”’”ORjBnOwcEdSetmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenMsgBox “ 已 经 存 在 此 标 准 编 号 地 记录!”,vbOKOnly+vbExclamation,”警告”txtItem(0>.SetFocusExitSubEndIfMrc.CloseEndIftxtSQL=”select*fromroomtypewheretypeid<>’”&Trim(txtItem(0>>&”’andtypename=’”&Trim(txtItem(1>>&”’”2MiJTy0dTTSetmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenMsgBox”已经存在相同客房标准地记录 !”,vbOKOnly+vbExclamation,“警告”txtItem(1>.SetfocusExitSubEndIftxtSQL=”delete

from

roomtype

wheretypeid=’”&Trim(txtItem(0>>&”’”‘删除已有记录gIiSpiue7ASetmrc=ExecuteSQL(txtSQL,MsgText>txtSQL=”select*fromroottype”‘添加新记录Setmrc=ExecuteSQL(txtSQL,MsgText>mrc.AddNewForintCount=0To3mrc.Fields(intCount>=Trim(txtItem(intCount>>NextintCountForintCount=0To3mrc.Fields(intCount+4>=Trim(Combo1(intCount>>NextintCountmrc.Fields(8>=Trim(txtItem(4>>mrc.Update ‘更新数据库mrc.CloseifgintTmode=1Then ‘判断是否属于添加状态MsgBox“添加纪录成功!”,vbOKOnly+vbExclamtion,“添加记录”ForintCount=0To4txtItem(intCount>=””‘清除已经输入地内容NextintCountForintCount=0To3Combo1(intCount>.ListIndex=0NextintCountmblChange=False’判断是否打开窗口ifflagTeditThenUnloadfrmRoomtypefrmRoomtype.txtSQL= “select*fromroomtypefrmRoomtype.ShowEndIfElseIfgintTmode=2Then ‘判断是否处于修改状态UnloadMeIfflagTeditThenUnloadfrmRoomtypeEndIf

”frmRoomtype.txtSQL=”select*fromroomtype ”frmRoomtype.ShowEndIfEndSubPrivateSubtxtItem_GotFocus(IndexAsInt eger>’文本框获得焦点时触发该事件,即自动选中输入地内容,便于修改uEh0U1YfmhtxtItem(Index>.SelStart=0txtItem(Index>.SelLength=Len(txtItem(Index>>EndSubPrivateSubcmdExit_Click(>

’单击按钮

cmdExit

取消添加信息IfmblChangeAndcmdSave.EnabledThenIf

MsgBox(

“

保 存

当 前 记 录

地 变 化吗？”,vbOKCancel+vbExclamation,”警告”>=vbOKThen‘保存IAg9qLsgBXCallcmdSave_ClickEndIfEndIfUnloadMeEndSubPrivate Sub txtItem_KeyDown(Index As Integer,KeyCode AsInteger,Shift_AsInteger>‘输入完内容,单击回车键将自动进入下一个文本框,触发文本框地Keydown事件WwghWvVhPEEnterToTabKeyCodeEndSub⑶修改客房标准窗体地创建PrivateSubFrom_Load(>ShowTitleShowDataflagTedit=TrueEndSubPrivateSubShowTitle(>‘调用showTitle函数,显示表头DimiAsIntegerWithmsgList.Cols=10.TextMatrix(0,1>= ”标准编号”.TextMatrix(0,2>= ”标准名称”.TextMatrix(0,3>= ”房间面积”.TextMatrix(0,4 >=”床位数量”.TextMatrix(0,5 >=”是否有空调”.TextMatrix(0,6 >=”是否有电话”.TextMatrix(0,7 >=”是否有电视”.TextMatrix(0,8 >=”是否有卫生间”.TextMatrix(0,9 >=”房间单价”.FixedRows=1‘固定表头Fori=0To9 ‘设置各列地对齐方式.ColAlignment(i>=0Nexti.FillStyle=flexFillRepeat ‘表头项居中.Col=0.Row=0.RowSel=1.ColSel=.Cols-1.CellAlignment=4.ColWidth(0>=300 ‘设置单元大小.ColWidth(1>=1000.ColWidth(2>=2000.ColWidth(3>=2000.ColWidth(4>=1000.ColWidth(5>=1000.ColWidth(6>=1000.ColWidth(7>=1000.ColWidth(8>=1000.ColWidth(9>=1000.Row=1EndWithEndSubPrivateSubShowData(>‘调用showData函数,显示记录列表DimiAsIntegerSetmrc=ExecuteSQL(txtSQL,MsgText> ‘根据需要设置 SQL语句,显示需要地记录asfpsfpi4kWithmsgList.Rows=1DoWhileNotmrc.EOF.Rows=Rows+1Fori=1Tomrc.Fields.CountSelectCasemrc.Fields(i-1>.TypeCaseadDBDate.TextMatrix(.Rows-1,i>=Format(mrc.Fields(i- 1>&””,”yyyy-mm-dd”>ooeyYZTjj1CaseElse.TextMatrix(.Rows-1,i>=mrc.Fields(i-1>&””EndSelectNextiMrc.MoveNextLoopEndWithmrc.CloseEndSubPrivateSubmsgList_MouseUp(ButtonAsInteger,ShiftAsInteger,xAsSingle,yAsSingle>‘选择记录,然后单击鼠标右键,触发MouseUp事件BkeGuInkxIIfButton=2AndShift=0ThenPopupMenufMainFrom.menuRoomtypeEndIfEndSubPrivateSubmenuModifyroomtype_Click(>DimintCountAsIntegerIfflagTeditThen ‘判断是否打开记录窗体IffrmRoomtype.msgList.Rows>1ThenfintTmode=2 ‘设置为修改状态intCount=frmRoomtype.msgList.Row ‘记下选择记录frmRoomtype1.txtSQL=”select*fromroomtypewheretypeid’”&Trim(frmRoomtype.msgList.TextMatrix(intCount,1>>&”’”PgdO0sRlMofrmRoomtype1.ShowElseCallmenuAddroomtype_ClickEndIfElsefrmRoomtype.txtSQL=”select*fromroomtype”frmRoomtype.ShowEndIfEndSubPrivateSubFrom_Resize(> ‘窗体变化时,调整标签和表格位置,使标签处于窗体地最上方,而表格始终处于标签下方 3cdXwckm15IfMe.WindowState<>vbMinimizedAndfMainFrom.WindowState<>vbMinimizedThen‘边界处理h8c52WOngMIfMe.Scaleheight<10*lblTitle.HeightThenExitSubEndIfIf Me.ScaleWidth<lblTitle.Width+lblTitle.Width/2Thenv4bdyGiousExitSubEndIflblTitle.Top=lblTitle.Height‘控制控件地位置lblTitle.Left=(Me.Width-lblTitle.Width>/2msgList.Top=lblTitle.Top+lblTitle.Height+lblTitle.Height/2 J0bm4qMpJ9msgList.Width=Me.ScaleWidth-200msgList.Left=Me.ScaleLeft+100msgList.Height=Me.ScaleHeight-msgList.Top-200EndIfEndSub⑷删除客房标准PrivateSubmenuDeleteroomtype_Click(>DimtxtSQLAsStringDimintCountAsIntegerDimmrcAsADODB.RecordsetDimMsgTextAsStringIfflagTeditThen

‘判断是否打开记录窗体IffrmRoomtype.msgList.Rows>1ThenIf

MsgBox( “ 真 地 要 删 除 这么 ？”,vbOKCancel+vbExclamation,

条 文 件 记 录”警告”>=vbOKThenXVauA9grYPintCount=frmRoomtype.msgList.RowtxtSQL=”delete from roomtype wheretypeid’”&Trim(frmRoomtype.msgList.TextMatrix(intCount,1>>&”’”bR9C6TJscwSetmrc=ExecuteSQL(txtSQL,MsgText>UnloadfrmRoomtype ‘关闭记录窗体frmRoomtype.txtSQL=”select*fromroomtype”‘重新选择所有记录frmRoomtype.ShowEndIfEndIfEndIfEndSub⑸客房信息管理模块地创建客房信息管理模块主要实现如下功能：·添加客房信息·修改客房信息·删除客房信息·查询客房信息具体实现代码如下：PrivateSubFrom_Load(>‘载入窗体时,自动添加客房信息种类DimsSqlAsStringDimintCountAsIntegerDimMsgTextAsStringDimmrccAsADODB.RecordsetIfgintRmode=1Then‘判断是否处于添加状态Me.Caption=me.Caption&“添加”txtSQL=”selectDISTINCTtypenamefromroomtype”Setmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc.Fields(0>>mrc.MoveNextLoopcboItem(0>.ListIndex=0Else

‘显示记录内容MsgBox“请先进行客房标准设置!”,vbOKOnly+vbExclamation,“警告”cmdSave.Enabled=FalseExitSubEndIfmrc.CloseElseIfgintRmode=2Then ‘判断是否处于修改状态Setmrcc=ExecuteSQL(txtSQL,MsgText> ‘设置

SQL语句显示当前选择记录

pN9LBDdtrdIfmrcc.EOF=FalseThenWithmrcctxtItem(0>=.Fields(0>ForintCount=1To2If

Not IsNull(.Fields(intCount+1>>Then

‘判断内容是否为空txtItem(intCount>=.Fields(intCount+1>EndIfNextintCounttxtItem(3>=.Fields(5>txtSQL=”select DISTINCT

typename

fromroomtype”Setmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc.Fields(0>>mrc.MoveNextLoopcboItem(0>.ListIndex=0ElseMsgBox “请先进行客房标准设置!”,vbOKOnly+vbExclamation,“警告”cmdSave.Enabled=FalseExitSubEndIfmrc.CloseEndWithEndIfmrcc.CloseMe.Caption=Me.Caption& “修改”EndIfmblChange=FalseEndSubPrivateSubcmdSave_Click(> ‘单击保存按钮触发事件DimintCountAsIntegerDimsMegAsStringDimmrccAsADODB.RecordsetDimMsgtextAsStringForintCount=0To3IfTrim(TxtItem(intCount>&

””>=””

ThenSelectCaseintCountCase0sMeg= “客房编号”Case1sMeg= “客房位置”EndSelectsMeg=sMeg& “不能为空！”MsgBoxsMeg,vbOKOnly+vbExclamation, “警告”txtItem(intCount>.SetFocusExitSubEndIfNextintCountIfgintRmode=1Then ‘判断是否有重复记录txtSQL=”select * from rooms where

roomNO=‘”&Trim(txtItem(0>>&

”’”DJ8T7nHuGTSetmrc=ExecuteSQL(txtSQL,msgText>Ifmrc.EOF=FalseThenMsgBox “ 已 经 存

在 此 客 房 编 号 地 记录！”,vbOKOnly+vbExclamation,“警告”txtItem(0>.SetFocusExitSubEndIfmrc.CloseEndIfIfgintRmode=2Then

‘判断是否处于修改状态txtSQL=”delete

from

rooms

where

roomNO=‘”&Trim(txtItem(0>>&

”’”QF81D7bvUASetmrc=ExecuteSQL(txtSQL,MsgText>EndIftxtSQL=”select*fromrooms”‘再加入新记录Setmrc=ExecuteSQL(txtSQL,MsgText>mrc.AddNew ‘为数据库对象添加记录mrc.Fields(0>=Trim(txtItem(0>>mrc.Fields(1>=Trim(txtItem(0>>ForintCount=1To2IfTrim(txtItem(intCount>&””>=””Thenmrc.Fields(intCount+1>=NullElsemrc.Fields(intCount+1>=Trim(txtItem(intCount>>EndIfNextintCountmrc.Fields(4>= ””mrc.Fields(5>=Trim(txtItem(3>>mrc.Updatemrc.CloseIfgintRmode=1ThenForintCount=0To3txtItem(intCount>= ””NextintCountmblChange=FalseIfflagReditThenUnloadfrmRoomfrmRoom.txtSQL=”select*fromrooms”‘重新显示数据frmRoom.ShowEndIfElseIfgintRmode=2ThenUnloadMeIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*fromrooms ”frmRoom.ShowEndIfEndSub·修改客房信息Private

Sub

menuModifyroom_Click(>

‘选择修改房间信息, 触menuModifyroom地Click 事件,程序判断为修改状态后,首先删除原有记录,然后把新内容加入到数据库中 4B7a9QFw9hDimintCountAsIntegerIfflagReditThen ‘判断记录窗体是否打开IffrmRoom.msgList.Rows>1Then ‘判断客房信息列表内容是否为空gintRmode=2‘设置为修改状态intCount=frmRoom.msgList.Row ‘纪录当前记录位置frmRoom1.txtSQL=”select*from rooms whereroomNO=’”&Trim(frmRoom.msgList.TextMatrix(intCount,1>>& ”’”ix6iFA8xoXfrmRoom1.ShowElseCallmenuAddroom_Click ‘添加记录EndIfElsefrmRoom.txtSQL=”select*fromrooms ”frmRoom.ShowEndIfEndSub·删除客房信息PrivateSubmenuDeleteroom_Click(>DimtxtSQLAsStringDimintCountAsIntegerDimmrcAsADODB.RecordsetDimMsgTextAsStringIfflagTeditThenIffrmRoom.msgList.Rows>1Then‘判断客房信息列表内容是否为空IfMsgBox(“真地要删除这条文件记录么？”,vbOKCancel+vbExclamation,“警告”>=vbOKThenwt6qbkCyDEintCount=frmRoom.msgList.Row ‘记载当前记录位置txtSQL=”delete

from

rooms

where

roomNO=‘”&Trim(frmRoom.msgList.TextMatrix(intCount,1>&

”’”Kp5zH46zRkSetmrc=ExecuteSQL(txtSQL,MsgText>

‘执行删除操作UnloadfrmRoomfrmRoom.txtSQL=”select*fromrooms”frmRoom.ShowEndIfEndIfEndIfEndSub·查询客房信息PrivateSubFrom_Load(>‘载入窗体时,将自动加入所有记录地入库时间DimiAsIntegerDimjAsIntegerDimsSqlAsStringDimtxtSQLAsStringDimMsgTextAsStringDimmrcAsADODB.RecordsettxtSQL=”select DISTINCTtypenamefrom roomtype”‘初始化客房种类Yl4HdOAA61Setmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFCombo1.AddItemTrim(mrc.Fields(0>>Mrc.MoveNextLoopCombo1.ListIndex=0ElseMsgBox “请先进行客房标准设置 !”,vbOKOnly+vbExclamation,”警告”ExitSubEndIfmrc.CloseEndSubPrivateSubcmdOK_Click(>‘设置完查询内容和方式后,单击cmdOk按钮查询DimsQSqlAsStringIfchkItem(0>.Value=vbCheckedThensQSql=”roomNO=’”&Trim(txtItem(0>&””>&”’”EndIfIfchkItem(1>.Value=vbCheckedThenIfTrim(sQSql& ””>=””ThensQSql=”roomtype=’”&Trim(Combo1&””>&”’”ElsesQSql=sQSql&”androomtype=’”&Trim(Combo1&””>&”’”EndIfEndIfIfTrim(sQSql>= ””ThenMsgBox“请设置查询条件！”,vbOKOnly+vbExclamation,“警告”ExitSubElseIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*fromroomswhere”&sQSqlfrmRoom.ShowEndIfMe.HideEndSub⑹订房信息管理模块地创建订房信息管理模块主要实现如下功能：·查询剩余客房信息·添加订房信息·修改订房信息·删除订房信息·查询订房库信息具体实现代码如下：PrivateSubFrom_Load(>‘载入窗体时,程序将自动加入客房种类和客房单价DimtxtSQLAsStringDimMsgTextAsStringDimmrcAsADODB.RecordsettxtSQL=”selectdistincttypenamefromroomtype ”‘选择所有客房种类ch4PJx4BlISetmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFCombo1(0>.AddItemmrc.Fields(0>mrc.MoveNextLoopEndIfmrc.ClosetxtSQL=”selectdistinctpricefromroomtypeSetmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFCombo1(1>.AddItemmrc.Fields(0>Combo1(2>.AddItemmrc.Fields(0>mrc.MoveNext

”LoopEndIfmrc.CloseEndSubPrivateSubcmdOK_Click(>DimsQSqlAsStringIfchkItem(0>.Value=vbCheckedThensQSql=”roomtype=’”&Trim(Combo1(0>&””>&”’”EndifIfchkItem(1>.Value=vbCheckedThenIfTrim(sQSql& ””>=””ThenIf Trim(Combo1(1>&””><>””AndTrim(Combo1(2>&””><>””qd3YfhxCzoThensQSql=”roomprice<=’”&Trim(Combo1(1>&””>&Androomprice >=’”&Trim(Combo1(2>&””>”’”E836L11DO5ElseIf Trim(Combo1(1>&””>=””AndTrim(Combo1(2>&””><>””ThenS42ehLvE3MsQSql=”roomprice>=’”&Trim(Combo1(2>&””>&”’”ElseIf Trim(Combo1(1>&””><>””AndTrim(Combo1(2>&””=””Then501nNvZFissQSql=”roomprice<=’”&Trim(Combo1(1>&””>&”’”EndIfElseIf Trim(Combo1(1>&””><>””AndTrim(Combo1(2>&””><>””ThenjW1viftGw9sQSql=sQSql&”androomprice>=’”&Trim(Combo1(1>&””>&”’androomprice >=’”&Trim(Combo1(2>&””>&”’”xS0DOYWHLPElseIf Trim(Combo1(1>&””>=””AndTrim(Combo1(2>&””><>””ThenLOZMkIqI0wsQSql=sQSql&”androomprice >=’”&Trim(Combo1(2>&””>&”’”ZKZUQsUJedElseIf Trim(Combo1(1>&””><>””AndTrim(Combo1(2>&””>=””ThendGY2mcoKtTsQSql=sQSql&”androomprice<=’”&Trim(Combo1(1>&””>&”’”rCYbSWRLIAEndIfEndIfIfTrim(sQSql>= ””ThenMsgBox “请设置查询条件!”,vbOKOnly+vbExclamation,“警告”ExitSubElseIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*from rooms where”&sQSql&”andputup”<>’y’”FyXjoFlMWhfrmRoom.ShowflagSedit=TrueEndIfMe.HideEndSubPrivateSubFrom_Load(> ‘添加订房信息,载入窗体时把所有未预定地客房信息加入其中 TuWrUpPObXDimsSqlAsStringDimintCountAsIntegerDimMsgTextAsStringIfflagSeditThen ‘判断打开方式Setmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenForintCount=0To3cboItem(intCount>.AddItemmrc.Fields(intCount>cboItem(intCount>.ListIndex=0NextintCounttxtItem(5>=mrc.Fields(5>EndIfmrc.ClosetxtNo=GetRkno(>gintBmode=1ElseIfgintBmode=1ThenMe.Caption=Me.Caption& “添加”txtSQL=”select DISTINCT roomNO from rooms whereputup<>’y’”7qWAq9jPqESetmrc=ExecuteSQL(txtSQL.MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc.Fields(0>>mrc.MoveNextLoopElseMsgBox “请先进行客房登记!”,vbOKOnly+vbExclamation,“警告”cmdSave.Enabled=FalseExitSubEndIfmrc.ClosetxtNo=GetRknoElseIfgintBmode=2ThenSetmrc.ExecuteSQL(txtSQL,MsgText>Ifmrc.EOFThenWithmrctxtNO=!booknoForintCount=0To1IfNotIsNull(.Fields(intCount+1>>ThentxtItem(intCount>=.Fields(intCount+1>EndIfNextintCountcboItem(0>.AddItem!roomnocboItem(0>.ListIndex=0ForintCount=2To4IfNotIsNull(.Fields(intCount+2>>ThentxtItem(intCount>=.Fields(intCount+2>EndIfNextintCountEndWithEndIfmrc.CloseMe.Caption=Me.Caption& “修改”txtSQL=”select*fromroomswhereroomNO=’”&Trim(cboItem(0>>&”’”llVIWTNQFkSetmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenForintCount=1To3cboItem(intCount>.AddItemmrc.Fields(intCount>cboItem(intCount>.ListIndex=0NextintCounttxtItem(5>=mrc.Fields(5>EndIfmrc.CloseEndIfEndIfmblChange=FalseEndSubPrivateSubcmdSave_Click(>‘添加记录到数据库中DimintCountAsIntegerDimsMegAsStringDimmrccAsADODB.RecordsetDimMsgTextAsStringForintCount=0To3 ‘判断输入内容是否为空IfTrim(txtItem(intCount>&

””>=””

ThenSelectCaseintCountCase0sMeg=

“顾客姓名”Case1sMeg=

“身份证号码”Case2sMeg=

“折扣”Case3sMeg=

“入住时间”EndSelectsMeg=sMeg& “不能为空!”MsgBoxsMeg,vbOKOnly+vbExclamation,“警告”txtItem(intCount>.SetFocusExitSubEndIfNextintCountForintCount=0To3IfTrim(vboItem(intCount>& ””>=””ThenSelectCaseintCountCase0sMeg= “客房编号”Case1sMeg= “客房种类”Case2sMeg= “客房位置”Case3sMeg= “客房单价”EndSelectsMeg=sMeg& “不能为空！”MsgBoxsMeg,vbOKOnly+vbExclamation, “警告”cboItem(intCount>.SetFocusExitSubEndIfNextintCountIfIsDate(txtItem(2>>ThentxtItem(2>=Format(txtItem(2>, ”yyyy-mm-dd”>ElseMsgBox

“ 入 库

时 间 应 输

入 日 期

(yyyy-mm-dd>!”,vbOKOnly+vbExclamation,

” 警告”yhUQsDgRT1txtItem(2>.SetFocusExitSubEndIfForintCount=1To3Step2IfNotIsNumeric(txtItem(intCount>>ThenMsgBox “请输入数字”,vbOKOnly+vbExclamation,“警告”txtItem(intCount>.SetFocusExitSubEndIfNextintCountIfgintBmode=2ThentxtS QL=”delete from bookingbookno=’”&Trim(txtNo>&”’”MdUZYnKS8ISetmrcc=ExecuteSQL(txtSQL,MsgText>EndIftxtSQL=”select*frombooking ”Setmrcc=ExecuteSQL(txtSQL,MsgText>

wheremrcc.AddNewmrcc.Fields(0>=txtNOForintCount=0To1mrcc.Fields(intCount+1>=txtItem(intText>NextintCountmrcc.Fields(3>=cboItem(0>ForintCount=2To4mrcc.Fields(intCount+2>=txtItem(intCount>NextintCountmrcc.Fields(8>=0mrcc.Updatemrcc.ClosetxtSQL=”select*from roomswhereroomNO=’”&Trim(vboItem(0>>&”’”09T7t6eTnoSetmrcc=ExecuteSQL(txtSQL,MsgText>IfNotmrcc.EOfThenmrcc!putup=”y”EndIfmrcc.Updatemrcc.CloseIfgintBmode=1ThenMsgBox “添加订房信息成功!”,vbOKOnly+vbExclamation,“添加订房消息”UnloadMeIfflagBeditThenUnloadfrmBookinEndIffrmBookin.txtSQL=”selectbookno,customname,customID,roomno,indate,discount,inmemo from booking whereamount=’0’”e5TfZQIUB5frmBookin.ShowElseMsgBox “修改订房信息成功!”,vbOKOnly+vbExclamation,“修改订房信息”UnloadMeIfflagBeditThenUnloadfrmBookinEndIffrmBookin.txtSQL=”selectbookno,customname,customID,roomno,indate,discount,inmemo from booking where amount=’0’”s1SovAcVQMfrmBookin.ShowEndIfEndSub⑺结算信息管理模块地创建结算信息管理模块主要实现如下功能：·添加结算信息·修改结算信息·查询结算信息具体实现代码如下：PrivateSubFrom_Load(>‘窗体载入时,自动加入所有未结算客房信息DimsSqlAsStringDimintCountAsIntegerDimMsgTextAsStringIfgintCmode=1Then‘判断是否处于添加状态Me.Caption=Me.Caption&“添加”txtSQL=”select DISTINCT roomno from booking whereamount=’0’”‘初始化客房编号,选择所有未结算客房编号GXRw1kFW5sSetmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc!roomno>mrc.MoveNextLoopElseMsgBox “没有顾客入住!”,vbOKOnly+vbExclamation,cmdSave.Enabled=FalseExitSubEndIfmrc.CloseElseIfgintCmode=2Then ‘判断是否处于修改状态Setmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenWithmrctxtNo=mrc.Fields(0>ForintCount=0To1txtItem(intCount>=.Fields(intCount+1>NextintCountcboItem(0>.AddItem.fields(3>cboItem(0>.ListIndex=0ForintCount=2To3IfNotIsNull(.Fields(intCount+2>>ThentxtItem(intCount>=.Fields(intCount+2>EndIfNextintCounttxtItem(5>=.Fields(6>txtItem(4>=.Fields(7>EndWithEndIfmrc.ClosetxtSQL=”select*from rooms

“警告”whereroomNO=’”&cboItem(0>&”’”UTREx49Xj9Setmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenWithmrcForintCount=1To3cboItem(intCount>.AddItem.Fields(intCount>cboItem(intCount>.ListIndex=0NextintCountEndWithEndIfmrc.CloseForintCount=0To3txtItem(intCount>.Enabled=FalseNextintCountMe.Caption=Me.Caption& “修改”EndIfmblChange=FalseEndSubPrivateSubcboItem_Click(IndexAsInteger>‘选择不同客房,触发cboItem下拉式文本框地click事件,显示相应地顾客信息8PQN3NDYyPDimsSqlAsStringDimMsgTextStringDimmrccAsADODB.RecordsetDimintCountAsIntegerIfgintCmode=1Then‘判断是否处于添加状态,否则将退出IfIndex=0ThencboItem(1>.Enabled=TruecboItem(2>.Enabled=TruecboItem(3>.Enabled=TruecboItem(1>.Clear ‘清除原有内容cboItem(2>.ClearcboItem(3>.CleartxtSQL=”selectroomNO,roomtype,roomposition,roompricefromwhereroomNo=’”&Trim(cboItem(0>>&”’”‘显示有关客房地具体信息mLPVzx7ZNwSetmrcc=ExecuteSQL(txtSQL,MsgText>IfNotmrcc.EOFThencboItem(1>.AddItemmrcc!roomtypecboItem(2>.AddItemmrcc!roompositioncboItem(3>.AddItemmrcc!roompricecboItem(1>.Enabled=FalsecboItem(2>.Enabled=FalsecboItem(3>.Enabled=FalsecboItem(1>.ListIndex=0cboItem(2>.ListIndex=0cboItem(3>.ListIndex=0cmdSave.Enabled=TrueEl