<?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="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="type" property="type"/>
        <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="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="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>


    <!-- 第三方演出列表 (不包含退票信息) -->
    <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="partnerPerformanceListResult">
        SELECT
        p.performances_id ,
        p.sponsor,
        p.audit_time,
        p.sort,
        p.title ,
        p.type,
        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` ,
        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 = 2)
            </if>
            <if test="stopSellDay!=-2">
                AND p.time_end BETWEEN #{stopSellTime} AND NOW()
            </if>
        </where>
        ORDER BY #{orderItem} #{orderSc}
    </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 20
    </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 = ${roadShowId}
        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="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>
</mapper>
