public async Task <ReportPagingUI> Get(int id, int skip, int limit, string filter = null, string orderby = null) { var output = new ReportPagingUI(); switch (id) { case 1: output = await GetReportTasks(skip, limit); break; case 2: output = await GetReportEquipmentTaskHistory(skip, limit, filter, orderby); break; default: throw new Exception("report not found"); } return(output); }
public async Task <ReportPagingUI> GetReportTasks(int skip, int limit) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var result = (await conn.QueryAsync <TrainTaskReport1>( $"{ReportCommon.SqlSelect} {ReportCommon.Sql1Common} {ReportCommon.Sql1PagingEnd}", new[] { typeof(TrainTaskReport1), typeof(TrainTaskStatus), typeof(Carriage), typeof(Model.Model), typeof(Train), typeof(EquipmentModel), typeof(Equipment), typeof(User) }, objects => { var task = (TrainTaskReport1)objects[0]; task.Status = (TrainTaskStatus)objects[1]; task.Carriage = (Carriage)objects[2]; task.Carriage.Model = (Model.Model)objects[3]; task.Carriage.Train = (Train)objects[4]; task.EquipmentModel = (EquipmentModel)objects[5]; task.EquipmentModel.Equipment = (Equipment)objects[6]; var user = (User)objects[7]; user.PasswordHash = null; task.User = user; switch (task.Carriage.Model.ModelType) { case ModelType.HeadVagon: task.CarriageTypeString = Other.Other.CarriageTypeString.HeadVagon; break; case ModelType.TractionVagon: task.CarriageTypeString = Other.Other.CarriageTypeString.TractionVagon; break; case ModelType.TrailerVagon: task.CarriageTypeString = Other.Other.CarriageTypeString.TrailerVagon; break; } return(task); }, new { skip = skip, limit = limit, repeat_task_status = Model.Enums.TaskStatus.Remake } )).ToArray(); var sqlc = $"{ReportCommon.SqlCount} {ReportCommon.Sql1Common}"; var count = conn.ExecuteScalar <int>(sqlc); var columns = new List <ReportColumnsUI> { new ReportColumnsUI { Name = "Номер Задачи", Type = "int" }, new ReportColumnsUI { Name = "Статус", Type = "enum" }, new ReportColumnsUI { Name = "Тип", Type = "enum" }, new ReportColumnsUI { Name = "Поезд", Type = "string" }, new ReportColumnsUI { Name = "Вагон", Type = "string" }, new ReportColumnsUI { Name = "Оборудование", Type = "string" }, new ReportColumnsUI { Name = "Инициатор", Type = "string" }, new ReportColumnsUI { Name = "Дата", Type = "date" }, new ReportColumnsUI { Name = "Повторы", Type = "int" } }; var ret = new ReportPagingUI { Columns = columns.ToArray(), Rows = result.Select(row => new List <string> { row.Id.ToString(), ((int)(row.Status?.Status ?? Model.Enums.TaskStatus.New)).ToString(), ((int)row.TaskType).ToString(), row.Carriage.Train.Name, //row.Carriage.Train.Name + " " + row.Carriage.Number, row.Carriage.Serial + " (" + row.Carriage.Number + ", " + row.CarriageTypeString + ")", row.EquipmentModel.Equipment.Name, row.User.Name, row.CreateDate.ToString(), row.Repeats.ToString() }) .Select(vals => new ReportValuesUI { Values = vals.ToArray() }).ToArray(), Total = count }; return(ret); } }
public async Task <ReportPagingUI> GetReportEquipmentTaskHistory(int skip, int limit, string filter, string orderby) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { CreateFilter(filter, out string sqlfilter); CreateOrderBy(orderby, out string sqlorderby); var result = (await conn.QueryAsync <TrainTaskReport1>( $"{ReportCommon.Sql2Select} {ReportCommon.Sql2Common} {sqlfilter} {sqlorderby} {ReportCommon.Sql2End}", new[] { typeof(TrainTaskReport1), typeof(Carriage), typeof(Train), typeof(EquipmentModel), typeof(Equipment) }, objects => { var task = (TrainTaskReport1)objects[0]; task.Carriage = (Carriage)objects[1]; task.Carriage.Train = (Train)objects[2]; task.EquipmentModel = (EquipmentModel)objects[3]; task.EquipmentModel.Equipment = (Equipment)objects[4]; return(task); }, new { skip = skip, limit = limit } )).ToArray(); var sqlc = $"{TaskCommon.SqlCount} {TaskCommon.SqlCommon}"; var count = conn.ExecuteScalar <int>(sqlc); var columns = new List <ReportColumnsUI> { new ReportColumnsUI { Name = "Номер", Type = "int", Alias = "TaskId" }, new ReportColumnsUI { Name = "Состав", Type = "string", Alias = "TrainName" }, new ReportColumnsUI { Name = "Вагон", Type = "string", Alias = "CarriageName" }, new ReportColumnsUI { Name = "Наименование оборудования", Type = "string", Alias = "EquipmentName" }, new ReportColumnsUI { Name = "Тип инцидента", Type = "string", Alias = "TaskTypeId" }, new ReportColumnsUI { Name = "Время создания инцидента", Type = "date", Alias = "CreateDate" }, new ReportColumnsUI { Name = "Время закрытия инцидента", Type = "date", Alias = "CloseDate" }, }; var sqlRmodel = new ModelRepository(_logger); foreach (var item in result) { var carriageTypeId = (int)(await sqlRmodel.GetById(item.Carriage.ModelId)).ModelType; if (carriageTypeId == 0) { item.CarriageTypeString = Other.Other.CarriageTypeString.HeadVagon; } if (carriageTypeId == 1) { item.CarriageTypeString = Other.Other.CarriageTypeString.TractionVagon; } if (carriageTypeId == 2) { item.CarriageTypeString = Other.Other.CarriageTypeString.TrailerVagon; } } var ret = new ReportPagingUI { Columns = columns.ToArray(), Rows = result.Select(row => new List <string> { row.Id.ToString(), row.Carriage.Train.Name, row.Carriage.Serial + " (" + row.Carriage.Number + ", " + row.CarriageTypeString + ")", row.EquipmentModel.Equipment.Name, _TaskTypeEnumToString(row.TaskType), row.CreateDate.ToString(), row.UpdateDate.ToString() }) .Select(vals => new ReportValuesUI { Values = vals.ToArray() }).ToArray(), Total = count }; return(ret); } }