DD.WellWorkover.Cloud/AsbCloudInfrastructure/Services/WellReport/WellReportExportService.cs

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using AsbCloudApp.Data;
using AsbCloudApp.Data.Subsystems;
using AsbCloudApp.Data.User;
using AsbCloudApp.Data.WellReport;
using AsbCloudApp.Services.WellReport;
using ClosedXML.Excel;

namespace AsbCloudInfrastructure.Services.WellReport;

public class WellReportExportService : IWellReportExportService
{
	private static readonly IDictionary<int, int> PlanOperatingModeRows = new Dictionary<int, int>()
	{
		{ 2, 79 }, // Направление план	
		{ 3, 81 }, // Кондуктор план
		{ 9, 83 }, // Кондуктор 2 план
		{ 1, 85 }, // Пилотный ствол план
		{ 7, 87 }, // Пилотный ствол 2 план
		{ 13, 89 }, // Пилотный ствол 3 план
		{ 4, 91 }, // Эксплуатационная колонна план
		{ 10, 93 }, // Эксплуатационная колонна 2 план
		{ 6, 95 }, // Хвостовик план
		{ 12, 97 }, // Хвостовик 2 план
		{ 18, 99 }, // Хвостовик 3 план
		{ 24, 101 }, // Хвостовик 4 план
		{ 30, 103 }, // Хвостовик 5 план
		{ 34, 105 }, // Хвостовик 6 план
		{ 35, 107 }, // Хвостовик 7 план
		{ 36, 109 }, // Хвостовик 8 план
		{ 37, 111 }, // Хвостовик 9 план
		{ 38, 113 } // Хвостовик 10 план
	};

	private static readonly IDictionary<int, int> FactOperatingModeRows = new Dictionary<int, int>
	{
		{ 2, 80 }, // Направление факт
		{ 3, 82 }, // Кондуктор факт
		{ 9, 84 }, // Кондуктор 2 факт
		{ 1, 86 }, // Пилотный ствол факт 
		{ 7, 88 }, // Пилотный ствол 2 факт
		{ 13, 90 }, // Пилотный ствол 3 факт
		{ 4, 92 }, // Эксплуатационная колонна факт
		{ 10, 94 }, // Эксплуатационная колонна 2 факт
		{ 6, 96 }, // Хвостовик факт
		{ 12, 98 }, // Хвостовик 2 факт
		{ 18, 100 }, // Хвостовик 3 факт
		{ 24, 102 }, // Хвостовик 4 факт
		{ 30, 104 }, // Хвостовик 5 факт
		{ 34, 106 }, // Хвостовик 6 факт
		{ 35, 108 }, // Хвостовик 7 факт
		{ 36, 110 }, // Хвостовик 8 факт
		{ 37, 112 }, // Хвостовик 9 факт
		{ 38, 114 } // Хвостовик 10 факт
	};

	private static readonly IDictionary<int, int> SubsystemRows = new Dictionary<int, int>()
	{
		{ 2, 140 }, // Направление
		{ 3, 141 }, // Кондуктор
		{ 9, 142 }, // Кондуктор 2
		{ 1, 143 }, // Пилотный ствол
		{ 7, 144 }, // Пилотный ствол 2
		{ 13, 145 }, // Пилотный ствол 3
		{ 4, 146 }, // Эксплуатационная колонна
		{ 10, 147 }, // Эксплуатационная колонна 2
		{ 6, 148 }, // Хвостовик
		{ 12, 149 }, // Хвостовик 2
		{ 18, 150 }, // Хвостовик 3
		{ 24, 151 }, // Хвостовик 4
		{ 30, 152 }, // Хвостовик 5
		{ 34, 153 }, // Хвостовик 6
		{ 35, 154 }, // Хвостовик 7
		{ 36, 155 }, // Хвостовик 8
		{ 37, 156 }, // Хвостовик 9
		{ 38, 157 } // Хвостовик 10
	};

	private static readonly IDictionary<int, int> SetpointsRows = new Dictionary<int, int>()
	{
		{ 2, 161 }, // Направление
		{ 3, 162 }, // Кондуктор
		{ 9, 163 }, // Кондуктор 2
		{ 1, 164 }, // Пилотный ствол
		{ 7, 165 }, // Пилотный ствол 2
		{ 13, 166 }, // Пилотный ствол 3
		{ 4, 167 }, // Эксплуатационная колонна
		{ 10, 168 }, // Эксплуатационная колонна 2
		{ 6, 169 }, // Хвостовик
		{ 12, 170 }, // Хвостовик 2
		{ 18, 171 }, // Хвостовик 3
		{ 24, 172 }, // Хвостовик 4
		{ 30, 173 }, // Хвостовик 5
		{ 34, 174 }, // Хвостовик 6
		{ 35, 175 }, // Хвостовик 7
		{ 36, 176 }, // Хвостовик 8
		{ 37, 177 }, // Хвостовик 9
		{ 38, 178 } // Хвостовик 10
	};

