<?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.sweet.mapper.SweetManualFiveMapper">

    <resultMap id="partnerPerformanceListResult" type="com.liquidnet.service.sweet.dto.SweetManualFiveDto">
        <result column="manual_id" property="manualId"/>
        <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="time_sell" property="timeSell"/>
        <result column="time_stop" property="timeStop"/>
        <result column="status" property="status"/>
        <result column="manualStatus" property="manualStatus"/>
        <result column="is_release" property="isRelease"/>
    </resultMap>

    <resultMap id="getManualAppletListResult" type="com.liquidnet.service.sweet.dto.SweetManualAppletFiveDto">
        <result column="manual_id" property="manualId"/>
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="field_id" property="fieldId"/>
        <result column="city_name" property="cityName"/>
        <result column="time_start" property="timeStart"/>
        <result column="time_end" property="timeEnd"/>
    </resultMap>

    <resultMap id="getManualAppletDtoResult" type="com.liquidnet.service.sweet.dto.SweetManualAppletFiveDto">
        <result column="manual_id" property="manualId"/>
        <result column="performances_id" property="performancesId"/>
        <result column="title" property="title"/>
        <result column="field_id" property="fieldId"/>
        <result column="time_start" property="timeStart"/>
        <result column="time_end" property="timeEnd"/>
        <result column="time_sell" property="timeSell"/>
        <result column="pay_countdown_minute" property="payCountdownMinute"/>
        <result column="is_member" property="isMember"/>
    </resultMap>

    <!-- 电子手册列表 -->
    <select id="getManualList" parameterType="java.util.Map" resultMap="partnerPerformanceListResult">
        SELECT
        IFNULL(sm.manual_id,0) as 'manual_id',
        p.performances_id ,
        p.title ,
        p.time_start ,
        p.time_end ,
        ps.status ,
        t.time_sell ,
        t.time_stop,
        IFNULL(sm.status ,0) as 'manualStatus',
        IFNULL(sm.is_release,0) as 'is_release'
        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 sweet_manual_Five AS sm ON p.performances_id = sm.performance_id
        LEFT JOIN(
        SELECT
        ttr.performance_id ,
        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
        <where>
            <if test="title!='' and title != null">
                AND (title LIKE concat('%', #{title}, '%'))
            </if>
        </where>
        ORDER BY p.created_at desc
    </select>

    <!-- 电子手册详情 -->
    <select id="getManualDetails" parameterType="java.util.Map" resultMap="partnerPerformanceListResult">
        SELECT IFNULL(sm.manual_id, 0)  as 'manual_id',
               p.performances_id,
               p.title,
               p.time_start,
               p.time_end,
               ps.status,
               t.time_sell,
               t.time_stop,
               IFNULL(sm.status, 0)     as 'manualStatus',
               IFNULL(sm.is_release, 0) as 'is_release'
        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 sweet_manual_Five AS sm ON p.performances_id = sm.performance_id
                 LEFT JOIN(
            SELECT ttr.performance_id,
                   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
        where sm.manual_id = #{manualId}
        ORDER BY p.created_at desc
    </select>


    <select id="getManualAppletList" resultMap="getManualAppletListResult">
        select manual_id, performances_id, title, field_id, city_name, time_start, time_end
        from kylin_performances as kp
                 left join sweet_manual_Five as sw on sw.performance_id = kp.performances_id
                 left join kylin_performance_relations as kpr on kpr.performance_id = kp.performances_id
        where sw.`status` = 1
          and sw.is_release = 1
    </select>

    <select id="getManualAppletDto" resultMap="getManualAppletDtoResult">
        SELECT
            IFNULL(sm.manual_id , 0) AS 'manual_id' ,
            p.performances_id ,
            p.title ,
            pr.field_id ,
            p.time_start ,
            p.time_end,
            t1.time_sell,
            t1.pay_countdown_minute,
             t1.is_member
        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 sweet_manual_Five AS sm ON p.performances_id = sm.performance_id
                LEFT JOIN(
                SELECT
                    ttr.performance_id ,
                    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
        ttr.performance_id ,
         pay_countdown_minute,
         ts.is_member,
        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 t1 ON p.performances_id = t1.performance_id
             where sm.`status` = 1 and sm.is_release = 1
    </select>

</mapper>
