sql 电信分析

CREATE OR REPLACE PROCEDURE P_SD_FCT_CDMA_TERM_MODEL(
    IN @LOAD_DATE VARCHAR(8) DEFAULT CONVERT(VARCHAR(8), DATEFORMAT(DATEADD(MM,-1,NOW()), 'YYYYMMDD'))
)
/*******************************************************************
--  PURPOSE    : 换机模型汇总过程
--  AUTHER     : 唐**
--  FREQUENCY  : 按要求生成每月历史数据
--  DATE       : 2016-01-05


---------------------------------------------------------------
--  输入参数   ：LOAD_DATE 表的生产日期，默认取系统时间
--  结果表     : FCT_CDMA_TERM_MODEL(分区表按月份分区)
---------------------------------------------------------------
--  MODIFIED HISTORY
--  DATE        AUTHOR      VERSION   DESCRIPTION
--  2016-01-05  唐**      V1.0      创建过程
*******************************************************************/


BEGIN
    --日志需求变量
    DECLARE @OBJECT_NAME        VARCHAR(30) DEFAULT 'P_SD_FCT_CDMA_TERM_MODEL';
    DECLARE @STEP_LOGS          VARCHAR(200);
    DECLARE @START_TIME         TIMESTAMP;
    DECLARE @ROW_CNT            NUMERIC(13);

--过程变量
    DECLARE @MONTH_ID                INT;                   --统计月份
    DECLARE @MONTH_LAST_DAY          VARCHAR(10);           --统计月最后一天
    DECLARE @NEXT_MONTH_FIRST_DAY    VARCHAR(10);           --统计月下月第一天
    DECLARE @PRE_MONTH_ID            INT;                   --统计上月
    DECLARE @PRE1_MONTH_ID           INT;                   --统计上上月
    DECLARE @PRE2_MONTH_ID           INT;                   --统计上上上月
    DECLARE @NEXT_MONTH_ID           INT;                   --统计下月
    DECLARE @SCRIPT VARCHAR(3000);
    
--变量初始化
    SET @MONTH_ID = CONVERT(INT,DATEFORMAT(@LOAD_DATE,'YYYYMM'));
    SET @MONTH_LAST_DAY = DATEFORMAT(DATEADD(DD,-1,DATEFORMAT(DATEADD(MM,1,@LOAD_DATE),'YYYYMM')||'01'),'YYYY-MM-DD');
SET @NEXT_MONTH_FIRST_DAY = DATEFORMAT(DATEADD(MM,1,@LOAD_DATE),'YYYY-MM')||'-01';
SET @PRE_MONTH_ID = CONVERT(INT,DATEFORMAT(DATEADD(MM,-1,@LOAD_DATE),'YYYYMM'));
SET @PRE1_MONTH_ID = CONVERT(INT,DATEFORMAT(DATEADD(MM,-2,@LOAD_DATE),'YYYYMM'));
SET @PRE2_MONTH_ID = CONVERT(INT,DATEFORMAT(DATEADD(MM,-3,@LOAD_DATE),'YYYYMM'));
SET @NEXT_MONTH_ID = CONVERT(INT,DATEFORMAT(DATEADD(MM,1,@LOAD_DATE),'YYYYMM'));
    
-------------------------------------------------------------------------------------------------------------------------
    SET @STEP_LOGS = 'STEP 1:取全网用户及其基础特征信息';
    SET @START_TIME = NOW();
    
--删除临时表
DROP TABLE IF EXISTS SD_TEMP_CDMA_BASIC_INFO; COMMIT;
    --插入全网用户及其基础特征信息到临时表
SELECT MONTH_ID
 ,SERV_ID
 ,SERV_NBR
 ,ACC_NBR
 ,CUST_NAME
 --,CASE WHEN LENGTH(CUST_NAME) > 4 THEN '公司客户' ELSE '私人客户' END AS CUST_NAME_TYPE
 ,CUST_ID
 ,SERV_GRP_TYPE
 ,PROD_ID
 --,CASE WHEN PROD_ID = 3204 THEN '预付费' WHEN PROD_ID = 3205 THEN '后付费' ELSE '其它' END AS PAY_TYPE
 --,CASE WHEN FINISH_DATE IS NULL THEN CREATE_DATE ELSE FINISH_DATE END AS INNET_DT 
 ,CREATE_DATE
 ,FINISH_DATE
 ,ONLINE_TIME AS YH_ONLINE_TIME
 ,IS_ZFK
 ,IS_CZ
 ,STATE
 ,WIRELESS_TYPE
 INTO SD_TEMP_CDMA_BASIC_INFO
 FROM RPT_COMM_CM_SERV_CDMA_2010
WHERE MONTH_ID = @MONTH_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

    SET @STEP_LOGS = 'STEP 2:增加是否VPN用户/是否VIP用户/客户类型/付费类型/入网时间信息';
    SET @START_TIME = NOW();

--增加是否VPN用户/是否VIP用户/客户类型/付费类型/入网时间信息
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD 
    (
 IS_VPN         INT         NULL
,IS_VIP         INT         NULL
,CUST_NAME_TYPE VARCHAR(30) NULL
,PAY_TYPE       VARCHAR(30) NULL
,INNET_DT       DATE        NULL
);


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 2.1:更新客户类型/付费类型/入网时间信息';
    SET @START_TIME = NOW();

