/// <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 (ovm.total <= 0)
{
return;
}
//排序
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 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);
}
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();
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;
}
/// <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 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);
}
/// <summary>
/// 查询条件（IQueryable）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="ivm"></param>
/// <returns></returns>
public static IQueryable <T> QueryWhere <T>(IQueryable <T> query, QueryDataInputVM ivm)
{
//条件
if (!string.IsNullOrWhiteSpace(ivm.wheres))
{
var whereItems = JArray.Parse(ivm.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":
{
query = DynamicQueryableExtensions.Where(query, field + "." + relation + "(@0)", value.ToStringOrEmpty());
}
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;
}
}
}
return(query);
}
/// <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);
}
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);
}
public QueryDataOutputVM QuerySysDictionary(QueryDataInputVM ivm)
{
var ovm = new QueryDataOutputVM();
var query = db.SysDictionary;
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();
var query = db.TempExample;
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);
}
/// <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 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);
}
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 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);
}
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 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);
}
/// <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);
}
/// <summary>
/// 查询条件（IEnumerable,仅支持部分）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="ivm"></param>
/// <returns></returns>
public static IEnumerable <T> QueryWhere <T>(IEnumerable <T> query, QueryDataInputVM ivm)
{
//条件
if (!string.IsNullOrWhiteSpace(ivm.wheres))
{
var whereItems = JArray.Parse(ivm.wheres);
foreach (var item in whereItems)
{
//关系符
var relation = item["relation"].ToStringOrEmpty();
string rel = DicSqlRelation[relation];
//字段
var field = item["field"].ToStringOrEmpty();
//值
var value = item["value"].ToString().ToLower();
switch (relation)
{
case "Equal":
query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower() == value);
break;
case "NotEqual":
query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower() != value);
break;
case "Contains":
query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().Contains(value));
break;
case "StartsWith":
query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().StartsWith(value));
break;
case "EndsWith":
query = query.Where(x => x.GetType().GetProperty(field).GetValue(x, null).ToString().ToLower().EndsWith(value));
break;
}
}
}
return(query);
}
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="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 = 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);
}
//查询SQL
ovm.QuerySql = query.ToQueryString();
//数据
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();
}
}
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);
}