	private const string TemplateName = "WellReport.xlsx";
	private const string SheetName = "Отчёт";

	private readonly IWellReportService wellReportService;

	private const string DateFromCell = "D5";
	private const string DateToCell = "E5";
	private const string DaysPlanCell = "D6";
	private const string DaysFactCell = "E6";
	private const string WithoutNtpDaysCell = "D8";
	private const string WellBoreDepthPlanCell = "D12";
	private const string VerticalDepthPlanCell = "E12";
	private const string WellBoreDepthFactCell = "D13";
	private const string VerticalDepthFactCell = "E13";
	private const string WellCell = "I5";
	private const string ClusterCell = "I6";
	private const string DepositCell = "I7";
	private const string CustomerCell = "N5";

	public WellReportExportService(IWellReportService wellReportService)
	{
		this.wellReportService = wellReportService;
	}

	public async Task<(string Name, Stream File)?> ExportAsync(int idWell, CancellationToken token)
	{
		var report = await wellReportService.GetAsync(idWell, token);

		if (report == null)
			return null;

		var stream = Assembly.GetExecutingAssembly().GetTemplateCopyStream(TemplateName);
		using var workbook = new XLWorkbook(stream);

		var sheet = workbook.GetWorksheet(SheetName);

		FillSheet(sheet, report);

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

		var name = $"Отчёт по скважине {report.Well.Caption} куст {report.Well.Cluster}.xlsx";

		return (name, memoryStream);
	}

	private static void FillSheet(IXLWorksheet sheet, WellReportDto report)
	{
		sheet.Cell(DateFromCell).SetCellValue(report.DateFrom);
		sheet.Cell(DateToCell).SetCellValue(report.DateTo);
		sheet.Cell(DaysPlanCell).SetCellValue(report.Days.Plan);
		sheet.Cell(DaysFactCell).SetCellValue(report.Days.Fact);
		sheet.Cell(WithoutNtpDaysCell).SetCellValue(report.WithoutNtpDays);
		sheet.Cell(WellBoreDepthPlanCell).SetCellValue(report.WellBoreDepth.Plan);
		sheet.Cell(WellBoreDepthFactCell).SetCellValue(report.WellBoreDepth.Fact);
		sheet.Cell(VerticalDepthPlanCell).SetCellValue(report.VerticalDepth.Plan);
		sheet.Cell(VerticalDepthFactCell).SetCellValue(report.VerticalDepth.Fact);
		sheet.Cell(WellCell).SetCellValue(report.Well.Caption);
		sheet.Cell(ClusterCell).SetCellValue(report.Well.Cluster);
		sheet.Cell(DepositCell).SetCellValue(report.Well.Deposit);

		var customer = report.Well.Companies.FirstOrDefault(x => x.IdCompanyType == 1);
		sheet.Cell(CustomerCell).SetCellValue(customer?.Caption);

		FillContacts(sheet, report.Contacts);
		FillSectionReports(sheet, report.SectionReports);
		FillDrillerReports(sheet, report.DrillerReports);
	}

	private static void FillContacts(IXLWorksheet sheet, IEnumerable<ContactDto> contacts)
	{
		var positionsByCompanyType = new Dictionary<int, string>()
		{
			{ 7, "Супервайзер" },
			{ 2, "Мастер" },
			{ 3, "Инженер по автоматизации" },
			{ 5, "Инженер по р-рам " },
			{ 6, "Инженер ННБ" },
			{ 14, "Инженер по долотам" },
			{ 4, "Инженер ГТИ" },
			{ 9, "Инженер по цементированию" }
		};

		const int positionColumn = 11;
		const int fullNameColumn = 14;
		const int companyColumn = 16;
		const int phoneColumn = 18;

		contacts = contacts.OrderByDescending(x => x.Id)
			.GroupBy(x => x.IdCompanyType)
			.Select(x => x.First());

		var row = 6;

		foreach (var contact in contacts)
        {
            if (!positionsByCompanyType.TryGetValue(contact.IdCompanyType, out var position))
                continue;

            sheet.Cell(row, positionColumn).SetCellValue(position);
            sheet.Cell(row, fullNameColumn).SetCellValue(contact.FullName);
            sheet.Cell(row, companyColumn).SetCellValue(contact.Company);
            sheet.Cell(row, phoneColumn).SetCellValue(contact.Phone);

            row++;
        }
	}

