protected override void RunActualProcess([NotNull][ItemNotNull] List <ScenarioSliceParameters> allSlices, [NotNull] AnalysisRepository analysisRepo)
{
Info("starting to make trafostation results");
MultiyearTrend myt = new MultiyearTrend();
foreach (var slice in allSlices)
{
HouseComponentRepository hcr = new HouseComponentRepository(analysisRepo, slice);
var houses = analysisRepo.GetSlice(slice).Fetch <House>();
Dictionary <string, double> energyUsePerTrafostation = new Dictionary <string, double>();
var hausanschlusses = analysisRepo.GetSlice(slice).Fetch <Hausanschluss>();
foreach (var house in houses)
{
var components = house.CollectHouseComponents(hcr);
foreach (var component in components)
{
if (component.HausAnschlussGuid == null)
{
continue;
}
var hausanschluss = hausanschlusses.GetByGuid(component.HausAnschlussGuid);
var trafo = hausanschluss.Trafokreis;
if (!energyUsePerTrafostation.ContainsKey(trafo))
{
energyUsePerTrafostation.Add(trafo, 0);
}
energyUsePerTrafostation[trafo] += component.EffectiveEnergyDemand;
}
}
double totalEnergy = 0;
foreach (var energy in energyUsePerTrafostation)
{
myt[slice].AddValue(energy.Key, energy.Value, DisplayUnit.GWh);
totalEnergy += energy.Value;
}
myt[slice].AddValue("Total", totalEnergy, DisplayUnit.GWh);
}
var filename3 = MakeAndRegisterFullFilename("TrafostationEnergyResults.xlsx", Constants.PresentSlice);
Info("Writing results to " + filename3);
XlsxDumper.DumpMultiyearTrendToExcel(filename3, myt);
SaveToArchiveDirectory(filename3, RelativeDirectory.Report, Constants.PresentSlice);
}
private List <HouseEnergyValue> GetPlannedEnergySumPerHouseForScenario([NotNull] ScenarioSliceParameters parameters)
{
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, parameters);
var houses = dbHouses.Fetch <House>();
HouseComponentRepository hcr = new HouseComponentRepository(dbHouses);
var results = new List <HouseEnergyValue>();
foreach (var house in houses)
{
var components = house.CollectHouseComponents(hcr);
double energySum = 0;
foreach (var component in components)
{
energySum += component.EffectiveEnergyDemand;
}
results.Add(new HouseEnergyValue(house.ComplexName, energySum));
}
return(results);
}
protected override void RunActualProcess(ScenarioSliceParameters slice)
{
if (!slice.Equals(Constants.PresentSlice))
{
return;
}
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice);
Info("using house db in " + dbHouses.ConnectionString);
var houses = dbHouses.Fetch <House>();
HouseComponentRepository hcr = new HouseComponentRepository(dbHouses);
var hausanschlusses = dbHouses.Fetch <Hausanschluss>();
var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var suppIsn = dbRaw.Fetch <HausanschlussImportSupplement>();
var standortDict = new Dictionary <string, string>();
var rlms = dbRaw.Fetch <RlmProfile>();
List <AssignmentEntry> ases = new List <AssignmentEntry>();
foreach (var supplement in suppIsn)
{
if (!string.IsNullOrWhiteSpace(supplement.TargetStandort))
{
if (standortDict.ContainsKey(supplement.TargetStandort))
{
throw new FlaException("Already contains standort " + supplement.TargetStandort);
}
standortDict.Add(supplement.TargetStandort, supplement.HaObjectid);
}
AssignmentEntry ase = ases.FirstOrDefault(x => x.ObjectId == supplement.HaObjectid);
if (ase == null)
{
ase = new AssignmentEntry(supplement.HaObjectid);
ases.Add(ase);
}
ase.Targets.Add(supplement.TargetStandort);
ase.TargetCount++;
}
List <string> checkedStandorte = new List <string>();
var lgzs = ReadZuordnungLastgänge();
var successfullAssignments = new List <LastgangZuordnung>();
RowCollection rc = new RowCollection("sheet", "Sheet1");
List <PvSystemEntry> assignedPVsystems = new List <PvSystemEntry>();
List <PvSystemEntry> otherPVsystems = new List <PvSystemEntry>();
foreach (House house in houses)
{
var houseComponents = house.CollectHouseComponents(hcr);
foreach (var component in houseComponents)
{
if (component.Standort == null)
{
continue;
}
if (standortDict.ContainsKey(component.Standort))
{
Hausanschluss ha = hausanschlusses.Single(x => x.Guid == component.HausAnschlussGuid);
string targetObjectID = standortDict[component.Standort];
if (ha.ObjectID != targetObjectID)
{
throw new FlaException("Incorrect hausanschluss for " + component.Name + ": was supposed to be " + targetObjectID +
" but instead was " + ha.ObjectID + " (standort: " + component.Standort);
}
var ase = ases.Single(x => x.ObjectId == ha.ObjectID);
ase.AssignedCount++;
ase.Assigned.Add(component.Standort);
checkedStandorte.Add(component.Standort);
}
if (component.HouseComponentType == HouseComponentType.BusinessWithLastgangHighVoltage)
{
var businessEntry = (BusinessEntry)component;
var rlmfn = businessEntry.RlmProfileName ?? throw new FlaException("No file");
var ha = hausanschlusses.First(x => x.Guid == businessEntry.HausAnschlussGuid);
var rlm = rlms.Single(x => x.Name == businessEntry.RlmProfileName);
var energysum = new Profile(rlm.Profile).EnergySum();
LogAssignment(lgzs,
rlmfn,
ha,
successfullAssignments,
rc,
businessEntry.Name,
businessEntry.Standort,
suppIsn,
"HS",
businessEntry.EffectiveEnergyDemand,
energysum,
house.ComplexName,
businessEntry.FinalIsn);
}
if (component.HouseComponentType == HouseComponentType.BusinessWithLastgangLowVoltage)
{
var businessEntry = (BusinessEntry)component;
var rlmfn = businessEntry.RlmProfileName ?? throw new FlaException("no file?");
var ha = hausanschlusses.First(x => x.Guid == businessEntry.HausAnschlussGuid);
var rlm = rlms.Single(x => x.Name == businessEntry.RlmProfileName);
var energysum = new Profile(rlm.Profile).EnergySum();
LogAssignment(lgzs,
rlmfn,
ha,
successfullAssignments,
rc,
businessEntry.Name,
businessEntry.Standort,
suppIsn,
"NS",
businessEntry.EffectiveEnergyDemand,
energysum,
house.ComplexName,
businessEntry.FinalIsn);
}
if (component.HouseComponentType == HouseComponentType.Kwkw)
{
var kwkw = (KleinWasserkraft)component;
var rlmfn = kwkw.RlmProfileName;
var ha = hausanschlusses.First(x => x.Guid == kwkw.HausAnschlussGuid);
var rlm = rlms.Single(x => x.Name == kwkw.RlmProfileName);
var energysum = new Profile(rlm.Profile).EnergySum();
LogAssignment(lgzs,
rlmfn,
ha,
successfullAssignments,
rc,
kwkw.Name,
kwkw.Standort,
suppIsn,
"WKW",
kwkw.EffectiveEnergyDemand,
energysum,
house.ComplexName,
kwkw.FinalIsn);
}
if (component.HouseComponentType == HouseComponentType.Photovoltaik)
{
var ha = hausanschlusses.First(x => x.Guid == component.HausAnschlussGuid);
if (lgzs.Any(x => string.Equals(x.Knoten.ToLower(), ha.ObjectID.ToLower(), StringComparison.InvariantCultureIgnoreCase)))
{
assignedPVsystems.Add((PvSystemEntry)component);
}
else
{
otherPVsystems.Add((PvSystemEntry)component);
}
}
}
}
foreach (var zuordnung in lgzs)
{
if (!successfullAssignments.Contains(zuordnung))
{
RowBuilder rb = RowBuilder.Start("Lastgang", zuordnung.FileName).Add("Diren Profilename", zuordnung.Knoten);
var has = hausanschlusses.Where(x => x.ObjectID == zuordnung.Knoten).ToList();
var haguids = has.Select(x => x.Guid).Distinct().ToList();
var pvs = assignedPVsystems.Where(x => haguids.Contains(x.HausAnschlussGuid)).ToList();
var houseGuids = has.Select(x => x.HouseGuid).Distinct().ToList();
var otherPV = otherPVsystems.Where(x => houseGuids.Contains(x.HouseGuid));
rb.Add("PV Systems", string.Join(";", pvs.Select(x => x.Name)));
rb.Add("Other PV Systems@house", string.Join(";", otherPV.Select(x => x.Name)));
var rlm = rlms.Where(x => x.Name.Contains(zuordnung.FileName)).ToList();
for (int i = 0; i < rlm.Count; i++)
{
var energysum = new Profile(rlm[i].Profile).EnergySum();
rb.Add("Profilesumme " + i, energysum);
}
rc.Add(rb);
if (has.Count > 0)
{
List <string> housenames = new List <string>();
foreach (var ha in has)
{
var house = houses.First(x => x.Guid == ha.HouseGuid);
housenames.Add(house.ComplexName);
}
rb.Add("Hausname", string.Join(",", housenames.Distinct()));
}
}
}
foreach (var pVsystem in otherPVsystems)
{
House house = houses.First(x => x.Guid == pVsystem.HouseGuid);
var rb = RowBuilder.Start("Hausname", house.ComplexName);
rb.Add("Effective Energy", pVsystem.EffectiveEnergyDemand);
rb.Add("Planned Energy", pVsystem.EffectiveEnergyDemand);
var ha = hausanschlusses.First(x => x.Guid == pVsystem.HausAnschlussGuid);
rb.Add("Zugeordnete ObjektID", ha.ObjectID);
rc.Add(rb);
}
var fn = MakeAndRegisterFullFilename("WrongAssignments.xlsx", slice);
XlsxDumper.WriteToXlsx(fn, rc);
var missingAssigments = lgzs.Where(x => !successfullAssignments.Contains(x)).ToList();
if (missingAssigments.Count > 0)
{
Info("Missing assignments: " + string.Join("\n", missingAssigments.Select(x => x.FileName)));
}
foreach (var pair in standortDict)
{
if (!checkedStandorte.Contains(pair.Key))
{
//throw new FlaException("Didn't find supplemental standort " + pair.Value + " in the list of components. Typo?");
}
}
foreach (var ase in ases)
{
foreach (var target in ase.Targets)
{
if (!ase.Assigned.Contains(target))
{
// throw new FlaException("Missing " + target + " from the list of assigned standorts");
}
}
foreach (var assigned in ase.Assigned)
{
if (!ase.Targets.Contains(assigned))
{
//throw new FlaException("Missing " + assigned + " from the list of target standorts");
}
}
}
}
// ReSharper disable once FunctionComplexityOverflow
private static void WriteOneLine([NotNull] ExcelWorksheet ws,
int row,
[NotNull] Dictionary <Column, int> columnNumbers,
[NotNull] House house,
[ItemNotNull][NotNull] List <GwrData> gwr,
[ItemNotNull][NotNull] List <EnergiebedarfsdatenBern> kanton,
[ItemNotNull][NotNull] List <MonthlyElectricityUsePerStandort> complexEnergy,
[NotNull] BuildingComplex mycomplex,
[NotNull] HeatingSystemEntry heatingSystem,
[NotNull][ItemNotNull] List <Occupant> occupants,
[NotNull][ItemNotNull] List <BusinessEntry> businessEntries,
[CanBeNull] PvSystemEntry pvsystem,
[NotNull][ItemNotNull] List <Household> households,
[NotNull][ItemNotNull] List <CarDistanceEntry> carDistances,
[NotNull] HouseComponentRepository hcr)
{
ws.Cells[row, columnNumbers[Column.ComplexName]].Value = house.ComplexName;
ws.Cells[row, columnNumbers[Column.Adressen]].Value = mycomplex.AdressesAsJson;
ws.Cells[row, columnNumbers[Column.EGids]].Value = CleanJson(mycomplex.EGIDsAsJson);
ws.Cells[row, columnNumbers[Column.LocalnetISNIds]].Value = CleanJson(mycomplex.GebäudeObjectIDsAsJson);
ws.Cells[row, columnNumbers[Column.GeoKoordinaten]].Value = CleanJson(mycomplex.GeoCoordsAsJson);
var gwrs = gwr.Where(x => {
if (x.EidgGebaeudeidentifikator_EGID == null)
{
throw new FlaException("x.EidgGebaeudeidentifikator_EGID != null");
}
return(mycomplex.EGids.Contains((long)x.EidgGebaeudeidentifikator_EGID));
}).ToList();
ws.Cells[row, columnNumbers[Column.WG_GWR_GebäudeAnzahl]].Value = gwrs.Count;
var ebbes = kanton.Where(x => mycomplex.EGids.Contains(x.egid)).ToList();
ws.Cells[row, columnNumbers[Column.WG_GEAK_Anzahl]].Value = ebbes.Sum(x => x.has_geak);
ws.Cells[row, columnNumbers[Column.EBBE_GebäudeTyp]].Value = CollapseList(ebbes.Select(x => x.upd_gtyp));
ws.Cells[row, columnNumbers[Column.EBBE_GebäudeTyp]].Value =
ws.Cells[row, columnNumbers[Column.GWR_WG]].Value = gwrs.Sum(x => x.AnzahlWohnungen_GANZWHG);
ws.Cells[row, columnNumbers[Column.EBBE_GArea]].Value = ebbes.Sum(x => x.garea);
ws.Cells[row, columnNumbers[Column.EBBE_EBF_HZ_Updated]].Value = ebbes.Sum(x => x.upd_ebf);
ws.Cells[row, columnNumbers[Column.EBBE_calc_ehzww]].Value = ebbes.Sum(x => x.calc_ehzww);
ws.Cells[row, columnNumbers[Column.EBBE_calc_ehz]].Value = ebbes.Sum(x => x.calc_ehz);
ws.Cells[row, columnNumbers[Column.EBBE_calc_eww]].Value = ebbes.Sum(x => x.calc_eww);
//energieträger aus ebbe
var heizträgerlst = ebbes.Select(x => x.upd_genhz).ToList();
long heizträger = 0;
if (heizträgerlst.Count > 0)
{
heizträger = heizträgerlst[0];
}
switch (heizträger)
{
case 0:
break;
case 7200:
break;
case 7201:
ws.Cells[row, columnNumbers[Column.EBHZ_OL]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7202:
ws.Cells[row, columnNumbers[Column.EBHZ_KO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7203:
ws.Cells[row, columnNumbers[Column.EBHZ_GZ]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7204:
ws.Cells[row, columnNumbers[Column.EBHZ_EL]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7205:
ws.Cells[row, columnNumbers[Column.EBHZ_HO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7206:
ws.Cells[row, columnNumbers[Column.EBHZ_WP]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7207:
ws.Cells[row, columnNumbers[Column.EBHZ_SO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7208:
ws.Cells[row, columnNumbers[Column.EBHZ_FW]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7209:
ws.Cells[row, columnNumbers[Column.EBHZ_A]].Value = ebbes.Sum(x => x.calc_ehz);
break;
default: throw new Exception("Unknown Heizungsträger: " + heizträger);
}
//warmwasser aus Ebbe
var wwträgerlst = ebbes.Select(x => x.upd_genww).ToList();
long wwträger = 0;
if (wwträgerlst.Count > 0)
{
wwträger = wwträgerlst[0];
}
switch (wwträger)
{
case 0:
break;
case 7200:
break;
case 7201:
ws.Cells[row, columnNumbers[Column.EBWW_OL]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7202:
ws.Cells[row, columnNumbers[Column.EBWW_KO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7203:
ws.Cells[row, columnNumbers[Column.EBWW_GZ]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7204:
ws.Cells[row, columnNumbers[Column.EBWW_EL]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7205:
ws.Cells[row, columnNumbers[Column.EBWW_HO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7206:
ws.Cells[row, columnNumbers[Column.EBWW_WP]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7207:
ws.Cells[row, columnNumbers[Column.EBWW_SO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7208:
ws.Cells[row, columnNumbers[Column.EBWW_FW]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7209:
ws.Cells[row, columnNumbers[Column.EBWW_A]].Value = ebbes.Sum(x => x.calc_eww);
break;
default: throw new Exception("Unknown Heizungsträger: " + heizträger);
}
ws.Cells[row, columnNumbers[Column.EBBE_EnergieträgerHz]].Value = CollapseList(ebbes.Select(x => x.upd_genhz.ToString()));
ws.Cells[row, columnNumbers[Column.EBBE_EnergieträgerWW]].Value = CollapseList(ebbes.Select(x => x.upd_genww.ToString()));
//entries from localnet
var monthlies = complexEnergy.Where(x => mycomplex.CleanedStandorte.Contains(x.CleanedStandort)).ToList();
ws.Cells[row, columnNumbers[Column.Localnet_Strom]].Value = monthlies.Sum(x => x.YearlyElectricityUseNetz);
ws.Cells[row, columnNumbers[Column.Localnet_Gas]].Value = monthlies.Sum(x => x.YearlyGasUse);
ws.Cells[row, columnNumbers[Column.Localnet_Wärme]].Value = monthlies.Sum(x => x.YearlyFernwaermeUse);
ws.Cells[row, columnNumbers[Column.BFH_EnergieträgerHeizung]].Value = heatingSystem.SynthesizedHeatingSystemType.ToString();
ws.Cells[row, columnNumbers[Column.BFH_EnergieBedarfHeizung]].Value = heatingSystem.EffectiveEnergyDemand;
ws.Cells[row, columnNumbers[Column.BFH_Einwohner]].Value = occupants.Count;
var businessTypes = businessEntries.Select(x => x.BusinessType.ToString()).Distinct().ToList();
var businessTypeStr = string.Join(",", businessTypes);
ws.Cells[row, columnNumbers[Column.BFH_Geschäfte]].Value = businessTypeStr;
if (pvsystem != null)
{
ws.Cells[row, columnNumbers[Column.BFH_PVSystemGrösseInKWh]].Value = pvsystem.EffectiveEnergyDemand;
}
ws.Cells[row, columnNumbers[Column.BFH_Geschäfte]].Value = businessTypeStr;
ws.Cells[row, columnNumbers[Column.BFH_StromHaushalteLow]].Value = households.Sum(x => x.LocalnetLowVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Column.BFH_StromHaushalteLow]].Value = households.Sum(x => x.LocalnetHighVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Column.BFH_StromGewerbeLow]].Value = businessEntries.Sum(x => x.LocalnetLowVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Column.BFH_StromGewerbeHigh]].Value =
businessEntries.Sum(x => x.LocalnetHighVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Column.FeuerungsstättenArt]].Value = heatingSystem.FeuerungsstättenType;
ws.Cells[row, columnNumbers[Column.FeuerungsstättenKesselLeistung]].Value = heatingSystem.FeuerungsstättenPower;
if (heatingSystem.FeuerungsstättenType == "Gas")
{
ws.Cells[row, columnNumbers[Column.FeuerungsstättenJahresEnergie1500hGas]].Value =
heatingSystem.EstimatedMinimumEnergyFromFeuerungsStätten;
ws.Cells[row, columnNumbers[Column.FeuerungsstättenJahresEnergie2200hGas]].Value =
heatingSystem.EstimatedMaximumEnergyFromFeuerungsStätten;
}
if (heatingSystem.FeuerungsstättenType == "Oel")
{
ws.Cells[row, columnNumbers[Column.FeuerungsstättenJahresEnergie1500hOel]].Value =
heatingSystem.EstimatedMinimumEnergyFromFeuerungsStätten;
ws.Cells[row, columnNumbers[Column.FeuerungsstättenJahresEnergie2200hOel]].Value =
heatingSystem.EstimatedMaximumEnergyFromFeuerungsStätten;
}
ws.Cells[row, columnNumbers[Column.BFH_AnzahlFahrzeuge]].Value = carDistances.Count;
ws.Cells[row, columnNumbers[Column.BFH_SummeAutoPendlerdistanz]].Value = carDistances.Sum(x => x.CommutingDistance) * 365;
ws.Cells[row, columnNumbers[Column.BFH_SummeAutoGesamtdistanz]].Value = carDistances.Sum(x => x.TotalDistance) * 365;
ws.Cells[row, columnNumbers[Column.BFH_SonnendachPotential]].Value = heatingSystem.FeuerungsstättenPower;
ws.Cells[row, columnNumbers[Column.TrafoKreis]].Value = string.Join(";", house.Hausanschluss.Select(x => x.Trafokreis));
var houseComponents = house.CollectHouseComponents(hcr);
Dictionary <HouseComponentType, double> houseComponentSums = new Dictionary <HouseComponentType, double>();
foreach (var hc in houseComponents)
{
if (!houseComponentSums.ContainsKey(hc.HouseComponentType))
{
houseComponentSums.Add(hc.HouseComponentType, 0);
}
houseComponentSums[hc.HouseComponentType] += hc.EffectiveEnergyDemand;
}
foreach (var entry in houseComponentSums)
{
Column mycol = GetColumn(entry.Key);
ws.Cells[row, columnNumbers[mycol]].Value = entry.Value;
}
}
protected override void RunActualProcess(ScenarioSliceParameters slice)
{
var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var dbComplexes = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice);
var dbComplexEnergy = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.ComplexEnergyData, Constants.PresentSlice);
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, slice);
var complexes = dbComplexes.Fetch <BuildingComplex>();
var gwr = dbRaw.Fetch <GwrData>();
var kanton = dbRaw.Fetch <EnergiebedarfsdatenBern>();
var complexEnergy = dbComplexEnergy.Fetch <MonthlyElectricityUsePerStandort>();
var houses = dbHouses.Fetch <House>();
var heatingSystems = dbHouses.Fetch <HeatingSystemEntry>();
var pvSystemEntries = dbHouses.Fetch <PvSystemEntry>();
var businessEntries = dbHouses.Fetch <BusinessEntry>();
var households = dbHouses.Fetch <Household>();
var carDistances = dbHouses.Fetch <CarDistanceEntry>();
HouseComponentRepository hcr = new HouseComponentRepository(dbHouses);
using (var p = new ExcelPackage()) {
//A workbook must have at least on cell, so lets add one...
var ws = p.Workbook.Worksheets.Add("MySheet");
//To set values in the spreadsheet use the Cells indexer.
var columnNumbers = MakeColumnNumberDictionary();
//header
foreach (var pair in columnNumbers)
{
ws.Cells[1, pair.Value].Value = pair.Key.ToString();
}
var row = 2;
foreach (var house in houses)
{
var complex = complexes.Single(x => x.ComplexName == house.ComplexName);
var heatingSystem = heatingSystems.Single(x => x.HouseGuid == house.Guid);
var pvSystem = pvSystemEntries.FirstOrDefault(x => x.HouseGuid == house.Guid);
var houseHouseholds = households.Where(x => x.HouseGuid == house.Guid).ToList();
var houseOccupants = houseHouseholds.SelectMany(x => x.Occupants).ToList();
var houseBusinessEntries = businessEntries.Where(x => x.HouseGuid == house.Guid).ToList();
var houseCarDistances = carDistances.Where(x => x.HouseGuid == house.Guid).ToList();
WriteOneLine(ws,
row,
columnNumbers,
house,
gwr,
kanton,
complexEnergy,
complex,
heatingSystem,
houseOccupants,
houseBusinessEntries,
pvSystem,
houseHouseholds,
houseCarDistances,
hcr);
row++;
}
//Save the new workbook. We haven't specified the filename so use the Save as method.
var filename = MakeAndRegisterFullFilename("BrunoHariExport." + slice + ".xlsx", slice);
p.SaveAs(new FileInfo(filename));
Warning("File: " + filename);
SaveToArchiveDirectory(filename, RelativeDirectory.Bruno, slice);
}
}
protected override void RunActualProcess([NotNull][ItemNotNull] List <ScenarioSliceParameters> allSlices,
[NotNull] AnalysisRepository analysisRepo)
{
Info("starting to make house results");
MultiyearTrend myt = new MultiyearTrend();
foreach (var slice in allSlices)
{
var households = analysisRepo.GetSlice(slice).Fetch <Household>();
var occupants = households.SelectMany(x => x.Occupants).ToList();
HouseComponentRepository hcr = new HouseComponentRepository(analysisRepo, slice);
var houses = analysisRepo.GetSlice(slice).Fetch <House>();
houses[0].CollectHouseComponents(hcr);
myt[slice].AddValue("Businesses", hcr.Businesses.Count, DisplayUnit.Stk);
myt[slice].AddValue("Haushalt Energieverbrauch [GWh]", hcr.Households.Sum(x => x.EffectiveEnergyDemand), DisplayUnit.GWh);
myt[slice].AddValue("Business Energieverbrauch [GWh]", hcr.Businesses.Sum(x => x.EffectiveEnergyDemand), DisplayUnit.GWh);
myt[slice].AddValue("Gebäudeinfrastruktur Energieverbrauch [GWh]",
hcr.BuildingInfrastructures.Sum(x => x.EffectiveEnergyDemand),
DisplayUnit.GWh);
myt[slice].AddValue("Wärmepumpen Energieverbrauch [GWh]",
hcr.HeatingSystemEntries.Where(x => x.SynthesizedHeatingSystemType == HeatingSystemType.Heatpump)
.Sum(x => x.EffectiveEnergyDemand),
DisplayUnit.GWh);
myt[slice].AddValue("Klimatisierung Energieverbrauch [GWh]",
hcr.AirConditioningEntries.Sum(x => x.EffectiveEnergyDemand),
DisplayUnit.GWh);
var dhws = analysisRepo.GetSlice(slice).Fetch <DHWHeaterEntry>();
myt[slice].AddValue("Energiebedarf Warmwasserboiler [GWh]", dhws.Sum(x => x.EffectiveEnergyDemand), DisplayUnit.GWh);
myt[slice].AddValue("PV Energie [Gwh]", hcr.PVSystems.Sum(x => x.EffectiveEnergyDemand), DisplayUnit.GWh);
double distance = hcr.CarDistanceEntries.Sum(x => x.CommutingDistance + x.FreizeitDistance) / occupants.Count;
myt[slice].AddValue("Wegedistanz / Person / Jahr", distance, DisplayUnit.Stk);
myt[slice].AddValue("Car Distance Entries", hcr.CarDistanceEntries.Count, DisplayUnit.Stk);
myt[slice].AddValue("Anzahl Gebäudeinfrastruktur", hcr.BuildingInfrastructures.Count, DisplayUnit.Stk);
myt[slice].AddValue("Wasserkraftwerke", hcr.Wasserkraft.Count, DisplayUnit.Stk);
myt[slice].AddValue("Wasserkraftwerke Energie", hcr.Wasserkraft.Sum(x => x.EffectiveEnergyDemand), DisplayUnit.Stk);
myt[slice].AddValue("Heizsysteme Anzahl", hcr.HeatingSystemEntries.Count, DisplayUnit.Stk);
myt[slice].AddValue("Heizsysteme Gesamtenergiebedarf [GWh]",
hcr.HeatingSystemEntries.Sum(x => x.EffectiveEnergyDemand),
DisplayUnit.GWh);
myt[slice].AddValue("Heizsysteme Wärmepumpen Anzahl",
hcr.HeatingSystemEntries.Count(x => x.SynthesizedHeatingSystemType == HeatingSystemType.Heatpump),
DisplayUnit.Stk);
myt[slice].AddValue("Klimatisierung Anzahl", hcr.AirConditioningEntries.Count, DisplayUnit.Stk);
var cars = analysisRepo.GetSlice(slice).Fetch <Car>();
myt[slice].AddValue("Autos gesamt", cars.Count, DisplayUnit.Stk);
myt[slice].AddValue("Elektroautos", cars.Count(x => x.CarType == CarType.Electric), DisplayUnit.Stk);
myt[slice].AddValue("DHW Elektrisch", dhws.Count(x => x.DhwHeatingSystemType == DhwHeatingSystem.Electricity), DisplayUnit.Stk);
myt[slice].AddValue("DHW Heatpump", dhws.Count(x => x.DhwHeatingSystemType == DhwHeatingSystem.Heatpump), DisplayUnit.Stk);
}
var filename3 = MakeAndRegisterFullFilename("HouseEnergyResults.xlsx", Constants.PresentSlice);
Info("Writing results to " + filename3);
XlsxDumper.DumpMultiyearTrendToExcel(filename3, myt);
SaveToArchiveDirectory(filename3, RelativeDirectory.Report, Constants.PresentSlice);
SaveToPublicationDirectory(filename3, Constants.PresentSlice, "4.4");
}
protected override void RunActualProcess([NotNull][ItemNotNull] List <ScenarioSliceParameters> allSlices,
[NotNull] AnalysisRepository analysisRepo)
{
Info("starting to make trafostation results");
MultiyearMultiVariableTrend myt = new MultiyearMultiVariableTrend();
foreach (var slice in allSlices)
{
HouseComponentRepository hcr = new HouseComponentRepository(analysisRepo, slice);
var houses = analysisRepo.GetSlice(slice).Fetch <House>();
Dictionary <string, double> loadPerEnergyType = new Dictionary <string, double>();
Dictionary <string, double> genPerEnergyType = new Dictionary <string, double>();
Dictionary <string, double> loadPerComponentType = new Dictionary <string, double>();
Dictionary <string, double> genPerPerComponentType = new Dictionary <string, double>();
foreach (var house in houses)
{
var components = house.CollectHouseComponents(hcr);
foreach (var component in components)
{
if (component.HausAnschlussGuid == null)
{
continue;
}
string energyType = component.EnergyType.ToString();
string componentType = component.HouseComponentType + " - " + energyType;
if (component.GenerationOrLoad == GenerationOrLoad.Load)
{
if (!loadPerEnergyType.ContainsKey(energyType))
{
loadPerEnergyType.Add(energyType, 0);
}
loadPerEnergyType[energyType] += component.EffectiveEnergyDemand;
if (!loadPerComponentType.ContainsKey(componentType))
{
loadPerComponentType.Add(componentType, 0);
}
loadPerComponentType[componentType] += component.EffectiveEnergyDemand;
}
else if (component.GenerationOrLoad == GenerationOrLoad.Generation)
{
if (!genPerEnergyType.ContainsKey(energyType))
{
genPerEnergyType.Add(energyType, 0);
}
genPerEnergyType[energyType] += component.EffectiveEnergyDemand;
if (!genPerPerComponentType.ContainsKey(componentType))
{
genPerPerComponentType.Add(componentType, 0);
}
genPerPerComponentType[componentType] += component.EffectiveEnergyDemand;
}
else
{
throw new FlaException("invalid type");
}
}
}
foreach (var pair in loadPerEnergyType)
{
myt[slice].AddValue("LoadPerEnergyType", pair.Key, pair.Value, DisplayUnit.GWh);
}
foreach (var pair in genPerEnergyType)
{
myt[slice].AddValue("GenerationPerEnergyType", pair.Key, pair.Value, DisplayUnit.GWh);
}
loadPerComponentType = Helpers.MakeSortedDictionary(loadPerComponentType);
foreach (var pair in loadPerComponentType)
{
myt[slice].AddValue("LoadPerComponentType", pair.Key, pair.Value, DisplayUnit.GWh);
}
genPerPerComponentType = Helpers.MakeSortedDictionary(genPerPerComponentType);
foreach (var pair in genPerPerComponentType)
{
myt[slice].AddValue("GenerationPerComponentType", pair.Key, pair.Value, DisplayUnit.GWh);
}
}
var filename3 = MakeAndRegisterFullFilename("TotalEnergyResultsAreaCharts.xlsx", Constants.PresentSlice);
Info("Writing results to " + filename3);
XlsxDumper.DumpMultiyearMultiVariableTrendToExcel(filename3, myt);
SaveToArchiveDirectory(filename3, RelativeDirectory.Report, Constants.PresentSlice);
}
protected override void RunActualProcess([NotNull][ItemNotNull] List <ScenarioSliceParameters> allSlices,
[NotNull] AnalysisRepository analysisRepo)
{
Info("starting to make trafostation results");
MultiyearMultiVariableTrend myt = new MultiyearMultiVariableTrend();
foreach (var slice in allSlices)
{
var dbArchive =
Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.ProfileGeneration, slice, DatabaseCode.SummedLoadForAnalysis);
var saHouses = SaveableEntry <ArchiveEntry> .GetSaveableEntry(dbArchive,
SaveableEntryTableType.SummedLoadsForAnalysis,
Services.Logger);
var entries = saHouses.LoadAllOrMatching();
var providerentries1 = entries.Where(x => x.Key.SumType == SumType.ByProvider).ToList();
double electricitySum = 0;
foreach (var entry in providerentries1)
{
if (entry.GenerationOrLoad == GenerationOrLoad.Generation)
{
continue;
}
double energy = entry.Profile.EnergySum();
string providertype = (entry.Key.ProviderType ?? throw new FlaException("No provider set")) + " " + entry.GenerationOrLoad;
Info("Providertype: " + providertype);
electricitySum += energy;
}
HouseComponentRepository hcr = new HouseComponentRepository(analysisRepo, slice);
var houses = analysisRepo.GetSlice(slice).Fetch <House>();
Dictionary <string, double> loadPerEnergyType = new Dictionary <string, double>();
Dictionary <string, double> genPerEnergyType = new Dictionary <string, double>();
foreach (var house in houses)
{
var components = house.CollectHouseComponents(hcr);
foreach (var component in components)
{
if (component.HausAnschlussGuid == null)
{
continue;
}
string energyType = component.EnergyType.ToString();
if (component.GenerationOrLoad == GenerationOrLoad.Load)
{
if (!loadPerEnergyType.ContainsKey(energyType))
{
loadPerEnergyType.Add(energyType, 0);
}
loadPerEnergyType[energyType] += component.EffectiveEnergyDemand;
}
else if (component.GenerationOrLoad == GenerationOrLoad.Generation)
{
if (!genPerEnergyType.ContainsKey(energyType))
{
genPerEnergyType.Add(energyType, 0);
}
genPerEnergyType[energyType] += component.EffectiveEnergyDemand;
}
else
{
throw new FlaException("invalid type");
}
}
}
foreach (var pair in loadPerEnergyType)
{
if (pair.Key == "Strom")
{
myt[slice].AddValue("Jahresenergiebedarf [GWh]",
ChartHelpers.GetFriendlyEnergTypeName(pair.Key),
electricitySum,
DisplayUnit.GWh);
}
else
{
myt[slice].AddValue("Jahresenergiebedarf [GWh]",
ChartHelpers.GetFriendlyEnergTypeName(pair.Key),
pair.Value,
DisplayUnit.GWh);
}
}
foreach (var pair in genPerEnergyType)
{
if (pair.Key == "Strom")
{
myt[slice].AddValue("GenerationPerEnergyType",
ChartHelpers.GetFriendlyEnergTypeName(pair.Key),
electricitySum,
DisplayUnit.GWh);
}
else
{
myt[slice].AddValue("GenerationPerEnergyType", ChartHelpers.GetFriendlyEnergTypeName(pair.Key), pair.Value, DisplayUnit.GWh);
}
}
}
var filename3 = MakeAndRegisterFullFilename("EnergieProEnergieträger.xlsx", Constants.PresentSlice);
Info("Writing results to " + filename3);
XlsxDumper.DumpMultiyearMultiVariableTrendToExcel(filename3, myt);
SaveToPublicationDirectory(filename3, Constants.PresentSlice, "5");
}
public void ProcessAllHouses([NotNull] ScenarioSliceParameters parameters,
[NotNull] Func <string, ScenarioSliceParameters, bool, string> makeAndRegisterFullFilename,
ProcessingMode processingMode,
[NotNull][ItemNotNull] List <string> developmentStatus)
{
if (!Directory.Exists(_processingResultPathForProfiles))
{
Directory.CreateDirectory(_processingResultPathForProfiles);
Thread.Sleep(500);
}
else
{
var dstDi = new DirectoryInfo(_processingResultPathForProfiles);
var files = dstDi.GetFiles();
Info("Cleaning " + files.Length + " files from result directory " + _processingResultPathForProfiles);
foreach (var file in files)
{
file.Delete();
}
}
var dbHouses = _services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, parameters);
Info("using house db in " + dbHouses.ConnectionString);
var houses = dbHouses.Fetch <House>();
HouseComponentRepository hcr = new HouseComponentRepository(dbHouses);
var households = dbHouses.Fetch <Household>();
var hausanschlusses = dbHouses.Fetch <Hausanschluss>();
var cars = dbHouses.Fetch <Car>();
var dbRaw = _services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var measuredRlmProfiles = dbRaw.Fetch <RlmProfile>();
DBDto dbdto = new DBDto(houses, hausanschlusses, cars, households, measuredRlmProfiles);
ProsumerComponentResultArchiver pra = new ProsumerComponentResultArchiver(Stage.ProfileGeneration, parameters, processingMode, _services);
var trafokreiseToProcess = PrepareListOfTrafokreiseToProcess(hausanschlusses);
List <HouseCreationAndCalculationJob> districts = new List <HouseCreationAndCalculationJob>();
var vdewValues = dbRaw.Fetch <VDEWProfileValue>();
var feiertage = dbRaw.Fetch <FeiertagImport>();
var slpProvider = new SLPProvider(parameters.DstYear, vdewValues, feiertage);
var loadProfileProviders = MakeDiContrainer(parameters, developmentStatus, hausanschlusses, houses, districts, slpProvider, dbdto);
if (processingMode == ProcessingMode.Collecting)
{
ClearAllExistingExportProfiles();
}
var lpgDirectoryInfo = GetLPGCalcDirectoryInfo(parameters);
ProfileGenerationRo pgRo = new ProfileGenerationRo();
DateTime startTime = DateTime.Now;
DateTime lastLog = DateTime.Now;
Info("Processing " + houses.Count + " houses.");
Info("Processing mode is " + processingMode);
List <string> brokenLpgDirectories = new List <string>();
List <string> brokenLpgJsons = new List <string>();
Dictionary <string, List <HouseComponentEntry> > houseComponentsByObjectID = BuildDictionaryByObjektID(houses,
hcr,
hausanschlusses,
pgRo,
out var numberOfcomponents);
int processedComponents = 0;
Stopwatch swCollecting = new Stopwatch();
Stopwatch swWriting = new Stopwatch();
Dictionary <string, int> numberOfEmptyProsumers = new Dictionary <string, int>();
HashSet <string> validHouseGuids = houses.Select(x => x.Guid).ToHashSet();
foreach (KeyValuePair <string, List <HouseComponentEntry> > pair in houseComponentsByObjectID)
{
var houseProsumers = new List <Prosumer>();
//erst alle profile einsammeln / vorbereiten
swCollecting.Start();
foreach (var component in pair.Value)
{
processedComponents++;
if (processedComponents % 1000 == 0)
{
Info(processingMode + " processed Components " + processedComponents + " / " + numberOfcomponents);
}
ProviderParameterDto ppdto = new ProviderParameterDto(component.Component, lpgDirectoryInfo.FullName, pgRo[component.Component]);
var provider = loadProfileProviders.GetCorrectProvider(component.Component);
pgRo[component.Component].UsedProvider = provider.Name;
if (processingMode == ProcessingMode.Preparing)
{
provider.PrepareLoadProfileIfNeeded(ppdto);
}
if (processingMode == ProcessingMode.Collecting)
{
if (trafokreiseToProcess.Contains(component.Hausanschluss.Trafokreis))
{
Prosumer p = provider.ProvideProfile(ppdto);
//Todo: add up profile per trafokreis, profile per provider etc right here
if (p != null)
{
// some providers that are not ready will return null
if (p.Profile?.EnergyOrPower != EnergyOrPower.Energy)
{
throw new FlaException("Got a power profile from " + provider.Name);
}
CheckProfileIntegrity(p, provider, component.Component, validHouseGuids);
// ReSharper disable once AssignNullToNotNullAttribute
pgRo[component.Component].AddProsumerInformation(p);
houseProsumers.Add(p);
pra.Archive(p);
}
else
{
if (!numberOfEmptyProsumers.ContainsKey(provider.Name))
{
numberOfEmptyProsumers.Add(provider.Name, 0);
}
numberOfEmptyProsumers[provider.Name]++;
}
}
}
}
swCollecting.Stop();
//dann alles rausschreiben
swWriting.Start();
if (processingMode == ProcessingMode.Collecting)
{
var generationProsumers = houseProsumers.Where(x => x.GenerationOrLoad == GenerationOrLoad.Generation).ToList();
var loadProsumers = houseProsumers.Where(x => x.GenerationOrLoad == GenerationOrLoad.Load).ToList();
var component = pair.Value[0];
if (loadProsumers.Count > 0)
{
Prosumer summedProsumer = GetSummedProsumer(loadProsumers, component.Hausanschluss.Trafokreis);
var haros = pgRo.HausanschlussByObjectId(pair.Key);
double maxPower = summedProsumer.Profile?.MaxPower() ?? 0;
foreach (var haro in haros)
{
haro.MaximumPower = maxPower;
}
WriteSumLineToCsv(summedProsumer, component.Hausanschluss.Trafokreis, GenerationOrLoad.Load);
}
if (generationProsumers.Count > 0)
{
var sumProfile = GetSummedProsumer(generationProsumers, component.Hausanschluss.Trafokreis);
WriteSumLineToCsv(sumProfile, component.Hausanschluss.Trafokreis, GenerationOrLoad.Generation);
// ReSharper disable once PossibleNullReferenceException
}
}
swWriting.Stop();
ReportProgress(startTime, processedComponents, numberOfcomponents, parameters, ref lastLog, swCollecting, swWriting);
}
Info("Finished processing all components, finishing up now. Duration: " + (DateTime.Now - startTime).TotalMinutes.ToString("F2") +
" minutes");
Info("collecting took " + swCollecting.Elapsed + " and writing took " + swWriting.Elapsed);
foreach (var pair in numberOfEmptyProsumers)
{
Info("NumberOfEmptyProsumers for " + pair.Key + " was " + pair.Value);
}
if (processingMode == ProcessingMode.Preparing)
{
DateTime endtime = new DateTime(parameters.DstYear, 12, 31);
string houseJobDirectory = Path.Combine(_services.RunningConfig.Directories.HouseJobsDirectory,
parameters.DstScenario.ToString(),
parameters.DstYear.ToString());
DirectoryInfo houseJobDi = new DirectoryInfo(houseJobDirectory);
WriteDistrictsForLPG(districts, houseJobDi, _services.Logger, parameters, endtime, pgRo);
}
else
{
DateTime startSavingToDB = DateTime.Now;
pra.FinishSavingEverything();
Info("Finished writing prosumers to db. Duration: " + (DateTime.Now - startSavingToDB).TotalMinutes.ToString("F2") + " minutes");
}
pra.Dispose();
var excelFiles = WriteExcelResultFiles(parameters, makeAndRegisterFullFilename, processingMode, pgRo);
if (_services.RunningConfig.CollectFilesForArchive)
{
SaveToArchiveDirectory(parameters, excelFiles, _services.StartingTime, _services.RunningConfig);
}
WriteBrokenLpgCalcCleanupBatch(parameters, makeAndRegisterFullFilename, processingMode, brokenLpgDirectories, brokenLpgJsons);
if (processingMode == ProcessingMode.Collecting)
{
foreach (var provider in loadProfileProviders.Providers)
{
provider.DoFinishCheck();
}
}
}
private Dictionary <string, List <HouseComponentEntry> > BuildDictionaryByObjektID([NotNull][ItemNotNull] List <House> houses,
[NotNull] HouseComponentRepository hcr,
[NotNull][ItemNotNull] List <Hausanschluss> hausanschlusses,
[NotNull] ProfileGenerationRo pgRo,
out int numberOfcomponents)
{
var houseComponentsByObjectID = new Dictionary <string, List <HouseComponentEntry> >();
numberOfcomponents = 0;
int housecount = 0;
foreach (House house in houses)
{
pgRo.AddHouse(house);
var houseComponents = house.CollectHouseComponents(hcr);
pgRo[house].NumberOfComponents = houseComponents.Count;
var hausanschlussGuids = houseComponents.Where(x => x.HausAnschlussGuid != null).Select(x => x.HausAnschlussGuid).Distinct().ToList();
foreach (string hausanschlussGuid in hausanschlussGuids)
{
var hausanschluss = hausanschlusses.First(x => x.Guid == hausanschlussGuid);
pgRo.AddHausanschluss(house, hausanschluss, "Mit Komponenten");
var anschlusshouseComponents = houseComponents.Where(x => x.HausAnschlussGuid == hausanschlussGuid).ToList();
if (!houseComponentsByObjectID.ContainsKey(hausanschluss.ObjectID))
{
houseComponentsByObjectID.Add(hausanschluss.ObjectID, new List <HouseComponentEntry>());
}
foreach (IHouseComponent component in anschlusshouseComponents)
{
houseComponentsByObjectID[hausanschluss.ObjectID].Add(new HouseComponentEntry(component, hausanschluss, house));
numberOfcomponents++;
pgRo.AddHouseComponent(house, hausanschluss, component);
}
}
foreach (var hausanschluss in house.Hausanschluss)
{
if (hausanschlussGuids.Contains(hausanschluss.Guid))
{
continue;
}
pgRo.AddHausanschluss(house, hausanschluss, "Ohne Komponenten");
}
housecount++;
if (_services.RunningConfig.LimitLoadProfileGenerationToHouses > 0)
{
if (housecount > _services.RunningConfig.LimitLoadProfileGenerationToHouses)
{
break;
}
}
}
return(houseComponentsByObjectID);
}
