protected override void RunActualProcess()
{
string fn = CombineForRaw("GWRAdressen.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "W4000", out var _);
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1) - 1; i++)
{
var o = arr[1, i];
if (o == null)
{
throw new Exception("Value was null");
}
hdict.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <GwrAdresse>();
db.BeginTransaction();
for (var row = 2; row < arr.GetLength(0); row++)
{
var a = new GwrAdresse();
if (arr[row, hdict["EGID"]] == null)
{
continue;
}
a.EidgGebaeudeidentifikator_EGID = Convert.ToInt32(arr[row, hdict["EGID"]]);
a.EidgEingangsidentifikator_EDID = Helpers.GetInt(arr[row, hdict["EDID"]]);
a.ErhebungsstelleBaustatistik_DESTNR = Helpers.GetInt(arr[row, hdict["DESTNR"]]);
a.BauprojektIdLiefersystem_DBABID = (string)arr[row, hdict["DBABID"]];
a.EingangsIdLiefersystem_DBADID = (string)arr[row, hdict["DBADID"]];
a.BFSGemeindenummer_GGDENR = Helpers.GetInt(arr[row, hdict["GGDENR"]]);
a.Gebaeudeeingangstatus_DSTAT = Helpers.GetInt(arr[row, hdict["DSTAT*"]]);
a.Strassenbezeichnung_DSTR = (string)arr[row, hdict["DSTR"]];
a.EingangsnummerGebaeude_DEINR = (string)arr[row, hdict["DEINR"]];
a.AmtlicheStrassennummer_DSTRANR = Helpers.GetInt(arr[row, hdict["DSTRANR"]]);
a.EidgStrassenidentifikator_DSTRID = Helpers.GetInt(arr[row, hdict["DSTRID"]]);
a.AmtlicherAdresscode_DADRC = Helpers.GetInt(arr[row, hdict["DADRC"]]);
// ReSharper disable once PossibleInvalidOperationException
a.Postleitzahl_DPLZ4 = (int)Helpers.GetDouble(arr[row, hdict["DPLZ4"]]);
a.PLZZusatzziffer_DPLZZ = Helpers.GetInt(arr[row, hdict["DPLZZ"]]);
a.EKoordinate_DKODE = Helpers.GetDouble(arr[row, hdict["DKODE"]]);
a.NKoordinate_DKODN = Helpers.GetDouble(arr[row, hdict["DKODN"]]);
a.XKoordinate_DKODX = Helpers.GetDouble(arr[row, hdict["DKODX"]]);
a.YKoordinate_DKODY = Helpers.GetDouble(arr[row, hdict["DKODY"]]);
a.Plausibilitaetsstatus_DPLAUS = Helpers.GetInt(arr[row, hdict["DPLAUS*"]]);
// a.DatumderletztenAenderung_DMUTDAT = Helpers.GetDateTime(arr[row, hdict["DMUTDAT"]])
// a.DatumdesExports_DEXPDAT = Helpers.GetDateTime(arr[row, hdict["DEXPDAT"]])
// a. = (string)(arr[row, hdict[""]])
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
var dbProfiles = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
dbProfiles.RecreateTable <VDEWProfileValue>();
string filename = CombineForFlaSettings("VDEWProfile.xlsx");
Info("Importing " + filename);
dbProfiles.BeginTransaction();
//var wb = ExcelHelper.OpenXls(filename, out var app);
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
for (var sheet = 1; sheet < 12; sheet++)
{
var arr = eh.ExtractDataFromExcel2(filename, sheet, "A1", "K102", out var sheetname);
ExtractColum(sheetname, 1, Season.Winter, TagTyp.Samstag, arr, dbProfiles);
ExtractColum(sheetname, 2, Season.Winter, TagTyp.Sonntag, arr, dbProfiles);
ExtractColum(sheetname, 3, Season.Winter, TagTyp.Werktag, arr, dbProfiles);
ExtractColum(sheetname, 4, Season.Sommer, TagTyp.Samstag, arr, dbProfiles);
ExtractColum(sheetname, 5, Season.Sommer, TagTyp.Sonntag, arr, dbProfiles);
ExtractColum(sheetname, 6, Season.Sommer, TagTyp.Werktag, arr, dbProfiles);
ExtractColum(sheetname, 7, Season.Uebergang, TagTyp.Samstag, arr, dbProfiles);
ExtractColum(sheetname, 8, Season.Uebergang, TagTyp.Sonntag, arr, dbProfiles);
ExtractColum(sheetname, 9, Season.Uebergang, TagTyp.Werktag, arr, dbProfiles);
}
dbProfiles.CompleteTransaction();
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("LeftoverAdressTranslationTable.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "B400", out var _);
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <AdressTranslationEntry>();
db.BeginTransaction();
for (var row = 0; row < arr.GetLength(0); row++)
{
var a = new AdressTranslationEntry();
if (arr[row, 0] == null)
{
continue;
}
a.OriginalStandort = (string)arr[row, 0];
if (arr[row, 1] == null)
{
throw new Exception("dst adress was null");
}
a.TranslatedAdress = (string)arr[row, 1];
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
// ReSharper disable once StringLiteralTypo
string fn = CombineForRaw("reduced.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "AH476000", out var _);
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1) - 1; i++)
{
var o = arr[0, i];
if (o == null)
{
throw new Exception("was null");
}
hdict.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <Localnet>();
db.BeginTransaction();
int negativeIsnCounter = -1000;
for (var row = 1; row < arr.GetLength(0); row++)
{
var a = new Localnet();
if (arr[row, hdict["Termin"]] == null)
{
continue;
}
AssignFields(a, arr, row, hdict, ref negativeIsnCounter);
db.Save(a);
}
db.CompleteTransaction();
var readVals = db.Fetch <Localnet>();
foreach (Localnet localnet in readVals)
{
if (localnet.ObjektIDGebäude == -2146826246)
{
throw new FlaException("-");
}
if (localnet.ObjektIDGebäude == null)
{
throw new FlaException("was null");
}
}
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("FeuerungsStätten.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "P2400", out var _);
var headerToColumns = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1) - 1; i++)
{
var o = arr[0, i] ?? "";
headerToColumns.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <FeuerungsStaette>();
db.BeginTransaction();
for (var row = 2; row < arr.GetLength(0); row++)
{
var a = new FeuerungsStaette();
if (arr[row, headerToColumns["EGID"]] == null)
{
continue;
}
a.AnlageNr = Convert.ToInt32(arr[row, headerToColumns["AnlageNr"]]);
a.AnlageStatus = (string)arr[row, headerToColumns["AnlageStatus"]];
a.Strasse = (string)arr[row, headerToColumns["Strasse"]];
var hausnummer = arr[row, headerToColumns["Hausnummer"]];
if (hausnummer != null)
{
a.Hausnummer = hausnummer.ToString();
}
a.PLZ = Helpers.GetInt(arr[row, headerToColumns["PLZ"]]);
a.Ort = (string)arr[row, headerToColumns["Ort"]];
a.EGID = Helpers.GetInt(arr[row, headerToColumns["EGID"]]);
a.EDID = Helpers.GetInt(arr[row, headerToColumns["EDID"]]);
a.XKoordinate = Helpers.GetInt(arr[row, headerToColumns["X-Koordinate"]]);
a.YKoordinate = Helpers.GetInt(arr[row, headerToColumns["Y-Koordinate"]]);
a.Gebäudeart = (string)arr[row, headerToColumns["Gebäudeart"]];
a.Brennstoff = (string)arr[row, headerToColumns["Brennstoff"]];
a.KesselBaujahr = Helpers.GetInt(arr[row, headerToColumns["KesselBaujahr"]]);
a.KesselLeistung = Helpers.GetInt(arr[row, headerToColumns["KesselLeistung"]]);
a.Energienutzung = (string)arr[row, headerToColumns["Energienutzung"]];
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("2018.05.01_PVA-Anlagen_Daten.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "K205", out var _);
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[0, i];
if (o == null)
{
throw new Exception("Value was null");
}
hdict.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <LocalnetPVAnlage>();
db.BeginTransaction();
for (var row = 1; row < arr.GetLength(0); row++)
{
var a = new LocalnetPVAnlage();
if (arr[row, hdict["Bezeichnung"]] == null)
{
continue;
}
a.Bezeichnung = Helpers.GetStringNotNull(arr[row, hdict["Bezeichnung"]]);
a.Anlagenummer = Helpers.GetStringNotNull(arr[row, hdict["Anlagenummer"]]);
a.Adresse = Helpers.GetStringNotNull(arr[row, hdict["Adresse"]]);
a.Inbetriebnahme = Helpers.GetDateTime(arr[row, hdict["Inbetriebnahme (Jahr)"]]).Year.ToString();
a.Leistungkwp = Helpers.GetNoNullDouble(arr[row, hdict["Solargenerator Leistung DC [kWp]"]]);
a.HKoord = Helpers.GetNoNullDouble(arr[row, hdict["Solargenerator Leistung DC [kWp]"]]);
a.VKoord = Helpers.GetNoNullDouble(arr[row, hdict["VKoord"]]);
db.Save(a);
}
db.CompleteTransaction();
}
private void ImportOutgoingCommuter()
{
string fn = CombineForRaw("Pendlerdaten_wohngemeinde_Burgdorf.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "K115", out var _);
var headerToColumnDict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[2, i];
if (o == null)
{
throw new Exception("was null");
}
if (!headerToColumnDict.ContainsKey(o.ToString()))
{
headerToColumnDict.Add(o.ToString(), i);
}
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <OutgoingCommuterSummary>();
db.BeginTransaction();
for (var row = 3; row < arr.GetLength(0); row++)
{
if (arr[row, headerToColumnDict["WG Kanton Nr."]] == null)
{
continue;
}
var a = new OutgoingCommuterSummary();
TransferFieldsOutgoing(arr, headerToColumnDict, row, a);
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("2019-02-27-b_EV_HA_pro_Trafokreis.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "AL4000", out var _);
var headerToColumnDict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[0, i];
if (o == null)
{
throw new Exception("was null");
}
if (!headerToColumnDict.ContainsKey(o.ToString()))
{
headerToColumnDict.Add(o.ToString(), i);
}
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <TrafoKreisImport>();
db.BeginTransaction();
for (var row = 1; row < arr.GetLength(0); row++)
{
if (arr[row, headerToColumnDict["U_OBJ_ID_I"]] == null)
{
continue;
}
var a = new TrafoKreisImport();
TransferFields(arr, headerToColumnDict, row, a);
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
// ReSharper disable once StringLiteralTypo
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(CombineForFlaSettings("BusinessInBurgdorf.xlsx"), 1, "A1", "C2000", out var _);
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <BusinessName>();
db.BeginTransaction();
for (var row = 2; row < arr.GetLength(0); row++)
{
var a = new BusinessName();
if (arr[row, 0] == null)
{
continue;
}
AssignFields(a, arr, row);
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("GWRData.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "AW4000", out var _);
var headerToColumnDict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1) - 1; i++)
{
var o = arr[1, i];
if (o == null)
{
throw new Exception("was null");
}
headerToColumnDict.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <GwrData>();
db.BeginTransaction();
for (var row = 2; row < arr.GetLength(0); row++)
{
var a = new GwrData();
if (arr[row, headerToColumnDict["EGID"]] == null)
{
continue;
}
TransferFields(arr, headerToColumnDict, row, a);
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
string fn = CombineForRaw("2018.04.03_Trafodaten.xlsx");
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "V170", out var _);
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[0, i];
if (o == null)
{
continue;
}
hdict.Add(o.ToString(), i);
}
var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
db.RecreateTable <LocalnetTrafostation>();
db.BeginTransaction();
for (var row = 1; row < arr.GetLength(0); row++)
{
var bezeichnung = Helpers.GetString(arr[row, hdict["Bezeichnung"]]);
var seriennummer = Helpers.GetString(arr[row, hdict["Seriennummer"]]);
var hersteller = Helpers.GetString(arr[row, hdict["Hersteller"]]);
var art = Helpers.GetString(arr[row, hdict["Art"]]);
var status = Helpers.GetString(arr[row, hdict["Status"]]);
var eingebautInLagerort = Helpers.GetString(arr[row, hdict["Eingebaut in/Lagerort"]]);
var einbauort = Helpers.GetString(arr[row, hdict["Einbauort"]]);
var adresse = Helpers.GetString(arr[row, hdict["Adresse"]]);
var vorlage = Helpers.GetString(arr[row, hdict["Vorlage"]]);
var komponentenart = Helpers.GetString(arr[row, hdict["Komponentenart"]]);
var leistungkVa = Helpers.GetString(arr[row, hdict["Leistung [kVA]"]]);
var primärnennstromA = Helpers.GetString(arr[row, hdict["Primärnennstrom [A]"]]);
var sekundärnennstromA = Helpers.GetString(arr[row, hdict["Sekundärnennstrom [A]"]]);
var baujahr = Helpers.GetString(arr[row, hdict["Baujahr"]]);
var sekundärstromA = Helpers.GetString(arr[row, hdict["Sekundärstrom (gemessen) [A]"]]);
var schaltgruppe = Helpers.GetString(arr[row, hdict["Schaltgruppe"]]);
var kurzschlussspannung = Helpers.GetString(arr[row, hdict["Kurzschlussspannung [%]"]]);
var eisenverlusteW = Helpers.GetString(arr[row, hdict["Eisenverluste [W]"]]);
var kupferverlusteW = Helpers.GetString(arr[row, hdict["Kupferverluste [W]"]]);
var iksekkA = Helpers.GetString(arr[row, hdict["Ik sek [kA]"]]);
var betriebsstatus = Helpers.GetString(arr[row, hdict["Betriebsstatus"]]);
var a = new LocalnetTrafostation(bezeichnung, seriennummer, hersteller, art, status, eingebautInLagerort,
einbauort, adresse, vorlage, komponentenart, leistungkVa, primärnennstromA,
sekundärnennstromA, baujahr, sekundärstromA, schaltgruppe, kurzschlussspannung,
eisenverlusteW, kupferverlusteW, iksekkA, betriebsstatus);
if (arr[row, hdict["Bezeichnung"]] == null)
{
continue;
}
if (string.IsNullOrWhiteSpace(arr[row, hdict["Bezeichnung"]].ToString()))
{
continue;
}
db.Save(a);
}
db.CompleteTransaction();
}
protected override void RunActualProcess()
{
var dbProfiles = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
dbProfiles.RecreateTable <LastgangBusinessAssignment>();
dbProfiles.BeginTransaction();
string filename = CombineForFlaSettings("BusinessRLMProfileAssignments.xlsx");
Info("Importing " + filename);
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(filename, 1, "A1", "G100", out var _);
//read header
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[0, i];
if (o == null)
{
continue;
}
hdict.Add(o.ToString(), i);
}
var usedKeys = new List <string>();
for (var row = 1; row < arr.GetLength(0); row++)
{
if (arr[row, hdict["Datei"]] == null)
{
continue;
}
var complexname = Helpers.GetString(arr[row, hdict["ComplexName"]]);
var businessname = Helpers.GetString(arr[row, hdict["BusinessName"]]);
var erzeugerid = Helpers.GetString(arr[row, hdict["ErzeugerID"]]);
var standort = Helpers.GetString(arr[row, hdict["Standort"]]);
string key = complexname + " " + businessname + " " + erzeugerid + " " + standort + " ";
if (usedKeys.Contains(key) && !key.Contains("none"))
{
throw new FlaException("key already used: " + key);
}
usedKeys.Add(key);
var fn = Helpers.GetString(arr[row, hdict["Datei"]]);
var rba = new LastgangBusinessAssignment(fn, complexname, businessname, erzeugerid)
{
Standort = standort
};
dbProfiles.Save(rba);
}
dbProfiles.CompleteTransaction();
var assignments = dbProfiles.Fetch <LastgangBusinessAssignment>();
var rlmprofiles = dbProfiles.Fetch <RlmProfile>();
var assignedNames = assignments.Select(x => x.RlmFilename).ToList();
var allFound = true;
foreach (var profile in rlmprofiles)
{
if (!assignedNames.Contains(profile.Name))
{
Info("Unassigned Profile: " + profile.Name + "\t" + profile.SumElectricity);
allFound = false;
}
}
if (!allFound)
{
throw new Exception("Unassigend rlm profiles");
}
foreach (var rlm in assignments)
{
if (rlm.ComplexName == "none")
{
continue;
}
if (string.IsNullOrWhiteSpace(rlm.BusinessName + rlm.Standort + rlm.ErzeugerID))
{
throw new FlaException("assignment without full info: " + rlm.RlmFilename);
}
}
}
private RlmProfile ImportOneProfile([NotNull] FileInfo filename)
{
var multiplier = 0;
if (filename.Name.Contains("_Bezug"))
{
multiplier = -1;
}
if (filename.Name.Contains("_Abgabe"))
{
multiplier = 1;
}
if (multiplier == 0)
{
throw new Exception("Unknown profile type:" + filename.Name);
}
Info("Importing " + filename.Name);
ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage);
var arr = eh.ExtractDataFromExcel2(filename.FullName, 2, "A1", "F35041", out var _);
var hdict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1); i++)
{
var o = arr[0, i];
if (o == null)
{
continue;
}
hdict.Add(o.ToString(), i);
}
var vals = new double[35040];
var dtlookup = new Dictionary <DateTime, int>();
var d = new DateTime(2017, 1, 1);
for (var i = 0; i < 35040; i++)
{
dtlookup.Add(d, i);
d = d.AddMinutes(15);
}
for (var row = 1; row < arr.GetLength(0); row++)
{
if (arr[row, hdict["Zeitpunkt (Beginn Messung)"]] == null)
{
continue;
}
if (string.IsNullOrWhiteSpace(arr[row, hdict["Zeitpunkt (Beginn Messung)"]].ToString()))
{
continue;
}
var dt = Helpers.GetDateTime(arr[row, hdict["Zeitpunkt (Beginn Messung)"]]);
var idx = dtlookup[dt];
vals[idx] = multiplier * Helpers.GetNoNullDouble(arr[row, hdict["Wert"]]);
var unit = Helpers.GetString(arr[row, hdict["Einheit"]]);
if (unit != "kW")
{
throw new Exception("unit not kw in file:" + filename.FullName);
}
}
var lstvals = new List <double>(vals);
var prof = new JsonSerializableProfile(filename.Name, lstvals.AsReadOnly(), EnergyOrPower.Power);
var a = new RlmProfile(filename.Name, 0, prof);
if (a.Profile.Values.Any(x => double.IsNaN(x)))
{
throw new FlaException("Profile was broken: " + filename.FullName);
}
return(a);
}
