<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zjrs.ggfw.sggfw.entity.DwzpxxSbMapper">

    <select id="getWsbList" resultType="com.zjrs.ggfw.sggfw.po.DwZpxxPO">
        select a.rs_rlzy_zwxx_lsh as ACB200,c.ztid as AAB001,b.AAB998,e.AAB004 as AAB004,F_RLZY_TRANSCODE('JBXX_AAB022',nvl(e.AAB022,'1583')) AAB022,
        F_RLZY_TRANSCODE('RS_RLZY_ZYXX',a.zpgz) as BCB566,(select tcoden from RS_RLZY_SJSB_CNVCODES where scode  = a.zpgz and code_type = 'RS_RLZY_ZYXX') as BCB563,
        F_RLZY_TRANSCODE('RS_RLZY_ZPFS',a.zpfs) as ACB201,a.ts_cjsj as AAE043,a.ts_xgsj as ACE014,a.ts_xgsj as BAE005,
        e.AAB003,e.AAB007, F_RLZY_TRANSCODE('RS_RLZY_YGXX',a.ygxs) as AAC013,a.ts_cjsj as BAE003,
        a.gzms as BCE055,a.zprs as BCB573,a.rs_nan as BCB574,a.rs_nv as BCB575,F_RLZY_TRANSCODE('JBXX_AAC011',a.whcd) as AAC011,a.lxdh as BCD071,
        (case
        when  substr(a.xzqh,1,6) is null  then '4408'
        when  substr(a.xzqh,1,6) = '440800' then '4408'
        when substr(a.xzqh,1,6) = '440840' then '4408'
        when substr(a.xzqh,5,6) = '00' then substr(a.xzqh,1,4)
        else
        substr(a.xzqh,1,6)
        end
        ) as AAB301,
        b.hrmc as BCB580,
        F_RLZY_TRANSCODE('ACB241',a.xz) as ACB241,F_RLZY_TRANSCODE('ACB242',a.xz) as ACB242
        from rs_rlzy_zwxx a join
        rs_rlzy_qyxx b on a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        join rs_ggfw_user c on c.userid = b.rs_yhlsh
        join ab01 e on e.ztid = c.ztid
        where a.SHJG = '1' and a.ts_yxbz = '1'
        and not exists(
        select 1 from RS_RLZY_SJSB_PUSHDATAS d where a.rs_rlzy_zwxx_lsh = d.sid
        )
    </select>


    <select id="xyUpdateDwzpxxList" resultType="com.zjrs.ggfw.sggfw.po.DwZpxxPO">
        select a.rs_rlzy_zwxx_lsh as ACB200,c.ztid as AAB001,b.AAB998,f.AAB004 as AAB004,F_RLZY_TRANSCODE('JBXX_AAB022',nvl(f.AAB022,'1583')) AAB022,
        F_RLZY_TRANSCODE('RS_RLZY_ZYXX',a.zpgz) as BCB566,(select tcoden from RS_RLZY_SJSB_CNVCODES where scode  = a.zpgz and code_type = 'RS_RLZY_ZYXX') as BCB563,
        F_RLZY_TRANSCODE('RS_RLZY_ZPFS',a.zpfs) as ACB201,a.ts_cjsj as AAE043,a.ts_xgsj as ACE014,a.ts_xgsj as BAE005,
        f.AAB003,f.AAB007, F_RLZY_TRANSCODE('RS_RLZY_YGXX',a.ygxs) as AAC013,a.ts_cjsj as BAE003,
        a.gzms as BCE055,a.zprs as BCB573,a.rs_nan as BCB574,a.rs_nv as BCB575,F_RLZY_TRANSCODE('JBXX_AAC011',a.whcd) as AAC011,a.lxdh as BCD071,
        (case
        when  substr(a.xzqh,1,6) is null  then '4408'
        when  substr(a.xzqh,1,6) = '440800' then '4408'
        when substr(a.xzqh,1,6) = '440840' then '4408'
        when substr(a.xzqh,5,6) = '00' then substr(a.xzqh,1,4)
        else
        substr(a.xzqh,1,6)
        end
        ) as AAB301,
        b.hrmc as BCB580,
        F_RLZY_TRANSCODE('ACB241',a.xz) as ACB241,F_RLZY_TRANSCODE('ACB242',a.xz) as ACB242
        from rs_rlzy_zwxx a join
        rs_rlzy_qyxx b on a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        join rs_ggfw_user c on c.userid = b.rs_yhlsh
        join RS_RLZY_SJSB_PUSHDATAS d on a.rs_rlzy_zwxx_lsh = d.sid
        join ab01 f on c.ztid = f.ztid
        where a.SHJG = '1' and a.ts_yxbz = '1' and a.ts_xgsj != d.xgsj
    </select>

    <select id="xyDelDwzpxxList" resultType="com.zjrs.ggfw.sggfw.po.DwZpxxPO">
        select  a.rs_rlzy_zwxx_lsh as ACB200,c.ztid as AAB001,b.AAB998,f.AAB004 as AAB004,F_RLZY_TRANSCODE('JBXX_AAB022',nvl(f.AAB022,'1583')) AAB022,
        F_RLZY_TRANSCODE('RS_RLZY_ZYXX',a.zpgz) as BCB566,(select tcoden from RS_RLZY_SJSB_CNVCODES where scode  = a.zpgz and code_type = 'RS_RLZY_ZYXX') as BCB563,
        F_RLZY_TRANSCODE('RS_RLZY_ZPFS',a.zpfs) as ACB201,a.ts_cjsj as AAE043,a.ts_xgsj as ACE014,a.ts_xgsj as BAE005,
        f.AAB003,f.AAB007, F_RLZY_TRANSCODE('RS_RLZY_YGXX',a.ygxs) as AAC013,a.ts_cjsj as BAE003,
        a.gzms as BCE055,a.zprs as BCB573,a.rs_nan as BCB574,a.rs_nv as BCB575,F_RLZY_TRANSCODE('JBXX_AAC011',a.whcd) as AAC011,a.lxdh as BCD071,
        (case
        when  substr(a.xzqh,1,6) is null  then '4408'
        when  substr(a.xzqh,1,6) = '440800' then '4408'
        when substr(a.xzqh,1,6) = '440840' then '4408'
        when substr(a.xzqh,5,6) = '00' then substr(a.xzqh,1,4)
        else
        substr(a.xzqh,1,6)
        end
        ) as AAB301,
        b.hrmc as BCB580,
        F_RLZY_TRANSCODE('ACB241',a.xz) as ACB241,F_RLZY_TRANSCODE('ACB242',a.xz) as ACB242
        from rs_rlzy_zwxx a  join
        rs_rlzy_qyxx b on a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        join rs_ggfw_user c on c.userid = b.rs_yhlsh
        join RS_RLZY_SJSB_PUSHDATAS d on a.rs_rlzy_zwxx_lsh = d.sid
        join ab01 f on c.ztid = f.ztid
        where a.ts_yxbz = '0' and d.ts_yxbz = '1'
    </select>
</mapper>
