<?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.rongwei.sfcommon.sys.dao.SaveCheckCommonDao">
    <select id="getUserInfoByOrgIdAndRoleCode" resultType="com.rongwe.scentity.vo.UserMailOrgVo">
        SELECT
        DISTINCTROW
        su.ID as id,
        su.EMAIL as email,
        suo.ORGID as orgId
        FROM
        sys_user su
        LEFT JOIN sys_user_role sur on sur.USERID =su.ID and sur.DELETED='0'
        LEFT JOIN sys_role sr on sur.ROLEID = sr.ID and sr.DELETED='0'
        lEFT JOIN sys_user_org suo on suo.USERID =su.ID and suo.DELETED='0'
        <where>
            su.DELETED ='0'
            AND sr.CODE =#{roleCode}
            <choose>
                <when test="orgIds != null and orgIds.size()>0">
                    AND suo.ORGID in
                    <foreach collection="orgIds" item="orgId" separator="," open="(" close=")">
                        #{orgId}
                    </foreach>
                </when>
                <otherwise>
                    AND suo.ORGID ='00aa'
                </otherwise>
            </choose>
        </where>
    </select>
    <select id="getSafetyManager" resultType="com.rongwei.rwadmincommon.system.domain.SysUserDo">
        SELECT GROUP_CONCAT(DISTINCT su.ID)   AS ID,
               GROUP_CONCAT(DISTINCT su.NAME) AS NAME
        FROM sys_user su
                 LEFT JOIN sys_user_role sur on su.ID = sur.USERID and sur.DELETED = '0'
                 left join sys_role sr on sr.ID = sur.ROLEID and sr.DELETED = '0'
        WHERE su.DELETED = '0'
          AND sr.CODE = 'code-safe'
    </select>
    <select id="getUserInfoByDeptCode" resultType="com.rongwe.scentity.vo.UserMailOrgVo">
        SELECT DISTINCTROW su.ID    AS id,
                           su.NAME  AS name,
                           su.EMAIL as email
        FROM sys_user su
                 LEFT JOIN sys_user_role sur on su.ID = sur.USERID and sur.DELETED = '0'
                 left join sys_role sr on sr.ID = sur.ROLEID and sr.DELETED = '0'
        WHERE su.DELETED = '0'
          AND sr.CODE = #{roleCode}
    </select>
    <select id="getSafetyPromoter" resultType="com.rongwe.scentity.vo.UserMailOrgVo">
        SELECT DISTINCTROW su.ID AS id,
        su.NAME AS name,
        suo.ORGID AS orgId
        FROM sys_user su
        LEFT JOIN sys_user_role sur on su.ID = sur.USERID and sur.DELETED = '0'
        left join sys_role sr on sr.ID = sur.ROLEID and sr.DELETED = '0'
        left join sys_user_org suo on suo.USERID = su.ID AND suo.DELETED = '0'
        WHERE su.DELETED = '0'
        AND sr.CODE = 'safety-engineer'
        <if test="deptIds != null and deptIds.size()>0">
            and suo.ORGID in
            <foreach collection="deptIds" separator="," open="(" close=")" item="deptId">
                #{deptId}
            </foreach>
        </if>
    </select>
    <update id="updateNotifyState">
        update sys_notify_announce_user set READSTATE =1
        <where>
            ANNOUNCEID = (select ID from sys_notify_announce where NOTIFYTYPE =#{notifyType} and DELETED='0'
            <if test="tenantId != null and tenantId != ''">
                    and TENANTID =#{tenantId}
            </if>
            );
        UPDATE sys_notify_announce SET DELETED='1' WHERE NOTIFYTYPE =#{notifyType}
        </where>
    </update>
    <select id="getUserIdByRoleCodeAndOrgIdAndTenantId" resultType="java.lang.String">
        select DISTINCT su.ID from sys_user su
        left join sys_user_role sur on sur.USERID = su.ID and sur.DELETED='0'
        left join sys_role sr on sr.ID = sur.ROLEID and sr.DELETED='0'
        left join sys_user_org suo on sur.USERID = suo.USERID and suo.DELETED='0'
        left join sys_organization so on suo.ORGID =so.ID and so.DELETED='0'
        <where>
            su.DELETED ='0'
            AND FIND_IN_SET(#{tenantId},so.FULLPID)
            <if test="roleCodes != null and roleCodes.size()>0">
            AND sr.code in
                <foreach collection="roleCodes" item="roleCode" open="(" close=")" separator=",">
                    #{roleCode}
                </foreach>
            </if>
            <if test="orgIds != null and orgIds.size()>0">
                AND so.ID in
                <foreach collection="orgIds" item="orgId" open="(" close=")" separator=",">
                    #{orgId}
                </foreach>
            </if>
        </where>
    </select>
    <select id="getUserInfoByTenantIdAndRoleCode" resultType="com.rongwe.scentity.vo.UserMailOrgVo">
        select
        su.ID as id,
        su.NAME as name,
        sr.CODE AS roleCode,
        su.EMAIL AS email
        from sys_user su
        LEFT JOIN sys_user_role sur on sur.USERID = su.ID and sur.DELETED = '0'
        LEFT JOIN sys_role sr on sur.ROLEID = sr.ID and sr.DELETED = '0'
        lEFT JOIN sys_user_org suo on suo.USERID = su.ID and suo.DELETED = '0'
        LEFT JOIN sys_organization so on suo.ORGID=so.ID and so.DELETED='0'
        <where>
            su.DELETED='0'
            AND (FIND_IN_SET(#{tenantId},so.FULLPID) or so.ID=#{tenantId})
            AND sr.CODE in
            <foreach collection="roleCodes" item="code" open="(" close=")" separator=",">
                #{code}
            </foreach>
        </where>
    </select>
    <select id="getCheckArea" resultType="com.rongwe.scentity.vo.CheckAreaVo">
        SELECT
            acia.AREANAME,
            acia.ID as id,
            IFNULL(CONCAT(GROUP_CONCAT(b.AREANAME order by b.TREECODE separator '/'),'/',acia.AREANAME),acia.AREANAME) as name
        FROM
            asp_check_items_area acia
                LEFT JOIN asp_check_items_area  b ON  FIND_IN_SET(b.ID,acia.FULLPID) and b.DELETED='0'
        WHERE
            acia.DELETED = '0'
            <bind name="pid" value="'%' + tenantid+ '%'" />
          and (acia.ID=#{tenantid} OR  acia.FULLPID like #{pid})
        group by  acia.ID
    </select>
    <select id="getOrgByTenantId" resultType="com.rongwei.rwadmincommon.system.domain.SysOrganizationDo">
        <bind name="pid" value="'%' + tenantid+ '%'" />
        select ID ,FULLNAME from sys_organization where DELETED='0' and (ID=#{tenantid} OR  FULLPID like #{pid})
    </select>
    <select id="getUserInfoByTenantId" resultType="com.rongwei.rwadmincommon.system.domain.SysUserDo">
        select
        su.ID as id,
        su.NAME as name
        from sys_user su
        lEFT JOIN sys_user_org suo on suo.USERID = su.ID and suo.DELETED = '0'
        LEFT JOIN sys_organization so on suo.ORGID=so.ID and so.DELETED='0'
        <where>
            su.DELETED='0'
            AND (FIND_IN_SET(#{tenantid},so.FULLPID) or so.ID=#{tenantid})
        </where>
    </select>
</mapper>
