數(shù)據(jù)庫課程設(shè)計酒店管理信息系統(tǒng)

酒店管理信息系統(tǒng)系統(tǒng)平臺：Windows2000數(shù)據(jù)庫服務(wù)器：MicrosoftSQLServer2000開發(fā)工具：VisualBasic6.0系統(tǒng)開發(fā)人員：⒈編寫目地酒店在正常地運(yùn)營中需要對客房資源、顧客信息、結(jié)算信息進(jìn)行管理,利用賓館管理信息系統(tǒng)及時了解各個環(huán)節(jié)中信息地變更,有利于提高管理效率.b5E2RGbCAP⒉系統(tǒng)功能分析系統(tǒng)開發(fā)地總體任務(wù)是實現(xiàn)賓館各種信息地系統(tǒng)化、規(guī)范化和自動化 .主要完成功能：●有關(guān)客房標(biāo)準(zhǔn)地制定、標(biāo)準(zhǔn)信息地輸入,包括標(biāo)準(zhǔn)編號、標(biāo)準(zhǔn)名稱、房間面積、床位數(shù)量、住房單價、是否有空調(diào)、電視機(jī)、電話以及單獨(dú)衛(wèi)生間等.p1EanqFDPw●客房標(biāo)準(zhǔn)信息地修改、查詢等 .●客房基本信息地輸入,包括客房編號、客房類型、客房位置、客房單價、備注等.●客房基本信息地查詢、修改,包括客房編號、客房類型、客房位置、客房單價、備注等.●剩余客房信息地查詢等.●訂房信息地輸入,包括客房編號、客房種類、客房位置、客房單價、顧客姓名、顧客身份證號碼、入住日期、折扣、備注信息等.DXDiTa9E3d●訂房信息地修改和查詢 ,包括客房編號、客房種類、客房位置、客房單價、顧客姓名、顧客身份證號碼、入住日期、折扣、備注信息等.RTCrpUDGiT●結(jié)算信息地輸入,包括客房編號、客房種類、位置、客房單價、顧客姓名、顧客身份證號碼、入住日期、折扣、結(jié)算日期、備注信息等.5PCzVD7HxA●結(jié)算信息地修改和查詢,包括客房編號、客房種類、客房位置、客房單價、顧客姓名、顧客身份證號碼、入住日期、折扣、結(jié)日期、備注信息等.jLBHrnAILg⒊系統(tǒng)功能模塊設(shè)計按結(jié)構(gòu)化程序設(shè)計思想,分析得出如下系統(tǒng)功能模塊圖賓館管理信息系統(tǒng)結(jié)訂客算房房系信信信統(tǒng)息息息管管管管理理理理剩結(jié)結(jié)結(jié)訂訂訂余設(shè)設(shè)算算算房房房客置置密用信信信信信信房客客碼戶息息息息息息信房房管管查修添查修添息信標(biāo)理理詢改加詢改加查息準(zhǔn)詢客客客客客房房房房房信信信標(biāo)標(biāo)息息息準(zhǔn)準(zhǔn)查修添修添詢改加改加xHAQX74J0X圖Ⅰ系統(tǒng)功能模塊圖⒋數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫設(shè)計步驟：● 數(shù)據(jù)庫需求分析● 數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計● 數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計①數(shù)據(jù)庫需求分析分析調(diào)查有關(guān)賓館管理信息需求地基礎(chǔ)上得本系統(tǒng)所處理地數(shù)據(jù)流程客房標(biāo)準(zhǔn)設(shè)置客房登記基本信息輸入基本信息錄入客房信息管理顧客登記訂房信息管理訂房信息管理 客房信息返回結(jié)算信息輸入結(jié)算信息管理LDAYtRyKfE圖Ⅱ賓館管理信息系統(tǒng)數(shù)據(jù)流程圖針對一般賓館管理信息系統(tǒng)地需求 ,通過對賓館管理過程地內(nèi)容和流程分析 ,設(shè)計如下面所示地數(shù)據(jù)項和數(shù)據(jù)結(jié)構(gòu) ：Zzz6ZB2Ltk● 客房標(biāo)準(zhǔn)信息,包括地數(shù)據(jù)項有：標(biāo)準(zhǔn)編號、標(biāo)準(zhǔn)名稱、房間面積、床位數(shù)量、住房單價、是否有空調(diào)、電視、電話、單獨(dú)衛(wèi)生間等.dvzfvkwMI1●客房信息,包括地數(shù)據(jù)項有：客房編號、客房種類、客房位置、客房單價、備注等.● 訂房信息,包括地數(shù)據(jù)項有：訂房編號、客房編號、客房種類、客房位置、客房備注信息、顧客姓名、顧客身份證號碼、入住時間、折扣、備注等.rqyn14ZNXI● 結(jié)算信息,包括地數(shù)據(jù)項有：訂房編號、客房編號、客房種類、客房位置、客房備注信息、顧客姓名、顧客身份證號碼、入住時間、折扣、結(jié)算時間、備注等.EmxvxOtOco②數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計本系統(tǒng)根據(jù)以上地設(shè)計規(guī)劃出地實體有：客房標(biāo)準(zhǔn)信息實體、客房信息實體、訂房信息實體、結(jié)算信息實體 .各個實體具體地 描述Ｅ-Ｒ圖如下：SixE2yXPq5客房標(biāo)準(zhǔn)信息實體標(biāo)準(zhǔn)編號

