<?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.ruoyi.project.zjsgfa.mapper.SjFdsgcsDcyxMapper">
    
    <resultMap type="SjFdsgcsDcyx" id="SjFdsgcsDcyxResult">
        <result property="id"    column="id"    />
        <result property="jh"    column="jh"    />
        <result property="kc"    column="kc"    />
        <result property="dcmc"    column="dcmc"    />
        <result property="jdcs"    column="jdcs"    />
        <result property="yx"    column="yx"    />
        <result property="bz"    column="bz"    />
    </resultMap>

    <sql id="selectSjFdsgcsDcyxVo">
        select id, dcmc, jdcs, yx, bz from sj_fdsgcs_dcyx
    </sql>

    <select id="selectSjFdsgcsDcyxList" parameterType="SjFdsgcsDcyx" resultMap="SjFdsgcsDcyxResult">
        <include refid="selectSjFdsgcsDcyxVo"/>
        <where>
            <if test="jh != null  and jh != ''"> and jh = #{jh}</if>
            <if test="kc != null  and kc != ''"> and kc = #{kc}</if>
            <if test="dcmc != null  and dcmc != ''"> and dcmc = #{dcmc}</if>
            <if test="jdcs != null  and jdcs != ''"> and jdcs = #{jdcs}</if>
            <if test="yx != null  and yx != ''"> and yx = #{yx}</if>
            <if test="bz != null  and bz != ''"> and bz = #{bz}</if>
        </where>
    </select>
    
    <select id="selectSjFdsgcsDcyxById" parameterType="Long" resultMap="SjFdsgcsDcyxResult">
        <include refid="selectSjFdsgcsDcyxVo"/>
        where id = #{id}
    </select>
    <select id="getListByJh" resultType="com.ruoyi.project.zjsgfa.domain.SjFdsgcsDcyx">
        select * from (
                          SELECT
                              id,
                              jh,
                              kc,
                              dcmc,
                              -- 截取 - 左边的部分作为起始深度
                              SUBSTRING_INDEX(jdcs, '-', 1) AS start_depth,
                              -- 截取 - 右边的部分作为结束深度，先从右往左取 1 个分隔符，再取结果中 - 右边（实际就是原字符串 - 右边部分 ）
                              SUBSTRING_INDEX(SUBSTRING_INDEX(jdcs, '-', -1), '-', 1) AS end_depth,
                              yx,
                              bz
                          FROM
                              sj_fdsgcs_dcyx) a where jh=#{jh} and start_depth>=#{kscs} and end_depth &lt;= #{jscs}
    </select>

    <insert id="insertSjFdsgcsDcyx" parameterType="SjFdsgcsDcyx" useGeneratedKeys="true" keyProperty="id">
        insert into sj_fdsgcs_dcyx
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="jh != null">jh,</if>
            <if test="kc != null">kc,</if>
            <if test="dcmc != null">dcmc,</if>
            <if test="jdcs != null">jdcs,</if>
            <if test="yx != null">yx,</if>
            <if test="bz != null">bz,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="jh != null">#{jh},</if>
            <if test="kc != null">#{kc},</if>
            <if test="dcmc != null">#{dcmc},</if>
            <if test="jdcs != null">#{jdcs},</if>
            <if test="yx != null">#{yx},</if>
            <if test="bz != null">#{bz},</if>
         </trim>
    </insert>
    <insert id="insertSjFdsgcsDcyxBatch">
        insert into sj_fdsgcs_dcyx (jh, kc, dcmc, jdcs, yx, bz) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.jh}, #{item.kc}, #{item.dcmc}, #{item.jdcs}, #{item.yx}, #{item.bz})
        </foreach>

    </insert>

    <update id="updateSjFdsgcsDcyx" parameterType="SjFdsgcsDcyx">
        update sj_fdsgcs_dcyx
        <trim prefix="SET" suffixOverrides=",">
            <if test="jh != null">jh = #{jh},</if>
            <if test="kc != null">kc = #{kc},</if>
            <if test="dcmc != null">dcmc = #{dcmc},</if>
            <if test="jdcs != null">jdcs = #{jdcs},</if>
            <if test="yx != null">yx = #{yx},</if>
            <if test="bz != null">bz = #{bz},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSjFdsgcsDcyxById" parameterType="Long">
        delete from sj_fdsgcs_dcyx where id = #{id}
    </delete>

    <delete id="deleteSjFdsgcsDcyxByIds" parameterType="String">
        delete from sj_fdsgcs_dcyx where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteSjFdsgcsDcyxByJh">
        delete from sj_fdsgcs_dcyx where jh = #{jh}
    </delete>
</mapper>