forked from ddrilling/AsbCloudServer
386 lines
15 KiB
C#
386 lines
15 KiB
C#
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);
|
||
}
|
||
}
|
||
} |