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>");
        }
    }
Example #2
0
    // 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);
    }
Example #3
0
    // 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);
    }
Example #4
0
    public Boolean DeleteCourseOffering(CourseOfferingVO inCourseOffering)
    {
        Boolean courseCode = false;

        try
        {
            courseCode = courseOfferingObj.DeleteCourseOffering(inCourseOffering);
        }
        catch (CustomException e)
        {
            throw e;
        }
        return(true);
    }
Example #5
0
    public Boolean AddCourseOffering(CourseOfferingVO inCourseOffering)
    {
        Boolean status = false;

        try
        {
            status = courseOfferingObj.AddCourseOffering(inCourseOffering);
        }
        catch (CustomException e)
        {
            throw e;
        }
        return(status);
    }
Example #6
0
    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);
    }
Example #7
0
    //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);
    }
Example #8
0
    // 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);
    }
Example #9
0
    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");
        }
    }
Example #10
0
 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");
     }
 }
Example #11
0
    // 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);
    }
Example #12
0
    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>");
            }
        }
    }