create table TBL_WLF_SYS_LOG

(

      S_TIME            VARCHAR2() not null,

    S_LEVEL            VARCHAR2(),

    S_PROCNAME        VARCHAR2(),

    S_MSG            VARCHAR2(),

    S_ADVICE        VARCHAR2()

)

tablespace TBS_WLF_DAT;

-- Add comments to the table

comment on table TBL_WLF_SYS_LOG

  is '存储过程日志表';

-- Add comments to the columns

comment on column TBL_WLF_SYS_LOG.S_TIME

  is '操作时间';

-- Add comments to the columns

comment on column TBL_WLF_SYS_LOG.S_LEVEL

  is '操作级别';

-- Add comments to the columns

comment on column TBL_WLF_SYS_LOG.S_PROCNAME

  is '执行存储过程名称';

-- Add comments to the columns

comment on column TBL_WLF_SYS_LOG.S_MSG

  is '错误信息';

-- Add comments to the columns

comment on column TBL_WLF_SYS_LOG.S_ADVICE

  is '建议信息';

CREATE OR REPLACE PROCEDURE VCODE.prc_wlf_sys_writelog(

  i_flag       INTEGER,

  i_id         INTEGER,

  str_procname varchar2,

  str_msg      varchar2,

  str_advice   varchar2

) IS

  -- 操作时间

  str_time   varchar2(32);

  -- 操作级别

  str_level  varchar2(32);

  -- 执行存储过程名称

  p_procname varchar2(1024);

  -- 错误信息，或者记录信息

  p_msg      varchar2(1024);

  -- 建议信息

  p_advice   varchar2(1024);

BEGIN

  IF (i_flag = 2 AND i_id >= 1 AND i_id <= 4) THEN

    CASE

      WHEN i_id = 1 THEN

        str_level := 'log';

      WHEN i_id = 2 THEN

        str_level := 'debug';

      WHEN i_id = 3 THEN

        str_level := 'alarm';

      ELSE

        str_level := 'error';

    END CASE;

    p_procname := str_procname;

    p_msg      := str_msg;

    p_advice   := str_advice;

  ELSE

    str_level  := 'error';

    p_procname := 'p_public_writelog';

    p_msg      := 'writelog_error';

    p_advice   := '';

  END IF;

  str_time := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');

  INSERT INTO tbl_wlf_sys_log

    (s_time, s_level, s_procname, s_msg, s_advice)

  VALUES

    (str_time, str_level, p_procname, p_msg, p_advice);

  COMMIT;

END prc_wlf_sys_writelog;

/

CREATE OR REPLACE procedure VCODE.PROC_CUSTOM_RECORD_UPDATE

is

  -- debug信息

    v_debugmsg      varchar2(1024);

  -- 错误信息

    v_errmsg      varchar2(1024);

  -- 查询用户邀请活动信息表 获取活动开始与结束时间

  cursor ACTIVITY_CUR

    is --声明显式游标

      select T.ACTIVITYID,

        T.COUNTSTARTTIME,

        T.COUNTENDTIME

      from vcode.T_INVITE_ACTIVITYINFO T

      where T.HASCOUNTTIME = 1;

  --定义游标变量，该变量的类型为基于游标ACTIVITY_CUR的记录

  type ACTIVITY_CUR_ROW is table of ACTIVITY_CUR%ROWTYPE;

    cs_invitestat SYS_REFCURSOR;

  type tp_CUSTOM_RECORD is table of T_INVITER_CUSTOM_RECORD%ROWTYPE;

  va_CUSTOM_RECORD tp_CUSTOM_RECORD;

  ACTIVITY_ID varchar2(50);

  START_TIME  date;

  END_TIME    date;

