USE [ytSummitTeleConf_DB]
GO
/****** Object: StoredProcedure [dbo].[UP_ConfHistory_GetConfSummarys] Script Date: 2013/11/13 16:06:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=============================================================
服 务 器: BJ-SQL-002
对象名称: UP_ConfHistory_GetConfSummarys
功能描述: 历史会议消费信息(包括成功召开的会议及取消预约的会议,统计时包含其短信费用)
测试参数: EXEC [UP_ConfHistory_GetConfSummarys] 254941, '', '2012-01-10', '2014-10-11', 2, 100, 0, '', 1, 0
修改记录:
=============================================================*/
ALTER PROCEDURE [dbo].[UP_ConfHistory_GetConfSummarys]
@SeqNo INT,
@ConfTheme VARCHAR(256),
@StartTime DATETIME,
@EndTime DATETIME,
@PageIndex INT,
@PageSize INT,
@IsGetAll BIT,
@Users XML,
@QueryType INT, --1-当天, 2-历史
@RecordCount INT OUTPUT
AS
SET NOCOUNT ON
--@Users参数格式:
/*
<Root>
<SeqNo>254941</SeqNo>
<SeqNo>254942</SeqNo>
</Root>
*/
DECLARE @tableSeqNo TABLE
(
SeqNo INT NOT NULL PRIMARY KEY
)
--优化当天查询效率
DECLARE @tableConfMember TABLE
(
RowID INT NOT NULL,
ConfRoom UNIQUEIDENTIFIER NOT NULL,
ConfState INT,
BatchID UNIQUEIDENTIFIER
)
--缓存当天查询统计
DECLARE @tableSummary TABLE
(
RowID INT NOT NULL PRIMARY KEY,
ConfRoom UNIQUEIDENTIFIER NOT NULL,
ConfState INT,
HoldTime INT,
CallFee INT,
ActorMember INT
)
IF(@IsGetAll = 1)
BEGIN
INSERT INTO @tableSeqNo
(
SeqNo
)
SELECT SeqNo = T.c.value('(./text())[1]', 'INT')
FROM @Users.nodes('Root/SeqNo') T(c)
END
ELSE
BEGIN
INSERT INTO @tableSeqNo
(
SeqNo
)
VALUES
(
@SeqNo
)
END
--总会议数
SELECT @RecordCount = COUNT(1)
FROM @tableSeqNo A
INNER JOIN dbo.WTC_TB_USERCONF B WITH(NOLOCK)
ON A.SeqNo = B.SeqNo
WHERE B.CONFTIME BETWEEN @StartTime AND @EndTime
AND B.Flag IN(0, 2)
AND B.CONFTITLE LIKE '%' + @ConfTheme + '%'
IF (@QueryType = 1)
BEGIN
--会议参会成员信息
INSERT INTO @tableConfMember
(
RowID,
ConfRoom,
ConfState,
BatchID
)
SELECT
A.RowID,
A.ConfRoom,
A.Flag,
B.BatchID
FROM
(
SELECT
RowID,
ConfRoom,
Flag
FROM
(
SELECT
RowID = ROW_NUMBER() OVER (ORDER BY B.CONFTIME DESC),
B.ConfRoom,
B.Flag
FROM @tableSeqNo A
INNER JOIN dbo.WTC_TB_USERCONF B WITH(NOLOCK)
ON A.SeqNo = B.SeqNo
WHERE B.CONFTIME BETWEEN @StartTime AND @EndTime
AND B.Flag IN(0, 2)
AND B.CONFTITLE LIKE '%' + @ConfTheme + '%'
) T
WHERE rowid BETWEEN (@PageSize * (@PageIndex - 1) + 1) AND (@PageSize * @PageIndex)
) A
LEFT JOIN dbo.WTC_TB_CONFMEMBERS B
ON A.ConfRoom = B.CONFROOM
--计算通话费用
INSERT @tableSummary
(
RowID,
ConfRoom,
ConfState,
HoldTime,
CallFee,
ActorMember
)
SELECT
A.RowID,
A.ConfRoom,
A.ConfState,
HoldTime = ISNULL(SUM(CASE B.HoldTime%60 WHEN 0 THEN B.HoldTime ELSE (B.HoldTime/60+1)*60 END), 0), --通话时长不足1分钟应该按1分钟计算
CallFee = ISNULL(SUM(B.ConsumeAmount),0),
ActorMember = COUNT(A.BatchID)
FROM @tableConfMember A
LEFT JOIN dbo.IB_UserConsumeList B
ON B.SeqNo = @SeqNo
AND B.SerFlag = 4
AND A.BatchID = B.BatchID
GROUP BY A.RowID, A.ConfRoom, A.ConfState
SELECT
A.RowID,
A.ConfRoom, --会议
B.SeqNo, --用户编号
ConfTheme = B.CONFTITLE, --主题
StartTime = B.CREATETIME, --开始时间,预约时间
EndTime = B.UPDATETIME, --结束时间,取消预约时间
A.HoldTime, --总时长
A.ActorMember , --参会人数
A.CallFee, --通话费用
C.SmsFee, --短信费用
B.IsRecord, --是否有录音(只针对已召开会议,新版中需要做复制同步)
A.ConfState, --会议状态:1-已召开, 2-取消预约
D.SummaryID --会议纪要ID
FROM @tableSummary A
INNER JOIN dbo.WTC_TB_CONFROOM B WITH(NOLOCK)
ON A.ConfRoom = B.CONFROOM
OUTER APPLY
(
SELECT SmsFee = SUM(Amount)
FROM dbo.IB_Sms_Send_Bill M WITH(NOLOCK)
WHERE M.ConfRoom = A.ConfRoom
) C
LEFT JOIN dbo.WTC_TB_ConfSummary D
ON A.ConfRoom = D.ConfRoom
ORDER BY A.RowID
END
ELSE
BEGIN
SELECT
A.RowID,
A.ConfRoom, --会议
A.SeqNo, --用户编号
ConfTheme = B.CONFTITLE, --主题
StartTime = B.CREATETIME, --开始时间,预约时间
EndTime = B.UPDATETIME, --结束时间,取消预约时间
HoldTime = C.confHoldTimeSum, --总时长
E.ActorMember , --参会人数
CallFee = C.confBancleSum, --通话费用
SmsFee = C.SmsBancleSum, --短信费用
B.IsRecord, --是否有录音(只针对已召开会议,新版中需要做复制同步)
ConfState = A.Flag, --会议状态:1-已召开, 2-取消预约
D.SummaryID --会议纪要ID
FROM
(
SELECT
RowID,
ConfRoom,
SeqNo,
Flag
FROM
(
SELECT
RowID = ROW_NUMBER() OVER (ORDER BY B.CONFTIME DESC),
B.ConfRoom,
B.SeqNo,
B.Flag
FROM @tableSeqNo A
INNER JOIN dbo.WTC_TB_USERCONF B WITH(NOLOCK)
ON A.SeqNo = B.SeqNo
WHERE B.CONFTIME BETWEEN @StartTime AND @EndTime
AND B.Flag IN(0, 2)
AND B.CONFTITLE LIKE '%' + @ConfTheme + '%'
) T
WHERE RowID BETWEEN (@PageSize * (@PageIndex - 1) + 1) AND (@PageSize * @PageIndex)
) A
LEFT JOIN dbo.WTC_TB_CONFROOM B WITH(NOLOCK)
ON A.CONFROOM = B.CONFROOM
LEFT JOIN dbo.WTC_TB_USERCONF_Stat C WITH(NOLOCK)
ON A.CONFROOM = C.CONFROOM
LEFT JOIN dbo.WTC_TB_ConfSummary D WITH(NOLOCK)
ON A.CONFROOM = D.ConfRoom
OUTER APPLY
(
SELECT ActorMember = COUNT(1)
FROM dbo.WTC_TB_CONFMEMBERS M WITH(NOLOCK)
WHERE M.CONFROOM = A.CONFROOM
) E
ORDER BY A.RowID
END