public List<Teacher> GetAllTeachers(int departmentId)
{
try
{
connection.Open();
List<Teacher> teachers = new List<Teacher>();
string query = "SELECT * FROM t_Teacher where departmentId=@id ";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", departmentId);
SqlDataReader teacherReader = command.ExecuteReader();
while (teacherReader.Read())
{
Teacher aTeacher = new Teacher();
aTeacher.TeacherId = teacherReader[0].ToString();
aTeacher.Name = teacherReader[1].ToString();
aTeacher.Address = teacherReader[2].ToString();
aTeacher.Email = teacherReader[3].ToString();
aTeacher.ContactNo = teacherReader[4].ToString();
aTeacher.ADesignation = new Designation();
aTeacher.ADepartment = new Department();
aTeacher.ADesignation.Id = Convert.ToInt16(teacherReader[5].ToString());
aTeacher.AssignCredit = float.Parse(teacherReader[6].ToString());
aTeacher.ADepartment.DepartmentId = Convert.ToInt16(teacherReader[7].ToString());
teachers.Add(aTeacher);
}
return teachers;
}
finally
{
connection.Close();
}
}
public Teacher GetTeacher(Teacher aTeacher)
{
connection.Open();
Teacher teacher = new Teacher();
string query = "select * from t_Teacher where teacherId=@id and departmentId=@deptId";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", aTeacher.TeacherId);
command.Parameters.AddWithValue("@deptId", aTeacher.ADepartment.DepartmentId);
SqlDataReader teacherReader = command.ExecuteReader();
while (teacherReader.Read())
{
teacher.TeacherId = teacherReader[0].ToString();
teacher.Name = teacherReader[1].ToString();
teacher.Address = teacherReader[2].ToString();
teacher.Email = teacherReader[3].ToString();
teacher.ContactNo = teacherReader[4].ToString();
teacher.ADesignation = new Designation();
teacher.ADepartment = new Department();
teacher.ADesignation.Id = Convert.ToInt16(teacherReader[5].ToString());
teacher.AssignCredit = float.Parse(teacherReader[6].ToString());
teacher.ADepartment.DepartmentId = Convert.ToInt16(teacherReader[7].ToString());
}
connection.Close();
return teacher;
}
public string SaveTeacher(Teacher aTeacher)
{
aTeacherGateway = new TeacherGateway();
if (!DoesThisEmailExist(aTeacher))
return aTeacherGateway.SaveTeacher(aTeacher);
else
return "This Email Already Exist";
}
protected void saveButton_Click(object sender, EventArgs e)
{
if(!IsValid)
{
return;
}
try
{
aTeacherManager = new TeacherManager();
Teacher aTeacher = new Teacher();
aTeacher.Name = nameTextBox.Value;
aTeacher.Address = addressTextBox.Value;
aTeacher.Email = emailTextBox.Value;
aTeacher.ContactNo = contactNoTextBox.Value;
aTeacher.AssignCredit = Convert.ToDouble(assignCreditTextBox.Value);
DesignationManager aDesignationManager = new DesignationManager();
aTeacher.ADesignation = aDesignationManager.GetDesignation(Convert.ToInt16(designationDropDownList.Text));
DepartmentManager aDepartmentManager = new DepartmentManager();
aTeacher.ADepartment = aDepartmentManager.GetDepartment(Convert.ToInt16(departmentDropDownList.Text));
string teacherId = aTeacherManager.GetTeacherId(Convert.ToInt16(departmentDropDownList.Text));
aTeacher.TeacherId = aTeacher.ADepartment.DepartmentCode.ToString() + "-" + teacherId + contactNoTextBox.Value;
string msg = aTeacherManager.SaveTeacher(aTeacher);
if (msg == aTeacher.TeacherId)
{
msgLabel.ForeColor = Color.Green;
msgLabel.Text =aTeacher.Name+" your Id is :"+ msg;
ClearAllTextBoxes();
}
else
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = msg;
}
}
catch (SqlException sqlException)
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = "Database error.See details error: " + sqlException.Message;
}
catch (Exception exception)
{
msgLabel.ForeColor = Color.Red;
string errorMessage = "Unknow error occured.";
errorMessage += exception.Message;
if (exception.InnerException != null)
{
errorMessage += exception.InnerException.Message;
}
msgLabel.Text = errorMessage;
}
}
public Teacher GetTeacherCreditInfo(Teacher aTeacher)
{
aTeacherGateway = new TeacherGateway();
return aTeacherGateway.GetTeacherCreditInfo(aTeacher);
}
public Teacher GetTeacher(Teacher aTeacher)
{
aTeacherGateway = new TeacherGateway();
return aTeacherGateway.GetTeacher(aTeacher);
}
private bool DoesThisEmailExist(Teacher aTeacher)
{
List<string> emails = new List<string>();
aTeacherGateway = new TeacherGateway();
emails = aTeacherGateway.GetAllEmails();
foreach (string email in emails)
{
if (email == aTeacher.Email)
{
return true;
}
}
return false;
}
private void UpdateTeacherCredit(TeacherCourse aTeacherCourse, float credit)
{
try
{
Teacher aTeacher = new Teacher();
aTeacher.TeacherId = aTeacherCourse.TeacherId;
aTeacher.ADepartment = new Department();
aTeacher.ADepartment.DepartmentId = aTeacherCourse.DepartmentId;
aTeacher = GetTeacherCreditInfo(aTeacher);
aTeacher.AssignCredit = Convert.ToDouble(aTeacher.AssignCredit) + credit;
aTeacher.RemaningCredit = Convert.ToDouble(aTeacher.RemaningCredit) - credit;
connection.Open();
string query = "UPDATE t_TeacherCredit SET AssignCredit =@assignCredit,RemainingCredit=@remainingCredit Where teacherId =@teacherId";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@assignCredit", aTeacher.AssignCredit);
command.Parameters.AddWithValue("@remainingCredit", aTeacher.RemaningCredit);
command.Parameters.AddWithValue("@teacherId", aTeacherCourse.TeacherId);
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
private void SaveTeacherCredit(Teacher aTeacher)
{
try
{
connection.Open();
float assignCredit = 0;
int status = 0;
string addTeacherQuery = "INSERT INTO t_TeacherCredit VALUES(@id,@assignCredit,@remaningCredit,@status)";
command.CommandText = addTeacherQuery;
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", aTeacher.TeacherId);
command.Parameters.AddWithValue("@assignCredit", assignCredit);
command.Parameters.AddWithValue("@remaningCredit", aTeacher.AssignCredit);
command.Parameters.AddWithValue("@status", status);
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
public void UpdateTeacherCredit()
{
List<Teacher> teachersId = new List<Teacher>();
try
{
connection.Open();
string query = "select teacherId,assignCredit from t_Teacher";
command.CommandText = query;
SqlDataReader idReader = command.ExecuteReader();
while (idReader.Read())
{
Teacher aTeacher = new Teacher();
aTeacher.TeacherId = idReader[0].ToString();
aTeacher.AssignCredit = double.Parse(idReader[1].ToString());
teachersId.Add(aTeacher);
}
}
finally
{
connection.Close();
UpdateCredit(teachersId);
}
}
public string SaveTeacher(Teacher aTeacher)
{
try
{
connection.Open();
string addTeacherQuery =
"INSERT INTO t_Teacher VALUES(@id,@name,@address,@email,@contactNo,@designation,@assignCredit,@deptCode)";
command.CommandText = addTeacherQuery;
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", aTeacher.TeacherId);
command.Parameters.AddWithValue("@name", aTeacher.Name);
command.Parameters.AddWithValue("@address", aTeacher.Address);
command.Parameters.AddWithValue("@email", aTeacher.Email);
command.Parameters.AddWithValue("@contactNo", aTeacher.ContactNo);
command.Parameters.AddWithValue("@designation", aTeacher.ADesignation.Id);
command.Parameters.AddWithValue("@assignCredit", aTeacher.AssignCredit);
command.Parameters.AddWithValue("@deptCode", aTeacher.ADepartment.DepartmentId);
command.ExecuteNonQuery();
return aTeacher.TeacherId;
}
finally
{
connection.Close();
SaveTeacherCredit(aTeacher);
}
}
public Teacher GetTeacherCreditInfo(Teacher aTeacher)
{
try
{
connection.Open();
Teacher teacher = new Teacher();
string query = "select * from t_TeacherCredit where teacherId=@id";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", aTeacher.TeacherId);
SqlDataReader teacherReader = command.ExecuteReader();
while (teacherReader.Read())
{
teacher.TeacherId = teacherReader[0].ToString();
teacher.AssignCredit = float.Parse(teacherReader[1].ToString());
teacher.RemaningCredit = float.Parse(teacherReader[2].ToString());
}
return teacher;
}
finally
{
connection.Close();
}
}
private void GetTeacherAssignCredit()
{
try
{
Teacher aTeacher = new Teacher();
aTeacher.ADepartment = new Department();
aTeacher.ADepartment.DepartmentId = Convert.ToInt16(departmentDropDownList.Text);
aTeacher.TeacherId = teacherDropDownList.Text;
TeacherManager aTeacherManager = new TeacherManager();
aTeacher = aTeacherManager.GetTeacherCreditInfo(aTeacher);
assignCreditTextBox.Text = aTeacher.AssignCredit.ToString();
remainingCreditTextBox.Value = aTeacher.RemaningCredit.ToString();
}
catch (Exception exception)
{
throw exception;
}
}
