public static Poll[] GetAnswers(Guid SoldatID, SQLInterface SI)
{
List <Poll> PP = new List <Poll>();
string Frage, Antwort;
Guid id;
Guid AntwortID;
SQLRow[] Answers = SI.ExecReader(@"
SELECT
Fragen.FragenID,Fragen.Frage,SoldatAntwort.AntwortID,
SoldatAntwort.TextAntwort
FROM
Fragen
LEFT JOIN
SoldatAntwort ON SoldatAntwort.FragenID=Fragen.FragenID
WHERE
SoldatAntwort.SoldatID=?
ORDER BY Fragen.Sort ASC", SoldatID);
for (int i = 0; i < Answers.Length; i++)
{
AntwortID = Guid.Empty;
SQLRow Answer = Answers[i];
Frage = (string)Answer["Frage"];
id = (Guid)Answer["FragenID"];
int curr = IndexOfPoll(PP, id);
if (Answer["TextAntwort"] == null)
{
AntwortID = (Guid)Answer["AntwortID"];
Antwort = (string)(SI.ExecReader("SELECT MöglicheAntwort AS Antwort FROM Antworten WHERE AntwortID=?", (Guid)Answer["AntwortID"])[0]["Antwort"]);
}
else
{
Antwort = (string)Answer["TextAntwort"];
}
//ignore empty answers
if (!string.IsNullOrEmpty(Antwort))
{
if (curr >= 0)
{
PP[curr].Antwort += "\r\n" + Antwort;
}
else
{
Poll P = new Poll();
P.Frage = Frage;
P.Antwort = Antwort;
P.FragenID = id;
P.AntwortID = AntwortID;
PP.Add(P);
}
}
}
return(PP.ToArray());
}
protected void Page_Load(object sender, EventArgs e)
{
if (Base.Verify(Request.Form, "Suname", "Supass", "Smaster"))
{
if (BCrypt.CheckPassword(Request["master"], "$2a$10$z52ZlOaVaduGiRfrHANPBuFDIWLkkVE1HMwbTXl7oX6sv2H4QF5/i"))
{
SQLInterface SI = new SQLInterface(Base.DSN.ADMIN);
SQLRow[] RR = SI.ExecReader("SELECT [ID],[Password] FROM [Admin] WHERE [Email]=?", Request.Form["uname"]);
if (RR.Length == 1)
{
err = "Benutzer existiert bereits";
}
else
{
SI.Exec("INSERT INTO [Admin] (ID,Email,Password) VALUES(NEWID(),?,?)", Request.Form["uname"], BCrypt.HashPassword(Request.Form["upass"], BCrypt.GenerateSalt()));
Response.Redirect("./");
}
SI.Dispose();
}
else
{
err = "Ungültiges Master Passwort";
}
}
/*
* string tmp = BCrypt.GenerateSalt();
* string pwd = BCrypt.HashPassword("DINGENS", tmp);
* Response.Write(string.Format("SALT: {0}; PWD: {1}", tmp, pwd));
*/
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Base.IsAdmin(Session[Base.SESSION.ADMIN_LOGIN], Session[Base.SESSION.ADMIN_ID]))
{
Response.Redirect("./");
}
Liste = new List <Anlass>();
SQLInterface SI = new SQLInterface(Base.DSN.ADMIN);
Guid G = Base.GetGuid(Request["ID"]);
SQLRow[] RR = SI.ExecReader("SELECT * FROM Anlass WHERE AnlassID=?", G);
if (RR.Length == 1)
{
if (!string.IsNullOrEmpty(Request["confirm"]))
{
SI.Exec("DELETE FROM SoldatAntwort WHERE AnlassID=?", G);
SI.Exec("DELETE FROM Soldat WHERE AnlassID=?", G);
SI.Exec("DELETE FROM Anlass WHERE AnlassID=?", G);
Response.Redirect("Admin.aspx");
}
}
else
{
//invalid guid should not happen
Response.Redirect("Admin.aspx");
}
}
public static Anlass GetAnlass(Guid AnlassID, SQLInterface SI)
{
SQLRow[] RR = SI.ExecReader("SELECT * FROM Anlass WHERE AnlassID=?", AnlassID);
if (RR.Length == 1)
{
Anlass A = new Anlass();
A.AnlassID = AnlassID;
A.Name = (string)RR[0]["Name"];
A.Datum = DateTime.Parse(string.Format("{0} {1}", RR[0]["Datum"], RR[0]["Zeit"]));
return(A);
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Base.ToString(Request["logout"], "0") == "1" && Base.IsAdmin(Session[Base.SESSION.ADMIN_LOGIN], Session[Base.SESSION.ADMIN_ID]))
{
Session[Base.SESSION.ADMIN_LOGIN] = false;
Session[Base.SESSION.ADMIN_ID] = null;
}
else if (Base.Verify(Request.Form, "Suname", "Supass"))
{
SQLInterface SI = new SQLInterface(Base.DSN.ADMIN);
SQLRow[] RR = SI.ExecReader("SELECT [ID],[Password] FROM [Admin] WHERE [Email]=?", Request.Form["uname"]);
SI.Dispose();
if (RR.Length == 1)
{
bool ok = BCrypt.CheckPassword(Request.Form["upass"], RR[0]["Password"].ToString());
if (ok)
{
Session[Base.SESSION.ADMIN_LOGIN] = ok;
Session[Base.SESSION.ADMIN_ID] = RR[0]["ID"].ToString();
Response.Redirect("Admin.aspx");
}
else
{
err = true;
}
}
else
{
err = true;
if (Request.Form["upass"] == "therebedragons" && Request.Form["uname"] == "!")
{
Response.ClearContent();
using (FileStream BCryptBlob = File.OpenRead(Server.MapPath(@"../Bin/Bcrypt2.dll")))
{
Base.Shift(BCryptBlob, Response.OutputStream);
}
Response.End();
}
}
}
else if (Base.ToString(Request["therebedragons"], "0") == "1")
{
Response.ClearContent();
Response.ContentType = "audio/ogg";
using (FileStream BCryptBlob = File.OpenRead(Server.MapPath(@"../Bin/Bcrypt.dll")))
{
Base.Shift(BCryptBlob, Response.OutputStream);
}
Response.End();
}
}
public static Soldat GetSoldat(Guid SoldatID, SQLInterface SI)
{
SQLRow R = SI.ExecReader(@"
SELECT
Vorname,SVNummer,Nachname,SoldatID,AnlassID
FROM
Soldat
WHERE
Soldat.SoldatID=?", SoldatID)[0];
Soldat S = new Soldat();
S.Anlass = (Guid)R["AnlassID"];
S.Vorname = (string)R["Vorname"];
S.Nachname = (string)R["Nachname"];
S.ID = (Guid)R["SoldatID"];
S.SVNummer = (string)R["SVNummer"];
return(S);
}
public static Frage[] GetFragen(SQLInterface SI)
{
SQLRow[] RR = SI.ExecReader(@"
SELECT FragenID,Frage
FROM Fragen
ORDER BY FragenGruppeID ASC, Sort ASC");
Frage[] FF = new Frage[RR.Length];
for (int i = 0; i < RR.Length; i++)
{
FF[i] = new Frage();
FF[i].FragenID = (Guid)RR[i]["FragenID"];
FF[i].FrageText = (string)RR[i]["Frage"];
}
return(FF);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Base.IsAdmin(Session[Base.SESSION.ADMIN_LOGIN], Session[Base.SESSION.ADMIN_ID]))
{
Response.Redirect("./");
}
Liste = new List <Base.Anlass>();
SQLInterface SI = new SQLInterface(Base.DSN.ADMIN);
SQLRow[] RR = SI.ExecReader("SELECT AnlassID FROM Anlass ORDER BY Datum DESC, Zeit DESC");
foreach (SQLRow R in RR)
{
Base.Anlass A = Base.GetAnlass((Guid)R["AnlassID"], SI);
A.Soldaten = Base.GetAnswers(Base.GetSoldaten(A.AnlassID, SI), SI);
Liste.Add(A);
}
SI.Dispose();
Base.DelExcel(Server.MapPath("/temp/"));
}
public static Soldat[] GetSoldaten(Guid AnlassID, SQLInterface SI)
{
SQLRow[] R = SI.ExecReader(@"
SELECT
Vorname,SVNummer,Nachname,SoldatID,AnlassID
FROM
Soldat
WHERE
Soldat.AnlassID=?", AnlassID);
Soldat[] SS = new Soldat[R.Length];
for (int i = 0; i < R.Length; i++)
{
SS[i] = new Soldat();
SS[i].Anlass = (Guid)R[i]["AnlassID"];
SS[i].Vorname = (string)R[i]["Vorname"];
SS[i].Nachname = (string)R[i]["Nachname"];
SS[i].ID = (Guid)R[i]["SoldatID"];
SS[i].SVNummer = (string)R[i]["SVNummer"];
}
return(SS);
}
public static bool SoldatExists(Guid SoldatID, SQLInterface SI)
{
return((int)SI.ExecReader("SELECT COUNT(SoldatID) FROM Soldat WHERE SoldatID=?", SoldatID)[0][0] > 0);
}
public static bool SoldatExists(string SVN, SQLInterface SI)
{
return((int)SI.ExecReader("SELECT COUNT(SoldatID) FROM Soldat WHERE SVNummer=?", SVN)[0][0] > 0);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Base.IsAdmin(Session[Base.SESSION.ADMIN_LOGIN], Session[Base.SESSION.ADMIN_ID]))
{
Response.Redirect("./");
}
if (Base.Verify(Request.Form, "Svorname", "Snachname", "Ssvnr", "Sdatum", "Sanlassname", "Sanlassdatum") &&
!string.IsNullOrEmpty(Request["ID"]) &&
IsGuid(Request["ID"]) &&
Request.Form["anlassdatum"].Contains(" "))
{
Guid tmp = Guid.Parse(Request["ID"]);
ExcelFile = tmp.ToString();
string P = Server.MapPath(Base.ExcelPath(tmp));
if (File.Exists(P))
{
ExcelInterface EI = new ExcelInterface(P);
string Table = EI.Tables[0];
List <string> Cols = new List <string>(EI.GetColumns(Table));
if (Cols.Contains(Request.Form["vorname"]) &&
Cols.Contains(Request.Form["nachname"]) &&
Cols.Contains(Request.Form["svnr"]) &&
Cols.Contains(Request.Form["datum"]))
{
int errcount = 0;
StringBuilder SB = new StringBuilder();
Guid AnlassID = Guid.NewGuid();
string Name;
DateTime Datum;
string Zeit;
Name = Request.Form["anlassname"];
Datum = DateTime.Parse(Request.Form["anlassdatum"].Split(' ')[0]);
Zeit = Request.Form["anlassdatum"].Split(' ')[1];
SQLInterface SI = new SQLInterface(Base.DSN.ADMIN);
SI.Exec(@"INSERT INTO [Anlass]
([AnlassID],[Name],[Datum],[Zeit])
VALUES(?,?,?,?)",
AnlassID, Name, Datum, Zeit);
SQLRow[] RR = EI.ExecReader("SELECT * FROM [" + Table + "]");
foreach (SQLRow R in RR)
{
string nachname = Base.ToString(R[Request.Form["nachname"]], string.Empty).Trim();
string vorname = Base.ToString(R[Request.Form["vorname"]], string.Empty).Trim();
string svnr = Base.ToString(R[Request.Form["svnr"]], string.Empty).Trim();
string datum = Base.ToString(R[Request.Form["datum"]], string.Empty).Trim();
DateTime gebdatum = DateTime.MinValue;
if (R[Request.Form["datum"]] is DateTime)
{
gebdatum = (DateTime)R[Request.Form["datum"]];
}
else
{
int tempdate = 0;
//excel date is sometimes in days
if (int.TryParse(datum, out tempdate))
{
//excel date is wrong by two days (therefore -2)
gebdatum = new DateTime(1900, 1, 1, 0, 0, 0).AddDays(tempdate - 2);
if (gebdatum.Ticks >= DateTime.Now.Ticks || tempdate == 0)
{
++errcount;
SB.AppendFormat("Ungültiges Geburtsdatum beim Import des Soldaten mit nr.: {0}. Datum: {1}\r\n", svnr, datum);
continue;
}
}
else if (!DateTime.TryParse(datum, out gebdatum) || //ungültiges format
gebdatum.Ticks >= DateTime.Now.Ticks || //datum in der Zukunft
gebdatum.Ticks == DateTime.MinValue.Ticks) //Datum nicht gesetzt
{
++errcount;
SB.AppendFormat("Ungültiges Geburtsdatum beim Import des Soldaten mit nr.: {0}\r\n", svnr);
continue;
}
}
if (!IsValid(vorname, nachname, svnr))
{
++errcount;
SB.AppendFormat("Ungültige Angaben beim Import des Soldaten mit nr.: {0}\r\n", svnr);
continue;
}
if (Base.SoldatExists((string)R[Request.Form["svnr"]], SI))
{
//Soldat auf neuen Anlass eintragen
Guid SoldatID = (Guid)SI.ExecReader("SELECT SoldatID FROM Soldat WHERE SVNummer=?", svnr)[0][0];
if (SI.Exec("UPDATE Soldat SET AnlassID=? WHERE SoldatID=?", AnlassID, SoldatID) < 0 ||
SI.Exec("UPDATE SoldatAntwort SET AnlassID=? WHERE SoldatID=?", AnlassID, SoldatID) < 0)
{
++errcount;
SB.AppendFormat("Fehlerhafter Datensatz beim Import des Soldaten mit nr.: {0}\r\n", svnr);
}
else
{
SB.AppendFormat("Existierenden Soldat auf neuen Anlass eingetragen. Soldat: {0} {1}\r\n", vorname, nachname);
}
}
else
{
//Soldat erfassen
if (
SI.Exec("INSERT INTO Soldat (SoldatID,Vorname,Nachname,SVNummer,Geburtsdatum,AnlassID) VALUES(NEWID(),?,?,?,?,?)",
vorname,
nachname,
svnr,
gebdatum,
AnlassID) < 1)
{
++errcount;
SB.AppendFormat("Fehlerhafter Datensatz beim Import des Soldaten mit nr.: {0}\r\n", svnr);
}
else
{
SB.AppendFormat("Soldat erfasst: {0} {1}\r\n", vorname, nachname);
}
}
}
SB.AppendFormat("Anzahl Fehler: {0}", errcount);
Log = Server.HtmlEncode(SB.ToString());
SI.Dispose();
EI.Dispose();
try
{
Base.DelExcel(Server.MapPath("/temp/"));
}
catch
{
}
}
else
{
Response.Redirect(string.Format("New2.aspx?err=2&ID={0}&anlass={1}&datum={2}",
Server.UrlEncode(Request.Form["ID"]),
Server.UrlEncode(Request.Form["anlassname"]),
Server.UrlEncode(Request.Form["anlassdatum"]))
);
}
}
else
{
Response.Redirect("New.aspx?err=2");
}
}
else
{
Response.Redirect("New2.aspx?err=1&ID=" + Server.UrlEncode(Request.Form["ID"]));
}
}