代码
一、 报表参数
1.nHotelID 名称：nHoteId 数据类型：Integer 隐藏 可用值：无查询 默认值： 无查询 =2
2.Team 名称： Team 数据类型：Integer 提示：团散 多值
可用值：无查询 标签： 住客＝2 团客＝1 散客＝0
默认值：无查询 =2
3.VIP 名称：VIP 数据类型：Integer 提示：房型 多值
可用值：来自查询 数据集： GuestKind 值字段： nId 标签字段：cName
默认值：来自查询 数据集： GuestKind 值字段： nId
数据集 GuestKind 定义
SQL语句： Select nId,cName From B_GuestKind Where bIsUse = 1 And nHotelId = @nHotelId order by nId
4.Area 名称：Area 数据类型：Integer 提示：区域 多值
可用值：来自查询 数据集： Area 值字段： nId 标签字段：cName
默认值：来自查询 数据集： Area 值字段： nId
数据集 Area 定义
SQL语句： select nId,cName from B_Area where bIsUse=1 and nHotelId=@nHotelId
5.RoomKind 名称：RoomKind 数据类型：Integer 提示：房型 多值
可用值：来自查询 数据集： RoomKind 值字段： nId 标签字段：cCode
默认值：来自查询 数据集： RoomKind 值字段： nId
数据集 RoomKind 定义
SQL语句： select nId,cCode from B_RoomKind where bIsUse=1 and nHotelId=@nHotelId
5.NightCheckListId 名称： NightCheckListId 数据类型：Integer 提示：夜核批次 多值
可用值：来自查询 数据集： NightCheckList 值字段： nId 标签字段：cName
默认值：空值
数据集 NightCheckList 定义
SQL语句：
select nId,cName=convert(nvarchar(12),DateAdd(dd,-1,dEndTime),112) from C_NightCheckList
where nHotelId=@nHotelId order by nId desc
二、 数据
1. 数据集 Title SQL语句： select cName from tHotelS where nId=@nHotelId
2. 数据集 HotelDB SQL语句
SELECT A.cName AS cGuestName, A.dArrivedTime, A.dLeftTime,A.nGuestCount, A.nKidCount,A.cPactCompany,
B.cCode AS cRoomCode,
C.cName AS cGuestKindName,
D.cCode AS cRoomKindCode,
E.cCode AS cRateCode,
F.cCode AS cMarkCode,
G.cTeamCode,
isnull(dbo.F_GetGuestRoomPriceRM(A.nHotelId,A.nId),0.00) AS RoomRate,
isnull(dbo.F_Z_GetProductPriceByRentKind(1,dbo.F_GetGuestRoomProduct(A.nHotelId,A.nId),A.nRentKindId,1),0.00) AS nPrice,
isnull(dbo.F_GetGuestRoomAllPrice(A.nHotelId,A.nId),0.00) AS nTotalPrice,
isnull(dbo.F_GetConsumeTolValue_NoCash(A.nId, 2, A.nHotelId),0.00) AS AccountEnd,
isnull(dbo.F_GetPrecashTolValue(A.nId, 2, A.nHotelId),0.00) AS AccountPrecash,
num=1
From C_NGuest H LEFT OUTER JOIN
C_Guest AS A ON A.nId=H.nGuestId LEFT OUTER JOIN
B_Room AS B ON B.nId = A.nRoomId LEFT OUTER JOIN
B_GuestKind AS C ON C.nId = A.nGuestKindId LEFT OUTER JOIN
B_RoomKind AS D ON D.nId = B.nRoomKindId LEFT OUTER JOIN
B_RentKind AS E ON E.nId = A.nRentKindId LEFT OUTER JOIN
B_MarkCode AS F ON F.nId = E.nMarkCodeId LEFT OUTER JOIN
C_Bill AS G ON G.nId = A.nBillId
Where H.nHotelId=@nHotelId
and H.nNightCheckListId=@NightCheckListId
and (G.bIsTeam in (@Team) or 2=@Team)
and B.nAreaId in(@Area)
and C.nId in (@VIP)
and D.nId in (@RoomKind)
Order BY cRoomCode
三、布局
房态查询表
=First(Fields!cName.Value, "Title")
="历史在店"+Parameters!Team.Label+"一览表"
=Parameters!NightCheckListId .Label
="打印时间："+Now
1. 插入一行
团代码
VIP
房型
房号
姓名
合约公司
抵店日期
离店日期
人数
小孩数
市场代码
合约类型
合约价
纯房租
房价
客帐余额
2. 插入一行
=Fields!cTeamCode.Value
=Fields!cGuestKindName.Value
=Fields!cRoomKindCode.Value
=Fields!cRoomCode.Value
=Fields!cGuestName.Value
=Fields!cPactCompany.Value
=FormatDateTime(Fields!dArrivedTime.Value,2)
=FormatDateTime(Fields!dLeftTime.Value,2)
=Fields!nGuestCount.Value
=Fields!nKidCount.Value
=Fields!cMarkCode.Value
=Fields!cRateCode.Value
=Fields!nPrice.Value
=Fields!RoomRate.Value
=Fields!nTotalPrice.Value
=Fields!AccountEnd.Value-Fields!AccountPrecash.Value
3. 总计
=Sum(Fields!num.Value)
=Sum(Fields!nGuestCount.Value)
=Sum(Fields!nKidCount.Value)
=Sum(Fields!nPrice.Value)
=Sum(Fields!RoomRate.Value)
=Sum(Fields!nTotalPrice.Value)
=Sum(Fields!AccountEnd.Value)-Sum(Fields!AccountPrecash.Value)
4 页脚
="页："+Format(Globals!PageNumber)+"/"+Format(Globals!TotalPages)
四、函数