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; } }