<?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.nasc.custom.mapper.NascDealRecordMapper">

    <insert id="insertDealRecord" parameterType="NascDealRecord" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO nasc_deal_record (serial_number, barcode, with_type, contacts, phone, id_card, with_status,
                                      enterprise_name, materials, matter_id, deal_user, create_time)
        VALUES (#{serialNumber}, #{barcode}, #{withType}, #{contacts}, #{phone}, #{idCard}, #{withStatus},
            #{enterpriseName}, #{materials}, #{matterId}, #{dealUser}, sysdate());
    </insert>

    <resultMap id="dealRecordResult" type="java.util.HashMap">
        <result property="id" column="id"/>
        <result property="serialNumber" column="serial_number"/>
        <result property="barcode" column="barcode"/>
        <result property="matterId" column="matter_id"/>
        <result property="withType" column="with_type"/>
        <result property="contacts" column="contacts"/>
        <result property="phone" column="phone"/>
        <result property="idCard" column="id_card"/>
        <result property="withStatus" column="with_status"/>
        <result property="materials" column="materials"/>
        <result property="createTime" column="create_time"/>
        <result property="itemName" column="item_name"/>
        <result property="userName" column="user_name"/>
        <result property="deptName" column="dept_name"/>
        <result property="enterpriseName" column="enterprise_name"/>
    </resultMap>

    <select id="selectDealRecordList" parameterType="NascDealRecord" resultMap="dealRecordResult">
        SELECT dr.id, dr.serial_number, dr.with_type, dr.contacts, dr.barcode, dr.matter_id,
               dr.phone, dr.id_card, dr.with_status, dr.materials, dr.enterprise_name,
               DATE_FORMAT(dr.create_time, '%Y-%m-%d %H:%i:%s') AS create_time,
               m.item_name, u.user_name, d.dept_name
        FROM nasc_deal_record dr
        LEFT JOIN nasc_matter m ON m.id = dr.matter_id
        LEFT JOIN sys_user u ON u.user_id = dr.deal_user
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
        <where>
            <if test="serialNumber != null and serialNumber != ''">
                AND dr.serial_number = #{serialNumber}
            </if>
            <if test="withStatus != null">
                AND dr.with_status = #{withStatus}
            </if>
        </where>
        ORDER BY dr.with_status ASC, dr.create_time DESC
    </select>

    <select id="getDealRecordById" resultMap="dealRecordResult">
        SELECT dr.id, dr.serial_number, dr.with_type, dr.contacts, dr.barcode,
               dr.phone, dr.id_card, dr.with_status, dr.materials, dr.enterprise_name,
               DATE_FORMAT(dr.create_time, '%Y-%m-%d %H:%i:%s') AS create_time,
               m.item_name, u.user_name
        FROM nasc_deal_record dr
        LEFT JOIN nasc_matter m ON m.id = dr.matter_id
        LEFT JOIN sys_user u ON u.user_id = dr.deal_user
        WHERE dr.id = #{id}
    </select>

    <update id="updateDealRecordStatus">
        UPDATE nasc_deal_record
        SET with_status = #{withStatus}, update_time = sysdate()
        WHERE id = #{id}
    </update>

    <update id="updateDealRecord">
        UPDATE nasc_deal_record
        SET with_type = #{withType}, contacts = #{contacts}, phone = #{phone}, id_card = #{idCard},
            with_status = #{withStatus}, enterprise_name = #{enterpriseName}, materials = #{materials},
            matter_id = #{matterId}, deal_user = #{dealUser}, update_time = sysdate()
        WHERE id = #{id}
    </update>

</mapper> 