bool InsertLeisureBookings () { String StartDateString = DateTime.Now.AddDays (-3).ToString ("yyyyMMdd"); String StartDateSQLCommand = TransportData.GetSQLFormattedDateTime(DateTime.Now.AddDays(-3)); String StartRuWDateSQLCommand = TransportData.GetSQLFormattedDateTime(DateTime.Now.AddDays(-4)); String [] IIS_AE1_SelectCommands = new String [] { "Select RecID, InsDate, InsBarKarte, Typ, BloHal, SauPla, Datum, Beginn, DauAnz, TarifID, ChipNr from Buchungen " + " where Datum > '" + StartDateString + "' order by Typ, BloHal, SauPla", "Select * from Orte", "Select * from RuW_Regel order by Datum desc", "Select * from RuW_Ausnahmen where Datum > " + StartRuWDateSQLCommand + " order by Datum" }; DataSet IIS_AE1_DataSet = DataBase.GetCommonMultiDataSet (IIS_AE1_SelectCommands); if (IIS_AE1_DataSet.Tables.Count != 4) { WMB.Basics.ReportErrorToEventViewer("AEAGProcessCollectedData.InsertLeisureBookings", "Beim lesen der IIS_AE1 Daten kamen nicht 4 Tables zurück - FormatFehler ???"); throw new Exception("Beim lesen der iis_ae1 Daten kamen nicht 4 Tables zurück"); } WCFStandardsNS.WCFStandards AEAGDataBase = new WCFStandards (); AEAGDataBase.DefaultConnectionStringName = "AEAGConnectionString"; //TODO //if (AEAGDataBase.GetSimpleSchemaDataSet () == null) // return false; String [] AEAG_SelectCommands = new String [] { "Select * from Ressource", "Select * from TimeTable", "Select * from BookableUnits", "Select * from FullBookableUnits", "Select * from Booking where BookedFor > " + StartDateSQLCommand }; DataSet AEAG_DataSet = AEAGDataBase.GetCommonMultiDataSet (AEAG_SelectCommands); if (AEAG_DataSet.Tables.Count != 5) { WMB.Basics.ReportErrorToEventViewer ("AEAGProcessCollectedData.InsertLeisureBookings", "Beim lesen der AEAG Daten kamen nicht 5 Tables zurück - FormatFehler ???"); return false; } NameValueCollection TypBloHalSauPlaConverter = LoadTypBloHalSauPlaConverter (AEAG_DataSet); if (WMB.Basics.IsTestRun) WMB.Basics.ReportInformationToEventViewer("Aus der Tabelle \"Buchungen\" werden " + Convert.ToString(IIS_AE1_DataSet.Tables["Buchungen"].Rows.Count) + " aus der DatenBank \"" + DataBase.DefaultConnectionStringName + "\" in die DatenBank \"" + AEAGDataBase.DefaultConnectionStringName + "\" übertragen"); foreach (DataRow BuchungenRow in IIS_AE1_DataSet.Tables["Buchungen"].Rows) { if (BuchungenRow ["SauPla"] == Convert.DBNull) continue; String TypBloHalSauPla = BuchungenRow ["Typ"].ToString () + BuchungenRow ["BloHal"].ToString () + BuchungenRow ["SauPla"].ToString (); if ((TypBloHalSauPlaConverter [TypBloHalSauPla] == null) || String.IsNullOrEmpty (TypBloHalSauPlaConverter [TypBloHalSauPla])) { WMB.Basics.ReportErrorToEventViewer ("AEAGProcessCollectedData.InsertLeisureBookings", "\"" + TypBloHalSauPla + "\" ist in Ressourcen nicht definiert"); continue; } String RessourceID = TypBloHalSauPlaConverter [TypBloHalSauPla]; if (String.IsNullOrEmpty (RessourceID)) { WMB.Basics.ReportErrorToEventViewer("\"" + TypBloHalSauPla + "\" ist in Ressourcen nicht definiert"); throw new Exception("\"" + TypBloHalSauPla + "\" ist in Ressourcen nicht definiert"); } String TimingTypForThisRessource = AEAG_DataSet.Tables ["FullBookableUnits"] .Select ("RessourceID = '" + RessourceID + "'") [0] ["TimingTyp"].ToString (); int NumberOfHoursForOneBooking = 0; if (TimingTypForThisRessource == "Stunde") NumberOfHoursForOneBooking = 1; if (TimingTypForThisRessource == "Doppel") NumberOfHoursForOneBooking = 2; int BeginnValue = Convert.ToInt32 (BuchungenRow ["Beginn"]); int DauAnz = Convert.ToInt32 (BuchungenRow ["DauAnz"]); int GeneratingStep = 0; while ((GeneratingStep * NumberOfHoursForOneBooking) < DauAnz) { BeginnValue += GeneratingStep * NumberOfHoursForOneBooking; GeneratingStep++; String StartHour = Convert.ToString (BeginnValue + 100).Substring (1); DataRow [] SelectedBookables = AEAG_DataSet.Tables ["FullBookableUnits"].Select ("RessourceID = '" + RessourceID + "' and TimingTyp = '" + TimingTypForThisRessource + "'", "TimeTableNameID"); if (SelectedBookables.Length < 1) { WMB.Basics.ReportErrorToEventViewer("\"" + RessourceID + "\" mit \"" + TimingTypForThisRessource + "\" ist in Ressourcen nicht definiert"); throw new Exception("\"" + RessourceID + "\" mit \"" + TimingTypForThisRessource + "\" ist in Ressourcen nicht definiert"); } String BookableUnitID = String.Empty; foreach (DataRow Entry in SelectedBookables) { if (Entry ["TimeTableNameID"].ToString ().Substring (0, 2) == StartHour) { BookableUnitID = Entry ["ID"].ToString (); break; } } if (String.IsNullOrEmpty (BookableUnitID)) continue; String BookedForBase = Convert.ToString (BuchungenRow ["Datum"]); DateTime BookedFor = DateTime.Parse (BookedForBase.Substring (0, 4) + "-" + BookedForBase.Substring (4, 2) + "-" + BookedForBase.Substring (6, 2)); String OldContent = GetBooking (AEAGDataBase, BookableUnitID, BookedFor); String NewContent = BuchungenRow ["InsBarKarte"].ToString (); if (OldContent != NewContent) { DateTime BookingDateTime = Convert.ToDateTime (BuchungenRow ["InsDate"]); SetBooking (AEAGDataBase, BookableUnitID, BookedFor, NewContent, BookingDateTime); } } } return true; }
void InsertRuW_Ausnahmen () { String StartRuWDateSQLCommand = TransportData.GetSQLFormattedDateTime(DateTime.Now.AddDays(-32)); String [] IIS_AE1_SelectCommands = new String [] { "Select * from RuW_Ausnahmen where Datum > " + StartRuWDateSQLCommand + " order by Datum" }; DataSet IIS_AE1_DataSet = DataBase.GetCommonMultiDataSet (IIS_AE1_SelectCommands); if (IIS_AE1_DataSet.Tables.Count != 1) { WMB.Basics.ReportErrorToEventViewer("Beim lesen der iis_ae1 Daten aus der DatenBank\r\n\"" + DataBase.DefaultConnectionStringName + "\"\r\nmit dem SQLString\r\n\"" + String.Join ("\r\n", IIS_AE1_SelectCommands) + "\"\r\n kamen nicht 1 Tables zurück"); throw new Exception ("Beim lesen der iis_ae1 Daten kamen nicht 1 Tables zurück"); } WCFStandardsNS.WCFStandards AEAGDataBase = new WCFStandards (); AEAGDataBase.DefaultConnectionStringName = "AEAGConnectionString"; String [] AEAG_SelectCommands = new String [] { "Select * from Ressource", "Select * from FullBookableUnits" }; DataSet AEAG_DataSet = AEAGDataBase.GetCommonMultiDataSet (AEAG_SelectCommands); if (AEAG_DataSet.Tables.Count != 2) { WMB.Basics.ReportErrorToEventViewer("Beim lesen der AEAG Daten aus der DatenBank\r\n\"" + AEAGDataBase.DefaultConnectionStringName + "\"\r\nmit dem SQLString \"" + String.Join("\r\n", AEAG_SelectCommands) + "\"\r\n kamen nicht 1 Tables zurück"); throw new Exception("Beim lesen der AEAG Daten kamen nicht 2 Tables zurück"); } NameValueCollection TypBloHalSauPlaConverter = LoadTypBloHalSauPlaConverter (AEAG_DataSet); foreach (DataRow AusnahmenRow in IIS_AE1_DataSet.Tables ["RuW_Ausnahmen"].Rows) { String TypBloHalSauPla = AusnahmenRow ["Typ"].ToString () + AusnahmenRow ["BloHal"].ToString () + AusnahmenRow ["SauPla"].ToString (); if ((TypBloHalSauPlaConverter [TypBloHalSauPla] == null) || String.IsNullOrEmpty (TypBloHalSauPlaConverter [TypBloHalSauPla])) { WMB.Basics.ReportErrorToEventViewer ("AEAGProcessCollectedData.InsertRuW_Ausnahmen", "\"" + TypBloHalSauPla + "\" aus den RuW_Ausnahmen ist in Ressourcen nicht definiert"); continue; } String RessourceID = TypBloHalSauPlaConverter [TypBloHalSauPla]; if (String.IsNullOrEmpty (RessourceID)) { WMB.Basics.ReportErrorToEventViewer("\"" + TypBloHalSauPla + "\" aus den RuW_Ausnahmen ist in Ressourcen nicht definiert"); throw new Exception ("\"" + TypBloHalSauPla + "\" aus den RuW_Ausnahmen ist in Ressourcen nicht definiert"); } String TimingTypForThisRessource = AEAG_DataSet.Tables ["FullBookableUnits"] .Select ("RessourceID = '" + RessourceID + "'") [0] ["TimingTyp"].ToString (); int NumberOfHoursForOneBooking = 0; if (TimingTypForThisRessource == "Stunde") NumberOfHoursForOneBooking = 1; if (TimingTypForThisRessource == "Doppel") NumberOfHoursForOneBooking = 2; int StartValue = Convert.ToInt32 (AusnahmenRow ["Start"]); int DauAnz = Convert.ToInt32 (AusnahmenRow ["Dauer"]); int GeneratingStep = 0; while ((GeneratingStep * NumberOfHoursForOneBooking) < DauAnz) { int BeginnValue = StartValue + GeneratingStep * NumberOfHoursForOneBooking; GeneratingStep++; if ((BeginnValue < 7) || (BeginnValue > 21)) continue; String StartHour = Convert.ToString (BeginnValue + 100).Substring (1); DataRow [] SelectedBookables = AEAG_DataSet.Tables ["FullBookableUnits"].Select ("RessourceID = '" + RessourceID + "' and TimingTyp = '" + TimingTypForThisRessource + "'", "TimeTableNameID"); if (SelectedBookables.Length < 1) { WMB.Basics.ReportErrorToEventViewer ("AEAGProcessCollectedData.InsertRuW_Ausnahmen", "\"" + RessourceID + "\" mit \"" + TimingTypForThisRessource + "\" ist in Ressourcen nicht definiert"); continue; } String BookableUnitID = String.Empty; foreach (DataRow Entry in SelectedBookables) { if (Entry ["TimeTableNameID"].ToString ().Substring (0, 2) == StartHour) { BookableUnitID = Entry ["ID"].ToString (); break; } } if (String.IsNullOrEmpty (BookableUnitID)) continue; DateTime BookedFor = Convert.ToDateTime (AusnahmenRow ["Datum"]).Date; String OldContent = GetBooking (AEAGDataBase, BookableUnitID, BookedFor); String NewContent = AusnahmenRow ["Art"].ToString (); if (OldContent != NewContent) { DateTime BookingDateTime = DateTime.Now; SetBooking (AEAGDataBase, BookableUnitID, BookedFor, NewContent, BookingDateTime); } } } }
public static DataSet GetEmptyAltErlaaInfoTimingsDataSet() { WCFStandards AccessHelper = new WCFStandards(); AccessHelper.DefaultConnectionStringName = "AltErlaaInfoConnectionString"; return AccessHelper.GetCommonMultiDataSet(new String[] { "Select top (0) * from Timings", "Select * from TimingTypen"}); }
public static DataSet GetAllEntriesForOneRedaktion (String InformationenID, String Redaktion) { WCFStandards Helper = new WCFStandards (); return Helper.GetCommonMultiDataSet (new String [] { "Select * from ProcessConnector where InformationenID = '" + InformationenID + "' and Redaktion = '" + Redaktion + "'", "Select * from InformationenAddOn where TableID in (select ID from ProcessConnector where InformationenID = '" + InformationenID + "' and Redaktion = '" + Redaktion + "')" }); }
public static DataSet LoadPossibleKeyValues () { WCFStandards DataAccess = new WCFStandards (); return DataAccess.GetCommonMultiDataSet(new string [] { "Select * from ZielGruppen order by ZielGruppe", "SELECT ID, Name FROM Organisationen WHERE (ID IN " + "(SELECT DISTINCT TabelleID FROM InformationenAddOn " + " WHERE (Tabelle = 'Organisationen'))) order by Name", "SELECT ID, Vorname, Name FROM Personen WHERE (ID IN " + "(SELECT DISTINCT TabelleID FROM InformationenAddOn " + " WHERE (Tabelle = 'Personen'))) order by Name, Vorname", "SELECT ID, NameID FROM Typ where (ID in " + "(SELECT distinct TypID FROM Informationen)) order by NameID" }); }
public static DataSet GetProcessConnectorDataforInformationID (String InformationenID) { WCFStandards DataAccess = new WCFStandards (); DataAccess.DefaultConnectionStringName = "AltErlaaInfoConnectionString"; return DataAccess.GetCommonMultiDataSet (new String [] { "Select * from InformationAddOn where InformationenID = '" + InformationenID + "' and Tabelle = 'ProcessConnector'", "Select * from ProcessConnector where ID in " + "(Select TabelleID from InformationAddOn where InformationenID = '" + InformationenID + "' and Tabelle = 'ProcessConnector' and not TabelleID = '00000000-0000-0000-0000-000000000000')" }); }
public static DataSet AllDataInEntryAsDataSet (Guid InformationenID) { WCFStandards Helper = new WCFStandards (); DataSet InformationenDataSet = Helper.GetCommonMultiDataSet (new String[ ] {"Select * from Informationen where ID = '" + InformationenID.ToString( ) + "'", "Select * from InformationenAddOn where InformationenID = '" + InformationenID + "' order by SortOrder"}); List<string> TableCommands = new List<string> (); foreach (DataRow InfoAddOn in InformationenDataSet.Tables ["InformationenAddOn"].Rows) { String Tabelle = InfoAddOn ["Tabelle"].ToString (); if (String.IsNullOrEmpty (Tabelle)) continue; if (InfoAddOn ["TabelleID"] == Convert.DBNull) continue; if ((System.Guid) InfoAddOn ["TabelleID"] == System.Guid.Empty) continue; String TabelleID = InfoAddOn ["TabelleID"].ToString (); if (Tabelle == "Timings") TableCommands.Add ("Select * from " + Tabelle + " where TimingsID = '" + TabelleID + "'"); else TableCommands.Add ("Select * from " + Tabelle + " where ID = '" + TabelleID + "'"); } DataSet TabellenDataSet = Helper.GetCommonMultiDataSet (TableCommands.ToArray ()); List<DataTable> TableTables = new List<DataTable> (); foreach (DataTable TabTable in TabellenDataSet.Tables) { TableTables.Add (TabTable); } TabellenDataSet.Tables.Clear (); foreach (DataTable TabTable in TableTables) { InformationenDataSet.Tables.Add (TabTable); } return InformationenDataSet; }