public List <OverrideEntry> ReadEntries() { string path = @"V:\Dropbox\BurgdorfStatistics\Corrections\Corrections.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 = row + 1; } p.Dispose(); return(ores); }
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); } }