<?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.jclorgmap.JclOrgMapper">

    <insert id="insertMap">
        insert into jcl_org_map
        (id, ftype, fobjid, fecid, uuid, fclass, fclassname, fnumber, fname, fleader, fleaderimg,
         fleadername, fleaderjobid, fleaderjob, fleaderlv, fleaderst, fparentid, fobjparentid,
         fplan, fonjob, fisvitual, fdatebegin, fdateend)
        values (#{id}, #{fType}, #{fObjId}, #{fEcId}, #{uuid}, #{fClass}, #{fClassName}, #{fNumber}, #{fName},
                #{fLeader}, #{fLeaderImg},
                #{fLeaderName}, #{fLeaderJobId}, #{fLeaderJob}, #{fLeaderLv}, #{fLeaderSt}, #{fParentId},
                #{fObjParentId},
                #{fPlan}, #{fOnJob}, #{fIsVitual}, #{fDateBegin}, #{fDateEnd})
    </insert>
    <update id="updateMap">
        update jcl_org_map
        <set>
            fdateend=#{yesterday},
        </set>
        where ftype=#{ftype} and fobjid=#{fobjid} and fdateend &gt; #{currentDate}
    </update>
    <delete id="deleteMap">
        delete
        from jcl_org_map
        where ftype = #{ftype}
          and fobjid = #{fobjid}
          and fdatebegin = #{currentDate}
    </delete>
    <select id="getJclOrgMapByObjID" resultType="com.engine.organization.entity.map.JclOrgMap">
        select *
        from jcl_org_map
        where fobjid = #{objId}
          and ftype = #{fType}
          AND FDATEBEGIN &lt;= #{currentDate}
          AND FDATEEND &gt;= #{currentDate}
    </select>
    <select id="getJclOrgMapByFParentId" resultType="com.engine.organization.entity.map.JclOrgMap">
        select *
        from jcl_org_map
        where fparentid = #{fparentid}
          AND FDATEBEGIN &lt;= #{currentDate}
          AND FDATEEND &gt;= #{currentDate}
    </select>
    <select id="getJclOrgMapById" resultType="com.engine.organization.entity.map.JclOrgMap">
        select *
        from jcl_org_map
        where id = #{id}
          AND FDATEBEGIN &lt;= #{currentDate}
          AND FDATEEND &gt;= #{currentDate}
    </select>
    <select id="getSumPlanAndJobByFParentId" resultType="com.engine.organization.entity.map.JclOrgMap">
        select sum(fplan) fplan,sum(fonjob) fonjob
        from jcl_org_map
        where fparentid = #{fparentid}
          AND FDATEBEGIN &lt;= #{currentDate}
          AND FDATEEND &gt;= #{currentDate}
    </select>
    <update id="updateMapById">
        update jcl_org_map
        <set>
            <if test="fplan != null">
                fplan=#{fplan},
            </if>
            <if test="fonjob != null">
                fonjob=#{fonjob},
            </if>
        </set>
        where id=#{id}
        AND FDATEBEGIN &lt;= #{currentDate}
        AND FDATEEND &gt;= #{currentDate}
    </update>

    <select id="getJclOrgMapByType" resultType="com.engine.organization.entity.map.JclOrgMap">
        select * from jcl_org_map where ftype = #{fType} AND FDATEBEGIN &lt;= #{currentDate} AND FDATEEND &gt;= #{currentDate}
    </select>
    <!--删除当天所有数据-->
    <delete id="deleteAllMap">
        delete from jcl_org_map where fdatebegin = #{currentDate}
    </delete>
    <!--修改所有失效日期-->
    <update id="updateAllMap">
        update jcl_org_map
        <set>
            fdateend=#{yesterday},
        </set>
        where fdateend &gt; #{currentDate}
    </update>

    <!--初始化人员数据-->
    <insert id="insertResToMap" databaseId="sqlserver">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleaderimg,
        fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fisvitual,fdatebegin,fdateend)
        select a.id+300000000,4,a.id,a.uuid,0,'行政维度',a.workcode,a.LASTNAME,a.messagerurl,
        c.id,c.jobtitlename,e.level_name,f.grade_name, isnull( b.id, '' ) +200000000,b.id,
        0,convert(varchar(100),getDate(),23),'2099-12-31'
        from hrmresource a
        left join jcl_org_job b ON a.JOBTITLE = b.ec_jobTitle and a.subcompanyid1=b.ec_company and a.departmentid=b.ec_department
        left join HrmJobTitles c on b.ec_jobTitle = c.id
        left join cus_fielddata d on a.id = d.id and d.scope='HrmCustomFieldByInfoType' and d.scopeid=3
        left join jcl_org_level e on d.${level}=e.id
        left join jcl_org_grade f on d.${grade}=f.id
        where a.status &lt; 4
    </insert>
    <insert id="insertResToMap" databaseId="mysql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleaderimg,
        fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fisvitual,fdatebegin,fdateend)
        select a.id+300000000,4,a.id,a.uuid,0,'行政维度',a.workcode,a.LASTNAME,a.messagerurl,
        c.id,c.jobtitlename,e.level_name,f.grade_name, ifnull( b.id, '' ) +200000000,b.id,
        0,NOW(),'2099-12-31'
        from hrmresource a
        left join jcl_org_job b ON a.JOBTITLE = b.ec_jobTitle and a.subcompanyid1=b.ec_company and a.departmentid=b.ec_department
        left join HrmJobTitles c on b.ec_jobTitle = c.id
        left join cus_fielddata d on a.id = d.id and d.scope='HrmCustomFieldByInfoType' and d.scopeid=3
        left join jcl_org_level e on d.${level}=e.id
        left join jcl_org_grade f on d.${grade}=f.id
        where a.status &lt; 4
    </insert>
    <insert id="insertResToMap" databaseId="postgresql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleaderimg,
        fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fisvitual,fdatebegin,fdateend)
        select a.id+300000000,4,a.id,a.uuid,0,'行政维度',a.workcode,a.LASTNAME,a.messagerurl,
        c.id,c.jobtitlename,e.level_name,f.grade_name, COALESCE( b.id, 0 ) +200000000,b.id,
        0,TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd'),'2099-12-31'
        from hrmresource a
        left join jcl_org_job b ON a.JOBTITLE = b.ec_jobTitle and a.subcompanyid1=b.ec_company and a.departmentid=b.ec_department
        left join HrmJobTitles c on b.ec_jobTitle = c.id
        left join cus_fielddata d on a.id = d.id and d.scope='HrmCustomFieldByInfoType' and d.scopeid=3
        left join jcl_org_level e on NULLIF(d.${level},'')=e.id
        left join jcl_org_grade f on NULLIF(d.${grade},'')=f.id
        where a.status &lt; 4
    </insert>
    <insert id="insertResToMap" parameterType="java.lang.String" databaseId="oracle">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleaderimg,
        fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fisvitual,fdatebegin,fdateend)
        select a.id+300000000,4,a.id,a.uuid,0,'行政维度',a.workcode,a.LASTNAME,a.messagerurl,
        c.id,c.jobtitlename,e.level_name,f.grade_name, nvl( b.id, '' ) +200000000,b.id,
        0,TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd'), TO_DATE('2099-12-31','yyyy-MM-dd')
        from hrmresource a
        left join jcl_org_job b ON a.JOBTITLE = b.ec_jobTitle and a.subcompanyid1=b.ec_company and a.departmentid=b.ec_department
        left join HrmJobTitles c on b.ec_jobTitle = c.id
        left join cus_fielddata d on a.id = d.id and d.scope='HrmCustomFieldByInfoType' and d.scopeid=3
        left join jcl_org_level e on ${level}=e.id
        left join jcl_org_grade f on ${grade}=f.id
        where a.status &lt; 4
    </insert>
    <!--初始化岗位-->
    <insert id="insertJobToMap" databaseId="sqlserver">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,
        fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+200000000,3,a.id,g.uuid,0,'行政维度',a.job_no,g.jobtitlename,
        ec_department+100000000,
        ec_department,
        isnull(e.fcnt,0),isnull(f.fcnt,0),0,convert(varchar(100),getDate(),23),'2099-12-31'
        from JCL_ORG_job as a
        left join (select job_id,sum(isnull(staff_num,0))  fcnt from JCL_ORG_STAFF
        where plan_id in (select id from JCL_ORG_STAFFPLAN
        where time_start &lt;= convert(varchar(100),getDate(),23) and time_end &gt; convert(varchar(100),getDate(),23)) and delete_type=0
        group by job_id) e on a.id=e.job_id
        left join ( select  c.id,count(1) fcnt from hrmresource a
                                                        left join Hrmjobtitles b on a.jobtitle=b.id and a.status&lt;4
                                                        left join jcl_org_job c on b.id=c.ec_jobtitle and a.subcompanyid1=c.ec_company and a.departmentid=c.ec_department
                    group by c.id) f
                  on f.id=a.id
        left join HrmJobTitles g on a.ec_jobTitle = g.id
        where isnull(a.delete_type,0) &lt;&gt; 1 and isnull(a.forbidden_tag,0) &lt;&gt; 1
    </insert>
    <insert id="insertJobToMap" databaseId="mysql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,
        fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+200000000,3,a.id,g.uuid,0,'行政维度',a.job_no,g.jobtitlename,
        ec_department+100000000,
        ec_department,
        ifnull(e.fcnt,0),ifnull(f.fcnt,0),0,NOW(),'2099-12-31'
        from JCL_ORG_job as a
        left join (select job_id,sum(ifnull(staff_num,0))  fcnt from JCL_ORG_STAFF
        where plan_id in (select id from JCL_ORG_STAFFPLAN
        where time_start&lt;=NOW() and time_end &gt; NOW() and delete_type=0)
        group by job_id) e on a.id=e.job_id
        left join ( select  c.id,count(1) fcnt from hrmresource a
                                                        left join Hrmjobtitles b on a.jobtitle=b.id and a.status&lt;4
                                                        left join jcl_org_job c on b.id=c.ec_jobtitle and a.subcompanyid1=c.ec_company and a.departmentid=c.ec_department
                    group by c.id) f
                  on f.id=a.id
        left join HrmJobTitles g on a.ec_jobTitle = g.id
        where ifnull(a.delete_type,0) &lt;&gt; 1 and ifnull(a.forbidden_tag,0) &lt;&gt; 1
    </insert>
    <insert id="insertJobToMap" databaseId="postgresql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,
        fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+200000000,3,a.id,g.uuid,0,'行政维度',a.job_no,g.jobtitlename,
        ec_department+100000000,
        ec_department,
        COALESCE(e.fcnt,0),COALESCE(f.fcnt,0),0,TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd'),'2099-12-31'
        from JCL_ORG_job as a
        left join (select job_id,sum(COALESCE(staff_num,0))  fcnt from JCL_ORG_STAFF
        where plan_id in (select id from JCL_ORG_STAFFPLAN
        where time_start&lt;=TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd') and time_end &gt; TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd') and delete_type=0)
        group by job_id) e on a.id=e.job_id
        left join ( select  c.id,count(1) fcnt from hrmresource a
                                                        left join Hrmjobtitles b on a.jobtitle=b.id and a.status&lt;4
                                                        left join jcl_org_job c on b.id=c.ec_jobtitle and a.subcompanyid1=c.ec_company and a.departmentid=c.ec_department
                    group by c.id) f
                  on f.id=a.id
        left join HrmJobTitles g on a.ec_jobTitle = g.id
        where COALESCE(a.delete_type,0) &lt;&gt; 1 and COALESCE(a.forbidden_tag,0) &lt;&gt; 1
    </insert>
    <insert id="insertJobToMap" databaseId="oracle">
        insert into jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,
        fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+200000000,3,a.id,g.uuid,0,'行政维度',a.job_no,g.jobtitlename,
        ec_department+100000000,
        ec_department,
        nvl(e.fcnt,0),nvl(f.fcnt,0),0,TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd'), TO_DATE('2099-12-31','yyyy-MM-dd')
        from JCL_ORG_job a
        left join (select job_id,sum(nvl(staff_num,0)) fcnt from JCL_ORG_STAFF
        where plan_id in (select id from JCL_ORG_STAFFPLAN
        where time_start&lt;=TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd') and time_end&gt;TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')) and delete_type=0
        group by job_id) e on a.id=e.job_id
        left join ( select  c.id,count(1) fcnt from hrmresource a
                                                        left join Hrmjobtitles b on a.jobtitle=b.id and a.status&lt;4
                                                        left join jcl_org_job c on b.id=c.ec_jobtitle and a.subcompanyid1=c.ec_company and a.departmentid=c.ec_department
                    group by c.id) f
                  on f.id=a.id
        left join HrmJobTitles g on a.ec_jobTitle = g.id
        where nvl(a.delete_type,0) &lt;&gt; 1 and nvl(a.forbidden_tag,0) &lt;&gt; 1
    </insert>
    <!--同步部门信息-->
    <insert id="insertDeptToMap" databaseId="sqlserver">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
        fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+100000000,2,a.id,a.uuid,0,'行政维度',a.DEPARTMENTCODE,a.departmentname,cast(b.BMFZR as varchar(10)),c.messagerurl,
        c.lastname,c.jobtitle,d.jobtitlemark,f.level_name,g.grade_name,
        (case isnull(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid+100000000 end),
        (case isnull(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid end),
        0,0,0,convert(varchar(100),getDate(),23),'2099-12-31'
        from
        HrmDepartment as a
        left join hrmdepartmentdefined as b on a.id=b.deptid
        left join hrmresource as c on cast(b.BMFZR as varchar(10))=cast(c.ID as varchar(10))
        left join hrmjobtitles as d on c.JOBTITLE=d.id
        left join cus_fielddata e on c.id = e.id and e.scope='HrmCustomFieldByInfoType' and e.scopeid=3
        left join jcl_org_level f on e.${level}=f.id
        left join jcl_org_grade g on e.${grade}=g.id
        where isnull(a.canceled,0) &lt;&gt; 1
    </insert>
    <insert id="insertDeptToMap" databaseId="mysql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
         fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
         fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+100000000,2,a.id,a.uuid,0,'行政维度',a.DEPARTMENTCODE,a.departmentname,b.BMFZR,c.messagerurl,
               c.lastname,c.jobtitle,d.jobtitlemark,f.level_name,g.grade_name,
               (case ifnull(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid+100000000 end),
               (case ifnull(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid end),
               0,0,0,NOW(),'2099-12-31'
        from
            HrmDepartment as a
                left join hrmdepartmentdefined as b on a.id=b.deptid
                left join hrmresource as c on b.BMFZR=c.ID
                left join hrmjobtitles as d on c.JOBTITLE=d.id
                left join cus_fielddata e on c.id = e.id and e.scope='HrmCustomFieldByInfoType' and e.scopeid=3
                left join jcl_org_level f on e.${level}=f.id
                left join jcl_org_grade g on e.${grade}=g.id
        where ifnull(a.canceled,0) &lt;&gt; 1
    </insert>
    <insert id="insertDeptToMap" databaseId="postgresql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
         fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
         fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+100000000,2,a.id,a.uuid,0,'行政维度',a.DEPARTMENTCODE,a.departmentname,b.BMFZR,c.messagerurl,
               c.lastname,c.jobtitle,d.jobtitlemark,f.level_name,g.grade_name,
               (case COALESCE(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid+100000000 end),
               (case COALESCE(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid end),
               0,0,0,TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd'),'2099-12-31'
        from
            HrmDepartment as a
                left join hrmdepartmentdefined as b on a.id=b.deptid
                left join hrmresource as c on NULLIF(b.BMFZR,'')=c.ID
                left join hrmjobtitles as d on c.JOBTITLE=d.id
                left join cus_fielddata e on c.id = e.id and e.scope='HrmCustomFieldByInfoType' and e.scopeid=3
                left join jcl_org_level f on NULLIF(e.${level},'')=f.id
                left join jcl_org_grade g on NULLIF(e.${grade},'')=g.id
        where COALESCE(a.canceled,'0') &lt;&gt; '1'
    </insert>
    <insert id="insertDeptToMap" databaseId="oracle">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
         fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
         fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id+100000000,2,a.id,a.uuid,0,'行政维度',a.DEPARTMENTCODE,a.departmentname,to_char(b.BMFZR),c.messagerurl,
               c.lastname,c.jobtitle,d.jobtitlemark,f.level_name,g.grade_name,
               (case nvl(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid+100000000 end),
               (case nvl(a.supdepid,0) when 0 then a.subcompanyid1 else a.supdepid end),
               0,0,0,TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd'), TO_DATE('2099-12-31','yyyy-MM-dd')
        from  HrmDepartment a
        left join hrmdepartmentdefined b on a.id=b.deptid
        left join hrmresource c on to_char(b.BMFZR)=to_char(c.ID)
        left join hrmjobtitles d on c.JOBTITLE=d.id
        left join cus_fielddata e on c.id = e.id and e.scope='HrmCustomFieldByInfoType' and e.scopeid=3
        left join jcl_org_level f on e.${level}=f.id
        left join jcl_org_grade g on e.${grade}=g.id
        where nvl(a.canceled,0) &lt;&gt; 1
    </insert>
    <!--同步分部信息-->
    <insert id="insertSubComToMap" databaseId="sqlserver">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
        fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id,1,a.id,a.uuid,0,'行政维度', '',a.subcompanyname,null,'',
        '',NULL,'','','',isnull(a.supsubcomid,0),isnull(a.supsubcomid,0),
        0,0,0,convert(varchar(100),getDate(),23),'2099-12-31' from hrmsubcompany as a
        where isnull(a.canceled,0) &lt;&gt; 1
    </insert>
    <insert id="insertSubComToMap" databaseId="mysql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
         fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
         fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id,1,a.id,a.uuid,0,'行政维度', '',a.subcompanyname,null,'',
        '',NULL,'','','',ifnull(a.supsubcomid,0),ifnull(a.supsubcomid,0),
        0,0,0,NOW(),'2099-12-31' from hrmsubcompany as a
        where IFNULL(a.canceled,0) &lt;&gt; 1
    </insert>
    <insert id="insertSubComToMap" databaseId="postgresql">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
         fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
         fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id,1,a.id,a.uuid,0,'行政维度', '',a.subcompanyname,null,'',
        '',NULL,'','','',COALESCE(a.supsubcomid,0),COALESCE(a.supsubcomid,0),
        0,0,0,TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd'),'2099-12-31' from hrmsubcompany as a
        where COALESCE(a.canceled,'0') &lt;&gt; '1'
    </insert>
    <insert id="insertSubComToMap" databaseId="oracle">
        insert into  jcl_org_map
        (id,ftype,fobjid,uuid,fclass,fclassname,fnumber,fname,fleader,fleaderimg,
        fleadername,fleaderjobid,fleaderjob,fleaderlv,fleaderst,fparentid,fobjparentid,
        fplan,fonjob,fisvitual,fdatebegin,fdateend)
        select a.id,1,a.id,a.uuid,0,'行政维度', '',a.subcompanyname,null,'',
        '',NULL,'','','',nvl(a.supsubcomid,0),nvl(a.supsubcomid,0),
        0,0,0,TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd'), TO_DATE('2099-12-31','yyyy-MM-dd') from hrmsubcompany a
        where nvl(a.canceled,0) &lt;&gt; 1
    </insert>
    <!--同步集团信息-->
    <insert id="insertComToMap" databaseId="sqlserver">
        INSERT INTO jcl_org_map ( id, ftype, fobjid, uuid,fclass, fclassname, fnumber, fname,
                                  fleader, fleaderimg, fleadername, fleaderjobid, fleaderjob, fparentid,fobjparentid,
                                  fplan, fonjob, fisvitual, fdatebegin, fdateend)
        SELECT 0, 0, 0, uuid, 0, '行政维度', '00', companyname,
               0, NULL, NULL, 0, NULL, -1, 0,
               0, 0, 0, convert(varchar(100),getDate(),23), '2099-12-31' FROM hrmcompany
    </insert>
    <insert id="insertComToMap" databaseId="oracle">
        INSERT INTO jcl_org_map ( id, ftype, fobjid, uuid,fclass, fclassname, fnumber, fname,
                                  fleader, fleaderimg, fleadername, fleaderjobid, fleaderjob, fparentid,fobjparentid,
                                  fplan, fonjob, fisvitual, fdatebegin, fdateend)
        SELECT 0, 0, 0, uuid, 0, '行政维度', '00', companyname,
               0, NULL, NULL, 0, NULL, -1, 0,
               0, 0, 0, TO_DATE(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd'), TO_DATE('2099-12-31','yyyy-MM-dd') FROM hrmcompany
    </insert>
    <insert id="insertComToMap" databaseId="mysql">
        INSERT INTO jcl_org_map ( id, ftype, fobjid, uuid,fclass, fclassname, fnumber, fname,
                                  fleader, fleaderimg, fleadername, fleaderjobid, fleaderjob, fparentid,fobjparentid,
                                  fplan, fonjob, fisvitual, fdatebegin, fdateend)
        SELECT 0, 0, 0, uuid, 0, '行政维度', '00', companyname,
               0, NULL, NULL, 0, NULL, -1, 0,
               0, 0, 0, NOW(), '2099-12-31' FROM hrmcompany
    </insert>
    <insert id="insertComToMap" databaseId="postgresql">
        INSERT INTO jcl_org_map ( id, ftype, fobjid, uuid,fclass, fclassname, fnumber, fname,
                                  fleader, fleaderimg, fleadername, fleaderjobid, fleaderjob, fparentid,fobjparentid,
                                  fplan, fonjob, fisvitual, fdatebegin, fdateend)
        SELECT 0, 0, 0, uuid, 0, '行政维度', '00', companyname,
               0, NULL, NULL, 0, NULL, -1, 0,
               0, 0, 0, TO_DATE(to_char(NOW(),'yyyy-MM-dd'),'yyyy-MM-dd'), '2099-12-31' FROM hrmcompany
    </insert>
    <delete id="deleteJobNull">
        delete from jcl_org_map where ftype =3 and fdateend &gt; #{currentDate} and id not in (select a.FPARENTID from (select FPARENTID from jcl_org_map where ftype=4 and fdateend &gt; #{currentDate}) a)
    </delete>
    <select id="getResInfo" resultType="com.engine.organization.entity.map.JclOrgMap">
        select a.id as fLeader,a.messagerurl as fLeaderImg,a.LASTNAME as fLeaderName,a.jobtitle as fLeaderJobId,b.JOBTITLEMARK as fLeaderJob,e.level_name as fLeaderLv,f.grade_name as fLeaderSt
        from hrmresource a
                 left join hrmjobtitles b on a.JOBTITLE=b.ID
                 left join cus_fielddata d on a.id = d.id and d.scope='HrmCustomFieldByInfoType' and d.scopeid=3
                 left join jcl_org_level e on d.${level}=e.id
                 left join jcl_org_grade f on d.${grade}=f.id
        where a.id=#{id}
    </select>


    <!--分部版本历史记录-->
    <insert id="insertSubVersionRecord">
        insert into jcl_org_chart_subcompany(creater, subcompanyid, subcompanyname, subcompanydesc, company,
                                             supsubcompanyid, supsubcompany, canceled, subcompanycode, limitusers,
                                             tlevel, versiondate, type)
        select #{creator},
               a.id             as subcompanyid,
               a.subcompanyname,
               a.subcompanydesc,
               a.companyid      as company,
               b.supsubcomid    as supsubcompanyid,
               b.subcompanyname as supsubcompany,
               a.canceled,
               a.subcompanycode,
               a.limitusers,
               a.tlevel,
               #{currentDate},
               c.fblx           as type
        from hrmsubcompany a
                 left join hrmsubcompany b on a.supsubcomid = b.id
                 left join hrmsubcompanydefined c on a.id = c.subcomid                                                                                                                                                                                                                                                left join hrmsubcompany b on a.supsubcomid=b.id
    </insert>

    <!--虚拟分部历史记录-->
    <insert id="insertSubVirtualVersionRecord">
        insert into jcl_chart_subcompanyvirtual(subcompanyvirtualid, creater, subcompanyname, subcompanycode,
                                                subcompanydesc, supsubcompany, supsubcomid, company, companyid,
                                                canceled, virtualtype, tlevel, showorder, versiondate)
        select a.id             as subcompanyvirtualid,
               #{creator},
               a.subcompanyname,
               a.subcompanycode,
               a.subcompanydesc,
               b.subcompanyname as supsubcompany,
               a.supsubcomid,
               c.companyname    as company,
               a.companyid,
               a.canceled,
               a.virtualtypeid  as virtualtype,
               a.tlevel,
               a.showorder,
               #{currentDate}
        from hrmsubcompanyvirtual a
                 left join hrmsubcompanyvirtual b on a.supsubcomid = b.id
                 left join hrmcompanyvirtual c on a.companyid = c.id
    </insert>

    <!--部门版本历史记录-->
    <insert id="insertDeptVersionRecord">
        insert into jcl_chart_department(creater, departmentid, departmentmark, departmentname, subcompanyid,
                                         subcompany, supdepartmentid, supdepartment, canceled, departmentcode,
                                         coadjutant, tlevel, versiondate, type, fleader)
        select #{creator},
               a.id             as departmentid,
               a.departmentmark,
               a.departmentname,
               a.subcompanyid1  as subcompanyid,
               b.SUBCOMPANYNAME as subcompany,
               a.supdepid       as supdepartmentid,
               c.departmentname as supdepartment,
               a.canceled,
               a.departmentcode,
               a.coadjutant,
               a.tlevel,
               #{currentDate},
               d.bmlx           as type,
               a.bmfzr          as fleader
        from hrmdepartment a
                 left join hrmsubcompany b on a.subcompanyid1 = b.id
                 left join hrmdepartment c on a.supdepid = c.id
                 left join hrmdepartmentdefined d on a.id=d.deptid;left join hrmdepartmentdefined d on a.id=d.deptid
    </insert>

    <!--虚拟部门版本历史记录-->
    <insert id="insertDeptVirtualVersionRecord">
        insert into jcl_chart_departmentvirtual(departmentvirtualid, creater, departmentname, departmentcode,
                                                departmentmark, supdeptment, supdepid, allsupdepid, subcompany,
                                                subcompanyid, canceled, virtualtype, tlevel, showorder, versiondate)
        select a.id             as departmentvirtualid,
               #{creator},
               a.departmentname,
               a.departmentcode,
               a.departmentmark,
               b.DEPARTMENTNAME as supdeptment,
               b.SUPDEPID       as supdepid,
               a.allsupdepid,
               c.SUBCOMPANYNAME as subcompany,
               a.SUBCOMPANYID1  as subcompanyid,
               a.canceled,
               a.virtualtype,
               a.tlevel,
               a.showorder,
               #{currentDate}
        from hrmdepartmentvirtual a
                 left join hrmdepartmentvirtual b on a.SUPDEPID = b.id
                 left join hrmsubcompanyvirtual c on a.SUBCOMPANYID1 = c.id
    </insert>

    <!--虚拟总部历史记录-->
    <insert id="insertCompanyVirtualVersionRecord">
        INSERT into jcl_chart_companyvirtual(companyvirtualid, creater, companyname, companycode, companydesc,
                                             showorder, canceled, virtualtype, virtualtypedesc, versiondate)
        select id as companyvirtualid,
               #{creator},
               companyname,
               companycode,
               companydesc,
               showorder,
               canceled,
               virtualtype,
               virtualtypedesc,
               #{currentDate}
        from hrmcompanyvirtual
    </insert>

    <!--岗位历史记录-->
    <insert id="insertJobVersionRecord">
        insert into jcl_org_chart_job(creater, jobname, department, subcompany, jobresponsibility, canceled, jobcode,
                                      description, versiondate)
        select #{creator},
               a.job_name      as jobname,
               a.ec_department as department,
               a.ec_company    as subcompany,
               b.jobresponsibility,
               a.forbidden_tag as canceled,
               a.job_no        as jobcode,
               a.description,
               #{currentDate}
        from jcl_org_job a
                 left join hrmjobtitles b on a.ec_jobtitle = b.id
    </insert>

    <insert id="insertResourceVersionRecord">
        insert into jcl_chart_resource(resourceid, creater, workyear, usekind, managerstr, status, sex, accounttype,
                                       belongto, loginid, maritalstatus, telephone, mobile, mobilecall, email,
                                       locationname, resourcetype, startdate, enddate, jobtitleid, jobtitle, joblevel,
                                       seclevel, departmentid, department, subcompanyid, subcompany, costcenter,
                                       manager, assistant, workcode, classification, versiondate, policy, degree,
                                       versionid)
        select a.id             as resourceid,
               1,
               workyear,
               usekind,
               managerstr,
               status,
               sex,
               accounttype,
               belongto,
               loginid,
               maritalstatus,
               a.telephone,
               mobile,
               mobilecall,
               email,
               b.locationname,
               resourcetype,
               startdate,
               enddate,
               c.id             as jobtitleid,
               d.JOBTITLENAME   as jobtitle,
               joblevel,
               seclevel,
               departmentid,
               e.departmentname as department,
               a.subcompanyid1  as subcompanyid,
               f.subcompanyname as subcompany,
               a.costcenterid   as costcenter,
               a.managerid      as manager,
               a.assistantid    as assistant,
               workcode,
               classification,
               '2023-07-11 15:47',
               policy,
               degree,
               1111
        from hrmresource a
                 left join hrmlocations b on a.locationid = b.id
                 left join jcl_org_job c on a.jobtitle = c.id
                 left join hrmjobtitles d on c.ec_jobtitle = d.id
                 left join hrmdepartment e on a.departmentid = e.id
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         left join hrmsubcompany f on a.subcompanyid1= f.id;
    </insert>

</mapper>