联系及时率

无详细内容 无 CREATE OR REPLACE PROCEDURE P_MDJ_1001066_TO_ZW(I_SPAN IN VARCHAR2, --无用参数可输入任意值 O_SQL OUT VARCHAR2, O_FILE OUT VARCHAR2)/* 宽带工单1001066外呼数据上传装维系统 * 2014-02-20 * 蒙冬珏 * 需求流程:(DocID:1989216) 关于客

CREATE OR REPLACE PROCEDURE P_MDJ_1001066_TO_ZW(I_SPAN IN VARCHAR2, --无用参数可输入任意值
                                                O_SQL  OUT VARCHAR2,
                                                O_FILE OUT VARCHAR2)
/* 宽带工单1001066外呼数据上传装维系统
 * 2014-02-20
 * 蒙冬珏
 * 需求流程:(DocID:1989216) 关于客服系统开发并传送宽带预约及时率报表的需求单
 */

 AS
  O_RET          NUMBER;
  PROCEDURE_NAME VARCHAR2(200);
  V_SQLECODE     VARCHAR2(50);
  V_SQLERRM      VARCHAR2(500);
  V_FILENAME     VARCHAR2(100);
  V_SQL          VARCHAR2(4000);
BEGIN
  PROCEDURE_NAME:=GETEXEC_PROCEDURE_NAME();
  O_RET := 1;

  execute immediate 'truncate table ICDPUB.T_MDJ_1001066_TO_ZW';

  INSERT INTO ICDPUB.T_MDJ_1001066_TO_ZW
  SELECT B.SERIALNO 流水号,
         CI.ORGANAME 地市名称,
         B.AREAID 小区编码,
         B.FIXMAN 维护员,
         A.CALLERNO 主叫号码,
         A.CALLEDNO 被叫号码,
         CASE WHEN A.ISPROCESSED IS NULL THEN '未呼出'
              WHEN A.ISPROCESSED = '0' THEN '呼出失败'
              ELSE '呼出成功' END 首次呼出是否成功,
         A.REMARK 首次呼出失败原因,
         A.FIRSTCONTACTSTARTTIME 首次联系时间,
         A.LASTCONTACTSTARTTIME 最后一次联系时间,
         A.CONTACTDURATION 首次通话时长,
         CASE WHEN A.FIRSTCONTACTSTARTTIME IS NULL THEN '否'
              WHEN (A.FIRSTCONTACTSTARTTIME - B.ARRIVETIME1) * 24 > 1 THEN '否'
              ELSE '是' END 是否1小时内联系用户,
         CASE WHEN A.FIRSTCONTACTSTARTTIME IS NULL THEN '否'
              WHEN (A.FIRSTCONTACTSTARTTIME - B.ARRIVETIME2) * 24 > 3 THEN '否'
              ELSE '是' END 是否3小时内联系用户,
         A.CALLCOUNT 本流水号累计联系用户次数
     FROM (SELECT T.SUBSNUMBER,MAX(CASE WHEN T.RN=1 THEN T.ISPROCESSED END) ISPROCESSED,MIN(T.CONTACTSTARTTIME) FIRSTCONTACTSTARTTIME,
                  MAX(T.CONTACTSTARTTIME) LASTCONTACTSTARTTIME,MAX(CASE WHEN RN=1 THEN T.CALLERNO END) CALLERNO,
                  MAX(CASE WHEN RN=1 THEN T.CALLEDNO END) CALLEDNO,MAX(CASE WHEN RN=1 THEN T.CONTACTDURATION END) CONTACTDURATION,
                  MAX(CASE WHEN RN=1 THEN T.REMARK END) REMARK,COUNT(T.SUBSNUMBER) CALLCOUNT --一张工单有多次呼叫，按工单流水分组，取第一次呼叫用户的记录
                  FROM
               (SELECT CD.SUBSNUMBER,            --工单流水号
                       CD.ISPROCESSED,			 --是否呼叫成功
                       CD.CONTACTSTARTTIME,		 --联系时间
                       CD.CALLERNO,				 --主叫号码
                       CD.CALLEDNO,				 --被叫号码
                       CD.CONTACTDURATION,		 --呼叫时长
                       CD.REMARK,				 --呼叫备注（失败原因）
                       ROW_NUMBER() OVER(PARTITION BY CD.SUBSNUMBER ORDER BY CD.CONTACTSTARTTIME) RN
                  FROM ICDPUB.T_CCT_CONTACTDETAIL CD
                 WHERE CD.CONTACTMODEID = '19'
                  AND CD.CONTACTSTARTTIME >= TRUNC(SYSDATE)-11
                  AND CD.CONTACTSTARTTIME = 18 THEN
                     TRUNC(CREATIONTIME + 1) + 9 / 24 --18点后下派的工单，下派时间算作第二天9点
                    WHEN TO_CHAR(CREATIONTIME,'HH24') >17 AND TO_CHAR(CREATIONTIME,'HH24') = 18 THEN
                     TRUNC(CREATIONTIME + 1) + 8.5 / 24
                    WHEN TO_CHAR(CREATIONTIME, 'hh24') >15 AND TO_CHAR(CREATIONTIME, 'hh24') = TRUNC(SYSDATE)-1
                      AND PP.ARCHIVEDATE