public CourseObject ViewCourse(int courseId)
{
try
{
string sqlSelect = $"SELECT [courseId], [courseName] FROM [dbo].[course_tbl] WHERE courseiD = @CourseID";
parameters = new List <ParameterList>();
parameters.Add(new ParameterList()
{
Key = "@CourseID", Value = courseId
});
adminDataAccess = new DataAccess();
CourseObject courseObj = new CourseObject();
Task <DataTable> task = Task.Run(() => adminDataAccess.GetDataAsync(sqlSelect, parameters));
courseObj.CourseId = Convert.ToInt32(task.Result.Columns["courseId"]);
courseObj.CourseName = task.Result.Columns["courseName"].ToString();
if (courseObj == null)
{
throw new NullReferenceException();
}
else
{
return(courseObj);
}
}
catch (Exception ex)
{
throw;
}
}
public int DeleteCourse(CourseObject course)
{
try
{
string delQuery = "DELETE FROM [dbo].[course_tbl] WHERE [courseId] = (@CourseId);";
SqlParameter param1 = new SqlParameter("@CourseId", SqlDbType.NVarChar, 8, "courseId");
param1.Value = course.CourseId;
sqlParameters = new List <SqlParameter>();
sqlParameters.Add(param1);
adminDataAccess = new DataAccess();
if (param1.Value == null)
{
throw new MyException();
}
else
{
Task <int> task = Task.Run(() => adminDataAccess.ExecuteCommandAsync(delQuery, CommandType.Text, sqlParameters));
return(task.Result);
}
}
catch (Exception ex)
{
throw;
}
}
public int AddCourse(CourseObject course)
{
try
{
string insQuery = "INSERT INTO [dbo].[course_tbl] (courseId, courseName) VALUES (@CourseId, @CourseName);";
SqlParameter param1, param2;
param1 = new SqlParameter("@CourseId", SqlDbType.NVarChar, 8, "courseId");
param1.Value = course.CourseId;
param2 = new SqlParameter("@CourseName", SqlDbType.NVarChar, 100, "courseName");
param2.Value = course.CourseName;
sqlParameters = new List <SqlParameter>(2);
sqlParameters.Add(param1);
sqlParameters.Add(param2);
adminDataAccess = new DataAccess();
Task <int> task = Task.Run(() => adminDataAccess.ExecuteCommandAsync(insQuery, CommandType.Text, sqlParameters));
return(task.Result);
}
catch (Exception ex)
{
throw;
}
}
public string Post(CourseObject content)
{
try
{
int id = PreferenceHelper.ProcessPreference(content);
return(id.ToString());
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public CourseObject CourseObject(string courseId, string studentId)
{
var course = GetCourse(courseId);
var teacher = course.Teacher;
var schedules = GetCourseSchedule(courseId);
var nextLecture = new Lecture(); //ToDo :: Create function to get the next lecture
var registration = _context.Registrations.Where(x => x.StudentId == studentId)
.Where(x => x.CourseId == courseId).SingleOrDefault(x => x.IsActive());
var validRegistration = registration != null;
var courseObject = new CourseObject()
{
Course = course, Teacher = teacher, Schedules = schedules, NextLecture = nextLecture, ValidRegistration = validRegistration
};
return(courseObject);
}
public void GenerateData()
{
var insertedList = new List <int>();
var coursePrefList = new List <CourseObject>();
//var schools = new List<string>(){ "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16" };
//var majors = new List<string>(){ "1", "2", "3", "4", "6", "7", "8", "9", "10", "11", "12", "13", "14", "16", "17", "18", "19", "20", "21", "22", "23", "24", "26", "27", "28", "29", "30", "31", "32" };
var schools = new List <string>()
{
"1"
};
var majors = new List <string>()
{
"1"
};
foreach (string school in schools)
{
foreach (string major in majors)
{
var courseObj = new CourseObject()
{
school = school,
courses = "5",
credits = "10",
major = major,
quarters = "20",
enrollment = ((int)Constants.EnrollmentType.FullTime).ToString(),
job = ((int)Constants.JobType.Unemployed).ToString(),
summer = "N"
};
coursePrefList.Add(courseObj);
//VaryEnrollment(courseObj, coursePrefList);
//VaryJob(courseObj, coursePrefList);
//VaryCredits(courseObj, coursePrefList);
//VaryMaxCreditsPerQuarter(courseObj, coursePrefList);
GeneratePlan(coursePrefList, insertedList);
}
}
insertedList.Should().NotBeEmpty();
}
private void VaryMaxCreditsPerQuarter(CourseObject courseObj, List <CourseObject> coursePrefList)
{
var quarters = new List <int>()
{
1, 2, 4, 8, 9, 12, 15
};
foreach (var credit in quarters)
{
var newCourseObj = new CourseObject()
{
job = courseObj.job,
school = courseObj.school,
major = courseObj.major,
credits = courseObj.credits,
summer = courseObj.summer,
enrollment = courseObj.enrollment,
quarters = credit.ToString()
};
coursePrefList.Add(newCourseObj);
}
}
private void VaryEnrollment(CourseObject courseObj, List <CourseObject> coursePrefList)
{
var enrollmentTypes = new List <Constants.EnrollmentType>()
{
Constants.EnrollmentType.FullTime, Constants.EnrollmentType.PartTime
};
foreach (var enrollmentType in enrollmentTypes)
{
var newCourseObj = new CourseObject()
{
job = courseObj.job,
school = courseObj.school,
major = courseObj.major,
credits = courseObj.credits,
summer = courseObj.summer,
enrollment = ((int)enrollmentType).ToString(),
quarters = courseObj.quarters
};
coursePrefList.Add(newCourseObj);
}
}
private void VaryJob(CourseObject courseObj, List <CourseObject> coursePrefList)
{
var jobTypes = new List <Constants.JobType>()
{
Constants.JobType.FullTime, Constants.JobType.PartTime, Constants.JobType.Unemployed
};
foreach (var jobType in jobTypes)
{
var newCourseObj = new CourseObject()
{
job = ((int)jobType).ToString(),
school = courseObj.school,
major = courseObj.major,
credits = courseObj.credits,
summer = courseObj.summer,
enrollment = courseObj.enrollment,
quarters = courseObj.quarters
};
coursePrefList.Add(newCourseObj);
}
}
private void VaryCredits(CourseObject courseObj, List <CourseObject> coursePrefList)
{
var credits = new List <int>()
{
5, 10, 15, 3, 1
};
foreach (var credit in credits)
{
var newCourseObj = new CourseObject()
{
job = courseObj.job,
school = courseObj.school,
major = courseObj.major,
credits = credit.ToString(),
summer = courseObj.summer,
enrollment = courseObj.enrollment,
quarters = courseObj.quarters
};
coursePrefList.Add(newCourseObj);
}
}
public int UpdateCourse(CourseObject course)
{
try
{
string updQuery = "$UPDATE [dbo].[course_tbl] SET courseName = @CourseName where courseId = @CourseId";
SqlParameter param1, param2;
param1 = new SqlParameter("@CourseName", SqlDbType.NVarChar, 50, "courseName");
param1.Value = course.CourseName;
param2 = new SqlParameter("@CourseId", SqlDbType.NVarChar, 8, "courseId");
param2.Value = course.CourseId;
if (param2.Value == null)
{
throw new NullReferenceException();
}
else
{
sqlParameters = new List <SqlParameter>();
sqlParameters.Add(param1);
sqlParameters.Add(param2);
adminDataAccess = new DataAccess();
Task <int> task = Task.Run(() => adminDataAccess.ExecuteCommandAsync(updQuery, CommandType.Text, sqlParameters));
return(task.Result);
}
}
catch (Exception ex)
{
throw;
}
}
public string PostSourceAVG(CourseObject courseObject)
{
return(SQLAssisants.Query_manual("score", "SELECT AVG(score) score,(SELECT course_name from course where course_id='" + courseObject.course_id + "') course_name FROM score INNER JOIN users ON (users.username = no) where course_id='" + courseObject.course_id + "' AND class_id='" + courseObject.class_id + "'"));
}
public string PostSourceByClassID(CourseObject courseObject)
{
return(SQLAssisants.Query_manual("score", "SELECT rank() over (order by score desc) rank,name,score,(SELECT course_name from course where course_id='" + courseObject.course_id + "') course_name from score INNER JOIN users ON (users.username = no) where course_id='" + courseObject.course_id + "' AND class_id='" + courseObject.class_id + "'"));
}
public List <CourseObject> GetListCourse(string lang, string langbase)
{
try
{
string connectionString = ("Server=127.0.0.1;uid=root;Database=doulingo_stories;pwd=123456");
string commandText = @"SELECT story.id, story.set_id, story.set_index, story.image, story.image_done, story.name,
story.xp, name_base FROM story
JOIN language l1 ON story.lang = l1.id AND l1.short = " + "\"" + lang + "\""
+ " JOIN language l2 ON story.lang_base = l2.id AND l2.short = " + "\"" + langbase + "\""
+ @" WHERE story.is_active = 0
GROUP BY story.id
ORDER BY set_id, set_index";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
//using (SqlCommand cmd = new SqlCommand(commandText, conn))
//{
conn.Open();
MySqlCommand cmd = new MySqlCommand(commandText, conn);
cmd.ExecuteNonQuery();
using (var result = cmd.ExecuteReader())
{
// do something with resultin
}
List <List <object> > items = new List <List <object> >();
List <CourseObject> li = new List <CourseObject>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = new List <object>();
items.Add(item);
for (int i = 0; i < reader.FieldCount; i++)
{
item.Add(reader[i]);
}
CourseObject l = new CourseObject()
{
id = (int)item[0],
setId = (int?)item[1],
setIndex = (int?)item[2],
image = item[3].ToString(),
imageDone = item[4].ToString(),
name = item[5].ToString(),
xp = (int?)item[6],
name_base = item[7].ToString(),
};
li.Add(l);
}
conn.Close();
// }
return(li);
}
return(null);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(null);
}
