C#酒店管理系统,采用3层架构.zip

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'RoomInfo_value') BEGIN PRINT 'Dropping View RoomInfo_value' DROP View RoomInfo_value END GO /****************************************************************************** ** File: ** Name: RoomInfo_value ** Desc: 房间信息 ** ** This template can be customized: Rid,--房间号 stateType,--状态 roomtel,--房间电话 ingroup,--所在区域 ttype,--房间类型 Uname--宾客姓名 OderPrice--预设单价 WorkPrice --折后单价 NdepositMoney--已交押金 totalMoney=--消费金额 ndatetime--进店时间 leaveTime--预离时间 UseTime--已用时间 Remark--备注 ** ** ** Auth: ** Date: ******************************************************************************* ** Change History ******************************************************************************* ** Date: Author: Description: ** -------- -------- ------------------------------------------- ** *******************************************************************************/ PRINT 'Creating View RoomInfo_value' GO CREATE View RoomInfo_value as select Rid,--房间号 stateType,--状态 roomtel,--房间电话 ingroup,--所在区域 ttype,--房间类型 Aname=(Select Aname from AdminInfo where Aid=(select Naid from newroom where Nrid=rid )),--备注 Uname=case when(select Uname from userinfo where uid=(select Nuid from Newroom where Nrid=rid ))!='' then (select Uname from userinfo where uid=(select Nuid from Newroom where Nrid=rid )) else ''end ,--宾客姓名 OderPrice=--预设单价 (case when (select Nistimeroom from NewRoom where Nrid=rid)=1 then convert(varchar(20),priceByhour)+'￥/小时' else convert(varchar(20),pricebyday)+'￥/天' end ), WorkPrice=--折后单价 (case when (select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))>=0 then case when (select Nistimeroom from NewRoom where Nrid=rid)=1--如果该该人选择计时则按时间计算折后单价 then convert(varchar(20),(select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*priceByhour/0.1)+'￥/小时' else convert(varchar(20),(select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*pricebyday/0.1)+'￥/天' end end ) , NdepositMoney=convert(varchar(20),(select NdepositMoney from NewRoom where Nrid=rid))+'￥',--已交押金 RoomMoney=--房间应收金额 convert(varchar(20), ( case when (select Nistimeroom from NewRoom where Nrid=rid)=1 then (select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*priceByhour/0.1* (select DATEDIFF ( hour , ndatetime , getdate())from newroom where Nrid=rid )--考虑打折钟点房按时计费 else (select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*pricebyday/0.1* (select DATEDIFF (day , ndatetime , getdate())from newroom where Nrid=rid )--考虑打折非钟点房按天计费 end ) )+'￥', totalMoney=--房间总消费金额 convert(varchar(20), ( (select (case when sum(snumber*wprice)>0 then sum(snumber*wprice) else 0 end ) from superfluityinfo,wareinfo where superfluityinfo.wid=wareinfo.wid and nid=rid )--额外消费+房间费 +case when (select Nistimeroom from NewRoom where Nrid=rid)=1 then (select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*priceByhour/0.1* (select DATEDIFF ( hour , ndatetime , getdate())from newroom where Nrid=rid )--考虑打折钟点房按时计费 else (select Discount from guestsort where gid=(select Ugid from userinfo where uid=(select Nuid from NewRoom where Nrid=rid)))*0.1*pricebyday/0.1* (select DATEDIFF (day , ndatetime , getdate())from newroom where Nrid=rid )--考虑打折非钟点房按天计费 end ) )+'￥', ndatetime=(select ndatetime from newroom where Nrid=rid),--进店时间 leaveTime=(select DATEADD ( day , Ndays, Ndatetime )from newroom where Nrid=rid),--预离时间 UseTime=--已用时间 ( case when (select Nistimeroom from NewRoom where Nrid=rid)=1--钟点房计算时间 then (convert(varchar(20),(select DATEDIFF ( minute , ndatetime , getdate())from newroom where Nrid=rid )/60))+'小时'+(convert(varchar(20),(select DATEDIFF ( minute , ndatetime , getdate())from newroom where Nrid=rid )%60))+'分钟'--已用时间按时间计算 else (convert(varchar(20),(select DATEDIFF ( minute , ndatetime , getdate())from newroom where Nrid=rid )/60/24))+'天'+(convert(varchar(20),(select DATEDIFF ( minute , ndatetime , getdate())from newroom where Nrid=rid )/60%24))+'小时'--已用时间按天计算 end ), Remark=(select Remark from newroom where Nrid=rid )--备注 from roominfo,roomstate,typeroom where tid=rtid and stateid=rstateid GO GRANT SELECT ON RoomInfo_value TO PUBLIC GO