public List <SupervisorFeedback> getAllUserFeedback(string userID)
{
SqlConnection conn = new SqlConnection();
List <SupervisorFeedback> toReturn = new List <SupervisorFeedback>();
UserDAO userDAO = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [SupervisorFeedback] where userTo=@userTo order by dateSubmitted desc";
comm.Parameters.AddWithValue("@userTo", userID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
SupervisorFeedback sf = new SupervisorFeedback();
sf.feedbackID = (int)dr["feedbackID"];
sf.title = (string)dr["title"];
sf.feedback = (string)dr["feedback"];
sf.userTo = userDAO.getUserByID((string)dr["userTo"]);
sf.userFrom = userDAO.getUserByID((string)dr["userFrom"]);
sf.dateSubmitted = (DateTime)dr["dateSubmitted"];
toReturn.Add(sf);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public ArrayList getNews()
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn = new ArrayList();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [News] where status = 'Active' order by levels ";
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
News a = new News();
string userid = (string)dr["userID"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.entry_time = ((DateTime)dr["entry_time_stamp"]);
a.banner_name = ((string)dr["banner_name"]);
a.banner_link = ((string)dr["banner_link"]);
a.banner_id = ((int)dr["banner_id"]);
if ((dr["news_text"] != DBNull.Value))
{
a.news_text = ((string)dr["news_text"]);
}
else
{
a.news_text = null;
}
string status = (string)dr["status"];
a.img_path = ((string)dr["img_path"]);
a.level = ((int)dr["levels"]);
toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Department getDeptByName(string deptName)
{
SqlConnection conn = new SqlConnection();
Department toReturn = null;
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Department] where dept_name=@dept_name";
comm.Parameters.AddWithValue("@dept_name", deptName);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new Department();
toReturn.setDeptName((string)dr["dept_name"]);
toReturn.setProjectedBudget((double)(dr["projected_budget"]));
toReturn.setActualBudget((double)(dr["actual_budget"]));
string dept_headID = (string)dr["dept_headID"];
toReturn.setDeptHead(userDAO.getUserByID(dept_headID));
toReturn.setCostCentre((int)dr["cost_centre"]);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Workflow getCurrentActiveWorkflow(string category)
{
SqlConnection conn = new SqlConnection();
Workflow toReturn = null;
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Workflow] where status=@status and category=@category";
comm.Parameters.AddWithValue("@status", "active");
comm.Parameters.AddWithValue("@category", category);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new Workflow();
toReturn.setWorkflowID((int)dr["wfid"]);
toReturn.setProbationPeriod((int)(dr["probation_period"]));
toReturn.setBondCriteria((double)(dr["bond_criteria"]));
string userCreatedID = (string)dr["userID"];
toReturn.setUserCreated(userDAO.getUserByID(userCreatedID));
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public List <TNF> getAllTNFByUserID(string userID)
{
SqlConnection conn = new SqlConnection();
List <TNF> toReturn = new List <TNF>();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [TNF] where userID=@userID";
comm.Parameters.AddWithValue("@userID", userID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TNF t = new TNF();
UserDAO userDAO = new UserDAO();
WorkflowDAO wfDAO = new WorkflowDAO();
WorkflowSubDAO wfsDAO = new WorkflowSubDAO();
User user = userDAO.getUserByID((string)dr["userID"]);
t.setUser(user);
t.setTNFID((int)dr["tnfid"]);
t.setType((string)dr["type"]);
t.setStatus((string)(dr["status"]));
t.setWFStatus((int)dr["wf_status"]);
t.setWorkflow(wfDAO.getWorkflowByID((int)dr["wfid"]));
if (!dr.IsDBNull(6))
{
t.setWorkflowSub(wfsDAO.getWorkflowSubByID((int)dr["wf_sub_id"]));
}
t.setApplicationDate(dr.GetDateTime(7));
toReturn.Add(t);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
//TO EDIT
public List <WorkflowApprover> getSortedWorkflowApprovers(string workflowID, string workflow_subID)
{
List <WorkflowApprover> toReturn = new List <WorkflowApprover>();
SqlConnection conn = new SqlConnection();
WorkflowApprover approver = null;
UserDAO userDAO = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Workflow_approvers] where wfid=@wfid and wfsid=@wfsid order by level asc";
comm.Parameters.AddWithValue("@wfid", workflowID);
comm.Parameters.AddWithValue("@wfsid", workflow_subID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
approver = new WorkflowApprover();
string wfid = (string)dr["wfid"];
Workflow wf = wfDAO.getWorkflowByID(wfid);
approver.setMainWF(wf);
string wfsid = (string)dr["wf_sub_id"];
WorkflowSub wfs = wfsDAO.getWorkflowSubByID(wfsid);
approver.setMainWFS(wfs);
string userID = (string)dr["userID"];
User user = userDAO.getUserByID(userID);
approver.setApprover(user);
approver.setLevel((int)dr["levels"]);
toReturn.Add(approver);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public ArrayList get_testimonials_by_course(Course_elearn course)
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn_list = new ArrayList();
Testimonial toReturn = null;
UserDAO u = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Testimonials where courseID=@id";
comm.Parameters.AddWithValue("@id", course.getCourseID());
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new Testimonial();
toReturn.setStaffName((string)dr["staff_name"]); //4
toReturn.set_course_elearn(course); //6
toReturn.setQuote((string)dr["quote"]);
toReturn.setUser(u.getUserByID((String)dr["userID"]));
toReturn.setID((int)dr["ID"]);
toReturn.setTitle((string)dr["title"]);
toReturn_list.Add(toReturn);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn_list);
}
public News_highlights getHighlightById(int id)
{
SqlConnection conn = new SqlConnection();
News_highlights a = new News_highlights();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [News_highlights] where highlight_id = @aid";
comm.Parameters.AddWithValue("@aid", id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
//News_highlights a = new News_highlights();
string userid = (string)dr["user_id"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.entry_time = ((DateTime)dr["date_posted"]);
a.title = ((string)dr["title"]);
a.body = ((string)dr["body"]);
a.news_text = ((string)dr["news_text"]);
a.highlight_id = ((int)dr["highlight_id"]);
string status = (string)dr["status"];
a.img_path = ((string)dr["img_path"]);
a.type = ((string)dr["type"]);
//toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(a);
}
public ArrayList getNewsHighlights()
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn = new ArrayList();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [News_highlights] where status = 'Active' order by date_posted desc";
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
News_highlights a = new News_highlights();
string userid = (string)dr["user_id"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.entry_time = ((DateTime)dr["date_posted"]);
a.title = ((string)dr["title"]);
a.body = ((string)dr["body"]);
a.news_text = ((string)dr["news_text"]);
a.highlight_id = ((int)dr["highlight_id"]);
string status = (string)dr["status"];
a.img_path = ((string)dr["img_path"]);
a.type = ((string)dr["type"]);
toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public ArrayList getAllQuizResultByQuizID(int quizID, string userID)
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn = new ArrayList();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [QuizResult] where quizID=@quizID and userID=@userID";
comm.Parameters.AddWithValue("@quizID", quizID);
comm.Parameters.AddWithValue("@userID", userID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
QuizResult qr = new QuizResult();
UserDAO userDAO = new UserDAO();
QuizDAO quizDAO = new QuizDAO();
QuizResultHistoryDAO qrhDAO = new QuizResultHistoryDAO();
qr.setQuizResultID((int)dr["quizResultID"]);
qr.setUser(userDAO.getUserByID((string)dr["userID"]));
qr.setQuiz(quizDAO.getQuizByID((int)dr["quizID"]));
qr.setScore((int)dr["score"]);
qr.setGrade((string)dr["grade"]);
qr.setDateSubmitted(dr.GetDateTime(5));
qr.setAttempt((int)dr["attempt"]);
toReturn.Add(qr);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Link getLinksById(int id)
{
SqlConnection conn = new SqlConnection();
Link a = new Link();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Links] where link_id = @id";
comm.Parameters.AddWithValue("@id", id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
//Article a = new Article();
a.link_path = ((string)dr["link_path"]);
a.description = ((string)dr["description"]);
//a.upload_datetime = ((string)dr["upload_datetime"]);
a.upload_datetime = ((DateTime)dr["upload_datetime"]);
string userid = (string)dr["upload_user"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.status = ((string)dr["status"]);
a.link_id = ((int)dr["link_id"]);
//toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(a);
}
public ArrayList getContacts()
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn = new ArrayList();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Contact] where status = 'Active' order by contact_id desc";
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Contact a = new Contact();
a.contact_id = ((int)dr["contact_id"]);
a.name = ((string)dr["name"]);
a.department = ((string)dr["department"]);
a.email = ((string)dr["email"]);
a.remarks = ((string)dr["remarks"]);
a.upload_datetime = ((DateTime)dr["upload_datetime"]);
string userid = (string)dr["user_upload"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.status = ((string)dr["status"]);
toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Article getArticleById(int id)
{
SqlConnection conn = new SqlConnection();
Article a = new Article();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Articles] where article_id = @aid";
comm.Parameters.AddWithValue("@aid", id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
//Article a = new Article();
a.article_id = ((int)dr["article_id"]);
a.article_name = ((string)dr["article_name"]);
a.article_body = ((string)dr["article_body"]);
a.upload_datetime = ((DateTime)dr["timestamp"]);
string userid = (string)dr["user_upload"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.status = ((string)dr["status"]);
//toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(a);
}
public Contact getContactById(int id)
{
SqlConnection conn = new SqlConnection();
Contact a = new Contact();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Contact] where contact_id = @aid";
comm.Parameters.AddWithValue("@aid", id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
a.contact_id = ((int)dr["contact_id"]);
a.name = ((string)dr["name"]);
a.department = ((string)dr["department"]);
a.email = ((string)dr["email"]);
a.remarks = ((string)dr["remarks"]);
a.upload_datetime = ((DateTime)dr["upload_datetime"]);
string userid = (string)dr["user_upload"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.status = ((string)dr["status"]);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(a);
}
public ArrayList getLinks()
{
SqlConnection conn = new SqlConnection();
ArrayList toReturn = new ArrayList();
UserDAO udao = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Links] where status = 'Active' order by link_id desc";
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Link a = new Link();
a.link_path = ((string)dr["link_path"]);
a.description = ((string)dr["description"]);
a.upload_datetime = ((DateTime)dr["upload_datetime"]);
string userid = (string)dr["upload_user"];
User user_now = udao.getUserByID(userid);
a.user = user_now;
a.status = ((string)dr["status"]);
a.link_id = ((int)dr["link_id"]);
toReturn.Add(a);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Course_elearn get_course_by_id(int id)
{
SqlConnection conn = new SqlConnection();
Course_elearn toReturn = null;
UserDAO userDAO = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Elearn_course] where elearn_courseID=@id";
comm.Parameters.AddWithValue("@id", id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new Course_elearn();
int cid = (int)dr["elearn_courseID"]; //1
toReturn.setCourseID(cid);
toReturn.setCourseName((string)dr["elearn_courseName"]); //2
if (!dr.IsDBNull(4))
{
toReturn.setCourseProvider((string)dr["elearn_courseProvider"]);
}
;
toReturn.setStartDate((DateTime)dr["start_date"]);//3
if (!dr.IsDBNull(4))
{
toReturn.setExpiryDate((DateTime)dr["expiry_date"]);
}
toReturn.setStatus((string)dr["status"]); //4
//get the prereq
toReturn.setDescription((string)dr["description"]); //6
toReturn.setEntryDate((DateTime)dr["entry_date"]);
ArrayList list = getPrereqOfCourse(cid); //5
if (list != null)
{
toReturn.setPrerequisite(list); //retrieve arraylist of all prereq course_elearn objects
}
toReturn.setCategoryID((int)dr["categoryID"]); //7
toReturn.setCourseCreator(userDAO.getUserByID((string)dr["courseCreator"]));
toReturn.setHoursAwarded((double)dr["hoursAwarded"]);
if (!dr.IsDBNull(11))
{
toReturn.setTargetAudience((string)dr["targetAudience"]);
}
toReturn.setCourseType((string)dr["courseType"]);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Quiz getQuizByID(int quizID)
{
SqlConnection conn = new SqlConnection();
Quiz toReturn = null;
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Quiz] q inner join [Elearn_course] ec on q.elearn_courseID = ec.elearn_courseID where q.quizID=@quizID";
comm.Parameters.AddWithValue("@quizID", quizID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new Quiz();
toReturn.setQuizID((int)dr["quizID"]);
toReturn.setTitle((string)dr["title"]);
toReturn.setDescription((string)dr["description"]);
toReturn.setPassingGrade((int)dr["passingGrade"]);
toReturn.setRandomOrder((string)dr["randomOrder"]);
toReturn.setStatus((string)dr["status"]);
toReturn.setTimeLimit((int)dr["timeLimit"]);
toReturn.setMultipleAttempts((string)dr["multipleAttempts"]);
toReturn.setNumberOfAttempts((int)dr["numberOfAttempts"]);
toReturn.setDisplayAnswer((string)dr["displayAnswer"]);
Course_elearnDAO ceDAO = new Course_elearnDAO();
//toReturn.setMainCourse(ceDAO.get_course_by_id((int)dr["elearn_courseID"]));
Course_elearn course = new Course_elearn();
UserDAO userDAO = new UserDAO();
int cid = (int)dr["elearn_courseID"]; //1
course.setCourseID(cid);
course.setCourseName((string)dr["elearn_courseName"]); //2
if (!dr.IsDBNull(4))
{
course.setCourseProvider((string)dr["elearn_courseProvider"]);
}
;
course.setStartDate((DateTime)dr["start_date"]);//3
if (!dr.IsDBNull(4))
{
course.setExpiryDate((DateTime)dr["expiry_date"]);
}
course.setStatus((string)dr["status"]); //4
//get the prereq
course.setDescription((string)dr["description"]); //6
course.setEntryDate((DateTime)dr["entry_date"]);
ArrayList list = ceDAO.getPrereqOfCourse(cid); //5
if (list != null)
{
course.setPrerequisite(list); //retrieve arraylist of all prereq course_elearn objects
}
course.setCategoryID((int)dr["categoryID"]); //7
course.setCourseCreator(userDAO.getUserByID((string)dr["courseCreator"]));
course.setHoursAwarded((double)dr["hoursAwarded"]);
if (!dr.IsDBNull(11))
{
course.setTargetAudience((string)dr["targetAudience"]);
}
toReturn.setMainCourse(course);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
