<?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.bonus.core.marketing.v2.mapper.MarketRuleMapper">
    <sql id="BaseColumn">
        rule_id,
           rule_name,
           scene_type,
           module_type,


           rule_type,
           effect_time_type,
           effect_begin_time,
           effect_end_time,
           effect_time_value as effect_time_value_str,
           effect_period,
           effect_period_value as effect_period_value_str,
           sort,
           remark,
           rule_info,
           group_flag,
           disable_flag,
           del_flag,
           crby,
           crtime,
           upby,
           uptime
    </sql>
    <select id="listSuitableMealRule" resultType="com.bonus.core.marketing.vo.MarketRuleVO">
        SELECT
        <include refid="BaseColumn"></include>
        FROM market_rule r
        WHERE r.del_flag = 2
        AND r.disable_flag = 2
        AND r.module_type = 1

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 1
        and (a.custom_sn = '-1'
        <if test="param.deviceSn != null and param.deviceSn != ''">
            or a.custom_sn = #{param.deviceSn}
        </if>
        ))
        order by r.sort, r.uptime desc
    </select>

    <select id="listSuitablePriceRule" resultType="com.bonus.core.marketing.vo.MarketRuleVO">
        SELECT
        <include refid="BaseColumn"></include>
        FROM market_rule r
        WHERE r.del_flag = 2
        AND r.disable_flag = 2
        AND r.module_type = 2
        <if test="param.sceneType != null">
            AND r.scene_type = #{param.sceneType}
        </if>

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 1
        and (a.custom_sn = '-1'
        <if test="param.deviceSn != null and param.deviceSn != ''">
            or a.custom_sn = #{param.deviceSn}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 2
        and (a.mealtime_type = -1
        <if test="param.mealtimeType != null">
            or a.mealtime_type = #{param.mealtimeType}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 3
        and (a.org_id = -1
        <if test="param.orgId != null">
            or a.org_id = #{param.orgId}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 4
        and (a.psn_type = -1
        <if test="param.psnType != null">
            or a.psn_type = #{param.psnType}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 5
        and (a.age_type = -1
        <if test="param.ageType != null">
            or a.age_type = #{param.ageType}
        </if>
        ))
        order by r.sort, r.uptime desc
    </select>

    <select id="listSuitablePayRule" resultType="com.bonus.core.marketing.vo.MarketRuleVO">
        SELECT
        <include refid="BaseColumn"></include>
        FROM market_rule r
        WHERE r.del_flag = 2
        AND r.disable_flag = 2
        AND r.module_type = 3

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 1
        and (a.custom_sn = '-1'
        <if test="param.deviceSn != null and param.deviceSn != ''">
            or a.custom_sn = #{param.deviceSn}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 3
        and (a.org_id = -1
        <if test="param.orgId != null">
            or a.org_id = #{param.orgId}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 4
        and (a.psn_type = -1
        <if test="param.psnType != null">
            or a.psn_type = #{param.psnType}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 5
        and (a.age_type = -1
        <if test="param.ageType != null">
            or a.age_type = #{param.ageType}
        </if>
        ))
        order by r.sort, r.uptime desc
    </select>

    <select id="listSuitableRechargeRule" resultType="com.bonus.core.marketing.vo.MarketRuleVO">
        SELECT
        <include refid="BaseColumn"></include>
        FROM market_rule r
        WHERE r.del_flag = 2
        AND r.disable_flag = 2
        AND r.module_type = 4

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 6
        and (a.pay_channel = -1
        <if test="param.payChannel != null">
            or a.pay_channel = #{param.payChannel}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 3
        and (a.org_id = -1
        <if test="param.orgId != null">
            or a.org_id = #{param.orgId}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 4
        and (a.psn_type = -1
        <if test="param.psnType != null">
            or a.psn_type = #{param.psnType}
        </if>
        ))

        AND EXISTS(select 1
        from market_rule_range a
        where a.rule_id = r.rule_id
        and a.range_type = 5
        and (a.age_type = -1
        <if test="param.ageType != null">
            or a.age_type = #{param.ageType}
        </if>
        ))
        order by r.sort, r.uptime desc
    </select>

</mapper>
