<?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.mcwl.memberCenter.mapper.BenefitMapper">

    <select id="getBenefitList" resultType="com.mcwl.memberCenter.domain.Benefit">
        select id, benefit_name_id, benefit_desc, benefit_type, benefit_discount, benefit_unit
        from mem_benefit
        where del_flag = '0'
        order by create_time desc
    </select>

    <select id="getBenefitListByMemberLevelId" resultType="com.mcwl.memberCenter.domain.Benefit">
        select b.id, benefit_name_id, benefit_desc, benefit_type, benefit_discount, benefit_unit
        from mem_benefit b
                 left join mem_member_benefit mb on b.id = mb.benefit_id
        where mb.member_level_id = #{memberLevelId}
          and b.del_flag = '0'
          and mb.del_flag = '0'
        order by b.create_time desc
    </select>
    <select id="getNotBenefitList" resultType="com.mcwl.memberCenter.domain.Benefit">
        select b.id, benefit_name_id, benefit_desc, benefit_type, benefit_discount, benefit_unit
        from mem_benefit b
        where b.id not in (select mb.benefit_id
                           from mem_member_benefit mb
                           where mb.member_level_id = #{memberLevelId}
                             and mb.del_flag = '0')
          and b.del_flag = '0'
        order by b.create_time desc
    </select>
    <select id="getBenefitByType" resultType="com.mcwl.memberCenter.domain.Benefit">
        select mb.id, benefit_name_id, benefit_desc, benefit_type, benefit_discount, benefit_unit
        from mem_benefit mb
                 left join mem_member_benefit mmb on mb.id = mmb.benefit_id
        where mb.del_flag = '0'
          and mmb.del_flag = '0'
          and member_level_id = #{memberLevelId}
          and benefit_type = #{benefitType}
    </select>
</mapper>