--更新客户类型/付费类型/入网时间信息
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET CUST_NAME_TYPE = CASE WHEN LENGTH(CUST_NAME) > 4 THEN '公司客户' ELSE '私人客户' END
     ,PAY_TYPE       = CASE WHEN PROD_ID = 3204 THEN '预付费' WHEN PROD_ID = 3205 THEN '后付费' ELSE '其它' END
 ,INNET_DT       = CASE WHEN FINISH_DATE IS NULL THEN CREATE_DATE ELSE FINISH_DATE END;
    COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 2.2:更新是否VPN用户';
    SET @START_TIME = NOW();


--更新是否VPN用户
UPDATE SD_TEMP_CDMA_BASIC_INFO SET IS_VPN = 0;
COMMIT;

UPDATE SD_TEMP_CDMA_BASIC_INFO AS A 
  SET IS_VPN = 1
 FROM VPN_ZB_LIST AS B
WHERE A.ACC_NBR = B.ACC_NBR;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 2.3:更新是否VIP用户';
    SET @START_TIME = NOW();


--更新是否VIP用户
UPDATE SD_TEMP_CDMA_BASIC_INFO SET IS_VIP = 0;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET IS_VIP = 1
 FROM REPORT.RPT_KF_CDMA_ZGDPZ_QD AS B, --张云娇维护
      RPT_COMM_CM_SERV_CDMA_2010 AS C
WHERE B.YEAR = 2016
  AND A.SERV_ID = C.SERV_ID
  AND C.CUST_NBR = B.CQ_CUST_NBR
  AND C.MONTH_ID = @MONTH_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3:增加付费账户类型（对公、对私、其它）、缴费方式';
    SET @START_TIME = NOW();

--增加付费账户类型（对公、对私、其它）、缴费方式
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD 
    (
 ACCT_ID        NUMERIC(10) NULL
,PAYMENT_METHOD VARCHAR(6)  NULL
,BANK_ACCT_TYPE VARCHAR(6)  NULL
,PAY_ACC_TYPE   VARCHAR(40) NULL
,PAY_MET_TYPE   VARCHAR(40) NULL
    );


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3.1:初始化付费账户类型';
    SET @START_TIME = NOW();


--初始化付费账户类型
UPDATE SD_TEMP_CDMA_BASIC_INFO SET PAY_ACC_TYPE = '其它',PAY_MET_TYPE = '其它';
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3.2:获取帐户标识';
    SET @START_TIME = NOW();


--获取帐户标识
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.ACCT_ID = B.ACCT_ID
 FROM ZWFXDEV.TB_CM_SERVACCT AS B
WHERE A.SERV_ID = B.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3.3:获取付费帐户标识、缴费标识';
    SET @START_TIME = NOW();


--获取付费帐户标识、缴费标识
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PAYMENT_METHOD = B.PAYMENT_METHOD
 ,A.BANK_ACCT_TYPE = B.BANK_ACCT_TYPE
 FROM ZWFXDEV.TB_CM_BANKACCT AS B
WHERE A.ACCT_ID = B.ACCT_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3.4:更新付费账户类型';
    SET @START_TIME = NOW();


--更新付费账户类型
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET PAY_ACC_TYPE = B.DICT_NAME
 FROM ZWFXDEV.TB_PC_DICT AS B
WHERE B.DICT_TYPEID = 'CM_ACCTTYPE'
  AND A.BANK_ACCT_TYPE = B.DICT_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 3.5:更新缴费方式';
    SET @START_TIME = NOW();
    
--更新缴费方式
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET PAY_MET_TYPE = B.DICT_NAME
 FROM ZWFXDEV.TB_PC_DICT AS B
WHERE B.DICT_TYPEID = 'CM_PAYM'
  AND A.PAYMENT_METHOD = B.DICT_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 4:增加用户行为信息';
    SET @START_TIME = NOW();

--增加用户行为信息
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD
(
 STM_DATA_01 NUMERIC(12,2) NULL,
 MOU_CALLER_01 NUMERIC(12,2) NULL,
 MOU_CALL_01 NUMERIC(12,2) NULL,
 FEE_01 NUMERIC(12,2) NULL,
 FEE_NEW_01 NUMERIC(12,2) NULL,
 MGS_COUNTS_01 NUMERIC(12) NULL,


 STM_DATA_02 NUMERIC(12,2) NULL,
 MOU_CALLER_02 NUMERIC(12,2) NULL,
 MOU_CALL_02 NUMERIC(12,2) NULL,
 FEE_02 NUMERIC(12,2) NULL,
 FEE_NEW_02 NUMERIC(12,2) NULL,
 MGS_COUNTS_02 NUMERIC(12) NULL,


 STM_DATA_03 NUMERIC(12,2) NULL,
 MOU_CALLER_03 NUMERIC(12,2) NULL,
 MOU_CALL_03 NUMERIC(12,2) NULL,
 FEE_03 NUMERIC(12,2) NULL,
 FEE_NEW_03 NUMERIC(12,2) NULL,
 MGS_COUNTS_03 NUMERIC(12) NULL
);


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 4.1:更新上月行为信息';
    SET @START_TIME = NOW();


