ipcc-3-日常运维

前言

1.中间跳过的名单拨打处理
2.统计批次进度问题
3.重呼数据未拨打问题(重启后拨打)
4.查看某个租户是否配置了重呼轮呼策略
5.批次数据号码处理(加前缀去掉前缀)

内容

1.中间跳过的名单拨打处理

先查看未拨打批次:

1
2
3

SELECT DISTINCT(ocm_buslist.event_id) FROM ocm_buslist ocm_buslist
  LEFT JOIN cti_cdr cdr ON ocm_buslist.list_id = cdr.memberid LEFT JOIN ocm_event e ON e.event_id = ocm_buslist.event_id
WHERE e.event_state IN(0,1,2) AND ocm_buslist.create_time<'2019-12-22 23:59:59' AND cdr.id IS NULL ORDER BY ocm_buslist.list_id DESC

1.1 数据量小时候处理

1.查询出未拨打的数据保存成.sql文件:

1
2
3

SELECT ocm_buslist.* FROM ocm_buslist ocm_buslist
  LEFT JOIN cti_cdr cdr ON ocm_buslist.list_id = cdr.memberid
WHERE ocm_buslist.event_id = xxx  AND  cdr.id IS NULL ORDER BY ocm_buslist.list_id DESC

2.查询批次(比较max_member_id和查询的list_id大小)

1	SELECT event_name,max_member_id FROM ocm_event WHERE event_id = xxx;

2.然后执行插入

3.最后删除之前list_id

1	DELETE FROM ocm_buslist WHERE event_id = xxx AND list_id IN(xxx1,xxx2);

4.查询导入之后数据大小

1	select count(*) from ocm_buslist where list_id > max_memberid

1.2 数据量大时候处理

批次是按照大于ocm_event表中的max_member_id对比进行拨打,在名单表中小于max_member_id将不会拨打。如果出现跳过拨打的数据,我们需要按照如下步骤处理:注意:首先先要查看对应的名单是否已经拨打但是没有插入扫cti_cdr表,防止重复导入拨打。[
操作:

1	grep 5314900 callnotify_callnotifysql01_1574*;

]

1.未拨打数据采集:[查询未拨打客户名单->将其导入到名单模板中重新导入]

1
2
3

SELECT ob.customer_name,ob.customer_phone,ob.car_no,ob.cust_id FROM ocm_buslist ob
  LEFT JOIN cti_cdr cdr ON ob.list_id = cdr.memberid
WHERE ob.event_id = xxx  AND  cdr.id IS NULL ORDER BY ob.list_id DESC

2.ocm_base_shbo处理:[删除未拨打的数据,删除之前先查询]
a.查询未拨打的数据:

SELECT sb.* FROM ocm_base_shbo sb WHERE sb.event_id = xxx AND sb.cust_id IN (SELECT DISTINCT ob.cust_id FROM ocm_buslist ob
  LEFT JOIN cti_cdr cdr ON ob.list_id = cdr.memberid
WHERE ob.event_id = xxx  AND  cdr.id IS NULL);
```  
b.删除未拨打数据:

DELETE FROM ocm_base_shbo WHERE event_id = xxx AND cust_id IN (SELECT ob.cust_id FROM ocm_buslist ob
LEFT JOIN cti_cdr cdr ON ob.list_id = cdr.memberid
WHERE ob.event_id = xxx AND cdr.id IS NULL);

1
2
3


3. 3.ocm_buslist处理:[删除未拨打的数据,删除之前先查询]  
a.查询未拨打数据

select * FROM ocm_buslist WHERE event_id = xxx AND list_id NOT IN (SELECT DISTINCT memberid FROM cti_cdr WHERE notifyid = xxx);

1
2


b.删除未拨打数据

DELETE FROM ocm_buslist WHERE event_id = xxx AND list_id NOT IN (SELECT DISTINCT memberid FROM cti_cdr WHERE notifyid = xxx);

1 2	4. 4.最后导入数据，修改批次统计:

select * from ocm_event_statistics where event_id = xxx


### 2.统计批次进度问题  
批次进度统计主要是表:ocm_event_statistics,其中进度主要由3部分指标组成:  
called_number(已拨打数量：从ocm_buslist中distinct cust_id)、filter_number(过滤掉的数量:从ocm_base_shbo表中count下:valid_flag=0的数据)、list_number(名单总数量:ocm_base_shbo中count(*))；如果ocm_buslist_timing表中含有数据的话即使ocm_buslist中数据拨打完了，也会是99%    
   
1. 查询批次整体进度情况

SELECT * FROM ocm_event_statistics WHERE event_id = xxx; //xxx批次id

1 2	1. 查看是否还有重呼数据

SELECT * FROM ocm_buslist_timing WHERE event_id = xxx; //xxx批次id

1 2	1. 查询导入到ocm_buslist中名单数量

SELECT COUNT(DISTINCT cust_id) FROM ocm_buslist WHERE event_id = xxx; //xxx批次id

SELECT COUNT(*) FROM ocm_base_shbo WHERE event_id = xxx AND valid_flag >= 1; //xxx批次id


1. 查询批次下总共数量:list_number

SELECT COUNT(*) FROM ocm_base_shbo WHERE event_id = xxx; //xxx批次id

1 2	1. 查看过滤掉的数据filter_number

SELECT COUNT(*) FROM ocm_base_shbo WHERE event_id = xxx AND valid_flag = 0; //xxx批次id


### 3.重呼数据未拨打问题(重启后拨打)  
有时候,我们的重呼数据一直可能存在于:ocm_buslist_timing表中而不会拨打,所以我们需要处理这些批次,重新启用停用(对于在执行时间段内的批次)。  
1. 1.查看在当前时间前未重呼的批次

SELECT event_id,event_state,event_name,send_time,over_time FROM ocm_event WHERE event_id IN (SELECT DISTINCT event_id FROM ocm_buslist_timing WHERE call_next_time

1 2	### 4.查看某个租户是否配置了重呼轮呼策略

SELECT enti.id,enti.entname,enti.state,oc.id,oc.parameter FROM cti_entinfo enti LEFT JOIN ocm_after_call_action oc ON enti.id = oc.entid


### 5.批次数据号码处理(加前缀去掉前缀)  
1. 1.参考
参考:https://blog.csdn.net/xiaosongbk/article/details/77199007
update forumdata_userttt set userLink=right(userLink,length(userLink)-2) where id >= 137;   
2. 2.去除批次号码的前缀（前两位）

UPDATE ocm_buslist SET customer_phone=RIGHT(customer_phone,LENGTH(customer_phone)-2) WHERE event_id = 1658 AND list_id>6253939;

1 2	3. 3.去除重呼表中93开头的号码前两位

UPDATE ocm_buslist_timing SET customer_phone RIGHT(customer_phone,LENGTH(customer_phone)-2) WHERE customer_phone LIKE ‘93%’;
```

2019-11-28