<?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.chinaweal.aiccs.aiccs.abnormal.mapper.TSBizSpeListMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.abnormal.entity.TSBizSpeList">
        <id column="SPELISTID" property="spelistid"/>
        <result column="BIZSEQ" property="bizseq"/>
        <result column="PRIPID" property="pripid"/>
        <result column="REGNO" property="regno"/>
        <result column="UNISCID" property="uniscid"/>
        <result column="ENTNAME" property="entname"/>
        <result column="ENTTYPE" property="enttype"/>
        <result column="SPECAUSE" property="specause"/>
        <result column="APPROVALDATE" property="approvaldate"/>
        <result column="SPEREASONS" property="spereasons"/>
        <result column="DECORG" property="decorg"/>
        <result column="AREA_CODE" property="areaCode"/>
        <result column="LAUPTIME" property="lauptime"/>
        <result column="ReconsiderationOrg" property="reconsiderationOrg"/>
        <result column="GovernmentOrg" property="governmentOrg"/>
        <result column="LitiOrg" property="litiOrg"/>
        <result column="status" property="status"/>
        <result column="intoSponsor" property="intoSponsor"/>
        <result column="remark" property="remark"/>
        <result column="decisionContent" property="decisionContent"/>
        <result column="dom" property="dom"/>
        <result column="reportYear" property="reportYear"/>
        <result column="intoSponsorId" property="intoSponsorId"/>
        <result column="isRemove" property="isRemove"/>
        <result column="toIncludeId" property="toIncludeId"/>
        <result column="LEREP" property="leRep"/>
        <result column="CERTYPE" property="cerType"/>
        <result column="CERNO" property="cerNo"/>
        <result column="removeReason" property="removeReason"/>
        <result column="removeTime" property="removeTime"/>
        <result column="removeHandler" property="removeHandler"/>
        <result column="insertTime" property="insertTime"/>
        <result column="correctBizseq" property="correctBizseq"/>
        <result column="type" property="type"/>
        <result column="caseSource" property="caseSource"/>
        <result column="contactEntity" property="contactEntity"/>
        <result column="contactMethodOrg" property="contactMethodOrg"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        SPELISTID, BIZSEQ, PRIPID, REGNO, ENTNAME, ENTTYPE, SPECAUSE, APPROVALDATE, SPEREASONS, DECORG, AREA_CODE, LAUPTIME, ReconsiderationOrg, GovernmentOrg,
        LitiOrg, status, intoSponsor, remark, decisionContent, UNISCID, dom, reportYear,intoSponsorId, isRemove, toIncludeId,
        LEREP,CERTYPE,CERNO,removeHandler,removeTime,removeReason,insertTime,correctBizseq, type,caseSource,contactEntity,contactMethodOrg
    </sql>

    <select id="selectBSPexptlist" parameterType="com.chinaweal.aiccs.aiccs.abnormal.entity.TSBizSpeList"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSpeList
        <where>
            <if test=" tsBizSpeListModel.bizseq != null and tsBizSpeListModel.bizseq != ''">
                and bizseq = #{tsBizSpeListModel.bizseq}
            </if>
            <if test=" tsBizSpeListModel.pripid != null and tsBizSpeListModel.pripid != ''">
                and pripid = #{tsBizSpeListModel.pripid}
            </if>
            <if test=" tsBizSpeListModel.regno != null and tsBizSpeListModel.regno != ''">
                and regno = #{tsBizSpeListModel.regno}
            </if>
            <if test=" tsBizSpeListModel.isRemove != null and tsBizSpeListModel.isRemove != ''">
                and isRemove = #{tsBizSpeListModel.isRemove}
            </if>
        </where>
    </select>

    <select id="selectBSPExptByPageList" parameterType="com.chinaweal.aiccs.aiccs.abnormal.entity.TSBizSpeList"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from TSBizSpeList
        <where>
            <if test=" tsBizSpeListModel.bizseq != null and tsBizSpeListModel.bizseq != ''">
                and bizseq = #{tsBizSpeListModel.bizseq}
            </if>
            <if test=" tsBizSpeListModel.pripid != null and tsBizSpeListModel.pripid != ''">
                and pripid = #{tsBizSpeListModel.pripid}
            </if>
            <if test=" tsBizSpeListModel.regno != null and tsBizSpeListModel.regno != ''">
                and regno = #{tsBizSpeListModel.regno}
            </if>
        </where>
    </select>

    <select id="selectAbnListBussByPage" parameterType="java.util.HashMap" resultMap="BaseResultMap">
        select
        a.SpeListID,a. BizSeq, a.PriPID,
        a.RegNO, a.EntName, a.EntType,
        a.SpeCause, a.ApprovalDate, a.SpeReasons,
        a.DecOrg, a.ReconsiderationOrg, a.GovernmentOrg,
        a.LitiOrg, a.status, a.intoSponsor, a.LaUpTime
        from TSBizSpeList a
        <where>
            <if test=" tsAbnListModel.regno != null and tsAbnListModel.regno != ''">
                and a.regno = #{tsAbnListModel.regno}
            </if>
            <if test="tsAbnListModel.entname != null and tsAbnListModel.entname != ''">
                and a.entname like '%'||#{tsAbnListModel.entname}||'%'
            </if>
            <if test="tsAbnListModel.decorg != null and tsAbnListModel.decorg != ''">
                and a.decorg = #{tsAbnListModel.decorg}
            </if>
            and exists( select 1 from tstasklist where bizseqid = a.bizseq
            <if test=" acceptno != null and acceptno != ''">
                and acceptno = #{acceptno}
            </if>
            )
            and exists( select 1 from TSAbnList where pripid = a.pripid and abnlistid = a.spelistid and operatestatus
            ='1' and remdate is null
            <if test="tsAbnListModel.abntime != null and tsAbnListModel.abntime != ''">
                and abntime &gt;= #{tsAbnListModel.abntime}
            </if>
            <if test="tsAbnListModel.endabntime != null and tsAbnListModel.endabntime != ''">
                and abntime &lt;= #{tsAbnListModel.endabntime}
            </if>
            )
        </where>
        order by a.ApprovalDate desc
    </select>

    <select id="selectBizSpeByPageList" parameterType="com.chinaweal.aiccs.aiccs.abnormal.entity.TSBizSpeList"
            resultMap="BaseResultMap">
       SELECT  <include refid="Base_Column_List"/> from  tsbizspelist where status = '2' GROUP BY BIZSEQ ORDER BY APPROVALDATE desc
    </select>

    <select id="selectBizSpeListPage" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.vo.TSBizSpeListVO">
        select <include refid="Base_Column_List"/>
        from TSBizSpeList
        <where>
            <if test="customParamMap.bizseq != null and customParamMap.bizseq != ''">
               and BIZSEQ = #{customParamMap.bizseq}
            </if>
            <if test="customParamMap.type != null and customParamMap.type != ''">
                <if test="customParamMap.type eq 'biz'">
                    and isRemove = '0'
                    order by insertTime desc,PRIPID asc
                </if>
                <if test="customParamMap.type eq 'rem'">
                    and isRemove = '1'
                    order by removeTime desc,PRIPID asc
                </if>
            </if>

        </where>
    </select>

    <insert id="saveTsbizspelistBatch">
        INSERT INTO tsbizspelist ( SPELISTID, BIZSEQ, PRIPID, UNISCID, REGNO, ENTNAME, ENTTYPE, DECORG, LAUPTIME, status, isRemove, intoSponsor, intoSponsorId,
        type, caseSource,contactEntity,contactMethodOrg ) VALUES
        <foreach collection="list" index="index" item="item" separator=",">
            ( #{item.spelistid,jdbcType=VARCHAR},
            #{item.bizseq,jdbcType=VARCHAR},
            #{item.pripid,jdbcType=VARCHAR},
            #{item.uniscid,jdbcType=VARCHAR},
            #{item.regno,jdbcType=VARCHAR},
            #{item.entname,jdbcType=VARCHAR},
            #{item.enttype,jdbcType=VARCHAR},
            #{item.decorg,jdbcType=VARCHAR},
            #{item.lauptime,jdbcType=TIMESTAMP},
            #{item.status,jdbcType=VARCHAR},
            #{item.isRemove,jdbcType=VARCHAR},
            #{item.intoSponsor,jdbcType=VARCHAR},
            #{item.intoSponsorId,jdbcType=VARCHAR},
            #{item.type,jdbcType=VARCHAR},
            #{item.caseSource,jdbcType=VARCHAR},
            #{item.contactEntity,jdbcType=VARCHAR},
            #{item.contactMethodOrg,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>

    <update id="updateByBizseq">
        UPDATE tsbizspelist SET
        SPECAUSE = #{tsBizSpeList.specause},
        caseSource = #{tsBizSpeList.caseSource},
        contactEntity = #{tsBizSpeList.contactEntity},
        contactMethodOrg = #{tsBizSpeList.contactMethodOrg},
        remark = #{tsBizSpeList.remark},
        decisionContent = #{tsBizSpeList.decisionContent},
        LAUPTIME = #{tsBizSpeList.lauptime,jdbcType=TIMESTAMP},
        <if test="tsBizSpeList.intoSponsor != null and tsBizSpeList.intoSponsor != ''">
            intoSponsor = #{tsBizSpeList.intoSponsor},
        </if>
        <if test="tsBizSpeList.intoSponsorId != null and tsBizSpeList.intoSponsorId != ''">
            intoSponsorId = #{tsBizSpeList.intoSponsorId},
        </if>
        <if test="tsBizSpeList.endApply != null and tsBizSpeList.endApply != ''">
            endApply = #{tsBizSpeList.endApply},
        </if>
        <if test="tsBizSpeList.endReason != null and tsBizSpeList.endReason != ''">
            endReason = #{tsBizSpeList.endReason},
        </if>
        <if test="tsBizSpeList.dom != null and tsBizSpeList.dom != ''">
            dom = #{tsBizSpeList.dom},
        </if>
        <if test="tsBizSpeList.reconsiderationOrg != null and tsBizSpeList.reconsiderationOrg != ''">
            ReconsiderationOrg = #{tsBizSpeList.reconsiderationOrg},
        </if>
        <if test="tsBizSpeList.governmentOrg != null and tsBizSpeList.governmentOrg != ''">
            GovernmentOrg = #{tsBizSpeList.governmentOrg},
        </if>
        <if test="tsBizSpeList.litiOrg != null and tsBizSpeList.litiOrg != ''">
            LitiOrg = #{tsBizSpeList.litiOrg},
        </if>
        status = #{tsBizSpeList.status}
        WHERE BIZSEQ = #{tsBizSpeList.bizseq}
    </update>

    <update id="updateStatus">
        UPDATE tsbizspelist SET
        <if test="tsBizSpeList.status != null and tsBizSpeList.status != ''">
            status = #{tsBizSpeList.status},
        </if>
        <if test="tsBizSpeList.approvaldate != null">
            APPROVALDATE = #{tsBizSpeList.approvaldate,jdbcType=TIMESTAMP},
        </if>
        LAUPTIME = #{tsBizSpeList.lauptime,jdbcType=TIMESTAMP}
        WHERE BIZSEQ = #{tsBizSpeList.bizseq}
    </update>

    <select id="getOldBizSpeListPage" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.vo.TSBizSpeListVO">
        select
        sentid as pripid,
        SREGNO as regno,
        sentname as entname,
        sdom as address,
        SPRINCIPAL as lerepname
        from tfdb.TB_SHS_APPROVE_ENTITY
        where SAPPROVECODE = #{SAPPROVECODE}
    </select>

    <select id="getOldBizSpeListInfo" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.vo.TSBizSpeListVO">
        select
        SREASONTYPENAME as specause,
        SREASONCONTENT as decisionContent
        from tfdb.TB_SHS_APPROVE where SAPPROVECODE = #{SAPPROVECODE}
    </select>
</mapper>