	private static void FillDrillerReports(IXLWorksheet sheet, IEnumerable<DrillerReportDto> drillerReports)
	{
		drillerReports = drillerReports.OrderBy(x => x.Shedule.DrillStart);

		const int IdSubsystemAPDRotor = 11;
		const int IdSubsystemAPDSlide = 12;
		const int IdSubsystemOscillation = 65536;

		const int fullNameColumn = 1;
		const int drillStartColumn = 5;
		const int drillEndColumn = 6;
		const int shiftStart = 7;
		const int shiftEnd = 8;
		const int kUsageApdRotorColumn = 9;
		const int kUsageApdSlideColumn = 10;
		const int kUsageOscillationColumn = 11;

		var row = 182;

		foreach (var drillingReport in drillerReports)
		{
			sheet.Cell(row, fullNameColumn).SetCellValue(drillingReport.Shedule.Driller?.FullName);
			sheet.Cell(row, drillStartColumn).SetCellValue(drillingReport.Shedule.DrillStart);
			sheet.Cell(row, drillEndColumn).SetCellValue(drillingReport.Shedule.DrillEnd);
			sheet.Cell(row, shiftStart).SetCellValue(drillingReport.Shedule.ShiftStart.ToString());
			sheet.Cell(row, shiftEnd).SetCellValue(drillingReport.Shedule.ShiftEnd.ToString());

			foreach (var subsystemStat in drillingReport.SubsystemsStat)
			{
				switch (subsystemStat.IdSubsystem)
				{
					case IdSubsystemAPDRotor:
						sheet.Cell(row, kUsageApdRotorColumn).SetCellValue(subsystemStat.KUsage);
						break;
					case IdSubsystemAPDSlide:
						sheet.Cell(row, kUsageApdSlideColumn).SetCellValue(subsystemStat.KUsage);
						break;
					case IdSubsystemOscillation:
						sheet.Cell(row, kUsageOscillationColumn).SetCellValue(subsystemStat.KUsage);
						break;
				}
			}

			row++;
		}
	}

	private static void FillSectionReports(IXLWorksheet sheet, IEnumerable<SectionReportDto> sectionReports)
	{
		foreach (var sectionReport in sectionReports)
		{
			FillOperatingMode(sheet, sectionReport.IdSection, sectionReport.OperatingMode);

			var drillingBySetpoints = sectionReport.DrillingBySetpoints;

			if (drillingBySetpoints != null)
				FillDrillingBySetpoints(sheet, sectionReport.IdSection, drillingBySetpoints);

			FillSubsystemsStat(sheet, sectionReport.IdSection, sectionReport.SubsystemsStat);
		}
	}

	private static void FillDrillingBySetpoints(IXLWorksheet sheet, int idSection,
		DrillingBySetpointsDto drillingBySetpoints)
	{
		const int pressureColumn = 8;
		const int axialLoadColumn = 9;
		const int topDriveTorqueColumn = 10;
		const int speedLimitColumn = 11;

		if (!SetpointsRows.TryGetValue(idSection, out var row))
			return;

		sheet.Cell(row, pressureColumn).SetCellValue(drillingBySetpoints.MetersByPressure);
		sheet.Cell(row, axialLoadColumn).SetCellValue(drillingBySetpoints.MetersByLoad);
		sheet.Cell(row, topDriveTorqueColumn).SetCellValue(drillingBySetpoints.MetersByTorque);
		sheet.Cell(row, speedLimitColumn).SetCellValue(drillingBySetpoints.MetersBySpeed);
	}

