<?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.lantone.security.mapper.ResourceMapper">

    <select id="getResourceRolesMap" resultType="string">
        SELECT
        CONCAT(t2.url,'+',t2.software_id,'=',GROUP_CONCAT(concat(t1.role_id,'_',t1.role_name)))
        FROM
        (SELECT
        a.`name` as role_name,b.role_id,c.software_id,e.resource_id
        FROM sys_role a JOIN sys_role_software_menu b ON a.id=b.role_id
        JOIN sys_software_menu c ON b.software_menu_id=c.id
		JOIN sys_software f ON c.software_id = f.id
        JOIN sys_menu d ON c.menu_id=d.id
        JOIN sys_menu_resource e ON d.id=e.menu_id
        WHERE a.is_deleted=#{isDeleted} AND b.is_deleted=#{isDeleted} AND c.is_deleted=#{isDeleted}
        AND d.is_deleted=#{isDeleted} AND e.is_deleted=#{isDeleted} AND f.is_deleted=#{isDeleted}
		AND a.`status`=#{status} AND d.`status`=#{status} AND f.`status`=#{status}) t1
        JOIN
        (SELECT
        b.role_id,c.software_id,c.resource_id,d.url
        FROM sys_role a JOIN sys_role_software_resource b ON a.id=b.role_id
        JOIN sys_software_resource c ON b.software_resource_id=c.id
		JOIN sys_software e ON c.software_id=e.id
        JOIN sys_resource d ON c.resource_id=d.id
        WHERE a.is_deleted=#{isDeleted} AND b.is_deleted=#{isDeleted} AND c.is_deleted=#{isDeleted}
        AND d.is_deleted=#{isDeleted} AND e.is_deleted=#{isDeleted} AND a.`status`=#{status}
		AND d.`status`=#{status} AND e.`status`=#{status}) t2
        ON t1.role_id=t2.role_id AND t1.resource_id=t2.resource_id AND t1.software_id=t2.software_id
        GROUP BY t2.url,t2.software_id
    </select>

</mapper>