统计
/****** Object: Procedure [dbo].[JOB_UP_Summit_UserConfStat] Script Date: 2014-3-17 10:00:50 ******/
USE [ytSummitTeleConf_DB];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO /*============================================================= =============================================================*/
CREATE PROC [dbo].[JOB_UP_Summit_UserConfStat]
@BeginTime DATETIME = NULL,
@EndTime DATETIME = NULL
AS
SET NOCOUNT ON DECLARE
@RangeBeginTime DATETIME,
@RangeEndTime DATETIME IF(@BeginTime IS NOT NULL AND @EndTime IS NOT NULL)
BEGIN
SELECT
@RangeBeginTime = @BeginTime,
@RangeEndTime = @EndTime
END
ELSE IF(@BeginTime IS NULL AND @EndTime IS NULL)
BEGIN
SELECT
@RangeBeginTime = CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120),
@RangeEndTime = CONVERT(CHAR(10), GETDATE(), 120)
END
ELSE
BEGIN
RAISERROR('日期范围错误!', 1, 16)
RETURN
END --通话消费
INSERT INTO WTC_TB_USERCONF_DetailStat
(
CONFROOM,
SeqNo,
startTime,
CallerNum,
CalledNum,
HoldTime,
Banlnce,
CallFlag,
MebID,
CreateTime,
ConfGUID,
BalanceType,
Data
)
SELECT
B.ConfROOM,
C.SeqNO,
C.startTime,
C.CallerNum,
C.CalledNum,
C.HoldTime,
C.ConsumeAmount AS Banlnce,
C.SubSerFlag AS CallFlag,
C.BatchCode,
GETDATE() AS CreateTime,
NEWID() AS ConfGUID,
C.BalanceType,
B.Data
FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK)
INNER JOIN dbo.WTC_TB_CONFMEMBERS B WITH(NOLOCK)
ON A.CONFROOM = B.CONFROOM
INNER JOIN IB_UserConsumeList C WITH(NOLOCK)
ON A.SeqNo = C.SeqNo
--AND B.BatchID = C.BatchID
AND CAST(B.MebID AS VARCHAR(36)) = C.BatchCode
AND C.Serflag = 4
WHERE A.Flag = 1 --召开成功的会议
AND A.CONFTIME >= @RangeBeginTime
AND A.CONFTIME < @RangeEndTime --短信消费
INSERT INTO dbo.WTC_TB_USERSMS_DetailStat
(
CONFROOM,
SeqNo,
MsgID,
Mobile,
SendTime,
[State],
SmsID,
MessageContent,
SmsType,
Amount
)
SELECT
A.CONFROOM,
A.SeqNo,
B.MsgID,
C.Mobile,
B.SendTime,
B.[State],
C.SmsID,
MessageContent = [Message],
B.SmsType,
B.Amount
FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK)
INNER JOIN dbo.IB_Sms_Send_Bill B WITH(NOLOCK)
ON A.CONFROOM = B.CONFROOM
INNER JOIN dbo.IB_Sms_Send_Detail_Bill C WITH(NOLOCK)
ON B.MsgID = C.MsgID
WHERE A.CONFTIME >= @RangeBeginTime --所有的会议都有可能收取短信费用
AND A.CONFTIME < @RangeEndTime --会议统计
INSERT INTO dbo.WTC_TB_USERCONF_Stat
(
CONFROOM,
SeqNo,
CONFTITLE,
CONFTIME,
confHoldTimeSum,
confBancleSum,
confBancleSumMoth,
CreateTime,
BillCallNum,
ConfCallNum,
SmsBancleSum
)
SELECT
A.CONFROOM,
A.SeqNo,
A.CONFTITLE,
A.CONFTIME,
ConfHoldTimeSum = ISNULL(B.ConfHoldTimeSum, 0),
ConfBancleSum = ISNULL(B.ConfBancleSum, 0),
ConfBancleSumMoth = ISNULL(B.ConfBancleSumMoth, 0),
GETDATE(),
BillCallNum = ISNULL(B.BillCallNum, 0), --计费通话数
ConfCallNum = ISNULL(C.ConfCallNum, 0), --会议通话数
SmsBancleSum = ISNULL(D.SmsBancleSum, 0)
FROM dbo.WTC_TB_USERCONF A WITH(NOLOCK)
OUTER APPLY
(
SELECT
ConfHoldTimeSum = SUM(CASE HoldTime%60 WHEN 0 THEN HoldTime ELSE (HoldTime/60+1)*60 END),
ConfBancleSum = SUM(Banlnce),
ConfBancleSumMoth = SUM(CASE WHEN BalanceType <> 3 THEN Banlnce END),
BillCallNum = COUNT(1)
FROM dbo.WTC_TB_USERCONF_DetailStat M WITH(NOLOCK)
WHERE M.CONFROOM = A.CONFROOM
) B
OUTER APPLY
(
SELECT ConfCallNum = COUNT(1)
FROM dbo.WTC_TB_CONFMEMBERS M WITH(NOLOCK)
WHERE M.CONFROOM = A.CONFROOM
) C
OUTER APPLY
(
SELECT SmsBancleSum = SUM(Amount)
FROM dbo.WTC_TB_USERSMS_DetailStat M WITH(NOLOCK)
WHERE M.CONFROOM = A.CONFROOM
) D
WHERE A.CONFTIME >= @RangeBeginTime
AND A.CONFTIME < @RangeEndTime GO