<?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.xinelu.manage.mapper.nursestationpersonrevenue.NurseStationPersonRevenueMapper">

    <resultMap type="NurseStationPersonRevenue" id="NurseStationPersonRevenueResult">
        <result property="id" column="id"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="appointmentOrderId" column="appointment_order_id"/>
        <result property="revenueAmount" column="revenue_amount"/>
        <result property="finishOrderTime" column="finish_order_time"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectNurseStationPersonRevenueVo">
        select id,
               nurse_station_person_id,
               appointment_order_id,
               revenue_amount,
               finish_order_time,
               create_by,
               create_time,
               update_by,
               update_time
        from nurse_station_person_revenue
    </sql>

    <select id="selectNurseStationPersonRevenueList" parameterType="NurseStationPersonRevenue"
            resultMap="NurseStationPersonRevenueResult">
        <include refid="selectNurseStationPersonRevenueVo"/>
        <where>
            <if test="nurseStationPersonId != null ">
                and nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="appointmentOrderId != null ">
                and appointment_order_id = #{appointmentOrderId}
            </if>
            <if test="revenueAmount != null ">
                and revenue_amount = #{revenueAmount}
            </if>
            <if test="finishOrderTime != null ">
                and finish_order_time = #{finishOrderTime}
            </if>
        </where>
    </select>

    <select id="selectNurseStationPersonRevenueById" parameterType="Long"
            resultMap="NurseStationPersonRevenueResult">
        <include refid="selectNurseStationPersonRevenueVo"/>
        where id = #{id}
    </select>

    <insert id="insertNurseStationPersonRevenue" parameterType="NurseStationPersonRevenue" useGeneratedKeys="true"
            keyProperty="id">
        insert into nurse_station_person_revenue
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nurseStationPersonId != null">nurse_station_person_id,
            </if>
            <if test="appointmentOrderId != null">appointment_order_id,
            </if>
            <if test="revenueAmount != null">revenue_amount,
            </if>
            <if test="finishOrderTime != null">finish_order_time,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="nurseStationPersonId != null">#{nurseStationPersonId},
            </if>
            <if test="appointmentOrderId != null">#{appointmentOrderId},
            </if>
            <if test="revenueAmount != null">#{revenueAmount},
            </if>
            <if test="finishOrderTime != null">#{finishOrderTime},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateNurseStationPersonRevenue" parameterType="NurseStationPersonRevenue">
        update nurse_station_person_revenue
        <trim prefix="SET" suffixOverrides=",">
            <if test="nurseStationPersonId != null">nurse_station_person_id =
                #{nurseStationPersonId},
            </if>
            <if test="appointmentOrderId != null">appointment_order_id =
                #{appointmentOrderId},
            </if>
            <if test="revenueAmount != null">revenue_amount =
                #{revenueAmount},
            </if>
            <if test="finishOrderTime != null">finish_order_time =
                #{finishOrderTime},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteNurseStationPersonRevenueById" parameterType="Long">
        delete
        from nurse_station_person_revenue
        where id = #{id}
    </delete>

    <delete id="deleteNurseStationPersonRevenueByIds" parameterType="String">
        delete from nurse_station_person_revenue where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectCount"
            resultType="com.xinelu.manage.vo.nursestationpersonrevenue.PersonRevenueCountVO">
        SELECT nsp.id nurseStationPersonId,
        nsp.nurse_person_name,
        ns.nurse_station_name,
        (select count(1) FROM nurse_station_person_revenue WHERE nurse_station_person_id = nsp.id) orderCount,
        (select IFNULL(SUM(revenue_amount), 0.00)
        FROM nurse_station_person_revenue
        where nurse_station_person_id = nsp.id) orderTotalPrice,
        (select count(1)
        FROM nurse_station_person_revenue
        WHERE nurse_station_person_id = nsp.id
        and finish_order_time &gt;= #{monthStartDateTime}
        and finish_order_time &lt;= #{monthEndDateTime}) monthOrderCount,
        (select IFNULL(SUM(revenue_amount), 0.00)
        FROM nurse_station_person_revenue
        where nurse_station_person_id = nsp.id
        and finish_order_time &gt;= #{monthStartDateTime}
        and finish_order_time &lt;= #{monthEndDateTime}) monthOrderTotalPrice,
        (select count(1)
        FROM nurse_station_person_revenue
        WHERE nurse_station_person_id = nsp.id
        and finish_order_time &gt;= #{startTime}
        and finish_order_time &lt;= #{nowTime}) todayOrderCount,
        (select IFNULL(SUM(revenue_amount), 0.00)
        FROM nurse_station_person_revenue
        where nurse_station_person_id = nsp.id
        and finish_order_time &gt;= #{startTime}
        and finish_order_time &lt;= #{nowTime}) todayOrderPrice
        FROM nurse_station_person nsp
        LEFT JOIN nurse_station_person_revenue nspr ON nsp.id = nspr.nurse_station_person_id
        LEFT JOIN nurse_station ns ON ns.id = nsp.nurse_station_id
        <where>
            <if test="nurseStationPersonId != null ">
                nsp.id = #{nurseStationPersonId}
            </if>
            <if test="nurseStationId != null ">
                AND ns.id = #{nurseStationId}
            </if>
        </where>
        GROUP BY nsp.id
    </select>

    <select id="selectRevenueAmountById"
            resultType="com.xinelu.manage.vo.nursestationpersonrevenue.PersonRevenueCountVO">
        select nsp.id nurseStationPersonId,
        nsp.nurse_person_name,
        (select IFNULL(SUM(revenue_amount), 0.00)
        FROM nurse_station_person_revenue
        where nurse_station_person_id = nspr.nurse_station_person_id
        and finish_order_time &gt;= #{startTime}
        and finish_order_time &lt;= #{endTime}) todayOrderPrice,
        (select IFNULL(SUM(revenue_amount), 0.00)
        FROM nurse_station_person_revenue
        where nurse_station_person_id = nspr.nurse_station_person_id
        and finish_order_time &gt;= #{monthStartDateTime}
        and finish_order_time &lt;= #{monthEndDateTime}) monthOrderTotalPrice,
        IFNULL(SUM(nspr.revenue_amount), 0.00) orderTotalPrice
        FROM nurse_station_person_revenue nspr
        LEFT JOIN nurse_station_person nsp on nsp.id = nspr.nurse_station_person_id
        <where>
            <if test="nurseStationPersonId != null ">
                nspr.nurse_station_person_id = #{nurseStationPersonId}
            </if>
        </where>
        group by nspr.nurse_station_person_id
    </select>

    <select id="selectRevenueDetails"
            resultType="com.xinelu.manage.vo.nursestationpersonrevenue.NurseStationPersonRevenueVO">
        select
        ao.order_no,
        ao.id appointmentOrderId,
        nspr.id nurseStationPersonRevenueId,
        nspr.revenue_amount,
        nspr.finish_order_time,
        aod.nurse_item_name,
        aod.service_date,
        aod.service_start_time
        FROM nurse_station_person_revenue nspr
        LEFT JOIN appointment_order ao ON ao.id = nspr.appointment_order_id
        LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
        <where>
            <if test="nurseStationPersonId != null ">
                nspr.nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="monthStartDateTime != null">
                and nspr.finish_order_time &gt;= #{monthStartDateTime}
            </if>
            <if test="monthEndDateTime != null">
                and nspr.finish_order_time &lt;= #{monthEndDateTime}
            </if>
        </where>
        ORDER BY nspr.finish_order_time Desc
    </select>
</mapper>
