public bool Add(TrainingAccess training)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
cmd = new SqlCommand("AddTraining");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter trainingName = cmd.Parameters.Add("@trainingName", SqlDbType.VarChar, 50);
trainingName.Direction = ParameterDirection.Input;
SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 512);
description.Direction = ParameterDirection.Input;
SqlParameter type = cmd.Parameters.Add("@type", SqlDbType.VarChar, 35);
type.Direction = ParameterDirection.Input;
SqlParameter moduleId = cmd.Parameters.Add("@moduleId", SqlDbType.Int);
moduleId.Direction = ParameterDirection.Input;
if (training.TrainingName != null)
{
trainingName.Value = training.TrainingName;
}
if (training.Description != null)
{
description.Value = training.Description;
}
if (training.Type != null)
{
type.Value = training.Type;
}
if (training.ModuleId != default(int))
{
moduleId.Value = training.ModuleId;
}
db.changeData(cmd);
db.Close();
if (Int32.Parse(RetVal.Value.ToString()) == 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="moduleId"></param>
/// <returns></returns>
public ModuleAccessDetail GetModuleDetailData(int moduleId)
{
ModuleAccessDetail moduleDetail = new ModuleAccessDetail();
List <TrainingAccess> trainingList = new List <TrainingAccess>();
List <QuizAccess> quizList = new List <QuizAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
//query training table
cmd.CommandText = "SELECT trainingId, trainingName, description, type FROM TrainingModule WHERE moduleId = @moduleId";
cmd.Parameters.AddWithValue("@moduleId", moduleId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
TrainingAccess training = new TrainingAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
training.TrainingId = 0;
}
else
{
training.TrainingId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
training.TrainingName = null;
}
else
{
training.TrainingName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
training.Description = null;
}
else
{
training.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
training.Type = null;
}
else
{
training.Type = Convert.ToString(dr[3]);
}
trainingList.Add(training);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
moduleDetail.Training = trainingList;
//query quiz table
cmd.CommandText = "SELECT quizId, quizName, description, passingGrade FROM Quiz WHERE moduleId = @moduleIdq";
cmd.Parameters.AddWithValue("@moduleIdq", moduleId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
QuizAccess quiz = new QuizAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
quiz.QuizId = 0;
}
else
{
quiz.QuizId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
quiz.QuizName = null;
}
else
{
quiz.QuizName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
quiz.Description = null;
}
else
{
quiz.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
quiz.PassingGrade = 0;
}
else
{
quiz.PassingGrade = Convert.ToInt32(dr[3]);
}
quizList.Add(quiz);
}
}
moduleDetail.Quiz = quizList;
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(moduleDetail);
}
public TrainingTransfer GeTrainingDetailData(int trainingId)
{
TrainingTransfer trainingDetail = new TrainingTransfer();
TrainingAccess training = new TrainingAccess();
List <DocumentAccess> documentList = new List <DocumentAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
//select data from TrainingModule table according trainingId
cmd.CommandText = "select * from TrainingModule WHERE trainingId = @trainingId;";
cmd.Parameters.AddWithValue("@trainingId", trainingId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
if (System.DBNull.Value.Equals(dr[0]))
{
training.TrainingId = 0;
}
else
{
training.TrainingId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
training.TrainingName = null;
}
else
{
training.TrainingName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
training.Description = null;
}
else
{
training.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
training.Type = null;
}
else
{
training.Type = Convert.ToString(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
training.ModuleId = 0;
}
else
{
training.ModuleId = Convert.ToInt32(dr[4]);
}
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
trainingDetail.Training = training;
//select document list from database
cmd.CommandText = "SELECT documentId, name, type, uploadDate, description, size, trainingId FROM Document WHERE trainingId = @trainingDetailId";
cmd.Parameters.AddWithValue("@trainingDetailId", trainingId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
DocumentAccess document = new DocumentAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
document.DocumentId = 0;
}
else
{
document.DocumentId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
document.DocumentName = null;
}
else
{
document.DocumentName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
document.Type = null;
}
else
{
document.Type = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
document.UploadDate = default(DateTime);
}
else
{
document.UploadDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
document.Description = null;
}
else
{
document.Description = Convert.ToString(dr[4]);
}
if (System.DBNull.Value.Equals(dr[5]))
{
document.Size = 0;
}
else
{
document.Size = Convert.ToInt32(dr[5]);
}
if (System.DBNull.Value.Equals(dr[6]))
{
document.TrainingId = 0;
}
else
{
document.TrainingId = Convert.ToInt32(dr[6]);
}
documentList.Add(document);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
trainingDetail.DocumentList = documentList;
return(trainingDetail);
}
/// <summary>
///
/// </summary>
/// <param name="moduleId"></param>
/// <returns></returns>
public List <TrainingAccess> GetTrainingDataForModule(int moduleId)
{
List <TrainingAccess> trainingList = new List <TrainingAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM TrainingModule WHERE moduleId = @moduleId;";
cmd.Parameters.Add("@moduleId", SqlDbType.Int).Value = moduleId;
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
TrainingAccess training = new TrainingAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
training.TrainingId = 0;
}
else
{
training.TrainingId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
training.TrainingName = null;
}
else
{
training.TrainingName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
training.Description = null;
}
else
{
training.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
training.Type = null;
}
else
{
training.Type = Convert.ToString(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
training.ModuleId = 0;
}
else
{
training.ModuleId = Convert.ToInt32(dr[4]);
}
trainingList.Add(training);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(trainingList);
}
/// <summary>
///
/// </summary>
/// <param name="quizRecord"></param>
/// <returns></returns>
public bool SaveQuizRecord(QuizRecordAccess quizRecord)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
DateTime today;
SqlDataReader dr;
int grade = 0;
bool isInsert = false;
//update data in Quiz table
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
//find whether there is a record in Quiz_record table
cmd.CommandText = "SELECT highestGrade FROM Quiz_record WHERE quizId = @quizId AND accountId = @accountId;";
cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId);
cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
TrainingAccess training = new TrainingAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
grade = 0;
isInsert = true;
}
else
{
grade = Convert.ToInt32(dr[0]);
}
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
if (grade <= quizRecord.HighestGrade)
{
grade = quizRecord.HighestGrade;
today = quizRecord.HighGradeTime;
}
else
{
today = DateTime.Now;
}
if (isInsert == true)
{
//insert a new record in Quiz_record table
cmd.CommandText = "INSERT INTO Quiz_record(accountId,quizId,attendTimes,highestGrade,highGradeTime)VALUES(@accountId, @quizId, @attendTimes, @highestGrade, @highGradeTime);";
cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId);
cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId);
cmd.Parameters.AddWithValue("@attendTimes", today);
cmd.Parameters.AddWithValue("@highestGrade", grade);
cmd.Parameters.AddWithValue("@highGradeTime", today);
}
else if (grade != quizRecord.HighestGrade)
{
//update data in Quiz_record table
cmd.CommandText = "UPDATE Quiz_record SET highestGrade = @highestGrade,highGradeTime = @highGradeTime WHERE quizId = @quizId AND accountId = @accountId ;";
cmd.Parameters.AddWithValue("@highestGrade", grade);
cmd.Parameters.AddWithValue("@highGradeTime", today);
cmd.Parameters.AddWithValue("@quizId", quizRecord.QuizId);
cmd.Parameters.AddWithValue("@accountId", quizRecord.AccountId);
}
db.changeData(cmd);
db.Close();
return(true);
}