/// <summary>
/// 添加
/// 扩展
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddExtend(Model.Results model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Company(");
strSql.Append("UserID,PapersID,TitleListID,Answer,Time,SubmitTime)");
strSql.Append(" values (");
strSql.Append("@UserID,@PapersID,@TitleListID,@Answer,@Time,@SubmitTime)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
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),
new SqlParameter("@SubmitTime", SqlDbType.DateTime)//添加提交时间
};
parameters[0].Value = model.UserID;
parameters[1].Value = model.PapersID;
parameters[2].Value = model.TitleListID;
parameters[3].Value = model.Answer;
parameters[4].Value = model.Time;
parameters[5].Value = model.SubmitTime;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.Results model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Results set ");
strSql.Append("Answer=@Answer,");
strSql.Append("Time=@Time");
strSql.Append(" where UserID=@UserID and PapersID=@PapersID and TitleListID=@TitleListID");
SqlParameter[] parameters =
{
new SqlParameter("@Answer", SqlDbType.NVarChar, 50),
new SqlParameter("@Time", SqlDbType.NVarChar, 50),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@PapersID", SqlDbType.Int),
new SqlParameter("@TitleListID", SqlDbType.Int)
};
parameters[0].Value = model.Answer;
parameters[1].Value = model.Time;
parameters[2].Value = model.UserID;
parameters[3].Value = model.PapersID;
parameters[4].Value = model.TitleListID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 提交答案
/// </summary>
/// <param name="Type">类型(单选/多选/不定项选/判断)</param>
/// <param name="PapersID">试卷ID</param>
/// <param name="gv">GridView</param>
private void Submit(string Type, int PapersID, GridView gv, string Time)
{
BLL.PapersTitleList bll = new BLL.PapersTitleList();
if (bll.nGetList(Type, PapersID))
{
for (int i = 0; i < gv.Rows.Count; i++)
{
Label lb = (Label)gv.Rows[i].FindControl("lb_ID");
int TitleListID = int.Parse(lb.Text);
string Str_radio = Request.Form["radio" + TitleListID];
//Response.Write(Type + "题目：" + TitleListID + " 答案：" + Str_radio + "<br/>");
BLL.Results bll1 = new BLL.Results();
Model.Results model = new Model.Results();
model.UserID = int.Parse(lb_U.Text);
model.PapersID = PapersID;
model.TitleListID = TitleListID;
model.Answer = Str_radio;
model.Time = Time;
//Common.Function.Alert(model.UserID.ToString());
//bll1.Add(model);
}
}
else
{
}
}
/// <summary>
/// 添加
/// </summary>
public void Add(Model.Results model)
{
if (dal.Add(model) > 0)
{
//Common.Function.AlertRedirect("交卷成功！", "/Aspx/Success.htm");
}
else
{
//Common.Function.AlertRedirect("交卷失败！系统繁忙请稍后再试！", "/Aspx/Failure.htm");
}
}
/// <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>
public bool UpdateExtend(Model.Results model)
{
return(dal.UpdateExtend(model));
}
/// <summary>
/// 添加
/// add by wangke 2016-12-12
/// </summary>
public int AddResult(Model.Results model)
{
return(dal.Add(model));
}
/// <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);
}
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>
/// <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);
}
}
