/// <summary> /// 加载页面操作数据 /// </summary> /// <returns></returns> public JsonResult LoadUserData(Guid?UserID) { try { string sql = @"SELECT t1.*,t2.departmentName FROM [SysUser] t1 left join SysDepartment t2 on t1.departmentId=t2.departmentId where t1.UserID=@UserID"; DataTable dt = SqlHelper.Query(sql, new SqlParameter("@UserID", UserID)).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt).FirstOrDefault() })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 加载模块详情 /// </summary> /// <returns></returns> public JsonResult LoadModuleData(Guid?id) { try { string sql = @" select m.*,p.ModuleName as parentName from SysModule m left join SysModule p on m.parentId=p.ModuleID where m.ModuleID=@id"; DataTable dt = SqlHelper.Query(sql, new SqlParameter("@id", id)).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt).FirstOrDefault() })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
public JsonResult QueryExampleTree() { try { string sql = @"select * from GeneralExample"; DataTable dt = SqlHelper.Query(sql).Tables[0]; var result = QuerySuite.ToDictionary(dt, "parentID", "exampleId"); return(Json(new { Code = 0, Total = result.Count, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
//获取下拉联动,同步 public JsonResult QueryTreeTableData() { try { string sql = @"select * from SysDepartment order by DepartmentCode "; DataTable dt = SqlHelper.Query(sql).Tables[0]; var result = QuerySuite.ToDictionary(dt, "ParentID", "DepartmentID"); return(Json(new { Code = 0, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
public JsonResult QueryMeunsDataForSql(string id, string type) { try { string sql = type == "SysModule" ? @"select * from SysModule where ModuleId = '" + id + "'" : @"select * from SysModulePage where Id = '" + id + "'"; DataTable dt = SqlHelper.Query(sql).Tables[0]; var result = QuerySuite.ToDictionary(dt); return(Json(new { Code = 0, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
public ActionResult QueryData() { try { string strSQL = @"select t1.[id],t1.[relationID],t1.[name],t1.[url],t1.[type],t1.[suffix],t1.[path],t1.[names],t1.[status],t1.[size],t1.[createBy],t2.userName createByName,t1.[createTime] from SysAttachment t1 left join SysUser t2 on t1.CreateBy=t2.UserID where t1.RelationID=@RelationID order by t1.createTime asc"; DataSet ds = SqlHelper.Query(strSQL, new SqlParameter("@RelationID", Request.Form["RelationID"].FirstOrDefault())); return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(ds.Tables[0]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取角色用户 /// </summary> /// <param name="RoleID"></param> /// <param name="userName"></param> /// <returns></returns> public JsonResult QueryRoleUserData(Guid?RoleID, string userName) { try { int offset = Convert.ToInt32(Request.Form["offset"].FirstOrDefault()); int limit = Convert.ToInt32(Request.Form["limit"].FirstOrDefault()); string order = Request.Form["order"].FirstOrDefault(); if (string.IsNullOrEmpty(order)) { order = "asc"; } string ordername = Request.Form["ordername"].FirstOrDefault(); if (string.IsNullOrEmpty(ordername)) { ordername = "users.UserID"; } string sql = string.Format(@"select ROW_NUMBER()OVER (order by {0} {1}) RowNumber, users.userName,users.mobile,users.post,dep.departmentFullName from SysRoleUser roles join SysUser users on roles.UserID=users.UserID left join SysDepartment dep on users.departmentId=dep.departmentId where roles.roleId=@RoleID ", ordername, order); List <SqlParameter> list = new List <SqlParameter>();//添加查询条件 list.Add(new SqlParameter("@RoleID", RoleID)); if (!string.IsNullOrEmpty(userName)) { sql += " and users.userName like @userName "; list.Add(new SqlParameter("@userName", string.Format("%{0}%", userName))); } string strSql = string.Format(@"select count(1) from ({0})t where 1=1 ; select * from ({0})t where RowNumber between {1} and {2} ", sql, offset + 1, offset + limit); SqlParameter[] param = list.ToArray(); DataSet ds = SqlHelper.Query(strSql, param); var result = QuerySuite.ToDictionary(ds.Tables[1]); var totals = ds.Tables[0].Rows[0][0]; return(Json(new { Code = 0, Total = totals, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取项目信息数据 /// </summary> /// <returns></returns> public JsonResult QueryTaskData() { try { QuerySuite querySuite = new QuerySuite(this, "CreateTime desc"); querySuite.Select("select * from PmTask"); querySuite.AddParam("TaskName", "like"); DataSet ds = SqlHelper.Query(querySuite.QuerySql, querySuite.Params); return(Json(new { Code = 0, Total = ds.Tables[0].Rows[0][0], Data = QuerySuite.ToDictionary(ds.Tables[1]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
//加载 public JsonResult LoadExample(Guid?exampleId) { try { string sql = @"select e.*,s.userName,p.departmentName from GeneralExample e left join SysUser s on e.ExampleUser=s.UserID left join SysDepartment p on e.Department=p.DepartmentID where e.exampleId=@exampleId"; DataTable dt = SqlHelper.Query(sql, new SqlParameter("@exampleId", exampleId)).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt).FirstOrDefault() })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取已选择的部门信息 /// </summary> /// <param name="DepID"></param> /// <returns></returns> public JsonResult GetSelectDepData(string DepID) { try { if (!string.IsNullOrEmpty(DepID)) { string sql = string.Format("select departmentId, parentId, departmentName, departmentFullName from SysDepartment where departmentId in ('{0}')", DepID.Replace(",", "','")); DataTable dt = SqlHelper.Query(sql).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt) })); } return(Json(new { Code = 0, Data = "" })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取页面操作数据 /// </summary> /// <returns></returns> public JsonResult QueryUserData(Guid?parentId) { try { QuerySuite querySuite = new QuerySuite(this, "orderNo asc"); querySuite.Select("select * from SysUser"); querySuite.AddParam(" and departmentId=@departmentId", new SqlParameter("departmentId", parentId)); querySuite.AddParam("userName", "like"); DataSet ds = SqlHelper.Query(querySuite.QuerySql, querySuite.Params); return(Json(new { Code = 0, Total = ds.Tables[0].Rows[0][0], Data = QuerySuite.ToDictionary(ds.Tables[1]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取已选择的用户 /// </summary> /// <param name="userID"></param> /// <returns></returns> public JsonResult GetSelectUserData(string userID) { try { if (!string.IsNullOrEmpty(userID)) { string sql = string.Format("select userName,userID from SysUser where UserID in ('{0}')", userID.Replace(",", "','")); DataTable dt = SqlHelper.Query(sql).Tables[0]; var json = QuerySuite.ToDictionary(dt); return(Json(new { Code = 0, Data = json })); } return(Json(new { Code = 0, Data = "" })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取菜单数据 /// </summary> /// <returns></returns> public JsonResult QueryMeunsData() { try { string sql = @"select * from (SELECT moduleId AS id, parentId, moduleName AS name,'' as url,moduleIcon as icon,'' as pageSign,0 AS [type],orderNo FROM SysModule module UNION SELECT id, ModuleID AS parentId, PageName AS Name,PageUrl as Url, PageIcon as Icon,PageSign, 1 AS [Type],OrderNo FROM SysModulePage) t order by OrderNo"; DataTable dt = SqlHelper.Query(sql).Tables[0]; var result = QuerySuite.ToDictionary(dt, "parentId", "id"); return(Json(new { Code = 0, Total = result.Count, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 加载页面详情 /// </summary> /// <returns></returns> public JsonResult LoadMeunsData(Guid?id) { try { if (!id.HasValue) { return(Json(new { Code = 0, Msg = "" })); } string sql = @" select page.*,module.moduleName from SysModulePage page left join SysModule module on page.ModuleID=module.ModuleID where page.id=@id"; DataTable dt = SqlHelper.Query(sql, new SqlParameter("@id", id)).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt).FirstOrDefault() })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取角色下的用户 /// </summary> /// <param name="RoleID"></param> /// <returns></returns> public JsonResult LoadRoleUserEdit(string RoleID) { try { if (string.IsNullOrEmpty(RoleID)) { return(Json(new { Code = 0, Data = "" })); } string sql = "select userId from SysRoleUser where roleId=@roleId"; SqlParameter[] param = { new SqlParameter("@roleId", RoleID) }; DataTable dt = SqlHelper.Query(sql, param).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
public ActionResult QueryCompleteExample() { try { QuerySuite querySuite = new QuerySuite(this, "exampleName desc"); querySuite.Select("select a.*,b.userName,c.departmentName from GeneralExample a left join SysUser b on a.UserPicker=b.UserID left join SysDepartment c on a.OUPicker=c.DepartmentID"); querySuite.AddParam("ExampleName", "like"); querySuite.AddParam("PlainText", "="); querySuite.AddParam("DateTimePicker", ">="); DataSet ds = SqlHelper.Query(querySuite.QuerySql, querySuite.Params); return(Json(new { Code = 0, Total = ds.Tables[0].Rows[0][0], Data = QuerySuite.ToDictionary(ds.Tables[1]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 根据父级id获取 部门数据 /// </summary> /// <returns></returns> public JsonResult QueryDepartmentData() { try { QuerySuite querySuite = new QuerySuite(this, "createTime desc"); querySuite.Select("select departmentId,departmentCode,departmentName,departmentFullName,createTime from SysDepartment"); querySuite.AddParam("parentId", "="); querySuite.AddParam("departmentName", "like"); querySuite.AddParam("departmentCode", "like"); DataSet ds = SqlHelper.Query(querySuite.QuerySql, querySuite.Params); return(Json(new { Code = 0, Total = ds.Tables[0].Rows[0][0], Data = QuerySuite.ToDictionary(ds.Tables[1]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取页面的操作按钮 /// </summary> /// <returns></returns> public JsonResult GetPageOperationData(Guid?id) { try { if (!id.HasValue) { return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } string sql = @"select SysOperation.operationSign,SysOperation.operationName,SysPageOperation.PageID from SysOperation left join SysPageOperation on SysOperation.operationSign=SysPageOperation.operationSign and SysPageOperation.PageID=@PageID order by SysOperation.OrderNo asc"; SqlParameter[] param = { new SqlParameter("@PageID", id) }; DataTable dt = SqlHelper.Query(sql, param).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取组织架构 /// </summary> /// <returns></returns> public JsonResult GetDepData(string userType, string departmentName) { try { List <SqlParameter> list = new List <SqlParameter>(); //添加查询条件 string sql = "select departmentId, parentId, departmentName, departmentFullName from SysDepartment where 1=1 "; if (!string.IsNullOrEmpty(departmentName)) { sql += " and departmentName like @departmentName "; list.Add(new SqlParameter("@departmentName", string.Format("%{0}%", departmentName))); } sql += " order by departmentName desc "; DataTable dt = SqlHelper.Query(sql, list.ToArray()).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt, "parentId", "departmentId") })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取日志数据 /// </summary> /// <returns></returns> public JsonResult GetLogData() { try { QuerySuite querySuite = new QuerySuite(this, "createTime desc"); querySuite.Select("select * from SysLog"); querySuite.AddParam("userName", "like"); querySuite.AddParam("departmentName", "like"); querySuite.AddParam("title", "like"); querySuite.AddParam("type", "like"); querySuite.AddParam("description", "like"); DataSet ds = SqlHelper.Query(querySuite.QuerySql, querySuite.Params); return(Json(new { Code = 0, Total = ds.Tables[0].Rows[0][0], Data = QuerySuite.ToDictionary(ds.Tables[1]) })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
public ActionResult LoadCompleteExample(Guid?ExampleID) { try { if (!ExampleID.HasValue) { return(Json(new { Code = 0, Msg = "" })); } string sql = @"select a.*,b.userName,c.departmentName from GeneralExample a left join SysUser b on a.UserPicker=b.UserID left join SysDepartment c on a.OUPicker=c.DepartmentID where a.ExampleID=@ExampleID"; DataTable dt = SqlHelper.Query(sql, new SqlParameter("@ExampleID", ExampleID)).Tables[0]; return(Json(new { Code = 0, Data = QuerySuite.ToDictionary(dt).FirstOrDefault() })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 获取菜单数据 /// </summary> /// <returns></returns> public JsonResult GetRoleMeunsData(Guid?roleId) { try { string sql = @"select * from ( SELECT moduleId AS id, parentId, moduleName AS name,0 AS [type],orderNo,'' as operationSign,'' as pageOperation FROM SysModule module UNION SELECT page.id, page.ModuleID AS parentId, page.PageName AS Name, 1 AS [Type],page.OrderNo,r.operationSign, STUFF((SELECT ','+ o.operationSign+'_'+o.operationName FROM SysPageOperation p join SysOperation o on p.operationSign=o.operationSign where PageID=page.id order by o.OrderNo asc FOR XML PATH('')), 1 ,1, '') as PageOperation FROM SysModulePage page left join SysRoleOperatePower r on page.id=r.ModulePageID and r.roleId=@roleId ) t order by OrderNo"; SqlParameter[] param = { new SqlParameter("@roleId", roleId) }; DataTable dt = SqlHelper.Query(sql, param).Tables[0]; var result = QuerySuite.ToDictionary(dt, "parentId", "id"); return(Json(new { Code = 0, Total = result.Count, Data = result })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }
/// <summary> /// 加载页面操作数据 /// </summary> /// <returns></returns> public JsonResult Load(string module, string page, string key) { try { string file = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "prototyping", module + ".xlsx"); DataTable dt = ExcelHelper.ExcelToDataTable(file, page); dt.Columns.Add("module"); dt.Columns.Add("page"); dt.Columns.Add("key"); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; row["module"] = module; row["page"] = page; row["key"] = Convert.ToString(row[0]) + i; } return(Json(new { Code = 0, Total = dt.Rows.Count, Data = QuerySuite.ToDictionary(dt, "key='" + key + "'")[0] })); } catch (Exception ex) { LogHelper.SaveLog(ex); return(Json(new { Code = 1, Msg = "服务器异常,请联系管理员!" })); } }