写卡记录补全 iccid
联调平台
目前写卡记录表没有 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