public async Task <ApiReturnValue <Lecturers> > GetLecturer(int pageSize = 100, int pageNumber = 1)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
Lecturers lecturers = new Lecturers()
{
TotalCount = await GetLecturerCount(),
PageSize = pageSize,
PageNumber = pageNumber,
Rows = await GetListLecturer(pageSize, pageNumber)
};
apiReturnValue.IsSuccess = true;
apiReturnValue.Object = lecturers;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
apiReturnValue.Object = null;
apiReturnValue.Error = new ApiReturnError()
{
displayMessage = "Error - GetLecturer", errorMessage = e.Message
};
}
return(apiReturnValue);
}
public async Task <ApiReturnValue <Subjects> > GetSubject(int pageSize = 100, int pageNumber = 1)
{
return(await Task.Run(() =>
{
ApiReturnValue <Subjects> apiReturnValue = new ApiReturnValue <Subjects>();
try
{
var lstSubject = JsonConvert.DeserializeObject <List <Subject> >(System.IO.File.ReadAllText(Path.Combine(CurrentDirectory, "Data", "Subject.json")));
Subjects subjects = new Subjects();
subjects.TotalCount = lstSubject.Count();
subjects.PageNumber = pageNumber;
subjects.PageSize = pageSize;
subjects.Rows = lstSubject.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
apiReturnValue.IsSuccess = true;
apiReturnValue.Object = subjects;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
apiReturnValue.Object = null;
apiReturnValue.Error.displayMessage = "";
apiReturnValue.Error.errorMessage = e.Message;
}
return apiReturnValue;
}));
}
public async Task <ApiReturnValue <Subjects> > GetSubject(string subjectName, int pageSize = 100, int pageNumber = 1)
{
var value = await Task.Run(() =>
{
ApiReturnValue <Subjects> apiReturnValue = new ApiReturnValue <Subjects>();
try
{
var lstSubject = JsonConvert.DeserializeObject <List <Subject> >(System.IO.File.ReadAllText(Path.Combine(CurrentDirectory, "Data", "Subject.json")));
if (subjectName != "null")
{
lstSubject = (from s in lstSubject where s.SubjectDescription.ToLower().Contains(subjectName.ToLower()) select s).ToList();
}
Subjects subjects = new Subjects();
subjects.TotalCount = lstSubject.Count();
subjects.PageNumber = pageNumber;
subjects.PageSize = pageSize;
subjects.Rows = lstSubject.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
apiReturnValue.IsSuccess = true;
apiReturnValue.Object = subjects;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
}
return(apiReturnValue);
});
return(value);
}
public async Task <ApiReturnValue <Lecturers> > CreateLecturer(Lecturer lecturer)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
if (System.IO.File.Exists(Path.Combine(CurrentDirectory, "Data", "Lecturer.json")))
{
ListLecturer = JsonConvert.DeserializeObject <List <Lecturer> >(System.IO.File.ReadAllText(Path.Combine(CurrentDirectory, "Data", "Lecturer.json")));
}
ListLecturer.Add(lecturer);
System.IO.File.WriteAllText(Path.Combine(CurrentDirectory, "Data", "Lecturer.json"), JsonConvert.SerializeObject(ListLecturer));
apiReturnValue.IsSuccess = true;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
apiReturnValue.Error.displayMessage = "Error - CreateLecturer";
apiReturnValue.Error.errorMessage = e.Message;
}
return(apiReturnValue);
}
public async Task <ApiReturnValue <Subjects> > CreateSubject(Subject subject)
{
return(await Task.Run(() =>
{
ApiReturnValue <Subjects> apiReturnValue = new ApiReturnValue <Subjects>();
try
{
if (System.IO.File.Exists(Path.Combine(CurrentDirectory, "Data", "Subject.json")))
{
ListSubject = JsonConvert.DeserializeObject <List <Subject> >(System.IO.File.ReadAllText(Path.Combine(CurrentDirectory, "Data", "Subject.json")));
}
ListSubject.Add(subject);
System.IO.File.WriteAllText(Path.Combine(CurrentDirectory, "Data", "Lecturer.json"), JsonConvert.SerializeObject(ListSubject));
apiReturnValue.IsSuccess = true;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
apiReturnValue.Error.displayMessage = "Error - CreateLecturer";
apiReturnValue.Error.errorMessage = e.Message;
}
return apiReturnValue;
}));
}
public async Task <ApiReturnValue <Lecturers> > GetLecturer(string lecturerName)
{
return(await Task.Run(() =>
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
var lstLecturer = JsonConvert.DeserializeObject <List <Lecturer> >(System.IO.File.ReadAllText(Path.Combine(CurrentDirectory, "Data", "Lecturer.json")));
//List<Lecturer> lstLecturer = new List<Lecturer>();
Lecturers lecturers = new Lecturers();
lecturers.TotalCount = 70;
lecturers.PageNumber = 1;
lecturers.PageSize = 20;
lecturers.Rows = lstLecturer;
apiReturnValue.IsSuccess = true;
apiReturnValue.Object = lecturers;
}
catch (Exception e)
{
apiReturnValue.IsSuccess = false;
}
return apiReturnValue;
}));
}
public async Task <ApiReturnValue <Subjects> > DeleteSubject(string subjectId)
{
ApiReturnValue <Subjects> apiReturnValue = new ApiReturnValue <Subjects>();
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ApiReturnValue <Lecturers> > DeleteLecturer(string lecturerId)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ApiReturnValue <Lecturers> > CreateLecturer(Lecturer lecturer)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
Lecturers.Add(lecturer);
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ApiReturnValue <Lecturers> > GetLecturer(string lecturerName)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
Lecturer lecturer = new Lecturer();
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ApiReturnValue <Subjects> > CreateSampleSubjects()
{
return(await Task.Run(() =>
{
List <Subject> lstSubjects = new List <Subject>()
{
new Subject {
SubjectCode = "IT-1000000001", SubjectDescription = "The Complete 2021 Web Development Bootcamp"
},
new Subject {
SubjectCode = "DE-3600000002", SubjectDescription = "Complete Blender Creator: Learn 3D Modelling for Beginners"
},
new Subject {
SubjectCode = "IT-1000000003", SubjectDescription = "Python for Data Science and Machine Learning"
},
new Subject {
SubjectCode = "IT-1000000020", SubjectDescription = "React - Guide (incl Hooks, React Router, Redux)"
},
new Subject {
SubjectCode = "DE-3600000032", SubjectDescription = "Advanced 3D Modelling"
},
new Subject {
SubjectCode = "DR-5000000003", SubjectDescription = "The Art & Science of Drawing / BASIC SKILLS"
},
new Subject {
SubjectCode = "IT-1000000301", SubjectDescription = "The Complete 2021 Web Development Bootcamp"
},
new Subject {
SubjectCode = "DR-5100000002", SubjectDescription = "Environment Art School: Complete Perspective Drawing Course"
},
new Subject {
SubjectCode = "IT-1000000303", SubjectDescription = "Machine Learning "
},
};
System.IO.File.WriteAllText(Path.Combine(CurrentDirectory, "Data", "Subject.json"), JsonConvert.SerializeObject(lstSubjects));
ApiReturnValue <Subjects> apiReturnValue = new ApiReturnValue <Subjects>()
{
IsSuccess = true,
Error = new ApiReturnError()
{
displayMessage = "", errorMessage = ""
},
Object = new Subjects()
{
TotalCount = 9, PageNumber = 1, PageSize = 100
}
};
return apiReturnValue;
}));
}
public async Task <ApiReturnValue <Lecturers> > GetLecturer(int pageSize = 100, int pageNumber = 1)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
///
////
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ActionResult <ApiReturnValue <Lecturers> > > GetLecturer(int pageSize = 100, int pageNumber = 1)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
apiReturnValue = await LecturerService.GetLecturer(pageSize, pageNumber);
}
catch (Exception e)
{
apiReturnValue = null;
Log.Error(e);
}
return(apiReturnValue);
}
public async Task <ActionResult <ApiReturnValue <Lecturers> > > GetLecturer()
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
apiReturnValue = await LecturerService.GetLecturer(0, 0);
}
catch (Exception e)
{
apiReturnValue = null;
Log.Error(e);
}
return(apiReturnValue);
}
public async Task <ActionResult <ApiReturnValue <Lecturers> > > DeleteLecturer(string id)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
/*
* var tblLecturer = await _context.tblLecturer.FindAsync(id);
*
* if (tblLecturer == null)
* {
* return NotFound();
* }
*/
Lecturer lecturer = new Lecturer();
return(apiReturnValue);
}
public async Task <ActionResult <ApiReturnValue <Lecturers> > > PostLecturer(Lecturer lecturer)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
try
{
apiReturnValue = await LecturerService.CreateLecturer(lecturer);
apiReturnValue.IsSuccess = true;
}
catch (Exception)
{
apiReturnValue.IsSuccess = false;
}
return(apiReturnValue);
}
public async Task <ApiReturnValue <TimeTables> > GetLecturerTimeTables(string lecturerName, string dayOfWeeks)
{
var strDayOfWeeks = "(" + dayOfWeeks + ")";
//var connectionString = _conStrings.TafeSaItStudiesContext;
List <TimeTable> lstTable = new List <TimeTable>()
{
new Models.TimeTable()
{
Crn = "CR10001", DayOfWeek = "Mon", ClassTime = "11:00-13:00", Campus = "City Campus", SubjectCode = "IT-1000000001", SubjectDesc = "The Complete 2021 Web Development Bootcamp",
ClassRoom = "C101", LecturerId = "LE-97898", LecturerName = "Lee", StartTerm = "1", EndTerm = "4"
},
new Models.TimeTable()
{
Crn = "CR10002", DayOfWeek = "Tue", ClassTime = "14:00-16:00", Campus = "City East Campus", SubjectCode = "DE-3600000002", SubjectDesc = "Complete Blender Creator: Learn 3D Modelling for Beginners",
ClassRoom = "C305", LecturerId = "LE-20098", LecturerName = "Williams", StartTerm = "1", EndTerm = "4"
},
new Models.TimeTable()
{
Crn = "CR10003", DayOfWeek = "Wed", ClassTime = "10:00-12:00", Campus = "City Campus", SubjectCode = "IT-1000000003", SubjectDesc = "Python for Data Science and Machine Learning",
ClassRoom = "A205", LecturerId = "LE-30989", LecturerName = "Ted", StartTerm = "1", EndTerm = "4"
},
};
lstTable = (from s in lstTable
where s.LecturerName.ToLower().Contains(lecturerName.ToLower()) &&
s.DayOfWeek.ToLower().Contains(dayOfWeeks.ToLower())
select s).ToList();
ApiReturnValue <TimeTables> apiReturnValue = new ApiReturnValue <TimeTables>()
{
IsSuccess = true,
Error = new ApiReturnError()
{
displayMessage = "", errorMessage = ""
},
Object = new TimeTables()
{
TotalCount = lstTable.Count(), PageNumber = 1, PageSize = 100, Rows = lstTable
}
};
return(apiReturnValue);
}
public async Task <ApiReturnValue <Subjects> > UpdateSubject(string subjectId, Subject subject)
{
return(await Task.Run(() =>
{
var returnValue = new ApiReturnValue <Subjects>()
{
IsSuccess = true,
Error = new ApiReturnError()
{
displayMessage = "", errorMessage = ""
},
Object = new Subjects()
{
TotalCount = ListSubject.Count(), PageNumber = 1, PageSize = 100, Rows = ListSubject
}
};
return returnValue;
}));
}
public async Task <ApiReturnValue <Lecturers> > GetLecturer(string lecturerName)
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
var lstLecturer = await GetLecturerById(lecturerName);
Lecturers lecturers = new Lecturers();
lecturers.TotalCount = 70;
lecturers.PageNumber = 1;
lecturers.PageSize = 20;
lecturers.Rows = lstLecturer;
apiReturnValue.IsSuccess = true;
apiReturnValue.Object = lecturers;
await Task.Delay(1000);
return(apiReturnValue);
}
public async Task <ApiReturnValue <Lecturers> > CreateSampleLecturer()
{
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>();
List <Lecturer> sampleLecturers = new List <Lecturer>()
{
new Lecturer {
LecturerId = "100001", GivenName = "Thomas", LastName = "Williams", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100002", GivenName = "Robert", LastName = "Brown", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100003", GivenName = "John", LastName = "Wilson", EmailAddress = "*****@*****.**"
},
};
await Task.Delay(1000);
Lecturers = sampleLecturers;
return(apiReturnValue);
}
public async Task <ApiReturnValue <TimeTables> > GetTimeTables(string campusId, string roomId, string dayOfWeek)
{
//string connectionString = _conStrings.TafeBuddyDbContext;
List <TimeTable> tTables = new List <TimeTable>();
using (SqlConnection con = new SqlConnection(DataConnectionSettings.ConnectionString))
{
con.Open();
string sqlTimeTable = "select a.crn as CRN, e.DayShortName as DayOfWeek, "
+ " concat(substring(convert(varchar, b.StartTime, 8),4,5) ,'-', substring(convert(varchar, b.EndTime, 8),4,5)) as ClassTime , "
+ " b.CampusCode as Campus, a.SubjectCode as SubjectCode , d.SubjectDescription as SubjectDesc, b.room as ClassRoom, "
+ " c.LecturerID as LecturerId, CONCAT(c.givenName, ' ', c.LastName) AS LecturerName"
+ " from crn_detail a "
+ " join crn_session_timetable b on a.CRN = b.CRN and a.TermYearStart = b.TermYearStart and a.TermCodeStart = b.TermCodeStart"
+ " Join lecturer c on a.LECTURERID = c.LecturerID "
+ " join subject d on a.SubjectCode = d.SubjectCode "
+ " join day_of_week e on b.DayCode = e.DayCode "
+ " AND b.CampusCode like '" + campusId + "%'"
+ " AND b.room = '" + roomId + "'"
+ " AND e.DayShortName like '%" + dayOfWeek + "%'"
+ " order by ClassTime";
using (SqlCommand command = new SqlCommand(sqlTimeTable, con))
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
TimeTable table = new TimeTable();
table.Crn = (string)reader["CRN"];
table.DayOfWeek = (string)reader["DayOfWeek"];
table.ClassTime = (string)reader["ClassTime"];
table.Campus = (string)reader["Campus"];
table.SubjectCode = (string)reader["SubjectCode"];
table.SubjectDesc = (string)reader["SubjectDesc"];
table.ClassRoom = (string)reader["ClassRoom"];
table.LecturerId = (string)reader["LecturerId"];
table.LecturerName = (string)reader["LecturerName"];
tTables.Add(table);
}
}
reader.Close();
}
con.Close();
}
ApiReturnValue <TimeTables> apiReturnValue = new ApiReturnValue <TimeTables>();
apiReturnValue.IsSuccess = true;
apiReturnValue.Error = null;
apiReturnValue.Object.PageSize = 100;
apiReturnValue.Object.PageNumber = 1;
apiReturnValue.Object.TotalCount = tTables.Count();
apiReturnValue.Object.Rows = tTables;
return(apiReturnValue);
}
public async Task <ApiReturnValue <TimeTables> > GetLecturerTimeTables(string lecturerName, string dayOfWeeks)
{
var strDayOfWeeks = "(" + dayOfWeeks + ")";
//var connectionString = _conStrings.TafeSaItStudiesContext;
var lstTimeTables = new List <TimeTable>();
using (var db = new AppDb(DataConnectionSettings.ConnectionString))
{
db.Connection.Open();
var sqlTimeTable =
"select a.crn as CRN, e.DayShortName as DayOfWeek, "
+ " concat(b.StartTime, '-', b.EndTime) as ClassTime ,"
+ " b.CampusCode as Campus, a.SubjectCode , d.SubjectDescription as SubjectDesc, b.room as ClassRoom, "
+ " c.LecturerID as LecturerId , concat(c.givenName, ' ', c.LastName) AS LecturerName, "
+ " concat( a.TermYearStart, '-T', a.TermCodeStart) as StartTerm , concat(a.TermYearEnd, '-T', a.TermCodeEnd) as EndTerm "
+ " from crn_detail a, crn_session_timetable b, lecturer c, subject d, day_of_week e "
+ " where a.crn = b.crn "
+ " and a.lecturerid = c.lecturerid "
+ " and a.subjectcode = d.subjectcode "
+ " and b.daycode = e.daycode "
+ " and year(now()) between a.TermYearStart and a.TermYearEnd "
//+ " and(select termcode from term_datetime where now() between startdate and enddate) between a.termcodestart and a.termcodeend "
+ " and b.daycode in " + strDayOfWeeks
+ " and concat(c.givenName, ' ', c.LastName) like '%" + lecturerName + "%'"
+ " order by b.DayCode, b.starttime; ";
using (MySqlCommand command = new MySqlCommand(sqlTimeTable, db.Connection))
using (MySqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var table = new TimeTable
{
Crn = (string)reader["CRN"],
DayOfWeek = (string)reader["DayOfWeek"],
ClassTime = (string)reader["ClassTime"],
Campus = (string)reader["Campus"],
SubjectCode = (string)reader["SubjectCode"],
SubjectDesc = (string)reader["SubjectDesc"],
ClassRoom = (string)reader["ClassRoom"],
LecturerId = (string)reader["LecturerId"],
LecturerName = (string)reader["LecturerName"],
StartTerm = (string)reader["StartTerm"],
EndTerm = (string)reader["EndTerm"]
};
lstTimeTables.Add(table);
}
}
reader.Close();
}
db.Connection.Close();
}
ApiReturnValue <TimeTables> apiReturnValue = new ApiReturnValue <TimeTables>()
{
IsSuccess = true,
Error = new ApiReturnError()
{
displayMessage = "", errorMessage = ""
},
Object = new TimeTables()
{
TotalCount = lstTimeTables.Count(), PageNumber = 1, PageSize = 100, Rows = lstTimeTables
}
};
return(apiReturnValue);
}
public async Task <ApiReturnValue <Lecturers> > CreateSampleLecturer()
{
List <Lecturer> lstLecturer = new List <Lecturer>()
{
new Lecturer {
LecturerId = "100001", GivenName = "James", LastName = "Oliver", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100002", GivenName = "Mary", LastName = "Amelia", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100003", GivenName = "John", LastName = "Jack", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100004", GivenName = "Patricia", LastName = "Olivia", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100005", GivenName = "Robert", LastName = "Harry", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100006", GivenName = "Jennifer", LastName = "Isla", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100007", GivenName = "Michael", LastName = "Jacob", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100008", GivenName = "Linda", LastName = "Emily", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100009", GivenName = "William", LastName = "Charlie", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100010", GivenName = "Elizabeth", LastName = "Poppy", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100011", GivenName = "David", LastName = "Thomas", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100012", GivenName = "Barbara", LastName = "Ava", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100013", GivenName = "Richard", LastName = "George", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100013", GivenName = "Susan", LastName = "Isabella", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100014", GivenName = "Joseph", LastName = "Oscar", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100015", GivenName = "Jessica", LastName = "Jessica", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100016", GivenName = "Thomas", LastName = "James", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100017", GivenName = "Sarah", LastName = "Liny", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100018", GivenName = "Charles", LastName = "William", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100015", GivenName = "Karen", LastName = "Smith", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100016", GivenName = "Christopher", LastName = "Jones", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100017", GivenName = "Nancy", LastName = "Davis", EmailAddress = "*****@*****.**"
},
new Lecturer {
LecturerId = "100018", GivenName = "Daniel", LastName = "Taylor", EmailAddress = "*****@*****.**"
},
};
await Task.Delay(1000);
System.IO.File.WriteAllText(Path.Combine(CurrentDirectory, "Data", "Lecturer.json"), JsonConvert.SerializeObject(lstLecturer));
ApiReturnValue <Lecturers> apiReturnValue = new ApiReturnValue <Lecturers>()
{
IsSuccess = true,
Error = new ApiReturnError()
{
displayMessage = "", errorMessage = ""
},
Object = new Lecturers()
{
TotalCount = 24, PageNumber = 1, PageSize = 100
}
};
return(apiReturnValue);
}
