/// <summary>
/// Creates a ContactItem based on the given ContactItem
/// </summary>
/// <param name="item"></param>
/// <returns>ContactItem</returns>
public ContactItem createContactItem(ContactItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string courseID = "";
if (item.Course.ID == 0)
{
courseID = "NULL";
}
else
{
courseID = "'" + item.Course.ID.ToString() + "'";
}
string SQL = "INSERT INTO [contact] ([relationtypid],[lastname],[firstname],[titel],[mail], [phonenumber],[roomnumber],[coursetypid],[responsibility]) " +
"VALUES('" + item.Type.ID.ToString() + "','" + item.LastName + "','" + item.FirstName + "','" + item.Title + "','" + item.Email + "','" + item.TelNumber + "','" + item.Room + "'," + courseID + ",'" + item.Responsibility + "');" +
"SELECT SCOPE_IDENTITY();";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
sqlConnection.Close();
sqlConnection = null;
return(getContactItem(LastID));
}
}
catch (System.Exception ex)
{
return(null);
}
}
/// <summary>
/// Saves new item and assing new id, return the new item
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public NewsItem saveNewPost(NewsItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "INSERT INTO [news] ([newsgroupid],[headline],[text],[createtime],[updatetime],[push],[attachmentsid]) " +
"VALUES (" + item.PostGroup.PostGroupID.ToString() + ",'" + item.Title + "','" + item.Message + "',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,0,NULL);" +
"SELECT SCOPE_IDENTITY()";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
sqlConnection.Close();
sqlConnection = null;
item.ID = LastID;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
public MenuItem editMenu(int id, MenuItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "UPDATE [menu] SET [mealid]='" + item.Meal.MealID + "',[date]='" + item.Date.ToString() + "'," +
" [price]='" + item.Price.ToString().Replace(",", ".") + "'" +
" WHERE [foodplanid] ='" + id.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return(item);
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
return(null);
}
}
/// <summary>
/// Return all News-Items starting by startID and returns amount, filtered by groups
/// </summary>
/// <param name="amount">How many items should found</param>
/// <param name="startID">What is the startid, desc</param>
/// <param name="groups">What groups should loaded</param>
/// <returns></returns>
public NewsItem[] getPosts(int amount, Int64 startID, int[] groups)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string groupid = "";
int j = 0;
foreach (int i in groups)
{
if (j > 0)
{
groupid = groupid + "," + i.ToString();
}
else
{
groupid = i.ToString();
}
j++;
}
NewsItem SQLItem = new NewsItem();
SQLItem.PostGroup = new PostGroupItem();
List <NewsItem> NewsItemList = new List <NewsItem>();
string SQL = "Select TOP " + amount + " news.newsid, news.newsgroupid, news.headline, news.text, news.createtime, news.updatetime, news.push, news.attachmentsid, newsgroup.newsgroupname " +
"from news " +
"LEFT JOIN newsgroup on news.newsgroupid = newsgroup.newsgroupid " +
"WHERE news.newsid >= " + startID + " AND news.newsgroupid IN(" + groupid + ")" +
" ORDER BY newsid DESC";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.ID = Convert.ToInt32(myReader["newsid"]);
SQLItem.Message = myReader["text"].ToString();
SQLItem.Title = myReader["headline"].ToString();
SQLItem.Date = Convert.ToDateTime(myReader["createtime"]);
//SQLItem.AuthorID
SQLItem.PostGroup.PostGroupID = Convert.ToInt32(myReader["newsgroupid"]);
SQLItem.PostGroup.Name = myReader["newsgroupname"].ToString();
NewsItemList.Add(SQLItem);
SQLItem = new NewsItem();
SQLItem.PostGroup = new PostGroupItem();
}
sqlConnection.Close();
sqlConnection = null;
return(NewsItemList.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
/// <summary>
/// return all groups in a array
/// </summary>
/// <returns></returns>
public PostGroupItem[] getPostGroupItems()
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
PostGroupItem SQLItem = new PostGroupItem();
List <PostGroupItem> lstPGI = new List <PostGroupItem>();
string SQL = "SELECT [newsgroupid],[newsgroupname] " +
" FROM[newsgroup];";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.Name = myReader["newsgroupname"].ToString();
SQLItem.PostGroupID = Convert.ToInt32(myReader["newsgroupid"]);
lstPGI.Add(SQLItem);
SQLItem = new PostGroupItem();
}
sqlConnection.Close();
sqlConnection = null;
return(lstPGI.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
/// <summary>
/// save complete new Item
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public PostGroupItem saveNewPostGroupItem(PostGroupItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "INSERT INTO [newsgroup] ([newsgroupname]) " +
"VALUES ('" + item.Name.ToString() + "');" +
"SELECT SCOPE_IDENTITY()";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
sqlConnection.Close();
sqlConnection = null;
item.PostGroupID = LastID;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
public UserItem saveNewUserItem(UserItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
//In der SQL Anweisung muss noch Contacttypid und CourseTypID und Verantwortlicher geändert werden.
string SQL = "INSERT INTO [contact] " +
"([mail],[lastname],[firstname],[dsgvo],[username], [groupid],[relationtypid],[dsgvodate]) " +
"VALUES('" + item.Email + "','" + item.Lastname + "','" + item.Firstname + "','" + item.DSGVO.ToString() + "','" + item.Username + "','" + item.StudyGroup.ID.ToString() + "','" + item.UserType.ID.ToString() + "','" + item.DSGVODate.ToString() + "');" +
"SELECT SCOPE_IDENTITY();";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
item.UserID = LastID;
sqlConnection.Close();
sqlConnection = null;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
/// <summary>
/// save complete new AuthorsToPostGroup
/// </summary>
/// <returns></returns>
public void addUserToPostGroupAuthors(int postGroupID, long userID)
{
try
{
using (sqlConnection)
{
if (checkIfUserIsPostGroupAuthor(postGroupID, userID) == false)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
string SQL = "INSERT INTO [postgroupauthor] ([userid],[newsgroupid]) " +
"VALUES (" + userID.ToString() + "," + postGroupID.ToString() + ");";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
}
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
}
}
public SemesterItem getCurrentSemesterByStudyGroup(string studyGroup)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnectionTimeTable();
SemesterItem Item = new SemesterItem();
try
{
using (sqlConnection)
{
string SQL = "SELECT Min( Tag ) AS Tag_start, Max( Tag ) AS Tag_ende,[Semester] FROM [SG_anwesend] " +
"WHERE SGID = '" + studyGroup + "' AND[Semester] IN(SELECT[Semester] FROM[SG_anwesend] " +
"WHERE SGID = '" + studyGroup + "' AND Tag BETWEEN CONVERT(date, DATEADD(WEEK, -2, GETDATE())) AND CONVERT(date, DATEADD(WEEK, 2, GETDATE())) Group BY Semester) Group BY Semester;";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
Item.Semester = Convert.ToInt32(myReader["Semester"]);
Item.Start = Convert.ToDateTime(myReader["Tag_start"]);
Item.End = Convert.ToDateTime(myReader["Tag_ende"]);
Item.StudyGroup = studyGroup;
}
sqlConnection.Close();
sqlConnection = null;
return(Item);
}
}
catch (System.Exception)
{
return(null);
}
}
/// <summary>
/// Edit UserItem by ID
/// </summary>
/// <param name="id"></param>
/// <param name="item"></param>
/// <returns></returns>
public UserItem editUserItem(long id, UserItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "UPDATE [user] SET " +
"[mail]='" + item.Email + "'," +
"[lastname]='" + item.Lastname + "'," +
"[firstname]='" + item.Firstname + "'," +
"[username]='" + item.Username + "'," +
"[groupid]='" + item.StudyGroup.ID.ToString() + "'," +
"[relationtypid]='" + item.UserType.ID.ToString() + "'," +
"[dsgvo]='" + item.DSGVO.ToString() + "'," +
"[dsgvodate]='" + item.DSGVODate.ToString() + "'" +
" WHERE [userid] ='" + id.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return(getUserItem(id));
}
}
catch (System.Exception)
{
return(null);
}
}
private MenuItem[] getAllMenusByFitlerWhere(string SQLWhere)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
MenuItem SQLItem = new MenuItem();
SQLItem.Meal = new MealItem();
SQLItem.Meal.Place = new PlaceItem();
List <MenuItem> MenuItemList = new List <MenuItem>();
string SQL = "SELECT [foodplanid],[menu].[mealid],[date],[price],[attachmentsid],[foodplace].[foodplaceid], [foodplace].[name], [meal].[mealname],[meal].[description]" +
" FROM [menu] " +
"INNER JOIN [meal] on [meal].[mealid] = [menu].[mealid]" +
"INNER JOIN [foodplace] on [meal].[foodplaceid] = [foodplace].[foodplaceid] " +
SQLWhere + "" +
" ORDER BY [date],[mealname] ASC;";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
int j = 0;
while (myReader.Read())
{
SQLItem.Date = Convert.ToDateTime(myReader["date"]);
SQLItem.Price = Convert.ToDecimal(myReader["price"]);
SQLItem.MenuID = Convert.ToInt32(myReader["foodplanid"]);
SQLItem.Meal.MealID = Convert.ToInt32(myReader["mealid"]);
SQLItem.Meal.MealName = myReader["mealname"].ToString();
SQLItem.Meal.Description = myReader["description"].ToString();
SQLItem.Meal.Place.PlaceID = Convert.ToInt32(myReader["foodplaceid"].ToString());
SQLItem.Meal.Place.PlaceName = myReader["name"].ToString();
MenuItemList.Add(SQLItem);
SQLItem = new MenuItem();
SQLItem.Meal = new MealItem();
SQLItem.Meal.Place = new PlaceItem();
j++;
}
sqlConnection.Close();
sqlConnection = null;
if (j == 0)
{
return(null);
}
else
{
return(MenuItemList.ToArray());
}
}
}
catch (System.Exception)
{
return(null);
}
}
public PlaceItem saveNewPlace(PlaceItem place)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "INSERT INTO [foodplace] ([name]) " +
"VALUES('" + place.PlaceName + "');" +
"SELECT SCOPE_IDENTITY();";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
sqlConnection.Close();
sqlConnection = null;
return getPlaceItem(LastID);
}
}
catch (System.Exception ex)
{
return null;
}
}
public void deleteMenu(int id)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "DELETE FROM [menu] WHERE [foodplanid] ='" + id.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return;
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
return;
}
}
/// <summary>
/// Update existing News-Item by replacing
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public NewsItem editPost(NewsItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "UPDATE [news] SET " +
"[newsgroupid]='" + item.PostGroup.PostGroupID.ToString() + "'," +
"[headline]='" + item.Title + "',[text]='" + item.Message + "'," +
"[updatetime]= CURRENT_TIMESTAMP,[push]=0," +
"[attachmentsid]=NULL " +
" WHERE [newsid] ='" + item.ID.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
public MealItem getMealItem(int MealID)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "";
MealItem SQLItem = new MealItem();
SQLItem.Place = new PlaceItem();
SQL = "SELECT [mealid],[mealname],[description],[foodplace].[foodplaceid], [foodplace].[name] " +
" FROM [meal] INNER JOIN [foodplace] on [meal].[foodplaceid] = [foodplace].[foodplaceid] " +
" WHERE [mealid]='" + MealID.ToString() + "';";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
SQLItem.MealID = Convert.ToInt32(myReader["mealid"]);
SQLItem.MealName = myReader["mealname"].ToString();
SQLItem.Description = myReader["description"].ToString();
SQLItem.Place.PlaceID = Convert.ToInt32(myReader["foodplaceid"].ToString());
SQLItem.Place.PlaceName = myReader["name"].ToString();
sqlConnection.Close();
sqlConnection = null;
return(SQLItem);
}
else
{
sqlConnection.Close();
sqlConnection = null;
return(null);
}
}
}
catch (System.Exception)
{
return(null);
}
}
public MealItem[] getMeals()
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
MealItem SQLItem = new MealItem();
SQLItem.Place = new PlaceItem();
List <MealItem> MealItemList = new List <MealItem>();
string SQL = "SELECT [mealid],[mealname],[description],[foodplace].[foodplaceid], [foodplace].[name] " +
" FROM [meal] INNER JOIN [foodplace] on [meal].[foodplaceid] = [foodplace].[foodplaceid] " +
";";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.MealID = Convert.ToInt32(myReader["mealid"]);
SQLItem.MealName = myReader["mealname"].ToString();
SQLItem.Description = myReader["description"].ToString();
SQLItem.Place.PlaceID = Convert.ToInt32(myReader["foodplaceid"].ToString());
SQLItem.Place.PlaceName = myReader["name"].ToString();
MealItemList.Add(SQLItem);
SQLItem = new MealItem();
SQLItem.Place = new PlaceItem();
}
sqlConnection.Close();
sqlConnection = null;
return(MealItemList.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
public StudyCourse getCourseById(int id)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
StudyCourse SQLItem = new StudyCourse();
string SQL = "SELECT [coursetypid],[shortname],[longname]" +
" FROM [coursetyp] WHERE [coursetypid] = '" + id.ToString() + "';";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
SQLItem.ID = Convert.ToInt32(myReader["coursetypid"].ToString());
SQLItem.LongText = myReader["longname"].ToString();
SQLItem.ShortText = myReader["shortname"].ToString();
sqlConnection.Close();
sqlConnection = null;
return(SQLItem);
}
else
{
sqlConnection.Close();
sqlConnection = null;
return(null);
}
}
}
catch (System.Exception)
{
return(null);
}
}
public PlaceItem getPlaceItemByName(string name)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
PlaceItem SQLItem = new PlaceItem();
string SQL = "SELECT [foodplaceid],[name] FROM [foodplace] " +
" WHERE [name]='" + name + "';";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
SQLItem.PlaceID = Convert.ToInt32(myReader["foodplaceid"]);
SQLItem.PlaceName = myReader["name"].ToString();
sqlConnection.Close();
sqlConnection = null;
return SQLItem;
}
else
{
sqlConnection.Close();
sqlConnection = null;
return null;
}
}
}
catch (System.Exception)
{
return null;
}
}
/// <summary>
/// Delete User by ID
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public void deleteUserItem(long id)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "DELETE FROM [user] WHERE [userid] =" + id.ToString() + ";";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return;
}
}
catch (System.Exception)
{
return;
}
}
public StudyCourse[] getAllCourses()
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
StudyCourse SQLItem = new StudyCourse();
List <StudyCourse> list = new List <StudyCourse>();
string SQL = "SELECT [coursetypid],[shortname],[longname]" +
" FROM [coursetyp];";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.ID = Convert.ToInt32(myReader["coursetypid"].ToString());
SQLItem.LongText = myReader["longname"].ToString();
SQLItem.ShortText = myReader["shortname"].ToString();
list.Add(SQLItem);
SQLItem = new StudyCourse();
}
sqlConnection.Close();
sqlConnection = null;
return(list.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
/// <summary>
/// return groupitem by id
/// </summary>
/// <returns></returns>
public PostGroupItem getPostGroupItem(int id)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
PostGroupItem SQLItem = new PostGroupItem();
string SQL = "SELECT [newsgroupid],[newsgroupname] " +
" FROM[newsgroup] WHERE [newsgroupid]=" + id.ToString() + ";";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
SQLItem.Name = myReader["newsgroupname"].ToString();
SQLItem.PostGroupID = Convert.ToInt32(myReader["newsgroupid"]);
sqlConnection.Close();
sqlConnection = null;
return(SQLItem);
}
else
{
sqlConnection.Close();
sqlConnection = null;
return(null);
}
}
}
catch (System.Exception)
{
return(null);
}
}
public PostGroupUserPushNotificationSetting[] getSubscribedPostGroupsSettings(long userID)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
PostGroupUserPushNotificationSetting SQLItem = new PostGroupUserPushNotificationSetting();
List <PostGroupUserPushNotificationSetting> list = new List <PostGroupUserPushNotificationSetting>();
string SQL = "SELECT[newsgroupid],[active] FROM subscribe WHERE userid='" + userID.ToString() + "';";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.PostGroupID = Convert.ToInt32(myReader["newsgroupid"].ToString());
SQLItem.PostGroupActive = Convert.ToBoolean(myReader["active"].ToString());
list.Add(SQLItem);
SQLItem = new PostGroupUserPushNotificationSetting();
}
sqlConnection.Close();
sqlConnection = null;
return(list.ToArray());
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
return(null);
}
}
public PlaceItem editPlace(int id, PlaceItem place)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "UPDATE [foodplace] SET [name]='" + place.PlaceName + "'" +
" WHERE [foodplaceid] ='" + id.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return place;
}
}
catch (System.Exception)
{
return null;
}
}
public bool checkIfUserIsPostGroupAuthor(int postGroupID, long userID)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "SELECT [postgroupauthorid] " +
" FROM [postgroupauthor] WHERE [newsgroupid]=" + postGroupID.ToString() + " AND [userid] = " + userID.ToString() + ";";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
sqlConnection.Close();
sqlConnection = null;
return(true);
}
else
{
sqlConnection.Close();
sqlConnection = null;
return(false);
}
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
return(false);
}
}
public PlaceItem[] getPlaces()
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
PlaceItem SQLItem = new PlaceItem();
List<PlaceItem> ListPlaceItem = new List<PlaceItem>();
string SQL = "SELECT [foodplaceid],[name] FROM [foodplace] " +
" ;";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
SQLItem.PlaceID = Convert.ToInt32(myReader["foodplaceid"]);
SQLItem.PlaceName = myReader["name"].ToString();
ListPlaceItem.Add(SQLItem);
SQLItem = new PlaceItem();
}
sqlConnection.Close();
sqlConnection = null;
return ListPlaceItem.ToArray();
}
}
catch (System.Exception)
{
return null;
}
}
/// <summary>
/// Edits a ContactItem based on the given ContactItem except for the ID
/// </summary>
/// <param name="id"></param>
/// <param name="item"></param>
/// <returns>ContactItem</returns>
public ContactItem editContactItem(int id, ContactItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string courseID = "";
if (item.Course.ID == 0)
{
courseID = "NULL";
}
else
{
courseID = "'" + item.Course.ID.ToString() + "'";
}
//In der SQL Anweisung muss noch Contacttypid und CourseTypID und Verantwortlicher geändert werden.
string SQL = "UPDATE [contact] SET [lastname]='" + item.LastName + "',[firstname]='" + item.FirstName + "'," +
"[titel]='" + item.Title + "',[mail]='" + item.Email + "', [phonenumber]='" + item.TelNumber + "'," +
"[roomnumber]='" + item.Room + "', [coursetypid]=" + courseID + " , [relationtypid]='" + item.Type.ID.ToString() + "', [responsibility] ='" + item.Responsibility + "' " +
" WHERE [contactid] ='" + id.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
public MenuItem saveNewMenu(MenuItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "INSERT INTO [menu] ([mealid],[date],[price]) " +
"VALUES('" + item.Meal.MealID.ToString() + "','" + item.Date.ToString() + "','" + item.Price + "');" +
"SELECT SCOPE_IDENTITY();";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
int LastID = Convert.ToInt32(myCommand.ExecuteScalar());
sqlConnection.Close();
sqlConnection = null;
return(getMenuItem(LastID));
}
}
catch (System.Exception ex)
{
return(null);
}
}
public SemesterItem[] getSemesterItem(string studyGroup)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnectionTimeTable();
SemesterItem Item = new SemesterItem();
List <SemesterItem> ListItem = new List <SemesterItem>();
try
{
using (sqlConnection)
{
string SQL = "SELECT Min(Tag ) AS Tag_start, Max( Tag ) AS Tag_ende,[Semester] FROM[SG_anwesend] WHERE SGID = 'WI16-1' Group BY Semester; ";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Item.Semester = Convert.ToInt32(myReader["Semester"]);
Item.Start = Convert.ToDateTime(myReader["Tag_start"]);
Item.End = Convert.ToDateTime(myReader["Tag_ende"]);
Item.StudyGroup = studyGroup;
ListItem.Add(Item);
Item = new SemesterItem();
}
sqlConnection.Close();
sqlConnection = null;
return(ListItem.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
public void deleteUserFromPostGroupAuthors(int postGroupID, long userID)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "DELETE FROM [postgroupauthor] WHERE [newsgroupid] =" + postGroupID.ToString() + " AND [userid] = " + userID.ToString() + ";";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return;
}
}
catch (System.Exception)
{
sqlConnection.Close();
sqlConnection = null;
return;
}
}
/// <summary>
/// Update existing Postgroup-Item by ID and replacing
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public PostGroupItem editPostGroupItem(int id, PostGroupItem item)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnection();
try
{
using (sqlConnection)
{
string SQL = "UPDATE [newsgroup] SET " +
"[newsgroupname]='" + item.Name + "'" +
" WHERE [newsgroupid] ='" + item.PostGroupID.ToString() + "';";
sqlConnection.Open();
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myCommand.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection = null;
return(item);
}
}
catch (System.Exception)
{
return(null);
}
}
