/// <summary>
/// Adds the grade item grade.
/// </summary>
/// <param name="assignmentId">The assignment identifier.</param>
/// <param name="studentId">The student identifier.</param>
public async Task AddGradeItemGrade(int assignmentId, int studentId)
{
const string sqlStatementGradeItemGrade =
"INSERT INTO Course_student_grade_item_grade(`aid`, `sid`, `grade`, `graded`) VALUES(@aid, @sid, @grade, @graded);";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var createStudentGradeItemCommand = db.CreateCommand();
createStudentGradeItemCommand.Connection = db;
createStudentGradeItemCommand.Transaction = transaction;
createStudentGradeItemCommand.CommandText = sqlStatementGradeItemGrade;
createStudentGradeItemCommand.Parameters.Add("@aid", SqliteType.Integer, 11);
createStudentGradeItemCommand.Parameters["@aid"].Value = assignmentId;
createStudentGradeItemCommand.Parameters.Add("@sid", SqliteType.Integer, 11);
createStudentGradeItemCommand.Parameters["@sid"].Value = studentId;
createStudentGradeItemCommand.Parameters.Add("@grade", SqliteType.Integer, 4);
createStudentGradeItemCommand.Parameters["@grade"].Value = 0;
createStudentGradeItemCommand.Parameters.Add("@graded", SqliteType.Integer, 1);
createStudentGradeItemCommand.Parameters["@graded"].Value = 0;
createStudentGradeItemCommand.ExecuteNonQuery();
transaction.Commit();
}
var dbQuery = "INSERT INTO `Course_student_grade_item_grade`(`aid`, `sid`, `grade`, `graded`) VALUES(" + assignmentId + "," + studentId + ", 0, 0);";
await ChangelogModifier.AppendChangeLog(dbQuery);
}
/// <summary>
/// Adds the rubric item.
/// </summary>
/// <param name="courseId">The course identifier.</param>
/// <param name="type">The type.</param>
/// <param name="grade">The grade.</param>
public async void AddRubricItem(int courseId, string type, double grade)
{
const string sqlStatementAddRubricItem =
"INSERT INTO `Course_rubric` (`cid`, `assignment_type`, `grade_percentage`) VALUES (@cid, @type, @grade);";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var addRubricCommand = db.CreateCommand();
addRubricCommand.Connection = db;
addRubricCommand.Transaction = transaction;
try
{
addRubricCommand.CommandText = sqlStatementAddRubricItem;
addRubricCommand.Parameters.Add("@cid", SqliteType.Integer, 11);
addRubricCommand.Parameters["@cid"].Value = courseId;
addRubricCommand.Parameters.Add("@type", SqliteType.Text, 60);
addRubricCommand.Parameters["@type"].Value = type;
addRubricCommand.Parameters.Add("@grade", SqliteType.Real);
addRubricCommand.Parameters["@grade"].Value = grade;
addRubricCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
var sqlStatementAdd = "INSERT INTO `Course_rubric` (`rid`, `cid`, `assignment_type`, `grade_percentage`) VALUES" +
" (" + 0 + "," + courseId + ",'" + type + "'," + grade + ");";
await ChangelogModifier.AppendChangeLog(sqlStatementAdd);
}
/// <summary>
/// Edits the rubric item.
/// </summary>
/// <param name="rubricId">The rubric identifier.</param>
/// <param name="type">The type.</param>
/// <param name="grade">The grade.</param>
public async void EditRubricItem(int rubricId, string type, double grade)
{
const string sqlStatementUpdateById =
"UPDATE `Course_rubric` SET `assignment_type`= @type, `grade_percentage` = @grade WHERE rid = @id";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var editRubricCommand = db.CreateCommand();
editRubricCommand.Connection = db;
editRubricCommand.Transaction = transaction;
try
{
editRubricCommand.CommandText = sqlStatementUpdateById;
editRubricCommand.Parameters.Add("@id", SqliteType.Integer, 11);
editRubricCommand.Parameters["@id"].Value = rubricId;
editRubricCommand.Parameters.Add("@type", SqliteType.Text, 60);
editRubricCommand.Parameters["@type"].Value = type;
editRubricCommand.Parameters.Add("@grade", SqliteType.Real);
editRubricCommand.Parameters["@grade"].Value = grade;
editRubricCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
var sqlStatementUpdate = "UPDATE `Course_rubric` SET `assignment_type`= '" + type +
"',`grade_percentage` = " + grade + " WHERE rid = " + rubricId;
await ChangelogModifier.AppendChangeLog(sqlStatementUpdate);
}
/// <summary>
/// Writes the new grade item to local database.
/// </summary>
/// <param name="gradeItem">The grade item.</param>
public async void WriteNewGradeItemsToLocalDb(GradeItem gradeItem)
{
CourseRepository courseRepo = new CourseRepository();
var sqlInsert = "INSERT INTO `Course_grade_item`(`cid`, `name`, `description`, `grade_type`, `max_grade`)"
+ " VALUES (@cid, @name, @description, @grade_type, @max_grade);";
int rowId = 0;
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var createGradeItemCommand = db.CreateCommand();
createGradeItemCommand.Connection = db;
createGradeItemCommand.Transaction = transaction;
try
{
createGradeItemCommand.CommandText = sqlInsert;
createGradeItemCommand.Parameters.Add("@cid", SqliteType.Integer, 11);
createGradeItemCommand.Parameters["@cid"].Value = gradeItem.CourseId;
createGradeItemCommand.Parameters.Add("@name", SqliteType.Text, 60);
createGradeItemCommand.Parameters["@name"].Value = gradeItem.Name;
createGradeItemCommand.Parameters.Add("@description", SqliteType.Text, 600);
createGradeItemCommand.Parameters["@description"].Value = gradeItem.Description;
createGradeItemCommand.Parameters.Add("@grade_type", SqliteType.Text, 60);
createGradeItemCommand.Parameters["@grade_type"].Value = gradeItem.Type;
createGradeItemCommand.Parameters.Add("@max_grade", SqliteType.Integer, 11);
createGradeItemCommand.Parameters["@max_grade"].Value = gradeItem.MaxGrade;
createGradeItemCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
var cmd = db.CreateCommand();
cmd.CommandText = "SELECT last_insert_rowid()";
object i = cmd.ExecuteScalar();
cmd.CommandText = "SELECT `aid` FROM `Course_grade_item` WHERE rowid=" + i.ToString();
i = cmd.ExecuteScalar();
rowId = (int)(long)i;
}
var sqlchange = "INSERT INTO `Course_grade_item`(`cid`, `name`, `description`, `grade_type`, `max_grade`)"
+ " VALUES (" + gradeItem.CourseId + ",'" + gradeItem.Name + "','" + gradeItem.Description
+ "','" + gradeItem.Type + "'," + gradeItem.MaxGrade + ");";
await ChangelogModifier.AppendChangeLog(sqlchange);
var students = courseRepo.GetAllStudentsByCourseLocalDb(gradeItem.CourseId);
foreach (var student in students)
{
await this.AddGradeItemGrade(rowId, student.Id);
}
}
/// <summary>
/// Updates the student course grade.
/// </summary>
/// <param name="studentId">The student identifier.</param>
/// <param name="courseId">The course identifier.</param>
public async void UpdateStudentCourseGrade(int studentId, int courseId)
{
List <GradeItemGrade> allGrades = (List <GradeItemGrade>) this.GetAllGradeItemGradeByCourse(courseId);
var studentGrades = from gi in allGrades
where gi.studentID == studentId
select gi;
double totalstudentgrade = 0;
var rubricRepo = new RubricRepository();
var types = rubricRepo.GetAllRubricItems(courseId);
List <GradeItemGrade> gradeList = studentGrades.ToList <GradeItemGrade>();
foreach (var type in types)
{
totalstudentgrade += this.CalculateTypeGrade(gradeList, type);
}
const string sqlStatementUpdateByIds =
"UPDATE `Course_student_participant_grade` SET `cid`= @cid, `sid` = @sid, `grade` = @grade WHERE cid = @cid AND sid = @sid ";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var editGradeItemGradeCommand = db.CreateCommand();
editGradeItemGradeCommand.Connection = db;
editGradeItemGradeCommand.Transaction = transaction;
try
{
editGradeItemGradeCommand.CommandText = sqlStatementUpdateByIds;
editGradeItemGradeCommand.Parameters.Add("@grade", SqliteType.Real);
editGradeItemGradeCommand.Parameters["@grade"].Value = totalstudentgrade;
editGradeItemGradeCommand.Parameters.Add("@cid", SqliteType.Integer, 11);
editGradeItemGradeCommand.Parameters["@cid"].Value = courseId;
editGradeItemGradeCommand.Parameters.Add("@sid", SqliteType.Integer, 11);
editGradeItemGradeCommand.Parameters["@sid"].Value = studentId;
editGradeItemGradeCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
var sqlStatementUpdate =
"UPDATE `Course_student_participant_grade` SET `cid`=" + courseId + ", `sid` = " +
+studentId + ", `grade` = " + totalstudentgrade + " WHERE cid = " + courseId + " AND sid = " + studentId + " ";
await ChangelogModifier.AppendChangeLog(sqlStatementUpdate);
}
/// <summary>
/// Deletes the rubric item.
/// </summary>
/// <param name="rubricId">The rubric identifier.</param>
public async void DeleteRubricItem(int rubricId)
{
const string sqlStatementDeleteById =
"DELETE FROM `Course_rubric` WHERE rid = @id";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var query = db.CreateCommand();
query.CommandText = sqlStatementDeleteById;
query.Parameters.Add("@id", SqliteType.Integer, 11);
query.Parameters["@id"].Value = rubricId;
query.ExecuteNonQuery();
this.dbConnection.Close();
}
var dbStatementDeleteById =
"DELETE FROM `Course_rubric` WHERE rid = " + rubricId + " ;";
await ChangelogModifier.AppendChangeLog(dbStatementDeleteById);
}
/// <summary>
/// Deletes the grade item from local database.
/// </summary>
/// <param name="gradeItem">The grade item.</param>
public async void DeleteGradeItemFromLocalDb(GradeItem gradeItem)
{
const string sqlStatementDeleteById =
"DELETE FROM `Course_grade_item` WHERE aid = @id";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var query = db.CreateCommand();
query.CommandText = sqlStatementDeleteById;
query.Parameters.Add("@id", SqliteType.Integer, 11);
query.Parameters["@id"].Value = gradeItem.AssignmentId;
query.ExecuteNonQuery();
db.Close();
}
var dbStatementDeleteById =
"DELETE FROM `Course_grade_item` WHERE aid = " + gradeItem.AssignmentId + " ;";
await ChangelogModifier.AppendChangeLog(dbStatementDeleteById);
}
/// <summary>
/// Updates the grade item grade.
/// </summary>
/// <param name="assignmentId">The assignment identifier.</param>
/// <param name="studentId">The student identifier.</param>
/// <param name="grade">The grade.</param>
public async void UpdateGradeItemGrade(int assignmentId, int studentId, int grade)
{
const string sqlStatementUpdateByIds =
"UPDATE `Course_student_grade_item_grade` SET `grade`= @grade, `graded` = @graded WHERE aid = @aid AND sid = @sid";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var editGradeItemGradeCommand = db.CreateCommand();
editGradeItemGradeCommand.Connection = db;
editGradeItemGradeCommand.Transaction = transaction;
try
{
editGradeItemGradeCommand.CommandText = sqlStatementUpdateByIds;
editGradeItemGradeCommand.Parameters.Add("@grade", SqliteType.Integer, 5);
editGradeItemGradeCommand.Parameters["@grade"].Value = grade;
editGradeItemGradeCommand.Parameters.Add("@aid", SqliteType.Integer, 11);
editGradeItemGradeCommand.Parameters["@aid"].Value = assignmentId;
editGradeItemGradeCommand.Parameters.Add("@sid", SqliteType.Integer, 11);
editGradeItemGradeCommand.Parameters["@sid"].Value = studentId;
editGradeItemGradeCommand.Parameters.Add("@graded", SqliteType.Integer, 1);
editGradeItemGradeCommand.Parameters["@graded"].Value = 1;
editGradeItemGradeCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
var sqlUpdateStatement = "UPDATE `Course_student_grade_item_grade` SET " +
"`grade`= " + grade + ", `graded` = 1 WHERE aid = " + assignmentId + " AND sid = " + studentId;
await ChangelogModifier.AppendChangeLog(sqlUpdateStatement);
var course = this.GetGradeItem(assignmentId).CourseId;
this.UpdateStudentCourseGrade(studentId, course);
}
/// <summary>
/// Edits the grade item in local database.
/// </summary>
/// <param name="gradeItem">The grade item.</param>
public async void EditGradeItemInLocalDb(GradeItem gradeItem)
{
const string sqlStatementUpdateById =
"UPDATE `Course_grade_item` SET `name`= @name, `description` = @description, `grade_type` = @type, `max_grade` = @grade WHERE aid = @aid";
using (SqliteConnection db = DataAccess.LocalConnection)
{
db.Open();
var transaction = db.BeginTransaction();
var editGradeItemCommand = db.CreateCommand();
editGradeItemCommand.Connection = db;
editGradeItemCommand.Transaction = transaction;
try
{
editGradeItemCommand.CommandText = sqlStatementUpdateById;
editGradeItemCommand.Parameters.Add("@aid", SqliteType.Integer, 11);
editGradeItemCommand.Parameters["@aid"].Value = gradeItem.AssignmentId;
editGradeItemCommand.Parameters.Add("@name", SqliteType.Text, 60);
editGradeItemCommand.Parameters["@name"].Value = gradeItem.Name;
editGradeItemCommand.Parameters.Add("@description", SqliteType.Text, 600);
editGradeItemCommand.Parameters["@description"].Value = gradeItem.Description;
editGradeItemCommand.Parameters.Add("@type", SqliteType.Text, 60);
editGradeItemCommand.Parameters["@type"].Value = gradeItem.Type;
editGradeItemCommand.Parameters.Add("@grade", SqliteType.Integer, 11);
editGradeItemCommand.Parameters["@grade"].Value = gradeItem.MaxGrade;
editGradeItemCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
var sqlStatementUpdate = "UPDATE `Course_grade_item` SET `name`= '" + gradeItem.Name +
"', `description` = '" + gradeItem.Description + "', `grade_type` = '" + gradeItem.Type + "', " +
"`max_grade` = " + gradeItem.MaxGrade + " WHERE aid = " + gradeItem.AssignmentId + ";";
await ChangelogModifier.AppendChangeLog(sqlStatementUpdate);
}