DD.WellWorkover.Cloud/AsbCloudInfrastructure/Services/DetectOperations/DetectedOperationExportService.cs

using AsbCloudDb.Model;
using ClosedXML.Excel;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using AsbCloudApp.Data.DetectedOperation;
using AsbCloudApp.Repositories;
using Microsoft.AspNetCore.Http.Extensions;
using AsbCloudApp.Exceptions;
using AsbCloudApp.Services;
using AsbCloudApp.Data;
using AsbCloudApp.Data.WellOperation;
using AsbCloudApp.Requests;
using AsbCloudInfrastructure.Services.DetectOperations.Detectors;

namespace AsbCloudInfrastructure.Services.DetectOperations;

public class DetectedOperationExportService
{
	private readonly IWellService wellService;
	private readonly IWellOperationCategoryRepository wellOperationCategoryRepository;
	private readonly IDetectedOperationRepository detectedOperationRepository;
	private const int headerRowsCount = 1;

	private const string cellDepositName = "B1";
	private const string cellClusterName = "B2";
	private const string cellWellName = "B3";
	private const string cellDeltaDate = "H2";

	private const int columnOperationName = 1;
	private const int columnDateStart = 2;
	private const int columnDateEnd = 3;
	private const int columnDuration = 4;
	private const int columnDepthStart = 5;
	private const int columnDepthEnd = 6;
	private const int columnDeltaDepth = 7;
	private const int columnDepth = 8;
	private const int columnIdReasonOfEnd = 9;
	private const int columnComment = 10;

	public DetectedOperationExportService(IWellService wellService,
		IWellOperationCategoryRepository wellOperationCategoryRepository,
		IDetectedOperationRepository detectedOperationRepository)
	{
		this.wellService = wellService;
		this.wellOperationCategoryRepository = wellOperationCategoryRepository;
		this.detectedOperationRepository = detectedOperationRepository;
	}

	/// <summary>
	/// Экспорт excel файла с операциями по скважине
	/// </summary>
	/// <param name="idWell">ключ скважины</param>
	/// <param name="host">хост</param>
	/// <param name="token"></param>
	/// <returns></returns>
	/// <exception cref="ArgumentInvalidException"></exception>
	public async Task<Stream> ExportAsync(int idWell, string host, CancellationToken token)
	{
		var well = await wellService.GetOrDefaultAsync(idWell, token);

		if (well is null)
			throw new ArgumentInvalidException(nameof(idWell), $"Well {idWell} does not exist");

		if (!well.IdTelemetry.HasValue)
			throw new ArgumentInvalidException(nameof(idWell), $"Well {idWell} has no telemetry");

		var request = new DetectedOperationByTelemetryRequest
		{
			IdTelemetry = well.IdTelemetry.Value
		};

		var operations = await detectedOperationRepository.Get(request, token);

		return await GenerateExcelFileStreamAsync(well, host, operations, token);
	}

	private async Task<Stream> GenerateExcelFileStreamAsync(WellDto well,
		string host,
		IEnumerable<DetectedOperationDto> operationDetectorResults,
		CancellationToken cancellationToken)
	{
		using var excelTemplateStream = await GetExcelTemplateStreamAsync(cancellationToken);

		using var workbook = new XLWorkbook(excelTemplateStream);

		AddToWorkbook(workbook, well, host, operationDetectorResults);

		MemoryStream memoryStream = new MemoryStream();
		workbook.SaveAs(memoryStream, new SaveOptions { });
		memoryStream.Seek(0, SeekOrigin.Begin);
		return memoryStream;
	}

	private void AddToWorkbook(XLWorkbook workbook, WellDto well, string host, IEnumerable<DetectedOperationDto> operations)
	{
		const string sheetName = "Операции";

		if (!operations.Any())
			return;

		var sheet = workbook.GetWorksheet(sheetName);

		var orderedOperations = operations
			.OrderBy(x => x.DateStart)
			.ThenBy(x => x.DepthStart).ToArray();

		AddToSheet(sheet, well, host, orderedOperations);
	}

