<?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.yunfeiyun.agmp.common.web.system.mapper.SysMenuMapper">

	<resultMap type="SysMenu" id="SysMenuResult">
		<id     property="menuId"         column="menuId"        />
		<result property="menuName"       column="menuName"      />
		<result property="menuParentid"       column="menuParentid"      />
		<result property="menuLevelcode"       column="menuLevelcode"      />
		<result property="menuSeq"       column="menuSeq"      />
		<result property="menuPath"           column="menuPath"           />
		<result property="menuComponent"      column="menuComponent"      />
		<result property="menuQuery"          column="menuQuery"          />
		<result property="menuOpentype"        column="menuOpentype"       />
		<result property="menuType"       column="menuType"      />
		<result property="menuVisiblestatus"     column="menuVisiblestatus"        />
		<result property="menuStatus"        column="menuStatus"        />
		<result property="menuApistatus"        column="menuApistatus"        />
		<result property="menuPerms"          column="menuPerms"          />
		<result property="menuIcon"           column="menuIcon"           />
		<result property="menuCreator"       column="menuCreator"      />
		<result property="menuCreateddate"     column="menuCreateddate"    />
		<result property="menuModifieddate"     column="menuModifieddate"    />
		<result property="menuModifier"       column="menuModifier"      />
		<result property="menuRemark"         column="menuRemark"         />
	</resultMap>

	<sql id="selectMenuVo">
		select menuId, menuName, menuParentid, menuSeq, menuPath, menuComponent, menuQuery, menuOpentype, menuType, menuVisiblestatus,menuStatus,menuApistatus, ifnull(menuPerms,'') as menuPerms, menuIcon, menuCreateddate,menuModifieddate,menuRemark
		from SysMenu
	</sql>

	<select id="selectMenuList" parameterType="SysMenu" resultMap="SysMenuResult">
		<include refid="selectMenuVo"/>
		<where>
			<if test="menuName != null and menuName != ''">
				AND menuName like concat('%', #{menuName}, '%')
			</if>
			<if test="menuVisiblestatus != null and menuVisiblestatus != ''">
				AND menuVisiblestatus = #{menuVisiblestatus}
			</if>
			<if test="menuStatus != null and menuStatus != ''">
				AND menuStatus = #{menuStatus}
			</if>
			<if test="menuPerms != null and menuPerms != ''">
				AND menuPerms like concat('%', #{menuPerms}, '%')
			</if>
		</where>
		order by menuParentid, menuSeq
	</select>

	<select id="selectMenuTreeAll" resultMap="SysMenuResult">
		select distinct m.menuId, m.menuParentid, m.menuName, m.menuPath, m.menuComponent, m.menuQuery, m.menuVisiblestatus,m.menuStatus, ifnull(m.menuPerms,'') as menuPerms, m.menuOpentype,  m.menuType, m.menuIcon, m.menuSeq, m.menuCreateddate
		from SysMenu m where m.menuType in ('M', 'C') and m.menuStatus = 0
		order by m.menuParentid, m.menuSeq
	</select>

	<select id="selectMenuListByUserId" parameterType="SysMenu" resultMap="SysMenuResult">
		select distinct m.menuId, m.menuParentid, m.menuName, m.menuPath, m.menuComponent, m.menuQuery, m.menuVisiblestatus,m.menuStatus, ifnull(m.menuPerms,'') as menuPerms, m.menuOpentype,  m.menuType, m.menuIcon, m.menuSeq, m.menuCreateddate
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		left join SysUserrole ur on rm.roleId = ur.roleId
		left join SysRole ro on ur.roleId = ro.roleId
		where ur.userId = #{params.userId}
		<if test="menuName != null and menuName != ''">
			AND m.menuName like concat('%', #{menuName}, '%')
		</if>
		<if test="menuStatus != null and menuStatus != ''">
			AND m.menuStatus = #{menuStatus}
		</if>
		order by m.menuParentid, m.menuSeq
	</select>

	<select id="selectMenuTreeByUserId" parameterType="String" resultMap="SysMenuResult">
		select distinct m.menuId, m.menuParentid, m.menuName, m.menuPath, m.menuComponent, m.menuQuery, m.menuVisiblestatus,m.menuStatus, ifnull(m.menuPerms,'') as menuPerms, m.menuOpentype, m.menuType, m.menuIcon, m.menuSeq, m.menuCreateddate
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		left join SysUserrole ur on rm.roleId = ur.roleId
		left join SysRole ro on ur.roleId = ro.roleId
		left join SysUser u on ur.userId = u.userId
		where u.userId = #{userId} and m.menuType in ('M', 'C') and m.menuStatus = 0  AND ro.roleDelstatus = 0
		order by m.menuParentid, m.menuSeq
	</select>

	<select id="selectMenuListByRoleId" resultType="String">
		select m.menuId
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		where rm.roleId = #{roleId}
		<if test="menuCheckStrictly">
			and m.menuId not in (select m.menuParentid from SysMenu m inner join SysRolemenu rm on m.menuId = rm.menuId and rm.roleId = #{roleId})
		</if>
		order by m.menuParentid, m.menuSeq
	</select>

	<select id="selectMenuPerms" resultType="String">
		select distinct m.menuPerms
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		left join SysUserrole ur on rm.roleId = ur.roleId
	</select>

	<select id="selectMenuPermsByUserId" parameterType="String" resultType="String">
		select distinct m.menuPerms
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		left join SysUserrole ur on rm.roleId = ur.roleId
		left join SysRole r on r.roleId = ur.roleId
		where m.menuStatus = '0' and r.roleAvlstatus = '0' and ur.userId = #{userId}
	</select>

	<select id="selectMenuPermsByRoleId" parameterType="String" resultType="String">
		select distinct m.menuPerms
		from SysMenu m
		left join SysRolemenu rm on m.menuId = rm.menuId
		where m.menuStatus = '0' and rm.roleId = #{roleId}
	</select>

	<select id="selectMenuById" parameterType="String" resultMap="SysMenuResult">
		<include refid="selectMenuVo"/>
		where menuId = #{menuId}
	</select>

	<select id="hasChildByMenuId" parameterType="String" resultType="Integer">
		select count(1) from SysMenu where menuParentid = #{menuId}
	</select>


	<select id="selectMenuTreeByCfgId" parameterType="String" resultMap="SysMenuResult">
		select distinct m.menuId, m.menuParentid, m.menuName,
		m.menuPath, m.menuComponent, m.menuQuery, m.menuVisiblestatus,
		m.menuStatus, ifnull(m.menuPerms,'') as menuPerms,
		m.menuOpentype, m.menuType, m.menuIcon,
		 m.menuSeq, m.menuCreateddate
		 from (select menuId as menuIdcfg, menucfgSys from SysMenuconfig
		 where menucfgSys = #{menucfgId})  t1
		 left join SysMenu m on m.menuId = t1.menuIdcfg
		 WHERE  m.menuStatus = 0
		 order by m.menuParentid, m.menuSeq
	</select>

	<select id="checkMenuNameUnique" parameterType="SysMenu" resultMap="SysMenuResult">
		<include refid="selectMenuVo"/>
		where menuName=#{menuName} and menuParentid = #{menuParentid} limit 1
	</select>

	<update id="updateMenu" parameterType="SysMenu">
		update SysMenu
		<set>
			<if test="menuName != null and menuName != ''">menuName = #{menuName},</if>
			<if test="menuParentid != null">menuParentid = #{menuParentid},</if>
			<if test="menuLevelcode != null">menuLevelcode = #{menuLevelcode},</if>
			<if test="menuSeq != null">menuSeq = #{menuSeq},</if>
			<if test="menuPath != null and menuPath != ''">menuPath = #{menuPath},</if>
			<if test="menuComponent != null">menuComponent = #{menuComponent},</if>
			<if test="menuQuery != null">`menuQuery` = #{menuQuery},</if>
			<if test="menuOpentype != null and menuOpentype != ''">menuOpentype = #{menuOpentype},</if>
			<if test="menuType != null and menuType != ''">menuType = #{menuType},</if>
			<if test="menuVisiblestatus != null">menuVisiblestatus = #{menuVisiblestatus},</if>
			<if test="menuStatus != null">menuStatus = #{menuStatus},</if>
			<if test="menuApistatus != null">menuApistatus = #{menuApistatus},</if>
			<if test="menuPerms !=null">menuPerms = #{menuPerms},</if>
			<if test="menuIcon !=null and menuIcon != ''">menuIcon = #{menuIcon},</if>
			<if test="menuRemark != null and menuRemark != ''">menuRemark = #{menuRemark},</if>
			<if test="menuCreator != null and menuCreator != ''">menuCreator = #{menuCreator},</if>
			<if test="menuModifier != null and menuModifier != ''">menuModifier= #{menuModifier},</if>
			menuModifieddate = date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
		</set>
		where menuId = #{menuId}
	</update>

	<insert id="insertMenu" parameterType="SysMenu">
		insert into SysMenu(
		<if test="menuId != null and menuId != ''">menuId,</if>
		<if test="menuParentid != null and menuParentid != ''">menuParentid,</if>
		<if test="menuLevelcode != null and menuLevelcode != ''">menuLevelcode,</if>
		<if test="menuName != null and menuName != ''">menuName,</if>
		<if test="menuSeq != null">menuSeq,</if>
		<if test="menuPath != null and menuPath != ''">menuPath,</if>
		<if test="menuComponent != null and menuComponent != ''">menuComponent,</if>
		<if test="menuQuery != null and menuQuery != ''">menuQuery,</if>
		<if test="menuOpentype != null and menuOpentype != ''">menuOpentype,</if>
		<if test="menuType != null and menuType != ''">menuType,</if>
		<if test="menuVisiblestatus != null">menuVisiblestatus,</if>
		<if test="menuStatus != null">menuStatus,</if>
		<if test="menuApistatus != null">menuApistatus,</if>
		<if test="menuPerms !=null and menuPerms != ''">menuPerms,</if>
		<if test="menuIcon != null and menuIcon != ''">menuIcon,</if>
		<if test="menuRemark != null and menuRemark != ''">menuRemark,</if>
		<if test="menuCreator != null and menuCreator != ''">menuCreator,</if>
		<if test="menuModifier != null and menuModifier != ''">menuModifier,</if>
		menuCreateddate,
		menuModifieddate
		)values(
		<if test="menuId != null and menuId != ''">#{menuId},</if>
		<if test="menuParentid != null and menuParentid != ''">#{menuParentid},</if>
		<if test="menuLevelcode != null and menuLevelcode != ''">#{menuLevelcode},</if>
		<if test="menuName != null and menuName != ''">#{menuName},</if>
		<if test="menuSeq != null">#{menuSeq},</if>
		<if test="menuPath != null and menuPath != ''">#{menuPath},</if>
		<if test="menuComponent != null and menuComponent != ''">#{menuComponent},</if>
		<if test="menuQuery != null and menuQuery != ''">#{menuQuery},</if>
		<if test="menuOpentype != null and menuOpentype != ''">#{menuOpentype},</if>
		<if test="menuType != null and menuType != ''">#{menuType},</if>
		<if test="menuVisiblestatus != null">#{menuVisiblestatus},</if>
		<if test="menuStatus != null">#{menuStatus},</if>
		<if test="menuApistatus != null">#{menuApistatus},</if>
		<if test="menuPerms !=null and menuPerms != ''">#{menuPerms},</if>
		<if test="menuIcon != null and menuIcon != ''">#{menuIcon},</if>
		<if test="menuRemark != null and menuRemark != ''">#{menuRemark},</if>
		<if test="menuCreator != null and menuCreator != ''">#{menuCreator},</if>
		<if test="menuModifier != null and menuModifier != ''">#{menuModifier},</if>
		date_format(sysdate(),'%Y-%m-%d %H:%i:%s'),
		date_format(sysdate(),'%Y-%m-%d %H:%i:%s')
		)
	</insert>

	<delete id="deleteMenuById" parameterType="String">
		delete from SysMenu where menuId = #{menuId}
	</delete>

</mapper> 