/// <summary> /// 根据库存量倒序查询 /// </summary> /// <param name="pager"></param> /// <returns></returns> public async Task <List <InventorySimpleModel> > searchByCountOrder(Pager <List <InventorySimpleModel> > pager) { int start = (pager.page - 1) * pager.recPerPage; LayerDbContext context = new LayerDbContext(); var inventoryResult = context.inventory.GroupBy(x => x.materialId).Select(x => new { materialId = x.Key, count = x.Sum(item => item.count) }); var materialResult = context.materialInfos; var returnData = from x in materialResult join y in inventoryResult on x.id equals y.materialId into Temp from temp in Temp.DefaultIfEmpty() select new InventorySimpleModel { materialId = x.id, alais = x.alias, materialName = x.materialName, mat_size = x.mat_size, unit = x.unit, alarmCount = x.alarmCount, count = temp == null ? 0 : temp.count }; returnData = returnData.OrderBy(x => x.count).Skip(start).Take(pager.recPerPage); return(await Task.Factory.StartNew(() => returnData.ToList())); }
public IEnumerable <LessonDescriptionsDetail> GetLessonDescription(int lessonId, int langId) { var lessonDescription = from des in CryptoLearnContext.LessonDescriptions join diag in CryptoLearnContext.LessonDescriptionDiagrams on des.DiagramId equals diag.Id into Temp from ET in Temp.DefaultIfEmpty() where des.LessonId == lessonId && des.LanguageId == langId orderby des.Id select new LessonDescriptionsDetail { Title = des.Title, Description = des.Paragraph, Diagram = ET == null ? string.Empty : ET.DescriptionDiagram, Caption = ET == null ? string.Empty : ET.DescriptionDiagramCaption }; return(lessonDescription.ToList()); }
/// <summary> /// 查询页面展示 /// </summary> /// <param name="pager"></param> /// <param name="t"></param> /// <returns></returns> public async Task <List <InventorySimpleModel> > searchByCondition(Pager <List <InventorySimpleModel> > pager, InventoryQuery t) { int start = (pager.page - 1) * pager.recPerPage; LayerDbContext context = new LayerDbContext(); var inventoryResult = context.inventory.GroupBy(x => x.materialId).Select(x => new { materialId = x.Key, count = x.Sum(item => item.count) }); var materialResult = context.materialInfos.Where(x => x.id > 0); materialResult = string.IsNullOrEmpty(t.materialName) ? materialResult : materialResult.Where(x => x.materialName == (t.materialName)); materialResult = string.IsNullOrEmpty(t.mat_size) ? materialResult : materialResult.Where(x => x.mat_size == t.mat_size); materialResult = string.IsNullOrEmpty(t.alias) ? materialResult : materialResult.Where(x => x.alias == (t.alias)); materialResult = string.IsNullOrEmpty(t.remark) ? materialResult : materialResult.Where(x => x.remark.Contains(t.remark)); var returnData = from x in materialResult join y in inventoryResult on x.id equals y.materialId into Temp from temp in Temp.DefaultIfEmpty() select new InventorySimpleModel { materialId = x.id, alais = x.alias, materialName = x.materialName, mat_size = x.mat_size, unit = x.unit, alarmCount = x.alarmCount, count = temp == null?0:temp.count }; if (t.lackFlag == 0) { returnData = returnData.Where(x => x.alarmCount >= x.count); } else if (t.lackFlag == 1) { returnData = returnData.Where(x => x.alarmCount < x.count); } returnData = returnData.OrderBy(x => x.count).Skip(start).Take(pager.recPerPage); return(await Task.Factory.StartNew(() => returnData.ToList())); }
/// <summary> /// 查询前十库存不足 /// </summary> /// <returns></returns> public List <InventorySimpleModel> searchTenLackInventory() { LayerDbContext context = new LayerDbContext(); var inventoryResult = context.inventory.GroupBy(x => x.materialId).Select(x => new { materialId = x.Key, count = x.Sum(item => item.count) }); var materialResult = context.materialInfos.Where(x => x.id > 0); var returnData = from x in materialResult join y in inventoryResult on x.id equals y.materialId into Temp from temp in Temp.DefaultIfEmpty() select new InventorySimpleModel { materialId = x.id, materialName = x.materialName, mat_size = x.mat_size, alarmCount = x.alarmCount, count = temp == null ? 0 : temp.count }; returnData = returnData.Where(x => x.alarmCount >= x.count); returnData = returnData.OrderBy(x => x.count).Take(11); return(returnData.ToList()); }
public OrderTool getHalfYearOrderNum() { DateTime startTime = DateTime.Now.AddMonths(-5).AddDays(1 - DateTime.Now.Day).Date; List <DateTime> MonthDate = new List <DateTime>(); MonthDate = BaseDataHelper.getSixMnthDate(DateTime.Now); LayerDbContext context = new LayerDbContext(); //异常订单数 var exceptOrderTemp = context.orderInfo.Where(x => x.status == "excepted" && x.receivedDate > startTime).GroupBy(x => x.createdDate.Month).Select(x => new { createdDate = x.Max(item => item.createdDate), count = x.Count() }).ToList(); //所有订单 var orderTemp = context.orderInfo.Where(x => x.createdDate > startTime).GroupBy(x => x.createdDate.Month).Select(x => new { createdDate = x.Max(item => item.createdDate), count = x.Count() }).ToList(); //完成订单 var completedOrderTemp = context.orderInfo.Where(x => x.status == "completed" && x.receivedDate > startTime).GroupBy(x => x.createdDate.Month).Select(x => new { createdDate = x.Max(item => item.createdDate), count = x.Count() }).ToList(); List <LineDataTool> orderData = new List <LineDataTool>(); List <LineDataTool> exceptedOrderData = new List <LineDataTool>(); List <LineDataTool> completedOrderData = new List <LineDataTool>(); LineDataTool model = null; foreach (var item in orderTemp) { model = new LineDataTool(); model.intData = item.count; model.date = item.createdDate.AddDays(1 - item.createdDate.Day).Date; orderData.Add(model); } foreach (var item in exceptOrderTemp) { model = new LineDataTool(); model.intData = item.count; model.date = item.createdDate.AddDays(1 - item.createdDate.Day).Date; exceptedOrderData.Add(model); } foreach (var item in completedOrderTemp) { model = new LineDataTool(); model.intData = item.count; model.date = item.createdDate.AddDays(1 - item.createdDate.Day).Date; completedOrderData.Add(model); } var normalData = (from x in MonthDate.OrderBy(x => x) join y in orderData on x equals y.date into Temp from t in Temp.DefaultIfEmpty() select t == null ? 0 : t.intData).ToArray(); var exceptData = (from x in MonthDate.OrderBy(x => x) join y in exceptedOrderData on x equals y.date into Temp from t in Temp.DefaultIfEmpty() select t == null ? 0 : t.intData).ToArray(); var copmpletedData = (from x in MonthDate.OrderBy(x => x) join y in completedOrderData on x equals y.date into Temp from t in Temp.DefaultIfEmpty() select t == null ? 0 : t.intData).ToArray(); OrderTool result = new OrderTool(); result.exceptedOrderNum = exceptData; result.produceOrderNum = normalData; result.completedOrderNum = copmpletedData; return(result); }
public float[] searchLastMonthProfit() { DateTime startDate = DateTime.Now.AddDays(-30).Date; DateTime endDate = DateTime.Now.Date; List <DateTime> dateList = BaseDataHelper.getLastMonthDate(DateTime.Now); LayerDbContext context = new LayerDbContext(); var profitData = context.profitRecord.Where(x => x.createdDate >= startDate && x.createdDate < endDate).GroupBy(x => x.createdDate.Day).Select(x => new { createdDate = x.Max(item => item.createdDate), profit = x.Sum(item => item.profit) }).ToList(); List <LineDataTool> profitData1 = new List <LineDataTool>(); LineDataTool model = null; foreach (var item in profitData) { model = new LineDataTool(); model.date = item.createdDate.Date; model.floatData = item.profit; profitData1.Add(model); } var result = (from x in dateList.OrderBy(x => x) join y in profitData1 on x equals y.date into Temp from temp in Temp.DefaultIfEmpty() select(temp == null ? 0 : temp.floatData)).ToArray(); return(result); }