/// <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>
/// 搜索用户，只返回前10个
/// </summary>
/// <param name="keyWords"></param>
/// <returns></returns>
public static List <T_User> SearchUser(string keyWords)
{
List <T_User> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(UserConnString),
"select Id, UserName, TrueName, Mobile, ProvinceId, CityId, DistrictId, SchoolId, GradeId, ClassId from T_User where concat(UserName, TrueName) like '%" + keyWords + "%' limit 20"))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_User>(dr);
}
}
return(model);
}
public static List <dto_Topic> GetTopics(int[] topicIds)
{
List <dto_Topic> list = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(BBSConnString),
"select Id, UserId, Invites, Title, TopicContent, TopicText, CreateDate, Good, Hit, ReplyCount, GradeId, SubjectId, Deleted, Blocked, Ip from T_Topic where Id in (" + string.Join(",", topicIds) + ")"))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Topic>(dr);
}
}
return(list);
}
/// <summary>
/// 获取学生提交的作业列表
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
public static List <dto_UserZy> GetSubmitedZy(int studentId)
{
List <dto_UserZy> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(ZyConnString),
"select ZyId, CreateDate from T_Answer where StudentId = @StudentId order by CreateDate desc",
"@StudentId".ToInt32InPara(studentId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_UserZy>(dr);
}
}
return(model);
}
/// <summary>
/// 搜索用户，只返回前10个
/// </summary>
/// <param name="keyWords"></param>
/// <returns></returns>
public static List <T_User> SearchUser(string keyWords, int exceptUserId)
{
List <T_User> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(UserConnString),
"select Id, UserName, TrueName, Psd, Mobile, FirstLoginDate, CreateDate, ZyPsd, ZyPrice, ProvinceId, CityId, DistrictId, SchoolId, GradeId, ClassId from T_User where concat(UserName, TrueName) like '%" + keyWords + "%' and Id <> @ExceptUserId limit 20",
"@ExceptUserId".ToInt32InPara(exceptUserId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_User>(dr);
}
}
return(model);
}
/// <summary>
/// 获取老师新建的作业列表
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <dto_UserZy> GetUserZy(int userId)
{
List <dto_UserZy> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(ZyConnString),
"select Id ZyId, UserId, BodyHtmlPath, AnswerHtmlPath, CreateDate, Structed from T_Zy where UserId = @UserId order by CreateDate desc",
"@UserId".ToInt32InPara(userId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_UserZy>(dr);
}
}
return(model);
}
/// <summary>
/// 根据大题Id一次获取所有小题的选项
/// </summary>
/// <param name="pId"></param>
/// <returns></returns>
public static List <T_QuesOptions> GetAllChildQuesOptions(int pId)
{
List <T_QuesOptions> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(QuesConnString),
"select id, optiona, optionb, optionc, optiond, optione, optionf, optiong, pid from T_QuesOptions where pid = @pid",
"@pid".ToInt32InPara(pId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_QuesOptions>(dr);
}
}
return(model);
}
public static List <T_CatalogNodes> GetCatalogNodes(int textbookId)
{
List <T_CatalogNodes> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(QuesConnString),
"select Id, Name, Ordinal, ParentId, TextBookId, Type from T_CatalogNodes a where a.TextBookId = @TextBookId order by ParentId, Ordinal",
"@TextBookId".ToInt32InPara(textbookId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_CatalogNodes>(dr);
}
}
return(model);
}
/// <summary>
/// 获取被关注用户列表
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <dto_RelateUser> GetBeRelatedUser(int userId)
{
List <dto_RelateUser> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(UserConnString),
"select Id, UserId, RUserId, CreateDate from T_UserRelate where RUserId = @RUserId",
"@RUserId".ToInt32InPara(userId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_RelateUser>(dr);
}
}
return(model);
}
/// <summary>
/// 根据大题Id获取其所有小题信息（不包含选项）
/// </summary>
/// <param name="pId"></param>
/// <returns></returns>
public static List <dto_CQuestion> GetChildQuestions(int pId)
{
List <dto_CQuestion> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(QuesConnString),
"select id, pid, ptypeid, typeid, typename, quesbody, quesanswer, quesparse from T_CQuestions where pid = @pid order by id",
"@pid".ToInt32InPara(pId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_CQuestion>(dr);
}
}
return(model);
}
public static List <T_City> GetCities(int provinceId)
{
List <T_City> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(BaseConnString),
"select CityId, CityName from T_City where ProvinceId = @ProvinceId",
"@ProvinceId".ToInt32InPara(provinceId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_City>(dr);
}
}
return(model);
}
/// <summary>
/// 获取作业结构
/// </summary>
/// <param name="zyId"></param>
/// <returns></returns>
public static List <T_ZyStruct> GetZyStruct(int zyId)
{
List <T_ZyStruct> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(ZyConnString),
"select Id, ZyId, BqNum, SqNum, QuesType, QuesAnswer, CreateDate from T_ZyStruct where ZyId = @ZyId",
"@ZyId".ToInt32InPara(zyId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_ZyStruct>(dr);
}
}
return(model);
}
public static List <dto_Topic> GetTop5Topics(DateTime firstCycleDay)
{
List <dto_Topic> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(BBSConnString),
"select Id, UserId, Invites, Title, TopicContent, TopicText, CreateDate, Good, Hit, ReplyCount, GradeId, SubjectId, Deleted, Blocked, Ip from T_Topic where Deleted = 0 and Blocked = 0 and CreateDate > @FirstCycleDay order by ReplyCount desc limit 5"
, "@FirstCycleDay".ToDateTimeInPara(firstCycleDay)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Topic>(dr);
}
}
return(model);
}
public static List <T_District> GetDistricts(int cityId)
{
List <T_District> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(BaseConnString),
"select DistrictId, DistrictName from T_District where CityId = @CityId",
"@CityId".ToInt32InPara(cityId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_District>(dr);
}
}
return(model);
}
public static List <T_School> GetSchools(int districtId)
{
List <T_School> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(BaseConnString),
"select SchoolId, SchoolName from T_School where DistrictId = @DistrictId",
"@DistrictId".ToInt32InPara(districtId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_School>(dr);
}
}
return(model);
}
public static List <dto_Answer> GetAnswers(int zyId)
{
List <dto_Answer> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(ZyConnString),
"select Id, ZyId, ZyType, StudentId, AnswerJson, AnswerImg, Submited, CreateDate from T_Answer where ZyId = @ZyId and Submited = 1",
"@ZyId".ToInt32InPara(zyId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Answer>(dr);
}
}
return(model);
}
public static List <T_KnowledgePoints> GetKnowledgePoints(int courseId)
{
List <T_KnowledgePoints> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(QuesConnString),
"select Id, CourseId, Name, Ordinal, ParentId, Type from T_KnowledgePoints a where a.CourseId = @CourseId order by ParentId, Ordinal",
"@CourseId".ToInt32InPara(courseId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_KnowledgePoints>(dr);
}
}
return(model);
}
/// <summary>
/// 获取提交过某作业的班级列表
/// </summary>
/// <param name="zyId"></param>
/// <returns></returns>
public static List <dto_ClassSubmitCount> GetSubmitClasses(int zyId)
{
List <dto_ClassSubmitCount> list = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(AnalyzeConnString),
"select SchoolId, GradeId, ClassId, SubmitCount from T_ClassSubmitCount where ZyId = @ZyId and SubmitCount > 0",
"@ZyId".ToInt32InPara(zyId)))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_ClassSubmitCount>(dr);
}
}
return(list);
}
/// <summary>
/// 根据UserId查找作业列表
/// </summary>
/// <param name="userId"></param>
/// <param name="lastId"></param>
/// <param name="count"></param>
/// <returns></returns>
public static List <dto_Zy> GetZyList(int userId, int lastId, int count)
{
List <dto_Zy> list = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(ZyConnString),
"select Id, UserId, ZyName, CourseId, SubjectId, CreateDate, OpenDate, DueDate, Type, Status from T_Zy where UserId = @UserId and Id < @Id order by Id desc limit @Count",
"@UserId".ToInt32InPara(userId),
"@Id".ToInt32InPara(lastId),
"@Count".ToInt32InPara(count)))
{
if (dr != null && dr.HasRows)
{
list = MySqlDBHelper.ConvertDataReaderToEntityList <dto_Zy>(dr);
}
}
return(list);
}
/// <summary>
/// 查找一个班的所有同学
/// </summary>
/// <param name="schoolId"></param>
/// <param name="gradeId"></param>
/// <param name="classId"></param>
/// <returns></returns>
public static List <T_User> GetClassmates(int schoolId, int gradeId, int classId)
{
List <T_User> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(UserConnString),
"select Id, UserName, TrueName, Psd, Mobile, FirstLoginDate, CreateDate, ZyPsd, ZyPrice, ProvinceId, CityId, DistrictId, SchoolId, GradeId, ClassId from T_User where SchoolId = @SchoolId and GradeId = @GradeId and ClassId = @ClassId",
"@SchoolId".ToInt32InPara(schoolId),
"@GradeId".ToInt32InPara(gradeId),
"@ClassId".ToInt32InPara(classId)))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_User>(dr);
}
}
return(model);
}
/// <summary>
/// 查看学生提交情况以及得分情况
/// </summary>
/// <param name="zyId"></param>
/// <param name="schoolId"></param>
/// <param name="gradeId"></param>
/// <param name="classId"></param>
/// <returns></returns>
public static List <dto_StudentPoint> GetStudentPoint2(int zyId, int schoolId, int gradeId, int classId)
{
List <dto_StudentPoint> result = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(AnalyzeConnString),
"select ZyId, StudentId, SubmitDate, Score from T_StudentPoint where ZyId = @ZyId and SchoolId = @SchoolId and GradeId = @GradeId and ClassId = @ClassId order by SubmitDate",
"@ZyId".ToInt32InPara(zyId),
"@SchoolId".ToInt32InPara(schoolId),
"@GradeId".ToInt32InPara(gradeId),
"@ClassId".ToInt32InPara(classId)))
{
if (dr != null && dr.HasRows)
{
result = MySqlDBHelper.ConvertDataReaderToEntityList <dto_StudentPoint>(dr);
}
}
return(result);
}
public static List <T_UserExtend> GetUserExtends(int[] uIds)
{
if (uIds.Length == 0)
{
return(null);
}
string userIds = string.Join(",", uIds);
List <T_UserExtend> model = null;
using (MySqlDataReader dr = MySqlHelper.ExecuteReader(Util.GetConnectString(UserConnString),
"select UserId, Locked from T_UserExtend where UserId in (" + userIds + ")"))
{
if (dr != null && dr.HasRows)
{
model = MySqlDBHelper.ConvertDataReaderToEntityList <T_UserExtend>(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>
/// 搜索试卷
/// </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);
}