<?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.ejweb.modules.sys.dao.RoleDao">
    
    <resultMap id="roleResult" type="Role">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="enname" column="enname" />
        <result property="roleType" column="roleType" />
        <result property="dataScope" column="dataScope" />
        <result property="remarks" column="remarks" />
        <result property="useable" column="useable" />
        <result property="sysData" column="sysData" /><!-- 
        <collection property="userList" ofType="User">
            <id property="id" column="userList.id" />
            <id property="loginName" column="userList.loginName" />
            <id property="name" column="userList.name" />
            <id property="email" column="userList.email" />
            <id property="phone" column="userList.phone" />
            <id property="mobile" column="userList.mobile" />
            <id property="office.name" column="userList.office.name" />
            <id property="company.name" column="userList.company.name" />
        </collection> -->
        <collection property="menuList" ofType="Menu">
            <id property="id" column="menuList.id" />
        </collection>
        <collection property="officeList" ofType="Office">
            <id property="id" column="officeList.id" />
        </collection>
    </resultMap>
    
    <sql id="roleColumns">
        a.id,
        a.office_id AS "office.id",
        a.name,
        a.enname,
        a.role_type AS roleType,
        a.data_scope AS dataScope,
        a.remarks,
        a.create_by AS "createBy.id",
        a.create_date,
        a.update_by AS "updateBy.id",
        a.update_date,
        a.del_flag,
        o.name AS "office.name",
        o.code,
        a.useable AS useable,
        a.un_checked,
        a.is_sys AS sysData
    </sql>

    <select id="get" resultMap="roleResult">
        SELECT
            <include refid="roleColumns"/>,<!--
            ur.user_id AS "userList.id",
            u.login_name AS "userList.loginName",
            u.name AS "userList.name",
            u.email AS "userList.email",
            u.phone AS "userList.phone",
            u.mobile AS "userList.mobile",
            uc.name AS "userList.company.name",
            uo.name AS "userList.office.name", -->
            rm.menu_id AS "menuList.id",
            ro.office_id AS "officeList.id"
        FROM ct_bbtc_sys_role a
        LEFT JOIN ct_bbtc_sys_office o
          ON o.id = a.office_id<!--
        LEFT JOIN ct_bbtc_sys_user2role ur ON ur.role_id = a.id
        LEFT JOIN ct_bbtc_sys_user u ON u.id = ur.user_id
        LEFT JOIN ct_bbtc_sys_office uc ON uc.id = u.company_id
        LEFT JOIN ct_bbtc_sys_office uo ON uo.id = u.office_id -->
        LEFT JOIN ct_bbtc_sys_menu2role rm
          ON rm.role_id = a.id
        LEFT JOIN ct_bbtc_sys_office2role ro
          ON ro.role_id = a.id
        WHERE a.id = #{id}
    </select>

    <select id="getByName" resultType="Role">
        SELECT
            <include refid="roleColumns"/>
        FROM ct_bbtc_sys_role a
        JOIN ct_bbtc_sys_office o ON o.id = a.office_id
        WHERE a.name = #{name} AND a.del_flag = #{DEL_FLAG_NORMAL}
    </select>

    <select id="getByEnname" resultType="Role">
        SELECT
            <include refid="roleColumns"/>
        FROM ct_bbtc_sys_role a
        JOIN ct_bbtc_sys_office o ON o.id = a.office_id
        WHERE a.enname = #{enname} AND a.del_flag = #{DEL_FLAG_NORMAL}
    </select>

    <select id="findList" resultMap="roleResult">
        SELECT <!-- DISTINCT -->
            <include refid="roleColumns"/>,
            ro.office_id AS "officeList.id"
        FROM ct_bbtc_sys_role a
        LEFT JOIN ct_bbtc_sys_office o ON o.id = a.office_id
        LEFT JOIN ct_bbtc_sys_user2role ur ON ur.role_id = a.id
        LEFT JOIN ct_bbtc_sys_user u ON u.id = ur.user_id
        LEFT JOIN ct_bbtc_sys_office2role ro ON ro.role_id = a.id
        WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.useable=#{useable}
        <if test="user != null and user.id != null and user.id != ''">
            AND u.id = #{user.id}
        </if>
        <if test="user != null and user.loginName != null and user.loginName != ''">
            AND u.login_name = #{user.loginName}
        </if>
        <!-- 数据范围过滤 -->
        ${sqlMap.dsf}
        ORDER BY o.code, a.name
    </select>

    <select id="findAllList" resultType="Role">
        SELECT
            <include refid="roleColumns"/>
        FROM ct_bbtc_sys_role a
        LEFT JOIN ct_bbtc_sys_office o ON o.id = a.office_id
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        <if test="unChecked != null and unChecked != ''">
            AND a.un_checked = #{unChecked}
        </if>
        ORDER BY o.code, a.name
    </select>
    
    <insert id="insert">
        INSERT INTO ct_bbtc_sys_role(
            id, 
            office_id, 
            name, 
            enname, 
            role_type, 
            data_scope, 
            create_by, 
            create_date, 
            update_by, 
            update_date, 
            remarks, 
            del_flag,
            is_sys,
            useable
        ) VALUES (
            #{id}, 
            #{office.id}, 
            #{name}, 
            #{enname}, 
            #{roleType}, 
            #{dataScope}, 
            #{createBy.id}, 
            #{createDate}, 
            #{updateBy.id}, 
            #{updateDate}, 
            #{remarks}, 
            #{delFlag},
            #{sysData},
            #{useable}
        )
    </insert>
    
    <update id="update">
        UPDATE ct_bbtc_sys_role SET
            office_id = #{office.id}, 
            name = #{name}, 
            enname = #{enname}, 
            role_type = #{roleType}, 
            data_scope = #{dataScope}, 
            update_by = #{updateBy.id}, 
            update_date = #{updateDate}, 
            remarks = #{remarks},
            is_sys = #{sysData},
            useable = #{useable}
        WHERE id = #{id}
    </update>
    
    <delete id="deleteRoleMenu">
        DELETE FROM ct_bbtc_sys_menu2role WHERE role_id = #{id}
    </delete>
    
    <insert id="insertRoleMenu">
        INSERT INTO ct_bbtc_sys_menu2role(role_id, menu_id)
        <foreach collection="menuList" item="menu" separator=" union all ">
            SELECT #{id}, #{menu.id}
            <if test="dbName != 'mssql'">
            FROM dual
            </if>
        </foreach>
    </insert>
    
    <delete id="deleteRoleOffice">
        DELETE FROM ct_bbtc_sys_office2role WHERE role_id = #{id}
    </delete>
    
    <insert id="insertRoleOffice">
        INSERT INTO ct_bbtc_sys_office2role(role_id, office_id)
        <foreach collection="officeList" item="office" separator=" union all ">
            SELECT #{id}, #{office.id}
            <if test="dbName != 'mssql'">
            FROM dual
            </if>
        </foreach>
    </insert>
    
    <update id="delete">
        UPDATE ct_bbtc_sys_role SET
            del_flag = #{DEL_FLAG_DELETE}
        WHERE id = #{id}
    </update>
    
</mapper>