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