<!--查询该部门，该门店门店有阶段性活动的数量-->
<select id="getStageActivityCount" parameterType="com.jd.wanjia.activity.dto.StageActivityParameterDTO" resultType="int">
SELECT
COUNT(1)
FROM
activity_info i
LEFT JOIN activity_object o ON i.activity_no = o.activity_no
WHERE
<!--activity_info表Type 活动类型1.开店任务2.阶段性任务3.营销任务4.图文任务5.物料任务-->
<if test="activityInfoType!=null">
i.type=#{activityInfoType}
</if>
<!--activity_info表activity_status 活动状态:0创建中,1审核中,2审核通过,3被驳回,4进行中,5已失效,6已结束-->
<if test="activityInfoStatus!=null">
AND i.activity_status=#{activityInfoStatus}
</if>
<!--对象类型1:平台商2:商家3:门店4:导购-->
<if test="objectType!=null">
AND i.object_type=#{objectType}
</if>
<!-- 查询条件里shopId对应object_id-->
<if test="shopId!=null">
AND (
o.object_id =#{shopId}
OR o.object_id is null
)
</if>
<if test="departNo!=null">
AND i.depart_no=#{departNo}
</if>
AND i.is_delete = 0
AND
(
o.is_delete = 0
or o.is_delete is null
)
</select>
<!--查询商品是否参与正在进行中的阶段性活动的商品信息-->
<select id="queryStageActivityByParameter" parameterType="com.jd.wanjia.activity.dto.StageActivityParameterDTO" resultType="com.jd.wanjia.activity.po.ActivitySku">
SELECT
MAX(i.activity_no) AS activityNo,s.sku_type AS skuType,s.sku_id AS skuId, s.brand_code AS brandCode,
s.first_cate_code AS firstCateCode, s.second_cate_code AS secondCateCode, s.third_cate_code AS thirdCateCode
FROM
activity_info i
INNER JOIN activity_sku s ON i.activity_no = s.activity_no
LEFT JOIN activity_object o ON i.activity_no = o.activity_no
WHERE
<!--activity_info表Type 活动类型1.开店任务2.阶段性任务3.营销任务4.图文任务5.物料任务-->
<if test="activityInfoType!=null">
i.type=#{activityInfoType}
</if>
<!--activity_info表activity_status 活动状态:0创建中,1审核中,2审核通过,3被驳回,4进行中,5已失效,6已结束-->
<if test="activityInfoStatus!=null">
AND i.activity_status=#{activityInfoStatus}
</if>
<!--对象类型1:平台商2:商家3:门店4:导购-->
<if test="objectType!=null">
AND i.object_type=#{objectType}
</if>
<!-- 查询条件里shopId对应object_id-->
<if test="shopId!=null">
AND (
o.object_id =#{shopId}
OR o.object_id is null
)
</if>
<if test="departNo!=null">
AND
i.depart_no=#{departNo}
</if>
<!--type'1.sku 2.品类'-->
<if test="sType!=null">
AND s.type=#{sType}
</if>
<!--sku_type'1.自营 2.POP',-->
<if test="skuTypeList!=null">
<![CDATA[ AND s.sku_type IN ]]>
<foreach collection="skuTypeList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
<if test="skuIdList!=null">
<![CDATA[ AND s.sku_id IN ]]>
<foreach collection="skuIdList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
<if test="brandCodeList!=null">
<![CDATA[ AND s.brand_code IN ]]>
<foreach collection="brandCodeList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
<if test="firstCateCodeList!=null">
<![CDATA[ AND s.first_cate_code IN ]]>
<foreach collection="firstCateCodeList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
<if test="secondCateCodeList!=null">
<![CDATA[ AND s.second_cate_code IN ]]>
<foreach collection="secondCateCodeList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
<if test="thirdCateCodeList!=null">
<![CDATA[ AND s.third_cate_code IN ]]>
<foreach collection="thirdCateCodeList" item="item" open="(" separator="," close=")">
<![CDATA[#{item}]]>
</foreach>
</if>
AND i.is_delete = 0
AND s.is_delete = 0
AND
(
o.is_delete = 0
or o.is_delete is null
)
<if test="sType==1">
GROUP BY s.sku_type,s.sku_id
</if>
<if test="sType==2">
GROUP BY s.sku_type,s.brand_code,s.first_cate_code,s.second_cate_code,s.third_cate_code
</if>
</select>
对应mapper.java
