public string GetBookingsByEventAsArray(string eventId)
{
var command = $"SELECT * FROM Buchungen INNER JOIN Nutzerdaten ON Buchungen.Nutzer=Nutzerdaten.Id";
if (eventId != null)
{
command += $" WHERE Veranstaltung={eventId}";
}
using (var reader = DatabaseManager.ExecuteReader(command))
{
return(DatabaseManager.ReadAsJsonArray(GetKeyTableDictionary(), reader));
}
}
public string GetAdmins(string queryString)
{
var result = String.Empty;
using (var reader = DatabaseManager.ExecuteReader("SELECT * FROM Admins INNER JOIN Nutzerdaten ON Admins.Id=Nutzerdaten.Id"))
{
result = DatabaseManager.ReadAsJsonArray(GetAdminKeyTableDictionary(), reader);
}
var jsonData = Json.SerializeObject(new Dictionary <string, string> {
{ "admins", result }
});
return(Json.QueryJsonData(jsonData, queryString, -1, false, false, Json.ArrayEntryOrKvpValue.ArrayEntry));
}
public string GetOrganizerCalendars(long organizerId)
{
var reader = DatabaseManager.ExecuteReader($"SELECT * FROM Kalenderberechtigte WHERE Veranstalter={organizerId}");
var ids = DatabaseManager.ReadAsJsonArray(new Dictionary <string, string> {
{ "id", "Kalender" }
}, reader);
var array = Json.DeserializeArray(ids);
var idList = new List <long>();
foreach (var a in array)
{
idList.Add(Convert.ToInt64(Json.GetKvpValue(a, "id", false)));
}
var result = new KalenderModel().GetCalendars(idList.ToArray());
return(Json.SerializeObject(new Dictionary <string, string> {
{ "kalender", result }
}));
}
public void ProcessBookings(string eventUid)
{
var eventId = GetEventId(eventUid);
var result = String.Empty;
using (var reader = DatabaseManager.ExecuteReader($"SELECT * FROM Buchungen WHERE Veranstaltung={eventId}"))
{
result = DatabaseManager.ReadAsJsonArray(GetBookingKeyTableDictionary(), reader);
}
var arr = Json.DeserializeArray(result);
foreach (var b in arr)
{
var id = Convert.ToInt64(Json.GetKvpValue(b, "id", false));
ProcessBooking(id);
}
}
public string GetCalendar(string name)
{
var command = "SELECT * FROM Kalender";
if (name != null)
{
command += $" WHERE Name={Json.SerializeString(name)}";
}
var r = DatabaseManager.ExecuteReader(command);
if (name != null)
{
return(DatabaseManager.ReadFirstAsJsonObject(GetCalendarKeyTableDictionary(), r, null));
}
else
{
var result = DatabaseManager.ReadAsJsonArray(GetCalendarKeyTableDictionary(), r);
return(Json.SerializeObject(new Dictionary <string, string> {
{ "kalender", result }
}));
}
}
public string GetAllCodes()
{
var reader = DatabaseManager.ExecuteReader("SELECT * FROM Emailverifizierungen");
return(DatabaseManager.ReadAsJsonArray(GetKeyTableDictionary(), reader, "emailverifizierungen"));
}
public string GetParticipantsAsArray()
{
var r = DatabaseManager.ExecuteReader($"SELECT * FROM Teilnehmer INNER JOIN Nutzerdaten ON Teilnehmer.Nutzer=Nutzerdaten.Id");
return(DatabaseManager.ReadAsJsonArray(GetKeyTableDictionary(), r));
}
public string GetParticipants(long eventId)
{
var r = DatabaseManager.ExecuteReader($"SELECT * FROM Teilnehmer INNER JOIN Nutzerdaten ON Teilnehmer.Nutzer=Nutzerdaten.Id WHERE Veranstaltung = {eventId}");
return(DatabaseManager.ReadAsJsonArray(GetKeyTableDictionary(), r, "teilnehmer"));
}
public bool WipeUnnecessaryData()
{
var usedDataList = new List <long>();
var toBeDeletedIdList = new List <long>();
var organizers = DatabaseManager.ReadAsJsonArray(new Dictionary <string, string> {
{ "id", "Id" }
}, DatabaseManager.ExecuteReader("SELECT * FROM Veranstalter"));
foreach (var o in Json.DeserializeArray(organizers))
{
var id = Json.GetKvpValue(o, "id", false);
if (id != null)
{
usedDataList.Add(Convert.ToInt64(id));
}
}
var bookings = DatabaseManager.ReadAsJsonArray(new Dictionary <string, string> {
{ "id", "Id" }
}, DatabaseManager.ExecuteReader("SELECT * FROM Buchungen"));
foreach (var b in Json.DeserializeArray(bookings))
{
var id = Json.GetKvpValue(b, "nutzer", false);
if (id != null)
{
usedDataList.Add(Convert.ToInt64(id));
}
}
var codes = DatabaseManager.ReadAsJsonArray(new Dictionary <string, string> {
{ "id", "Id" }
}, DatabaseManager.ExecuteReader("SELECT * FROM Emailverifizierungen"));
foreach (var c in Json.DeserializeArray(codes))
{
var id = Json.GetKvpValue(c, "nutzer", false);
if (id != null)
{
usedDataList.Add(Convert.ToInt64(id));
}
}
var users = DatabaseManager.ReadAsJsonArray(new Dictionary <string, string> {
{ "id", "Id" }
}, DatabaseManager.ExecuteReader("SELECT * FROM Nutzerdaten"));
foreach (var u in Json.DeserializeArray(users))
{
var id = Json.GetKvpValue(u, "id", false);
if (id != null)
{
var i = Convert.ToInt64(id);
if (!usedDataList.Contains(i))
{
toBeDeletedIdList.Add(i);
}
}
}
return(DatabaseManager.ExecuteDelete("Nutzerdaten", toBeDeletedIdList.ToArray()));
}
