知行合一

博客园 首页 新随笔 联系 订阅 管理

 
一、 报表参数

        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

 


 

 

posted on 2010-02-01 10:46  callbin  阅读(395)  评论(0编辑  收藏  举报