Synear_API/sql/sql_20180530...


/****** Object:  Trigger [dbo].[Table_UpdateCallRecordList]    Script Date: 05/29/2018 17:41:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<赵鹏举>
-- Create date: <2016-04-07>
-- Description:	<外线呼入后，转入到坐席，更新业务库通话记录表中通话信息>
-- Author:		<郑兵兵>
-- Update date: <2018-05-30>
-- Description:	<通话记录表增加坐席组的记录，方便分组查询记录使用>
-- =============================================
ALTER TRIGGER [dbo].[Table_UpdateCallRecordList] ON [dbo].[rep_ext_call_in]
    AFTER INSERT
AS
    BEGIN
    DECLARE @callId bigint
    declare @calltype int --呼入类型(0内线呼入1外线呼入2坐席转移呼入3三方呼入)
    declare @userid int
    declare @username nvarchar(50)
    declare @usercode nvarchar(50)
    declare @seatcode nvarchar(50)
    --读取插入记录的类型和坐席
    SELECT @callId=INSERTED.CallId,@calltype=INSERTED.CallType,@usercode=cast(INSERTED.CalleeAgentID as varchar(50)) FROM INSERTED  
    --读取坐席对应信息
    select @userid=F_Userid,@username=F_UserName from T_Sys_UserAccount where F_UserCode=@usercode 
    --读取坐席对应坐席组
    select @seatcode=dbo.GetSeatCodeByUserCode(@usercode)
    
   if @calltype=1
        begin
        UPDATE dbo.T_Call_CallRecords SET 
						IvrEndTime=INSERTED.TimeAlerting ,--IVR结束时间 就是振铃开始时间
						IvrLongTime=DATEDIFF(SECOND,IvrStartTime,INSERTED.TimeAlerting),--IVR时长
						WaitEndTime=INSERTED.TimeAlerting ,--排队等待结束时间 就是振铃开始时间 
						WaitLongTime= DATEDIFF(SECOND,WaitStartTime,INSERTED.TimeAlerting),--IVR时长
                        RingStartTime = INSERTED.TimeAlerting ,--振铃时间
                        RingEndTime = DATEADD(s, INSERTED.PeriodAlerting, INSERTED.TimeAlerting) ,--振铃结束时间
                        TalkStartTime = INSERTED.TimeAnswer ,--应答时间
                        TalkEndTime = DATEADD(s, INSERTED.PeriodTalking, INSERTED.TimeAnswer),--通话结束时间
                        RingLongTime=INSERTED.PeriodAlerting,--振铃时长
                        TalkLongTime=INSERTED.PeriodTalking,--通话时长
                        UserId=@userid,--坐席id
                        UserName=@username,--坐席姓名
                        UserCode=CalleeAgentID,--坐席工号
                        ExtNumber=CalleeNum,--坐席分机
                        FilePath=RecFileName,--录音地址
                        LongTime=DATEDIFF(SECOND,inserted.TimeAlerting,inserted.TimeHangUp),--总时长
                        actionid=inserted.ActionID,
                        actiontype=1,
                        groupcode=@seatcode
        FROM INSERTED
        WHERE T_Call_CallRecords.CallId = cast(INSERTED.CallId as varchar(50)) and T_Call_CallRecords.actiontype<2 and T_Call_CallRecords.CallType=0
       end 
      else
        begin
			INSERT INTO T_Call_CallRecords(CallId,CallNumber,CallType,CallState,DealType,
			BeginTime,IvrStartTime,IvrEndTime,RingStartTime,RingEndTime,
			TalkStartTime,TalkEndTime,EndTime,RingLongTime,TalkLongTime,LongTime,
			UserId,UserName,UserCode,FilePath,ExtNumber,actionid,actiontype,
			IsDeal,OperateType,MYD,groupcode) 
			select cast(CallId as varchar(50)),CallerNum,0,IsAnswer,6,
			TimeAlerting,TimeAlerting,TimeAlerting,TimeAlerting,DATEADD(s, PeriodAlerting, TimeAlerting),
			TimeAnswer,DATEADD(s, PeriodTalking, TimeAnswer),TimeHangUp,PeriodAlerting,PeriodTalking,DATEDIFF(SECOND,TimeAlerting,TimeHangUp),
			@userid,@username,CalleeAgentID,RecFileName,CalleeNum,inserted.ActionID,calltype,
			0,0,0,@seatcode from INSERTED
        end
        
      
    END