public List <PDQuestionsByUser> GetPDQuestionByUserID(int uid, int langid) { try { int testid = 10; int batid = 3; int total_answers = 0; int qid = 0; bool flag = false; List <PDQuestionsByUser> questions = new List <PDQuestionsByUser>(); using (SqlConnection con = new SqlConnection(connectionString)) { string QueryData = "select count(c_id) as total_answers from tblPersonalityCandAnswers where c_id = " + uid + " and batid=" + batid + ""; SqlCommand cmd = new SqlCommand(QueryData, con); DataSet ds = new DataSet(); con.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { total_answers = Convert.ToInt32(ds.Tables[0].Rows[0][0]); } ds.Clear(); ds.Dispose(); con.Close(); // if candidate is giving the test for the first time if (total_answers == 0) { if (qid == 0) { qid = 1; } } else { // if session expires or candidate log out then resume the test when he log in again if (total_answers < 24) { QueryData = ""; QueryData = "select q_id from tblPersonalityCandAnswers where c_id= " + uid + " and batid=" + batid + " order by q_id desc"; SqlCommand cmd1 = new SqlCommand(QueryData, con); DataSet ds1 = new DataSet(); con.Open(); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); da1.Fill(ds1); if (ds1.Tables[0].Rows.Count > 0) { // HwordId.Value = (Convert.ToInt32(ds.Tables[0].Rows[0][10]) + 4).ToString(); qid = (Convert.ToInt32(ds1.Tables[0].Rows[0][0]) + 1); } ds1.Clear(); ds1.Dispose(); con.Close(); } else { PDQuestionsByUser obj = new PDQuestionsByUser(); UserTestStatus userTestStatus = new UserTestStatus(); userTestStatus.batid = batid; userTestStatus.testid = testid; userTestStatus.uId = uid; userTestStatus.testStatus = "Complete"; userTestStatus.factorStatus = "Complete"; if (SubmitUserTestStatus(userTestStatus)) { flag = true; } obj.CompleteFlag = flag; questions.Add(obj); return(questions); } } if (Convert.ToInt32(qid) > 24) { PDQuestionsByUser obj = new PDQuestionsByUser(); UserTestStatus userTestStatus = new UserTestStatus(); userTestStatus.batid = batid; userTestStatus.testid = testid; userTestStatus.uId = uid; userTestStatus.testStatus = "Complete"; userTestStatus.factorStatus = "Complete"; if (SubmitUserTestStatus(userTestStatus)) { flag = true; } obj.CompleteFlag = flag; questions.Add(obj); return(questions); } else { QueryData = ""; QueryData = "SELECT qno,questext,M as Most,L as Least,Status,q_id from tblPersonalityQuestions where q_id=" + qid + " and langid=" + langid + ""; SqlCommand cmd2 = new SqlCommand(QueryData, con); DataSet ds2 = new DataSet(); con.Open(); SqlDataAdapter da2 = new SqlDataAdapter(cmd2); da2.Fill(ds2); if (ds2.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds2.Tables[0].Rows.Count; i++) { PDQuestionsByUser question = new PDQuestionsByUser(); question.qno = Convert.ToInt32(ds2.Tables[0].Rows[i]["qno"]); question.questext = ds2.Tables[0].Rows[i]["questext"].ToString(); question.Most = ds2.Tables[0].Rows[i]["Most"].ToString(); question.Least = ds2.Tables[0].Rows[i]["Least"].ToString(); question.Status = ds2.Tables[0].Rows[i]["Status"].ToString(); question.q_id = Convert.ToInt32(ds2.Tables[0].Rows[i]["q_id"]); question.CompleteFlag = false; questions.Add(question); } return(questions); } else { return(null); } } } } catch (Exception ex) { Log.Error(ex); return(null); } }
// submit user test status after test complete private Boolean SubmitUserTestStatus(UserTestStatus userTestStatus) { Boolean flag = false; try { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd1 = new SqlCommand("SELECT count(id)as CandAnsCnt FROM tblPersonalityCandAnswers where c_id=" + userTestStatus.uId + " and batid=" + userTestStatus.batid, con); con.Open(); SqlDataReader sdr = cmd1.ExecuteReader(); if (sdr.HasRows) { int CandAnsCnt = 0; while (sdr.Read()) { CandAnsCnt = Convert.ToInt32(sdr["CandAnsCnt"]); } con.Close(); if (CandAnsCnt == 24) { con.Open(); int intEffectedRows = 0; SqlDataAdapter getstatus = new SqlDataAdapter("SELECT * FROM tblUserTestMaster WHERE batid=" + userTestStatus.batid + " and uId = " + userTestStatus.uId + " and testid=" + userTestStatus.testid, con); DataSet dsstatus = new DataSet(); getstatus.Fill(dsstatus); if (dsstatus.Tables[0].Rows.Count == 0) { string strcmd = "INSERT INTO tblUserTestMaster(uId,testid,batid,testStatus,factorStatus,dateofcomplete) VALUES (@uId,@testid,@batid,@testStatus,@factorStatus,@dateofcomplete)"; SqlCommand cmd = new SqlCommand(strcmd, con); cmd.Parameters.AddWithValue("@uId", userTestStatus.uId); cmd.Parameters.AddWithValue("@testid", userTestStatus.testid); cmd.Parameters.AddWithValue("@batid", userTestStatus.batid); cmd.Parameters.AddWithValue("@testStatus", userTestStatus.testStatus); cmd.Parameters.AddWithValue("@factorStatus", userTestStatus.factorStatus); cmd.Parameters.AddWithValue("@dateofcomplete", DateTime.Now); intEffectedRows = cmd.ExecuteNonQuery(); flag = true; } else { string strcmd = "update tblUserTestMaster set testStatus=@testStatus,factorStatus=@factorStatus,dateofcomplete=@dateofcomplete where uId=@uId and testid=@testid and batid=@batid"; SqlCommand cmd = new SqlCommand(strcmd, con); cmd.Parameters.AddWithValue("@uId", userTestStatus.uId); cmd.Parameters.AddWithValue("@testid", userTestStatus.testid); cmd.Parameters.AddWithValue("@batid", userTestStatus.batid); cmd.Parameters.AddWithValue("@testStatus", userTestStatus.testStatus); cmd.Parameters.AddWithValue("@factorStatus", userTestStatus.factorStatus); cmd.Parameters.AddWithValue("@dateofcomplete", DateTime.Now); intEffectedRows = cmd.ExecuteNonQuery(); flag = true; } con.Close(); } } } return(flag); } catch (Exception ex) { Log.Error("" + ex); return(flag); } }
public static object UserTestsView(UrlHelper url, List <UserTest> userTests) { return(table.Class("defaultTable")[Head("ФИО", "Время", "Дата", "Статус"), userTests.Select(x => Row( x.User.FullName, TimeSpan.FromSeconds(x.Time).ToString("mm\\:ss"), x.RunDate.DefaultWithTimeString(), url.UserTestLink(x, UserTestStatus.GetName(x.Status)).OpenInUiDialog()) )]); }
//User PD Test answers submit 24 public Boolean SubmitPDTest(int c_id, PDAnswers[] pdAnswers) { Boolean flag = false; try { using (SqlConnection con = new SqlConnection(connectionString)) { //PD Test Answer Table DataTable answertbl = new DataTable(); answertbl.Columns.Add("c_id", typeof(int)); answertbl.Columns.Add("q_id", typeof(int)); answertbl.Columns.Add("most_qno", typeof(int)); answertbl.Columns.Add("least_qno", typeof(int)); answertbl.Columns.Add("most_code", typeof(string)); answertbl.Columns.Add("least_code", typeof(string)); answertbl.Columns.Add("most_status", typeof(string)); answertbl.Columns.Add("least_status", typeof(string)); answertbl.Columns.Add("batid", typeof(int)); for (int j = 0; j < pdAnswers.Length; j++) { int q_id = Convert.ToInt32(pdAnswers[j].qid); int most_qid = Convert.ToInt32(pdAnswers[j].Mostqno); int least_qid = Convert.ToInt32(pdAnswers[j].Leastqno); string most_code = pdAnswers[j].MostCode; string least_code = pdAnswers[j].LeastCode; string most_status = pdAnswers[j].MostStatus; string least_status = pdAnswers[j].LeastStatus; int batid = Convert.ToInt32(pdAnswers[j].batid); answertbl.Rows.Add(c_id, q_id, most_qid, least_qid, most_code, least_code, most_status, least_status, batid); } //old comment on 17-7-2020 by bhagyashri //SqlCommand cmd = new SqlCommand("SELECT * FROM tblPersonalityCandAnswers where c_id=" + c_id + " and batid=" + pdAnswers[0].batid, con); //con.Open(); //SqlDataReader sdr = cmd.ExecuteReader(); //if (sdr.HasRows) //{ // sdr.Close(); // Log.Info("PD Test is already complete..!!"); // flag = true; //} //else //{ // sdr.Close(); // if (answertbl.Rows.Count == 24) // { // using (var bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) // { // foreach (DataColumn dc in answertbl.Columns) // { // bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); // } // bulkcopy.BulkCopyTimeout = 100000; // bulkcopy.DestinationTableName = "tblPersonalityCandAnswers"; // bulkcopy.WriteToServer(answertbl); // } // // insert user test status // UserTestStatus userTestStatus = new UserTestStatus(); // userTestStatus.uId = c_id; // userTestStatus.testid = 10; // userTestStatus.batid = Convert.ToInt32(pdAnswers[0].batid); // userTestStatus.testStatus = "Complete"; // userTestStatus.factorStatus = "Complete"; // userTestStatus.dataofcomplete = DateTime.Now; // if (SubmitUserTestStatus(userTestStatus)) // flag = true; // } //} //old comment complete //New code by bhagyashri #region New code SqlCommand cmd = new SqlCommand("SELECT count(id)as CandAnsCnt FROM tblPersonalityCandAnswers where c_id=" + c_id + " and batid=" + pdAnswers[0].batid, con); con.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.HasRows) { int CandAnsCnt = 0; while (sdr.Read()) { CandAnsCnt = Convert.ToInt32(sdr["CandAnsCnt"]); } if (CandAnsCnt == 24) { Log.Info("PD Test is already complete..!!"); sdr.Close(); flag = true; } else { if (answertbl.Rows.Count < 24) { using (var bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { foreach (DataColumn dc in answertbl.Columns) { bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } bulkcopy.BulkCopyTimeout = 100000; bulkcopy.DestinationTableName = "tblPersonalityCandAnswers"; bulkcopy.WriteToServer(answertbl); } flag = true; } else if (answertbl.Rows.Count == 24) { using (var bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { foreach (DataColumn dc in answertbl.Columns) { bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } bulkcopy.BulkCopyTimeout = 100000; bulkcopy.DestinationTableName = "tblPersonalityCandAnswers"; bulkcopy.WriteToServer(answertbl); } // insert user test status UserTestStatus userTestStatus = new UserTestStatus(); userTestStatus.uId = c_id; userTestStatus.testid = 10; userTestStatus.batid = Convert.ToInt32(pdAnswers[0].batid); userTestStatus.testStatus = "Complete"; userTestStatus.factorStatus = "Complete"; userTestStatus.dataofcomplete = DateTime.Now; if (SubmitUserTestStatus(userTestStatus)) { flag = true; } } } } else { if (answertbl.Rows.Count <= 24) { using (var bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { foreach (DataColumn dc in answertbl.Columns) { bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } bulkcopy.BulkCopyTimeout = 100000; bulkcopy.DestinationTableName = "tblPersonalityCandAnswers"; bulkcopy.WriteToServer(answertbl); } // insert user test status UserTestStatus userTestStatus = new UserTestStatus(); userTestStatus.uId = c_id; userTestStatus.testid = 10; userTestStatus.batid = Convert.ToInt32(pdAnswers[0].batid); userTestStatus.testStatus = "Complete"; userTestStatus.factorStatus = "Complete"; userTestStatus.dataofcomplete = DateTime.Now; if (SubmitUserTestStatus(userTestStatus)) { flag = true; } } } #endregion } return(flag); } catch (Exception ex) { Log.Error("" + ex); return(flag); } }
public List <List <string> > GetResultData(List <List <UserTest> > userTests, bool trackTest = false) { var items = _.List("Тест", "Фио", "Результат", "Ответы(П \\ Н)", "Дата", "Модули:"); var data = new List <List <string> >(); foreach (var userTestList in userTests) { if (!userTestList.Any()) { continue; } var modules = TestModuleService.GetForTest(userTestList.First().TestId).OrderBy(x => x.Id).ToList(); data.Add(new List <string>(items).AddFluent(modules.Select(x => x.Name)));; foreach (var userTest in userTestList) { var stats = EntityUtils.GetStats(userTest); data.Add(_.List(userTest.Test.Name, userTest.User.FullName, trackTest ? UserTestStatus.TrackNames[userTest.Status] : UserTestStatus.GetName(userTest.Status), new UserTestStats.RightWrong { R = userTest.RightCount.GetValueOrDefault(), W = userTest.WrongCount.GetValueOrDefault() }.ToString(), userTest.RunDate.DefaultWithTimeString()).AddFluent("").AddFluent( stats.Select(x => x.Value.ToString()))); } } return(data); }