<?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.yami.shop.dao.ShopSkuMapper">


    <resultMap id="shopProdMap" type="com.yami.shop.bean.model.ShopSku">
        <id column="id" property="id"/>
        <result column="shop_id" property="shopId"/>
        <result column="sku_id" property="skuId"/>
        <result column="shop_sku_price" property="shopSkuPrice"/>
        <result column="shop_sku_stocks" property="shopSkuStocks"/>
        <result column="is_delete" property="isDelete"/>
        <result column="update_time" property="updateTime"/>
        <result column="rec_time" property="recTime"/>
    </resultMap>

    <update id="deleteByProdId">
        UPDATE tz_shop_sku
        SET is_delete = 1
        WHERE spu_id = #{prodId}
    </update>

    <select id="selectByShopAndSku" resultType="com.yami.shop.bean.model.ShopSku">
        SELECT s.*, sku.sku_name AS skuName
        FROM tz_shop_sku s
                 join tz_sku sku on sku.sku_id = s.sku_id and sku.is_delete = 0
        WHERE s.shop_id = #{shopId}
          AND s.sku_id = #{skuId}
          AND s.is_delete = 0
    </select>


    <select id="selectShopSkuPage" resultType="com.yami.shop.bean.vo.ProductPageVO">
        SELECT
        sku.prod_id spuId,
        sku.hb_spu_id,
        sku.pic,
        sku.sku_name AS skuName,
        a.prod_name,
        a.channel_code,
        sku.hb_sku_id,
        sku.sku_code,
        b.shop_id,
        b.shop_name,
        c.shop_sku_stocks,
        sku.sku_id,
        sku.`status`,
        MAX(cp.update_time) as update_time
        from tz_shop_sku c
        JOIN tz_sku sku ON sku.sku_id = c.sku_id AND sku.is_delete = 0
        join tz_prod a on a.prod_id = sku.prod_id and a.is_delete = 0
        JOIN tz_shop_detail b ON c.shop_id = b.shop_id
        LEFT JOIN tz_channel_prod cp ON c.shop_id = cp.shop_id and sku.sku_id = cp.sku_id and cp.is_delete = 0
        <where>
            c.is_delete = 0
            AND c.id IS NOT NULL
            AND sku.sku_id IS NOT NULL
            AND b.shop_id IS NOT NULL
            <if test='query.keywords!=null and query.keywords.trim() neq ""'>
                AND ( sku.sku_name LIKE concat('%',#{query.keywords},'%') OR sku.hb_sku_id = #{query.keywords} OR
                a.hb_spu_id = #{query.keywords})
            </if>
            <if test='query.hbSkuId!=null and query.hbSkuId.trim() neq ""'>
                AND sku.hb_sku_id = #{query.hbSkuId}
            </if>
            <if test='query.skuName!=null and query.skuName.trim() neq ""'>
                AND sku.sku_name LIKE concat('%',#{query.skuName},'%')
            </if>
            <if test='query.skuCode!=null and query.skuCode.trim() neq ""'>
                AND sku.sku_code = #{query.skuCode}
            </if>
            <if test='query.shopId!=null'>
                AND c.shop_id = #{query.shopId}
            </if>
            <if test='query.status!=null'>
                AND a.status = #{query.status} and a.prod_id IS NOT NULL
            </if>
            <if test='(query.startTime!=null and query.startTime!="") and (query.endTime!=null and query.endTime!="")'>
                AND (cp.update_time BETWEEN #{query.startTime} AND #{query.endTime})
            </if>
            <if test='(query.minPrice!=null and query.minPrice!="") and (query.maxPrice!=null and query.maxPrice!="")'>
                AND (cp.channel_prod_price BETWEEN #{query.minPrice} AND #{query.maxPrice})
            </if>
        </where>
        GROUP BY c.shop_id, sku.sku_id, c.shop_sku_stocks,c.rec_time
        order by c.rec_time desc
    </select>


    <select id="exportList" resultType="com.yami.shop.bean.vo.ChannelProdExportVO">
        SELECT
        sku.hb_sku_id AS hbSkuId,
        sku.sku_name AS skuName,
        b.shop_name AS shopName,
        c.shop_sku_stocks AS shopSkuStock,

        channel.channel_name channelName,
        cp.purchase_price purchasePrice,
        cp.delivery_price deliveryPrice,
        cp.channel_prod_price channelProdPrice

        from tz_shop_sku c
        LEFT JOIN tz_sku sku ON sku.sku_id = c.sku_id AND sku.is_delete = 0
        left join tz_prod a on a.prod_id = sku.prod_id and a.is_delete = 0
        LEFT JOIN tz_shop_detail b ON c.shop_id = b.shop_id
        LEFT JOIN tz_channel_prod cp ON c.shop_id = cp.shop_id and sku.sku_id = cp.sku_id and cp.is_delete = 0
        left join tz_channel channel on cp.channel_id = channel.id
        <where>
            c.is_delete = 0
            AND c.id IS NOT NULL
            AND sku.sku_id IS NOT NULL
            AND b.shop_id IS NOT NULL
            <if test='query.keywords!=null and query.keywords.trim() neq ""'>
                AND ( sku.sku_name LIKE concat('%',#{query.keywords},'%') OR sku.hb_sku_id = #{query.keywords} OR
                sku.hb_spu_id = #{query.keywords} )
            </if>

            <if test='query.hbSkuId!=null and query.hbSkuId.trim() neq ""'>
                AND sku.hb_sku_id = #{query.hbSkuId}
            </if>
            <if test='query.skuCode!=null and query.skuCode.trim() neq ""'>
                AND sku.sku_code = #{query.skuCode}
            </if>
            <if test='query.shopId!=null'>
                AND c.shop_id = #{query.shopId}
            </if>
            <if test='query.status!=null'>
                AND a.status = #{query.status} and a.prod_id IS NOT NULL
            </if>
            <if test='(query.startTime!=null and query.startTime!="") and (query.endTime!=null and query.endTime!="")'>
                AND (cp.update_time BETWEEN #{query.startTime} AND #{query.endTime})
            </if>
            <if test='(query.minPrice!=null and query.minPrice!="") and (query.maxPrice!=null and query.maxPrice!="")'>
                AND (cp.channel_prod_price BETWEEN #{query.minPrice} AND #{query.maxPrice})
            </if>
        </where>
        order by c.rec_time desc
    </select>
</mapper>
