//В эксельку кто когда сваливает с депо
public async Task <ReportResponse> EscapeFromDepoReport(ExcelDataRequest input)
{
var sqlRPlaneTrain = new PlanedRouteTrainsRepository(_logger);
var sqlRRoute = new RoutesRepository(_logger);
var sqlRTrain = new TrainRepository(_logger);
var sqlRTrip = new TripsRepository(_logger);
var sqlRUser = new UserRepository(_logger);
var sqlRPlaneStation = new PlanedStationOnTripsRepository(_logger);
var sqlRPlaneBrigade = new PlaneBrigadeTrainsRepository(_logger);
if (input.Date == null)
{
throw new ValidationException("Не задан StartDate");
}
var result = new ReportResponse {
Rows = new List <Row>()
};
result.Columns = new List <Column>
{
new Column("col0", "Маршрут", "string"),
//№ поезда(Походу рейс https://alcodevelop.atlassian.net/browse/CPPK-4)
new Column("col1", "№ поезда", "string"),
new Column("col2", "Машинист", "string"),
new Column("col3", "Состав", "string"),
new Column("col4", "КП", "string"),
};
//Бля ну получим все поезда наверно
var planeTrains = await sqlRPlaneTrain.GetAll();
//За указанные сутки
planeTrains = planeTrains.Where(x => x.Date.Date == input.Date.Date).ToList();
foreach (var planeTrain in planeTrains)
{
var route = await sqlRRoute.ById(planeTrain.RouteId);
var train = await sqlRTrain.ById(planeTrain.TrainId);
//Берем все станки маршрута
var planeStations = await sqlRPlaneStation.ByPlannedRouteTrainId(planeTrain.Id);
var startStation = planeStations.OrderBy(x => x.OutTime).FirstOrDefault();
var trip = new Trip();
if (startStation != null)
{
trip = await sqlRTrip.ById(startStation.TripId);
}
var planeBrigades = await sqlRPlaneBrigade.ByPlanedRouteTrainId(planeTrain.Id);
//TODO брать 1-го юзера и делать машинистом, както неправильно) хДД
User motorman = null;
if (planeBrigades.Count != 0)
{
motorman = await sqlRUser.ById(planeBrigades.First().UserId);
}
var toadd = new Row
{
Id = new RowId(DateTime.Now.Ticks.GetHashCode(), 2),
HasItems = false.ToString(),
ParentId = null,
//Маршрут
Col0 = route.Name,
//№ поезда(Походу рейс https://alcodevelop.atlassian.net/browse/CPPK-4)
Col1 = trip.Name,
//Машинист
Col2 = motorman?.Name,
//Состав
Col3 = train.Name,
//КП
Col4 = startStation?.OutTime.ToStringTimeOnly()
};
result.Rows.Add(toadd);
}
return(result);
}
public async Task <IActionResult> EscapeFromDepo([FromBody] ExcelDataRequest input)
{
if (input == null)
{
throw new ValidationException("Не распарсилось");
}
var service = new ReportTableService(_logger, _mapper);
var toExcel = await service.EscapeFromDepoReport(input);
var depo = "МохнатаяКокаина";
//TODO хз хз...
string sWebRootFolder = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
string sFileName = $@"Отчет_выход_из_депо_{depo}_{input.Date.Date.ToStringDateOnly()}.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
//TODO Уточнить
var workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet($"Отчет_выход_из_депо_{depo}_{input.Date.Date.ToStringDateOnly()}");
//Наверно дефолтная ширина колонок
//excelSheet.SetColumnWidth(3, 256 * 40);
ICellStyle styleAlingCenter = workbook.CreateCellStyle();
styleAlingCenter.Alignment = HorizontalAlignment.Center;
ICellStyle styleRedAlingCenter = workbook.CreateCellStyle();
styleRedAlingCenter.Alignment = HorizontalAlignment.Center;
XSSFFont redFont = (XSSFFont)workbook.CreateFont();
redFont.Color = IndexedColors.Red.Index;
styleRedAlingCenter.SetFont(redFont);
IRow row = excelSheet.CreateRow(0);
var cell = row.CreateCell(0);
cell.CellStyle = styleAlingCenter;
cell.SetCellValue($"Выход из депо {depo} на утро");
//cell.CellStyle.WrapText = true;
excelSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 4));
//2 строчка заголовка
row = excelSheet.CreateRow(1);
cell = row.CreateCell(0);
cell.SetCellValue($"{DateTime.Now.ToStringDateOnly()}г. ({GetStringDayOfWeek(DateTime.Now.DayOfWeek)})");
cell.CellStyle = styleRedAlingCenter;
//cell.CellStyle.WrapText = true;
excelSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));
//Заголовки для данных
row = excelSheet.CreateRow(2);
for (int i = 0; i < toExcel.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(toExcel.Columns[i].Title);
}
var firstDataRowIndex = 3;
for (var index = 0; index < toExcel.Rows.Count; index++)
{
var deRow = toExcel.Rows[index];
row = excelSheet.CreateRow(index + firstDataRowIndex);
row.CreateCell(0).SetCellValue(deRow.Col0);
row.CreateCell(1).SetCellValue(deRow.Col1);
row.CreateCell(2).SetCellValue(deRow.Col2);
row.CreateCell(3).SetCellValue(deRow.Col3);
row.CreateCell(4).SetCellValue(deRow.Col4);
row.CreateCell(5).SetCellValue(deRow.Col5);
row.CreateCell(6).SetCellValue(deRow.Col6);
row.CreateCell(7).SetCellValue(deRow.Col7);
row.CreateCell(8).SetCellValue(deRow.Col8);
row.CreateCell(9).SetCellValue(deRow.Col9);
row.CreateCell(10).SetCellValue(deRow.Col10);
}
//Ширина столбцов
for (int i = 0; i < 11; i++)
{
excelSheet.SetColumnWidth(i, 5000);
}
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
