public void DeleteData()
{
List <ILMPCourseVO> ilmpCourses = new List <ILMPCourseVO>();
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))
{
sqlconn.Open();
string query = "DELETE FROM CourseOffering";
SqlCommand cmd = new SqlCommand(query, sqlconn);
cmd.ExecuteNonQuery();
sqlconn.Close();
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
ILMPVO ilmpVO = new ILMPVO();
ilmpVO.IlmpId = int.Parse(hfIlmpId.Value);
ilmpVO.Active = ddActive.SelectedItem.Text;
ilmpVO.Description = txtDescription.Text;
// description to be added
List <ILMPCourseVO> ilmpCourses = new List <ILMPCourseVO>();
ILMPCourseVO ilmpCourseVO;
for (int i = 0; i < gvIlmpCourse.Rows.Count - 1; i++)
{
ilmpCourseVO = new ILMPCourseVO();
ilmpCourseVO.CourseCode = ((Label)gvIlmpCourse.Rows[i].Cells[0].FindControl("lblCourseCode")).Text;
DropDownList ddl1 = (DropDownList)gvIlmpCourse.Rows[i].Cells[1].FindControl("ddCourseType");
DropDownList ddl2 = (DropDownList)gvIlmpCourse.Rows[i].Cells[2].FindControl("ddSemester");
DropDownList ddl3 = (DropDownList)gvIlmpCourse.Rows[i].Cells[3].FindControl("ddYear");
DropDownList ddl4 = (DropDownList)gvIlmpCourse.Rows[i].Cells[3].FindControl("ddResult");
// type to be added if required
ilmpCourseVO.Semester = int.Parse(ddl2.SelectedItem.Text);
if (ddl3.SelectedItem.Text != null && ddl3.SelectedItem.Text != "Select")
{
ilmpCourseVO.Year = int.Parse(ddl3.SelectedItem.Text);
}
ilmpCourseVO.Result = ddl4.SelectedItem.Text;
ilmpCourses.Add(ilmpCourseVO);
}
ilmpVO.IlmpCourses = ilmpCourses;
Boolean status = ilmpBO.UpdateILMP(ilmpVO);
if (status)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' ILMP updated successfully ');", true);
//Response.Write("<script>alert(' ILMP updated successfully ');</script>");
// btnUpdate.Enabled = false;
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' Error in updating ILMP ');", true);
//Response.Write("<script>alert(' Error in updating ILMP ');</script>");
//btnUpdate.Enabled = true;
}
}
catch (CustomException ex)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + ex.Message + " ');", true);
}
}
public ILMPVO GetILMPCoursesWorkshopForId(int ilmpId)
{
ILMPVO ilmpVO = new ILMPVO();
List <ILMPCourseVO> ilmpCourses = new List <ILMPCourseVO>();
try
{
ilmpVO = GetILMPDetailsForId(ilmpId);
DBConnection.conn.Open();
string query = "SELECT ic.Semester,ic.Year, ic.CourseCode FROM dbo.ilmp i"
+ " LEFT OUTER JOIN dbo.IlmpCourse ic ON ic.IlmpID = i.IlmpID WHERE i.IlmpID=@IlmpID"
+ " ORDER BY ic.year, ic.Semester";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@IlmpID", ilmpId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
ILMPCourseVO ilmpCourse;
while (reader.Read())
{
ilmpCourse = new ILMPCourseVO();
ilmpCourse.Semester = Int32.Parse(reader["Semester"].ToString());
ilmpCourse.Year = Int32.Parse(reader["Year"].ToString());
ilmpCourse.CourseCode = reader["CourseCode"].ToString();
ilmpCourses.Add(ilmpCourse);
}
ilmpVO.IlmpCourses = ilmpCourses;
reader.Close();
}
}
catch (SqlException ex)
{
ExceptionUtility.LogException(ex, "Error Page");
throw ex;
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(ilmpVO);
}
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>");
}
}
}
private void AddNewRowToCourseGrid(ILMPCourseVO ilmpCourseVO)
{
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
drCurrentRow = dtCurrentTable.NewRow();
//add new row to DataTable
dtCurrentTable.Rows.Add(drCurrentRow);
//Store the current data to ViewState
ViewState["CurrentTable"] = dtCurrentTable;
for (int i = 0; i < dtCurrentTable.Rows.Count - 1; i++)
{
if (i != dtCurrentTable.Rows.Count - 2)
{
}
else
{
DropDownList ddl1 = (DropDownList)gvIlmpCourse.Rows[i].Cells[2].FindControl("ddSemester");
DropDownList ddl2 = (DropDownList)gvIlmpCourse.Rows[i].Cells[3].FindControl("ddYear");
// Update the DataRow with the DDL Selected Items
dtCurrentTable.Rows[i]["Column1"] = ilmpCourseVO.CourseCode;
CourseProgrammeVO coursePgmVO = new CourseProgrammeVO();
coursePgmVO.CourseCode = ilmpCourseVO.CourseCode;
coursePgmVO.ProgrammeId = studentMajorVO.ProgrammeID;
coursePgmVO.MajorId = studentMajorVO.MajorID;
// for custom template, course may not be in same major selected
/*if (.SelectedItem.Text == "Generic")
* {
* ILMPCourseGridVO ilmpCourseGridVO = courseBO.GetCourseDetailsForTemplate(coursePgmVO);
* dtCurrentTable.Rows[i]["Column2"] = ilmpCourseGridVO.CourseType;
* dtCurrentTable.Rows[i]["Column5"] = ilmpCourseGridVO.Title;
* dtCurrentTable.Rows[i]["Column6"] = ilmpCourseGridVO.Credits;
* dtCurrentTable.Rows[i]["Column7"] = ilmpCourseGridVO.Level;
* dtCurrentTable.Rows[i]["Column8"] = ilmpCourseGridVO.Prerequisites;
* }
* else
* {*/
CourseVO courseVO = courseBO.GetCourseDetailsForCourseCode(coursePgmVO.CourseCode);
dtCurrentTable.Rows[i]["Column2"] = ilmpCourseVO.CourseType;
dtCurrentTable.Rows[i]["Column5"] = courseVO.Title;
dtCurrentTable.Rows[i]["Column6"] = courseVO.Credits;
dtCurrentTable.Rows[i]["Column7"] = courseVO.Level;
dtCurrentTable.Rows[i]["Column8"] = courseVO.Prerequisites.AllPrerequisites;
//}
FillSemesterDropDown(ddl1, coursePgmVO.CourseCode);
if (ddl1.SelectedItem != null && ddl1.SelectedItem.Text != "Select")
{
dtCurrentTable.Rows[i]["Column3"] = ilmpCourseVO.Semester; //ddl1.SelectedItem.Text;
FillYearDropDown(ddl2, coursePgmVO.CourseCode, int.Parse(ddl1.SelectedItem.Text));
}
if (ddl2.SelectedItem != null)
{
dtCurrentTable.Rows[i]["Column4"] = ilmpCourseVO.Year; //ddl2.SelectedItem.Text;
}
}
}
//Rebind the Grid with the current data
gvIlmpCourse.DataSource = dtCurrentTable;
gvIlmpCourse.DataBind();
}
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('ViewState is null ');", true);
Response.Write("ViewState is null");
}
//Set Previous Data on Postbacks
SetPreviousDataInCourseGrid();
}
protected void btnSave_Click(object sender, EventArgs e)
{
ILMPVO ilmpVO = new ILMPVO();
try
{
ilmpVO.Name = txtName.Text;
if (txtStudentId.Text.Trim() != "")
{
try
{
ilmpVO.StudentId = Int32.Parse(txtStudentId.Text);
}
catch (ParseException)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' StudentId is not valid ');", true);
// Response.Write("<script>alert('StudentId is not valid');</script>");
return;
}
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' Please enter student id');", true);
// Response.Write("<script>alert('Please enter student id');</script>");
return;
}
ilmpVO.Active = ddActive.SelectedItem.Text;
ilmpVO.Description = txtDescription.Text;
List <ILMPCourseVO> templateCourses = new List <ILMPCourseVO>();
if (ViewState["CurrentTable"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentTable"];
// storing the grid values in KeyValue Pair because every semester will have multiple entries, it should be consolidated to store in database
if (dt.Rows.Count > 0)
{
ILMPCourseVO ilmpCourseVO = new ILMPCourseVO();
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
ilmpCourseVO = new ILMPCourseVO();
Label courseCode = (Label)gvIlmp.Rows[i].Cells[0].FindControl("lblCourseCode");
DropDownList ddl1 = (DropDownList)gvIlmp.Rows[i].Cells[1].FindControl("ddCourseType");
DropDownList ddl2 = (DropDownList)gvIlmp.Rows[i].Cells[2].FindControl("ddSemester");
DropDownList ddl3 = (DropDownList)gvIlmp.Rows[i].Cells[3].FindControl("ddYear");
string courseType = ddl1.SelectedItem.Text;
int semester = Int32.Parse(ddl2.SelectedItem.Text);
int year = 0;
if (ddl3.SelectedItem.Text != null && ddl3.SelectedItem.Text != "Select")
{
year = Int32.Parse(ddl3.SelectedItem.Text);
}
ilmpCourseVO.CourseCode = courseCode.Text;
ilmpCourseVO.Semester = semester;
ilmpCourseVO.Year = year;
ilmpCourseVO.CourseType = courseType;
templateCourses.Add(ilmpCourseVO);
}
ilmpVO.IlmpCourses = templateCourses;
ilmpVO.TemplateId = int.Parse(hfTemplateId.Value);
ILMPBO ilmpBO = new ILMPBO();
string status = ilmpBO.AddILMP(ilmpVO);
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + status + "');", true);
//Response.Write("<script>alert('" + status + "');</script>");
/* if (status.Contains("success"))
* {
* // btnSave.Enabled = false;
* }
* else
* {
* btnSave.Enabled = true;
* }*/
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('There are no courses added in ILMP');", true);
// Response.Write("<script>alert('There are no courses added in ILMP');</script>");
}
}
}
catch (CustomException ex)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + ex.Message + "');", true);
}
}
