public QueryDataOutputVM QuerySysUser(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = from a in db.SysUser
join b in db.SysRole on a.SrId equals b.SrId
select new
{
a.SuId,
a.SuNickname,
a.SrId,
a.SuSign,
a.SuStatus,
a.SuGroup,
a.SuName,
a.SuPwd,
a.SuCreateTime,
OldUserPwd = a.SuPwd,
b.SrName
};
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// 查询拼接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="ivm"></param>
/// <param name="ovm"></param>
public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ref QueryDataOutputVM ovm)
{
//总条数
ovm.Total = query.Count();
//排序
if (!string.IsNullOrWhiteSpace(ivm.Sort))
{
query = QueryableTo.OrderBy(query, ivm.Sort, ivm.Order);
}
//分页
if (ivm.Pagination == 1)
{
query = query.Skip((ivm.Page - 1) * ivm.Rows).Take(ivm.Rows);
}
var sql = query.ToQueryString();
Console.WriteLine(sql);
//数据
var data = query.ToList();
ovm.Data = data;
}
public QueryDataOutputVM QueryNoteList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var uinfo = Apps.LoginService.Get(HttpContext);
var query = from a in db.Notepad
join b in db.UserInfo on a.Uid equals b.UserId
orderby a.NoteCreateTime descending
where a.Uid == uinfo.UserId
select new Domain.Notepad
{
NoteId = a.NoteId,
NoteTitle = a.NoteTitle,
NoteCreateTime = a.NoteCreateTime,
NoteUpdateTime = a.NoteUpdateTime,
Uid = a.Uid,
Spare3 = b.Nickname
};
if (!string.IsNullOrWhiteSpace(ivm.Pe1))
{
query = query.Where(x => x.NoteTitle.Contains(ivm.Pe1));
}
Application.CommonService.QueryJoin(query, ivm, ref ovm);
return(ovm);
}
/// <summary>
/// 查询文章列表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryWriteList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = from a in db.UserWriting
join b in db.UserInfo on a.Uid equals b.UserId
select new
{
a.UwId,
a.UwTitle,
a.UwCreateTime,
a.UwUpdateTime,
a.UwReadNum,
a.UwReplyNum,
a.UwOpen,
a.UwStatus,
a.UwLaud,
a.UwMark,
a.UwCategory,
b.UserId,
b.Nickname,
b.UserName,
b.UserMail
};
if (!string.IsNullOrWhiteSpace(ivm.Pe1))
{
query = query.Where(x => x.UwTitle.Contains(ivm.Pe1));
}
Application.CommonService.QueryJoin(query, ivm, ref ovm);
return(ovm);
}
public QueryDataOutputVM QueryNoteList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var uinfo = new Func.UserAuthAid(HttpContext).Get();
using (var db = new ContextBase())
{
var query = from a in db.Notepad
join b in db.UserInfo on a.Uid equals b.UserId
orderby a.NoteCreateTime descending
where a.Uid == uinfo.UserId
select new Notepad
{
NoteId = a.NoteId,
NoteTitle = a.NoteTitle,
NoteCreateTime = a.NoteCreateTime,
NoteUpdateTime = a.NoteUpdateTime,
Uid = a.Uid,
Spare3 = b.Nickname
};
if (!string.IsNullOrWhiteSpace(ivm.pe1))
{
query = query.Where(x => x.NoteTitle.Contains(ivm.pe1));
}
Func.Common.QueryJoin(query, ivm, ref ovm);
}
return(ovm);
}
public QueryDataOutputVM QuerySysUser(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = from a in db.SysUser
join b in db.SysRole on a.SrId equals b.SrId
select new
{
a.SuId,
a.SuNickname,
a.SrId,
a.SuSign,
a.SuStatus,
a.SuGroup,
a.SuName,
a.SuPwd,
a.SuCreateTime,
OldUserPwd = a.SuPwd,
b.SrName
};
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
public QueryDataOutputVM QueryConfigTable(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysTableConfig.Where(x => x.ColHide != 2);
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// Grid多表格-子表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryGridChange2(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysUser;
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// 查询表配置示例
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryTempExample(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.TempExample;
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// 查询表配置
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QuerySysTableConfig(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysTableConfig;
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// 查询单据主表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryInvoiceMain(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.TempInvoiceMain;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
/// <summary>
/// Grid多表格-主表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryGridChange1(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysRole;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
public QueryDataOutputVM QuerySysDictionary(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysDictionary;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
public QueryDataOutputVM QueryConfigTable(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = db.SysTableConfig.Where(x => x.ColHide != 2);
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
/// <summary>
/// 查询表配置示例
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryTempExample(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = db.TempExample;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
/// <summary>
/// Grid多表格-子表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryGridChange2(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = db.SysUser;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
/// <summary>
/// 查询表配置
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QuerySysTableConfig(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = db.SysTableConfig;
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
public QueryDataOutputVM QueryTableConfig(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var queryHas = from a in db.SysTableConfig
group a by a.TableName into g
select g.Key;
var listHas = queryHas.ToList();
var dbname = db.Database.GetDbConnection().Database;
var sql = QueryScripts(ContextBase.TDB.ToString(), "name").Replace("@DataBaseName", dbname);
var listRow = new List <TreeNodeVM>();
if (!string.IsNullOrWhiteSpace(sql))
{
using var dt = new DataTable();
using (var conn = db.Database.GetDbConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
dt.Load(cmd.ExecuteReader());
}
foreach (DataRow dr in dt.Rows)
{
var key = dr[0].ToString();
var val = (listHas.Exists(x => x.ToLower() == key.ToLower()) ? "1" : "");
var row = new TreeNodeVM
{
id = key,
pid = val
};
listRow.Add(row);
}
}
var listFilter = Func.Common.QueryWhere(listRow, ivm);
ovm.data = listFilter;
ovm.total = listFilter.Count();
}
return(ovm);
}
/// <summary>
/// 查询回复列表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryReplyList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = from a in db.UserReply
join b1 in db.UserInfo on a.Uid equals b1.UserId into bg
from b in bg.DefaultIfEmpty()
select new
{
a.UrId,
a.Uid,
a.UrAnonymousName,
a.UrAnonymousLink,
a.UrAnonymousMail,
a.UrTargetType,
a.UrTargetId,
a.UrContent,
a.UrContentMd,
a.UrCreateTime,
a.UrStatus,
a.UrTargetPid,
a.Spare1,
a.Spare2,
a.Spare3,
UserId = b == null ? 0 : b.UserId,
Nickname = b == null ? null : b.Nickname,
UserName = b == null ? null : b.UserName,
UserMail = b == null ? null : b.UserMail
};
if (!string.IsNullOrWhiteSpace(ivm.Pe1))
{
query = GlobalTo.TDB switch
{
SharedEnum.TypeDB.SQLite => query.Where(x => EF.Functions.Like(x.UrContent, $"%{ivm.Pe1}%")),
SharedEnum.TypeDB.PostgreSQL => query.Where(x => EF.Functions.ILike(x.UrContent, $"%{ivm.Pe1}%")),
_ => query.Where(x => x.UrContent.Contains(ivm.Pe1)),
};
}
Application.CommonService.QueryJoin(query, ivm, ref ovm);
return(ovm);
}
/// <summary>
/// 查询单据明细表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryInvoiceDetail(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = from a in db.TempInvoiceDetail select a;
if (string.IsNullOrWhiteSpace(ivm.Pe1))
{
query = query.Where(x => false);
}
else
{
query = query.Where(x => x.TimId == ivm.Pe1);
}
Application.CommonService.QueryJoin(query, ivm, db, ref ovm);
return(ovm);
}
public QueryDataOutputVM QuerySysButton(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var list = db.SysButton.OrderBy(x => x.SbBtnOrder).ToList();
var tree = Core.TreeTo.ListToTree(list, "SbPid", "SbId", new List <string> {
Guid.Empty.ToString()
});
ovm.Data = tree.ToJArray();
//列
if (ivm.ColumnsExists != 1)
{
ovm.Columns = db.SysTableConfig.Where(x => x.TableName == ivm.TableName).OrderBy(x => x.ColOrder).ToList();
}
return(ovm);
}
/// <summary>
/// 查询回复列表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryReplyList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = from a in db.UserReply
join b1 in db.UserInfo on a.Uid equals b1.UserId into bg
from b in bg.DefaultIfEmpty()
select new
{
a.UrId,
a.Uid,
a.UrAnonymousName,
a.UrAnonymousLink,
a.UrAnonymousMail,
a.UrTargetType,
a.UrTargetId,
a.UrContent,
a.UrContentMd,
a.UrCreateTime,
a.UrStatus,
a.UrTargetPid,
a.Spare1,
a.Spare2,
a.Spare3,
b.UserId,
b.Nickname,
b.UserName,
b.UserMail
};
if (!string.IsNullOrWhiteSpace(ivm.pe1))
{
query = query.Where(x => x.UrContent.Contains(ivm.pe1));
}
Func.Common.QueryJoin(query, ivm, ref ovm);
}
return(ovm);
}
public QueryDataOutputVM QuerySysMenu(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var list = db.SysMenu.OrderBy(x => x.SmOrder).ToList();
var tree = Core.TreeTo.ListToTree(list, "SmPid", "SmId", new List <string> {
Guid.Empty.ToString()
});
ovm.data = tree.ToJArray();
//列
if (ivm.columnsExists != 1)
{
ovm.columns = db.SysTableConfig.Where(x => x.TableName == ivm.tableName).OrderBy(x => x.ColOrder).ToList();
}
}
return(ovm);
}
/// <summary>
/// 查询单据明细表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryInvoiceDetail(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
using (var db = new ContextBase())
{
var query = from a in db.TempInvoiceDetail select a;
if (string.IsNullOrWhiteSpace(ivm.pe1))
{
query = query.Where(x => false);
}
else
{
query = query.Where(x => x.TimId == ivm.pe1);
}
Func.Common.QueryJoin(query, ivm, db, ref ovm);
}
return(ovm);
}
/// <summary>
/// 查询文章列表
/// </summary>
/// <param name="ivm"></param>
/// <returns></returns>
public QueryDataOutputVM QueryWriteList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = from a in db.UserWriting
join b in db.UserInfo on a.Uid equals b.UserId
select new
{
a.UwId,
a.UwTitle,
a.UwCreateTime,
a.UwUpdateTime,
a.UwReadNum,
a.UwReplyNum,
a.UwOpen,
a.UwStatus,
a.UwLaud,
a.UwMark,
a.UwCategory,
b.UserId,
b.Nickname,
b.UserName,
b.UserMail
};
if (!string.IsNullOrWhiteSpace(ivm.Pe1))
{
query = GlobalTo.TDB switch
{
SharedEnum.TypeDB.SQLite => query.Where(x => EF.Functions.Like(x.UwTitle, $"%{ivm.Pe1}%")),
SharedEnum.TypeDB.PostgreSQL => query.Where(x => EF.Functions.ILike(x.UwTitle, $"%{ivm.Pe1}%")),
_ => query.Where(x => x.UwTitle.Contains(ivm.Pe1)),
};
}
Application.CommonService.QueryJoin(query, ivm, ref ovm);
return(ovm);
}
/// <summary>
/// 查询拼接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="ivm"></param>
/// <param name="ru"></param>
/// <param name="ovm"></param>
public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ref QueryDataOutputVM ovm)
{
//总条数
ovm.total = query.Count();
//排序
if (!string.IsNullOrWhiteSpace(ivm.sort))
{
query = Fast.QueryableTo.OrderBy(query, ivm.sort, ivm.order);
}
//分页
if (ivm.pagination == 1)
{
query = query.Skip((ivm.page - 1) * ivm.rows).Take(ivm.rows);
}
//数据
var data = query.ToList();
ovm.data = data;
}
public QueryDataOutputVM QueryTagList(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var uinfo = new Func.UserAuthAid(HttpContext).Get();
var query = from a in _context.Tags
orderby a.TagId descending
where string.IsNullOrEmpty(ivm.pe1) ? true : a.TagName.Contains(ivm.pe1)
select new Tags
{
TagId = a.TagId,
TagName = a.TagName,
TagOwner = a.TagOwner,
TagStatus = a.TagStatus,
createTime = a.createTime,
updateTime = a.updateTime,
};
Func.Common.QueryJoin(query, ivm, ref ovm);
return(ovm);
}
public QueryDataOutputVM QueryTableInfo(string names)
{
var listName = names.Split(',').ToList();
var innames = string.Join("','", listName);
var ovm = new QueryDataOutputVM();
var dt = new DataTable();
using (var db = new ContextBase())
{
var dbname = db.Database.GetDbConnection().Database;
if (ContextBase.TDB == ContextBase.TypeDB.SQLite)
{
//补齐列
dt.Columns.Add(new DataColumn("表名"));
dt.Columns.Add(new DataColumn("表说明"));
using var conn = db.Database.GetDbConnection();
conn.Open();
//遍历表查询
foreach (var tname in listName)
{
var sql = QueryScripts(ContextBase.TDB.ToString(), "info").Replace("@TableName", tname);
if (!string.IsNullOrWhiteSpace(sql))
{
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
var newdr = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
var field = reader.GetName(i);
if (!dt.Columns.Contains(field))
{
dt.Columns.Add(new DataColumn(field));
}
newdr[field] = reader[field].ToString();
switch (field)
{
case "notnull":
newdr[field] = newdr[field].ToString() == "1" ? "YES" : "";
break;
case "pk":
newdr[field] = newdr[field].ToString() == "0" ? "" : "YES (" + newdr[field].ToString() + ")";
break;
}
}
newdr["表名"] = tname;
newdr["表说明"] = "";
dt.Rows.Add(newdr.ItemArray);
}
}
}
//更改列名
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName == "name")
{
dc.ColumnName = "字段名";
}
if (dc.ColumnName == "type")
{
dc.ColumnName = "类型";
}
if (dc.ColumnName == "notnull")
{
dc.ColumnName = "不为空";
}
if (dc.ColumnName == "dflt_value")
{
dc.ColumnName = "默认值";
}
if (dc.ColumnName == "pk")
{
dc.ColumnName = "主键";
}
}
}
else
{
var sql = QueryScripts(ContextBase.TDB.ToString(), "info").Replace("@DataBaseName", dbname).Replace("@TableName", innames);
if (!string.IsNullOrWhiteSpace(sql))
{
using var conn = db.Database.GetDbConnection();
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
dt.Load(cmd.ExecuteReader());
}
}
ovm.data = dt;
ovm.total = dt.Rows.Count;
}
#region 其它处理
switch (ContextBase.TDB)
{
//mysql默认值，单独查询
case ContextBase.TypeDB.MySQL:
using (var db = new ContextBase())
{
var conn = db.Database.GetDbConnection();
conn.Open();
var cmd = conn.CreateCommand();
foreach (var name in listName)
{
cmd.CommandText = "desc " + name + ";";
var dtdefault = new DataTable();
dtdefault.Load(cmd.ExecuteReader());
foreach (DataRow dr in dt.Rows)
{
if (dr["表名"].ToString().ToLower() == name.ToLower())
{
var dv = dtdefault.Select("Field='" + dr["字段名"].ToString() + "'")[0]["Default"];
if (dv != DBNull.Value)
{
dr["默认值"] = dv;
}
}
}
}
cmd.Dispose();
conn.Dispose();
}
break;
}
#endregion
return(ovm);
}
/// <summary>
/// 查询拼接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="ivm"></param>
/// <param name="db"></param>
/// <param name="ovm"></param>
public static void QueryJoin <T>(IQueryable <T> query, QueryDataInputVM ivm, ContextBase db, ref QueryDataOutputVM ovm)
{
//条件
query = QueryWhere(query, ivm);
//总条数
ovm.total = query.Count();
//排序
if (!string.IsNullOrWhiteSpace(ivm.sort))
{
query = Fast.QueryableTo.OrderBy(query, ivm.sort, ivm.order);
}
//分页
if (ivm.pagination == 1)
{
query = query.Skip((Math.Max(ivm.page, 1) - 1) * ivm.rows).Take(ivm.rows);
}
//数据
var data = query.ToList();
ovm.data = data;
//导出时，存储数据表格
if (ivm.handleType == "export")
{
ovm.table = ToDataTableForString(data);
}
//列
if (ivm.columnsExists != 1)
{
ovm.columns = db.SysTableConfig.Where(x => x.TableName == ivm.tableName).OrderBy(x => x.ColOrder).ToList();
}
}
/// <summary>
/// 数据实体映射
/// </summary>
public static DataTable ModelsMapping(QueryDataInputVM ivm, QueryDataOutputVM ovm)
{
//转表（类型为字符串）
DataTable dt = ovm.table;
//更改列长度
foreach (DataColumn col in dt.Columns)
{
col.MaxLength = short.MaxValue * 9;
}
var listColumns = ovm.columns as List <SysTableConfig>;
//调整列排序
var colorder = listColumns.Where(x => dt.Columns.Contains(x.ColField)).OrderBy(x => x.ColOrder).ToList();
for (int i = 0; i < colorder.Count; i++)
{
var ci = colorder[i];
if (dt.Columns.Contains(ci.ColField))
{
dt.Columns[ci.ColField].SetOrdinal(i);
}
}
#region 单元格转换
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
string field = dt.Columns[i].ColumnName;
dr[i] = CellFormat(ivm.tableName, field, dr[i].ToString(), dr);
}
}
#endregion
//剔除不导出的列
List <SysTableConfig> removeCol = listColumns.Where(x => x.ColExport != 1).ToList();
foreach (SysTableConfig col in removeCol)
{
if (dt.Columns.Contains(col.ColField))
{
dt.Columns.Remove(dt.Columns[col.ColField]);
}
}
//剔除没在表配置的列
List <string> removeColNotExists = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (listColumns.Where(x => x.ColField == dc.ColumnName).Count() == 0)
{
removeColNotExists.Add(dc.ColumnName);
}
}
foreach (string col in removeColNotExists)
{
dt.Columns.Remove(dt.Columns[col]);
}
//更改列名为中文（重复的列，后面追加4位随机数）
foreach (SysTableConfig col in listColumns)
{
if (dt.Columns.Contains(col.ColField))
{
try
{
dt.Columns[col.ColField].ColumnName = col.ColTitle;
}
catch (Exception)
{
dt.Columns[col.ColField].ColumnName = col.ColTitle + "-" + Core.RandomTo.NumCode();
}
}
}
return(dt);
}
