<?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.liquidnet.service.kylin.mapper.KylinOrderTicketsMapper">
    <resultMap id="orderTicketsListDaoResult" type="com.liquidnet.service.kylin.dao.OrderTicketsListDao">
        <result column="order_tickets_id" property="orderTicketsId"/>
        <result column="sponsor" property="sponsor"/>
        <result column="order_code" property="orderCode"/>
        <result column="title" property="title"/>
        <result column="status" property="status"/>
        <result column="get_ticket_type" property="getTicketType"/>
        <result column="pay_type" property="payType"/>
        <result column="payment_type" property="paymentType"/>
        <result column="tied" property="tied"/>
        <result column="price" property="price"/>
        <result column="number" property="number"/>
        <result column="price_member" property="priceMember"/>
        <result column="price_total" property="priceTotal"/>
        <result column="price_voucher" property="priceVoucher"/>
        <result column="price_actual" property="priceActual"/>
        <result column="price_express" property="priceExpress"/>
        <result column="price_refund" property="priceRefund"/>
        <result column="refund_number" property="refundNumber"/>
        <result column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_mobile" property="userMobile"/>
        <result column="order_type" property="orderType"/>
        <result column="created_at" property="createdAt"/>
    </resultMap>

    <resultMap id="orderPushDamaiDto" type="com.liquidnet.service.kylin.dao.OrderPushDamaiDto">
        <result column="order_tickets_id" property="orderTicketsId"/>
        <result column="mid" property="mid"/>
        <result column="status" property="status"/>
        <result column="is_payment" property="isPayment"/>
        <result column="enter_name" property="enterName"/>
        <result column="enter_id_code" property="enterIdCode"/>
        <result column="enter_mobile" property="enterMobile"/>
        <result column="enter_type" property="enterType"/>
        <result column="time_pay" property="timePay"/>
        <result column="time_id" property="timeId"/>
        <result column="ticket_id" property="ticketId"/>
        <result column="is_member" property="isMember"/>
        <result column="order_ticket_entities_id" property="orderTicketEntitiesId"/>
        <result column="get_ticket_type" property="getTicketType"/>
        <result column="transfer_status" property="transferStatus"/>
    </resultMap>

    <resultMap id="orderScriptDtoResult" type="com.liquidnet.service.kylin.dao.OrderScriptDto">
        <result column="mid" property="mid"/>
        <result column="order_tickets_id" property="orderTicketsId"/>
        <result column="order_code" property="orderCode"/>
        <result column="status" property="status"/>
        <result column="ticket_id" property="ticketId"/>
        <result column="number" property="number"/>
        <result column="user_id" property="userId"/>
        <result column="coupon_code" property="couponCode"/>
        <result column="coupon_type" property="couponType"/>
    </resultMap>

    <select id="getPerformanceRefundOrderStatis" resultType="java.util.HashMap">
        SELECT
        SUM(o.price_actual - o.price_refund) as total_price_refund, COUNT(*) as
        total_refund_number
        FROM kylin_order_tickets AS o
        JOIN kylin_order_ticket_relations AS r ON r.order_id = o.order_tickets_id
        JOIN kylin_order_ticket_status AS s ON s.order_id = o.order_tickets_id
        <where>
            r.performance_id=#{targetId}
            AND s.status IN (1, 3, 6)
            AND s.transfer_status IN (0, 5)
            AND s.pay_status=1
            AND o.coupon_type='no'
            AND o.payment_type IN
            <foreach collection="paymentType" item="payment_type" index="index" open="(" close=")" separator=",">
                '${payment_type}'
            </foreach>
        </where>
        LIMIT 1
    </select>
    <select id="getUserPerformance" parameterType="String" resultType="java.util.Map">
        SELECT c.title as 'title' ,c.performanceId as 'performanceId', SUM(commiss) as 'commiss',c.timeEnd as 'timeEnd' FROM(
        select o.performance_title as 'title',r.performance_id as 'performanceId' ,SUM(r.agent_distributions * (o.price_actual-o.price_refund)) as 'commiss',kp.time_end as 'timeEnd'
        from (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
        LEFT JOIN kylin_order_ticket_status as s on o.order_tickets_id=s.order_id
        LEFT JOIN kylin_order_ticket_relations 	as r ON o.order_tickets_id=r.order_id
        LEFT JOIN kylin_performances as kp ON  r.performance_id= kp.performances_id
        where r.agent_id=#{agentId} and s.pay_status='1' AND (r.agent_distributions_master !=0 or r.agent_distributions!=0)  GROUP BY r.performance_id
        UNION ALL
        select o.performance_title as 'title',r.performance_id as 'performanceId' ,SUM(r.agent_distributions_master * (o.price_actual-o.price_refund)) as 'commiss',kp.time_end as 'timeEnd'
        from (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
        LEFT JOIN kylin_order_ticket_status as s on o.order_tickets_id=s.order_id
        LEFT JOIN kylin_order_ticket_relations 	as r ON o.order_tickets_id=r.order_id
        LEFT JOIN kylin_performances as kp ON  r.performance_id= kp.performances_id
        where r.agent_id_master=#{agentId} and s.pay_status='1' AND (r.agent_distributions_master !=0 or r.agent_distributions!=0)  GROUP BY r.performance_id
        ) as c GROUP BY  c.performanceId
    </select>

    <select id="getUserPerformanceByUidAndProId" parameterType="String" resultType="java.util.Map">
        SELECT c.title as 'title' ,c.performanceId as 'performanceId', SUM(commiss) as 'commiss',c.timeEnd as 'timeEnd' FROM(
        select o.performance_title as 'title',r.performance_id as 'performanceId' ,SUM(r.agent_distributions * (o.price_actual-o.price_refund)) as 'commiss',kp.time_end as 'timeEnd'
        from (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
                 LEFT JOIN kylin_order_ticket_status as s on o.order_tickets_id=s.order_id
                 LEFT JOIN kylin_order_ticket_relations 	as r ON o.order_tickets_id=r.order_id
                 LEFT JOIN kylin_performances as kp ON  r.performance_id= kp.performances_id
        where r.agent_id=#{agentId} and s.pay_status='1'  GROUP BY r.performance_id
        UNION ALL
        select o.performance_title as 'title',r.performance_id as 'performanceId' ,SUM(r.agent_distributions_master * (o.price_actual-o.price_refund)) as 'commiss',kp.time_end as 'timeEnd'
        from (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
                 LEFT JOIN kylin_order_ticket_status as s on o.order_tickets_id=s.order_id
                 LEFT JOIN kylin_order_ticket_relations 	as r ON o.order_tickets_id=r.order_id
                 LEFT JOIN kylin_performances as kp ON  r.performance_id= kp.performances_id
        where r.agent_id_master=#{agentId} and s.pay_status='1'  GROUP BY r.performance_id
        ) as c  WHERE c.performanceId = #{performanceId}
    </select>


    <select id="getUserOrder" parameterType="String" resultType="java.util.Map">
            select p.title as 'title',p.performances_id as 'yanchuId' from kylin_performances p
            LEFT JOIN kylin_performance_status s ON p.performances_id=s.performance_id where
            time_end>DATE_SUB(CURDATE(), INTERVAL 6 MONTH) and s.`status` in (3,6,7,8,9,10);
    </select>
    <select id="getUserCommission" parameterType="String" resultType="java.util.Map">
        select SUM(r.agent_distributions * (o.price_actual-o.price_refund)) as 'commiss'
        from  (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
        LEFT JOIN kylin_order_ticket_status as s on  o.order_tickets_id=s.order_id
        LEFT JOIN kylin_order_ticket_relations as r ON o.order_tickets_id=r.order_id
        where  r.agent_id=#{agentId}    and s.pay_status='1' AND (r.agent_distributions_master !=0 or r.agent_distributions!=0)
    </select>
    <select id="getUserSellOneDate" parameterType="String" resultType="java.util.Map">
      select t.title as 'title',ticket_id as 'ticketId',(SUM(number)-SUM(refund_number)) as 'number',(SUM(o.price_actual)-SUM(o.price_refund)) as 'price',SUM(r.agent_distributions * (o.price_actual-o.price_refund)) as 'commiss',r.agent_distributions as 'paioTi'
	from  (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
	LEFT JOIN kylin_order_ticket_status as s on  o.order_tickets_id=s.order_id
	LEFT JOIN kylin_order_ticket_relations as r ON o.order_tickets_id=r.order_id
	LEFT JOIN kylin_tickets  as t  ON  r.ticket_id=t.tickets_id
	where  r.agent_id=#{agentId} and r.performance_id=#{performanceId}  AND (r.agent_distributions_master !=0 or r.agent_distributions!=0) and s.pay_status='1' GROUP BY r.ticket_id

    </select>
    <select id="getUserSellAllDate" parameterType="String" resultType="java.util.Map">
    select sum(c.number) as 'number',SUM(c.price) as 'price',SUM(c.commiss) as 'commiss' from (
    select t.title as 'title',ticket_id as 'ticketId',(SUM(number)-SUM(refund_number)) as 'number',(SUM(o.price_actual)-SUM(o.price_refund)) as 'price',SUM(r.agent_distributions * (o.price_actual-o.price_refund)) as 'commiss',r.agent_distributions as 'paioTi'
	from  (select * from kylin_order_tickets where created_at>DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) as o
	LEFT JOIN kylin_order_ticket_status as s on  o.order_tickets_id=s.order_id
	LEFT JOIN kylin_order_ticket_relations as r ON o.order_tickets_id=r.order_id
	LEFT JOIN kylin_tickets  as t  ON  r.ticket_id=t.tickets_id
	where  r.agent_id=#{agentId} and r.performance_id=#{performanceId} AND (r.agent_distributions_master !=0 or r.agent_distributions!=0)   and s.pay_status='1' GROUP BY r.ticket_id
    ) as c

    </select>
    <select id="getUserSellDetail" parameterType="String" resultType="java.util.Map">
    select o.user_mobile as 'mobile',o.order_code as 'orderId',(o.number-refund_number) as 'number',o.created_at as 'createTime' from kylin_order_tickets as o
		LEFT JOIN kylin_order_ticket_status as s ON o.order_tickets_id=s.order_id
		LEFT JOIN kylin_order_ticket_relations as r on o.order_tickets_id=r.order_id
    where  r.agent_id=#{agentId} and s.pay_status='1' AND (r.agent_distributions_master !=0 or r.agent_distributions!=0) and r.ticket_id=#{ticketId} and (o.number-refund_number)>0
    </select>

    <select id="getCanRefundOrderList" parameterType="java.lang.String"
            resultType="com.liquidnet.service.kylin.entity.KylinOrderTickets">
        SELECT o.mid, order_tickets_id, number, price_actual, performance_title
        FROM kylin_order_tickets AS o
        JOIN kylin_order_ticket_relations AS r ON r.order_id = o.order_tickets_id
        JOIN kylin_order_ticket_status AS s ON s.order_id = o.order_tickets_id
        <where>
            r.performance_id=${targetId}
            AND s.status IN (1, 3, 6)
            AND s.transfer_status IN (0, 5)
            AND s.pay_status=1
            AND o.coupon_type='no'
            AND o.mid>${mid}
        </where>
        LIMIT ${limitNum}
    </select>
    <select id="orderList" resultMap="orderTicketsListDaoResult">
        SELECT
        kot.order_tickets_id,
        kp.sponsor,
        kot.order_code,
        kp.title,
        kots.status,
        kot.get_ticket_type,
        kot.pay_type,
        kot.payment_type,
        '无' as tied,
        kot.price,
        kot.number,
        kot.price_member,
        kot.price_total,
        kot.price_voucher,
        kot.price_actual,
        kot.price_express,
        kot.price_refund,
        kot.refund_number,
        kot.user_id,
        kot.user_name,
        kot.user_mobile,
        kot.order_type,
        date_format(kot.created_at, '%Y-%m-%d %H:%i:%s') created_at
        FROM kylin_order_tickets kot
        inner join kylin_order_ticket_status kots on kot.order_tickets_id = kots.order_id
        inner join kylin_order_ticket_relations kotr on kot.order_tickets_id = kotr.order_id
        inner join kylin_performances kp on kotr.performance_id = kp.performances_id
        <where>
            <if test="userId != null  and userId != ''">and kot.user_id = #{userId}</if>
            <if test="userName != null  and userName != ''">and kot.user_name like concat('%', #{userName}, '%')</if>
            <if test="userMobile != null  and userMobile != ''">and kot.user_mobile = #{userMobile}</if>
            <if test="performanceTitle != null  and performanceTitle != ''">
                and kot.performance_title like concat('%', #{performanceTitle}, '%')
            </if>
            <if test="orderCode != null  and orderCode != ''">and kot.order_code = #{orderCode}</if>
            <if test="orderType != null  and orderType != ''">and kot.order_type = #{orderType}</if>
            <if test="orderVersion != null  and orderVersion != ''">and kot.order_version = #{orderVersion}</if>
            <if test="number != null ">and kot.number = #{number}</if>
            <if test="price != null ">and kot.price = #{price}</if>
            <if test="priceMember != null ">and kot.price_member = #{priceMember}</if>
            <if test="priceTotal != null ">and kot.price_total = #{priceTotal}</if>
            <if test="priceVoucher != null ">and kot.price_voucher = #{priceVoucher}</if>
            <if test="priceActual != null ">and kot.price_actual = #{priceActual}</if>
            <if test="priceExpress != null ">and kot.price_express = #{priceExpress}</if>
            <if test="priceRefund != null ">and kot.price_refund = #{priceRefund}</if>
            <if test="refundNumber != null ">and kot.refund_number = #{refundNumber}</if>
            <if test="payType != null  and payType != ''">and kot.pay_type = #{payType}</if>
            <if test="paymentType != null  and paymentType != ''">and kot.payment_type = #{paymentType}</if>
            <if test="timePay != null  and timePay != ''">and kot.time_pay = #{timePay}</if>
            <if test="expressContacts != null  and expressContacts != ''">and kot.express_contacts =
                #{expressContacts}
            </if>
            <if test="expressAddress != null  and expressAddress != ''">and kot.express_address = #{expressAddress}</if>
            <if test="expressPhone != null  and expressPhone != ''">and kot.express_phone = #{expressPhone}</if>
            <if test="couponType != null  and couponType != ''">and kot.coupon_type = #{couponType}</if>
            <if test="getTicketType != null  and getTicketType != ''">and kot.get_ticket_type = #{getTicketType}</if>
            <if test="getTicketDescribe != null  and getTicketDescribe != ''">and kot.get_ticket_describe =
                #{getTicketDescribe}
            </if>
            <if test="payCountdownMinute != null ">and kot.pay_countdown_minute = #{payCountdownMinute}</if>
            <if test="comment != null  and comment != ''">and kot.comment = #{comment}</if>
            <if test="createdAt != null ">and kot.created_at = #{createdAt}</if>
            <if test="updatedAt != null ">and kot.updated_at = #{updatedAt}</if>
            <if test="orderSource != null  and orderSource != ''">and kot.order_source = #{orderSource}</if>
            <if test="orderType != null  and orderType != ''">and kot.order_type = #{orderType}</if>
            <if test="sponsor!=null and sponsor!=''">
                AND kp.sponsor = #{sponsor}
            </if>
            <if test="orderStatus!=null and orderStatus!=''">
                AND kots.status = #{orderStatus}
            </if>
        </where>
        order by kot.created_at desc
        <!--        <if test="orderItem!=''">-->
        <!--            ORDER BY ${orderItem} ${orderSc}-->
        <!--        </if>-->
    </select>

    <select id="orderList_COUNT" resultType="Long">
        <choose>
            <when test="(sponsor!=null and sponsor!='') or (orderStatus!=null and orderStatus!='')">
                SELECT count(1)
                FROM kylin_order_tickets kot
                inner join kylin_order_ticket_status kots on kot.order_tickets_id = kots.order_id
                inner join kylin_order_ticket_relations kotr on kot.order_tickets_id = kotr.order_id
                inner join kylin_performances kp on kotr.performance_id = kp.performances_id
            </when>
            <otherwise>
                SELECT
                count(1)
                FROM kylin_order_tickets kot
            </otherwise>
        </choose>
        <where>
            <if test="userId != null  and userId != ''">and kot.user_id = #{userId}</if>
            <if test="userName != null  and userName != ''">and kot.user_name like concat('%', #{userName}, '%')</if>
            <if test="userMobile != null  and userMobile != ''">and kot.user_mobile = #{userMobile}</if>
            <if test="performanceTitle != null  and performanceTitle != ''">
                and kot.performance_title like concat('%', #{performanceTitle}, '%')
            </if>
            <if test="orderCode != null  and orderCode != ''">and kot.order_code = #{orderCode}</if>
            <if test="orderType != null  and orderType != ''">and kot.order_type = #{orderType}</if>
            <if test="orderVersion != null  and orderVersion != ''">and kot.order_version = #{orderVersion}</if>
            <if test="number != null ">and kot.number = #{number}</if>
            <if test="price != null ">and kot.price = #{price}</if>
            <if test="priceMember != null ">and kot.price_member = #{priceMember}</if>
            <if test="priceTotal != null ">and kot.price_total = #{priceTotal}</if>
            <if test="priceVoucher != null ">and kot.price_voucher = #{priceVoucher}</if>
            <if test="priceActual != null ">and kot.price_actual = #{priceActual}</if>
            <if test="priceExpress != null ">and kot.price_express = #{priceExpress}</if>
            <if test="priceRefund != null ">and kot.price_refund = #{priceRefund}</if>
            <if test="refundNumber != null ">and kot.refund_number = #{refundNumber}</if>
            <if test="payType != null  and payType != ''">and kot.pay_type = #{payType}</if>
            <if test="paymentType != null  and paymentType != ''">and kot.payment_type = #{paymentType}</if>
            <if test="timePay != null  and timePay != ''">and kot.time_pay = #{timePay}</if>
            <if test="expressContacts != null  and expressContacts != ''">and kot.express_contacts =
                #{expressContacts}
            </if>
            <if test="expressAddress != null  and expressAddress != ''">and kot.express_address = #{expressAddress}</if>
            <if test="expressPhone != null  and expressPhone != ''">and kot.express_phone = #{expressPhone}</if>
            <if test="couponType != null  and couponType != ''">and kot.coupon_type = #{couponType}</if>
            <if test="getTicketType != null  and getTicketType != ''">and kot.get_ticket_type = #{getTicketType}</if>
            <if test="getTicketDescribe != null  and getTicketDescribe != ''">and kot.get_ticket_describe =
                #{getTicketDescribe}
            </if>
            <if test="payCountdownMinute != null ">and kot.pay_countdown_minute = #{payCountdownMinute}</if>
            <if test="comment != null  and comment != ''">and kot.comment = #{comment}</if>
            <if test="createdAt != null ">and kot.created_at = #{createdAt}</if>
            <if test="updatedAt != null ">and kot.updated_at = #{updatedAt}</if>
            <if test="orderSource != null  and orderSource != ''">and kot.order_source = #{orderSource}</if>
            <if test="orderType != null  and orderType != ''">and kot.order_type = #{orderType}</if>
            <if test="sponsor!=null and sponsor!=''">
                AND kp.sponsor = #{sponsor}
            </if>
            <if test="orderStatus!=null and orderStatus!=''">
                AND kots.status = #{orderStatus}
            </if>
        </where>
    </select>

    <select id="pushDamaiDetailsList" parameterType="java.lang.String" resultMap="orderPushDamaiDto">
        SELECT ote.mid,
               order_tickets_id,
               enter_name,
               enter_id_code,
               ote.status,
               ote.is_payment,
               enter_type,
               enter_mobile,
               time_pay,
               otr.time_id,
               otr.ticket_id,
               otr.is_member,
               ote.order_ticket_entities_id,
               get_ticket_type,
               ots.transfer_status
        FROM kylin_order_tickets AS ot
                 LEFT JOIN kylin_order_ticket_status AS ots ON ots.order_id = ot.order_tickets_id
                 LEFT JOIN kylin_order_ticket_relations AS otr ON otr.order_id = ot.order_tickets_id
                 LEFT JOIN kylin_order_ticket_entities AS ote ON ote.order_id = ot.order_tickets_id
                 LEFT JOIN kylin_ticket_status AS kys ON kys.ticket_id = otr.ticket_id
        WHERE (ots.status = 1 or ots.status = 3 or ots.status = 4 or ots.status = 6)
          AND ote.ticket_id = ${ticketsId} group by order_ticket_entities_id
    </select>

    <select id="orderScriptDto" parameterType="java.lang.String" resultMap="orderScriptDtoResult">
        select s.mid,
        order_tickets_id,
        order_code,
        status,
        ticket_id,
        number,
        user_id,
        oc.coupon_code,
        oc.coupon_type
        from (SELECT ot.mid,
        order_tickets_id,
        order_code,
        status,
        ticket_id,
        number,
        user_id
        FROM kylin_order_tickets AS ot
        inner JOIN kylin_order_ticket_status AS ots ON ots.order_id = ot.order_tickets_id
        inner JOIN kylin_order_ticket_relations AS otr ON otr.order_id = ot.order_tickets_id
        WHERE status = 0 AND ot.coupon_type !='exchange' AND NOW() > DATE_SUB(ot.created_at, INTERVAL
        -(pay_countdown_minute + 1) MINUTE)) as s
        left JOIN kylin_order_coupons AS oc ON oc.order_id = s.order_tickets_id
        <if test="userId!=''">
            AND user_id = #{userId}
        </if>

    </select>
    <select id="performanceExpressPerformanceOrderList"
            resultType="com.liquidnet.service.kylin.dao.PerformanceExpressPerformanceOrderListAdminDao">
        SELECT
        kot.order_tickets_id,
        kot.order_code,
        kot.price_express,
        kot.number,

        kot.user_id,
        kot.user_name,
        kot.user_mobile,
        kot.express_contacts,
        kot.express_address,
        kot.express_phone,

        kots.status,
        kots.express_type,
        kots.produce_code as product_code,

        oe.order_express_id,
        oe.mailno,
        oe.freight_price,
        oe.express_status,

        kem.title as product_title

        FROM kylin_order_tickets kot
        INNER JOIN kylin_order_ticket_status kots ON kot.order_tickets_id = kots.order_id
        INNER JOIN kylin_order_ticket_relations kotr ON kot.order_tickets_id = kotr.order_id
        LEFT JOIN (SELECT * FROM kylin_order_express WHERE express_status NOT IN (1, 3) AND send_type = 1) AS oe ON
        oe.order_tickets_id = kot.order_tickets_id
        LEFT JOIN kylin_express_module kem ON kem.product_code = kots.produce_code
        -- LEFT JOIN (SELECT * FROM kylin_order_express_route ORDER BY mid DESC LIMIT 1) AS oer ON oe.order_express_id =
        -- oer.order_express_id
        <where>
            kotr.performance_id = #{performancesId}
            AND kot.get_ticket_type = 'express'
            AND kots.status IN (1, 3, 4, 6)
            AND kots.transfer_status IN (0, 1, 3, 5)
            AND kots.pay_status = 1
            <if test="orderCode != null  and orderCode != ''">AND kot.order_code = #{orderCode}</if>
            <if test="orderTicketsId != null  and orderTicketsId != ''">AND kot.order_tickets_id = #{orderTicketsId}
            </if>
            <if test="orderStatus!=null and orderStatus!=''">AND kots.status = #{orderStatus}</if>

            <if test="userMobile != null  and userMobile != ''">AND kot.user_mobile = #{userMobile}</if>
            <if test="userName != null  and userName != ''">AND kot.user_name LIKE concat('%', #{userName}, '%')</if>

            <if test="expressPhone != null  and expressPhone != ''">AND kot.express_phone = #{expressPhone}</if>
            <if test="expressContacts != null  and expressContacts != ''">AND kot.express_contacts LIKE concat('%',
                #{expressContacts}, '%')
            </if>
            <if test="expressStatus != '' and expressStatus != 1 and expressStatus != 70 and expressStatus != 80">AND oe.express_status =
                ${expressStatus}
            </if>
            <if test="expressStatus == 1">AND (oe.express_status IS NULL OR oe.express_status = ${expressStatus})</if>
            <if test="expressStatus == 70">AND (oe.express_status > 3 AND oe.express_status != 80 AND oe.express_status
                != 8000)
            </if>
            <if test="expressStatus == 80">AND (oe.express_status = 80 OR oe.express_status = 8000)</if>
            <if test="mailno != ''">AND oe.mailno LIKE concat('%', #{mailno}, '%')</if>
            <if test="ticketsId != '' and ticketsId != null">AND kotr.ticket_id = ${ticketsId}</if>
            <if test="expressType != '' and expressType != null">AND kots.express_type = ${expressType}</if>
            <if test="productCode != '' and productCode != null">AND kots.produce_code = ${productCode}</if>
        </where>
        ORDER BY kot.mid DESC
    </select>
    <select id="getCanPlaceOrderList" resultType="com.liquidnet.service.kylin.entity.KylinOrderTickets">
        SELECT
        kot.order_tickets_id,
        kot.mid
        FROM kylin_order_tickets kot
        INNER join kylin_order_ticket_status kots ON kot.order_tickets_id = kots.order_id
        INNER join kylin_order_ticket_relations kotr ON kot.order_tickets_id = kotr.order_id
        <where>
            kotr.performance_id = #{performanceId}
            AND kot.get_ticket_type = 'express'
            AND kots.status IN (1, 6)
            AND kots.pay_status = 1
            AND kot.mid > ${mid}
        </where>
        ORDER BY kot.mid ASC
        LIMIT ${limitNum}
    </select>

    <select id="getOrderUserIdByTimesId" resultType="com.liquidnet.service.kylin.dao.OrderIdsDao">
        select order_tickets_id,user_id from kylin_order_tickets as kot left join kylin_order_ticket_relations as kotr on kotr.order_id = kot.order_tickets_id
        where ticket_id =#{ticketId}
    </select>
    <select id="selectExpressList" resultType="com.liquidnet.service.kylin.dao.OrderExpressExportListDao">
        SELECT
            d.title,
            d.use_start,
            c.order_code,
            c.number,
            c.user_name,
            c.user_mobile,
            c.express_contacts,
            c.express_phone,
            c.express_address,
            f.enter_name,
            f.enter_mobile,
            f.enter_id_code
        FROM kylin_performances AS p
                 JOIN kylin_order_ticket_relations AS b ON b.performance_id = p.performances_id
                 JOIN kylin_order_tickets AS c ON c.order_tickets_id = b.order_id
                 JOIN kylin_order_ticket_status AS e ON e.order_id = b.order_id
                 JOIN kylin_order_ticket_entities AS f ON f.order_id = b.order_id
                 JOIN kylin_tickets AS d ON d.tickets_id = b.ticket_id
        WHERE c.get_ticket_type = 'express'
          AND e.status = 1
          AND e.transfer_status in (0, 3)
          AND p.performances_id = #{performanceId}
        ORDER BY c.mid DESC;
    </select>


    <select id="getOrderScore" resultType="Integer">
       select IFNULL(sum(price_actual),0) from kylin_order_tickets kot
            inner join kylin_order_ticket_relations as kotr on kotr.order_id = kot.order_tickets_id
            inner join kylin_order_ticket_status as kots on kots.order_id = kot.order_tickets_id
            inner join kylin_order_ticket_entities as kote on kote.order_id = kot.order_tickets_id
            where kots.status in (1) and kot.user_id =  #{uid} ;
    </select>

    <select id="selectListByPerformanceId" resultType="com.liquidnet.service.kylin.dao.KylinOrderTicketAndAgentVo">
        SELECT
            kot.order_tickets_id,
            kot.user_id,
            kot.performance_title,
            kot.number,
            kot.refund_number,
            kot.price_actual,
            kot.price_refund,
            kk.agent_id,
            kk.agent_distributions,
            kk.agent_id_master,
            kk.agent_distributions_master,
            kk.ticket_id
        FROM
            kylin_order_tickets AS kot
                LEFT JOIN (
                SELECT
                    kotr.order_id,
                    kotr.agent_id,
                    kotr.performance_id,
                    kotr.agent_distributions,
                    kotr.agent_id_master,
                    kotr.agent_distributions_master,
                    kotr.ticket_id
                FROM
                    kylin_order_ticket_relations AS kotr
                        LEFT JOIN kylin_order_ticket_status AS kots ON kotr.order_id = kots.order_id
            ) AS kk ON kot.order_tickets_id = kk.order_id
        <where>
            kk.agent_id != 0
            <if test="performancesId!=''">
                AND kk.performance_id = #{performancesId}
            </if>
            <if test="ticketId!=null">
                AND kk.ticket_id = #{ticketId}
            </if>
        </where>

    </select>

    <select id="getAgentIdListByPerId" resultType="String">
        SELECT agent_id
        FROM kylin_order_ticket_relations
        WHERE performance_id = #{performancesId}
          AND agent_id != 0
        GROUP BY
            agent_id;
    </select>

    <select id="selectNumAndPrice" resultType="java.util.Map">
        SELECT
            SUM(kot.number-kot.refund_number) agentSaleNum,
            SUM((kot.number-kot.refund_number) * kt.price) totalPrice
        FROM
            kylin_order_tickets AS kot
                LEFT JOIN (
                SELECT
                    kotr.order_id,
                    kotr.agent_id,
                    kotr.performance_id,
                    kotr.agent_distributions,
                    kotr.agent_id_master,
                    kotr.agent_distributions_master,
                    kotr.ticket_id
                FROM
                    kylin_order_ticket_relations AS kotr
                        LEFT JOIN kylin_order_ticket_status AS kots ON kotr.order_id = kots.order_id
                WHERE  kots.STATUS IN ( 1, 6 ,3 )
            ) AS kk ON kot.order_tickets_id = kk.order_id
                LEFT JOIN kylin_tickets AS kt ON kt.tickets_id = kk.ticket_id
        WHERE
            kk.agent_id != 0
             AND (kk.agent_distributions_master !=0 or kk.agent_distributions!=0)
	AND kk.performance_id = #{performancesId}
	AND kk.ticket_id = #{ticketsId}
    </select>


    <select id="getPayOrderIds" resultType="String">
        SELECT order_tickets_id
FROM kylin_order_tickets kot
         INNER JOIN kylin_order_ticket_relations as kotr on kotr.order_id = kot.order_tickets_id
         INNER JOIN kylin_order_ticket_status as kots on kots.order_id = kot.order_tickets_id
WHERE kotr.performance_id = #{performanceId}
  AND kots.status IN (1, 3, 6)
  AND kots.transfer_status in (0, 1, 3, 5);
    </select>
</mapper>
