-
-
Save ShilGen/a96525050ef5bf62395dc0eff0e938b6 to your computer and use it in GitHub Desktop.
pbk4.cs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Профиль А+ (60 мин) и потребление | |
/* Столбцы: | |
Инфраструктурная компания | |
Наименование объекта | |
Внутренний код | |
Базовая станция | |
Субъект РФ | |
Адрес | |
Точка учета | |
Прибор учета | |
Ктт/Ктн | |
Показания на начало периода | |
Показания на конец периода | |
Потребление за период по показаниям | |
Потребление за период по профилю | |
Разница | |
*/ | |
var workSheet = WorkbookNonExcel.Worksheets.FirstOrDefault(); | |
// интервалы | |
var di30Min = new DiscretInterval {Discret = KnownDiscret.Min, DiscretValue = 30}; | |
var diHour = new DiscretInterval {Discret = KnownDiscret.Hour, DiscretValue = 1}; | |
// знаки после запятой | |
var decimalPlaces = 2; | |
int SI_multiples = 1000; // для отображения значений в кВт | |
//максимальное количество счетчиков в отчете | |
var maxCounters = 14094; | |
// выравнивание дат | |
//ReportParams.StartDt = ReportParams.StartDt.AlignToDay().AddMinutes(30); | |
ReportParams.StartDt = ReportParams.StartDt.AlignToDay().AddMinutes(60); | |
ReportParams.EndDt = ReportParams.EndDt.AlignToDay(); | |
if (!string.IsNullOrEmpty(ReportFileName)) workSheet.Cells[0, 0].Value = ReportFileName; | |
workSheet.Cells[1, 0].Value = string.Format("За период c {0:dd.MM.yyyy} по {1:dd.MM.yyyy}", ReportParams.StartDt, ReportParams.EndDt); | |
workSheet.Cells[2, 0].Value = string.Format("Сформирован {0:dd.MM.yyyy HH:mm}", DateTime.UtcNow.AddHours(User.GetClassInfo().GetCurrentUserTimeZoneOffset())); | |
//DayIntervalData interval = new DayIntervalData() {StartDt = ReportParams.StartDt, EndDt = ReportParams.EndDt}; | |
var meterPoints = ReportParams.ClassifierNodes.SelectMany(x => x.GetAllChildrenOfClass(MeterPoint.GetClassInfo())).OfType<MeterPoint>().Distinct().ToArray(); | |
if (meterPoints.Length > maxCounters) | |
{ | |
AddLogInfo($"Количество счетчиков в отчете выше допустимого значения. " + "Выберите меньшее количество счетчиков. Максимальное количество счетчиков " + maxCounters); | |
throw new ExecuteRegularException("Превышено максимальное количество счетчиков"); | |
} | |
// исходные данные по параметрам | |
//var parameters = new Dictionary<DirectionBasedParameter, string>{{DirectionBasedParameter.Instances.PowerActiveForward30Min, "A+, кВт" },}; | |
var parameters = new Dictionary<DirectionBasedParameter, string>{{DirectionBasedParameter.Instances.EnergyActiveForward1hour, "A+, кВт" },}; | |
var parametr = TariffZoneBasedParameter.Instances.EnergyActiveForwardTotalFixDay; | |
// поддерживаемые параметры ТУ | |
var meterPointSupportedParameterInfo = new Dictionary<MeterPoint, DirectionBasedParameter[]>(); | |
var meterPointsStartColumn = new Dictionary<MeterPoint, int>(); | |
Dictionary<string, string> GetDataBook(MeterPoint meterPoint) | |
{ | |
ClassifierItemParentItemsInfoData[] cl_g = meterPoint.GetClassifierItemParents().ToArray(); | |
ClassifierItemParentItemsInfoData cl = cl_g.FirstOrDefault(x => x.Classifier.Caption == "Субъекты РФ"); | |
if (cl != null) | |
{ | |
var DataBook = new Dictionary<string, string>(); | |
foreach (var g in cl.TransitionalItemsChain) | |
{ | |
DataBook[g.ParentClassifierItem.Class.Caption] = g.ParentClassifierItem.AttributeCaption; | |
} | |
return DataBook; | |
} | |
else | |
return null; | |
} | |
foreach (var meterPoint in meterPoints) | |
{ | |
var meter = meterPoint.AttributeElectricityMeter; | |
meterPointSupportedParameterInfo[meterPoint] = parameters.Select(x => x.Key).Where(x => meter == null || meter.IsParameterSupported(x).GetValueOrDefault(true)).ToArray(); | |
} | |
var tableColumnsMaxCount = meterPoints.Count() * 4 + 4; | |
var tableColumnsCountAlphaIndex = tableColumnsMaxCount.AlphaAddress(0); | |
var startRowIndexHeader = 4; | |
var startRowIndexDate = 19; | |
var rangeBorder = new List<string>(); | |
var headerRange = new List<string>(); | |
var mergeRanges = new List<string>(); | |
var summRanges = new List<string>(); | |
// Заголовок таблицы | |
workSheet.Cells[startRowIndexHeader, 0].Value = "Инфраструктурная компания"; | |
workSheet.Cells[startRowIndexHeader + 1, 0].Value = "Наименование объекта"; | |
workSheet.Cells[startRowIndexHeader + 2, 0].Value = "Внутренний код"; | |
workSheet.Cells[startRowIndexHeader + 3, 0].Value = "Базовая станция"; | |
workSheet.Cells[startRowIndexHeader + 4, 0].Value = "Субъект РФ"; | |
workSheet.Cells[startRowIndexHeader + 5, 0].Value = "Адрес"; | |
workSheet.Cells[startRowIndexHeader + 6, 0].Value = "Точка учета"; | |
workSheet.Cells[startRowIndexHeader + 7, 0].Value = "Прибор учета"; | |
workSheet.Cells[startRowIndexHeader + 8, 0].Value = "Ктт/Ктн"; | |
workSheet.Cells[startRowIndexHeader + 9, 0].Value = "Показания на начало периода"; | |
workSheet.Cells[startRowIndexHeader + 10, 0].Value = "Показания на конец периода"; | |
workSheet.Cells[startRowIndexHeader + 11, 0].Value = "Потребление за период по показаниям"; | |
workSheet.Cells[startRowIndexHeader + 12, 0].Value = "Потребление за период по профилю"; | |
workSheet.Cells[startRowIndexHeader + 13, 0].Value = "Разница"; | |
workSheet.Cells[startRowIndexHeader + 14, 0].Value = "Дата и время"; | |
var meterPointCoefs = new Dictionary<MeterPoint, Tuple<double, double>>(); | |
var lastColumnIndex = 1; | |
var startMergeIndex = 1; | |
foreach(var meterPoint in meterPoints) | |
{ | |
startMergeIndex = lastColumnIndex; | |
meterPointsStartColumn[meterPoint] = lastColumnIndex; | |
Dictionary<string, string> MPd = GetDataBook(meterPoint); | |
string[] mp_name = Regex.Split(meterPoint.Caption, @"\\"); | |
var parents = meterPoint.GetUpperItems(); | |
var mmp = parents.OfType<SmallPowerConsumerBoundary>().FirstOrDefault(); | |
// Первичное заполнение ячеек с данными | |
workSheet.Cells[startRowIndexHeader + 1, lastColumnIndex].Value = "н/д"; | |
workSheet.Cells[startRowIndexHeader + 2, lastColumnIndex].Value = "н/д"; | |
workSheet.Cells[startRowIndexHeader + 3, lastColumnIndex].Value = "н/д"; | |
workSheet.Cells[startRowIndexHeader + 4, lastColumnIndex].Value = "н/д"; | |
if (mmp != null){ | |
workSheet.Cells[startRowIndexHeader + 0, lastColumnIndex].Value = (mmp.ReadValueByAttributeCaption("Инфраструктурная компания") != null) ? mmp.ReadValueByAttributeCaption("Инфраструктурная компания").ToString() : "н/д" ; | |
workSheet.Cells[startRowIndexHeader + 2, lastColumnIndex].Value = (mmp.ReadValueByAttributeCaption("Внутренний код") != null) ? mmp.ReadValueByAttributeCaption("Внутренний код") : "н/д" ; | |
workSheet.Cells[startRowIndexHeader + 3, lastColumnIndex].Value = (mmp.ReadValueByAttributeCaption("Базовая станция") != null) ? mmp.ReadValueByAttributeCaption("Базовая станция") : "н/д" ; | |
workSheet.Cells[startRowIndexHeader + 4, lastColumnIndex].Value = (MPd != null) ? MPd["Субъект РФ"]: "-"; | |
workSheet.Cells[startRowIndexHeader + 5, lastColumnIndex].Value = (mmp.ReadValueByAttributeCaption("Адрес") != null) ? mmp.ReadValueByAttributeCaption("Адрес") : "н/д" ; | |
} | |
if (mp_name.Length == 3){ | |
workSheet.Cells[startRowIndexHeader + 1, lastColumnIndex].Value = mp_name[1]; | |
workSheet.Cells[startRowIndexHeader + 6 , lastColumnIndex].Value = mp_name[2]; | |
} | |
if (mp_name.Length == 2){ | |
workSheet.Cells[startRowIndexHeader + 1, lastColumnIndex].Value = mp_name[0]; | |
workSheet.Cells[startRowIndexHeader + 6 , lastColumnIndex].Value = mp_name[1]; | |
} | |
var meter = meterPoint.AttributeElectricityMeter; | |
if (meter != null) {workSheet.Cells[startRowIndexHeader + 7, lastColumnIndex].Value = meter.AttributeSerialNumber;} | |
var trans = meterPoint.GetMeasureTransformersInfo(); | |
var ktt = trans.CurrentRatio ?? 1; | |
if ((double)ktt <= 0) ktt = 1; | |
var ktn = trans.VoltageRatio ?? 1; | |
if ((double)ktn <= 0) ktn = 1; | |
meterPointCoefs.Add(meterPoint, new Tuple<double, double>(ktt, ktn)); | |
workSheet.Cells[startRowIndexHeader + 8, lastColumnIndex].Value = string.Format("{0}/{1}", ktt, ktn); | |
DirectionBasedParameter[] meterParams; | |
if (meterPointSupportedParameterInfo.TryGetValue(meterPoint, out meterParams) && meterParams != null && meterParams.Length > 0) { | |
foreach(var par in meterParams) { | |
workSheet.Cells[startRowIndexHeader + 14, lastColumnIndex].Value = parameters[par]; | |
lastColumnIndex++; | |
} | |
} else { | |
workSheet.Cells[startRowIndexHeader + 14, lastColumnIndex].Value = "-"; | |
lastColumnIndex++; | |
} | |
if (meterParams.Length > 1) | |
{ | |
workSheet.Cells.GetSubrangeAbsolute(startRowIndexHeader, startMergeIndex, startRowIndexHeader, lastColumnIndex - 1).Merged = true; | |
workSheet.Cells.GetSubrangeAbsolute(startRowIndexHeader + 1, startMergeIndex, startRowIndexHeader + 1, lastColumnIndex - 1).Merged = true; | |
workSheet.Cells.GetSubrangeAbsolute(startRowIndexHeader + 2, startMergeIndex, startRowIndexHeader + 2, lastColumnIndex - 1).Merged = true; | |
} | |
workSheet.VerticalPageBreaks.Add(lastColumnIndex); | |
} | |
workSheet.Cells.GetSubrangeAbsolute(startRowIndexHeader, 0, startRowIndexHeader + 3, lastColumnIndex - 1).SetHeaderFormat(); | |
// вывод данных | |
var interval = new DayIntervalData | |
{ | |
StartDt = ReportParams.StartDt.AddMinutes(-60), | |
EndDt = ReportParams.EndDt | |
}; | |
// настройки выборки данных | |
var settings = new SettingsGetMeterPointDataData{ RequireAllIntervalsForIntervalParameters = true}; // получить все интервалы | |
DateTime tmpDt; | |
int dataRow; | |
using(PreloadManager.Current.RegisterCache(() => new MeterPointGetEnergyValueCache(meterPoints, parameters.Keys, interval))) | |
{ | |
foreach (var meterPoint in meterPoints) | |
{ | |
var columnIndex = meterPointsStartColumn[meterPoint]; | |
// показания за период | |
var data_pp = meterPoint.GetMeterPointFinalData(parametr, interval); | |
var startValue = data_pp.FirstOrDefault();//OrDefault(x => x.ValueDt != null && x.ValueDt == ReportParams.StartDt); | |
var stopValue = meterPoint.GetBaseMeterPointLastFinalData(parametr, ReportParams.EndDt); | |
if (startValue != null){ | |
workSheet.Cells[startRowIndexHeader + 9, columnIndex].Value = startValue.Value / SI_multiples; | |
} | |
if (stopValue != null){ workSheet.Cells[startRowIndexHeader + 10, columnIndex].Value =stopValue.Value / SI_multiples; } | |
if (startValue != null & stopValue != null){ | |
workSheet.Cells[startRowIndexHeader + 11, columnIndex].Value = (stopValue.Value - startValue.Value) / SI_multiples; | |
} | |
// -- показания за период | |
meterPointSupportedParameterInfo.TryGetValue(meterPoint, out var meterParams); | |
if (meterParams == null) meterParams = new DirectionBasedParameter[0]; | |
if (meterParams.Length == 0) // нет поддерживаемых параметров ПУ | |
{ | |
tmpDt = ReportParams.StartDt; | |
dataRow = startRowIndexDate; | |
while (tmpDt <= ReportParams.EndDt) | |
{ | |
workSheet.Cells[dataRow, columnIndex/*+i*/].Value = "н/д"; | |
dataRow++; | |
tmpDt = tmpDt.AddMinutes(60); | |
} | |
continue; | |
} | |
for (int i = 0; i < meterParams.Length; i++) // проход по параметрам | |
{ | |
dataRow = startRowIndexDate; | |
var datas = meterPoint.GetMeterPointFinalDataEx(meterParams[i], interval, settings); | |
foreach (var data in datas) | |
{ | |
workSheet.Cells[dataRow, columnIndex+i].Value = "н/д"; | |
if (!double.IsNaN(data.Value)) workSheet.Cells[dataRow, columnIndex+i].Value = Math.Round(data.Value / 1000, decimalPlaces); | |
dataRow++; | |
} | |
} | |
} | |
} | |
// вывод штампов времени | |
tmpDt = ReportParams.StartDt; | |
dataRow = startRowIndexDate; | |
while (tmpDt <= ReportParams.EndDt) | |
{ | |
workSheet.Cells[dataRow, 0].Value = tmpDt.ToString("dd.MM.yyyy HH:mm"); | |
dataRow++; | |
tmpDt = tmpDt.AddMinutes(60); | |
} | |
workSheet.Cells[dataRow, 0].Value = string.Format("Итого кВт*ч / кВАР*ч"); | |
for (var i = 1; i < lastColumnIndex; i++) | |
{ | |
workSheet.Cells[dataRow, i].Formula = string.Format("=IFERROR(SUM({0}), \"н/д\")", string.Format("{0}:{1}",(i+1).AlphaAddress(startRowIndexDate + 1), (i+1).AlphaAddress(dataRow))); | |
workSheet.Cells[startRowIndexHeader + 12, i].Formula = string.Format("=IFERROR(SUM({0}), \"н/д\")", string.Format("{0}:{1}",(i+1).AlphaAddress(startRowIndexDate + 1), (i+1).AlphaAddress(dataRow))); | |
workSheet.Cells[startRowIndexHeader + 13, i].Formula = string.Format("=IFERROR({0}, \"н/д\")", string.Format("{0}-{1}",(i+1).AlphaAddress(startRowIndexHeader + 12), (i+1).AlphaAddress(dataRow+1))); | |
} | |
workSheet.Cells.GetSubrangeAbsolute(dataRow, 0, dataRow, lastColumnIndex - 1).SetSumFormat(); | |
workSheet.Cells.GetSubrangeAbsolute(startRowIndexHeader, 0, dataRow, lastColumnIndex - 1).SetBorder(); | |
#if !NETCORE | |
var startAutoFitRow = startRowIndexHeader + 1; | |
var endAutoFitRow = startRowIndexHeader + 4; | |
int columnCount = workSheet.CalculateMaxUsedColumns(); | |
for (int i = 1; i < columnCount; i++) workSheet.Columns[i].AutoFit(1, workSheet.Rows[startAutoFitRow], workSheet.Rows[endAutoFitRow]); | |
for (int j = startRowIndexHeader; j <= startRowIndexDate; j++) workSheet.Rows[j].AutoFit(); | |
#endif |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment