<?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.engine.organization.mapper.version.CompanyVersionMapper">
    <resultMap id="BaseResultMap" type="com.engine.organization.entity.version.HrmSubCompanyVersion">
        <result column="id" property="id"/>
        <result column="subcomid" property="subComId"/>
        <result column="subcompanydesc" property="subCompanyDesc"/>
        <result column="subcompanycode" property="subCompanyCode"/>
        <result column="subcompanyname" property="subCompanyName"/>
        <result column="supssubcomid" property="supSubComId"/>
        <result column="showorder" property="showOrder"/>
        <result column="canceled" property="canceled"/>
        <result column="description" property="description"/>
        <result column="operator" property="operator"/>
        <result column="version" property="version"/>
        <result column="operate_time" property="operateTime"/>
    </resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
        t.id ,t.subcomid ,t.subcompanydesc ,t.subcompanycode ,t.subcompanyname ,t.supsubcomid ,
        t.showorder ,t.canceled ,t.description ,t.operator ,t.version ,t.operate_time
    </sql>

    <select id="getMaxVersion" resultMap="BaseResultMap" parameterType="java.lang.String">
        select
        <include refid="baseColumns"/>
        from hrmsubcompany_version t where t.subcomid=#{subComId} order by t.version desc limit 1
    </select>

    <select id="getMaxVersion" resultMap="BaseResultMap" parameterType="java.lang.String" databaseId="oracle">
        select * from (select <include refid="baseColumns"/> from hrmsubcompany_version t where t.subcomid=#{subComId} order by t.version desc) where rownum=1
    </select>
    <select id="getMaxVersion" resultMap="BaseResultMap" parameterType="java.lang.String" databaseId="sqlserver">
        select top  1
        <include refid="baseColumns"/>
        from hrmsubcompany_version t where t.subcomid=#{subComId} order by t.version desc
    </select>

    <insert id="insertHrmSubComVersion">
        insert into hrmsubcompany_version
        (subcomid ,subcompanydesc ,subcompanycode ,subcompanyname ,supsubcomid ,
         showorder ,canceled ,description ,operator ,version ,operate_time)
        values (#{subComId}, #{subCompanyDesc}, #{subCompanyCode}, #{subCompanyName}, #{supSubComId},
                #{showOrder}, #{canceled}, #{description}, #{operator}, #{version}, #{operateTime})
    </insert>

    <select  id="getMaxDeptVersion" resultType="com.engine.organization.entity.version.HrmDepartmentVersion">
        select * from hrmdepartment_version where departmentid=#{departmentid} order by version desc limit 1
    </select>
    <select  id="getMaxDeptVersion" resultType="com.engine.organization.entity.version.HrmDepartmentVersion" databaseId="oracle">
        select * from (select * from hrmdepartment_version where departmentid=#{departmentid} order by version desc) where rownum=1
    </select>
    <select  id="getMaxDeptVersion" resultType="com.engine.organization.entity.version.HrmDepartmentVersion" databaseId="sqlserver">
        select top  1 * from hrmdepartment_version where departmentid=#{departmentid} order by version desc
    </select>

    <insert id="insertHrmDeptVersion">
        insert into hrmdepartment_version
            (departmentid, departmentmark, departmentcode, departmentname, subcompanyid1, supdepid,
             bmfzr, showorder, canceled, description, operator, version, operate_time)
             VALUES (#{departmentId}, #{departmentMark}, #{departmentCode}, #{departmentName}, #{subCompanyId1}, #{supDepId},
                     #{bmfzr}, #{showOrder}, #{canceled}, #{description}, #{operator}, #{version}, #{operateTime})
    </insert>
    <select id="getMaxHrmVersion" resultType="com.engine.organization.entity.version.HrmResourceVersion">
        select * from hrmresource_version where resourceid=#{resourceid} order by version desc limit 1
    </select>
    <select id="getMaxHrmVersion" resultType="com.engine.organization.entity.version.HrmResourceVersion" databaseId="oracle">
        select * from (select * from hrmresource_version where resourceid=#{resourceid} order by version desc) where rownum=1
    </select>
    <select id="getMaxHrmVersion" resultType="com.engine.organization.entity.version.HrmResourceVersion" databaseId="sqlserver">
        select top  1 * from hrmresource_version where resourceid=#{resourceid} order by version desc
    </select>

    <insert id="insertHrmResourceVersion"  parameterType="com.engine.organization.entity.version.HrmResourceVersion">
        insert into hrmresource_version
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="resourceid != null">resourceid,</if>
            <if test="companystartdate != null">companystartdate,</if>
            <if test="companyworkyear != null">companyworkyear,</if>
            <if test="workstartdate != null">workstartdate,</if>
            <if test="workyear != null">workyear,</if>
            <if test="usekind != null">usekind,</if>
            <if test="jobcall != null">jobcall,</if>
            <if test="accumfundaccount != null">accumfundaccount,</if>
            <if test="birthplace != null">birthplace,</if>
            <if test="folk != null">folk,</if>
            <if test="residentphone != null">residentphone,</if>
            <if test="residentpostcode != null">residentpostcode,</if>
            <if test="extphone != null">extphone,</if>
            <if test="managerstr != null">managerstr,</if>
            <if test="status != null">status,</if>
            <if test="fax != null">fax,</if>
            <if test="islabouunion != null">islabouunion,</if>
            <if test="weight != null">weight,</if>
            <if test="tempresidentnumber != null">tempresidentnumber,</if>
            <if test="probationenddate != null">probationenddate,</if>
            <if test="countryid != null">countryid,</if>
            <if test="passwdchgdate != null">passwdchgdate,</if>
            <if test="lloginid != null">lloginid,</if>
            <if test="dsporder != null">dsporder,</if>
            <if test="passwordstate != null">passwordstate,</if>
            <if test="accounttype != null">accounttype,</if>
            <if test="belongto != null">belongto,</if>
            <if test="messagerurl != null">messagerurl,</if>
            <if test="accountname != null">accountname,</if>
            <if test="loginid != null">loginid,</if>
            <if test="password != null">password,</if>
            <if test="lastname != null">lastname,</if>
            <if test="sex != null">sex,</if>
            <if test="birthday != null">birthday,</if>
            <if test="nationality != null">nationality,</if>
            <if test="maritalstatus != null">maritalstatus,</if>
            <if test="telephone != null">telephone,</if>
            <if test="mobile != null">mobile,</if>
            <if test="mobilecall != null">mobilecall,</if>
            <if test="email != null">email,</if>
            <if test="locationid != null">locationid,</if>
            <if test="workroom != null">workroom,</if>
            <if test="homeaddress != null">homeaddress,</if>
            <if test="resourcetype != null">resourcetype,</if>
            <if test="startdate != null">startdate,</if>
            <if test="enddate != null">enddate,</if>
            <if test="jobtitle != null">jobtitle,</if>
            <if test="jobactivitydesc != null">jobactivitydesc,</if>
            <if test="joblevel != null">joblevel,</if>
            <if test="seclevel != null">seclevel,</if>
            <if test="departmentid != null">departmentid,</if>
            <if test="subcompanyid1 != null">subcompanyid1,</if>
            <if test="costcenterid != null">costcenterid,</if>
            <if test="managerid != null">managerid,</if>
            <if test="assistantid != null">assistantid,</if>
            <if test="bankid1 != null">bankid1,</if>
            <if test="accountid1 != null">accountid1,</if>
            <if test="resourceimageid != null">resourceimageid,</if>
            <if test="certificatenum != null">certificatenum,</if>
            <if test="nativeplace != null">nativeplace,</if>
            <if test="educationlevel != null">educationlevel,</if>
            <if test="bememberdate != null">bememberdate,</if>
            <if test="bepartydate != null">bepartydate,</if>
            <if test="workcode != null">workcode,</if>
            <if test="regresidentplace != null">regresidentplace,</if>
            <if test="healthinfo != null">healthinfo,</if>
            <if test="residentplace != null">residentplace,</if>
            <if test="policy != null">policy,</if>
            <if test="degree != null">degree,</if>
            <if test="height != null">height,</if>
            <if test="classification != null">classification,</if>
            <if test="description != null">description,</if>
            <if test="operator != null">operator,</if>
            <if test="version != null">version,</if>
            <if test="operateTime != null">operate_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id,jdbcType=INTEGER},</if>
            <if test="resourceid != null">#{resourceid,jdbcType=INTEGER},</if>
            <if test="companystartdate != null">#{companystartdate,jdbcType=TIMESTAMP},</if>
            <if test="companyworkyear != null">#{companyworkyear,jdbcType=INTEGER},</if>
            <if test="workstartdate != null">#{workstartdate,jdbcType=TIMESTAMP},</if>
            <if test="workyear != null">#{workyear,jdbcType=INTEGER},</if>
            <if test="usekind != null">#{usekind,jdbcType=INTEGER},</if>
            <if test="jobcall != null">#{jobcall,jdbcType=INTEGER},</if>
            <if test="accumfundaccount != null">#{accumfundaccount,jdbcType=VARCHAR},</if>
            <if test="birthplace != null">#{birthplace,jdbcType=VARCHAR},</if>
            <if test="folk != null">#{folk,jdbcType=VARCHAR},</if>
            <if test="residentphone != null">#{residentphone,jdbcType=VARCHAR},</if>
            <if test="residentpostcode != null">#{residentpostcode,jdbcType=VARCHAR},</if>
            <if test="extphone != null">#{extphone,jdbcType=VARCHAR},</if>
            <if test="managerstr != null">#{managerstr,jdbcType=VARCHAR},</if>
            <if test="status != null">#{status,jdbcType=INTEGER},</if>
            <if test="fax != null">#{fax,jdbcType=VARCHAR},</if>
            <if test="islabouunion != null">#{islabouunion,jdbcType=CHAR},</if>
            <if test="weight != null">#{weight,jdbcType=INTEGER},</if>
            <if test="tempresidentnumber != null">#{tempresidentnumber,jdbcType=VARCHAR},</if>
            <if test="probationenddate != null">#{probationenddate,jdbcType=TIMESTAMP},</if>
            <if test="countryid != null">#{countryid,jdbcType=INTEGER},</if>
            <if test="passwdchgdate != null">#{passwdchgdate,jdbcType=TIMESTAMP},</if>
            <if test="lloginid != null">#{lloginid,jdbcType=VARCHAR},</if>
            <if test="dsporder != null">#{dsporder,jdbcType=INTEGER},</if>
            <if test="passwordstate != null">#{passwordstate,jdbcType=INTEGER},</if>
            <if test="accounttype != null">#{accounttype,jdbcType=INTEGER},</if>
            <if test="belongto != null">#{belongto,jdbcType=INTEGER},</if>
            <if test="messagerurl != null">#{messagerurl,jdbcType=VARCHAR},</if>
            <if test="accountname != null">#{accountname,jdbcType=VARCHAR},</if>
            <if test="loginid != null">#{loginid,jdbcType=VARCHAR},</if>
            <if test="password != null">#{password,jdbcType=VARCHAR},</if>
            <if test="lastname != null">#{lastname,jdbcType=VARCHAR},</if>
            <if test="sex != null">#{sex,jdbcType=CHAR},</if>
            <if test="birthday != null">#{birthday,jdbcType=CHAR},</if>
            <if test="nationality != null">#{nationality,jdbcType=INTEGER},</if>
            <if test="maritalstatus != null">#{maritalstatus,jdbcType=CHAR},</if>
            <if test="telephone != null">#{telephone,jdbcType=VARCHAR},</if>
            <if test="mobile != null">#{mobile,jdbcType=VARCHAR},</if>
            <if test="mobilecall != null">#{mobilecall,jdbcType=VARCHAR},</if>
            <if test="email != null">#{email,jdbcType=VARCHAR},</if>
            <if test="locationid != null">#{locationid,jdbcType=INTEGER},</if>
            <if test="workroom != null">#{workroom,jdbcType=VARCHAR},</if>
            <if test="homeaddress != null">#{homeaddress,jdbcType=VARCHAR},</if>
            <if test="resourcetype != null">#{resourcetype,jdbcType=CHAR},</if>
            <if test="startdate != null">#{startdate,jdbcType=TIMESTAMP},</if>
            <if test="enddate != null">#{enddate,jdbcType=TIMESTAMP},</if>
            <if test="jobtitle != null">#{jobtitle,jdbcType=INTEGER},</if>
            <if test="jobactivitydesc != null">#{jobactivitydesc,jdbcType=VARCHAR},</if>
            <if test="joblevel != null">#{joblevel,jdbcType=INTEGER},</if>
            <if test="seclevel != null">#{seclevel,jdbcType=INTEGER},</if>
            <if test="departmentid != null">#{departmentid,jdbcType=INTEGER},</if>
            <if test="subcompanyid1 != null">#{subcompanyid1,jdbcType=INTEGER},</if>
            <if test="costcenterid != null">#{costcenterid,jdbcType=INTEGER},</if>
            <if test="managerid != null">#{managerid,jdbcType=INTEGER},</if>
            <if test="assistantid != null">#{assistantid,jdbcType=INTEGER},</if>
            <if test="bankid1 != null">#{bankid1,jdbcType=INTEGER},</if>
            <if test="accountid1 != null">#{accountid1,jdbcType=VARCHAR},</if>
            <if test="resourceimageid != null">#{resourceimageid,jdbcType=INTEGER},</if>
            <if test="certificatenum != null">#{certificatenum,jdbcType=VARCHAR},</if>
            <if test="nativeplace != null">#{nativeplace,jdbcType=VARCHAR},</if>
            <if test="educationlevel != null">#{educationlevel,jdbcType=INTEGER},</if>
            <if test="bememberdate != null">#{bememberdate,jdbcType=TIMESTAMP},</if>
            <if test="bepartydate != null">#{bepartydate,jdbcType=TIMESTAMP},</if>
            <if test="workcode != null">#{workcode,jdbcType=VARCHAR},</if>
            <if test="regresidentplace != null">#{regresidentplace,jdbcType=VARCHAR},</if>
            <if test="healthinfo != null">#{healthinfo,jdbcType=CHAR},</if>
            <if test="residentplace != null">#{residentplace,jdbcType=VARCHAR},</if>
            <if test="policy != null">#{policy,jdbcType=VARCHAR},</if>
            <if test="degree != null">#{degree,jdbcType=VARCHAR},</if>
            <if test="height != null">#{height,jdbcType=VARCHAR},</if>
            <if test="classification != null">#{classification,jdbcType=CHAR},</if>
            <if test="description != null">#{description,jdbcType=VARCHAR},</if>
            <if test="operator != null">#{operator,jdbcType=VARCHAR},</if>
            <if test="version != null">#{version,jdbcType=FLOAT},</if>
            <if test="operateTime != null">#{operateTime,jdbcType=TIMESTAMP},</if>
        </trim>
    </insert>


    <select id="getHrmSubComp" resultType="com.engine.organization.entity.version.HrmSubCompanyDto">
        select id ,subcompanyname, subcompanydesc,companyid,supsubcomid,canceled,subcompanycode,showorder from HrmSubCompany
        where id=#{id}
    </select>
    <select id="getHrmDepartment" resultType="com.engine.organization.entity.version.HrmDepartmentDto">
        select a.id,a.departmentmark,a.departmentname,a.subcompanyid1,a.supdepid,a.canceled,
               a.departmentcode,a.showorder,b.bmfzr from HrmDepartment a
               left join hrmdepartmentdefined b on a.id=b.deptid
        where a.id=#{id}
    </select>
    <select id="getHrmResource" resultType="com.engine.organization.entity.version.HrmResourceDto">
        select id,departmentid,dsporder,subcompanyid1,jobtitle,mobile,telephone,managerid,lastname from HrmResource
        where id=#{id}
    </select>

    <select id="getSubComVersionList" resultType="com.engine.organization.entity.version.HrmSubCompanyVersion">
        select a.id,a.subComId,a.subCompanyDesc,a.subCompanyCode,a.subCompanyName,a.supSubComId,a.showOrder,a.canceled,
        a.description,a.operator,a.version,a.operate_time as operateTime,b.subcompanyname as supSubComName from HRMSUBCOMPANY_VERSION a
               left join hrmsubcompany b on a.supSubComId=b.id
        <where>
            <if test="subComId !=null">
                and a.subComId = #{subComId}
            </if>
            <if test="version !=null">
                and a.version = #{version}
            </if>
        </where>
        ORDER BY a.VERSION ASC
    </select>
    <select id="getDeptVersionList" resultType="com.engine.organization.entity.version.HrmDepartmentVersion">
        select a.id,a.departmentid,a.departmentmark,a.departmentcode,a.departmentname,a.subcompanyid1,
        a.supdepid,a.bmfzr,a.showorder,a.canceled,a.description,a.operator,a.version,
        a.operate_time as operateTime,b.subcompanyname,c.departmentname as supDepName,d.lastname as bmfzrName from HRMDEPARTMENT_VERSION a
        left join hrmsubcompany b on a.subcompanyid1=b.id
        left join hrmdepartment c on a.supdepid=c.id
        left join hrmresource d on a.bmfzr=d.id
        <where>
            <if test="departmentid !=null">
                and a.departmentid = #{departmentid}
            </if>
            <if test="version !=null">
                and a.version = #{version}
            </if>
        </where>
        ORDER BY a.VERSION ASC
    </select>
    <select id="getResourceVersionList" resultType="com.engine.organization.entity.version.HrmResourceVersion">
        select a.id,a.resourceid,a.companystartdate,a.companyworkyear,a.workstartdate,a.workyear,a.usekind,
        a.jobcall,a.accumfundaccount,a.birthplace,a.folk,a.residentphone,a.residentpostcode,a.extphone,
        a.managerstr,a.status,a.fax,a.islabouunion,a.weight,a.tempresidentnumber,a.probationenddate,
        a.countryid,a.passwdchgdate,a.lloginid,a.dsporder,a.passwordstate,a.accounttype,a.belongto,
        a.messagerurl,a.accountname,a.loginid,a.password,a.lastname,a.sex,a.birthday,a.nationality,
        a.maritalstatus,a.telephone,a.mobile,a.mobilecall,a.email,a.locationid,a.workroom,a.homeaddress,
        a.resourcetype,a.startdate,a.enddate,a.jobtitle,a.jobactivitydesc,a.joblevel,a.seclevel,
        a.departmentid,a.subcompanyid1,a.costcenterid,a.managerid,a.assistantid,a.bankid1,a.accountid1,
        a.resourceimageid,a.certificatenum,a.nativeplace,a.educationlevel,a.bememberdate,a.bepartydate,
        a.workcode,a.regresidentplace,a.healthinfo,a.residentplace,a.policy,a.degree,a.height,a.classification,
        a.description,a.operator,a.version,a.operate_time as operateTime,b.departmentname,c.subcompanyname,
        d.jobtitlename
        from HRMRESOURCE_VERSION a
        left join hrmdepartment b on a.departmentid=b.id
        left join hrmsubcompany c on a.subcompanyid1=c.id
        left join hrmjobtitles d on a.jobtitle=d.id
        <where>
            <if test="resourceid !=null">
                and a.resourceid = #{resourceid}
            </if>
            <if test="version !=null">
                and a.version = #{version}
            </if>
        </where>
        ORDER BY a.VERSION ASC
    </select>
</mapper>