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); } }
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); } }
private LectureItem[] getTimeTable(string value, DateTime startTime, DateTime endTime, int typ) { sqlConnection = null; sqlConnection = TimeTableDatabase.getConnectionTimeTable(); try { using (sqlConnection) { //DateTime endTime = new DateTime(); endTime = Convert.ToDateTime(endTime);//DateTime.Now; DateTime StartHourLessons = new DateTime(); DateTime EndHourLessons = new DateTime(); DateTime LessonsDay = new DateTime(); LectureItem SQLItem = new LectureItem(); List <LectureItem> list = new List <LectureItem>(); //SELECT[Unterricht].[Tag], [Unterricht].Stunde,[Unterricht].OrtsID,[Unterricht].DozentID,[Unterricht].NutzungsID,[Unterricht].Bemerkung,[Stunde].Anfang,[Stunde].Ende,[Zuhoerer].SGID FROM[Unterricht] //LEFT JOIN[Stunde] ON[Unterricht].Stunde = [Stunde].Stunde //LEFT JOIN[Zuhoerer] ON[Unterricht].Tag = [Zuhoerer].Tag AND[Unterricht].Stunde = [Zuhoerer].Stunde AND[Unterricht].OrtsID = [Zuhoerer].OrtsID //WHERE[Zuhoerer].Tag BETWEEN '2017-01-01 00:00:00' AND '2019-01-01 00:00:00' AND[SGID]='WI16-1' ORDER BY[Zuhoerer].SGID, [Unterricht].Tag, [Stunde].Stunde string SQLWhere = ""; if (typ == 1) { SQLWhere = " AND[DozentID] = '" + value + "' "; } else { SQLWhere = " AND[SGID]= '" + value + "' "; } string SQL = "SELECT[Unterricht].[Tag], [Unterricht].Stunde,[Unterricht].OrtsID,[Unterricht].DozentID,[Unterricht].NutzungsID,[Unterricht].Bemerkung,[Stunde].Anfang,[Stunde].Ende,[Zuhoerer].SGID FROM[Unterricht] " + "LEFT JOIN[Stunde] ON[Unterricht].Stunde = [Stunde].Stunde " + "LEFT JOIN[Zuhoerer] ON[Unterricht].Tag = [Zuhoerer].Tag AND[Unterricht].Stunde = [Zuhoerer].Stunde AND[Unterricht].OrtsID = [Zuhoerer].OrtsID " + "WHERE[Zuhoerer].Tag BETWEEN '" + startTime + "' AND '" + endTime + "' " + SQLWhere + " ORDER BY[Zuhoerer].SGID, [Unterricht].Tag, [Stunde].Stunde"; sqlConnection.Open(); SqlDataReader myReader = null; SqlCommand myCommand = new SqlCommand(SQL, sqlConnection); myReader = myCommand.ExecuteReader(); while (myReader.Read()) { if (myReader["Anfang"].ToString() != "") { StartHourLessons = Convert.ToDateTime(myReader["Anfang"]); } else { StartHourLessons = Convert.ToDateTime("01.01.2000 00:00"); } if (myReader["Ende"].ToString() != "") { EndHourLessons = Convert.ToDateTime(myReader["Ende"]); } else { EndHourLessons = Convert.ToDateTime("01.01.2000 00:00"); } LessonsDay = Convert.ToDateTime(myReader["Tag"]); SQLItem.Start = Convert.ToDateTime(LessonsDay.Date + StartHourLessons.TimeOfDay); SQLItem.End = Convert.ToDateTime(LessonsDay.Date + EndHourLessons.TimeOfDay);; SQLItem.Lecturer = myReader["DozentID"].ToString(); SQLItem.Place = myReader["OrtsID"].ToString(); SQLItem.StudyGroup = myReader["SGID"].ToString(); SQLItem.Title = myReader["NutzungsID"].ToString(); //Bemerkungsfeld feld noch list.Add(SQLItem); SQLItem = new LectureItem(); } sqlConnection.Close(); sqlConnection = null; return(list.ToArray()); } } catch (System.Exception) { return(null); } }