/// <summary>
/// 题型_非选择题
/// </summary>
/// <param name="strFirstWhere"></param>
/// <param name="strWhere"></param>
/// <returns></returns>
public static DataTable fTiMuList(string strFirstWhere, string strWhere)
{
string strSql = "select * from (select ROW_NUMBER() OVER (order by TiMuNeiRong.id)as xuhao,TiMuSuoYin.*, TiMuNeiRong.content,TiMuNeiRong.objective_answer,TiMuNeiRong.answer from TiMuSuoYin ,TiMuNeiRong ,edu_question_type where TiMuSuoYin.gid = TiMuNeiRong.gid and edu_question_type.id = TiMuSuoYin.edu_question_type_Id and DataLength(TiMuNeiRong.content)>0 ";
if (!string.IsNullOrEmpty(strFirstWhere.Trim()))
{
strSql += " AND " + strFirstWhere;
}
strSql += ") as a ";
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql += " Where " + strWhere;
}
return(SqlDB.ExecuteDataset(strCon, CommandType.Text, strSql).Tables[0]);
}
/// <summary>
/// 知识点_选择题
/// </summary>
/// <param name="strFirstWhere"></param>
/// <param name="strWhere"></param>
/// <returns></returns>
public static DataTable zsdTiMuList(string strFirstWhere, string strWhere)
{
string strSql = "select * from (select ROW_NUMBER() OVER (order by TiMuNeiRong.id)as xuhao,TiMuSuoYin.*, TiMuNeiRong.content,TiMuNeiRong.objective_answer,TiMuNeiRong.answer from TiMuSuoYin ,TiMuNeiRong ,edu_question_type,zhishidian,zhishidian2timusuoyin where TiMuSuoYin.gid = TiMuNeiRong.gid and edu_question_type.id = TiMuSuoYin.edu_question_type_Id and len(ltrim(rtrim(TiMuNeiRong.objective_answer))) = 1 and zhishidian2timusuoyin.ZhiShiDianId = zhishidian.Id and zhishidian2timusuoyin.TiMuSuoYinId = TiMuSuoYin.Id ";
if (!string.IsNullOrEmpty(strFirstWhere.Trim()))
{
strSql += " AND " + strFirstWhere;
}
strSql += ") as a ";
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql += " Where " + strWhere;
}
return(SqlDB.ExecuteDataset(strCon, CommandType.Text, strSql).Tables[0]);
}
public static DataTable JiChuGongGuList(string TablePre, int ZhiShiDianId, int PageSize, int PageIndex)
{
string strSql = "select * from (select ROW_NUMBER() over(order by kcq.gid asc) as row,kc.difficulty,kc.score,kcq.* from zhishidian left join " + TablePre + "_exam_question_index as kc "
+ " on zhishidian.name = kc.zh_knowledge and zhishidian.subject_id = kc.subject_id "
+ " left join " + TablePre + "_exam_question as kcq on kc.gid = kcq.gid "
+ " where zhishidian.Id = " + ZhiShiDianId
+ " ) as newtable";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset((PageIndex - 1) * PageSize, PageSize, strCon, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static DataTable ShiTiShowList(string TablePre, string strGidList)
{
string strSql = "select zhishidian.Id as zsdId, kc.difficulty,kc.score,kcq.*,TiMuNeiRong.Id "
+ "from zhishidian left join " + TablePre + "_exam_question_index as kc "
+ "on zhishidian.name = kc.zh_knowledge and zhishidian.subject_id = kc.subject_id "
+ "left join " + TablePre + "_exam_question as kcq on kc.gid = kcq.gid "
+ "left join TiMuNeiRong on kcq.gid = TiMuNeiRong.gid "
+ "where (question_type = '选择题' or question_type = '单项填空') and LEN(kcq.objective_answer) = 1 and kcq.gid in (" + strGidList + ")";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset(strCon, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static DataTable ShiTiList(string TablePre, int ZhiShiDianId)
{
string strSql = "select * from (select ROW_NUMBER() over(order by kcq.gid asc) as row,kc.difficulty,kc.score,kcq.* from zhishidian left join " + TablePre + "_exam_question_index as kc "
+ " on zhishidian.name = kc.zh_knowledge and zhishidian.subject_id = kc.subject_id "
+ " left join " + TablePre + "_exam_question as kcq on kc.gid = kcq.gid "
+ " where (question_type = '选择题' or question_type = '单项填空') and LEN(objective_answer) = 1 "
+ " and zhishidian.Id = " + ZhiShiDianId
+ " ) as newtable";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset(strCon, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static DataTable ZhiShiDianList(string TablePre)
{
string strSql = "select zhishidian.Id, zhishidian.name from zhishidian "
+ "left join [GaoKaoBaoKao].[dbo].ZhiShiDianRelation as zsdr "
+ "on zhishidian.Id = zsdr.ZhenTiZSDId and zsdr.KMZSDId > 0 "
+ "left join " + TablePre + "_exam_question_index as kc "
+ " on zhishidian.name = kc.zh_knowledge and zhishidian.subject_id = kc.subject_id "
+ " left join " + TablePre + "_exam_question as kcq on kc.gid = kcq.gid "
+ " where (question_type = '选择题' or question_type = '单项填空') and LEN(objective_answer) = 1 "
+ " group by zhishidian.Id, zhishidian.name";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset(strCon, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static DataTable ChaFenShuXianPageList(string strWhere, int SchoolProvinceId, int WenLi, int StudentProvinceId, int Year, int PageSize, int PageIndex)
{
string strSql;
string strSqlTable = " WHERE FSYXLQ.SchoolId = S.Id ";
if (SchoolProvinceId > 0)
{
strSqlTable += " and S.ProvinceId = " + SchoolProvinceId;
}
if (WenLi > 0)
{
strSqlTable += " and FSYXLQ.KeLei = " + WenLi;
}
if (StudentProvinceId > 0)
{
strSqlTable += " and FSYXLQ.ProvinceId = " + StudentProvinceId;
}
if (Year > 0)
{
strSqlTable += " and FSYXLQ.DataYear = " + Year;
}
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql = "SELECT FSYXLQ.*,S.Id as SchoolId,S.SchoolName,S.ProvinceId as SProvinceId,S.ProvinceName as SProvinceName,FSYXLQ.YuanXiaoDaiMa as SchoolCode FROM [" + DAL.Common.GetProvinceTableName(StudentProvinceId, "FenShengYuanXiaoLuQu", (Year > 0 ? Year.ToString() : "All")) + "] AS FSYXLQ,[School] AS S " + strSqlTable + " AND " + strWhere;
}
else
{
strSql = "SELECT FSYXLQ.*,S.Id as SchoolId,S.SchoolName,S.ProvinceId as SProvinceId,S.ProvinceName as SProvinceName,FSYXLQ.YuanXiaoDaiMa as SchoolCode FROM [" + DAL.Common.GetProvinceTableName(StudentProvinceId, "FenShengYuanXiaoLuQu", (Year > 0 ? Year.ToString() : "All")) + "] AS FSYXLQ,[School] AS S " + strSqlTable;
}
strSql = strSql + " order by S.Id asc";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset((PageIndex - 1) * PageSize, PageSize, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static Entity.Join_Card Join_CardEntityGetByCardBank(string CardBank, string CardPass)
{
Entity.Join_Card info = new Entity.Join_Card();
SqlParameter[] prams =
{
SqlDB.MakeInParam("@CardBank", SqlDbType.NVarChar, 100, CardBank),
SqlDB.MakeInParam("@CardPass", SqlDbType.NVarChar, 50, CardPass),
};
DataTable dt = SqlDB.ExecuteDataset(strCon, CommandType.StoredProcedure, "Join_CardEntityGet", prams).Tables[0];
//DataTable dt = SqlDB.ExecuteDataset(strCon, CommandType.Text, "SELECT * FROM [Join_Card] WHERE CardBank = '" + CardBank + "' AND CardPass = '******'").Tables[0];
if (dt.Rows.Count > 0)
{
info.CardId = Basic.Utils.StrToInt(dt.Rows[0]["CardId"].ToString(), 0);
info.CardBank = dt.Rows[0]["CardBank"].ToString();
info.CardPass = dt.Rows[0]["CardPass"].ToString();
info.IsValid = Basic.Utils.StrToInt(dt.Rows[0]["IsValid"].ToString(), 0);
info.UseState = Basic.Utils.StrToInt(dt.Rows[0]["UseState"].ToString(), 0);
info.DianId = Basic.Utils.StrToInt(dt.Rows[0]["DianId"].ToString(), 0);
info.ProvinceId = Basic.Utils.StrToInt(dt.Rows[0]["ProvinceId"].ToString(), 0);
info.StudentId = Basic.Utils.StrToInt(dt.Rows[0]["StudentId"].ToString(), 0);
info.UseIp = dt.Rows[0]["UseIp"].ToString();
info.IsTest = Basic.Utils.StrToInt(dt.Rows[0]["IsTest"].ToString(), 0);
info.OpenCardTime = Basic.TypeConverter.StrToDateTime(dt.Rows[0]["OpenCardTime"].ToString());
info.UseTime = Basic.TypeConverter.StrToDateTime(dt.Rows[0]["UseTime"].ToString());
info.OpenCardTime = Basic.TypeConverter.StrToDateTime(dt.Rows[0]["OpenCardTime"].ToString());
info.UseTime = Basic.TypeConverter.StrToDateTime(dt.Rows[0]["UseTime"].ToString());
return(info);
}
else
{
return(null);
}
}
public static Entity.YuSheChengJi YuSheChengJiEntityGetByStudentId(int StudentId)
{
Entity.YuSheChengJi info = new Entity.YuSheChengJi();
DataTable dt = SqlDB.ExecuteDataset(CommandType.Text, "SELECT * FROM [YuSheChengJi] WHERE StudentId = " + StudentId + ";").Tables[0];
if (dt.Rows.Count > 0)
{
info.Id = Basic.Utils.StrToInt(dt.Rows[0]["Id"].ToString(), 0);
info.StudentId = Basic.Utils.StrToInt(dt.Rows[0]["StudentId"].ToString(), 0);
info.KeLei = Basic.Utils.StrToInt(dt.Rows[0]["KeLei"].ToString(), 0);
info.FenShu = Basic.Utils.StrToInt(dt.Rows[0]["FenShu"].ToString(), 0);
info.PcFirst = Basic.Utils.StrToInt(dt.Rows[0]["PcFirst"].ToString(), 0);
info.PcSecond = Basic.Utils.StrToInt(dt.Rows[0]["PcSecond"].ToString(), 0);
info.PcThird = Basic.Utils.StrToInt(dt.Rows[0]["PcThird"].ToString(), 0);
info.PcZhuanKe = Basic.Utils.StrToInt(dt.Rows[0]["PcZhuanKe"].ToString(), 0);
info.AddTime = Basic.Utils.StrToDateTime(dt.Rows[0]["AddTime"].ToString());
return(info);
}
else
{
return(null);
}
}
public static Entity.ProfessionalArticle ProfessionalArticleEntityGetByProfessionalId(int intProfessionalId)
{
Entity.ProfessionalArticle info = new Entity.ProfessionalArticle();
DataTable dt = SqlDB.ExecuteDataset(CommandType.Text, "SELECT Top 1 * FROM [ProfessionalArticle] WHERE ProfessionalId = " + intProfessionalId + " ORDER BY Id DESC").Tables[0];
if (dt.Rows.Count > 0)
{
info.Id = Basic.Utils.StrToInt(dt.Rows[0]["Id"].ToString(), 0);
info.ProfessionalId = Basic.Utils.StrToInt(dt.Rows[0]["ProfessionalId"].ToString(), 0);
info.CategoryId = Basic.Utils.StrToInt(dt.Rows[0]["CategoryId"].ToString(), 0);
info.Icon = dt.Rows[0]["Icon"].ToString();
info.Title = dt.Rows[0]["Title"].ToString();
info.ShortTitle = dt.Rows[0]["ShortTitle"].ToString();
info.MetaTitle = dt.Rows[0]["MetaTitle"].ToString();
info.MetaKeyWords = dt.Rows[0]["MetaKeyWords"].ToString();
info.MetaDescription = dt.Rows[0]["MetaDescription"].ToString();
info.Summary = dt.Rows[0]["Summary"].ToString();
info.Content = dt.Rows[0]["Content"].ToString();
info.IsPause = Basic.Utils.StrToInt(dt.Rows[0]["IsPause"].ToString(), 0);
info.IsTuiJian = Basic.Utils.StrToInt(dt.Rows[0]["IsTuiJian"].ToString(), 0);
info.IsNew = Basic.Utils.StrToInt(dt.Rows[0]["IsNew"].ToString(), 0);
info.IsZhiDing = Basic.Utils.StrToInt(dt.Rows[0]["IsZhiDing"].ToString(), 0);
info.AddWid = Basic.Utils.StrToInt(dt.Rows[0]["AddWid"].ToString(), 0);
info.IsCheck = Basic.Utils.StrToInt(dt.Rows[0]["IsCheck"].ToString(), 0);
info.CheckWid = Basic.Utils.StrToInt(dt.Rows[0]["CheckWid"].ToString(), 0);
info.ClickNum = Basic.Utils.StrToInt(dt.Rows[0]["ClickNum"].ToString(), 0);
info.AddTime = Basic.Utils.StrToDateTime(dt.Rows[0]["AddTime"].ToString());
info.CheckTime = Basic.Utils.StrToDateTime(dt.Rows[0]["CheckTime"].ToString());
info.PublishTime = Basic.Utils.StrToDateTime(dt.Rows[0]["PublishTime"].ToString());
info.ZhiDingTime = Basic.Utils.StrToDateTime(dt.Rows[0]["ZhiDingTime"].ToString());
return(info);
}
else
{
return(null);
}
}
/// <summary>
/// 错题本 分页
/// </summary>
/// <param name="strWhere">条件，可以为空</param>
/// <returns>返回DataTable</returns>
public static DataTable CuoTiBenPageList(string strWhere, int PageSize, int PageIndex)
{
string strSql;
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql = "SELECT TiMuNeiRong.*,cuotiben.Answer as cuotibenAnswer,cuotiben.Id as cuotibenId FROM [TiMuNeiRong],[cuotiben] WHERE TiMuNeiRong.id = cuotiben.TiMuId AND " + strWhere;
}
else
{
strSql = "SELECT TiMuNeiRong.*,cuotiben.Answer as cuotibenAnswer,cuotiben.Id as cuotibenId FROM [TiMuNeiRong],[cuotiben] WHERE TiMuNeiRong.id = cuotiben.TiMuId ";
}
strSql += " ORDER BY cuotibenId DESC";
DataSet ds = new DataSet();
ds = SqlDB.ExecuteDataset((PageIndex - 1) * PageSize, PageSize, strCon, CommandType.Text, strSql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(new DataTable());
}
public static Entity.Join_Student Join_StudentEntityGetBank(string StudentBank, string StudentPass)
{
Entity.Join_Student info = new Entity.Join_Student();
SqlParameter[] prams =
{
SqlDB.MakeInParam("@Bank", SqlDbType.NVarChar, 100, StudentBank),
SqlDB.MakeInParam("@Pass", SqlDbType.NVarChar, 50, StudentPass),
SqlDB.MakeInParam("@MD5Pass", SqlDbType.NVarChar, 50, Basic.CreateMD5.Md5Encrypt(StudentPass)),
};
DataTable dt = SqlDB.ExecuteDataset(strCon, CommandType.StoredProcedure, "StudentEntityGet", prams).Tables[0];
// DataTable dt = SqlDB.ExecuteDataset(strCon, CommandType.Text, "SELECT * FROM [Join_Student] WHERE StudentBank = '" + StudentBank + "' AND (StudentPass = '******' OR StudentPass = '******')").Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
info.StudentId = Basic.Utils.StrToInt(dt.Rows[0]["StudentId"].ToString(), 0);
info.StudentBank = dt.Rows[0]["StudentBank"].ToString();
info.StudentPass = dt.Rows[0]["StudentPass"].ToString();
info.StudenName = dt.Rows[0]["StudenName"].ToString();
info.StudentName = dt.Rows[0]["StudentName"].ToString();
info.Sex = Basic.Utils.StrToInt(dt.Rows[0]["Sex"].ToString(), 0);
info.CellTel = dt.Rows[0]["CellTel"].ToString();
info.CellPhone = dt.Rows[0]["CellPhone"].ToString();
info.CellPhoneCheck = Basic.Utils.StrToInt(dt.Rows[0]["CellPhoneCheck"].ToString(), 0);
info.Address = dt.Rows[0]["Address"].ToString();
info.SchoolName = dt.Rows[0]["SchoolName"].ToString();
info.IdNumber = dt.Rows[0]["IdNumber"].ToString();
info.CheckIdNumber = Basic.Utils.StrToInt(dt.Rows[0]["CheckIdNumber"].ToString(), 0);
info.IdNumberPic = dt.Rows[0]["IdNumberPic"].ToString();
info.BirthDate = dt.Rows[0]["BirthDate"].ToString();
info.RegisterWay = Basic.Utils.StrToInt(dt.Rows[0]["RegisterWay"].ToString(), 0);
info.RegisterOrigin = dt.Rows[0]["RegisterOrigin"].ToString();
info.DldId = Basic.Utils.StrToInt(dt.Rows[0]["DldId"].ToString(), 0);
info.UserCategory = Basic.Utils.StrToInt(dt.Rows[0]["UserCategory"].ToString(), 0);
info.PositionCase = dt.Rows[0]["PositionCase"].ToString();
info.IsPause = Basic.Utils.StrToInt(dt.Rows[0]["IsPause"].ToString(), 0);
info.Mail = dt.Rows[0]["Mail"].ToString();
info.QQ = dt.Rows[0]["QQ"].ToString();
info.About = dt.Rows[0]["About"].ToString();
info.Company = dt.Rows[0]["Company"].ToString();
info.MSN = dt.Rows[0]["MSN"].ToString();
info.ArtDialog = Basic.Utils.StrToInt(dt.Rows[0]["ArtDialog"].ToString(), 0);
info.FuQinPhone = dt.Rows[0]["FuQinPhone"].ToString();
info.MuQinPhone = dt.Rows[0]["MuQinPhone"].ToString();
info.WenLi = Basic.Utils.StrToInt(dt.Rows[0]["WenLi"].ToString(), 0);
info.BanJi = dt.Rows[0]["BanJi"].ToString();
info.BanZhuRen = dt.Rows[0]["BanZhuRen"].ToString();
info.ProvinceId = Basic.Utils.StrToInt(dt.Rows[0]["ProvinceId"].ToString(), 0);
info.CityId = Basic.Utils.StrToInt(dt.Rows[0]["CityId"].ToString(), 0);
info.CountyId = Basic.Utils.StrToInt(dt.Rows[0]["CountyId"].ToString(), 0);
info.IsAutoCreat = Basic.Utils.StrToInt(dt.Rows[0]["IsAutoCreat"].ToString(), 0);
info.BanZhuRenMobile = dt.Rows[0]["BanZhuRenMobile"].ToString();
info.StudentLevel = Basic.Utils.StrToInt(dt.Rows[0]["StudentLevel"].ToString(), 0);
info.GKYear = Basic.Utils.StrToInt(dt.Rows[0]["GKYear"].ToString(), 0);
info.RegisterTime = Basic.Utils.StrToDateTime(dt.Rows[0]["RegisterTime"].ToString());
return(info);
}
else
{
return(null);
}
}