public void SearchMenu()
{
int cnt = 0;
MenuQueryParm p = new MenuQueryParm();
MenuService ms = new MenuService();
var list = ms.Search(p, out cnt);
var count = list.Count();
System.Console.WriteLine(count);
}
public IHttpActionResult SubMenu(MenuQueryParm parm)
{
try
{
int rescnt = 0;
MenuService ms = new MenuService();
var list = ms.SubMenuByPid(parm, out rescnt);
return(Json(new { code = 1, msg = "ok", list = list, resultcount = rescnt }));
}
catch (Exception e)
{
return(Json(new { code = 0, msg = e.Message }));
}
}
public IEnumerable <sys_menu> SubMenuByPid(MenuQueryParm parm, out int resultcount)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT id,");
sql.Append("pid,");
sql.Append("title,");
sql.Append("menutype,");
sql.Append("code,");
sql.Append("icon,");
sql.Append("path,");
sql.Append("viewpath,");
sql.Append("seq,");
sql.Append("adduser,");
sql.Append("addtime,");
sql.Append("status FROM sys_menu where status =1 and pid=:pid ");
using (var conn = new OraDBHelper().Conn)
{
var q = conn.Query <sys_menu>(sql.ToString(), new { pid = parm.pid })
.OrderBy(t => t.id)
.ToPagedList(parm.pageindex, parm.pagesize);
foreach (var item in q)
{
item.children = new List <sys_menu>();
item.hasChildren = true;
}
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_menu> Search(MenuQueryParm parm, out int resultcount)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" select ta.id,");
sql.Append("ta.pid,");
sql.Append("ta.title,");
sql.Append("ta.menutype,");
sql.Append("ta.code,");
sql.Append("ta.icon,");
sql.Append("ta.path,");
sql.Append("ta.viewpath,");
sql.Append("ta.seq,");
sql.Append("ta.adduser,");
sql.Append("ta.addtime,");
sql.Append("ta.status,");
sql.Append("tc.id,");
sql.Append("tc.title,");
sql.Append("tc.status,");
sql.Append("tc.code ");
sql.Append(" from SYS_MENU ta, sys_role_menu tb,sys_role tc ");
sql.Append(" where ta.id = tb.menuid ");
sql.Append(" and tb.roleid = tc.id ");
OracleDynamicParameters p = new OracleDynamicParameters();
if (parm.pid != -1)
{
sql.Append(" and ta.pid = :pid ");
p.Add(":pid", parm.pid, OracleMappingType.Int32, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and ta.title like :title ");
p.Add(":title", parm.keyword, OracleMappingType.NVarchar2, ParameterDirection.Input);
}
using (var conn = new OraDBHelper().Conn)
{
var menudic = new Dictionary <int, sys_menu>();
var query = conn.Query <sys_menu, sys_role, sys_menu>(sql.ToString(), (menu, role) =>
{
sys_menu menuEntry;
if (!menudic.TryGetValue(menu.id, out menuEntry))
{
menuEntry = menu;
menuEntry.roles = new List <sys_role>();
menudic.Add(menuEntry.id, menuEntry);
}
menuEntry.roles.Add(role);
return(menuEntry);
}, p, splitOn: "id")
.Distinct()
.OrderBy(t => t.pid)
.ThenBy(t => t.seq)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = query.TotalItemCount;
return(query);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_menu> MenuTree(MenuQueryParm parm, out int resultcount)
{
try
{
OracleDynamicParameters op = new OracleDynamicParameters();
StringBuilder exp = new StringBuilder();
if (!string.IsNullOrEmpty(parm.keyword))
{
exp.Append(" and (title like :key or code like :key) ");
op.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(parm.status))
{
exp.Append(" and status = :status ");
op.Add(":status", parm.status, OracleMappingType.Int32, ParameterDirection.Input);
}
if (parm.pid > 0)
{
exp.Append(" and pid = :pid ");
op.Add(":pid", parm.pid, OracleMappingType.Int32, ParameterDirection.Input);
}
StringBuilder sql = new StringBuilder();
sql.Append("with tm (id,pid,title,menutype,code,icon,path,viewpath,seq,adduser,addusername,addtime,status) as");
sql.Append("(SELECT id,");
sql.Append("pid,");
sql.Append("title,");
sql.Append("menutype,");
sql.Append("code,");
sql.Append("icon,");
sql.Append("path,");
sql.Append("viewpath,");
sql.Append("seq,");
sql.Append("adduser,(select name from sys_user where id = sys_menu.adduser) as addusername,");
sql.Append("addtime,");
sql.Append("status FROM sys_menu where ");
if (exp.Length == 0)
{
sql.Append(" pid = 0 ");
}
else
{
sql.Append(" 1=1 ");
sql.Append(exp);
}
sql.Append(" union all ");
sql.Append("select t1.id,t1.pid,t1.title,t1.menutype,t1.code,t1.icon,t1.path,t1.viewpath,t1.seq,t1.adduser,");
sql.Append("(select name from sys_user where id = t1.adduser) as addusername,");
sql.Append("t1.addtime,t1.status from sys_menu t1,tm where ");
if (exp.Length == 0)
{
sql.Append(" t1.pid = tm.id ");
}
else
{
sql.Append(" t1.id = tm.pid ");
}
sql.Append(")");
sql.Append("select * from tm ");
sql.Append(" order by pid,seq asc");
using (var conn = new OraDBHelper().Conn)
{
var list = conn.Query <sys_menu>(sql.ToString(), op);
IEnumerable <sys_menu> menulist = list.Where(t => t.pid == 0);
foreach (var item in menulist)
{
item.children = Create_Child(new List <sys_role_menu>(), list, item.id);
item.hasChildren = false;
}
var q = menulist.OrderBy(t => t.pid).ThenBy(t => t.seq).ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
