<?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>
