using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using wms.sqlsugar.model.pt; using WMS.BZModels; using WMS.BZModels.Dto.PT.ReportDtos; using WMS.Info; using WMS.BZSqlSugar; using System.Reflection; using WMS.BZModels.Dto; using WMS.Util; namespace WMS.BZServices.PT { public class StatisticsreportService { private readonly Repository _flowrepository; private readonly Repository _taskoldrepository; public StatisticsreportService(Repository flowrepository, Repository taskoldrepository) { _flowrepository = flowrepository; _taskoldrepository = taskoldrepository; } 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); list.Result.ForEach(o => { o.NetWQty = Math.Round(Convert.ToDecimal(o.NetWQty), 2, MidpointRounding.AwayFromZero); }); return list; } public IList GetList(StatisticsQueryDto statisticsQueryDto) { ISugarQueryable sugarQueryable = GetQueryable(statisticsQueryDto); var list = sugarQueryable.ToList(); list.ForEach(o => { o.NetWQty = Math.Round(Convert.ToDecimal(o.NetWQty), 2, MidpointRounding.AwayFromZero); }); 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, NetWQty = SqlFunc.AggregateSum(Convert.ToDecimal(taskold.Weight ?? 0.0)), Count = SqlFunc.AggregateDistinctCount(taskold.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.EndTime >= hourTaskQueryDto.EndTimeBegin.Value); predicate = predicate.AndIF(hourTaskQueryDto.EndTimeEnd.HasValue, m => m.EndTime <= hourTaskQueryDto.EndTimeEnd.Value); predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (new string[] { "1", "2", "3", "5" }.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().Select(it => new TaskInOutDto { }); if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101")) { sugarQueryable = _taskoldrepository.Queryable().Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .Select(it => new { EditTime = it.EndTime.Value.Date, Hour = it.EndTime.Value.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().Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .Select(it => new { Type = it.Type, EditTime = it.EndTime.Value.Date, Hour = it.EndTime.Value.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 = 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"); 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, (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 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(m => m.Status == (int)BZModels.TaskStatus.Finish); predicate = predicate.And(m => new string[] { "1", "2", "5" }.Contains(m.Type.ToString())); predicate = predicate.AndIF(queryDto.EndTimeBegin.HasValue, m => m.EndTime >= queryDto.EndTimeBegin.Value); predicate = predicate.AndIF(queryDto.EndTimeEnd.HasValue, m => m.EndTime <= queryDto.EndTimeEnd.Value); ISugarQueryable sugarQueryable; if (queryDto.GroupName == "Hour") { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .GroupBy(it => new { it.EndTime.Value.Year, it.EndTime.Value.Month, it.EndTime.Value.Day, it.EndTime.Value.Hour }) .Select(it => new InOutReportDto { Year = it.EndTime.Value.Year.ToString(), Month = it.EndTime.Value.Month.ToString(), Day = it.EndTime.Value.Day.ToString(), Hour = it.EndTime.Value.Hour.ToString(), InQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End()), OutQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2).Return(1).End()), }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour }) } else { sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)) .GroupBy(it => new { it.EndTime.Value.Year, it.EndTime.Value.Month, it.EndTime.Value.Day }) .Select(it => new InOutReportDto { Year = it.EndTime.Value.Year.ToString(), Month = it.EndTime.Value.Month.ToString(), Day = it.EndTime.Value.Day.ToString(), InQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End()), OutQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2).Return(1).End()), }).MergeTable(); //.OrderBy(o => new { o.Year, o.Month, o.Day }) if (pagination.sidx == "Hour") pagination.sidx = "Day"; } var lists = sugarQueryable.ToPage(pagination); lists.Result.Add(new InOutReportDto { Year = "合计：", InQty = lists.Result.Sum(o => o.InQty), OutQty = lists.Result.Sum(o => o.OutQty), }); return lists; } } }