mybatis

<?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="ConBoxGoodsDao">
    <resultMap id="BaseResultMap" type="com.jd.jdx.x.sr.cms.domain.container.model.ConBoxGoods">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="seller_no" property="sellerNo" jdbcType="VARCHAR"/>
        <result column="seller_name" property="sellerName" jdbcType="VARCHAR"/>
        <result column="box_no" property="boxNo" jdbcType="VARCHAR"/>
        <result column="box_name" property="boxName" jdbcType="VARCHAR"/>
        <result column="store_no" property="storeNo" jdbcType="VARCHAR"/>
        <result column="store_name" property="storeName" jdbcType="VARCHAR"/>
        <result column="goods_no" property="goodsNo" jdbcType="VARCHAR"/>
        <result column="goods_name" property="goodsName" jdbcType="VARCHAR"/>
        <result column="sales_price" property="salesPrice" jdbcType="DECIMAL"/>
        <result column="discount" property="discount" jdbcType="DECIMAL"/>
        <result column="realtime_price" property="realtimePrice" jdbcType="DECIMAL"/>
        <result column="inside_price" property="insidePrice" jdbcType="DECIMAL"/>
        <result column="stock_qty_norm" property="stockQtyNorm" jdbcType="INTEGER"/>
        <result column="project_no" property="projectNo" jdbcType="VARCHAR"/>
        <result column="project_name" property="projectName" jdbcType="VARCHAR"/>
        <result column="template_no" property="templateNo" jdbcType="VARCHAR"/>
        <result column="template_name" property="templateName" jdbcType="VARCHAR"/>
        <result column="stock_threshold" property="stockThreshold" jdbcType="INTEGER"/>
        <result column="goods_status" property="goodsStatus" jdbcType="INTEGER"/>
        <result column="shelves" property="shelves" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="create_user" property="createUser" jdbcType="VARCHAR"/>
        <result column="update_user" property="updateUser" jdbcType="VARCHAR"/>
        <result column="ts" property="ts" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="INTEGER"/>
        <result column="sys_version" property="sysVersion" jdbcType="TINYINT"/>
        <result column="reserve1" property="reserve1" jdbcType="VARCHAR"/>
        <result column="reserve2" property="reserve2" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        <trim prefix="" suffix="" suffixOverrides=",">
            id,
            seller_no,
            seller_name,
            box_no,
            box_name,
            store_no,
            store_name,
            goods_no,
            goods_name,
            sales_price,
            discount,
            realtime_price,
            inside_price,
            stock_qty_norm,
            project_no,
            project_name,
            template_no,
            template_name,
            stock_threshold,
            goods_status,
            shelves,
            create_time,
            update_time,
            create_user,
            update_user,
            ts,
            is_delete,
            sys_version,
            reserve1,
            reserve2,
        </trim>
    </sql>

    <!-- 根据Id查询 -->
    <select id="getConBoxGoods" resultMap="BaseResultMap" parameterType="Long">
        select
        <include refid="Base_Column_List"/>
        from con_box_goods
        where id = #{id,jdbcType=BIGINT}
    </select>

    <!-- 根据条件查询列表 -->
    <select id="findConBoxGoodsList" resultMap="BaseResultMap"
            parameterType="com.jd.jdx.x.sr.cms.domain.container.query.ConBoxGoodsQuery">
        select
        <include refid="Base_Column_List"/>
        from con_box_goods
        <where>
            is_delete=0
        </where>
    </select>


    <!-- 根据skuList查询列表 -->
    <select id="getBoxGoodsListBySkuList" resultMap="BaseResultMap"
            parameterType="com.jd.jdx.x.sr.cms.domain.container.dto.GoodsBoxDto">
        select
        <include refid="Base_Column_List"/>
        from con_box_goods
        <where>
            is_delete=0
            <if test="sellerNo != null">
                and seller_no=#{sellerNo}
            </if>
            <if test="deviceNo != null">
                and box_no=#{deviceNo}
            </if>
            <if test="skuList != null">
                and goods_no in
                <foreach item="item" collection="skuList" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>


        </where>


    </select>

    <!--根据商家设备-获取设备商品信息-->
    <select id="getBoxGoodsListByListDto" resultMap="BaseResultMap"
            parameterType="com.jd.jdx.x.sr.cms.domain.container.dto.UIGoodsBoxDto">
        select
        <include refid="Base_Column_List"/>
        from con_box_goods
        <where>
            is_delete=0
            <if test="sellerNo != null">
                and seller_no=#{sellerNo}
            </if>
            <if test="listBoxNos != null">
                and box_no in
                <foreach item="item" collection="listBoxNos" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <!-- 插入实体 -->
    <insert id="insertConBoxGoods" parameterType="com.jd.jdx.x.sr.cms.domain.container.model.ConBoxGoods"
            useGeneratedKeys="true" keyProperty="id">
        insert into con_box_goods
        <trim prefix="(" suffix=")" suffixOverrides=",">
            id,
            seller_no,
            seller_name,
            box_no,
            box_name,
            store_no,
            store_name,
            goods_no,
            sales_price,
            discount,
            realtime_price,
            inside_price,
            stock_qty_norm,
            project_no,
            project_name,
            template_no,
            template_name,
            stock_threshold,
            goods_status,
            shelves,
            create_time,
            update_time,
            create_user,
            update_user,
            is_delete,
            sys_version,
            reserve1,
            reserve2,
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{id,jdbcType=BIGINT},
            #{sellerNo,jdbcType=VARCHAR},
            #{sellerName,jdbcType=VARCHAR},
            #{boxNo,jdbcType=VARCHAR},
            #{boxName,jdbcType=VARCHAR},
            #{storeNo,jdbcType=VARCHAR},
            #{storeName,jdbcType=VARCHAR},
            #{goodsNo,jdbcType=VARCHAR},
            #{salesPrice,jdbcType=DECIMAL},
            #{discount,jdbcType=DECIMAL},
            #{realtimePrice,jdbcType=DECIMAL},
            #{insidePrice,jdbcType=DECIMAL},
            #{stockQtyNorm,jdbcType=INTEGER},
            #{projectNo,jdbcType=VARCHAR},
            #{projectName,jdbcType=VARCHAR},
            #{templateNo,jdbcType=VARCHAR},
            #{templateName,jdbcType=VARCHAR},
            #{stockThreshold,jdbcType=INTEGER},
            #{goodsStatus,jdbcType=INTEGER},
            #{shelves,jdbcType=VARCHAR},
            #{createTime,jdbcType=TIMESTAMP},
            #{updateTime,jdbcType=TIMESTAMP},
            #{createUser,jdbcType=VARCHAR},
            #{updateUser,jdbcType=VARCHAR},
            #{isDelete,jdbcType=INTEGER},
            #{sysVersion,jdbcType=TINYINT},
            #{reserve1,jdbcType=VARCHAR},
            #{reserve2,jdbcType=VARCHAR},
        </trim>
    </insert>
    <!-- 修改实体 -->
    <update id="updateConBoxGoods" parameterType="com.jd.jdx.x.sr.cms.domain.container.model.ConBoxGoods">
        update con_box_goods
        <set>
            <trim prefix="" suffix="" suffixOverrides=",">
                <if test="id != null">
                    id = #{id,jdbcType=BIGINT},
                </if>
                <if test="sellerNo != null">
                    seller_no = #{sellerNo,jdbcType=VARCHAR},
                </if>
                <if test="sellerName != null">
                    seller_name = #{sellerName,jdbcType=VARCHAR},
                </if>
                <if test="boxNo != null">
                    box_no = #{boxNo,jdbcType=VARCHAR},
                </if>
                <if test="boxName != null">
                    box_no = #{boxName,jdbcType=VARCHAR},
                </if>
                <if test="storeNo != null">
                    store_no = #{storeNo,jdbcType=VARCHAR},
                </if>
                <if test="storeName != null">
                    store_name = #{storeName,jdbcType=VARCHAR},
                </if>
                <if test="goodsNo != null">
                    goods_no = #{goodsNo,jdbcType=VARCHAR},
                </if>
                <if test="salesPrice != null">
                    sales_price = #{salesPrice,jdbcType=DECIMAL},
                </if>
                <if test="discount != null">
                    discount = #{discount,jdbcType=DECIMAL},
                </if>
                <if test="realtimePrice != null">
                    realtime_price = #{realtimePrice,jdbcType=DECIMAL},
                </if>
                <if test="insidePrice != null">
                    inside_price = #{insidePrice,jdbcType=DECIMAL},
                </if>
                <if test="stockQtyNorm != 0">
                    stock_qty_norm = #{stockQtyNorm,jdbcType=INTEGER},
                </if>
                <if test="projectNo != null">
                    project_no = #{projectNo,jdbcType=VARCHAR},
                </if>
                <if test="projectName != null">
                    project_name = #{projectName,jdbcType=VARCHAR},
                </if>
                <if test="templateNo != null">
                    template_no = #{templateNo,jdbcType=VARCHAR},
                </if>
                <if test="templateName != null">
                    template_name = #{templateName,jdbcType=VARCHAR},
                </if>
                <if test="stockThreshold != 0">
                    stock_threshold = #{stockThreshold,jdbcType=INTEGER},
                </if>
                <if test="goodsStatus != 0">
                    goods_status = #{goodsStatus,jdbcType=INTEGER},
                </if>
                <if test="shelves != null">
                    shelves = #{shelves,jdbcType=VARCHAR},
                </if>
                <if test="createTime != null">
                    create_time = #{createTime,jdbcType=TIMESTAMP},
                </if>
                <if test="updateTime != null">
                    update_time = #{updateTime,jdbcType=TIMESTAMP},
                </if>
                <if test="createUser != null">
                    create_user = #{createUser,jdbcType=VARCHAR},
                </if>
                <if test="updateUser != null">
                    update_user = #{updateUser,jdbcType=VARCHAR},
                </if>
                <if test="ts != null">
                    ts = #{ts,jdbcType=TIMESTAMP},
                </if>
                <if test="isDelete != 0">
                    is_delete = #{isDelete,jdbcType=INTEGER},
                </if>
                <if test="sysVersion != 0">
                    sys_version = #{sysVersion,jdbcType=TINYINT},
                </if>
                <if test="reserve1 != null">
                    reserve1 = #{reserve1,jdbcType=VARCHAR},
                </if>
                <if test="reserve2 != null">
                    reserve2 = #{reserve2,jdbcType=VARCHAR},
                </if>
            </trim>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>
    <!-- 删除实体 -->
    <update id="deleteConBoxGoods" parameterType="Long">
        update con_box_goods
        <set>
            is_delete=1
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <!--相关设备商品表 sller_no  sku price  更新人，更新时间-->
    <update id="updateConBoxGoodsPrice" parameterType="com.jd.jdx.x.sr.cms.domain.container.model.ConBoxGoods">
        update con_box_goods
        <set>
            <trim prefix="" suffix="" suffixOverrides=",">
                realtime_price = #{realtimePrice,jdbcType=DECIMAL},
                update_time = #{updateTime,jdbcType=TIMESTAMP},
                update_user = #{updateUser,jdbcType=VARCHAR}
            </trim>
        </set>
        where seller_no=#{sellerNo,jdbcType=VARCHAR} and goods_no=#{goodsNo,jdbcType=VARCHAR}
    </update>


    <update id="updateListBoxGoodsPrice" parameterType="java.util.List">
        update con_box_goods
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="realtime_price =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.realtimePrice!=null">
                        when id=#{i.id} then #{i.realtimePrice}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.updateTime!=null">
                        when id=#{i.id} then #{i.updateTime}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_user =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.updateUser!=null">
                        when id=#{i.id} then #{i.updateUser}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index">
            id=#{i.id}
        </foreach>
    </update>


</mapper>