<?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.dgtly.goods.mapper.GoodsInfoMapper">
    
    <resultMap type="GoodsInfo" id="GoodsInfoResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="companyId"    column="company_id"    />
        <result property="typeId"    column="type_id"    />
        <result property="price"    column="price"    />
        <result property="cover"    column="cover"    />
        <result property="slideshow"    column="slideshow"    />
        <result property="des"    column="des"    />
        <result property="delivery"    column="delivery"    />
        <result property="producedTime"    column="produced_time"    />
        <result property="producedAddress"    column="produced_address"    />
        <result property="warehouseAddress"    column="warehouse_address"    />
        <result property="weight"    column="weight"    />
        <result property="putawayFlag"    column="putaway_flag"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="remark"    column="remark"    />
        <result property="companyName"    column="companyName"    />
        <result property="typeName"    column="typeName"    />
        <collection  property="goodsAttributes"   javaType="java.util.List"       resultMap="GoodsAttributeResult" />
        <collection  property="goodsSizes"   javaType="java.util.List"       resultMap="GoodsSizeResult" />
        <collection  property="goodsColors"   javaType="java.util.List"       resultMap="GoodsColorResult" />
    </resultMap>

    <resultMap type="GoodsAttribute" id="GoodsAttributeResult">
        <result property="id"    column="goods_attribute_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="attributeId"    column="attribute_id"    />
        <result property="attributeName"    column="attributeName"    />
        <result property="sort"    column="sort"    />
        <result property="value"    column="value"    />
    </resultMap>
    <resultMap type="GoodsColor" id="GoodsColorResult">
        <result property="id"    column="goods_color_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="color"    column="color"    />
    </resultMap>
    <resultMap type="GoodsSize" id="GoodsSizeResult">
        <result property="id"    column="goods_size_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="size"    column="size"    />
    </resultMap>

    <sql id="selectGoodsInfoVo">
        select gi.id, gi.name, gi.company_id, gi.type_id, gi.price, gi.cover, gi.slideshow, gi.des, gi.delivery, gi.produced_time, gi.produced_address,
         gi.warehouse_address, gi.weight,gi.putaway_flag, gi.del_flag, gi.create_time, gi.create_by, gi.update_time, gi.update_by, gi.remark,
         gt.name typeName,sc.company_name companyName,
         ga.attribute_id,ga.value,gs.size,gc.color,
         gta.attribute_name attributeName,gta.sort
        from goods_info gi
        left join goods_type gt on gt.id =gi.type_id
        left join sys_company sc on sc.id =gi.company_id
        left join goods_attribute ga on ga.goods_id = gi.id
        left join goods_type_attribute gta on gta.id = ga.attribute_id
        left join goods_size gs on gs.goods_id = gi.id
        left join goods_color gc on gc.goods_id = gi.id
    </sql>

    <select id="selectGoodsInfoList" parameterType="GoodsInfo" resultMap="GoodsInfoResult">
        select gi.id, gi.name, gi.company_id, gi.type_id, gi.price, gi.cover, gi.slideshow, gi.create_time, gi.create_by, gi.update_time, gi.update_by, gi.remark,
        gi.putaway_flag,gi.weight,gi.produced_time,
        gt.name typeName,sc.company_name companyName
        from goods_info gi
        left join goods_type gt on gt.id =gi.type_id
        left join sys_company sc on sc.id =gi.company_id
        <where>
            gi.del_flag = "0"
            <if test="name != null  and name != ''"> and gi.name like concat('%', #{name}, '%')</if>
            <if test="companyId != null "> and gi.company_id = #{companyId}</if>
            <if test="typeId != null "> and find_in_set(#{typeId},gt.ancestors) </if>
            <if test="price != null "> and gi.price = #{price}</if>
            <if test="cover != null  and cover != ''"> and gi.cover = #{cover}</if>
            <if test="slideshow != null  and slideshow != ''"> and gi.slideshow = #{slideshow}</if>
            <if test="des != null  and des != ''"> and gi.des = #{des}</if>
            <if test="delivery != null  and delivery != ''"> and gi.delivery = #{delivery}</if>
            <if test="producedTime != null "> and gi.produced_time = #{producedTime}</if>
            <if test="producedAddress != null  and producedAddress != ''"> and gi.produced_address = #{producedAddress}</if>
            <if test="warehouseAddress != null  and warehouseAddress != ''"> and gi.warehouse_address = #{warehouseAddress}</if>
            <if test="weight != null  and weight != ''"> and gi.weight = #{weight}</if>
            <if test="putawayFlag != null  and putawayFlag != ''"> and gi.putaway_flag = #{putawayFlag}</if>
        </where>
        order by gi.putaway_flag ,gi.create_time
    </select>
    
    <select id="selectGoodsInfoById" parameterType="Long" resultMap="GoodsInfoResult">
        <include refid="selectGoodsInfoVo"/>
        where gi.id = #{id}
    </select>
        
    <insert id="insertGoodsInfo" parameterType="GoodsInfo" useGeneratedKeys="true" keyProperty="id">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
            select LAST_INSERT_ID();
        </selectKey>
        insert into goods_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null  and name != ''">name,</if>
            <if test="companyId != null ">company_id,</if>
            <if test="typeId != null ">type_id,</if>
            <if test="price != null ">price,</if>
            <if test="cover != null  and cover != ''">cover,</if>
            <if test="slideshow != null  and slideshow != ''">slideshow,</if>
            <if test="des != null  and des != ''">des,</if>
            <if test="delivery != null  and delivery != ''">delivery,</if>
            <if test="producedTime != null ">produced_time,</if>
            <if test="producedAddress != null  and producedAddress != ''">produced_address,</if>
            <if test="warehouseAddress != null  and warehouseAddress != ''">warehouse_address,</if>
            <if test="weight != null  and weight != ''">weight,</if>
            <if test="putawayFlag != null  and putawayFlag != ''">putaway_flag,</if>
            <if test="delFlag != null  and delFlag != ''">del_flag,</if>
            <if test="createTime != null ">create_time,</if>
            <if test="createBy != null  and createBy != ''">create_by,</if>
            <if test="updateTime != null ">update_time,</if>
            <if test="updateBy != null  and updateBy != ''">update_by,</if>
            <if test="remark != null  and remark != ''">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null  and name != ''">#{name},</if>
            <if test="companyId != null ">#{companyId},</if>
            <if test="typeId != null ">#{typeId},</if>
            <if test="price != null ">#{price},</if>
            <if test="cover != null  and cover != ''">#{cover},</if>
            <if test="slideshow != null  and slideshow != ''">#{slideshow},</if>
            <if test="des != null  and des != ''">#{des},</if>
            <if test="delivery != null  and delivery != ''">#{delivery},</if>
            <if test="producedTime != null ">#{producedTime},</if>
            <if test="producedAddress != null  and producedAddress != ''">#{producedAddress},</if>
            <if test="warehouseAddress != null  and warehouseAddress != ''">#{warehouseAddress},</if>
            <if test="weight != null  and weight != ''">#{weight},</if>
            <if test="putawayFlag != null  and putawayFlag != ''">#{putawayFlag},</if>
            <if test="delFlag != null  and delFlag != ''">#{delFlag},</if>
            <if test="createTime != null ">#{createTime},</if>
            <if test="createBy != null  and createBy != ''">#{createBy},</if>
            <if test="updateTime != null ">#{updateTime},</if>
            <if test="updateBy != null  and updateBy != ''">#{updateBy},</if>
            <if test="remark != null  and remark != ''">#{remark},</if>
         </trim>
    </insert>

    <update id="updateGoodsInfo" parameterType="GoodsInfo">
        update goods_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null  and name != ''">name = #{name},</if>
            <if test="companyId != null ">company_id = #{companyId},</if>
            <if test="typeId != null ">type_id = #{typeId},</if>
            <if test="price != null ">price = #{price},</if>
            <if test="cover != null  and cover != ''">cover = #{cover},</if>
            <if test="slideshow != null  and slideshow != ''">slideshow = #{slideshow},</if>
            <if test="des != null  and des != ''">des = #{des},</if>
            <if test="delivery != null  and delivery != ''">delivery = #{delivery},</if>
            <if test="producedTime != null ">produced_time = #{producedTime},</if>
            <if test="producedAddress != null  and producedAddress != ''">produced_address = #{producedAddress},</if>
            <if test="warehouseAddress != null  and warehouseAddress != ''">warehouse_address = #{warehouseAddress},</if>
            <if test="weight != null  and weight != ''">weight = #{weight},</if>
            <if test="putawayFlag != null  and putawayFlag != ''">putaway_flag = #{putawayFlag},</if>
            <if test="delFlag != null  and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="createTime != null ">create_time = #{createTime},</if>
            <if test="createBy != null  and createBy != ''">create_by = #{createBy},</if>
            <if test="updateTime != null ">update_time = #{updateTime},</if>
            <if test="updateBy != null  and updateBy != ''">update_by = #{updateBy},</if>
            <if test="remark != null  and remark != ''">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteGoodsInfoById" parameterType="Long">
        update goods_info set  del_flag = '2' where  id = #{id}
    </delete>

    <delete id="deleteGoodsInfoByIds" parameterType="String">
        update goods_info set  del_flag = '2' where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectGoodsInfoHotList" parameterType="GoodsInfo" resultMap="GoodsInfoResult">
        select gi.id, gi.name, gi.company_id, gi.type_id, gi.price, gi.cover, gi.slideshow, gi.create_time, gi.create_by, gi.update_time, gi.update_by, gi.remark,
        gi.putaway_flag,gi.weight,gi.produced_time,
        gt.name typeName,sc.company_name companyName
        from goods_info gi
        left join goods_type gt on gt.id =gi.type_id
        left join sys_company sc on sc.id =gi.company_id
        where
            gi.del_flag = "0"
            and gi.putaway_flag = "1"
        order by gi.create_time
        limit 0,10
    </select>
    
</mapper>