using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using WMS.BZModels; using WMS.Info; using wms.sqlsugar.model.sx; using WMS.BZSqlSugar; using WMS.BZModels.Dto.SX.ReportDtos; using System.Reflection; using WMS.BZModels.Dto; using WMS.Util; using NPOI.Util; namespace WMS.BZServices.SX { public class StatisticsreportService { private readonly Repository _flowrepository; private readonly Repository _taskoldrepository; private readonly Repository _taskdtlrepository; private readonly Repository _palletizingrepository; private readonly Repository _billInvnowRepository; private readonly Repository _billPboxruleRepository; public StatisticsreportService(Repository flowrepository, Repository taskoldrepository, Repository taskdtlrepository, Repository palletizingrepository, Repository billInvnowRepository, Repository billPboxruleRepository) { _flowrepository = flowrepository; _taskoldrepository = taskoldrepository; _taskdtlrepository = taskdtlrepository; _palletizingrepository = palletizingrepository; _billInvnowRepository = billInvnowRepository; _billPboxruleRepository = billPboxruleRepository; } public PagedInfo GetPageList(Pagination pagination, StatisticsQueryDto statisticsQueryDto) { //if (pagination.sord.ToUpper() != "ASC") //{ // pagination.sidx = pagination.sidx + " DESC"; //} //if (pagination.sidx.IsEmpty()) //{ // pagination.sidx = "AddTime DESC"; //} //var predicate = Expressionable.Create(); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.KeyWord), (flow, taskold) => flow.MatCode.Contains(statisticsQueryDto.KeyWord) || taskold.Floor.ToString() == statisticsQueryDto.KeyWord //|| taskold.Tunnel.Contains(statisticsQueryDto.KeyWord) || taskold.Device.Contains(statisticsQueryDto.KeyWord) || taskold.AddrTo.Contains(statisticsQueryDto.KeyWord) || taskold.AddrFrom.Contains(statisticsQueryDto.KeyWord)); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.MatCode), (flow, taskold) => flow.MatCode.Contains(statisticsQueryDto.MatCode)); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Tunnel), (flow, taskold) => taskold.Tunnel.Contains(statisticsQueryDto.Tunnel)); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Floor), (flow, taskold) => taskold.Floor.ToString() == statisticsQueryDto.Floor); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Device), (flow, taskold) => taskold.Device.Contains(statisticsQueryDto.Device)); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.AddrFrom), (flow, taskold) => taskold.AddrFrom.Contains(statisticsQueryDto.AddrFrom)); //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.AddrTo), (flow, taskold) => taskold.AddrTo.Contains(statisticsQueryDto.AddrTo)); var list = GetQueryable(statisticsQueryDto).ToPage(pagination); return list; } public PagedInfo GetBGradeStatisticesPageList(Pagination pagination, StatisticsQueryDto statisticsQueryDto) { var list = GetBGradeStatisticesQueryable(statisticsQueryDto).ToPage(pagination); return list; } public IList GetList(StatisticsQueryDto statisticsQueryDto) { ISugarQueryable sugarQueryable = GetQueryable(statisticsQueryDto); var list = sugarQueryable.ToList(); return list; } private ISugarQueryable GetQueryable(StatisticsQueryDto statisticsQueryDto) { var sugarQueryable = _taskoldrepository.Context.Queryable().With(SqlWith.NoLock).SplitTable(p => p.Take(10)) .LeftJoin((taskold, mater) => taskold.MaterialCode == mater.Code) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.Status), (taskold, mater) => taskold.Status.Equals(statisticsQueryDto.Status)) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.KeyWord) || mater.Name.Contains(statisticsQueryDto.KeyWord)) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.MatCode)) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (taskold, mater) => mater.Name.Contains(statisticsQueryDto.MatName)) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (taskold, mater) => taskold.AddTime >= statisticsQueryDto.AddTimeFrom) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeTo != null && statisticsQueryDto.AddTimeTo.HasValue, (taskold, mater) => taskold.AddTime <= statisticsQueryDto.AddTimeTo) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeBegin.HasValue, (taskold, mater) => taskold.StartTime >= statisticsQueryDto.StartTimeBegin) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeEnd.HasValue, (taskold, mater) => taskold.StartTime <= statisticsQueryDto.StartTimeEnd) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeBegin.HasValue, (taskold, mater) => taskold.EndTime >= statisticsQueryDto.EndTimeBegin) .WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeEnd.HasValue, (taskold, mater) => taskold.EndTime <= statisticsQueryDto.EndTimeEnd) .GroupBy((taskold, mater) => new { mater.Code, mater.Name, taskold.Floor, taskold.Type }) .Select((taskold, mater) => new StatisticsDto { MatCode = mater.Code, MatName = mater.Name, Floor = taskold.Floor, Type = taskold.Type, Count = SqlFunc.AggregateDistinctCount(taskold.ID) }).MergeTable(); return sugarQueryable; } private ISugarQueryable GetBGradeStatisticesQueryable(StatisticsQueryDto statisticsQueryDto) { var sugarQueryable = _billInvnowRepository.Context.Queryable().With(SqlWith.NoLock) .Where(p=> p.InvStateCode == "InvEcecState_In" && p.Grade == "B" && string.IsNullOrEmpty(p.PreStock) && (DateTime.Now - p.ProductTime).TotalHours >= 72 && (DateTime.Now - p.ProductTime).TotalHours < 120) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (p) => p.MatCode.Contains(statisticsQueryDto.KeyWord) || p.MatName.Contains(statisticsQueryDto.KeyWord)) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (p) => p.MatCode.Contains(statisticsQueryDto.MatCode)) .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (p) => p.MatName.Contains(statisticsQueryDto.MatName)) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (p) => taskold.AddTime >= statisticsQueryDto.AddTimeFrom) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeTo != null && statisticsQueryDto.AddTimeTo.HasValue, (p) => taskold.AddTime <= statisticsQueryDto.AddTimeTo) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeBegin.HasValue, (p) => taskold.StartTime >= statisticsQueryDto.StartTimeBegin) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeEnd.HasValue, (p) => taskold.StartTime <= statisticsQueryDto.StartTimeEnd) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeBegin.HasValue, (p) => taskold.EndTime >= statisticsQueryDto.EndTimeBegin) //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeEnd.HasValue, (p) => taskold.EndTime <= statisticsQueryDto.EndTimeEnd) .GroupBy((p) => new { p.MatCode, p.MatName, p.HWTypeCode , p.Wind }) .Select((p) => new StatisticsDto { MatCode = p.MatCode, MatName = p.MatName, HwType = p.HWTypeCode, Wind = p.Wind, Count = SqlFunc.AggregateDistinctCount(p.Id) }).MergeTable(); return sugarQueryable; } /// /// 24小时内任务动态 /// /// /// /// public PagedInfo GetHourTask(Pagination pagination, HourTaskQueryDto hourTaskQueryDto) { if (!hourTaskQueryDto.EndTimeBegin.HasValue) { hourTaskQueryDto.EndTimeBegin = DateTime.Now.Date; } if (!hourTaskQueryDto.EndTimeEnd.HasValue) { hourTaskQueryDto.EndTimeEnd = DateTime.Now.AddHours(1); } if ((hourTaskQueryDto.EndTimeEnd.Value - hourTaskQueryDto.EndTimeBegin.Value).Days > 60) { throw BZSysExCore.ThrowFailException("查询日期范围不能超过60天!"); } var predicate = Expressionable.Create(); predicate = predicate.And(m => m.Status == (int)BZModels.TaskStatus.Finish); predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.WarehouseCode), m => hourTaskQueryDto.WarehouseCode.Equals(m.WarehouseCode)); predicate = predicate.AndIF(hourTaskQueryDto.EndTimeBegin.HasValue, m => m.EditTime >= hourTaskQueryDto.EndTimeBegin.Value); predicate = predicate.AndIF(hourTaskQueryDto.EndTimeEnd.HasValue, m => m.EditTime <= hourTaskQueryDto.EndTimeEnd.Value); predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (new string[] { "1", "2", "3" }.Any(o => o.Equals(hourTaskQueryDto.TaskType))), m => m.Type.Equals(hourTaskQueryDto.TaskType)); predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.BusType), m => m.BusType.Equals(hourTaskQueryDto.BusType)); var sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Select(it => new TaskInOutDto { }); if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101")) { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .Select(it => new { EditTime = it.EditTime.Date, Hour = it.EditTime.Hour }) .MergeTable()//将查询结果转成一个表 .GroupBy(o => new { o.Hour, o.EditTime }) .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Hour), Hour = it.Hour, CreateTime = it.EditTime }) .OrderBy(o => o.CreateTime); } else { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .Select(it => new { Type = it.Type, EditTime = it.EditTime.Date, Hour = it.EditTime.Hour }) .MergeTable()//将查询结果转成一个表 .GroupBy(o => new { o.Type, o.Hour, o.EditTime }) .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Type), Type = it.Type, Hour = it.Hour, CreateTime = it.EditTime }) .OrderBy(o => o.CreateTime); } var lists = sugarQueryable.ToList(); PagedInfo HourTasks = new PagedInfo() { Result = new List() }; //var plist = new List(typeof(HourTaskDto).GetProperties()); var plist = typeof(HourTaskDto); var types = lists.Select(o => o.Type).Distinct().OrderBy(o => o).ToList(); var dates = lists.Select(o => o.CreateTime).Distinct().OrderBy(o => o).ToList(); foreach (var p in dates) { if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101")) { if (!lists.Any(o => o.CreateTime == p)) { continue; } HourTaskDto s = Activator.CreateInstance(); var TaskDate = plist.GetProperty("TaskDate"); //var TaskDate = plist.Find(p => p.Name == "TaskDate"); if (TaskDate != null) TaskDate.SetValue(s, p.ToString("yyyy-MM-dd"), null); var TaskType = plist.GetProperty("TaskType"); if (TaskType != null) TaskType.SetValue(s, -1, null); for (int i = 0; i < 24; i++) { PropertyInfo info = plist.GetProperty("A" + i.ToString()); if (info != null) { var first = lists.FirstOrDefault(o => o.CreateTime == p && o.Hour == i); if (first != null) { info.SetValue(s, first.Count, null); } } } HourTasks.Result.Add(s); } else { foreach (var t in types) { if (!lists.Any(o => o.CreateTime == p && o.Type == t)) { continue; } HourTaskDto s = Activator.CreateInstance(); var TaskType = plist.GetProperty("TaskType"); if (TaskType != null) TaskType.SetValue(s, (BZModels.TaskType)t, null); var TaskDate = plist.GetProperty("TaskDate"); if (TaskDate != null) TaskDate.SetValue(s, p.ToString("yyyy-MM-dd"), null); for (int i = 0; i < 24; i++) { PropertyInfo info = plist.GetProperty("A" + i.ToString()); if (info != null) { var first = lists.FirstOrDefault(o => o.Type == t && o.CreateTime == p && o.Hour == i); if (first != null) { info.SetValue(s, first.Count, null); } } } HourTasks.Result.Add(s); } } } if (!pagination.sidx.IsEmpty()) { pagination.sidx = pagination.sidx.Replace("DESC", "").Replace("ASC", ""); HourTasks.Result.Sort( delegate (HourTaskDto info1, HourTaskDto info2) { Type t = typeof(HourTaskDto); PropertyInfo pro = t.GetProperty(pagination.sidx); if (pagination.sidx == "TaskDate" || pagination.sidx == "TypeName") { return pagination.sord.ToLower().Contains("asc") ? pro.GetValue(info1, null).ToString().CompareTo(pro.GetValue(info2, null).ToString()) : pro.GetValue(info2, null).ToString().CompareTo(pro.GetValue(info1, null).ToString()); } else return pagination.sord.ToLower().Contains("asc") ? pro.GetValue(info1, null).ToInt().CompareTo(pro.GetValue(info2, null).ToInt()) : pro.GetValue(info2, null).ToInt().CompareTo(pro.GetValue(info1, null).ToInt()); }); //typeof(HourTaskDto).GetProperty( pagination.sidx.Replace("DESC", "").Replace("ASC", ""))).ToList(); } HourTasks.PageIndex = 1; HourTasks.PageSize = HourTasks.Result.Count; HourTasks.TotalNum = HourTasks.Result.Count; return HourTasks; } /// /// 码垛数 /// /// /// /// public PagedInfo GetPalletizingReport(Pagination pagination, PalletizingReportQueryDto QueryDto) { if (!QueryDto.EndTimeBegin.HasValue) { QueryDto.EndTimeBegin = DateTime.Now.Date; } if (!QueryDto.EndTimeEnd.HasValue) { QueryDto.EndTimeEnd = DateTime.Now.AddHours(1); } var predicate = Expressionable.Create(); predicate = predicate.And(m => m.Finish != 1 && !new string[] { "Special", "OnePack" }.Contains(m.BoxRule)); predicate = predicate.And(m => m.PalletizState == 1); predicate = predicate.AndIF(QueryDto.EndTimeBegin.HasValue, m => m.AddTime >= QueryDto.EndTimeBegin.Value); predicate = predicate.AndIF(QueryDto.EndTimeEnd.HasValue, m => m.AddTime <= QueryDto.EndTimeEnd.Value); ISugarQueryable sugarQueryable; if (QueryDto.GroupName == "Hour") { sugarQueryable = _palletizingrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()) .GroupBy(it => new { it.EditTime.Year, it.EditTime.Month, it.EditTime.Day, it.EditTime.Hour }) .Select(it => new PalletizingReportDto { Year = it.EditTime.Year.ToString(), Month = it.EditTime.Month.ToString(), Day = it.EditTime.Day.ToString(), Hour = it.EditTime.Hour.ToString(), BigQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 1).Return(1).End()), SmallQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 2).Return(1).End()), TotalQty = SqlFunc.AggregateCount(1), }).MergeTable(); } else { sugarQueryable = _palletizingrepository.Queryable().With(SqlWith.NoLock).With(SqlWith.NoLock).Where(predicate.ToExpression()) .GroupBy(it => new { it.EditTime.Year, it.EditTime.Month, it.EditTime.Day }) .Select(it => new PalletizingReportDto { Year = it.EditTime.Year.ToString(), Month = it.EditTime.Month.ToString(), Day = it.EditTime.Day.ToString(), BigQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 1).Return(1).End()), SmallQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 2).Return(1).End()), TotalQty = SqlFunc.AggregateCount(1), }).MergeTable(); if (pagination.sidx == "Hour") pagination.sidx = "Day"; } var lists = sugarQueryable.ToPage(pagination); lists.Result.Add(new PalletizingReportDto { Year = "合计：", BigQty = lists.Result.Sum(o => o.BigQty), SmallQty = lists.Result.Sum(o => o.SmallQty), TotalQty = lists.Result.Sum(o => o.TotalQty) }); return lists; } public PagedInfo GetInOutReport(Pagination pagination, InOutReportQueryDto queryDto) { if (!queryDto.EndTimeBegin.HasValue) { queryDto.EndTimeBegin = DateTime.Now.Date; } if (!queryDto.EndTimeEnd.HasValue) { queryDto.EndTimeEnd = DateTime.Now.AddHours(1); } var predicate = Expressionable.Create(); predicate = predicate.And((taskold, dtl) => taskold.Status >= (int)BZModels.TaskStatus.Finish); predicate = predicate.And((taskold, dtl) => new string[] { "1", "2" }.Contains(taskold.Type.ToString())); predicate = predicate.AndIF(queryDto.EndTimeBegin.HasValue, (taskold, dtl) => dtl.AddTime >= queryDto.EndTimeBegin.Value); predicate = predicate.AndIF(queryDto.EndTimeEnd.HasValue, (taskold, dtl) => dtl.AddTime <= queryDto.EndTimeEnd.Value); ISugarQueryable sugarQueryable; var joinq = _taskdtlrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3)); if (queryDto.GroupName == "Hour") { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3)) .LeftJoin(joinq, (taskold, dtl) => taskold.ID == dtl.ParentTaskCode).Where(predicate.ToExpression()) .GroupBy((taskold, dtl) => new { dtl.AddTime.Year, dtl.AddTime.Month, dtl.AddTime.Day, dtl.AddTime.Hour }) .Select((taskold, dtl) => new InOutReportDto { Year = dtl.AddTime.Year.ToString(), Month = dtl.AddTime.Month.ToString(), Day = dtl.AddTime.Day.ToString(), Hour = dtl.AddTime.Hour.ToString(), CordInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End()), TwistInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End()), StackingTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End()), TorsionTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End()), TotalInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End()), TotalOutQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End()), }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour }) } else { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3)) .LeftJoin(joinq, (taskold, dtl) => taskold.ID == dtl.ParentTaskCode).Where(predicate.ToExpression()) .GroupBy((taskold, dtl) => new { dtl.AddTime.Year, dtl.AddTime.Month, dtl.AddTime.Day }) .Select((taskold, dtl) => new InOutReportDto { Year = dtl.AddTime.Year.ToString(), Month = dtl.AddTime.Month.ToString(), Day = dtl.AddTime.Day.ToString(), CordInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End()), TwistInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End()), StackingTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End()), TorsionTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End()), TotalInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End()), TotalOutQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End()), }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour }) if (pagination.sidx == "Hour") pagination.sidx = "Day"; } var lists = sugarQueryable.ToPage(pagination); lists.Result.Add(new InOutReportDto { Year = "合计：", CordInQty = lists.Result.Sum(o => o.CordInQty), TwistInQty = lists.Result.Sum(o => o.TwistInQty), StackingTaskQty = lists.Result.Sum(o => o.StackingTaskQty), TorsionTaskQty = lists.Result.Sum(o => o.TorsionTaskQty), TotalInQty = lists.Result.Sum(o => o.TotalInQty), TotalOutQty = lists.Result.Sum(o => o.TotalOutQty), }); return lists; } public PagedInfo GetCurStockInfo(Pagination pagination, CurStockInfoQueryDto queryDto) { var predicate = Expressionable.Create(); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.KeyWord), (billInvnow) => billInvnow.MatCode.Contains(queryDto.KeyWord) || billInvnow.MatName.Contains(queryDto.KeyWord) || billInvnow.InDocsNo.Contains(queryDto.KeyWord) || billInvnow.ContGrpBarCode.Contains(queryDto.KeyWord)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatName), (billInvnow) => billInvnow.MatName.Contains(queryDto.MatName)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatCode), (billInvnow) => billInvnow.MatCode.Contains(queryDto.MatCode)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.DocNo), (billInvnow) => billInvnow.InDocsNo.Contains(queryDto.DocNo)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.WbGroupCode), (billInvnow) => billInvnow.WbGroupCode.Contains(queryDto.WbGroupCode)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Wind), (billInvnow) => billInvnow.Wind.Contains(queryDto.Wind)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.HWType), (billInvnow) => billInvnow.HWTypeCode.Contains(queryDto.HWType)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.IsBlack), (billInvnow) => billInvnow.IsBlack.Equals(queryDto.IsBlack)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Grade), (billInvnow) => billInvnow.Grade.Contains(queryDto.Grade)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.ContGrpBarCode), (billInvnow) => billInvnow.ContGrpBarCode.Contains(queryDto.ContGrpBarCode)); predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeFrom.HasValue, (billInvnow) => billInvnow.ProductTime >= queryDto.ProductTimeFrom); predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeTo.HasValue, (billInvnow) => billInvnow.ProductTime <= queryDto.ProductTimeTo); var CurStockInfoLists = _billInvnowRepository.Context.Queryable() .With(SqlWith.NoLock) .Where((billInvnow) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && ((billInvnow.IsControlpanel == false) || (billInvnow.IsControlpanel == true && billInvnow.InDocsNo.StartsWith("CHA")))) .Where(predicate.ToExpression()) .Select((billInvnow) => new CurStockInfo { MatName = billInvnow.MatName, MatCode = billInvnow.MatCode, DocNo = billInvnow.InDocsNo, WbGroupCode = billInvnow.WbGroupCode, Wind = billInvnow.Wind, HWType = billInvnow.HWTypeCode, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = billInvnow.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = billInvnow.Grade, ContGrpBarCode = billInvnow.ContGrpBarCode, BatchNo = billInvnow.BatchNo }).MergeTable().ToList(); //var boxlistqueryable = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).LeftJoin(queryable, (billPboxrule, list) => billPboxrule.DocsNo == list.DocNo) // .Select((billPboxrule, list) => billPboxrule); var doclist = _billPboxruleRepository.Context.Queryable().With(SqlWith.NoLock).Select(p => new CurStockInfo() { DocNo = p.DocsNo, SKU = p.SkuCode }).Distinct().ToList(); var newCurStockInfoLists = from st in CurStockInfoLists join doc in doclist on st.DocNo equals doc.DocNo into tempstock from nst in tempstock.DefaultIfEmpty() select new CurStockInfo { MatName = st.MatName, MatCode = st.MatCode, DocNo = nst == null ? "" : nst.DocNo, WbGroupCode = st.WbGroupCode, Wind = st.Wind, HWType = st.HWType, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = st.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = st.Grade, ContGrpBarCode = st.ContGrpBarCode, SKU = nst == null ? "" : nst.DocNo, BatchNo = st.BatchNo }; // var pagelists = queryable.ToPage(pagination); var lists = newCurStockInfoLists.Distinct(new CompareStock()); var list = new List(); // var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).ToList(); var DocNos= lists.Select(o => o.DocNo).ToList(); var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).Where(o=> DocNos.Contains(o.DocsNo)) .ToList(); foreach (var item in lists.GroupBy(p => new { p.SKU, p.MatCode, p.DocNo, p.WbGroupCode, p.Grade,p.BatchNo }).ToList()) { list.Add(new CurStockInfo() { SKU = item.Key.SKU ?? "", MatName = item.First().MatName, DocNo = item.Key.DocNo, WbGroupCode = item.Key.WbGroupCode ?? "", BatchNo = item.Key.BatchNo, NoBCount = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L") + "L" }, BCount = new List() { item.Count(p => p.IsBlack == "1" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "1" && p.Wind == "L") + "L" }, Grade = item.Key.Grade ?? "", //Rule = item.Key.Rule ?? "", FinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode == 2).Count(), NoFinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode == 0 && !string.IsNullOrEmpty(p.BatchNo) && !string.IsNullOrEmpty(p.Wind) && p.PackRule == "SPC").Count(), Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode)) }); } PagedInfo pagelists = new PagedInfo() { Result = new List() }; pagelists.Result = list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); pagelists.PageIndex = 1; pagelists.PageSize = list.Count; pagelists.TotalNum = list.Count; // pagelists.Result = list; // return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); return pagelists; } /// /// 时效当前库存分布（控制盘） /// /// public PagedInfo GetCurStockInfoControl(Pagination pagination, CurStockInfoQueryDto queryDto) { var predicate = Expressionable.Create(); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.KeyWord), (billInvnow, boxrule) => billInvnow.MatCode.Contains(queryDto.KeyWord) || billInvnow.MatName.Contains(queryDto.KeyWord) || billInvnow.InDocsNo.Contains(queryDto.KeyWord) || billInvnow.ContGrpBarCode.Contains(queryDto.KeyWord) || boxrule.SkuCode.Contains(queryDto.KeyWord)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.SKU), (billInvnow, boxrule) => boxrule.SkuCode.Contains(queryDto.SKU)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatName), (billInvnow, boxrule) => billInvnow.MatName.Contains(queryDto.MatName)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatCode), (billInvnow, boxrule) => billInvnow.MatCode.Contains(queryDto.MatCode)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.DocNo), (billInvnow, boxrule) => billInvnow.InDocsNo.Contains(queryDto.DocNo)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.WbGroupCode), (billInvnow, boxrule) => billInvnow.WbGroupCode.Contains(queryDto.WbGroupCode)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Wind), (billInvnow, boxrule) => billInvnow.Wind.Contains(queryDto.Wind)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.HWType), (billInvnow, boxrule) => billInvnow.HWTypeCode.Contains(queryDto.HWType)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.IsBlack), (billInvnow, boxrule) => billInvnow.IsBlack.Equals(queryDto.IsBlack)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Grade), (billInvnow, boxrule) => billInvnow.Grade.Contains(queryDto.Grade)); predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.ContGrpBarCode), (billInvnow, boxrule) => billInvnow.ContGrpBarCode.Contains(queryDto.ContGrpBarCode)); predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeFrom.HasValue, (billInvnow, boxrule) => billInvnow.ProductTime >= queryDto.ProductTimeFrom); predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeTo.HasValue, (billInvnow, boxrule) => billInvnow.ProductTime <= queryDto.ProductTimeTo); var CurStockInfoLists = _billInvnowRepository.Context.Queryable((billInvnow, boxrule) => new object[] { JoinType.Left,billInvnow.InDocsNo == boxrule.DocsNo,JoinType.Left}) .With(SqlWith.NoLock) .Where((billInvnow, boxrule) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && billInvnow.IsControlpanel == true && billInvnow.Grade == "A") .Where(predicate.ToExpression()) .Select((billInvnow, boxrule) => new CurStockInfo { SKU = boxrule.SkuCode, MatName = billInvnow.MatName, MatCode = billInvnow.MatCode, DocNo = billInvnow.InDocsNo, WbGroupCode = billInvnow.WbGroupCode, Wind = billInvnow.Wind, HWType = billInvnow.HWTypeCode, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = billInvnow.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = billInvnow.Grade, ContGrpBarCode = billInvnow.ContGrpBarCode, ProductTime = billInvnow.ProductTime }).MergeTable().ToList(); //var pagelists = queryable.ToPage(pagination); var lists = CurStockInfoLists.Distinct(new CompareStock()); //var lists = pagelists.Result ?? new List().Distinct(new CompareStock()); //var lists = queryable.ToList().Distinct(new CompareStock()); var list = new List(); //var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).ToList(); //var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).LeftJoin(queryable, (billPboxrule, list) => billPboxrule.DocsNo == list.DocNo) // .Select((billPboxrule, list) => billPboxrule).ToList(); var DocNos = lists.Select(o => o.DocNo).ToList(); var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).Where(o => DocNos.Contains(o.DocsNo)).ToList(); foreach (var item in lists.GroupBy(p => new { p.SKU, p.MatCode, p.WbGroupCode, p.Grade }).ToList()) { list.Add(new CurStockInfo() { SKU = item.Key.SKU ?? "", MatName = item.First().MatName, WbGroupCode = item.Key.WbGroupCode ?? "", HaveBlack = new List() { "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" +")", "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" +")", "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" +")", }, Grade = item.Key.Grade ?? "", //Rule = item.Key.Rule ?? "", Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode)), TodayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" }, YesterdayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" }, BeforeYesterdayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" }, }); } // return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); //pagelists.Result = list; PagedInfo pagelists = new PagedInfo() { Result = new List() }; pagelists.Result = list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); pagelists.PageIndex = 1; pagelists.PageSize = list.Count; pagelists.TotalNum = list.Count; return pagelists; } } }