麦德龙IVR满意度调研PCR(默认为不满意)

 
USE [Metro]
GO
/****** 对象:  StoredProcedure [dbo].[sp_Get_Dissatis_Detail]    脚本日期: 02/08/2012 10:03:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Author:  Summit.Lau
-- Create date: 2012.1.13
-- Description: 麦德龙IVR满意度调研PCR(默认为不满意)
/* Example: 
exec sp_Get_Dissatis_Detail @DateBegin=20111207,@DateEnd=20111220,@phone = '58605910',@handler = '73545'
exec sp_Get_Dissatis_Detail @DateBegin=20120101,@DateEnd=20121220,@Satis=1
exec sp_Get_Dissatis_Detail @DateBegin=20120101,@DateEnd=20121220
exec sp_Get_Dissatis_Detail @DateBegin=20111207,@DateEnd=20111220,@phone = '58605910'
exec sp_Get_Dissatis_Detail @DateBegin=20111207,@DateEnd=20121220,@Agent = '77103',@Satis=1
exec sp_Get_Dissatis_Detail @time_begin = '20110120' ,@time_end = '20121207',@Satis=1,@Agent = '77103',@phone = '057185193547'
@Time_begin = '20120201', @Time_end = '20120206'
*/
-- ===================================================================================================
ALTER PROCEDURE [dbo].[sp_Get_Dissatis_Detail]
(
 --@DateBegin BIGINT = NULL, ---- 开始日期
 --@DateEnd BIGINT = NULL, ---- 结束日期
 @Time_Begin VARCHAR(8) = null,  -- 起始时间
 @Time_End VARCHAR(8) = null,  -- 截止时间
 --@DateBegin BIGINT = '',   ---- 开始日期
 --@DateEnd BIGINT = '',   ---- 结束日期
 @phone  varchar(20) = '', ---客户信息（来电号码）
 @Agent varchar(20) = NULL, --- 座席工号 
 @Satis   int = 3     --- 满意度 1、满意 2、一般 3、不满意
)
AS 
begin
TRY

  DECLARE @DateBegin BIGINT, ---- 开始日期
          @DateEnd BIGINT, ---- 结束日期
          @Beg_Date INT,
          @End_Date INT,
          --@Agent   varchar(20),
          @CallIn  VARCHAR(20),
          @Error    INT
   DECLARE  @Temp_Table TABLE
    (
      Sdate BIGINT,      
      Calling VARCHAR(20),
      Agent VARCHAR(20)
    )   
       
   SELECT @DateBegin = CAST(CONVERT(VARCHAR(10),ISNULL(@Time_Begin,GETDATE()),112) AS INT)
   SELECT @DateEnd =  CAST(CONVERT(VARCHAR(10),ISNULL(@time_end,GETDATE()),112) AS INT) 
   --SET @Agent = ISNULL(@handler,'')   
   SET @CallIn = ISNULL(@Phone,'')
   INSERT INTO @Temp_Table
    SELECT REPLACE(sdate,'.',''),Calling,Agentid FROM dbo.Metro_Survey WHERE LEN(Agentid)>0 AND Satisfied = @Satis 
     ORDER BY Sdate DESC 
  SELECT @Beg_Date = DateBeg,@End_Date = DateEnd FROM Defin_Data(NULL,@DateBegin,@DateEnd) 
--  SELECT * FROM @Temp_Table
--    SELECT C.* ,
--    
--    ISNULL(
--(SELECT 
--DISTINCT A.RecordId
--FROM 
--[Link_MetrLog].[VisionLog].dbo.[records] A
--WHERE 
--    A.Calling = C.Calling COLLATE chinese_prc_ci_as
--    AND  A.Master = C.Agent COLLATE chinese_prc_ci_as
--    AND  A.StartDate = C.Sdate 
--    AND  A.inbound = 1
--) ,0) RecordId
--FROM @Temp_Table C 
--WHERE 
--C.Calling = CASE WHEN ISNULL(@CallIn,'')= ''  THEN Calling
--           ELSE @CallIn
--END
--AND
--C.Agent = CASE WHEN ISNULL(@Agent,'')= ''  THEN Agent
--           ELSE @Agent
--END
--AND 
--c.Sdate BETWEEN @Beg_Date AND @End_Date 
 SELECT 
DISTINCT A.StartDate Sdate,c.Calling,c.Agent Agent,A.RecordId

FROM 
[Link_MetrLog].[VisionLog].dbo.[records] A,@Temp_Table C 
WHERE A.Calling = C.Calling COLLATE chinese_prc_ci_as
    AND  A.Master = C.Agent  COLLATE chinese_prc_ci_as
    AND  A.StartDate = C.Sdate
    AND  A.inbound = 1
    AND C.Calling = CASE WHEN ISNULL(@CallIn,'')= ''  THEN C.Calling
           ELSE @CallIn
END
AND
C.Agent = CASE WHEN ISNULL(@Agent,'')= ''  THEN C.Agent
           ELSE @Agent
END
AND 
c.Sdate BETWEEN @Beg_Date AND @End_Date 
    ORDER BY A.StartDate DESC 

END TRY
begin catch
 --if @@trancount != 0 rollback tran a
 set @Error = -1
 print '[dbo.sp_Get_Dissatis_Derail](应用失败！)'
 print Error_Message()
end CATCH


SELECT * FROM dbo.Metro_Survey