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 IEnumerable <sys_user> Get_User_List(UserQueryParm parm, out int Result_Count)
{
try
{
using (Db db = new Db())
{
var list = db.sys_users as IQueryable <sys_user>;
if (!string.IsNullOrEmpty(parm.key))
{
list = list.Where(t => t.Name.Contains(parm.key.Trim()));
}
if (!string.IsNullOrEmpty(parm.tel))
{
list = list.Where(t => t.Mobile.Contains(parm.tel.Trim()) || t.Tel.Contains(parm.tel.Trim()));
}
PagedList <sys_user> retlist = list.OrderByDescending(t => t.Id).ToPagedList(parm.pageindex, parm.pagesize);
Result_Count = retlist.TotalItemCount;
return(retlist.ToList());
}
}
catch (Exception)
{
throw;
}
}
public IHttpActionResult userlist([FromBody] UserQueryParm parm)
{
try
{
int cnt = 0;
UserService us = new UserService();
var list = us.Get_User_List(parm, out cnt);
return(Json(new { state = 1, msg = "ok", resultcount = cnt, list = list }));
}
catch (Exception e)
{
return(Json(new { state = 0, msg = e.Message, resultcount = 0, list = new List <sys_user>() }));
}
}
public IHttpActionResult List(UserQueryParm q)
{
try
{
int resultcount = 0;
UserService us = new UserService();
var list = us.Search(q, out resultcount);
return(Json(new { code = 1, msg = "ok", list = list.ToList(), resultcount = resultcount }));
}
catch (Exception e)
{
return(Json(new { code = 0, msg = e.Message, resultcount = 0 }));
}
}