public async Task <IEnumerable <Interviewee> > GetExamAttendedintervieweesBySessionIdAsync(long interviewSessionId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
var query = "SELECT * FROM dbo.IntervieweeView WHERE InterviewSessionId = @InterviewSessionId AND AttendedExam = @AttendedExam " +
"ORDER BY TotalMarksObtained DESC";
var result = await db.QueryAsync <Interviewee>(query, new
{
InterviewSessionId = interviewSessionId,
AttendedExam = 1
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> AddInterviewSessionAsync(InterviewSession model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
Console.WriteLine(model.CreatedBy);
await db.OpenAsync();
string optionQuery = "INSERT INTO dbo.InterviewSessions VALUES" +
" (@Title, @StartDate,@EndDate,@CreatedBy,@AuditTs,@Deleted)";
var result = await db.ExecuteAsync(optionQuery,
new
{
Title = model.Title,
StartDate = model.SessionStartDate,
EndDate = model.SessionEndDate,
CreatedBy = model.CreatedBy,
AuditTs = DateTime.Now,
Deleted = 0
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <bool> CheckJobExistsInSessionAsync(long sessionId, int jobTitleId, long examSetId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string query = "SELECT * FROM dbo.SessionwiseJobs WHERE InterviewSessionId = @sessionId " +
" AND JobTitleId = @jobTitleId AND ExamSetId = @examSetId";
var result = await db.QueryAsync <SessionwiseJob>(query,
new
{
sessionId = sessionId,
jobTitleId = jobTitleId,
examSetId = examSetId
});
bool exists = result.Any();
Console.WriteLine(exists);
return(exists);
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> DeleteOptionByQuestionIdAsync(long questionId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
Console.WriteLine("delete called");
await db.OpenAsync();
string questionQuery = "UPDATE dbo.ObjectiveQuestionOptions SET deleted = @delete WHERE QuestionId = @QuestionId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
QuestionId = questionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <bool> IntervieweeValidationAsync(string emailaddress, string contactnumber)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
var query = "SELECT * FROM dbo.InterviewSessionCandidate_view WHERE EmailAddress = @EmailAddress" +
"AND ContactNumber = @ContactNumber";
var result = await db.GetAsync <Interviewee>(query, new
{
EmailAddress = emailaddress,
ContactNumber = contactnumber
});
if (result != null)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> UpdatePasswordAsync(Password model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.Users SET " +
"Password = @Password," +
"PasswordChanged = @PasswordChanged " +
"WHERE EmailAddress = @EmailAddress";
var result = await db.ExecuteAsync(questionQuery,
new
{
Password = model.NewPassword,
PasswordChanged = 1,
EmailAddress = model.EmailAddress
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> DeleteAsync(long sessionwiseJobId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.SessionwiseJobs SET deleted = @delete WHERE SessionwiseJobId = @SessionwiseJobId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
SessionwiseJobId = sessionwiseJobId
});
Console.WriteLine(result);
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <SetQuestion> > GetQuestionsBySetIdAsync(long examSetId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionsForSetView " +
"WHERE ExamSetId = @examSetId " +
"ORDER BY QuestionTypeId, QuestionCategoryId,QuestionComplexityId";
var result = await db.QueryAsync <SetQuestion>(questionQuery, new
{
examSetId = examSetId
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
public static async Task <string> GetUserPasswordAsync(string emailAddress)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
db.Open();
string questionQuery = "SELECT password FROM dbo.Users " +
"WHERE EmailAddress = @EmailAddress";
var result = await db.QueryAsync <string>(questionQuery,
new
{
EmailAddress = emailAddress
});
return(result.FirstOrDefault());
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> UpdateAsync(InterviewSession model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string optionQuery = "UPDATE dbo.InterviewSessions SET " +
"Title = @Title," +
"SessionStartDate = @StartDate," +
"SessionEndDate = @EndDate," +
"AuditTs = @AuditTs " +
"WHERE InterviewSessionId = @IntervierwSessionId";
var result = await db.ExecuteAsync(optionQuery,
new
{
Title = model.Title,
StartDate = model.SessionStartDate,
EndDate = model.SessionEndDate,
AuditTs = DateTime.Now,
IntervierwSessionId = model.InterviewSessionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <ResultSummary> > ResultSummaryAsync(ResultSummary model)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.InterviewResultSummaryView WHERE " +
"(@IntervieweeId IS NULL OR IntervieweeId = @IntervieweeId) " +
"AND (@InterviewSessionId IS NULL OR InterviewSessionId = @InterviewSessionId) " +
"AND (EmailAddress LIKE @EmailAddress) " +
"AND (IntervieweeName LIKE @IntervieweeName)" +
" ORDER BY MarksObtained DESC";
var result = await db.QueryAsync <ResultSummary>(questionQuery,
new
{
IntervieweeId = model.IntervieweeId == 0 ? (int?)null : (int?)model.IntervieweeId,
InterviewSessionId = model.InterviewSessionId == 0 ? (int?)null : (int?)model.InterviewSessionId,
EmailAddress = "%" + model.EmailAddress + "%",
IntervieweeName = "%" + model.IntervieweeName + "%"
});
Console.WriteLine(questionQuery);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public static async Task <Interviewee> GetIntervieweeDetailAsync(string emailaddress, string contactnumber)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
string questionQuery = "SELECT * FROM dbo.InterviewSessionCandidate_view " +
"WHERE EmailAddress = @emailaddress " +
"AND ContactNumber = @contactNumber";
var result = await db.QueryAsync <Interviewee>(questionQuery,
new
{
emailaddress = emailaddress,
contactNumber = contactnumber
});
return(result.FirstOrDefault());
}
}
catch (Exception)
{
throw;
}
}
public static async Task <int> MarkAttendedExamAsync(long intervieweeId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "update Interviewees set AttendedExam = @Attended WHERE IntervieweeId = @IntervieweeId";
var result = await db.ExecuteAsync(questionQuery,
new
{
Attended = 1,
IntervieweeId = intervieweeId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> DeleteAsync(int jobTitleId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string deleteQuery = "UPDATE dbo.JobTitles SET deleted = @delete WHERE jobTitleId = @jobTitleId";
var result = await db.ExecuteAsync(deleteQuery,
new
{
delete = 1,
jobTitleId = jobTitleId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <ExamSet> > GetExamSetsByJobTitleAsync(long jobTitleId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string examSetsQuery = "SELECT * FROM ExamSets where JobTitleId = @jobTitleId AND Deleted = @deleted";
var result = await db.QueryAsync <ExamSet>(examSetsQuery,
new
{
jobTitleId = jobTitleId,
deleted = 0
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <SessionwiseJob> > GetJobsBySessionIdAsync(long sessionId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string query = "SELECT * FROM SessionJobView WHERE InterviewSessionId = @sessionId";
var result = await db.QueryAsync <SessionwiseJob>(query,
new
{
sessionId = sessionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> DeleteAsync(long UserId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.Users SET deleted = @delete WHERE UserId = @UserId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
UserId = UserId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <QuestionBanks> > SearchQuestionAsync(QuestionSearch model)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionBankView WHERE " +
"(@QuestionTypeId IS NULL OR QuestionTypeId = @QuestionTypeId) " +
"AND (@QuestionCategoryId IS NULL OR QuestionCategoryId = @QuestionCategoryId) " +
"AND (@QuestionComplexityId IS NULL OR QuestionComplexityId = @QuestionComplexityId) " +
"AND (Question LIKE @Question)";
var result = await db.QueryAsync <QuestionBanks>(questionQuery,
new
{
QuestionTypeId = model.QuestionTypeId == 0 ? (int?)null : (int?)model.QuestionTypeId,
QuestionCategoryId = model.QuestionCategoryId == 0 ? (int?)null : (int?)model.QuestionCategoryId,
QuestionComplexityId = model.QuestionComplexityId == 0 ? (int?)null : (int?)model.QuestionComplexityId,
Question = "%" + model.Question + "%"
});
Console.WriteLine(questionQuery);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> AddQuestionsAsync(SetQuestionViewModel model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
using (IDbTransaction tran = db.BeginTransaction())
{
try
{
string questionQuery = "UPDATE dbo.SetQuestions SET deleted = @delete WHERE ExamSetId = @ExamSetId";
var deleteResponse = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
ExamSetId = ((model.QuestionsForSet).FirstOrDefault()).ExamSetId
}, tran);
var result = 0;
foreach (var item in model.QuestionsForSet)
{
string optionQuery = "INSERT INTO dbo.SetQuestions VALUES" +
" (@ExamSetId, @QuestionId,@CreatedBy,@AuditTs,@Deleted)";
result = await db.ExecuteAsync(optionQuery,
new
{
ExamSetId = item.ExamSetId,
QuestionId = item.QuestionId,
CreatedBy = item.CreatedBy,
AuditTs = DateTime.Now,
Deleted = 0
}, tran);
}
tran.Commit();
return(result);
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <QuestionViewModel> > GetInterviewQuestionAnswerSheet(long intervieweeId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
List <QuestionViewModel> question = new List <QuestionViewModel>();
var query = "SELECT * FROM dbo.InterviewQuestions WHERE IntervieweeId = @IntervieweeId";
var result = await db.QueryAsync <QuestionBanks>(query, new
{
IntervieweeId = intervieweeId
});
foreach (var item in result)
{
// can also check if the question type is subjective or objective;
var optionsQuery = "SELECT * FROM dbo.ObjectiveQuestionOptions WHERE QuestionId = @QuestionId";
var options = await db.QueryAsync <ObjectiveQuestionOption>(optionsQuery, new
{
QuestionId = item.QuestionId
});
var answerQuery = "SELECT * FROM dbo.AnswersByInterviewees " +
"WHERE IntervieweeId = @IntervieweeId " +
"AND SetQuestionId = @SetQuestionId ";
var answer = await db.GetAsync <AnswersByInterviewees>(answerQuery, new
{
IntervieweeId = item.IntervieweeId,
SetQuestionId = item.SetQuestionId
});
Console.WriteLine(item.QuestionId);
Console.WriteLine(item.SetQuestionId);
var questionModel = new QuestionViewModel
{
Question = item,
Options = options,
Answers = answer
};
question.Add(questionModel);
}
return(question);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <QuestionViewModel> > GetinterviewQuestions(long intervieweeId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
List <QuestionViewModel> question = new List <QuestionViewModel>();
var query = "SELECT ROW_NUMBER() OVER (ORDER BY QuestionId) AS SN, dbo.GetTotalCorrectAnswerCount(QuestionId) as AnsCount, * FROM dbo.InterviewQuestions WHERE IntervieweeId = @IntervieweeId";
var result = await db.QueryAsync <QuestionBanks>(query, new
{
IntervieweeId = intervieweeId
});
var examSetQuery = "SELECT * FROM dbo.ExamSetForIntervieweeView WHERE IntervieweeId = @IntervieweeId";
var setInfo = await db.QueryAsync <ExamSet>(examSetQuery, new
{
IntervieweeId = intervieweeId
});
foreach (var item in result)
{
// can also check if the question type is subjective or objective;
var optionsQuery = "SELECT ObjectiveQuestionOptionId, QuestionId, AnswerOption, Attachment FROM dbo.ObjectiveQuestionOptions WHERE QuestionId = @QuestionId And Deleted = 0";
var options = await db.QueryAsync <ObjectiveQuestionOption>(optionsQuery, new
{
QuestionId = item.QuestionId
});
// item.AnsCount = (from z in options
// orderby z.QuestionId
// select z.AnsCount).Distinct().FirstOrDefault();
var questionModel = new QuestionViewModel
{
Question = item,
Options = options,
ExamSet = setInfo.FirstOrDefault()
};
question.Add(questionModel);
}
return(question);
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> SaveAnswerAsync(IEnumerable <AnswersByInterviewees> Answer)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
using (IDbTransaction tran = db.BeginTransaction())
{
try
{
var result = 0;
foreach (var item in Answer)
{
string optionQuery = "INSERT INTO dbo.AnswersByInterviewees VALUES" +
" (@IntervieweeId, @SetQuestionId,@subjectiveAnswer,@ObjectiveAnswer,@AnsweredBy,@AuditTs,@Deleted,@IsChecked)";
result = await db.ExecuteAsync(optionQuery,
new
{
IntervieweeId = item.IntervieweeId,
SetQuestionId = item.SetQuestionId,
subjectiveAnswer = item.subjectiveAnswer,
ObjectiveAnswer = item.ObjectiveAnswer,
AnsweredBy = item.AnsweredBy,
AuditTs = DateTimeOffset.UtcNow,
Deleted = 0,
IsChecked = 0
}, tran);
}
tran.Commit();
Console.WriteLine("result");
Console.WriteLine(result);
return(result);
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
}
catch (Exception)
{
throw;
}
}
}
public void Add(string name)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
db.Open();
var data = db.Query("select * from [dbo].[TestTable]");
Console.Write(data);
}
}
catch (System.Exception ex)
{
throw ex;
}
}
// public async Task<QuestionViewModel> FilterAnswerAsync(int questionType){
// }
public async Task CheckObjectiveAnswers(long intervieweeId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
DynamicParameters ObjParm = new DynamicParameters();
ObjParm.Add("@intervieweeId", intervieweeId);
db.Open();
await db.ExecuteAsync("sp_check_objective_answers", ObjParm, commandType : CommandType.StoredProcedure);
}
}
catch (Exception)
{
throw;
}
}
public async Task <dynamic> GetInterviewObjectiveAnswerSheetForExamineer(long intervieweeId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
List <InterviewQuestionAnswersheetForExamineerViewModel> question = new List <InterviewQuestionAnswersheetForExamineerViewModel>();
var query = "SELECT * FROM dbo.InterviewQuestionsToCheckAnswersView WHERE IntervieweeId = @IntervieweeId AND QuestionTypeId = @QuestionTypeId";
var result = await db.QueryAsync <InterviewQuestionsForExamineer>(query, new
{
IntervieweeId = intervieweeId,
QuestionTypeId = 2
});
foreach (var item in result)
{
// can also check if the question type is subjective or objective;
var optionsQuery = "SELECT * FROM dbo.ObjectiveOptionsWithAnswersByIntervieweesView WHERE SetQuestionId = @setQuestionId AND IntervieweeId = @IntervieweeId";
var options = await db.QueryAsync <ObjectiveQuestionOption>(optionsQuery, new
{
setQuestionId = item.SetQuestionId,
IntervieweeId = intervieweeId
});
var questionModel = new InterviewQuestionAnswersheetForExamineerViewModel
{
Question = item,
Options = options
};
question.Add(questionModel);
}
return(question);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <QuestionBanks> > SelectQuestionBankViewAsync()
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionBankView";
var result = await db.QueryAsync <QuestionBanks>(questionQuery);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <QuestionViewModel> GetQuestionByQuestionIdAsync(long questionId)
{
try
{
Console.WriteLine(questionId);
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionBankView " +
"WHERE questionId = @questionId";
var question = (await db.QueryAsync <QuestionBanks>(questionQuery, new
{
questionId = questionId
})).FirstOrDefault();
var questionModel = new QuestionViewModel();
questionModel.Question = question;
if (question.QuestionTypeId != 1)
{
string optionsQuery = "SELECT * FROM dbo.ObjectiveQuestionOptions " +
"WHERE QuestionId = @questionId " +
"AND Deleted = 0";
var options = await db.QueryAsync <ObjectiveQuestionOption>(optionsQuery, new
{
questionId = questionId
});
questionModel.Options = options;
}
return(questionModel);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <InterviewSession> > GetInterviewSessionHistory()
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
List <QuestionViewModel> question = new List <QuestionViewModel>();
var query = "SELECT * FROM dbo.InterviewSessionHistoryView";
var result = await db.QueryAsync <InterviewSession>(query);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <User> > GetAllActiveUsersAsync()
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM UsersView";
var result = await db.QueryAsync <User>(questionQuery);
return(result);
}
catch (Exception)
{
throw;
}
}
}
public static async Task <User> GetUserDetailAsync(string emailaddress)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
string questionQuery = "SELECT * FROM dbo.Users " +
"WHERE EmailAddress = @emailaddress ";
var result = await db.QueryAsync <User>(questionQuery,
new
{
emailaddress = emailaddress
});
return(result.FirstOrDefault());
}
}
catch (Exception)
{
throw;
}
}