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

    <!-- 第三方演出列表 (不包含退票信息) -->
    <select id="partnerPerformanceList" parameterType="java.util.Map" resultMap="partnerPerformanceListResult">
        SELECT
        p.performances_id ,
        p.title ,
        p.time_start ,
        p.time_end ,
        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
        ) ,
        0
        ) 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 ,
        ts.ticket_id ,
        sum(ts.total_general) AS 'total_general'
        FROM
        kylin_ticket_status AS ts
        LEFT JOIN kylin_ticket_relations AS tr ON tr.ticket_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) AS 'sale_general' ,
        sum(ot.price_actual) AS 'total_sale_price' ,
        ote.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_entities AS ote ON ote.order_id = ot.order_tickets_id
        WHERE
        ots.`status` = 0
        OR ots.`status` = 1
        GROUP BY ote.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>
            AND pr.merchant_id = #{merchantId}
        </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 ,
        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
        ) ,
        0
        ) 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 ,
        ts.ticket_id ,
        sum(ts.total_general) AS 'total_general'
        FROM
        kylin_ticket_status AS ts
        LEFT JOIN kylin_ticket_relations AS tr ON tr.ticket_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) AS 'sale_general' ,
        sum(ot.price_actual) AS 'total_sale_price' ,
        ote.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_entities AS ote ON ote.order_id = ot.order_tickets_id
        WHERE
        ots.`status` = 0
        OR ots.`status` = 1
        GROUP BY ote.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)
          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,
            IFNULL(kts.total_general , 0) AS 'total_general' ,
            IFNULL(ot.total_sale_general , 0) AS 'sale_general' ,
            IFNULL((kts.total_general - ot.total_sale_general),0) AS 'surplus_general',
            IFNULL(ot.total_sale_price , 0) AS 'total_sale_price' ,
            IFNULL(kts.total_exchange , 0) AS 'total_exchange',
            IFNULL(ot.total_refund_general , 0) AS 'total_refund_general',
            IFNULL(ot.total_refund_price , 0) AS 'total_refund_price',
            IFNULL(ot.total_member_number , 0) AS 'total_member_number',
            IFNULL(ot.total_paying_number , 0) AS 'total_paying_number',
            IFNULL(ut.total_buy_users , 0) AS 'total_buy_users'
        FROM
            (select t.performances_id from kylin_performances t where t.performances_id = ${performancesId}) AS kp
                inner JOIN(
                SELECT
                    kote.performance_id AS 'performance_id',
                    kotr.ticket_id,
                    sum(kot.number) AS 'total_sale_general' ,
                    sum(kot.price_actual) AS 'total_sale_price',
                    sum(kot.refund_number) as 'total_refund_general',
                    sum(kot.price_refund) as 'total_refund_price',
                    sum(case when kotr.is_member = 1 then kot.number else 0 end) total_member_number,
                    sum(case when kots.`status` = 0 then kot.number else 0 end) total_paying_number
                FROM
                    kylin_order_ticket_relations kotr
                        inner join kylin_order_tickets AS kot on kotr.ticket_id = kot.order_tickets_id
                        inner JOIN kylin_order_ticket_status AS kots ON kots.order_id = kot.order_tickets_id
                        inner JOIN kylin_order_ticket_entities AS kote ON kote.order_id = kot.order_tickets_id
                WHERE kotr.performance_id = ${performancesId} and (kots.`status` = 0 OR kots.`status` = 1)
            ) AS ot ON ot.performance_id = kp.performances_id
                inner join kylin_tickets kt on kt.tickets_id = ot.ticket_id
                inner join kylin_ticket_status kts on kts.ticket_id = kt.tickets_id
                inner 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.ticket_id = kot.order_tickets_id
                                                                         inner JOIN kylin_order_ticket_status AS kots ON kots.order_id = kot.order_tickets_id
                                                                         inner JOIN kylin_order_ticket_entities AS kote ON kote.order_id = kot.order_tickets_id
                                                                 WHERE kotr.performance_id = ${performancesId} and (kots.`status` = 0 OR kots.`status` = 1)
                                                                 group by kotr.performance_id,kot.user_id
                                                             ) t
            ) AS ut ON ut.performance_id = kp.performances_id
    </select>
</mapper>
