<?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.project.cases.mapper.CaseMapper">

    <resultMap type="Case" id="CaseResult">
        <result property="id"       column="id"       />
        <result property="caseNo"       column="case_no"       />
        <result property="name"    column="name"    />
        <result property="userNo"     column="user_no"     />
        <result property="sex"  column="sex"  />
        <result property="age"         column="age"          />
        <result property="position"       column="position"       />
        <result property="department"     column="department"     />
        <result property="rank"       column="rank"       />
        <result property="phone"     column="phone"     />
        <result property="filingTime"         column="filing_time"          />
        <result property="creatTime"         column="creat_time"          />
    </resultMap>

    <sql id="selectCaseVo">
        select id,case_no, name, user_no, sex, age, position, department, rank, phone, filing_time, creat_time
        from cases
    </sql>

    <select id="selectCaseById" parameterType="integer" resultMap="CaseResult">
        <include refid="selectCaseVo"/>
        where id = #{id}
    </select>

    <select id="selectCaseList" parameterType="Case" resultMap="CaseResult">
        <include refid="selectCaseVo"/>
        <where>
            <if test="caseNo != null and caseNo != ''">
                AND case_no like '%'||#{caseNo}||'%' <!-- concat('%', #{noticeTitle}, '%') -->
            </if>
            <if test="name != null and name != ''">
                AND name like '%'||#{name}||'%' <!-- concat('%', #{createBy}, '%') -->
            </if>
        </where>
        order by id desc
    </select>

    <insert id="insertCase" parameterType="Case">
        insert into cases (
        <if test="caseNo != null and caseNo != '' ">case_no, </if>
        <if test="name != null and name != '' ">name, </if>
        <if test="userNo != null and userNo != '' ">user_no, </if>
        <if test="sex != null and sex != '' ">sex, </if>
        <if test="age != null and age != ''">age,</if>
        <if test="position != null and position != ''">position,</if>
        <if test="department != null and department != ''">department,</if>
        <if test="rank != null and rank != ''">rank,</if>
        <if test="phone != null and phone != ''">phone,</if>
        <if test="filingTime != null and filingTime != ''">filing_time,</if>
        creat_time
        )values(
        <if test="caseNo != null and caseNo != ''">#{caseNo}, </if>
        <if test="name != null and name != ''">#{name}, </if>
        <if test="userNo != null and userNo != ''">#{userNo}, </if>
        <if test="sex != null and sex != ''">#{sex}, </if>
        <if test="age != null and age != ''">#{age},</if>
        <if test="position != null and position != ''">#{position},</if>
        <if test="department != null and department != ''">#{department},</if>
        <if test="rank != null and rank != ''">#{rank},</if>
        <if test="phone != null and phone != ''">#{phone},</if>
        <if test="filingTime != null and filingTime != ''">#{filingTime},</if>
        (datetime(CURRENT_TIMESTAMP,'localtime'))
        )
    </insert>

    <update id="updateCase" parameterType="Case">
        update cases
        <set>
            <if test="caseNo != null and name != ''">case_No = #{caseNo}, </if>
            <if test="name != null and name != ''">name = #{name}, </if>
            <if test="userNo != null and userNo != ''">user_no = #{userNo}, </if>
            <if test="sex != null">sex = #{sex}, </if>
            <if test="age != null and age != ''">age = #{age}, </if>
            <if test="position != null and position != ''">position = #{position},</if>
            <if test="department != null and department != ''">department = #{department},</if>
            <if test="rank != null and rank != ''">rank = #{rank},</if>
            <if test="phone != null and phone != ''">phone = #{phone},</if>
            <if test="filingTime != null and filingTime != ''">filing_time = #{filingTime}</if>
        </set>
        where id = #{id}
    </update>

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

    <select id="selectCaseByCaseNo" parameterType="string" resultType="integer">
        select count(*) from cases where case_no = #{caseNo}
    </select>
</mapper>