public sys_role Find(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select ta.id,");
sql.Append(" ta.code,");
sql.Append(" ta.status,");
sql.Append(" ta.title,");
sql.Append(" ta.adduser,");
sql.Append(" ta.addusername,");
sql.Append(" tc.id,");
sql.Append(" tc.pid,");
sql.Append(" tc.title,");
sql.Append(" tc.menutype,");
sql.Append(" tc.code,");
sql.Append(" tc.seq");
sql.Append(" from sys_role ta, sys_role_menu tb, sys_menu tc");
sql.Append(" where ta.id = tb.roleid ");
sql.Append(" and tb.menuid = tc.id ");
sql.Append(" and tc.status = 1 ");
sql.Append(" and ta.status = 1 ");
sql.Append(" and ta.id = :id ");
using (var conn = new OraDBHelper().Conn)
{
Dictionary <int, sys_role> dic_role = new Dictionary <int, sys_role>();
var role_menus = conn.Query <sys_role_menu>("select roleid,menuid,permis from sys_role_menu where roleid=:id", new { id = id });
var query = conn.Query <sys_role, sys_menu, sys_role>(sql.ToString(), (role, menu) =>
{
sys_role roleentity = new sys_role();
if (!dic_role.TryGetValue(role.id, out roleentity))
{
roleentity = role;
roleentity.role_menus = new List <sys_menu>();
dic_role.Add(roleentity.id, roleentity);
}
var permis = role_menus.Where(t => t.menuid == menu.id).Select(t => t.permis).FirstOrDefault();
if (permis != null)
{
menu.menu_permission = JsonConvert.DeserializeObject <sys_permission>(permis);
}
else
{
menu.menu_permission = new sys_permission();
}
roleentity.role_menus.Add(menu);
return(roleentity);
}, new { id = id });
return(query.FirstOrDefault());
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <base_sbxx> Search(DeviceQueryParm parm, out int resultcount)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select sbbh, sbmc, gcdm, scx, gwh, sblx, txfs, ip, sfky, sflj, bz, lrr, lrsj, com, port from base_sbxx where 1=1 ");
OracleDynamicParameters p = new OracleDynamicParameters();
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (sbbh like :key or sbmc like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Util.Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <base_sbxx>(sql.ToString(), p)
.OrderBy(t => t.sbbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <zxjc_djgw> Search(CheckPointQueryParm parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select ta.gcdm, ta.scx, ta.gwh,(select work_name from zxjc_gxzd where work_no = ta.gwh) as gwmc,ta.jx_no, ta.status_no, ta.djno, ta.djxx, ta.scbz, ta.lrr, ta.lrsj, ta.djlx ");
sql.Append(" from zxjc_djgw ta where 1 = 1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (ta.gwh like :key or lower(ta.jx_no) like :key or lower(ta.djxx) like :key )");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Util.Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_djgw>(sql.ToString(), p)
.OrderByDescending(t => t.djno)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public sys_user Find(int id)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select ta.id, ta.code, ta.name, ta.pwd, ta.token, ta.adduser, ta.addtime, ta.status, ta.headimg,tc.id,tc.code,tc.title,tc.status \r\n");
sql.Append(" from sys_user ta, sys_user_role tb, sys_role tc where ta.id = tb.userid and tb.roleid = tc.id and ta.id=:userid \r\n");
using (var conn = new OraDBHelper().Conn)
{
var userdic = new Dictionary <int, sys_user>();
var q = conn.Query <sys_user, sys_role, sys_user>(sql.ToString(), (user, role) =>
{
sys_user userEntry;
if (!userdic.TryGetValue(user.id, out userEntry))
{
userEntry = user;
userEntry.roles = new List <sys_role>();
userdic.Add(userEntry.id, userEntry);
}
userEntry.roles.Add(role);
return(userEntry);
}, new { userid = id });
return(q.FirstOrDefault());
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
//public override int Modify(zxjc_djxx entity)
//{
// try
// {
// StringBuilder sql = new StringBuilder();
// sql.Append("update zxjc_djxx set gcdm=:gcdm,");
// sql.Append(" scx=:scx,");
// sql.Append(" gwh =:gwh,");
// sql.Append(" jx_no =:jx_no,");
// sql.Append(" status_no =:status_no,");
// sql.Append(" djno =:djno,");
// sql.Append(" djxx =:djxx,");
// sql.Append(" djjg =:djjg,");
// sql.Append(" bz =:bz where id=:id ");
// using (var conn = new OraDBHelper(constr).Conn)
// {
// return conn.Execute(sql.ToString(), entity);
// }
// }
// catch (Exception e)
// {
// log.Error(e.Message);
// throw;
// }
//}
public IEnumerable <zxjc_djxx> Search(sys_page parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select id,gcdm,scx,gwh,jx_no,status_no,djno,djxx,djjg,bz,lrr,lrsj from zxjc_djxx where 1=1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (djxx like :key or jx_no like :key or djno like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_djxx>(sql.ToString(), p)
.OrderBy(t => t.djno)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_user> Search(UserQueryParm parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select ta.id, ta.code, ta.name, ta.pwd, ta.token, ta.adduser, ta.addtime, ta.status, ta.headimg,tc.id,tc.code,tc.title,tc.status \r\n");
sql.Append(" from sys_user ta, sys_user_role tb, sys_role tc where ta.id = tb.userid and tb.roleid = tc.id \r\n");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (ta.code like :key or ta.name like :key) \r\n");
p.Add(":key", parm.keyword, OracleMappingType.NVarchar2, ParameterDirection.Input);
}
if (parm.queryexp.Count() != 0)
{
sql.Append(" and ");
foreach (var item in parm.queryexp)
{
sql.Append($"{item.left}");
if (item.oper == "like")
{
sql.Append($" {item.colname} {item.oper} '%{item.value}%' {item.logic} ");
}
else
{
sql.Append($" {item.colname} {item.oper} '{item.value}' {item.logic} ");
}
sql.Append($"{item.right}");
}
}
using (var conn = new OraDBHelper().Conn)
{
var userdic = new Dictionary <int, sys_user>();
var q = conn.Query <sys_user, sys_role, sys_user>(sql.ToString(),
(user, role) =>
{
sys_user userEntry;
if (!userdic.TryGetValue(user.id, out userEntry))
{
userEntry = user;
userEntry.roles = new List <sys_role>();
userdic.Add(userEntry.id, userEntry);
}
userEntry.roles.Add(role);
return(userEntry);
}, p, splitOn: "id")
.Distinct()
.OrderByDescending(t => t.id)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public int LogOut(string token)
{
try
{
var newtoken = new JWTHelper().CreateToken();
StringBuilder sql = new StringBuilder();
sql.Append("update sys_user set token=:newtoken where token=:token");
StringBuilder sql1 = new StringBuilder();
sql1.Append("SELECT id,code,name,token,adduser,addtime,status,headimg FROM sys_user where token=:token");
using (var conn = new OraDBHelper().Conn)
{
var user = conn.Query <sys_user>(sql1.ToString(), new { token = token }).FirstOrDefault();
int cnt = conn.Execute(sql.ToString(), new { token = token, newtoken = newtoken });
if (cnt > 0)
{
LogService logservice = new LogService();
logservice.LogoutLog(user);
}
return(cnt);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 人员信息
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public IEnumerable <sec_users> PersonList(string key)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT comp_no,");
sql.Append(" user_code,");
sql.Append(" user_name,");
sql.Append(" user_type,");
sql.Append(" depart_no,");
sql.Append(" gwxx,");
sql.Append(" pass_word,");
sql.Append(" bz,");
sql.Append(" class_no,");
sql.Append(" tsqx,");
sql.Append(" scx,");
sql.Append(" lx");
sql.Append(" FROM sec_users where 1=1 ");
if (!string.IsNullOrEmpty(key))
{
sql.Append(" and (user_code like :key or user_name like :key or scx like :key or class_no like :key )");
}
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Query <sec_users>(sql.ToString(), new { key = "%" + key + "%" }));
}
}
catch (Exception)
{
throw;
}
}
public IEnumerable <zxjc_t_jstc> Search(sys_page parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("select jtid,jcbh,jcmc,jcms,wjlj,jwdx,scry,scpc,scsj,yxqx1,yxqx2,gcdm,fp_flg,fp_sj,fpr,wjfl,scx from zxjc_t_jstc where 1=1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (jcmc like :key or jcbh like :key or jcms like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
sql.Append(Tool.ComQueryExp(parm.explist));
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_t_jstc>(sql.ToString(), p)
.OrderByDescending(t => t.jcbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public List <string> FindCodesByPid(int pid)
{
try
{
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <string>("select code from sys_menu where status = 1 and pid = :pid", new { pid = pid }).ToList());
}
}
catch (Exception)
{
throw;
}
}
public IEnumerable <sys_user> FindUserByName(string key)
{
try
{
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <sys_user>("select id,status,code,name from sys_user where status = 1 and (name like :key or code like :key)", new { key = "%" + key + "%" }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 机型状态
/// </summary>
/// <param name="jx"></param>
/// <returns></returns>
public IEnumerable <ztbm_new> GetZtList(string jx)
{
try
{
using (var conn = new OraDBHelper(constr).Conn)
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct cpbm as ztbm from ZTBM_NEW where lower(jx) like :key and rownum < 10 order by cpbm asc");
return(conn.Query <ztbm_new>(sql.ToString(), new { key = "%" + jx.ToLower() + "%" }));
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 工厂
/// </summary>
/// <returns></returns>
public IEnumerable <base_gcxx> FactoryList()
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select gcdm, gcmc,gsxx,gsmc from base_gcxx");
using (var conn = new OraDBHelper(constr).Conn)
{
return(conn.Query <base_gcxx>(sql.ToString()));
}
}
catch (Exception)
{
throw;
}
}
public IEnumerable <zxjc_ryxx_jn> Search(SkillQueryParm parm, out int resultcount)
{
try
{
OracleDynamicParameters p = new OracleDynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT ta.gcdm, ta.user_code,(select user_name from sec_users where user_code = ta.user_code) as user_name, ta.jnbh, ta.jnxx, ta.scx, ta.gwh,(select work_name from ZXJC_GXZD where work_no = ta.gwh) as gwmc, ta.sfhg, ta.lrr, ta.lrsj, ta.jnfl, ta.jnsj");
sql.Append(" FROM zxjc_ryxx_jn ta where 1 = 1 ");
if (!string.IsNullOrEmpty(parm.keyword))
{
sql.Append(" and (user_code like :key or jnbh like :key) ");
p.Add(":key", "%" + parm.keyword + "%", OracleMappingType.Varchar2, System.Data.ParameterDirection.Input);
}
if (parm.explist.Count > 0)
{
sql.Append(" and ");
foreach (var item in parm.explist)
{
sql.Append($"{item.left}");
if (item.oper == "like")
{
sql.Append($" {item.colname} {item.oper} '%{item.value}%' {item.logic} ");
}
else
{
sql.Append($" {item.colname} {item.oper} '{item.value}' {item.logic} ");
}
sql.Append($"{item.right}");
}
}
using (var conn = new OraDBHelper(constr).Conn)
{
var q = conn.Query <zxjc_ryxx_jn>(sql.ToString(), p)
.OrderBy(t => t.jnbh)
.ToPagedList(parm.pageindex, parm.pagesize);
resultcount = q.TotalItemCount;
return(q);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
/// <summary>
/// 物料信息
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public IEnumerable <base_wlxx> GetWlList(string key)
{
try
{
using (var conn = new OraDBHelper(constr).Conn)
{
StringBuilder sql = new StringBuilder();
sql.Append("select wlbm,wlmc,wljc,wlz,wlsx from base_wlxx where gc='9100' ");
sql.Append(" and ( lower(wlbm) like :key or lower(wlmc) like :key ) ");
sql.Append(" and rownum < 10 order by wlbm asc");
return(conn.Query <base_wlxx>(sql.ToString(), new { key = "%" + key.ToLower() + "%" }));
}
}
catch (Exception)
{
throw;
}
}
public int Modify(zxjc_djgw entity)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update zxjc_djgw ");
sql.Append(" set gcdm = :gcdm, ");
sql.Append(" scx = :scx, ");
sql.Append(" gwh = :gwh, ");
sql.Append(" jx_no = :jx_no, ");
sql.Append(" status_no = :status_no, ");
sql.Append(" djno = :djno, ");
sql.Append(" djxx = :djxx, ");
sql.Append(" scbz = :scbz, ");
sql.Append(" djlx = :djlx ");
sql.Append(" where djno = :djno ");
StringBuilder sql1 = new StringBuilder();
sql1.Append("select gcdm, ");
sql1.Append(" scx, ");
sql1.Append(" gwh, ");
sql1.Append(" jx_no, ");
sql1.Append(" status_no, ");
sql1.Append(" djno, ");
sql1.Append(" djxx, ");
sql1.Append(" scbz, ");
sql1.Append(" djlx from zxjc_djgw ");
sql1.Append(" where djno = :djno ");
using (var conn = new OraDBHelper(constr).Conn)
{
zxjc_djgw old = conn.Query <zxjc_djgw>(sql1.ToString(), new { djno = entity.djno }).FirstOrDefault();
int ret = conn.Execute(sql.ToString(), entity);
if (ret > 0)
{
logservice.UpdateLogJson <zxjc_djgw>(entity, old);
}
return(ret);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_menu_permission> UserPermission(int userid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" select tc.menuid,tc.permission ");
sql.Append(" from sys_user_role ta, sys_role_menu tb, sys_menu_data tc ");
sql.Append(" where ta.roleid = tb.roleid ");
sql.Append(" and tb.menuid = tc.menuid ");
sql.Append(" and ta.userid = :userid ");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <sys_menu_permission>(sql.ToString(), new { userid = userid }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_user> GetRoleUsers(int roleid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select distinct tb.id, tb.code, tb.name");
sql.Append(" from sys_user_role ta, sys_user tb");
sql.Append(" where ta.userid = tb.id");
sql.Append(" and tb.status = 1");
sql.Append(" and ta.roleid = :id");
using (var conn = new OraDBHelper().Conn)
{
return(conn.Query <sys_user>(sql.ToString(), new { id = roleid }));
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
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;
}
}
public IEnumerable <dynamic> PermissionTree()
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT id,");
sql.Append("pid,");
sql.Append("code,title,menutype");
sql.Append(" FROM sys_menu where status =1 order by pid,seq asc");
using (var conn = new OraDBHelper().Conn)
{
List <sys_menu> tree = new List <sys_menu>();
var list = conn.Query <sys_menu>(sql.ToString());
var pages = list.Where(t => t.menutype == "02");
int maxid = int.MaxValue;
List <sys_menu> alllist = new List <sys_menu>();
foreach (var item in list.Where(t => t.menutype == "01"))
{
alllist.Add(item);
}
foreach (var item in pages)
{
alllist.Add(item);
sys_menu node = new sys_menu();
node.id = maxid - item.id * 123;
node.pid = item.id;
node.title = "页面功能";
alllist.Add(node);
var flist = list.Where(t => t.menutype == "03" && t.pid == item.id).ToList();
foreach (var fitem in flist)
{
fitem.pid = node.id;
alllist.Add(fitem);
}
sys_menu node1 = new sys_menu();
node1.id = maxid - item.id * 234;
node1.pid = item.id;
node1.title = "编辑字段";
alllist.Add(node1);
var elist = list.Where(t => t.menutype == "04" && t.pid == item.id).ToList();
string hjson = JsonConvert.SerializeObject(elist);
foreach (var eitem in elist)
{
eitem.pid = node1.id;
alllist.Add(eitem);
}
sys_menu node2 = new sys_menu();
node2.id = maxid - item.id * 345;
node2.pid = item.id;
node2.title = "隐藏字段";
alllist.Add(node2);
var hlist = JsonConvert.DeserializeObject <List <sys_menu> >(hjson);
foreach (var hitem in hlist)
{
hitem.id = hitem.id * 10;
hitem.pid = node2.id;
alllist.Add(hitem);
}
}
//foreach (var item in alllist)
//{
// System.Console.WriteLine($"{item.id}----{item.pid}----{item.title}");
//}
foreach (var item in alllist.Where(t => t.pid == 0))
{
//System.Console.WriteLine($"{item.id}----{item.pid}----{item.title}");
item.children = SubPermissionTree(alllist, item.id);
tree.Add(item);
}
return(tree);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
public IEnumerable <sys_menu> Get_Role_Menus(int roleid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("select tb.id,");
sql.Append(" tb.pid,");
sql.Append(" tb.title,");
sql.Append(" tb.menutype,");
sql.Append(" tb.code,");
sql.Append(" tb.seq,");
sql.Append(" ta.permis");
sql.Append(" from sys_role_menu ta, sys_menu tb");
sql.Append(" where ta.roleid = :id");
sql.Append(" and tb.status = 1");
sql.Append(" and tb.id = ta.menuid");
StringBuilder sql1 = new StringBuilder();
sql1.Append(" select ta.id, ta.pid, ta.title, ta.menutype, ta.code ");
sql1.Append(" from SYS_MENU ta, sys_role_menu tb ");
sql1.Append(" where ta.pid = tb.menuid ");
sql1.Append(" and ta.menutype in ('03', '04') ");
sql1.Append(" and tb.roleid = :id ");
using (var conn = new OraDBHelper().Conn)
{
var list = conn.Query(sql.ToString(), new { id = roleid });
var list_fun = conn.Query <sys_menu>(sql1.ToString(), new { id = roleid });
int maxid = int.MaxValue;
List <sys_menu> menulist = new List <sys_menu>();
foreach (var item in list)
{
sys_menu mitem = new sys_menu()
{
id = Convert.ToInt32(item.ID),
pid = Convert.ToInt32(item.PID),
title = item.TITLE,
menutype = item.MENUTYPE,
code = item.CODE,
seq = Convert.ToInt32(item.SEQ)
};
int funpageid = maxid - mitem.id * 123;
int eidtpageid = maxid - mitem.id * 234;
int hidepageid = maxid - mitem.id * 345;
if (item.PERMIS != null)
{
sys_permission jsonobj = JsonConvert.DeserializeObject <sys_permission>(item.PERMIS.ToString());
mitem.menu_permission = jsonobj;
if (jsonobj.funs.Count > 0)
{
menulist.Add(new sys_menu()
{
id = funpageid,
pid = mitem.id,
title = "页面功能",
menutype = "03",
});
foreach (var obj in jsonobj.funs)
{
var fun_temp = list_fun.Where(t => t.pid == mitem.id && t.code == obj).FirstOrDefault();
if (fun_temp == null)
{
continue;
}
sys_menu funitem = new sys_menu();
funitem.menutype = "03";
funitem.code = fun_temp.code;
funitem.title = fun_temp.title;
funitem.pid = funpageid;
funitem.id = fun_temp.id;
menulist.Add(funitem);
}
}
if (jsonobj.editfields.Count > 0)
{
menulist.Add(new sys_menu()
{
id = eidtpageid,
pid = mitem.id,
title = "编辑字段",
menutype = "04",
});
foreach (var obj in jsonobj.editfields)
{
var edit_temp = list_fun.Where(t => t.pid == mitem.id && t.code == obj).FirstOrDefault();
if (edit_temp == null)
{
continue;
}
sys_menu editf = new sys_menu();
editf.menutype = "04";
editf.code = edit_temp.code;
editf.title = edit_temp.title;
editf.pid = eidtpageid;
editf.id = edit_temp.id;
menulist.Add(editf);
}
}
if (jsonobj.hidefields.Count > 0)
{
menulist.Add(new sys_menu()
{
id = hidepageid,
pid = mitem.id,
title = "隐藏字段",
menutype = "04",
});
foreach (var obj in jsonobj.hidefields)
{
var hide_temp = list_fun.Where(t => t.pid == mitem.id && t.code == obj).FirstOrDefault();
if (hide_temp == null)
{
continue;
}
sys_menu hidef = new sys_menu();
hidef.menutype = "04";
hidef.code = hide_temp.code;
hidef.title = hide_temp.title;
hidef.pid = hidepageid;
hidef.id = hide_temp.id * 10;
menulist.Add(hidef);
}
}
}
else
{
mitem.menu_permission = new sys_permission();
}
menulist.Add(mitem);
}
//foreach (var item in menulist)
//{
// System.Console.WriteLine($"{item.id}---{item.pid}----{item.title}");
//}
return(menulist);
}
}
catch (Exception e)
{
log.Error(e.Message);
throw;
}
}
