<?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.fs.doctor.mapper.DoctorMapper">
    
    <resultMap type="Doctor" id="DoctorResult">
        <result property="id"    column="id"    />
        <result property="doctorId"    column="doctor_id"    />
        <result property="doctorName"    column="doctor_name"    />
        <result property="accountType"    column="account_type"    />
        <result property="mobile"    column="mobile"    />
        <result property="idCard"    column="id_card"    />
        <result property="institution"    column="institution"    />
        <result property="province"    column="province"    />
        <result property="city"    column="city"    />
        <result property="district"    column="district"    />
        <result property="department"    column="department"    />
        <result property="jobTitle"    column="job_title"    />
        <result property="thirdPartyCode"    column="third_party_code"    />
        <result property="researcherCode"    column="researcher_code"    />
        <result property="registerTime"    column="register_time"    />
        <result property="licenseImage"    column="license_image"    />
        <result property="titleCertImage"    column="title_cert_image"    />
        <result property="contractStatus"    column="contract_status"    />
        <result property="bankCardNo"    column="bank_card_no"    />
        <result property="bankName"    column="bank_name"    />
        <result property="bankBranch"    column="bank_branch"    />
        <result property="status"    column="status"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleted"    column="deleted"    />
        <result property="pwd"    column="pwd"    />
        <result property="companyId"    column="company_id"    />
        <result property="provinceName" column="province_name"/>
        <result property="cityName" column="city_name"/>
        <result property="districtName" column="district_name"/>
        <result property="auditInstanceId" column="audit_instance_id"/>
        <result property="companyName" column="company_name"/>
        <result property="balance" column="balance"/>
    </resultMap>

    <sql id="selectDoctorVo">
        select
        d.id,
        d.doctor_id,
        d.doctor_name,
        d.account_type,
        d.mobile,
        d.id_card,
        d.institution,
        d.province,
        d.city,
        d.district,
        d.department,
        d.job_title,
        d.third_party_code,
        d.researcher_code,
        d.register_time,
        d.license_image,
        d.title_cert_image,
        d.contract_status,
        d.bank_card_no,
        d.bank_name,
        d.bank_branch,
        d.status,
        d.create_time,
        d.update_time,
        d.deleted,
        d.pwd,
        d.company_id,
        d.audit_instance_id,
        d.balance,
        company.company_name,

        p.city_name as province_name,
        c.city_name as city_name,
        a.city_name as district_name

        from doctor d
        left join fs_city p on p.city_id = d.province
        left join fs_city c on c.city_id = d.city
        left join fs_city a on a.city_id = d.district
        left join company company on d.company_id  = company.company_id
    </sql>

    <select id="selectDoctorList" parameterType="Doctor" resultMap="DoctorResult">
        SELECT DISTINCT d.id, d.doctor_id, d.doctor_name, d.account_type, d.mobile, d.id_card,
        d.institution, d.province, d.city, d.district, d.department, d.job_title,
        d.third_party_code, d.researcher_code, d.register_time, d.license_image,
        d.title_cert_image, d.contract_status, d.bank_card_no, d.bank_name,
        d.bank_branch, d.status, d.create_time, d.update_time, d.deleted, d.pwd,
        d.company_id, d.balance, d.audit_instance_id, d.reject_reason, d.task_num,
        p.city_name as provinceName,
        c.city_name as cityName,
        a.city_name as districtName,
        company.company_name as companyName
        FROM doctor d
        left join fs_city p on p.city_id = d.province
        left join fs_city c on c.city_id = d.city
        left join fs_city a on a.city_id = d.district
        left join company company on d.company_id  = company.company_id
        <if test="companyUserId != null">
            INNER JOIN doctor_company_user dcu ON d.doctor_id = dcu.doctor_id AND dcu.deleted = 0
        </if>
        <where>
            <if test="companyUserId != null">
                AND dcu.company_user_id = #{companyUserId}
            </if>
            <if test="doctorId != null  and doctorId != ''"> and d.doctor_id = #{doctorId}</if>
            <if test="doctorName != null  and doctorName != ''"> and d.doctor_name like concat('%', #{doctorName}, '%')</if>
            <if test="accountType != null  and accountType != ''"> and d.account_type = #{accountType}</if>
            <if test="mobile != null  and mobile != ''"> and d.mobile = #{mobile}</if>
            <if test="idCard != null  and idCard != ''"> and d.id_card = #{idCard}</if>
            <if test="institution != null  and institution != ''"> and d.institution = #{institution}</if>
            <if test="province != null  and province != ''"> and d.province = #{province}</if>
            <if test="city != null  and city != ''"> and d.city = #{city}</if>
            <if test="district != null  and district != ''"> and d.district = #{district}</if>
            <if test="department != null  and department != ''"> and d.department = #{department}</if>
            <if test="jobTitle != null  and jobTitle != ''"> and d.job_title = #{jobTitle}</if>
            <if test="thirdPartyCode != null  and thirdPartyCode != ''"> and d.third_party_code = #{thirdPartyCode}</if>
            <if test="researcherCode != null  and researcherCode != ''"> and d.researcher_code = #{researcherCode}</if>
            <if test="registerTime != null "> and d.register_time = #{registerTime}</if>
            <if test="licenseImage != null  and licenseImage != ''"> and d.license_image = #{licenseImage}</if>
            <if test="titleCertImage != null  and titleCertImage != ''"> and d.title_cert_image = #{titleCertImage}</if>
            <if test="contractStatus != null "> and d.contract_status = #{contractStatus}</if>
            <if test="bankCardNo != null  and bankCardNo != ''"> and d.bank_card_no = #{bankCardNo}</if>
            <if test="bankName != null  and bankName != ''"> and d.bank_name like concat('%', #{bankName}, '%')</if>
            <if test="bankBranch != null  and bankBranch != ''"> and d.bank_branch = #{bankBranch}</if>
            <if test="status != null "> and d.status = #{status}</if>
            <if test="deleted != null "> and d.deleted = #{deleted}</if>
            <if test="pwd != null  and pwd != ''"> and d.pwd = #{pwd}</if>
            <if test="companyId != null "> and d.company_id = #{companyId}</if>
        </where>
        <!-- 添加排序：最新添加的在前 -->
        order by d.create_time desc
    </select>
    
    <select id="selectDoctorById" parameterType="Long" resultMap="DoctorResult">
        <include refid="selectDoctorVo"/>
        where d.id = #{id}
    </select>


    <select id="selectDoctorByDoctorId" parameterType="String" resultMap="DoctorResult">
        <include refid="selectDoctorVo"/>
        where d.doctor_id = #{doctorId}
    </select>
        
    <insert id="insertDoctor" parameterType="Doctor" useGeneratedKeys="true" keyProperty="id">
        insert into doctor
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id,</if>
            <if test="doctorName != null">doctor_name,</if>
            <if test="accountType != null">account_type,</if>
            <if test="mobile != null">mobile,</if>
            <if test="idCard != null">id_card,</if>
            <if test="institution != null">institution,</if>
            <if test="province != null">province,</if>
            <if test="city != null">city,</if>
            <if test="district != null">district,</if>
            <if test="department != null">department,</if>
            <if test="jobTitle != null">job_title,</if>
            <if test="thirdPartyCode != null">third_party_code,</if>
            <if test="researcherCode != null">researcher_code,</if>
            <if test="registerTime != null">register_time,</if>
            <if test="licenseImage != null">license_image,</if>
            <if test="titleCertImage != null">title_cert_image,</if>
            <if test="contractStatus != null">contract_status,</if>
            <if test="bankCardNo != null">bank_card_no,</if>
            <if test="bankName != null">bank_name,</if>
            <if test="bankBranch != null">bank_branch,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleted != null">deleted,</if>
            <if test="pwd != null">pwd,</if>
            <if test="companyId != null">company_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">#{doctorId},</if>
            <if test="doctorName != null">#{doctorName},</if>
            <if test="accountType != null">#{accountType},</if>
            <if test="mobile != null">#{mobile},</if>
            <if test="idCard != null">#{idCard},</if>
            <if test="institution != null">#{institution},</if>
            <if test="province != null">#{province},</if>
            <if test="city != null">#{city},</if>
            <if test="district != null">#{district},</if>
            <if test="department != null">#{department},</if>
            <if test="jobTitle != null">#{jobTitle},</if>
            <if test="thirdPartyCode != null">#{thirdPartyCode},</if>
            <if test="researcherCode != null">#{researcherCode},</if>
            <if test="registerTime != null">#{registerTime},</if>
            <if test="licenseImage != null">#{licenseImage},</if>
            <if test="titleCertImage != null">#{titleCertImage},</if>
            <if test="contractStatus != null">#{contractStatus},</if>
            <if test="bankCardNo != null">#{bankCardNo},</if>
            <if test="bankName != null">#{bankName},</if>
            <if test="bankBranch != null">#{bankBranch},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="pwd != null">#{pwd},</if>
            <if test="companyId != null">#{companyId},</if>
         </trim>
    </insert>

    <update id="updateDoctor" parameterType="Doctor">
        update doctor
        <trim prefix="SET" suffixOverrides=",">
            <if test="doctorId != null and doctorId != ''">doctor_id = #{doctorId},</if>
            <if test="doctorName != null">doctor_name = #{doctorName},</if>
            <if test="accountType != null">account_type = #{accountType},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="idCard != null">id_card = #{idCard},</if>
            <if test="institution != null">institution = #{institution},</if>
            <if test="province != null">province = #{province},</if>
            <if test="city != null">city = #{city},</if>
            <if test="district != null">district = #{district},</if>
            <if test="department != null">department = #{department},</if>
            <if test="jobTitle != null">job_title = #{jobTitle},</if>
            <if test="thirdPartyCode != null">third_party_code = #{thirdPartyCode},</if>
            <if test="researcherCode != null">researcher_code = #{researcherCode},</if>
            <if test="registerTime != null">register_time = #{registerTime},</if>
            <if test="licenseImage != null">license_image = #{licenseImage},</if>
            <if test="titleCertImage != null">title_cert_image = #{titleCertImage},</if>
            <if test="contractStatus != null">contract_status = #{contractStatus},</if>
            <if test="bankCardNo != null">bank_card_no = #{bankCardNo},</if>
            <if test="bankName != null">bank_name = #{bankName},</if>
            <if test="bankBranch != null">bank_branch = #{bankBranch},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
            <if test="pwd != null">pwd = #{pwd},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="status != null">status = #{status},</if>
        </trim>
        where doctor.id = #{id}
    </update>

    <delete id="deleteDoctorById" parameterType="Long">
        delete from doctor where doctor.id = #{id}
    </delete>

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




    <select id="getDoctorOptionVOList"
            parameterType="com.fs.doctor.vo.DoctorOptionReqVO"
            resultType="com.fs.doctor.vo.DoctorOptionRespVO">
        select
        d.id as doctorId,
        d.doctor_name as doctorName
        from doctor d
        <where>
            <if test="doctorName != null and doctorName != ''">
                and d.doctor_name like concat('%', #{doctorName}, '%')
            </if>

            <if test="companyId != null">
                and d.company_id = #{companyId}
            </if>
        </where>

        <!-- 更像的排前：以输入开头的姓名优先 -->
        <if test="doctorName != null and doctorName != ''">
            order by
            case when d.doctor_name like concat(#{doctorName}, '%') then 0 else 1 end,
            d.id desc
        </if>
        <if test="doctorName == null or doctorName == ''">
            order by d.id desc
        </if>

        limit 20
    </select>


    <update id="updateDoctorPwd" parameterType="com.fs.doctor.domain.Doctor">
        update doctor
        set pwd = #{pwd},
        update_time = now()
        where id = #{id}
    </update>



    <update id="addPoints">
        update doctor
        set balance = ifnull(balance, 0) + #{points}
        where id = #{doctorId}
    </update>



    <select id="selectDoctorListByIds" resultMap="DoctorResult">
        <include refid="selectDoctorVo"/>
        where d.id in
        <foreach item="id" collection="doctorIds" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>



</mapper>