--更新上月行为信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.STM_DATA_01 = B.STM_DATA,
  A.MOU_CALLER_01 = B.MOU_CALL,
  A.MOU_CALL_01 = B.MOU,
  A.FEE_01 = B.FEE,
  A.FEE_NEW_01 = B.FEE_NEW,
  A.MGS_COUNTS_01 = B.MGS_COUNTS
 FROM RPT_COMM_CM_SERV_CDMA_2010 AS B
WHERE B.MONTH_ID = @PRE_MONTH_ID
  AND A.SERV_ID = B.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 4.2:更新上上月行为信息';
    SET @START_TIME = NOW();


--更新上上月行为信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.STM_DATA_02 = B.STM_DATA,
  A.MOU_CALLER_02 = B.MOU_CALL,
  A.MOU_CALL_02 = B.MOU,
  A.FEE_02 = B.FEE,
  A.FEE_NEW_02 = B.FEE_NEW,
  A.MGS_COUNTS_02 = B.MGS_COUNTS
 FROM RPT_COMM_CM_SERV_CDMA_2010 AS B
WHERE B.MONTH_ID = @PRE1_MONTH_ID
  AND A.SERV_ID = B.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 4.3:更新上上上月行为信息';
    SET @START_TIME = NOW();


--更新上上上月行为信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.STM_DATA_03 = B.STM_DATA,
  A.MOU_CALLER_03 = B.MOU_CALL,
  A.MOU_CALL_03 = B.MOU,
  A.FEE_03 = B.FEE,
  A.FEE_NEW_03 = B.FEE_NEW,
  A.MGS_COUNTS_03 = B.MGS_COUNTS
 FROM RPT_COMM_CM_SERV_CDMA_2010 AS B
WHERE B.MONTH_ID = @PRE2_MONTH_ID
  AND A.SERV_ID = B.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 5:增加是否活跃用户';
    SET @START_TIME = NOW();

--增加是否活跃用户
--前三个月的流量+通话+短信大于30为合约用户
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD IS_HIGH_USE INT NULL;


UPDATE SD_TEMP_CDMA_BASIC_INFO SET IS_HIGH_USE = 0;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.IS_HIGH_USE = 1
WHERE (MOU_CALL_01 + STM_DATA_01 + MGS_COUNTS_01) > 30;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.IS_HIGH_USE = 1
WHERE (MOU_CALL_02 + STM_DATA_02 + MGS_COUNTS_02) > 30;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.IS_HIGH_USE = 1
WHERE (MOU_CALL_03 + STM_DATA_03 + MGS_COUNTS_03) > 30;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 6:增加主套餐信息';
    SET @START_TIME = NOW();


--增加主套餐信息
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD (MAIN_DISC_ID NUMERIC(10) NULL,MAIN_PRICE_ID NUMERIC(10) NULL,MAIN_DISC_NAME VARCHAR(100));


--更新主套餐信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.MAIN_DISC_ID = B.DISC_ID,
  A.MAIN_PRICE_ID = B.PRICE_ID,
  A.MAIN_DISC_NAME = C.DISC_NAME
 FROM RPT_COMM_CM_MSDISC_2010 AS B,DIM_MAIN_DISC AS C
WHERE B.PROD_ID IN (3204,3205)
  AND B.MONTH_ID = @MONTH_ID
  AND A.SERV_ID = B.SERV_ID
  AND B.DISC_ID = C.DISC_ID
  AND B.PRICE_ID = C.PRICE_ID
  AND B.LIMIT_DATE >= @MONTH_LAST_DAY;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 7:处理补贴信息需要创建多个临时表';
    SET @START_TIME = NOW();

--处理补贴信息需要创建多个临时表
--临时表1(SD_TEMP_SERV_CDMA_BT):存放补贴基础信息
--临时表2(SD_TEMP_SERV_CDMA_BT_MLEVEL):获取最小LEVEL，以得到DATA_SOUCE_ID
--临时表3(SD_TEMP_SERV_CDMA_BT_ETL):处理补贴信息：当有多个补贴时，取最近一个补贴


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 7.1:获取补贴基础信息';
    SET @START_TIME = NOW();

--删除临时表1
DROP TABLE IF EXISTS SD_TEMP_SERV_CDMA_BT;

--获取补贴基础信息
SELECT A.SERV_ID,
  B.DISC_ID,
  B.PRICE_ID,
  C.DATA_SOUCE_DL,
  B.CREATE_DATE,
  B.LIMIT_DATE,
  B.MSINFO_ID,
  C.DISC_LEVEL,
  C.DISC_NAME,
  C.PRICE_NAME,
  C.DATA_SOUCE_ID,
  C.DATA_SOUCE_NAME
 INTO SD_TEMP_SERV_CDMA_BT
 FROM SD_TEMP_CDMA_BASIC_INFO AS A,RPT_COMM_CM_MSDISC_2010 AS B,REPORT.RPT_DAILY_CDMA_TERMINAL_DISC_V2 AS C
WHERE B.MONTH_ID = @MONTH_ID
  AND A.SERV_ID = B.SERV_ID
  AND B.DISC_ID = C.DISC_ID
  AND B.PRICE_ID = C.PRICE_ID
  AND C.DATA_SOUCE_DL IN ('终端补贴','话费补贴');
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 7.2:获取最小LEVEL，以得到DATA_SOUCE_ID';
    SET @START_TIME = NOW();


