<?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.taais.demo.mapper.DemoCustomerMapper">

    <resultMap type="DemoCustomer" id="DemoCustomerResult">
        <result property="customerId" column="customer_id"/>
        <result property="customerName" column="customer_name"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="sex" column="sex"/>
        <result property="birthday" column="birthday"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <resultMap id="DemoCustomerDemoGoodsResult" type="DemoCustomer" extends="DemoCustomerResult">
        <collection property="demoGoodsList" notNullColumn="sub_goods_id" javaType="java.util.List"
                    resultMap="DemoGoodsResult"/>
    </resultMap>

    <resultMap type="DemoGoods" id="DemoGoodsResult">
        <result property="goodsId" column="sub_goods_id"/>
        <result property="customerId" column="sub_customer_id"/>
        <result property="name" column="sub_name"/>
        <result property="weight" column="sub_weight"/>
        <result property="price" column="sub_price"/>
        <result property="date" column="sub_date"/>
        <result property="type" column="sub_type"/>
    </resultMap>

    <sql id="selectDemoCustomerVo">
        select customer_id, customer_name, phonenumber, sex, birthday, remark
        from demo_customer
    </sql>

    <select id="selectDemoCustomerList" parameterType="DemoCustomer" resultMap="DemoCustomerResult">
        <include refid="selectDemoCustomerVo"/>
        <where>
            <if test="customerName != null  and customerName != ''">and customer_name like concat('%', #{customerName},
                '%')
            </if>
            <if test="phonenumber != null  and phonenumber != ''">and phonenumber = #{phonenumber}</if>
            <if test="sex != null  and sex != ''">and sex = #{sex}</if>
            <if test="birthday != null ">and birthday = #{birthday}</if>
        </where>
    </select>

    <select id="selectDemoCustomerByCustomerId" parameterType="Long" resultMap="DemoCustomerDemoGoodsResult">
        select a.customer_id,
               a.customer_name,
               a.phonenumber,
               a.sex,
               a.birthday,
               a.remark,
               b.goods_id    as sub_goods_id,
               b.customer_id as sub_customer_id,
               b.name        as sub_name,
               b.weight      as sub_weight,
               b.price       as sub_price,
               b.date        as sub_date,
               b.type        as sub_type
        from demo_customer a
                 left join demo_goods b on b.customer_id = a.customer_id
        where a.customer_id = #{customerId}
    </select>

    <insert id="insertDemoCustomer" parameterType="DemoCustomer" useGeneratedKeys="true" keyProperty="customerId">
        insert into demo_customer
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="customerName != null">customer_name,</if>
            <if test="phonenumber != null">phonenumber,</if>
            <if test="sex != null">sex,</if>
            <if test="birthday != null">birthday,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="customerName != null">#{customerName},</if>
            <if test="phonenumber != null">#{phonenumber},</if>
            <if test="sex != null">#{sex},</if>
            <if test="birthday != null">#{birthday},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>

    <update id="updateDemoCustomer" parameterType="DemoCustomer">
        update demo_customer
        <trim prefix="SET" suffixOverrides=",">
            <if test="customerName != null">customer_name = #{customerName},</if>
            <if test="phonenumber != null">phonenumber = #{phonenumber},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="birthday != null">birthday = #{birthday},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where customer_id = #{customerId}
    </update>

    <delete id="deleteDemoCustomerByCustomerId" parameterType="Long">
        delete
        from demo_customer
        where customer_id = #{customerId}
    </delete>

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

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

    <delete id="deleteDemoGoodsByCustomerId" parameterType="Long">
        delete
        from demo_goods
        where customer_id = #{customerId}
    </delete>

    <insert id="batchDemoGoods">
        insert into demo_goods( customer_id, name, weight, price, date, type) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.customerId}, #{item.name}, #{item.weight}, #{item.price}, #{item.date}, #{item.type})
        </foreach>
    </insert>
</mapper>
