<?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.NascCertificateSendMapper">

    <insert id="insertCertificateSend" parameterType="NascCertificateSend" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO nasc_certificate_send (cert_id, print_user, cert_status, serial_number, print_time)
        VALUES
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.certId}, #{item.printUser}, 0, #{item.serialNumber}, sysdate())
        </foreach>
    </insert>

    <update id="updateCertificateSend">
        UPDATE nasc_certificate_send
        SET send_user = #{sendUser}, cert_status = 1, send_time = sysdate()
        WHERE id IN
        <foreach collection="certificateSendList" item="item" index="index"
                 separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>

    <resultMap id="certificateSendResult" type="java.util.HashMap">
        <result property="id" column="id"/>
        <result property="certStatus" column="cert_status"/>
        <result property="serialNumber" column="serial_number"/>
        <result property="printTime" column="print_time"/>
        <result property="sendTime" column="send_time"/>
        <result property="certificateNo" column="certificate_no"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateType" column="certificate_type"/>
        <result property="phone" column="phone"/>
        <result property="sendUser" column="send_user"/>
        <result property="printUser" column="print_user"/>
        <result property="sendDept" column="send_dept"/>
        <result property="printDept" column="print_dept"/>
    </resultMap>

    <select id="selectCertificateSendList" resultMap="certificateSendResult">
        SELECT cs.id, cs.cert_status, cs.serial_number,
               DATE_FORMAT(cs.print_time, '%Y-%m-%d %H:%i:%s') AS print_time,
               DATE_FORMAT(cs.send_time, '%Y-%m-%d %H:%i:%s') AS send_time,
               c.certificate_no, c.certificate_name, c.certificate_type, c.phone,
               u.user_name AS send_user, u1.user_name AS print_user,
               d.dept_name AS send_dept, d1.dept_name AS print_dept
        FROM nasc_certificate_send cs
        LEFT JOIN sys_user u ON u.user_id = cs.send_user
        LEFT JOIN sys_user u1 ON u1.user_id = cs.print_user
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
        LEFT JOIN sys_dept d1 ON d1.dept_id = u1.dept_id
        LEFT JOIN nasc_certificate c ON c.id = cs.cert_id
        <where>
            <if test="serialNumber != null and serialNumber != ''">
                AND cs.serial_number = #{serialNumber}
            </if>
        </where>
        ORDER BY cs.cert_status ASC, cs.print_time DESC
    </select>

</mapper> 