--删除临时表2
DROP TABLE IF EXISTS SD_TEMP_SERV_CDMA_BT_MLEVEL;

--获取最小LEVEL，以得到DATA_SOUCE_ID
SELECT SERV_ID,
  MIN(DISC_LEVEL) AS DISC_LEVEL
 INTO SD_TEMP_SERV_CDMA_BT_MLEVEL
 FROM SD_TEMP_SERV_CDMA_BT
GROUP BY SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 7.3:获取补贴金额';
    SET @START_TIME = NOW();


--获取补贴金额
ALTER TABLE SD_TEMP_SERV_CDMA_BT ADD AMOUNT NUMERIC(12,2) NULL;


/* UPDATE SD_TEMP_SERV_CDMA_BT AS A
  SET A.AMOUNT=B.AMOUNT
 FROM REPORT.RPT_MONTHLY_CDMA_TERMINAL_ALLOWANCE_LIST_2010 AS B
WHERE A.SERV_ID=B.SERV_ID
  AND A.DISC_ID=B.DISC_ID
  AND B.MONTH_ID = @MONTH_ID
  AND B.AMOUNT > 0;
COMMIT; */

--更新口径：20160106林春芳
UPDATE SD_TEMP_SERV_CDMA_BT AS A
  SET A.AMOUNT=B.AMOUNT/100
 FROM zwfxdev.tb_ms_stamps_exchange_union AS B --表名需要小写
WHERE A.SERV_ID=B.SERV_ID
  AND A.DISC_ID=B.DISC_ID
  AND B.MONTH_ID = @MONTH_ID
  AND B.AMOUNT > 0;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 7.4:处理补贴信息：当有多个补贴时，取最近一个补贴';
    SET @START_TIME = NOW();

--删除临时表3
DROP TABLE IF EXISTS SD_TEMP_SERV_CDMA_BT_ETL;

--处理补贴信息：当有多个补贴时，取最近一个补贴
SELECT *,
  CASE WHEN DATEDIFF(MM,@NEXT_MONTH_FIRST_DAY,LIMIT_DATE) <= 0 THEN NULL ELSE DATEDIFF(MM,@NEXT_MONTH_FIRST_DAY,LIMIT_DATE) END BT_LEFT_M,
  ROW_NUMBER() OVER (PARTITION BY SERV_ID,DATA_SOUCE_DL ORDER BY LIMIT_DATE DESC) RN,
  COUNT(DISC_ID) OVER (PARTITION BY SERV_ID,DATA_SOUCE_DL) SUM_CNT,
  COUNT(CASE WHEN LIMIT_DATE > @NEXT_MONTH_FIRST_DAY THEN DISC_ID ELSE NULL END) OVER (PARTITION BY SERV_ID,DATA_SOUCE_DL) VAILD_CNT,
  COUNT(CASE WHEN LIMIT_DATE <= @NEXT_MONTH_FIRST_DAY THEN DISC_ID ELSE NULL END) OVER (PARTITION BY SERV_ID,DATA_SOUCE_DL) INVAILD_CNT
 INTO SD_TEMP_SERV_CDMA_BT_ETL
 FROM SD_TEMP_SERV_CDMA_BT;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 8:增加补贴相关信息';
    SET @START_TIME = NOW();

--增加补贴相关信息
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD 
(
 HF_BT_LIMIT_DATE DATE NULL,       --话费补贴到期时间
 ZD_BT_LIMIT_DATE DATE NULL,       --终端补贴到期时间
 HF_BT_LEFT_M NUMERIC(10) NULL,    --话费补贴到期时长
 ZD_BT_LEFT_M NUMERIC(10) NULL,    --终端补贴到期时长
 ZD_BT_FEE NUMERIC(10,2) NULL,     --终端补贴金额
 HF_BT_FEE NUMERIC(10,2) NULL,     --话费补贴金额
 ZD_SUM_CNT NUMERIC(10) NULL,      --终端补贴总次数
 ZD_VAILD_CNT NUMERIC(10) NULL,    --终端补贴有效次数
 ZD_INVAILD_CNT NUMERIC(10) NULL,  --终端补贴无效次数
 HF_SUM_CNT NUMERIC(10) NULL,      --话费补贴总次数
 HF_VAILD_CNT NUMERIC(10) NULL,    --话费补贴有效次数
 HF_INVAILD_CNT NUMERIC(10) NULL,  --话费补贴无效次数
 IS_ZD_BT_ONCE INT NULL,           --曾经是否存在终端补贴
 IS_ZD_BT INT NULL,                --当前是否存在终端补贴
 IS_HF_BT_ONCE INT NULL,           --曾经是否存在话费补贴
 IS_HF_BT INT NULL                 --当前是否存在话费补贴
);


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 8.1:更新话费补贴信息';
    SET @START_TIME = NOW();


--更新话费补贴信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.HF_BT_LIMIT_DATE = B.LIMIT_DATE,
  A.HF_BT_LEFT_M = B.BT_LEFT_M,
  A.HF_BT_FEE = B.AMOUNT,
  A.HF_SUM_CNT = B.SUM_CNT,
  A.HF_VAILD_CNT = B.VAILD_CNT,
  A.HF_INVAILD_CNT = B.INVAILD_CNT
 FROM SD_TEMP_SERV_CDMA_BT_ETL AS B
