/// <summary>
/// 获取话题列表
/// </summary>
/// <param name="gradeId"></param>
/// <param name="subjectId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <dto_Topic> GetTopics(int gradeId, int subjectId, int pageIndex, int pageSize, out int totalCount)
{
string tcsql = "T_Topic where Deleted = 0 and Blocked = 0 ";
List <MySqlParameter> pl = new List <MySqlParameter>();
if (gradeId != 0)
{
tcsql += "and GradeId = @GradeId ";
pl.Add(new MySqlParameter("@GradeId", gradeId));
}
if (subjectId != 0)
{
tcsql += "and SubjectId = @SubjectId ";
pl.Add(new MySqlParameter("@SubjectId", subjectId));
}
List <dto_Topic> model = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(BBSConnString),
"Id, UserId, Invites, Title, TopicContent, TopicText, CreateDate, Good, Hit, ReplyCount, GradeId, SubjectId, Ip",
tcsql,
"CreateDate desc",
pageSize,
pageIndex,
out totalCount,
pl.ToArray()))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Topic>(dr);
}
}
return(model);
}
/// <summary>
/// 根据UserId查找作业列表
/// </summary>
/// <param name="userId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <dto_Zy> GetZyList(int userId, int pageIndex, int pageSize, out int totalCount)
{
List <dto_Zy> list = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(ZyConnString),
"Id, UserId, ZyName, CourseId, SubjectId, CreateDate, OpenDate, DueDate, Type, Status ",
"T_Zy where UserId = @UserId",
"Id desc",
pageSize,
pageIndex,
out totalCount,
"@UserId".ToInt32InPara(userId)))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Zy>(dr);
}
}
return(list);
}
public static List <dto_UserZy> GetSubmitedZy(int studentId, int pageIndex, int pageSize, out int totalCount)
{
List <dto_UserZy> model = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(ZyConnString),
"ZyId, CreateDate",
"T_Answer where StudentId = @StudentId",
"CreateDate desc",
pageSize,
pageIndex,
out totalCount,
"@StudentId".ToInt32InPara(studentId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_UserZy>(dr);
}
}
return(model);
}
/// <summary>
/// 获取老师新建的作业列表，分页
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <dto_UserZy> GetUserZy(int userId, int pageIndex, int pageSize, out int totalCount)
{
List <dto_UserZy> model = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(ZyConnString),
"Id ZyId, UserId, BodyHtmlPath, AnswerHtmlPath, CreateDate, Structed",
"T_Zy where UserId = @UserId",
"CreateDate desc",
pageSize,
pageIndex,
out totalCount,
"@UserId".ToInt32InPara(userId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_UserZy>(dr);
}
}
return(model);
}
public static List <dto_Reply> GetReplyList(int topicId, int pageSize, int pageIndex, out int totalCount)
{
List <dto_Reply> result = null;
using (var dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(BBSConnString),
"Id, UserId, TopicId, ReplyContent, CreateDate, Ip, Blocked, Good",
"T_Reply where TopicId = @TopicId and Deleted = 0",
"CreateDate",
pageSize,
pageIndex,
out totalCount,
"@TopicId".ToInt32InPara(topicId)))//查询数据库
{
if (dr != null && dr.HasRows)
{
result = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Reply>(dr);
}
}
return(result);
}
/// <summary>
/// 根据一组Userid获取他们新建的作业
/// </summary>
/// <param name="userIds"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <dto_Zy> GetZyList(int[] userIds, int pageIndex, int pageSize, out int totalCount)
{
if (userIds == null || userIds.Length == 0)
{
totalCount = 0;
return(null);
}
List <dto_Zy> list = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(ZyConnString),
"Id, UserId, ZyName, CourseId, SubjectId, CreateDate, OpenDate, DueDate, Type, Status ",
"T_Zy where UserId in (" + string.Join(",", userIds) + ")",
"Id desc",
pageSize,
pageIndex,
out totalCount))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Zy>(dr);
}
}
return(list);
}
/// <summary>
/// 根据UserId查找作业列表
/// </summary>
/// <param name="userId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <int> GetSubmitedZyIds(int userId, int pageIndex, int pageSize, out int totalCount)
{
List <int> list = null;
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(ZyConnString),
"ZyId ",
"T_Answer where StudentId = @StudentId",
"Id desc",
pageSize,
pageIndex,
out totalCount,
"@StudentId".ToInt32InPara(userId)))
{
if (dr != null && dr.HasRows)
{
list = new List <int>();
while (dr.Read())
{
list.Add(int.Parse(dr[0].ToString()));
}
}
}
return(list);
}
/// <summary>
/// 根据查询条件返回符合条件的试题Id数组
/// </summary>
/// <param name="courseId"></param>
/// <param name="kpointId"></param>
/// <param name="cpointId"></param>
/// <param name="typeId"></param>
/// <param name="diffType"></param>
/// <param name="paperYear"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static int[] GetQuesIds(int courseId, int kpointId, int cpointId, int typeId, int diffType, int paperYear, int pageIndex, int pageSize, out int totalCount)
{
List <int> model = null;
string wherestr = "T_Questions where courseid = @courseId ";
string orderstr = "usagetimes desc";
List <MySqlParameter> pl = new List <MySqlParameter>();
pl.Add(new MySqlParameter("@courseid", courseId));
if (typeId != 0)
{
wherestr += "and btypeid=@btypeid ";
pl.Add(new MySqlParameter("@btypeid", typeId));
}
if (diffType != 0)
{
wherestr += "and difftype=@difftype ";
pl.Add(new MySqlParameter("@difftype", diffType));
}
if (paperYear > 0)
{
wherestr += "and paperyear = @paperyear ";
pl.Add(new MySqlParameter("@paperyear", paperYear));
}
else if (paperYear < 0)
{
wherestr += "and paperyear <= @paperyear ";
pl.Add(new MySqlParameter("@paperyear", paperYear * -1));
}
if (kpointId != 0)
{
wherestr += "and kpoints like '%\"" + kpointId + "\"%'";
}
if (cpointId != 0)
{
int[] s = B_QuesBase.GetSimilarCatalogs(courseId, cpointId);
if (s != null)
{
wherestr += "and (";
var str = "";
foreach (var c in s)
{
str += " or cpoints like '%\"" + c + "\"%'";
}
wherestr += str.Substring(3) + ")";
}
else
{
wherestr += "and cpoints like '%\"" + cpointId + "\"%'";
}
}
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(QuesConnString),
"id",
wherestr,
orderstr,
pageSize,
pageIndex,
out totalCount,
pl.ToArray()))
{
if (dr != null && dr.HasRows)
{
model = new List <int>();
while (dr.Read())
{
model.Add(int.Parse(dr[0].ToString()));
}
}
}
return(model == null ? null : model.ToArray());
}
/// <summary>
/// 搜索试卷
/// </summary>
/// <param name="courseId"></param>
/// <param name="gradeId"></param>
/// <param name="typeId"></param>
/// <param name="paperYear"></param>
/// <param name="areaId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <dto_Paper> SearchPapers(int courseId, int gradeId, int typeId, int paperYear, int areaId, int pageIndex, int pageSize, out int totalCount)
{
List <dto_Paper> list = null;
string whereStr = " T_Paper where CourseId=@CourseId ";
List <MySqlParameter> param = new List <MySqlParameter>();
param.Add(new MySqlParameter("@CourseId", MySqlDbType.Int32)
{
Value = courseId
});
if (gradeId != 0)
{
whereStr = whereStr + " and GradeId=@GradeId";
param.Add(new MySqlParameter("@GradeId", MySqlDbType.Int32)
{
Value = gradeId
});
}
if (typeId != 0)
{
whereStr = whereStr + " and TypeId=@TypeId";
param.Add(new MySqlParameter("@TypeId", MySqlDbType.Int32)
{
Value = typeId
});
}
if (paperYear != 0 && paperYear > 0)
{
whereStr = whereStr + " and PaperYear=@PaperYear";
param.Add(new MySqlParameter("@PaperYear", MySqlDbType.Int32)
{
Value = paperYear
});
}
else if (paperYear < 0)
{
whereStr = whereStr + " and PaperYear<=" + paperYear * -1;
param.Add(new MySqlParameter("@PaperYear", MySqlDbType.Int32)
{
Value = paperYear
});
}
if (areaId != 0)
{
whereStr = whereStr + " and AreaId=@AreaId";
param.Add(new MySqlParameter("@AreaId", MySqlDbType.Int32)
{
Value = areaId
});
}
using (MySqlDataReader dr = MySqlDBHelper.GetPageReader(Util.GetConnectString(QuesConnString),
"PaperId, CourseId, TypeId, GradeId, AreaId, PaperYear, Title, QuestionIds",
whereStr,
"PaperYear desc",
pageSize,
pageIndex,
out totalCount,
param.ToArray()
))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Paper>(dr);
}
}
return(list);
}