forked from ddrilling/AsbCloudServer
386 lines
15 KiB
C#
386 lines
15 KiB
C#
using AsbCloudApp.Data;
|
||
using AsbCloudApp.Exceptions;
|
||
using AsbCloudApp.Services;
|
||
using AsbCloudDb.Model;
|
||
using ClosedXML.Excel;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Threading;
|
||
using System.Threading.Tasks;
|
||
using AsbCloudApp.Data.WellOperation;
|
||
|
||
namespace AsbCloudInfrastructure.Services.WellOperationService;
|
||
|
||
public class ScheduleReportService : IScheduleReportService
|
||
{
|
||
private readonly IWellOperationService wellOperationService;
|
||
private readonly IWellService wellService;
|
||
const string sheetNameSchedule = "Сетевой график";
|
||
const string sheetNameSchedulePlan = "План";
|
||
const string sheetNameScheduleFact = "Факт";
|
||
const string sheetNameSchedulePrediction = "Прогноз";
|
||
const string sheetNameTvd = "ГГД";
|
||
const int maxChartsToWrap = 88;
|
||
|
||
public ScheduleReportService(IWellOperationService wellOperationService, IWellService wellService)
|
||
{
|
||
this.wellOperationService = wellOperationService;
|
||
this.wellService = wellService;
|
||
}
|
||
|
||
public async Task<Stream> MakeReportAsync(int idWell, CancellationToken token = default)
|
||
{
|
||
var tvd = await wellOperationService.GetTvdAsync(idWell, token);
|
||
|
||
var well = await wellService.GetOrDefaultAsync(idWell, token)
|
||
?? throw new ArgumentInvalidException(nameof(idWell), "idWell doesn`t exist");
|
||
|
||
var ecxelTemplateStream = GetExcelTemplateStream();
|
||
using var workbook = new XLWorkbook(ecxelTemplateStream);
|
||
FillScheduleSheetToWorkbook(workbook, tvd, well);
|
||
FillTvdSheetToWorkbook(workbook, tvd, well);
|
||
MemoryStream memoryStream = new MemoryStream();
|
||
workbook.SaveAs(memoryStream, new SaveOptions { });
|
||
memoryStream.Seek(0, SeekOrigin.Begin);
|
||
return memoryStream;
|
||
}
|
||
|
||
private static void FillScheduleSheetToWorkbook(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
|
||
{
|
||
FillScheduleSheet(workbook, tvd, well);
|
||
|
||
var tvdList = tvd.ToList();
|
||
var plans = tvd
|
||
.Where(t => t.Plan is not null)
|
||
.Select(t => t.Plan!)
|
||
.OrderBy(t => t.DateStart);
|
||
FillCurrentScheduleSheet(workbook, plans, sheetNameSchedulePlan);
|
||
|
||
var facts = tvd
|
||
.Where(t => t.Fact is not null)
|
||
.Select(t => t.Fact!)
|
||
.OrderBy(t => t.DateStart);
|
||
FillCurrentScheduleSheet(workbook, facts, sheetNameScheduleFact);
|
||
|
||
var predictions = tvd
|
||
.Where(t => t.Predict is not null)
|
||
.Select(t => t.Predict!)
|
||
.OrderBy(t => t.DateStart);
|
||
FillCurrentScheduleSheet(workbook, predictions, sheetNameSchedulePrediction);
|
||
}
|
||
|
||
private static void FillCurrentScheduleSheet(XLWorkbook workbook, IEnumerable<WellOperationDto> tvdList, string sheetName)
|
||
{
|
||
var sheet = workbook.GetWorksheet(sheetName);
|
||
|
||
const int headerRowsCount = 6;
|
||
|
||
const int columnRowNumber = 2;
|
||
const int columnCaption = 3;
|
||
const int columnWellDepthStart = 4;
|
||
const int columnWellDepthEnd = 5;
|
||
const int columnDuration = 6;
|
||
const int columnDateStart = 7;
|
||
const int columnDateEnd = 8;
|
||
|
||
int i = 1;
|
||
foreach (var tvdItem in tvdList)
|
||
{
|
||
var row = sheet.Row(i + headerRowsCount);
|
||
SetCell(row, columnRowNumber, $"{i}");
|
||
SetCell(row, columnCaption, $"{tvdItem.OperationCategoryName} {tvdItem.CategoryInfo}".Trim());
|
||
SetCell(row, columnWellDepthStart, tvdItem.DepthStart);
|
||
SetCell(row, columnWellDepthEnd, tvdItem.DepthEnd);
|
||
SetCell(row, columnDuration, tvdItem.DurationHours);
|
||
SetCell(row, columnDateStart, tvdItem.DateStart);
|
||
SetCell(row, columnDateEnd, tvdItem.DateStart.AddHours(tvdItem.DurationHours));
|
||
i++;
|
||
}
|
||
}
|
||
|
||
private static void FillScheduleSheet(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
|
||
{
|
||
var sheet = workbook.GetWorksheet(sheetNameSchedule);
|
||
|
||
const int headerRowsCount = 6;
|
||
const int rowTitle = 3;
|
||
|
||
const int columnRowNumber = 2;
|
||
const int columnCaption = 3;
|
||
const int columnWellDepthStartPlan = 4;
|
||
const int columnWellDepthStartFact = 5;
|
||
const int columnWellDepthStartPredict = 6;
|
||
const int columnWellDepthEndPlan = 7;
|
||
const int columnWellDepthEndFact = 8;
|
||
const int columnWellDepthEndPredict = 9;
|
||
const int columnDeltaWellDepthPerDay = 10;
|
||
const int columnDurationPlan = 11;
|
||
const int columnDurationFact = 12;
|
||
const int columnDurationPredict = 13;
|
||
const int columnDateStartPlan = 14;
|
||
const int columnDateStartFact = 15;
|
||
const int columnDateStartPredict = 16;
|
||
const int columnDateEndPlan = 17;
|
||
const int columnDateEndFact = 18;
|
||
const int columnDateEndPredict = 19;
|
||
const int columnGuilty = 20;
|
||
const int columnNpt = 21;
|
||
|
||
var subTitle = $"на строительство скважины №{well.Caption}, куст: {well.Cluster}, м/р: {well.Deposit}";
|
||
sheet.Row(rowTitle).Cell(3).SetCellValue(subTitle);
|
||
|
||
var tvdList = tvd.ToList();
|
||
var facts = tvd
|
||
.Where(t => t.Fact is not null)
|
||
.Select(t => t.Fact!)
|
||
.ToList();
|
||
|
||
DateTimeOffset lastFactDate = default;
|
||
var lastFactI = 0;
|
||
|
||
int i = 0;
|
||
for (; i < tvdList.Count; i++)
|
||
{
|
||
var tvdItem = tvdList[i];
|
||
var operation = tvdItem.Fact ?? tvdItem.Plan;
|
||
if (operation is null)
|
||
continue;
|
||
|
||
var row = sheet.Row(1 + i + headerRowsCount);
|
||
|
||
SetCell(row, columnRowNumber, $"{1 + i}");
|
||
SetCell(row, columnCaption, $"{operation.OperationCategoryName} {operation.CategoryInfo}".Trim());
|
||
|
||
SetCell(row, columnWellDepthStartPlan, tvdItem.Plan?.DepthStart);
|
||
SetCell(row, columnWellDepthStartFact, tvdItem.Fact?.DepthStart);
|
||
SetCell(row, columnWellDepthStartPredict, tvdItem.Predict?.DepthStart);
|
||
|
||
SetCell(row, columnWellDepthEndPlan, tvdItem.Plan?.DepthEnd);
|
||
SetCell(row, columnWellDepthEndFact, tvdItem.Fact?.DepthEnd);
|
||
SetCell(row, columnWellDepthEndPredict, tvdItem.Predict?.DepthEnd);
|
||
|
||
SetCell(row, columnDeltaWellDepthPerDay, null);
|
||
if (tvdItem.Fact is not null)
|
||
{
|
||
var fact = tvdItem.Fact;
|
||
if (lastFactDate == default)
|
||
lastFactDate = fact.DateStart;
|
||
|
||
if (i > 0 && fact.DateStart.DayOfYear != lastFactDate.DayOfYear)
|
||
{
|
||
var daylyOperations = facts
|
||
.Where(t => t.DateStart >= lastFactDate && t.DateStart < fact.DateStart);
|
||
if (daylyOperations.Any())
|
||
{
|
||
var depthDayStart = daylyOperations.Min(o => o.DepthStart);
|
||
var depthDayEnd = daylyOperations.Max(o => o.DepthEnd);
|
||
var delta = depthDayEnd - depthDayStart;
|
||
SetCell(sheet.Row(1 + lastFactI + headerRowsCount), columnDeltaWellDepthPerDay, delta);
|
||
lastFactDate = fact.DateStart;
|
||
}
|
||
}
|
||
lastFactI = i;
|
||
}
|
||
|
||
SetCell(row, columnDurationPlan, tvdItem.Plan?.DurationHours);
|
||
SetCell(row, columnDurationFact, tvdItem.Fact?.DurationHours);
|
||
SetCell(row, columnDurationPredict, tvdItem.Predict?.DurationHours);
|
||
|
||
SetCell(row, columnDateStartPlan, tvdItem.Plan?.DateStart);
|
||
SetCell(row, columnDateStartFact, tvdItem.Fact?.DateStart);
|
||
SetCell(row, columnDateStartPredict, tvdItem.Predict?.DateStart);
|
||
|
||
SetCell(row, columnDateEndPlan, tvdItem.Plan?.DateStart.AddHours(tvdItem.Plan?.DurationHours ?? 0));
|
||
SetCell(row, columnDateEndFact, tvdItem.Fact?.DateStart.AddHours(tvdItem.Fact?.DurationHours ?? 0));
|
||
SetCell(row, columnDateEndPredict, tvdItem.Predict?.DateStart.AddHours(tvdItem.Predict?.DurationHours ?? 0));
|
||
|
||
if (tvdItem.Fact is not null && WellOperationCategory.NonProductiveTimeSubIds.Contains(tvdItem.Fact.IdCategory))
|
||
{
|
||
SetCell(row, columnGuilty, tvdItem.Fact.Comment);
|
||
SetCell(row, columnNpt, tvdItem.Fact.DurationHours);
|
||
row.Row(columnRowNumber, columnNpt).Style.Fill.BackgroundColor = XLColor.Red;
|
||
}
|
||
else
|
||
{
|
||
SetCell(row, columnGuilty, null);
|
||
SetCell(row, columnNpt, null);
|
||
}
|
||
}
|
||
|
||
var rowNumSummary = 1 + i + headerRowsCount;
|
||
var rowNumStart = 1 + headerRowsCount;
|
||
var rowNumEnd = i + headerRowsCount;
|
||
|
||
string MakeRangeFunction(string funcName, int column)
|
||
=> $"={funcName}({GetColunmLetter(column)}{rowNumStart}:{GetColunmLetter(column)}{rowNumEnd})";
|
||
|
||
IXLCell AddRangeFormula(IXLRow row, string funcName, int column)
|
||
{
|
||
var cell = row.Cell(column);
|
||
cell.FormulaA1 = MakeRangeFunction(funcName, column);
|
||
return cell;
|
||
}
|
||
|
||
var rowSummary = sheet.Row(rowNumSummary);
|
||
rowSummary.Style.Font.Bold = true;
|
||
rowSummary.Cell(columnCaption).SetCellValue("Итого:");
|
||
|
||
AddRangeFormula(rowSummary, "sum", columnDeltaWellDepthPerDay);
|
||
AddRangeFormula(rowSummary, "sum", columnDurationPlan);
|
||
AddRangeFormula(rowSummary, "sum", columnDurationFact);
|
||
var cell = AddRangeFormula(rowSummary, "max", columnDateEndPlan);
|
||
SetDateTime(cell);
|
||
cell = AddRangeFormula(rowSummary, "max", columnDateEndFact);
|
||
SetDateTime(cell);
|
||
AddRangeFormula(rowSummary, "sum", columnNpt);
|
||
SetBorder(rowSummary.Cells(true).Style);
|
||
|
||
var rowSummary2 = sheet.Row(rowNumSummary + 1);
|
||
rowSummary2.Style.NumberFormat.Format = "0,00";
|
||
rowSummary2.Cell(columnCaption).SetCellValue("в сутках:");
|
||
rowSummary2.Cell(columnDurationPlan).FormulaA1 = $"={GetColunmLetter(columnDurationPlan)}{rowNumSummary}/24";
|
||
SetNumber(rowSummary2.Cell(columnDurationPlan));
|
||
rowSummary2.Cell(columnDurationFact).FormulaA1 = $"={GetColunmLetter(columnDurationFact)}{rowNumSummary}/24";
|
||
SetNumber(rowSummary2.Cell(columnDurationFact));
|
||
rowSummary2.Cell(columnNpt).FormulaA1 = $"={GetColunmLetter(columnNpt)}{rowNumSummary}/24";
|
||
SetNumber(rowSummary2.Cell(columnNpt));
|
||
SetBorder(rowSummary2.Cells(true).Style);
|
||
}
|
||
|
||
private static void FillTvdSheetToWorkbook(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
|
||
{
|
||
var sheet = workbook.GetWorksheet(sheetNameTvd);
|
||
|
||
const int rowTitle = 2;
|
||
const int rowSubtitle = 3;
|
||
const int colTitle = 5;
|
||
|
||
const int rowTopStatTitle = 2;
|
||
const int colTopStatvalue = 10;
|
||
|
||
const int colBottomStatvalue = 3;
|
||
const int rowStartDateFact = 43;
|
||
const int rowEndDatePlan = 44;
|
||
const int rowEndDateFact = 45;
|
||
|
||
sheet.Row(rowSubtitle).Cell(colTitle).Value
|
||
= $"скважины №{well.Caption}, куст: {well.Cluster}, м/р: {well.Deposit}";
|
||
|
||
SetCell(sheet.Row(rowTitle), colTopStatvalue, DateTime.Now);
|
||
|
||
var Plan = tvd.Where(t => t.Plan is not null)
|
||
.Select(t => t.Plan);
|
||
var Fact = tvd.Where(t => t.Fact is not null)
|
||
.Select(t => t.Fact);
|
||
var Predict = tvd.Where(t => t.Predict is not null)
|
||
.Select(t => t.Predict);
|
||
|
||
var startDateFact = Fact.FirstOrDefault()?.DateStart;
|
||
var planLast = Plan.LastOrDefault();
|
||
var factLast = Fact.LastOrDefault();
|
||
var predictLast = Predict.LastOrDefault();
|
||
|
||
static DateTime GetEndDate(WellOperationDto operation)
|
||
=> operation is not null
|
||
? operation.DateStart.Date.AddHours(operation.DurationHours)
|
||
: default;
|
||
|
||
var endDatePlan = planLast is not null ? GetEndDate(planLast) : default;
|
||
var endDateFact = factLast is not null ? GetEndDate(factLast) : default;
|
||
var endDatePredict = predictLast is not null ? GetEndDate(predictLast) : default;
|
||
|
||
var endDate = endDatePredict > endDateFact
|
||
? endDatePredict
|
||
: endDateFact;
|
||
|
||
if (startDateFact is not null)
|
||
{
|
||
SetCell(sheet.Row(rowStartDateFact), colBottomStatvalue, startDateFact);
|
||
SetCell(sheet.Row(rowEndDatePlan), colBottomStatvalue, endDatePlan);
|
||
SetCell(sheet.Row(rowEndDateFact), colBottomStatvalue, endDate);
|
||
if (endDate != default)
|
||
{
|
||
var deltaEndDate = (endDatePlan - endDate).TotalDays;
|
||
SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue, Math.Abs(deltaEndDate));
|
||
if (deltaEndDate >= 0)
|
||
SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue - 1, "+")
|
||
.Style.Font.SetFontColor(XLColor.Green);
|
||
else
|
||
SetCell(sheet.Row(rowTopStatTitle + 1), colTopStatvalue - 1, "-")
|
||
.Style.Font.SetFontColor(XLColor.Red);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static string GetColunmLetter(int columnNumber)
|
||
{
|
||
string letter = "";
|
||
|
||
while (columnNumber > 0)
|
||
{
|
||
int modulo = (columnNumber - 1) % 26;
|
||
letter = Convert.ToChar('A' + modulo) + letter;
|
||
columnNumber = (columnNumber - modulo) / 26;
|
||
}
|
||
|
||
return letter;
|
||
}
|
||
|
||
private static IXLStyle SetBorder(IXLStyle style)
|
||
{
|
||
style.Border.RightBorder = XLBorderStyleValues.Thin;
|
||
style.Border.LeftBorder = XLBorderStyleValues.Thin;
|
||
style.Border.TopBorder = XLBorderStyleValues.Thin;
|
||
style.Border.BottomBorder = XLBorderStyleValues.Thin;
|
||
style.Border.InsideBorder = XLBorderStyleValues.Thin;
|
||
return style;
|
||
}
|
||
|
||
private static IXLCell SetDateTime(IXLCell cell)
|
||
{
|
||
cell.Style.DateFormat.Format = "DD.MM.YYYY HH:MM:SS";
|
||
return cell;
|
||
}
|
||
|
||
private static IXLCell SetNumber(IXLCell cell)
|
||
{
|
||
cell.Style.NumberFormat.Format = "0.00";
|
||
return cell;
|
||
}
|
||
|
||
private static IXLCell SetCell(IXLRow row, int colunm, object? value)
|
||
{
|
||
var cell = row.Cell(colunm);
|
||
cell.SetCellValue(value);
|
||
|
||
SetBorder(cell.Style);
|
||
cell.Style.Alignment.WrapText = true;
|
||
|
||
if (value is string valueString && valueString.Length > maxChartsToWrap)
|
||
{
|
||
var baseHeight = row.Height;
|
||
row.Height = 0.82d * baseHeight * Math.Ceiling(1d + valueString.Length / maxChartsToWrap);
|
||
}
|
||
|
||
if (value is DateTime)
|
||
{
|
||
SetDateTime(cell);
|
||
}
|
||
else if (value is IFormattable)
|
||
{
|
||
SetNumber(cell);
|
||
}
|
||
|
||
return cell;
|
||
}
|
||
|
||
private static Stream GetExcelTemplateStream()
|
||
{
|
||
var stream = System.Reflection.Assembly.GetExecutingAssembly()
|
||
.GetManifestResourceStream("AsbCloudInfrastructure.Services.WellOperationService.ScheduleReportTemplate.xlsx")!;
|
||
return stream;
|
||
}
|
||
}
|