fj-dc/.svn/pristine/cb/cbcececb1f7dda6dcbba934b7dd01481ca96af9a.svn-base

<?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="cn.com.goldenwater.dcproj.dao.TacPblmInfoDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.TacPblmInfo" id="tacPblmInfoResultMap">
        <result property="id" column="ID"/>
        <result property="objType" column="OBJ_TYPE"/>
        <result property="objId" column="OBJ_ID"/>
        <result property="pblmPasi" column="PBLM_PASI"/>
        <result property="ifCasePblm" column="IF_CASE_PBLM"/>
        <result property="subjectIds" column="SUBJECT_IDS"/>
        <result property="subjectNames" column="subject_Names"/>
        <result property="pblmDesc" column="PBLM_DESC"/>
        <result property="pblmReason" column="PBLM_REASON"/>
        <result property="pblmSggtn" column="PBLM_SGGTN"/>
        <result property="pblmSn" column="PBLM_SN"/>
        <result property="pblmstdId" column="PBLMSTD_ID"/>
        <result property="rectId" column="RECT_ID"/>
        <result property="rgstrId" column="RGSTR_ID"/>
        <result property="listType" column="LIST_TYPE"/>
        <result property="listId" column="LIST_ID"/>
        <result property="groupId" column="GROUP_ID"/>
        <result property="persId" column="PERS_ID"/>
        <result property="persNm" column="PERS_NM"/>
        <result property="intm" column="INTM"/>
        <result property="spclId" column="SPCL_ID"/>
        <result property="spclNm" column="SPCL_NM"/>
        <result property="pblmNm" column="PBLM_NM"/>
        <result property="hasVedio" column="HAS_VEDIO"/>
        <result property="spclRvwOptn" column="SPCL_RVW_OPTN"/>
        <result property="spclTm" column="SPCL_TM"/>
        <result property="uptm" column="UPTM"/>
        <result property="note" column="NOTE"/>
        <result property="sn" column="SN"/>
        <result property="pblmQlttvCd" column="PBLM_QLTTV_CD"/>
        <result property="pblmQlttvNm" column="PBLM_QLTTV_NM"/>
        <result property="isCpQa" column="IS_CP_QA"/>
        <result property="dataStat" column="DATA_STAT"/>
        <result property="province" column="PROVINCE"/>
        <result property="name" column="NAME"/>
        <result property="location" column="LOCATION"/>
        <result property="adName" column="AD_NAME"/>
        <result property="adCode" column="AD_CODE"/>
        <result property="pblmTypeDesc" column="PBLM_TYPE_DESC"/>
        <result property="reasonState" column="REASON_STATE"/>
        <result property="pblmReasonDetail" column="PBLM_REASON_DETAIL"/>
        <result property="pblmMeasure" column="PBLM_MEASURE"/>
        <result property="pblmstdSn" column="PBLMSTD_SN"/>
        <result property="isPblmStbOth" column="IS_PBLM_STB_OTH"/>
        <result property="pblmStbOthCate" column="PBLM_STB_OTH_CATE"/>
        <result property="pblmStbOthDesc" column="PBLM_STB_OTH_DESC"/>
        <result property="relativeLaw" column="RELATIVE_LAW"/>
        <result property="lawContent" column="LAW_CONTENT"/>
    </resultMap>

    <sql id="table_columns">
        ID,PBLM_TYPE_DESC,
        OBJ_TYPE,
        OBJ_ID,
        PBLM_PASI,
        IF_CASE_PBLM,
        SUBJECT_IDS,
        PBLM_DESC,
        PBLM_REASON,
        PBLM_SGGTN,
        PBLM_SN,
        PBLMSTD_ID,
        RECT_ID,
        RGSTR_ID,
        LIST_TYPE,
        LIST_ID,
        GROUP_ID,
        PERS_ID,
        PERS_NM,
        INTM,
        SPCL_ID,
        SPCL_NM,
        SPCL_RVW_OPTN,
        SPCL_TM,
        UPTM,PBLM_NM,HAS_VEDIO,subject_Names,NOTE,
        SN,PBLM_QLTTV_CD,PBLM_QLTTV_NM,IS_CP_QA,DATA_STAT,PROVINCE,REASON_STATE,
        PBLM_REASON_DETAIL
        ,PBLM_MEASURE
        ,IS_PBLM_STB_OTH
        ,PBLM_STB_OTH_CATE
        ,PBLM_STB_OTH_DESC
        ,RELATIVE_LAW
        ,LAW_CONTENT
    </sql>
    <sql id="entity_properties">
        #{id},#{pblmTypeDesc},
        #{objType},
        #{objId},
        #{pblmPasi},
        #{ifCasePblm},
        #{subjectIds},
        #{pblmDesc},
        #{pblmReason}, #{pblmSggtn}, #{pblmSn},
        #{pblmstdId}, #{rectId}, #{rgstrId},
        #{listType},
        #{listId},
        #{groupId},
        #{persId},
        #{persNm},
        #{intm},
        #{spclId},
        #{spclNm},
        #{spclRvwOptn},
        #{spclTm},
        #{uptm},#{pblmNm},#{hasVedio},#{subjectNames},#{note},
        #{sn},#{pblmQlttvCd},#{pblmQlttvNm},#{isCpQa},#{dataStat},#{province},
        #{reasonState},
        #{pblmReasonDetail},
        #{pblmMeasure},
        #{isPblmStbOth},
        #{pblmStbOthCate},
        #{pblmStbOthDesc},
        #{relativeLaw},
        #{lawContent}
    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="isPblmStbOth != null and isPblmStbOth != ''">and IS_PBLM_STB_OTH = #{isPblmStbOth}</if>
            <if test="pblmStbOthCate != null and pblmStbOthCate != ''">and PBLM_STB_OTH_CATE = #{pblmStbOthCate}</if>
            <if test="pblmStbOthDesc != null and pblmStbOthDesc != ''">and PBLM_STB_OTH_DESC = #{pblmStbOthDesc}</if>
            <if test="relativeLaw != null and relativeLaw != ''">and RELATIVE_LAW like '%${relativeLaw}%'</if>
            <if test="lawContent != null and lawContent != ''">and LAW_CONTENT like '%${lawContent}%' </if>
            <if test="pblmQlttvCd != null and pblmQlttvCd != ''">and PBLM_QLTTV_CD = #{pblmQlttvCd}</if>
            <if test="pblmQlttvNm != null and pblmQlttvNm != ''">and PBLM_QLTTV_NM = #{pblmQlttvNm}</if>
            <if test="isCpQa != null and isCpQa != ''">and IS_CP_QA = #{isCpQa}</if>
            <if test="objType != null and objType != ''">and OBJ_TYPE = #{objType}</if>
            <if test="pblmTypeDesc != null and pblmTypeDesc != ''">and pblm_Type_Desc = #{pblmTypeDesc}</if>
            <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
            <if test="pblmPasi != null and pblmPasi != ''">and PBLM_PASI = #{pblmPasi}</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="subjectIds != null and subjectIds != ''">and SUBJECT_IDS = #{subjectIds}</if>
            <if test="subjectNames != null and subjectNames != ''">and subject_Names like '%${subjectNames}%'</if>
            <if test="pblmDesc != null and pblmDesc != ''">and PBLM_DESC like '%${pblmDesc}%'</if>
            <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
            <if test="pblmNm != null and pblmNm != ''">and PBLM_NM like '%${pblmNm}%'</if>
            <if test="pblmReason != null and pblmReason != ''">and PBLM_REASON like '%${pblmReason}%'</if>
            <if test="pblmSggtn != null and pblmSggtn != ''">and PBLM_SGGTN like '%${pblmSggtn}%'</if>
            <if test="pblmSn != null and pblmSn != ''">and PBLM_SN = #{pblmSn}</if>
            <if test="pblmstdId != null and pblmstdId != ''">and PBLMSTD_ID = #{pblmstdId}</if>
            <if test="rectId != null and rectId != ''">and RECT_ID = #{rectId}</if>
            <if test="rgstrId != null and rgstrId != ''">and RGSTR_ID = #{rgstrId}</if>
            <if test="listType != null and listType != ''">and LIST_TYPE = #{listType}</if>
            <if test="listId != null and listId != ''">and LIST_ID = #{listId}</if>
            <if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
            <if test="province != null and province != ''">and PROVINCE = #{province}</if>
            <if test="persId != null and persId != ''">and PERS_ID = #{persId}</if>
            <if test="persNm != null and persNm != ''">and PERS_NM like '%${persNm}%'</if>
            <if test="intm != null">and INTM = #{intm}</if>
            <if test="spclId != null and spclId != ''">and SPCL_ID = #{spclId}</if>
            <if test="spclNm != null and spclNm != ''">and SPCL_NM like '%${spclNm}%'</if>
            <if test="spclRvwOptn != null and spclRvwOptn != ''">and SPCL_RVW_OPTN like '%${spclRvwOptn}%'</if>
            <if test="spclTm != null">and SPCL_TM = #{spclTm}</if>
            <if test="uptm != null">and UPTM = #{uptm}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="sn != null and sn != ''">and SN = #{sn}</if>
            <if test="dataStat != null and dataStat != ''">and data_Stat = #{dataStat}</if>
            <if test="reasonState != null and reasonState != ''">and REASON_STATE = #{reasonState}</if>
            <if test="pblmReasonDetail != null and pblmReasonDetail != ''">and PBLM_REASON_DETAIL = #{pblmReasonDetail}</if>
            <if test="pblmMeasure != null and pblmMeasure != ''">and PBLM_MEASURE = #{pblmMeasure}</if>
        </trim>
    </sql>

    <sql id="mysql">
        <if test="objType != null and objType != ''">and OBJ_TYPE = #{objType}</if>
        <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
        <if test="pblmPasi != null and pblmPasi != ''">and PBLM_PASI = #{pblmPasi}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="subjectIds != null and subjectIds != ''">and SUBJECT_IDS = #{subjectIds}</if>
        <if test="subjectNames != null and subjectNames != ''">and subject_Names like '%${subjectNames}%'</if>
        <if test="pblmDesc != null and pblmDesc != ''">and PBLM_DESC like '%${pblmDesc}%'</if>
        <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
        <if test="pblmNm != null and pblmNm != ''">
            and PBLM_NM like '%${pblmNm}%'
        </if>
        <if test="pblmTypeDesc != null and pblmTypeDesc != ''">and pblm_Type_Desc = #{pblmTypeDesc}</if>

        <if test="pblmReason != null and pblmReason != ''">and PBLM_REASON like '%${pblmReason}%'</if>
        <if test="pblmSggtn != null and pblmSggtn != ''">and PBLM_SGGTN like '%${pblmSggtn}%'</if>
        <if test="relativeLaw != null and relativeLaw != ''">and RELATIVE_LAW like '%${relativeLaw}%'</if>
        <if test="lawContent != null and lawContent != ''">and LAW_CONTENT like '%${lawContent}%' </if>
        <if test="pblmSn != null and pblmSn != ''">and PBLM_SN = #{pblmSn}</if>
        <if test="pblmstdId != null and pblmstdId != ''">and PBLMSTD_ID = #{pblmstdId}</if>
        <if test="rectId != null and rectId != ''">and RECT_ID = #{rectId}</if>
        <if test="rgstrId != null and rgstrId != ''">and RGSTR_ID = #{rgstrId}</if>
        <if test="listType != null and listType != ''">and LIST_TYPE = #{listType}</if>
        <if test="listId != null and listId != ''">and LIST_ID = #{listId}</if>
        <if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
        <if test="sn != null and sn != ''">and SN = #{sn}</if>
        <if test="pblmQlttvCd != null and pblmQlttvCd != ''">and PBLM_QLTTV_CD = #{pblmQlttvCd}</if>
        <if test="pblmQlttvNm != null and pblmQlttvNm != ''">and PBLM_QLTTV_NM = #{pblmQlttvNm}</if>
        <if test="isCpQa != null and isCpQa != ''">and IS_CP_QA = #{isCpQa}</if>
        <if test="dataStat != null and dataStat != ''">and data_Stat = #{dataStat}</if>
        <if test="reasonState != null and reasonState != ''">and REASON_STATE = #{reasonState}</if>
        <if test="pblmMeasure != null and pblmMeasure != ''">and PBLM_MEASURE = #{pblmMeasure}</if>
        <if test="pblmReasonDetail != null and pblmReasonDetail != ''">and PBLM_REASON_DETAIL = #{pblmReasonDetail}</if>
    </sql>

    <select id="get" resultMap="tacPblmInfoResultMap" parameterType="String">
        select t.*,r.name,r.location,h.ad_name,r.ad_code from tac_pblm_info t ,tac_pawp_rgstr r,att_ad_X_base h
        where substr(t.rgstr_id,1,32)=r.id and r.ad_code=h.ad_code and t.id=#{id}
    </select>

    <select id="getBy" resultMap="tacPblmInfoResultMap">
        select
        <include refid="table_columns"/>
        from TAC_PBLM_INFO t
        <include refid="page_where"/>
    </select>


    <select id="findAll" resultMap="tacPblmInfoResultMap">
        select
        <include refid="table_columns"/>
        from TAC_PBLM_INFO t where 1=1
        <include refid="choseSql"/>
    </select>

    <select id="findPblmList" resultMap="tacPblmInfoResultMap">
        select * from tac_pblm_info t where 1=1
        <include refid="mysql"/>
        <include refid="choseSql"/>
    </select>

    <select id="findList" resultMap="tacPblmInfoResultMap">
        select t.* from (
        select t.*,r.name,r.location,r.ad_code from tac_pblm_info t ,tac_pawp_rgstr r where substr(t.rgstr_id,1,32)=r.id
        ) t where 1=1
        <if test="persId != null and persId != ''">and t.pers_Id = #{persId}</if>
        <include refid="commonSql"/>
    </select>

    <select id="findListBy" resultMap="tacPblmInfoResultMap">
        select
        <include refid="table_columns"/>
        from tac_pblm_info t
        <include refid="page_where"/>
    </select>
    <select id="selectCount" resultType="int">
        select count(ID) from TAC_PBLM_INFO t
        <include refid="page_where"/>
    </select>

    <select id="findPageInfoNew" resultMap="tacPblmInfoResultMap">
        select t.*,r.name,r.location,r.ad_code from TAC_PBLM_INFO t ,tac_pawp_rgstr r where t.rgstr_id=r.id
        <include refid="commonSql"/>
    </select>

    <select id="findProvincePblmInfo" resultMap="tacPblmInfoResultMap">
        select t.*,r.name,r.location,r.ad_code from TAC_PROVINCE_PBLM_INFO t ,tac_pawp_rgstr r where t.rgstr_id=r.id
        <if test="stepId != null and stepId != ''">and t.STEP_ID = #{stepId}</if>
        <include refid="commonSql"/>
    </select>

    <select id="findPageInfoNew2" resultMap="tacPblmInfoResultMap">
        SELECT T.*,R.NAME,R.LOCATION,R.AD_CODE
        FROM ${pblmTableName} T ,TAC_PAWP_RGSTR R
        WHERE T.RGSTR_ID=R.ID
        <if test="stepId != null and stepId != ''">
            AND t.TASK_ID = #{stepId}
        </if>
        <include refid="commonSql"/>
    </select>

    <select id="findPageJieduanInfo" resultMap="tacPblmInfoResultMap">
        SELECT T.*,R.LOCATION,R.NAME,R.AD_CODE
        FROM TAC_PBLM_INFO T,TAC_PAWP_RGSTR R
        WHERE R.ID=SUBSTR(T.RGSTR_ID,1,32)
        AND LENGTH(T.RGSTR_ID) &gt;32
        <include refid="jieduanSql"/>
        order by T.OBJ_TYPE, t.pblm_Pasi, t.SN
    </select>

    <select id="findPageJieduanZLorTPY" resultMap="tacPblmInfoResultMap">
        select t.*,r.location,r.name,r.ad_code from TAC_PAWP_RGSTR r,tac_pblm_info t where r.id=substr(t.rgstr_id,1,32)
        and length(t.rgstr_id) &gt;32
        and r.group_id in (select group_id from TAC_INSP_YEAR_BATCH_GROUP_PERS p
        where p.pers_id in (select id from TAC_WORKER_B b where b.login_id=#{persId}))
        <include refid="jieduanSql"/>
        <include refid="mySearch"/>
        order by T.OBJ_TYPE, t.pblm_Pasi, t.SN
    </select>

    <sql id="jieduanSql">
        <if test="rgstrId != null and rgstrId != ''">and t.RGSTR_ID like '${rgstrId}%'</if>
        <include refid="mySearch"/>
    </sql>

    <sql id="commonSql">
        <if test="rgstrId != null and rgstrId != ''">and t.RGSTR_ID = #{rgstrId}</if>
        <include refid="mySearch"/>
    </sql>

    <sql id="mySearch">
        <if test="listType != null and listType != ''">and t.LIST_TYPE = #{listType}</if>
        <if test="hasVedio != null and hasVedio != ''">and t.HAS_VEDIO = #{hasVedio}</if>
        <if test="pblmTypeDesc != null and pblmTypeDesc != ''">and pblm_Type_Desc = #{pblmTypeDesc}</if>
        <if test="pblmPasi != null and pblmPasi != ''">and t.PBLM_PASI = #{pblmPasi}</if>
        <if test="pblmQlttvCd != null and pblmQlttvCd != ''">and t.PBLM_QLTTV_CD = #{pblmQlttvCd}</if>
        <if test="pblmQlttvNm != null and pblmQlttvNm != ''">and t.PBLM_QLTTV_NM = #{pblmQlttvNm}</if>
        <if test="adCode != null and adCode != ''">and r.AD_CODE = #{adCode}</if>
        <if test="objId != null and objId != ''">and r.OBJ_ID = #{objId}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="subjectNames != null and subjectNames != ''">and subject_Names like '%${subjectNames}%'</if>
        <if test="groupId != null and groupId != ''">and r.GROUP_ID = #{groupId}</if>
        <include refid="choseSql"/>
        <if test="pblmNm != null and pblmNm != ''">
            and PBLM_NM like '%${pblmNm}%'
        </if>
        order by T.OBJ_TYPE, t.pblm_Pasi, t.SN
    </sql>

    <select id="findPageInfoZLorTPY" resultMap="tacPblmInfoResultMap">
        select t.*,r.location,r.name,r.ad_code from TAC_PAWP_RGSTR r,tac_pblm_info t where t.rgstr_id=r.id
        and r.group_id in (select group_id from TAC_INSP_YEAR_BATCH_GROUP_PERS p
        where p.pers_id in (select id from TAC_WORKER_B b where b.login_id=#{persId}))
        <include refid="commonSql"/>
    </select>

    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.TacPblmInfo">
        insert into TAC_PBLM_INFO(
        <include refid="table_columns"/>
        )
        values (
        <include refid="entity_properties"/>
        )
    </insert>

    <delete id="delete" parameterType="java.lang.String">
        delete from TAC_PBLM_INFO where ID = #{id}
    </delete>

    <delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.TacPblmInfo">
        delete from TAC_PBLM_INFO
        <include refid="page_where"/>
    </delete>

    <update id="deleteInFlag" parameterType="java.lang.String">
        update TAC_PBLM_INFO set flag_valid = 0 where ID = #{id}
    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.TacPblmInfo">
        update TAC_PBLM_INFO
        <trim prefix="set" suffixOverrides=",">
            <if test="isPblmStbOth != null and isPblmStbOth != ''">IS_PBLM_STB_OTH = #{isPblmStbOth},</if>
            <if test="pblmStbOthCate != null">PBLM_STB_OTH_CATE = #{pblmStbOthCate},</if>
            <if test="pblmStbOthDesc != null">PBLM_STB_OTH_DESC = #{pblmStbOthDesc},</if>
            <if test="relativeLaw != null "> RELATIVE_LAW = #{relativeLaw} , </if>
            <if test="lawContent != null "> LAW_CONTENT = #{lawContent}, </if>
            <if test="pblmQlttvCd != null and pblmQlttvCd != ''">PBLM_QLTTV_CD = #{pblmQlttvCd},</if>
            <if test="pblmQlttvNm != null and pblmQlttvNm != ''">PBLM_QLTTV_NM = #{pblmQlttvNm},</if>
            <if test="isCpQa != null and isCpQa != ''">IS_CP_QA = #{isCpQa},</if>
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="pblmPasi != null">PBLM_PASI = #{pblmPasi},</if>
            <if test="ifCasePblm != null">IF_CASE_PBLM = #{ifCasePblm},</if>
            <if test="subjectIds != null">SUBJECT_IDS = #{subjectIds},</if>
            <if test="pblmTypeDesc != null and pblmTypeDesc != ''">pblm_Type_Desc = #{pblmTypeDesc},</if>

            <if test="pblmDesc != null">PBLM_DESC = #{pblmDesc},</if>
            <if test="pblmReason != null">PBLM_REASON = #{pblmReason},</if>
            <if test="pblmSggtn != null">PBLM_SGGTN = #{pblmSggtn},</if>
            <if test="pblmSn != null">PBLM_SN = #{pblmSn},</if>
            <if test="province != null and province != ''">PROVINCE = #{province},</if>
            <if test="pblmstdId != null">PBLMSTD_ID = #{pblmstdId},</if>
            <if test="subjectNames != null ">subject_Names = #{subjectNames},</if>
            <if test="rectId != null and rectId != ''">RECT_ID = #{rectId},</if>
            <if test="rgstrId != null and rgstrId != ''">RGSTR_ID = #{rgstrId},</if>
            <if test="listType != null">LIST_TYPE = #{listType},</if>
            <if test="listId != null">LIST_ID = #{listId},</if>
            <if test="groupId != null">GROUP_ID = #{groupId},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
            <if test="persNm != null and persNm != ''">PERS_NM = #{persNm},</if>
            <if test="hasVedio != null">HAS_VEDIO = #{hasVedio},</if>
            <if test="pblmNm != null">PBLM_NM = #{pblmNm},</if>
            <if test="intm != null">INTM = #{intm},</if>
            <if test="spclId != null and spclId != ''">SPCL_ID = #{spclId},</if>
            <if test="spclNm != null and spclNm != ''">SPCL_NM = #{spclNm},</if>
            <if test="spclRvwOptn != null">SPCL_RVW_OPTN = #{spclRvwOptn},</if>
            <if test="spclTm != null">SPCL_TM = #{spclTm},</if>
            <if test="uptm != null">UPTM = #{uptm},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="dataStat != null and dataStat != ''">data_Stat = #{dataStat},</if>
            <if test="reasonState != null and reasonState != ''">REASON_STATE = #{reasonState},</if>
            <if test="pblmReasonDetail != null and pblmReasonDetail != ''">PBLM_REASON_DETAIL = #{pblmReasonDetail},</if>
            <if test="pblmMeasure != null and pblmMeasure != ''">PBLM_MEASURE = #{pblmMeasure},</if>
            SN = #{sn},
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.TacPblmInfo">
        update TAC_PBLM_INFO
        <trim prefix="set" suffixOverrides=",">
            <if test="isPblmStbOth != null and isPblmStbOth != ''">IS_PBLM_STB_OTH = #{isPblmStbOth},</if>
            <if test="pblmStbOthCate != null and pblmStbOthCate != ''">PBLM_STB_OTH_CATE = #{pblmStbOthCate},</if>
            <if test="pblmStbOthDesc != null and pblmStbOthDesc != ''">PBLM_STB_OTH_DESC = #{pblmStbOthDesc},</if>
            <if test="relativeLaw != null "> RELATIVE_LAW = #{relativeLaw} , </if>
            <if test="lawContent != null "> LAW_CONTENT = #${lawContent}, </if>
            <if test="pblmQlttvCd != null and pblmQlttvCd != ''">PBLM_QLTTV_CD = #{pblmQlttvCd},</if>
            <if test="pblmQlttvNm != null and pblmQlttvNm != ''">PBLM_QLTTV_NM = #{pblmQlttvNm},</if>
            <if test="isCpQa != null and isCpQa != ''">IS_CP_QA = #{isCpQa},</if>
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="pblmTypeDesc != null and pblmTypeDesc != ''">pblm_Type_Desc = #{pblmTypeDesc},</if>
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="pblmPasi != null">PBLM_PASI = #{pblmPasi},</if>
            <if test="ifCasePblm != null">IF_CASE_PBLM = #{ifCasePblm},</if>
            <if test="subjectIds != null">SUBJECT_IDS = #{subjectIds},</if>
            <if test="pblmDesc != null">PBLM_DESC = #{pblmDesc},</if>
            <if test="pblmReason != null">PBLM_REASON = #{pblmReason},</if>
            <if test="pblmSggtn != null">PBLM_SGGTN = #{pblmSggtn},</if>
            <if test="subjectNames != null">subject_Names = #{subjectNames},</if>
            <if test="pblmSn != null">PBLM_SN = #{pblmSn},</if>
            <if test="pblmstdId != null">PBLMSTD_ID = #{pblmstdId},</if>
            <if test="province != null and province != ''">PROVINCE = #{province},</if>
            <if test="rectId != null and rectId != ''">RECT_ID = #{rectId},</if>
            <if test="rgstrId != null and rgstrId != ''">RGSTR_ID = #{rgstrId},</if>
            <if test="listType != null">LIST_TYPE = #{listType},</if>
            <if test="listId != null">LIST_ID = #{listId},</if>
            <if test="groupId != null">GROUP_ID = #{groupId},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
            <if test="persNm != null">PERS_NM = #{persNm},</if>
            <if test="intm != null">INTM = #{intm},</if>
            <if test="spclId != null and spclId != ''">SPCL_ID = #{spclId},</if>
            <if test="spclNm != null">SPCL_NM = #{spclNm},</if>
            <if test="spclRvwOptn != null">SPCL_RVW_OPTN = #{spclRvwOptn},</if>
            <if test="spclTm != null">SPCL_TM = #{spclTm},</if>
            <if test="uptm != null">UPTM = #{uptm},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="dataStat != null and dataStat != ''">data_Stat = #{dataStat},</if>
            <if test="reasonState != null and reasonState != ''">REASON_STATE = #{reasonState},</if>
            <if test="pblmReasonDetail != null and pblmReasonDetail != ''">PBLM_REASON_DETAIL = #{pblmReasonDetail},</if>
            <if test="pblmMeasure != null and pblmMeasure != ''">PBLM_MEASURE = #{pblmMeasure},</if>
            SN = #{sn},
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->

    <select id="getPblmCountByYear" resultType="java.lang.Integer"
            parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam">
        select case when count(*) is NULL then 0 else count(*) end from TAC_PBLM_INFO t left join
        TAC_INSP_YEAR_BATCH_OBJ o on t.obj_id = o.id where o.id is not null and o.year = #{year}
        <if test="persId != null and persId != ''">
            and o.group_id in (select t.group_id from TAC_INSP_YEAR_BATCH_GROUP_PERS t WHERE PERS_ID = (select b.id from
            TAC_WORKER_B b where b.login_id = #{persId})
            )
        </if>
        <include refid="choseSql"/>
    </select>

    <select id="getMaxSn" resultType="String">
        SELECT Max(sn) from TAC_PBLM_INFO t where 1=1
        <include refid="choseSql"/>
    </select>

    <sql id="choseSql">
        <choose>
            <when test="province !=null and province !=''">
                and t.province=#{province}
            </when>
            <otherwise>
                and t.province is null
            </otherwise>
        </choose>
    </sql>
    <select id="countPblmInfoByType" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select PBLM_PASI as code,count(*) as count from (
        select * from TAC_PBLM_INFO t left join TAC_PAWP_RGSTR r on t.obj_id = r.obj_id left join
        TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        where r.id is not null and t.pblm_pasi is not null
        <include refid="choseSql"/>
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>

        )w group by PBLM_PASI
    </select>

    <select id="countPblmInfoBySub" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select t.sub_id as code,t.pblm_pasi as name, count(*) as count from (
        select distinct t.id,t.pblm_pasi,s.sub_id from TAC_PBLM_INFO t left join TAC_PAWP_RGSTR r on t.obj_id = r.obj_id
        left join TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        left join TAC_PBLM_SUBJECT s on t.id = s.pblm_id
        where t.pblm_pasi is not null and s.id is not null and s.sub_id is not null
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and t.RGSTR_ID like '%_${rgstrId}'
        </if>
        ) t group by t.sub_id,t.pblm_pasi
    </select>

    <select id="countPblmInfoBySubNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select t.SYS_TYPE as code,t.pblm_pasi as name, count(*) as count from (
        select distinct t.id,t.pblm_pasi,os.SYS_TYPE from TAC_PBLM_INFO t left join
        TAC_PAWP_RGSTR r on t.obj_id = r.obj_id left join TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        left join TAC_PBLM_SUBJECT s on t.id = s.pblm_id left join tac_obj_subject os on s.sub_id = os.ID
        where t.pblm_pasi is not null and s.id is not null and s.sub_id is not null
        <include refid="choseSql"/>
        <if test="year != null">
            and o.year =#{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        ) t group by t.SYS_TYPE,t.pblm_pasi
    </select>

    <select id="countPblmInfoByAdCode" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select AD_Code as code,concat('(',AD_Name,')',name) as name,count(*) as count,PBLM_PASI AS pblmPasi from (
        select t.*,r.ad_code,b.ad_name,r.name from TAC_PBLM_INFO t left join TAC_PAWP_RGSTR r on t.obj_id = r.obj_id
        left join TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        left join att_ad_x_base b on r.ad_code = b.ad_code
        where r.id is not null and t.pblm_pasi is not null
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and t.RGSTR_ID like '%_${rgstrId}'
        </if>
        ) w group by AD_Code,AD_Name,name,PBLM_PASI order by COUNT desc
    </select>


    <select id="countPblmInfoByAdCodeNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select AD_Code as code, PBLM_PASI AS name, count(*) as count
        from (
        select t.*, a.ad_code, a.ad_name
        from TAC_PBLM_INFO t
        left join TAC_PAWP_RGSTR r on t.obj_id = r.obj_id
        left join TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        left join TAC_INSP_YEAR_BATCH_AREA a on a.GROUP_ID=o.GROUP_ID
        where r.id is not null
        and t.pblm_pasi is not null
        <include refid="choseSql"/>
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        ) w group by AD_Code, AD_Name,PBLM_PASI order by COUNT desc
    </select>

    <select id="countPblmInfoByObjTypeNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        WITH O AS (
        SELECT B.TYPE AS NAME,
        COUNT(T.ID) PBLM_SIZE
        FROM TAC_INSP_YEAR_BATCH_OBJ O
        JOIN TAC_PAWP_RGSTR R ON O.ID = R.OBJ_ID
        LEFT JOIN TAC_ATT_PAWP_BASE B ON B.ID = O.OJB_ID
        LEFT JOIN (SELECT * FROM TAC_PBLM_INFO) T ON T.RGSTR_ID = R.ID
        WHERE T.PBLM_PASI IS NOT NULL
        <include refid="choseSql"/>
        <if test="year != null">
            and O.year = #{year}
        </if>
        <if test="batch != null">
            and O.batch = #{batch}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        GROUP BY B.TYPE
        )

        SELECT
        name, SUM(PBLM_SIZE) AS "count"
        FROM O
        WHERE name is not null
        GROUP BY name
        ORDER BY name
    </select>


    <select id="countPblmInfoByStb" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        ( CASE WHEN count( * ) IS NULL THEN 0 ELSE count( * ) END ) AS count,
        (case when spfsnlType='质量实体' then '工程质量'
        when spfsnlType='质量行为' then '工程质量'
        when spfsnlType='安全管理' then '工程安全'
        else spfsnlType  end ) AS name,
        pblmType,
        pblmPasi AS code
        FROM
        (
        SELECT
        tb.SPFSNL_TYPE AS spfsnlType,
        t.PBLM_PASI AS pblmPasi,
        tb.pblm_type AS pblmType,
        t.PBLM_NM
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        LEFT JOIN TAC_OBJ_PBLMSTB tb ON tb.id = t.PBLMSTD_ID
        WHERE
        o.id IS NOT NULL
        AND t.pblm_pasi IS NOT NULL
        AND tb.id IS NOT NULL
        <if test="year != null">and o.year = #{year}
        </if>
        <if test="batch != null">and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="objId != null and objId != ''">
            and t.obj_id = #{objId}
        </if>
        <choose>
            <when test="rgstrId != null and rgstrId !=''">
                and t.RGSTR_ID like '%_${rgstrId}'
                and length(t.RGSTR_ID) > 32
            </when>
            <otherwise>
                and length(t.RGSTR_ID)=32
            </otherwise>
        </choose>
        ) w
        WHERE
        spfsnlType NOT IN ( '质量管理—实体质量', '计划管理' )
        GROUP BY
        spfsnlType,
        pblmType,
        pblmPasi
        ORDER BY
        pblmType
    </select>

    <select id="countPblmInfoByStbZjNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        ( CASE WHEN count( * ) IS NULL THEN 0 ELSE count( * ) END ) AS count,
        (case when spfsnlType='质量实体' then '工程质量'
        when spfsnlType='质量行为' then '工程质量'
        when spfsnlType='安全管理' then '工程安全'
        else spfsnlType  end ) AS name ,
        pblmType,
        pblmPasi AS code
        FROM
        (
        SELECT
        tb.SPFSNL_TYPE AS spfsnlType,
        t.PBLM_PASI AS pblmPasi,
        tb.pblm_type AS pblmType,
        t.PBLM_NM
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        LEFT JOIN TAC_OBJ_PBLMSTB tb ON tb.id = t.PBLMSTD_ID
        WHERE
        o.id IS NOT NULL
        AND t.pblm_pasi IS NOT NULL
        AND tb.id IS NOT NULL
        <include refid="choseSql"/>
        <if test="year != null">and o.year = #{year}
        </if>
        <if test="batch != null">and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="objId != null and objId != ''">
            and t.obj_id = #{objId}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        ) w
        WHERE
        spfsnlType NOT IN ( '质量管理—实体质量', '计划管理' )
        GROUP BY
        spfsnlType,
        pblmType,
        pblmPasi
        ORDER BY
        pblmType
    </select>

    <select id="countPblmInfoByCase" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select count(*) as count,t.if_case_pblm as code from (
        select distinct t.id,(case when t.if_case_pblm is null then '0' else t.if_case_pblm end ) as if_case_pblm from
        TAC_PBLM_INFO t left join TAC_PAWP_RGSTR r on t.obj_id = r.obj_id left join TAC_INSP_YEAR_BATCH_OBJ o on o.id =
        r.obj_id
        left join TAC_PBLM_SUBJECT s on t.id = s.pblm_id
        where 1=1
        <include refid="choseSql"/>
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        ) t group by t.if_case_pblm
    </select>

    <select id="countPblmInfoByQlttv" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select count(*),t.PBLM_QLTTV_CD as code,t.PBLM_QLTTV_NM as name from (
        select distinct t.id,t.PBLM_QLTTV_CD,t.PBLM_QLTTV_NM from TAC_PBLM_INFO t left join TAC_PAWP_RGSTR r on t.obj_id
        = r.obj_id left join TAC_INSP_YEAR_BATCH_OBJ o on o.id = r.obj_id
        left join TAC_PBLM_SUBJECT s on t.id = s.pblm_id
        where t.PBLM_QLTTV_NM is not null
        <include refid="choseSql"/>
        <if test="year != null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">
            and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">
            and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        ) t group by t.PBLM_QLTTV_CD,t.PBLM_QLTTV_NM
    </select>

    <select id="findListByYearBatch" resultMap="tacPblmInfoResultMap">
        select t.*,r.name,r.location,r.ad_code from tac_pblm_info t ,tac_pawp_rgstr r where substr(t.rgstr_id,1,32)=r.id and length(t.rgstr_id) &gt;32
                                                                                        and r.id in (
                select B.Id from TAC_INSP_YEAR_BATCH_OBJ A
                                     JOIN TAC_PAWP_RGSTR B
                                          ON A.ID=B.OBJ_ID
                WHERE B.STATE=#{state}
                  and a.year=#{year}
                  and A.BATCH=#{batch}
            )
                                                                                        and t.rgstr_id like '%_${state}' and r.ad_code like '${orgId}%'
    </select>

    <select id="countIfCasePblm" resultType="java.lang.Integer">
        SELECT
        count(*)
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        LEFT JOIN TAC_OBJ_PBLMSTB tb ON tb.id = t.PBLMSTD_ID
        WHERE
        t.pblm_pasi IS NOT NULL
        AND tb.id IS NOT NULL
        <if test="year != null">and o.year = #{year}
        </if>
        <if test="batch != null">and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="objId != null and objId != ''">
            and t.obj_id = #{objId}
        </if>
        <choose>
            <when test="rgstrId != null and rgstrId !=''">
                and t.RGSTR_ID like '%_${rgstrId}'
            </when>
            <otherwise>
                and length(t.RGSTR_ID)=32
            </otherwise>
        </choose>
        <choose>
            <when test="ifCasePblm == 1">
                and t.IF_CASE_PBLM = 1
            </when>
            <otherwise>
                and (t.IF_CASE_PBLM = 0 or t.IF_CASE_PBLM is null)
            </otherwise>
        </choose>

    </select>

    <select id="countIfCasePblmNew" resultType="java.lang.Integer">
        SELECT
        count(*)
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        LEFT JOIN TAC_OBJ_PBLMSTB tb ON tb.id = t.PBLMSTD_ID
        WHERE
        t.pblm_pasi IS NOT NULL
        AND tb.id IS NOT NULL
        <include refid="choseSql"/>

        <if test="year != null">and o.year = #{year}
        </if>
        <if test="batch != null">and o.batch = #{batch}
        </if>
        <if test="type != null and type != ''">and r.type = #{type}
        </if>
        <if test="isPoverty != null and isPoverty != ''">and r.IS_POVERTY = #{isPoverty}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
        <if test="searchStat != null and searchStat != ''">
            and t.RGSTR_ID like '%_${searchStat}'
        </if>
        <if test="objId != null and objId != ''">
            and t.obj_id = #{objId}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        <choose>
            <when test="ifCasePblm == 1">
                and t.IF_CASE_PBLM = 1
            </when>
            <otherwise>
                and (t.IF_CASE_PBLM = 0 or t.IF_CASE_PBLM is null)
            </otherwise>
        </choose>

    </select>

    <select id="countPblmInfoByQt" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        count( * ) AS count,
        a.showName,
        a.name
        FROM
        (
        SELECT
        t.LIST_TYPE as name,
        tp.PBLM_TYPE_DESC AS showName
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_OBJ_PBLMSTB tp ON t.PBLMSTD_ID = tp.id
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        WHERE
        t.PBLMSTD_ID IS NOT NULL
        AND tp.id IS NOT NULL
        and tp.PBLM_TYPE_DESC is not null
        <if test="year !=null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="stbClaName != null and stbClaName != ''">
            and t.LIST_TYPE = #{stbClaName}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and t.RGSTR_ID like '_${rgstrId}%'
        </if>
        ) A
        GROUP BY
        a.showName,
        a.name
        ORDER BY
        a.showName
    </select>

    <select id="countPblmInfoByQtNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        count( * ) AS count,
        a.showName,
        a.name
        FROM
        (
        SELECT
        t.LIST_TYPE as name,
        tp.PBLM_TYPE_DESC AS showName
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_OBJ_PBLMSTB tp ON t.PBLMSTD_ID = tp.id
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        WHERE
        t.PBLMSTD_ID IS NOT NULL
        AND tp.id IS NOT NULL
        and tp.PBLM_TYPE_DESC is not null
        and tp.PBLM_TYPE_DESC !='其他'
        <include refid="choseSql"/>
        <if test="year !=null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="stbClaName != null and stbClaName != ''">
            and t.LIST_TYPE = #{stbClaName}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        ) A
        GROUP BY
        a.showName,
        a.name
        ORDER BY
        a.showName
    </select>

    <select id="countPblmInfoObjByQt" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        count( * ) AS count,
        a.showName,
        a.name
        FROM
        (
        SELECT
        DISTINCT
        t.PBLMSTD_ID,
        tp.PBLM_TYPE_DESC AS showName,
        t.LIST_TYPE as name
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_OBJ_PBLMSTB tp ON t.PBLMSTD_ID = tp.id
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        WHERE
        t.PBLMSTD_ID IS NOT NULL
        AND tp.id IS NOT NULL
        and tp.PBLM_TYPE_DESC is not null
        <if test="year !=null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="stbClaName != null and stbClaName != ''">
            and t.LIST_TYPE = #{stbClaName}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and t.RGSTR_ID like '_${rgstrId}%'
        </if>
        ) A
        GROUP BY
        a.name,a.showName
        ORDER BY
        a.showName
    </select>

    <select id="countPblmInfoObjByQtNew" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        count( * ) AS count,
        a.showName,
        a.name
        FROM
        (
        SELECT
        DISTINCT
        t.PBLMSTD_ID,
        tp.PBLM_TYPE_DESC AS showName,
        t.LIST_TYPE as name
        FROM
        TAC_PBLM_INFO t
        LEFT JOIN TAC_OBJ_PBLMSTB tp ON t.PBLMSTD_ID = tp.id
        LEFT JOIN TAC_INSP_YEAR_BATCH_OBJ o ON o.id = t.obj_id
        WHERE
        t.PBLMSTD_ID IS NOT NULL
        AND tp.id IS NOT NULL
        and tp.PBLM_TYPE_DESC is not null
        and tp.PBLM_TYPE_DESC !='其他'
        <include refid="choseSql"/>
        <if test="year !=null">
            and o.year = #{year}
        </if>
        <if test="batch != null">
            and o.batch = #{batch}
        </if>
        <if test="stbClaName != null and stbClaName != ''">
            and t.LIST_TYPE = #{stbClaName}
        </if>
        <if test="rgstrId != null and rgstrId !=''">
            and length(t.RGSTR_ID)=32
        </if>
        ) A
        GROUP BY
        a.name,a.showName
        ORDER BY
        a.showName
    </select>

    <select id="countAdNameByAdAdCade" parameterType="cn.com.goldenwater.dcproj.param.TacInspYearBatchAreaParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        select a.AD_CODE as code, a.AD_NAME as name, count(*) as count
        from att_ad_x_base a
        where a.GUID is not null
          and a.AD_CODE in (${adCode})
        group by a.AD_CODE, a.AD_NAME
    </select>

    <select id="countPblmInfoByObj" parameterType="cn.com.goldenwater.dcproj.param.TacPblmInfoParam"
            resultType="cn.com.goldenwater.dcproj.dto.TacCountDto">
        SELECT
        a.ID as rgstrId,
        a.name,
        count(*) AS count
        FROM
        (
        SELECT r.ID, r.NAME
        FROM TAC_INSP_YEAR_BATCH_OBJ o
        join TAC_PAWP_RGSTR r on o.id = r.obj_id
        join TAC_PBLM_INFO t on t.obj_id = r.obj_id
        <where>
            <include refid="choseSql"/>
            <if test="year !=null">and o.year = #{year}</if>
            <if test="batch != null">and o.batch = #{batch}</if>
            <if test="rgstrId != null and rgstrId !=''">and length(t.RGSTR_ID)=32</if>
            <if test="adCode != null and adCode != ''">and r.AD_CODE like concat('%' , substr(#{adCode},1,4) , '%')</if>
        </where>
        ) A
        GROUP BY a.name,a.ID
    </select>
</mapper>