protected override void RunActualProcess()
{
SqlConnection.RecreateTable <HeatingSystemEntry>(Stage.Houses, Constants.PresentSlice);
var dbHouses = SqlConnection.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice).Database;
var dbRaw = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice).Database;
var dbComplexes = SqlConnection.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice).Database;
var rnd = new Random();
var houses = dbHouses.Fetch <House>();
var houseHeatingMethods = dbHouses.Fetch <HouseHeating>();
var feuerungsstättenRaw = dbRaw.Fetch <FeuerungsStaette>();
var complexes = dbComplexes.Fetch <BuildingComplex>();
Dictionary <string, BuildingComplex> buildingComplexesByName = new Dictionary <string, BuildingComplex>();
foreach (BuildingComplex complex in complexes)
{
buildingComplexesByName.Add(complex.ComplexName, complex);
}
var potentialHeatingSystems = dbHouses.Fetch <PotentialHeatingSystemEntry>();
dbHouses.BeginTransaction();
OverrideRepository overrideRepository = new OverrideRepository();
double totalFernwärme = potentialHeatingSystems.Sum(x => x.YearlyFernwärmeDemand);
var overrideEntries = overrideRepository.ReadEntries();
var houseNames = houses.Select(x => x.ComplexName).ToHashSet();
foreach (OverrideEntry entry in overrideEntries)
{
if (!houseNames.Contains(entry.ComplexName))
{
throw new FlaException("Kein Haus für Override Entry " + entry.ComplexName + ". Vermutlich vertippt?");
}
}
List <HeatingSystemEntry> hses = new List <HeatingSystemEntry>();
foreach (var house in houses)
{
var complex = buildingComplexesByName[house.ComplexName];
var feuerungsStättenForHouse = feuerungsstättenRaw.Where(x => {
Debug.Assert(x.EGID != null, "x.EGID != null");
return(complex.EGids.Contains((long)x.EGID));
}).ToList();
//set the age of the heating system, randomly up to 30 years old
var feuerungsstättenType = string.Join(",", feuerungsStättenForHouse.Select(x => x.Brennstoff?.ToString()));
var hausanschluss = house.Hausanschluss[0];
var hse = new HeatingSystemEntry(house.HouseGuid, Guid.NewGuid().ToString(), feuerungsstättenType,
hausanschluss.HausanschlussGuid, hausanschluss.Adress);
var oldestYear = feuerungsStättenForHouse.Min(x => x.KesselBaujahr);
if (oldestYear == null)
{
hse.Age = rnd.Next(30);
}
else
{
hse.Age = 2019 - oldestYear.Value;
}
hse.FeuerungsstättenPower = feuerungsStättenForHouse.Sum(x => {
if (x.KesselLeistung == null)
{
throw new FlaException("Kesselleistung was null");
}
return((double)x.KesselLeistung);
});
hse.EstimatedMinimumEnergyFromFeuerungsStätten = hse.FeuerungsstättenPower * 1500;
hse.EstimatedMaximumEnergyFromFeuerungsStätten = hse.FeuerungsstättenPower * 2200;
//this is a localnet heating system, so use the localnet information
var potentialHouseHeatingSystems = potentialHeatingSystems.Where(x => x.HouseGuid == house.HouseGuid).ToList();
var houseHeatingMethod = houseHeatingMethods.Single(x => x.HouseGuid == house.HouseGuid);
hse.AverageHeatingEnergyDemandDensity = houseHeatingMethod.MergedHeatingEnergyDensity;
OverrideEntry ore = overrideEntries.FirstOrDefault(x => x.ComplexName == house.ComplexName);
if (ore != null)
{
Info("Override Entry for " + house.ComplexName);
}
if (potentialHouseHeatingSystems.Count > 0)
{
var totalGas = potentialHouseHeatingSystems.Sum(x => x.YearlyGasDemand);
var totalWärme = potentialHouseHeatingSystems.Sum(x => x.YearlyFernwärmeDemand);
if (totalGas > 0)
{
//localnet heizung
hse.YearlyEnergyDemand = totalGas;
hse.OriginalHeatingSystemType = HeatingSystemType.GasheatingLocalnet;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Gas;
if (totalWärme > 0)
{
throw new Exception("Both wärme and gas");
}
if (ore != null)
{
throw new FlaException("Overrride Entry für Localnet Gas Gebäude: " + house.ComplexName + ", but that doesn't make sense");
}
}
if (totalWärme > 0)
{
hse.YearlyEnergyDemand = totalWärme;
hse.OriginalHeatingSystemType = HeatingSystemType.FernwärmeLocalnet;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Fernwärme;
if (ore != null)
{
throw new FlaException("Overrride Entry für Localnet Gebäude: " + house.ComplexName);
}
}
} // no data, so use feuerungsstätten
else if (ore != null)
{
//kanton heizung
hse.OriginalHeatingSystemType = HeatingSystemType.None;
hse.SynthesizedHeatingSystemType = ore.HeatingSystemType;
hse.YearlyEnergyDemand = ore.Amount;
}
else if (feuerungsStättenForHouse.Count > 0)
{
var fs = feuerungsStättenForHouse[0];
if (fs.Brennstoff == "Oel")
{
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenOil;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingMethod.KantonTotalEnergyDemand;
}
else if (fs.Brennstoff == "Gas")
{
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenGas;
//beco says gas, but can't be, because localnet says no
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingMethod.KantonTotalEnergyDemand;
}
else
{
throw new Exception("invalid heating system");
}
} // no beco, so use ebbe daten
else if (houseHeatingMethod.KantonHeatingMethods.Count > 0)
{
//kanton heizung
var kantonHeatingMethod = houseHeatingMethod.KantonHeatingMethods[0];
GetKantonHeatingSystem(kantonHeatingMethod, hse, houseHeatingMethod);
}
else if (feuerungsStättenForHouse.Count > 0)
{
var fs = feuerungsStättenForHouse[0];
if (fs.Brennstoff == "Oel")
{
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenOil;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingMethod.KantonTotalEnergyDemand;
}
else if (fs.Brennstoff == "Gas")
{
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenGas;
//beco says gas, but can't be, because localnet says no
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingMethod.KantonTotalEnergyDemand;
}
else
{
throw new Exception("invalid heating system");
}
} // no beco, so use ebbe daten
else if (houseHeatingMethod.KantonHeatingMethods.Count > 0)
{
//kanton heizung
var kantonHeatingMethod = houseHeatingMethod.KantonHeatingMethods[0];
GetKantonHeatingSystem(kantonHeatingMethod, hse, houseHeatingMethod);
}
else
{
hse.OriginalHeatingSystemType = HeatingSystemType.None;
hse.SynthesizedHeatingSystemType = HeatingSystemType.None;
hse.YearlyEnergyDemand = 0;
}
dbHouses.Save(hse);
hses.Add(hse);
}
dbHouses.CompleteTransaction();
double finalFernwärme = hses.Where(x => x.SynthesizedHeatingSystemType == HeatingSystemType.Fernwärme).Sum(x => x.YearlyEnergyDemand);
if (Math.Abs(finalFernwärme - totalFernwärme) > 1)
{
throw new FlaException("Fernwärme changed: Nach allem:" + finalFernwärme + " davor: " + totalFernwärme);
}
}
// ReSharper disable once FunctionComplexityOverflow
private void WriteOneLine([NotNull] ExcelWorksheet ws, int row, [NotNull] Dictionary <Columns, 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)
{
ws.Cells[row, columnNumbers[Columns.ComplexName]].Value = house.ComplexName;
ws.Cells[row, columnNumbers[Columns.Adressen]].Value = mycomplex.AdressesAsJson;
ws.Cells[row, columnNumbers[Columns.EGids]].Value = CleanJson(mycomplex.EGIDsAsJson);
ws.Cells[row, columnNumbers[Columns.LocalnetISNIds]].Value = CleanJson(mycomplex.GebäudeObjectIDsAsJson);
ws.Cells[row, columnNumbers[Columns.GeoKoordinaten]].Value = CleanJson(mycomplex.GeoCoordsAsJson);
var gwrs = gwr.Where(x => {
Debug.Assert(x.EidgGebaeudeidentifikator_EGID != null, "x.EidgGebaeudeidentifikator_EGID != null");
return(mycomplex.EGids.Contains((long)x.EidgGebaeudeidentifikator_EGID));
}).ToList();
ws.Cells[row, columnNumbers[Columns.WG_GWR_GebäudeAnzahl]].Value = gwrs.Count;
var ebbes = kanton.Where(x => mycomplex.EGids.Contains(x.egid)).ToList();
ws.Cells[row, columnNumbers[Columns.WG_GEAK_Anzahl]].Value = ebbes.Sum(x => x.has_geak);
ws.Cells[row, columnNumbers[Columns.EBBE_GebäudeTyp]].Value = CollapseList(ebbes.Select(x => x.upd_gtyp));
ws.Cells[row, columnNumbers[Columns.EBBE_GebäudeTyp]].Value = ws.Cells[row, columnNumbers[Columns.GWR_WG]].Value = gwrs.Sum(x => x.AnzahlWohnungen_GANZWHG);
ws.Cells[row, columnNumbers[Columns.EBBE_GArea]].Value = ebbes.Sum(x => x.garea);
ws.Cells[row, columnNumbers[Columns.EBBE_EBF_HZ_Updated]].Value = ebbes.Sum(x => x.upd_ebf);
ws.Cells[row, columnNumbers[Columns.EBBE_calc_ehzww]].Value = ebbes.Sum(x => x.calc_ehzww);
ws.Cells[row, columnNumbers[Columns.EBBE_calc_ehz]].Value = ebbes.Sum(x => x.calc_ehz);
ws.Cells[row, columnNumbers[Columns.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[Columns.EBHZ_OL]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7202:
ws.Cells[row, columnNumbers[Columns.EBHZ_KO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7203:
ws.Cells[row, columnNumbers[Columns.EBHZ_GZ]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7204:
ws.Cells[row, columnNumbers[Columns.EBHZ_EL]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7205:
ws.Cells[row, columnNumbers[Columns.EBHZ_HO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7206:
ws.Cells[row, columnNumbers[Columns.EBHZ_WP]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7207:
ws.Cells[row, columnNumbers[Columns.EBHZ_SO]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7208:
ws.Cells[row, columnNumbers[Columns.EBHZ_FW]].Value = ebbes.Sum(x => x.calc_ehz);
break;
case 7209:
ws.Cells[row, columnNumbers[Columns.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[Columns.EBWW_OL]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7202:
ws.Cells[row, columnNumbers[Columns.EBWW_KO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7203:
ws.Cells[row, columnNumbers[Columns.EBWW_GZ]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7204:
ws.Cells[row, columnNumbers[Columns.EBWW_EL]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7205:
ws.Cells[row, columnNumbers[Columns.EBWW_HO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7206:
ws.Cells[row, columnNumbers[Columns.EBWW_WP]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7207:
ws.Cells[row, columnNumbers[Columns.EBWW_SO]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7208:
ws.Cells[row, columnNumbers[Columns.EBWW_FW]].Value = ebbes.Sum(x => x.calc_eww);
break;
case 7209:
ws.Cells[row, columnNumbers[Columns.EBWW_A]].Value = ebbes.Sum(x => x.calc_eww);
break;
default: throw new Exception("Unknown Heizungsträger: " + heizträger);
}
/*
*
*
* ,
* EBWW_U,
* EBWW_OL,
* EBWW_KO,
* EBWW_GZ,
* EBWW_EL,
* EBWW_HO,
* EBWW_WP,
* EBWW_SO,
* EBWW_FW,
* EBWW_A,
*
*/
ws.Cells[row, columnNumbers[Columns.EBBE_EnergieträgerHz]].Value = CollapseList(ebbes.Select(x => x.upd_genhz.ToString()));
ws.Cells[row, columnNumbers[Columns.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[Columns.Localnet_Strom]].Value = monthlies.Sum(x => x.YearlyElectricityUseNetz);
ws.Cells[row, columnNumbers[Columns.Localnet_Gas]].Value = monthlies.Sum(x => x.YearlyGasUse);
ws.Cells[row, columnNumbers[Columns.Localnet_Wärme]].Value = monthlies.Sum(x => x.YearlyFernwaermeUse);
ws.Cells[row, columnNumbers[Columns.BFH_EnergieträgerHeizung]].Value = heatingSystem.SynthesizedHeatingSystemType.ToString();
ws.Cells[row, columnNumbers[Columns.BFH_EnergieBedarfHeizung]].Value = heatingSystem.YearlyEnergyDemand;
ws.Cells[row, columnNumbers[Columns.BFH_Einwohner]].Value = occupants.Count;
var businessTypes = businessEntries.Select(x => x.BusinessType.ToString()).Distinct().ToList();
var businessTypeStr = string.Join(",", businessTypes);
ws.Cells[row, columnNumbers[Columns.BFH_Geschäfte]].Value = businessTypeStr;
if (pvsystem != null)
{
ws.Cells[row, columnNumbers[Columns.BFH_PVSystemGrösseInKWh]].Value = pvsystem.YearlyPotential;
}
ws.Cells[row, columnNumbers[Columns.BFH_Geschäfte]].Value = businessTypeStr;
ws.Cells[row, columnNumbers[Columns.BFH_StromHaushalteLow]].Value =
households.Sum(x => x.LowVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Columns.BFH_StromHaushalteLow]].Value =
households.Sum(x => x.HighVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Columns.BFH_StromGewerbeLow]].Value = businessEntries.Sum(x => x.LowVoltageYearlyTotalElectricityUse);
ws.Cells[row, columnNumbers[Columns.BFH_StromGewerbeHigh]].Value = businessEntries.Sum(x => x.HighVoltageYearlyTotalElectricityUse);
//ws.Cells[row, columnNumbers[Columns.BFH_GasGewerbe]].Value = businessEntries.Sum(x => x.YearlyGasUse);
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenArt]].Value = heatingSystem.FeuerungsstättenType;
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenKesselLeistung]].Value = heatingSystem.FeuerungsstättenPower;
if (heatingSystem.FeuerungsstättenType == "Gas")
{
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenJahresEnergie1500hGas]].Value = heatingSystem.EstimatedMinimumEnergyFromFeuerungsStätten;
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenJahresEnergie2200hGas]].Value = heatingSystem.EstimatedMaximumEnergyFromFeuerungsStätten;
}
if (heatingSystem.FeuerungsstättenType == "Oel")
{
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenJahresEnergie1500hOel]].Value = heatingSystem.EstimatedMinimumEnergyFromFeuerungsStätten;
ws.Cells[row, columnNumbers[Columns.FeuerungsstättenJahresEnergie2200hOel]].Value = heatingSystem.EstimatedMaximumEnergyFromFeuerungsStätten;
}
ws.Cells[row, columnNumbers[Columns.BFH_AnzahlFahrzeuge]].Value = carDistances.Count;
ws.Cells[row, columnNumbers[Columns.BFH_SummeAutoPendlerdistanz]].Value = carDistances.Sum(x => x.CommutingDistance) * 365;
ws.Cells[row, columnNumbers[Columns.BFH_SummeAutoGesamtdistanz]].Value = carDistances.Sum(x => x.TotalDistance) * 365;
ws.Cells[row, columnNumbers[Columns.BFH_SonnendachPotential]].Value = heatingSystem.FeuerungsstättenPower;
ws.Cells[row, columnNumbers[Columns.TrafoKreis]].Value = string.Join(";", house.Hausanschluss.Select(x => x.Trafokreis));
}
private static void GetKantonHeatingSystem(HeatingSystemType kantonHeatingMethod, [NotNull] HeatingSystemEntry hse, [NotNull] HouseHeating houseHeatingData)
{
switch (kantonHeatingMethod)
{
case HeatingSystemType.Electricity:
hse.OriginalHeatingSystemType = HeatingSystemType.Electricity;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Electricity;
// change this to consider the canton values and leave some electricty rest
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Heatpump:
hse.OriginalHeatingSystemType = HeatingSystemType.Heatpump;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Heatpump;
// change this to consider the canton values and leave some electricty rest
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.SolarThermal:
hse.OriginalHeatingSystemType = HeatingSystemType.SolarThermal;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Other;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Gas:
//can't be gas, since localnet says it is not gas
hse.OriginalHeatingSystemType = HeatingSystemType.Gas;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Fernwärme:
hse.OriginalHeatingSystemType = HeatingSystemType.Fernwärme;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Öl:
hse.OriginalHeatingSystemType = HeatingSystemType.Öl;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
//: change this to consider the canton values and leave some electricty rest
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Kohle:
hse.OriginalHeatingSystemType = HeatingSystemType.Kohle;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Other;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.None:
hse.OriginalHeatingSystemType = HeatingSystemType.None;
hse.SynthesizedHeatingSystemType = HeatingSystemType.None;
if (houseHeatingData.KantonTotalEnergyDemand > 1)
{
throw new Exception("No heating method but energy demand");
}
break;
case HeatingSystemType.Other:
hse.OriginalHeatingSystemType = HeatingSystemType.Other;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Other;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Holz:
hse.OriginalHeatingSystemType = HeatingSystemType.Holz;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Other;
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
break;
case HeatingSystemType.Unbekannt:
break;
case HeatingSystemType.GasheatingLocalnet:
hse.OriginalHeatingSystemType = HeatingSystemType.GasheatingLocalnet;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Gas;
hse.YearlyEnergyDemand = houseHeatingData.LocalnetGasEnergyUse;
break;
case HeatingSystemType.FernwärmeLocalnet:
hse.OriginalHeatingSystemType = HeatingSystemType.FernwärmeLocalnet;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Fernwärme;
hse.YearlyEnergyDemand = houseHeatingData.LocalnetFernwärmeEnergyUse;
break;
case HeatingSystemType.FeuerungsstättenOil:
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenOil;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
break;
case HeatingSystemType.FeuerungsstättenGas:
hse.YearlyEnergyDemand = houseHeatingData.KantonTotalEnergyDemand;
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenGas;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
break;
default: throw new Exception("Unknown heating method: " + kantonHeatingMethod);
}
}
