<?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.KylinPerformancesMapper">

    <resultMap id="partnerPerformanceListResult" type="com.liquidnet.service.kylin.dao.PerformancePartnerListDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="time_start" property="timeStart"/>
        <result column="time_end" property="timeEnd"/>
        <result column="total_general" property="totalGeneral"/>
        <result column="sale_general" property="saleGeneral"/>
        <result column="total_sale_price" property="totalSalePrice"/>
        <result column="surplus_general" property="surplusGeneral"/>
        <result column="time_sell" property="timeSell"/>
        <result column="time_stop" property="timeStop"/>
        <result column="status" property="status"/>
        <result column="type" property="type"/>
        <result column="sort" property="sort"/>
        <result column="audit_status" property="auditStatus"/>
        <result column="reject_txt" property="rejectTxt"/>
        <result column="created_at" property="createdAt"/>
    </resultMap>

    <resultMap id="misPerformanceListResult" type="com.liquidnet.service.kylin.dao.PerformanceAdminListDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="time_start" property="timeStart"/>
        <result column="time_end" property="timeEnd"/>
        <result column="total_general" property="totalGeneral"/>
        <result column="sale_general" property="saleGeneral"/>
        <result column="total_sale_price" property="totalSalePrice"/>
        <result column="surplus_general" property="surplusGeneral"/>
        <result column="status" property="status"/>
        <result column="time_sell" property="timeSell"/>
        <result column="time_stop" property="timeStop"/>
        <result column="audit_status" property="auditStatus"/>
        <result column="reject_txt" property="rejectTxt"/>
        <result column="sort" property="sort"/>
        <result column="audit_time" property="auditTime"/>
        <result column="field_audit_status" property="fieldAuditStatus"/>
        <result column="sponsor" property="sponsor"/>
        <result column="sync_damai" property="syncDamai"/>
        <result column="created_at" property="createdAt"/>
    </resultMap>

    <resultMap id="performanceTitleDaoResult" type="com.liquidnet.service.kylin.dao.PerformanceTitleDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="img_poster" property="imgPoster"/>
    </resultMap>

    <resultMap id="OrderExportDaoResult" type="com.liquidnet.service.kylin.dao.OrderExportDao">
        <result column="performance_title" property="performanceTitle"/>
        <result column="user_mobile" property="userMobile"/>
        <result column="user_name" property="userName"/>

        <result column="title" property="title"/>
        <result column="use_time" property="useTime"/>
        <result column="use_start" property="useStart"/>
        <result column="use_end" property="useEnd"/>

        <result column="order_code" property="orderCode"/>
        <result column="code" property="code"/>
        <result column="pay_code" property="payCode"/>
        <result column="payment_id" property="paymentId"/>
        <result column="status" property="status"/>
        <result column="payment_type" property="paymentType"/>
        <result column="type" property="type"/>
        <result column="title1" property="title1"/>
        <result column="title2" property="title2"/>
        <result column="price" property="price"/>
        <result column="price_express" property="priceExpress"/>
        <result column="number" property="number"/>
        <result column="refund_number" property="refundNumber"/>
        <result column="price_actual" property="priceActual"/>
        <result column="price_refund" property="priceRefund"/>
        <result column="refund_at" property="refundAt"/>
        <result column="reject" property="reject"/>
        <result column="time_pay" property="timePay"/>
    </resultMap>

    <resultMap id="memberOrderExportDaoResult" type="com.liquidnet.service.kylin.dao.MemberOrderExportDao">
        <result column="pay_no" property="payNo"/>
        <result column="payment_id" property="paymentId"/>
        <result column="pay_type" property="payType"/>
        <result column="state" property="state"/>
        <result column="price" property="price"/>
        <result column="payment_at" property="paymentAt"/>
        <result column="created_at" property="createdAt"/>
        <result column="updated_at" property="updatedAt"/>
        <result column="refund_at" property="refundAt"/>
        <result column="refund_price" property="refundPrice"/>
    </resultMap>

    <resultMap id="commodityOrderExportDaoResult" type="com.liquidnet.service.kylin.dao.CommodityOrderExportDao">
        <result column="code" property="code"/>
        <result column="payment_id" property="paymentId"/>
        <result column="name" property="name"/>
        <result column="payment_type" property="paymentType"/>
        <result column="price_actual" property="priceActual"/>
        <result column="created_at" property="createdAt"/>
        <result column="price_express" property="priceExpress"/>
        <result column="price_refund" property="priceRefund"/>
        <result column="status" property="status"/>
        <result column="refund_at" property="refundAt"/>
    </resultMap>

    <!--    <resultMap id="OrderExportDaoResult" type="com.liquidnet.service.kylin.dao.OrderExportDao">-->
    <!--        <result column="performance_title" property="performanceTitle"/>-->
    <!--        <result column="user_mobile" property="userMobile"/>-->
    <!--        <result column="user_name" property="userName"/>-->
    <!--    </resultMap>-->

    <resultMap id="PerformanceSimpleAllDaoResult" type="com.liquidnet.service.kylin.dao.PerformanceSimpleAllDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="tickets_id" property="ticketId"/>
        <result column="ticket_title" property="ticketTitle"/>
        <result column="ticket_times_id" property="timeId"/>
        <result column="time_title" property="timeTitle"/>
        <result column="total_general" property="totalGeneral"/>
        <result column="total_exchange" property="totalExchange"/>
    </resultMap>

    <resultMap id="performanceRecommendDaoResult" type="com.liquidnet.service.kylin.dao.PerformanceRecommendDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="is_recommend" property="isRecommend"/>
    </resultMap>


    <resultMap id="PerformanceRecommendAdminDaoResult"
               type="com.liquidnet.service.kylin.dao.PerformanceRecommendAdminDao">
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="time_start" property="timeStart"/>
        <result column="status" property="status"/>
        <result column="is_recommend" property="isRecommend"/>
    </resultMap>

    <resultMap id="ticketMemberAuditDaoResult" type="com.liquidnet.service.kylin.dao.TicketMemberAuditDao">
        <result column="tickets_id" property="ticketsId"/>
        <result column="title" property="title"/>
        <result column="use_start" property="useStart"/>
        <result column="status" property="status"/>
        <result column="time_start" property="timeStart"/>
        <result column="price" property="price"/>
        <result column="price_discount_member" property="priceDiscountMember"/>
        <result column="advance_minute_member" property="advanceMinuteMember"/>
        <result column="member_limit_count" property="memberLimitCount"/>
        <result column="is_member" property="isMember"/>
        <result column="is_exclusive" property="isExclusive"/>
    </resultMap>

    <resultMap id="performanceOrderStatisticalDaoResult"
               type="com.liquidnet.service.kylin.dao.PerformanceOrderStatisticalDao">
        <result column="performances_id" property="performancesId"/>
        <result column="tickets_id" property="ticketsId"/>
        <result column="title" property="title"/>
        <result column="price" property="price"/>
        <result column="type" property="type"/>
        <result column="use_start" property="useStart"/>
        <result column="use_end" property="useEnd"/>
        <result column="total_general" property="totalGeneral"/>
        <result column="sale_general" property="saleGeneral"/>
        <result column="surplus_general" property="surplusGeneral"/>
        <result column="total_sale_price" property="totalSalePrice"/>
        <result column="total_exchange" property="totalExchange"/>
        <result column="total_refund_general" property="totalRefundGeneral"/>
        <result column="total_refund_price" property="totalRefundPrice"/>
        <result column="total_member_number" property="totalMemberNumber"/>
        <result column="total_paying_number" property="totalPayingNumber"/>
        <result column="total_buy_users" property="totalBuyUsers"/>
    </resultMap>
    <resultMap id="performanceOrderListDaoResult" type="com.liquidnet.service.kylin.dao.PerformanceOrderListDao">
        <result column="performances_id" property="performancesId"/>
        <result column="tickets_id" property="ticketsId"/>
        <result column="title" property="title"/>
        <result column="price" property="price"/>
        <result column="type" property="type"/>
        <result column="use_start" property="useStart"/>
        <result column="total_general" property="totalGeneral"/>
        <result column="sale_general" property="saleGeneral"/>
        <result column="surplus_general" property="surplusGeneral"/>
        <result column="total_sale_price" property="totalSalePrice"/>
        <result column="total_exchange" property="totalExchange"/>
        <result column="total_refund_general" property="totalRefundGeneral"/>
        <result column="total_refund_price" property="totalRefundPrice"/>
        <result column="total_member_number" property="totalMemberNumber"/>
        <result column="total_paying_number" property="totalPayingNumber"/>
        <result column="total_buy_users" property="totalBuyUsers"/>
    </resultMap>
    <resultMap id="performanceSponsorDaoResult" type="com.liquidnet.service.kylin.dao.PerformanceSponsorDao">
        <result column="sponsor_id" property="sponsorId"/>
        <result column="sponsor" property="sponsorName"/>
        <result column="sponsor_type" property="sponsorType"/>
    </resultMap>

    <!-- 第三方演出列表 (不包含退票信息) -->
    <select id="partnerPerformanceList" parameterType="java.util.Map" resultMap="partnerPerformanceListResult">
        SELECT
        p.performances_id ,
        p.title ,
        p.time_start ,
        p.time_end ,
        t.time_sell ,
        t.time_stop ,
        IFNULL(t.total_general , 0) AS 'total_general' ,
        IFNULL(ot.sale_general , 0) AS 'sale_general' ,
        IFNULL(ot.total_sale_price , 0) AS 'total_sale_price' ,
        IFNULL(
        (
        t.total_general - ot.sale_general
        ) ,
        t.total_general
        ) AS 'surplus_general' ,
        ps.`status` ,
        p.type ,
        ps.audit_status ,
        p.reject_txt ,
        p.created_at
        FROM
        kylin_performances AS p
        LEFT JOIN kylin_performance_status AS ps ON p.performances_id = ps.performance_id
        LEFT JOIN kylin_performance_relations AS pr ON p.performances_id = pr.performance_id
        LEFT JOIN(
        SELECT
        ttr.performance_id ,
        sum(ts.total_general) AS 'total_general' ,
        MIN(
        DATE_SUB(
        t.time_start ,
        INTERVAL pay_countdown_minute MINUTE
        )
        ) AS 'time_sell' ,
        MAX(t.time_end) AS 'time_stop'
        FROM
        kylin_ticket_status AS ts
        LEFT JOIN kylin_ticket_relations AS tr ON tr.ticket_id = ts.ticket_id
        LEFT JOIN kylin_tickets AS t ON t.tickets_id = ts.ticket_id
        LEFT JOIN kylin_ticket_time_relation AS ttr ON tr.times_id = ttr.times_id
        GROUP BY
        ttr.performance_id
        ) AS t ON p.performances_id = t.performance_id
        LEFT JOIN(
        SELECT(
        sum(ot.number) - sum(ot.refund_number)
        ) AS 'sale_general' ,
        (
        sum(ot.price_actual) - sum(ot.price_refund)
        ) AS 'total_sale_price' ,
        otr.performance_id AS 'performance_id' 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 WHERE (ots.`status` = 1 or ots.`status` = 0 or ots.`status` = 3 or ots.`status` = 6) AND
        coupon_type = 'no' and ots.transfer_status in (0,1,2,5)
        GROUP BY otr.performance_id
        ) AS ot ON ot.performance_id = p.performances_id
        <where>
            <if test="title!=''">
                AND title LIKE concat('%', #{title}, '%')
            </if>
            <if test="status !='0'">
                <if test="status!='-2'">
                    AND ps.STATUS = #{status}
                </if>
                <if test="auditStatus!='-2'">
                    AND ps.audit_status = #{auditStatus}
                </if>
                <if test="timeStart!=''">
                    AND p.time_start BETWEEN #{timeStart} AND #{timeEnd}
                </if>
                <if test="stopSellDay!='-2'">
                    AND p.time_end BETWEEN #{stopSellTime} AND NOW()
                </if>
            </if>
            <if test="status=='0'">
                AND ps.STATUS = #{status}
            </if>
            <if test="merchantId != 1">
                AND pr.merchant_id = #{merchantId}
            </if>
        </where>
        ORDER BY ${orderItem} ${orderSc}
    </select>
    <!-- Mis演出列表 (不包含退票信息) -->
    <select id="misPerformanceList" parameterType="java.util.Map" resultMap="misPerformanceListResult">
        SELECT
        p.performances_id ,
        p.sponsor,
        p.audit_time,
        p.sort,
        p.title ,
        p.type,
        p.time_start ,
        p.time_end ,
        ps.sync_damai ,
        t.time_sell,
        t.time_stop,
        IFNULL(t.total_general , 0) AS 'total_general' ,
        IFNULL(ot.sale_general , 0) AS 'sale_general' ,
        IFNULL(ot.total_sale_price , 0) AS 'total_sale_price' ,
        IFNULL(
        (
        t.total_general - ot.sale_general
        ) ,
        t.total_general
        ) AS 'surplus_general' ,
        ps.`status` ,
        ps.audit_status ,
        ps.field_audit_status,
        p.reject_txt ,
        p.created_at
        FROM
        kylin_performances AS p
        LEFT JOIN kylin_performance_status AS ps ON p.performances_id = ps.performance_id
        LEFT JOIN kylin_performance_relations AS pr ON p.performances_id = pr.performance_id
        LEFT JOIN(
        SELECT
        ttr.performance_id ,
        sum(ts.total_general) AS 'total_general' ,
        MIN(
        DATE_SUB(
        t.time_start ,
        INTERVAL pay_countdown_minute MINUTE
        )
        ) AS 'time_sell' ,
        MAX(t.time_end) AS 'time_stop'
        FROM
        kylin_ticket_status AS ts
        LEFT JOIN kylin_ticket_relations AS tr ON tr.ticket_id = ts.ticket_id
        LEFT JOIN kylin_tickets AS t ON t.tickets_id = ts.ticket_id
        LEFT JOIN kylin_ticket_time_relation AS ttr ON tr.times_id = ttr.times_id
        GROUP BY
        ttr.performance_id
        ) AS t ON p.performances_id = t.performance_id
        LEFT JOIN(
        SELECT
        (sum(ot.number)- sum(ot.refund_number)) AS 'sale_general' ,
        (sum(ot.price_actual) - sum(ot.price_refund)) AS 'total_sale_price' ,
        otr.performance_id AS 'performance_id'
        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
        WHERE
        (ots.`status` = 1 or ots.`status` = 0 or ots.`status` = 3 or ots.`status` = 6) and coupon_type ='no' and
        ots.transfer_status in (0,1,2,5)
        GROUP BY otr.performance_id
        ) AS ot ON ot.performance_id = p.performances_id
        <where>
            <if test="title!=''">
                AND title LIKE concat('%', #{title}, '%')
            </if>
            <if test="cityName!=''">
                AND city_name LIKE concat('%', #{cityName}, '%')
            </if>
            <if test="status!='-2'">
                AND ps.STATUS = #{status}
            </if>
            <if test="status=='-2'">
                AND (ps.STATUS >= 3 or ps.STATUS = 1)
            </if>
            <if test="auditStatus!='-2'">
                AND ps.audit_status = #{auditStatus}
            </if>
            <!--            <if test="auditStatus=='-2'">-->
            <!--                AND (ps.audit_status = 0 or ps.audit_status = 1 or ps.audit_status = 2)-->
            <!--            </if>-->
            <if test="stopSellDay!=-2">
                AND p.time_end BETWEEN #{stopSellTime} AND NOW()
            </if>
        </where>
        <if test="orderItem!=''">
            ORDER BY ${orderItem} ${orderSc}
        </if>
    </select>
    <!-- Mis 根据巡演查询演出列表 -->
    <select id="misTitleByRoadShowIdList" parameterType="java.lang.String" resultMap="performanceTitleDaoResult">
        SELECT performances_id,
               title,
               img_poster
        FROM kylin_performances AS p
                 LEFT JOIN kylin_performance_status AS ps ON ps.performance_id = p.performances_id
                 LEFT JOIN kylin_performance_relations AS pr ON pr.performance_id = p.performances_id
        WHERE (ps.status = 3 or ps.status = 6 or ps.status = 7 or ps.status = 8 or ps.status = 9 or ps.status = 10)
          AND pr.road_show_id = ${roadShowId}
        LIMIT 100
    </select>
    <!-- Mis 根据状态查询演出列表 -->
    <select id="misTitleByStatusList" parameterType="java.lang.String" resultMap="performanceTitleDaoResult">
        SELECT performances_id,
        title,
        img_poster
        FROM kylin_performances AS p
        LEFT JOIN kylin_performance_status AS ps ON ps.performance_id = p.performances_id
        LEFT JOIN kylin_performance_relations AS pr ON pr.performance_id = p.performances_id
        <where>
            ps.status in ${status}
            <if test="title!=''">
                AND title LIKE concat('%', #{title}, '%')
            </if>
        </where>
        LIMIT 20
    </select>
    <!-- Mis 根据状态查询演出列表复杂 -->
    <select id="misPerformanceDetail" resultMap="PerformanceSimpleAllDaoResult">
        SELECT
        performances_id,
        p.title ,
        tt.ticket_times_id,
        tt.title as 'time_title',
        t.tickets_id,
        t.title as 'ticket_title',
        ts.total_general as 'total_general',
        ts.total_exchange as 'total_exchange'
        FROM kylin_performances AS p
        INNER JOIN kylin_performance_status AS ps ON ps.performance_id = p.performances_id
        INNER JOIN kylin_performance_relations AS pr ON pr.performance_id = p.performances_id
        INNER JOIN kylin_ticket_time_relation AS ttr ON ttr.performance_id = p.performances_id
        INNER JOIN kylin_ticket_times AS tt ON tt.ticket_times_id = ttr.times_id
        INNER JOIN kylin_ticket_relations AS tr ON tr.times_id = ttr.times_id
        INNER JOIN kylin_tickets AS t ON t.tickets_id = tr.ticket_id
        INNER JOIN kylin_ticket_status AS ts ON ts.ticket_id = tr.ticket_id
        <where>
            <foreach collection="performanceList" item="performanceIds" index="index" open="performances_id in ("
                     close=")" separator=",">
                ${performanceIds}
            </foreach>
        </where>
        ORDER BY performances_id desc
    </select>
    <!-- Mis演出推荐列表 -->
    <select id="reCommendPerformanceList" resultMap="PerformanceRecommendAdminDaoResult">
        SELECT performances_id,
               title,
               time_start,
               status,
               is_recommend
        FROM kylin_performances AS p
                 LEFT JOIN kylin_performance_status AS ps ON ps.performance_id = p.performances_id
        WHERE is_recommend > 0
        ORDER BY is_recommend DESC
    </select>
    <!-- Mis 根据演出id查询推荐数据 -->
    <select id="getRecommend" parameterType="java.lang.String" resultMap="performanceRecommendDaoResult">
        SELECT performances_id,
               title,
               ps.is_recommend
        FROM kylin_performances AS p
                 LEFT JOIN kylin_performance_status AS ps ON ps.performance_id = p.performances_id
                 LEFT JOIN kylin_performance_relations AS pr ON pr.performance_id = p.performances_id
        WHERE p.performances_id = #{performancesId}
    </select>
    <!-- Mis根据演出id查询演出名称 -->
    <select id="misPerformanceMemberInfo" resultMap="performanceTitleDaoResult">
        SELECT performances_id,
               title
        FROM kylin_performances AS p
                 LEFT JOIN kylin_performance_relations AS pr ON pr.performance_id = p.performances_id
        WHERE performances_id = ${performancesId}
    </select>
    <!-- Mis根据演出id查询票种信息 -->
    <select id="misTicketMemberInfo" resultMap="ticketMemberAuditDaoResult">
        SELECT tickets_id,
               t.title,
               use_start,
               t.status,
               time_start,
               price,
               price_discount_member,
               advance_minute_member,
               member_limit_count,
               is_member,
               is_exclusive
        FROM kylin_ticket_time_relation AS ttr
                 LEFT JOIN kylin_ticket_times AS tt ON tt.ticket_times_id = ttr.times_id
                 LEFT JOIN(
            SELECT tickets_id,
                   tr.times_id as 'times_id',
                   title,
                   status,
                   t.time_start,
                   price,
                   advance_minute_member,
                   price_discount_member,
                   member_limit_count,
                   is_member,
                   is_exclusive
            FROM kylin_tickets AS t
                     LEFT JOIN kylin_ticket_status AS ts ON ts.ticket_id = t.tickets_id
                     LEFT JOIN kylin_ticket_relations AS tr ON tr.ticket_id = t.tickets_id
        ) as t on t.times_id = ttr.times_id
        where ttr.performance_id = ${performancesId};

    </select>
    <!-- 演出订单统计-->
    <select id="getPerformanceOrderStatisticalList" parameterType="java.lang.String"
            resultMap="performanceOrderStatisticalDaoResult">
        SELECT
            kp.performances_id ,
            kt.tickets_id,
            kt.title,
            kt.price,
            kt.type,
            kt.use_start,
            kt.use_end,
            IFNULL(kts.total_general , 0) AS 'total_general' ,
            IFNULL(ot.total_sale_general , 0) AS 'sale_general' ,
            IFNULL((kts.total_general - IFNULL(ot.total_sale_general,0) - IFNULL(otp.total_paying_number , 0)),0) AS 'surplus_general',
            IFNULL(ot.total_sale_price , 0) AS 'total_sale_price' ,
            IFNULL(kts.total_exchange , 0) AS 'total_exchange',
            IFNULL(rd.total_refund_general , 0) AS 'total_refund_general',
            IFNULL(rd.total_refund_price , 0) AS 'total_refund_price',
            IFNULL(ot.total_member_number , 0) AS 'total_member_number',
            IFNULL(otp.total_paying_number , 0) AS 'total_paying_number',
            IFNULL(ut.total_buy_users , 0) AS 'total_buy_users'
        FROM
            (select t.performances_id,ktr.ticket_id from kylin_performances t inner join kylin_ticket_time_relation kttr
                                                                                         on t.performances_id = kttr.performance_id
                                                                              inner join kylin_ticket_relations ktr on kttr.times_id = ktr.times_id
             where t.performances_id = ${performancesId}) AS kp
                left JOIN(
                SELECT
                    kotr.performance_id AS 'performance_id',
                    kotr.ticket_id,
                    sum(kot.number)-sum(ifnull(kot.refund_number,0)) AS 'total_sale_general' ,
                    sum(kot.price_actual)-sum(ifnull(kot.price_refund,0)) AS 'total_sale_price',
                    sum(case when kotr.is_member = 1 then kot.number else 0 end) total_member_number
                FROM
                    kylin_order_ticket_relations kotr
                        inner join kylin_order_tickets AS kot 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 1>0 and kotr.performance_id = ${performancesId}
                  and kots.`status` in(1,3,6) and kot.coupon_type ='no' and kots.transfer_status in (0,1,2,5)
                group by kotr.performance_id,kotr.ticket_id
            ) AS ot ON (ot.performance_id = kp.performances_id and ot.ticket_id = kp.ticket_id)
                left JOIN(
                SELECT
                    kotr.performance_id,kotr.ticket_id,
                    sum(ifnull(kot.number,0)) total_paying_number
                FROM
                    kylin_order_ticket_relations kotr
                        inner join kylin_order_tickets AS kot 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 1>0 and kotr.performance_id = ${performancesId}
                  and kots.`status` = 0 and kot.coupon_type ='no' and kots.transfer_status in (0,1,2,5)
                group by kotr.performance_id,kotr.ticket_id
            ) AS otp ON (otp.performance_id = kp.performances_id and otp.ticket_id = kp.ticket_id)
                left JOIN(
                SELECT
                    kotr.performance_id,kotr.ticket_id,
                    sum(kot.refund_number) as 'total_refund_general',
                    sum(kot.price_refund) as 'total_refund_price'
                FROM
                    kylin_order_ticket_relations kotr
                        inner join kylin_order_tickets AS kot 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 1>0 and kotr.performance_id = ${performancesId}
                  and kots.`status` in(4,6) and kot.coupon_type ='no' and kots.transfer_status in (0,1,2,5)
                group by kotr.performance_id,kotr.ticket_id
            ) AS rd ON (rd.performance_id = kp.performances_id and rd.ticket_id = kp.ticket_id)
                left join kylin_tickets kt on kt.tickets_id = kp.ticket_id
                left join kylin_ticket_status kts on kts.ticket_id = kp.ticket_id
                left JOIN(
                select t.performance_id,count(t.user_id) as 'total_buy_users' from(
                                                                                      SELECT
                                                                                          kotr.performance_id,
                                                                                          kot.user_id
                                                                                      FROM
                                                                                          kylin_order_ticket_relations kotr
                                                                                              inner join kylin_order_tickets AS kot 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 = ${performancesId} and kots.`status` in (0,1,3,6)
                                                                                      group by kotr.performance_id,kot.user_id
                                                                                  ) t
            ) AS ut ON ut.performance_id = kp.performances_id
        order by kt.type desc,kt.title,kt.use_start asc
    </select>
    <select id="getPerformanceSponsorList" resultMap="performanceSponsorDaoResult">
        select distinct kp.sponsor_id, kp.sponsor, kp.sponsor_type
        from kylin_performances kp
        <where>
            kp.sponsor_id != ''
            <if test="sponsorType!=null and sponsorType!=''">
                AND kp.sponsor_type = #{sponsorType}
            </if>
        </where>
        order by sponsor_id
    </select>
    <select id="performanceExpressPerformanceList"
            resultType="com.liquidnet.service.kylin.dao.PerformanceExpressPerformanceListAdminDao">
        SELECT
        p.performances_id ,
        p.title ,
        p.time_start ,
        p.time_end ,
        p.created_at
        FROM
        kylin_performances AS p
        LEFT JOIN kylin_performance_status AS ps ON p.performances_id = ps.performance_id
        LEFT JOIN kylin_performance_relations AS pr ON p.performances_id = pr.performance_id
        JOIN kylin_ticket_time_relation AS a ON a.performance_id = p.performances_id
        JOIN kylin_ticket_relations AS b ON b.times_id = a.times_id
        JOIN kylin_ticket_status AS c ON c.ticket_id = b.ticket_id
        <where>
            c.is_express = 1
            <if test="title!=''">
                AND p.title LIKE concat('%', #{title}, '%')
            </if>
            <if test="performancesId!=''">
                AND p.performances_id = #{performancesId}
            </if>
        </where>
        GROUP BY p.performances_id
        ORDER BY p.created_at DESC
    </select>
    <select id="countExpressNumber"
            resultType="com.liquidnet.service.kylin.dao.PerformanceExpressPerformanceListAdminDao">
        SELECT
            COUNT(*) total_number
        FROM
            kylin_performance_relations AS pr
                LEFT JOIN kylin_order_ticket_relations AS otr ON pr.performance_id = otr.performance_id
                LEFT JOIN kylin_order_tickets AS ot ON ot.order_tickets_id = otr.order_id
                LEFT JOIN kylin_order_ticket_status AS ots ON ots.order_id = otr.order_id
        WHERE pr.performance_id = #{performancesId}
          AND ots.status IN (1, 3, 4, 6)
          AND ots.pay_status = 1
          AND ots.transfer_status IN (0, 1, 3, 5)
          AND ot.get_ticket_type = 'express'
    </select>

    <select id="exportOrderByPerformanceIdAll" resultMap="OrderExportDaoResult">
SELECT performance_title,user_mobile,user_name,title,CONCAT(use_start,use_end) as 'use_time',use_start,use_end
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_tickets as kt on kt.tickets_id = kotr.ticket_id
WHERE performance_id = #{performancesId}
GROUP BY user_mobile,tickets_id;
    </select>

    <select id="exportOrderByPerformanceIdPay" resultMap="OrderExportDaoResult">
SELECT performance_title,user_mobile,user_name,title,CONCAT(use_start,use_end) as 'use_time',use_start,use_end
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_tickets as kt on kt.tickets_id = kotr.ticket_id
WHERE performance_id =  #{performancesId}
  AND status IN (1, 3)
GROUP BY user_mobile,tickets_id;
    </select>
    <select id="getPerformanceSimpleByTicketId" resultType="com.liquidnet.service.kylin.dao.PerformanceSimpleAllDao">
        <!--        select kp.performances_id  as performancesId,-->
        <!--               kp.title,-->
        <!--               ktt.ticket_times_id as timeId,-->
        <!--               ktt.title           as timeTitle,-->
        <!--               kt.tickets_id       as ticketId,-->
        <!--               kt.title            as ticketTitle-->
        <!--        from kylin_performances kp-->
        <!--                 left join kylin_ticket_time_relation kttr on kttr.performance_id = kp.performances_id-->
        <!--                 left join kylin_ticket_times ktt on kttr.times_id = ktt.ticket_times_id-->
        <!--                 left join kylin_ticket_relations ktr on ktr.times_id = kttr.times_id-->
        <!--                 left join kylin_tickets kt on kt.tickets_id = ktr.ticket_id-->
        <!--        <where>-->
        <!--            <if test="scope != null and scope == '1'">-->
        <!--                kp.performances_id=#{id}-->
        <!--            </if>-->
        <!--            <if test="scope != null and scope == '2'">-->
        <!--                ktr.times_id=#{id}-->
        <!--            </if>-->
        <!--            <if test="scope != null and scope == '3'">-->
        <!--                ktr.ticket_id=#{id}-->
        <!--            </if>-->
        <!--        </where>-->
        <!--        limit 1-->
        select kp.performances_id as performancesId,
        kp.title,
        ktt.ticket_times_id as timeId,
        ktt.title as timeTitle,
        kt.tickets_id as ticketId,
        kt.title as ticketTitle
        from kylin_performances kp
        left join kylin_ticket_time_relation kttr on kttr.performance_id = kp.performances_id
        left join kylin_ticket_times ktt on kttr.times_id = ktt.ticket_times_id
        left join kylin_ticket_relations ktr on ktr.times_id = kttr.times_id
        left join kylin_tickets kt on kt.tickets_id = ktr.ticket_id
        where ktr.ticket_id = #{ticketId}
    </select>
    <select id="getPerformanceSimpleByTimesId" resultType="com.liquidnet.service.kylin.dao.PerformanceSimpleAllDao">
        select kp.performances_id  as performancesId,
               kp.title,
               ktt.ticket_times_id as timeId,
               ktt.title           as timeTitle
        from kylin_performances kp
                 left join kylin_ticket_time_relation kttr on kttr.performance_id = kp.performances_id
                 left join kylin_ticket_times ktt on kttr.times_id = ktt.ticket_times_id
        where ktt.ticket_times_id = #{timesId}
    </select>
    <select id="getPerformanceSimpleByPerformancesId"
            resultType="com.liquidnet.service.kylin.dao.PerformanceSimpleAllDao">
        select kp.performances_id as performancesId,
               kp.title
        from kylin_performances kp
        where kp.performances_id = #{performancesId}
    </select>

    <update id="updateComment">
        update kylin_performances set `comment` = null where    `comment` = #{sort};
    </update>

    <update id="updateCommentByPer">
        update kylin_performances set `comment` = #{sort} where    `performances_id` = #{performancesId};
    </update>

    <select id="selectByComment" resultType="com.liquidnet.service.kylin.entity.KylinPerformances">
        select performances_id,comment from kylin_performances where `comment` != "" ORDER BY `comment`;
    </select>

    <select id="selectPerIdByAgent" resultType="com.liquidnet.service.kylin.dao.report.KylinPerformancesDto">
        SELECT
            kp.performances_id AS performancesId,
            kp.title,
            kp.city_name AS cityName,
            kp.time_end AS timeEnd,
            kp.time_start AS timeStart,
            kp.`comment`,
            IFNULL( map.`open`, 0 ) AS OPEN,
            IFNULL( map.price, 0 ) AS totalSalePrice,
            kps.`status`,
            sa.type
        FROM
            kylin_performances AS kp
                LEFT JOIN kylin_performance_status AS kps ON kps.performance_id = kp.performances_id
                LEFT JOIN smile_agent AS sa ON sa.performance_id = kp.performances_id
                LEFT JOIN (
                SELECT
                    kk.performance_id,
                    sum( kot.number - kot.refund_number ) AS `open`,
                    sum( kot.price_actual - kot.price_refund ) AS price
                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
                                INNER JOIN smile_user AS su ON su.uid = kotr.agent_id
                        WHERE
                            kots.STATUS IN ( 1, 6 ,3 )
                    ) AS kk ON kot.order_tickets_id = kk.order_id
                WHERE
                    kk.agent_id != 0
                    AND (kk.agent_distributions_master !=0 or kk.agent_distributions!=0)
                GROUP BY
                    kk.performance_id
            ) AS map ON map.performance_id = kp.performances_id
        WHERE
                kp.performances_id IN ( SELECT sa.performance_id FROM smile_agent AS sa WHERE sa.del_tag = 0 GROUP BY sa.performance_id )
        GROUP BY
            sa.performance_id
        ORDER BY
            kp.COMMENT DESC,
            kp.time_start DESC
    </select>

    <select id="getListAll" resultType="com.liquidnet.service.kylin.dao.report.KylinPerformancesDto">
        SELECT
        kp.performances_id AS performancesId,
        kp.title,
        kps.`status`,
        sa.type
        FROM
        kylin_performances AS kp
        LEFT JOIN kylin_performance_status AS kps ON kps.performance_id = kp.performances_id
        LEFT JOIN smile_agent AS sa ON sa.performance_id = kp.performances_id
        WHERE
        kp.performances_id IN ( SELECT sa.performance_id FROM smile_agent AS sa WHERE sa.del_tag = 0 AND sa.type = 0
        GROUP BY sa.performance_id )
        AND sa.`type` = 0
        <if test="title!=''">
            AND kp.title LIKE concat('%', #{title}, '%')
        </if>
        GROUP BY
        sa.performance_id
        ORDER BY
        kp.COMMENT DESC,
        kp.time_start DESC
    </select>

    <select id="selectTicketIdByPerId" resultType="com.liquidnet.service.kylin.dao.KylinPerformancesDao">
        SELECT
            kp.performances_id,
            kts.ticket_id,
            kt.title,
            kp.title,
            kp.city_name,
            kp.time_end,
            kp.COMMENT
        FROM
            kylin_performances AS kp
                LEFT JOIN kylin_ticket_time_relation AS kttr ON kttr.performance_id = kp.performances_id
                LEFT JOIN kylin_ticket_relations AS ktr ON ktr.times_id = kttr.times_id
                INNER JOIN kylin_ticket_status AS kts ON kts.ticket_id = ktr.ticket_id
                LEFT JOIN kylin_tickets AS kt ON kt.tickets_id = ktr.ticket_id
        WHERE
            kts.is_agent = 1 AND kp.performances_id = #{performancesId}
        ORDER BY
            kp.COMMENT,
            kp.time_start ASC
    </select>

    <!-- 订单信息-->
    <select id="exportOrderByOrderTime" resultMap="OrderExportDaoResult">
        select temtable.order_code        as order_code,
                temtable.code               as code,
                temtable.payment_id        as payment_id,
                temtable.performance_title as performance_title,
                case
                    when (temtable.status = 1) THEN '已支付'
                    when (temtable.status = 3) THEN '退款中'
                    when (temtable.status = 4) THEN '已退款'
                    when (temtable.status = 6) THEN '部分退款'
                    END               as status,
                (CASE
                     WHEN temtable.payment_type = 'WAPALIPAY' THEN '支付宝'
                     WHEN temtable.payment_type = 'APPALIPAY' THEN '支付宝'
                     WHEN temtable.payment_type = 'JSWEPAY' THEN '微信'
                     WHEN temtable.payment_type = 'APPLETWEPAY' THEN '微信'
                     WHEN temtable.payment_type = 'APPWEPAY' THEN '微信'
                     WHEN temtable.payment_type = 'WAPWEPAY' THEN '微信'
                     WHEN temtable.payment_type = 'APPUNIONPAY' THEN '银联云闪付'
                     WHEN temtable.payment_type = 'WAPUNIONPAY' THEN '银联云闪付'
                     WHEN temtable.payment_type = 'APPLETDOUYINPAY' THEN '抖音支付'
                     ELSE '其他' END)   as payment_type,
                case
                    when (temtable.type = '1') then '音乐节'
                    when (temtable.type = '2') then '演唱会'
                    when (temtable.type = '3') then '小型演出'
                    when (temtable.type = '4') then '展览'
                    when (temtable.type = '6') then '舞台剧'
                    when (temtable.type = '101') then '音乐节'
                    when (temtable.type = '102') then '小型演出(livehouse演出)'
                    when (temtable.type = '103') then '巡演'
                    end                  type,
                temtable.title1              as title1,
                temtable.title2             as title2,
                temtable.price             as price,
                temtable.price_express     as price_express,
                temtable.number            as number,
                temtable.refund_number     as refund_number,
                temtable.price_actual      as price_actual,
                temtable.price_refund      as price_refund,
                temtable.refund_at        as refund_at,
                temtable.reject            as reject,
                temtable.time_pay          as time_pay
        from
            (select kot.order_code,
                    do.code,
                    kot.payment_id,
                    kot.performance_title,
                    ks.status,
                    kot.payment_type,
                    kp.type,
                    kt.title as 'title1',
                     ktt.title as 'title2',
                     kot.price,
                    kot.price_express,
                    kot.number,
                    kot.refund_number,
                    kot.price_actual,
                    kot.price_refund,
                    dor.refund_at,
                    kor.reject,
                    kot.time_pay
             from kylin_order_tickets kot
                      inner join kylin_order_ticket_status ks on kot.order_tickets_id = ks.order_id
                      left join kylin_order_refunds kor on kor.order_tickets_id = kot.order_tickets_id
                      inner join kylin_order_ticket_relations kr on kot.order_tickets_id = kr.order_id
                      inner join kylin_tickets kt on kt.tickets_id = kr.ticket_id
                      inner join kylin_performances kp on kp.performances_id = kr.performance_id
                      inner join dragon_orders do on do.order_code = kot.order_code and do.status = 1
                      left join dragon_order_refunds dor on dor.order_refund_id = do.code
                      left join kylin_ticket_times ktt on ktt.ticket_times_id = kr.time_id
             where ks.status in (1, 3)
               and ks.transfer_status in (0, 1, 2, 5)
               and (do.payment_at between #{beginTime} and #{endTime})
             group by do.code
             union
             select kot.order_code,
                    do.code,
                    kot.payment_id,
                    kot.performance_title,
                    ks.status,
                    kot.payment_type,
                    kp.type,
                    kt.title as 'title1',
                     ktt.title as 'title2',
                     kot.price,
                    kot.price_express,
                    kot.number,
                    kot.refund_number,
                    kot.price_actual,
                    kot.price_refund,
                    dor.refund_at,
                    kor.reject,
                    kot.time_pay
             from kylin_order_tickets kot
                      inner join kylin_order_ticket_status ks on kot.order_tickets_id = ks.order_id
                      left join kylin_order_refunds kor on kor.order_tickets_id = kot.order_tickets_id
                      inner join kylin_order_ticket_relations kr on kot.order_tickets_id = kr.order_id
                      inner join kylin_tickets kt on kt.tickets_id = kr.ticket_id
                      inner join kylin_performances kp on kp.performances_id = kr.performance_id
                      inner join dragon_orders do on do.order_code = kot.order_code and do.status = 1
                      inner join dragon_order_refunds dor on dor.order_refund_id = do.code
                      left join kylin_ticket_times ktt on ktt.ticket_times_id = kr.time_id
             where ks.status in (1, 3, 4, 6)
               and ks.transfer_status in (0, 1, 2, 5)
               and dor.refund_at between #{beginTime} and #{endTime}
             group by do.code) temtable group by temtable.code

    </select>
    <!-- 演出时间查订单信息-->
    <select id="exportOrderByShowTime" resultMap="OrderExportDaoResult">
        select kot.order_code        as order_code,
        kot.pay_code          as pay_code,
        kot.payment_id        as payment_id,
        kot.performance_title as performance_title,
        (CASE
        WHEN kot.payment_type = 'WAPALIPAY' THEN '支付宝'
        WHEN kot.payment_type = 'APPALIPAY' THEN '支付宝'
        WHEN kot.payment_type = 'JSWEPAY' THEN '微信'
        WHEN kot.payment_type = 'APPLETWEPAY' THEN '微信'
        WHEN kot.payment_type = 'APPWEPAY' THEN '微信'
        WHEN kot.payment_type = 'WAPWEPAY' THEN '微信'
        WHEN kot.payment_type = 'APPUNIONPAY' THEN '银联云闪付'
        WHEN kot.payment_type = 'WAPUNIONPAY' THEN '银联云闪付'
        WHEN kot.payment_type = 'APPLETDOUYINPAY' THEN '抖音支付'
        ELSE '其他' END)   as payment_type,
        case
        when (kp.type = '1') then '音乐节'
        when (kp.type = '2') then '演唱会'
        when (kp.type = '3') then '小型演出'
        when (kp.type = '4') then '展览'
        when (kp.type = '6') then '舞台剧'
        when (kp.type = '101') then '音乐节'
        when (kp.type = '102') then '小型演出(livehouse演出)'
        when (kp.type = '103') then '巡演'
        end                   as type,
        kt.title              as title1,
        kot.number            as number,
        ktt.title             as title2,
        kor.updated_at        as refund_at,
        kor.reject            as reject,
        kot.price_actual      as price_actual,
        kot.time_pay          as time_pay
        from kylin_order_tickets kot
        inner join kylin_order_ticket_status ks
        on kot.order_tickets_id = ks.order_id and ks.transfer_status in (0, 1, 2, 5)
        left join kylin_order_refunds kor on kor.order_tickets_id = kot.order_tickets_id
        inner join kylin_order_ticket_relations kr on kot.order_tickets_id = kr.order_id
        inner join kylin_tickets kt on kt.tickets_id = kr.ticket_id
        inner join kylin_performances kp on kp.performances_id = kr.performance_id
        and (kp.time_start &gt;= #{beginTime}) and (kp.time_start &lt;= #{endTime})
        left join kylin_ticket_times ktt on ktt.ticket_times_id = kr.time_id
        where ks.status in (1, 3, 4, 6)
        group by kot.order_tickets_id
    </select>
    <!-- 演出id查订单信息-->
    <select id="exportOrderByShowId" resultMap="OrderExportDaoResult">
        select kot.order_code        as order_code,
                kot.pay_code          as pay_code,
                kot.payment_id        as payment_id,
                kot.performance_title as performance_title,
                (CASE
                     WHEN kot.payment_type = 'WAPALIPAY' THEN '支付宝'
                     WHEN kot.payment_type = 'APPALIPAY' THEN '支付宝'
                     WHEN kot.payment_type = 'JSWEPAY' THEN '微信'
                     WHEN kot.payment_type = 'APPLETWEPAY' THEN '微信'
                     WHEN kot.payment_type = 'APPWEPAY' THEN '微信'
                     WHEN kot.payment_type = 'WAPWEPAY' THEN '微信'
                     WHEN kot.payment_type = 'APPUNIONPAY' THEN '银联云闪付'
                     WHEN kot.payment_type = 'WAPUNIONPAY' THEN '银联云闪付'
                     ELSE '其他' END)   as payment_type,
                kot.number            as number,
                kot.price             as price,
                kot.price_actual      as price_actual,
                kot.price_express     as price_express,
                kot.refund_number     as refund_number,
                case
                    when (kp.type = '1') then '音乐节'
                    when (kp.type = '2') then '演唱会'
                    when (kp.type = '3') then '小型演出'
                    when (kp.type = '4') then '展览'
                    when (kp.type = '6') then '舞台剧'
                    when (kp.type = '101') then '音乐节'
                    when (kp.type = '102') then '小型演出(livehouse演出)'
                    when (kp.type = '103') then '巡演'
                    end               as type ,
                kt.title              as title1,
                ktt.title             as title2,
                kor.updated_at        as refund_at,
                kor.reject            as reject,
                kot.price_refund      as price_refund,
                kot.time_pay          as time_pay
        from kylin_order_tickets kot
                 inner join kylin_order_ticket_status ks on kot.order_tickets_id = ks.order_id
                 left join kylin_order_refunds kor on kor.order_tickets_id = kot.order_tickets_id
                 inner join kylin_order_ticket_relations kr on kot.order_tickets_id = kr.order_id
                            and kr.performance_id = #{showId}
                 inner join kylin_tickets kt on kt.tickets_id = kr.ticket_id
                 inner join kylin_performances kp on kp.performances_id = kr.performance_id
                 left join kylin_ticket_times ktt on ktt.ticket_times_id = kr.time_id
        where ks.status in (1, 3, 4, 6)
          and ks.transfer_status in (0, 1, 2, 5)
        group by kot.order_tickets_id
    </select>
    <!-- 会员订单信息-->
    <select id="exportMemberOrder" resultMap="memberOrderExportDaoResult">
        select amo.pay_no     as pay_no,
                amo.payment_id as payment_id,
                (case amo.pay_type
                     when 'alipay' then '支付宝'
                     when 'wepay' then '微信'
                     when 'unionpay' then '银联'
                     else '其他'
                    end)       as pay_type,
                (case amo.state
                     when '1' then '已支付'
                     when '3' then '超时支付'
                     when '4' then '退款中'
                     when '5' then '已退款'
                     else '其他'
                    end)       as state,
                amo.price      as price,
                amo.payment_at as payment_at,
                amo.created_at as created_at,
                amo.updated_at as updated_at,
                amr.refund_at  as refund_at,
               amr.refund_price as refund_price
        from adam_member_order amo
                 left join adam_member_refund amr on amr.order_no = amo.order_no
        where amo.pay_type != 'giftcode'
  and amo.state in (1, 3, 4, 5)
  and (amo.payment_at between #{beginTime} and #{endTime}
    or amo.created_at between #{beginTime} and #{endTime}
    or amo.updated_at between #{beginTime} and #{endTime})
    </select>
    <!-- 商品订单信息-->
    <select id="exportCommodityOrder" resultMap="commodityOrderExportDaoResult">
        select temtable.code                                                            as code,
                temtable.payment_id                                                          as payment_id,
                name as name,
                (CASE temtable.payment_type
                     WHEN 'WAPALIPAY' THEN '支付宝'
                     WHEN 'APPALIPAY' THEN '支付宝'
                     WHEN 'JSWEPAY' THEN '微信'
                     WHEN 'APPLETWEPAY' THEN '微信'
                     WHEN 'APPWEPAY' THEN '微信'
                     WHEN 'WAPWEPAY' THEN '微信'
                     WHEN 'APPUNIONPAY' THEN '银联云闪付'
                     WHEN 'WAPUNIONPAY' THEN '银联云闪付'
                     WHEN 'APPLETDOUYINPAY' THEN '抖音支付'
                     ELSE '其他' END)                                                     as payment_type,
                temtable.price_actual                                                        as price_actual,
                temtable.created_at                                                           as created_at,
                temtable.price_express                                                       as price_express,
                temtable.price_refund                                                        as price_refund,
               CASE
                   WHEN temtable.STATUS = '0' THEN
                       '待付款'
                   WHEN temtable.STATUS = '2' THEN
                       '待发货'
                   WHEN temtable.STATUS = '3' THEN
                       '待收货'
                   WHEN temtable.STATUS = '4' THEN
                       '已完成'
                   WHEN temtable.STATUS = '5' THEN
                       '取消订单'
                   WHEN temtable.STATUS = '6' THEN
                       '退款通过'
                   WHEN temtable.STATUS = '7' THEN
                       '退货通过'
                   END AS status,
                temtable.refund_at                                                           as refund_at
        from
            (select do.code,
                    gso.payment_id,
                    group_concat(distinct concat(gos.spu_name, '*', gos.num) separator ';') as 'name',
                     gso.payment_type,
                    gso.price_actual,
                    do.created_at,
                    gso.price_express,
                    gso.price_refund,
                    gso.status,
                    gbo.refund_at
             from goblin_store_order gso
                      inner join goblin_order_sku gos on gos.order_id = gso.order_id
                      inner join dragon_orders do on do.order_code = gso.master_order_code and do.status = 1
                      left join goblin_back_order gbo on gbo.order_id = gso.order_id and gbo.status in (2, 8)
             where
                 do.created_at between #{beginTime} and #{endTime}
               and gso.status not in (0, 5)
             group by do.code
             union
             select do.code,
                    gso.payment_id,
                    group_concat(distinct concat(gos.spu_name, '*', gos.num) separator ';') as 'name',
                     gso.payment_type,
                    gso.price_actual,
                    do.created_at,
                    gso.price_express,
                    gso.price_refund,
                    gso.status,
                    gbo.refund_at
             from goblin_store_order gso
                      inner join goblin_order_sku gos on gos.order_id = gso.order_id
                      inner join dragon_orders do on do.order_code = gso.master_order_code and do.status = 1
                      inner join dragon_order_refunds dor on  dor.order_refund_id = do.code
                      left join goblin_back_order gbo on gbo.order_id = gso.order_id and gbo.status in (2, 8)
             where
                 dor.refund_at between #{beginTime} and #{endTime}
               and gso.status not in (0, 5)
             group by do.code) temtable group by temtable.code
    </select>

</mapper>
