private static string GetChapterExamQuestionSqlString(int categoryId, bool isContainSelf, int topCount, int questionLibrary) { StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendFormat(" SELECT {1} FROM ( SELECT TOP {0} {1} ", topCount, EXAMQUESTIONFILEDLIST); sbSQL.Append(" FROM ExamQuestion "); sbSQL.Append(" INNER JOIN ( "); sbSQL.Append(ExamCategoryRepository.GetCategoryTableStringByCategoryId(categoryId, isContainSelf)); sbSQL.Append(" ) AS CategoryList "); sbSQL.Append(" ON ExamQuestion.CategoryID = CategoryList.CID "); sbSQL.AppendFormat(" WHERE QuestionLibrary = {0} ", questionLibrary); sbSQL.AppendFormat(" ORDER BY NEWID() ) AS a_{0} ", categoryId); return(sbSQL.ToString()); }
public static int GetQuestionCountByCategoryId(int driverId, int categoryId, QuestionType qt) { List <int> _categoryIdList = new List <int>(); IList <ExamCategory> examCategoryList = ExamCategoryRepository.GetExamCategoryListByParentId(categoryId, true); foreach (ExamCategory item in examCategoryList) { _categoryIdList.Add(item.CategoryID); } StringBuilder sbSQL = new StringBuilder(); sbSQL.Append(" SELECT COUNT(QuestionID) FROM ExamQuestion "); sbSQL.Append(" WHERE EXISTS ("); sbSQL.Append(" SELECT CID FROM ("); sbSQL.Append(ExamCategoryRepository.GetCategoryUnionAllTable(_categoryIdList)); sbSQL.Append(" ) AS C WHERE C.CID = ExamQuestion.CategoryID "); sbSQL.Append(" ) "); if (driverId == (int)DriverType.Moto) { //摩托车单独处理 sbSQL.Append(" AND ExamQuestion.QuestionLibrary = 2 "); } else { sbSQL.Append(" AND ExamQuestion.QuestionLibrary = 1 "); } if (qt == QuestionType.Choice) { sbSQL.Append(" AND ExamQuestion.QuestionType = 1 "); } if (qt == QuestionType.Judgmeng) { sbSQL.Append(" AND ExamQuestion.QuestionType = 2 "); } return(Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbSQL.ToString()))); }
/// <summary> /// 获得问题集合 /// </summary> /// <param name="driverId">驾驶证类型</param> /// <param name="provinceId">省份</param> /// <param name="qt">问题类型,选择题或者判断题,Null:说明包含全部题</param> /// <param name="categoryId">类别ID,可以是多个类别</param> /// <returns></returns> public static IList <ExamQuestion> GetExamQuestionList(int driverId, string categories, QuestionType qt, ExerciseType et) { string[] _categoryArray = categories.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); List <int> _categoryList = new List <int>(); IList <ExamQuestion> examQuestionList = new List <ExamQuestion>(); ExamQuestion examQuestion = null; StringBuilder sbSql = new StringBuilder(); sbSql.AppendFormat(" SELECT {0} ", EXAMQUESTIONFILEDLIST); sbSql.Append(" FROM ExamQuestion "); sbSql.Append(" INNER JOIN ( "); //判断是否根据用户选择分类来获取问题 foreach (string s in _categoryArray) { if (Utils.IsInt(s)) { int __i = Convert.ToInt32(s); if (__i > 0 && __i < 100) { //把0和分类ID大于100的排除掉 //因为总的分类ID也超不过100 _categoryList.Add(__i); } } } if (_categoryList.Count > 0) { sbSql.Append(ExamCategoryRepository.GetCategoryUnionAllTable(_categoryList)); } else { sbSql.Append(ExamCategoryRepository.GetCategoryTableStringByDriverType(driverId)); } sbSql.Append(" ) AS CategoryList "); sbSql.Append(" ON ExamQuestion.CategoryID = CategoryList.CID "); sbSql.Append(" WHERE 1 = 1 "); switch (driverId) { case 1: //小车 case 2: //客车专用 case 3: //货车专用 case 4: //吊车电瓶车 case 5: //地方试题 sbSql.Append(" AND ExamQuestion.QuestionLibrary = 1 "); break; case 6: //摩托车 sbSql.Append(" AND ExamQuestion.QuestionLibrary = 2 "); break; } if (qt == QuestionType.Choice) { sbSql.Append(" AND ExamQuestion.QuestionType = 1 "); } if (qt == QuestionType.Judgmeng) { sbSql.Append(" AND ExamQuestion.QuestionType = 2 "); } if (et == ExerciseType.Random) { sbSql.Append(" ORDER BY NEWID() "); } using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbSql.ToString())) { while (dr.Read()) { examQuestion = GetExamQuestionByQuestionId(Convert.ToInt32(dr["QuestionID"])); examQuestionList.Add(examQuestion); } } return(examQuestionList); }