<?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.vctgo.system.mapper.SysPlatformExpertMapper">

    <resultMap type="com.vctgo.system.domain.SysPlatformExpert" id="SysPlatformExpertResult">
        <result property="id"    column="id"    />
        <result property="userId"    column="user_id"    />
        <result property="politicalStatus"    column="political_status"    />
        <result property="identificationCard"    column="identification_card"    />
        <result property="birthDate"    column="birth_date"    />
        <result property="provinceId"    column="province_id"    />
        <result property="cityId"    column="city_id"    />
        <result property="districtId"    column="district_id"    />
        <result property="belongRegion"    column="belong_region"    />
        <result property="nation"    column="nation"    />
        <result property="workOrganization"    column="work_organization"    />
        <result property="workPosition"    column="work_position"    />
        <result property="professionalTitleGrade"    column="professional_title_grade"    />
        <result property="socialService"    column="social_service"    />
        <result property="researchField"    column="research_field"    />
        <result property="researchDiscipline"    column="research_discipline"    />
        <result property="workExperience"    column="work_experience"    />
        <result property="education"    column="education"    />
        <result property="expertProfile"    column="expert_profile"    />
        <result property="expertDetails"    column="expert_details"    />
        <result property="scientificResearchAchievement"    column="scientific_research_achievement"    />
        <result property="honoraryAchievement"    column="honorary_achievement"    />
        <result property="remark"    column="remark"    />
        <result property="isDeleted"    column="is_deleted"    />
        <result property="createById"    column="create_by_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateById"    column="update_by_id"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="tenantId"    column="tenant_id"    />
    </resultMap>

    <sql id="selectSysPlatformExpertVo">
        select id, user_id, political_status, identification_card, birth_date, province_id, city_id, district_id, belong_region, nation, work_organization, work_position, professional_title_grade, social_service, research_field, research_discipline,work_experience,education, expert_profile, expert_details, scientific_research_achievement, honorary_achievement, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_platform_expert
    </sql>

    <sql id="defaultDeleteCondition">
        is_deleted = 0
    </sql>

    <resultMap id="selectExpertInfo" type="com.vctgo.system.domain.SysPlatformExpert" extends="SysPlatformExpertResult">
        <collection property="sysPlatformExpertEducationList" ofType="com.vctgo.system.domain.SysPlatformExpertEducation" javaType="java.util.List"
                    column="{userId = userId}" select="selectExpertEducationByUserId" fetchType="eager"/>
        <collection property="sysPlatformExpertWorkExperienceList" ofType="com.vctgo.system.domain.SysPlatformExpertWorkExperience" javaType="java.util.List"
                    column="{userId = userId}" select="selectWorkExperienceByUserId" fetchType="eager"/>
    </resultMap>

    <select id="selectExpertEducationByUserId" resultType="com.vctgo.system.domain.SysPlatformExpertEducation">
        SELECT * FROM sys_platform_expert_education WHERE is_deleted='0' and user_id = #{userId}
    </select>

    <select id="selectWorkExperienceByUserId" resultType="com.vctgo.system.domain.SysPlatformExpertWorkExperience">
        SELECT * FROM sys_platform_expert_work_experience WHERE is_deleted='0' and user_id = #{userId}
    </select>

    <select id="selectExpertByUserId" resultType="com.vctgo.system.domain.SysPlatformExpert">
        SELECT
            pe.id,
            pu.nick_name nickName,
            pu.phonenumber,
            pu.sex,
            pu.STATUS,
            pu.create_time,
            pu.email,
            pu.avatar,
            pe.user_id,
            pe.political_status,
            pe.identification_card,
            pe.birth_date,
            pe.province_id,
            pe.city_id,
            pe.district_id,
            pe.belong_region,
            pe.nation,
            pe.work_organization,
            pe.work_position,
            pe.professional_title_grade,
            pe.social_service,
            pe.research_field,
            pe.research_discipline,
            pe.expert_profile,
            pe.expert_details,
            pe.scientific_research_achievement,
            pe.honorary_achievement,
            pe.remark,
            pe.education,
            pe.work_experience
        FROM
            sys_platform_user pu
                LEFT JOIN sys_platform_expert pe ON pu.user_id = pe.user_id AND pe.is_deleted = '0'  and pe.user_id = #{userId}
        WHERE
            pu.del_flag = '0' AND pu.is_expert = '1' AND pu.user_id = #{userId}
    </select>

    <select id="selectSysPlatformExpertList"  resultMap="SysPlatformExpertResult">
        SELECT
            pu.user_id,
            pu.nick_name,
            pu.phonenumber,
            pu.sex,
            pu.status,
            pu.create_time,
            spu.nick_name create_by,
            pe.professional_title_grade,
            pe.belong_region,
            pe.research_field,
            pe.research_discipline,
            pe.id,
            pe.province_id,
            pe.city_id,
            pe.district_id,
            pe.belong_region,
            ( SELECT GROUP_CONCAT( ur.role_id ) FROM sys_platform_user_role ur WHERE ur.user_id = pu.user_id ) roleIdString
        FROM
            sys_platform_user pu
        LEFT JOIN sys_platform_user spu on pu.create_by_id = spu.user_id
        LEFT JOIN sys_platform_expert pe ON pu.user_id = pe.user_id and pe.is_deleted = '0'
        <where>
            pu.del_flag = '0' AND pu.is_expert = '1'
            <if test="query.nickName != null and query.nickName != ''">AND pu.nick_name like concat('%', #{query.nickName}, '%')</if>
            <if test="query.phonenumber != null and query.phonenumber != ''">AND pu.phonenumber like concat('%', #{query.phonenumber}, '%')</if>
            <if test="query.status != null and query.status != ''">AND pu.status = #{query.status}</if>
            <if test="query.userId != null "> and pe.user_id = #{query.userId}</if>
            <if test="query.politicalStatus != null  and query.politicalStatus != ''"> and pe.political_status = #{query.politicalStatus}</if>
            <if test="query.identificationCard != null  and query.identificationCard != ''"> and pe.identification_card = #{query.identificationCard}</if>
            <if test="query.birthDate != null  and query.birthDate != ''"> and pe.birth_date = #{query.birthDate}</if>
            <if test="query.provinceId != null  and query.provinceId != ''"> and pe.province_id = #{query.provinceId}</if>
            <if test="query.cityId != null  and query.cityId != ''"> and pe.city_id = #{query.cityId}</if>
            <if test="query.districtId != null  and query.districtId != ''"> and pe.district_id = #{query.districtId}</if>
            <if test="query.belongRegion != null  and query.belongRegion != ''"> and pe.belong_region = #{query.belongRegion}</if>
            <if test="query.nation != null  and query.nation != ''"> and pe.nation = #{query.nation}</if>
            <if test="query.workOrganization != null  and query.workOrganization != ''"> and pe.work_organization = #{query.workOrganization}</if>
            <if test="query.workPosition != null  and query.workPosition != ''"> and pe.work_position = #{query.workPosition}</if>
            <if test="query.professionalTitleGrade != null  and query.professionalTitleGrade != ''"> and pe.professional_title_grade = #{query.professionalTitleGrade}</if>
            <if test="query.socialService != null  and query.socialService != ''"> and pe.social_service = #{query.socialService}</if>
            <if test="query.researchField != null  and query.researchField != ''"> and pe.research_field = #{query.researchField}</if>
            <if test="query.researchDiscipline != null  and query.researchDiscipline != ''"> and pe.research_discipline = #{query.researchDiscipline}</if>
            <if test="query.expertProfile != null  and query.expertProfile != ''"> and pe.expert_profile = #{query.expertProfile}</if>
            <if test="query.expertDetails != null  and query.expertDetails != ''"> and pe.expert_details = #{query.expertDetails}</if>
            <if test="query.scientificResearchAchievement != null  and query.scientificResearchAchievement != ''"> and pe.scientific_research_achievement = #{query.scientificResearchAchievement}</if>
            <if test="query.honoraryAchievement != null  and query.honoraryAchievement != ''"> and pe.honorary_achievement = #{query.honoraryAchievement}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''"> and pe.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null "> and pe.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null "> and pe.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null "> and pe.tenant_id = #{query.tenantId}</if>
            <if test="query.params.beginTime != null and query.params.beginTime != ''">
                and date_format(pu.create_time,'%y%m%d') &gt;= date_format(#{query.params.beginTime},'%y%m%d')
            </if>
            <if test="query.params.endTime != null and query.params.endTime != ''">
                and date_format(pu.create_time,'%y%m%d') &lt;= date_format(#{query.params.endTime},'%y%m%d')
            </if>
        </where>
        order by pe.create_time desc
    </select>

    <update id="logicalDeleteSysPlatformExpertByIds" parameterType="String">
        update sys_platform_expert set is_deleted = #{query.isDeleted},
        update_by_id = #{query.updateById}, update_by = #{query.updateBy},
        update_time = #{query.updateTime} where id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

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