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