<?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.unis.base.server.dao.TalentGroupDao">
    <sql id="base_column_list">
        u.user_code, u.user_loginname, u.user_name, u.user_sex, u.user_title,
        u.is_dimission, u.buty, u.user_post, u.is_disabled, u.area_name,
        u.county_code, u.dept_code, u.org_code
    </sql>
    <insert id="insertGroup">
        insert into base:d_talent_group (group_id, group_name, creation_time, creator)
        values (#{groupId}, #{groupName}, #{creationTime}, #{creator})
    </insert>
    <insert id="setGroupID">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base:d_ryxxsjy (group_id)
            VALUES
            (
            #{obj.groupId}
            )
            where ryxm=#{userName} and qymc=#{deptName}
        </foreach>
    </insert>
    <insert id="insertQiYe">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_ryxxsjy (group_id)
            VALUES
            (
            #{obj.groupId}
            )
            where qymc=#{deptName}
        </foreach>
    </insert>
    <delete id="delectMore">
        delete
        from base.d_talent_group
        where group_id=#{groupId}
    </delete>
    <delete id="deleteSomein">
        update base.d_ryxxsjy
        set group_id = '0',association='无' where rybh=#{userId}
    </delete>
    <update id="deleteSome">
        update base.d_ryxxsjy
        set group_id = '0',association='无' where group_id=#{groupId}
    </update>
    <update id="updatePerson">
        update base.d_ryxxsjy
        set group_id = #{groupId},association=#{association} where rybh=#{userId}
    </update>
    <update id="updateGroup">
        update base.d_ryxxsjy
        set group_id = #{groupId},association=(select group_name from base.d_talent_group where group_id=#{groupId}) where qymc=#{deptId}
    </update>

    <select id="selectGroup" resultType="com.unis.base.common.vo.GroupPersonVO">
        select u.user_code yybh, u.user_name ryxm, d.dept_name qymc, u.user_title zwmc, b.xw, r.honorary_name rych
        from usercenter.sys_user_info u
        left join usercenter.sys_department d  on u.dept_code = d.dept_code
        left join base.d_ryxxsjy b on u.user_mobile=b.lxdh
        left join base.d_report r on u.user_mobile=r.telphone
        where u.group_id=#{groupId}
    </select>
    <select id="selectPerson" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select *
        from base.d_gerenmx
        where rybh=#{rybh}
    </select>
    <!-- <select id="listInfo" resultType="com.unis.base.common.vo.PersonnelMxVO">

     </select>-->
    <select id="selectGroupID" resultType="java.lang.String">
        select group_id
        from base.d_talent_group
        where group_name=#{groupName}
    </select>
    <select id="selectG" resultType="com.unis.base.common.vo.GroupVO">
        select * from base.d_talent_group
        <where>
            <if test="groupName!=null and groupName!='' ">
                and group_name  LIKE CONCAT('%', #{groupName}, '%')
            </if>
        </where>
    </select>
    <select id="selectPhone" resultType="java.lang.String">
        select user_mobile
        from usercenter.sys_user_info
        where user_code=#{userId}
    </select>
    <select id="selectRybh" resultType="java.lang.String">
        select rybh
        from base.d_ryxxsjy
        where lxdh=#{telphone}
    </select>
    <select id="selectEducation" resultType="com.unis.base.common.vo.EducationVO">
        select *
        from base.d_education
        where rybh  LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectReport" resultType="com.unis.base.common.vo.ReportVO">
        select *
        from base.d_report
        where rybh  LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectSkill" resultType="com.unis.base.common.vo.SkillVO">
        select *
        from base.d_skill
        where rybh LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectRotaterotateVO" resultType="com.unis.base.common.vo.RotaterotateVO">
        select *
        from base.d_rotaterotate
        where rybh LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectFeiEducationVO" resultType="com.unis.base.common.vo.FeiEducationVO">
        select *
        from base.d_feieducation
        where rybh LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectOtherCertificatesVO" resultType="com.unis.base.common.vo.OtherCertificatesVO">
        select *
        from base.d_certificates
        where rybh LIKE CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectpersonId" resultType="com.unis.base.common.vo.SelectPersonVo">
        select rybh userId
        from base.d_ryxxsjy
        where tyshxydm=#{deptId}
    </select>
    <select id="selectPP" resultType="com.unis.base.common.vo.GroupPersonVO">
        select u.rybh,
               u.ryxm,
               u.qymc,
               e.quan_academic          academic,
               e.quan_highest_education xw,
               (select group_concat(r.honorary_name) from base.d_report r where r.rybh=u.rybh) rych
        from base.d_ryxxsjy u
            left join base.d_education e on u.rybh=e.rybh
        where u.rybh=#{userId}
    </select>
    <select id="selectGroups" resultType="com.unis.base.common.vo.GroupPersonSVO">
--         select u.rybh,
--                u.ryxm,
--                u.qymc,
--                u.zwmc,
--                e.quan_highest_education xw,
--                (select group_concat(r.honorary_name) from base.d_report r where r.rybh=u.rybh) rych,
--                u.lxdh
--         from base.d_ryxxsjy u
--             left join base.d_education e on u.rybh=e.rybh
--         where u.group_id LIKE CONCAT('%', #{groupId}, '%')

        SELECT u.rybh,
               u.ryxm,
               u.qymc,
               u.zwmc,
               e.quan_highest_education AS xw,
               GROUP_CONCAT(r.honorary_name ORDER BY r.honorary_name SEPARATOR ',') AS rych,
               u.lxdh
        FROM base.d_ryxxsjy u
                 LEFT JOIN base.d_education e ON u.rybh = e.rybh
                 LEFT JOIN base.d_report r ON u.rybh = r.rybh
        WHERE u.group_id LIKE CONCAT('%', #{groupId}, '%')
        GROUP BY u.rybh, u.ryxm, u.qymc, u.zwmc, e.quan_highest_education, u.lxdh

    </select>
    <select id="selectname" resultType="java.lang.String">
        select id
        from base.d_talent_group
        where group_name=#{groupName}
    </select>
    <select id="selectGg" resultType="com.unis.base.common.vo.GetPagePersonnelVO">
        select distinct qymc, tyshxydm
        from base.d_ryxxsjy
        where qymc is not null
    </select>
    <select id="selectBm" resultType="com.unis.base.common.vo.BmRecordVO">
        select distinct bmmc qymc, bmdm tyshxydm
        from base.d_ryxxsjy
        where qymc LIKE CONCAT('%', #{qymc}, '%')
       and bmmc is not null
    </select>
    <select id="seleRy" resultType="com.unis.base.common.vo.BRyVO">
        select rybh, ryxm
        from base.d_ryxxsjy
        where bmmc=#{bmmc}
    </select>
    <select id="selectRtt" resultType="com.unis.base.common.vo.BRyVO">
        select rybh, ryxm
        from base.d_ryxxsjy
        where tyshxydm=#{tyshxydm}
    </select>
    <select id="listInfo" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select
        *
        from base.d_ryxxsjy
        <where>
            <if test="deptCodes != null ">
                and bmmc in
                <foreach collection="deptCodes" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>

    </select>
    <select id="selectAll" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select
        *
        from base.d_ryxxsjy
        <where>
            <if test="qymc != null ">
                and qymc in
                <foreach collection="qymc" item="items" index="index" open="(" separator="," close=")">
                    #{items}
                </foreach>
            </if>
        </where>
    </select>
    <select id="selectPerAll" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select
        *
        from base.d_ryxxsjy
        <where>
            <if test="rybh != null ">
                and rybh in
                <foreach collection="rybh" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="selectPersons" resultType="java.lang.String">
        select rybh
        from base.d_ryxxsjy
        where group_id  LIKE CONCAT('%', #{groupId}, '%')
    </select>
    <select id="selectGroupInfo" resultType="com.unis.base.server.entity.TalentGroupDO">
        select *
        from base.d_talent_group
        where group_id=#{groupId}
    </select>


</mapper>
