public string QuerySysUser(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = from a in db.SysUser
join b in db.SysRole on a.RoleId equals b.Id
select new
{
a.Id,
a.Nickname,
a.RoleId,
a.Sign,
a.Status,
a.UserGroup,
a.UserName,
a.UserPwd,
a.CreateTime,
OldUserPwd = a.UserPwd,
RoleName = b.Name
};
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
/// <summary>
/// 查询拼接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="param"></param>
/// <param name="ru"></param>
/// <param name="or"></param>
public static void QueryJoin <T>(IQueryable <T> query, QueryDataVM.GetParams param, ContextBase db, ref QueryDataVM.OutputResult or)
{
//条件
query = QueryWhere(query, param);
//总条数
or.total = query.Count();
//排序
if (!string.IsNullOrWhiteSpace(param.sort))
{
query = Fast.QueryableTo.OrderBy(query, param.sort, param.order);
}
//分页
if (param.pagination == 1)
{
query = query.Skip((param.page - 1) * param.rows).Take(param.rows);
}
//数据
or.data = query.ToList();
//列
if (param.columnsExists != 1)
{
or.columns = db.SysTableConfig.Where(x => x.TableName == param.tableName).OrderBy(x => x.ColOrder).ToList();
}
}
public string QuerySysRole(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysRole;
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
public string QueryConfigTable(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysTableConfig.Where(x => x.ColHide != 2);
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
public string QuerySysTableConfig(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysTableConfig;
//query = query.Where(x => EF.Functions.Like(x.TableName, "%log%"));
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public string QueryConfigTable(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var ru = new RepositoryUse())
{
var query = from a in ru.Context.Set <SysTableConfig>()
select a;
Func.Common.QueryJoin(query, param, ru, ref or);
}
return(or.ToJson());
}
public string QueryData(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
//根据标识检索是否存在对应的数据表，（表字典DicTable 可过滤掉不让查询的表，也可以自定义DicTable表字典）
//前端直传表名查询会有安全问题，所以通过uri标识在后台做对应且可过滤
if (Func.Common.DicTable.ContainsKey(param.uri))
{
//表名
param.tableName = Func.Common.DicTable[param.uri];
//条件
if (!string.IsNullOrWhiteSpace(param.wheres))
{
var jwhere = JObject.Parse(param.wheres) as JToken;
param.wheres = QueryDataVM.SqlQueryWhere(jwhere);
}
if (string.IsNullOrWhiteSpace(param.wheres))
{
param.wheres = "1=1";
}
//排序
param.sortOrderJoin = DataBase.OrderByJoin(param.sort, param.order);
//查询：列、数据表、总条数
if (param.columnsExists != 1)
{
or.columns = Func.Common.QuerySysTableConfigList(x => x.TableName == param.tableName);
}
var ds = Func.Common.QueryDS(param);
or.data = ds.Tables[0];
if (param.pagination == 1)
{
or.total = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
}
else
{
or.total = ds.Tables[0].Rows.Count;
}
}
return(or.ToJson());
}
public string QuerySysButton(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var list = db.SysButton.OrderBy(x => x.BtnOrder).ToList();
var tree = Func.Common.ListToTree(list, "Pid", "Id", new List <string> {
Guid.Empty.ToString()
});
or.data = tree.ToJArray();
//列
if (param.columnsExists != 1)
{
or.columns = db.SysTableConfig.Where(x => x.TableName == param.tableName).OrderBy(x => x.ColOrder).ToList();
}
}
return(or.ToJson());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public string QuerySysUser(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
#region 自定义SQL查询主体
//查询主体，select 开头 ，QueryJoin里面会处理分页排序及查询条件
string sql = "select a.*,UserPwd as OldUserPwd,b.Name as RoleName from SysUser a left join SysRole b on a.RoleID=b.ID";
//查询总条数，可选，不传，则以查询主体sql查询总条数，此处如果用主体SQL多了不必要的关联查询
string sqlcount = "select count(1) from SysUser";
Func.Common.QueryJoin(sql, param, ref or, sqlcount);
#endregion
#region linq查询，Like等SQL查询条件不支持
//using (var ru = new RepositoryUse())
//{
// var query = from a in ru.Context.Set<SysUser>()
// join b in ru.Context.Set<SysRole>() on a.RoleID equals b.ID
// select new
// {
// a.ID,
// a.RoleID,
// a.UserName,
// a.UserPwd,
// OldUserPwd = a.UserPwd,
// a.Nickname,
// a.CreateTime,
// a.Status,
// a.UserGroup,
// RoleName = b.Name
// };
// Func.Common.QueryJoin(query, param, ru, ref or);
//}
#endregion
return(or.ToJson());
}
/// <summary>
/// 查询条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="param"></param>
/// <returns></returns>
public static IQueryable <T> QueryWhere <T>(IQueryable <T> query, QueryDataVM.GetParams param)
{
//条件
if (!string.IsNullOrWhiteSpace(param.wheres))
{
var whereItems = JArray.Parse(param.wheres);
foreach (var item in whereItems)
{
//关系符
var relation = item["relation"].ToStringOrEmpty();
string rel = DicSqlRelation[relation];
//字段
var field = item["field"].ToStringOrEmpty();
//值
var value = item["value"];
//值引号
var vqm = "\"";
switch (relation)
{
case "Equal":
case "NotEqual":
case "LessThan":
case "GreaterThan":
case "LessThanOrEqual":
case "GreaterThanOrEqual":
{
string val = vqm + value.ToStringOrEmpty() + vqm;
string iwhere = string.Format(rel, field, val);
query = DynamicQueryableExtensions.Where(query, iwhere);
}
break;
case "Contains":
case "StartsWith":
case "EndsWith":
{
string iwhere = string.Format(rel, value.ToStringOrEmpty());
query = query.Where(x => EF.Functions.Like(x.GetType().GetProperty(field).GetValue(x, null).ToString(), iwhere));
}
break;
case "BetweenAnd":
if (value.Count() == 2)
{
var v1 = vqm + value[0].ToString() + vqm;
var v2 = vqm + value[1].ToString() + vqm;
var iwhere = string.Format(rel, field, v1, v2);
query = DynamicQueryableExtensions.Where(query, iwhere);
}
break;
case "In":
{
var list = new List <string>();
int len = value.Count();
for (int i = 0; i < len; i++)
{
list.Add(value[i].ToString());
}
query = query.Where(x => list.Contains(x.GetType().GetProperty(field).GetValue(x, null).ToString()));
}
break;
case "NotIn":
{
var list = new List <string>();
int len = value.Count();
for (int i = 0; i < len; i++)
{
list.Add(value[i].ToString());
}
query = query.Where(x => !list.Contains(x.GetType().GetProperty(field).GetValue(x, null).ToString()));
}
break;
}
}
}
return(query);
}
public void Export(QueryDataVM.GetParams param)
{
if (!string.IsNullOrWhiteSpace(param.uri))
{
//根据uri转换表名，不建议直接传表名（有些表涉密不允许导出）
string tablename = "";
var listtb = new List <string>()
{
"syslog",
"systableconfig",
"sysuser"
};
tablename = listtb.Where(x => x.ToLower() == param.uri).FirstOrDefault();
if (string.IsNullOrWhiteSpace(tablename))
{
return;
}
//导出文件名
string filename = "导出表格";
//生成excel的临时文件目录
string path = GlobalTo.WebRootPath + "/temp/";
//存放导出的数据
var dt = new DataTable();
//查询导出的数据
string sql = ExportSql(tablename);
#region 表配置处理（列排序、列改名、剔除不要的列）
using (var db = new ContextBase())
{
var configquery = from a in db.SysTableConfig
where a.TableName == param.tableName && a.ColHide == 0 && a.ColExport == 1
orderby(a.ColFrozen ?? 0) descending, a.ColOrder ascending
select new
{
a.ColField,
a.ColTitle
};
//配置信息
var configlist = configquery.ToList();
var listcol = configlist.Select(x => x.ColField.ToLower()).ToList();
using (var conn = db.Database.GetDbConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
dt.Load(cmd.ExecuteReader());
}
//清空表格主键（不允许移除主键列）
dt.PrimaryKey = null;
//需要删除的列
var listcolRemove = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (!listcol.Contains(dc.ColumnName.ToLower()))
{
listcolRemove.Add(dc.ColumnName);
}
}
//删除列
foreach (var item in listcolRemove)
{
dt.Columns.Remove(item);
}
//列排序、重命名
for (int i = 0; i < configlist.Count; i++)
{
var item = configlist[i];
if (dt.Columns.Contains(item.ColField))
{
var dc = dt.Columns[item.ColField];
//列排序
dc.SetOrdinal(i);
//重命名列
int index = 1;
string newcol = item.ColTitle;
while (dt.Columns.Contains(newcol))
{
newcol = item.ColTitle + index.ToString();
index++;
}
dc.ColumnName = newcol;
}
}
}
#endregion
#region 生成Excel并输出流
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filename += DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (Fast.NpoiTo.DataTableToExcel(dt, path + filename))
{
new Core.DownTo(Response).Stream(path, filename);
}
#endregion
}
}
/// <summary>
/// 公共导出
/// 默认查询所有数据 再根据表配置保留要导出的列
///
/// </summary>
public void Export(QueryDataVM.GetParams param)
{
if (Func.Common.DicTable.ContainsKey(param.uri))
{
//表名
param.tableName = Func.Common.DicTable[param.uri];
//导出文件名
string filename = "导出表格";
//生成excel的临时文件目录
string path = Startup.HostingEnvironment.WebRootPath + "\\temp\\";
//存放导出的数据
var dt = new DataTable();
//查询导出的数据
string sql = ExportSql(param.tableName.ToLower());
dt = SQLServerDB.GetDataTable(sql);
#region 表配置处理（列排序、列改名、剔除不要的列）
using (var ru = new RepositoryUse())
{
var configquery = from a in ru.Context.Set <Domain.SysTableConfig>()
where a.TableName == param.tableName && a.ColHide == 0 && a.ColExport == 1
orderby(a.ColFrozen == null ? 0 : a.ColFrozen) descending, a.ColOrder ascending
select new
{
a.ColField,
a.ColTitle
};
//配置信息
var configlist = configquery.ToList();
var listcol = configlist.Select(x => x.ColField.ToLower()).ToList();
//需要删除的列
var listcolRemove = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (!listcol.Contains(dc.ColumnName.ToLower()))
{
listcolRemove.Add(dc.ColumnName);
}
}
//删除列
foreach (var item in listcolRemove)
{
dt.Columns.Remove(item);
}
//列排序、重命名
for (int i = 0; i < configlist.Count; i++)
{
var item = configlist[i];
if (dt.Columns.Contains(item.ColField))
{
var dc = dt.Columns[item.ColField];
//列排序
dc.SetOrdinal(i);
//重命名列
int index = 1;
string newcol = item.ColTitle;
while (dt.Columns.Contains(newcol))
{
newcol = item.ColTitle + index.ToString();
index++;
}
dc.ColumnName = newcol;
}
}
}
#endregion
#region 生成Excel并输出流
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filename += DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (Core.NPOITo.DataTableToExcel(dt, path + filename))
{
new Core.DownTo(Response).Stream(path, filename);
}
#endregion
}
}
