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

    <resultMap id="certificateResult" type="java.util.HashMap">
        <result property="id" column="id"/>
        <result property="certificateType" column="certificate_type"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateNo" column="certificate_no"/>
        <result property="phone" column="phone"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <insert id="insertCertificate" parameterType="NascCertificate" useGeneratedKeys="true" keyProperty="id">
        INSERT IGNORE INTO nasc_certificate (certificate_type, certificate_name, certificate_no, phone, create_time)
        VALUES
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.certificateType}, #{item.certificateName}, #{item.certificateNo}, #{item.phone}, sysdate())
        </foreach>
    </insert>

    <select id="selectCertificateList" resultMap="certificateResult">
        SELECT c.id, c.certificate_type, c.certificate_name, c.certificate_no, c.phone,
               DATE_FORMAT(c.create_time, '%Y-%m-%d %H:%i:%s') AS create_time
        FROM nasc_certificate c
        <where>
            <if test="certificateType != null and certificateType != ''">
                AND c.certificate_type = #{certificateType}
            </if>
            <if test="certificateName != null and certificateName != ''">
                AND c.certificate_name = #{certificateName}
            </if>
            <if test="certificateNo != null and certificateNo != ''">
                AND c.certificate_no = #{certificateNo}
            </if>
            <if test="phone != null and phone != ''">
                AND c.phone = #{phone}
            </if>
        </where>
        ORDER BY c.create_time DESC
    </select>

</mapper> 