fj-dc/.svn/pristine/d3/d31afb4a89ad5fc2b51b695989db063eded63c2a.svn-base

<?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="cn.com.goldenwater.dcproj.dao.MenuDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.Menu" id="menuResultMap">
        <result property="id" column="ID" />
        <result property="privId" column="PRIV_ID" />
        <result property="parentId" column="PARENT_ID" />
        <result property="menuName" column="MENU_NAME" />
        <result property="sortLevel" column="SORT_LEVEL" />
        <result property="sequence" column="SEQUENCE" />
        <result property="href" column="HREF" />
        <result property="icon" column="ICON" />
        <result property="ownApp" column="OWN_APP" />
    </resultMap>

    <resultMap type="cn.com.goldenwater.dcproj.dto.MenuDto" id="dtoMenuResultMap">
        <result property="id" column="ID" />
        <result property="privId" column="PRIV_ID" />
        <result property="privName" column="PRIV_NAME" />
        <result property="parentId" column="PARENT_ID" />
        <result property="parentName" column="PARENT_NAME" />
        <result property="menuName" column="MENU_NAME" />
        <result property="sortLevel" column="SORT_LEVEL" />
        <result property="sequence" column="SEQUENCE" />
        <result property="href" column="HREF" />
        <result property="icon" column="ICON" />
        <result property="checked" column="CHECKED"/>
        <result property="code" column="CODE"/>
        <result property="flagLeaf" column="FLAG_LEAF"/>
        <result property="ownApp" column="OWN_APP" />
    </resultMap>

    <sql id="table_columns">
        ID,
        PRIV_ID,
        PARENT_ID,
        MENU_NAME,
        SORT_LEVEL,
        SEQUENCE,
        HREF,
        ICON,OWN_APP
    </sql>
    <sql id="entity_properties">
        #{id},
        #{privId},
        #{parentId},
        #{menuName},
        #{sortLevel},
        #{sequence},
        #{href},
        #{icon},#{ownApp}
    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="privId != null and privId != ''">and PRIV_ID = #{privId}</if>
            <if test="parentId != null and parentId != ''">and PARENT_ID = #{parentId}</if>
            <if test="menuName != null and menuName != ''">and MENU_NAME LIKE concat(concat('%',#{menuName}),'%')</if>
            <if test="sortLevel != null">and SORT_LEVEL = #{sortLevel}</if>
            <if test="href != null and href != ''">and HREF = #{href}</if>
            <if test="icon != null and icon != ''">and ICON = #{icon}</if>
            <if test="ownApp != null and ownApp != ''">and OWN_APP like '%${ownApp}%'</if>
        </trim>
    </sql>


    <select id="get" resultMap="menuResultMap" parameterType="String" >
        select <include refid="table_columns" /> from GW_SYS_MENU where ID = #{id}
    </select>

    <select id="getBy" resultMap="menuResultMap">
        select <include refid="table_columns" /> from GW_SYS_MENU <include refid="page_where" />
    </select>

    <select id="findAll" resultMap="menuResultMap">
        select <include refid="table_columns" /> from GW_SYS_MENU
    </select>

    <select id="findList" resultMap="menuResultMap">
        select <include refid="table_columns" /> from GW_SYS_MENU <include refid="page_where" />
    </select>

    <select id="selectCount" resultType="int" >
        select count(ID) from GW_SYS_MENU <include refid="page_where" />
    </select>


    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.Menu">
        insert into GW_SYS_MENU( <include refid="table_columns" /> )
        values ( <include refid="entity_properties" /> )
    </insert>

    <delete id="delete" parameterType="java.lang.String">
        delete from GW_SYS_MENU where ID = #{id}
    </delete>

    <delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.Menu">
        delete from GW_SYS_MENU <include refid="page_where" />
    </delete>

    <update id="deleteInFlag" parameterType="java.lang.String">
        update GW_SYS_MENU set flag_valid = 0 where>ID = #{id}
    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.Menu">
        update GW_SYS_MENU
        <trim prefix="set" suffixOverrides=",">
            <if test="privId != null and privId != ''">PRIV_ID = #{privId},</if>
            <if test="parentId != null and parentId != ''">PARENT_ID = #{parentId},</if>
            <if test="menuName != null and menuName != ''">MENU_NAME = #{menuName},</if>
            <if test="sortLevel != null">SORT_LEVEL = #{sortLevel},</if>
            <if test="sequence != null">SEQUENCE = #{sequence},</if>
            <if test="ownApp != null and ownApp != ''">OWN_APP = #{ownApp},</if>
            HREF = #{href},
            ICON = #{icon}
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="clearMemuPriv" parameterType="String">
        UPDATE GW_SYS_MENU
        <set>
            PRIV_ID = null
        </set>
        <where>
            PRIV_ID = #{privId}
        </where>
    </update>

    <update id="updateMemuPriv" parameterType="java.util.List">
        <foreach collection="list" index="index" item="item" open="begin" close=";end;" separator=";" >
            UPDATE GW_SYS_MENU
            <set>
                PRIV_ID = #{item.privId}
            </set>
            <where>
                ID = #{item.id}
            </where>
        </foreach>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.Menu">
        update GW_SYS_MENU
        <trim prefix="set" suffixOverrides=",">
            <if test="privId != null and privId != ''">PRIV_ID = #{privId},</if>
            <if test="parentId != null and parentId != ''">PARENT_ID = #{parentId},</if>
            <if test="menuName != null and menuName != ''">MENU_NAME = #{menuName},</if>
            <if test="sortLevel != null">SORT_LEVEL = #{sortLevel},</if>
            <if test="sequence != null">SEQUENCE = #{sequence},</if>
            <if test="href != null and href != ''">HREF = #{href},</if>
            <if test="icon != null and icon != ''">ICON = #{icon},</if>
        </trim>
        <include refid="page_where" />
    </update>

    <update id="updateByPrivId" parameterType="cn.com.goldenwater.dcproj.model.Menu">
        update GW_SYS_MENU
        <trim prefix="set" suffixOverrides=",">
            <if test="parentId != null and parentId != ''">PARENT_ID = #{parentId},</if>
            <if test="menuName != null and menuName != ''">MENU_NAME = #{menuName},</if>
            <if test="sortLevel != null">SORT_LEVEL = #{sortLevel},</if>
            <if test="sequence != null">SEQUENCE = #{sequence},</if>
            <if test="href != null and href != ''">HREF = #{href},</if>
            <if test="icon != null and icon != ''">ICON = #{icon},</if>
            <if test="ownApp != null and ownApp != ''"> OWN_APP = #{ownApp},</if>
        </trim>
        <where>
            PRIV_ID = #{privId}
        </where>
    </update>


    <!-- 根据userid查询菜单-->
    <select id="getMenuByUserId" resultMap="menuResultMap" parameterType="String">
        with recursive
        cte0 as (
        select t.id,t.priv_id, t.parent_id,t.menu_name,t.sort_level,t.sequence,t.href,t.own_app , t.icon ,t.parent_name,
        array[sequence]::INT[] as sort_path
        from t_temp t
        where coalesce(parent_id::text,'') =  '0'
        union all select k.id, k.priv_id,k. parent_id, k.menu_name,k.sort_level, k.sequence,k.href,k.own_app , k.icon , k.parent_name,
        ct.sort_path || array[k.sequence]::INT[] as sort_path
        from t_temp k,
        cte0 ct
        where ct.id = k.parent_id
        ),
        t_temp as (select c.id , c.priv_id , c.parent_id , c.menu_name , c.sort_level , c.sequence , c.href , c.own_app , c.icon , e.menu_name as parent_name
        from
        gw_sys_menu c
        left join gw_sys_menu e on
        c.parent_id = e.id
        left join gw_sys_priv d on
        d.id = c.priv_id
        where
        c.id is not null  <if test="name != null and name != ''"> and C.MENU_NAME like '%'||#{name}||'%'</if>
        <if test="ownApp !=null and ownApp !=''">
            AND C.own_app like '%${ownApp}%'
        </if>

        <if test="list != null and list.size()>0">
            AND D.OWNER_SYSTEM IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>)
        select * from cte0
        order by cte0.sort_path
    </select>

    <!-- 分页获取列表 -->
    <select id="findPage" resultMap="menuResultMap" parameterType="cn.com.goldenwater.dcproj.param.MenuParam">
        select
        <include refid="table_columns" />
        from GW_SYS_MENU
        <include refid="page_where" />
        order by
        <if test="orderBy != null and orderBy != ''"> #{orderBy},</if>id desc
    </select>

    <!-- 分页获取列表 -->
    <select id="findPageDto" resultMap="dtoMenuResultMap" parameterType="cn.com.goldenwater.dcproj.param.MenuParam">
        SELECT
        A.ID,A.PRIV_ID,B.PRIV_NAME,A.PARENT_ID,C.MENU_NAME PARENT_NAME,A.MENU_NAME,A.SORT_LEVEL,A.SEQUENCE,A.HREF,A.ICON,A.OWN_APP
        FROM GW_SYS_MENU A
        LEFT JOIN GW_SYS_PRIV B ON A.PRIV_ID=B.ID
        LEFT JOIN GW_SYS_MENU C ON A.PARENT_ID=C.ID
        WHERE 1 = 1
        <if test="id != null and id != ''">AND A.ID = #{id}</if>
        <if test="privId != null and privId != ''">AND A.PRIV_ID = #{privId}</if>
        <if test="parentId != null and parentId != ''">AND A.PARENT_ID = #{parentId}</if>
        <if test="menuName != null and menuName != ''">AND A.MENU_NAME LIKE '%'||#{menuName}||'%'</if>
        <if test="sortLevel != null and sortLevel != ''">AND A.SORT_LEVEL = #{sortLevel}</if>
        <if test="href != null and href != ''">AND A.HREF=#{href}</if>
        <if test="icon != null and icon != ''">AND A.ICON   =#{icon}</if>
        <if test="ownApp !=null and ownApp !=''">
            AND A.own_app like '%${ownApp}%'
        </if>
        order by
        A.id desc
    </select>

    <!-- 分页获取列表 -->
    <select id="findMenuList" resultMap="dtoMenuResultMap" parameterType="cn.com.goldenwater.dcproj.param.MenuParam">
        SELECT
        A.ID,A.PRIV_ID,B.PRIV_NAME,A.PARENT_ID,C.MENU_NAME PARENT_NAME,A.MENU_NAME,A.SORT_LEVEL,A.SEQUENCE,A.HREF,A.ICON,A.OWN_APP
        <if test="privId != null and privId != ''">,CASE WHEN A.PRIV_ID =#{privId} then 1 else 0 END CHECKED </if>

        FROM GW_SYS_MENU A
        LEFT JOIN GW_SYS_PRIV B ON A.PRIV_ID=B.ID
        LEFT JOIN GW_SYS_MENU C ON A.PARENT_ID=C.ID
        where 1=1
        <if test="ownApp !=null and ownApp !=''">
            AND A.own_app like '%${ownApp}%'
        </if>
        order by
        A.id desc
    </select>


    <select id="getListByUserId" resultMap="dtoMenuResultMap" parameterType="string">
        select distinct D.ID,D.MENU_NAME,D.SORT_LEVEL,D.HREF,D.PARENT_ID,D.ICON,D.OWN_APP,D.SEQUENCE,D.PRIV_ID,
        E.PRIV_NAME,E.CODE,E.FLAG_LEAF,E.PRIV_NAME,E.CODE,E.FLAG_LEAF from  GW_SYS_REL_USER_ROLE B  LEFT JOIN GW_SYS_REL_ROLE_PRIV C ON B.ROLE_ID=C.ROLE_ID LEFT JOIN GW_SYS_PRIV E ON C.PRIV_ID=E.ID
        RIGHT JOIN  GW_SYS_MENU D ON E.ID=D.PRIV_ID
        WHERE b.user_id=#{userId} AND E.PRIV_TYPE=#{privType}
        <if test="ownApp !=null and ownApp !=''">
            AND D.own_app like '%${ownApp}%'
        </if>
        ORDER BY D.SORT_LEVEL,D.SEQUENCE
    </select>
</mapper>