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;
}
