<?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.smile.mapper.SmileAgentMapper">

    <select id="listAgentByShowIdAndTicketId" resultType="com.liquidnet.service.smile.entity.SmileAgent">
        select id,performance_id from smile_agent
        <where>
            <if test="performanceId!=''">
                AND performance_id = #{performanceId}
            </if>
            <if test="ticketId!=''">
                AND ticket_id = #{ticketId}
            </if>
        </where>
    </select>

    <update id="updateAgentById">
        update smile_agent
        set ord_carry   = #{ordCarry},
            total_carry = #{totalCarry}
        where id = #{id}
    </update>

    <insert id="insertAgent">
        INSERT INTO `smile_agent`
        VALUES (NULL, #{performancesId}, #{ticketId}, 0, 0, 0);
    </insert>

    <select id="getUserDataByPerIdAndTicketsId" resultType="java.util.Map">
        SELECT *,
               SUM(kk.number - kk.refund_number)   num,
               SUM(kk.price *kk.`use`* ( kk.number - kk.refund_number )) totalPrice
        FROM (SELECT kote.mid,
                     kote.order_id,
                     kote.ticket_id           tid,
                     kt.title,
                     kote.user_id,
                     kote.performance_id      perId,
                     kote.is_payment,
                     kote.status,
                     kot.number,
                     kot.refund_number,
                     kot.price,
                     kotr.agent_id            agentId,
                     kotr.agent_id_master,
                     kotr.agent_distributions `use`,
                     kotr.agent_distributions_master,
                     su.type
              FROM kylin_order_ticket_entities kote
                       LEFT JOIN kylin_order_tickets kot ON kote.order_id = kot.order_tickets_id
                       LEFT JOIN kylin_order_ticket_relations kotr ON kotr.order_id = kot.order_tickets_id
                       LEFT JOIN kylin_order_ticket_status kots ON kots.order_id = kotr.order_id
                       LEFT JOIN smile_user su ON su.uid = kotr.agent_id
                       LEFT JOIN kylin_tickets kt ON kote.ticket_id = kt.tickets_id
              WHERE kote.performance_id = #{performancesId}
                AND kote.ticket_id = #{ticketId}
                AND kotr.agent_id = #{uid}
                AND kotr.agent_status = 1
                AND kots.status = 1
                AND kots.pay_status = 1
              GROUP BY kote.order_id) kk
    </select>

    <select id="getUserOrgByPerIdAndUidAndAgentMaster" resultType="java.util.Map">
        SELECT
            SUM(
                        ( kot.price_actual - kot.price_refund )* kotr.agent_distributions_master) totalPrice
        FROM
            kylin_order_ticket_relations AS kotr
                INNER JOIN kylin_order_tickets AS kot ON kot.order_tickets_id = kotr.order_id
        WHERE
                kot.order_tickets_id IN (
                SELECT
                    kotr.order_id
                FROM
                    kylin_order_ticket_relations AS kotr
                        LEFT JOIN kylin_order_ticket_status AS kots ON kots.order_id = kotr.order_id
                WHERE
                    kotr.agent_id IN ( SELECT uid FROM smile_user WHERE agent_id = #{uid} )
                  AND kots.status IN (1,6,3)
            ) AND kotr.performance_id = #{performancesId}  AND kotr.ticket_id = #{ticketId}
    </select>

    <select id="selectAgentList" resultType="com.liquidnet.service.smile.entity.dao.SmileAgentDao">
        SELECT sa.id,
               sa.performance_id,
               sa.ticket_id,
               sa.ord_carry,
               sa.total_carry,
               kt.title ticket_name
        FROM smile_agent sa
                 LEFT JOIN kylin_tickets kt ON kt.tickets_id = sa.ticket_id
        WHERE sa.performance_id = #{performancesId}
          AND sa.del_tag = 0
    </select>

    <select id="selectAgentCarryGtZeroByPerId" resultType="com.liquidnet.service.smile.entity.SmileAgent">
        SELECT id,
               performance_id,
               ticket_id
        FROM smile_agent
        WHERE performance_id = #{performancesId}
          AND del_tag = 0
    </select>

    <select id="selectCountPerId"  resultType="int">
        SELECT COUNT(sa.id) FROM (SELECT id FROM smile_agent WHERE del_tag = 0 GROUP BY performance_id) sa;
    </select>

    <select id="selectMapByPerId" resultType="com.liquidnet.service.smile.entity.dto.ShowBaseVoDto">
        SELECT
            sukotr.`name`,
            sukotr.city,
            sukotr.uid AS agent_id,
            sukotr.ticket_id AS tid,
            sukotr.type,
            kot.price,
            SUM(kot.price_actual - kot.price_refund) price_real,
            sukotr.agent_distributions AS `use`,
            SUM( kot.number - kot.refund_number ) number,
            SUM( ( kot.price_actual - kot.price_refund ) * sukotr.agent_distributions ) totalPrice
        FROM
            kylin_order_tickets AS kot
                INNER JOIN (
                SELECT
                    su.uid,
                    su.`name`,
                    su.city,
                    su.type,
                    kotr.order_id,
                    kotr.performance_id,
                    kotr.ticket_id,
                    kotr.agent_distributions
                FROM
                    smile_user AS su
                        INNER JOIN kylin_order_ticket_relations AS kotr ON kotr.agent_id = su.uid
                        INNER JOIN kylin_order_ticket_status AS kots ON kots.order_id = kotr.order_id
                WHERE
                        su.uid IN (
                        SELECT kotr.agent_id
                        FROM kylin_order_ticket_relations as kotr
                                 LEFT JOIN kylin_order_ticket_status AS kots ON kotr.order_id = kots.order_id
                                 INNER JOIN smile_user AS su ON su.uid = kotr.agent_id
                        WHERE kots.status IN (1,6,3)
                          AND kotr.agent_id != 0
                          AND kotr.performance_id = #{performancesId}
                        group by kotr.agent_id
                    )
                  AND kotr.performance_id = #{performancesId}
                  AND kots.`status` IN ( 1, 6 ,3)
            ) AS sukotr ON sukotr.order_id = kot.order_tickets_id
        GROUP BY
            sukotr.uid,
            sukotr.ticket_id
    </select>

</mapper>
