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);
}