WHERE A.SERV_ID = B.SERV_ID
  AND B.RN = 1
  AND B.DATA_SOUCE_DL = '话费补贴';
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 8.2:更新终端补贴信息';
    SET @START_TIME = NOW();


--更新终端补贴信息
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.ZD_BT_LIMIT_DATE = B.LIMIT_DATE,
  A.ZD_BT_LEFT_M = B.BT_LEFT_M,
  A.ZD_BT_FEE = B.AMOUNT,
  A.ZD_SUM_CNT = B.SUM_CNT,
  A.ZD_VAILD_CNT = B.VAILD_CNT,
  A.ZD_INVAILD_CNT = B.INVAILD_CNT
 FROM SD_TEMP_SERV_CDMA_BT_ETL AS B
WHERE A.SERV_ID = B.SERV_ID
  AND B.RN = 1
  AND B.DATA_SOUCE_DL = '终端补贴';
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 8.3:更新是否补贴标识';
    SET @START_TIME = NOW();


--更新是否补贴标识
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET IS_ZD_BT_ONCE = 0,
  IS_ZD_BT = 0,
  IS_HF_BT_ONCE = 0,
  IS_HF_BT = 0;
COMMIT;


--更新曾经是否存在终端补贴
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET IS_ZD_BT_ONCE = 1
WHERE ZD_SUM_CNT > 0;
COMMIT;


--更新曾经是否存在话费补贴
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET IS_HF_BT_ONCE = 1
WHERE HF_SUM_CNT > 0;
COMMIT;


--更新当前是否存在终端补贴
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET IS_ZD_BT = 1
WHERE ZD_VAILD_CNT > 0;
COMMIT;


--更新当前是否存在话费补贴
UPDATE SD_TEMP_CDMA_BASIC_INFO 
  SET IS_HF_BT = 1
WHERE HF_VAILD_CNT > 0;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9:增加产品类型、是否融合、是否副卡、DATA_SOUCE_ID';
    SET @START_TIME = NOW();

--增加产品类型、是否融合、是否副卡、DATA_SOUCE_ID（是什么？）
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD 
(
 PROD_TYPE VARCHAR(60) NULL,     --产品类型
 PROD_TYPE1 VARCHAR(60) NULL,    --产品中类
 IS_RH INT NULL,                 --是否融合
 IS_FK INT NULL,                 --是否副卡
 DATA_SOUCE_ID NUMERIC(10) NULL, --？
 BT_DISC_ID NUMERIC(10) NULL     --补贴套餐标识
);
 
--更新产品类型
UPDATE SD_TEMP_CDMA_BASIC_INFO SET PROD_TYPE = '其它产品';
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9.1:更新是否副卡';
    SET @START_TIME = NOW();


--更新是否副卡
UPDATE SD_TEMP_CDMA_BASIC_INFO SET IS_FK = 0;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.IS_FK = 1
 FROM RPT_COMM_CM_MSDISC_2010 AS B,REPORT.DIM_YDRB_DISC AS C
WHERE B.MONTH_ID = @MONTH_ID
  AND C.DISC_TYPE = '副卡'
  AND B.DISC_ID = C.DISC_ID
  AND B.PRICE_ID = C.PRICE_ID
  AND A.SERV_ID = B.SERV_ID
  AND B.LIMIT_DATE > @MONTH_LAST_DAY;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9.2:更新是否融合';
    SET @START_TIME = NOW();


--更新是否融合
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
SET A.IS_RH = 0;
COMMIT;
 
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.IS_RH = 1 
 FROM RPT_COMM_CM_MSDISC_2010 AS B,RPT_COMM_CM_MSDISC_2010 AS C
WHERE A.SERV_ID = B.SERV_ID
  AND B.MSINFO_ID = C.MSINFO_ID
  AND B.MONTH_ID = @MONTH_ID
  AND C.MONTH_ID = @MONTH_ID
  AND C.PROD_ID = ANY(SELECT PROD_ID FROM DIM_PROD WHERE STAT_CAT_ID IN( 0,1,2,7,8,9 ) ) --关联固话或者宽带(非ITV)
  AND B.PROD_ID IN (3204,3205 ) ;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9.3:更新DATA_SOUCE_ID,补贴套餐标识';
    SET @START_TIME = NOW();


--更新DATA_SOUCE_ID,补贴套餐标识
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A SET A.DATA_SOUCE_ID = 0;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.DATA_SOUCE_ID = B.DATA_SOUCE_ID,
  A.BT_DISC_ID = B.DISC_ID
 FROM SD_TEMP_SERV_CDMA_BT AS B,SD_TEMP_SERV_CDMA_BT_MLEVEL AS C
WHERE A.SERV_ID = B.SERV_ID
  AND B.SERV_ID = C.SERV_ID
  AND B.DISC_LEVEL = C.DISC_LEVEL;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.DATA_SOUCE_ID = 11 
 FROM RPT_COMM_CM_MSDISC_2010 AS B,REPORT.DIM_YDRB_DISC AS C,REPORT.RPT_DAILY_CDMA_TERMINAL_DISC_V2 AS D
