protected void btnAddCourseOffering_Click(object sender, EventArgs e)
{
try
{
if (txtYearCourseOffering.Text == "")
{
Response.Write("<script>alert('Please enter year');</script>");
return;
}
int year = 0;
string courseCode = ddlCourseCodeOffering.SelectedItem.Text;
int semester = Convert.ToInt32(ddlSemesterCourseOffering.SelectedItem.Text);
try
{
year = int.Parse(txtYearCourseOffering.Text);
if (year < DateTime.Now.Year)
{
Response.Write("<script>alert('Year should be current year or greater than that');</script>");
return;
}
}
catch (ParseException)
{
Response.Write("<script>alert('Please enter valid year');</script>");
return;
}
DateTime createdDTM = DateTime.Now;
DBConnection.conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.CourseOffering WHERE CourseCode='" + courseCode + "' AND Semester=" + semester + " AND Year=" + year, DBConnection.conn);
Int32 count = (Int32)cmd.ExecuteScalar();
DBConnection.conn.Close();
if (count > 0)
{
Response.Write("<script>alert('Course offering already exists);</script>");
}
else
{
CourseOfferingVO courseOfferingVO = new CourseOfferingVO(courseCode, semester, year, createdDTM);
if (courseOfferingBO.AddCourseOffering(courseOfferingVO))
{
Response.Write("<script>alert('Course Offering added successfully');</script>");
FillDdlCourseCode();
}
else
{
Response.Write("<script>alert('Course Offering addition fail');</script>");
}
}
}
catch (CustomException ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
Response.Write("<script>alert('Error in adding course offering');</script>");
}
}
// update course offering
public Boolean UpdateCourseOffering(CourseOfferingVO inCourseOffering, int oldSemester, int oldYear)
{
Boolean status = false;
try
{
DBConnection.conn.Open();
String query = "UPDATE dbo.CourseOffering SET CourseCode=@CourseCode,Semester=@Semester, Year=@Year, CreatedDTM=@CreatedDTM WHERE CourseCode=@CourseCode and Semester=@oldSemester and Year=@oldYear";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@CourseCode", inCourseOffering.CourseCode);
cmd.Parameters.AddWithValue("@Semester", inCourseOffering.Semester);
cmd.Parameters.AddWithValue("@Year", inCourseOffering.Year);
cmd.Parameters.AddWithValue("@CreatedDTM", DateTime.Now);
cmd.Parameters.AddWithValue("@oldSemester", oldSemester);
cmd.Parameters.AddWithValue("@oldYear", oldYear);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
if (e.Number == 2627)
{
throw new CustomException("Duplicate record exists.Cannot Update.");
}
else if (e.Number == 547)
{
throw new CustomException("CourseCode not found in Course. Please add course and try again");
}
else
{
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(status);
}
// add course offering into database
public Boolean AddCourseOffering(CourseOfferingVO inCourseOffering)
{
Boolean status = false;
try
{
DBConnection.conn.Open();
String query = "INSERT INTO dbo.CourseOffering (CourseCode,Semester, Year,IsActive, CreatedDTM) VALUES (@CourseCode,@Semester,@Year,@IsActive, @CreatedDTM)";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@CourseCode", inCourseOffering.CourseCode);
cmd.Parameters.AddWithValue("@Semester", inCourseOffering.Semester);
cmd.Parameters.AddWithValue("@Year", inCourseOffering.Year);
cmd.Parameters.AddWithValue("@IsActive", "Yes");
cmd.Parameters.AddWithValue("@CreatedDTM", inCourseOffering.CreatedTime);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
if (e.Number == 2627)
{
throw new CustomException("Duplicate record exists. Please remove dupliactes and try again");
}
else if (e.Number == 547)
{
throw new CustomException("CourseCode not found in Course. Please add course and try again");
}
else
{
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(status);
}
public Boolean DeleteCourseOffering(CourseOfferingVO inCourseOffering)
{
Boolean courseCode = false;
try
{
courseCode = courseOfferingObj.DeleteCourseOffering(inCourseOffering);
}
catch (CustomException e)
{
throw e;
}
return(true);
}
public Boolean AddCourseOffering(CourseOfferingVO inCourseOffering)
{
Boolean status = false;
try
{
status = courseOfferingObj.AddCourseOffering(inCourseOffering);
}
catch (CustomException e)
{
throw e;
}
return(status);
}
public Boolean UpdateCourseOffering(CourseOfferingVO inCourseOffering, int oldSemester, int oldYear)
{
Boolean status = false;
try
{
status = courseOfferingObj.UpdateCourseOffering(inCourseOffering, oldSemester, oldYear);
}
catch (CustomException e)
{
throw e;
}
return(status);
}
//Get list of courses available for given programme, major and semester
public List <CourseOfferingVO> GetOfferedCoursesForPgmMajor(int semester, int year, string programmeId, string majorId)
{
List <CourseOfferingVO> coursesOffered = new List <CourseOfferingVO>();
try
{
DBConnection.conn.Open();
string query = "SELECT cp.CourseCode from dbo.CourseProgramme cp inner join dbo.CourseOffering co " +
" on cp.CourseCode=co.CourseCode " +
" where cp.ProgrammeID=@ProgrammeId and cp.MajorID=@MajorId and co.Semester=@Semester AND co.Year=@Year and co.IsActive='Yes' ";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@Year", year);
cmd.Parameters.AddWithValue("@Semester", semester);
cmd.Parameters.AddWithValue("@ProgrammeId", programmeId);
cmd.Parameters.AddWithValue("@MajorId", majorId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseOfferingVO courseOfferingVO = new CourseOfferingVO();
courseOfferingVO.CourseCode = reader["CourseCode"].ToString();
coursesOffered.Add(courseOfferingVO);
}
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(coursesOffered);
}
// Get all details from course offering
public List <CourseOfferingVO> GetOfferedCourses()
{
List <CourseOfferingVO> coursesOffered = new List <CourseOfferingVO>();
try
{
DBConnection.conn.Open();
string query = "SELECT * from dbo.CourseOffering where isActive='Yes'";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseOfferingVO courseOfferingVO = new CourseOfferingVO();
courseOfferingVO.CourseCode = reader["CourseCode"].ToString();
courseOfferingVO.CourseCode = reader["Semester"].ToString();
courseOfferingVO.CourseCode = reader["Year"].ToString();
coursesOffered.Add(courseOfferingVO);
}
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(coursesOffered);
}
protected void btnUpdateCourseOffering_Click(object sender, EventArgs e)
{
try
{
string courseCode = txtEditCourseCodeOffering.Text;
int semester = Convert.ToInt32(ddlEditSemesterCourseOffering.SelectedValue);
int year = Convert.ToInt32(txtEditYearCourseOffering.Text);
DateTime createdDTM = DateTime.Now;
DBConnection.conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.CourseOffering WHERE CourseCode='" + courseCode + "' AND Semester=" + semester + " AND Year=" + year, DBConnection.conn);
Int32 count = (Int32)cmd.ExecuteScalar();
DBConnection.conn.Close();
if (count > 0)
{
Response.Write("<script>alert('Course Offering exists already ');</script>");
}
else
{
CourseOfferingVO courseOfferingVO = new CourseOfferingVO(courseCode, semester, year, createdDTM);
int oldSem = int.Parse(hfSemester.Value);
int oldYear = int.Parse(hfYear.Value);
if (courseOfferingBO.UpdateCourseOffering(courseOfferingVO, oldSem, oldYear))
{
Response.Write("<script>alert('Course updated successfully');</script>");
}
else
{
Response.Write("<script>alert('Course updation failed');</script>");
}
}
}
catch (CustomException ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
}
}
protected void btnDelCourseOffering_Click(object sender, EventArgs e)
{
if (txtEditCourseCodeOffering.Text == "")
{
Response.Write("<script>alert('Please select a CourseOffering');</script>");
}
else
{
string courseCode = txtEditCourseCodeOffering.Text;
CourseOfferingVO courseOfferingVO = new CourseOfferingVO(courseCode);
courseOfferingVO.Semester = int.Parse(ddlEditSemesterCourseOffering.SelectedItem.Text);
courseOfferingVO.Year = int.Parse(txtEditYearCourseOffering.Text);
if (courseOfferingBO.DeleteCourseOffering(courseOfferingVO))
{
Response.Write("<script>alert('Course Offering deleted successfully');</script>");
}
else
{
Response.Write("<script>alert('Course Offering deletion failed');</script>");
}
Response.Redirect("~/CourseOfferingList.aspx");
}
}
// delete the given course offering
public Boolean DeleteCourseOffering(CourseOfferingVO inCourseOffering)
{
string courseCode = "";
try
{
DBConnection.conn.Open();
String query = "DELETE FROM dbo.CourseOffering WHERE courseCode=@CourseCode AND Semester=@Semester AND Year=@Year";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@CourseCode", inCourseOffering.CourseCode);
cmd.Parameters.AddWithValue("@Semester", inCourseOffering.Semester);
cmd.Parameters.AddWithValue("@Year", inCourseOffering.Year);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
courseCode = inCourseOffering.CourseCode;
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException(ApplicationConstants.UnhandledException + ": " + e.Message);
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(true);
}
protected void BtnUpload_Click(object sender, EventArgs e)
{
try
{
List <ILMPCourseVO> ilmpCourses = new List <ILMPCourseVO>();
List <ILMPCourseVO> existingOffering;
List <ILMPCourseVO> UserMessageCourseOffering = new List <ILMPCourseVO>();
CourseOfferingDaoImpl courseOfferingDaoImpl = new CourseOfferingDaoImpl();
List <CourseOfferingVO> allCourseOffering = new List <CourseOfferingVO>();
List <CourseOfferingVO> existCourseOffering;
/* using (SqlConnection sqlconn = new SqlConnection(mainconn))
* {
* sqlconn.Open();
* string query = "select distinct ic.coursecode,ic.Semester,ic.Year from ilmpcourse ic inner join ilmp i on i.IlmpID = ic.IlmpID and i.Active='Yes'";
* SqlCommand cmd = new SqlCommand(query, sqlconn);
* SqlDataReader reader = cmd.ExecuteReader();
* if (reader.HasRows)
* {
* ILMPCourseVO ilmpCourseVO;
* while (reader.Read())
* {
* ilmpCourseVO = new ILMPCourseVO();
* ilmpCourseVO.CourseCode = reader["coursecode"].ToString();
* ilmpCourseVO.Semester = int.Parse(reader["Semester"].ToString());
* ilmpCourseVO.Year = int.Parse(reader["Year"].ToString());
* ilmpCourses.Add(ilmpCourseVO);
* }
* }
* sqlconn.Close();
* }*/
using (SqlConnection sqlconn = new SqlConnection(mainconn))
{
using (SqlBulkCopy sqlbkcpy = new SqlBulkCopy(sqlconn))
{
string filepath = Server.MapPath("~/Files/") + Path.GetFileName(fsBulkCourseOfferingUpload.PostedFile.FileName);
if (fsBulkCourseOfferingUpload.HasFile)
{
fsBulkCourseOfferingUpload.SaveAs(filepath);
DataTable dtable = new DataTable();
dtable.Columns.AddRange(new DataColumn[4]
{
new DataColumn("CourseCode", typeof(string)),
new DataColumn("Semester", typeof(int)),
new DataColumn("Year", typeof(int)),
new DataColumn("CreatedDTM", typeof(string))
});
string data = File.ReadAllText(filepath);
Boolean headerRowHasBeenSkipped = false;
ILMPCourseVO uploadedCourse = new ILMPCourseVO();
CourseOfferingVO uploadedCourseOffering = new CourseOfferingVO();
DeleteCourseOffering();
foreach (string row in data.Split('\n'))
{
uploadedCourse = new ILMPCourseVO();
uploadedCourseOffering = new CourseOfferingVO();
if (headerRowHasBeenSkipped)
{
if (!string.IsNullOrEmpty(row))
{
dtable.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
string celltemp = "";
if (cell.Contains("\r"))
{
celltemp = cell.Replace("\r", "");
}
else
{
celltemp = cell;
}
if (i == 0)
{
uploadedCourse.CourseCode = celltemp;
uploadedCourseOffering.CourseCode = celltemp;
}
if (i == 1)
{
uploadedCourse.Semester = int.Parse(celltemp);
uploadedCourseOffering.Semester = int.Parse(celltemp);
}
dtable.Rows[dtable.Rows.Count - 1][i] = celltemp;
if (i == 2)
{
uploadedCourse.Year = int.Parse(celltemp);
uploadedCourseOffering.Year = int.Parse(cell);
dtable.Rows[dtable.Rows.Count - 1][3] = DateTime.Now;
}
i++;
}
}
/* existingOffering = ilmpCourses.Where(c => c.CourseCode == uploadedCourse.CourseCode & c.Semester == uploadedCourse.Semester & c.Year == uploadedCourse.Year).ToList();
* if (existingOffering.Count > 0)
* {
* UserMessageCourseOffering.Concat(existingOffering);
* dtable.Rows[dtable.Rows.Count - 1].Delete();
* }
*/
/* DBConnection.conn.Open();
* SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM dbo.CourseOffering WHERE CourseCode='" + uploadedCourse.CourseCode + "' AND Semester=" + uploadedCourse.Semester + "AND Year=" + uploadedCourse.Year, DBConnection.conn);
* Int32 count = (Int32)cmd.ExecuteScalar();
* if (count > 0)
* {
* dtable.Rows[dtable.Rows.Count - 1].Delete();
* }
* DBConnection.conn.Close();*/
}
headerRowHasBeenSkipped = true;
}
sqlbkcpy.DestinationTableName = "dbo.tmpCourseOffering";
sqlconn.Open();
sqlbkcpy.WriteToServer(dtable);
sqlconn.Close();
// call stored procedure to perform course offering validations
DBConnection.conn.Open();
SqlCommand cmd1 = new SqlCommand("spLoadCourseOffering", DBConnection.conn);
cmd1.CommandType = CommandType.StoredProcedure;
// execute the command
int result = cmd1.ExecuteNonQuery();
DBConnection.conn.Close();
ClientScript.RegisterStartupScript(this.GetType(), "successmsg", "File has successfully uploaded ");
//Response.Write("<script>alert('File has successfully uploaded. Please check CourseOffernigException.csv for errors');</script>");
WriteErrorsIntoCSV();
/*if (UserMessageCourseOffering.Count > 0)
* {
* string existingcl = "";
* foreach (ILMPCourseVO cv in UserMessageCourseOffering)
* {
* existingcl = cv.CourseCode + ", " + cv.Semester + "," + cv.Year + ":";
* }
* Response.Write("<script>alert('ILMP exists for" + existingcl + "');</script>");
* }
* else
* if (allCourseOffering.Count > 0)
* {
* string existco = "";
* foreach (CourseOfferingVO co in allCourseOffering)
* {
* existco = co.CourseCode + "," + co.Semester + "," + co.Year + ":";
* }
* Response.Write("<script>alert('Course Code exists for " + existco + "');</script>");
* }
* else
* {*/
// }
File.Delete(filepath);
}
else
{
Response.Write("<script>alert('Please select file to upload');</script>");
}
}
}
}
catch (ThreadAbortException)
{
//Response.Write("<script>alert('File has successfully uploaded ');</script>");
ClientScript.RegisterStartupScript(this.GetType(), "successmsg", "File has successfully uploaded ");
//ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' File has successfully uploaded ');", true);
}
catch (Exception ex)
{
ExceptionUtility.LogException(ex, "Error Page");
if (ex.Message.Contains("used by another process"))
{
Response.Write("<script>alert('CSV file is in use.Please close it and try again');</script>");
}
else
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
}
