<?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="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"/>
    </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' 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 ,
        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'
        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
        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
        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="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'
                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'
                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'
                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
    </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,
        p.created_at,
        p.created_at,

        oep.total1,
        oep.total2,
        oep.total3
        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 kylin_order_express_performances AS oep ON oep.performances_id = pr.performance_id
        <where>
            <if test="title!=''">
                AND title LIKE concat('%', #{title}, '%')
            </if>
            <if test="performancesId!=''">
                AND performances_id = #{performancesId}
            </if>
        </where>
        ORDER BY 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 NOT IN (0, 2, 5)
        AND ot.get_ticket_type = 'express'
    </select>
</mapper>
