一、 报表参数
1.nHotelID 名称：nHoteId 数据类型：Integer 隐藏 可用值：无查询 默认值： 无查询 =2
2.nRoomId 名称： nRoomId 数据类型：Integer 提示：房号 多值
可用值：来自查询 数据集： Room 值字段： nId 标签字段：cCode
默认值：来自查询 数据集： Room 值字段： nId
数据集 Room 定义：
SQL语句： select nId,cCode from B_Room where bIsUse=1 and nHotelId=@nHotelId
3.RoomKindId 名称：RoomKindId 数据类型：Integer 提示：房型 多值
可用值：来自查询 数据集： RoomKind 值字段： nId 标签字段：cCode
默认值：来自查询 数据集： RoomKind 值字段： nId
数据集 RoomKind 定义
SQL语句： select nId,cCode from B_RoomKind where bIsUse=1 and nHotelId=@nHotelId
4.nRoomStateId 名称： nRoomStateId 数据类型：Integer 提示：房态 多值
可用值：来自查询 数据集： RoomState 值字段： nId 标签字段：cName
默认值：无查询 数据集： 值=5
数据集 RoomState 定义
SQL语句： select nId,cName from A_RoomState
5.nAreadId 名称：nAreadId 数据类型：Integer 提示：区域 多值
可用值：来自查询 数据集： Area 值字段： nId 标签字段：cName
默认值：来自查询 数据集： Area 值字段： nId
数据集 Area 定义
SQL语句： select nId,cName from B_Area where bIsUse=1 and nHotelId=@nHotelId
5.nFloorId 名称： nFloorId 数据类型：Integer 提示：栋/层 多值
可用值：来自查询 数据集： Floor 值字段： nId 标签字段：cName
默认值：来自查询 数据集： Floor 值字段： nId
数据集 Floor 定义
SQL语句： select nId,cName from B_Floor where bIsUse=1 and nHotelId=@nHotelId
6.nDirectionId 名称： nDirectionId 数据类型：Integer 提示：朝向 多值
可用值：来自查询 数据集： Direction 值字段： nId 标签字段：cName
默认值：来自查询 数据集： Direction 值字段： nId
数据集 Direction 定义
SQL语句： select nId,cName from B_Direction where bIsUse=1 and nHotelId=@nHotelId
7.lang 名称：lang 数据类型：String 提示：语言 允许空白值
可用值：无查询 标签： 中文=CN English=EN
默认值： 无查询 CN
二、 数据
1. 数据集 Title SQL语句： select cName from tHotelS where nId=@nHotelId
2. 数据集 HotelDB SQL语句
select A.nId as roomId,A.cCode as roomCode,right('00000000'+ A.cCode,8) AS ConverRoomcode,B.cCode as roomKindCode,B.cName as roomKindName,
C.cCode as roomStateCode,C.cName as roomStateName,
[dbo].[F_GetRoomStateRemark](A.nId,A.nHotelId) AS roomStateRemark,
[dbo].[F_GetRoomState_BeginDate](A.nId,A.nHotelId) as roomStateBeginDate,
[dbo].[F_GetRoomState_EndDate](A.nId,A.nHotelId) as roomStateEndDate,
num=1
from B_Room A
left join B_RoomKind B on B.nId=A.nRoomKindId
left join A_RoomState C on C.nId=dbo.F_GetRoomState(A.nId,A.nHotelId)
left join B_Area D on D.nId=A.nAreaId
left join B_Floor E on E.nId=A.nFloorId
left join B_Direction F on F.nId=A.nDirectionId
where A.nHotelId=@nHotelId
and A.bIsUse=1
and A.nId in (@nRoomId)
and B.nId in (@nRoomKindId)
and C.nId in (@nRoomStateId)
and D.nId in (@nAreaId)
and E.nId in (@nFloorId)
and F.nId in (@nDirectionId)
order by ConverRoomcode
三、布局
房态查询表
=First(Fields!cName.Value, "Title")
="打印时间："+Now
1. 插入一行
=iif(Parameters!lang.Value="CN","房号","Room Code")
=iif(Parameters!lang.Value="CN","房型","Room Type")
=iif(Parameters!lang.Value="CN","房态","Status")
=iif(Parameters!lang.Value="CN","起始时间","FROM")
=iif(Parameters!lang.Value="CN","结束时间","TO")
=iif(Parameters!lang.Value="CN","备注","Remarks")
2. 插入一行
=Fields!RoomCode.Value
=Fields!RoomKindCode.Value
=Fields!RoomStateCode.Value
=FormatDateTime(Fields!roomStateBeginDate.Value,2)
=FormatDateTime(Fields!roomStateEndDate.Value,2)
=Fields!roomStateRemark.Value
3. 总计
=Sum(Fields!num.Value)
四、函数
1.ALTER function [dbo].[F_GetRoomStateRemark]
(
@nRoomId int,
@nHotelId int
)
returns nvarchar(500)
as
begin
declare @ret nvarchar(500),@nRoomStateId int
select @nRoomStateId=dbo.F_GetRoomState(@nRoomId,@nHotelId)
select @ret='房务信息：'+cRemark from C_RoomDayState
where nRoomId=@nRoomId
and nHotelId=@nHotelId
and bActivable=1
and nRoomStateId=@nRoomStateId
--and getdate() between dBeginTime and dEndTime
order by dTag
if @ret is null
begin
set @ret=''
end
return @ret
end
2.
ALTER function [dbo].[F_GetRoomState]
(
@nRoomId int,
@nHotelId int
)
returns int
as
begin
declare @ret int
select @ret=(
select top 1 A.nRoomStateId from C_RoomDayState A
left join A_RoomState B on B.nId=A.nRoomStateId
where
A.nRoomId=@nRoomId
and A.bActivable=1
and A.nHotelId=@nHotelId
order by B.nOrder asc)
if @ret is null
begin
set @ret=dbo.F_GetRoomStateId_Clean()
end
return @ret
END
3.
ALTER function [dbo].[F_GetRoomStateId_Clean]()
returns int
as
begin
-- select * from dbo.A_RoomState
-- declare @ret int
-- select @ret=nId from A_RoomState where cCode='VC'
-- select @ret
return 1
end
4.
ALTER function [dbo].[F_GetRoomState_BeginDate]
(
@nRoomId int,
@nHotelId int
)
returns datetime
as
begin
declare @ret datetime
select @ret=(
select top 1 A.dBeginTime from C_RoomDayState A
left join A_RoomState B on B.nId=A.nRoomStateId
where
A.nRoomId=@nRoomId
and A.bActivable=1
and A.nHotelId=@nHotelId
order by B.nOrder asc)
if @ret is null
begin
set @ret=getdate()
end
return @ret
end
5.
ALTER function [dbo].[F_GetRoomState_EndDate]
(
@nRoomId int,
@nHotelId int
)
returns datetime
as
begin
declare @ret datetime
select @ret=(
select top 1 A.dEndTime from C_RoomDayState A
left join A_RoomState B on B.nId=A.nRoomStateId
where
A.nRoomId=@nRoomId
and A.bActivable=1
and A.nHotelId=@nHotelId
order by B.nOrder asc)
if @ret is null
begin
set @ret=getdate()
end
return @ret
end
