<?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.course.mapper.ScMemberCardsMapper">

    <select id="getByMemberId" resultType="ScMemberCard">
        SELECT
            b.*
        from sc_member_cards b
        where b.status in ('ACTIVE','INACTIVE')
          and (b.expiry_date is null or b.expiry_date > #{claDate})
          and (
                (b.charge_type='count' and b.remaining_count>0)
                or
                (b.charge_type='total_fee' and b.remaining_total_fee>0.0)
            )
          AND is_deleted = 0
          and b.member_id=#{memberId}
          and b.tenant_id=#{tenantId}
        order by  b.expiry_date is null,b.expiry_date

    </select>

    <select id="getCourseDetail" resultType="RespMemberTypeCourses">
        SELECT

            f.id AS member_card_id,
            f.card_no AS card_no,
            f.expiry_date AS expiry_date,
            f.remaining_count AS remaining_count,
            f.remaining_total_fee AS remaining_total_fee,
            f.charge_type AS charge_type,

            a.card_type_id,
            a.card_name,

            b.course_type_id,
            b.course_type,

            e.course_id,
            e.course_name,
            e.tuition_fee,

            CASE
                WHEN a.charge_type = 'default' THEN a.default_num
                WHEN a.charge_type = 'customize' THEN c.cnt
                ELSE 0
                END AS deduct_cnt,
            CASE
                WHEN a.charge_type = 'default' THEN a.default_fee
                WHEN a.charge_type = 'customize' THEN c.total_fee
                ELSE 0.00
                END AS deduct_fee
        FROM
            sc_member_card_types a

                INNER JOIN sc_course_type b
                           ON JSON_CONTAINS(
                                   a.course_types,
                                   CAST(b.course_type_id AS CHAR),
                                   '$'
                               )

                LEFT JOIN sc_member_card_charge c
                          ON a.charge_type = 'customize'
                              AND c.card_type_id = a.card_type_id
                              AND c.course_type_id = b.course_type_id

                LEFT JOIN sc_member_card_courses d
                          ON a.restricted_courses = '2'
                              AND d.card_type_id = a.card_type_id
                              AND d.course_type_id = b.course_type_id

                LEFT JOIN sc_member_cards f
                          ON a.card_type_id = f.card_type_id

                INNER JOIN sc_course e
                           ON (
                                  a.restricted_courses = '1'
                               AND e.course_type_id = b.course_type_id
                               )
                           OR (
                                  a.restricted_courses = '2'
                                AND JSON_CONTAINS(d.courses,CAST(e.course_id AS CHAR),'$')
                               )

        WHERE
            a.delete_flag = '0'
          AND b.in_use = '1'
          AND e.delete_flag = '0'
        and (a.restricted_teacher='1'
        or (a.restricted_teacher='2'
        and JSON_CONTAINS(teacher_list, CAST(#{teacherId} AS JSON))))
          and e.course_id=#{courseId}
          and f.id in
                <foreach item="id" collection="cards" open="(" separator="," close=")">
                    #{id}
                </foreach>

        ORDER BY a.card_type_id,b.course_type_id
    </select>

    <select id="getMemberTypeById" resultType="RespMemberTypeCourses">
        select *
            from sc_member_card_types a
            where a.card_type_id= #{typeId}
    </select>


</mapper>
