<?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.zt.mapper.JsaaMapper">
    
    <resultMap type="Jsaa" id="JsaaResult">
        <result property="jh"    column="jh"    />
        <result property="bqrq"    column="bqrq"    />
        <result property="bqsj"    column="bqsj"    />
        <result property="azrq"    column="azrq"    />
        <result property="azsj"    column="azsj"    />
        <result property="kzrq1"    column="kzrq1"    />
        <result property="kzsj1"    column="kzsj1"    />
        <result property="kzrq2"    column="kzrq2"    />
        <result property="kzsj2"    column="kzsj2"    />
        <result property="kzrq3"    column="kzrq3"    />
        <result property="kzsj3"    column="kzsj3"    />
        <result property="kzrq4"    column="kzrq4"    />
        <result property="kzsj4"    column="kzsj4"    />
        <result property="kzrq5"    column="kzrq5"    />
        <result property="kzsj5"    column="kzsj5"    />
        <result property="wzrq"    column="wzrq"    />
        <result property="wzsj"    column="wzsj"    />
        <result property="wjrq"    column="wjrq"    />
        <result property="wjsj"    column="wjsj"    />
        <result property="zjzq1"    column="zjzq1"    />
        <result property="zjzq2"    column="zjzq2"    />
        <result property="jjzq1"    column="jjzq1"    />
        <result property="jjzq2"    column="jjzq2"    />
        <result property="zgcssj1"    column="zgcssj1"    />
        <result property="zgcssj2"    column="zgcssj2"    />
        <result property="fgjc"    column="fgjc"    />
        <result property="qxjc"    column="qxjc"    />
        <result property="yxcd"    column="yxcd"    />
        <result property="yxshl"    column="yxshl"    />
        <result property="pjdtjc"    column="pjdtjc"    />
        <result property="sjjs"    column="sjjs"    />
        <result property="wjjs"    column="wjjs"    />
        <result property="wjczjs"    column="wjczjs"    />
        <result property="wzcw"    column="wzcw"    />
        <result property="wjff"    column="wjff"    />
        <result property="zjy"    column="zjy"    />
        <result property="zjys"    column="zjys"    />
        <result property="pjjxzs"    column="pjjxzs"    />
        <result property="jszl"    column="jszl"    />
        <result property="gjzl"    column="gjzl"    />
        <result property="sgcs"    column="sgcs"    />
        <result property="zjcb"    column="zjcb"    />
        <result property="zhcb"    column="zhcb"    />
        <result property="mmzjcb"    column="mmzjcb"    />
        <result property="mmzhcb"    column="mmzhcb"    />
        <result property="ygsmc"    column="ygsmc"    />
        <result property="qk"    column="qk"    />
        <result property="tsgyj"    column="tsgyj"    />
        <result property="zjh"    column="zjh"    />
        <result property="fzs"    column="fzs"    />
        <result property="djfz"    column="djfz"    />
        <result property="cyc"    column="cyc"    />
        <result property="czds"    column="czds"    />
        <result property="sjcw"    column="sjcw"    />
        <result property="jx"    column="jx"    />
        <result property="jb"    column="jb"    />
        <result property="gs"    column="gs"    />
        <result property="zjbh"    column="zjbh"    />
    </resultMap>

    <sql id="selectJsaaVo">
        select a.jh,
               bqrq,
               bqsj,
               azrq,
               azsj,
               kzrq1,
               kzsj1,
               kzrq2,
               kzsj2,
               kzrq3,
               kzsj3,
               kzrq4,
               kzsj4,
               kzrq5,
               kzsj5,
               wzrq,
               wzsj,
               wjrq,
               wjsj,
               zjzq1,
               zjzq2,
               jjzq1,
               jjzq2,
               zgcssj1,
               zgcssj2,
               fgjc,
               qxjc,
               yxcd,
               yxshl,
               pjdtjc,
               sjjs,
               wjjs,
               wjczjs,
               wzcw,
               wjff,
               zjy,
               zjys,
               pjjxzs,
               jszl,
               gjzl,
               sgcs,
               zjcb,
               zhcb,
               mmzjcb,
               mmzhcb,
               ygsmc,
               qk,
               tsgyj,
               zjh,
               fzs,
               djfz,
               cyc,
               czds,
               sjcw,b.jx,b.jb,f.GS,f.ZJBH
        from jsaa a left join JSBA b on a.JH = b.JH
                    left join  JSFM f on a.jh=f.jh
    </sql>

    <select id="selectJsaaList" parameterType="Jsaa" resultMap="JsaaResult">
        <include refid="selectJsaaVo"/>
        <where>  
            <if test="jh != null "> and a.jh like  CONCAT(CONCAT('%', #{jh}), '%') </if>
            <if test="bqrq != null "> and bqrq = #{bqrq}</if>
            <if test="bqsj != null  and bqsj != ''"> and bqsj = #{bqsj}</if>
            <if test="azrq != null "> and azrq = #{azrq}</if>
            <if test="azsj != null  and azsj != ''"> and azsj = #{azsj}</if>
            <if test="kzrq1 != null "> and kzrq1 = #{kzrq1}</if>
            <if test="kzsj1 != null  and kzsj1 != ''"> and kzsj1 = #{kzsj1}</if>
            <if test="kzrq2 != null "> and kzrq2 = #{kzrq2}</if>
            <if test="kzsj2 != null  and kzsj2 != ''"> and kzsj2 = #{kzsj2}</if>
            <if test="kzrq3 != null "> and kzrq3 = #{kzrq3}</if>
            <if test="kzsj3 != null  and kzsj3 != ''"> and kzsj3 = #{kzsj3}</if>
            <if test="kzrq4 != null "> and kzrq4 = #{kzrq4}</if>
            <if test="kzsj4 != null  and kzsj4 != ''"> and kzsj4 = #{kzsj4}</if>
            <if test="kzrq5 != null "> and kzrq5 = #{kzrq5}</if>
            <if test="kzsj5 != null  and kzsj5 != ''"> and kzsj5 = #{kzsj5}</if>
            <if test="wzrq != null "> and wzrq = #{wzrq}</if>
            <if test="wzsj != null  and wzsj != ''"> and wzsj = #{wzsj}</if>
            <if test="wjrq != null "> and wjrq = #{wjrq}</if>
            <if test="wjsj != null  and wjsj != ''"> and wjsj = #{wjsj}</if>
            <if test="zjzq1 != null "> and zjzq1 = #{zjzq1}</if>
            <if test="zjzq2 != null "> and zjzq2 = #{zjzq2}</if>
            <if test="jjzq1 != null "> and jjzq1 = #{jjzq1}</if>
            <if test="jjzq2 != null "> and jjzq2 = #{jjzq2}</if>
            <if test="zgcssj1 != null "> and zgcssj1 = #{zgcssj1}</if>
            <if test="zgcssj2 != null "> and zgcssj2 = #{zgcssj2}</if>
            <if test="fgjc != null "> and fgjc = #{fgjc}</if>
            <if test="qxjc != null "> and qxjc = #{qxjc}</if>
            <if test="yxcd != null "> and yxcd = #{yxcd}</if>
            <if test="yxshl != null "> and yxshl = #{yxshl}</if>
            <if test="pjdtjc != null "> and pjdtjc = #{pjdtjc}</if>
            <if test="sjjs != null "> and sjjs = #{sjjs}</if>
            <if test="wjjs != null "> and wjjs = #{wjjs}</if>
            <if test="wjczjs != null "> and wjczjs = #{wjczjs}</if>
            <if test="wzcw != null  and wzcw != ''"> and wzcw = #{wzcw}</if>
            <if test="wjff != null  and wjff != ''"> and wjff = #{wjff}</if>
            <if test="zjy != null "> and zjy = #{zjy}</if>
            <if test="zjys != null "> and zjys = #{zjys}</if>
            <if test="pjjxzs != null "> and pjjxzs = #{pjjxzs}</if>
            <if test="jszl != null  and jszl != ''"> and jszl = #{jszl}</if>
            <if test="gjzl != null  and gjzl != ''"> and gjzl = #{gjzl}</if>
            <if test="sgcs != null "> and sgcs = #{sgcs}</if>
            <if test="zjcb != null "> and zjcb = #{zjcb}</if>
            <if test="zhcb != null "> and zhcb = #{zhcb}</if>
            <if test="mmzjcb != null "> and mmzjcb = #{mmzjcb}</if>
            <if test="mmzhcb != null "> and mmzhcb = #{mmzhcb}</if>
            <if test="ygsmc != null  and ygsmc != ''"> and ygsmc = #{ygsmc}</if>
            <if test="qk != null  and qk != ''"> and qk = #{qk} </if>
            <if test="tsgyj != null  and tsgyj != ''"> and tsgyj = #{tsgyj}</if>
            <if test="zjh != null  and zjh != ''"> and zjh = #{zjh}</if>
            <if test="fzs != null "> and fzs = #{fzs}</if>
            <if test="djfz != null "> and djfz = #{djfz}</if>
            <if test="cyc != null  and cyc != ''"> and cyc = #{cyc}</if>
            <if test="czds != null "> and czds = #{czds}</if>
            <if test="sjcw != null  and sjcw != ''"> and sjcw = #{sjcw}</if>
            <if test="wjsjks!=null and wjsjks!=''">
                and to_char(WJRQ,'YYYY')>=#{wjsjks}
            </if>
            <if test="wjsjjs!=null and wjsjjs!=''">
                and to_char(WJRQ,'YYYY')&lt;=#{wjsjjs}
            </if>

            <if test="gs!=null and gs!=''">
                and f.gs like CONCAT(CONCAT('%', #{gs}), '%')
            </if>
        </where>
    </select>
    
    <select id="selectJsaaByJh" parameterType="String" resultMap="JsaaResult">
        <include refid="selectJsaaVo"/>
        where a.jh = #{jh}
    </select>
    <select id="selectQklist" resultType="com.ruoyi.project.zt.domain.Jsaa">
        select qk,count(jh) jsl from JSAA
        where 1=1
        <if test="qk != null  and qk != ''"> and qk like   CONCAT(CONCAT('%', #{qk}), '%')</if>
        group by qk order by  jsl desc
    </select>

    <insert id="insertJsaa" parameterType="Jsaa">
        <selectKey keyProperty="jh" resultType="long" order="BEFORE">
            SELECT seq_jsaa.NEXTVAL as jh FROM DUAL
        </selectKey>
        insert into jsaa
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="jh != null">jh,</if>
            <if test="bqrq != null">bqrq,</if>
            <if test="bqsj != null">bqsj,</if>
            <if test="azrq != null">azrq,</if>
            <if test="azsj != null">azsj,</if>
            <if test="kzrq1 != null">kzrq1,</if>
            <if test="kzsj1 != null">kzsj1,</if>
            <if test="kzrq2 != null">kzrq2,</if>
            <if test="kzsj2 != null">kzsj2,</if>
            <if test="kzrq3 != null">kzrq3,</if>
            <if test="kzsj3 != null">kzsj3,</if>
            <if test="kzrq4 != null">kzrq4,</if>
            <if test="kzsj4 != null">kzsj4,</if>
            <if test="kzrq5 != null">kzrq5,</if>
            <if test="kzsj5 != null">kzsj5,</if>
            <if test="wzrq != null">wzrq,</if>
            <if test="wzsj != null">wzsj,</if>
            <if test="wjrq != null">wjrq,</if>
            <if test="wjsj != null">wjsj,</if>
            <if test="zjzq1 != null">zjzq1,</if>
            <if test="zjzq2 != null">zjzq2,</if>
            <if test="jjzq1 != null">jjzq1,</if>
            <if test="jjzq2 != null">jjzq2,</if>
            <if test="zgcssj1 != null">zgcssj1,</if>
            <if test="zgcssj2 != null">zgcssj2,</if>
            <if test="fgjc != null">fgjc,</if>
            <if test="qxjc != null">qxjc,</if>
            <if test="yxcd != null">yxcd,</if>
            <if test="yxshl != null">yxshl,</if>
            <if test="pjdtjc != null">pjdtjc,</if>
            <if test="sjjs != null">sjjs,</if>
            <if test="wjjs != null">wjjs,</if>
            <if test="wjczjs != null">wjczjs,</if>
            <if test="wzcw != null">wzcw,</if>
            <if test="wjff != null">wjff,</if>
            <if test="zjy != null">zjy,</if>
            <if test="zjys != null">zjys,</if>
            <if test="pjjxzs != null">pjjxzs,</if>
            <if test="jszl != null">jszl,</if>
            <if test="gjzl != null">gjzl,</if>
            <if test="sgcs != null">sgcs,</if>
            <if test="zjcb != null">zjcb,</if>
            <if test="zhcb != null">zhcb,</if>
            <if test="mmzjcb != null">mmzjcb,</if>
            <if test="mmzhcb != null">mmzhcb,</if>
            <if test="ygsmc != null">ygsmc,</if>
            <if test="qk != null">qk,</if>
            <if test="tsgyj != null">tsgyj,</if>
            <if test="zjh != null">zjh,</if>
            <if test="fzs != null">fzs,</if>
            <if test="djfz != null">djfz,</if>
            <if test="cyc != null">cyc,</if>
            <if test="czds != null">czds,</if>
            <if test="sjcw != null">sjcw,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="jh != null">#{jh},</if>
            <if test="bqrq != null">#{bqrq},</if>
            <if test="bqsj != null">#{bqsj},</if>
            <if test="azrq != null">#{azrq},</if>
            <if test="azsj != null">#{azsj},</if>
            <if test="kzrq1 != null">#{kzrq1},</if>
            <if test="kzsj1 != null">#{kzsj1},</if>
            <if test="kzrq2 != null">#{kzrq2},</if>
            <if test="kzsj2 != null">#{kzsj2},</if>
            <if test="kzrq3 != null">#{kzrq3},</if>
            <if test="kzsj3 != null">#{kzsj3},</if>
            <if test="kzrq4 != null">#{kzrq4},</if>
            <if test="kzsj4 != null">#{kzsj4},</if>
            <if test="kzrq5 != null">#{kzrq5},</if>
            <if test="kzsj5 != null">#{kzsj5},</if>
            <if test="wzrq != null">#{wzrq},</if>
            <if test="wzsj != null">#{wzsj},</if>
            <if test="wjrq != null">#{wjrq},</if>
            <if test="wjsj != null">#{wjsj},</if>
            <if test="zjzq1 != null">#{zjzq1},</if>
            <if test="zjzq2 != null">#{zjzq2},</if>
            <if test="jjzq1 != null">#{jjzq1},</if>
            <if test="jjzq2 != null">#{jjzq2},</if>
            <if test="zgcssj1 != null">#{zgcssj1},</if>
            <if test="zgcssj2 != null">#{zgcssj2},</if>
            <if test="fgjc != null">#{fgjc},</if>
            <if test="qxjc != null">#{qxjc},</if>
            <if test="yxcd != null">#{yxcd},</if>
            <if test="yxshl != null">#{yxshl},</if>
            <if test="pjdtjc != null">#{pjdtjc},</if>
            <if test="sjjs != null">#{sjjs},</if>
            <if test="wjjs != null">#{wjjs},</if>
            <if test="wjczjs != null">#{wjczjs},</if>
            <if test="wzcw != null">#{wzcw},</if>
            <if test="wjff != null">#{wjff},</if>
            <if test="zjy != null">#{zjy},</if>
            <if test="zjys != null">#{zjys},</if>
            <if test="pjjxzs != null">#{pjjxzs},</if>
            <if test="jszl != null">#{jszl},</if>
            <if test="gjzl != null">#{gjzl},</if>
            <if test="sgcs != null">#{sgcs},</if>
            <if test="zjcb != null">#{zjcb},</if>
            <if test="zhcb != null">#{zhcb},</if>
            <if test="mmzjcb != null">#{mmzjcb},</if>
            <if test="mmzhcb != null">#{mmzhcb},</if>
            <if test="ygsmc != null">#{ygsmc},</if>
            <if test="qk != null">#{qk},</if>
            <if test="tsgyj != null">#{tsgyj},</if>
            <if test="zjh != null">#{zjh},</if>
            <if test="fzs != null">#{fzs},</if>
            <if test="djfz != null">#{djfz},</if>
            <if test="cyc != null">#{cyc},</if>
            <if test="czds != null">#{czds},</if>
            <if test="sjcw != null">#{sjcw},</if>
         </trim>
    </insert>

    <update id="updateJsaa" parameterType="Jsaa">
        update jsaa
        <trim prefix="SET" suffixOverrides=",">
            <if test="bqrq != null">bqrq = #{bqrq},</if>
            <if test="bqsj != null">bqsj = #{bqsj},</if>
            <if test="azrq != null">azrq = #{azrq},</if>
            <if test="azsj != null">azsj = #{azsj},</if>
            <if test="kzrq1 != null">kzrq1 = #{kzrq1},</if>
            <if test="kzsj1 != null">kzsj1 = #{kzsj1},</if>
            <if test="kzrq2 != null">kzrq2 = #{kzrq2},</if>
            <if test="kzsj2 != null">kzsj2 = #{kzsj2},</if>
            <if test="kzrq3 != null">kzrq3 = #{kzrq3},</if>
            <if test="kzsj3 != null">kzsj3 = #{kzsj3},</if>
            <if test="kzrq4 != null">kzrq4 = #{kzrq4},</if>
            <if test="kzsj4 != null">kzsj4 = #{kzsj4},</if>
            <if test="kzrq5 != null">kzrq5 = #{kzrq5},</if>
            <if test="kzsj5 != null">kzsj5 = #{kzsj5},</if>
            <if test="wzrq != null">wzrq = #{wzrq},</if>
            <if test="wzsj != null">wzsj = #{wzsj},</if>
            <if test="wjrq != null">wjrq = #{wjrq},</if>
            <if test="wjsj != null">wjsj = #{wjsj},</if>
            <if test="zjzq1 != null">zjzq1 = #{zjzq1},</if>
            <if test="zjzq2 != null">zjzq2 = #{zjzq2},</if>
            <if test="jjzq1 != null">jjzq1 = #{jjzq1},</if>
            <if test="jjzq2 != null">jjzq2 = #{jjzq2},</if>
            <if test="zgcssj1 != null">zgcssj1 = #{zgcssj1},</if>
            <if test="zgcssj2 != null">zgcssj2 = #{zgcssj2},</if>
            <if test="fgjc != null">fgjc = #{fgjc},</if>
            <if test="qxjc != null">qxjc = #{qxjc},</if>
            <if test="yxcd != null">yxcd = #{yxcd},</if>
            <if test="yxshl != null">yxshl = #{yxshl},</if>
            <if test="pjdtjc != null">pjdtjc = #{pjdtjc},</if>
            <if test="sjjs != null">sjjs = #{sjjs},</if>
            <if test="wjjs != null">wjjs = #{wjjs},</if>
            <if test="wjczjs != null">wjczjs = #{wjczjs},</if>
            <if test="wzcw != null">wzcw = #{wzcw},</if>
            <if test="wjff != null">wjff = #{wjff},</if>
            <if test="zjy != null">zjy = #{zjy},</if>
            <if test="zjys != null">zjys = #{zjys},</if>
            <if test="pjjxzs != null">pjjxzs = #{pjjxzs},</if>
            <if test="jszl != null">jszl = #{jszl},</if>
            <if test="gjzl != null">gjzl = #{gjzl},</if>
            <if test="sgcs != null">sgcs = #{sgcs},</if>
            <if test="zjcb != null">zjcb = #{zjcb},</if>
            <if test="zhcb != null">zhcb = #{zhcb},</if>
            <if test="mmzjcb != null">mmzjcb = #{mmzjcb},</if>
            <if test="mmzhcb != null">mmzhcb = #{mmzhcb},</if>
            <if test="ygsmc != null">ygsmc = #{ygsmc},</if>
            <if test="qk != null">qk = #{qk},</if>
            <if test="tsgyj != null">tsgyj = #{tsgyj},</if>
            <if test="zjh != null">zjh = #{zjh},</if>
            <if test="fzs != null">fzs = #{fzs},</if>
            <if test="djfz != null">djfz = #{djfz},</if>
            <if test="cyc != null">cyc = #{cyc},</if>
            <if test="czds != null">czds = #{czds},</if>
            <if test="sjcw != null">sjcw = #{sjcw},</if>
        </trim>
        where jh = #{jh}
    </update>

    <delete id="deleteJsaaByJh" parameterType="String">
        delete from jsaa where jh = #{jh}
    </delete>

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