<?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.sv.mapper.MemberLessonTicketMapper">

    <resultMap id="MemberLessonTicketMap" type="com.sv.entity.MemberLessonTicket">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="veneu_type" property="veneuType" jdbcType="TINYINT"/>
        <result column="venue_id" property="venueId" jdbcType="INTEGER"/>
        <result column="ticket_id" property="ticketId" jdbcType="INTEGER"/>
        <result column="ticket" property="ticket" jdbcType="VARCHAR"/>
        <result column="lesson_id" property="lessonId" jdbcType="INTEGER"/>
        <result column="member_id" property="memberId" jdbcType="INTEGER"/>
        <result column="order_sn" property="orderSn" jdbcType="VARCHAR"/>
        <result column="pay_type" property="payType" jdbcType="TINYINT"/>
        <result column="member_card_id" property="memberCardId" jdbcType="INTEGER"/>
        <result column="pay_status" property="payStatus" jdbcType="TINYINT"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <result column="platform_id" property="platformId" jdbcType="INTEGER"/>
        <result column="created_id" property="createdId" jdbcType="INTEGER"/>
        <result column="modified_id" property="modifiedId" jdbcType="INTEGER"/>
        <result column="created_time" property="createdTime" jdbcType="TIMESTAMP"/>
        <result column="modified_time" property="modifiedTime" jdbcType="TIMESTAMP"/>
        <result column="pay_time" property="payTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="TINYINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="nickname" property="nickname" jdbcType="VARCHAR"/>
        <result column="lessonname" property="lessonname" jdbcType="VARCHAR"/>
        <result column="price" property="price" jdbcType="VARCHAR"/>
        <result column="prices" property="prices" jdbcType="VARCHAR"/>
        <result column="card_type" property="cardType" jdbcType="VARCHAR"/>
        <result column="parent_order_id" property="parentOrderId" jdbcType="VARCHAR"/>
        <result column="trade_sn" property="tradeSn" jdbcType="VARCHAR"/>
        <result column="counts" property="counts" jdbcType="VARCHAR"/>
        <result column="mobile" property="mobile" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap id="MemberLessonTicketDtoMap" type="com.sv.dto.api.MemberLessonTicketDTO">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="veneu_type" property="veneuType" jdbcType="TINYINT"/>
        <result column="venue_id" property="venueId" jdbcType="INTEGER"/>
        <result column="ticket_id" property="ticketId" jdbcType="INTEGER"/>
        <result column="ticket" property="ticket" jdbcType="VARCHAR"/>
        <result column="lesson_id" property="lessonId" jdbcType="INTEGER"/>
        <result column="member_id" property="memberId" jdbcType="INTEGER"/>
        <result column="order_sn" property="orderSn" jdbcType="VARCHAR"/>
        <result column="pay_type" property="payType" jdbcType="TINYINT"/>
        <result column="member_card_id" property="memberCardId" jdbcType="INTEGER"/>
        <result column="pay_status" property="payStatus" jdbcType="TINYINT"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <result column="platform_id" property="platformId" jdbcType="INTEGER"/>
        <result column="created_id" property="createdId" jdbcType="INTEGER"/>
        <result column="modified_id" property="modifiedId" jdbcType="INTEGER"/>
        <result column="created_time" property="createdTime" jdbcType="TIMESTAMP"/>
        <result column="modified_time" property="modifiedTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="TINYINT"/>
        <association property="lessonImage" select="com.sv.mapper.VenueLessonImageMapper.findByLesson"
                     column="lesson_id"></association>
        <association property="order" select="com.sv.mapper.OrderMapper.findByOrderSn" column="order_sn"></association>
    </resultMap>

    <resultMap id="MemberLessonTicketDetailDtoMap" type="com.sv.dto.api.MemberLessonTicketDetailDTO"
               extends="MemberLessonTicketMap">
        <association property="venueLesson" select="com.sv.mapper.VenueLessonMapper.findByLessonId"
                     column="lesson_id"></association>
        <association property="order" select="com.sv.mapper.OrderMapper.findByOrderSn" column="order_sn"></association>
    </resultMap>

    <resultMap id="MemberLessonTicketExpiredMap" type="com.sv.dto.api.MemberLessonTicketDTO">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="veneu_type" property="veneuType" jdbcType="TINYINT"/>
        <result column="venue_id" property="venueId" jdbcType="INTEGER"/>
        <result column="ticket_id" property="ticketId" jdbcType="INTEGER"/>
        <result column="ticket" property="ticket" jdbcType="VARCHAR"/>
        <result column="lesson_id" property="lessonId" jdbcType="INTEGER"/>
        <result column="member_id" property="memberId" jdbcType="INTEGER"/>
        <result column="order_sn" property="orderSn" jdbcType="VARCHAR"/>
        <result column="pay_type" property="payType" jdbcType="TINYINT"/>
        <result column="member_card_id" property="memberCardId" jdbcType="INTEGER"/>
        <result column="pay_status" property="payStatus" jdbcType="TINYINT"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <result column="platform_id" property="platformId" jdbcType="INTEGER"/>
        <result column="created_id" property="createdId" jdbcType="INTEGER"/>
        <result column="modified_id" property="modifiedId" jdbcType="INTEGER"/>
        <result column="created_time" property="createdTime" jdbcType="TIMESTAMP"/>
        <result column="modified_time" property="modifiedTime" jdbcType="TIMESTAMP"/>
        <result column="deleted" property="deleted" jdbcType="TINYINT"/>
    </resultMap>

    <!--表名 -->
    <sql id="tableName">
		sv_member_lesson_ticket
	</sql>

    <!-- 字段 -->
    <sql id="Field">
		id,
		veneu_type,
		venue_id,
		ticket_id,
		ticket,
		lesson_id,
		member_id,
		order_sn,
		pay_type,
		member_card_id,
		pay_status,
		status,
		platform_id,
		created_id,
		modified_id,
		created_time,
		modified_time,
		deleted
	</sql>

    <!-- 字段值 -->
    <sql id="FieldValue">
		#{id, jdbcType=INTEGER},
		#{veneuType, jdbcType=TINYINT},
		#{venueId, jdbcType=INTEGER},
		#{ticketId, jdbcType=INTEGER},
		#{ticket, jdbcType=VARCHAR},
		#{lessonId, jdbcType=INTEGER},
		#{memberId, jdbcType=INTEGER},
		#{orderSn, jdbcType=VARCHAR},
		#{payType, jdbcType=TINYINT},
		#{memberCardId, jdbcType=INTEGER},
		#{payStatus, jdbcType=TINYINT},
		#{status, jdbcType=TINYINT},
		#{platformId, jdbcType=INTEGER},
		#{createdId, jdbcType=INTEGER},
		#{modifiedId, jdbcType=INTEGER},
		#{createdTime, jdbcType=TIMESTAMP},
		#{modifiedTime, jdbcType=TIMESTAMP},
		#{deleted, jdbcType=TINYINT}
	</sql>

    <!-- 查询全部记录 -->
    <select id="findAll" resultMap="MemberLessonTicketMap">
        SELECT
        <include refid="Field"></include>
        FROM
        <include refid="tableName"></include>
        WHERE
        deleted = 0
    </select>

    <!-- 查询全部记录-oms -->
    <select id="findAlls" resultMap="MemberLessonTicketDetailDtoMap">
        SELECT
        smlt.id,
        smlt.veneu_type,
        smlt.venue_id,
        smlt.ticket_id,
        smlt.ticket,
        smlt.lesson_id,
        smlt.member_id,
        smlt.order_sn,
        smlt.pay_type,
        smlt.member_card_id,
        smlt.pay_status,
        smlt.status,
        smlt.platform_id,
        smlt.created_id,
        smlt.modified_id,
        smlt.created_time,
        smlt.modified_time,
        smlt.deleted,
        sv.name,
        svl.price AS prices,
        svl.name AS lessonname,
        sm.nickname,
        sm.mobile,
        so.price,
        smc.card_type,
        so.pay_time,
        so.parent_order_id,
        so.trade_sn,
        (SELECT COUNT(0) from sv_member_lesson_ticket WHERE order_sn = smlt.order_sn) AS counts
        FROM
        sv_member_lesson_ticket AS smlt
        LEFT JOIN sv_venue AS sv ON smlt.venue_id = sv.id
        LEFT JOIN sv_venue_lesson AS svl ON smlt.lesson_id = svl.id
        LEFT JOIN sv_member AS sm ON smlt.member_id = sm.id
        LEFT JOIN sv_member_card AS smc ON smlt.member_card_id = smc.id
        LEFT JOIN sv_order AS so ON smlt.order_sn = so.order_sn
        WHERE
        smlt.deleted = 0
        AND sv.deleted = 0
        AND svl.deleted = 0
        AND so.deleted = 0
        <if test="lessonId != null">
            AND smlt.lesson_id = #{lessonId}
            AND smlt.status != '2'
        </if>
        <if test="payStatus != null">
            AND smlt.pay_status = #{payStatus}
        </if>
        <if test="orderSn != null">
            AND smlt.order_sn = #{orderSn}
        </if>
        <if test="lessonname != null">
            AND svl.name = #{lessonname}
        </if>
        <if test="starTime != ''">
            AND smlt.created_time &gt;= #{starTime}
        </if>
        <if test="endTime != ''">
            AND smlt.created_time &lt;= #{endTime}
        </if>
        GROUP BY smlt.order_sn
    </select>

    <!-- 新增-->
    <insert id="insert" parameterType="com.sv.entity.MemberLessonTicket" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
        <include refid="tableName"></include>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="veneuType != null">
                veneu_type,
            </if>
            <if test="venueId != null">
                venue_id,
            </if>
            <if test="ticketId != null">
                ticket_id,
            </if>
            <if test="ticket != null">
                ticket,
            </if>
            <if test="lessonId != null">
                lesson_id,
            </if>
            <if test="memberId != null">
                member_id,
            </if>
            <if test="orderSn != null">
                order_sn,
            </if>
            <if test="payType != null">
                pay_type,
            </if>
            <if test="memberCardId != null">
                member_card_id,
            </if>
            <if test="payStatus != null">
                pay_status,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="platformId != null">
                platform_id,
            </if>
            <if test="createdId != null">
                created_id,
            </if>
            <if test="modifiedId != null">
                modified_id,
            </if>
            <if test="createdTime != null">
                created_time,
            </if>
            <if test="modifiedTime != null">
                modified_time,
            </if>
            <if test="deleted != null">
                deleted,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="veneuType != null">
                #{veneuType},
            </if>
            <if test="venueId != null">
                #{venueId},
            </if>
            <if test="ticketId != null">
                #{ticketId},
            </if>
            <if test="ticket != null">
                #{ticket},
            </if>
            <if test="lessonId != null">
                #{lessonId},
            </if>
            <if test="memberId != null">
                #{memberId},
            </if>
            <if test="orderSn != null">
                #{orderSn},
            </if>
            <if test="payType != null">
                #{payType},
            </if>
            <if test="memberCardId != null">
                #{memberCardId},
            </if>
            <if test="payStatus != null">
                #{payStatus},
            </if>
            <if test="status != null">
                #{status},
            </if>
            <if test="platformId != null">
                #{platformId},
            </if>
            <if test="createdId != null">
                #{createdId},
            </if>
            <if test="modifiedId != null">
                #{modifiedId},
            </if>
            <if test="createdTime != null">
                #{createdTime},
            </if>
            <if test="modifiedTime != null">
                #{modifiedTime},
            </if>
            <if test="deleted != null">
                #{deleted},
            </if>
        </trim>
    </insert>

    <!-- 删除-->
    <delete id="delete" parameterType="java.lang.Integer">
        UPDATE
        <include refid="tableName"></include>
        SET
        deleted = 1
        WHERE
        id = #{id}
    </delete>

    <!-- 批量删除 -->
    <delete id="deleteByIds">
        UPDATE
        <include refid="tableName"></include>
        SET
        deleted = 1
        WHERE
        id IN
        <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

    <!-- 更新记录 -->
    <update id="update" parameterType="com.sv.entity.MemberLessonTicket">
        UPDATE
        <include refid="tableName"></include>
        <set>
            <if test="veneuType != null">
                veneu_type = #{veneuType},
            </if>
            <if test="venueId != null">
                venue_id = #{venueId},
            </if>
            <if test="ticketId != null">
                ticket_id = #{ticketId},
            </if>
            <if test="ticket != null">
                ticket = #{ticket},
            </if>
            <if test="lessonId != null">
                lesson_id = #{lessonId},
            </if>
            <if test="memberId != null">
                member_id = #{memberId},
            </if>
            <if test="orderSn != null">
                order_sn = #{orderSn},
            </if>
            <if test="payType != null">
                pay_type = #{payType},
            </if>
            <if test="memberCardId != null">
                member_card_id = #{memberCardId},
            </if>
            <if test="payStatus != null">
                pay_status = #{payStatus},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="platformId != null">
                platform_id = #{platformId},
            </if>
            <if test="createdId != null">
                created_id = #{createdId},
            </if>
            <if test="modifiedId != null">
                modified_id = #{modifiedId},
            </if>
            <if test="createdTime != null">
                created_time = #{createdTime},
            </if>
            <if test="modifiedTime != null">
                modified_time = #{modifiedTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 通过编号查询 -->
    <select id="findById" parameterType="java.lang.Integer" resultMap="MemberLessonTicketDetailDtoMap">
		SELECT
			smlt.id,
			smlt.veneu_type,
			smlt.venue_id,
			smlt.ticket_id,
			smlt.ticket,
			smlt.lesson_id,
			smlt.member_id,
			smlt.order_sn,
			smlt.pay_type,
			smlt.member_card_id,
			smlt.pay_status,
			smlt.status,
			smlt.platform_id,
			smlt.created_id,
			smlt.modified_id,
			smlt.created_time,
			smlt.modified_time,
			smlt.deleted,
			sv.name,
			svl.name AS lessonname,
			sm.nickname,
			svl.price,
			smc.card_type,
			so.pay_time,
			so.parent_order_id,
			so.trade_sn
		FROM
			sv_member_lesson_ticket AS smlt
		LEFT JOIN sv_venue AS sv ON smlt.venue_id = sv.id
		LEFT JOIN sv_venue_lesson AS svl ON smlt.lesson_id = svl.id
		LEFT JOIN sv_member AS sm ON smlt.member_id = sm.id
		LEFT JOIN sv_member_card AS smc ON smlt.member_card_id = smc.id
		LEFT JOIN sv_order AS so ON smlt.order_sn = so.order_sn
		WHERE smlt.id = #{id}
	</select>

    <!--更新用户预约的场馆课程票号状态-->
    <update id="updateStatus">
        UPDATE
        <include refid="tableName"></include>
        SET
        status = #{status, jdbcType=TINYINT}
        WHERE
        id
        IN
        <foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </update>

    <!-- api根据订单号，查找‘用户预约的场馆课程票号’记录 -->
    <select id="findByOrderSn" resultMap="MemberLessonTicketMap">
        select
        id,
        veneu_type,
        venue_id,
        ticket_id,
        ticket,
        lesson_id,
        member_id,
        pay_status
        from
        <include refid="tableName"></include>
        where
        order_sn = #{orderSn}
    </select>

    <!-- api修改‘用户预约的场馆课程票号’支付状态：已支付 -->
    <update id="updateTicketStatus">
        UPDATE
        <include refid="tableName"></include>
        SET
        pay_status = 1
        WHERE
        id
        IN
        <foreach collection="memberLessonTickets" item="memberLessonTickets" index="index" open="(" close=")"
                 separator=",">
            #{memberLessonTickets.id}
        </foreach>
        AND
        deleted = 0
    </update>

    <!-- api 用户预约记录 -->
    <select id="findMemberLessonTicketReserve" resultMap="MemberLessonTicketDtoMap">
        SELECT
        st.lesson_id,
        se.`name` as lessonName,
        se.date,
        se.start_time as startTime,
        se.end_time as endTime,
        se.price,
        st.member_card_id,
        st.order_sn,
        st.`status`,
        (SELECT COUNT(0) FROM sv_member_lesson_ticket WHERE order_sn = st.order_sn) AS lessonCount,
        st.created_time,
        st.pay_type,
        ue.address as address,
        ue.longitude,
        ue.latitude
        FROM
        sv_member_lesson_ticket AS st
        LEFT JOIN sv_venue_lesson AS se ON st.lesson_id = se.id
        LEFT JOIN sv_venue as ue ON se.venue_id = ue.id
        WHERE
        st.member_id = #{memberId}
        AND st.pay_status = 1
        <if test="status == 0">
            AND st.`status` = 0
        </if>
        <if test="status == 1">
            AND st.`status` in (1,2,3)
        </if>
        and st.deleted = 0
        GROUP BY
        st.order_sn
        ORDER BY
        st.created_time DESC
    </select>

    <!--预约记录详情页-->
    <select id="memberLessonTicketDetail" resultMap="MemberLessonTicketDetailDtoMap">
		SELECT
			venue_id,
			lesson_id,
			member_card_id,
			(SELECT COUNT(0) FROM sv_member_lesson_ticket WHERE order_sn = #{orderSn}) AS lessonCount,
			order_sn,
			status,
			pay_type,
			pay_status
		FROM
			sv_member_lesson_ticket
		WHERE
			member_id = #{memberId}
		AND order_sn = #{orderSn}
		AND deleted = 0
		GROUP BY
			order_sn
	</select>

    <!-- api  修改用户预约课程的使用状态为已取消 -->
    <update id="updateMemberLessonTicketStatus">
        UPDATE
        <include refid="tableName"></include>
        SET
        status = #{status},
        modified_time = NOW()
        WHERE
        id
        IN
        <foreach collection="memberLessonTickets" item="memberLessonTickets" index="index" open="(" close=")"
                 separator=",">
            #{memberLessonTickets.id}
        </foreach>
        AND
        deleted = 0
    </update>

    <!-- api判断用户当天是否取消过预约 -->
    <select id="findCancelNumber" resultMap="MemberLessonTicketMap">
		SELECT
			venue_id
		FROM
			sv_member_lesson_ticket
		WHERE
			STATUS = 2
		AND Date(modified_time) = Date(NOW())
		AND pay_status = 1
		and member_id = #{memberId}
	</select>

    <!-- api 查询用户当天是否使用过免费卡  -->
    <select id="findByMemberId" resultMap="MemberLessonTicketMap">
		SELECT
			veneu_type,
			venue_id,
			lesson_id,
			member_card_id
		FROM
			sv_member_lesson_ticket
		WHERE
			member_id = #{memberId}
		AND DATE(created_time) = DATE(NOW())
	</select>

    <!-- 查询用户预约的当前课程，根据用户id,课程ID -->
    <select id="findByMemberIdAndLessonId" resultMap="MemberLessonTicketMap">
		SELECT
			id,
			veneu_type,
			venue_id,
			ticket_id,
			ticket,
			lesson_id,
			member_id,
			pay_status,
			status,
			order_sn
		FROM
			sv_member_lesson_ticket
		WHERE
			member_id = #{memberId}
		AND lesson_id = #{lessonId}
		AND pay_status = 1
		AND `status` != 2
		AND deleted = 0
	</select>

    <!-- api查询所有未支付订单 -->
    <select id="findOrders" resultMap="MemberLessonTicketMap">
		SELECT
			id,
			veneu_type,
			venue_id,
			ticket_id,
			ticket,
			lesson_id,
			member_id,
			pay_status,
			created_time
		FROM
			sv_member_lesson_ticket
		WHERE
			pay_status = 0
		AND deleted = 0
		and status = 0
		AND pay_type = 1
		and TIMEDIFF(NOW(),created_time)> '00:02:00'
	</select>

    <!--api 查询所有未支付订单，课程ID -->
    <select id="findOrdersLessonId" resultMap="MemberLessonTicketMap">
		SELECT
			id,
			veneu_type,
			venue_id,
			ticket_id,
			ticket,
			lesson_id,
			member_id,
			pay_status,
			created_time
		FROM
			sv_member_lesson_ticket
		WHERE
			pay_status = 0
		AND deleted = 0
		and status = 0
		AND pay_type = 1
		and TIMEDIFF(NOW(),created_time)> '00:02:00'
		GROUP BY lesson_id
	</select>
    <!-- 未支付课程数量 -->
    <select id="lessonNum" resultType="java.lang.Integer">
		SELECT
		COUNT(0)
		FROM
			sv_member_lesson_ticket
		WHERE
			lesson_id = #{lessonId}
		AND pay_status = 0
		AND deleted = 0
		AND `status` = 0
		AND pay_type = 1
		AND TIMEDIFF(NOW(), created_time) > '00:02:00'
	</select>

    <!-- api 判断当天内,是否使用会员卡购买过该课程 -->
    <select id="findBuyByCard" resultType="java.lang.Integer">
		SELECT
			COUNT(0)
		FROM
			sv_member_lesson_ticket
		WHERE
			member_id = #{memberId}
		AND lesson_id = #{lessonId}
		AND pay_type = 2
		AND deleted = 0
		AND `status` != 2
		AND pay_status = 1
	</select>

    <!-- api查询所有预约课程的用户，签到状态 -->
    <select id="findAllExpired" resultMap="MemberLessonTicketExpiredMap">
		SELECT
			st.id,
			st.lesson_id,
			le.date,
			le.start_time as startTime,
			le.end_time as endTime,
			le.`name`,
			st.member_id
		FROM
			sv_member_lesson_ticket AS st
		JOIN sv_venue_lesson AS le ON st.lesson_id = le.id
		WHERE
		st.`status` = 0
		AND st.pay_status = 1
		AND st.deleted = 0
		AND le.date &lt;= DATE(NOW())
	</select>

    <!-- api 修改用于已过期，但未签到的课程状态为3 -->
    <update id="updateMemberStatus">
		update
			sv_member_lesson_ticket
		set `status` = 3
		where id = #{id}
	</update>

    <!-- 判断，用户是否购买过课程价格为0的 -->
    <select id="findByZero" resultType="java.lang.Integer">
		SELECT
			COUNT(0)
		FROM
			sv_member_lesson_ticket
		WHERE
			member_id = #{memberId}
		AND lesson_id = #{lessonId}
		AND `status` != 2
	</select>

    <select id="findTicketByStatusAndTime" resultMap="MemberLessonTicketDetailDtoMap">
		SELECT * from sv_member_lesson_ticket
		WHERE deleted = 0 and member_id = #{memberId}
		and status = #{status} and <![CDATA[created_time <= #{endDate}]]> and created_time >= #{startDate}
	</select>
</mapper>