	private static void FillSubsystemsStat(IXLWorksheet sheet, int idSection,
		IEnumerable<SubsystemStatDto> subsystemsStat)
	{
		const int idSubsystemAPDRotor = 11;
		const int idSubsystemAPDSlide = 12;
		const int idSubsystemOscillation = 65536;

		const int kUsageApdRotorColumn = 3;
		const int kUsageApdSlideColumn = 4;
		const int kUsageOscillationColumn = 5;

		const int sumDepthIntervalApdRotorColumn = 14;
		const int sumDepthIntervalApdSlideColumn = 15;
		const int sumDepthIntervalApdOscillation = 17;

		if (!SubsystemRows.TryGetValue(idSection, out var row))
			return;

		foreach (var subsystemStat in subsystemsStat)
		{
			switch (subsystemStat.IdSubsystem)
			{
				case idSubsystemAPDRotor:
					sheet.Cell(row, kUsageApdRotorColumn).SetCellValue(subsystemStat.KUsage);
					sheet.Cell(row, sumDepthIntervalApdRotorColumn).SetCellValue(subsystemStat.SumDepthInterval);
					break;
				case idSubsystemAPDSlide:
					sheet.Cell(row, kUsageApdSlideColumn).SetCellValue(subsystemStat.KUsage);
					sheet.Cell(row, sumDepthIntervalApdSlideColumn).SetCellValue(subsystemStat.SumDepthInterval);
					break;
				case idSubsystemOscillation:
					sheet.Cell(row, kUsageOscillationColumn).SetCellValue(subsystemStat.KUsage);
					sheet.Cell(row, sumDepthIntervalApdOscillation).SetCellValue(subsystemStat.SumDepthInterval);
					break;
			}
		}
	}

	private static void FillOperatingMode(IXLWorksheet sheet, int idSection,
		PlanFactDto<OperatingModeDto> operatingMode)
	{
		const int depthStartColumn = 3;
		const int depthEndColumn = 4;
		const int ropMinColumn = 6;
		const int ropMaxColumn = 7;
		const int ropAvgColumn = 8;
		const int weightOnBitMinColumn = 9;
		const int weightOnBitMaxColumn = 10;
		const int weightOnBitAvgColumn = 11;
		const int driveTorqueMinColumn = 12;
		const int driveTorqueMaxColumn = 13;
		const int driveTorqueAvgColumn = 14;
		const int differentialPressureMinColumn = 15;
		const int differentialPressureMaxColumn = 16;
		const int differentialPressureAvgColumn = 17;
		const int frowRateMinColumn = 18;
		const int frowRateMaxColumn = 19;

        if (PlanOperatingModeRows.TryGetValue(idSection, out var planRow))
        {
            sheet.Cell(planRow, depthStartColumn).SetCellValue(operatingMode.Plan?.DepthStart);
            sheet.Cell(planRow, depthEndColumn).SetCellValue(operatingMode.Plan?.DepthEnd);

            sheet.Cell(planRow, ropMinColumn).SetCellValue(operatingMode.Plan?.RopMin);
            sheet.Cell(planRow, ropMaxColumn).SetCellValue(operatingMode.Plan?.RopMax);
            sheet.Cell(planRow, ropAvgColumn).SetCellValue(operatingMode.Plan?.RopAvg);

            sheet.Cell(planRow, weightOnBitMinColumn).SetCellValue(operatingMode.Plan?.WeightOnBitMin);
            sheet.Cell(planRow, weightOnBitMaxColumn).SetCellValue(operatingMode.Plan?.WeightOnBitMax);
            sheet.Cell(planRow, weightOnBitAvgColumn).SetCellValue(operatingMode.Plan?.WeightOnBitAvg);

            sheet.Cell(planRow, driveTorqueMinColumn).SetCellValue(operatingMode.Plan?.DriveTorqueMin);
            sheet.Cell(planRow, driveTorqueMaxColumn).SetCellValue(operatingMode.Plan?.DriveTorqueMax);
            sheet.Cell(planRow, driveTorqueAvgColumn).SetCellValue(operatingMode.Plan?.DriveTorqueAvg);

            sheet.Cell(planRow, differentialPressureMinColumn)
                .SetCellValue(operatingMode.Plan?.DifferentialPressureMin);
            sheet.Cell(planRow, differentialPressureMaxColumn)
                .SetCellValue(operatingMode.Plan?.DifferentialPressureMax);
            sheet.Cell(planRow, differentialPressureAvgColumn)
                .SetCellValue(operatingMode.Plan?.DifferentialPressureAvg);

            sheet.Cell(planRow, frowRateMinColumn).SetCellValue(operatingMode.Plan?.FrowRateMin);
            sheet.Cell(planRow, frowRateMaxColumn).SetCellValue(operatingMode.Plan?.FrowRateMax);
        }

        if (FactOperatingModeRows.TryGetValue(idSection, out var factRow))
        {
            sheet.Cell(factRow, depthStartColumn).SetCellValue(operatingMode.Fact?.DepthStart);
            sheet.Cell(factRow, depthEndColumn).SetCellValue(operatingMode.Fact?.DepthEnd);
        }
	}
}