<?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.hospitalpersoninfo.HospitalPersonInfoMapper">

    <resultMap type="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo" id="HospitalPersonInfoResult">
        <result property="id" column="id"/>
        <result property="hospitalId" column="hospital_id"/>
        <result property="departmentId" column="department_id"/>
        <result property="personCode" column="person_code"/>
        <result property="personName" column="person_name"/>
        <result property="personPhone" column="person_phone"/>
        <result property="personAddress" column="person_address"/>
        <result property="cardNo" column="card_no"/>
        <result property="academicTitle" column="academic_title"/>
        <result property="consultingFee" column="consulting_fee"/>
        <result property="personIntroduce" column="person_introduce"/>
        <result property="personSort" column="person_sort"/>
        <result property="personPictureUrl" column="person_picture_url"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="personAccount" column="person_account"/>
        <result property="personPassword" column="person_password"/>
        <result property="status" column="status"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.hospitalpersoninfo.HospitalPersonInfoVO" id="HospitalPersonByIdVO">
        <result property="id" column="id"/>
        <result property="hospitalId" column="hospital_id"/>
        <result property="departmentId" column="department_id"/>
        <result property="personCode" column="person_code"/>
        <result property="personName" column="person_name"/>
        <result property="personPhone" column="person_phone"/>
        <result property="personAddress" column="person_address"/>
        <result property="sex" column="sex"/>
        <result property="cardNo" column="card_no"/>
        <result property="academicTitle" column="academic_title"/>
        <result property="consultingFee" column="consulting_fee"/>
        <result property="personIntroduce" column="person_introduce"/>
        <result property="personSort" column="person_sort"/>
        <result property="personPictureUrl" column="person_picture_url"/>
        <result property="personAccount" column="person_account"/>
        <result property="personPassword" column="person_password"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="hospitalName" column="hospital_name"/>
        <result property="departmentName" column="department_name"/>
        <result property="status" column="status"/>
        <collection property="hospitalPersonCertificateList" javaType="java.util.List"
                    resultMap="HospitalPersonInfoById"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.domain.hospitalpersoncertificate.HospitalPersonCertificate" id="HospitalPersonInfoById">
        <result property="hospitalPersonId" column="hospital_person_id"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="certificateUrl" column="certificate_url"/>
        <result property="certificateSort" column="certificate_sort"/>
    </resultMap>


    <sql id="selectHospitalPersonInfoVo">
        select id,
               hospital_id,
               department_id,
               person_code,
               person_name,
               person_phone,
               person_address,
               card_no,
               academic_title,
               consulting_fee,
               person_introduce,
               person_sort,
               person_picture_url,
               person_account,
               person_password,
               create_by,
               create_time,
               update_by,
               update_time,
               status
        from hospital_person_info
    </sql>

    <select id="selectHospitalPersonInfoList"
            parameterType="com.xinelu.manage.vo.hospitalpersoninfo.HospitalPersonInfoVO"
            resultType="com.xinelu.manage.vo.hospitalpersoninfo.HospitalPersonInfoVO">
        SELECT
        hpi.id,
        hpi.hospital_id,
        hpi.department_id,
        hi.hospital_name,
        hdi.department_name,
        hpi.person_code,
        hpi.person_name,
        hpi.person_phone,
        hpi.person_address,
        hpi.card_no,
        hpi.academic_title,
        hpi.consulting_fee,
        hpi.person_introduce,
        hpi.person_sort,
        hpi.person_picture_url,
        hpi.create_by,
        hpi.create_time,
        hpi.update_by,
        hpi.update_time,
        hpi.status
        FROM
        hospital_person_info hpi
        LEFT JOIN hospital_department_info hdi ON hpi.department_id = hdi.id
        LEFT JOIN hospital_info hi ON hpi.hospital_id = hi.id
        <where>
            <if test="hospitalId != null ">
                and hpi.hospital_id = #{hospitalId}
            </if>
            <if test="departmentId != null ">
                and hpi.department_id = #{departmentId}
            </if>
            <if test="personCode != null  and personCode != ''">
                and hpi.person_code = #{personCode}
            </if>
            <if test="personName != null  and personName != ''">
                and hpi.person_name like concat('%', #{personName}, '%')
            </if>
            <if test="personPhone != null  and personPhone != ''">
                and hpi.person_phone = #{personPhone}
            </if>
            <if test="personAddress != null  and personAddress != ''">
                and hpi.person_address = #{personAddress}
            </if>
            <if test="cardNo != null  and cardNo != ''">
                and hpi.card_no = #{cardNo}
            </if>
            <if test="academicTitle != null  and academicTitle != ''">
                and hpi.academic_title = #{academicTitle}
            </if>
            <if test="consultingFee != null ">
                and hpi.consulting_fee = #{consultingFee}
            </if>
            <if test="personIntroduce != null  and personIntroduce != ''">
                and hpi.person_introduce = #{personIntroduce}
            </if>
            <if test="personSort != null ">
                and hpi.person_sort = #{personSort}
            </if>
            <if test="status != null and status != ''">
                and hpi.status = #{status}
            </if>
	        <!-- 数据范围过滤 -->
	        ${params.dataScope}
        </where>
        ORDER BY id DESC
    </select>
	<select id="selectList"
		parameterType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo"
		resultType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
		SELECT hpi.id, hpi.hospital_id, hpi.department_id,
		hpi.person_code,
		hpi.person_name,
		hpi.person_phone,
		hpi.person_address,
		hpi.card_no,
		hpi.academic_title,
		hpi.consulting_fee,
		hpi.person_introduce,
		hpi.person_sort,
		hpi.person_picture_url,
		hpi.create_by,
		hpi.create_time,
		hpi.update_by,
		hpi.update_time,
        status
		FROM hospital_person_info hpi
		<where>
			<if test="hospitalId != null ">
				and hpi.hospital_id = #{hospitalId}
			</if>
			<if test="departmentId != null ">
				and hpi.department_id = #{departmentId}
			</if>
			<if test="personCode != null  and personCode != ''">
				and hpi.person_code = #{personCode}
			</if>
			<if test="personName != null  and personName != ''">
				and hpi.person_name like concat('%', #{personName}, '%')
			</if>
			<if test="academicTitle != null  and academicTitle != ''">
				and hpi.academic_title = #{academicTitle}
			</if>
            <if test="status != null and status != ''">
                and hpi.status = #{status}
            </if>
		</where>
		ORDER BY id DESC
	</select>

    <select id="selectHospitalPersonInfoById" parameterType="Long"
            resultMap="HospitalPersonByIdVO">
        SELECT hpi.id,
               hpi.hospital_id,
               hpi.department_id,
               hi.hospital_name,
               hdi.department_name,
               hpi.person_code,
               hpi.person_name,
               hpi.person_phone,
               hpi.person_address,
               hpi.sex,
               hpi.card_no,
               hpi.academic_title,
               hpi.consulting_fee,
               hpi.person_introduce,
               hpi.person_sort,
               hpi.person_picture_url,
               hpi.person_account,
               hpi.person_password,
               hpi.create_by,
               hpi.create_time,
               hpi.update_by,
               hpi.update_time,
               hpi.status,
               hpc.hospital_person_id,
               hpc.certificate_name,
               hpc.certificate_code,
               hpc.certificate_url,
               hpc.certificate_sort
        FROM hospital_person_info hpi
                 LEFT JOIN hospital_department_info hdi ON hpi.department_id = hdi.id
                 LEFT JOIN hospital_person_certificate hpc ON hpi.id = hpc.hospital_person_id
                 LEFT JOIN hospital_info hi ON hpi.hospital_id = hi.id
        where hpi.id = #{id}
    </select>

    <insert id="insertHospitalPersonInfo" parameterType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into hospital_person_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="hospitalId != null">hospital_id,
            </if>
            <if test="departmentId != null">department_id,
            </if>
            <if test="personCode != null">person_code,
            </if>
            <if test="personName != null">person_name,
            </if>
            person_phone,
            <if test="personAddress != null">person_address,
            </if>
            card_no,
            <if test="academicTitle != null">academic_title,
            </if>
            <if test="consultingFee != null">consulting_fee,
            </if>
            <if test="personIntroduce != null">person_introduce,
            </if>
            <if test="personSort != null">person_sort,
            </if>
            <if test="personPictureUrl != null">person_picture_url,
            </if>
            <if test="personAccount != null">person_account,
            </if>
            <if test="personPassword != null">person_password,
            </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>
            <if test="status != null">
                status,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="hospitalId != null">#{hospitalId},
            </if>
            <if test="departmentId != null">#{departmentId},
            </if>
            <if test="personCode != null">#{personCode},
            </if>
            <if test="personName != null">#{personName},
            </if>
            #{personPhone},
            <if test="personAddress != null">#{personAddress},
            </if>
            #{cardNo},
            <if test="academicTitle != null">#{academicTitle},
            </if>
            <if test="consultingFee != null">#{consultingFee},
            </if>
            <if test="personIntroduce != null">#{personIntroduce},
            </if>
            <if test="personSort != null">#{personSort},
            </if>
            <if test="personPictureUrl != null">#{personPictureUrl},
            </if>
            <if test="personAccount != null">#{personAccount},
            </if>
            <if test="personPassword != null">#{personPassword},
            </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>
            <if test="status != null">
                #{status},
            </if>
        </trim>
    </insert>

    <update id="updateHospitalPersonInfo" parameterType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
        update hospital_person_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="hospitalId != null">hospital_id =
                #{hospitalId},
            </if>
            department_id = #{departmentId},
            <if test="personCode != null">person_code =
                #{personCode},
            </if>
            <if test="personName != null">person_name =
                #{personName},
            </if>
            person_phone = #{personPhone},
            <if test="personAddress != null">person_address =
                #{personAddress},
            </if>
            card_no = #{cardNo},
            <if test="academicTitle != null">academic_title =
                #{academicTitle},
            </if>
            <if test="consultingFee != null">consulting_fee =
                #{consultingFee},
            </if>
            <if test="personIntroduce != null">person_introduce =
                #{personIntroduce},
            </if>
            <if test="personSort != null">person_sort =
                #{personSort},
            </if>
            <if test="personPictureUrl != null">person_picture_url =
                #{personPictureUrl},
            </if>
            <if test="personAccount != null">person_account =
                #{personAccount},
            </if>
            <if test="personPassword != null">person_password =
                #{personPassword},
            </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>
            <if test="status != null">
                status = #{status},
            </if>
        </trim>
        where id = #{id}
    </update>

    <update id="editHospitalPersonInfo" parameterType="com.xinelu.manage.dto.hospitalpersoninfo.HospitalPersonInfoDtoo">
        update hospital_person_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="hospitalId != null">hospital_id =
                #{hospitalId},
            </if>
            <if test="departmentId != null">department_id =
                #{departmentId},
            </if>
            <if test="personCode != null">person_code =
                #{personCode},
            </if>
            <if test="personName != null">person_name =
                #{personName},
            </if>
            person_phone = #{personPhone},
            <if test="personAddress != null">person_address =
                #{personAddress},
            </if>
            card_no = #{cardNo},
            <if test="academicTitle != null">academic_title =
                #{academicTitle},
            </if>
            <if test="consultingFee != null">consulting_fee =
                #{consultingFee},
            </if>
            <if test="personIntroduce != null">person_introduce =
                #{personIntroduce},
            </if>
            <if test="personSort != null">person_sort =
                #{personSort},
            </if>
            <if test="personPictureUrl != null">person_picture_url =
                #{personPictureUrl},
            </if>
            <if test="personAccount != null">person_account =
                #{personAccount},
            </if>
            <if test="personPassword != null">person_password =
                #{personPassword},
            </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>
            <if test="status != null">
                status = #{status},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteHospitalPersonInfoById" parameterType="Long">
        delete
        from hospital_person_info
        where id = #{id}
    </delete>

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

    <select id="selectHospitalPersonInfoByCount" resultType="java.lang.Integer">
        select
        count(1)
        from hospital_person_info
        <where>
            <if test="personPhone != null  and personPhone != ''">
                and person_phone = #{personPhone}
            </if>
            <if test="cardNo != null  and cardNo != ''">
                and card_no = #{cardNo}
            </if>
        </where>
    </select>

    <select id="selectHospitalPersonByIds" parameterType="Long"
            resultType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
        select id,
        hospital_id,
        department_id,
        person_code,
        person_name,
        person_phone,
        person_address,
        card_no,
        academic_title,
        consulting_fee,
        person_introduce,
        person_sort,
        person_picture_url,
        create_by,
        create_time,
        update_by,
        update_time,
        status
        from hospital_person_info
        <where>
            <if test="hospitalPersonIdsList != null  and hospitalPersonIdsList.size()>0 ">
                and id in
                <foreach item="hospitalPersonIds" collection="hospitalPersonIdsList" open="(" separator=","
                         close=")">
                    #{hospitalPersonIds}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectHospitalPerson"
            parameterType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo"
            resultType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
        select id,
               department_id,
               person_name,
               person_picture_url,
               academic_title,
               consulting_fee,
               person_introduce
        from hospital_person_info
        where department_id = #{departmentId}
        <if test="status != null and status != ''">
            and status = #{status}
        </if>
        order by person_sort asc
    </select>

    <select id="selectHospitalPersonInfoByIdCount" resultType="int">
        select
        count(1)
        from hospital_person_info
        <where>
            <if test="id != null ">
                and id = #{id}
            </if>
        </where>
    </select>

    <!-- 根据科室人员编码查询人员信息 -->
    <select id="getByPersonCode" resultType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
        <include refid="selectHospitalPersonInfoVo"></include>
        where person_code = #{personCode}
        <if test="status != null and status != ''">
            and status = #{status}
        </if>
        limit 1
    </select>

    <!-- 根据科室人员编号更新 -->
    <update id="updateByPersonCode" parameterType="com.xinelu.manage.domain.hospitalpersoninfo.HospitalPersonInfo">
        update hospital_person_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="hospitalId != null">hospital_id =
                #{hospitalId},
            </if>
            <if test="departmentId != null">department_id =
                #{departmentId},
            </if>
            <if test="personName != null">person_name =
                #{personName},
            </if>
            <if test="personPhone != null">
                person_phone = #{personPhone},
            </if>
            <if test="personAddress != null">person_address =
                #{personAddress},
            </if>
            <if test="cardNo != null">
                card_no = #{cardNo},
            </if>
            <if test="academicTitle != null">academic_title =
                #{academicTitle},
            </if>
            <if test="consultingFee != null">consulting_fee =
                #{consultingFee},
            </if>
            <if test="personIntroduce != null">person_introduce =
                #{personIntroduce},
            </if>
            <if test="personSort != null">person_sort =
                #{personSort},
            </if>
            <if test="personPictureUrl != null">person_picture_url =
                #{personPictureUrl},
            </if>
            <if test="personAccount != null">person_account =
                #{personAccount},
            </if>
            <if test="personPassword != null">person_password =
                #{personPassword},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            update_time = now(),
        </trim>
        where person_code = #{personCode}
    </update>
</mapper>