WHERE B.MONTH_ID = @MONTH_ID
  AND C.DISC_TYPE = '合约融合'
  AND B.DISC_ID = C.DISC_ID
  AND B.PRICE_ID = C.PRICE_ID
  AND B.CREATE_DATE >= C.MOD_DATE --7月竣工开始计算
  AND B.LIMIT_DATE > @MONTH_LAST_DAY
  AND A.SERV_ID = B.SERV_ID
  AND A.BT_DISC_ID = D.DISC_ID
  AND D.IS_DCP_HY = 1; --单产品合约套餐
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9.4:更新产品类型';
    SET @START_TIME = NOW();


--更新产品类型
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PROD_TYPE = CASE WHEN A.IS_RH = 0 AND A.PROD_ID = 3205 THEN '后付费单产品'
 WHEN A.IS_RH = 0 AND(A.PROD_ID <> 3205 OR A.PROD_ID IS NULL) THEN '预付费单产品'
 WHEN A.IS_RH = 1 AND A.SERV_GRP_TYPE = '01' THEN '政企融合'
 WHEN A.IS_RH = 1 AND(A.SERV_GRP_TYPE <> '01' OR A.SERV_GRP_TYPE IS NULL) THEN '家庭融合' END;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PROD_TYPE = '无线宽带'
WHERE A.WIRELESS_TYPE > 0;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 9.5:更新产品中类(口径1)';
    SET @START_TIME = NOW();


--更新产品中类(口径1)
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A SET A.PROD_TYPE1 = A.PROD_TYPE;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PROD_TYPE1 = CASE WHEN A.DATA_SOUCE_ID IN( 1,2,3,4) THEN '合约计划'
  WHEN A.DATA_SOUCE_ID = 6 THEN '乐享5折'
  ELSE PROD_TYPE1
  END 
WHERE A.PROD_TYPE1 = '后付费单产品';
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PROD_TYPE1 = '合约计划'
WHERE A.DATA_SOUCE_ID = 11
  AND A.PROD_TYPE IN( '后付费单产品','政企融合','家庭融合' ) 
  AND A.PROD_ID = 3205;
COMMIT;


UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.PROD_TYPE1 = '副卡'
WHERE A.IS_FK = 1;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 10:增加客户信息：1、在网时长（取客户下最早产品的在网时长）;2、客户下移动号码数';
    SET @START_TIME = NOW();

--增加客户信息：1、在网时长（取客户下最早产品的在网时长）;2、客户下移动号码数
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD 
 (
  KH_ONLINE_TIME NUMERIC(10) NULL  --在网时长
 ,NBR_COUNT NUMERIC(10) NULL       --移动号码数
 );


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 10.1:更新客户在网时长';
    SET @START_TIME = NOW();

--获取客户在网时长
--汇总在用用户表，结果插入临时表
DROP TABLE IF EXISTS SD_TEMP_CUST_ONLINE_TIME;
SELECT CUST_ID,MAX(ONLINE_TIME) AS ONLINE_TIME
 INTO SD_TEMP_CUST_ONLINE_TIME
 FROM RPT_COMM_CM_SERV_2010
WHERE MONTH_ID = @MONTH_ID
GROUP BY CUST_ID; 
COMMIT;


--更新客户在网时长
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.KH_ONLINE_TIME = B.ONLINE_TIME
 FROM SD_TEMP_CUST_ONLINE_TIME AS B
WHERE A.CUST_ID = B.CUST_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 10.2:更新号码数';
    SET @START_TIME = NOW();


--获取客户下移动号码数量,插入临时表
DROP TABLE IF EXISTS SD_TEMP_CUST_NBR_COUNT;
SELECT CUST_ID,COUNT(DISTINCT SERV_ID) AS NBR_COUNT
 INTO SD_TEMP_CUST_NBR_COUNT
 FROM SD_TEMP_CDMA_BASIC_INFO
WHERE PROD_ID IN (3204,3205)
GROUP BY CUST_ID;
COMMIT;


--更新号码数
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.NBR_COUNT = B.NBR_COUNT
 FROM SD_TEMP_CUST_NBR_COUNT AS B
WHERE A.CUST_ID = B.CUST_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11:增加终端信息';
    SET @START_TIME = NOW();

