<?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.resource.HrmResourceMapper">
    <resultMap id="HrmResourceMap" type="com.engine.organization.entity.hrmresource.po.ResourcePO">
        <result column="id" property="id"/>
        <result column="subcompanyid1" property="subcompanyid1"/>
        <result column="departmentid" property="departmentid"/>
        <result column="jobtitle" property="jobtitle"/>
        <result column="lastname" property="lastName"/>
        <result column="dspOrder" property="dspOrder"/>
    </resultMap>
    <resultMap id="SearchTemplateMap" type="com.engine.organization.entity.hrmresource.param.SearchTemplateParam">
        <result column="id" property="key"/>
        <result column="name" property="showname"/>
        <!--        <result column="selected" property="selected"/>-->
    </resultMap>
    <update id="updateCustomTemplateUsed">
        update JCL_ORG_SEARCH_TEMPLATE set isused = null where creator = #{userId}
    </update>


    <select id="selectFilterDatas" resultType="com.engine.organization.entity.hrmresource.po.ResourcePO">
        select distinct a.subcompanyid1, a.departmentid, a.jobtitle
        from hrmresource a
        where a.status &lt; 4 and a.seclevel &gt;= 0
          and not exists
            (select 1
             from jcl_org_job b
             where a.subcompanyid1 = b.ec_company
               and a.departmentid = b.ec_department
               and a.jobtitle = b.ec_jobtitle
               and b.delete_type = 0)
    </select>
    <select id="listByFilter" resultMap="HrmResourceMap">
        select id, subcompanyid1 , departmentid , jobtitle , lastname , dspOrder from hrmresource t
        where 1=1
        <include refid="likeSql"/>
        <if test="resourcePO.subcompanyid1 != null">
            and subcompanyid1 = #{resourcePO.subcompanyid1}
        </if>
        <if test="resourcePO.departmentid != null">
            and departmentid = #{resourcePO.departmentid}
        </if>
        <if test="resourcePO.jobtitle != null">
            and jobtitle = #{resourcePO.jobtitle}
        </if>
    </select>
    <select id="getPersonnelResumeById"
            resultType="com.engine.organization.entity.resume.po.PersonnelResumePO">
        select lastname,
               sex,
               birthday,
               resourceimageid  as image,
               nativeplace,
               policy           as politics,
               a.departmentname as department,
               maritalstatus    as marriage,
               b.jobtitlename   as jobTitle,
               companystartdate,
               workstartdate,
               certificatenum   as idCard,
               residentplace    as address,
               mobile           as telephone,
               email
        from hrmresource h
                 inner join hrmdepartment a on a.id = h.departmentid
                 inner join hrmjobtitles b on b.id = h.jobtitle
        where h.id = #{id}
    </select>
    <select id="getPersonnelResumeList"
            resultType="com.engine.organization.entity.resume.po.PersonnelResumePO">
        select h.id,
               lastname,
               sex,
               birthday,
               resourceimageid  as image,
               resourceimageid  as imageId,
               nativeplace,
               policy           as politics,
               a.departmentname as department,
               maritalstatus    as marriage,
               b.jobtitlename   as jobTitle,
               companystartdate,
               workstartdate,
               certificatenum   as idCard,
               residentplace    as address,
               mobile           as telephone,
               email
        from hrmresource h
                 inner join hrmdepartment a on a.id = h.departmentid
                 inner join hrmjobtitles b on b.id = h.jobtitle
        where h.status &lt; 4
    </select>
    <select id="getHrmFamilyInfoByUser" resultType="com.engine.organization.entity.resume.po.HrmFamilyInfoPO">
        select *
        from HrmFamilyInfo
        where resourceid = #{resourceId}
    </select>
    <select id="getPersonnelScreening" resultMap="HrmResourceMap">
        select t.id, t.subcompanyid1 , t.departmentid , t.jobtitle , t.lastname , t.dspOrder
        from hrmresource t inner join JCL_ORG_JOB a ON a.EC_COMPANY =t.SUBCOMPANYID1 AND a.EC_DEPARTMENT =t.DEPARTMENTID
        AND a.EC_JOBTITLE =t.JOBTITLE
        where 1 = 1
        <if test="subCompanyIds != null and subCompanyIds.size > 0">
            AND t.subcompanyid1 IN
            <foreach collection="subCompanyIds" open="(" item="subcompanyid1" separator="," close=")">
                #{subcompanyid1}
            </foreach>
        </if>
        <if test="departmentIds != null and departmentIds.size > 0">
            AND t.departmentid IN
            <foreach collection="departmentIds" open="(" item="departmentid" separator="," close=")">
                #{departmentid}
            </foreach>
        </if>
        <if test="jobIds != null and jobIds.size > 0">
            AND a.id IN
            <foreach collection="jobIds" open="(" item="jobId" separator="," close=")">
                #{jobId}
            </foreach>
        </if>
        <if test="resourceIds != null and resourceIds.size > 0">
            AND t.id IN
            <foreach collection="resourceIds" open="(" item="resourceId" separator="," close=")">
                #{resourceId}
            </foreach>
        </if>
    </select>
    <select id="getSearchTemplatesByUser" resultMap="SearchTemplateMap">
        select id,
               name
        from jcl_org_search_template
        where creator = #{userId}
    </select>
    <select id="getSearchTemplateById"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_search_template
        where id = #{id}
    </select>

    <select id="getCustomTemplateById"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_custom_template
        where id = #{id}
    </select>
    <select id="getCustomTemplatesByUser"
            resultMap="SearchTemplateMap">
        select id,
               name
        from jcl_org_custom_template
        where creator = #{userId} and overall is null
    </select>

    <select id="getCustomOverAll" resultMap="SearchTemplateMap">
        select id,
               name
        from jcl_org_custom_template
        where overall = 1
    </select>

    <insert id="insertSearchTemplate" parameterType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO"
            keyProperty="id"
            keyColumn="id" useGeneratedKeys="true">
        INSERT INTO JCL_ORG_SEARCH_TEMPLATE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="basicFields != null">
                basic_fields,
            </if>
            <if test="personalFields != null">
                personal_fields,
            </if>
            <if test="workFields != null">
                work_fields,
            </if>
            <if test="isused != null">
                isused,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="basicFields != null">
                #{basicFields},
            </if>
            <if test="personalFields != null">
                #{personalFields},
            </if>
            <if test="workFields != null">
                #{workFields},
            </if>
            <if test="isused != null">
                #{isused},
            </if>
        </trim>
    </insert>

    <insert id="insertSearchTemplate" parameterType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO"
            databaseId="oracle">
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select JCL_ORG_SEARCH_TEMPLATE_ID.currval from dual
        </selectKey>
        INSERT INTO JCL_ORG_SEARCH_TEMPLATE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="basicFields != null">
                basic_fields,
            </if>
            <if test="personalFields != null">
                personal_fields,
            </if>
            <if test="workFields != null">
                work_fields,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="basicFields != null">
                #{basicFields},
            </if>
            <if test="personalFields != null">
                #{personalFields},
            </if>
            <if test="workFields != null">
                #{workFields},
            </if>
        </trim>
    </insert>
    <insert id="insertCustomTemplate" parameterType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO"
            keyProperty="id"
            keyColumn="id" useGeneratedKeys="true">
        INSERT INTO JCL_ORG_CUSTOM_TEMPLATE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="basicFields != null">
                basic_fields,
            </if>
            <if test="personalFields != null">
                personal_fields,
            </if>
            <if test="workFields != null">
                work_fields,
            </if>
            <if test="isused != null">
                isused,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="basicFields != null">
                #{basicFields},
            </if>
            <if test="personalFields != null">
                #{personalFields},
            </if>
            <if test="workFields != null">
                #{workFields},
            </if>
            <if test="isused != null">
                #{isused},
            </if>
        </trim>
    </insert>

    <insert id="insertCustomTemplate" parameterType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO"
            databaseId="oracle">
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select JCL_ORG_CUSTOM_TEMPLATE_ID.currval from dual
        </selectKey>
        INSERT INTO JCL_ORG_CUSTOM_TEMPLATE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="basicFields != null">
                basic_fields,
            </if>
            <if test="personalFields != null">
                personal_fields,
            </if>
            <if test="workFields != null">
                work_fields,
            </if>
            <if test="isused != null">
                isused,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="name != null">
                #{name},
            </if>
            <if test="basicFields != null">
                #{basicFields},
            </if>
            <if test="personalFields != null">
                #{personalFields},
            </if>
            <if test="workFields != null">
                #{workFields},
            </if>
            <if test="isused != null">
                #{isused},
            </if>
        </trim>
    </insert>

    <delete id="deleteSearchTemplate">
        delete
        from jcl_org_search_template
        where id = #{id}
          and creator = #{userId}
    </delete>

    <delete id="deleteCustomTemplate">
        delete
        from jcl_org_custom_template
        where id = #{id}
          and creator = #{userId}
    </delete>

    <sql id="likeSql">
        <if test="resourcePO.lastName != null and resourcePO.lastName != ''">
            AND t.lastname like CONCAT('%',#{resourcePO.lastName},'%')
        </if>
    </sql>
    <sql id="likeSql" databaseId="oracle">
        <if test="resourcePO.lastName != null and resourcePO.lastName != ''">
            AND t.lastname like '%'||#{resourcePO.lastName}||'%'
        </if>
    </sql>
    <sql id="likeSql" databaseId="postgresql">
        <if test="resourcePO.lastName != null and resourcePO.lastName != ''">
            AND t.lastname like '%'||#{resourcePO.lastName}||'%'
        </if>
    </sql>
    <sql id="likeSql" databaseId="sqlserver">
        <if test="resourcePO.lastName != null and resourcePO.lastName != ''">
            AND t.lastname like '%'+#{resourcePO.lastName}+'%'
        </if>
    </sql>


    <select id="getSearchTemplateByName"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_search_template
        where creator = #{userId}
          and name = #{name}
    </select>
    <select id="getCustomTemplateByName"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_custom_template
        where creator = #{userId}
          and name = #{name}
    </select>

    <select id="queryLabelName" parameterType="java.lang.String" resultType="java.lang.String">
        select b.LABELNAME
        from hrm_formfield a
                 inner join htmllabelinfo b on a.fieldlabel = b.indexid
        where a.FIELDNAME = #{fieldName}
          and b.LANGUAGEID = ${language}
        union all
        SELECT t3.LABELNAME as labelname
        FROM cus_formfield t1,
             cus_formdict t2,
             htmllabelinfo t3
        WHERE t1.fieldid = t2.id and t1.FIELDLABLE = t3.indexid and t3.LANGUAGEID = ${language}
          AND t1.scope = 'HrmCustomFieldByInfoType'
          and t1.SCOPEID = #{scopeId}
          and t2.fieldname = #{fieldName}
    </select>
    <select id="getUsedCustomTemplateByUser"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_custom_template
        where ISUSED = 1
          and overall is null
          and creator = #{userId}
    </select>

    <select id="getOverallCustomTemplate"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_custom_template
        where overall = 1
    </select>



    <select id="selectByDepartmentId" resultType="com.engine.organization.entity.hrmresource.po.ResourceChartPO">
        select id,workcode,lastname,sex,departmentid,subcompanyid1,jobtitle,status,mobile
        from hrmresource where departmentid = #{departmentId} and status &lt; 4 order by id
    </select>

    <select id="selectByIdAndStatus" resultType="com.engine.organization.entity.hrmresource.po.ResourceChartPO">
        select id,workcode,lastname,sex,departmentid,subcompanyid1,jobtitle,status,mobile
        from hrmresource where departmentid = #{departmentId}
        AND status IN
        <foreach collection="statusList" open="(" item="status" separator="," close=")">
            #{status}
        </foreach>
        order by id
    </select>
    <select id="getUsedSerarchTemplateByUser"
            resultType="com.engine.organization.entity.hrmresource.po.SearchTemplatePO">
        select *
        from jcl_org_search_template
        where ISUSED = 1
        and creator = #{userId}
    </select>

</mapper>