<?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.job.JobMapper">
    <resultMap id="BaseResultMap" type="com.engine.organization.entity.job.po.JobPO">
        <result column="id" property="id"/>
        <result column="job_no" property="jobNo"/>
        <result column="jobtitlename" property="jobTitleName"/>
        <result column="ec_jobTitle" property="ecJobTitle"/>
        <result column="ec_company" property="ecCompany"/>
        <result column="ec_department" property="ecDepartment"/>
        <result column="ec_jobTitle" property="ecJobTitle"/>
        <result column="sequence_id" property="sequenceId"/>
        <result column="scheme_id" property="schemeId"/>
        <result column="parent_job" property="parentJob"/>
        <result column="is_key" property="isKey"/>
        <result column="workplace" property="workplace"/>
        <result column="work_duty" property="workDuty"/>
        <result column="work_authority" property="workAuthority"/>
        <result column="description" property="description"/>
        <result column="show_order" property="showOrder"/>
        <result column="forbidden_tag" property="forbiddenTag"/>
        <result column="grade_id" property="gradeId"/>
        <result column="level_id" property="levelId"/>
        <result column="creator" property="creator"/>
        <result column="delete_type" property="deleteType"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
        t
        .
        id
        ,
        t.job_no,
        h.jobtitlename,
        t.ec_jobTitle,
        t.ec_company,
        t.ec_department,
        t.sequence_id,
        t.scheme_id,
        t.parent_job,
        t.is_key,
        t.workplace,
        t.description,
        t.work_duty,
        t.work_authority,
        t.show_order,
        t.forbidden_tag,
        t.grade_id,
        t.level_id
    </sql>
    <insert id="insertIgnoreNull" parameterType="com.engine.organization.entity.job.po.JobPO"
            keyProperty="id"
            keyColumn="id" useGeneratedKeys="true">
        INSERT INTO jcl_org_job
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="deleteType != null">
                delete_type,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>

            <if test="jobNo != null ">
                job_no,
            </if>
            <if test="sequenceId != null ">
                sequence_id,
            </if>
            <if test="schemeId != null ">
                scheme_id,
            </if>
            <if test="parentJob != null ">
                parent_job,
            </if>
            <if test="isKey != null ">
                is_key,
            </if>
            <if test="workplace != null ">
                workplace,
            </if>
            <if test="description != null ">
                description,
            </if>
            <if test="workDuty != null ">
                work_duty,
            </if>
            <if test="workAuthority != null ">
                work_authority,
            </if>
            <if test="showOrder != null ">
                show_order,
            </if>
            <if test="ecCompany != null ">
                ec_company,
            </if>
            <if test="ecDepartment != null ">
                ec_department,
            </if>
            <if test="ecJobTitle != null ">
                ec_jobTitle,
            </if>
            forbidden_tag,
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="deleteType != null">
                #{deleteType},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="jobNo != null ">
                #{jobNo},
            </if>
            <if test="sequenceId != null ">
                #{sequenceId},
            </if>
            <if test="schemeId != null ">
                #{schemeId},
            </if>
            <if test="parentJob != null ">
                #{parentJob},
            </if>
            <if test="isKey != null ">
                #{isKey},
            </if>
            <if test="workplace != null ">
                #{workplace},
            </if>
            <if test="description != null ">
                #{description},
            </if>
            <if test="workDuty != null ">
                #{workDuty},
            </if>
            <if test="workAuthority != null ">
                #{workAuthority},
            </if>
            <if test="showOrder != null ">
                #{showOrder},
            </if>
            <if test="ecCompany != null ">
                #{ecCompany},
            </if>
            <if test="ecDepartment != null ">
                #{ecDepartment},
            </if>
            <if test="ecJobTitle != null ">
                #{ecJobTitle},
            </if>
            0,
        </trim>
    </insert>

    <insert id="insertIgnoreNull" parameterType="com.engine.organization.entity.job.po.JobPO" databaseId="oracle">
        <selectKey keyProperty="id" resultType="long" order="AFTER">
            select JCL_ORG_JOB_ID.currval from dual
        </selectKey>
        INSERT INTO jcl_org_job
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                creator,
            </if>
            <if test="deleteType != null">
                delete_type,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>

            <if test="jobNo != null ">
                job_no,
            </if>
            <if test="sequenceId != null ">
                sequence_id,
            </if>
            <if test="schemeId != null ">
                scheme_id,
            </if>
            <if test="parentJob != null ">
                parent_job,
            </if>
            <if test="isKey != null ">
                is_key,
            </if>
            <if test="workplace != null ">
                workplace,
            </if>
            <if test="description != null ">
                description,
            </if>
            <if test="workDuty != null ">
                work_duty,
            </if>
            <if test="workAuthority != null ">
                work_authority,
            </if>
            <if test="showOrder != null ">
                show_order,
            </if>
            <if test="ecCompany != null ">
                ec_company,
            </if>
            <if test="ecDepartment != null ">
                ec_department,
            </if>
            <if test="ecJobTitle != null ">
                ec_jobTitle,
            </if>
            forbidden_tag,
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="creator != null">
                #{creator},
            </if>
            <if test="deleteType != null">
                #{deleteType},
            </if>
            <if test="createTime != null">
                #{createTime},
            </if>
            <if test="updateTime != null">
                #{updateTime},
            </if>
            <if test="jobNo != null ">
                #{jobNo},
            </if>
            <if test="sequenceId != null ">
                #{sequenceId},
            </if>
            <if test="schemeId != null ">
                #{schemeId},
            </if>
            <if test="parentJob != null ">
                #{parentJob},
            </if>
            <if test="isKey != null ">
                #{isKey},
            </if>
            <if test="workplace != null ">
                #{workplace},
            </if>
            <if test="description != null ">
                #{description},
            </if>
            <if test="workDuty != null ">
                #{workDuty},
            </if>
            <if test="workAuthority != null ">
                #{workAuthority},
            </if>
            <if test="showOrder != null ">
                #{showOrder},
            </if>
            <if test="ecCompany != null ">
                #{ecCompany},
            </if>
            <if test="ecDepartment != null ">
                #{ecDepartment},
            </if>
            <if test="ecJobTitle != null ">
                #{ecJobTitle},
            </if>
            0,
        </trim>
    </insert>

    <update id="updateBaseJob" parameterType="com.engine.organization.entity.job.po.JobPO">
        update jcl_org_job
        <set>
            creator=#{creator},
            update_time=#{updateTime},
            ec_company=#{ecCompany},
            ec_department=#{ecDepartment},
            sequence_id=#{sequenceId},
            scheme_id=#{schemeId},
            parent_job=#{parentJob},
            is_key=#{isKey},
            workplace=#{workplace},
            description=#{description},
            work_duty=#{workDuty},
            work_authority=#{workAuthority},
            show_order=#{showOrder},
            <if test="forbiddenTag != null">
                forbidden_tag=#{forbiddenTag},
            </if>
        </set>
        WHERE id = #{id} AND delete_type = 0
    </update>
    <update id="updateForbiddenTagById">
        update jcl_org_job
        <set>
            forbidden_tag=#{forbiddenTag},
        </set>
        WHERE id = #{id} AND delete_type = 0
    </update>

    <update id="deleteByIds">
        UPDATE jcl_org_job
        SET delete_type = 1
        WHERE delete_type = 0
        AND id IN
        <foreach collection="ids" open="(" item="id" separator="," close=")">
            #{id}
        </foreach>
    </update>


    <select id="listByFilter" resultType="com.engine.organization.entity.job.dto.JobListDTO"
            parameterType="com.engine.organization.entity.job.po.JobPO">
        SELECT
        a.subcompanyname ,
        b.departmentname ,
        c.sequence_name,
        d.scheme_name,
        e.jobactivityname,
        f.jobgroupname,
        <include refid="baseColumns"/>
        FROM jcl_org_job t
        left join hrmjobtitles h on
        t.ec_jobTitle = h.id
        left join hrmsubcompany a on
        t.ec_company = a.id
        left join hrmdepartment b on
        t.ec_department = b.id
        left join jcl_org_sequence c on
        t.sequence_id = c.id
        left join jcl_org_scheme d on
        t.scheme_id = d.id
        left join hrmjobactivities e on h.jobactivityid =e.id
        left join hrmjobgroups f on e.jobgroupid =f.id
        WHERE t.delete_type = 0
        <include refid="likeSQL"/>
        <if test=" jobPO.parentJob != null ">
            and t.parent_job = #{jobPO.parentJob}
        </if>
        <if test=" jobPO.ecCompany != null ">
            and t.ec_company = #{jobPO.ecCompany}
        </if>
        <if test=" jobPO.ecDepartment != null ">
            and t.ec_department = #{jobPO.ecDepartment}
        </if>
        <if test=" jobPO.sequenceId != null ">
            and t.sequence_id = #{jobPO.sequenceId}
        </if>
        <if test=" jobPO.schemeId != null ">
            and t.scheme_id = #{jobPO.schemeId}
        </if>
        <if test=" jobPO.isKey != null ">
            and t.is_key = #{jobPO.isKey}
        </if>
        <if test=" jobPO.forbiddenTag != null ">
            and t.forbidden_tag = #{jobPO.forbiddenTag}
        </if>
        order by ${orderSql}
    </select>

    <select id="listNoFilter" resultType="com.engine.organization.entity.job.dto.JobListDTO">
        SELECT
        a.subcompanyname ,
        b.departmentname ,
        c.sequence_name,
        d.scheme_name,
        e.jobactivityname,
        f.jobgroupname,
        <include refid="baseColumns"/>
        FROM jcl_org_job t
        left join hrmsubcompany a on
        t.ec_company = a.id
        left join hrmdepartment b on
        t.ec_department = b.id
        left join jcl_org_sequence c on
        t.sequence_id = c.id
        left join jcl_org_scheme d on
        t.scheme_id = d.id
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        left join hrmjobactivities e on h.jobactivityid =e.id
        left join hrmjobgroups f on e.jobgroupid =f.id
        WHERE t.delete_type = 0 order by ${orderSql}
    </select>

    <select id="getJobById" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where t.delete_type = 0
        and t.id = #{id}
    </select>
    <select id="listJobsByIds" resultType="java.util.Map">
        select
        t.id as "id",
        h.jobtitlename as "name"
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        WHERE t.delete_type = 0
        AND t.id IN
        <foreach collection="ids" open="(" item="id" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="listByNo" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where job_no = #{jobNo} AND delete_type = 0
    </select>
    <select id="getJobsByPid" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where delete_type = 0
        and parent_job = #{pid}
    </select>

    <select id="listJobsByDepartmentId" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where delete_type = 0
        and ec_department = #{departmentId}
    </select>
    <select id="listAll" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where delete_type = 0
    </select>
    <select id="listPOsByFilter" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        FROM jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        WHERE t.delete_type = 0
        <include refid="likeSQL"/>
        <if test=" jobPO.sequenceId != null ">
            and t.sequence_id = #{jobPO.sequenceId}
        </if>
        <if test=" jobPO.schemeId != null ">
            and t.scheme_id = #{jobPO.schemeId}
        </if>
        <if test=" jobPO.isKey != null ">
            and t.is_key = #{jobPO.isKey}
        </if>
        <if test=" jobPO.forbiddenTag != null ">
            and t.forbidden_tag = #{jobPO.forbiddenTag}
        </if>
    </select>
    <select id="getMaxShowOrder" resultType="java.lang.Integer">
        select max(show_order)
        from jcl_org_job
    </select>


    <select id="selectByConditions" resultType="java.lang.Integer">
        select count(1)
        from jcl_org_job
        where ec_company = #{resourcePO.subcompanyid1}
          and ec_department = #{resourcePO.departmentid}
          and ec_jobtitle = #{resourcePO.jobtitle}
          and delete_type = 0
    </select>

    <select id="getIdByNameAndPid" resultType="java.lang.Long">
        select
        t.id
        from
        jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where
        delete_type = 0
        and h.JOBTITLENAME = #{jobName}
        and ec_company = #{parentCompany}
        <include refid="nullparentDept"/>
    </select>
    <select id="getJobsByIds" resultMap="BaseResultMap">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where delete_type = 0
        AND t.id IN
        <foreach collection="ids" open="(" item="id" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="listUsedId" resultType="java.lang.String">
        select t.id
        from jcl_org_job t
                 inner join hrmresource h on t.ec_jobtitle = h.jobtitle and t.ec_company = h.subcompanyid1 and
                                             t.ec_department = h.departmentid
        where h.status &lt; 4
    </select>
    <select id="isAllowDelete" resultType="java.lang.String">
        select t.id
        from jcl_org_job t
                 inner join hrmresource h on t.ec_jobtitle = h.jobtitle and t.ec_company = h.subcompanyid1 and
                                             t.ec_department = h.departmentid and t.id = #{jobId} and h.status &lt; 4
    </select>
    <select id="getJobByResource" resultType="com.engine.organization.entity.job.po.JobPO">
        select
        <include refid="baseColumns"/>
        from jcl_org_job t
        left join hrmjobtitles h on t.ec_jobTitle = h.id
        where t.delete_type = 0
        <if test="resourcePO.subcompanyid1 != null">
            and t.ec_company = #{resourcePO.subcompanyid1}
        </if>
        <if test="resourcePO.subcompanyid1 == null">
            and t.ec_company is null
        </if>
        <if test="resourcePO.departmentid != null">
            and t.ec_department = #{resourcePO.departmentid}
        </if>
        <if test="resourcePO.departmentid == null">
            and t.ec_department is null
        </if>
        <if test="resourcePO.jobtitle != null">
            and t.ec_jobTitle = #{resourcePO.jobtitle}
        </if>
    </select>


    <update id="updateJobCompany">
        update jcl_org_job
        <set>
            parent_comp = #{parentCompany},
            ec_company =#{ecCompany},
        </set>
        where delete_type = 0
        AND id IN
        <foreach collection="ids" open="(" item="id" separator="," close=")">
            #{id}
        </foreach>
    </update>


    <sql id="nullparentDept">
        and ifnull(ec_department,0) =
        #{parentDepartment}
    </sql>

    <sql id="nullparentDept" databaseId="sqlserver">
        and isnull(ec_department,0) =
        #{parentDepartment}
    </sql>

    <sql id="nullparentDept" databaseId="oracle">
        and NVL(ec_department,0) =
        #{parentDepartment}
    </sql>
    <sql id="nullparentDept" databaseId="postgresql">
        and COALESCE(ec_department,0) =
        #{parentDepartment}
    </sql>


    <sql id="likeSQL">
        <if test=" jobPO.jobNo != null and jobPO.jobNo != '' ">
            and t.job_no like CONCAT('%',#{jobPO.jobNo},'%')
        </if>
        <if test=" jobPO.jobTitleName != null and jobPO.jobTitleName != '' ">
            and h.jobtitlename like CONCAT('%',#{jobPO.jobTitleName},'%')
        </if>
        <if test=" jobPO.workplace != null and jobPO.workplace != '' ">
            and t.workplace like CONCAT('%',#{jobPO.workplace},'%')
        </if>
        <if test=" jobPO.description != null and jobPO.description != '' ">
            and t.description like CONCAT('%',#{jobPO.description},'%')
        </if>
        <if test=" jobPO.workDuty != null and jobPO.workDuty != '' ">
            and t.work_duty like CONCAT('%',#{jobPO.workDuty},'%')
        </if>
        <if test=" jobPO.workAuthority != null and jobPO.workAuthority != '' ">
            and t.work_authority like CONCAT('%',#{jobPO.workAuthority},'%')
        </if>
    </sql>

    <sql id="likeSQL" databaseId="oracle">
        <if test=" jobPO.jobNo != null and jobPO.jobNo != '' ">
            and t.job_no like '%'||#{jobPO.jobNo}||'%'
        </if>
        <if test=" jobPO.jobTitleName != null and jobPO.jobTitleName != '' ">
            and h.jobtitlename like '%'||#{jobPO.jobTitleName}||'%'
        </if>
        <if test=" jobPO.workplace != null and jobPO.workplace != '' ">
            and t.workplace like '%'||#{jobPO.workplace}||'%'
        </if>
        <if test=" jobPO.description != null and jobPO.description != '' ">
            and t.description like '%'||#{jobPO.description}||'%'
        </if>
        <if test=" jobPO.workDuty != null and jobPO.workDuty != '' ">
            and t.work_duty like '%'||#{jobPO.workDuty}||'%'
        </if>
        <if test=" jobPO.workAuthority != null and jobPO.workAuthority != '' ">
            and t.work_authority like '%'||#{jobPO.workAuthority}||'%'
        </if>
    </sql>
    <sql id="likeSQL" databaseId="postgresql">
        <if test=" jobPO.jobNo != null and jobPO.jobNo != '' ">
            and t.job_no like '%'||#{jobPO.jobNo}||'%'
        </if>
        <if test=" jobPO.jobTitleName != null and jobPO.jobTitleName != '' ">
            and h.jobtitlename like '%'||#{jobPO.jobTitleName}||'%'
        </if>
        <if test=" jobPO.workplace != null and jobPO.workplace != '' ">
            and t.workplace like '%'||#{jobPO.workplace}||'%'
        </if>
        <if test=" jobPO.description != null and jobPO.description != '' ">
            and t.description like '%'||#{jobPO.description}||'%'
        </if>
        <if test=" jobPO.workDuty != null and jobPO.workDuty != '' ">
            and t.work_duty like '%'||#{jobPO.workDuty}||'%'
        </if>
        <if test=" jobPO.workAuthority != null and jobPO.workAuthority != '' ">
            and t.work_authority like '%'||#{jobPO.workAuthority}||'%'
        </if>
    </sql>

    <sql id="likeSQL" databaseId="sqlserver">
        <if test=" jobPO.jobNo != null and jobPO.jobNo != '' ">
            and t.job_no like '%'+#{jobPO.jobNo}+'%'
        </if>
        <if test=" jobPO.jobTitleName != null and jobPO.jobTitleName != '' ">
            and h.jobTitleName like '%'+#{jobPO.jobTitleName}+'%'
        </if>
        <if test=" jobPO.workplace != null and jobPO.workplace != '' ">
            and t.workplace like '%'+#{jobPO.workplace}+'%'
        </if>
        <if test=" jobPO.description != null and jobPO.description != '' ">
            and t.description like '%'+#{jobPO.description}+'%'
        </if>
        <if test=" jobPO.workDuty != null and jobPO.workDuty != '' ">
            and t.work_duty like '%'+#{jobPO.workDuty}+'%'
        </if>
        <if test=" jobPO.workAuthority != null and jobPO.workAuthority != '' ">
            and t.work_authority like '%'+#{jobPO.workAuthority}+'%'
        </if>
    </sql>
</mapper>