<?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.ruoyi.web.modules.goods.mapper.BizGoodsMapper">

    <resultMap id="BizGoodsResult" type="com.ruoyi.web.modules.goods.domain.BizGoods">
        <id property="goodsId" column="goods_id"/>
        <result property="shopId" column="shop_id"/>
        <result property="categoryId" column="category_id"/>
        <result property="shopCategoryId" column="shop_category_id"/>
        <result property="attrTemplateId" column="attr_template_id"/>
        <result property="goodsSn" column="goods_sn"/>
        <result property="goodsName" column="goods_name"/>
        <result property="mainPic" column="main_pic"/>
        <result property="detailContent" column="detail_content"/>
        <result property="salePrice" column="sale_price"/>
        <result property="stock" column="stock"/>
        <result property="salesCount" column="sales_count"/>
        <result property="goodsStatus" column="goods_status"/>
        <result property="rejectReason" column="reject_reason"/>
        <result property="submitTime" column="submit_time"/>
        <result property="auditTime" column="audit_time"/>
        <result property="offShelfTime" column="off_shelf_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectGoodsVo">
        select goods_id, shop_id, category_id, shop_category_id, attr_template_id, goods_sn, goods_name, main_pic, detail_content,
               sale_price, stock, sales_count, goods_status, reject_reason, submit_time, audit_time,
               off_shelf_time, del_flag, create_by, create_time, update_by, update_time, remark
        from biz_goods
    </sql>

    <sql id="listJoin">
        from biz_goods g
        inner join biz_shop s on g.shop_id = s.shop_id and s.del_flag = '0'
        inner join biz_merchant m on s.merchant_id = m.merchant_id and m.del_flag = '0'
        left join biz_goods_category c2 on g.category_id = c2.category_id and c2.del_flag = '0'
        left join biz_goods_category c1 on c2.parent_id = c1.category_id and c1.del_flag = '0'
        left join biz_goods_category sc2 on g.shop_category_id = sc2.category_id and sc2.del_flag = '0'
            and sc2.shop_id = g.shop_id
        left join biz_goods_category sc1 on sc2.parent_id = sc1.category_id and sc1.del_flag = '0'
            and sc1.shop_id = g.shop_id
        where g.del_flag = '0'
    </sql>

    <sql id="listSelect">
        select g.goods_id as goodsId, g.goods_sn as goodsSn, g.goods_name as goodsName, g.main_pic as mainPic,
               g.sale_price as salePrice, g.stock as stock, g.sales_count as salesCount,
               g.goods_status as goodsStatus, s.shop_name as shopName,
               ifnull(nullif(m.merchant_name, ''), ifnull(m.company_name, m.person_name)) as merchantName,
               concat(ifnull(c1.category_name, ''), ' > ', ifnull(c2.category_name, '')) as categoryPath,
               case when sc2.category_id is null then null
                    else concat(ifnull(sc1.category_name, ''), ' > ', ifnull(sc2.category_name, ''))
               end as shopCategoryName
    </sql>

    <sql id="listWhere">
        <if test="shopId != null">
            and g.shop_id = #{shopId}
        </if>
        <if test="goodsStatusQuery != null and goodsStatusQuery != ''">
            and g.goods_status = #{goodsStatusQuery}
        </if>
        <if test="keyword != null and keyword != ''">
            and (
                g.goods_name like concat('%', #{keyword}, '%')
                or g.goods_sn like concat('%', #{keyword}, '%')
                or s.shop_name like concat('%', #{keyword}, '%')
            )
        </if>
        <if test="goodsSn != null and goodsSn != ''">
            and g.goods_sn like concat('%', #{goodsSn}, '%')
        </if>
        <if test="goodsName != null and goodsName != ''">
            and g.goods_name like concat('%', #{goodsName}, '%')
        </if>
        <if test="shopName != null and shopName != ''">
            and s.shop_name like concat('%', #{shopName}, '%')
        </if>
        <if test="categoryId != null">
            and g.category_id = #{categoryId}
        </if>
    </sql>

    <select id="selectById" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where goods_id = #{goodsId} and del_flag = '0'
    </select>

    <select id="selectByIdAndShop" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where goods_id = #{goodsId} and shop_id = #{shopId} and del_flag = '0'
    </select>

    <select id="selectPlatformList" resultType="com.ruoyi.web.modules.goods.vo.GoodsListVO">
        <include refid="listSelect"/>
        <include refid="listJoin"/>
        and g.goods_status != '0'
        <include refid="listWhere"/>
        <choose>
            <when test="goodsStatusQuery != null and goodsStatusQuery == '1'">
                order by g.submit_time asc, g.goods_id asc
            </when>
            <otherwise>
                order by g.update_time desc, g.goods_id desc
            </otherwise>
        </choose>
    </select>

    <select id="countPlatformPending" resultType="int">
        select count(1)
        from biz_goods g
        inner join biz_shop s on g.shop_id = s.shop_id and s.del_flag = '0'
        where g.del_flag = '0'
          and g.goods_status = '1'
    </select>

    <select id="selectSellerList" resultType="com.ruoyi.web.modules.goods.vo.GoodsListVO">
        <include refid="listSelect"/>
        <include refid="listJoin"/>
        <include refid="listWhere"/>
        order by g.create_time desc, g.goods_id desc
    </select>

    <select id="countBlockingByShopId" resultType="int">
        select count(1) from biz_goods
        where shop_id = #{shopId} and del_flag = '0'
          and goods_status in ('1', '2')
    </select>

    <select id="countByCategoryId" resultType="int">
        select count(1) from biz_goods
        where category_id = #{categoryId} and del_flag = '0'
    </select>

    <select id="countByShopCategoryId" resultType="int">
        select count(1) from biz_goods
        where shop_category_id = #{shopCategoryId} and del_flag = '0'
    </select>

    <select id="countByAttrTemplateId" resultType="int">
        select count(1) from biz_goods
        where attr_template_id = #{templateId} and del_flag = '0'
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="goodsId">
        insert into biz_goods (
            shop_id, category_id, shop_category_id, attr_template_id, goods_sn, goods_name, main_pic, detail_content,
            sale_price, stock, sales_count, goods_status, del_flag, create_by, create_time
        ) values (
            #{shopId}, #{categoryId}, #{shopCategoryId}, #{attrTemplateId}, #{goodsSn}, #{goodsName}, #{mainPic}, #{detailContent},
            #{salePrice}, #{stock}, #{salesCount}, #{goodsStatus}, #{delFlag}, #{createBy}, sysdate()
        )
    </insert>

    <update id="updateSeller">
        update biz_goods
        <set>
            <if test="categoryId != null">category_id = #{categoryId},</if>
            <if test="shopCategoryId != null">shop_category_id = #{shopCategoryId},</if>
            <if test="attrTemplateId != null">attr_template_id = #{attrTemplateId},</if>
            <if test="goodsName != null">goods_name = #{goodsName},</if>
            <if test="mainPic != null">main_pic = #{mainPic},</if>
            <if test="detailContent != null">detail_content = #{detailContent},</if>
            <if test="salePrice != null">sale_price = #{salePrice},</if>
            <if test="stock != null">stock = #{stock},</if>
            update_by = #{updateBy},
            update_time = sysdate()
        </set>
        where goods_id = #{goodsId} and del_flag = '0'
    </update>

    <update id="updateSubmitStatus">
        update biz_goods
        set goods_status = #{goodsStatus},
            <if test="updateFirstSubmit">
                submit_time = #{submitTime},
            </if>
            reject_reason = null,
            update_by = #{updateBy},
            update_time = sysdate()
        where goods_id = #{goodsId} and del_flag = '0'
    </update>

    <update id="updateAuditPass">
        update biz_goods
        set goods_status = '2',
            audit_time = sysdate(),
            reject_reason = null,
            update_by = #{updateBy},
            update_time = sysdate()
        where goods_id = #{goodsId} and goods_status = '1' and del_flag = '0'
    </update>

    <update id="updateAuditReject">
        update biz_goods
        set goods_status = '3',
            reject_reason = #{rejectReason},
            audit_time = sysdate(),
            update_by = #{updateBy},
            update_time = sysdate()
        where goods_id = #{goodsId} and goods_status = '1' and del_flag = '0'
    </update>

    <update id="updateOffShelf">
        update biz_goods
        set goods_status = '4',
            off_shelf_time = sysdate(),
            update_by = #{updateBy},
            update_time = sysdate()
        where goods_id = #{goodsId} and goods_status = '2' and del_flag = '0'
    </update>

    <update id="logicDeleteByIds">
        update biz_goods
        set del_flag = '2',
            update_by = #{updateBy},
            update_time = sysdate()
        where shop_id = #{shopId}
          and del_flag = '0'
          and goods_id in
        <foreach collection="goodsIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="deductStock">
        update biz_goods
        set stock = stock - #{quantity},
            sales_count = sales_count + #{quantity},
            update_time = sysdate()
        where goods_id = #{goodsId}
          and stock &gt;= #{quantity}
          and del_flag = '0'
    </update>

    <update id="incrementStock">
        update biz_goods
        set stock = stock + #{quantity},
            update_time = sysdate()
        where goods_id = #{goodsId}
          and shop_id = #{shopId}
          and del_flag = '0'
    </update>

    <select id="selectByIdAndShopForUpdate" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where goods_id = #{goodsId}
          and shop_id = #{shopId}
          and del_flag = '0'
        for update
    </select>

    <select id="selectStockPickList" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where shop_id = #{shopId}
          and del_flag = '0'
        <if test="keyword != null and keyword != ''">
            and (
                goods_name like concat('%', #{keyword}, '%')
                or goods_sn like concat('%', #{keyword}, '%')
            )
        </if>
        order by goods_id desc
    </select>

    <select id="selectByGoodsSnAndShop" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where shop_id = #{shopId}
          and goods_sn = #{goodsSn}
          and del_flag = '0'
    </select>

    <select id="selectOutboundPickList" resultMap="BizGoodsResult">
        <include refid="selectGoodsVo"/>
        where shop_id = #{shopId}
          and del_flag = '0'
          and stock &gt; 0
        <if test="keyword != null and keyword != ''">
            and (
                goods_name like concat('%', #{keyword}, '%')
                or goods_sn like concat('%', #{keyword}, '%')
            )
        </if>
        order by goods_id desc
    </select>

    <update id="decrementStockForOutbound">
        update biz_goods
        set stock = stock - #{quantity},
            update_time = sysdate()
        where goods_id = #{goodsId}
          and shop_id = #{shopId}
          and stock &gt;= #{quantity}
          and del_flag = '0'
    </update>

    <update id="setStockForAdjust">
        update biz_goods
        set stock = #{stock},
            update_time = sysdate()
        where goods_id = #{goodsId}
          and shop_id = #{shopId}
          and del_flag = '0'
    </update>

    <select id="selectStockQueryList" resultType="com.ruoyi.web.modules.stock.vo.StockQueryListRowVO">
        select g.goods_id as goodsId,
               g.goods_name as goodsName,
               g.main_pic as mainPic,
               g.stock as stock
        from biz_goods g
        where g.shop_id = #{shopId}
          and g.del_flag = '0'
        <if test="keyword != null and keyword != ''">
            and (
                g.goods_name like concat('%', #{keyword}, '%')
                or exists (
                    select 1 from biz_goods_attr a
                    where a.goods_id = g.goods_id
                      and a.attr_type = '2'
                      and (
                          a.value_text like concat('%', #{keyword}, '%')
                          or a.item_name like concat('%', #{keyword}, '%')
                      )
                )
            )
        </if>
        order by g.update_time desc, g.goods_id desc
    </select>
</mapper>
