# 2024年适配

## sheet1 管理端-移网感知类

统计数据在原有基础（河北_CEM移网质量投诉明细全量数据）上增加新订阅的 河北客户体验管理智能定责投诉明细月累计接口日 表中 日定责问题分类(duty_reason_id_day) 字段值为 业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫 的部分。

新建表 management_detail 用于

```sql
drop table if exists tsl_data.management_detail;
CREATE TABLE tsl_data.management_detail (
  id bigserial NOT NULL,
  origin varchar(100) NOT NULL,
  stat_day_id varchar(8) NOT NULL,
  acct_date varchar(8) NOT NULL,
  sheet_no varchar(150) NOT NULL,
  compl_area_local varchar(300) NOT NULL,
  compl_city_local varchar(300) NULL,
  create_time timestamp NULL DEFAULT now(),
  CONSTRAINT management_detail_pkey PRIMARY KEY (id)
)
```

从 mobile_complaint_day 表中同步账期数据到 management_detail 河北_CEM移网质量投诉明细

```sql
insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no, compl_area_local, compl_city_local)
select 'mobile_complaint_day' as origin, concat(month_id, day_id) as stat_day_id, acct_date, sheet_no, compl_area_local, gis_city as compl_city_local
from tsl_data.mobile_complaint_day mcd 
where month_id = '202312' and day_id = '24'
```

从 complaint_details_fix_ywd_day 表中同步账期数据到 management_detail 河北客户体验管理智能定责投诉明细月累计接口日

```sql
insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no, compl_area_local, compl_city_local)
select 'complaint_details_fix_ywd_day' as origin, concat(month_id, day_id) as stat_day_id, concat(month_id1, day_id1) as acct_date, sheet_no, compl_area_local, compl_city_local
from tsl_data.complaint_details_fix_ywd_day cdfyd 
where duty_reason_id_day = '业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫'
and month_id = '202312' and day_id = '24'
```

## 投诉处理时长、超时工单概况调整

- 从工作流获取数据

```sql
select create_time as work_flow_create_time, 
update_time as work_flow_update_time,
city as city_id, 
form_basic_data->>'yh_region' as region_id, 
form_basic_data->>'kd_kfsn' as kfsn,
-- 客服受理时间
form_basic_data->>'kd_accepttime' as kd_accepttime,
-- 客服要求答复时间
form_basic_data->>'kd_requestreplytime' as kd_requestreplytime,
-- 客服答复状态
form_basic_data->>'re_is_status' as re_is_status,
-- 退单时间
form_basic_data->>'re_is_date' as re_is_date,
-- 退单内容 这个字段有问题
-- form_basic_data->>'kd_kfopinion' as kd_kfopinion,
-- 客服归档时间
form_basic_data->>'kf_file_time' as kf_file_time,
-- 客服归档类型
form_basic_data->>'kf_file_type' as kf_file_type
from flow_form_basic
where create_time >= to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
```

- 新建本地存储表

```sql
CREATE TABLE tsl_data.work_flow_basic_data (
    id bigserial NOT null,
    order_no varchar(50) not null,
    kfsn varchar(50) NOT NULL,
    city_id varchar(50) NULL,
    region_id varchar(50) NULL,
    kd_accept_time varchar(50) NULL,
    kd_request_reply_time varchar(50) NULL,
    re_is_status_id varchar(50) NULL,
    kf_file_time varchar(50) NULL,
    kf_file_type varchar(50) NULL,
    work_flow_create_time timestamp NULL,
    work_flow_update_time timestamp NULL,
    CONSTRAINT work_flow_basic_data_pkey PRIMARY KEY (id)
);
```

c43d1e18b0b84da0b7bd13e482e8a5a1    未答复
06678b79185349b5bf0c24490a978fbb    已退单
06678b79185349b5bf0c24490a977fbb    已回单

投诉处理时长 = 归档时间 - 受理时间

kd_accepttime 客服受理时间 没有空的清苦给你
re_is_status 客服答复状态 不存在空值

> 已退单的排除掉，不做考察

### 已回单情况

- kf_file_type 客服归档类型 情况

回访归档  有归档时间  6438
null  归档时间为空  231
GIS归档  有归档时间  2

