public List <OverrideEntry> ReadEntries([NotNull] ServiceRepository services)
{
string path = Path.Combine(services.RunningConfig.Directories.BaseUserSettingsDirectory, "HeatingSystemOverrides.xlsx");
var p = new ExcelPackage(new FileInfo(path));
var ws = p.Workbook.Worksheets[1];
int row = 2;
List <OverrideEntry> ores = new List <OverrideEntry>();
while (ws.Cells[row, 1].Value != null)
{
string name = (string)ws.Cells[row, 1].Value;
string heatingSystemTypeStr = (string)ws.Cells[row, 2].Value;
HeatingSystemType hst = (HeatingSystemType)Enum.Parse(typeof(HeatingSystemType), heatingSystemTypeStr);
string energyDemandSourceStr = (string)ws.Cells[row, 3].Value;
EnergyDemandSource eds = (EnergyDemandSource)Enum.Parse(typeof(EnergyDemandSource), energyDemandSourceStr);
double amount = (double)ws.Cells[row, 4].Value;
OverrideEntry ore = new OverrideEntry(name, hst, eds, amount);
ores.Add(ore);
row += 1;
}
p.Dispose();
return(ores);
}
protected override void RunActualProcess()
{
var dbHouses = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
dbHouses.RecreateTable <HeatingSystemEntry>();
var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var dbComplexes = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice);
var rnd = new Random();
var houses = dbHouses.Fetch <House>();
var hausanschlusses = dbHouses.Fetch <Hausanschluss>();
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(Services);
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];
List <FeuerungsStaette> feuerungsStättenForHouse = feuerungsstättenRaw.Where(x => {
if (x.EGID == null)
{
throw new FlaException("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()).Distinct());
var hausanschluss = house.GetHausanschlussByIsn(new List <int>(), null, hausanschlusses, MyLogger, false);
if (hausanschluss != null && hausanschluss.ObjectID.ToLower().Contains("kleinanschluss"))
{
hausanschluss = null;
}
var hse = new HeatingSystemEntry(house.Guid,
Guid.NewGuid().ToString(),
feuerungsstättenType,
hausanschluss?.Guid,
house.ComplexName,
house.ComplexName + " - Heating System - Unknown");
hses.Add(hse);
var oldestYear = feuerungsStättenForHouse.Min(x => x.KesselBaujahr);
if (oldestYear == null || oldestYear.Value < 1800)
{
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.Guid).ToList();
var houseHeatingMethod = houseHeatingMethods.Single(x => x.HouseGuid == house.Guid);
double totalHeatDemand = 0;
OverrideEntry ore = overrideEntries.FirstOrDefault(x => x.ComplexName == house.ComplexName);
if (ore != null)
{
Debug("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
totalHeatDemand = 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)
{
totalHeatDemand = totalWärme;
hse.OriginalHeatingSystemType = HeatingSystemType.FernwärmeLocalnet;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Fernwärme;
}
} // no data, so use feuerungsstätten
else if (ore != null)
{
//kanton heizung
hse.OriginalHeatingSystemType = HeatingSystemType.None;
if (ore.HeatingSystemType == HeatingSystemType.Heatpump && hse.HausAnschlussGuid == null)
{
hse.SynthesizedHeatingSystemType = HeatingSystemType.None;
}
hse.SynthesizedHeatingSystemType = ore.HeatingSystemType;
totalHeatDemand = 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;
totalHeatDemand = 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;
totalHeatDemand = 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, ref totalHeatDemand);
}
else if (feuerungsStättenForHouse.Count > 0)
{
var fs = feuerungsStättenForHouse[0];
if (fs.Brennstoff == "Oel")
{
hse.OriginalHeatingSystemType = HeatingSystemType.FeuerungsstättenOil;
hse.SynthesizedHeatingSystemType = HeatingSystemType.Öl;
totalHeatDemand = 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;
totalHeatDemand = 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, ref totalHeatDemand);
}
else
{
hse.OriginalHeatingSystemType = HeatingSystemType.None;
hse.SynthesizedHeatingSystemType = HeatingSystemType.None;
totalHeatDemand = 0;
}
if (hse.SynthesizedHeatingSystemType == HeatingSystemType.Heatpump && hse.HausAnschlussGuid == null)
{
hse.SynthesizedHeatingSystemType = HeatingSystemType.None;
}
if (hse.SynthesizedHeatingSystemType == HeatingSystemType.Electricity && hse.HausAnschlussGuid == null)
{
throw new FlaException("electric heating without anschluss");
}
if (hse.SynthesizedHeatingSystemType == HeatingSystemType.Heatpump && hse.HausAnschlussGuid == null)
{
throw new FlaException("hp heating without anschluss");
}
hse.Standort = house.ComplexName + " - " + hse.SynthesizedHeatingSystemType;
hse.ProvideProfile = false;
hse.HeatingSystemType2017 = hse.SynthesizedHeatingSystemType;
if (house.Appartments.Count == 0)
{
throw new FlaException("Not a single area in the house " + house.ComplexName);
}
double avgHeatDemand = totalHeatDemand / house.Appartments.Count;
foreach (var appartment in house.Appartments)
{
hse.HeatDemands.Add(new AppartmentHeatingDemand(appartment.Guid,
appartment.EnergieBezugsFläche,
avgHeatDemand,
Constants.PresentSlice.DstYear));
}
if (Math.Abs(hse.HeatDemand - totalHeatDemand) > 0.01)
{
throw new FlaException("Invalid heat demand");
}
hse.OriginalHeatDemand2017 = hse.HeatDemand;
dbHouses.Save(hse);
}
dbHouses.CompleteTransaction();
double finalFernwärme = hses.Where(x => x.SynthesizedHeatingSystemType == HeatingSystemType.Fernwärme).Sum(x => x.EffectiveEnergyDemand);
if (Math.Abs(finalFernwärme - totalFernwärme) > 1)
{
throw new FlaException("Fernwärme changed: Nach allem:" + finalFernwärme + " davor: " + totalFernwärme);
}
RowCollection rc = new RowCollection("Validation", "Validierung");
foreach (var feuerungsStaette in feuerungsstättenRaw)
{
string adress = feuerungsStaette.Strasse + " " + feuerungsStaette.Hausnummer;
RowBuilder rb = RowBuilder.Start("FeuerungsAdresse", adress);
rc.Add(rb);
rb.Add("Brennstoff", feuerungsStaette.Brennstoff);
rb.Add("Energienutzung", feuerungsStaette.Energienutzung);
rb.Add("Leistung", feuerungsStaette.KesselLeistung);
if (feuerungsStaette.EGID != null)
{
int egid = (int)feuerungsStaette.EGID;
var house = houses.FirstOrDefault(x => x.EGIDs.Contains(egid));
if (house != null)
{
rb.Add("Haus", house.ComplexName);
rb.Add("HausAdresse", house.Adress);
HeatingSystemEntry hse = hses.Single(x => x.HouseGuid == house.Guid);
rb.Add("Gewählter Heizungstyp 1", hse.OriginalHeatingSystemType.ToString());
rb.Add("Gewählter Heizungstyp 2", hse.SynthesizedHeatingSystemType.ToString());
rb.Add("EBF", house.EnergieBezugsFläche);
}
else
{
var h2 = houses.FirstOrDefault(x => x.Adress?.Contains(adress) == true);
if (h2 != null)
{
rb.Add("Findbar über Adresse", h2.ComplexName);
HeatingSystemEntry hse = hses.Single(x => x.HouseGuid == h2.Guid);
rb.Add("Gewählter Heizungstyp 1", hse.OriginalHeatingSystemType.ToString());
rb.Add("Gewählter Heizungstyp 2", hse.SynthesizedHeatingSystemType.ToString());
rb.Add("EBF", h2.EnergieBezugsFläche);
}
}
}
else
{
rb.Add("Egid fehlt", "True");
}
}
var fn = MakeAndRegisterFullFilename("Feuerungsstätten-Validation.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(fn, rc);
}