--增加终端信息
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD
(
 BRAND VARCHAR(100) NULL,                 --本月在用终端
 TERM_TYPE VARCHAR(60) NULL,              --本月在用终端类型(1X,3G,4G)
 TERM_DUR NUMERIC(10) NULL,               --本月在用终端使用时长
 TERM_PRICE NUMERIC(10) NULL,             --本月在用终端价格
 TERM_CNT NUMERIC(10) NULL,               --该用户三年内使用终端数量
 BRAND_LAST1 VARCHAR(100) NULL,           --上个终端
 TERM_TYPE_LAST1 VARCHAR(60) NULL,        --上个终端类型
 TERM_DUR_LAST1 NUMERIC(10) NULL,         --上个终端类型使用时长
 TERM_PRICE_LAST1 NUMERIC(10) NULL,       --上个终端价格
 BRAND_LAST2 VARCHAR(100) NULL,           --上上个终端
 TERM_TYPE_LAST2 VARCHAR(60) NULL,        --上上个终端类型
 TERM_DUR_LAST2 NUMERIC(10) NULL,         --上上个终端类型使用时长
 TERM_PRICE_LAST2 NUMERIC(10) NULL,       --上上个终端价格
 BRAND_LAST3 VARCHAR(100) NULL,           --上上上个终端
 TERM_TYPE_LAST3 VARCHAR(60) NULL,        --上上上个终端类型
 TERM_DUR_LAST3 NUMERIC(10) NULL,         --上上上个终端类型使用时长
 TERM_PRICE_LAST3 NUMERIC(10) NULL,       --上上上个终端价格
 PRE_PHONE_STRCODE VARCHAR(255) NULL,     --上个月使用终端串号
 PHONE_STRCODE VARCHAR(255) NULL,         --本月使用终端串号
 NEXT_PHONE_STRCODE VARCHAR(255) NULL,    --下个月使用终端串号
 IS_HJ INT NULL                           --本月是否换机
);


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.1:更新本月终端及串号';
    SET @START_TIME = NOW();
 
--更新本月终端及串号
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.BRAND = B.BRAND,
      A.TERM_TYPE = B.BRAND_TYPE,
  A.PHONE_STRCODE = B.PHONE_STRCODE
 FROM V_RPT_TERMINAL_TYPE AS B
WHERE A.ACC_NBR = B.ACC_NBR
  AND B.MONTH_ID = @MONTH_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.2:更新本月终端使用时长';
    SET @START_TIME = NOW();


--更新本月终端使用时长,价格
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.TERM_DUR = DATEDIFF(MM,B.REGISTER_TIME,@MONTH_LAST_DAY)
     ,A.TERM_PRICE = B.TERMINAL_PRICE
 FROM BUF_CMDA_TERMINAL_INFO AS B
WHERE A.SERV_ID = B.SERV_ID
  AND A.ACC_NBR = B.ACC_NBR
  AND A.PHONE_STRCODE = B.PHONE_STRCODE;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.3:更新本月终端价格';
    SET @START_TIME = NOW();


--更新本月终端价格
UPDATE SD_TEMP_CDMA_BASIC_INFO 
       SET BRAND = STR_REPLACE(BRAND ,' ','-')  WHERE BRAND LIKE '% %' ;
    COMMIT;
   
    UPDATE SD_TEMP_CDMA_BASIC_INFO AS A 
       SET A.TERM_PRICE = B.TERMINAL_PRICE_0 -- ISNULL(A.ZD_BT_FEE,0)
 FROM T_DX_JX_11 AS B
WHERE A.BRAND = B.TERMINAL_TYPE AND A.TERM_PRICE IS NULL;
    COMMIT;
   
    UPDATE SD_TEMP_CDMA_BASIC_INFO AS A 
       SET A.TERM_PRICE = B.TERMINAL_PRICE -- ISNULL(A.ZD_BT_FEE,0)
 FROM T_DX_JX_11_1 AS B
WHERE A.BRAND = B.TERMINAL_TYPE AND A.TERM_PRICE IS NULL;
    COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.4:获取上月终端';
    SET @START_TIME = NOW();

--获取上月终端
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
SET A.PRE_PHONE_STRCODE = B.PHONE_STRCODE
 FROM V_RPT_TERMINAL_TYPE AS B
WHERE A.ACC_NBR = B.ACC_NBR
AND B.MONTH_ID = @PRE_MONTH_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.5:获取下月终端';
    SET @START_TIME = NOW();

--获取下月终端
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
SET A.NEXT_PHONE_STRCODE = B.PHONE_STRCODE
 FROM V_RPT_TERMINAL_TYPE AS B
WHERE A.ACC_NBR = B.ACC_NBR
AND B.MONTH_ID = @NEXT_MONTH_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.6:更新IS_HJ';
    SET @START_TIME = NOW();

--初始化IS_HJ字段
UPDATE SD_TEMP_CDMA_BASIC_INFO
SET IS_HJ = 0;
    COMMIT;

--更新IS_HJ(确认换机)
UPDATE SD_TEMP_CDMA_BASIC_INFO
SET IS_HJ = 1
WHERE PRE_PHONE_STRCODE <> PHONE_STRCODE AND PHONE_STRCODE = NEXT_PHONE_STRCODE;
COMMIT;
 
--更新IS_HJ(未真正换机)
UPDATE SD_TEMP_CDMA_BASIC_INFO
SET IS_HJ = 2
WHERE PRE_PHONE_STRCODE <> PHONE_STRCODE AND PHONE_STRCODE <> NEXT_PHONE_STRCODE;
COMMIT;  


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.7:更新TERM_CNT';
    SET @START_TIME = NOW();

--创建临时表，获取近3个月终端使用个数
DROP TABLE IF EXISTS SD_TEMP_BUF_CDMA_TERM_CNT;
SELECT SERV_ID,COUNT(DISTINCT PHONE_STRCODE) CNT
 INTO SD_TEMP_BUF_CDMA_TERM_CNT
 FROM BUF_CMDA_TERMINAL_INFO
WHERE REGISTER_TIME <= @MONTH_LAST_DAY
  AND REGISTER_TIME > DATEFORMAT(DATEADD(YY,-3,@MONTH_LAST_DAY),'YYYY-MM-DD')
