| | |
| | | <?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.goods.mapper.lottery.LotteryEventMapper"> |
| | | |
| | | |
| | | <select id="pageAppLotteryEvent" resultType="com.ruoyi.goods.domain.vo.AppLotteryEventPageVo"> |
| | | |
| | | |
| | | <select id="pageAppLotteryEvent" resultType="com.ruoyi.goods.domain.vo.AppLotteryEventPageVo"> |
| | | select * |
| | | from (select a.id, |
| | | a.name, |
| | |
| | | a.cover_image as coverImage, |
| | | if(ifnull(b.num, 0) = 0, false, true) as participation, |
| | | CASE |
| | | WHEN NOW() > a.start_time and now() <= a.end_time and ifnull(b.num, 0) > 0 THEN 1 |
| | | WHEN NOW() > a.start_time and now() <= a.end_time and ifnull(b.num, 0) = 0 THEN 2 |
| | | ELSE 3 END as sort |
| | | WHEN NOW() > a.start_time and now() <= a.end_time and ifnull(b.num, 0) = 0 THEN 1 |
| | | WHEN NOW() > a.start_time and now() <= a.end_time and ifnull(b.num, 0) > 0 THEN 2 |
| | | WHEN NOW() < a.start_time THEN 3 |
| | | ELSE 4 END as sort |
| | | from t_lottery_event a |
| | | left join (select count(1) as num, lottery_event_id |
| | | from t_user_lottery_event |
| | |
| | | and a.activity_type in (4, 5)) as aa |
| | | order by aa.sort, aa.startTime |
| | | </select> |
| | | |
| | | |
| | | <select id="getShopLotteryDrawList" resultType="com.ruoyi.goods.domain.vo.ShopLotteryDrawListVo"> |
| | | |
| | | |
| | | <select id="getShopLotteryDrawList" resultType="com.ruoyi.goods.domain.vo.ShopLotteryDrawListVo"> |
| | | select id, |
| | | `name`, |
| | | activity_profile as activityProfile, |
| | |
| | | order by create_time desc |
| | | </select> |
| | | <select id="pageMgtLotteryEvent" resultType="com.ruoyi.goods.domain.vo.MgtLotteryEventPageVo"> |
| | | SELECT |
| | | id, |
| | | shop_id, |
| | | name, |
| | | activity_type, |
| | | CONCAT(start_time,'-',end_time) activityTime, |
| | | create_time, |
| | | CASE |
| | | WHEN NOW() < start_time THEN 1 -- 未开始 |
| | | WHEN NOW() BETWEEN start_time AND end_time THEN 2 -- 进行中 |
| | | ELSE 3 -- 已结束 |
| | | END AS `status`, |
| | | CASE |
| | | WHEN NOW() BETWEEN start_time AND end_time THEN 1 |
| | | ELSE 0 |
| | | END AS flag |
| | | FROM |
| | | t_lottery_event |
| | | where |
| | | del_flag =0 |
| | | <choose> |
| | | <when test="dto.status != null and dto.status == 0"> |
| | | -- 0-全部状态,不添加额外条件 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 1"> |
| | | AND start_time > NOW() -- 1-未开始 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 2"> |
| | | AND start_time <= NOW() AND end_time >= NOW() -- 2-进行中 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 3"> |
| | | AND end_time < NOW() -- 3-已结束 |
| | | </when> |
| | | </choose> |
| | | <if test="null != dto.name and ''!= dto.name "> |
| | | and `name` like concat('%',#{dto.name},'%') |
| | | </if> |
| | | <if test="null != dto.activityType and dto.activityType!=0 "> |
| | | and activity_type = #{dto.activityType} |
| | | </if> |
| | | <if test="dto.shopIds != null and dto.shopIds.size() > 0"> |
| | | <choose> |
| | | <when test="dto.flag != null and dto.flag == 1"> |
| | | AND (shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | OR shop_id IS NULL) |
| | | </when> |
| | | <otherwise> |
| | | AND shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | </otherwise> |
| | | </choose> |
| | | </if> |
| | | order by create_time desc |
| | | limit #{dto.offset},#{dto.pageSize} |
| | | </select> |
| | | <select id="pageMgtLotteryEventCount" resultType="java.lang.Long"> |
| | | SELECT |
| | | count(*) |
| | | FROM |
| | | t_lottery_event |
| | | where |
| | | del_flag =0 |
| | | <choose> |
| | | <when test="dto.status != null and dto.status == 0"> |
| | | -- 0-全部状态,不添加额外条件 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 1"> |
| | | AND start_time > NOW() -- 1-未开始 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 2"> |
| | | AND start_time <= NOW() AND end_time >= NOW() -- 2-进行中 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 3"> |
| | | AND end_time < NOW() -- 3-已结束 |
| | | </when> |
| | | </choose> |
| | | <if test="null != dto.name and ''!= dto.name "> |
| | | and `name` like concat('%',#{dto.name},'%') |
| | | </if> |
| | | <if test="null != dto.activityType and dto.activityType!=0 "> |
| | | and activity_type = #{dto.activityType} |
| | | </if> |
| | | <if test="dto.shopIds != null and dto.shopIds.size() > 0"> |
| | | <choose> |
| | | <when test="dto.flag != null and dto.flag == 1"> |
| | | AND (shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | OR shop_id IS NULL) |
| | | </when> |
| | | <otherwise> |
| | | AND shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | </otherwise> |
| | | </choose> |
| | | </if> |
| | | </select> |
| | | SELECT |
| | | id, |
| | | shop_id, |
| | | name, |
| | | activity_type, |
| | | CONCAT(start_time,'-',end_time) activityTime, |
| | | create_time, |
| | | CASE |
| | | WHEN NOW() < start_time THEN 1 -- 未开始 |
| | | WHEN NOW() BETWEEN start_time AND end_time THEN 2 -- 进行中 |
| | | ELSE 3 -- 已结束 |
| | | END AS `status`, |
| | | CASE |
| | | WHEN NOW() BETWEEN start_time AND end_time THEN 1 |
| | | ELSE 0 |
| | | END AS flag |
| | | FROM |
| | | t_lottery_event |
| | | where |
| | | del_flag =0 |
| | | <choose> |
| | | <when test="dto.status != null and dto.status == 0"> |
| | | -- 0-全部状态,不添加额外条件 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 1"> |
| | | AND start_time > NOW() -- 1-未开始 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 2"> |
| | | AND start_time <= NOW() AND end_time >= NOW() -- 2-进行中 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 3"> |
| | | AND end_time < NOW() -- 3-已结束 |
| | | </when> |
| | | </choose> |
| | | <if test="null != dto.name and ''!= dto.name "> |
| | | and `name` like concat('%',#{dto.name},'%') |
| | | </if> |
| | | <if test="null != dto.activityType and dto.activityType!=0 "> |
| | | and activity_type = #{dto.activityType} |
| | | </if> |
| | | <if test="dto.shopIds != null and dto.shopIds.size() > 0"> |
| | | <choose> |
| | | <when test="dto.flag != null and dto.flag == 1"> |
| | | AND (shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | OR shop_id IS NULL) |
| | | </when> |
| | | <otherwise> |
| | | AND shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | </otherwise> |
| | | </choose> |
| | | </if> |
| | | order by create_time desc |
| | | limit #{dto.offset},#{dto.pageSize} |
| | | </select> |
| | | <select id="pageMgtLotteryEventCount" resultType="java.lang.Long"> |
| | | SELECT |
| | | count(*) |
| | | FROM |
| | | t_lottery_event |
| | | where |
| | | del_flag =0 |
| | | <choose> |
| | | <when test="dto.status != null and dto.status == 0"> |
| | | -- 0-全部状态,不添加额外条件 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 1"> |
| | | AND start_time > NOW() -- 1-未开始 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 2"> |
| | | AND start_time <= NOW() AND end_time >= NOW() -- 2-进行中 |
| | | </when> |
| | | <when test="dto.status != null and dto.status == 3"> |
| | | AND end_time < NOW() -- 3-已结束 |
| | | </when> |
| | | </choose> |
| | | <if test="null != dto.name and ''!= dto.name "> |
| | | and `name` like concat('%',#{dto.name},'%') |
| | | </if> |
| | | <if test="null != dto.activityType and dto.activityType!=0 "> |
| | | and activity_type = #{dto.activityType} |
| | | </if> |
| | | <if test="dto.shopIds != null and dto.shopIds.size() > 0"> |
| | | <choose> |
| | | <when test="dto.flag != null and dto.flag == 1"> |
| | | AND (shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | OR shop_id IS NULL) |
| | | </when> |
| | | <otherwise> |
| | | AND shop_id IN |
| | | <foreach collection="dto.shopIds" item="shopId" open="(" separator="," close=")"> |
| | | #{shopId} |
| | | </foreach> |
| | | </otherwise> |
| | | </choose> |
| | | </if> |
| | | </select> |
| | | </mapper> |