<?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.applet.mapper.nursepersonapplogin.NursePersonAppLoginMapper">

    <resultMap type="com.xinelu.applet.vo.nursepersonapplogin.NurseStationPersonUserVO" id="NursePersonVO">
        <result property="id" column="id"/>
        <result property="nurseStationIds" column="nurse_station_ids"/>
        <result property="departmentCode" column="department_code"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="avatar" column="avatar"/>
        <result property="nursePersonCode" column="nurse_person_code"/>
        <result property="nursePersonName" column="nurse_person_name"/>
        <result property="nursePersonType" column="nurse_person_type"/>
        <result property="sex" column="sex"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="birthDate" column="birth_date"/>
        <result property="openid" column="openid"/>
        <result property="unionid" column="unionid"/>
        <result property="evaluateStarCount" column="evaluate_star_count"/>
        <result property="positionalTitleLevel" column="positional_title_level"/>
        <result property="geniusItem" column="genius_item"/>
        <result property="totalRevenueAmount" column="total_revenue_amount"/>
        <result property="workStatus" column="work_status"/>
        <result property="modifyCheckStatus" column="modify_check_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="nurseStationPersonCertificateList" javaType="java.util.List"
                    resultMap="NursePersonCertificateInfoById"/>
    </resultMap>

    <resultMap type="NurseStationPersonCertificate" id="NursePersonCertificateInfoById">
        <result property="id" column="certificateId"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateUrl" column="certificate_url"/>
        <result property="sort" column="sort"/>
    </resultMap>

    <select id="selectOrderByNursePersonCount"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.OrderByNursePersonVO">
        SELECT
        order_status
        FROM appointment_order
        <where>
            <if test="nurseStationPersonId != null">
                nurse_station_person_id = #{nurseStationPersonId}
            </if>
            <if test="orderStatusList != null and orderStatusList != ''">
                AND order_status in
                <foreach collection="orderStatusList" item="orderStatusList" open="(" separator="," close=")">
                    #{orderStatusList}
                </foreach>
            </if>
            and del_flag = 0
        </where>
    </select>


    <select id="selectOrderByNursePersonId"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.OrderAndItemVO">
        SELECT
        ao.id,
        aod.order_no,
        aod.order_count,
        aod.order_status,
        ao.order_commission_amount,
        aod.appointment_status,
        aod.service_date,
        aod.service_start_time,
        aod.service_end_time,
        aod.total_price,
        aod.nurse_item_name,
        aod.item_serve_duration_unit,
        aod.create_time,
        aod.service_address,
        nsi.item_picture_url
        FROM
        appointment_order ao
        LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
        LEFT JOIN nurse_station_item nsi ON nsi.id = aod.nurse_station_item_id
        <where>
            <if test="nursePersonId != null">
                ao.nurse_station_person_id = #{nursePersonId}
            </if>
            <!-- 已完成订单状态和未完成的订单状态 -->
            <if test="orderStatusList != null and orderStatusList != ''">
                AND ao.order_status in
                <foreach collection="orderStatusList" item="orderStatus" open="(" separator="," close=")">
                    #{orderStatus}
                </foreach>
            </if>
            <if test="nurseItemName != null and nurseItemName != ''">
                AND aod.nurse_item_name like concat('%', #{nurseItemName}, '%')
            </if>
            and ao.del_flag = 0
            and aod.del_flag = 0
        </where>
        ORDER BY aod.order_status DESC
    </select>

    <select id="getOrderDetailsAndPatientByDetailsId"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.OrderDetailsAndPatientVO">
        SELECT
        ao.id appointmentOrderId,
        aod.id orderDetailsId,
        aod.order_no,
        aod.service_address,
        aod.order_count,
        aod.service_date,
        aod.service_start_time,
        aod.service_end_time,
        aod.total_price,
        aod.nurse_item_name,
        aod.item_serve_duration_unit,
        aod.order_status,
        ao.order_commission_amount,
        nsi.item_picture_url,
        pi.patient_name,
        pi.birth_date,
        pi.phone,
        IFNULL(pi.address, '') address,
        pi.home_longitude,
        pi.home_latitude,
        aod.disabling_condition,
        aod.disabling_reason,
        aod.on_duty_picture_url,
        aod.service_end_picture_url,
        aod.user_signature_picture_url,
        aod.service_progress_video_url
        FROM appointment_order ao
        LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
        LEFT JOIN patient_info pi ON pi.id = ao.patient_id
        LEFT JOIN nurse_station_item nsi ON nsi.id = aod.nurse_station_item_id
        <where>
            <if test="orderNo != null  and orderNo != ''">
                AND ao.order_no = #{orderNo}
            </if>
            and aod.del_flag = 0
            and ao.del_flag = 0
        </where>
    </select>

    <update id="updateAppointmentOrderByOrderNo">
        UPDATE appointment_order a,
            appointment_order_details b
        SET a.order_status = #{orderStatus},
            a.task_return_reason = #{taskReturnReason},
            a.nurse_station_person_id = null,
            b.order_status = #{orderStatus},
            a.update_time = now(),
            b.update_time = now()
        WHERE
            a.order_no = b.order_no
          AND a.del_flag = 0
          AND a.order_status &lt;&gt; #{orderStatus}
          AND b.order_status &lt;&gt; #{orderStatus}
          AND a.order_no = #{orderNo}
    </update>

    <select id="getSysUserPasswordByPhone" resultType="int">
        SELECT
        count( 1 )
        FROM
        sys_user su
        LEFT JOIN nurse_station_person nsp ON su.phonenumber = nsp.phone
        <where>
            <if test="phonenumber != null and phonenumber != ''">
                and su.phonenumber = #{phonenumber}
            </if>
            <if test="stationPersonPassword != null and stationPersonPassword != ''">
                and su.station_person_password = #{stationPersonPassword}
            </if>
            and su.del_flag = 0
        </where>
    </select>

    <insert id="insertNurseAppLoginSysUser"
            parameterType="com.xinelu.applet.vo.nursepersonapplogin.NurseAppLoginSysUserVO"
            useGeneratedKeys="true" keyProperty="userId">
        insert into sys_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null ">user_name,
            </if>
            <if test="nickName != null ">nick_name,
            </if>
            <if test="phonenumber != null ">phonenumber,
            </if>
            <if test="stationPersonPassword != null">station_person_password,
            </if>
            <if test="nurseStationIds != null">nurse_station_ids,
            </if>
            <if test="nurseStationPersonId != null">nurse_station_person_id,
            </if>
            <if test="createTime != null">create_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userName != null ">#{userName},
            </if>
            <if test="nickName != null ">#{nickName},
            </if>
            <if test="phonenumber != null ">#{phonenumber},
            </if>
            <if test="stationPersonPassword != null ">#{stationPersonPassword},
            </if>
            <if test="nurseStationPersonId != null ">#{nurseStationPersonId},
            </if>
            <if test="nurseStationIds != null ">#{nurseStationIds},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
        </trim>
    </insert>


    <update id="updateUserPassWord" parameterType="SysUser">
        update sys_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName != null ">user_name = #{userName},
            </if>
            <if test="nickName != null ">nick_name = #{nickName},
            </if>
            <if test="phonenumber != null ">phonenumber = #{phonenumber},
            </if>
            <if test="stationPersonPassword != null ">station_person_password = #{stationPersonPassword},
            </if>
            <if test="avatar != null ">avatar = #{avatar},
            </if>
        </trim>
        where user_id = #{userId}
    </update>


    <select id="getUserAndPerson"
            parameterType="com.xinelu.applet.vo.nursepersonapplogin.NursePersonAppLoginsUserVO"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.NursePersonAppLoginsUserVO">
        SELECT
        su.user_id,
        su.user_name,
        su.nick_name,
        su.phonenumber,
        su.nurse_station_ids,
        su.station_person_password,
        nsp.id nursePersonId,
        nsp.nurse_station_id,
        nsp.nurse_person_name,
        nsp.phone
        FROM
        sys_user su
        LEFT JOIN nurse_station_person nsp ON su.phonenumber = nsp.phone
        <where>
            <if test="phonenumber != null and phonenumber != ''">
                and su.phonenumber = #{phonenumber}
            </if>
            <if test="stationPersonPassword != null and stationPersonPassword != ''">
                and su.station_person_password = #{stationPersonPassword}
            </if>
            and su.del_flag = 0
        </where>
    </select>

    <select id="getFromPhoneUserId" resultType="Long">
        select
        user_id
        from sys_user
        <where>
            <if test="phonenumber != null and phonenumber != ''">
                phonenumber = #{phonenumber}
            </if>
            and del_flag = 0
        </where>
    </select>

    <select id="getNursePersonPersonalInformation"
            parameterType="com.xinelu.applet.vo.nursepersonapplogin.NursePersonAppLoginsUserVO"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.NursePersonAppLoginsUserVO">
        SELECT
        su.user_id,
        su.user_name,
        su.nick_name,
        su.phonenumber,
        su.avatar,
        su.nurse_station_ids
        FROM
        sys_user su
        <where>
            <if test="phonenumber != null and phonenumber != ''">
                and su.phonenumber = #{phonenumber}
            </if>
            <if test="stationPersonPassword != null and stationPersonPassword != ''">
                and su.station_person_password = #{stationPersonPassword}
            </if>
            and su.del_flag = 0
        </where>
    </select>

    <update id="updateHeadAvatarHeadUserId" parameterType="SysUser">
        update sys_user
        set avatar = #{avatar}
        where user_id = #{userId}
    </update>


    <select id="getPersonNurseStationLists"
            parameterType="com.xinelu.applet.vo.nursepersonapplogin.PersonNurseStationAppVO"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.PersonNurseStationAppVO">
        select id,
        area_code,
        user_id,
        nurse_station_code,
        nurse_station_name,
        nurse_station_type,
        longitude,
        latitude,
        phone,
        address,
        duty_person,
        duty_phone,
        create_by,
        create_time,
        update_by,
        update_time
        from nurse_station
        <where>
            <if test="areaCode != null ">
                and area_code = #{areaCode}
            </if>
            <if test="id != null ">
                and id = #{id}
            </if>
            <if test="userId != null ">
                and user_id = #{userId}
            </if>
            <if test="nurseStationCode != null  and nurseStationCode != ''">
                and nurse_station_code like concat('%', #{nurseStationCode}, '%')
            </if>
            <if test="nurseStationName != null  and nurseStationName != ''">
                and nurse_station_name like concat('%', #{nurseStationName}, '%')
            </if>
            <if test="nurseStationType != null  and nurseStationType != ''">
                and find_in_set(#{nurseStationType},nurse_station_type)
            </if>
        </where>
    </select>

    <select id="getNursePersonUserId" parameterType="Long"
            resultType="com.xinelu.applet.vo.nursepersonapplogin.NursePersonAppLoginsUserVO">
        SELECT
        su.user_id,
        su.user_name,
        su.nick_name,
        su.phonenumber,
        su.avatar,
        su.nurse_station_ids
        FROM
        sys_user su
        <where>
            <if test="userId != null ">
                and su.user_id = #{userId}
            </if>
            and su.del_flag = 0
        </where>
    </select>

    <select id="getNurseStationPersonById"
            parameterType="integer"
            resultMap="NursePersonVO">
        SELECT
        su.user_id,
        su.user_name,
        su.nick_name,
        su.phonenumber,
        su.avatar,
        su.nurse_station_ids,
        nsp.id nursePersonId,
        nsp.department_code,
        nsp.nurse_person_name,
        nsp.nurse_person_type,
        nsp.sex,
        nsp.phone,
        nsp.address,
        nsp.birth_date,
        nsp.openid,
        nsp.unionid,
        nsp.evaluate_star_count,
        nsp.positional_title_level,
        nsp.genius_item,
        nsp.total_revenue_amount,
        nsp.work_status,
        nsp.modify_check_status,
        nsp.create_by,
        nsp.create_time,
        nsp.update_by,
        nsp.update_time
        -- nspc.id certificateId,
        -- nspc.certificate_name,
        -- nspc.certificate_url,
        -- nspc.sort
        FROM
        nurse_station_person nsp
        LEFT JOIN sys_user su ON su.phonenumber = nsp.phone
        -- LEFT JOIN nurse_station_person_certificate nspc ON nspc.nurse_station_person_id = nsp.id
        <where>
            <if test="nursePersonId != null and nursePersonId != ''">
                and nsp.id = #{nursePersonId}
            </if>
            and su.del_flag = 0
        </where>
        -- ORDER BY nspc.sort
    </select>
</mapper>
