专家入库相关sql模糊查询

实体类

@Data
@TableName("ess_lib_speciality_valset")
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "EssLibSpecialityValset", description = "专家专业信息表")
public class EssLibSpecialityValset extends BaseEntity {

    @ApiModelProperty(value = "专家级别信息Id")
    private Long expertsBaseId;

    @ApiModelProperty(value = "专家库Id")
    private Long expertsId;

    @ApiModelProperty(value = "对应专业值集表id")
    private Long isExpertsId;

    @ApiModelProperty(value = "编码")
    private String code;

    @ApiModelProperty(value = "名称")
    private String name;

    @ApiModelProperty(value = "描述")
    private String datadicDesc;

    @ApiModelProperty(value = "顺序")
    private Integer dispOrder;

    @ApiModelProperty(value = "状态 0 保存 1 启用 2 停用")
    private String status;

    @ApiModelProperty(value = "类型")
    private String type;

    @ApiModelProperty(value = "级别")
    private Integer level;


    @ApiModelProperty(value = "父值集code")
    private   String parentCode;

    @JSONField(serializeUsing = ToStringSerializer.class)
    @ApiModelProperty(value = "上级Id")
    private Long parentId;

}

sql

 <select id="selectExpertSharePage" resultType="com.cntaiping.ess.libexperts.vo.ExpertModel">
        select
            ele.id,
            ele.id                 judgeId,
            eleb.id                libExpertsBaseId,
            ele.expert_code        expertCode,
            eleb.name,
            eleb.id_card           idCard,
            eleb.telephone,
            eleb.organization_name organizationName,
            eleb.province_code     provinceCode,
            eleb.city_code         cityCode,
            eleb.job_title         jobTitles,
            eleb.user_id           userId,
            ele.storage_date       storageDate,
            ele.expert_status      expertStatus,
            ele.expert_origin      expertOrigin,
            ele.org_id             orgId,
            eleb.work_department workDepartment,
            eleb.duties duties,
            eleb.expert_level expertLevel,
            eleb.office_phone officePhone,
            (select group_concat(elep.full_name)
             from ess_lib_experts_professions elep
             where elep.libexpert_id = ele.id and elep.is_deleted = '0') professionNames
        from ess_lib_experts ele
            left join ess_lib_experts_base eleb on eleb.id = ele.lib_experts_base_id and eleb.is_deleted = '0'
        where
            ele.is_deleted = '0'
            <!-- 根据机构筛选专家 -->
            <if test="vo.needUserId != null">
                AND eleb.user_id IS NOT NULL
            </if>
            AND eleb.expert_change_status > '0'
            AND ele.expert_status > 0
            <if test="vo.needOuterIn != null and vo.needOuterIn == 0">
                and ele.expert_origin != '30'
            </if>
            <if test="vo.orgIdList != null and vo.orgIdList.size() != 0">
                and ele.org_id in
                <foreach collection="vo.orgIdList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <!-- 根据毕业专业id集筛选专家 -->
            <if test="vo.graduateProfessionIds != null and vo.graduateProfessionIds.size() != 0">
                and eleb.graduate_profession_id in
                <foreach collection="vo.graduateProfessionIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <!-- 根据职等标签id集筛选专家 -->
            <if test="vo.isDutiesLevelIds != null and vo.isDutiesLevelIds.size() != 0">
                and eleb.is_duties_level_id in
                <foreach collection="vo.isDutiesLevelIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <!-- 根据从事专业id集筛选专家 -->
          <!--  <if test="vo.isProfessionIds != null and vo.isProfessionIds.size() != 0">
                and eleb.is_profession_id in
                <foreach collection="vo.isProfessionIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>-->
        <if test="vo.isProfessionIds != null and vo.isProfessionIds.size() != 0">
            and exists (
            select els.id from ess_lib_speciality_valset els  where els.is_deleted = '0'
            and els.experts_id = ele.id
            and els.type = '20'
            and els.is_experts_id in
            <foreach collection="param.isProfessionIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
            )
        </if>
            <!-- 根据职称证书id集筛选专家 -->
            <if test="vo.titleCertificateIds != null and vo.titleCertificateIds.size() != 0">
                and exists (
                    select elen.id from ess_lib_expert_nvq elen where elen.is_deleted = '0'
                    and elen.evaluation_expert_id = ele.id
                    and elen.certi_type = '10'
                    and elen.certificate_id in
                    <foreach collection="vo.titleCertificateIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                )
            </if>
            <!-- 根据执业证书id集筛选专家 -->
            <if test="vo.PracticeCertificateIds != null and vo.PracticeCertificateIds.size() != 0">
                and exists (
                    select elen.id from ess_lib_expert_nvq elen where elen.is_deleted = '0'
                    and elen.evaluation_expert_id = ele.id
                    and elen.certi_type = '20'
                    and elen.certificate_id in
                    <foreach collection="vo.PracticeCertificateIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                )
            </if>
            <if test="vo.orgId != null and (vo.orgIdList == null or vo.orgIdList.size() == 0)">
                and ele.org_id = #{vo.orgId}
            </if>
            <if test="vo.excludeUserIds != null and vo.excludeUserIds.size() != 0">
                and eleb.user_id not in
                <foreach collection="vo.excludeUserIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <if test="vo.excludeIds != null and vo.excludeIds.size() != 0">
                and ele.id not in
                <foreach collection="vo.excludeIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <!-- 关键字 -->
            <if test="vo.searchKey != null and vo.searchKey != ''">
                and (
                        eleb.name like concat('%', #{vo.searchKey}, '%')
                                or eleb.id_card like concat('%', #{vo.searchKey}, '%')
                                or eleb.telephone like concat('%', #{vo.searchKey}, '%')
                        )
            </if>
            <!-- 状态 -->
            <if test="vo.expertStatusList != null and vo.expertStatusList.size() != 0">
                and ele.expert_status in
                <foreach collection="vo.expertStatusList" item="expertStatus" open="(" close=")" separator=",">
                    #{expertStatus}
                </foreach>
            </if>
            <!-- 标签 -->
            <if test="vo.tagIdList != null and vo.tagIdList.size() != 0">
                and exists(
                        select t1.id
                        from ess_lib_experts_tags t1 where t1.is_deleted = '0'
                                                       and t1.libexpert_id = ele.id
                                                       and t1.libtag_id in
                <foreach collection="vo.tagIdList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
                )
            </if>
            <!-- 专业 -->
            <if test="vo.professionIdList != null and vo.professionIdList.size() != 0">
                and exists(
                        select t1.id
                        from ess_lib_experts_professions t1 where t1.is_deleted = '0'
                                                              and t1.libexpert_id = ele.id
                                                              and t1.libpro_id in
                <foreach collection="vo.professionIdList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
                )
            </if>
            <!-- 入库时间 -->
            <choose>
                <when test="vo.storageStartDate != null and vo.storageStartDate != '' and vo.storageEndDate != null and vo.storageEndDate != ''">
                    and ele.storage_date between #{vo.storageStartDate} and #{vo.storageEndDate}
                </when>
                <when test="vo.storageStartDate != null and vo.storageStartDate != ''">
                    and ele.storage_date >= #{vo.storageStartDate}
                </when>
                <when test="vo.storageEndDate != null and vo.storageEndDate != ''">
                    and ele.storage_date <![CDATA[ <= ]]> #{vo.storageEndDate}
                </when>
            </choose>
            <!-- 专家单位 -->
            <if test="vo.orgCodeList != null and vo.orgCodeList.size() != 0">
                and eleb.organization_code in
                <foreach collection="vo.orgCodeList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <!-- 所在单位 -->
            <if test="vo.libOrgIdList != null and vo.libOrgIdList.size() != 0">
                and (ele.org_id in
                <foreach collection="vo.libOrgIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
                or ele.lib_id in
                <foreach collection="vo.libOrgIdList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>)
            </if>
            <!-- 专家类型 -->
            <if test="vo.expertOriginList != null and vo.expertOriginList.size() != 0">
                and ele.expert_origin in
                <foreach collection="vo.expertOriginList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <!-- 专家等级 -->
            <if test="vo.expertLevelList != null and vo.expertLevelList.size() != 0">
                and eleb.expert_level in
                <foreach collection="vo.expertLevelList" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <!-- 省 -->
            <if test="vo.provinceCode != null and vo.provinceCode != ''">
                and eleb.province_code = #{vo.provinceCode}
            </if>
            <!-- 市/区 -->
            <if test="vo.cityCode != null and vo.cityCode != '' and vo.cityCode != '100000'">
                and eleb.city_code = #{vo.cityCode}
            </if>
        order by ele.id desc
    </select>