private string convertLectureItemToString(LectureItem item)
{
return($"{item.StudyGroup};" +
$"{item.Semester};" +
$"{item.Title};" +
$"{item.Lecturer};" +
$"{item.Place};" +
$"{item.Start};" +
$"{item.End}");
}
public IActionResult getLectureFeed([FromQuery] string studentNumber = "", [FromQuery] string hash = "")
{
long userID = 0;
LectureItem[] lectures = new LectureItem[0];
UserItem userItem = userDB.getUserByName(studentNumber);
if (userItem != null)
{
userID = userItem.UserID;
lectures = lectureService.getLectures(userID);
}
return(Ok(lectures));
}
public LectureItem[] getSemesterLectures(string studyGroup, SemesterItem semesterItem)
{
List <LectureItem> lectures = new List <LectureItem>();
using (StreamReader sr = new StreamReader(filepath))
{
string line;
while ((line = sr.ReadLine()) != null)
{
LectureItem item = convertStringToLectureItem(line);
if (item.Start >= semesterItem.Start && item.StudyGroup == studyGroup && item.Start <= semesterItem.End)
{
lectures.Add(item);
}
}
}
lectures = sortLectures(lectures);
return(lectures.ToArray());
}
public static List <LectureItem> readExcel()
{
List <LectureItem> items = new List <LectureItem>();
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.DefaultExt = ".xls";
dlg.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
if (dlg.ShowDialog() != true)
{
return(null);
}
onFileSelected?.Invoke(dlg.FileName);
string[,] data = LoadExcel(dlg.FileName);
for (int row = 0; row < data.GetLength(0); row++)
{
string[] rowArray = new string[data.GetLength(1)];
for (int col = 0; col < data.GetLength(1); col++)
{
rowArray[col] = data[row, col];
}
LectureItem item = null;
try {
item = ProcessRow(row + 1, rowArray);
} catch (Exception ex) {
return(null);
}
if (item != null)
{
items.Add(item);
}
}
return(items);
}
public LectureItem[] getLecturesByLecturer(string lecturer, DateTime startTime, DateTime endDate)
{
List <LectureItem> lectures = new List <LectureItem>();
using (StreamReader sr = new StreamReader(filepath))
{
string currentLine;
while ((currentLine = sr.ReadLine()) != null)
{
LectureItem item = convertStringToLectureItem(currentLine);
if (item.Start >= startTime && item.Lecturer == lecturer)
{
lectures.Add(item);
}
}
}
lectures = sortLectures(lectures);
lectures = lectures.Where(x => x.Start <= endDate).ToList();
return(lectures.ToArray());
}
public LectureItem[] getLectures(long userID)
{
LectureItem[] lectures = new LectureItem[0];
UserItem userItem = userDB.getUserItem(userID);
if (userItem != null)
{
if (userItem.UserType.Name == "Student")
{
string studyGroup = userItem.StudyGroup.ShortName;
SemesterItem currentSemester = semesterDB.getCurrentSemesterByStudyGroup(studyGroup);
if (currentSemester == null)
{
//Create pseudo-semester
currentSemester = new SemesterItem
{
Start = getFirstOfMonth(),
End = getFirstOfMonth().AddMonths(3),
StudyGroup = studyGroup
};
}
lectures = timetableDB.getSemesterLectures(studyGroup, currentSemester);
}
else if (userItem.UserType.Name == "Dozent")
{
//ToDo: get dozID
string dozID = "Prof. Penzel";
DateTime startDate = getFirstOfMonth();
DateTime endDate = startDate.AddMonths(3);
lectures = timetableDB.getLecturesByLecturer(dozID, startDate, endDate);
}
}
return(lectures);
}
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);
}
}