> 归档时间为空的情况可以认为是回单了但未归档

### 未答复情况

> ??? 未答复的情况应如何处理

撤单归档  有归档时间  7
回访归档  有归档时间  9
null  归档时间为空  708

> 归档时间为空的情况如何处理？

111

回单超时？

回单未超时？

### 超时工单

```sql
with t1 as (select sdd.real_name as city,kfsn, to_timestamp(kf_file_time, 'yyyy-mm-dd hh24:mi:ss') - to_timestamp(kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > interval '36hours' as is_timeout
from tsl_data.work_flow_basic_data wfbd, tsl_data.sys_data_dictionary sdd 
where kf_file_time is not null
and re_is_status_id != '06678b79185349b5bf0c24490a978fbb'
and wfbd.city_id = sdd.nick_code and to_timestamp(kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
t2 as (select '全省' as city, count(1) as total_num, count(is_timeout or null) as timeout_num from t1),
t3 as (select city, count(1) as total_num, count(is_timeout or null) as timeout_num from t1 group by city)
select * from t2 union select* from t3
```

### 平均处理时长

```sql
with t1 as (select sdd.real_name,kfsn, (extract(epoch from to_timestamp(kf_file_time, 'YYYY-MM-DD HH24:MI:SS'))-extract(epoch from to_timestamp(kd_accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600 as cost_time
from tsl_data.work_flow_basic_data wfbd, tsl_data.sys_data_dictionary sdd 
where wfbd.city_id = sdd.nick_code and kf_file_time is not null and re_is_status_id != '06678b79185349b5bf0c24490a978fbb'
and to_timestamp(wfbd.kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
select real_name as city, avg(cost_time) as avg_cost
from t1 group by real_name order by real_name
```

## 接口测试

### 1. 报表生成

```http
POST http://192.168.10.7:22222/tsl_data/report/generate/v3
Content-Type: application/json

20240118
```

### 2. 数据入库

### 2.1 mobile_complaint

```http
POST http://192.168.10.7:22222/tsl_data/source/warehouse/mobile_complaint
Content-Type: application/json

20240117
```

### 2.2 high_quality_count

```http
POST http://192.168.10.7:22222/tsl_data/source/warehouse/high_quality_count
Content-Type: application/json

20240117
```

### 2.3 high_quality_list

```http
POST http://192.168.10.7:22222/tsl_data/source/warehouse/high_quality_list
Content-Type: application/json

20240117
```

### 2.4 complaint_details_fix_ywd

```http
POST http://192.168.10.7:22222/tsl_data/source/warehouse/complaint_details_fix_ywd
Content-Type: application/json

20240117
```

### 2.5 management_detail

```http
POST http://192.168.10.7:22222/tsl_data/source/update/management_detail
Content-Type: application/json

20240117
```

### 2.6 highQualityData

```http
POST http://192.168.10.7:22222/tsl_data/source/highQualityData/generate
Content-Type: application/json

20240117
```

### 2.7 work_flow_basic_data

```http
POST http://192.168.10.7:22222/tsl_data/source/update/work_flow_basic_data
Content-Type: application/json

```

### 2.8 cem_mobile_experience_list

```http
POST http://192.168.10.7:22222/tsl_data/source/warehouse/cem_mobile_experience_list
Content-Type: application/json

20240117
```

## 取消sheet2客户端考核， 新增 服务请求压降

```sql
create table tsl_data.cem_mobile_experience_list (
  id bigserial NOT null,
  month_id varchar(6) null,
  day_id varchar(2) null,
  service_id varchar(300) null,
  contact_id varchar(300) null,
  create_time varchar(8) null,
  mobile_no varchar(300) null,
  service_type_name1 varchar(300) null,
  pro_name varchar(30) null,
  code_cust_area varchar(30) null,
  zyx varchar(300) null,
  lwly_name varchar(300) null,
  big_type_name varchar(300) null,
  small_type_name varchar(300) null,
  acct_month varchar(24) null,
  day_id1 varchar(6) null,
  sheet_no varchar(300) null,
  compl_city_local varchar(300) null,
  CONSTRAINT cem_mobile_experience_list_pkey PRIMARY KEY (id)
)
```