<?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="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,

        oe.order_express_id,
        oe.mailno,
        oe.freight_price,
        oe.express_status
        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 (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 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>

        </where>
    </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 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};
    </select>


    <select id="getOrderScore" resultType="Integer">
       select count(kot.user_id),kot.user_id,DATE_FORMAT(kot.created_at,'%Y-%m-%d') 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} group by DATE_FORMAT(kot.created_at,'%Y-%m-%d');
    </select>
</mapper>
