<?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.bonus.system.dept.dao.EightMembersVacancyDao">

    <!--八大员缺员兼职查询-列表查询-->
    <select id="getList" resultType="com.bonus.system.dept.entity.EightMembersVacancyVo">
        SELECT sd.id,
               sd.value AS roleName
        FROM sys_dict sd
        WHERE sd.type = 'deptRole' AND sd.is_active = '1'
        <if test="id!=null and id!=0L">
            AND sd.id = #{id}
        </if>
        ORDER BY update_time
    </select>
    <!--1.查询缺员项目部数量、2.查询兼职项目部数量-->
    <select id="getNumByType" resultType="java.lang.Long">
        <if test="type == 1">
            SELECT so.id
            FROM sys_organization so
            LEFT JOIN sys_department_role sdr ON so.id = sdr.org_id AND sdr.department_role_id = #{id} AND sdr.reviewer_status = '1'
            LEFT JOIN sys_department_user sdu ON sdr.id = sdu.department_id AND sdu.is_active = '1'
            WHERE so.is_department = '1' AND so.reviewer_status = '1' AND so.is_active = '1'
            GROUP BY so.id
            HAVING(GROUP_CONCAT(ANY_VALUE(sdu.user_id))) IS NULL
        </if>
        <if test="type == 2">
            SELECT so.id
            FROM sys_organization so
            LEFT JOIN sys_department_role sdr ON so.id = sdr.org_id AND sdr.department_role_id = #{id} AND sdr.reviewer_status = '1'
            LEFT JOIN sys_department_user sdu ON sdr.id = sdu.department_id AND sdu.is_active = '1'
            LEFT JOIN (
                SELECT sdr.org_id,
                sdu.user_id,
                COUNT(*) AS num
                FROM sys_department_user sdu
                LEFT JOIN sys_department_role sdr ON sdu.department_id = sdr.id AND sdr.reviewer_status = '1' AND sdr.department_role_id != #{id}
                WHERE sdu.is_active = '1'
                GROUP BY sdr.org_id,sdu.user_id
            ) a ON so.id = a.org_id AND sdu.user_id = a.user_id
            WHERE so.is_department = '1' AND so.reviewer_status = '1' AND so.is_active = '1'
            AND sdu.user_id IS NOT NULL AND a.num IS NOT NULL
        </if>
    </select>
    <!--八大员缺员兼职查询-缺员项目部详情-->
    <select id="getStaffingList"
            resultType="com.bonus.system.dept.entity.EightMembersVacancyVo$VacancyDetail">
        SELECT a.id,
        a.orgName,
        MAX(IF( a.VALUE = '项目经理', a.userName, '' )) AS projectManager,
        MAX(IF( a.VALUE = '安全员', a.userName, '' )) AS safetyOfficer,
        MAX(IF( a.VALUE = '质检员', a.userName, '' )) AS qualityInspector,
        MAX(IF( a.VALUE = '项目总工', a.userName, '' )) AS projectChiefEngineer,
        MAX(IF( a.VALUE = '机械员', a.userName, '' )) AS machinist,
        MAX(IF( a.VALUE = '资料员', a.userName, '' )) AS document,
        MAX(IF( a.VALUE = '施工员', a.userName, '' )) AS constructionWorker,
        MAX(IF( a.VALUE = '材料员', a.userName, '' )) AS materialMan,
        MAX(IF( a.VALUE = '其他', a.userName, '' )) AS other
        FROM (
            SELECT so.id,
            so.org_name AS orgName,
            GROUP_CONCAT( DISTINCT sd.value ) AS value,
            GROUP_CONCAT( su.user_name ) AS userName
            FROM sys_organization so
            LEFT JOIN sys_department_role sdr ON so.id = sdr.org_id AND sdr.reviewer_status = '1'
            LEFT JOIN sys_dict sd ON sd.id = sdr.department_role_id AND sd.type = 'deptRole' AND sd.is_active = '1'
            LEFT JOIN sys_department_user sdu ON sdr.id = sdu.department_id AND sdu.is_active = '1'
            LEFT JOIN sys_user su ON su.user_id = sdu.user_id AND su.is_active = '1'
            <where>
                so.is_department = '1' AND so.reviewer_status = '1' AND so.is_active = '1'
                <if test="list!=null">
                    AND so.id IN
                    <foreach collection="list" open="(" close=")" separator="," item="item">
                        #{item}
                    </foreach>
                </if>
                <if test="params.orgName!=null and params.orgName!=''">
                    AND INSTR(so.org_name,#{params.orgName}) > 0
                </if>
            </where>
            GROUP BY so.id,sdr.department_role_id
        ) a
        GROUP BY a.id
    </select>

    <!--角色下拉选-->
    <select id="getRoleList" resultType="java.util.Map">
        SELECT sd.id,
        sd.value AS `value`
        FROM sys_dict sd
        WHERE sd.is_active = '1'
        ORDER BY update_time
    </select>
</mapper>