<?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.nursestationperson.NurseStationPersonMapper">

    <resultMap type="NurseStationPerson" id="NurseStationPersonResult">
        <result property="id" column="id"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="departmentCode" column="department_code"/>
        <result property="userId" column="user_id"/>
        <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="positionalTitleLevelCheck" column="positional_title_level_check"/>
        <result property="geniusItemCheck" column="genius_item_check"/>
        <result property="modifyInfoFlag" column="modify_info_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.nursestationperson.NurseStationPersonVO" id="NursePersonVO">
        <result property="id" column="id"/>
        <result property="nurseStationName" column="nurse_station_name"/>
        <result property="departmentName" column="department_name"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="departmentCode" column="department_code"/>
        <result property="userId" column="user_id"/>
        <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="positionalTitleLevelCheck" column="positional_title_level_check"/>
        <result property="geniusItemCheck" column="genius_item_check"/>
        <result property="modifyInfoFlag" column="modify_info_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"/>
        <collection property="nurseStationPersonCertificateList" javaType="java.util.List"
                    resultMap="NursePersonCertificateInfoById"/>
    </resultMap>

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


    <resultMap type="com.xinelu.manage.dto.nursestationperson.NurseStationPersonCheckDTO"
               id="NursePersonCertificateDTO">
        <result property="id" column="id"/>
        <result property="nurseStationName" column="nurse_station_name"/>
        <result property="departmentName" column="department_name"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="departmentCode" column="department_code"/>
        <result property="userId" column="user_id"/>
        <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="positionalTitleLevelCheck" column="positional_title_level_check"/>
        <result property="geniusItemCheck" column="genius_item_check"/>
        <result property="modifyInfoFlag" column="modify_info_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"/>
        <collection property="nurseStationPersonCheckList" javaType="java.util.List"
                    resultMap="NursePersonCheckVOInfoById"/>
    </resultMap>

    <resultMap type="NurseStationPersonCheck" id="NursePersonCheckVOInfoById">
        <result property="id" column="certificateId"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="certificateUrl" column="certificate_url"/>
        <result property="sort" column="sort"/>
    </resultMap>


    <sql id="selectNurseStationPersonVo">
        select id,
               nurse_station_id,
               department_code,
               user_id,
               nurse_person_code,
               nurse_person_name,
               nurse_person_type,
               sex,
               phone,
               address,
               birth_date,
               openid,
               unionid,
               evaluate_star_count,
               positional_title_level,
               genius_item,
               total_revenue_amount,
               work_status,
               modify_check_status,
               positional_title_level_check,
               genius_item_check,
               modify_info_flag,
               create_by,
               create_time,
               update_by,
               update_time
        from nurse_station_person
    </sql>

    <select id="selectNurseStationPersonList" parameterType="NurseStationPerson" resultMap="NurseStationPersonResult">
        <include refid="selectNurseStationPersonVo"/>
        <where>
            <if test="nurseStationId != null ">
                and nurse_station_id = #{nurseStationId}
            </if>
            <if test="departmentCode != null  and departmentCode != ''">
                and department_code = #{departmentCode}
            </if>
            <if test="userId != null ">
                and user_id = #{userId}
            </if>
            <if test="nursePersonCode != null  and nursePersonCode != ''">
                and nurse_person_code = #{nursePersonCode}
            </if>
            <if test="nursePersonName != null  and nursePersonName != ''">
                and nurse_person_name like concat('%', #{nursePersonName}, '%')
            </if>
            <if test="nursePersonType != null  and nursePersonType != ''">
                and nurse_person_type = #{nursePersonType}
            </if>
            <if test="sex != null  and sex != ''">
                and sex = #{sex}
            </if>
            <if test="phone != null  and phone != ''">
                and phone = #{phone}
            </if>
            <if test="address != null  and address != ''">
                and address = #{address}
            </if>
            <if test="birthDate != null ">
                and birth_date = #{birthDate}
            </if>
            <if test="openid != null  and openid != ''">
                and openid = #{openid}
            </if>
            <if test="unionid != null  and unionid != ''">
                and unionid = #{unionid}
            </if>
            <if test="evaluateStarCount != null ">
                and evaluate_star_count = #{evaluateStarCount}
            </if>
            <if test="positionalTitleLevel != null  and positionalTitleLevel != ''">
                and positional_title_level = #{positionalTitleLevel}
            </if>
            <if test="geniusItem != null  and geniusItem != ''">
                and genius_item = #{geniusItem}
            </if>
            <if test="totalRevenueAmount != null ">
                and total_revenue_amount = #{totalRevenueAmount}
            </if>
            <if test="workStatus != null  and workStatus != ''">
                and work_status = #{workStatus}
            </if>
            <if test="modifyCheckStatus != null  and modifyCheckStatus != ''">
                and modify_check_status = #{modifyCheckStatus}
            </if>
            <if test="positionalTitleLevelCheck != null  and positionalTitleLevelCheck != ''">
                and positional_title_level_check = #{positionalTitleLevelCheck}
            </if>
            <if test="geniusItemCheck != null  and geniusItemCheck != ''">
                and genius_item_check = #{geniusItemCheck}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>

    <select id="selectNurseStationPersonById" parameterType="Long"
            resultMap="NursePersonVO">
        SELECT ns.nurse_station_name,
               nsd.department_name,
               nsp.id,
               nsp.nurse_station_id,
               nsp.department_code,
               nsp.user_id,
               nsp.nurse_person_code,
               nsp.nurse_person_name,
               nsp.nurse_person_type,
               nsp.sex,
               nsp.phone,
               nsp.address,
               nsp.create_by,
               nsp.create_time,
               nsp.update_by,
               nsp.update_time,
               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.positional_title_level_check,
               nsp.genius_item_check,
               nsp.modify_info_flag,
               nspc.id certificateId,
               nspc.nurse_station_person_id,
               nspc.certificate_name,
               nspc.certificate_code,
               nspc.sort,
               nspc.certificate_url
        FROM nurse_station_person nsp
                 LEFT JOIN nurse_station ns ON ns.id = nsp.nurse_station_id
                 LEFT JOIN nurse_station_department nsd ON nsp.department_code = nsd.department_code
                 LEFT JOIN nurse_station_person_certificate nspc ON nsp.id = nspc.nurse_station_person_id
        where nsp.id = #{id}
    </select>

    <insert id="insertNurseStationPerson" parameterType="NurseStationPerson" useGeneratedKeys="true"
            keyProperty="id">
        insert into nurse_station_person
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nurseStationId != null">nurse_station_id,
            </if>
            <if test="departmentCode != null">department_code,
            </if>
            <if test="userId != null">user_id,
            </if>
            <if test="nursePersonCode != null">nurse_person_code,
            </if>
            <if test="nursePersonName != null">nurse_person_name,
            </if>
            <if test="nursePersonType != null">nurse_person_type,
            </if>
            <if test="sex != null">sex,
            </if>
            <if test="phone != null">phone,
            </if>
            <if test="address != null">address,
            </if>
            <if test="birthDate != null">birth_date,
            </if>
            <if test="openid != null">openid,
            </if>
            <if test="unionid != null">unionid,
            </if>
            <if test="evaluateStarCount != null">evaluate_star_count,
            </if>
            <if test="positionalTitleLevel != null">positional_title_level,
            </if>
            <if test="geniusItem != null">genius_item,
            </if>
            <if test="totalRevenueAmount != null">total_revenue_amount,
            </if>
            <if test="workStatus != null">work_status,
            </if>
            <if test="modifyCheckStatus != null">modify_check_status,
            </if>
            <if test="positionalTitleLevelCheck != null">positional_title_level_check,
            </if>
            <if test="geniusItemCheck != null">genius_item_check,
            </if>
            <if test="modifyInfoFlag != null">modify_info_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="departmentCode != null">#{departmentCode},
            </if>
            <if test="userId != null">#{userId},
            </if>
            <if test="nursePersonCode != null">#{nursePersonCode},
            </if>
            <if test="nursePersonName != null">#{nursePersonName},
            </if>
            <if test="nursePersonType != null">#{nursePersonType},
            </if>
            <if test="sex != null">#{sex},
            </if>
            <if test="phone != null">#{phone},
            </if>
            <if test="address != null">#{address},
            </if>
            <if test="birthDate != null">#{birthDate},
            </if>
            <if test="openid != null">#{openid},
            </if>
            <if test="unionid != null">#{unionid},
            </if>
            <if test="evaluateStarCount != null">#{evaluateStarCount},
            </if>
            <if test="positionalTitleLevel != null">#{positionalTitleLevel},
            </if>
            <if test="geniusItem != null">#{geniusItem},
            </if>
            <if test="totalRevenueAmount != null">#{totalRevenueAmount},
            </if>
            <if test="workStatus != null">#{workStatus},
            </if>
            <if test="modifyCheckStatus != null">#{modifyCheckStatus},
            </if>
            <if test="positionalTitleLevelCheck != null">#{positionalTitleLevelCheck},
            </if>
            <if test="geniusItemCheck != null">#{geniusItemCheck},
            </if>
            <if test="modifyInfoFlag != null">#{modifyInfoFlag},
            </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="updateNurseStationPerson" parameterType="NurseStationPerson">
        update nurse_station_person
        <trim prefix="SET" suffixOverrides=",">
            <if test="nurseStationId != null">nurse_station_id =
                #{nurseStationId},
            </if>
            <if test="departmentCode != null">department_code =
                #{departmentCode},
            </if>
            <if test="userId != null">user_id =
                #{userId},
            </if>
            <if test="nursePersonCode != null">nurse_person_code =
                #{nursePersonCode},
            </if>
            <if test="nursePersonName != null">nurse_person_name =
                #{nursePersonName},
            </if>
            <if test="nursePersonType != null">nurse_person_type =
                #{nursePersonType},
            </if>
            <if test="sex != null">sex =
                #{sex},
            </if>
            <if test="phone != null">phone =
                #{phone},
            </if>
            <if test="address != null">address =
                #{address},
            </if>
            <if test="birthDate != null">birth_date =
                #{birthDate},
            </if>
            <if test="openid != null">openid =
                #{openid},
            </if>
            <if test="unionid != null">unionid =
                #{unionid},
            </if>
            <if test="evaluateStarCount != null">evaluate_star_count =
                #{evaluateStarCount},
            </if>
            <if test="positionalTitleLevel != null">positional_title_level =
                #{positionalTitleLevel},
            </if>
            <if test="geniusItem != null">genius_item =
                #{geniusItem},
            </if>
            <if test="totalRevenueAmount != null">total_revenue_amount =
                #{totalRevenueAmount},
            </if>
            <if test="workStatus != null">work_status =
                #{workStatus},
            </if>
            <if test="modifyCheckStatus != null">modify_check_status =
                #{modifyCheckStatus},
            </if>
            <if test="positionalTitleLevelCheck != null">positional_title_level_check =
                #{positionalTitleLevelCheck},
            </if>
            <if test="geniusItemCheck != null">genius_item_check =
                #{geniusItemCheck},
            </if>
            <if test="modifyInfoFlag != null">modify_info_flag =
                #{modifyInfoFlag},
            </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="deleteNurseStationPersonById" parameterType="Long">
        delete
        from nurse_station_person
        where id = #{id}
    </delete>

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

    <insert id="insertNurseStationPersonList" parameterType="java.util.List">
        insert into nurse_station_person(
        nurse_station_id,
        department_code,
        user_id,
        nurse_person_code,
        nurse_person_name,
        nurse_person_type,
        sex,
        phone,
        address,
        birth_date,
        openid,
        unionid,
        evaluate_star_count,
        positional_title_level,
        genius_item,
        total_revenue_amount,
        work_status,
        modify_check_status,
        positional_title_level_check,
        genius_item_check,
        modify_info_flag,
        create_by,
        create_time,
        update_by,
        update_time
        ) values
        <foreach item="nurseStationPerson" index="index" collection="list" separator=",">
            (
            #{nurseStationPerson.nurseStationId},
            #{nurseStationPerson.departmentCode},
            #{nurseStationPerson.userId},
            #{nurseStationPerson.nursePersonCode},
            #{nurseStationPerson.nursePersonName},
            #{nurseStationPerson.nursePersonType},
            #{nurseStationPerson.sex},
            #{nurseStationPerson.phone},
            #{nurseStationPerson.address},
            #{nurseStationPerson.birthDate},
            #{nurseStationPerson.openid},
            #{nurseStationPerson.unionid},
            #{nurseStationPerson.evaluateStarCount},
            #{nurseStationPerson.positionalTitleLevel},
            #{nurseStationPerson.geniusItem},
            #{nurseStationPerson.totalRevenueAmount},
            #{nurseStationPerson.workStatus},
            #{nurseStationPerson.modifyCheckStatus},
            #{nurseStationPerson.positionalTitlLlevelCheck},
            #{nurseStationPerson.geniusItemCheck},
            #{nurseStationPerson.modifyInfoFlag},
            #{nurseStationPerson.createBy},
            #{nurseStationPerson.createTime},
            #{nurseStationPerson.updateBy},
            #{nurseStationPerson.updateTime}
            )
        </foreach>
    </insert>

    <select id="getNurseStationPersonList" parameterType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonVO"
            resultType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonVO">
        SELECT
        ns.nurse_station_name,
        nsd.department_name,
        nsp.id,
        nsp.nurse_station_id,
        nsp.department_code,
        nsp.user_id,
        nsp.nurse_person_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.positional_title_level_check,
        nsp.genius_item_check,
        nsp.modify_info_flag,
        nsp.create_by,
        nsp.create_time,
        nsp.update_by,
        nsp.update_time
        FROM
        nurse_station_person nsp
        LEFT JOIN nurse_station ns ON ns.id = nsp.nurse_station_id
        LEFT JOIN nurse_station_department nsd ON nsp.department_code = nsd.department_code
        <where>
            <if test="nurseStationId != null ">
                and nsp.nurse_station_id = #{nurseStationId}
            </if>
            <if test="nurseStationName != null  and nurseStationName != ''">
                and ns.nurse_station_name like concat('%', #{nurseStationName}, '%')
            </if>
            <if test="departmentName != null  and departmentName != ''">
                and nsd.department_name like concat('%', #{departmentName}, '%')
            </if>
            <if test="nursePersonName != null  and nursePersonName != ''">
                and nsp.nurse_person_name like concat('%', #{nursePersonName}, '%')
            </if>
            <if test="departmentCode != null  and departmentCode != ''">
                and nsp.department_code = #{departmentCode}
            </if>
            <if test="userId != null ">
                and nsp.user_id = #{userId}
            </if>
            <if test="nursePersonCode != null  and nursePersonCode != ''">
                and nsp.nurse_person_code = #{nursePersonCode}
            </if>
            <if test="nursePersonType != null  and nursePersonType != ''">
                and nsp.nurse_person_type = #{nursePersonType}
            </if>
            <if test="sex != null  and sex != ''">
                and nsp.sex = #{sex}
            </if>
            <if test="phone != null  and phone != ''">
                and nsp.phone = #{phone}
            </if>
            <if test="address != null  and address != ''">
                and nsp.address = #{address}
            </if>
            <if test="birthDate != null ">
                and nsp.birth_date = #{birthDate}
            </if>
            <if test="openid != null  and openid != ''">
                and nsp.openid = #{openid}
            </if>
            <if test="unionid != null  and unionid != ''">
                and nsp.unionid = #{unionid}
            </if>
            <if test="evaluateStarCount != null ">
                and nsp.evaluate_star_count = #{evaluateStarCount}
            </if>
            <if test="positionalTitleLevel != null  and positionalTitleLevel != ''">
                and nsp.positional_title_level = #{positionalTitleLevel}
            </if>
            <if test="geniusItem != null  and geniusItem != ''">
                and nsp.genius_item = #{geniusItem}
            </if>
            <if test="totalRevenueAmount != null ">
                and nsp.total_revenue_amount = #{totalRevenueAmount}
            </if>
            <if test="workStatus != null  and workStatus != ''">
                and nsp.work_status = #{workStatus}
            </if>
            <if test="modifyCheckStatus != null  and modifyCheckStatus != ''">
                and nsp.modify_check_status = #{modifyCheckStatus}
            </if>
            <if test="positionalTitleLevelCheck != null  and positionalTitleLevelCheck != ''">
                and nsp.positional_title_level_check = #{positionalTitleLevelCheck}
            </if>
            <if test="geniusItemCheck != null  and geniusItemCheck != ''">
                and nsp.genius_item_check = #{geniusItemCheck}
            </if>
        </where>
        ORDER BY
        nsp.create_time DESC
    </select>

    <insert id="insertNurseStationPersonImportList" parameterType="java.util.List">
        insert into nurse_station_person(
        nurse_station_id,
        department_code,
        user_id,
        nurse_person_code,
        nurse_person_name,
        nurse_person_type,
        sex,
        phone,
        address,
        birth_date,
        openid,
        unionid,
        evaluate_star_count,
        positional_title_level,
        genius_item,
        total_revenue_amount,
        work_status,
        modify_check_status,
        positional_title_level_check,
        genius_item_check,
        modify_info_flag,
        create_by,
        create_time,
        update_by,
        update_time
        ) values
        <foreach item="nurseStationPerson" index="index" collection="list" separator=",">
            (
            #{nurseStationPerson.nurseStationId},
            #{nurseStationPerson.departmentCode},
            #{nurseStationPerson.userId},
            #{nurseStationPerson.nursePersonCode},
            #{nurseStationPerson.nursePersonName},
            #{nurseStationPerson.nursePersonType},
            #{nurseStationPerson.sex},
            #{nurseStationPerson.phone},
            #{nurseStationPerson.address},
            #{nurseStationPerson.birthDate},
            #{nurseStationPerson.openid},
            #{nurseStationPerson.unionid},
            #{nurseStationPerson.evaluateStarCount},
            #{nurseStationPerson.positionalTitleLevel},
            #{nurseStationPerson.geniusItem},
            #{nurseStationPerson.totalRevenueAmount},
            #{nurseStationPerson.workStatus},
            #{nurseStationPerson.modifyCheckStatus},
            #{nurseStationPerson.positionalTitlLlevelCheck},
            #{nurseStationPerson.geniusItemCheck},
            #{nurseStationPerson.modifyInfoFlag},
            #{nurseStationPerson.createBy},
            #{nurseStationPerson.createTime},
            #{nurseStationPerson.updateBy},
            #{nurseStationPerson.updateTime}
            )
        </foreach>
    </insert>

    <select id="getNurseStationPersonPhone" resultType="int">
        SELECT
        count( 1 )
        FROM
        nurse_station_person
        <where>
            <if test="phone != null">
                phone = #{phone}
            </if>
        </where>
    </select>

    <select id="getAllNurseStationPersonInfo"
            resultType="com.xinelu.manage.dto.nursestationperson.NurseStationPersonImportDTO">
        SELECT
        nsp.nurse_person_name,
        nsp.sex,
        nsp.phone,
        nsp.address,
        nsp.nurse_station_id,
        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.positional_title_level_check,
        nsp.genius_item_check,
        nsp.modify_info_flag,
        ns.nurse_station_name
        FROM
        nurse_station_person nsp
        LEFT JOIN nurse_station ns ON nsp.nurse_station_id = ns.id
        <where>
            <if test="phoneList != null and phoneList.size() > 0">
                and nsp.phone in
                <foreach item="phone" collection="phoneList" open="(" separator="," close=")">
                    #{phone}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getNurseStationPersonPhoneUser" parameterType="NurseStationPerson" resultType="NurseStationPerson">
        select id,
        nurse_station_id,
        department_code,
        user_id,
        nurse_person_code,
        nurse_person_name,
        nurse_person_type,
        sex,
        phone,
        address,
        birth_date,
        openid,
        unionid,
        evaluate_star_count,
        positional_title_level,
        genius_item,
        total_revenue_amount,
        work_status,
        modify_check_status,
        positional_title_level_check,
        genius_item_check,
        modify_info_flag,
        create_by,
        create_time,
        update_by,
        update_time
        from nurse_station_person
        <where>
            <if test="phone != null">
                phone = #{phone}
            </if>
        </where>
    </select>

    <select id="getAllNurseStationPersonIdByPhone"
            resultType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonVO">
        SELECT
        id,
        nurse_station_id,
        nurse_person_name,
        sex,
        phone
        FROM
        nurse_station_person
        <where>
            <if test="personPhoneList != null and personPhoneList.size() > 0">
                and phone in
                <foreach item="phone" collection="personPhoneList" open="(" separator="," close=")">
                    #{phone}
                </foreach>
            </if>
        </where>
    </select>

    <insert id="insertSysUserList" parameterType="java.util.List">
        insert into sys_user(
        user_name,
        nick_name,
        sex,
        phonenumber,
        password,
        del_flag,
        status,
        station_person_password,
        nurse_station_person_id,
        nurse_station_ids,
        create_by,
        create_time
        ) values
        <foreach item="sysUsers" index="index" collection="list" separator=",">
            (
            #{sysUsers.userName},
            #{sysUsers.nickName},
            #{sysUsers.sex},
            #{sysUsers.phonenumber},
            #{sysUsers.password},
            #{sysUsers.delFlag},
            #{sysUsers.status},
            #{sysUsers.stationPersonPassword},
            #{sysUsers.nurseStationPersonId},
            #{sysUsers.nurseStationIds},
            #{sysUsers.createBy},
            #{sysUsers.createTime}
            )
        </foreach>
    </insert>


    <insert id="insertNurseStationPersonSysUser"
            parameterType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonSysUserVO"
            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="sex != null">sex,
            </if>
            <if test="status != null">status,
            </if>
            <if test="password != null">password,
            </if>
            <if test="delFlag != null">del_flag,
            </if>
            <if test="createBy != null">create_by,
            </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="nurseStationIds != null ">#{nurseStationIds},
            </if>
            <if test="nurseStationPersonId != null ">#{nurseStationPersonId},
            </if>
            <if test="sex != null ">#{sex},
            </if>
            <if test="status != null ">#{status},
            </if>
            <if test="password != null">#{password},
            </if>
            <if test="delFlag != null ">#{delFlag},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
        </trim>
    </insert>

    <update id="updateNurseStationPersonSysUser"
            parameterType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonSysUserVO">
        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="sex != null ">sex = #{sex},
            </if>
            <if test="delFlag != null ">del_flag = #{delFlag},
            </if>
            <if test="updateBy != null">update_by = #{updateBy},
            </if>
            <if test="updateTime != null">update_time =#{updateTime},
            </if>
        </trim>
        where user_id = #{userId}
    </update>


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

    <select id="getNurseStationUserId" resultType="Long">
        SELECT
        su.user_id
        FROM
        sys_user su
        <where>
            <if test="phone != null and phone != ''">
                su.phonenumber = #{phone}
            </if>
        </where>
    </select>

    <select id="getNurseStationPersonInfo" parameterType="long" resultType="nurseStationPerson">
        <include refid="selectNurseStationPersonVo"/>
        where id = #{id}
    </select>

    <select id="getNurseStationUserCount" resultType="int">
        SELECT
        count(1)
        FROM
        sys_user su
        <where>
            <if test="phone != null and phone != ''">
                su.phonenumber = #{phone}
            </if>
            and su.del_flag = 0
        </where>
    </select>


    <select id="getSysUserAndPerson"
            parameterType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonSysUserVO"
            resultType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonSysUserVO">
        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>
            and su.del_flag = 0
        </where>
    </select>


    <select id="selectSysUserIdByPhone"
            resultType="com.xinelu.manage.vo.nursestationperson.NurseStationPersonVO">
        SELECT
        nurse_station_person_id id,
        phonenumber phone
        FROM sys_user
        <where>
            <if test="personPhoneList != null and personPhoneList.size() > 0">
                and phonenumber in
                <foreach item="phonenumber" collection="personPhoneList" open="(" separator="," close=")">
                    #{phonenumber}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectNurseStationPersonOpenid" parameterType="string" resultMap="NurseStationPersonResult">
        <include refid="selectNurseStationPersonVo"/>
        <where>
            <if test="openid != null  and openid != ''">
                and openid = #{openid}
            </if>
        </where>
    </select>

    <update id="updateNursePersonCheck" parameterType="NurseStationPerson">
        update nurse_station_person
        <trim prefix="SET" suffixOverrides=",">
            <if test="positionalTitleLevelCheck != null">positional_title_level_check =
                #{positionalTitleLevelCheck},
            </if>
            <if test="geniusItemCheck != null">genius_item_check =
                #{geniusItemCheck},
            </if>
            <if test="modifyCheckStatus != null">modify_check_status =
                #{modifyCheckStatus},
            </if>
            <if test="modifyInfoFlag != null">modify_info_flag =
                #{modifyInfoFlag},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>


    <update id="updateNursePersonWorkStatus" parameterType="NurseStationPerson">
        update nurse_station_person
        <trim prefix="SET" suffixOverrides=",">
            <if test="workStatus != null">work_status =
                #{workStatus},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>


    <select id="selectAvgEvaluateSatisfaction"
            resultType="integer">
        SELECT
        IFNULL(AVG(oei.composite_score),0) evaluateSatisfaction
        FROM
        nurse_station_person nsp
        INNER JOIN appointment_order ao ON ao.nurse_station_person_id = nsp.id
        INNER JOIN order_evaluate_info oei ON oei.order_no = ao.order_no
        <where>
            <if test="nurseStationPersonId != null ">
                and nsp.id = #{nurseStationPersonId}
            </if>
        </where>
    </select>

    <select id="getNurseStationPersonCheck"
            resultMap="NursePersonCertificateDTO">
        SELECT
        nsp.id,
        nsp.nurse_person_name,
        nsp.evaluate_star_count,
        nsp.total_revenue_amount,
        nsp.work_status,
        nsp.modify_check_status,
        nsp.positional_title_level_check,
        nsp.genius_item_check,
        nsp.modify_info_flag,
        nspc.id certificateId,
        nspc.nurse_station_person_id,
        nspc.certificate_name,
        nspc.certificate_code,
        nspc.sort,
        nspc.certificate_url
        FROM nurse_station_person nsp
        LEFT JOIN nurse_station_person_check nspc ON nsp.id = nspc.nurse_station_person_id
        <where>
            <if test="nurseStationPersonId != null ">
                and nsp.id = #{nurseStationPersonId}
            </if>
        </where>
    </select>

    <select id="getNurseStationPersonSysUserRole" resultType="int">
        SELECT
        COUNT( 1 )
        FROM
        sys_user su
        LEFT JOIN sys_user_role sur ON sur.user_id = su.user_id
        <where>
            <if test="roleId != null ">
                and sur.role_id = #{roleId}
            </if>
            <if test="nurseStationPersonId != null ">
                and su.nurse_station_person_id = #{nurseStationPersonId}
            </if>
        </where>
    </select>

    <select id="getNurseStationPersonIdList" parameterType="NurseStationPerson" resultType="NurseStationPerson">
        select id,
        nurse_station_id,
        department_code,
        user_id,
        nurse_person_code,
        nurse_person_name,
        nurse_person_type,
        sex,
        phone,
        address,
        birth_date,
        openid,
        unionid,
        evaluate_star_count,
        positional_title_level,
        genius_item,
        total_revenue_amount,
        work_status,
        modify_check_status,
        positional_title_level_check,
        genius_item_check,
        modify_info_flag,
        create_by,
        create_time,
        update_by,
        update_time
        from nurse_station_person
        <where>
            <if test="nurseStationPersonIdList != null and nurseStationPersonIdList.size() > 0">
                and id in
                <foreach item="nurseStationPersonIdList" collection="nurseStationPersonIdList" open="(" separator=","
                         close=")">
                    #{nurseStationPersonIdList}
                </foreach>
            </if>
        </where>
    </select>

    <update id="updateTotalRevenueAmountById">
        update nurse_station_person
        set total_revenue_amount = IFNULL(total_revenue_amount, 0) + #{totalRevenueAmount},
            update_time          = now()
        where id = #{id}
    </update>
</mapper>