-- Created by 10169291 on 2017/4/1, 10:03:35 --------
select 'set sql_mode=ANSI' as prompt;
set sql_mode=ANSI;
select 'change database to zxinsag' as prompt;
use zxinsag;
select 'Dropping TL_NORTH_SUBID_PHONE...' as prompt;
drop table if exists TL_NORTH_SUBID_PHONE;
select 'Dropping TL_NORTH_SUBSCRIBE...' as prompt;
drop table if exists TL_NORTH_SUBSCRIBE;
select 'Dropping TL_NORTH_SUBSCRIBE_UPDATE...' as prompt;
drop table if exists TL_NORTH_SUBSCRIBE_UPDATE;
select 'Dropping TL_SOUTH_SUBSCRIBE...' as prompt;
drop table if exists TL_SOUTH_SUBSCRIBE;
select 'Dropping TO_SOUTH_SUBSCRIBE...' as prompt;
drop table if exists TO_SOUTH_SUBSCRIBE;
select 'Dropping TP_CALLEVENT_DEF...' as prompt;
drop table if exists TP_CALLEVENT_DEF;
select 'Dropping TP_DIRECTION_DEF...' as prompt;
drop table if exists TP_DIRECTION_DEF;
select 'Dropping TP_NOTIFY_MODE_DEF...' as prompt;
drop table if exists TP_NOTIFY_MODE_DEF;
select 'Dropping TT_NORTH_SUB_COMPARE...' as prompt;
drop table if exists TT_NORTH_SUB_COMPARE;
select 'Dropping TT_SOUTH_SUB_RESULT...' as prompt;
drop table if exists TT_SOUTH_SUB_RESULT;
select 'Dropping TT_SUBSCRIBE_EVENT...' as prompt;
drop table if exists TT_SUBSCRIBE_EVENT;
select 'Dropping TT_SUBSCRIBE_PHONE...' as prompt;
drop table if exists TT_SUBSCRIBE_PHONE;
-- modify by LC 20171027 北向号码表减少各种事件和每个号码南向操作的返回后果
select 'Creating table TL_NORTH_SUBID_PHONE' as prompt;
create table TL_NORTH_SUBID_PHONE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
n_subid VARCHAR(64) not null,
phone VARCHAR(32) not null,
direct_mo INT,
direct_mt INT,
ev_begin INT,
ev_ringing INT,
ev_answer INT,
ev_busy INT,
ev_notreachable INT,
ev_routefailure INT,
ev_noanswer INT,
ev_abandon INT,
ev_release INT,
ev_exception INT,
notify_block INT,
rltcode int,
opresult int COMMENT '每个号码南向操作返回的后果 0-订阅胜利 1-订阅正在同步 2-订阅失败 3-更新胜利 4-正在更新 5-更新失败 6-删除胜利 7-正在删除 8-删除失败' -- add by LC 20171027
)
;
create index IDX_NORTH_PHONE on TL_NORTH_SUBID_PHONE (PHONE);
create index IDX_NORTH_SUBID on TL_NORTH_SUBID_PHONE (N_SUBID);
select 'Creating table TL_NORTH_SUBSCRIBE' as prompt;
CREATE TABLE TL_NORTH_SUBSCRIBE
(
intime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
n_subid VARCHAR(64) NOT NULL COMMENT '北向订阅ID',
appid VARCHAR(32),
sepid VARCHAR(64),
platform INT,
direct_mo INT,
direct_mt INT,
ev_begin INT,
ev_ringing INT,
ev_answer INT,
ev_busy INT,
ev_notreachable INT,
ev_routefailure INT,
ev_noanswer INT,
ev_abandon INT,
ev_release INT,
ev_exception INT,
notify_block INT,
rltcode INT,
constraint pk_uag_sys_code primary key (n_subid)
)
;
select 'Creating table TL_NORTH_SUBSCRIBE_UPDATE' as prompt;
create table TL_NORTH_SUBSCRIBE_UPDATE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
dataid int not null,
n_subid VARCHAR(64) not null,
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int
)
;
select 'Creating table TL_SOUTH_SUBSCRIBE' as prompt;
create table TL_SOUTH_SUBSCRIBE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
s_subid VARCHAR(64) not null COMMENT '南向订阅ID',
netcode VARCHAR(64) not null COMMENT '南向网元CODE',
phone VARCHAR(32) not null COMMENT '用户号码',
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int COMMENT '是否BLOCK',
constraint PK_SOUTH_SUBSCRIBE primary key (S_SUBID, NETCODE),
constraint UNQ_SOUTH_PHONE unique (PHONE)
)
;
select 'Creating table TO_SOUTH_SUBSCRIBE' as prompt;
create table TO_SOUTH_SUBSCRIBE
(
intime TIMESTAMP default CURRENT_TIMESTAMP,
dataid int,
dataidx int,
optype int,
s_subid VARCHAR(64),
netcode VARCHAR(64),
phone VARCHAR(32),
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int,
iftosouth int COMMENT '是否向南向同步 0-不同步 1-同步'
)
;
select 'Creating table TP_CALLEVENT_DEF' as prompt;
create table TP_CALLEVENT_DEF
(
callevent_id int not null,
description VARCHAR(100) not null,
col_index int,
constraint PK_CALLEVENT_ID primary key (CALLEVENT_ID),
constraint UNQ_CALLEVENT_DESCRIPTION unique (DESCRIPTION)
)
;
select 'Creating table TP_DIRECTION_DEF' as prompt;
create table TP_DIRECTION_DEF
(
direction_id int not null,
description VARCHAR(100) not null,
constraint PK_DIRECTION_ID primary key (DIRECTION_ID),
constraint UNQ_DIRECTION_DESCRIPTION unique (DESCRIPTION)
)
;
select 'Creating table TP_NOTIFY_MODE_DEF' as prompt;
create table TP_NOTIFY_MODE_DEF
(
notify_mode int not null,
description VARCHAR(100) not null,
constraint PK_NOTIFY_MODE primary key (NOTIFY_MODE),
constraint UNQ_NOTIFY_DESCRIPTION unique (DESCRIPTION)
)
;
-- modify by LC 20170828 以前创立的是长期表，现改为一般表
select 'Creating table TT_NORTH_SUB_COMPARE' as prompt;
CREATE TABLE TT_NORTH_SUB_COMPARE
(
phone VARCHAR(32) not null,
direct_mo int,
direct_mt int,
ev_begin int,
ev_ringing int,
ev_answer int,
ev_busy int,
ev_notreachable int,
ev_routefailure int,
ev_noanswer int,
ev_abandon int,
ev_release int,
ev_exception int,
notify_block int,
optype int comment '0-new,1-update,2-delete',
dataflag int comment '1-before,2-after'
)
;
create index IDX_NORTH_SUB_COMP_PHONE on TT_NORTH_SUB_COMPARE (PHONE);
-- modify by LC 20170828 以前创立的是长期表，现改为一般表
select 'Creating table TT_SOUTH_SUB_RESULT' as prompt;
CREATE TABLE TT_SOUTH_SUB_RESULT
(
s_subid VARCHAR(64),
netcode VARCHAR(64),
phone VARCHAR(32) not null,
rltcode VARCHAR(10),
constraint UNQ_SOUTH_RESULT_PHONE unique (PHONE)
)
;
-- modify by LC 20170828 以前创立的是长期表，现改为一般表
select 'Creating table TT_SUBSCRIBE_EVENT' as prompt;
CREATE TABLE TT_SUBSCRIBE_EVENT
(
event_description VARCHAR(32) not null
)
;
-- modify by LC 20170828 以前创立的是长期表，现改为一般表
select 'Creating table TT_SUBSCRIBE_PHONE' as prompt;
CREATE TABLE TT_SUBSCRIBE_PHONE
(
phone VARCHAR(32) not null,
constraint UNQ_TT_SUBSCRIBE_PHONE unique (PHONE)
)
;
-- 序列批改为表中读取数据
-- select 'Creating sequence SEQ_DATAID' as prompt;
-- create sequence SEQ_DATAID
-- minvalue 1
-- maxvalue 999999999
-- start with 1
-- increment by 1
-- cache 10
-- cycle;
-- 创立序列表
select 'Creating table zxinsag.sequence_tab' as prompt;
drop table IF EXISTS zxinsag.sequence_tab;
CREATE TABLE zxinsag.sequence_tab (
seq_name VARCHAR(50) NOT NULL, -- 序列名称
current_val BIGINT NOT NULL, -- 以后值
increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度)
CONSTRAINT PK_SEQUENCE PRIMARY KEY (seq_name)
);
INSERT INTO zxinsag.sequence_tab (seq_name, current_val, increment_val) VALUES ('SEQ_DATAID',1,1);
COMMIT;
-- 读取以后序列值
select 'Creating FUNCTION zxinsag.currval' as prompt;
DROP FUNCTION IF EXISTS zxinsag.currval;
DELIMITER //
CREATE FUNCTION zxinsag.currval(v_seq_name VARCHAR(50))
RETURNS INTEGER
BEGIN
DECLARE VALUE INTEGER; SET VALUE = 0; SELECT current_val INTO VALUE FROM sequence_tab WHERE seq_name = v_seq_name; RETURN VALUE;
END
//
delimiter ;
-- 获取下一步序列值
select 'Creating FUNCTION zxinsag.nextval' as prompt;
DROP FUNCTION IF EXISTS zxinsag.nextval;
DELIMITER //
CREATE FUNCTION zxinsag.nextval(v_seq_name VARCHAR(50),v_step INT)
RETURNS INTEGER
BEGIN
DECLARE VALUE INTEGER; UPDATE sequence_tab SET current_val=current_val+(increment_val * v_step) WHERE seq_name = v_seq_name; SET VALUE = currval(v_seq_name);IF VALUE > 999999999 THEN UPDATE sequence_tab SET current_val = 1 WHERE seq_name = v_seq_name; END IF;RETURN currval(v_seq_name); END
//
delimiter ;
select 'Creating view VW_NORTH_SUBSCRIBE' as prompt;
DROP VIEW IF EXISTS vw_north_subscribe;
CREATE VIEW vw_north_subscribe AS
SELECT
a.phone, b.n_subid, b.appid, IF((b.ev_begin = 1),'Begin',NULL) ev_begin, IF((b.ev_ringing = 1),'Ringing',NULL) ev_ringing, IF((b.ev_answer = 1),'Answer',NULL) ev_answer, IF((b.ev_busy = 1),'Busy',NULL) ev_busy, IF((b.ev_notreachable = 1),'Not Reachable',NULL) ev_notreachable, IF((b.ev_routefailure = 1),'Route Failure',NULL) ev_routefailure, IF((b.ev_noanswer = 1),'No Answer',NULL) ev_noanswer, IF((b.ev_abandon = 1),'Abandon',NULL) ev_abandon, IF((b.ev_release = 1),'Release',NULL) ev_release, IF((b.ev_exception = 1),'Exception',NULL) ev_exception, IF((b.direct_mo = 1),IF((b.direct_mt = 1),'Both','MO'),'MT') direction, IF((b.notify_block = 1),'Block','Notify') notify_block, IF(ISNULL(b.rltcode),1,IF((b.rltcode = 0),0,2)) rltcodeFROM tl_north_subid_phone a
JOIN tl_north_subscribe b
ON a.n_subid = b.n_subid;
/*select 'Creating view VW_NORTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS vw_north_subscribe_out;
create view vw_north_subscribe_out as
SELECT a.phone,
a.n_subid, b.platform, b.appid, IF((b.ev_begin = 1), 'Begin', NULL) ev_begin, IF((b.ev_ringing = 1), 'Ringing', NULL) ev_ringing, IF((b.ev_answer = 1), 'Answer', NULL) ev_answer, IF((b.ev_busy = 1), 'Busy', NULL) ev_busy, IF((b.ev_notreachable = 1), 'Not Reachable', NULL) ev_notreachable, IF((b.ev_routefailure = 1), 'Route Failure', NULL) ev_routefailure, IF((b.ev_noanswer = 1), 'No Answer', NULL) ev_noanswer, IF((b.ev_abandon = 1), 'Abandon', NULL) ev_abandon, IF((b.ev_release = 1), 'Release', NULL) ev_release, IF((b.ev_exception = 1), 'Exception', NULL) ev_exception, IF((b.direct_mo = 1), IF((b.direct_mt = 1), 'Both', 'MO'), 'MT') direction, IF((b.notify_block = 1), 'Block', 'Notify') notify_block, c.s_subid, c.netcode, e.aspid, '' notify_urlFROM tl_north_subid_phone a
JOIN tl_north_subscribe b
ON a.n_subid = b.n_subidJOIN tl_south_subscribe c
ON c.phone = a.phoneJOIN zxsdp_op.appbasic d
ON b.appid = d.appidJOIN zxsdp_op.aspbasic e
ON d.aspindex = e.aspindexWHERE a.rltcode = 0;*/
-- 订阅相干视图批改 数据库 zxinsag 配合苏研批改 将 zxsdp_op.appbasic d JOIN zxsdp_op.aspbasic e on d.aspindex = e.aspindex 改为视图 zxsdp_op.uag_ucp_app
-- 20171107 modify by LC 一级平台进行订阅时对应的APPID是不存在的，要在视图 vw_north_subscribe_out 视图中展现这种记录 因而将 "JOIN zxsdp_op.uag_ucp_app d" 改为 "LEFT JOIN zxsdp_op.uag_ucp_app d" 左连贯。
select 'Creating view zxinsag.VW_NORTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS zxinsag.vw_north_subscribe_out;
create view zxinsag.vw_north_subscribe_out as
SELECT a.phone,
a.n_subid, b.platform, b.appid, IF((b.ev_begin = 1), 'Begin', NULL) ev_begin, IF((b.ev_ringing = 1), 'Ringing', NULL) ev_ringing, IF((b.ev_answer = 1), 'Answer', NULL) ev_answer, IF((b.ev_busy = 1), 'Busy', NULL) ev_busy, IF((b.ev_notreachable = 1), 'Not Reachable', NULL) ev_notreachable, IF((b.ev_routefailure = 1), 'Route Failure', NULL) ev_routefailure, IF((b.ev_noanswer = 1), 'No Answer', NULL) ev_noanswer, IF((b.ev_abandon = 1), 'Abandon', NULL) ev_abandon, IF((b.ev_release = 1), 'Release', NULL) ev_release, IF((b.ev_exception = 1), 'Exception', NULL) ev_exception, IF((b.direct_mo = 1), IF((b.direct_mt = 1), 'Both', 'MO'), 'MT') direction, IF((b.notify_block = 1), 'Block', 'Notify') notify_block, c.s_subid, c.netcode, d.spid as aspid, '' notify_urlFROM zxinsag.tl_north_subid_phone a
JOIN zxinsag.tl_north_subscribe b
ON a.n_subid = b.n_subidJOIN zxinsag.tl_south_subscribe c
ON c.phone = a.phoneLEFT JOIN zxsdp_op.uag_ucp_app d
ON b.appid = d.serviceidWHERE a.rltcode = 0;
select 'Creating view VW_SOUTH_SUBSCRIBE_OUT' as prompt;
DROP VIEW IF EXISTS vw_south_subscribe_out;
create view vw_south_subscribe_out as
select dataid,
dataidx, optype, phone, s_subid, netcode, IF(ev_begin = 1, 'Begin', null) ev_begin, IF(ev_ringing = 1, 'Ringing', null) ev_ringing, IF(ev_answer = 1, 'Answer', null) ev_answer, IF(ev_busy = 1, 'Busy', null) ev_busy, IF(ev_notreachable = 1, 'Not Reachable', null) ev_notreachable, IF(ev_routefailure = 1, 'Route Failure', null) ev_routefailure, IF(ev_noanswer = 1, 'No Answer', null) ev_noanswer, IF(ev_abandon = 1, 'Abandon', null) ev_abandon, IF(ev_release = 1, 'Release', null) ev_release, IF(ev_exception = 1, 'Exception', null) ev_exception, IF(direct_mo = 1, IF(direct_mt = 1, 'Both', 'MO'), 'MT') direction, IF(notify_block = 1, 'Block', 'Notify') notify_block, iftosouthfrom to_south_subscribe;
-- 20171114 added by LC 减少北向用户详细信息视图
select 'Creating view VW_NORTH_PHONE' as prompt;
DROP VIEW IF EXISTS VW_NORTH_PHONE;
create view VW_NORTH_PHONE as
select intime,
n_subid, phone, IF(ev_begin = 1, 'Begin', null) ev_begin, IF(ev_ringing = 1, 'Ringing', null) ev_ringing, IF(ev_answer = 1, 'Answer', null) ev_answer, IF(ev_busy = 1, 'Busy', null) ev_busy, IF(ev_notreachable = 1, 'Not Reachable', null) ev_notreachable, IF(ev_routefailure = 1, 'Route Failure', null) ev_routefailure, IF(ev_noanswer = 1, 'No Answer', null) ev_noanswer, IF(ev_abandon = 1, 'Abandon', null) ev_abandon, IF(ev_release = 1, 'Release', null) ev_release, IF(ev_exception = 1, 'Exception', null) ev_exception, IF(direct_mo = 1, IF(direct_mt = 1, 'Both', 'MO'), 'MT') direction, IF(notify_block = 1, 'Block', 'Notify') notify_block, opresultfrom TL_NORTH_SUBID_PHONE;
select 'Creating procedure P_QUERY_SOUTH_SUBSCRIBE' as prompt;
DROP PROCEDURE IF EXISTS p_query_south_subscribe;
delimiter //
create procedure p_query_south_subscribe
(
in vi_phone VARCHAR(32),in vi_s_subid VARCHAR(64),in vi_netcode VARCHAR(64),out vo_return int,out vo_errmsg VARCHAR(200),out vo_info VARCHAR(200))
proc:begin
DECLARE errocode int default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;set vo_return = 0;set vo_errmsg = ' ';set vo_info = ' ';if vi_phone = '' and vi_s_subid = '' then set vo_return = 1; set vo_errmsg = 'error: either phone or subid is required!'; leave proc;end if;select CONCAT_WS('|',t.phone,t.s_subid,t.netcode) into vo_info from tl_south_subscribe t where (t.phone = vi_phone or vi_phone = '') and (t.s_subid = vi_s_subid and t.netcode = vi_netcode or vi_s_subid = ''); set vo_return = 0;IF errocode = 2 THEN set vo_return = 2; set vo_errmsg = 'no data found!';else if errocode = 1 then set vo_return = -1; set vo_errmsg = 'other erro'; end if;END IF;end
//
delimiter ;
select 'Creating procedure P_SUBSCRIBE_CLEAN' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_clean;
DELIMITER //
CREATE PROCEDURE p_subscribe_clean()
proc:BEGIN
DECLARE v_expiredtime TIMESTAMP DEFAULT DATE_ADD(NOW(), INTERVAL -15 MINUTE);-- 删除已过操作有效期还未胜利的北向订阅记录DELETE FROM tl_north_subscribe WHERE rltcode IS NULL AND intime < v_expiredtime;-- 删除有效的北向订阅号码DELETE FROM tl_north_subid_phone USING tl_north_subid_phone LEFT JOIN tl_north_subscribe ON tl_north_subid_phone.n_subid = tl_north_subscribe.n_subidWHERE tl_north_subscribe.n_subid IS NULL;-- 删除已过操作有效期的北向更新记录DELETE FROM tl_north_subscribe_update WHERE intime < v_expiredtime;-- 删除已过操作有效期的南向导出记录DELETE FROM to_south_subscribe WHERE intime < v_expiredtime;COMMIT; END
//
delimiter ;
-- 删除记录定时工作
select 'Creating job P_SUBSCRIBE_CLEAN_JOB' as prompt;
SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS P_SUBSCRIBE_CLEAN_JOB;
CREATE EVENT P_SUBSCRIBE_CLEAN_JOB ON SCHEDULE
EVERY 15 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
-- ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ENABLE
DO call zxinsag.p_subscribe_clean();
select 'Creating procedure P_SUBSCRIBE_DELETE' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_delete;
DELIMITER //
create procedure p_subscribe_delete
(
in vi_n_subid VARCHAR(64),out vo_return int,out vo_errmsg VARCHAR(200),out vo_dataid int,out vo_datanum int)
proc:begin
DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;DECLARE v_count int default 0;DECLARE v_dataid bigint default 0;-- added by LC 20171011 减少数据处理条数长期变量用于累计须要南向同步和不须要两种记录数DECLARE v_datanum_tmp int default 0;DECLARE errocode int default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;set vo_return = 0;set vo_errmsg = ' ';set vo_datanum = 0;/** 北向退订申请*/-- 清理长期表 added by LC 20170828TRUNCATE TABLE TT_NORTH_SUB_COMPARE;truncate table tt_subscribe_phone;select count(1) into v_count from tl_north_subscribe t where -- t.rltcode = 0 and 删除订阅事件时，能够删除状态不失常的事件 20170922 modify by LC t.n_subid = vi_n_subid;if v_count = 0then set vo_return = 1; set vo_errmsg = 'error: this subId is not exist!'; leave proc;end if;-- 获取这个 n_subid 的订阅号码insert into tt_subscribe_phone (phone) select t.phone from tl_north_subid_phone t where t.rltcode = 0 and t.n_subid = vi_n_subid;if errocode = 1 then set vo_return = 2; set vo_errmsg = 'insert tt_subscribe_phone error.'; leave proc;end if;-- 计算本次退订前的并集insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select c.phone, max(a.direct_mo), max(a.direct_mt), max(a.ev_begin), max(a.ev_ringing), max(a.ev_answer), max(a.ev_busy), max(a.ev_notreachable), max(a.ev_routefailure), max(a.ev_noanswer), max(a.ev_abandon), max(a.ev_release), max(a.ev_exception), max(a.notify_block), 1 optype, 1 dataflag from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c where a.n_subid = b.n_subid and b.phone = c.phone and a.rltcode = 0 and b.rltcode = 0 group by c.phone;if errocode = 1 then set vo_return = 3; set vo_errmsg = 'insert tt_north_sub_compare error.'; leave proc;end if;-- 计算本次退订后的并集-- 2017109 modify by LC 要将进入长期表的记录的各种事件初始化为0，否则所有事件字段值为null 在进行left join 时null = null 筛选不出数据insert into tt_north_sub_compare (phone, optype, dataflag , direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block) select distinct t.phone, 2 optype, 2 dataflag, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from (select b.phone, c.phone phone_del from tt_subscribe_phone b left join tl_north_subid_phone c on b.phone = c.phone and c.n_subid <> vi_n_subid and c.rltcode = 0) t where t.phone_del is null;if errocode = 1 then set vo_return = 4; set vo_errmsg = 'insert tt_north_sub_compare error.'; leave proc;end if;insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select t.phone, max(t.direct_mo), max(t.direct_mt), max(t.ev_begin), max(t.ev_ringing), max(t.ev_answer), max(t.ev_busy), max(t.ev_notreachable), max(t.ev_routefailure), max(t.ev_noanswer), max(t.ev_abandon), max(t.ev_release), max(t.ev_exception), max(t.notify_block), 1 optype, 2 dataflag from (select b.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c where a.n_subid = b.n_subid and b.phone = c.phone and b.rltcode = 0 and a.n_subid <> vi_n_subid and a.rltcode = 0) t group by t.phone;if errocode = 1 then set vo_return = 5; set vo_errmsg = 'insert tt_north_sub_compare error.'; leave proc;end if;select zxinsag.nextval('SEQ_DATAID',1) into v_dataid;-- v_dataid := seq_dataid.nextval();-- 插入须要南向操作的记录insert into to_south_subscribe (intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, iftosouth) select v_sysdate, v_dataid, @rownum:=@rownum+1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, 1 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a left join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) where b.phone is null ) t left join tl_south_subscribe s on t.phone = s.phone;if errocode = 1 then set vo_return = 6; set vo_errmsg = 'insert to_south_subscribe error.'; leave proc;end if; select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;if vo_datanum = 0then -- 不须要去南向交互 delete from tl_north_subid_phone where n_subid = vi_n_subid; delete from tl_north_subscribe where n_subid = vi_n_subid;else -- 20171109 added by LC 新增删除操作如果有局部须要南向同步，局部不须要的状况则间接删除不须要去南向同步的号码记录，须要向南向同步的保留，期待响应音讯上来进行解决。 DELETE FROM tl_north_subid_phone USING tl_north_subid_phone JOIN (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) t ON tl_north_subid_phone.phone = t.phone;end if;-- 20171107 added by LC 删除操作时每个号码状态设置为"删除中" 期待南向删除的返回后果update tl_north_subid_phone aset a.opresult = 7where a.n_subid = vi_n_subid;if errocode = 1 then set vo_return = 7; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc;end if;-- 插入不须要南向操作的记录 取交加 added by LC 20171010insert into to_south_subscribe (intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, iftosouth) select v_sysdate, v_dataid, @rownum:=@rownum+1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, 0 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) t left join tl_south_subscribe s on t.phone = s.phone;if errocode = 1 then set vo_return = 8; set vo_errmsg = 'insert to_south_subscribe error.'; leave proc;end if;select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;-- 清理长期表 added by LC 20170828TRUNCATE TABLE TT_NORTH_SUB_COMPARE;truncate table tt_subscribe_phone;commit;set vo_return = 0;set vo_dataid = v_dataid;if errocode = 1 then set vo_return = -1; set vo_errmsg = 'other erro';end if;end
//
delimiter ;
select 'Creating procedure p_subscribe_req' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_req;
DELIMITER //
create procedure p_subscribe_req
(
in vi_n_subid VARCHAR(64),in vi_appid VARCHAR(32),in vi_sepid VARCHAR(64),in vi_platform int,in vi_phones VARCHAR(2048),in vi_events VARCHAR(1024),in vi_direction VARCHAR(16),in vi_notify VARCHAR(32),out vo_return int,out vo_errmsg VARCHAR(200),out vo_dataid int,out vo_datanum int)
proc:begin
/** 北向订阅申请*/DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;DECLARE v_dataid bigint default 0;DECLARE v_count int default 0;DECLARE v_direct_mo int default 0;DECLARE v_direct_mt int default 0;DECLARE v_ev_begin int default 0;DECLARE v_ev_ringing int default 0;DECLARE v_ev_answer int default 0;DECLARE v_ev_busy int default 0;DECLARE v_ev_notreachable int default 0;DECLARE v_ev_routefailure int default 0;DECLARE v_ev_noanswer int default 0;DECLARE v_ev_abandon int default 0;DECLARE v_ev_release int default 0;DECLARE v_ev_exception int default 0;DECLARE v_notify_block int default 0;DECLARE v_locate int default 1;DECLARE v_all_char_langth int default 0;DECLARE v_shot_langht int default 0;DECLARE v_temp_phone VARCHAR(32) default ' ';DECLARE v_temp_event VARCHAR(32) default ' ';-- added by LC 20171011 减少数据处理条数长期变量用于累计须要南向同步和不须要两种记录数DECLARE v_datanum_tmp int default 0;DECLARE errocode int default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;set vo_return = 0;set vo_errmsg = ' ';set vo_dataid = 0;set vo_datanum = 0;-- 清理长期表 added by LC 20170828TRUNCATE TABLE TT_NORTH_SUB_COMPARE;truncate table tt_subscribe_phone;truncate table TT_SUBSCRIBE_EVENT;select count(1) into v_count from tl_north_subscribe t where t.n_subid = vi_n_subid;if v_count > 0then set vo_return = 1; set vo_errmsg = 'error: this subId has been used!'; leave proc;end if;-- 宰割号码/*insert into tt_subscribe_phone (phone) select distinct trim(regexp_substr(vi_phones, '[^;]+', 1, rownum)) from dual connect by rownum <= length(vi_phones) - length(replace(vi_phones, ';', '')) + 1; */ select CHAR_LENGTH(vi_phones) into v_all_char_langth;select CHAR_LENGTH(REPLACE(vi_phones, ';', '')) into v_shot_langht;set v_shot_langht = v_all_char_langth - v_shot_langht + 1; WHILE v_locate <= v_shot_langht DO SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(vi_phones,';',v_locate),';',-1)) into v_temp_phone; insert into tt_subscribe_phone (phone) values (v_temp_phone) ON DUPLICATE KEY UPDATE phone = VALUES(phone); set v_locate = v_locate + 1;END WHILE;set v_locate = 1;if errocode = 1 then set vo_return = 2; set vo_errmsg = 'split phone error.'; leave proc;end if;select count(1) into v_count from tt_subscribe_phone a join tl_north_subid_phone b on a.phone = b.phone join tl_north_subscribe c on c.n_subid = b.n_subid where c.appid = vi_appid and c.rltcode = 0;if v_count > 0then set vo_return = 3; set vo_errmsg = 'error: there are some users has been subscribed before!'; leave proc;end if;-- 宰割事件/*insert into tt_subscribe_event (event_description) select distinct trim(regexp_substr(vi_events, '[^;]+', 1, rownum)) from dual connect by rownum <= length(vi_events) - length(replace(vi_events, ';', '')) + 1; */ select CHAR_LENGTH(vi_events) into v_all_char_langth;select CHAR_LENGTH(REPLACE(vi_events, ';', '')) into v_shot_langht;set v_shot_langht = v_all_char_langth - v_shot_langht + 1; WHILE v_locate <= v_shot_langht DO SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(vi_events,';',v_locate),';',-1)) into v_temp_event; insert into tt_subscribe_event (event_description) values (v_temp_event) ON DUPLICATE KEY UPDATE event_description = VALUES(event_description); set v_locate = v_locate + 1;END WHILE;if errocode = 1 then set vo_return = 4; set vo_errmsg = 'split event error.'; leave proc;end if;select count(if(b.col_index = 1, 1, null)), count(if(b.col_index = 2, 1, null)), count(if(b.col_index = 3, 1, null)), count(if(b.col_index = 4, 1, null)), count(if(b.col_index = 5, 1, null)), count(if(b.col_index = 6, 1, null)), count(if(b.col_index = 7, 1, null)), count(if(b.col_index = 8, 1, null)), count(if(b.col_index = 9, 1, null)), count(if(b.col_index = 10, 1, null)) into v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception from tt_subscribe_event a join tp_callevent_def b on a.event_description = b.description;if vi_direction = 'MO'then set v_direct_mo = 1;else if vi_direction = 'MT' then set v_direct_mt = 1; else set v_direct_mo = 1; set v_direct_mt = 1; end if;end if;if vi_notify = 'Block'then set v_notify_block = 1;end if;-- 计算本次订阅前的并集insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select c.phone, max(a.direct_mo), max(a.direct_mt), max(a.ev_begin), max(a.ev_ringing), max(a.ev_answer), max(a.ev_busy), max(a.ev_notreachable), max(a.ev_routefailure), max(a.ev_noanswer), max(a.ev_abandon), max(a.ev_release), max(a.ev_exception), max(a.notify_block), 1 optype, 1 dataflag from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c where a.n_subid = b.n_subid and b.phone = c.phone and a.rltcode = 0 and b.rltcode = 0 group by c.phone;if errocode = 1 then set vo_return = 5; set vo_errmsg = 'into tt_north_sub_compare error.'; leave proc;end if;-- 订阅入库-- 20171030 modify by LC 给北向号码表减少事件信息的录入insert into tl_north_subid_phone (n_subid, phone, intime, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block,opresult) select vi_n_subid, t.phone, v_sysdate, v_direct_mo, v_direct_mt, v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception, v_notify_block, 1 from tt_subscribe_phone t; if errocode = 1 then set vo_return = 6; set vo_errmsg = 'into tl_north_subid_phone error.'; leave proc;end if;insert into tl_north_subscribe (intime, n_subid, appid, sepid, platform, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block)values (v_sysdate, vi_n_subid, vi_appid, vi_sepid, vi_platform, v_direct_mo, v_direct_mt, v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception, v_notify_block); if errocode = 1 then set vo_return = 7; set vo_errmsg = 'into tl_north_subscribe error.'; leave proc;end if;-- 计算本次订阅后的并集insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select c.phone, max(a.direct_mo), max(a.direct_mt), max(a.ev_begin), max(a.ev_ringing), max(a.ev_answer), max(a.ev_busy), max(a.ev_notreachable), max(a.ev_routefailure), max(a.ev_noanswer), max(a.ev_abandon), max(a.ev_release), max(a.ev_exception), max(a.notify_block), c.optype, 2 dataflag from tl_north_subscribe a, tl_north_subid_phone b, (select e.phone, if(d.phone is null, 0, 1) optype from tt_subscribe_phone e left join tl_north_subid_phone d on d.phone = e.phone and d.rltcode = 0) c where a.n_subid = b.n_subid and b.phone = c.phone and (a.rltcode = 0 or a.n_subid = vi_n_subid) and (b.rltcode = 0 or b.n_subid = vi_n_subid) group by c.phone, c.optype;if errocode = 1 then set vo_return = 8; set vo_errmsg = 'into tt_north_sub_compare error.'; leave proc;end if;select zxinsag.nextval('SEQ_DATAID',1) into v_dataid;-- v_dataid := seq_dataid.nextval();-- 插入须要南向操作的记录insert into to_south_subscribe (dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, intime, iftosouth) select v_dataid, @rownum := @rownum + 1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, v_sysdate, 1 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a left join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) where b.phone is null ) t left join tl_south_subscribe s on t.phone = s.phone;if errocode = 1 then set vo_return = 9; set vo_errmsg = 'into to_south_subscribe error.'; leave proc;end if;select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;if vo_datanum = 0then -- 不须要去南向申请 update tl_north_subid_phone set rltcode = 0,opresult = 0 where n_subid = vi_n_subid; if errocode = 1 then set vo_return = 10; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; update tl_north_subscribe set rltcode = 0 where n_subid = vi_n_subid; if errocode = 1 then set vo_return = 11; set vo_errmsg = 'update tl_north_subscribe error.'; leave proc; end if;else -- 20170921 added by LC 新增订阅事件如果反复订阅不向南向同步，并设置为本次订阅后果正确 update tl_north_subid_phone a, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) t set a.rltcode = 0, a.opresult = 0 where a.n_subid = vi_n_subid AND a.phone = t.phone;end if; -- 插入不须要南向操作的记录 取交加 added by LC 20171010insert into to_south_subscribe (dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, intime, iftosouth) select v_dataid, @rownum := @rownum + 1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, v_sysdate, 0 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) t left join tl_south_subscribe s on t.phone = s.phone;if errocode = 1 then set vo_return = 10; set vo_errmsg = 'into to_south_subscribe error.'; leave proc;end if;select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;commit;set vo_return = 0;set vo_dataid = v_dataid;-- 清理长期表 added by LC 20170828TRUNCATE TABLE TT_NORTH_SUB_COMPARE;truncate table tt_subscribe_phone;truncate table TT_SUBSCRIBE_EVENT;if errocode = 1 then set vo_return = -1; set vo_errmsg = 'other erro';end if;end
//
delimiter ;
select 'Creating procedure p_subscribe_res' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_res;
DELIMITER //
create procedure p_subscribe_res
(
in vi_dataid int,in vi_n_subid VARCHAR(64),in vi_optype int,in vi_phone VARCHAR(2048),in vi_result VARCHAR(1000),in vi_s_subid VARCHAR(4000),in vi_netcode VARCHAR(4000),out vo_return int,out vo_errmsg VARCHAR(200))
proc:begin
/* * 南向订阅交互后果返回*/DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;DECLARE v_success_num int default 0;DECLARE v_all_add_success_num int default 0; -- 20171101 added by LC 订阅操作不须要南向同步的号码+南向同步胜利的号码数量DECLARE v_all_update_success_num int default 0; -- 20171101 added by LC 更新操作不须要南向同步的号码+南向同步胜利的号码数量DECLARE v_failed_num int default 0;DECLARE v_count int default 0;DECLARE v_result VARCHAR(1000) default ' '; DECLARE v_s_subid VARCHAR(4000) default ' '; -- 兼容全副为空的状况DECLARE v_netcode VARCHAR(4000) default ' '; -- 兼容全副为空的状况-- 拆分用变量DECLARE v_locate INT DEFAULT 1;DECLARE v_all_char_langth INT DEFAULT 0;DECLARE v_shot_langht INT DEFAULT 0;DECLARE v_shot_langht_phone INT DEFAULT 0;DECLARE v_shot_langht_subid INT DEFAULT 0;DECLARE v_shot_langht_netcode INT DEFAULT 0;DECLARE v_shot_langht_rltcode INT DEFAULT 0;DECLARE v_temp_phone VARCHAR(32) DEFAULT ' ';DECLARE v_temp_subid VARCHAR(64) DEFAULT ' ';DECLARE v_temp_netcode VARCHAR(64) DEFAULT ' ';DECLARE v_temp_rltcode VARCHAR(10) DEFAULT ' ';DECLARE errocode int default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;select concat(vi_result,' ') into v_result; -- 兼容全副为空的状况select concat(vi_s_subid,' ') into v_s_subid; -- 兼容全副为空的状况select concat(vi_netcode,' ') into v_netcode; -- 兼容全副为空的状况set vo_return = 0;set vo_errmsg = ' ';select count(1) into v_count from to_south_subscribe t where t.dataid = vi_dataid;if v_count = 0then set vo_return = 1; set vo_errmsg = 'error: dataid is not exist!'; leave proc;end if;-- 清理长期表truncate table tt_south_sub_result;-- 宰割关联南向申请后果/*insert into tt_south_sub_result (s_subid, netcode, phone, rltcode) select c.s_subid, d.netcode, a.phone, b.rltcode from (select rownum seq, trim(regexp_substr(vi_phone, '[^;]+', 1, rownum)) phone from dual connect by rownum <= length(vi_phone) - length(replace(vi_phone, ';', '')) + 1) a, (select rownum seq, trim(regexp_substr(v_result, '[^;]+', 1, rownum)) rltcode from dual connect by rownum <= length(v_result) - length(replace(v_result, ';', '')) + 1) b, (select rownum seq, trim(regexp_substr(v_s_subid, '[^;]+', 1, rownum)) s_subid from dual connect by rownum <= length(v_s_subid) - length(replace(v_s_subid, ';', '')) + 1) c, (select rownum seq, trim(regexp_substr(v_netcode, '[^;]+', 1, rownum)) netcode from dual connect by rownum <= length(v_netcode) - length(replace(v_netcode, ';', '')) + 1) d where a.seq = b.seq and b.seq = c.seq and c.seq = d.seq;*/ SET v_locate = 1;-- 号码数量SELECT CHAR_LENGTH(vi_phone) INTO v_all_char_langth;SELECT CHAR_LENGTH(REPLACE(vi_phone, ';', '')) INTO v_shot_langht_phone;SET v_shot_langht_phone = v_all_char_langth - v_shot_langht_phone + 1;-- result数量SELECT CHAR_LENGTH(v_result) INTO v_all_char_langth;SELECT CHAR_LENGTH(REPLACE(v_result, ';', '')) INTO v_shot_langht_rltcode;SET v_shot_langht_rltcode = v_all_char_langth - v_shot_langht_rltcode + 1;-- subid数量SELECT CHAR_LENGTH(v_s_subid) INTO v_all_char_langth;SELECT CHAR_LENGTH(REPLACE(v_s_subid, ';', '')) INTO v_shot_langht_subid;SET v_shot_langht_subid = v_all_char_langth - v_shot_langht_subid + 1;-- netcode数量SELECT CHAR_LENGTH(v_netcode) INTO v_all_char_langth;SELECT CHAR_LENGTH(REPLACE(v_netcode, ';', '')) INTO v_shot_langht_netcode;SET v_shot_langht_netcode = v_all_char_langth - v_shot_langht_netcode + 1;-- 取最小值select LEAST(v_shot_langht_phone, v_shot_langht_rltcode, v_shot_langht_subid, v_shot_langht_netcode) into v_shot_langht;if v_shot_langht = 0 then if GREATEST(v_shot_langht_phone,v_shot_langht_rltcode,v_shot_langht_subid,v_shot_langht_netcode) > 0 then set v_shot_langht = 1; end if;end if;WHILE v_locate <= v_shot_langht DO SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(vi_phone,';',v_locate),';',-1)) INTO v_temp_phone; SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(v_result,';',v_locate),';',-1)) INTO v_temp_rltcode; SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(v_s_subid,';',v_locate),';',-1)) INTO v_temp_subid; SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(v_netcode,';',v_locate),';',-1)) INTO v_temp_netcode; INSERT INTO tt_south_sub_result (s_subid, netcode, phone, rltcode) VALUES (v_temp_subid,v_temp_netcode,v_temp_phone,v_temp_rltcode); SET v_locate = v_locate + 1;END WHILE;SET v_locate = 1; if errocode = 1 then set vo_return = 2; set vo_errmsg = 'INTO tt_south_sub_result error.'; leave proc;end if;-- 计算南向操作胜利和失败的数量select count(if(b.rltcode = '0', 1, null)), count(if(b.rltcode = '0', null, 1)) into v_success_num, v_failed_num from to_south_subscribe a left join tt_south_sub_result b on a.phone = b.phone where a.dataid = vi_dataid and a.iftosouth = 1;-- 合入订阅胜利和更新胜利的数据 INSERT INTO tl_south_subscribe (intime,s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block) (SELECT v_sysdate, b.s_subid, b.netcode, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block FROM to_south_subscribe a, tt_south_sub_result b WHERE a.phone = b.phone AND a.optype < 2 and a.dataid = vi_dataid AND b.rltcode = '0' and a.iftosouth = 1) ON DUPLICATE KEY UPDATE phone = VALUES(phone), S_SUBID = VALUES(S_SUBID), NETCODE = VALUES(NETCODE), direct_mo = a.direct_mo, direct_mt = a.direct_mt, ev_begin = a.ev_begin, ev_ringing = a.ev_ringing, ev_answer = a.ev_answer, ev_busy = a.ev_busy, ev_notreachable = a.ev_notreachable, ev_routefailure = a.ev_routefailure, ev_noanswer = a.ev_noanswer, ev_abandon = a.ev_abandon, ev_release = a.ev_release, ev_exception = a.ev_exception, notify_block = a.notify_block;if errocode = 1 then set vo_return = 3; set vo_errmsg = 'INTO tl_south_subscribe error.'; leave proc;end if; -- 删除退订胜利的delete from tl_south_subscribe where phone in (select a.phone from to_south_subscribe a, tt_south_sub_result b where a.phone = b.phone and a.dataid = vi_dataid and a.optype = 2 and b.rltcode = '0' and a.iftosouth = 1); if errocode = 1 then set vo_return = 4; set vo_errmsg = 'delete tl_south_subscribe error.'; leave proc;end if; if vi_optype = 0then -- 20171101 added by LC 计算订阅南向同步胜利+不须要南向同步默认胜利的数量 select count(1) into v_all_add_success_num from tl_north_subid_phone a where a.n_subid = vi_n_subid AND a.rltcode = 0 AND a.opresult = 0; set v_all_add_success_num = v_success_num + v_all_add_success_num; -- 订阅操作 if v_all_add_success_num = 0 then -- 所有号码都失败 -- 20171027 modify by LC 全副失败 北向记录后果失败，录入本次事件；失败号码后果失败间接删除北向的记录和所有号码全副记录 /*update tl_north_subscribe a set a.rltcode = 2 where a.rltcode is null and a.n_subid = vi_n_subid;*/ delete from tl_north_subscribe where rltcode is null and n_subid = vi_n_subid; if errocode = 1 then set vo_return = 5; set vo_errmsg = 'delete tl_north_subscribe error.'; leave proc; end if; -- 20171027 modify by LC 全副失败 北向记录后果失败，录入本次事件；失败号码后果失败间接删除北向的记录和所有号码全副记录 /*update tl_north_subid_phone a set a.rltcode = 2 where a.rltcode is null and a.n_subid = vi_n_subid;*/ delete from tl_north_subid_phone where rltcode is null and n_subid = vi_n_subid; if errocode = 1 then set vo_return = 6; set vo_errmsg = 'delete tl_north_subid_phone error.'; leave proc; end if; else -- 有胜利的要更新北向订阅数据 -- 20171027 modify by LC 局部胜利 北向记录后果胜利，录入本次事件(现有流程不变)；胜利号码后果胜利，录入本次事件；失败号码后果失败，将号码删除只保留胜利的号码 update tl_north_subid_phone a set a.rltcode = 0, a.opresult = 0 where a.rltcode is null and a.n_subid = vi_n_subid and a.phone in (select b.phone from tt_south_sub_result b where b.rltcode = '0'); if errocode = 1 then set vo_return = 7; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; /*update tl_north_subid_phone a set a.rltcode = 2 a.opresult = 1 where a.rltcode is null and a.n_subid = vi_n_subid and a.phone in (select b.phone from tt_south_sub_result b where b.rltcode <> '0');*/ -- 20171027 modify by LC 局部胜利 北向记录后果胜利，录入本次事件(现有流程不变)；胜利号码后果胜利，录入本次事件；失败号码后果失败，将号码删除只保留胜利的号码 delete from tl_north_subid_phone where rltcode is null and n_subid = vi_n_subid and phone in (select phone from tt_south_sub_result where rltcode <> '0'); if errocode = 1 then set vo_return = 8; set vo_errmsg = 'delete tl_north_subid_phone error.'; leave proc; end if; update tl_north_subscribe a set a.rltcode = 0 where a.rltcode is null and a.n_subid = vi_n_subid; if errocode = 1 then set vo_return = 9; set vo_errmsg = 'update tl_north_subscribe error.'; leave proc; end if; end if;else if vi_optype = 1 then -- 20171101 added by LC 计算更新南向同步胜利+不须要南向同步默认胜利的数量 select count(1) into v_all_update_success_num from tl_north_subid_phone a where a.n_subid = vi_n_subid AND a.rltcode = 0 AND a.opresult = 3; set v_all_update_success_num = v_success_num + v_all_update_success_num; -- 更新操作 if v_all_update_success_num = 0 then -- 20171102 modify by LC 没有任何更新胜利的记录，将所有的号码状态改为"更新失败"，北向事件记录、用户事件记录不变 update tl_north_subid_phone a set a.opresult = 5 where a.n_subid = vi_n_subid and a.opresult = 4; if errocode = 1 then set vo_return = 10; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; else -- 只有有胜利记录更新北向订阅数据 UPDATE tl_north_subscribe d,tl_north_subscribe_update b SET d.direct_mo = b.direct_mo, d.direct_mt = b.direct_mt, d.ev_begin = b.ev_begin, d.ev_ringing = b.ev_ringing, d.ev_answer = b.ev_answer, d.ev_busy = b.ev_busy, d.ev_notreachable = b.ev_notreachable, d.ev_routefailure = b.ev_routefailure, d.ev_noanswer = b.ev_noanswer, d.ev_abandon = b.ev_abandon, d.ev_release = b.ev_release, d.ev_exception = b.ev_exception, d.notify_block = b.notify_block WHERE d.n_subid = b.n_subid AND b.dataid = vi_dataid AND b.n_subid = vi_n_subid; if errocode = 1 then set vo_return = 11; set vo_errmsg = 'update tl_north_subscribe error.'; leave proc; end if; -- 20171102 modify by LC 将南向同步胜利的号码状态改为"更新胜利"，用户事件记录跟新为最新 update tl_north_subid_phone d,tl_north_subscribe_update b set d.opresult = 3, d.direct_mo = b.direct_mo, d.direct_mt = b.direct_mt, d.ev_begin = b.ev_begin, d.ev_ringing = b.ev_ringing, d.ev_answer = b.ev_answer, d.ev_busy = b.ev_busy, d.ev_notreachable = b.ev_notreachable, d.ev_routefailure = b.ev_routefailure, d.ev_noanswer = b.ev_noanswer, d.ev_abandon = b.ev_abandon, d.ev_release = b.ev_release, d.ev_exception = b.ev_exception, d.notify_block = b.notify_block where d.n_subid = vi_n_subid and d.opresult = 4 and d.n_subid = b.n_subid and b.dataid = vi_dataid and d.phone in (select c.phone from tt_south_sub_result c where c.rltcode = '0'); if errocode = 1 then set vo_return = 12; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; -- 20171102 modify by LC 将南向同步失败的号码状态改为"更新失败"，用户事件记录不更新 update tl_north_subid_phone d,tl_north_subscribe_update b set d.opresult = 5 where d.n_subid = vi_n_subid and d.opresult = 4 and d.n_subid = b.n_subid and b.dataid = vi_dataid and d.phone in (select c.phone from tt_south_sub_result c where c.rltcode <> '0'); if errocode = 1 then set vo_return = 13; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; delete from tl_north_subscribe_update where dataid = vi_dataid and n_subid = vi_n_subid; if errocode = 1 then set vo_return = 14; set vo_errmsg = 'delete tl_north_subscribe_update error.'; leave proc; end if; end if; else if vi_optype = 2 and v_failed_num = 0 then -- 退订操作，全副胜利删除北向订阅数据 delete from tl_north_subid_phone where n_subid = vi_n_subid; if errocode = 1 then set vo_return = 15; set vo_errmsg = 'delete tl_north_subid_phone error.'; leave proc; end if; delete from tl_north_subscribe where n_subid = vi_n_subid; if errocode = 1 then set vo_return = 16; set vo_errmsg = 'delete tl_north_subscribe error.'; leave proc; end if; else -- 20171109 added by LC 退订操作局部号码胜利 -- 删除南向退订胜利的记录 delete from tl_north_subid_phone where rltcode = '0' and opresult = 7 and n_subid = vi_n_subid and phone in (select phone from tt_south_sub_result where rltcode = '0'); if errocode = 1 then set vo_return = 17; set vo_errmsg = 'delete tl_north_subid_phone error.'; leave proc; end if; -- 删除失败的更新为删除失败 update tl_north_subid_phone set opresult = 8 where rltcode = '0' and opresult = 7 and n_subid = vi_n_subid and phone in (select phone from tt_south_sub_result where rltcode <> '0'); if errocode = 1 then set vo_return = 18; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if; end if; end if;end if;-- 删除本次导出的南向交互数据delete from to_south_subscribe where dataid = vi_dataid;if errocode = 1 then set vo_return = 19; set vo_errmsg = 'delete to_south_subscribe error.'; leave proc;end if; commit;set vo_return = 0;-- 清理长期表truncate table tt_south_sub_result;if errocode = 1 then set vo_return = -1; set vo_errmsg = 'other erro';end if;end
//
delimiter ;
select 'Creating procedure p_subscribe_update' as prompt;
DROP PROCEDURE IF EXISTS p_subscribe_update;
DELIMITER //
create procedure p_subscribe_update
(
in vi_n_subid VARCHAR(64),in vi_events VARCHAR(1024),in vi_direction VARCHAR(16),in vi_notify VARCHAR(32),out vo_return int,out vo_errmsg VARCHAR(200),out vo_dataid int,out vo_datanum int)
proc:begin
/** 北向更新申请*/DECLARE v_sysdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;DECLARE v_count int default 0;DECLARE v_dataid bigint default 0;DECLARE v_direct_mo int default 0;DECLARE v_direct_mt int default 0;DECLARE v_ev_begin int default 0;DECLARE v_ev_ringing int default 0;DECLARE v_ev_answer int default 0;DECLARE v_ev_busy int default 0;DECLARE v_ev_notreachable int default 0;DECLARE v_ev_routefailure int default 0;DECLARE v_ev_noanswer int default 0;DECLARE v_ev_abandon int default 0;DECLARE v_ev_release int default 0;DECLARE v_ev_exception int default 0;DECLARE v_notify_block int default 0;DECLARE v_locate INT DEFAULT 1;DECLARE v_all_char_langth INT DEFAULT 0;DECLARE v_shot_langht INT DEFAULT 0;DECLARE v_temp_phone VARCHAR(32) DEFAULT ' ';DECLARE v_temp_event VARCHAR(32) DEFAULT ' ';-- added by LC 20171011 减少数据处理条数长期变量用于累计须要南向同步和不须要两种记录数DECLARE v_datanum_tmp int default 0;DECLARE errocode int default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errocode = 1;DECLARE CONTINUE HANDLER FOR not found SET errocode = 2;set vo_return = 0;set vo_errmsg = ' ';set vo_datanum = 0;set vo_dataid = 0;select count(1) into v_count from tl_north_subscribe t where t.rltcode = 0 and t.n_subid = vi_n_subid;if v_count = 0then set vo_return = 1; set vo_errmsg = 'error: this subId is not exist!'; leave proc;end if;-- added by LC 20170829 革除长期表truncate table tt_north_sub_compare;truncate table tt_subscribe_phone;truncate table TT_SUBSCRIBE_EVENT;-- 获取这个 n_subid 的订阅号码insert into tt_subscribe_phone (phone) select t.phone from tl_north_subid_phone t where t.rltcode = 0 and t.n_subid = vi_n_subid; if errocode = 1 then set vo_return = 2; set vo_errmsg = 'insert tt_subscribe_phone error.'; leave proc;end if; if vi_events = ''then select t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception into v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception from tl_north_subscribe t where t.n_subid = vi_n_subid and t.rltcode = 0;else -- 宰割事件 /*insert into tt_subscribe_event (event_description) select distinct trim(regexp_substr(vi_events, '[^;]+', 1, rownum)) from dual connect by rownum <= length(vi_events) - length(replace(vi_events, ';', '')) + 1;*/ SET v_locate = 1; SELECT CHAR_LENGTH(vi_events) INTO v_all_char_langth; SELECT CHAR_LENGTH(REPLACE(vi_events, ';', '')) INTO v_shot_langht; SET v_shot_langht = v_all_char_langth - v_shot_langht + 1; WHILE v_locate <= v_shot_langht DO SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(vi_events,';',v_locate),';',-1)) INTO v_temp_event; INSERT INTO tt_subscribe_event (event_description) VALUES (v_temp_event) ON DUPLICATE KEY UPDATE event_description = VALUES(event_description); SET v_locate = v_locate + 1; END WHILE; if errocode = 1 then set vo_return = 3; set vo_errmsg = 'insert tt_subscribe_event error.'; leave proc; end if; select count(if(b.col_index = 1, 1, null)), count(if(b.col_index = 2, 1, null)), count(if(b.col_index = 3, 1, null)), count(if(b.col_index = 4, 1, null)), count(if(b.col_index = 5, 1, null)), count(if(b.col_index = 6, 1, null)), count(if(b.col_index = 7, 1, null)), count(if(b.col_index = 8, 1, null)), count(if(b.col_index = 9, 1, null)), count(if(b.col_index = 10, 1, null)) into v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception from tt_subscribe_event a join tp_callevent_def b on a.event_description = b.description;end if;if vi_direction = ''then select t.direct_mo, t.direct_mt into v_direct_mo, v_direct_mt from tl_north_subscribe t where t.n_subid = vi_n_subid and t.rltcode = 0;else if vi_direction = 'MO' then set v_direct_mo = 1; else if vi_direction = 'MT' then set v_direct_mt = 1; else set v_direct_mo = 1; set v_direct_mt = 1; end if; end if;end if;if vi_notify = ''then select t.notify_block into v_notify_block from tl_north_subscribe t where t.n_subid = vi_n_subid and t.rltcode = 0;else if vi_notify = 'Block' then set v_notify_block = 1; end if;end if;-- 计算本次更新前的并集insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select c.phone, max(a.direct_mo), max(a.direct_mt), max(a.ev_begin), max(a.ev_ringing), max(a.ev_answer), max(a.ev_busy), max(a.ev_notreachable), max(a.ev_routefailure), max(a.ev_noanswer), max(a.ev_abandon), max(a.ev_release), max(a.ev_exception), max(a.notify_block), 1 optype, 1 dataflag from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c where a.n_subid = b.n_subid and b.phone = c.phone and a.rltcode = 0 and b.rltcode = 0 group by c.phone; if errocode = 1 then set vo_return = 4; set vo_errmsg = 'insert tt_north_sub_compare error.'; leave proc;end if;-- 计算本次更新后的并集insert into tt_north_sub_compare (phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, optype, dataflag) select t.phone, max(t.direct_mo), max(t.direct_mt), max(t.ev_begin), max(t.ev_ringing), max(t.ev_answer), max(t.ev_busy), max(t.ev_notreachable), max(t.ev_routefailure), max(t.ev_noanswer), max(t.ev_abandon), max(t.ev_release), max(t.ev_exception), max(t.notify_block), 1 optype, 2 dataflag from (select b.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from tl_north_subscribe a, tl_north_subid_phone b, tt_subscribe_phone c where a.n_subid = b.n_subid and c.phone = b.phone and b.n_subid <> vi_n_subid and a.rltcode = 0 and b.rltcode = 0 union all select d.phone, v_direct_mo direct_mo, v_direct_mt direct_mt, v_ev_begin ev_begin, v_ev_ringing ev_ringing, v_ev_answer ev_answer, v_ev_busy ev_busy, v_ev_notreachable ev_notreachable, v_ev_routefailure ev_routefailure, v_ev_noanswer ev_noanswer, v_ev_abandon ev_abandon, v_ev_release ev_release, v_ev_exception ev_exception, v_notify_block notify_block from tt_subscribe_phone d) t group by t.phone; if errocode = 1 then set vo_return = 5; set vo_errmsg = 'insert tt_north_sub_compare error.'; leave proc;end if;select zxinsag.nextval('SEQ_DATAID',1) into v_dataid;-- v_dataid := seq_dataid.nextval();-- 插入须要南向操作的记录insert into to_south_subscribe (intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, iftosouth) select v_sysdate, v_dataid, @rownum := @rownum + 1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, 1 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a LEFT JOIN (select * from tt_north_sub_compare where dataflag = 1) b ON (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_answer = b.ev_answer AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) WHERE b.phone IS NULL ) t left join tl_south_subscribe s on t.phone = s.phone; if errocode = 1 then set vo_return = 6; set vo_errmsg = 'insert to_south_subscribe error.'; leave proc;end if;select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;-- 20171102 added by LC 更新操作时每个号码状态设置为"更新中" 期待南向更新的返回后果update tl_north_subid_phone aset a.opresult = 4where a.n_subid = vi_n_subid;if vo_datanum = 0then -- 不须要去南向申请 update tl_north_subscribe t set t.direct_mo = v_direct_mo, t.direct_mt = v_direct_mt, t.ev_begin = v_ev_begin, t.ev_ringing = v_ev_ringing, t.ev_answer = v_ev_answer, t.ev_busy = v_ev_busy, t.ev_notreachable = v_ev_notreachable, t.ev_routefailure = v_ev_routefailure, t.ev_noanswer = v_ev_noanswer, t.ev_abandon = v_ev_abandon, t.ev_release = v_ev_release, t.ev_exception = v_ev_exception, t.notify_block = v_notify_block where t.n_subid = vi_n_subid; if errocode = 1 then set vo_return = 7; set vo_errmsg = 'update tl_north_subscribe error.'; leave proc; end if; -- 20171031 added by LC 北向号码表减少事件信息,更新操作时须要一起更新 update tl_north_subid_phone t set t.opresult = 3, t.direct_mo = v_direct_mo, t.direct_mt = v_direct_mt, t.ev_begin = v_ev_begin, t.ev_ringing = v_ev_ringing, t.ev_answer = v_ev_answer, t.ev_busy = v_ev_busy, t.ev_notreachable = v_ev_notreachable, t.ev_routefailure = v_ev_routefailure, t.ev_noanswer = v_ev_noanswer, t.ev_abandon = v_ev_abandon, t.ev_release = v_ev_release, t.ev_exception = v_ev_exception, t.notify_block = v_notify_block where t.n_subid = vi_n_subid; if errocode = 1 then set vo_return = 8; set vo_errmsg = 'update tl_north_subscribe error.'; leave proc; end if;else -- 保留更新记录 insert into tl_north_subscribe_update (intime, dataid, n_subid, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block) values (v_sysdate, v_dataid, vi_n_subid, v_direct_mo, v_direct_mt, v_ev_begin, v_ev_ringing, v_ev_answer, v_ev_busy, v_ev_notreachable, v_ev_routefailure, v_ev_noanswer, v_ev_abandon, v_ev_release, v_ev_exception, v_notify_block); if errocode = 1 then set vo_return = 9; set vo_errmsg = 'insert tl_north_subscribe_update error.'; leave proc; end if; -- 20171031 added by LC 北向号码表减少事件信息,更新操作时须要一起更新 update tl_north_subid_phone t, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a join (select * from tt_north_sub_compare where dataflag = 1) b on (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_answer = b.ev_answer AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) a set t.opresult = 3, t.direct_mo = v_direct_mo, t.direct_mt = v_direct_mt, t.ev_begin = v_ev_begin, t.ev_ringing = v_ev_ringing, t.ev_answer = v_ev_answer, t.ev_busy = v_ev_busy, t.ev_notreachable = v_ev_notreachable, t.ev_routefailure = v_ev_routefailure, t.ev_noanswer = v_ev_noanswer, t.ev_abandon = v_ev_abandon, t.ev_release = v_ev_release, t.ev_exception = v_ev_exception, t.notify_block = v_notify_block where t.n_subid = vi_n_subid AND t.phone = a.phone; if errocode = 1 then set vo_return = 10; set vo_errmsg = 'update tl_north_subid_phone error.'; leave proc; end if;end if;-- 插入不须要南向操作的记录 取交加 added by LC 20171010insert into to_south_subscribe (intime, dataid, dataidx, optype, s_subid, netcode, phone, direct_mo, direct_mt, ev_begin, ev_ringing, ev_answer, ev_busy, ev_notreachable, ev_routefailure, ev_noanswer, ev_abandon, ev_release, ev_exception, notify_block, iftosouth) select v_sysdate, v_dataid, @rownum := @rownum + 1 AS rownum, t.optype, s.s_subid, s.netcode, t.phone, t.direct_mo, t.direct_mt, t.ev_begin, t.ev_ringing, t.ev_answer, t.ev_busy, t.ev_notreachable, t.ev_routefailure, t.ev_noanswer, t.ev_abandon, t.ev_release, t.ev_exception, t.notify_block, 0 from (SELECT @rownum:=0) r, (select DISTINCT a.optype, a.phone, a.direct_mo, a.direct_mt, a.ev_begin, a.ev_ringing, a.ev_answer, a.ev_busy, a.ev_notreachable, a.ev_routefailure, a.ev_noanswer, a.ev_abandon, a.ev_release, a.ev_exception, a.notify_block from (select * from tt_north_sub_compare where dataflag = 2) a JOIN (select * from tt_north_sub_compare where dataflag = 1) b ON (a.phone = b.phone AND a.optype = b.optype AND a.direct_mo = b.direct_mo AND a.direct_mt = b.direct_mt AND a.ev_begin = b.ev_begin AND a.ev_ringing = b.ev_ringing AND a.ev_busy = b.ev_busy AND a.ev_notreachable = b.ev_notreachable AND a.ev_answer = b.ev_answer AND a.ev_routefailure = b.ev_routefailure AND a.ev_noanswer = b.ev_noanswer AND a.ev_abandon = b.ev_abandon AND a.ev_release = b.ev_release AND a.ev_exception = b.ev_exception AND a.notify_block = b.notify_block) ) t left join tl_south_subscribe s on t.phone = s.phone; if errocode = 1 then set vo_return = 11; set vo_errmsg = 'insert to_south_subscribe error.'; leave proc;end if;select ROW_COUNT() into v_datanum_tmp;set vo_datanum = vo_datanum + v_datanum_tmp;commit;set vo_return = 0;set vo_dataid = v_dataid;-- added by LC 20170829 革除长期表truncate table tt_north_sub_compare;truncate table tt_subscribe_phone;truncate table TT_SUBSCRIBE_EVENT;if errocode = 1 then set vo_return = -1; set vo_errmsg = 'other erro';end if;end
//
delimiter ;
select '1 records loaded' prompt;
select 'Loading TP_CALLEVENT_DEF...' prompt;
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (1, 'Begin', 1);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (2, 'Ringing', 2);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (3, 'Answer', 3);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (4, 'Busy', 4);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (5, 'Not Reachable', 5);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (6, 'Route Failure', 6);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (7, 'No Answer', 7);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (8, 'Abandon', 8);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (9, 'Release', 9);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (10, 'Exception', 10);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (11, 'Play Result', null);
insert into TP_CALLEVENT_DEF (callevent_id, description, col_index)
values (12, 'Collect Result', null);
commit;
select '12 records loaded' prompt;
select 'Loading TP_DIRECTION_DEF...' prompt;
insert into TP_DIRECTION_DEF (direction_id, description)
values (2, 'Both');
insert into TP_DIRECTION_DEF (direction_id, description)
values (0, 'MO');
insert into TP_DIRECTION_DEF (direction_id, description)
values (1, 'MT');
commit;
select '3 records loaded' prompt;
select 'Loading TP_NOTIFY_MODE_DEF...' prompt;
insert into TP_NOTIFY_MODE_DEF (notify_mode, description)
values (1, 'Block');
insert into TP_NOTIFY_MODE_DEF (notify_mode, description)
values (0, 'Notify');
commit;
select '2 records loaded' prompt;