GROUP BY SERV_ID;
COMMIT;


--更新TERM_CNT
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.TERM_CNT = B.CNT
 FROM SD_TEMP_BUF_CDMA_TERM_CNT AS B
WHERE A.SERV_ID = B.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.8:创建临时表，获取用户本用终端排序序号';
    SET @START_TIME = NOW();

--创建临时表，获取用户本用终端排序序号
DROP TABLE IF EXISTS SD_TEMP_BUF_CDMA_TERM_RANK;
SELECT A.SERV_ID,MIN(RANK_ID) RANK_ID
 INTO SD_TEMP_BUF_CDMA_TERM_RANK
 FROM BUF_CMDA_TERMINAL_INFO AS A,SD_TEMP_CDMA_BASIC_INFO AS B
WHERE A.REGISTER_TIME <= @MONTH_LAST_DAY
  AND A.SERV_ID = B.SERV_ID
  AND A.PHONE_STRCODE <> B.PHONE_STRCODE
GROUP BY A.SERV_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.9:更新上个终端信息，序号为上一步得到的最小序号+1';
    SET @START_TIME = NOW();

--更新上个终端信息，序号为上一步得到的最小序号+1
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.BRAND_LAST1 = B.BRAND,
  A.TERM_TYPE_LAST1 = B.BRAND_TYPE,
  A.TERM_DUR_LAST1 = DATEDIFF(MM,B.REGISTER_TIME,@MONTH_LAST_DAY)- A.TERM_DUR,
  A.TERM_PRICE_LAST1 = B.TERMINAL_PRICE
 FROM BUF_CMDA_TERMINAL_INFO AS B,SD_TEMP_BUF_CDMA_TERM_RANK AS C
WHERE A.SERV_ID = B.SERV_ID
  AND B.SERV_ID = C.SERV_ID
  AND B.RANK_ID = C.RANK_ID;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.10:更新上上个终端信息，序号为上一步得到的最小序号+2';
    SET @START_TIME = NOW();


--更新上上个终端信息，序号为上一步得到的最小序号+2
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.BRAND_LAST2 = B.BRAND,
  A.TERM_TYPE_LAST2 = B.BRAND_TYPE,
  A.TERM_DUR_LAST2 = DATEDIFF(MM,B.REGISTER_TIME,@MONTH_LAST_DAY)- A.TERM_DUR - A.TERM_DUR_LAST1,
  A.TERM_PRICE_LAST2 = B.TERMINAL_PRICE
 FROM BUF_CMDA_TERMINAL_INFO AS B,SD_TEMP_BUF_CDMA_TERM_RANK AS C
WHERE A.SERV_ID = B.SERV_ID
  AND B.SERV_ID = C.SERV_ID
  AND B.RANK_ID = C.RANK_ID + 1;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 11.11:更新上上上个终端信息，序号为上一步得到的最小序号+3';
    SET @START_TIME = NOW();


--更新上上上个终端信息，序号为上一步得到的最小序号+3
UPDATE SD_TEMP_CDMA_BASIC_INFO AS A
  SET A.BRAND_LAST3 = B.BRAND,
  A.TERM_TYPE_LAST3 = B.BRAND_TYPE,
  A.TERM_DUR_LAST3 = DATEDIFF(MM,B.REGISTER_TIME,@MONTH_LAST_DAY)- A.TERM_DUR - A.TERM_DUR_LAST1 - A.TERM_DUR_LAST2,
  A.TERM_PRICE_LAST3 = B.TERMINAL_PRICE
 FROM BUF_CMDA_TERMINAL_INFO AS B,SD_TEMP_BUF_CDMA_TERM_RANK AS C
WHERE A.SERV_ID = B.SERV_ID
  AND B.SERV_ID = C.SERV_ID
  AND B.RANK_ID = C.RANK_ID + 2;
COMMIT;

--增加新终端价格字段，已经废弃，新增是为了保留结构一至
ALTER TABLE SD_TEMP_CDMA_BASIC_INFO ADD TERM_PRICE_NEW NUMERIC(10) NULL;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());

SET @STEP_LOGS = 'STEP 12:重命名目标表';
    SET @START_TIME = NOW();

SET @SCRIPT = 'ALTER TABLE SD_TEMP_CDMA_BASIC_INFO RENAME BUF_SERV_CDMA_BASIC_INFO_'||@MONTH_ID ||'';

EXECUTE IMMEDIATE @SCRIPT;
COMMIT;

TRUNCATE TABLE BUF_SERV_CDMA_BASIC_INFO;

SET @SCRIPT = 'INSERT INTO BUF_SERV_CDMA_BASIC_INFO SELECT * FROM BUF_SERV_CDMA_BASIC_INFO_'||@MONTH_ID ||'';

EXECUTE IMMEDIATE @SCRIPT;
COMMIT;


    SET @ROW_CNT = @@ROWCOUNT;
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,1,SQLCODE,ERRORMSG());
    
EXCEPTION
  WHEN OTHERS THEN
    CALL P_PUB_ERROR_LOG(@OBJECT_NAME,@STEP_LOGS,@START_TIME,@ROW_CNT,-1,SQLCODE,ERRORMSG());
    ROLLBACK;
    
END;