begin

  -- 存储过程开始日志

  v_debugmsg := 'VCODE.PROC_CUSTOM_RECORD_UPDATE begin  log- ';

  prc_wlf_sys_writelog(2, 2, 'PROC_CUSTOM_RECORD_UPDATE', v_debugmsg, '');

  --For 循环  遍历用户邀请活动信息表，根据活动开始结束时间间隔获取数据信息

  for ACTIVITY_CUR_ROW in ACTIVITY_CUR

  LOOP

  ACTIVITY_ID := ACTIVITY_CUR_ROW.ACTIVITYID;

    START_TIME  := ACTIVITY_CUR_ROW.COUNTSTARTTIME;

    -- 取当天的最后一秒

    select TRUNC(ACTIVITY_CUR_ROW.COUNTENDTIME+1)-1/(24*3600)

    into END_TIME

    from DUAL;

    -- 根据活动ID，开始时间，结束时间，查询被邀请人记录表获取邀请人激活人数与最后激活时间，查询奖励记录表获取书券奖励，并关联一起。

    open cs_invitestat for

    SELECT t4.INVITERMSISDN,COUNT(1) AS TOTALACTIVENUMBER,max(t4.ACTIVETIME) AS LASTACTIVETIME,t4.ACTIVITYID,CASE WHEN SUM(t3.PRIZENUM) is null THEN 0 ELSE SUM(t3.PRIZENUM) END  totalTicket

    FROM (

      SELECT t1.INVITERMSISDN,t1.INVITEEMSISDN,t1.ACTIVETIME,t1.ACTIVITYID   FROM T_INVITEE_RECORD t1

        WHERE t1.ACTIVITYID=ACTIVITY_ID

                  and t1.ACTIVESTATUS = 1

                  and t1.INVITEETYPE = 0

                  and t1.ACTIVETIME <= END_TIME

                  and t1.ACTIVETIME    >= START_TIME ) t4

     LEFT JOIN

     (SELECT t2.INVITERMSISDN,t2.INVITEEMSISDN,SUM(t2.PRIZENUM) PRIZENUM,t2.ACTIVETIME

      FROM T_INVITING_AWARDS_RECORD t2

       WHERE t2.PRIZETYPE=3 AND t2.ISSEND IN (0,1,2,3,10)

            AND t2.ACTIVITYID=ACTIVITY_ID

            and t2.ACTIVETIME <= END_TIME

            and t2.ACTIVETIME    >= START_TIME

            and t2.REWARDTYPE = 0

            group by t2.ACTIVETIME,t2.INVITEEMSISDN,t2.INVITERMSISDN) t3

    ON t4.INVITERMSISDN=t3.INVITERMSISDN  AND t4.INVITEEMSISDN=t3.INVITEEMSISDN   AND   t4.ACTIVETIME=t3.ACTIVETIME

    group by t4.ACTIVITYID,t4.INVITERMSISDN

    ORDER BY TOTALACTIVENUMBER desc,LASTACTIVETIME ASC;

    fetch cs_invitestat bulk collect into va_CUSTOM_RECORD limit 500;

    -- 遍历结果，并插入T_INVITER_CUSTOM_RECORD 自定义排行表中，如果存在数据则更新（邀请人、活动ID相同），不存在则插入

    forall i in 1..va_CUSTOM_RECORD.count

    merge into vcode.T_INVITER_CUSTOM_RECORD T5

      using (select * from dual)

      on (INVITERMSISDN = va_CUSTOM_RECORD(i).INVITERMSISDN AND ACTIVITYID=va_CUSTOM_RECORD(i).ACTIVITYID)

    when matched then

        update

          set TOTALACTIVENUMBER      =va_CUSTOM_RECORD(i).TOTALACTIVENUMBER,

            LASTACTIVETIME           =va_CUSTOM_RECORD(i).LASTACTIVETIME,

            TOTALTICKET           =va_CUSTOM_RECORD(i).TOTALTICKET

          where T5.TOTALACTIVENUMBER!=va_CUSTOM_RECORD(i).TOTALACTIVENUMBER OR T5.TOTALTICKET!=va_CUSTOM_RECORD(i).TOTALTICKET

    when not matched then

        insert

          (

            INVITERMSISDN,

            TOTALACTIVENUMBER,

            LASTACTIVETIME,

            ACTIVITYID,

            TOTALTICKET

          )

          values

          (

            va_CUSTOM_RECORD(i).INVITERMSISDN,

            va_CUSTOM_RECORD(i).TOTALACTIVENUMBER,

            va_CUSTOM_RECORD(i).LASTACTIVETIME,

            va_CUSTOM_RECORD(i).ACTIVITYID,

            va_CUSTOM_RECORD(i).TOTALTICKET

          );

        commit;

  end LOOP;

  -- 存储过程开始日志

  v_debugmsg := 'VCODE.PROC_CUSTOM_RECORD_UPDATE end  log- ';

  prc_wlf_sys_writelog(2, 2, 'PROC_CUSTOM_RECORD_UPDATE', v_debugmsg, '');

exception

  when others then

    begin

      rollback;

      v_errmsg := 'sqlexception~~sqlcode:' || to_char(sqlcode) ||

                  ' sqlstate:' || substr(sqlerrm, 1, 512);

      prc_wlf_sys_writelog(2, 4, 'PROC_CUSTOM_RECORD_UPDATE', v_errmsg, '');

    end;

end;

/