//get course details for given programme and major
public ILMPCourseGridVO GetCourseDetailsForTemplate(CourseProgrammeVO courseProgarmmeVO)
{
ILMPCourseGridVO ilmpcourseVO = new ILMPCourseGridVO();
try
{
DBConnection.conn.Open();
string query = "SELECT cpgm.CourseType,c.Title,c.Credits,c.Level,cp.AllPrerequisites from dbo.Course c" +
" INNER JOIN dbo.CourseProgramme cpgm ON cpgm.CourseCode=c.CourseCode" +
" LEFT JOIN dbo.CoursePrerequisite cp ON cp.CourseCode=c.CourseCode " +
" WHERE c.CourseCode = @CourseCode AND cpgm.ProgrammeID=@ProgrammeID AND cpgm.MajorID=@MajorID AND c.Active='Yes'";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@CourseCode", courseProgarmmeVO.CourseCode);
cmd.Parameters.AddWithValue("@ProgrammeID", courseProgarmmeVO.ProgrammeId);
cmd.Parameters.AddWithValue("@MajorID", courseProgarmmeVO.MajorId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ilmpcourseVO.CourseType = reader["CourseType"].ToString();
ilmpcourseVO.Title = reader["Title"].ToString();
ilmpcourseVO.Credits = Int32.Parse(reader["Credits"].ToString());
ilmpcourseVO.Level = Int32.Parse(reader["Level"].ToString());
ilmpcourseVO.Prerequisites = reader["AllPrerequisites"].ToString();
}
}
}
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(ilmpcourseVO);
}
//Get all course code and type for programme and major
public List <CourseProgrammeVO> GetCourseForPgmMajor(string inprogrammeId, string inmajorid)
{
List <CourseProgrammeVO> courses = new List <CourseProgrammeVO>();
try
{
DBConnection.conn.Open();
string query = "SELECT CourseCode, CourseType FROM dbo.CourseProgramme WHERE ProgrammeID=@ProgrammeId AND MajorID=@MajorId";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@ProgrammeId", inprogrammeId);
cmd.Parameters.AddWithValue("@MajorId", inmajorid);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
CourseProgrammeVO courseProgrammeVO;
while (reader.Read())
{
courseProgrammeVO = new CourseProgrammeVO();
courseProgrammeVO.CourseCode = reader["CourseCode"].ToString();
courseProgrammeVO.CourseType = reader["CourseType"].ToString();
courses.Add(courseProgrammeVO);
}
}
}
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(courses);
}
// Get programme and major for the given course code
public List <CourseProgrammeVO> GetCourseProgrammeForCourseCode(string incourseCode)
{
List <CourseProgrammeVO> CourseProgrammeList = new List <CourseProgrammeVO>();
try
{
DBConnection.conn.Open();
string query = "SELECT ProgrammeId,MajorId from CourseProgramme where CourseCode=@CourseCode";
SqlCommand cmd = new SqlCommand(query, DBConnection.conn);
cmd.Parameters.AddWithValue("@CourseCode", incourseCode);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
CourseProgrammeVO coursePgmVO;
while (reader.Read())
{
coursePgmVO = new CourseProgrammeVO();
coursePgmVO.ProgrammeId = reader["ProgrammeId"].ToString();
coursePgmVO.MajorId = reader["MajorId"].ToString();
CourseProgrammeList.Add(coursePgmVO);
}
}
}
catch (SqlException e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException("Error in fetching course ");
}
catch (Exception e)
{
ExceptionUtility.LogException(e, "Error Page");
throw new CustomException("Error in fetching course ");
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
return(CourseProgrammeList);
}
public ILMPCourseGridVO GetCourseDetailsForTemplate(CourseProgrammeVO courseProgarmmeVO)
{
ILMPCourseGridVO ilmpcourse = courseobj.GetCourseDetailsForTemplate(courseProgarmmeVO);
return(ilmpcourse);
}
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 btnAddCourse_Click(object sender, EventArgs e)
{
try
{
if (txtCourseCode.Text.Trim() == "")
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('Please enter course code');", true);
return;
}
else if (txtTitle.Text.Trim() == "")
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('Please enter title');", true);
return;
}
string courseCode = txtCourseCode.Text;
string title = txtTitle.Text;
int credits = Convert.ToInt32(ddlCredits.Text);
int level = Convert.ToInt32(ddlLevel.Text);
string offeredFrequency = txtOfferedFrequency.Text;
string active = "Yes";
DateTime createdDTM = DateTime.Now;
DateTime updatedDTM = DateTime.Now;
string prerequisite = "";
List <CourseProgrammeVO> coursePgm = new List <CourseProgrammeVO>();
CourseVO courseVO = new CourseVO(courseCode, title, credits, level, offeredFrequency, active, createdDTM, updatedDTM);
// get listbox items and set in hidden field
if (lbPrerequisite.Items.Count > 0)
{
string tempprequisite = "";
for (int i = 0; i < lbPrerequisite.Items.Count; i++)
{
tempprequisite += lbPrerequisite.Items[i].Value;
if (i != lbPrerequisite.Items.Count - 1)
{
tempprequisite += "&";
}
}
hfPrerequisite.Value = tempprequisite;
}
if (ddCorequisite.SelectedItem.Text != "Select")
{
hfPrerequisite.Value += "#" + ddCorequisite.SelectedItem.Text;
}
string allPrequisite = hfPrerequisite.Value;
/*if (hfPrerequisite.Value.Trim() == "" && ddCorequisite.SelectedItem.Text == "Select")
* {
* }
* else if (hfPrerequisite.Value.Trim() != "" && ddCorequisite.SelectedItem.Text != "Select")
* {
* //allPrequisite = hfPrerequisite.Value.Substring(1);
* allPrequisite += "#" + ddCorequisite.SelectedItem.Text;
* }
* else if (hfPrerequisite.Value.Trim() != "Select")
* {
* /*if (hfPrerequisite.Value.Length > 5)
* allPrequisite = hfPrerequisite.Value.Substring(1);
* else*/
// allPrequisite = hfPrerequisite.Value;
/* }
* else if (ddCorequisite.SelectedItem.Text != "Select")
* {
* allPrequisite += "#" + ddCorequisite.SelectedItem.Text;
* } */
if (allPrequisite != "")
{
if (allPrequisite.StartsWith("&"))
{
allPrequisite = allPrequisite.Substring(1);
}
CoursePrerequisiteVO prerequisiteVO = new CoursePrerequisiteVO(courseCode, prerequisite, "Prerequisite", allPrequisite);
courseVO.Prerequisites = prerequisiteVO;
}
if (rbtnBIT.Checked)
{
foreach (ListItem cb in cblBITMajor.Items)
{
if (cb.Selected)
{
CourseProgrammeVO coursePgmVO = new CourseProgrammeVO();
coursePgmVO.CourseCode = courseCode;
coursePgmVO.ProgrammeId = rbtnBIT.Text;
coursePgmVO.MajorId = cb.Value;
coursePgmVO.CourseType = ddCourseType.SelectedItem.Text;
coursePgm.Add(coursePgmVO);
}
}
}
if (rbtnGDIT.Checked)
{
foreach (ListItem cb in cblGDITMajor.Items)
{
if (cb.Selected)
{
CourseProgrammeVO coursePgmVO = new CourseProgrammeVO();
coursePgmVO.CourseCode = courseCode;
coursePgmVO.ProgrammeId = rbtnGDIT.Text;
coursePgmVO.MajorId = cb.Value;
coursePgmVO.CourseType = ddCourseType.SelectedItem.Text;
coursePgm.Add(coursePgmVO);
}
}
}
if (coursePgm.Count >= 1)
{
courseVO.Program = coursePgm;
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('Please select atleast one programme and major');", true);
return;
}
string status = courseBO.AddCourse(courseVO);
Response.Write("<script>alert('" + status + "');</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');</script>");
}
}
private void AddNewRowToCourseGrid(TemplateCourseVO templateCourseVO)
{
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)gvIlmp.Rows[i].Cells[2].FindControl("ddSemester");
DropDownList ddl2 = (DropDownList)gvIlmp.Rows[i].Cells[3].FindControl("ddYear");
// Update the DataRow with the DDL Selected Items
dtCurrentTable.Rows[i]["Column1"] = templateCourseVO.CourseCode;
CourseProgrammeVO coursePgmVO = new CourseProgrammeVO();
coursePgmVO.CourseCode = templateCourseVO.CourseCode;
coursePgmVO.ProgrammeId = ddProgramme.SelectedItem.Value;
coursePgmVO.MajorId = ddMajor.SelectedItem.Value;
ILMPCourseGridVO ilmpCourseGridVO = new ILMPCourseGridVO();
// for custom template, course may not be in same major selected
if (rbtnILMPTemplateType.SelectedItem.Text == "Generic")
{
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"] = "COM";
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;
}
// fills the semester dropdown from courseoffering. If there is any courseoffering, by default first value coming from db will be selected in semester dropdown
FillSemesterDropDown(ddl1, coursePgmVO.CourseCode);
// checks whether courseoffering is present by checking semster default selected value
if (ddl1.SelectedItem != null && ddl1.SelectedItem.Text != "Select")
{
dtCurrentTable.Rows[i]["Column3"] = templateCourseVO.Semester; //set the selected semester of dropdown to semester from ILMP template
FillYearDropDown(ddl2, coursePgmVO.CourseCode, int.Parse(ddl1.SelectedItem.Text));
}
if (ddl2.SelectedItem != null)
{
dtCurrentTable.Rows[i]["Column4"] = templateCourseVO.Year;//set the selected year of dropdown to year from ILMP template
}
}
}
//Rebind the Grid with the current data
gvIlmp.DataSource = dtCurrentTable;
gvIlmp.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 BtnUpload_Click(object sender, EventArgs e)
{
string existingCourse = "";
string failedCourses = "";
bool courseExists = false;
List <CourseVO> courseVOList = new List <CourseVO>();
try
{
ICourseDao courseDao = new CourseDaoImpl();
List <string> existingCourseCodeList = courseDao.GetAllCourseCodeAsList();
string filepath = Server.MapPath("~/Files/") + Path.GetFileName(fuBulkCourseUpload.PostedFile.FileName);
if (fuBulkCourseUpload.HasFile)
{
fuBulkCourseUpload.SaveAs(filepath);
string data = File.ReadAllText(filepath);
Boolean headerRowHasBeenSkipped = false;
foreach (string row in data.Split('\n'))
{
if (headerRowHasBeenSkipped)
{
courseExists = false;
if (!string.IsNullOrEmpty(row))
{
CourseVO courseVO = new CourseVO();
List <string> pgmList = new List <string>();
string courseType = "COM";
int i = 0;
List <CourseProgrammeVO> coursePgmVOList = new List <CourseProgrammeVO>();
foreach (string cell in row.Split(','))
{
if (!courseExists)
{
string celltemp = "";
if (cell.Contains("\r"))
{
celltemp = cell.Replace("\r", "").Trim();
}
else
{
celltemp = cell;
}
celltemp = celltemp.Trim();
switch (i)
{
case 0:
{
if (existingCourseCodeList.Contains(celltemp))
{
existingCourse += celltemp + ",";
courseExists = true;
}
else
{
courseVO.CourseCode = celltemp;
}
break;
}
case 1:
{
courseVO.Title = celltemp;
break;
}
case 2:
{
if (celltemp.Contains("&"))
{
foreach (string pgm in celltemp.Split('&'))
{
pgmList.Add(pgm.Trim());
}
}
else
{
pgmList.Add(celltemp);
}
break;
}
case 3:
{
courseType = celltemp;
break;
}
case 4:
{
if (celltemp.Contains("&"))
{
CourseProgrammeVO cp;
foreach (string pgm in pgmList)
{
foreach (string mjr in celltemp.Split('&'))
{
cp = new CourseProgrammeVO();
cp.ProgrammeId = pgm.Trim();
cp.CourseType = courseType.Trim();
cp.MajorId = mjr.Trim();
coursePgmVOList.Add(cp);
}
}
}
else
{
CourseProgrammeVO cp;
foreach (string pgm in pgmList)
{
cp = new CourseProgrammeVO();
cp.ProgrammeId = pgm.Trim();
cp.CourseType = courseType.Trim();
cp.MajorId = celltemp.Trim();
coursePgmVOList.Add(cp);
}
}
break;
}
case 5:
{
courseVO.Credits = int.Parse(celltemp);
break;
}
case 6:
{
courseVO.Level = int.Parse(celltemp);
break;
}
case 7:
{
courseVO.OfferedFrequency = celltemp;
break;
}
case 8:
{
if (celltemp != "NA")
{
courseVO.Prerequisites.AllPrerequisites = celltemp.Trim();
courseVO.Prerequisites.CourseCode = courseVO.CourseCode;
}
break;
}
case 9:
{
string allprerequisite = courseVO.Prerequisites.AllPrerequisites;
if (celltemp != "NA")
{
if (allprerequisite != null)
{
courseVO.Prerequisites.AllPrerequisites = allprerequisite + "#" + celltemp;
}
else
{
courseVO.Prerequisites.AllPrerequisites = "#" + celltemp;
courseVO.Prerequisites.CourseCode = courseVO.CourseCode;
}
}
else
{
courseVO.Prerequisites.AllPrerequisites = allprerequisite;
}
courseVO.Prerequisites.PrerequisiteCode = "";
courseVO.Prerequisites.Type = "Prerequisite";
break;
}
}
}
i++;
}
if (!courseExists)
{
courseVO.Program = coursePgmVOList;
courseVOList.Add(courseVO);
}
}
}
headerRowHasBeenSkipped = true;
}
if (courseVOList.Count > 0)
{
foreach (CourseVO courseVO in courseVOList)
{
courseVO.Active = "Yes";
string status = courseBO.AddCourse(courseVO);
if (status.Contains("fail"))
{
failedCourses += courseVO.CourseCode;
}
}
string usermessage = " Courses upload completed.";
if (failedCourses != "")
{
usermessage += " FailedCourseList " + failedCourses;
}
if (existingCourse != "")
{
usermessage += " Existing courses cannot be added. " + existingCourse;
}
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + usermessage + "');", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert(' Uploaded courses exists already');", 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>");
}
}
finally
{
if (DBConnection.conn != null)
{
DBConnection.conn.Close();
}
}
}
