public async Task<IReporter> GetReporter(CalculateRequest calcRequest, int drillMonth, string logoPath)
{
this.calculateRequest = calcRequest;
var report = new Reporter();
var periods = Utils.GetZeitraume(calculateRequest.Stichtag);
report.AddNewSection();
report = await this.CreateHeader(report, logoPath);
report.AddInfo();
report.AddJahresbetrachtung(await meteoGtzService.GetGtzYearsSum(this.calculateRequest, true));
var results = (await meteoGtzService.GetRelativeVerteilung(this.calculateRequest, false)).ToList();
var dt = results.ToDataTable();
report.AddMonatsbetrachtung(dt, periods);
var tempData = drillMonth > 0 ?
await this.klimaService.GetTemperaturMohtsDrill(calculateRequest, drillMonth) :
await this.klimaService.GetTemperaturGroupedByPeriods(calculateRequest);
report.AddTagesmitteltemperaturen(tempData.ToList(), periods);
report.AddSpacing(30);
report.AddRemarks();
return report;
}
public async Task Download(CalculateRequest calcRequest, int drillMonth, string logoPath, string fileName)
{
using (var reporter = await GetReporter(calcRequest, drillMonth, logoPath))
{
reporter.Download(fileName);
}
}
public async Task<bool> CheckPlz(CalculateRequest calculateRequest)
{
switch (calculateRequest.RequestType)
{
case RequestType.Plz:
return (await this.meteoGtzRepository.FindByAsync(p => p.Plz == calculateRequest.Value, true)).Any();
}
return true;
}
public static CalculateRequest ToModel(this CalculateRequestViewModel source, bool firstDayOfMonth = true)
{
var result = new CalculateRequest
{
Stichtag = firstDayOfMonth ? source.Date.GetValueOrDefault().GetFirstDayOfMonth() : source.Date.GetValueOrDefault(),
RequestType = source.RequestType.GetValueOrDefault(),
Value = source.RequestType == RequestType.Bundesland ? source.BundeslandId.GetValueOrDefault() : source.Plz.ConvertTo<int>(),
Plz = source.Plz
};
return result;
}
public async Task<IEnumerable<MeteoGtzPeriod>> GetGtzByPeriods(
CalculateRequest calculateRequest,
IntervalType intervalType = IntervalType.M36)
{
var meteoGtzDatas = await this.GetMeteoGtz(calculateRequest, intervalType);
var startDate = calculateRequest.Stichtag;
var datumVon1 = startDate.GetPastDate(12);
var datumBis1 = startDate;
var datumVon2 = datumVon1.AddYears(-1);
var datumBis2 = datumBis1.AddYears(-1);
var datumVon3 = datumVon2.AddYears(-1);
var datumBis3 = datumBis2.AddYears(-1);
var result = meteoGtzDatas.GroupBy(
p => new
{
p.Monat,
p.Lgtz,
p.Promille
}).Select(
(gr, index) => new MeteoGtzPeriod
{
Monat = gr.Key.Monat,
Lgtz = gr.Key.Lgtz,
Promille = gr.Key.Promille,
Period1 =
gr.Where(p => this.GetDate(p.Jahr, p.Monat).IsBetween(datumVon1, datumBis1))
.Select(p => p.Gtz)
.FirstOrDefault(),
Period2 =
gr.Where(p => this.GetDate(p.Jahr, p.Monat).IsBetween(datumVon2, datumBis2))
.Select(p => p.Gtz)
.FirstOrDefault(),
Period3 =
gr.Where(p => this.GetDate(p.Jahr, p.Monat).IsBetween(datumVon3, datumBis3))
.Select(p => p.Gtz)
.FirstOrDefault()
});
return result;
}
public async Task<IEnumerable<KlimaTemperatur>> GetTemperatur(CalculateRequest calculateRequest, IntervalType intervalType = IntervalType.M24)
{
var startDate = calculateRequest.Stichtag.GetPastDate((int)intervalType);
var endDate = calculateRequest.Stichtag.GetLastDayOfMonth();
IEnumerable<KlimaTemperatur> temperaturs = null;
switch (calculateRequest.RequestType)
{
case RequestType.Plz:
var station = await wetterStationRepository.GetByPlz(calculateRequest.Value);
if (station == null) return new List<KlimaTemperatur>();
temperaturs = await this.GetTemperaturByPlz(station.WsCode, startDate, endDate);
break;
case RequestType.Bundesland:
temperaturs = await this.GetTemperaturByBundesland(calculateRequest.Value, startDate, endDate);
break;
case RequestType.Deutschland:
temperaturs = await this.GetTemperaturDeutschland(startDate, endDate);
break;
}
return temperaturs;
}
public async Task<MeteoGtzYear> GetYearsDataRelativeToCurrentYear(
CalculateRequest calculateRequest,
bool isHeizperiode,
IntervalType intervalType = IntervalType.M36)
{
var meteoGtzSumYears = await this.GetGtzYearsSum(calculateRequest, isHeizperiode, intervalType);
var result = meteoGtzSumYears.ToRelativeData();
return result;
}
public async Task<MeteoGtzYear> GetGtzYearsSum(
CalculateRequest calculateRequest,
bool isHeizperiode,
IntervalType intervalType = IntervalType.M36)
{
var source = await this.GetGtzByPeriods(calculateRequest, intervalType);
source = isHeizperiode ? source.Where(p => p.Monat.IsHeizMonat()) : source;
var meteoGtzPeriods = source.ToList();
if (meteoGtzPeriods.Count == 0)
{
return null;
}
var result = new MeteoGtzYear
{
//IsHeizperiode = isHeizperiode,
Period1 = meteoGtzPeriods.Sum(p => p.Period1),
Period2 = meteoGtzPeriods.Sum(p => p.Period2),
Period3 = meteoGtzPeriods.Sum(p => p.Period3),
Lgtz = meteoGtzPeriods.Sum(p => p.Lgtz)
};
return result;
}
public async Task<IEnumerable<MeteoGtzPeriodRelative>> GetRelativeVerteilung(
CalculateRequest calculateRequest,
bool isHeizperiode,
IntervalType intervalType = IntervalType.M36)
{
var source = await this.GetGtzByPeriods(calculateRequest, intervalType);
source = isHeizperiode ? source.Where(p => p.Monat.IsHeizMonat()) : source;
var meteoGtzPeriods = source.ToList();
double sumLgtz = meteoGtzPeriods.Sum(p => p.Lgtz);
var result = meteoGtzPeriods.Select(
p => new MeteoGtzPeriodRelative
{
Monat = p.Monat,
Promille = p.Promille,
Period1 = (p.Lgtz - p.Period1) / sumLgtz * 100,
Period2 = (p.Lgtz - p.Period2) / sumLgtz * 100,
});
return result;
}
public async Task<IEnumerable<MeteoGtzData>> GetMeteoGtz(
CalculateRequest calculateRequest,
IntervalType intervalType = IntervalType.M36)
{
var startDate = calculateRequest.Stichtag.GetPastDate((int)intervalType);
var endDate = calculateRequest.Stichtag.GetLastDayOfMonth();
IEnumerable<MeteoGtzData> meteoGtzDatas = null;
switch (calculateRequest.RequestType)
{
case RequestType.Plz:
meteoGtzDatas = await this.GetGtzByPlz(calculateRequest.Value, startDate, endDate);
break;
case RequestType.Bundesland:
meteoGtzDatas = await this.GetGtzByBundesland(calculateRequest.Value, startDate, endDate);
break;
case RequestType.Deutschland:
meteoGtzDatas = await this.GetGtzDeutschland(startDate, endDate);
break;
}
return meteoGtzDatas;
}
public async Task<double?> GetJahrBedarfWithPromille(
CalculateRequest calculateRequest,
bool isHeizperiode,
IntervalType intervalType = IntervalType.M36)
{
var source = await this.GetGtzByPeriods(calculateRequest, intervalType);
source = isHeizperiode ? source.Where(p => p.Monat.IsHeizMonat()) : source;
var sum = source.Aggregate<MeteoGtzPeriod, double?>(
0.0,
(current, item) => current + item.Period1 / item.Period2 * item.Promille);
var result = (sum / 97 - 1) * 100;
return result;
}
public async Task<IEnumerable<KlimaTemperaturPeriod>> GetTemperaturMohtsDrill(CalculateRequest calculateRequest, int selectedMonth,
IntervalType intervalType = IntervalType.M24)
{
var temperaturGrouperByPeriods = await GetTemperaturGroupedByPeriods(calculateRequest);
var stichTag = calculateRequest.Stichtag;
int month1 = 0, month3 = 0;
var month2 = selectedMonth;
var startMonth = stichTag.Month;
int endMonth = stichTag.GetPastDate((int)intervalType).Month;
if (selectedMonth != startMonth)
{
month3 = selectedMonth == 12 ? 1 : selectedMonth + 1;
}
if (selectedMonth != endMonth)
{
month1 = selectedMonth == 1 ? 12 : selectedMonth - 1;
}
var result =
temperaturGrouperByPeriods.Where(
p => p.Datum.Month == month1 || p.Datum.Month == month2 || p.Datum.Month == month3);
return result;
}
public async Task<IEnumerable<KlimaTemperaturPeriod>> GetTemperaturGroupedByPeriods(CalculateRequest calculateRequest, IntervalType intervalType = IntervalType.M24)
{
var temperaturs = await GetTemperatur(calculateRequest);
var result = this.GroupTemperaturByPeriods(temperaturs, calculateRequest.Stichtag);
return result;
}
public async Task Export(CalculateRequest calculateRequest)
{
this.calculateRequest = calculateRequest;
//InitOldData();
var grid1 = new WebDataGrid
{
//DataSource = this.chartService.GetJahresbetrachtungProzentual(IntervalType.M36)
DataSource = (await meteoGtzService.GetYearsDataRelativeToCurrentYear(calculateRequest, true)).ToDataTable(p => p.Period1)
};
grid1.DataBind();
var grid2 = new WebDataGrid
{
//DataSource = ChartService.CalculateSum(this.chartService.GetMonatsRelativeVerteilungJahr(false))
DataSource = (await meteoGtzService.GetRelativeVerteilung(calculateRequest, false)).ToDataTable().CalculateSum()
};
grid2.DataBind();
var grid3 = new WebDataGrid
{
//DataSource = ChartService.CalculateSum(this.chartService.MonatsSummenGtzJahr)
DataSource = (await meteoGtzService.GetGtzByPeriods(calculateRequest)).ToDataTable().CalculateSum()
};
grid3.DataBind();
var wb = new Workbook();
var font = wb.Styles.NormalStyle.StyleFormat.Font;
font.Name = "Microsoft Sans Serif";
font.Height = 9 * 20;
var ws = wb.Worksheets.Add("Daten");
ws.Rows[3].Cells[0].Value = Title1;
ws.Rows[3].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
ws.Rows[12].Cells[0].Value = Title2;
ws.Rows[12].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
ws.Rows[29].Cells[0].Value = "Monatssummen der GTZ im Vergleich";
ws.Rows[29].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
ws.Rows[8].Cells[0].Value = "Heizbedarf für das Abrechnungsjahr";
ws.Rows[8].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//ws.Rows[9].Cells[0].Value = this.chartService.GetJahrBedarfWithPromille();
ws.Rows[9].Cells[0].Value = await meteoGtzService.GetJahrBedarfWithPromille(calculateRequest, true);
var wdge1 = new WebDataGridExport
{
Grid = grid1,
RowOffset = 4
};
var wdge2 = new WebDataGridExport
{
Grid = grid2,
RowOffset = 13
};
var wdge3 = new WebDataGridExport
{
Grid = grid3,
RowOffset = 30
};
RegionFormat(ws.GetRegion("A5:C5"), true);
RegionFormat(ws.GetRegion("A14:F14"), true);
RegionFormat(ws.GetRegion("A27:F27"));
RegionFormat(ws.GetRegion("A28:F28"));
RegionFormatBold(ws.GetRegion("A27:A28"));
RegionFormat(ws.GetRegion("A31:F31"), true);
RegionFormat(ws.GetRegion("A44:F44"));
RegionFormat(ws.GetRegion("A45:F45"));
RegionFormatBold(ws.GetRegion("A44:A45"));
RegionFormatBold(ws.GetRegion("A27:A28"));
webExcelExporter.Export(ws, wdge1, wdge2, wdge3);
}
