<?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"/>
    </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
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 performance_id =  #{performancesId}
GROUP BY user_mobile;
    </select>

    <select id="exportOrderByPerformanceIdPay" resultMap="OrderExportDaoResult">
SELECT performance_title,user_mobile,user_name
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 performance_id =  #{performancesId}
  AND status IN (1, 3)
GROUP BY user_mobile;
    </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.entity.KylinPerformances">
        select kp.performances_id,kp.title,kp.city_name,kp.time_end,kp.time_start,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
        where kts.is_agent = 1
        group by kp.performances_id
        order by kp.comment, kp.time_start asc
    </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>


</mapper>
