<?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.trainingorder.TrainingOrderMapper">

    <resultMap type="TrainingOrder" id="TrainingOrderResult">
        <result property="id" column="id"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="nursePersonName" column="nurse_person_name"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="trainingOrderStatus" column="training_order_status"/>
        <result property="trainingOrderAmount" column="training_order_amount"/>
        <result property="trainingOrderChannel" column="training_order_channel"/>
        <result property="trainingOrderTime" column="training_order_time"/>
        <result property="remark" column="remark"/>
        <result property="delFlag" column="del_flag"/>
        <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>

    <resultMap type="com.xinelu.manage.vo.trainingorder.NurseAppletPersonTrainingOrderItemVO"
               id="NurseAppletPersonTrainingOrderItemVOResult">
        <result property="trainingOrderId" column="training_order_id"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="trainingItemId" column="training_item_id"/>
        <result property="trainingParentId" column="training_parent_id"/>
        <result property="trainingItemTitle" column="training_item_title"/>
        <result property="trainingItemCode" column="training_item_code"/>
        <result property="trainingItemPrice" column="training_item_price"/>
        <result property="trainingItemCoverUrl" column="training_item_cover_url"/>
        <result property="trainingItemPosterUrl" column="training_item_poster_url"/>
        <result property="trainingItemType" column="training_item_type"/>
        <result property="trainingItemDetails" column="training_item_details"/>
        <result property="trainingItemContent" column="training_item_content"/>
        <result property="trainingItemDate" column="training_item_date"/>
        <result property="trainingItemStartTime" column="training_item_start_time"/>
        <result property="trainingItemEndTime" column="training_item_end_time"/>
        <result property="trainingItemAddress" column="training_item_address"/>
        <result property="trainingItemNotes" column="training_item_notes"/>
        <result property="trainingItemSort" column="training_item_sort"/>
        <result property="trainingOrderStatus" column="training_order_status"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <collection property="trainingOrderItemDirectoryVOList" javaType="java.util.List"
                    resultMap="TrainingItemDirectoryResult"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.trainingorderitemdirectory.TrainingOrderItemDirectoryVO"
               id="TrainingItemDirectoryResult">
        <result property="id" column="trainingItemDirectoryId"/>
        <result property="trainingItemDirectoryId" column="training_item_directory_id"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="trainingItemId" column="training_item_id"/>
        <result property="itemDirectoryName" column="item_directory_name"/>
        <result property="itemDirectoryCode" column="item_directory_code"/>
        <result property="itemDirectoryTitle" column="item_directory_title"/>
        <result property="itemDirectoryUrl" column="item_directory_url"/>
        <result property="itemDirectoryVideoDuration" column="item_directory_video_duration"/>
        <result property="itemDirectoryIntroduce" column="item_directory_introduce"/>
        <result property="trainingItemDirectoryId" column="training_item_directory_id"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="itemDirectoryWatchStatus" column="item_directory_watch_status"/>
        <result property="itemDirectorySort" column="item_directory_sort"/>
        <result property="watchDate" column="watch_date"/>
        <result property="watchTime" column="watch_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>

    <resultMap type="com.xinelu.manage.vo.trainingorder.TrainingOrderVO" id="TrainingOrderVOResult">
        <result property="trainingOrderId" column="trainingOrderId"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="nursePersonName" column="nurse_person_name"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="trainingOrderStatus" column="training_order_status"/>
        <result property="trainingOrderAmount" column="training_order_amount"/>
        <result property="trainingOrderChannel" column="training_order_channel"/>
        <result property="trainingOrderTime" column="training_order_time"/>
        <result property="nurseStationName" column="nurse_station_name"/>
        <result property="remark" column="remark"/>
        <collection property="trainingOrderDetailsList" javaType="java.util.List"
                    resultMap="trainingOrderDetailsResult"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.trainingorderdetails.TrainingOrderDetailsVO" id="trainingOrderDetailsResult">
        <result property="trainingOrderDetailsId" column="trainingOrderDetailsId"/>
        <result property="trainingOrderId" column="training_order_id"/>
        <result property="trainingOrderNo" column="training_order_no"/>
        <result property="trainingItemId" column="training_item_id"/>
        <result property="trainingParentId" column="training_parent_id"/>
        <result property="trainingItemTitle" column="training_item_title"/>
        <result property="trainingItemCode" column="training_item_code"/>
        <result property="trainingItemPrice" column="training_item_price"/>
        <result property="trainingItemCoverUrl" column="training_item_cover_url"/>
        <result property="trainingItemPosterUrl" column="training_item_poster_url"/>
        <result property="trainingItemType" column="training_item_type"/>
        <result property="trainingItemDetails" column="training_item_details"/>
        <result property="trainingItemContent" column="training_item_content"/>
        <result property="trainingItemDate" column="training_item_date"/>
        <result property="trainingItemStartTime" column="training_item_start_time"/>
        <result property="trainingItemEndTime" column="training_item_end_time"/>
        <result property="trainingItemAddress" column="training_item_address"/>
        <result property="trainingItemNotes" column="training_item_notes"/>
        <result property="delFlag" column="del_flag"/>
    </resultMap>

    <sql id="selectTrainingOrderVo">
        select id,
               nurse_station_id,
               nurse_station_person_id,
               nurse_person_name,
               training_order_no,
               training_order_status,
               training_order_amount,
               training_order_channel,
               training_order_time,
               remark,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time
        from training_order
    </sql>

    <select id="selectTrainingOrderList" parameterType="TrainingOrder" resultMap="TrainingOrderResult">
        <include refid="selectTrainingOrderVo"/>
        <where>
            <if test="nurseStationId != null ">
                and nurse_station_id = #{nurseStationId}
            </if>
            <if test="nurseStationPersonId != null ">
                and nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="nursePersonName != null  and nursePersonName != ''">
                and nurse_person_name like concat('%', #{nursePersonName}, '%')
            </if>
            <if test="trainingOrderNo != null  and trainingOrderNo != ''">
                and training_order_no = #{trainingOrderNo}
            </if>
            <if test="trainingOrderStatus != null  and trainingOrderStatus != ''">
                and training_order_status = #{trainingOrderStatus}
            </if>
            <if test="trainingOrderAmount != null ">
                and training_order_amount = #{trainingOrderAmount}
            </if>
            <if test="trainingOrderChannel != null  and trainingOrderChannel != ''">
                and training_order_channel = #{trainingOrderChannel}
            </if>
            <if test="trainingOrderTime != null ">
                and training_order_time = #{trainingOrderTime}
            </if>
        </where>
    </select>

    <select id="selectTrainingOrderById" parameterType="Long"
            resultMap="TrainingOrderResult">
        <include refid="selectTrainingOrderVo"/>
        where id = #{id}
    </select>

    <insert id="insertTrainingOrder" parameterType="TrainingOrder" useGeneratedKeys="true"
            keyProperty="id">
        insert into training_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nurseStationId != null">nurse_station_id,
            </if>
            <if test="nurseStationPersonId != null">nurse_station_person_id,
            </if>
            <if test="nursePersonName != null">nurse_person_name,
            </if>
            <if test="trainingOrderNo != null">training_order_no,
            </if>
            <if test="trainingOrderStatus != null">training_order_status,
            </if>
            <if test="trainingOrderAmount != null">training_order_amount,
            </if>
            <if test="trainingOrderChannel != null">training_order_channel,
            </if>
            <if test="trainingOrderTime != null">training_order_time,
            </if>
            <if test="remark != null">remark,
            </if>
            <if test="delFlag != null">del_flag,
            </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="nurseStationId != null">#{nurseStationId},
            </if>
            <if test="nurseStationPersonId != null">#{nurseStationPersonId},
            </if>
            <if test="nursePersonName != null">#{nursePersonName},
            </if>
            <if test="trainingOrderNo != null">#{trainingOrderNo},
            </if>
            <if test="trainingOrderStatus != null">#{trainingOrderStatus},
            </if>
            <if test="trainingOrderAmount != null">#{trainingOrderAmount},
            </if>
            <if test="trainingOrderChannel != null">#{trainingOrderChannel},
            </if>
            <if test="trainingOrderTime != null">#{trainingOrderTime},
            </if>
            <if test="remark != null">#{remark},
            </if>
            <if test="delFlag != null">#{delFlag},
            </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="updateTrainingOrder" parameterType="TrainingOrder">
        update training_order
        <trim prefix="SET" suffixOverrides=",">
            <if test="nurseStationId != null">nurse_station_id =
                #{nurseStationId},
            </if>
            <if test="nurseStationPersonId != null">nurse_station_person_id =
                #{nurseStationPersonId},
            </if>
            <if test="nursePersonName != null">nurse_person_name =
                #{nursePersonName},
            </if>
            <if test="trainingOrderNo != null">training_order_no =
                #{trainingOrderNo},
            </if>
            <if test="trainingOrderStatus != null">training_order_status =
                #{trainingOrderStatus},
            </if>
            <if test="trainingOrderAmount != null">training_order_amount =
                #{trainingOrderAmount},
            </if>
            <if test="trainingOrderChannel != null">training_order_channel =
                #{trainingOrderChannel},
            </if>
            <if test="trainingOrderTime != null">training_order_time =
                #{trainingOrderTime},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </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="deleteTrainingOrderById" parameterType="Long">
        delete
        from training_order
        where id = #{id}
    </delete>

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

    <select id="selectTrainingOrderVideoList"
            parameterType="com.xinelu.manage.dto.trainingitem.NurseAppletPersonTrainingItemDTO"
            resultType="com.xinelu.manage.vo.trainingorderdetails.NurseAppletPersonTrainingOrderDetailsVO">
        SELECT
        t.id trainingOrderId,
        t.nurse_station_id,
        t.nurse_station_person_id,
        t.nurse_person_name,
        t.training_order_status,
        t.training_order_time,
        tod.id trainingOrderDetailsId,
        tod.training_order_no,
        tod.training_item_id,
        tod.training_parent_id,
        tod.training_item_title,
        tod.training_item_code,
        tod.training_item_price,
        tod.training_item_cover_url,
        tod.training_item_poster_url,
        tod.training_item_type,
        tod.training_item_details,
        tod.training_item_content,
        tod.del_flag
        FROM
        training_order t
        LEFT JOIN training_order_details tod ON t.training_order_no = tod.training_order_no
        <where>
            <if test="trainingOrderDetailsId != null ">
                and tod.id = #{trainingOrderDetailsId}
            </if>
            <if test="trainingItemId != null ">
                and tod.training_item_id = #{trainingItemId}
            </if>
            <if test="trainingParentId != null ">
                and tod.training_parent_id = #{trainingParentId}
            </if>
            <if test="trainingItemType != null  and trainingItemType != ''">
                and tod.training_item_type &lt;&gt; #{trainingItemType}
            </if>
            <if test="trainingOrderStatus != null  and trainingOrderStatus != ''">
                and t.training_order_status = #{trainingOrderStatus}
            </if>
            <if test="nurseStationPersonId != null ">
                and t.nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="trainingOrderNo != null and trainingOrderNo != ''">
                and t.training_order_no = #{trainingOrderNo}
            </if>
            AND t.del_flag = 0
            AND tod.del_flag = 0
        </where>
        ORDER BY
        t.training_order_time DESC
    </select>

    <select id="selectTrainingOrderVideoDetailsList"
            parameterType="com.xinelu.manage.dto.trainingitem.NurseAppletPersonTrainingItemDTO"
            resultMap="NurseAppletPersonTrainingOrderItemVOResult">
        SELECT
        tod.training_order_id,
        tod.training_order_no,
        tod.training_item_id,
        tod.training_parent_id,
        tod.training_item_title,
        tod.training_item_code,
        tod.training_item_price,
        tod.training_item_cover_url,
        tod.training_item_poster_url,
        tod.training_item_type,
        tod.training_item_details,
        tod.training_item_content,
        toid.training_item_directory_id,
        toid.item_directory_code,
        toid.training_order_no,
        toid.item_directory_name,
        toid.item_directory_title,
        toid.item_directory_video_duration,
        toid.item_directory_url,
        toid.item_directory_introduce,
        toid.item_directory_watch_status,
        ( SELECT watch_time FROM training_item_watch_record WHERE training_item_directory_id =
        toid.training_item_directory_id AND training_order_id = tod.training_order_id) watch_time,
        (select training_order_status from training_order where training_order_no = tod.training_order_no)
        training_order_status
        FROM
        training_order_details tod
        LEFT JOIN training_order_item_directory toid ON tod.training_order_no = toid.training_order_no
        <where>
            <if test="trainingItemId != null ">
                and tod.training_item_id = #{trainingItemId}
            </if>
            <if test="trainingOrderDetailsId != null ">
                and toid.training_order_details_id = #{trainingOrderDetailsId}
            </if>
            <if test="trainingItemType != null  and trainingItemType != ''">
                and tod.training_item_type = #{trainingItemType}
            </if>
            <if test="trainingOrderNo != null  and trainingOrderNo != ''">
                and tod.training_order_no = #{trainingOrderNo}
            </if>
            AND tod.del_flag = 0
        </where>
    </select>

    <select id="selectTrainingOrderVOList" parameterType="com.xinelu.manage.vo.trainingorder.TrainingOrderVO"
            resultType="com.xinelu.manage.vo.trainingorder.TrainingOrderVO">
        select tor.id,
        tor.nurse_station_id,
        tor.nurse_station_person_id,
        tor.nurse_person_name,
        tor.training_order_no,
        tor.training_order_status,
        tor.training_order_amount,
        tor.training_order_channel,
        tor.training_order_time,
        tor.remark,
        ( select training_item_title from training_order_details
        where training_order_no = tor.training_order_no
        and training_parent_id = #{trainingParentId} ) trainingItemTitle,
        ns.nurse_station_name
        from training_order tor
        left join nurse_station ns on tor.nurse_station_id = ns.id
        <where>
            <if test="nurseStationId != null ">
                and tor.nurse_station_id = #{nurseStationId}
            </if>
            <if test="nurseStationPersonId != null ">
                and tor.nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="nursePersonName != null  and nursePersonName != ''">
                and tor.nurse_person_name like concat('%', #{nursePersonName}, '%')
            </if>
            <if test="trainingOrderNo != null  and trainingOrderNo != ''">
                and tor.training_order_no = #{trainingOrderNo}
            </if>
            <if test="trainingOrderStatus != null  and trainingOrderStatus != ''">
                and tor.training_order_status = #{trainingOrderStatus}
            </if>
            <if test="trainingOrderTime != null ">
                and tor.training_order_time = #{trainingOrderTime}
            </if>
            AND tor.del_flag = 0
        </where>
        order by tor.training_order_time desc
    </select>

    <select id="getTrainingOrderInfoByOrderNo" parameterType="string" resultType="trainingOrder">
        <include refid="selectTrainingOrderVo"/>
        where training_order_no = #{trainingOrderNo}
    </select>

    <update id="updateTrainingOrderStatus" parameterType="string">
        update training_order
        set training_order_status = #{trainingOrderStatus},
            update_time           = now()
        where del_flag = 0
          and training_order_no = #{trainingOrderNo}
          and training_order_status &lt;&gt; #{trainingOrderStatus}
    </update>

    <select id="getWaitPayTrainingOrderList" parameterType="string" resultType="trainingOrder">
        select
        id,
        nurse_station_person_id,
        training_order_no,
        training_order_status,
        training_order_amount,
        training_order_channel,
        training_order_time
        from
        training_order
        <where>
            del_flag = 0 and training_order_status = #{trainingOrderStatus}
            <if test="trainingOrderList != null and trainingOrderList.size() > 0">
                and training_order_no in
                <foreach collection="trainingOrderList" item="trainingOrderNo" open="(" separator="," close=")">
                    #{trainingOrderNo}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getWaitPayTrainingOrderListByTime" resultType="trainingOrder">
        select id,
               nurse_station_person_id,
               training_order_no,
               training_order_status,
               training_order_amount,
               training_order_channel,
               training_order_time
        from training_order
        where del_flag = 0
          and training_order_time &lt;= #{time}
          and training_order_status = #{trainingOrderStatus}
    </select>

    <update id="updateBatchTrainingOrderStatus">
        update training_order set training_order_status = #{trainingOrderStatus}, update_time = now()
        where
        del_flag = 0
        and training_order_status &lt;&gt; #{trainingOrderStatus}
        and training_order_no in
        <foreach collection="orderNoList" item="trainingOrderNo" open="(" separator="," close=")">
            #{trainingOrderNo}
        </foreach>
    </update>

    <select id="getTrainingRefundOrderList" resultType="com.xinelu.manage.vo.TrainingRefundOrderVO">
        SELECT tr.training_order_no,
               tr.training_order_status,
               rf.out_refund_no
        FROM training_order tr
                 INNER JOIN refund_info rf ON tr.training_order_no = rf.order_no
        WHERE tr.training_order_status = #{trainingOrderStatus}
          AND rf.wechat_refund_status = #{refundStatus}
          AND tr.del_flag = 0
    </select>

    <select id="selectTrainingOrderVoById" resultType="com.xinelu.manage.vo.trainingorder.TrainingOrderVO"
            resultMap="TrainingOrderVOResult">
        select tod.id trainingOrderDetailsId,
               tod.training_parent_id,
               tod.training_item_title,
               tod.training_item_code,
               tod.training_item_price,
               tod.training_item_cover_url,
               tod.training_item_poster_url,
               tod.training_item_type,
               tod.training_item_details,
               tod.training_item_content,
               tor.id trainingOrderId,
               tor.nurse_station_id,
               tor.nurse_station_person_id,
               tor.nurse_person_name,
               tor.training_order_no,
               tor.training_order_status,
               tor.training_order_amount,
               tor.training_order_channel,
               tor.training_order_time,
               tor.remark,
               ns.nurse_station_name
        from training_order tor
                 left join training_order_details tod ON tor.training_order_no = tod.training_order_no
                 left join nurse_station ns on tor.nurse_station_id = ns.id
        where tor.id = #{id}
          AND tor.del_flag = 0
          AND tod.del_flag = 0
    </select>

    <select id="selectRefundInformation" resultType="com.xinelu.manage.vo.trainingorder.TrainingOrderVO">
        select tor.nurse_person_name,
               tor.training_order_no,
               tor.training_order_status,
               tor.training_order_amount,
               (select training_item_title
                from training_order_details
                where training_order_no = tor.training_order_no
                  and training_item_type = #{trainingItemType}) trainingItemTitle,
               ns.nurse_station_name
        from training_order tor
                 left join nurse_station ns on tor.nurse_station_id = ns.id
        where tor.id = #{id}
    </select>
</mapper>
