/// <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 = "服务器异常，请联系管理员！" }));
}
}