/// <summary>
/// 根据集合获取完整商品属性集合
/// </summary>
/// <returns></returns>
public List <GoodDto> GetGoodDto(GetGoodApo pageDataApo, out int totalCount)
{
totalCount = 0;
List <GoodDto> data;
//查询语句
var queryable = Db.Queryable <Goods>()
.Where(it => pageDataApo.goodsEpsDatas.Contains(it.code))
.WhereIF(!string.IsNullOrWhiteSpace(pageDataApo.name), it => it.name.Contains(pageDataApo.name))
.WhereIF(!string.IsNullOrWhiteSpace(pageDataApo.code), it => it.goods_code.Contains(pageDataApo.code))
.GroupBy(it => it.goods_code)
.OrderBy(it => it.expire_date, OrderByType.Asc)
.Select(it => new GoodDto
{
name = it.name,
goods_code = it.goods_code,
amount = SqlFunc.AggregateCount(it.id),
expire_time = SqlFunc.AggregateMin(it.expire_date),
position = it.position
});
//如果小于0，默认查全部
if (pageDataApo.PageSize > 0)
{
data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount);
}
else
{
data = queryable.ToList();
totalCount = data.Count();
}
return(data);
}
/// <summary>
/// 根据门店按等级获取客户汇总
/// </summary>
/// <param name="mctNum"></param>
/// <param name="storeIds"></param>
/// <param name="isActivation"></param>
/// <returns></returns>
public Dictionary <string, int> StatisticsList(string mctNum, string storeIds, bool isActivation = false)
{
using (SqlSugarClient db = MySqlHelper.GetInstance())
{
var query = db.Queryable <Cus_Customer, Cus_CustomerLevel>((s, sl) => new object[] { JoinType.Left, s.CusLevelId == sl.Id })
.Where((s, sl) => s.MctNum == mctNum)
.WhereIF(!string.IsNullOrEmpty(storeIds), (s, sl) => storeIds.Contains(s.StoreId))
.WhereIF(isActivation, (s, sl) => s.IsActivation == isActivation)
.GroupBy((s, sl) => new { Grade = sl.Grade })
.Select((s, sl) => new { Grade = sl.Grade, Count = SqlFunc.AggregateCount(1) });
;
var data = query.ToList();
var addCustomer = new Dictionary <string, int>();
if (data != null)
{
foreach (var item in data)
{
if (!string.IsNullOrEmpty(item.Grade))
{
addCustomer.Add(item.Grade, item.Count);
}
}
}
return(addCustomer);
}
}
public void HandleBookTag(List <EBookTag> list)
{
List <EBookTag> newList = new List <EBookTag>();
try
{
foreach (var tag in list)
{
var c = _BookTagDb.IsExist(
s => new CountResult {
Count = SqlFunc.AggregateCount(s.Id)
},
a => a.BookCode == tag.BookCode && a.TagCode == tag.TagCode);
//var c = _BookTagDb.IsExist(s => new { ct = SqlFunc.AggregateCount(s.Id) },
// a => a.BookCode == tag.BookCode && a.TagCode == tag.TagCode);
if (c == 0)
{
newList.Add(tag);
}
}
_BookTagDb.AddRange(newList);
}
catch (Exception ex)
{
throw ex;
}
}
private static void HandleSome()
{
var bookTagDb = _ServiceProvider.GetService <BookTagRepository>();
var list = bookTagDb.Db.Queryable <EBookTag>()
.GroupBy(b => b.BookCode)
.Having(b => SqlFunc.AggregateCount(b.BookCode) > 4)
.Select(b => new { c = SqlFunc.AggregateCount(b.BookCode), b.BookCode }).ToList();
int i = 0;
foreach (var bt in list)
{
var delList = bookTagDb.Db.Queryable <EBookTag>()
.Take(bt.c - 4)
.Where(b => b.BookCode == bt.BookCode)
.OrderBy(b => b.Id, OrderByType.Desc).ToList();
var pks = new List <int>();
foreach (var del in delList)
{
pks.Add(del.Id);
Console.WriteLine($"{bt.BookCode}-{del.TagCode}");
}
bookTagDb.Db.Deleteable <EBookTag>().In(pks.ToArray()).ExecuteCommand();
//i++;
//if (i > 20)
// break;
}
}
/// <summary>
/// 查询店铺销售排行榜
/// </summary>
/// <returns></returns>
public Task <ApiResult <List <ShopSaleTop> > > GetShopSaleTopReport()
{
var res = new ApiResult <List <ShopSaleTop> >()
{
statusCode = (int)ApiEnum.Error
};
try
{
var query = Db.Queryable <ErpSaleOrder, ErpShops>((eso, es) => new object[] {
JoinType.Left, eso.ShopGuid == es.Guid
})
.Select((eso, es) => new ShopSaleTop()
{
ShopName = es.ShopName,
Money = SqlFunc.AggregateSum(eso.RealMoney),
Counts = SqlFunc.AggregateCount(eso.Counts)
})
.PartitionBy("ShopName")
.OrderBy("Money desc")
.Take(7);
res.data = query.ToList();
var total = res.data.Sum(m => m.Money);
foreach (var item in res.data)
{
item.Ratio = Math.Round((item.Money / total * 100), 2);
}
res.statusCode = (int)ApiEnum.Status;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(Task.Run(() => res));
}
/// <summary>
/// 根据Section Code ，覆盖所有相关 DataSecion.
///
/// </summary>
public async Task CoverNewSectionCodeAsync(List <EDataSection> newList)
{
var rAll = await base.Db.Ado.UseTranAsync(() =>
{
foreach (var es in newList)
{
int n = base.IsExist(a => new CountResult {
Count = SqlFunc.AggregateCount(a.Id)
}, a => a.SectionCode == es.SectionCode && a.ItemCode == es.ItemCode);
if (n == 0)
{
base.Add(es);
}
else
{
int r = base.DelAll(a => a.SectionCode == es.SectionCode && a.ItemCode == es.ItemCode && a.CreateDateTime.AddDays(30) < DateTime.Today);
if (r > 0)
{
base.Add(es);
}
}
}
// base.AddRange(newList);
});
if (!rAll.IsSuccess)
{
NLogUtil.ErrorTxt($"[CoverNewSectionCode]建立Book和 Section 关系:{rAll.ErrorMessage}");
}
}
public Task <List <RTag> > GetTagList(int number = 0, OrderByType orderByType = OrderByType.Desc)
{
var q = Db.Queryable <ETag, EBookTag>((tag, bt) => new object[] {
JoinType.Inner, tag.Code == bt.TagCode
})
.GroupBy((tag, bt) => new
{
tag.Code,
tag.Name
})
.Select((tag, bt) => new RTag
{
Code = tag.Code,
Name = tag.Name,
Count = SqlFunc.AggregateCount(tag.Code)
}).MergeTable()
.OrderBy(t => t.Count, orderByType);
if (number > 0)
{
return(q.Take(number).ToListAsync());
}
else
{
return(q.ToListAsync());
}
}
/// <summary>
/// 获取所有书籍被借阅的次数
/// </summary>
/// <returns></returns>
public IEnumerable <object> GetBookBorrowTimes()
{
//太踏马鬼畜了这玩意
try
{
var ls = DbContext.DBstatic
.Queryable <Borrows, Book>((br, bk) => new object[]
{
JoinType.Left, br.Book_id == bk.Id
})
.GroupBy((br, bk) => bk.Isbn)
.Having((br, bk) => SqlFunc.AggregateCount(bk.Isbn) > 0)
.Select((br, bk) => new PopBook {
ISBN = bk.Isbn, Times = SqlFunc.AggregateCount(bk.Isbn)
})
.MergeTable()
.Mapper(pb => { pb.GetName(); })
.OrderBy(pb => pb.Times, OrderByType.Desc)
.ToList();
return(ls);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
public Task <List <RSectionTag> > GetSectionTag(string secCode, int number)
{
var q = Db.Queryable <ESectionTag, ETag, EBookTag>((st, t, bt) => new object[]
{
JoinType.Left, st.TagCode == t.Code,
JoinType.Inner, t.Code == bt.TagCode,
})
.GroupBy((st, t, bt) => new
{
t.Code,
t.Name,
st.SectionCode,
// st.SectionName
})
.Select((st, t, bt) => new RSectionTag
{
SectionCode = st.SectionCode,
// SectionName = st.SectionName,
TagCode = t.Code,
// TagName = t.Name,
TagCount = SqlFunc.AggregateCount(t.Code)
})
.Where(st => st.SectionCode == secCode)
.MergeTable()
.OrderBy(t => t.TagCount, OrderByType.Desc);
if (number > 0)
{
return(q.Take(number).ToListAsync());
}
else
{
return(q.ToListAsync());
}
}
/// <summary>
/// exclutedSecCode 空，只要有tag再某个Section中，则被排除
/// </summary>
/// <param name="exclutedSecCode"></param>
/// <param name="number"></param>
/// <returns></returns>
public Task <List <RTag> > GetTagNotinSection(string exclutedSecCode = "", int number = 0)
{
var q = Db.Queryable <ETag, EBookTag>((tag, bt) => new object[] {
JoinType.Inner, tag.Code == bt.TagCode
})
.GroupBy((tag, bt) => new
{
tag.Code,
tag.Name
})
.Select((tag, bt) => new RTag
{
Code = tag.Code,
Name = tag.Name,
Count = SqlFunc.AggregateCount(tag.Code)
})
.Where(tag => SqlFunc.Subqueryable <ESectionTag>()
.WhereIF(!string.IsNullOrEmpty(exclutedSecCode), s => s.SectionCode == exclutedSecCode)
.Where(s => s.TagCode == tag.Code).NotAny())
.MergeTable()
.OrderBy(t => t.Count, OrderByType.Desc);
if (number > 0)
{
return(q.Take(number).ToListAsync());
}
else
{
return(q.ToListAsync());
}
}
/// <summary>
/// 查询今日余刀
/// 用于查刀和催刀
/// </summary>
/// <returns>余刀表</returns>
public Dictionary <long, int> GetTodayAtkCount()
{
try
{
using var dbClient = SugarUtils.CreateSqlSugarClient(DBPath);
return(dbClient.Queryable <GuildBattle>()
.AS(BattleTableName)
.Where(attack => attack.Time > BotUtils.GetUpdateStamp() &&
attack.Attack != AttackType.Compensate &&
attack.Attack != AttackType.CompensateKill)
.GroupBy(member => member.Uid)
.Select(member => new
{
member.Uid,
times = SqlFunc.AggregateCount(member.Uid)
})
.ToList()
.ToDictionary(member => member.Uid,
member => member.times));
}
catch (Exception e)
{
Log.Error("Database error", Log.ErrorLogBuilder(e));
return(null);
}
}
/// <summary>
/// 采购员采购业绩
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <PurchaseBuyerAmountCountModel> > > GetPurchaseBuyerAmountCount(CurrentUser currentUser)
{
try
{
var today = await _db.Instance.Queryable <TPSMPurchaseOrderMainDbModel, TSMUserAccountDbModel>(
(t1, t2) => new object[]
{
JoinType.Left, t1.BuyerId == t2.ID
}
).Where((t1, t2) => t1.CompanyId == currentUser.CompanyID)
.GroupBy((t1, t2) => t2.AccountName)
.Where((t1, t2) => !SqlFunc.IsNullOrEmpty(t2.AccountName) &&
t1.OrderDate >= Convert.ToDateTime($"{DateTime.Now.ToString("yyyy-MM")}-01") &&
t1.OrderDate < Convert.ToDateTime($"{DateTime.Now.AddMonths(1).ToString("yyyy-MM")}-01")
)
.Select((t1, t2) => new PurchaseBuyerAmountCountModel
{
Value = SqlFunc.AggregateSum(t1.PurchaseAmount),
OrderCount = SqlFunc.AggregateCount(t1.ID),
UserName = t2.AccountName
})
.ToListAsync();
return(ResponseUtil <List <PurchaseBuyerAmountCountModel> > .SuccessResult(today));
}
catch (Exception ex)
{
return(ResponseUtil <List <PurchaseBuyerAmountCountModel> >
.FailResult(null, $"统计采购员采购业绩发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
public static void Easy()
{
var db = GetInstance();
var getAll = db.Queryable <Student>().ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
}
public void Init()
{
var communityId = "";
var buildId = "";
var unitId = "";
var keyword = "";
GetInstance().CodeFirst.InitTables(typeof(MainTable), typeof(SubTable), typeof(Brand), typeof(VendorAndBrand));
GetInstance().Queryable <MainTable>().Where(u =>
(u.CommunityID == communityId || SqlFunc.IsNullOrEmpty(communityId)) &&
(SqlFunc.Contains(u.BuildID, buildId) || SqlFunc.IsNullOrEmpty(buildId)) &&
(SqlFunc.Contains(u.UnitID, unitId) || SqlFunc.IsNullOrEmpty(unitId)) &&
(SqlFunc.Contains(u.RoomNumber, keyword) || SqlFunc.Contains(u.RoomerName, keyword) ||
SqlFunc.Contains(u.HousePlace, keyword) || SqlFunc.Contains(u.UnitName, keyword) ||
SqlFunc.Contains(u.BuildName, keyword) || SqlFunc.IsNullOrEmpty(keyword)))
.GroupBy(ru => new { ru.RoomNumber, ru.RoomID })
.Select(ru => new
{
RoomNumber = SqlFunc.AggregateMax(ru.RoomNumber),
CountRoomer = SqlFunc.AggregateCount(ru.RoomerName),
RoomID = SqlFunc.AggregateMax(ru.RoomID),
Owner = SqlFunc.Subqueryable <SubTable>().Where(r => r.RoomID == ru.RoomID && SqlFunc.Equals(r.RoomUserType, "业主") && SqlFunc.Equals(r.RoomUserType, "业主")).Select(s => s.RoomerName)
}).OrderBy((r) => r.RoomNumber, type: OrderByType.Desc).ToPageListAsync(1, 2).Wait();
GetInstance().Updateable <Student>().UpdateColumns(it =>
new Student()
{
Name = "a".ToString(),
CreateTime = DateTime.Now.AddDays(-1)
}
).Where(it => it.Id == 1).ExecuteCommand();
var list = GetInstance().Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.SchoolId == sc.Id && st.CreateTime == DateTime.Now.AddDays(-1)
})
.Where(st => st.Name == "jack").ToList();
GetInstance().Updateable <BugStudent>().Where(it => true).UpdateColumns(it => new BugStudent()
{
Float = 11
}).ExecuteCommand();
var reslut = GetInstance().Queryable <BugStudent>().ToList();
var list2 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) => new object[] {
JoinType.Left, b.Id == vb.BrandId
})
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var list3 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) =>
b.Id == vb.BrandId)
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var query = GetInstance().Queryable <Student>().Select(o => o);
var result = query.ToList();
}
/// <summary>
/// 获取待完成上架工单
/// </summary>
/// <returns></returns>
public List <ReplenishOrderDto> GetReplenishOrderDto(BasePageDataApo pageDataApo, out int totalCount)
{
totalCount = 0;
List <ReplenishOrderDto> data;
//查询语句
var queryable = Db.Queryable <ReplenishOrder, ReplenishSubOrder, ReplenishSubOrderdtl>((ro, rso, rsod) => new object[] {
JoinType.Left, rso.replenish_order_code == ro.code,
JoinType.Left, rso.id == rsod.replenish_sub_orderid
})
.GroupBy((ro, rso) => ro.code)
.Where((ro, rso, rsod) => rsod.status == (int)RPOStatusType.待完成)
.OrderBy((ro, rso) => ro.create_time, OrderByType.Desc)
.Select((ro, rso, rsod) => new ReplenishOrderDto
{
id = ro.id,
code = ro.code,
status = ro.status,
distribute_time = ro.create_time,
not_picked_goods_num = SqlFunc.AggregateCount(rsod.id)
});
//如果小于0，默认查全部
if (pageDataApo.PageSize > 0)
{
data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount);
}
else
{
data = queryable.ToList();
totalCount = data.Count();
}
return(data);
}
public string EChart(Bootstrap.BootstrapParams bootstrap)
{
//sql й╣ож╥╫й╫
// string sql = "";
// if (_configuration["SqlSugar:DbType"] == "SqlServer")
// {
// sql = $@"SELECT CONVERT
// ( VARCHAR ( 10 ), CreateDate, 121 ) AS CreateDate,
// COUNT( * ) AS COUNT
//FROM
// Sys_log
//WHERE
// LogType = 'login'
// AND CreateDate BETWEEN '{bootstrap.datemin}'
// AND '{bootstrap.datemax}'
//GROUP BY
// CONVERT ( VARCHAR ( 10 ), CreateDate, 121 )
//HAVING
// COUNT( * ) >= 0";
// }
// else
// {
// sql = $@"SELECT
// DATE_FORMAT( CreateDate, '%Y-%d-%m' ) AS CreateDate,
// COUNT( * ) AS COUNT
//FROM
// Sys_log
//WHERE
// LogType = 'login'
// AND CreateDate BETWEEN '{bootstrap.datemin}'
// AND '{bootstrap.datemax}'
//GROUP BY
// DATE_FORMAT( CreateDate, '%Y-%d-%m' )
//HAVING
// COUNT( * ) >= 0";
// }
//var list = _client.Ado.SqlQuery<Log>(sql);
var list = _client.Queryable
(
_client.Queryable <Sys_log>()
.Where(s => s.LogType == LogType.login.EnumToString())
.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB() && s.CreateDate <= bootstrap.datemax.ToDateTimeE())
.GroupBy(s =>
SqlFuncL.ToDateFormat(s.CreateDate)
)
.Having(s => SqlFunc.AggregateCount(s.LogId) >= 0)
.Select(s => new Log()
{
CreateDate = SqlFuncL.ToDateFormat(s.CreateDate),
COUNT = SqlFunc.AggregateCount(s.LogId)
})
)
.ToList();
return(list.JilToJson());
}
public bool IsExistPlan(string planCode = GenCodeHelper.Plan_FromDouBanTagUrls)
{
// var ct = SqlFunc.AggregateCount<EPlan_FromDouBanTagUrls>(a=>a.Code);
var c = IsExist(s => new CountResult {
Count = SqlFunc.AggregateCount(s.Code)
},
a => a.Code == GenCodeHelper.Plan_FromDouBanTagUrls);
return(c > 0);
}
/// <summary>
/// 查询各个标签文章数量
/// </summary>
/// <returns></returns>
public dynamic TagsCount()
{
return(Db.Queryable <TagsInfo>().Where(tag => tag.EnabledMark == true)
.OrderBy(o => o.SortCode, OrderByType.Asc)
.Select(tag => new
{
TagId = tag.TagId,
TagName = tag.TagName,
Color = tag.BGColor,
Total = SqlFunc.Subqueryable <ArticleTags>().Where(at => SqlFunc.Subqueryable <ArticleInfo>()
.Where(c => tag.TagId == at.TagsId && c.ArticleId == at.ArticleId && c.DeleteMark == false && c.Visible == true).Any()
).Select(s => SqlFunc.AggregateCount(s.ArticleId))
}).ToList());
}
/// <summary>
/// 获取所有完整商品属性集合
/// </summary>
/// <returns></returns>
public List <GoodDto> GetAllGoodsDto()
{
//查询语句
return(Db.Queryable <Goods>()
.GroupBy(it => it.goods_code)
.OrderBy(it => it.goods_code, OrderByType.Asc)
.Select(it => new GoodDto
{
name = it.name,
goods_code = it.goods_code,
amount = SqlFunc.AggregateCount(it.id),
expire_time = SqlFunc.AggregateMin(it.expire_date),
position = it.position
}).ToList());
}
public async Task <IActionResult> Index()
{
try
{
var ibStat = await _client.Queryable <Wms_inventorybox>()
.GroupBy(x => x.Status)
.Select(x => new { Status = x.Status, Count = SqlFunc.AggregateCount(x.Status) })
.ToListAsync();
ViewBag.TotalInventoryBox = ibStat.Sum(x => x.Count);
ViewBag.OutingInventoryBox = ibStat.FirstOrDefault(x => x.Status == (int)InventoryBoxStatus.Outing)?.Count ?? 0;
ViewBag.OutedInventoryBox = ibStat.FirstOrDefault(x => x.Status == (int)InventoryBoxStatus.Outed)?.Count ?? 0;
ViewBag.BackingInventoryBox = ibStat.FirstOrDefault(x => x.Status == (int)InventoryBoxStatus.Backing)?.Count ?? 0;
ViewBag.NoneInventoryBox = ibStat.FirstOrDefault(x => x.Status == (int)InventoryBoxStatus.None)?.Count ?? 0;
ViewBag.UsedInventoryBox = await _client.Queryable <Wms_inventorybox>()
.Where(x => x.UsedSize != 0)
.CountAsync();
ViewBag.StockInCount = await _client.Queryable <Wms_stockin>().Where(
x => x.StockInDate >= DateTime.Today && x.StockInDate <= DateTime.Today.AddDays(1) &&
x.WarehouseId == 1)
.CountAsync();
ViewBag.StockInedCount = await _client.Queryable <Wms_stockin>().Where(
x => x.StockInDate >= DateTime.Today && x.StockInDate <= DateTime.Today.AddDays(1) &&
x.StockInStatus == (int)StockInStatus.task_finish &&
x.WarehouseId == 1)
.CountAsync();
ViewBag.StockOutCount = await _client.Queryable <Wms_stockout>().Where(
x => x.StockOutDate >= DateTime.Today && x.StockOutDate <= DateTime.Today.AddDays(1) &&
x.WarehouseId == 1)
.CountAsync();
ViewBag.StockOutedCount = await _client.Queryable <Wms_stockout>().Where(
x => x.StockOutDate >= DateTime.Today && x.StockOutDate <= DateTime.Today.AddDays(1) &&
x.StockOutStatus == (int)StockOutStatus.task_finish &&
x.WarehouseId == 1)
.CountAsync();
}
catch (Exception ex)
{
_logger.LogError(ex, "获取统计数据异常");
}
return(View());
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 2).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
}
public List <CommonModelNameCount> GetRiskAccidentTypeTJ(string orgid)
{
var data = new List <CommonModelNameCount>();
using (var db = _dbContext.GetIntance())
{
data = db.Queryable <RiskClassIficationEntity, DataDictEntity>((rcie, dde) => new object[] {
JoinType.Left, rcie.RiskFactorType == dde.ItemCode && dde.DataType == DataDictConst.ACCIDENT_TYPE,
}).Where((rcie, dde) => rcie.DeleteMark == 1 && dde.DeleteMark == 1).WhereIF(!string.IsNullOrEmpty(orgid), (rcie, dde) => rcie.OrgId == orgid && dde.OrgId == orgid).GroupBy((rcie, dde) => dde.ItemName)
.Select((rcie, dde) => new CommonModelNameCount
{
Name = dde.ItemName,
Count = SqlFunc.AggregateCount(dde.ItemName)
}).ToList();
}
return(data);
}
public List <MedicineTypeBySupplierDTO> getMedicineGroupBySupplier()
{
List <MedicineTypeBySupplierDTO> group = Db.Queryable <CONTRACT, CONTRACT_ITEM, SUPPLIER>((c, ci, s) => c.SUPPLIER_ID == s.SUPPLIER_ID &&
c.CONTRACT_ID == ci.CONTRACT_ID).GroupBy((c, ci, s) => s.SUPPLIER_ID)
.Select((c, ci, s) => new MedicineTypeBySupplierDTO {
_type_number = SqlFunc.AggregateCount(ci.MEDICINE_ID),
_supplier_id = s.SUPPLIER_ID
}).ToList();
foreach (MedicineTypeBySupplierDTO temp in group)
{
SUPPLIER s = Db.Queryable <SUPPLIER>().InSingle(temp._supplier_id);
temp._name = s.NAME;
}
return(group);
}
/// <summary>
/// 获取规则违规人次数
/// </summary>
/// <returns></returns>
public List <StaticsViewModel> GetGZWG(string yljgbh, int page, int limit, ref int count)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <Check_BeForeResultInfo>()
.Where(a => a.DataType == "2" && a.InstitutionCode == yljgbh)
.GroupBy(a => new { a.RulesCode, a.RulesName })
.OrderBy(a => SqlFunc.AggregateCount(a.RulesCode), OrderByType.Desc)
.Select(a => new StaticsViewModel()
{
commonname = a.RulesName, rulecode = a.RulesCode, count = SqlFunc.AggregateCount(a.RulesCode).ToString()
})
.ToPageList(page, limit, ref count);
}
return(DataResult);
}
public static void Sql()
{
using (SqlSugarClient db = DBConfig.GetSugarConn())
{
string[] roles = new string[] { "Administrator", "Admin", "PowerUser", "User", "Guest" };
var data = db.Queryable <SYS_USER, SYS_USER_ROLE>((u, ur) => new object[] { JoinType.Inner, u.USER_ACCOUNT == ur.USER_ACCOUNT && ur.STATUS == "O" })
.Where((u, ur) => u.STATUS == "O" && (u.EMAIL_ADDR != null || u.EMAIL_ADDR == "*****@*****.**") && u.IS_LOCKED == "N" &&
roles.Contains(ur.ROLE_CODE))
.GroupBy((u, ur) => ur.ROLE_CODE)
.Having(u => SqlFunc.AggregateCount(u.USER_ACCOUNT) > 2)
.OrderBy(u => SqlFunc.AggregateCount(u.USER_ACCOUNT))
.Select((u, ur) => new
{
USER_COUNT = SqlFunc.AggregateCount(u.USER_ACCOUNT),
ROLE_CODE = ur.ROLE_CODE
}).ToDataTable();
}
}
/// <summary>
/// 根据规则名称获取各个医院违规人数
/// </summary>
/// <param name="rulename"></param>
/// <param name="flag"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViewsJGMCByRule(string rulename, string flag, string year)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <CheckResultInfoEntity>().GroupBy(a => new { a.InstitutionCode, a.InstitutionName, a.RulesCode })
.Where(a => a.DataType == flag && a.RulesName == rulename)
.WhereIF(!string.IsNullOrEmpty(year), a => a.Year == year)
.OrderBy(a => SqlFunc.AggregateCount(a.InstitutionCode), OrderByType.Desc)
.Select(a => new StaticsViewModel()
{
commonname = a.InstitutionName + "|" + a.RulesCode, count = SqlFunc.AggregateCount(a.InstitutionCode).ToString()
})
.ToList();
}
return(DataResult);
}
/// <summary>
/// 获取审核结果列表按照医院等级分组
/// </summary>
/// <param name="flag"></param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViewsByJGJB(string flag, string year)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <CheckResultInfoEntity, YYXXEntity>((a, b) => new object[] {
JoinType.Left, a.InstitutionCode == b.YYDMYYDM,
}).GroupBy((a, b) => new { b.YLJGDJBM, b.YLJGDJMC })
.Where((a, b) => a.DataType == flag)
.WhereIF(!string.IsNullOrEmpty(year), (a, b) => a.Year == year)
.OrderBy((a, b) => SqlFunc.AggregateCount(b.YLJGDJBM), OrderByType.Desc)
.Select((a, b) => new StaticsViewModel()
{
commonname = b.YLJGDJMC, count = SqlFunc.AggregateCount(b.YLJGDJBM).ToString()
})
.ToList();
}
return(DataResult);
}
/// <summary>
/// 根据机构等级获取机构各个规则违规人数
/// </summary>
/// <param name="djname"></param>
/// <param name="flag"></param>
/// <param name="jgbm"></param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViewsJGMCByDJ(string djname, string flag, string jgbm, string year)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <CheckResultInfoEntity, YYXXEntity>((a, b) => new object[] {
JoinType.Left, a.InstitutionCode == b.YYDMYYDM,
})
.WhereIF(!string.IsNullOrEmpty(jgbm) && jgbm != "100", (a, b) => a.InstitutionCode == jgbm)
.WhereIF(!string.IsNullOrEmpty(year), (a, b) => a.Year == year)
.Where((a, b) => a.DataType == flag && b.YLJGDJMC == djname)
.GroupBy((a, b) => new { a.RulesName, a.RulesCode })
.OrderBy(a => SqlFunc.AggregateCount(a.RulesCode), OrderByType.Desc)
.Select(a => new StaticsViewModel()
{
rulename = a.RulesName, rulecode = a.RulesCode, count = SqlFunc.AggregateCount(a.RulesCode).ToString()
})
.ToList();
}
return(DataResult);
}
/// <summary>
/// 获取左侧医院菜单
/// </summary>
/// <param name="level">医院等级</param>
/// <returns></returns>
public List <TreeModule> GetInstitutionList(string level, string year)
{
var DataResult = new List <TreeModule>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <CheckResultInfoEntity, YYXXEntity>((a, b) => new object[] {
JoinType.Left, a.InstitutionCode == b.YYDMYYDM,
}).Where((a, b) => a.DataType == "2" && b.YLJGDJMC == level)
.WhereIF(!string.IsNullOrEmpty(year), (a, b) => a.Year == year)
.GroupBy((a, b) => new { a.InstitutionCode, a.InstitutionName })
.OrderBy((a, b) => SqlFunc.AggregateCount(a.InstitutionCode), OrderByType.Desc)
.Select((a, b) => new TreeModule()
{
ID = a.InstitutionCode,
PID = "100",
NAME = a.InstitutionName,
RuleCode = "123",
Url = "213"
}).ToList();
}
return(DataResult);
}