SQL 会议消费记录统计

统计

/****** 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