protected override void RunActualProcess()
{
var arr = ExcelHelper.ExtractDataFromExcel(@"U:\SimZukunft\RawDataForMerging\GWRData.xlsx", 1, "A1", "AW4000");
var headerToColumnDict = new Dictionary <string, int>();
for (var i = 0; i < arr.GetLength(1) - 1; i++)
{
var o = arr[2, i + 1];
if (o == null)
{
throw new Exception("was null");
}
headerToColumnDict.Add(o.ToString(), i + 1);
}
SqlConnection.RecreateTable <GwrData>(Stage.Raw, Constants.PresentSlice);
var db = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice).Database;
db.BeginTransaction();
for (var row = 3; 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();
}
// ReSharper disable once FunctionComplexityOverflow
private static void TransferFields([NotNull][ItemNotNull] object[,] arr,
[NotNull] Dictionary <string, int> hdict,
int row,
[NotNull] GwrData a)
{
a.EidgGebaeudeidentifikator_EGID = Convert.ToInt32(arr[row, hdict["EGID"]]);
a.EidgBauprojektidentifikator_EPROID = Helpers.GetInt(arr[row, hdict["EPROID"]]);
a.ErhebungsstelleBaustatistik_GESTNR = Helpers.GetInt(arr[row, hdict["GESTNR"]]);
a.BauprojektIdLiefersystem_GBABID = (string)arr[row, hdict["GBABID"]];
a.GebaeudeIDLiefersystem_GBAGID = (string)arr[row, hdict["GBAGID"]];
a.BFSGemeindenummer_GDENR = Helpers.GetInt(arr[row, hdict["GDENR"]]);
a.EidgGrundstuecksidentifikator_GEGRID = (string)arr[row, hdict["GEGRID"]];
a.Grundbuchkreisnummer_GGBKR = Helpers.GetInt(arr[row, hdict["GGBKR"]]);
a.Parzellennummer_GPARZ = Helpers.GetInt(arr[row, hdict["GPARZ"]]);
a.AmtlicheGebaeudenummer_GEBNR = Helpers.GetInt(arr[row, hdict["GEBNR"]]);
a.NamedesGebaeudes_GBEZ = (string)arr[row, hdict["GBEZ"]];
a.AnzahlGebaeudeeingaenge_GANZDOM = Helpers.GetInt(arr[row, hdict["GANZDOM"]]);
a.EKoordinate_GKODE = Helpers.GetDouble(arr[row, hdict["GKODE"]]);
a.NKoordinate_GKODN = Helpers.GetDouble(arr[row, hdict["GKODN"]]);
a.XKoordinate_GKODX = Helpers.GetDouble(arr[row, hdict["GKODX"]]);
a.YKoordinate_GKODY = Helpers.GetDouble(arr[row, hdict["GKODY"]]);
a.Koordinatenherkunft_GKSCE = Helpers.GetInt(arr[row, hdict["GKSCE*"]]);
a.Lokalcode1_GLOC1 = Helpers.GetInt(arr[row, hdict["GLOC1"]]);
a.Lokalcode2_GLOC2 = Helpers.GetInt(arr[row, hdict["GLOC2"]]);
a.Lokalcode3_GLOC3 = Helpers.GetInt(arr[row, hdict["GLOC3"]]);
a.Lokalcode4_GLOC4 = Helpers.GetInt(arr[row, hdict["GLOC4"]]);
a.Gebaeudestatus_GSTAT = Helpers.GetInt(arr[row, hdict["GSTAT*"]]);
a.Gebaeudekategorie_GKAT = Helpers.GetInt(arr[row, hdict["GKAT*"]]);
a.Gebaeudeklasse_GKLAS = Helpers.GetInt(arr[row, hdict["GKLAS*"]]);
a.Baujahr_GBAUJ = Helpers.GetInt(arr[row, hdict["GBAUJ"]]);
a.Bauperiode_GBAUP = Helpers.GetInt(arr[row, hdict["GBAUP*"]]);
a.Renovationsjahr_GRENJ = Helpers.GetInt(arr[row, hdict["GRENJ"]]);
a.Renovationsperiode_GRENP = Helpers.GetInt(arr[row, hdict["GRENP*"]]);
a.Abbruchjahr_GABBJ = Helpers.GetInt(arr[row, hdict["GABBJ"]]);
a.Gebaeudeflaeche_GAREA = Helpers.GetInt(arr[row, hdict["GAREA"]]);
a.AnzahlGeschosse_GASTW = Helpers.GetInt(arr[row, hdict["GASTW"]]);
a.AnzahlseparateWohnraeume_GAZZI = Helpers.GetInt(arr[row, hdict["GAZZI"]]);
a.AnzahlWohnungen_GANZWHG = Helpers.GetInt(arr[row, hdict["GANZWHG"]]);
a.Heizungsart_GHEIZ = Helpers.GetInt(arr[row, hdict["GHEIZ*"]]);
a.EnergietraegerderHeizung_GENHZ = Helpers.GetInt(arr[row, hdict["GENHZ*"]]);
a.Warmwasserversorgung_GWWV = Helpers.GetInt(arr[row, hdict["GWWV**"]]);
a.EnergietraegerfuerWarmwasser_GENWW = Helpers.GetInt(arr[row, hdict["GENWW*"]]);
a.AnzahlEingangsrecords_GADOM = Helpers.GetInt(arr[row, hdict["GADOM"]]);
a.AnzahlWohnungsrecords_GAWHG = Helpers.GetInt(arr[row, hdict["GAWHG"]]);
a.PlausibilitaetsstatusderKoordinaten_GKPLAUS = Helpers.GetInt(arr[row, hdict["GKPLAUS"]]);
a.StatusWohnungsbestandes_GWHGSTD = Helpers.GetInt(arr[row, hdict["GWHGSTD*"]]);
a.VerifikationWohnungsbestand_GWHGVER = Helpers.GetInt(arr[row, hdict["GWHGVER**"]]);
a.PlausibilitaetsstatusGebaeude_GPLAUS = Helpers.GetInt(arr[row, hdict["GPLAUS*"]]);
a.Baumonat_GBAUM = (string)arr[row, hdict["GBAUM"]];
a.Renovationsmonat_GRENM = Helpers.GetInt(arr[row, hdict["GRENM"]]);
a.Abbruchmonat_GABBM = Helpers.GetInt(arr[row, hdict["GABBM"]]);
}
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();
}
