/// <summary>
/// 获取项目信息数据
/// </summary>
/// <returns></returns>
public JsonResult QueryProjectData()
{
try
{
QuerySuite querySuite = new QuerySuite(this, "CreateTime desc");
querySuite.Select("select * from PmProject");
querySuite.AddParam("ProjectName", "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 = "服务器异常，请联系管理员！" }));
}
}
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>
/// 获取角色下的用户
/// </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 = "服务器异常，请联系管理员！" }));
}
}
/// <summary>
/// 获取数据字典数据
/// </summary>
/// <returns></returns>
public JsonResult QueryDictionary()
{
try
{
QuerySuite querySuite = new QuerySuite(this, "orderNo asc");
querySuite.Select("select * from SysDictionary");
querySuite.AddParam("Type", "like");
querySuite.AddParam("Member", "like");
querySuite.AddParam("MemberName", "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 FileResult ExportCompleteExample()
{
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.ExportSql, querySuite.Params);
return(null);
}
catch (Exception ex)
{
LogHelper.SaveLog(ex);
return(null);
}
}
/// <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>
/// 根据父级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 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 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 DeleteUserData(string ids)
{
try
{
var result = SqlHelper.ExecuteSql(QuerySuite.DeleteSql(ids, "SysUser", "userID"));
//删除人脸特征
foreach (string id in ids.Split(','))
{
var item = dbContext.SysUser.FirstOrDefault(x => x.UserId == new Guid(id));
if (item != null)
{
FaceCompareBLL.DeleteUserFace(item);
}
}
return(Json(new { Code = 0, Msg = "删除成功" }));
}
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 = "服务器异常，请联系管理员！" }));
}
}
public JsonResult One(string tablename)
{
try
{
string where = string.IsNullOrEmpty(tablename) ? "" : "where d.name='" + tablename + "'";
string strSql = string.Format(@"SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,d.name) else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
非空 = case when a.isnullable=1 then ''else '√' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],a.name)
FROM syscolumns a left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
{0}
order by d.name,a.colorder", where);
SqlDataAdapter sda = new SqlDataAdapter(strSql, SqlHelper.ConnectionString);
DataTable dt = new DataTable();
sda.Fill(dt);
string[] extable = new string[] { "Example", "FlowBills", "FlowBillsRecord", "FlowBillsRecordUser", "FlowMain", "FlowOrderCodes", "FlowStep", "FlowStepPath", "GeneralExample",
"SysAttachment", "SysDictionary", "SysLog", "SysModule", "SysModulePage", "SysOperation", "SysPageOperation", "SysRole", "SysRoleOperatePower", "SysRoleUser",
"SysServer", "SysUserClientId", "SysUserOpenId" };
List <Table> tables = new List <Table>();
Table table = new Table();
foreach (DataRow row in dt.Rows)
{
if (Convert.ToString(row["字段序号"]) == "1" && extable.Count(x => x == Convert.ToString(row["表名"])) == 0)
{
table = new Table
{
Name = Convert.ToString(row["表名"]),
Remark = Convert.ToString(row["表说明"]),
Columns = new List <Column>()
};
tables.Add(table);
}
if (string.IsNullOrEmpty(tablename))
{
continue;
}
table.Columns.Add(new Column
{
Order = Convert.ToString(row["字段序号"]),
Label = Convert.ToString(row["字段说明"]),
Name = Convert.ToString(row["字段名"]),
CodeName = QuerySuite.FormatKey(Convert.ToString(row["字段名"])),
IsKey = Convert.ToString(row["主键"]),
Length = Convert.ToString(row["长度"]),
Type = Convert.ToString(row["类型"]),
Scale = Convert.ToString(row["小数位数"]),
Value = Convert.ToString(row["默认值"]),
IsNull = Convert.ToString(row["非空"])
});
}
return(Json(new { Code = 0, Data = tables }));
}
catch (Exception ex)
{
LogHelper.SaveLog(ex);
return(Json(new { Code = 1, Msg = "服务器异常，请联系管理员！" }));
}
}
