/// <summary> /// This Methods provides to get current time from database /// </summary> /// <param name="sFormatNumber"> It takes format string to convert the sqlserver datetime </param> /// <returns> return result</returns> public Result GetCurrentTime(String sFormatNumber) { logger.Info("start GetCurrentTime Utility+GetCurrentTimeFromDataBase"); Result oResult = new Result(); DAOUtil oDAOUtil = new DAOUtil(); String sSelect = String.Empty; Object oObject = null; try { if (!sFormatNumber.Equals(String.Empty)) { sSelect = "select convert(varchar, getdate(), " + sFormatNumber + ") as currentTime"; oObject=oDAOUtil.GetExecuteScalar(sSelect); if (oObject != null) { oResult.ResultObject = oObject; oResult.ResultMessage = "Get time from DataBase success..."; oResult.ResultIsSuccess = true; } else { oResult.ResultIsSuccess = false; oResult.ResultMessage = "Get time from DataBase failed..."; } } else { sSelect = "select getdate() as currentTime"; } } catch (Exception oEx) { oResult.ResultIsSuccess = false; oResult.ResultMessage = "Exception at geting time from DataBase..."; oResult.ResultException = oEx; logger.Info("Esception GetCurrentTime Utility+GetCurrentTimeFromDataBase",oEx); } logger.Info("End GetCurrentTime Utility+GetCurrentTimeFromDataBase"); return oResult; }
/// <summary> /// This method get total possible time for answering questions which are set up for an exam /// </summary> /// <param name="oExam"> It takes Exam Object </param> /// <returns> It returns Result Object </returns> public Result GetTotalLastSetupQuestionTime(Exam oExam) { logger.Info("Start GetTotalLastSetupQuestionTime QuestionDAO+DAO"); Result oResult = new Result(); DAOUtil oDAOUtil = new DAOUtil(); String sSelect = String.Empty; Object oObject = new Object(); float fTotalTime = 0f; try { sSelect = "select sum(EX_Question.QuestionPossibleAnswerTime) from EX_Question inner join EX_QuestionGeneration on EX_Question.QuestionID = EX_QuestionGeneration.QuestionID where EX_QuestionGeneration.ExamID='" + oExam.ExamID + "'"; oObject = oDAOUtil.GetExecuteScalar(sSelect); if (oObject.ToString().Length > 0) { fTotalTime = float.Parse(oObject.ToString()); } oResult.ResultObject = fTotalTime; oResult.ResultMessage = "GetTotalLastSetupQuestionTime Success..."; oResult.ResultIsSuccess = true; } catch (Exception oEx) { oResult.ResultIsSuccess = false; oResult.ResultException = oEx; oResult.ResultMessage = "Exception occured during GetTotalLastSetupQuestionTime..."; logger.Info("Exception GetTotalLastSetupQuestionTime QuestionDAO+DAO", oEx); } logger.Info("End GetTotalLastSetupQuestionTime QuestionDAO+DAO"); return oResult; }
/// <summary> /// This Method Load Question Categories With Type of an exam For A Candidate. /// For example: (Category: Math,IQ,English,etc...) (Only Two Type: Objective, Descriptive) /// </summary> /// <param name="oCandidateForExam"> It takes CandidateForExam Object </param> /// <returns> It returns Result Object </returns> public Result LoadCategoriesWithType(CandidateForExam oCandidateForExam) { //new CLogger("Start LoadCategoriesWithType CandidateExamProcessDAO+DAO", FileNameManagerInDLL.sLogFileName, 1).WriteLog("Start LoadCategoriesWithType CandidateExamProcessDAO+DAO", ELogLevel.Debug); logger.Info("Start LoadCategoriesWithType CandidateExamProcessDAO+DAO"); Result oResult = new Result(); DAOUtil oDAOUtil = new DAOUtil(); SqlDataReader oSqlDataReader = null; String sSelect = String.Empty; List<CandidateMenu> oListCandidateMenu = new List<CandidateMenu>(); try { sSelect = "select distinct EX_Category.CategoryID, EX_Category.CategoryName from" +" EX_Category inner join EX_Question on EX_Category.CategoryID=EX_Question.QuestionCategoryID" +" inner join EX_QuestionGeneration on EX_QuestionGeneration.QuestionID=EX_Question.QuestionID" + " where EX_QuestionGeneration.ExamID='" + oCandidateForExam.CadidateCandidateExam.CandiadteExamExam.ExamID + "'" +" order by EX_Category.CategoryID asc"; oSqlDataReader = oDAOUtil.GetReader(sSelect); while (oSqlDataReader.Read()) { CandidateMenu oCandidateMenu = new CandidateMenu(); oCandidateMenu.CandidateMenuCategory.CategoryID = int.Parse(oSqlDataReader["CategoryID"].ToString()); oCandidateMenu.CandidateMenuCategory.CategoryName = oSqlDataReader["CategoryName"].ToString(); oListCandidateMenu.Add(oCandidateMenu); } oSqlDataReader.Close(); foreach (CandidateMenu oCandidateMenuInList in oListCandidateMenu) { sSelect = "select distinct EX_QuestionType.TypeID, EX_QuestionType.TypeName" +" from EX_QuestionType inner join EX_Question on EX_QuestionType.TypeID=EX_Question.QuestionTypeID" + " inner join EX_QuestionGeneration on EX_QuestionGeneration.QuestionID=EX_Question.QuestionID where EX_QuestionGeneration.ExamID='" + oCandidateForExam.CadidateCandidateExam.CandiadteExamExam.ExamID + "' and EX_Question.QuestionCategoryID ='" + oCandidateMenuInList.CandidateMenuCategory.CategoryID + "' order by EX_QuestionType.TypeID asc"; List<QuestionType> oListQuestionType = new List<QuestionType>(); oSqlDataReader = oDAOUtil.GetReader(sSelect); while (oSqlDataReader.Read()) { QuestionType oQuestionType = new QuestionType(); oQuestionType.QuestionTypeID = int.Parse(oSqlDataReader["TypeID"].ToString()); oQuestionType.QuestionTypeName = oSqlDataReader["TypeName"].ToString(); oListQuestionType.Add(oQuestionType); } oSqlDataReader.Close(); foreach (QuestionType oQuestionTypeInList in oListQuestionType) { sSelect = "select count(EX_Question.QuestionID) as TotalQuestions from EX_Question inner join EX_QuestionGeneration on EX_QuestionGeneration.QuestionID=EX_Question.QuestionID where EX_QuestionGeneration.ExamID='" + oCandidateForExam.CadidateCandidateExam.CandiadteExamExam.ExamID + "' and EX_Question.QuestionCategoryID ='" + oCandidateMenuInList.CandidateMenuCategory.CategoryID + "' and EX_Question.QuestionTypeID='" + oQuestionTypeInList.QuestionTypeID + "'"; oQuestionTypeInList.QuestionTypeTotalQuestions = int.Parse(oDAOUtil.GetExecuteScalar(sSelect).ToString()); } oCandidateMenuInList.CandidateMenuCategoryQuestionType = oListQuestionType; } oResult.ResultObject = oListCandidateMenu; oResult.ResultMessage = "Candidate Menu Category with type Load Success..."; oResult.ResultIsSuccess = true; } catch (Exception oEx) { oResult.ResultIsSuccess = false; oResult.ResultMessage = "Exception in Candidate Menu Category with type Load for Candidate..."; oResult.ResultException = oEx; logger.Info("Exception LoadCategoriesWithType CandidateExamProcessDAO+DAO", oEx); //new CLogger("Exception LoadCategoriesWithType CandidateExamProcessDAO+DAO", FileNameManagerInDLL.sLogFileName, 1).WriteLog("Exception LoadCategoriesWithType CandidateExamProcessDAO+DAO", ELogLevel.Debug, oEx); } finally { if (oSqlDataReader!=null && !oSqlDataReader.IsClosed) { oSqlDataReader.Close(); } } //new CLogger("Out LoadCategoriesWithType CandidateExamProcessDAO+DAO", FileNameManagerInDLL.sLogFileName, 1).WriteLog("Out LoadCategoriesWithType CandidateExamProcessDAO+DAO", ELogLevel.Debug); ; logger.Info("End LoadCategoriesWithType CandidateExamProcessDAO+DAO"); return oResult; }
/// <summary> /// This method sums the question categoty & question type wise obtain mark & total mark. /// </summary> /// <param name="oExam"> It takes Exam Object </param> /// <param name="oListCandidateMenu"> It takes List<CandidateMenu> Object </param> /// <returns> It returns Result Object </returns> public Result GetCandidateAVGMarksForTypeAndCategory(Exam oExam, List<CandidateMenu> oListCandidateMenu) { logger.Info("Start GetCandidateTotalMarksForTypeAndCategory ResultViewDAO+DAO"); Result oResult = new Result(); DAOUtil oDAOUtil = new DAOUtil(); String sSelect = String.Empty; SqlDataReader oSqlDataReader = null; //CandidateResultsView oCandidateResultsView = new CandidateResultsView(); //oCandidateResultsView.ListCandidates = new List<Candidate>(); //oCandidateResultsView.ListOfListCandidateResults = new List<List<CandidateMenu>>(); List<Candidate> oListCandidate = new List<Candidate>(); Object oObject = new Object(); try { DataTable dt = new DataTable(); DataColumn dcolName = new DataColumn("Name", typeof(System.String)); dt.Columns.Add(dcolName); float fTotalMarksForColumn = 0f; foreach (CandidateMenu oCandidateMenuInList in oListCandidateMenu) //columns { foreach (QuestionType oQuestionTypeInList in oCandidateMenuInList.CandidateMenuCategoryQuestionType) //columns { float fMarkForAColumn = 0f; object oObjectForMarkForAColumn = new object(); sSelect = " select sum(EX_QuestionGeneration.SetupQuestionMark) as TotalSetupMarkOfTypeOfCategory" + " from EX_QuestionGeneration inner join EX_Question on EX_QuestionGeneration.QuestionID=EX_Question.QuestionID" + " inner join EX_QuestionType on EX_QuestionType.TypeID = EX_Question.QuestionTypeID" + " where EX_QuestionGeneration.ExamID='"+oExam.ExamID+"'" + " and EX_Question.QuestionCategoryID='" + oCandidateMenuInList.CandidateMenuCategory.CategoryID + "'" + " and EX_QuestionType.TypeID='" + oQuestionTypeInList.QuestionTypeID + "'"; oObjectForMarkForAColumn = oDAOUtil.GetExecuteScalar(sSelect); if (oObjectForMarkForAColumn.ToString().Length <= 0) { fMarkForAColumn = 0f; } else { fMarkForAColumn = float.Parse(oObjectForMarkForAColumn.ToString()); } fTotalMarksForColumn = fTotalMarksForColumn + fMarkForAColumn; DataColumn dcolCategory = new DataColumn(oCandidateMenuInList.CandidateMenuCategory.CategoryName + "(" + oQuestionTypeInList.QuestionTypeName + ") = " + fMarkForAColumn.ToString(), typeof(System.Double)); dt.Columns.Add(dcolCategory); } } DataColumn dcolTotal = new DataColumn("Total = " + fTotalMarksForColumn.ToString(), typeof(System.Double)); dt.Columns.Add(dcolTotal); sSelect = "select distinct EX_CandidateExam.CandidateID,EX_Candidate.Name from" +" EX_Candidate inner join EX_CandidateExam on " +" EX_Candidate.CompositeCandidateID=EX_CandidateExam.CandidateID" +" where EX_CandidateExam.ExamID='"+oExam.ExamID+"'"; oSqlDataReader = oDAOUtil.GetReader(sSelect); while (oSqlDataReader.Read()) { Candidate oCandidate = new Candidate(); oCandidate.CandidateCompositeID = oSqlDataReader["CandidateID"].ToString(); oCandidate.CandidateName = oSqlDataReader["Name"].ToString(); oListCandidate.Add(oCandidate); } oSqlDataReader.Close(); //int iCandidateCount = 0; foreach (Candidate oCandidateInList in oListCandidate) { DataRow drow = dt.NewRow(); int iColumnIndex = 0; //********orginal*****// //drow["Name"] = oCandidateInList.CandidateName; drow[iColumnIndex++] = oCandidateInList.CandidateName; //*******end orginal**********// float fTotalMarks = 0f; //List<CandidateMenu> oListCandidateMenuForView = new List<CandidateMenu>(); //for (int i = 0; i < oListCandidateMenu.Count; i++) //{ // oListCandidateMenuForView.Add(oListCandidateMenu[i]); //} //oCandidateResultsView.ListOfListCandidateResults.Add(oListCandidateMenuForView); foreach (CandidateMenu oCandidateMenuInList in oListCandidateMenu) { //int iTypeCount = 0; foreach (QuestionType oQuestionTypeInList in oCandidateMenuInList.CandidateMenuCategoryQuestionType) { //sSelect = "select sum(EX_CandidateExam.ObtainMark) as AvgTypeMark, EX_QuestionType.TypeID,EX_QuestionType.TypeName from EX_QuestionType inner join EX_Question on EX_QuestionType.TypeID = EX_Question.QuestionTypeID inner join EX_QuestionGeneration on EX_Question.QuestionID= EX_QuestionGeneration.QuestionID inner join EX_CandidateExam on EX_QuestionGeneration.QuestionID = EX_CandidateExam.QuestionID group by EX_CandidateExam.ExamID,EX_CandidateExam.CandidateID,EX_Question.QuestionCategoryID,EX_QuestionType.TypeID,EX_QuestionType.TypeName //having EX_CandidateExam.ExamID='" + oExam.ExamID + "' and EX_CandidateExam.CandidateID='" + oCandidateInList.CandidateCompositeID + "' //and EX_Question.QuestionCategoryID='" + oCandidateMenuInList.CandidateMenuCategory.CategoryID + "' and EX_QuestionType.TypeID='" + oQuestionTypeInList.QuestionTypeID+ "'"; sSelect = "select sum(EX_CandidateExam.ObtainMark) as AvgTypeMark, EX_QuestionType.TypeID,EX_QuestionType.TypeName" +" from EX_CandidateExam inner join EX_Question on EX_CandidateExam.QuestionID=EX_Question.QuestionID" +" inner join EX_QuestionType on EX_QuestionType.TypeID = EX_Question.QuestionTypeID" + " where EX_CandidateExam.ExamID='" + oExam.ExamID + "' and" + " EX_CandidateExam.CandidateID='" + oCandidateInList.CandidateCompositeID + "'" +" and EX_Question.QuestionCategoryID='" + oCandidateMenuInList.CandidateMenuCategory.CategoryID + "'" + " and EX_QuestionType.TypeID='" + oQuestionTypeInList.QuestionTypeID + "'" +" group by EX_CandidateExam.ExamID,EX_CandidateExam.CandidateID,EX_Question.QuestionCategoryID," +" EX_QuestionType.TypeID,EX_QuestionType.TypeName"; oObject = oDAOUtil.GetExecuteScalar(sSelect); if (oObject.ToString().Length <= 0) { //oQuestionTypeInList.AVGQuestionTypeMark = 0f; //********orginal*****// //drow[oCandidateMenuInList.CandidateMenuCategory.CategoryName + "(" + oQuestionTypeInList.QuestionTypeName + ")"] = 0f; drow[iColumnIndex++] = 0f; //*******end orginal**********// fTotalMarks = fTotalMarks + 0f; //oCandidateMenuInList.CandidateMenuCategoryQuestionType[iTypeCount].AVGQuestionTypeMark = 0f; } else { //oQuestionTypeInList.AVGQuestionTypeMark = float.Parse(oObject.ToString()); //oCandidateMenuInList.CandidateMenuCategoryQuestionType[iTypeCount].AVGQuestionTypeMark = float.Parse(oObject.ToString()); //********orginal*****// //drow[oCandidateMenuInList.CandidateMenuCategory.CategoryName + "(" + oQuestionTypeInList.QuestionTypeName + ")"] = float.Parse(oObject.ToString()); drow[iColumnIndex++] = float.Parse(oObject.ToString()); //*******end orginal**********// fTotalMarks = fTotalMarks + float.Parse(oObject.ToString()); } //iTypeCount=iTypeCount+1; } } //iCandidateCount = iCandidateCount + 1; //oCandidateResultsView.ListOfListCandidateResults.Add(oListCandidateMenu); //********orginal*****// //drow["Total"] = fTotalMarks; drow[iColumnIndex++] = fTotalMarks; //*******end orginal**********// dt.Rows.Add(drow); } oResult.ResultObject = dt; oResult.ResultMessage = "GetCandidateAVGMarksForTypeAndCategory Success..."; oResult.ResultIsSuccess = true; } catch (Exception oEx) { oResult.ResultIsSuccess = false; oResult.ResultException = oEx; oResult.ResultMessage = "GetCandidateAVGMarksForTypeAndCategory Exception..."; logger.Info("Exception GetCandidateAVGMarksForTypeAndCategory ResultViewDAO+DAO", oEx); } finally { if (oSqlDataReader!=null && !oSqlDataReader.IsClosed) { oSqlDataReader.Close(); } } logger.Info("End GetCandidateAVGMarksForTypeAndCategory ResultViewDAO+DAO"); return oResult; }