通用酒店客房管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)說(shuō)明書-在線瀏覽
2025-02-17 18:19本頁(yè)面
【正文】 從那幾個(gè)表導(dǎo)出的 BillNo 帳單編號(hào) 商品設(shè)置表(CommoditySet)，其它商品消費(fèi)明細(xì)表(CommodityConsume) RegisterNo 入住單號(hào) CommodityNo 商品編號(hào) CommodityName 商品名稱 CommodityPrice 商品單價(jià) Amount 數(shù)量 RemainAmount 剩余可退數(shù)量 TotalAccount 消費(fèi) 金 額 ConsumeTime 消費(fèi)時(shí)間 Operator 操作員 Remark 備注 消費(fèi) 退單 明 細(xì) 視圖 （ V_AlterBill）： 字段名 字段說(shuō)明 是從那幾個(gè)表導(dǎo)出的 No 編號(hào) 商品設(shè)置表(CommoditySet)， 消費(fèi)退單明細(xì)表 (AlterBill) RegisterNo 入住單 號(hào) CommodityNo 商品編號(hào) CommodityName 項(xiàng)目名稱 CommodityPrice 商品單價(jià) Amount 退 單數(shù)量 TotalAccount 退單金 額 AlterTime 退單時(shí)間 Operator 操作員 Reason 退單原因 存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程 清單（ ProcedureList）： 存儲(chǔ)過(guò)程名 說(shuō)明 P_TotalAccount 計(jì)算應(yīng)收帳款 P_DayStatistics 統(tǒng)計(jì)日營(yíng)業(yè)狀況 P_GuestHistoryInfo 保存客史資料 計(jì)算應(yīng)收帳款 （ P_TotalAccount）： CREATE procedure P_TotalAccount RegisterNo varchar(10) AS BEGIN TRAN declare daycount float,flag int,arrivetime datetime declare start int,halfday int,wholeday int,extra int,hour int IF NOT EXISTS(SELECT * FROM PayBill WHERE RegisterNo=RegisterNo) insert into PayBill(RegisterNo) values(RegisterNo) update PayBill set LeaveTime=getdate() where RegisterNo=RegisterNo update PayBill set PayTime=getdate() where RegisterNo=RegisterNo 計(jì)算住店總天數(shù) SELECT arrivetime=ArriveTime FROM RegisterInfo WHERE RegisterNo=RegisterNo SELECT start=CheckIn FROM TimeSet SELECT halfday=CheckOutHalfDay FROM TimeSet SELECT wholeday=CheckOutWholeDay FROM TimeSet SELECT extra=ExtraTime FROM TimeSet SELECT hour=datediff(hh,arrivetime,getdate()) SELECT daycount=hour/24 時(shí)間超過(guò)設(shè)定的某個(gè)值，追加一天 if hour(hour/24)*24wholeday+isnull(extra,0)start SELECT daycount=daycount+1 時(shí)間超過(guò)設(shè)定的某個(gè)值，追加半天 else if hour(hour/24)*24halfday+isnull(extra,0)start SELECT daycount=daycount+ 總天數(shù)未滿半天算半天 if daycount=0 SELECT daycount= 計(jì)算房費(fèi) declare room decimal(10,2) declare num varchar(10),price decimal(10,2),ischange varchar(5) 聲明游標(biāo) declare mycur cursor for select RoomNo,RealPrice,IsChangeRoom from V_RegisterRoomInfo where RegisterNo=RegisterNo open mycur fetch next from mycur into num,price,ischange SELECT room=0 WHILE (FETCH_STATUS=0) BEGIN IF ischange=39。 在住店期間換過(guò)房 BEGIN declare oldprice decimal(10,2),changetime datetime,changeday float SELECT oldprice=OldRealPrice,changetime=ChangeTime FROM ChangeRoom WHERE RegisterNo=RegisterNo and NewRoomNo=num 計(jì)算換房后在新客房住的天數(shù) SELECT hour=datediff(hh,changetime,getdate()) SELECT changeday=hour/24 if hour(hour/24)*24wholeday+isnull(extra,0)start SELECT changeday=changeday+1 else if hour(hour/24)*24halfday+isnull(extra,0)start SELECT changeday=changeday+ update RegisterRoomInfo set StayDay=changeday where RegisterNo=RegisterNo and RoomNo=num update ChangeRoom set OldStayDay=daycountchangeday 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
試題試卷相關(guān)推薦
鄂ICP備17016276號(hào)-1