roominfo_view.sql

一个基本的酒店管理系统

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