Synear_API/sql/sql_20180530...


/****** Object:  Trigger [dbo].[Table_UpdateCallOutRecordList]    Script Date: 05/30/2018 09:35:42 ******/
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_UpdateCallOutRecordList] ON [dbo].[rep_ext_call_out]
   AFTER INSERT
AS 
BEGIN
declare @telphone nvarchar(50) --读取被叫号码
declare @userid int
    declare @username nvarchar(50)
    declare @usercode nvarchar(50)
    declare @seatcode nvarchar(50)
    --读取插入记录的类型和坐席
    SELECT @usercode=cast(INSERTED.CallerAgentID as varchar(50)),@telphone=CalleeNum FROM INSERTED  
    --读取坐席对应信息
    select @userid=F_Userid,@username=F_UserName from T_Sys_UserAccount where F_UserCode=@usercode 
    --读取坐席对应坐席组
    select @seatcode=dbo.GetSeatCodeByUserCode(@usercode)
    
    declare @tel1 nchar(1)--截取号码第一位
     declare @tel2 nchar(2)--截取号码第一位
	select @tel1=LEFT(@telphone,1),@tel2=LEFT(@telphone,2)
    if LEN(@telphone)>9 --and @tel1<>'1' 
    begin --判断号码大于9位数的，即为带区号的固定电话
		if @tel1<>'1'
		begin--首位不是1
			set @telphone='0'+@telphone
		end
		else
		begin
			if @tel2='10'
			begin --针对北京区号010，增加前缀
				set @telphone='0'+@telphone
			end
		end
    end
    
       INSERT  INTO dbo.T_Call_CallRecords
                        ( CallId ,
                          CallNumber ,
                          CallType ,
                          CallState ,
                          DealType ,
                          BeginTime ,
                          RingStartTime,
                          RingEndTime,
                          TalkStartTime ,
                          TalkEndTime,
                          EndTime ,
                          TalkLongTime ,
                          UserId,
                          UserName,
                          UserCode ,
                          ExtNumber,
                          FilePath ,
                          IsDeal ,
                          OperateType ,
                          TaskType,
                          TaskPhoneID,
                          TaskID,
                          ActionID,
                          ActionType,
                          groupcode
                        )
                        SELECT  CallID ,
                                @telphone ,
                                1 ,
                                IsCallOutSucceed ,
                                6 ,
                                TimeRingBack ,
                                DATEADD(s, 2, INSERTED.TimeRingBack),
                                DATEADD(s, -1, INSERTED.TimeConnected),
                                TimeConnected,
                                DATEADD(s, INSERTED.PeriodTalking, INSERTED.TimeConnected),
                                TimeHangUp,
                                PeriodTalking ,
                                @userid,
                                @username,
                                CallerAgentID ,
                                CallerNum,
                                RecFileName ,
                                1,
                                0,
                                inserted.TaskType,
                                inserted.TaskPhoneID,
                                inserted.TaskID,
                                inserted.ActionID,
                                1,
                                @seatcode
                        FROM    INSERTED --where ActionID=1
END