<?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.PersonnelMxDao">

    <sql id="baseColumn">
        bd.rybh,
         bd.qymc,
        bd.xb,
        bd.cssj,
        bd.gddh,
        bd.bmmc,
        bd.gejj,
        bd.ryxm,
        bd.zzmm,
         bd.lxdh,
       bd.gwxz,
        bd.zwmc,
          bd.rzrq
    </sql>
    <insert id="insertBatch">
        insert into base.d_ryxxsjy (rybh, qymc, xb, cssj, gddh, bmmc, grjj, ryxm, zzmm, lxdh, gwxz, zwmc, rzrq,
                                    association, zmmc, dzyx, rotaterotate_type, head, annex, unittype, status, newly,
                                    org_code,person_status,bmdm)
        VALUES (
            #{rybh},
            #{qymc},
            #{xb},
            #{cssj},
            #{gddh},
            #{bmmc},
            #{grjj},
            #{ryxm},
            #{zzmm},
            #{lxdh},
            #{gwxz},
            #{zwmc},
            #{rzrq},
            #{association},
            #{zmmc},
            #{dzyx},
            #{rotaterotateType},
            #{head},
            #{annex},
            #{unittype},
            #{status},
            #{newly},
            #{orgCode},
            #{personStatus},
            #{bmdm}
            )
    </insert>
    <!--<insert id="insertQiyi">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_qiye (qymc,unittype,tyshxydm,personnumber)
            VALUES
            (
            #{obj.qymc},
            #{obj.unittype},
            #{obj.tyshxydm},
            #{obj.personnumber}
            )
        </foreach>
    </insert>-->
    <insert id="insertEducation">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_education
            (rybh,quan_educational_form,quan_speciality,quan_highest_education,quan_institutions
            ,quan_academic,quan_foodstuff,telphone)
            VALUES
            (
            #{obj.rybh},
            #{obj.quanEducationalForm},
            #{obj.quanSpeciality},
            #{obj.quanHighestEducation},
            #{obj.quanInstitutions},
            #{obj.quanAcademic},
            #{obj.quanFoodstuff},
            #{obj.telphone}
            )
        </foreach>
    </insert>
    <insert id="insertReport">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_report (rybh,title,report_number,release_time,report_name
            ,honorary_name,authoritys,honorary_title,telphone)
            VALUES
            (
            #{obj.rybh},
            #{obj.title},
            #{obj.reportNumber},
            #{obj.releaseTime},
            #{obj.reportName},
            #{obj.honoraryName},
            #{obj.authoritys},
            #{obj.honoraryTitle},
            #{obj.telphone}
            )
        </foreach>
    </insert>
    <insert id="insertSkill">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_skill (rybh,skill_level,skill_date,skill_name)
            VALUES
            (
            #{obj.rybh},
            #{obj.skillLevel},
            #{obj.skillDate},
            #{obj.skillName}
            )
        </foreach>
    </insert>
    <insert id="insertRotater">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_rotaterotate (rybh,serial_number,business_type,business_time)
            VALUES
            (
            #{obj.rybh},
            #{obj.serialNumber},
            #{obj.businessType},
            #{obj.businessTime}
            )
        </foreach>
    </insert>
    <insert id="insertFeieducation">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_feieducation (rybh,fei_educational_form,fei_speciality,fei_highest_education,
            fei_institutions,fei_academic,fei_foodstuff)
            VALUES
            (
            #{obj.rybh},
            #{obj.feiEducationalForm},
            #{obj.feiSpeciality},
            #{obj.feiHighestEducation},
            #{obj.feiInstitutions},
            #{obj.feiAcademic},
            #{obj.feiFoodstuff}
            )
        </foreach>
    </insert>
    <insert id="insertCertificate">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_certificates (rybh,skill_level,speciality,acquisition_time)
            VALUES
            (
            #{obj.rybh},
            #{obj.skillLevel},
            #{obj.speciality},
            #{obj.acquisitionTime}
            )
        </foreach>
    </insert>


    <update id="updateN">
        update base.d_ryxxsjy
        set person_status = 2 where rybh like  CONCAT('%', #{rybh}, '%')
    </update>
    <update id="updateY">
        update base.d_ryxxsjy
        set person_status = 1 where rybh like CONCAT('%', #{rybh}, '%')
    </update>
    <update id="ubdateRy">
        update base.d_ryxxsjy
        set qymc = #{qymc}, xb = #{xb}, cssj = #{cssj}, gddh = #{gddh}, bmmc = #{bmmc}, zmmc = #{zmmc}
            , dzyx = #{dzyx}, unittype = #{unittype}, grjj = #{grjj}, ryxm = #{ryxm}, zzmm = #{zzmm}, lxdh = #{lxdh}, gwxz = #{gwxz}, zwmc = #{zwmc}
            , rzrq = #{rzrq}, association = #{association}, rotaterotate_type = #{rotaterotateType}, head = #{head}, annex = #{annex} where qymc=#{qymc} and lxdh=#{lxdh}
    </update>
    <insert id="updateS">
        insert into base.d_skill (skill_level, skill_date, skill_name, rybh)
        values (#{skillLevel}, #{skillDate}, #{skillName}, #{rybh})
    </insert>
    <insert id="updateC">
        insert into base.d_certificates (skill_level, speciality, acquisition_time, rybh)
        values (#{skillLevel}, #{speciality}, #{acquisitionTime}, #{rybh})
    </insert>
    <insert id="updateR">
        insert into base.d_rotaterotate (serial_number, business_type, business_time, rybh)
        values (#{serialNumber}, #{businessType}, #{businessTime}, #{rybh})
    </insert>
    <insert id="updateRe">
        insert into base.d_report (title, report_number, release_time, report_name, honorary_name, authoritys,
                                   honorary_title, rybh)
        values
            (#{title}, #{reportNumber}, #{releaseTime}, #{reportName}, #{honoraryName}, #{authoritys}, #{honoraryTitle}, #{rybh})
    </insert>
    <insert id="updateFe">
        insert into base.d_feieducation (fei_educational_form, fei_speciality, fei_highest_education, fei_institutions,
                                         fei_academic, fei_foodstuff, rybh)
        values (#{feiEducationalForm}, #{feiSpeciality}, #{feiHighestEducation}, #{feiInstitutions}, #{feiAcademic}, #{feiFoodstuff}, #{rybh})
    </insert>
    <insert id="updateEd">
        insert into base.d_education (quan_educational_form, quan_speciality, quan_highest_education, quan_institutions,
                                      quan_academic, quan_foodstuff, rybh)
        values (#{quanEducationalForm}, #{quanSpeciality}, #{quanHighestEducation}, #{quanInstitutions}, #{quanAcademic}, #{quanFoodstuff}, #{rybh})
    </insert>
    <delete id="deleteS">
        delete
        from base.d_skill
        where rybh=#{rybh}
    </delete>
    <delete id="deleteC">
        delete
        from base.d_certificates
        where rybh=#{rybh}
    </delete>
    <delete id="deleteR">
        delete
        from base.d_rotaterotate
        where rybh=#{rybh}
    </delete>
    <delete id="deleteRe">
        delete
        from base.d_report
        where rybh=#{rybh}
    </delete>
    <delete id="deleteFe">
        delete
        from base.d_feieducation
        where rybh=#{rybh}
    </delete>
    <delete id="deleteEd">
        delete
        from base.d_education
        where rybh=#{rybh}
    </delete>

    <!-- <delete id="deleteOld">
         truncate table base.d_qiye
     </delete>-->
    <!--<insert id="insertBatch">
        <foreach collection="list" item="obj" separator=";">
            INSERT INTO base.d_gerenmx
            (
            rybh
            , name
            , kddm
            , birthday
            , gender
            , political_outlook
            , duties_code
            , duties
            , id
            , post_code
            , telephone
            , report_number
            , authoritys
            , institutions
            , academic
            , qymc
            , tyshxydm
            , honorary_title
            , jyxs
            )
            VALUES
            (
            #{obj.rybh},
            #{obj.name},
            #{obj.kddm},
            #{obj.birthday},
            #{obj.gender},
            #{obj.politicalOutlook},
            #{obj.dutiesCode},
            #{obj.duties},
            #{obj.id},
            #{obj.postCode},
            #{obj.telephone},
            #{obj.reportNumber},
            #{obj.authoritys},
            #{obj.institutions},
            #{obj.academic},
            #{obj.qymc},
            #{obj.tyshxydm},
            #{obj.honoraryTitle},
            #{obj.jyxs}
            )
        </foreach>
    </insert>-->

    <!--    <delete id="deleteAll">-->
    <!--        truncate table base.d_gerenmx-->
    <!--    </delete>-->

    <!--<select id="selectDetails" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select rybh
             , ryxm  name
             , kddm
             , cssj  birthday
             , xb    gender
             , zzmm  politicalOutlook
             , zwdm  dutiesCode
             , zwmc  duties
             , rybh id
             , gwdm  postCode
             , lxdh  telephone
             , jbzs  reportNumber
             , fzdw  authoritys
             , byyx  institutions
             , xw    academic
             , qymc
             , tyshxydm
             , rych  honoraryTitle
             , jyxs
        from base.d_ryxxsjy
    </select>-->
    <select id="selectBatch" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select *
        from base.d_gerenmx

    </select>
    <select id="selectPerson" resultType="com.unis.base.common.vo.PageCountVO">
        select count(rybh) personnumber, qymc
        from base.d_ryxxsjy
        <where>
            <if test="qymc!=null and qymc!=''">
                and qymc =#{qymc}
            </if>
            <if test="unittype!=null and unittype!=''">
                and unittype=#{unittype}
            </if>
            <if test="orgCode!=null and orgCode!=''">
                and org_code=#{orgCode}
            </if>
            <if test="deptId!=null and deptId!=''">
                and bmdm=#{deptId}
            </if>
        </where>
        group by qymc
    </select>
    <select id="selectPersonDetails" resultType="com.unis.base.common.vo.PersonnelVO">
        select ryxm name, xb gender, zwmc post, zzmm politicalOutlook, lxdh telephone
        from base.d_ryxxsjy
        where ryxm=#{ryxm}
    </select>
    <select id="selectSmal" resultType="com.unis.base.common.vo.PersonnelVO">
        select count(rybh) personnumber, tyshxydm
        from base.d_ryxxsjy

        group by tyshxydm
    </select>
    <select id="selectDetails" resultType="com.unis.base.server.entity.PersonnelDO">
        select ryxm name,
               xb   gender,
               zwmc post,
               zzmm politicalOutlook,
               lxdh telephone,
               person_status,
               rybh,
               newly
        from base.d_ryxxsjy
        <where>
            qymc = #{qymc}
            <if test="ryxm!=null and ryxm!=''">
                and ryxm like  CONCAT('%', #{ryxm}, '%')
            </if>
            <if test="personStatus!=null and personStatus!=''">
                and person_status=#{personStatus}
            </if>
        </where>
    </select>
    <select id="selectduty" resultType="com.unis.base.server.entity.QyiDo">
        SELECT
            qy.qymc,
            qy.tyshxydm,
            qy.qyxz,
            ry.kddm,
            ry.newly,
            sub.unittype,
            COUNT(t1.rybh) personnumber
        FROM
            d_ryxxsjy ry
        INNER JOIN
            tz_slqy qy ON ry.tyshxydm = qy.tyshxydm
        LEFT JOIN
            d_ryxxsjy t1 ON t1.rybh = ry.rybh AND t1.qyzt = 1
        LEFT JOIN
            (SELECT
                qymc,
                unittype
            FROM
                (SELECT
                qymc,
                unittype,
                ROW_NUMBER() OVER(PARTITION BY qymc ORDER BY create_date ASC) as rn
                FROM d_ryxxsjy) t
            WHERE t.rn = 1) sub
        ON sub.qymc = ry.qymc
        <where>
            ry.status = 1
            and qy.qymc IS NOT NULL
            <if test="qymc!=null and qymc!=''">
                AND qy.qymc LIKE '%' || #{qymc} || '%'
            </if>
            <if test="unittype!=null and unittype!=''">
                AND sub.unittype=#{unittype}
            </if>
            <if test="orgCode!=null and orgCode!=''">
                AND qy.org_code=#{orgCode}
            </if>
            <if test="deptId!=null and deptId!=''">
                AND ry.bmdm=#{deptId}
            </if>
        </where>
        GROUP BY
        qy.qymc,
        qy.tyshxydm,
        qy.qyxz,
        ry.kddm,
        ry.newly,
        sub.unittype

    </select>
    <select id="selectdutys" resultType="com.unis.base.common.vo.PagePersonnelVO">
        select distinct qymc, tyshxydm, unittype
        from base.d_ryxxsjy
        <where>
            <if test="qymc!=null and qymc!=''">
                and qymc=#{qymc}
            </if>
            <if test="unittype!=null and unittype!=''">
                and unittype=#{unittype}
            </if>
        </where>
    </select>
    <select id="selectPersons" resultType="com.unis.base.common.vo.PageCountVO">
        select count(rybh) personnumber, tyshxydm
        from base.d_ryxxsjy
        <where>
            <if test="qymc!=null and qymc!=''">
                and qymc=#{qymc}
            </if>
            <if test="unittype!=null and unittype!=''">
                and unittype=#{unittype}
            </if>
        </where>
        group by tyshxydm
    </select>
    <select id="selectrybh" resultType="java.lang.String">
        select rybh
        from base.d_ryxxsjy
        where ryxm=#{ryxm}
    </select>
    <select id="selectGroup" resultType="java.lang.String">
        select group_id
        from base.d_ryxxsjy
        where ryxm=#{ryxm}
    </select>
    <select id="selectGroupName" resultType="java.lang.String">
        select group_name
        from base.d_talent_group
        where group_id=#{groupId}
    </select>
    <select id="selectRybhd" resultType="java.lang.String">
        select rybh
        from base.d_ryxxsjy
        where lxdh=#{lxdh}
    </select>
    <select id="selectPer" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select rybh,
               qymc,
               xb,
               cssj,
               gddh,
               bmmc,
               grjj,
               ryxm,
               zzmm,
               lxdh,
               gwxz,
               zwmc,
               rzrq,
               group_id,
               association,
               zmmc,
               dzyx,
               rotaterotate_type,
               head,
               annex,
               unittype,
               status,
               newly
        from base.d_ryxxsjy
        where rybh LIKE CONCAT('%', #{rybh}, '%')
    </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="selectDetailzs" resultType="com.unis.base.server.entity.PersonnelDO">
        select ryxm name, xb gender, zwmc post, zzmm politicalOutlook, lxdh telephone, person_status
        from base.d_ryxxsjy
        where rybh like CONCAT('%', #{rybh}, '%')
    </select>
    <select id="selectduty1" resultType="com.unis.base.server.entity.QyiDo">
        SELECT
            qy.qymc,
            qy.tyshxydm,
            qy.qyxz,
            ry.kddm,
            ry.newly,
            sub.unittype,
            COUNT(t1.rybh) personnumber
        FROM
            d_ryxxsjy ry
        INNER JOIN
            tz_slqy qy ON ry.tyshxydm = qy.tyshxydm
        LEFT JOIN
            d_ryxxsjy t1 ON t1.rybh = ry.rybh AND t1.qyzt = 1
        LEFT JOIN
            (SELECT
                qymc,
                unittype
            FROM
            (SELECT
                qymc,
                unittype,
                ROW_NUMBER() OVER(PARTITION BY qymc ORDER BY create_date ASC) as rn
            FROM d_ryxxsjy) t
            WHERE t.rn = 1) sub
        ON sub.qymc = ry.qymc
        <where>
            ry.status = 1
            <if test="qymc!=null and qymc!=''">
                AND qy.qymc  like CONCAT('%', #{qymc}, '%')
            </if>
            <if test="orgCode!=null and orgCode!=''">
                AND qy.org_code = #{orgCode}
            </if>

            <if test="unittype!=null and unittype!=''">
                AND sub.unittype=#{unittype}
            </if>
        </where>
        GROUP BY
            qy.qymc,
            qy.tyshxydm,
            qy.qyxz,
            ry.kddm,
            ry.newly,
            sub.unittype
    </select>
    <select id="selectPerson1" resultType="com.unis.base.common.vo.PageCountVO">
        select count(rybh) personnumber, tyshxydm,qymc
        from base.d_ryxxsjy
        <where>
            <if test="qymc!=null and qymc!=''">
                and qymc  like CONCAT('%', #{qymc}, '%')
            </if>
            <if test="unittype!=null and unittype!=''">
                and unittype=#{unittype}
            </if>
            <if test="orgCode!=null and orgCode!=''">
                and org_code=#{orgCode}
            </if>
        </where>
        group by tyshxydm,qymc order by tyshxydm
    </select>
    <select id="selectTy" resultType="java.lang.String">
        select tyshxydm
        from base.d_ryxxsjy
        where qymc=#{qymc}
    </select>
    <select id="selectByDeptId" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select rybh,
               qymc,
               xb,
               cssj,
               gddh,
               bmmc,
               grjj,
               ryxm,
               zzmm,
               lxdh,
               gwxz,
               zwmc,
               rzrq,
               group_id,
               association,
               zmmc,
               dzyx,
               rotaterotate_type,
               head,
               annex,
               unittype,
               status,
               newly
        from base.d_ryxxsjy
        where qymc = #{deptId}
    </select>
    <select id="selectByGroupId" resultType="com.unis.base.common.vo.PersonnelMxVO">
        select rybh,
               qymc,
               xb,
               cssj,
               gddh,
               bmmc,
               grjj,
               ryxm,
               zzmm,
               lxdh,
               gwxz,
               zwmc,
               rzrq,
               group_id,
               association,
               zmmc,
               dzyx,
               rotaterotate_type,
               head,
               annex,
               unittype,
               status,
               newly
        from base.d_ryxxsjy
        where group_id like CONCAT('%', #{groupId}, '%')
    </select>


</mapper>