	private void AddToSheet(IXLWorksheet sheet, WellDto well, string host, IList<DetectedOperationDto> operations)
	{
		var wellOperationCategories = wellOperationCategoryRepository.Get(true);

		sheet.Cell(cellDepositName).SetCellValue(well.Deposit);
		sheet.Cell(cellClusterName).SetCellValue(well.Cluster);
		sheet.Cell(cellWellName).SetCellValue(well.Caption);

		var deltaDate = operations.Max(o => o.DateEnd - o.DateStart);

		sheet.Cell(cellDeltaDate).SetCellValue(deltaDate);

		for (int i = 0; i < operations.Count; i++)
		{
			var current = operations[i];
			var dateStart = current.DateStart.DateTime;
			var dateEnd = current.DateEnd.DateTime;

			var row = sheet.Row(5 + i + headerRowsCount);

			var categoryName = GetCategoryName(wellOperationCategories, current);

			row.Cell(columnDateStart).SetCellValue(dateStart);
			row.Cell(columnOperationName).SetCellValue(categoryName);
			row.Cell(columnDateEnd).SetCellValue(dateEnd);
			row.Cell(columnDuration).SetCellValue((dateEnd - dateStart).TotalMinutes);
			row.Cell(columnDepthStart).SetCellValue(current.DepthStart);
			row.Cell(columnDepthEnd).SetCellValue(current.DepthEnd);
			row.Cell(columnDepth).SetCellValue(current.DepthEnd - current.DepthStart);

			if (current.ExtraData.TryGetValue("IdReasonOfEnd", out object? idReasonOfEndObject)
				&& idReasonOfEndObject is int idReasonOfEnd)
			{
				var reasonOfEnd = GetIdReasonOfEnd(idReasonOfEnd);
				row.Cell(columnIdReasonOfEnd).SetCellValue(reasonOfEnd);
			}

			var query = new QueryBuilder();
			query.Add("end", dateStart.AddSeconds(1800 * 0.9).ToString("yyyy-MM-ddTHH:mm:ss.fff"));
			query.Add("range", "1800");

			var link = $"{host}/well/{well.Id}/telemetry/monitoring{query}";
			row.Cell(columnDateStart).SetHyperlink(link);

			var deltaDepth = i > 0 && i + 1 < operations.Count
				? current.DepthStart - operations[i - 1].DepthEnd
				: 0;

			row.Cell(columnDeltaDepth).SetCellValue(deltaDepth);

			var comment = CreateComment(operations[i]);
			row.Cell(columnComment).SetCellValue(comment);
		}
	}

	private static string GetCategoryName(IEnumerable<WellOperationCategoryDto> wellOperationCategories, DetectedOperationDto current)
	{
		var idCategory = current.IdCategory;
		if (idCategory == WellOperationCategory.IdSlide && current.EnabledSubsystems.IsAutoOscillation)
			return "Бурение в слайде с осцилляцией";

		var category = wellOperationCategories.FirstOrDefault(o => o.Id == current.IdCategory);

		if (category is not null)
			return category.Name;

		return $"Операция №{idCategory}";
	}

	private static string GetIdReasonOfEnd(int idReasonOfEnd)
		=> idReasonOfEnd switch
		{
			0 => "Не определена",
			1 => "Не определено начало операции",
			101 => "Разница глубин забоя и положением долота",
			300 => "Низкое давление",
			301 => "Высокое давление",
			700 => "Изменение глубины долота и осевая нагрузка < веса на крюке",
			_ => idReasonOfEnd.ToString($"Причина № {idReasonOfEnd}"),
		};

	private async Task<Stream> GetExcelTemplateStreamAsync(CancellationToken cancellationToken)
	{
		string resourceName = Assembly.GetExecutingAssembly()
			.GetManifestResourceNames()
			.FirstOrDefault(n => n.EndsWith("DetectOperations.xlsx"))!;

		using var stream = Assembly.GetExecutingAssembly()
			.GetManifestResourceStream(resourceName)!;

		var memoryStream = new MemoryStream();
		await stream.CopyToAsync(memoryStream, cancellationToken);
		memoryStream.Position = 0;

		return memoryStream;
	}

	private static string CreateComment(DetectedOperationDto operation)
	{
		switch (operation.IdCategory)
		{
			case WellOperationCategory.IdRotor:
			case WellOperationCategory.IdSlide:
				var comment = "";
				if (operation.ExtraData.TryGetValue(DetectorDrilling.ExtraDataKeyAvgRotorSpeed, out object? oAvgRotorSpeed))
					comment += $"Средняя скорость оборотов ротора: {oAvgRotorSpeed}\r\n";

				if (operation.ExtraData.TryGetValue(DetectorDrilling.ExtraDataKeyDispersionOfNormalizedRotorSpeed,
						out object? oDispersionOfNormalizedRotorSpeed))
					comment += $"Дисперсия нормированных оборотов ротора: {oDispersionOfNormalizedRotorSpeed}";

				return comment;

			default:
				return string.Empty;
		}
	}
}