/// <summary>
/// 显示所有
/// </summary>
/// <param name="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="CourseID">课程ID</param>
/// <param name="Extent">程度</param>
/// <returns></returns>
public List <Model.TitleList> GetList(string Type, int CourseID, string Extent)
{
string strSql = "";
strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "' AND [Extent]='" + Extent + "'";
List <Model.TitleList> List = new List <Model.TitleList>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.TitleList model = new Model.TitleList();
model.Title = DR["Title"].ToString();
model.T_F = DR["T_F"].ToString();
model.Analysis = DR["Analysis"].ToString();
model.ID = int.Parse(DR["ID"].ToString());
model.Extent = DR["Extent"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 显示所有
/// </summary>
/// <param name="TitleListID">题目ID</param>
/// <returns></returns>
public List <Model.PapersOptionsList> GetList(int TitleListID, int PapersID)
{
string strSql = "";
//strSql = "Select * FROM [PapersOptionsList] Where [TitleListID]='" + TitleListID + "' and [PapersID]='" + PapersID + "' order by newid()";
strSql = "Select * FROM [PapersOptionsList] Where [TitleListID]='" + TitleListID + "' and [PapersID]='" + PapersID + "' order by ID";
List <Model.PapersOptionsList> List = new List <Model.PapersOptionsList>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.PapersOptionsList model = new Model.PapersOptionsList();
model.Name = DR["Name"].ToString();
model.T_F = DR["T_F"].ToString();
model.PapersID = int.Parse(DR["PapersID"].ToString());
model.TitleListID = int.Parse(DR["TitleListID"].ToString());
model.ID = int.Parse(DR["ID"].ToString());
model.Score = int.Parse(DR["Score"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
///
/// </summary>
/// <param name="PageCount"></param>
/// <param name="PageSize"></param>
/// <param name="Type"></param>
/// <param name="Extent"></param>
/// <returns></returns>
public List <Model.TitleList> GetList(int PageCount, int PageSize, string Type, string Extent)
{
string strSql = "";
string RedCountsql = "";
int RowTotal = (PageCount - 1) * PageSize;//
RedCountsql = "(select count(ID) from [TitleList] Where [Type]='" + Type + "' and [Extent]='" + Extent + "' ) as RowTotal";
strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [TitleList] where [ID] not in (Select Top " + RowTotal + " [ID] from [TitleList] Where [Type]='" + Type + "' and [Extent]='" + Extent + "' ORDER BY ID DESC) and [Type]='" + Type + "' and [Extent]='" + Extent + "' ORDER BY ID DESC";
List <Model.TitleList> List = new List <Model.TitleList>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.TitleList model = new Model.TitleList();
model.RowTotal = int.Parse(DR["RowTotal"].ToString());
model.ID = int.Parse(DR["ID"].ToString());
model.Type = DR["Type"].ToString();
model.Title = DR["Title"].ToString();
model.T_F = DR["T_F"].ToString();
model.CourseID = int.Parse(DR["CourseID"].ToString());
model.Extent = DR["Extent"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 详细信息
/// </summary>
/// <param name="ID">ID</param>
/// <returns></returns>
public List <Model.TitleList> Details(int ID)
{
string strSql = "";
strSql = "Select * FROM [TitleList] Where [ID]='" + ID + "'";
List <Model.TitleList> List = new List <Model.TitleList>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.TitleList model = new Model.TitleList();
model.Title = DR["Title"].ToString();
model.T_F = DR["T_F"].ToString();
model.Analysis = DR["Analysis"].ToString();
model.ID = int.Parse(DR["ID"].ToString());
model.CourseID = int.Parse(DR["CourseID"].ToString());
model.Status_id = int.Parse(DR["Status_id"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <Model.SystemInfo> GetList()
{
string strSql = "";
strSql = "Select * FROM SystemInfo";
List <Model.SystemInfo> List = new List <Model.SystemInfo>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.SystemInfo model = new Model.SystemInfo();
model.CourseSys = DR["CourseSys"].ToString();
model.HardInfo = DR["HardInfo"].ToString();
model.SN = DR["SN"].ToString();
model.sPassword = DR["sPassword"].ToString();
model.Updata = DR["Updata"].ToString();
model.UpdataCourseSys = DR["UpdataCourseSys"].ToString();
model.UpdataHardInfo = DR["UpdataHardInfo"].ToString();
model.UpdataSN = DR["UpdataSN"].ToString();
model.UpdataUserSys = DR["UpdataUserSys"].ToString();
model.UserSys = DR["UserSys"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 获取所有列表
/// </summary>
/// <returns></returns>
public List <Model.Status> GetList(string Ys_Code)
{
string strSql = "";
strSql = "Select * FROM [Status] WHERE [YS_TYPE]='" + Ys_Code + "'";
List <Model.Status> List = new List <Model.Status>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Status model = new Model.Status();
model.YS_ID = int.Parse(DR["YS_ID"].ToString());
model.YS_CODE = DR["YS_CODE"].ToString();
model.YS_DESC = DR["YS_DESC"].ToString();
model.YS_SEQ = int.Parse(DR["YS_SEQ"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 返回题目类别
/// </summary>
/// <returns></returns>
public List <Model.Status> GetCategory()
{
string strSql = "";
strSql = "Select YS_TYPE,YS_REMARK FROM [Status] GROUP BY YS_TYPE,YS_REMARK";
List <Model.Status> List = new List <Model.Status>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Status model = new Model.Status();
model.YS_TYPE = DR["YS_TYPE"].ToString();
model.YS_REMARK = DR["YS_REMARK"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <Model.HardInfo> GetList()
{
string strSql = "";
strSql = "Select * FROM HardInfo";
List <Model.HardInfo> List = new List <Model.HardInfo>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.HardInfo model = new Model.HardInfo();
model.HostName = DR["HostName"].ToString();
model.CpuID = DR["CpuID"].ToString();
model.HardDiskID = DR["HardDiskID"].ToString();
model.NetCardMAC = DR["NetCardMAC"].ToString();
model.NetCardIP = DR["NetCardIP"].ToString();
model.VolOf = DR["VolOf"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 详细信息
/// </summary>
/// <param name="ID">ID</param>
/// <returns></returns>
public List <Model.Papers> Details(int ID)
{
string strSql = "";
strSql = "Select * FROM [Papers] Where [ID]='" + ID + "'";
List <Model.Papers> List = new List <Model.Papers>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.Papers model = new Model.Papers();
model.ID = int.Parse(DR["ID"].ToString());
model.Name = DR["Name"].ToString();
model.CourseID = int.Parse(DR["CourseID"].ToString());
model.Type = DR["Type"].ToString();
model.Lock = int.Parse(DR["Lock"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 显示所有
/// </summary>
/// <param name="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="PapersID">试卷ID</param>
/// <returns></returns>
public List <Model.PapersTitleList> GetList(string Type, int PapersID, int StatusID)
{
string strSql = "";
//strSql = "Select * FROM [PapersTitleList] Where [Type]='" + Type + "' AND [PapersID]='" + PapersID + "' AND Status_id='"+ StatusID + "' order by newid()";
//add by wangke 2016-12-13 题目排序调整
strSql = "Select * FROM [PapersTitleList] Where [Type]='" + Type + "' AND [PapersID]='" + PapersID + "' AND Status_id='" + StatusID + "' order by ID";
List <Model.PapersTitleList> List = new List <Model.PapersTitleList>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.PapersTitleList model = new Model.PapersTitleList();
model.Title = DR["Title"].ToString();
model.T_F = DR["T_F"].ToString();
model.ID = int.Parse(DR["ID"].ToString());
model.Score = int.Parse(DR["Score"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <Model.Db> GetList()
{
string strSql = "";
strSql = "Select * FROM Db";
List <Model.Db> List = new List <Model.Db>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Db model = new Model.Db();
model.Server = DR["Server"].ToString();
model.Database = DR["Database"].ToString();
model.Password = DR["Password"].ToString();
model.Instance = DR["Instance"].ToString();
model.Port = DR["Port"].ToString();
model.User = DR["User"].ToString();
model.Key = DR["Key"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 显示所有
/// </summary>
/// <param name="PageCount">PageCount</param>
/// <param name="PageSize">PageSize</param>
/// <param name="Type">类型(试卷?问卷)</param>
/// <param name="CourseID">课程ID</param>
/// <returns></returns>
public List <Model.Papers> GetList(int PageCount, int PageSize, string Type, int CourseID)
{
string strSql = "";
string RedCountsql = "";
int RowTotal = (PageCount - 1) * PageSize;//
RedCountsql = "(select count(ID) from [Papers] Where [CourseID]='" + CourseID + "' and [Type]='" + Type + "' ) as RowTotal";
strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [Papers] where [ID] not in (Select Top " + RowTotal + " [ID] from [Papers] Where [CourseID]='" + CourseID + "' and [Type]='" + Type + "' ORDER BY ID DESC) and [CourseID]='" + CourseID + "' and [Type]='" + Type + "' ORDER BY ID DESC";
List <Model.Papers> List = new List <Model.Papers>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Papers model = new Model.Papers();
model.RowTotal = int.Parse(DR["RowTotal"].ToString());
model.ID = int.Parse(DR["ID"].ToString());
model.Name = DR["Name"].ToString();
model.CourseID = int.Parse(DR["CourseID"].ToString());
model.Type = DR["Type"].ToString();
model.Lock = int.Parse(DR["Lock"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <Model.UserSystem> GetList(int PageCount, int PageSize)
{
string strSql = "";
string RedCountsql = "";
int RowTotal = (PageCount - 1) * PageSize;//
RedCountsql = "(select count(TR_ID) from [AP_TRAINEE_USER]) as RowTotal";
strSql = "Select Top " + PageSize + " [TR_ID],[TR_POSITION],[TR_NICKNAME],[TR_NAME],[TR_SEX],[TR_CITY],[TR_PHONE],[TR_EMAIL],[TR_COMPANY_TEL],[TR_QR_CODE]," + RedCountsql + " FROM AP_TRAINEE_USER where [TR_ID] not in (Select Top " + RowTotal + " [TR_ID] from AP_TRAINEE_USER ORDER BY TR_ID DESC) ORDER BY TR_ID DESC";
List <Model.UserSystem> List = new List <Model.UserSystem>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
if (GetUserSysSetupInfo())
{
DR = UserSys.ExecuteReader(strSql.ToString(), parameters);
}
else
{
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
}
using (DR)
{
while (DR.Read())
{
Model.UserSystem model = new Model.UserSystem();
model.ID = int.Parse(DR["TR_ID"].ToString());
model.cnName = DR["TR_NAME"].ToString();
model.enName = DR["TR_NICKNAME"].ToString();
model.Sex = DR["TR_SEX"].ToString();
model.ZipCode = DR["TR_QR_CODE"].ToString();
model.Address = DR["TR_CITY"].ToString();
model.Mobile = DR["TR_COMPANY_TEL"].ToString();
model.Phone = DR["TR_PHONE"].ToString();
model.Email = DR["TR_EMAIL"].ToString();
model.RowTotal = int.Parse(DR["RowTotal"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 显示所有
/// add by wangke 2016-11-23 添加返回datatable类型
/// </summary>
/// <param name="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="CourseID">课程ID</param>
/// <returns></returns>
public DataTable GetList(string Type, int CourseID, int Status_id)
{
string strSql = "";
if (Status_id != 0)
{
strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "' AND [Status_id]=" + Status_id;
}
else
{
strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "'";
}
DataTable dt = new DataTable();
dt.Columns.Add("Title");
dt.Columns.Add("T_F");
dt.Columns.Add("Analysis");
dt.Columns.Add("ID");
dt.Columns.Add("Extent");
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
DataRow dr = dt.NewRow();
dr["Title"] = DR["Title"].ToString();
dr["T_F"] = DR["T_F"].ToString();
dr["Analysis"] = DR["Analysis"].ToString();
dr["ID"] = DR["ID"].ToString();
dr["Extent"] = DR["Extent"].ToString();
dt.Rows.Add(dr);
}
DR.Close();
DR.Dispose();
}
return(dt);
}
/// <summary>
/// 查询所有
/// </summary>
/// <param name="UserName">用户名(可中可英)</param>
/// <returns></returns>
public List <Model.UserSystem> GetList(string UserName)
{
string strSql = "";
strSql = "Select * FROM [AP_TRAINEE_USER] Where [TR_NAME] like '%" + UserName + "%' or [TR_NICKNAME] like '%" + UserName + "%'";
List <Model.UserSystem> List = new List <Model.UserSystem>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
if (GetUserSysSetupInfo())
{
DR = UserSys.ExecuteReader(strSql.ToString(), parameters);
}
else
{
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
}
using (DR)
{
while (DR.Read())
{
Model.UserSystem model = new Model.UserSystem();
model.ID = int.Parse(DR["TR_ID"].ToString());
model.cnName = DR["TR_NAME"].ToString();
model.enName = DR["TR_NICKNAME"].ToString();
model.Sex = DR["TR_SEX"].ToString();
model.ZipCode = DR["TR_QR_CODE"].ToString();
model.Address = DR["TR_CITY"].ToString();
model.Mobile = DR["TR_COMPANY_TEL"].ToString();
model.Phone = DR["TR_PHONE"].ToString();
model.Email = DR["TR_EMAIL"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <param name="CourseLbID">类别ID</param>
/// <returns></returns>
public List <Model.CourseSystem> GetList(int PageCount, int PageSize, int CourseLbID)
{
string strSql = "";
string RedCountsql = "";
int RowTotal = (PageCount - 1) * PageSize;//
RedCountsql = "(select count(CR_ID) from [AP_COURSE] where [CR_CATEGORY_ID]='" + CourseLbID + "' ) as RowTotal";
strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [AP_COURSE] where [CR_ID] not in (Select Top " + RowTotal + " [CR_ID] from [AP_COURSE] Where [CR_CATEGORY_ID]='" + CourseLbID + "' ORDER BY CR_ID DESC ) And [CR_CATEGORY_ID]='" + CourseLbID + "' ORDER BY CR_ID DESC";
List <Model.CourseSystem> List = new List <Model.CourseSystem>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
if (GetCourseSysSetupInfo())
{
DR = CourseSys.ExecuteReader(strSql.ToString(), parameters);
}
else
{
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
}
using (DR)
{
while (DR.Read())
{
Model.CourseSystem model = new Model.CourseSystem();
model.ID = int.Parse(DR["CR_ID"].ToString());
model.Title = DR["CR_COURSE_NAME"].ToString();
model.RowTotal = int.Parse(DR["RowTotal"].ToString());
model.Credit = int.Parse(DR["CR_CREDIT"].ToString());
model.Money = int.Parse(DR["CR_MONEY"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 统计分值
/// </summary>
/// <param name="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="PapersID">试卷ID</param>
/// <returns></returns>
private List <Model.PapersTitleList> nSumScore(string Type, int PapersID)
{
string strSql = "";
if (Type != "")
{
strSql = "SELECT SUM([Score]) as sumscore FROM [ExaminationSystem].[Exam].[PapersTitleList] where PapersID='" + PapersID + "' and Type='" + Type + "'";
}
else
{
strSql = "SELECT SUM([Score]) as sumscore FROM [ExaminationSystem].[Exam].[PapersTitleList] where PapersID='" + PapersID + "'";
}
List <Model.PapersTitleList> List = new List <Model.PapersTitleList>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
using (DR)
{
while (DR.Read())
{
Model.PapersTitleList model = new Model.PapersTitleList();
if (DR["sumscore"].ToString() == "")
{
model.Score = 0;
}
else
{
model.Score = int.Parse(DR["sumscore"].ToString());
}
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <Model.CourseSystem> GetList()
{
string strSql = "";
strSql = "Select * FROM [AP_COURSE_CATEGORY]";
List <Model.CourseSystem> List = new List <Model.CourseSystem>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
if (GetCourseSysSetupInfo())
{
DR = CourseSys.ExecuteReader(strSql.ToString(), parameters);
}
else
{
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
}
using (DR)
{
while (DR.Read())
{
Model.CourseSystem model = new Model.CourseSystem();
model.CourseLbID = int.Parse(DR["CRC_ID"].ToString());
model.Name = DR["CRC_CATEGORY_NAME"].ToString();
model.Up = int.Parse(DR["CRC_PARENT_ID"].ToString());
model.CourseCount = GetCourseCount(int.Parse(DR["CRC_ID"].ToString()));
//model.CourseCount = 1;
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 显示详细
/// </summary>
/// <param name="ID">ID</param>
/// <returns></returns>
public List <Model.CourseSystem> Details(int ID)
{
string strSql = "";
strSql = "Select * FROM [AP_COURSE] Where [CR_ID]='" + ID + "'";
List <Model.CourseSystem> List = new List <Model.CourseSystem>();
SqlParameter[] parameters =
{
};
SqlDataReader DR;
if (GetCourseSysSetupInfo())
{
DR = CourseSys.ExecuteReader(strSql.ToString(), parameters);
}
else
{
DR = ExamSys.ExecuteReader(strSql.ToString(), parameters);
}
using (DR)
{
while (DR.Read())
{
Model.CourseSystem model = new Model.CourseSystem();
model.ID = int.Parse(DR["CR_ID"].ToString());
model.Title = DR["CR_COURSE_NAME"].ToString();
model.Abstract = DR["CR_DESC"].ToString();
model.Outline = DR["CR_OUTLINE"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
/// <summary>
/// 详细信息
/// </summary>
/// <param name="UserID">用户ID</param>
/// <param name="PapersID">试卷ID</param>
/// <returns></returns>
public List <Model.Results> Details(int UserID, int PapersID, string Time)
{
DataTable dt = DBUtility.ExamSys.gettb("SELECT SUM(Score)FROM [Results] WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'");
//改卷
if (dt.Rows[0][0].ToString() == "")
{
//根据用户ID和试卷ID查询[成绩单]中所有题目
DataTable dt_Results = DBUtility.ExamSys.gettb("SELECT [TitleListID],[Answer] FROM [Results] WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'");
int Count = dt_Results.Rows.Count;
for (int i = 0; i < Count; i++)
{
//题目ID
int TitleListID = int.Parse(dt_Results.Rows[i][0].ToString());
//用户答案
string Answer = dt_Results.Rows[i][1].ToString();
if (Answer == "T" || Answer == "F")
{
//判断题
DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [T_F],[Score] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'");
string SysScore = dt_TitleList.Rows[0]["Score"].ToString();
if (dt_TitleList.Rows[0]["T_F"].ToString() == Answer)
{
//用户答案正确
DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
else
{
if (Answer == "")
{
DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [Score],[Type] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'");
string Type = dt_TitleList.Rows[0]["Type"].ToString();
string SysScore = dt_TitleList.Rows[0]["Score"].ToString();
if (Type == "单选")
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
if (Type == "多选")
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
if (Type == "不定项选")
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
}
}
else
{
DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [Score],[Type] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'");
string Type = dt_TitleList.Rows[0]["Type"].ToString();
string SysScore = dt_TitleList.Rows[0]["Score"].ToString();
if (Type == "单选")
{
int ID = int.Parse(Answer);
DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' AND ID='" + ID + "'");
if (dt_PapersOptionsList.Rows[0]["T_F"].ToString() == "")
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
//用户答案正确
DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
else
{
if (Type == "多选")
{
DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' ORDER BY [ID] ASC");
string sAnswer = null;
for (int j = 0; j < dt_PapersOptionsList.Rows.Count; j++)
{
if (dt_PapersOptionsList.Rows[j]["T_F"].ToString() != "")
{
sAnswer = sAnswer + dt_PapersOptionsList.Rows[j]["ID"].ToString() + ",";
}
}
int length = sAnswer.Length;
sAnswer = sAnswer.Substring(0, length - 1);
string myAnswer = Answer;
int[] c = StringToInt(myAnswer);
myAnswer = "";
for (int ii = 0; ii < c.Count(); ii++)
{
myAnswer = myAnswer + c[ii] + ",";
}
int length1 = myAnswer.Length;
myAnswer = myAnswer.Substring(0, length - 1);
if (sAnswer == myAnswer)
{
//用户答案正确
DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
else
{
if (Type == "不定项选")
{
DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' ORDER BY [ID] ASC");
string sAnswer = "";
for (int j = 0; j < dt_PapersOptionsList.Rows.Count; j++)
{
if (dt_PapersOptionsList.Rows[j]["T_F"].ToString() != "")
{
sAnswer = sAnswer + dt_PapersOptionsList.Rows[j]["ID"].ToString() + ",";
}
}
int length = sAnswer.Length;
sAnswer = sAnswer.Substring(0, length - 1);
string myAnswer = Answer;
int[] c = StringToInt(myAnswer);
myAnswer = "";
for (int ii = 0; ii < c.Count(); ii++)
{
myAnswer = myAnswer + c[ii] + ",";
}
int length1 = myAnswer.Length;
myAnswer = myAnswer.Substring(0, length - 1);
if (sAnswer == myAnswer)
{
//用户答案正确
DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
else
{
//用户答案错误
DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
else
{
//值选题
int ID = int.Parse(Answer);
DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' AND ID='" + ID + "'");
//用户值答案分值
DBUtility.ExamSys.non("update Results set [Score]='" + dt_PapersOptionsList.Rows[0]["Score"].ToString() + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'");
}
}
}
}
}
}
//For结束
string strSql = "";
strSql = "Select DISTINCT [UserID],[PapersID] FROM Results WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'";
List <Model.Results> List = new List <Model.Results>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Results model = new Model.Results();
model.UserName = getUserName(UserID);
model.Score = getScore(UserID, PapersID, Time);
model.SysScore = getSysScore(UserID, PapersID, Time);
model.PapersName = getPapersName(PapersID);
model.Percentage = getPercentage(model.Score, model.SysScore);
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
else
{
string strSql = "";
strSql = "Select DISTINCT [UserID],[PapersID],[Time] FROM Results WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'";
List <Model.Results> List = new List <Model.Results>();
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Results model = new Model.Results();
model.UserName = getUserName(UserID);
model.Score = getScore(UserID, PapersID, Time);
model.SysScore = getSysScore(UserID, PapersID, Time);
model.PapersName = getPapersName(PapersID);
model.Percentage = getPercentage(model.Score, model.SysScore);
model.Time = DR["Time"].ToString();
List.Add(model);
}
DR.Close();
DR.Dispose();
}
return(List);
}
}
public List <Model.Results> GetList(int PageCount, int PageSize, string Sort, string Search, string Type)
{
string strSql = "";
strSql = "SELECT DISTINCT Exam.Results.Time FROM Exam.Papers INNER JOIN Exam.Results ON Exam.Papers.ID = Exam.Results.PapersID ";
List <Model.Results> List = new List <Model.Results>();
DataTable dt_ResultaForTime = DBUtility.ExamSys.gettb(strSql);
int dtCount = dt_ResultaForTime.Rows.Count;
//DROP tempResultsForTime表
try
{
strSql = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exam].[tempResultsForTime]') AND type in (N'U')) DROP TABLE [Exam].[tempResultsForTime]";
DBUtility.ExamSys.non(strSql);
}
catch
{ }
if (dtCount > 0)
{
for (int i = 0; i < dtCount; i++)
{
if (i == 0)
{
strSql = "Select Top 1 [Score],[UserID],[PapersID],[Time] Into tempResultsForTime FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'";
}
else
{
strSql = "Insert Into tempResultsForTime([Score],[UserID],[PapersID],[Time]) Select Top 1 [Score],[UserID],[PapersID],[Time] FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'";
}
DBUtility.ExamSys.non(strSql);
}
//string strSql = "";
string RedCountsql = "";
int RowTotal = (PageCount - 1) * PageSize;//
RedCountsql = "(select count(*) from [tempResultsForTime] where " + Type + " like '%" + Search + "%') as RowTotal";
strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [tempResultsForTime] where [Time] not in (Select Top " + RowTotal + " [Time] from [tempResultsForTime] where " + Type + " like '%" + Search + "%' ORDER BY " + Sort + ") and " + Type + " like '%" + Search + "%' ORDER BY " + Sort + "";
//strSql = "Select * FROM tempResultsForTime ORDER BY Time DESC";
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Results model = new Model.Results();
model.Time = DR["Time"].ToString();
model.UserID = int.Parse(DR["UserID"].ToString());
model.UserName = getUserName(model.UserID);
model.PapersID = int.Parse(DR["PapersID"].ToString());
model.PapersName = getPapersName(model.PapersID);
if (DR["Score"].ToString() == "")
{
model.Percentage = "";
model.Score = 0;
model.SysScore = 0;
}
else
{
model.Score = getScore(model.UserID, model.PapersID, model.Time);
model.SysScore = getSysScore(model.UserID, model.PapersID, model.Time);
model.Percentage = getPercentage(model.Score, model.SysScore);
}
model.RowTotal = int.Parse(DR["RowTotal"].ToString());
List.Add(model);
}
DR.Close();
DR.Dispose();
}
}
else
{
}
return(List);
}
/// <summary>
/// 查询所有
/// </summary>
/// <param name="UserID">用户ID</param>
/// <returns></returns>
public List <Model.Results> GetListUserID(int UserID)
{
string strSql = "";
strSql = "Select DISTINCT [Time] FROM Results Where UserID='" + UserID + "'";
List <Model.Results> List = new List <Model.Results>();
DataTable dt_ResultaForTime = DBUtility.ExamSys.gettb(strSql);
int dtCount = dt_ResultaForTime.Rows.Count;
//DROP tempResultsForTime表
try
{
strSql = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exam].[tempResultsForTime]') AND type in (N'U')) DROP TABLE [Exam].[tempResultsForTime]";
DBUtility.ExamSys.non(strSql);
}
catch
{ }
if (dtCount > 0)
{
for (int i = 0; i < dtCount; i++)
{
if (i == 0)
{
strSql = "Select Top 1 [Score],[UserID],[PapersID],[Time] Into tempResultsForTime FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'";
}
else
{
strSql = "Insert Into tempResultsForTime([Score],[UserID],[PapersID],[Time]) Select Top 1 [Score],[UserID],[PapersID],[Time] FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'";
}
DBUtility.ExamSys.non(strSql);
}
strSql = "Select * FROM tempResultsForTime ORDER BY Time DESC";
SqlParameter[] parameters =
{
};
using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters))
{
while (DR.Read())
{
Model.Results model = new Model.Results();
model.Time = DR["Time"].ToString();
model.UserID = int.Parse(DR["UserID"].ToString());
model.UserName = getUserName(model.UserID);
model.PapersID = int.Parse(DR["PapersID"].ToString());
model.PapersName = getPapersName(model.PapersID);
if (DR["Score"].ToString() == "")
{
model.Percentage = "";
model.Score = 0;
model.SysScore = 0;
}
else
{
model.Score = getScore(model.UserID, model.PapersID, model.Time);
model.SysScore = getSysScore(model.UserID, model.PapersID, model.Time);
model.Percentage = getPercentage(model.Score, model.SysScore);
}
List.Add(model);
}
DR.Close();
DR.Dispose();
}
}
else
{
}
return(List);
}