<?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.liquidnet.service.goblin.mapper.GoblinSelfMarketingMapper">

    <resultMap id="goblinZhengzaiStoreListDtoResult" type="com.liquidnet.service.goblin.dto.GoblinZhengzaiStoreListDto">
        <result column="store_id" property="storeId"/>
        <result column="store_name" property="storeName"/>
        <result column="show_time" property="showTime"/>
        <result column="spu_count" property="spuCount"/>
        <result column="sku_count" property="skuCount"/>
    </resultMap>

    <resultMap id="goblinZhengzaiStoreDetailsDtoResult"
               type="com.liquidnet.service.goblin.dto.GoblinZhengzaiStoreDetailsDto">
        <result column="sku_id" property="skuId"/>
        <result column="spec_name" property="specName"/>
        <result column="spec_vname" property="specVname"/>
        <result column="price" property="price"/>
        <result column="stock" property="stock"/>
        <result column="sku_stock" property="skuStock"/>
        <result column="price_marketing" property="priceMarketing"/>
        <result column="stock_marketing" property="stockMarketing"/>
        <result column="buy_limit" property="buyLimit"/>
        <result column="buy_type" property="buyType"/>
    </resultMap>

    <resultMap id="goblinSelfMarketingDtoResult"
               type="com.liquidnet.service.goblin.dto.GoblinSelfMarketingDto">
        <result column="self_market_id" property="selfMarketId"/>
        <result column="performance_id" property="performanceId"/>
        <result column="performance_img" property="performanceImg"/>
        <result column="name" property="name"/>
        <result column="type" property="type"/>
        <result column="status" property="status"/>
        <result column="describes" property="describes"/>
        <result column="start_time" property="startTime"/>
        <result column="end_time" property="endTime"/>
        <result column="created_at" property="createdAt"/>
        <result column="store_count" property="storeCount"/>
    </resultMap>


    <select id="getZhengzaiStoreList" resultMap="goblinZhengzaiStoreListDtoResult">
select b.store_id,
       c.store_name,
       b.show_time,
       IFNull(count(distinct d.spu_id), 0) as 'spu_count',
       IFNull(count(distinct d.sku_id), 0) as 'sku_count'
from goblin_self_marketing as a
         inner join goblin_marketing_zhengzai_relation as b on a.self_market_id = b.self_market_id
         inner join goblin_store_info as c on c.store_id = b.store_id and b.del_tag = 0
         left join goblin_marketing_zhengzai as d on d.store_id = b.store_id  and d.self_market_id = b.self_market_id
where a.self_market_id = #{marketId} and b.del_tag = 0
GROUP BY b.store_id ORDER BY b.created_at asc
    </select>

    <select id="getZhengzaiStoreDetails" resultMap="goblinZhengzaiStoreDetailsDtoResult">
    select ggsku.sku_id,price,spec_name, spec_vname, ggsku.stock, ggsku.sku_stock from goblin_goods_sku ggsku
         inner join goblin_goods_sku_spec_value ggssv on ggsku.sku_id = ggssv.sku_id
         left join (select spec_name, spec_vname, ggs.spec_id
                     from goblin_goods_spec ggs
                              inner join goblin_goods_spec_value ggsv on ggsv.spec_id = ggs.spec_id) as a
                    on a.spec_id = ggssv.spec_id where ggsku.sku_id in (
                    select sku_id from goblin_marketing_zhengzai where self_market_id = #{marketId} and store_id = #{storeId}
                    )
     </select>

    <select id="getZhengzaiList" resultMap="goblinSelfMarketingDtoResult">
        select
        gsm.mid,gsm.name,gsm.performance_id,gsm.performance_img,gsm.created_at,gsm.updated_at,gsm.type,gsm.status,gsm.start_time,gsm.end_time,gsm.self_market_id,gsm.describes,count(gmzr.store_id)
        as 'store_count'
        from goblin_self_marketing as gsm left join goblin_marketing_zhengzai_relation gmzr on gsm.self_market_id = gmzr.self_market_id and gmzr.del_tag !=1
        <where>
            gsm.del_flag = 0
            <if test="purchaseName != null and purchaseName!=''">and gsm.name like concat('%', #{purchaseName}, '%')
            </if>
            <if test="ct != null and ct!=''">and gsm.created_at = #{ct}</if>
            <if test="st != null and et != null and st != '' and et != ''">and and gsm.start_time <![CDATA[ >= ]]> #{st}
                and
                gsm.end_time <![CDATA[ <= ]]> #{et}
            </if>
            <if test="status==0">and gsm.start_time <![CDATA[ >= ]]> NOW() and gsm.status!=7</if>
            <if test="status==1">and gsm.start_time <![CDATA[ < ]]> NOW() and gsm.end_time <![CDATA[ > ]]> NOW() and
                gsm.status!=7
            </if>
            <if test="status==2">and gsm.end_time <![CDATA[ <= ]]> NOW() and gsm.status!=7</if>
            <if test="status==7">and gsm.status!=7</if>
        </where>
        group by gsm.self_market_id
        order by gsm.created_at desc
    </select>

</mapper>
