public string GetUser(long id)
{
var command = $"SELECT * FROM Nutzerdaten WHERE Id={id}";
var r = DatabaseManager.ExecuteReader(command);
return(DatabaseManager.ReadFirstAsJsonObject(GetUserKeyTableDictionary(), r, null));
}
public string GetOrganizer(long id)
{
var command = $"SELECT * FROM Veranstalter INNER JOIN Nutzerdaten ON Veranstalter.Id=Nutzerdaten.Id WHERE Veranstalter.Id={id}";
var r = DatabaseManager.ExecuteReader(command);
return(DatabaseManager.ReadFirstAsJsonObject(GetOrganizerKeyTableDictionary(), r, null));
}
public string GetAdmin(long id)
{
var command = $"SELECT * FROM Admins INNER JOIN Nutzerdaten ON Admins.Id=Nutzerdaten.Id WHERE Admins.Id={id}";
var r = DatabaseManager.ExecuteReader(command);
return(DatabaseManager.ReadFirstAsJsonObject(GetAdminKeyTableDictionary(), r, null));
}
public string GetBooking(long bookingId)
{
using (var reader = DatabaseManager.ExecuteReader($"SELECT * FROM Nutzerdaten INNER JOIN Buchungen ON Buchungen.Nutzer=Nutzerdaten.Id WHERE Buchungen.Id={bookingId}"))
{
return(DatabaseManager.ReadFirstAsJsonObject(GetKeyTableDictionary(), reader, null));
}
}
public static string GetId(string queryCommand)
{
var id = DatabaseManager.ReadFirstAsJsonObject(new Dictionary <string, string> {
{ "id", "Id" }
}, DatabaseManager.ExecuteReader(queryCommand), null);
return(Json.GetKvpValue(id, "id", false));
}
public string GetEvent(long eventId)
{
var command = $"SELECT * FROM Veranstaltungen WHERE Veranstaltungen.Id={eventId}";
using (var reader = DatabaseManager.ExecuteReader(command))
{
return(DatabaseManager.ReadFirstAsJsonObject(GetEventKeyTableDictionary(), reader, null));
}
}
public bool ProcessBooking(long bookingId)
{
var result = String.Empty;
using (var reader = DatabaseManager.ExecuteReader($"SELECT * FROM Nutzerdaten INNER JOIN Buchungen ON Buchungen.Nutzer=Nutzerdaten.Id WHERE Buchungen.Id={bookingId}"))
{
result = DatabaseManager.ReadFirstAsJsonObject(GetKeyTableDictionary(), reader, null);
}
return(ProcessBooking(result, bookingId));
}
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 long GetCalendarId(string name)
{
if (name == null)
{
return(-1);
}
var command = $"SELECT * FROM Kalender WHERE Name={Json.SerializeString(name)}";
var r = DatabaseManager.ExecuteReader(command);
var result = DatabaseManager.ReadFirstAsJsonObject(GetCalendarKeyTableDictionary(), r, null);
if (result == null)
{
return(-1);
}
return(Convert.ToInt64(Json.GetValue(result, "id", false)));
}
public string GetEvent(string uid)
{
var result = CalendarManager.GetEventAsJsonObject(uid);
if (result == null)
{
return(null);
}
var command = $"SELECT * FROM Veranstaltungen WHERE Veranstaltungen.Uid=\"{uid}\"";
using (var reader = DatabaseManager.ExecuteReader(command))
{
result = Json.MergeObjects(new string[] { result, DatabaseManager.ReadFirstAsJsonObject(GetEventKeyTableDictionary(), reader, null) }, true);
}
return(result);
}
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 bool ProcessVerification(string code)
{
var dateTime = CalendarManager.GetDateTime(DateTime.Now);
var reader = DatabaseManager.ExecuteReader($"SELECT * FROM Emailverifizierungen WHERE Code=\"{code}\" AND Zeitfrist>={dateTime}");
var r = DatabaseManager.ReadFirstAsJsonObject(new Dictionary <string, string> {
{ "nutzer", "Nutzer" }
}, reader, null);
var id = Convert.ToInt64(Json.GetKvpValue(r, "nutzer", false));
if (new NutzerModel().VerifyUser(id) > 0)
{
DeleteVerificationCode(GetVerificationCodeId(code));
return(true);
}
else
{
return(false);
}
}
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 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()));
}
public string GetAllCodes()
{
var reader = DatabaseManager.ExecuteReader("SELECT * FROM Emailverifizierungen");
return(DatabaseManager.ReadAsJsonArray(GetKeyTableDictionary(), reader, "emailverifizierungen"));
}
public string GetCalendar(long id)
{
var r = DatabaseManager.ExecuteReader($"SELECT * FROM Kalender WHERE Id={id}");
return(DatabaseManager.ReadFirstAsJsonObject(GetCalendarKeyTableDictionary(), r, null));
}
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"));
}
