通用酒店客房管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)說明書-預(yù)覽頁
【正文】 countchangeday where RegisterNo=RegisterNo and NewRoomNo=num 計(jì)算房費(fèi) SELECT room=room+(daycountchangeday)*oldprice+changeday*price END ELSE BEGIN update RegisterRoomInfo set StayDay=daycount where RegisterNo=RegisterNo and RoomNo=num SELECT room=room+daycount*price END fetch next from mycur into num,price,ischange END close mycur deallocate mycur update PayBill set RoomIne=room where RegisterNo=RegisterNo 計(jì)算其他商品消費(fèi)金額 declare other decimal(10,2),modity decimal(10,2) SELECT other=0 聲明游標(biāo) declare mycur2 cursor for select TotalAccount from V_CommodityConsume where RegisterNo=RegisterNo open mycur2 fetch next from mycur2 into modity WHILE (FETCH_STATUS=0) BEGIN SELECT other=other+modity fetch next from mycur2 into modity END close mycur2 deallocate mycur2 減去已退單的商品消費(fèi) declare alter decimal(10,2) 聲明游標(biāo) declare mycur3 cursor for select TotalAccount from V_AlterBill where RegisterNo=RegisterNo open mycur3 fetch next from mycur3 into alter WHILE (FETCH_STATUS=0) BEGIN SELECT other=other+alter fetch next from mycur3 into alter END close mycur3 deallocate mycur3 update PayBill set OtherIne=other where RegisterNo=RegisterNo 計(jì)算 消費(fèi)總金額 update PayBill set TotalAccount=room+other where RegisterNo=RegisterNo 查詢預(yù)付金額 declare prepayment decimal(10,2) SELECT prepayment=Prepayment FROM RegisterInfo WHERE RegisterNo=RegisterNo update PayBill set Prepayment=isnull(prepayment,0) where RegisterNo=RegisterNo 計(jì)算應(yīng)付金額 update PayBill set Account=TotalAccountPrepayment where RegisterNo=RegisterNo COMMIT GO 統(tǒng)計(jì)日營業(yè)狀況 （ P_DayStatistics）： CREATE procedure P_DayStatistics AS BEGIN TRAN declare date datetime declare mycur cursor for select str(YEAR(PayTime),4)+39。+str(DAY(PayTime),2) from V_PayBill where IsPaid=39。 and PayTime between date and DATEADD(dd,1,date) update DayBusiness set GuestAmount=guestamount,RoomIne=roomine,OtherIne=otherincome,DayIne=day where Date=date fetch next from mycur into date END close mycur deallocate mycur COMMIT GO 保存客史資料 （ P_GuestHistoryInfo）： CREATE procedure P_GuestHistoryInfo RegisterNo varchar(10),Name varchar(20),ArriveTime datetime AS BEGIN TRAN declare identityno varchar(20) SELECT identityno=IdentityNo FROM GuestInfo WHERE RegisterNo=RegisterNo and Name=Name 若登記資料時(shí)沒填身份證號(hào) IF identityno=null BEGIN insert into GuestHistoryInfo select Name,Sex,Nationality,IdentityNo,WorkPlace,Phone,Address,ArriveTime,getdate(),Remark from GuestInfo where RegisterNo=RegisterNo and Name=Name END ELSE BEGIN 若存在該賓客的歷史資料 ,則更新記錄 IF EXISTS(SELECT * FROM GuestHistoryInfo WHERE IdentityNo=identityno) BEGIN update GuestHistoryInfo set RecentArriveTime=ArriveTime,RecentLeaveTime=getdate() where IdentityNo=identityno END ELSE 若不存在該賓客的歷史資料 ,則插入該賓客的信息 insert into GuestHistoryInfo select Name,Sex,Nationality,IdentityNo,WorkPlace,Phone,Address,ArriveTime,getdate(),Remark from GuestInfo where RegisterNo=RegisterNo and Name=Name END COMMIT GO
試題試卷相關(guān)推薦
鄂ICP備17016276號(hào)-1