<?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.qs.mp.admin.mapper.TicketMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qs.mp.admin.domain.Ticket">
        <id column="ticket_id" property="ticketId" />
        <result column="box_id" property="boxId" />
        <result column="pkg_id" property="pkgId" />
        <result column="title" property="title" />
        <result column="serial_no" property="serialNo" />
        <result column="face_price" property="facePrice" />
        <result column="cipher_lucky_num" property="cipherLuckyNum" />
        <result column="plain_lucky_num" property="plainLuckyNum" />
        <result column="draw_num" property="drawNum" />
        <result column="is_physical" property="isPhysical" />
        <result column="status" property="status" />
        <result column="created_time" property="createdTime" />
        <result column="updated_time" property="updatedTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ticket_id, box_id, pkg_id, title, serial_no, face_price, cipher_lucky_num, plain_lucky_num, draw_num, is_physical, status, created_time, updated_time
    </sql>

    <select id="getRandOne" resultMap="BaseResultMap">
        select t1.*
        from mp_ticket  as t1
            JOIN  (SELECT ROUND(RAND() * (SELECT MAX(ticket_id) FROM mp_ticket where box_id = #{boxId} AND status = #{status} )) AS id) AS t2
        WHERE t1.ticket_id >= t2.id and t1.box_id = #{boxId} AND t1.status = #{status}
        ORDER BY t1.ticket_id LIMIT 1;
    </select>

    <select id="listTicket" resultMap="BaseResultMap">
        select t1.serial_no, t1.cipher_lucky_num, t1.draw_num
        from mp_ticket t1
        left join (
            select ticket_id
            from mp_ticket
                     ${ew.customSqlSegment}
        ) t2
        on t1.ticket_id = t2.ticket_id

    </select>
    
    <select id="getTicketQtyCnt" resultType="integer">
       select
		 IFNULL(SUM( CASE WHEN (t2.`status` = 1 and t3.type = 'offline') THEN 1 ELSE 0 END ),0)  AS offLineQtyCnt
		from mp_channel_order_detail t1
		left join mp_ticket t2 on t1.pkg_id = t2.pkg_id
		left join mp_ticket_box t3 on t2.box_id = t3.box_id
		where t1.channel_id = #{channelId};
    </select>
    
     <select id="listTicketQtyCnt" resultType="com.qs.mp.admin.domain.vo.TicketCntVO">
       select
         t1.channel_id channelId,
		SUM( CASE WHEN (t2.`status` = 1 and t3.type = 'offline') THEN 1 ELSE 0 END ) AS offLineQtyCnt
		from mp_channel_order_detail t1
		left join mp_ticket t2 on t1.pkg_id = t2.pkg_id
		left join mp_ticket_box t3 on t2.box_id = t3.box_id
		${ew.customSqlSegment}
		GROUP BY t1.channel_id
    </select>
</mapper>
