<?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.user.mapper.UserTicketOrderMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qs.mp.user.domain.UserTicketOrder">
        <id column="order_id" property="orderId" />
        <result column="user_id" property="userId" />
        <result column="title" property="title" />
        <result column="box_id" property="boxId" />
        <result column="order_amt" property="orderAmt" />
        <result column="discount_amt" property="discountAmt" />
        <result column="pay_amt" property="payAmt" />
        <result column="ticket_num" property="ticketNum" />
        <result column="memo" property="memo" />
        <result column="resource" property="resource" />
        <result column="coupon_info" property="couponInfo" />
        <result column="share_info" property="shareInfo" />
        <result column="status" property="status" />
        <result column="comm_status" property="commStatus" />
        <result column="channel_id" property="channelId" />
        <result column="created_time" property="createdTime" />
        <result column="updated_time" property="updatedTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        order_id, user_id, title, box_id, order_amt, discount_amt, pay_amt, ticket_num, memo, resource, coupon_info, share_info, status, comm_status, channel_id, created_time, updated_time
    </sql>
    
    
    <!-- 统计交易金额列表 -->
    <select id="selectIndexPayAmtList" resultType="com.qs.mp.admin.domain.vo.IndexVO">
		select 
          DATE_FORMAT(t1.created_time, '%Y%m%d') AS time,
          SUM(t1.pay_amt) AS payAmt
	     from `mp_user_ticket_order` t1
		${ew.customSqlSegment}
		GROUP BY time
	</select>
	
	 <!-- 统计交易用户数列表 -->
    <select id="selectIndexPayUserCntList" resultType="com.qs.mp.admin.domain.vo.IndexVO">
		select 
          DATE_FORMAT(t1.created_time, '%Y%m%d') AS time,
          COUNT(DISTINCT t1.user_id) AS payUserCnt
	     from `mp_user_ticket_order` t1
		${ew.customSqlSegment}
		GROUP BY time
	</select>
	
	<!-- 统计交易实时数据列表 -->
    <select id="selectIndexDailyInfoList" resultType="com.qs.mp.admin.domain.vo.IndexVO">
		select 
          DATE_FORMAT(t1.created_time, '%Y%m%d') AS time,
          SUM(t1.pay_amt) AS payAmt,
          SUM(IFNULL(t1.ticket_num,0)) AS ticketNum,
          COUNT(DISTINCT t1.user_id) AS payUserCnt
	     from `mp_user_ticket_order` t1
		${ew.customSqlSegment}
		GROUP BY time
	</select>
	
	
	 <!-- 统计票组销售TOP10 -->
    <select id="selectIndexTicketBoxTop" resultType="com.qs.mp.admin.domain.vo.IndexVO">
		select a.pay_amt as payAmt,b.title
		from (
		select 
          t1.box_id,
          SUM(t1.pay_amt) AS pay_amt
	     from `mp_user_ticket_order` t1
		${ew.customSqlSegment}
		GROUP BY t1.box_id
		ORDER BY pay_amt DESC limit 10 ) a
		left join mp_ticket_box b on a.box_id = b.box_id
		ORDER BY payAmt DESC 
	</select>
	
	<!-- 统计经销商交易额TOP10 -->
    <select id="selectIndexTicketSiteTop" resultType="com.qs.mp.admin.domain.vo.IndexVO">
		select a.pay_amt as payAmt,b.`name`
		from (
		select 
          t1.channel_id,
          SUM(t1.pay_amt) AS pay_amt
	     from `mp_user_ticket_order` t1
		${ew.customSqlSegment}
		GROUP BY t1.channel_id
		ORDER BY pay_amt DESC limit 10 ) a
		left join mp_channel b on a.channel_id = b.channel_id
		ORDER BY payAmt DESC 
	</select>
	
	 <!-- 查询子渠道全部的盲票销量 -->
    <select id="getChannelTotalTicketNumCnt" resultType="integer">
		select IFNULL(SUM(t1.ticket_num),0) as ticketNum
		from mp_user_ticket_order t1
		left join mp_channel t2 on t1.channel_id = t2.channel_id
		where (t2.channel_no like concat(#{channelNo},'.%') or t2.channel_no = #{channelNo})
		and t1.`status` = 1
	</select>
	
	<!-- 查询经销商的盲票销量 -->
    <select id="getSaleSiteTotalTicketNumCnt" resultType="integer">
		select IFNULL(SUM(t1.ticket_num),0) as ticketNum
		from mp_user_ticket_order t1
		where t1.`status` = 1 and t1.channel_id = #{channelId}
	</select>
	
	
	<!-- 查询经销商的线下、线上盲票销量 -->
    <select id="getTicketSaleCnt" resultType="com.qs.mp.admin.domain.vo.TicketCntVO">
		 select
		 SUM( CASE WHEN (t2.type = 'online') THEN IFNULL(t1.ticket_num,0) ELSE 0 END ) AS onLineSaleCnt,
		 SUM( CASE WHEN (t2.type = 'offline') THEN IFNULL(t1.ticket_num,0) ELSE 0 END ) AS offLineSaleCnt
		from mp_user_ticket_order t1
		left join mp_ticket_box t2 on t1.box_id = t2.box_id
		where t1.channel_id = #{channelId} and t1.`status` = 1
	</select>
	
	
	<!-- 查询经销商的线下、线上盲票销量 -->
    <select id="listTicketSaleCnt" resultType="com.qs.mp.admin.domain.vo.TicketCntVO">
		 select
		 t1.channel_id,
		 SUM( CASE WHEN (t2.type = 'online') THEN IFNULL(t1.ticket_num,0) ELSE 0 END ) AS onLineSaleCnt,
		 SUM( CASE WHEN (t2.type = 'offline') THEN IFNULL(t1.ticket_num,0) ELSE 0 END ) AS offLineSaleCnt
		from mp_user_ticket_order t1
		left join mp_ticket_box t2 on t1.box_id = t2.box_id
		${ew.customSqlSegment}
		GROUP BY t1.channel_id
	</select>

    <select id="listUserTicketOrder" resultType="com.qs.mp.admin.domain.vo.UserTicketOrderListVO">
		select t1.order_id,
				t1.created_time,
				t1.status,
				t1.title,
				t1.sale_price,
				t1.pay_amt,
				t1.discount_amt,
				t1.ticket_num,
				t2.nick_name,
				t2.phonenumber phone,
				t3.type boxType,
				t4.name channelName
		from mp_user_ticket_order t1
			left join sys_user t2 on t1.user_id = t2.user_id
			left join mp_ticket_box t3 on t1.box_id = t3.box_id
			left join mp_channel t4 on t1.channel_id = t4.channel_id
		${ew.customSqlSegment}
    </select>

    <select id="selectUserTicketOrderDetail" resultType="com.qs.mp.admin.domain.vo.UserTicketOrderDetailVO">
		select t1.order_id,
				t1.discount_amt,
				t1.status,
				t1.title ticketBoxTitle,
				t1.sale_comm_rate,
				t1.sale_price,
				t1.pay_amt,
				t1.created_time,
				t1.ticket_num,
				t2.nick_name,
				t2.phonenumber phone,
				t3.name channelName,
				t4.type ticketBoxType
		from mp_user_ticket_order t1
			left join sys_user t2 on t1.user_id = t2.user_id
			left join mp_channel t3 on t1.channel_id = t3.channel_id
			left join mp_ticket_box t4 on t1.box_id = t4.box_id
		${ew.customSqlSegment}
	</select>

    <select id="countUserTicketOrder" resultType="integer">
		select count(*)
		from mp_user_ticket_order t1
		left join sys_user t2 on t1.user_id = t2.user_id
		left join mp_ticket_box t3 on t1.box_id = t3.box_id
		${ew.customSqlSegment}
	</select>
</mapper>
