public StudentDBServices updateQansC(RealetedTask rt) { SqlConnection con = null; try { con = connect("DBConnectionString"); da = new SqlDataAdapter("select * from AnsClose AS ac where ac.TaskId='" + rt.Task.QuizList[0].TaskId + "' and ac.StudentEmail='" + rt.StPerformer.Mail + "' and ac.QuestionnaireId='" + rt.Task.QuizList[0].QuizID + "' and ac.TeamId='" + rt.YearOfStudy + "'", con); SqlCommandBuilder builder = new SqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { if (con != null) { con.Close(); } } return(this); }
public int updateisWaitingPQ(RealetedTask rt) { int numEffected = 0; SqlConnection con = null; try { con = connect("DBConnectionString"); // create a connection to the database using the connection String defined in the web config file String UpdateSIquiz = "UPDATE PerformQuestionnaire Set isWaiting=1 where TaskId='" + rt.Task.QuizList[0].TaskId + "' and StudentId='" + rt.StPerformer.Mail + "' and QuestionnaireId='" + rt.Task.QuizList[0].QuizID + "' and TeamId='" + rt.YearOfStudy + "'"; SqlCommand cmd = new SqlCommand(UpdateSIquiz, con); numEffected += cmd.ExecuteNonQuery(); } catch (Exception ex) { // write to log throw (ex); } finally { if (con != null) { con.Close(); } } return(numEffected);; }
private int getNum(RealetedTask arg) { int i = -1; int num = 0; foreach (var a in arg.Task.QuizList) { i++; if (a.TaskId == "1") { num = i; break; } } return(num); }
public int insertperformQuestionnaire(RealetedTask rt) { int numEffected = 0; SqlConnection con; SqlCommand cmd; String cStr = ""; string sqlFormattedDate = rt.STime.ToString("yyyy-MM-dd HH:mm:ss.fff"); try { con = connect("DBConnectionString"); // create the connection } catch (Exception ex) { // write to log throw (ex); } cStr = BuildPQInsertCommand(rt.Task.QuizList[0], rt.StPerformer, rt.YearOfStudy, sqlFormattedDate); cmd = CreateCommand(cStr, con); // create the command try { numEffected += cmd.ExecuteNonQuery(); // execute the command } catch (Exception ex) { if (con != null) { // close the db connection con.Close(); } // write to log throw (ex); } finally { if (con != null) { // close the db connection con.Close(); } } return(numEffected); }
public void deletePQ(Quiz del, RealetedTask rt) { int numEffected = 0; SqlConnection con; SqlCommand cmd; try { con = connect("DBConnectionString"); // create the connection } catch (Exception ex) { // write to log throw (ex); } StringBuilder sb = new StringBuilder(); sb.AppendFormat("Delete from PerformQuestionnaire where [QuestionnaireId]='{0}' and [TaskId]='{1}' and [StudentId]='{2}' and [TeamId]='{3}'", del.QuizID, rt.Task.TaskId, rt.StPerformer.Mail, rt.YearOfStudy);; String cStr = sb.ToString(); cmd = CreateCommand(cStr, con); // create the command try { numEffected += cmd.ExecuteNonQuery(); // execute the command } catch (Exception ex) { if (con != null) { // close the db connection con.Close(); } // write to log throw (ex); } finally { if (con != null) { // close the db connection con.Close(); } } }
public int validateTime(RealetedTask rt) { SqlConnection con = null; List <Inteligence> list = new List <Inteligence>(); int num = 0; try { con = connect("DBConnectionString"); // create a connection to the database using the connection String defined in the web config file int qnum = getNum(rt); String selectSTR = "select* from [dbo].[PerformQuestionnaire] as pq inner join RealatedTo as rt on pq.TaskId=rt.TaskId and rt.TaskId='" + rt.Task.TaskId + "' and pq.StudentId='" + rt.Task.Title + "' and pq.[QuestionnaireId]='" + rt.Task.QuizList[qnum].QuizID + "' where (getDate()>=rt.ForDate) and (getDate()<rt.[OpenTill] or getDate()<pq.Ptime)"; SqlCommand cmd = new SqlCommand(selectSTR, con); // get a reader SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end if (dr.HasRows) { if (con != null) { con.Close(); } return(1); } else { con.Close(); dr = null; cmd.Connection = null; con = connect("DBConnectionString"); String selectSTR2 = "select* from RealatedTo as rt where (getDate()>=rt.ForDate) and (getDate()<rt.[OpenTill]) and rt.TaskId='" + rt.Task.TaskId + "' "; SqlCommand cmd2 = new SqlCommand(selectSTR2, con); SqlDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { if (con != null) { con.Close(); } return(1); } } } catch (Exception ex) { // write to log throw (ex); } finally { if (con != null) { con.Close(); } } return(num); }
public int postQ(RealetedTask rt) { int numEffected = 0; SqlConnection con; SqlCommand cmd; String cStr = ""; numEffected += insertperformQuestionnaire(rt); try { con = connect("DBConnectionString"); // create the connection } catch (Exception ex) { // write to log throw (ex); } foreach (var item in rt.Task.QuizList[0].Question) { if (item.Type == "A" || item.Type == "M") { foreach (var a in item.Answer) { if (a.IsPicked) { cStr = BuildAnsCInsertCommand(rt.Task.QuizList[0], item, a, rt.YearOfStudy, rt.StPerformer); // helper method to build the insert string cmd = CreateCommand(cStr, con); // create the command try { numEffected += cmd.ExecuteNonQuery(); // execute the command } catch (Exception ex) { if (con != null) { // close the db connection con.Close(); } // write to log throw (ex); } } } } else { cStr = BuildAnsOInsertCommand(rt.Task.QuizList[0], item, rt.YearOfStudy, rt.StPerformer); // helper method to build the insert string cmd = CreateCommand(cStr, con); // create the command try { numEffected += cmd.ExecuteNonQuery(); // execute the command } catch (Exception ex) { if (con != null) { // close the db connection con.Close(); } // write to log throw (ex); } } } if (con != null) { // close the db connection con.Close(); } return(numEffected); }
public List <RealetedTask> getSTasks(string userEmail, string teamId) { SqlConnection con = null; List <RealetedTask> rtList = new List <RealetedTask>(); try { con = connect("DBConnectionString"); // create a connection to the database using the connection String defined in the web config file String part1 = " select tbl.taskId,tbl.Title,tbl.SubjectName,tbl.Date_Assignment,tbl.ForDate,tbl.OpenTill,tbl.YearOfStudy,tbl.IntelligenceName,tbl.[Name],tbl.creationTime,tbl.QuestionnaireId"; String part2 = " ,pio.points,pio.Spoints,quest.QuestionId,quest.Content,quest.[Type],quest.OrderNum,quest.ImgLink,quest.VideoLink,a.Content AS ans_content,a.IsRight,a.AnswerId ,ac.[AnswerId] as picked,ao.[FileLink],ao.[Answer],pq.[Note],pq.Ptime,pq.Grade, "; String part3 = " case when pq.[StudentId]='" + userEmail + "' then 1 else 0 END as IsChoose "; String part4 = " from (select t.taskId,t.Title,t.SubjectName,rt.Date_Assignment,rt.ForDate,rt.OpenTill,rt.YearOfStudy,q.IntelligenceName,i.[Name],max(q.creationTime)as creationTime,max(q.QuestionnaireId) as QuestionnaireId "; String part5 = " from task as t inner join RealatedTo rt on rt.TaskId=t.TaskId and rt.TeamId='" + teamId + "' inner join Questionnaire as q on q.TaskId=rt.TaskId and rt.Date_Assignment>q.creationTime "; String part6 = " inner join Intelligence as i on q.IntelligenceName=i.[EnglishName] group by t.taskId,t.Title,t.SubjectName,q.IntelligenceName,i.[Name],rt.Date_Assignment,rt.ForDate,rt.OpenTill,rt.YearOfStudy)as tbl "; String part7 = " inner join Question as quest on quest.QuestionnaireId=tbl.QuestionnaireId inner join PointsInIntelligence as pio on pio.IntelligenceName=tbl.IntelligenceName and pio.StudentEmail='" + userEmail + "' "; String part8 = " left join Answer as a on a.QuestionId=quest.QuestionId left join [dbo].[PerformQuestionnaire] as pq on pq.[QuestionnaireId]=tbl.[QuestionnaireId] and pq.[StudentId]='" + userEmail + "' and pq.TeamId='" + teamId + "' "; String part9 = " left join [dbo].[AnsClose] as ac on ac.[AnswerId]=a.[AnswerId] and ac.StudentEmail='" + userEmail + "' left join [dbo].[AnsOpen] as ao on ao.[QuestionId]=quest.[QuestionId] and ao.StudentEmail='" + userEmail + "' order by tbl.ForDate,tbl.TaskId,tbl.IntelligenceName,points "; String selectSTR = part1 + part2 + part3 + part4 + part5 + part6 + part7 + part8 + part9; SqlCommand cmd = new SqlCommand(selectSTR, con); // get a reader SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end RealetedTask rt = new RealetedTask(); rt.Task = new Task(); rt.Task.QuizList = new List <Quiz>(); Quiz q = new Quiz(); Question que = new Question(); while (dr.Read()) { // Read till the end of the data into a row if (dr["TaskId"].ToString() != rt.Task.TaskId) { if (rt.Task.TaskId != null) { rt.Task.QuizList.Add(q); rtList.Add(rt); } rt = new RealetedTask(); q = new Quiz(); rt.Task = new Task(); rt.Task.QuizList = new List <Quiz>(); rt.Task.Grade = Convert.ToInt32(dr["IsChoose"]); rt.Task.TaskId = dr["TaskId"].ToString(); rt.Task.Title = dr["Title"].ToString(); rt.Task.Grade = 0; rt.Task.Sub = new Subject(dr["SubjectName"].ToString(), ""); rt.YearOfStudy = dr["YearOfStudy"].ToString(); rt.ForDate = Convert.ToDateTime(dr["ForDate"]); rt.TillDate = Convert.ToDateTime(dr["OpenTill"]); rt.AssigmentDate = Convert.ToDateTime(dr["Date_Assignment"]); } if (dr["Note"] != DBNull.Value) { rt.Note = dr["Note"].ToString(); } if (dr["Grade"] != DBNull.Value) { rt.Score = Convert.ToInt32(dr["Grade"]); } if (dr["QuestionnaireId"].ToString() != q.QuizID) { if (q.QuizID != null) { rt.Task.QuizList.Add(q); } q = new Quiz(); q.Question = new List <Question>(); q.QuizID = dr["QuestionnaireId"].ToString(); q.TaskId = dr["IsChoose"].ToString(); if (q.TaskId == "1") { if (dr["Ptime"] != DBNull.Value) { rt.STime = Convert.ToDateTime(dr["Ptime"]); } rt.Task.Grade = 1; } q.Inteligence = new Inteligence(Convert.ToInt32(dr["points"]), dr["Name"].ToString(), dr["IntelligenceName"].ToString(), 0); if (dr["Spoints"] != DBNull.Value) { q.Inteligence.Spoints = Convert.ToInt32(dr["Spoints"]); } } if (dr["QuestionId"].ToString() != que.QuestionId) { que = new Question(dr["Type"].ToString(), dr["Content"].ToString(), new List <Answer>(), dr["ImgLink"].ToString(), dr["VideoLink"].ToString(), Convert.ToInt32(dr["OrderNum"]), dr["QuestionId"].ToString()); que.Answer = new List <Answer>(); q.Question.Add(que); } if (dr["ans_content"] != DBNull.Value) { que.Answer.Add(new Answer(dr["ans_content"].ToString(), Convert.ToBoolean(dr["IsRight"]), Convert.ToInt32(dr["AnswerId"]))); if (dr["picked"] != DBNull.Value) { que.Answer[que.Answer.Count - 1].IsPicked = true; } else { que.Answer[que.Answer.Count - 1].IsPicked = false; } } else if (dr["Answer"] != DBNull.Value || dr["FileLink"] != DBNull.Value) { if (dr["FileLink"] != DBNull.Value) { que.AnsContent = dr["FileLink"].ToString(); } else { que.AnsContent = dr["Answer"].ToString(); } } } if (rt.Task.TaskId != null) { rt.Task.QuizList.Add(q); rtList.Add(rt); } } catch (Exception ex) { // write to log throw (ex); } finally { if (con != null) { con.Close(); } } return(rtList); }
public int postQ([FromBody] RealetedTask rt) { rt.STime = DateTime.Now; return(rt.postQ()); }
public List <Dictionary <string, string> > getStTasksInTeam(Dictionary <string, string> info) { RealetedTask rt = new RealetedTask(); return(rt.getStTasksInTeam(info)); }
public int updateQFB([FromBody] Dictionary <string, string> qfb) { RealetedTask rt = new RealetedTask(); return(rt.updateQFB(qfb)); }
public List <Dictionary <string, string> > getTTasks(string data) { RealetedTask rt = new RealetedTask(); return(rt.getTTasks(data)); }
public int changeQ([FromBody] RealetedTask rt) { return(rt.changeQ()); }
public int validateTime(RealetedTask rt) { return(rt.validateTime()); }
public int updateQ([FromBody] RealetedTask rt) { Quiz q = new Quiz(); return(q.updateQ(rt)); }
public List <RealetedTask> getSTasks(string data) { RealetedTask rt = new RealetedTask(); return(rt.getSTasks(data)); }
public int deleteAssigment([FromBody] Dictionary <string, string> info) { RealetedTask rt = new RealetedTask(); return(rt.deleteAssigment(info)); }
public List <RealetedTask> getTTasks(string teamId) { SqlConnection con = null; List <RealetedTask> rtList = new List <RealetedTask>(); try { con = connect("DBConnectionString"); // create a connection to the database using the connection String defined in the web config file String part1 = " select tbl.taskId,tbl.Title,tbl.SubjectName,tbl.Date_Assignment,tbl.ForDate,tbl.OpenTill,tbl.YearOfStudy"; String part2 = " from (select t.taskId,t.Title,t.SubjectName,rt.Date_Assignment,rt.ForDate,rt.OpenTill,rt.YearOfStudy "; String part3 = " from task as t inner join RealatedTo rt on rt.TaskId=t.TaskId and rt.TeamId= '" + teamId + "' "; String part4 = " group by t.taskId,t.Title,t.SubjectName,rt.Date_Assignment,rt.ForDate,rt.OpenTill,rt.YearOfStudy)as tbl "; String selectSTR = part1 + part2 + part3 + part4; SqlCommand cmd = new SqlCommand(selectSTR, con); // get a reader SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end RealetedTask rt = new RealetedTask(); rt.Task = new Task(); while (dr.Read()) { // Read till the end of the data into a row if (dr["TaskId"].ToString() != rt.Task.TaskId) { if (rt.Task.TaskId != null) { //rt.Task.QuizList.Add(q); rtList.Add(rt); } rt = new RealetedTask(); rt.Task = new Task(); //rt.Task.QuizList = new List<Quiz>(); //rt.Task.Grade = Convert.ToInt32(dr["IsChoose"]); rt.Task.TaskId = dr["TaskId"].ToString(); rt.Task.Title = dr["Title"].ToString(); rt.Task.Grade = 0; //rt.Task.Sub = new Subject(dr["SubjectName"].ToString(), ""); rt.YearOfStudy = dr["YearOfStudy"].ToString(); rt.ForDate = Convert.ToDateTime(dr["ForDate"]); rt.TillDate = Convert.ToDateTime(dr["OpenTill"]); rt.AssigmentDate = Convert.ToDateTime(dr["Date_Assignment"]); } //if (dr["QuestionnaireId"].ToString() != q.QuizID) //{ // if (q.QuizID != null) // { // rt.Task.QuizList.Add(q); // } //q = new Quiz(); //q.Question = new List<Question>(); //q.QuizID = dr["QuestionnaireId"].ToString(); //q.TaskId = dr["IsChoose"].ToString(); //if (q.TaskId == "1") //{ // rt.Task.Grade = 1; //} //q.Inteligence = new Inteligence(Convert.ToInt32(dr["points"]), dr["Name"].ToString(), dr["IntelligenceName"].ToString()); //} //if (dr["QuestionId"].ToString() != que.QuestionId) //{ // que = new Question(dr["Type"].ToString(), dr["Content"].ToString(), new List<Answer>(), dr["ImgLink"].ToString(), dr["VideoLink"].ToString(), Convert.ToInt32(dr["OrderNum"]), dr["QuestionId"].ToString()); // que.Answer = new List<Answer>(); // q.Question.Add(que); //} //if (dr["ans_content"] != DBNull.Value) //{ // que.Answer.Add(new Answer(dr["ans_content"].ToString(), Convert.ToBoolean(dr["IsRight"]), Convert.ToInt32(dr["AnswerId"]))); // if (dr["picked"] != DBNull.Value) // { // que.Answer[que.Answer.Count - 1].IsPicked = true; // } // else // { // que.Answer[que.Answer.Count - 1].IsPicked = false; // } //} //else if (dr["Answer"] != DBNull.Value || dr["FileLink"] != DBNull.Value) //{ // if (dr["FileLink"] != DBNull.Value) // { // que.AnsContent = dr["FileLink"].ToString(); // } // else // { // que.AnsContent = dr["Answer"].ToString(); // } //} } rtList.Add(rt); } catch (Exception ex) { // write to log throw (ex); } finally { if (con != null) { con.Close(); } } return(rtList); }