DD.WellWorkover.Cloud/AsbCloudInfrastructure/Services/WellOperationService/ScheduleReportService.cs

386 lines
15 KiB
C#
Raw Permalink Normal View History

using AsbCloudApp.Data;
2023-04-13 15:34:16 +05:00
using AsbCloudApp.Exceptions;
2022-03-17 16:56:13 +05:00
using AsbCloudApp.Services;
2022-12-09 18:32:18 +05:00
using AsbCloudDb.Model;
2022-03-17 16:56:13 +05:00
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;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
namespace AsbCloudInfrastructure.Services.WellOperationService;
public class ScheduleReportService : IScheduleReportService
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
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)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
this.wellOperationService = wellOperationService;
this.wellService = wellService;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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);
}
2024-08-19 10:01:07 +05:00
private static void FillCurrentScheduleSheet(XLWorkbook workbook, IEnumerable<WellOperationDto> tvdList, string sheetName)
{
var sheet = workbook.GetWorksheet(sheetName);
2024-08-19 10:01:07 +05:00
const int headerRowsCount = 6;
2024-08-19 10:01:07 +05:00
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;
2024-08-19 10:01:07 +05:00
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++;
}
2024-08-19 10:01:07 +05:00
}
2024-08-19 10:01:07 +05:00
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++)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
var tvdItem = tvdList[i];
var operation = tvdItem.Fact ?? tvdItem.Plan;
if (operation is null)
continue;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
var row = sheet.Row(1 + i + headerRowsCount);
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
SetCell(row, columnRowNumber, $"{1 + i}");
SetCell(row, columnCaption, $"{operation.OperationCategoryName} {operation.CategoryInfo}".Trim());
2024-08-19 10:01:07 +05:00
SetCell(row, columnWellDepthStartPlan, tvdItem.Plan?.DepthStart);
SetCell(row, columnWellDepthStartFact, tvdItem.Fact?.DepthStart);
SetCell(row, columnWellDepthStartPredict, tvdItem.Predict?.DepthStart);
2024-08-19 10:01:07 +05:00
SetCell(row, columnWellDepthEndPlan, tvdItem.Plan?.DepthEnd);
SetCell(row, columnWellDepthEndFact, tvdItem.Fact?.DepthEnd);
SetCell(row, columnWellDepthEndPredict, tvdItem.Predict?.DepthEnd);
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
SetCell(row, columnDeltaWellDepthPerDay, null);
if (tvdItem.Fact is not null)
{
var fact = tvdItem.Fact;
if (lastFactDate == default)
lastFactDate = fact.DateStart;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
if (i > 0 && fact.DateStart.DayOfYear != lastFactDate.DayOfYear)
{
var daylyOperations = facts
.Where(t => t.DateStart >= lastFactDate && t.DateStart < fact.DateStart);
if (daylyOperations.Any())
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
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;
2022-03-17 16:56:13 +05:00
}
}
2024-08-19 10:01:07 +05:00
lastFactI = i;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
SetCell(row, columnDurationPlan, tvdItem.Plan?.DurationHours);
SetCell(row, columnDurationFact, tvdItem.Fact?.DurationHours);
SetCell(row, columnDurationPredict, tvdItem.Predict?.DurationHours);
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
SetCell(row, columnDateStartPlan, tvdItem.Plan?.DateStart);
SetCell(row, columnDateStartFact, tvdItem.Fact?.DateStart);
SetCell(row, columnDateStartPredict, tvdItem.Predict?.DateStart);
2024-08-19 10:01:07 +05:00
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));
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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;
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
else
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
SetCell(row, columnGuilty, null);
SetCell(row, columnNpt, null);
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
var rowNumSummary = 1 + i + headerRowsCount;
var rowNumStart = 1 + headerRowsCount;
var rowNumEnd = i + headerRowsCount;
2022-04-11 18:00:34 +05:00
2024-08-19 10:01:07 +05:00
string MakeRangeFunction(string funcName, int column)
=> $"={funcName}({GetColunmLetter(column)}{rowNumStart}:{GetColunmLetter(column)}{rowNumEnd})";
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
IXLCell AddRangeFormula(IXLRow row, string funcName, int column)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
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);
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
private static void FillTvdSheetToWorkbook(XLWorkbook workbook, IEnumerable<PlanFactPredictBase<WellOperationDto>> tvd, WellDto well)
{
var sheet = workbook.GetWorksheet(sheetNameTvd);
2022-04-11 18:00:34 +05:00
2024-08-19 10:01:07 +05:00
const int rowTitle = 2;
const int rowSubtitle = 3;
const int colTitle = 5;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
const int rowTopStatTitle = 2;
const int colTopStatvalue = 10;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
const int colBottomStatvalue = 3;
const int rowStartDateFact = 43;
const int rowEndDatePlan = 44;
const int rowEndDateFact = 45;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
sheet.Row(rowSubtitle).Cell(colTitle).Value
= $"скважины №{well.Caption}, куст: {well.Cluster}, м/р: {well.Deposit}";
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
SetCell(sheet.Row(rowTitle), colTopStatvalue, DateTime.Now);
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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);
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
var startDateFact = Fact.FirstOrDefault()?.DateStart;
var planLast = Plan.LastOrDefault();
var factLast = Fact.LastOrDefault();
var predictLast = Predict.LastOrDefault();
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
static DateTime GetEndDate(WellOperationDto operation)
=> operation is not null
? operation.DateStart.Date.AddHours(operation.DurationHours)
: default;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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;
2022-04-11 18:00:34 +05:00
2024-08-19 10:01:07 +05:00
var endDate = endDatePredict > endDateFact
? endDatePredict
: endDateFact;
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
if (startDateFact is not null)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
SetCell(sheet.Row(rowStartDateFact), colBottomStatvalue, startDateFact);
SetCell(sheet.Row(rowEndDatePlan), colBottomStatvalue, endDatePlan);
SetCell(sheet.Row(rowEndDateFact), colBottomStatvalue, endDate);
if (endDate != default)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
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);
2022-03-17 16:56:13 +05:00
}
}
2024-08-19 10:01:07 +05:00
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
private static string GetColunmLetter(int columnNumber)
{
string letter = "";
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
while (columnNumber > 0)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
int modulo = (columnNumber - 1) % 26;
letter = Convert.ToChar('A' + modulo) + letter;
columnNumber = (columnNumber - modulo) / 26;
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
return letter;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
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;
}
2022-04-11 18:00:34 +05:00
2024-08-19 10:01:07 +05:00
private static IXLCell SetDateTime(IXLCell cell)
{
cell.Style.DateFormat.Format = "DD.MM.YYYY HH:MM:SS";
return cell;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
private static IXLCell SetNumber(IXLCell cell)
{
cell.Style.NumberFormat.Format = "0.00";
return cell;
}
2022-03-17 16:56:13 +05:00
2024-08-19 10:01:07 +05:00
private static IXLCell SetCell(IXLRow row, int colunm, object? value)
{
var cell = row.Cell(colunm);
cell.SetCellValue(value);
2022-04-11 18:00:34 +05:00
2024-08-19 10:01:07 +05:00
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);
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
if (value is DateTime)
{
SetDateTime(cell);
}
else if (value is IFormattable)
2022-03-17 16:56:13 +05:00
{
2024-08-19 10:01:07 +05:00
SetNumber(cell);
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
return cell;
2022-03-17 16:56:13 +05:00
}
2024-08-19 10:01:07 +05:00
private static Stream GetExcelTemplateStream()
{
var stream = System.Reflection.Assembly.GetExecutingAssembly()
.GetManifestResourceStream("AsbCloudInfrastructure.Services.WellOperationService.ScheduleReportTemplate.xlsx")!;
return stream;
}
2022-03-17 16:56:13 +05:00
}