public void AddressCityListTest()
{
IDataManager <User> dataManager = new XmlDataManager <User>();
if (File.Exists(Constants.DataFilePath + Constants.XmlDataFileName))
{
File.Delete(Constants.DataFilePath + Constants.XmlDataFileName);
}
var createData = CreateUsersData();
createData.Add(new User {
AddressCity = createData[1].AddressCity
});
createData.Add(new User {
AddressCity = createData[2].AddressCity
});
dataManager.Save(createData, Constants.DataFilePath + Constants.XmlDataFileName);
var data = CreateUsersData();
Assert.IsTrue(new AddressCityList().Cities.Count - 1 == data.Count, "Address cities");
if (File.Exists(Constants.DataFilePath + Constants.XmlDataFileName))
{
File.Delete(Constants.DataFilePath + Constants.XmlDataFileName);
}
dataManager.Save(CreateUsersData(), Constants.DataFilePath + Constants.XmlDataFileName);
}
// Function to add UserData in the database
public void AddBetreuer(string Vorname, string Name, string Email, int Titel, string Telefonnummer)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "insert into tab_betreuer ([txtVorname],[txtName],[txtMail],[intTitel],[txtTelefon]) " +
"values ('" + Vorname + "','" + Name + "','" + Email + "','" + Titel + "','" + Telefonnummer + "')";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = UserConnection;
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Data Saved Successful");
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
//function to search a title's id
public int Search_Title_ID(string titel)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT ID FROM tab_titel where txtTitel = '" + titel + "' ";
OleDbConnection UserConnection1 = new OleDbConnection
{
ConnectionString = config.DbConnectionString
};
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = UserConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["ID"].ToString();
int id = Convert.ToInt32(resultat);
UserConnection1.Close();
return(id);
}
else
{
UserConnection1.Close();
return(0);
}
}
// Anzeige der nformationen des Betreuer
public void ShowBetreuerInfo(DataGridView grid)
{
try
{
/*t.txtTitel AS Titel, tab_titel AS t, WHERE t.ID = b.intTitel */
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT b.ID AS BetreuerID, t.txtTitel AS Titel, b.txtVorname AS Vorname, b.txtName AS Name, b.txtMail AS AdresseMail, b.txtTelefon AS Telefonnumer " +
"FROM tab_titel AS t, tab_betreuer AS b WHERE t.ID = b.intTitel";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
UserConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = UserConnection;
cmd.CommandText = query;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
grid.DataSource = dt;
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Function to remove Betreuer from the tab_betreuer
public void RemoveUser(int id)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "delete from tab_betreuer where ID = " + id + "";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = UserConnection;
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Data Removed Successful");
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
static string test(string query, string xml, string doc)
{
InMemoryStore store = InMemoryStore.getInstance();
Zorba zorba = Zorba.getInstance(store);
XmlDataManager dataManager = zorba.getXmlDataManager();
Iterator docIter = dataManager.parseXML(xml);
docIter.open();
Item idoc = new Item();
docIter.next(idoc);
docIter.close();
docIter.Dispose();
DocumentManager docManager = dataManager.getDocumentManager();
docManager.put(doc, idoc);
XQuery xquery = zorba.compileQuery(query);
string result = xquery.execute();
xquery.destroy();
xquery.Dispose();
zorba.shutdown();
InMemoryStore.shutdown(store);
return(result);
}
public void AddHochschule(int Land_ID)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "insert into tab_hochschule ([txtName],[intLand],[Hochschulestadt])" +
" values ('" + NameTextBox.Text.Trim() + "','" + Land_ID + "','" + Stadt_CB.Text.Trim() + "')";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand
{
CommandType = CommandType.Text,
CommandText = query,
Connection = UserConnection
};
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Data Saved Successful");
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Check if the BewerbungIDin tab_fehltcp exist
public bool Search_BewerbungID_in_tab_FehlCP(int bewerbungsID)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
int count = 0;
OleDbConnection LoginConnection = new OleDbConnection();
LoginConnection.ConnectionString = config.DbConnectionString;
LoginConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = LoginConnection;
cmd.CommandText = "SELECT intBewerbung FROM tab_fehlcp where intBewerbung = " + bewerbungsID + " ";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
count++;
}
LoginConnection.Close();
// Test if the given username exists in the database
if (count != 0)
{
return(true);
}
else
{
return(false);
}
}
public void AddSemester()
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "insert into tab_semester ([txtSemester])" +
" values ('" + SemesterTextBox.Text.Trim() + "')";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand
{
CommandType = CommandType.Text,
CommandText = query,
Connection = UserConnection
};
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Data Saved Successful");
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// Disconect the user
private void LogoutButton_Click(object sender, EventArgs e)
{
UserData user = new UserData();
SuperUserData superUser = new SuperUserData();
if (File.Exists("userData.xml"))
{
user = XmlDataManager.XmlUserDataReader("userData.xml");
if (user.UserAttribut != "SuperAdmin")
{
Disconnect_USer(User_online);
//Application.ExitThread();
Application.Exit();
}
else
{
if (File.Exists("SuperUserStatut.xml"))
{
superUser = XmlDataManager.XmlSuperUserDataReader("SuperUserStatut.xml");
superUser.SuperUserstatut = 0;
XmlDataManager.XmlDataWriter(superUser, "SuperUserStatut.xml");
Application.Exit();
}
}
}
}
public int Search_Land_ID(string LandName)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT ID FROM tab_land where txtName = '" + LandName + "' ";
OleDbConnection LandConnection1 = new OleDbConnection();
LandConnection1.ConnectionString = config.DbConnectionString;
LandConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = LandConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["ID"].ToString();
int id = Convert.ToInt32(resultat);
LandConnection1.Close();
return(id);
}
else
{
LandConnection1.Close();
return(0);
}
}
// Function to einstufen
public void Einstufen(int bewerbungid, string benutzer, string masterstudiengang, string masterstudiengang2, string masterstudiengang3)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "insert into tab_status ([intBewerbung],[datDatum],[txtBenutzer],[Masterstudiengang_1],[Masterstudiengang_2],[Masterstudiengang_3]) values ('" + bewerbungid + "','" + DateTime.Now + "','" + benutzer + "','" + masterstudiengang + "','" + masterstudiengang2 + "', '" + masterstudiengang3 + "')";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = UserConnection;
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// function to search a Semester_ID in database
private int Search_ID_Smester(string Semester)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT Id FROM tab_semester where txtSemester = '" + Semester + "'";
OleDbConnection UserConnection1 = new OleDbConnection();
UserConnection1.ConnectionString = config.DbConnectionString;
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = UserConnection1;
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = query;
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["ID"].ToString();
UserConnection1.Close();
int id = Convert.ToInt32(resultat);
return(id);
}
else
{
UserConnection1.Close();
return(0);
}
}
//Load Nationalität in NationalitätComboBox
private void Load_Nationalitaet_Database()
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT txtNationalität FROM tab_land";
OleDbConnection UserConnection1 = new OleDbConnection
{
ConnectionString = config.DbConnectionString
};
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = UserConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
string sName = reader["txtNationalität"].ToString();
NationalitaetCB.Items.Add(sName);
}
UserConnection1.Close();
}
//Load Hochschule in MasterStudiengangComboBox
private void Load_Master_Studiengang_Database()
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT txtName FROM tab_masterstudiengang";
OleDbConnection UserConnection1 = new OleDbConnection
{
ConnectionString = config.DbConnectionString
};
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = UserConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
string sName = reader["txtName"].ToString();
MasterstudiengangCB.Items.Add(sName);
MasterstudiengangCB2.Items.Add(sName);
MasterstudiengangCB3.Items.Add(sName);
}
UserConnection1.Close();
}
// return the studiengang ID
public int Search_StudiengangID(string studiengang)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT ID FROM tab_studiengang where txtName = '" + studiengang + "'";
OleDbConnection UserConnection1 = new OleDbConnection();
UserConnection1.ConnectionString = config.DbConnectionString;
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = UserConnection1;
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = query;
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["ID"].ToString();
UserConnection1.Close();
int id = Convert.ToInt32(resultat);
return(id);
}
else
{
UserConnection1.Close();
return(0);
}
}
public bool Check_Benutzer_Data(UserData userdat)
{
int count = 0;
config = XmlDataManager.XmlConfigDataReader("configs.xml");
OleDbConnection LoginConnection = new OleDbConnection();
LoginConnection.ConnectionString = config.DbConnectionString;
LoginConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = LoginConnection;
cmd.CommandText = "SELECT * FROM tab_User where Username = '******' and Password = '******' and Attribut = '" + userdat.UserAttribut + "' ";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
count++;
}
LoginConnection.Close();
// Test if the given username exists in the database
if (count == 1)
{
return(true);
}
else
{
return(false);
}
}
private void Load_Hochschule_Database()
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT txtName FROM tab_hochschule";
OleDbConnection UserConnection1 = new OleDbConnection
{
ConnectionString = config.DbConnectionString
};
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = UserConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
string sName = reader["txtName"].ToString();
HochschuleComboBox.Items.Add(sName);
}
}
// Search the connected user
public bool Search_User_Online_To_Disconect(UserData userdat)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
int count = 0;
OleDbConnection LoginConnection = new OleDbConnection();
LoginConnection.ConnectionString = config.DbConnectionString;
LoginConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = LoginConnection;
cmd.CommandText = "SELECT * FROM tab_User where Username = '******' ";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
count++;
}
LoginConnection.Close();
// Test if the given username exists in the database
if (count == 1)
{
return(true);
}
else
{
return(false);
}
}
// fill the ComboBox Titel with Database
private void Load_Titel_Database()
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT txtTitel FROM tab_titel";
OleDbConnection UserConnection1 = new OleDbConnection
{
ConnectionString = config.DbConnectionString
};
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand
{
Connection = UserConnection1,
CommandType = CommandType.Text,
CommandText = query
};
OleDbDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
string Titel = reader["txtTitel"].ToString();
TitelComboBox.Items.Add(Titel);
}
UserConnection1.Close();
}
// Anzeige der Informationen des Studenten
public void ShowStudentenInfo(DataGridView grid, int BewerbungsID)
{
try
{
/*t.txtTitel AS Titel, tab_titel AS t, WHERE t.ID = b.intTitel */
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query1 = "SELECT t.intBewerbung AS BewerbungsID, t.Masterstudiengang_1 AS Masterstudiengang_1, t.Masterstudiengang_2 AS Masterstudiengang_2, t.Masterstudiengang_3 AS Masterstudiengang_3, t.datDatum AS Datum, t.txtBenutzer AS txtBenutzer" +
"FROM tab_status AS t WHERE t.intBewerbung = " + BewerbungsID + "";
string query = "SELECT intBewerbung AS BewerbungID, Masterstudiengang_1 AS Masterstudiengang1, Masterstudiengang_2 AS Masterstudiengang2, Masterstudiengang_3 AS Masterstudiengang3, datDatum AS Datum, txtBenutzer AS Benutzer " +
"FROM tab_status AS t WHERE t.intBewerbung = " + BewerbungsID + "";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
UserConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = UserConnection;
cmd.CommandText = query;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
grid.DataSource = dt;
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
// return the nationality ID
public string Search_Nationalitaet(int nationalitaetID)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT Nationalitaet FROM tab_land where ID = " + nationalitaetID + "";
OleDbConnection UserConnection1 = new OleDbConnection();
UserConnection1.ConnectionString = config.DbConnectionString;
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = UserConnection1;
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = query;
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["txtNationalität"].ToString();
UserConnection1.Close();
return(resultat);
}
else
{
UserConnection1.Close();
return(null);
}
}
// function to search a Semester in database
public string Search_ID_Smester(int SemesterID)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT txtSemester FROM tab_semester where Id = " + SemesterID + "";
OleDbConnection UserConnection1 = new OleDbConnection();
UserConnection1.ConnectionString = config.DbConnectionString;
UserConnection1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = UserConnection1;
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = query;
OleDbDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string resultat = reader["txtSemester"].ToString();
UserConnection1.Close();
return(resultat);
}
else
{
UserConnection1.Close();
return(null);
}
}
public void Show_Database(DataGridView grid, int ID)
{
try
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "SELECT t.intBewerbung AS BewerbungsID, t.datDatum AS Datum, t.txtBenutzer AS Benutzer, t.Masterstudiengang_1, t.Masterstudiengang_2, t.Masterstudiengang_3 FROM tab_status AS t WHERE t.intBewerbung = " + ID + " ";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
UserConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = UserConnection;
cmd.CommandText = query;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
grid.DataSource = dt;
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
private String ExportDocx(string masterStudiengang, int fileIndex)
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
WordDocCreator Docx = new WordDocCreator(@Path.GetDirectoryName(Application.ExecutablePath).Trim() + "\\bci.docx");
Docx.FindAndReplace("<name>", bewerbungsdata.Name.Trim());
Docx.FindAndReplace("<vorname>", bewerbungsdata.Vorname.Trim());
Docx.FindAndReplace("<nationalitaet>", bewerbungsdata.Nationalitaet.Trim());
Docx.FindAndReplace("<studiengang>", bewerbungsdata.Studiengang.Trim());
Docx.FindAndReplace("<hochschule>", bewerbungsdata.Hochschule.Trim());
Docx.FindAndReplace("<note>", bewerbungsdata.Note.Trim());
Docx.FindAndReplace("<erworbenecp>", bewerbungsdata.Creditpunkte.Trim());
Docx.FindAndReplace("<masterstudiengang>", masterStudiengang.Trim());
Docx.FindAndReplace("<ablehnungsgrund>", bewerbungsdata.Ablehnungsgrund.Trim());
Docx.FindAndReplace("<kommentar>", bewerbungsdata.Comment.Trim());
Docx.FindAndReplace("<date>", DateTime.Now.ToShortDateString());
string filename = "\\" + bewerbungsdata.Name.Trim() + bewerbungsdata.Vorname.Trim() + fileIndex.ToString().Trim() + ".docx";
string filepath = config.SaveDocxPath.Trim() + filename.Trim();
Docx.CreateDocx(filepath);
return(filepath);
}
public bool RemoveBewerbung(int id)
{
bool resp = false;
config = XmlDataManager.XmlConfigDataReader("configs.xml");
string query = "delete from tab_bewerbung where ID = " + id + "";
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = UserConnection;
UserConnection.Open();
try
{
cmd.ExecuteNonQuery();
UserConnection.Close();
resp = true;
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
return(resp);
}
private void Show_Database()
{
try
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
OleDbConnection UserConnection = new OleDbConnection();
UserConnection.ConnectionString = config.DbConnectionString;
UserConnection.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = UserConnection;
cmd.CommandText = "SELECT tab_studiengang.ID AS ID , tab_studiengang.txtName AS Studiengang , tab_hochschule.txtName AS Hochschule, tab_studiengang.intRegelstudienzeit AS Regelstudienzeit," +
" tab_studiengang.intCredits AS Credit,tab_titel.txtTitel AS Titel, tab_studiengang.blnCPErsatz AS CPErsatz " +
"FROM tab_studiengang, tab_hochschule, tab_titel " +
"WHERE tab_studiengang.intHochschule = tab_hochschule.ID AND tab_studiengang.intTitel = tab_titel.ID ORDER BY tab_studiengang.ID ";
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
StudiengangGridView.DataSource = dt;
UserConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex);
}
}
public static void SaveCurrentUsers()
{
if (File.Exists(Constants.DataFilePath + Constants.XmlDataFileName))
{
IDataManager <User> dataManager = new XmlDataManager <User>();
dataManager.Save(CurrentUsers.ToList(), Constants.DataFilePath + Constants.XmlDataFileName);
}
}
private void SettingsUI_Load(object sender, EventArgs e)
{
if (File.Exists("configs.xml"))
{
config = XmlDataManager.XmlConfigDataReader("configs.xml");
DbPathTexbox.Text = config.DatabasePath;
DocxPathTB.Text = config.SaveDocxPath;
}
}
//Window Loader
private void Form_Login_Load(object sender, EventArgs e)
{
if (File.Exists("userData.xml"))
{
userData = XmlDataManager.XmlUserDataReader("userData.xml");
UserNameTextBox.Text = userData.Username;
AttributComboBox.Text = userData.UserAttribut;
}
}
