联调平台

目前写卡记录表没有 iccid，只有 imsi，根据 imsi 补全 iccid，上报到芯片管理平台

write_card 表添加 iccid 相关字段

alter table writecard_work add  old_iccid varchar(50) DEFAULT null COMMENT 'oldIccid';
alter table writecard_work add  new_iccid varchar(50) DEFAULT null COMMENT 'newIccid';

补全 iccid

update writecard_work ww set ww.old_iccid=(select ee.iccid from esim_ec ee where ee.imsi =ww.old_imsi),
ww.new_iccid=(select ee.iccid from esim_ec ee where ee.imsi =ww.new_imsi);

写卡记录取前一天写完的记录

添加 update_time

alter table writecard_work add  update_time datetime(0) DEFAULT null COMMENT '更新时间';

获取更新时间为前一天，并且 is_end 为 1 的数据

select
id, user_id, create_time, usesdk, eid, imei, old_msisdn, old_imsi, new_msisdn, new_imsi,
create_result, to_ctboss, examine_time, examine_status, ota_req_time, ota_req_message,
ota_ready_time, ota_ready_message, ota_start_time, ota_start_message, send_writecard_id,
send_wrtiecard_time, send_writecard_result, status, is_end, has_success_result,update_time,old_iccid,new_iccid
from writecard_work
where 1=1
and is_end=1
<if test="startTime != null and startTime != ''">
	and update_time >= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
	and update_time <![CDATA[<=]]> #{endTime}
</if>
order by id desc

补全 updateTime

update writecard_work ww set update_time=create_time where update_time is null;

芯片管理平台

writecard_process_record 表中只有 msisdn 没有 iccid，从 phone_number_gprs_status 获取码号对应的 iccid

写卡记录表添加 iccid

alter table write_card_records add  old_iccid varchar(50) DEFAULT null COMMENT 'oldIccid';
alter table write_card_records add  new_iccid varchar(50) DEFAULT null COMMENT 'newIccid';

alter table write_card_records_history add  old_iccid varchar(50) DEFAULT null COMMENT 'oldIccid';
alter table write_card_records_history add  new_iccid varchar(50) DEFAULT null COMMENT 'newIccid';

写卡记录

SELECT
<include refid="com.chinamobile.dao.WritecardProcessRecordMapper.Base_Column_List"></include>
FROM writecard_process_record w
WHERE 1=1
and finish_date<![CDATA[ >=]]>#{startTime}
and finish_date<![CDATA[ <=]]>#{endTime}
and status in ('3','4')

writecard_process_record 获取当前已经写完的卡而不是刚创建的写卡记录 finish_date 为前一天，并且 status 为 3-- 写卡操作结束，失败 4-- 写卡操作结束，成功的记录

空写记录状态码（0-- 等待临时号审批 1-- 等待 OneNet 通知设备上电 2-- 已发起写卡，等待反馈 3-- 写卡操作结束，失败 4-- 写卡操作结束，成功）5-- 写卡撤销 6-- 写卡超时

根据码号获取 iccid

select
<include refid="com.chinamobile.dao.PhoneNumberInfoFromCtbossMapper.Base_Column_List" />
from phone_number_info_from_ctboss
where phone_number = #{phoneNumber}

写卡平台

write_log 表中有 iccid

写卡记录

写卡记录取 end_time 是前一天，并且 status 是 1- 写卡失败 2 写卡成功的记录

select
<include refid="com.cmiot.airwrite.dao.idao.WriteLogMapper.Base_Column_List"/>
from write_log
where
1=1
and status in ('1','2')
<if test="startTime != null and startTime != ''">
	and end_time >= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
	and end_time <![CDATA[<=]]> #{endTime}
</if>
order by id desc

获取码号是否写卡功能
上一篇 多次写卡时码号和各种参数的对应关系
芯片管理平台接口目录