客房單價標(biāo)準(zhǔn)名稱 床位數(shù)量客房標(biāo)準(zhǔn)信息實體Ｅ-Ｒ圖客房信息實體客房標(biāo)準(zhǔn) 客房單價客房編號 客房位置客房信息實體Ｅ-Ｒ圖訂房信息實體客房信息 折扣顧客信息入住時間訂房信息實體Ｅ-Ｒ圖結(jié)算信息實體客房信息 結(jié)算金額顧客信息 結(jié)算時間結(jié)算信息實體Ｅ-Ｒ圖客房標(biāo)準(zhǔn)信息客房登記客房信息管理訂房 結(jié)算信息管理訂房信息管理 客房結(jié)算實體之間關(guān)系Ｅ-Ｒ圖③數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計首先將賓館管理信息系統(tǒng)地數(shù)據(jù)庫概念結(jié)構(gòu)轉(zhuǎn)化為SQL2000數(shù)據(jù)庫系統(tǒng)所支持地實際數(shù)據(jù)模型,即：數(shù)據(jù)庫地邏輯結(jié)構(gòu).其各個表格地設(shè)計結(jié)果如下6ewMyirQFL列名數(shù)據(jù)類型可否為空說明TypeidVarcharNOTNULL標(biāo)準(zhǔn)編號TypenameVarcharNOTNULL標(biāo)準(zhǔn)名稱AreaNumericNULL房間面積BednumNumericNULL床位數(shù)量HairconditionVarcharNULL是否有空調(diào)HtelephoneVarcharNULL是否有電話HtelevisionVarcharNULL是否有電視HtoiletVarcharNULL是否有單獨(dú)衛(wèi)生間PriceNumericNULL單價Roomtype客房標(biāo)準(zhǔn)信息表列名數(shù)據(jù)類型可否為空說明roomNOVarcharNOTNULL客房編號RoomtypeVarcharNOTNULL客房種類RoompositionVarcharNULL客房位置RoompriceNumericNULL單價PutupVarcharNOTNULL是否被預(yù)定RoommemoTextNULL備注Rooms客房信息表列名數(shù)據(jù)類型可否為空說明BooknoVarcharNOTNULL訂房編號CustomnameVarcharNOTNULL顧客姓名CustomIDVarcharNOTNULL身份證號碼RoomnoVarcharNOTNULL客房編號IndateDatetimeNULL入住日期DiscountNumericNULL折扣InmenoTextNULL備注CheckdateDatetimeNULL結(jié)算日期AmountNumericNULL金額Bookin訂房信息表⒌數(shù)據(jù)庫結(jié)構(gòu)地實現(xiàn)利用SQL2000數(shù)據(jù)庫系統(tǒng)中地查詢分析實現(xiàn)數(shù)據(jù)庫地邏輯結(jié)構(gòu),其表格如下：創(chuàng)建系統(tǒng)用戶表格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]創(chuàng)建客房標(biāo)準(zhǔn)信息表格 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創(chuàng)建客房信息表格 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]創(chuàng)建訂房信息表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]⒍賓館管理信息系統(tǒng)具體程序?qū)崿F(xiàn)⑴創(chuàng)建公用模塊 Module1.bas添加公共數(shù)據(jù)操作函數(shù),用以執(zhí)行各種SQL語句.添加函數(shù)ExecuteSQL,代碼如下：Public FunctionExecuteSQL(ByValSQLAsString,MsgStringAsString>_AsADODB.RecordsetV7l4jRB8Hs‘執(zhí)行SQL語句,并返回記錄集對象DimcnnAsADODB.ConnectionDimrstAsADODB.RecordsetDimsTokens(>AsString‘異常處理OnErrorGoToExecuteSQL_Error’‘用Split 函數(shù)產(chǎn)生一個包含各個子串地數(shù)組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用來保存當(dāng)前按鍵‘轉(zhuǎn)換成Tab鍵SendKey s”{TAB}”EndIfEndsub‘添加全局變量,用來記錄各個窗口地讀寫狀態(tài) ,代碼如下：PublicflagTeditAsBoolean‘標(biāo)示是否進(jìn)入修改地窗體PublicflagReditAsBoolean

mZkklkzaaPPublicflagBeditAsBooleanPublicflagCeditAsBooleanPublicgintCmodeAsIntegerPublicgintTmodeAsInteger ’記錄是添加還是修改狀態(tài) ,1為添加,2為修改PublicgintRmodeAsIntegerPublicgintBmodeAsIntegerPublicflagSeditAsBoolean⑵系統(tǒng)用戶管理模塊地創(chuàng)建用戶管理模塊主要實現(xiàn)：·用戶登陸.·添加用戶.·修改用戶具體客房標(biāo)準(zhǔn)添加代碼：PrivateSubForm_Load(> ‘載入窗體后,判斷所處狀態(tài)DimintCountAsIntegerDimMsgTextAsStringDimiAsIntegerIfgintTmode=1Then ‘判斷是否屬于添加狀態(tài)Me.Caption=Me.Caption&”添加”Fori=0To3Combo1(i>.AddItem ”否”Combo1 (i>.AddItem ”是”Combo1(i>.ListIndex=0NextiElseIfgintTmode=2Then‘判斷是否處于修改狀態(tài)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(>’用戶輸入內(nèi)容完畢后,單擊cmdSave按鈕觸發(fā)Click事件AVktR43bpwDimintCountAsIntegerDimsMegAsStringDimMsgTextAsStringForintCount=0To4‘判斷是否輸入內(nèi)容IfTrim(txtItem(intCount>&””>=””ThenSelectCaseintCountCase0sMeg=”標(biāo)準(zhǔn)編號”Case1sMeg=”標(biāo)準(zhǔn)名稱”Case2sMeg=”房間面積”Case3sMeg=”床位數(shù)量”Case4sMeg=”床位單價”EndSelectsMeg=sMeg&”不能為空!”MsgBoxsMeg,vbOkOnly+vbExclamation,”警告”txtItem(intCount>.SetFocusExitSubEndifNextintCount‘判斷輸入內(nèi)容是否為數(shù)字ForintCount=2To4IfNotIsNumeric(Trim(txtItem(intCount>>>ThenSelectCaseintCountCase2sMeg=”房間面積”Case3sMeg=”床位數(shù)量”Case4sMeg=”床位單價”EndSelectsMeg=sMeg&”請輸入數(shù)字!”MsgBoxsMeg,vbOKOnly+vbExclamation,”警告”txtItem(intCount>.SeFocusExitSubEndIfNextintCountIfgintTmode=1Then

‘判斷是否有相同

ID

記錄txtSQL=”select*from

roomtype

where

typeid_=’”&Trim(txtItem(0>>& ”’”O(jiān)RjBnOwcEdSetmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenMsgBox “ 已 經(jīng) 存 在 此 標(biāo) 準(zhǔn) 編 號 地 記錄!”,vbOKOnly+vbExclamation,”警告”txtItem(0>.SetFocusExitSubEndIfMrc.CloseEndIftxtSQL=”select*fromroomtypewheretypeid<>’”&Trim(txtItem(0>>&”’andtypename=’”&Trim(txtItem(1>>&”’”2MiJTy0dTTSetmrc=ExecuteSQL(txtSQL,MsgText>Ifmrc.EOF=FalseThenMsgBox”已經(jīng)存在相同客房標(biāo)準(zhǔn)地記錄 !”,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 ‘更新數(shù)據(jù)庫mrc.CloseifgintTmode=1Then ‘判斷是否屬于添加狀態(tài)MsgBox“添加紀(jì)錄成功!”,vbOKOnly+vbExclamtion,“添加記錄”ForintCount=0To4txtItem(intCount>=””‘清除已經(jīng)輸入地內(nèi)容NextintCountForintCount=0To3Combo1(intCount>.ListIndex=0NextintCountmblChange=False’判斷是否打開窗口ifflagTeditThenUnloadfrmRoomtypefrmRoomtype.txtSQL= “select*fromroomtypefrmRoomtype.ShowEndIfElseIfgintTmode=2Then ‘判斷是否處于修改狀態(tài)UnloadMeIfflagTeditThenUnloadfrmRoomtypeEndIf

”frmRoomtype.txtSQL=”select*fromroomtype ”frmRoomtype.ShowEndIfEndSubPrivateSubtxtItem_GotFocus(IndexAsInt eger>’文本框獲得焦點時觸發(fā)該事件,即自動選中輸入地內(nèi)容,便于修改uEh0U1YfmhtxtItem(Index>.SelStart=0txtItem(Index>.SelLength=Len(txtItem(Index>>EndSubPrivateSubcmdExit_Click(>

’單擊按鈕

cmdExit

取消添加信息IfmblChangeAndcmdSave.EnabledThenIf

MsgBox(

“

保 存

當(dāng) 前 記 錄

地 變 化嗎？”,vbOKCancel+vbExclamation,”警告”>=vbOKThen‘保存IAg9qLsgBXCallcmdSave_ClickEndIfEndIfUnloadMeEndSubPrivate Sub txtItem_KeyDown(Index As Integer,KeyCode AsInteger,Shift_AsInteger>‘輸入完內(nèi)容,單擊回車鍵將自動進(jìn)入下一個文本框,觸發(fā)文本框地Keydown事件WwghWvVhPEEnterToTabKeyCodeEndSub⑶修改客房標(biāo)準(zhǔn)窗體地創(chuàng)建PrivateSubFrom_Load(>ShowTitleShowDataflagTedit=TrueEndSubPrivateSubShowTitle(>‘調(diào)用showTitle函數(shù),顯示表頭DimiAsIntegerWithmsgList.Cols=10.TextMatrix(0,1>= ”標(biāo)準(zhǔn)編號”.TextMatrix(0,2>= ”標(biāo)準(zhǔn)名稱”.TextMatrix(0,3>= ”房間面積”.TextMatrix(0,4 >=”床位數(shù)量”.TextMatrix(0,5 >=”是否有空調(diào)”.TextMatrix(0,6 >=”是否有電話”.TextMatrix(0,7 >=”是否有電視”.TextMatrix(0,8 >=”是否有衛(wèi)生間”.TextMatrix(0,9 >=”房間單價”.FixedRows=1‘固定表頭Fori=0To9 ‘設(shè)置各列地對齊方式.ColAlignment(i>=0Nexti.FillStyle=flexFillRepeat ‘表頭項居中.Col=0.Row=0.RowSel=1.ColSel=.Cols-1.CellAlignment=4.ColWidth(0>=300 ‘設(shè)置單元大小.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(>‘調(diào)用showData函數(shù),顯示記錄列表DimiAsIntegerSetmrc=ExecuteSQL(txtSQL,MsgText> ‘根據(jù)需要設(shè)置 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>‘選擇記錄,然后單擊鼠標(biāo)右鍵,觸發(fā)MouseUp事件BkeGuInkxIIfButton=2AndShift=0ThenPopupMenufMainFrom.menuRoomtypeEndIfEndSubPrivateSubmenuModifyroomtype_Click(>DimintCountAsIntegerIfflagTeditThen ‘判斷是否打開記錄窗體IffrmRoomtype.msgList.Rows>1ThenfintTmode=2 ‘設(shè)置為修改狀態(tài)intCount=frmRoomtype.msgList.Row ‘記下選擇記錄frmRoomtype1.txtSQL=”select*fromroomtypewheretypeid’”&Trim(frmRoomtype.msgList.TextMatrix(intCount,1>>&”’”PgdO0sRlMofrmRoomtype1.ShowElseCallmenuAddroomtype_ClickEndIfElsefrmRoomtype.txtSQL=”select*fromroomtype”frmRoomtype.ShowEndIfEndSubPrivateSubFrom_Resize(> ‘窗體變化時,調(diào)整標(biāo)簽和表格位置,使標(biāo)簽處于窗體地最上方,而表格始終處于標(biāo)簽下方 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⑷刪除客房標(biāo)準(zhǔn)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 ‘關(guān)閉記錄窗體frmRoomtype.txtSQL=”select*fromroomtype”‘重新選擇所有記錄frmRoomtype.ShowEndIfEndIfEndIfEndSub⑸客房信息管理模塊地創(chuàng)建客房信息管理模塊主要實現(xiàn)如下功能：·添加客房信息·修改客房信息·刪除客房信息·查詢客房信息具體實現(xiàn)代碼如下：PrivateSubFrom_Load(>‘載入窗體時,自動添加客房信息種類DimsSqlAsStringDimintCountAsIntegerDimMsgTextAsStringDimmrccAsADODB.RecordsetIfgintRmode=1Then‘判斷是否處于添加狀態(tài)Me.Caption=me.Caption&“添加”txtSQL=”selectDISTINCTtypenamefromroomtype”Setmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc.Fields(0>>mrc.MoveNextLoopcboItem(0>.ListIndex=0Else

‘顯示記錄內(nèi)容MsgBox“請先進(jìn)行客房標(biāo)準(zhǔn)設(shè)置!”,vbOKOnly+vbExclamation,“警告”cmdSave.Enabled=FalseExitSubEndIfmrc.CloseElseIfgintRmode=2Then ‘判斷是否處于修改狀態(tài)Setmrcc=ExecuteSQL(txtSQL,MsgText> ‘設(shè)置

SQL語句顯示當(dāng)前選擇記錄

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

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

‘判斷內(nèi)容是否為空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 “請先進(jìn)行客房標(biāo)準(zhǔn)設(shè)置!”,vbOKOnly+vbExclamation,“警告”cmdSave.Enabled=FalseExitSubEndIfmrc.CloseEndWithEndIfmrcc.CloseMe.Caption=Me.Caption& “修改”EndIfmblChange=FalseEndSubPrivateSubcmdSave_Click(> ‘單擊保存按鈕觸發(fā)事件DimintCountAsIntegerDimsMegAsStringDimmrccAsADODB.RecordsetDimMsgtextAsStringForintCount=0To3IfTrim(TxtItem(intCount>&

””>=””

ThenSelectCaseintCountCase0sMeg= “客房編號”Case1sMeg= “客房位置”EndSelectsMeg=sMeg& “不能為空！”MsgBoxsMeg,vbOKOnly+vbExclamation, “警告”txtItem(intCount>.SetFocusExitSubEndIfNextintCountIfgintRmode=1Then ‘判斷是否有重復(fù)記錄txtSQL=”select * from rooms where

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

”’”DJ8T7nHuGTSetmrc=ExecuteSQL(txtSQL,msgText>Ifmrc.EOF=FalseThenMsgBox “ 已 經(jīng) 存

在 此 客 房 編 號 地 記錄！”,vbOKOnly+vbExclamation,“警告”txtItem(0>.SetFocusExitSubEndIfmrc.CloseEndIfIfgintRmode=2Then

‘判斷是否處于修改狀態(tài)txtSQL=”delete

from

rooms

where

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

”’”QF81D7bvUASetmrc=ExecuteSQL(txtSQL,MsgText>EndIftxtSQL=”select*fromrooms”‘再加入新記錄Setmrc=ExecuteSQL(txtSQL,MsgText>mrc.AddNew ‘為數(shù)據(jù)庫對象添加記錄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”‘重新顯示數(shù)據(jù)frmRoom.ShowEndIfElseIfgintRmode=2ThenUnloadMeIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*fromrooms ”frmRoom.ShowEndIfEndSub·修改客房信息Private

Sub

menuModifyroom_Click(>

‘選擇修改房間信息, 觸menuModifyroom地Click 事件,程序判斷為修改狀態(tài)后,首先刪除原有記錄,然后把新內(nèi)容加入到數(shù)據(jù)庫中 4B7a9QFw9hDimintCountAsIntegerIfflagReditThen ‘判斷記錄窗體是否打開IffrmRoom.msgList.Rows>1Then ‘判斷客房信息列表內(nèi)容是否為空gintRmode=2‘設(shè)置為修改狀態(tài)intCount=frmRoom.msgList.Row ‘紀(jì)錄當(dāng)前記錄位置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‘判斷客房信息列表內(nèi)容是否為空IfMsgBox(“真地要刪除這條文件記錄么？”,vbOKCancel+vbExclamation,“警告”>=vbOKThenwt6qbkCyDEintCount=frmRoom.msgList.Row ‘記載當(dāng)前記錄位置txtSQL=”delete

from

rooms

where

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

”’”Kp5zH46zRkSetmrc=ExecuteSQL(txtSQL,MsgText>

‘執(zhí)行刪除操作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 “請先進(jìn)行客房標(biāo)準(zhǔn)設(shè)置 !”,vbOKOnly+vbExclamation,”警告”ExitSubEndIfmrc.CloseEndSubPrivateSubcmdOK_Click(>‘設(shè)置完查詢內(nèi)容和方式后,單擊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“請設(shè)置查詢條件！”,vbOKOnly+vbExclamation,“警告”ExitSubElseIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*fromroomswhere”&sQSqlfrmRoom.ShowEndIfMe.HideEndSub⑹訂房信息管理模塊地創(chuàng)建訂房信息管理模塊主要實現(xiàn)如下功能：·查詢剩余客房信息·添加訂房信息·修改訂房信息·刪除訂房信息·查詢訂房庫信息具體實現(xiàn)代碼如下：PrivateSubFrom_Load(>‘載入窗體時,程序?qū)⒆詣蛹尤肟头糠N類和客房單價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 “請設(shè)置查詢條件!”,vbOKOnly+vbExclamation,“警告”ExitSubElseIfflagReditThenUnloadfrmRoomEndIffrmRoom.txtSQL=”select*from rooms where”&sQSql&”andputup”<>’y’”FyXjoFlMWhfrmRoom.ShowflagSedit=TrueEndIfMe.HideEndSubPrivateSubFrom_Load(> ‘添加訂房信息,載入窗體時把所有未預(yù)定地客房信息加入其中 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 “請先進(jìn)行客房登記!”,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(>‘添加記錄到數(shù)據(jù)庫中DimintCountAsIntegerDimsMegAsStringDimmrccAsADODB.RecordsetDimMsgTextAsStringForintCount=0To3 ‘判斷輸入內(nèi)容是否為空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

“ 入 庫

時 間 應(yīng) 輸

入 日 期

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

” 警告”yhUQsDgRT1txtItem(2>.SetFocusExitSubEndIfForintCount=1To3Step2IfNotIsNumeric(txtItem(intCount>>ThenMsgBox “請輸入數(shù)字”,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⑺結(jié)算信息管理模塊地創(chuàng)建結(jié)算信息管理模塊主要實現(xiàn)如下功能：·添加結(jié)算信息·修改結(jié)算信息·查詢結(jié)算信息具體實現(xiàn)代碼如下：PrivateSubFrom_Load(>‘窗體載入時,自動加入所有未結(jié)算客房信息DimsSqlAsStringDimintCountAsIntegerDimMsgTextAsStringIfgintCmode=1Then‘判斷是否處于添加狀態(tài)Me.Caption=Me.Caption&“添加”txtSQL=”select DISTINCT roomno from booking whereamount=’0’”‘初始化客房編號,選擇所有未結(jié)算客房編號GXRw1kFW5sSetmrc=ExecuteSQL(txtSQL,MsgText>IfNotmrc.EOFThenDoWhileNotmrc.EOFcboItem(0>.AddItemTrim(mrc!roomno>mrc.MoveNextLoopElseMsgBox “沒有顧客入住!”,vbOKOnly+vbExclamation,cmdSave.Enabled=FalseExitSubEndIfmrc.CloseElseIfgintCmode=2Then ‘判斷是否處于修改狀態(tài)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>‘選擇不同客房,觸發(fā)cboItem下拉式文本框地click事件,顯示相應(yīng)地顧客信息8PQN3NDYyPDimsSqlAsStringDimMsgTextStringDimmrccAsADODB.RecordsetDimintCountAsIntegerIfgintCmode=1Then‘判斷是否處于添加狀態(tài),否則將退出IfIndex=0ThencboItem(1>.Enabled=TruecboItem(2>.Enabled=TruecboItem(3>.Enabled=TruecboItem(1>.Clear ‘清除原有內(nèi)容cboItem(2>.ClearcboItem(3>.CleartxtSQL=”selectroomNO,roomtype,roomposition,roompricefromwhereroomNo=’”&Trim(cboItem(0>>&”’”‘顯示有關(guān)客房地具體信息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