/// <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>
/// <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.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="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="PapersID">试卷ID</param>
/// <returns></returns>
public List <Model.PapersTitleList> GetList(string Type, int PapersID)
{
string strSql = "";
strSql = "Select * FROM [PapersTitleList] Where [Type]='" + Type + "' AND [PapersID]='" + PapersID + "' order by newid()";
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>
/// <param name="model"></param>
/// <returns></returns>
public int Add(Model.TitleList model)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@CourseID", SqlDbType.Int),
new SqlParameter("@Type", SqlDbType.NVarChar, 50),
new SqlParameter("@Title", SqlDbType.NVarChar, 500),
new SqlParameter("@T_F", SqlDbType.NVarChar, 50),
new SqlParameter("@Analysis", SqlDbType.Text),
new SqlParameter("@Extent", SqlDbType.NVarChar, 50),
new SqlParameter("@Status_id", SqlDbType.Int)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.CourseID;
parameters[2].Value = model.Type;
parameters[3].Value = model.Title;
parameters[4].Value = model.T_F;
parameters[5].Value = model.Analysis;
parameters[6].Value = model.Extent;
parameters[7].Value = model.Status_id;
ExamSys.RunProcedure("TitleList_Add", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
/// <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>
/// <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>
/// <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>
/// 删除TitleListID
/// </summary>
/// <param name="ID">所属题目ID</param>
/// <returns></returns>
public int Del_TitleListID(int ID)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@ID", SqlDbType.Int),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = ID;
ExamSys.RunProcedure("OptionsList_Del_TitleListID", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
/// <summary>
/// 显示所有
/// </summary>
/// <returns></returns>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,Name,Type ");
strSql.Append(" FROM Papers ");
SqlParameter[] parameters =
{
};
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(ExamSys.Query(strSql.ToString()));
}
/// <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-12-8
/// 获得数据列表
/// 获取该试卷题目所属StatusId
/// </summary>
public DataTable GetList(int papersID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT Status_id FROM ExaminationSystem.Exam.PapersTitleList WHERE PapersID=@papersID GROUP BY Status_id ");
List <Model.Status> List = new List <Model.Status>();
SqlParameter[] parameters =
{
new SqlParameter("@papersID", SqlDbType.Int)
};
parameters[0].Value = papersID;
DataTable dt = ExamSys.Query(strSql.ToString(), parameters).Tables[0];
return(dt);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Updata(Model.Papers model)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@Lock", SqlDbType.Int),
new SqlParameter("@Name", SqlDbType.NVarChar,50),
new SqlParameter("@ID", SqlDbType.Int),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.Lock;
parameters[2].Value = model.Name;
parameters[3].Value = model.ID;
ExamSys.RunProcedure("Papers_Updata", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(Model.OptionsList model)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@TitleListID", SqlDbType.Int),
new SqlParameter("@Name", SqlDbType.NVarChar,500),
new SqlParameter("@T_F", SqlDbType.NVarChar, 50),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.TitleListID;
parameters[2].Value = model.Name;
parameters[3].Value = model.T_F;
ExamSys.RunProcedure("OptionsList_Add", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
/// <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>
public DataTable GetStatusList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select YS_CODE,YS_DESC,YS_TYPE,YS_REMARK,YS_SEQ,YS_ID ");
strSql.Append(" FROM Status ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
List <Model.Status> List = new List <Model.Status>();
SqlParameter[] parameters =
{
};
DataTable dt = ExamSys.Query(strSql.ToString(), parameters).Tables[0];
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>
/// <param name="model"></param>
/// <returns></returns>
public int Add(Model.Results model)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@PapersID", SqlDbType.Int),
new SqlParameter("@TitleListID", SqlDbType.Int),
new SqlParameter("@Answer", SqlDbType.NVarChar,50),
new SqlParameter("@Time", SqlDbType.NVarChar, 50)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.UserID;
parameters[2].Value = model.PapersID;
parameters[3].Value = model.TitleListID;
parameters[4].Value = model.Answer;
parameters[5].Value = model.Time;
ExamSys.RunProcedure("Results_Add", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
/// <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="model"></param>
/// <returns></returns>
public int Updata(Model.HardInfo model)
{
int rowsAffected;
SqlParameter[] parameters =
{
new SqlParameter("@ReturnValue", SqlDbType.Int),
new SqlParameter("@HostName", SqlDbType.NVarChar, 500),
new SqlParameter("@CpuID", SqlDbType.NVarChar, 500),
new SqlParameter("@HardDiskID", SqlDbType.NVarChar, 500),
new SqlParameter("@NetCardMAC", SqlDbType.NVarChar, 500),
new SqlParameter("@VolOf", SqlDbType.NVarChar, 500),
new SqlParameter("@NetCardIP", SqlDbType.NVarChar, 500),
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.HostName;
parameters[2].Value = model.CpuID;
parameters[3].Value = model.HardDiskID;
parameters[4].Value = model.NetCardMAC;
parameters[5].Value = model.VolOf;
parameters[6].Value = model.NetCardIP;
ExamSys.RunProcedure("HardInfo_Updata", parameters, out rowsAffected);
return((int)parameters[0].Value);
}
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>
/// 执行SQL语句
/// </summary>
public DataTable ExecSQL(string strSql, params SqlParameter[] parames)
{
DataTable dt = ExamSys.Query(strSql.ToString(), parames).Tables[0];
return(dt);
}
/// <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);
}
/// <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);
}
}
