/// <summary> /// 自定义列表(无分页) /// </summary> /// <param name="topn">前N条,为0时表示所有符合条件的</param> /// <param name="condition">自定义条件,要以And开头</param> /// <param name="orderby">排序,不需要Order by </param> /// <returns>List<JAAJ_SubjectScoreInfo></returns> public static List <JAAJ_SubjectScoreInfo> GetListByCondition(int topn, string condition, string orderby) { string strSqlString = "SELECT "; if (topn > 0) { strSqlString += " TOP " + topn; } strSqlString += " * FROM JAAJ_SubjectScores WHERE 1=1"; if (condition != "") { strSqlString += condition; } if (orderby != "") { strSqlString += " ORDER BY " + orderby; } else { strSqlString += " ORDER BY iSubjectScoreID DESC"; } SqlDatabase odbDataBase = new SqlDatabase(connectionString); DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString); List <JAAJ_SubjectScoreInfo> olstInfo = new List <JAAJ_SubjectScoreInfo>(); JAAJ_SubjectScoreInfo oInfo = null; try { using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand)) { while (reader.Read()) { oInfo = new JAAJ_SubjectScoreInfo(); ModelHelper.LoadInfoData(oInfo, reader); olstInfo.Add(oInfo); } } } catch (Exception oeException) { throw new Exception(oeException.Message, oeException); } return(olstInfo); }
/// <summary> /// 解析成绩XML保存考生成绩 /// </summary> /// <param name="nvcBatchNO">批次号</param> /// <param name="iExamineeID">考生ID</param> public void SaveScore(string nvcBatchNO, int iExamineeID) { oepiExamProceInfo = ScoreData.GetExamProceInfoByExamineeID(iExamineeID); if (oepiExamProceInfo != null) { //int iSubjectItemID = oepiExamProceInfo.iSubjectItemID; //下面代码可以根据科目子项ID调出对应的子项对应的试题..... BaseInfo obiBaseInfo = JAAJExamManagementSys.Common.CurrentScoreFieldsSetting; foreach (SubjectItem osiSubjectItem in obiBaseInfo.SubjectItemList) { JAAJ_SubjectItemInfo osiiSubjectItemInfo = ScoreData.GetSubjectItemInfoByID(oepiExamProceInfo.iSubjectItemID); if (osiiSubjectItemInfo != null) { #region 给子项分值赋值 ossiJAAJ_SubjectScoreInfo = new JAAJ_SubjectScoreInfo(); ossiJAAJ_SubjectScoreInfo.nvcBatchNO = nvcBatchNO; //批次号 ossiJAAJ_SubjectScoreInfo.iExamineeID = iExamineeID; //考生ID,从考生表查 ossiJAAJ_SubjectScoreInfo.iSubjectID = osiiSubjectItemInfo.iSubjectID; ossiJAAJ_SubjectScoreInfo.iSubjectItemID = osiiSubjectItemInfo.iSubjectItemID; ossiJAAJ_SubjectScoreInfo.decSubjectScore = osiSubjectItem.SubjectItemScore; //考生本次考试所考子项总分值 ossiJAAJ_SubjectScoreInfo.datScoreDate = DateTime.Now; ossiJAAJ_SubjectScoreInfo.nvcMemo = ""; #endregion foreach (Title otTitle in osiSubjectItem.TitleList[0].TitleList) { #region 加入试题成绩 JAAJ_SubItemScoreInfo osisiJAAJ_SubItemScoreInfo = new JAAJ_SubItemScoreInfo(); osisiJAAJ_SubItemScoreInfo.iExamineeID = iExamineeID; //需要根据身份证号查出赋值 osisiJAAJ_SubItemScoreInfo.iTitleID = 0; osisiJAAJ_SubItemScoreInfo.nvcTitleName = otTitle.TitleName; osisiJAAJ_SubItemScoreInfo.nvcDescription = otTitle.TitleDescription; osisiJAAJ_SubItemScoreInfo.decSubjectItemScore = otTitle.TitleScore; //本道题本次考试所得分值 osisiJAAJ_SubItemScoreInfo.nvcMemo = ""; #endregion #region 循环加入试题对应的步骤成绩 if (otTitle.StepList.Count > 0) { foreach (Step osStep in otTitle.StepList[0].StepList) { JAAJ_StepScoreInfo ossiJAAJ_StepScoreInfo = new JAAJ_StepScoreInfo(); ossiJAAJ_StepScoreInfo.nvcStepName = osStep.StepName; ossiJAAJ_StepScoreInfo.nvcDescription = osStep.StepDescription; ossiJAAJ_StepScoreInfo.decStepScore = osStep.StepScore; //本步骤本次考试所得分值 ossiJAAJ_StepScoreInfo.nvcMemo = ""; osisiJAAJ_SubItemScoreInfo.JAAJ_StepScoreInfoList.Add(ossiJAAJ_StepScoreInfo); } } #endregion osisiJAAJ_SubItemScoreInfoList.Add(osisiJAAJ_SubItemScoreInfo); } EnumSubmitResult enResult = ScoreData.Save(ossiJAAJ_SubjectScoreInfo, osisiJAAJ_SubItemScoreInfoList); if (enResult == EnumSubmitResult.Success) { //MessageBox.Show("分值提交成功", "提示"); } else { //MessageBox.Show("分值提交失败", "提示"); } } } } }
public void SaveScore() { //本次考题得分总数; int FireSum = 0; //第一步分数; int OneFire = 0;; //第二步骤得分; int twoFire = 0; //判断着火的类型与灭火器选择的分数; //String //FireStruct.sFireType = s[2];//着火的类型 1:水基灭火 int nTemp = 0; if (nTemp == 0) { OneFire = 15; } else { OneFire = 0; } //是否成功对准火焰根部灭火; int nTemp1 = 0; if (nTemp1 == 0) { twoFire = 10; } else { twoFire = 0; } FireSum = twoFire + OneFire; string sFireSum = Convert.ToString(FireSum); sFireSum = "本次灭火总成绩:" + sFireSum; JAAJ_SubjectItemInfo osiiSubjectItemInfo = ScoreData.GetSubjectItemInfoByID(oepiExamProceInfo.iSubjectItemID); if (osiiSubjectItemInfo != null) { #region 给子项总分值赋值 //给子项总分值赋值 ossiJAAJ_SubjectScoreInfo = new JAAJ_SubjectScoreInfo(); ossiJAAJ_SubjectScoreInfo.nvcBatchNO = oeiJAAJ_ExamineeInfo.nvcBatchNO; ossiJAAJ_SubjectScoreInfo.iExamineeID = oeiJAAJ_ExamineeInfo.iExamineeID; ossiJAAJ_SubjectScoreInfo.iSubjectID = osiiSubjectItemInfo.iSubjectID; ossiJAAJ_SubjectScoreInfo.iSubjectItemID = osiiSubjectItemInfo.iSubjectItemID; ossiJAAJ_SubjectScoreInfo.decSubjectScore = FireSum; //考生本次考试所考子项总分值 ossiJAAJ_SubjectScoreInfo.datScoreDate = DateTime.Now; ossiJAAJ_SubjectScoreInfo.nvcMemo = ""; #endregion #region 第一题 //第一题 JAAJ_SubItemScoreInfo osisiJAAJ_SubItemScoreInfo = new JAAJ_SubItemScoreInfo(); osisiJAAJ_SubItemScoreInfo.iExamineeID = oeiJAAJ_ExamineeInfo.iExamineeID; osisiJAAJ_SubItemScoreInfo.iTitleID = 0; osisiJAAJ_SubItemScoreInfo.nvcTitleName = "厨房灭火"; osisiJAAJ_SubItemScoreInfo.nvcDescription = "厨房灭火"; osisiJAAJ_SubItemScoreInfo.decSubjectItemScore = FireSum; //本道题本次考试所得分值 osisiJAAJ_SubItemScoreInfo.nvcMemo = ""; #region 第一题试题步骤实体集赋值 #region 第一步骤 JAAJ_StepScoreInfo ossiJAAJ_StepScoreInfo = new JAAJ_StepScoreInfo(); ossiJAAJ_StepScoreInfo.nvcStepName = "灭火器选择"; ossiJAAJ_StepScoreInfo.nvcDescription = "灭火器选择"; ossiJAAJ_StepScoreInfo.decStepScore = OneFire;//本步骤本次考试所得分值 ossiJAAJ_StepScoreInfo.nvcMemo = ""; osisiJAAJ_SubItemScoreInfo.JAAJ_StepScoreInfoList.Add(ossiJAAJ_StepScoreInfo); #endregion #region 第二步骤 ossiJAAJ_StepScoreInfo = new JAAJ_StepScoreInfo(); ossiJAAJ_StepScoreInfo.nvcStepName = "是否对准火焰根部,开始灭火"; ossiJAAJ_StepScoreInfo.nvcDescription = "是否对准火焰根部,开始灭火"; ossiJAAJ_StepScoreInfo.decStepScore = twoFire;//本步骤本次考试所得分值 ossiJAAJ_StepScoreInfo.nvcMemo = ""; osisiJAAJ_SubItemScoreInfo.JAAJ_StepScoreInfoList.Add(ossiJAAJ_StepScoreInfo); #endregion #endregion osisiJAAJ_SubItemScoreInfoList.Add(osisiJAAJ_SubItemScoreInfo); #endregion EnumSubmitResult enResult = ScoreData.Save(ossiJAAJ_SubjectScoreInfo, osisiJAAJ_SubItemScoreInfoList); if (enResult == EnumSubmitResult.Success) { //MessageBox.Show("分值提交成功", "提示"); } else { //MessageBox.Show("分值提交失败", "提示"); } } }
/// <summary> /// 获取该批次考生每个子项的成绩 /// </summary> /// <param name="strBatchNO">选中批次号</param> /// <param name="strDate">考试日期</param> /// <returns></returns> public static List <JAAJ_SubjectScoreInfo> GetListWithTotalScore(string strSql, string strBatch) { StringBuilder strSqlString = new StringBuilder(); strSqlString.Append("SELECT subjectScore.iExamineeID,nvcName,nvcExamineeGUID,examinee.nvcMemo AS ExamineeGUID, subjectScore.nvcBatchNO, nvcIDNum, subjects.nvcSubjectName,subjectScore.iSubjectID, SUM(decSubjectScore) AS sumScore , CONVERT(varchar(10),datScoreDate,23) AS datScoreDate,batch.nvcMemo AS batchGUID "); strSqlString.Append(" FROM JAAJ_Examinees AS examinee, JAAJ_SubjectScores AS subjectScore, JAAJ_Subjects AS subjects ,JAAJ_SubjectItems AS subItems,JAAJ_Exams as Exam,JAAJ_Batchs as batch "); //2015-06-17:增加 “subjectScore.iSubjectScoreID IN (SELECT MAX(iSubjectScoreID) AS iSubjectScoreID FROM JAAJ_SubjectScores GROUP BY isubjectItemId,iExamineeID) AND” 作用:用于取出当天最后一次成绩,用于规避如果考生当天多次考试,成绩计算重复计算 strSqlString.Append(" WHERE subjectScore.iSubjectScoreID IN (SELECT MAX(iSubjectScoreID) AS iSubjectScoreID FROM JAAJ_SubjectScores GROUP BY isubjectItemId,iExamineeID) AND examinee.iExamineeID=subjectScore.iExamineeID AND Exam.iExamStatus=0 AND subjects.iSubjectID=subjectScore.iSubjectID AND subItems.iSubjectItemID= subjectScore.iSubjectItemID AND CONVERT(varchar(10),datScoreDate,23)=CONVERT(varchar(10),Exam.datExamDate,23) AND subjectScore.nvcBatchNO=batch.nvcBatchNO "); if (!string.IsNullOrEmpty(strSql)) { strSqlString.Append(strSql); } strSqlString.Append(" GROUP BY subjects.nvcSubjectName, subjectScore.iSubjectID, subjectScore.iSubjectID, subjectScore.iExamineeID, examinee.nvcName, examinee.nvcExamineeGUID, subjectScore.nvcBatchNO,nvcIDNum,CONVERT(varchar(10),datScoreDate,23),examinee.nvcMemo,batch.nvcMemo ORDER BY subjectScore.nvcBatchNO DESC, subjectScore.iExamineeID "); string strFinalSql = strSqlString.ToString(); SqlDatabase odbDataBase = new SqlDatabase(connectionString); DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strFinalSql); List <JAAJ_SubjectScoreInfo> olstInfo = new List <JAAJ_SubjectScoreInfo>(); JAAJ_SubjectScoreInfo oInfo = null; List <JAAJ_SubjectScoreInfo> ListInfo = new List <JAAJ_SubjectScoreInfo>(); JAAJ_SubjectScoreInfo ScoreInfo = new JAAJ_SubjectScoreInfo(); try { using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand)) { while (reader.Read()) { oInfo = new JAAJ_SubjectScoreInfo(); ModelHelper.LoadInfoData(oInfo, reader); olstInfo.Add(oInfo); } } //获取所考科目的得分权重 List <JAAJ_ExamSubjectInfo> ListPersent = GetPersent(strBatch); //每个科目子项的成绩乘以对应的权重 for (int i = 0; i < olstInfo.Count; i++) { for (int j = 0; j < ListPersent.Count; j++) { if (olstInfo[i].iSubjectID == ListPersent[j].iSubjectID && olstInfo[i].datScoreDate == ListPersent[j].datExamDate) { olstInfo[i].decTotalScore += olstInfo[i].sumScore * ListPersent[j].decExamPersent; } } } //得到每个考生对应日期的考试总成绩 var result = from p in olstInfo group p by new { p.iExamineeID, p.nvcIDNum, p.nvcName, p.nvcBatchNO, p.datScoreDate, p.nvcExamineeGUID } into g select new { nvcExamineeGUID = g.Key.nvcExamineeGUID, nvcIDNum = g.Key.nvcIDNum, nvcBatchNO = g.Key.nvcBatchNO, nvcName = g.Key.nvcName, decTotalScore = g.Sum(p => p.decTotalScore), datScoreDate = g.Key.datScoreDate, //iSubjectScoreID = g.Select(p => p.iSubjectScoreID), iExamineeID = g.Key.iExamineeID }; ListInfo = new List <JAAJ_SubjectScoreInfo>(); foreach (var r in result) { ScoreInfo = new JAAJ_SubjectScoreInfo(); ScoreInfo.nvcExamineeGUID = r.nvcExamineeGUID.ToString(); ScoreInfo.nvcIDNum = r.nvcIDNum.ToString(); ScoreInfo.nvcBatchNO = r.nvcBatchNO.ToString(); ScoreInfo.nvcName = r.nvcName.ToString(); ScoreInfo.decTotalScore = r.decTotalScore; ScoreInfo.datScoreDate = DateTime.Parse(r.datScoreDate.ToString()); ScoreInfo.iExamineeID = r.iExamineeID; ListInfo.Add(ScoreInfo); } } catch (Exception oeException) { throw new Exception(oeException.Message, oeException); } return(ListInfo); }
public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();// "Data Source=WANGCHAO;Database=JAAJ;user id=sa;password=123456;Connect Timeout=30"; #region 保存客户端分值 Save /// <summary> /// 保存客户端分值 /// </summary> /// <param name="info">子项成绩</param> /// <param name="osisiJAAJ_SubItemScoreInfoList">子项试题成绩与试题步骤成绩</param> /// <returns>保存结果,成功或失败</returns> public static EnumSubmitResult Save(JAAJ_SubjectScoreInfo info, List <JAAJ_SubItemScoreInfo> osisiJAAJ_SubItemScoreInfoList) { int i = 0; string strStoreProcedure = "JAAJ_SubjectScore_Save"; SqlDatabase odbDataBase = new SqlDatabase(connectionString); DbConnection conn = odbDataBase.CreateConnection(); DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure); conn.Open(); DbTransaction odtTran = conn.BeginTransaction(); EnumSubmitResult enmResult = EnumSubmitResult.Failed; try { odbDataBase.AddInParameter(odbCommand, "@iSubjectScoreID", System.Data.DbType.Int32, info.iSubjectScoreID); odbDataBase.AddInParameter(odbCommand, "@nvcBatchNO", System.Data.DbType.String, info.nvcBatchNO); odbDataBase.AddInParameter(odbCommand, "@iExamineeID", System.Data.DbType.Int32, info.iExamineeID); odbDataBase.AddInParameter(odbCommand, "@iSubjectID", System.Data.DbType.Int32, info.iSubjectID); odbDataBase.AddInParameter(odbCommand, "@iSubjectItemID", System.Data.DbType.Int32, info.iSubjectItemID); odbDataBase.AddInParameter(odbCommand, "@decSubjectScore", System.Data.DbType.Decimal, info.decSubjectScore); odbDataBase.AddInParameter(odbCommand, "@datScoreDate", System.Data.DbType.DateTime, info.datScoreDate); odbDataBase.AddInParameter(odbCommand, "@nvcMemo", System.Data.DbType.String, info.nvcMemo); odbDataBase.AddOutParameter(odbCommand, "@result", System.Data.DbType.Int32, 8); odbDataBase.AddOutParameter(odbCommand, "@iSubjectScoreID2", System.Data.DbType.Int32, 8); odbDataBase.ExecuteNonQuery(odbCommand, odtTran); i = int.Parse(odbCommand.Parameters["@result"].Value.ToString()); int iSubjectScoreID = (int)odbDataBase.GetParameterValue(odbCommand, "@iSubjectScoreID2"); enmResult = (EnumSubmitResult)i; if (enmResult == EnumSubmitResult.Failed) { odtTran.Rollback(); return(enmResult = EnumSubmitResult.Failed); } //循环执行科目子项试题分数保存 foreach (JAAJ_SubItemScoreInfo osisinfo in osisiJAAJ_SubItemScoreInfoList) { strStoreProcedure = "JAAJ_SubItemScore_Save"; odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure); odbDataBase.AddInParameter(odbCommand, "@iSubItemScoreID", System.Data.DbType.Int32, osisinfo.iSubItemScoreID); odbDataBase.AddInParameter(odbCommand, "@iSubjectScoreID", System.Data.DbType.Int32, iSubjectScoreID); odbDataBase.AddInParameter(odbCommand, "@iExamineeID", System.Data.DbType.Int32, osisinfo.iExamineeID); odbDataBase.AddInParameter(odbCommand, "@iTitleID", System.Data.DbType.Int32, osisinfo.iTitleID); odbDataBase.AddInParameter(odbCommand, "@nvcTitleName", System.Data.DbType.String, osisinfo.nvcTitleName); odbDataBase.AddInParameter(odbCommand, "@nvcDescription", System.Data.DbType.String, osisinfo.nvcDescription); odbDataBase.AddInParameter(odbCommand, "@decSubjectItemScore", System.Data.DbType.Decimal, osisinfo.decSubjectItemScore); odbDataBase.AddInParameter(odbCommand, "@nvcMemo", System.Data.DbType.String, osisinfo.nvcMemo); odbDataBase.AddOutParameter(odbCommand, "@result", System.Data.DbType.Int32, 8); odbDataBase.AddOutParameter(odbCommand, "@iSubItemScoreID2", System.Data.DbType.Int32, 8); odbDataBase.ExecuteNonQuery(odbCommand, odtTran); i = int.Parse(odbCommand.Parameters["@result"].Value.ToString()); int iSubItemScoreID = (int)odbDataBase.GetParameterValue(odbCommand, "@iSubItemScoreID2"); enmResult = (EnumSubmitResult)i; if (enmResult == EnumSubmitResult.Failed) { odtTran.Rollback(); return(enmResult = EnumSubmitResult.Failed); } //循环执行科目子项试题步骤分数保存 strStoreProcedure = "JAAJ_StepScore_Save"; foreach (JAAJ_StepScoreInfo ossiinfo in osisinfo.JAAJ_StepScoreInfoList) { odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure); odbDataBase.AddInParameter(odbCommand, "@iStepScoreID", System.Data.DbType.Int32, ossiinfo.iStepScoreID); odbDataBase.AddInParameter(odbCommand, "@iSubItemScoreID", System.Data.DbType.Int32, iSubItemScoreID); odbDataBase.AddInParameter(odbCommand, "@iStepID", System.Data.DbType.Int32, ossiinfo.iStepID); odbDataBase.AddInParameter(odbCommand, "@nvcStepName", System.Data.DbType.String, ossiinfo.nvcStepName); odbDataBase.AddInParameter(odbCommand, "@nvcDescription", System.Data.DbType.String, ossiinfo.nvcDescription); odbDataBase.AddInParameter(odbCommand, "@decStepScore", System.Data.DbType.Decimal, ossiinfo.decStepScore); odbDataBase.AddInParameter(odbCommand, "@nvcMemo", System.Data.DbType.String, ossiinfo.nvcMemo); odbDataBase.AddOutParameter(odbCommand, "@result", System.Data.DbType.Int32, 8); odbDataBase.ExecuteNonQuery(odbCommand, odtTran); i = int.Parse(odbCommand.Parameters["@result"].Value.ToString()); enmResult = (EnumSubmitResult)i; //出错不在往下执行,直接回滚 if (enmResult == EnumSubmitResult.Failed)//失败 { odtTran.Rollback(); return(enmResult = EnumSubmitResult.Failed); } } } odtTran.Commit(); } catch { odtTran.Rollback(); enmResult = EnumSubmitResult.Failed; } odbCommand.Dispose(); odtTran.Dispose(); conn.Close(); return(enmResult); }
private void button_Click(object sender, RoutedEventArgs e) { //提交成绩 List <JAAJ_SubItemScoreInfo> listsuItemScoreInfos = new List <JAAJ_SubItemScoreInfo>(); string cardNum = ""; int batchId = 0; //查询考生信息 StringBuilder buiderSql = new StringBuilder(); buiderSql.Append("select "); buiderSql.Append("exam.iexamineeid,exam.nvcName,exam.bsex,exam.nvcNation,"); buiderSql.Append("exam.nvcIDNum,sub.iSubjectID,sub.nvcSubjectItemIDs,bat.nvcBatchNo "); buiderSql.Append(" from JAAJ_Batchs bat "); buiderSql.Append("left join JAAJ_BatchExamineeMap map on bat.iBatchID=map.iBatchID "); buiderSql.Append("left join JAAJ_Examinees exam on map.iExamineeID=exam.iExamineeID "); buiderSql.Append("left join JAAJ_Exams exams on exams.nvcBatchNO=bat.nvcBatchNO "); buiderSql.Append(" left join JAAJ_ExamSubjects sub on sub.iExamID=exams.iExamID "); buiderSql.Append(" where exam.nvcIDNum = '" + cardNum + "' and bat.iBatchID='" + batchId + "'"); DataTable table = SQLHelp.GetDataTable(buiderSql.ToString()); if (table == null) { return; } JAAJ_SubjectScoreInfo scorInfo = new JAAJ_SubjectScoreInfo(); scorInfo.iExamineeID = int.Parse(table.Rows[0]["iexamineeid"].ToString()); scorInfo.iSubjectID = int.Parse(table.Rows[0]["iSubjectID"].ToString()); scorInfo.iSubjectItemID = int.Parse(table.Rows[0]["nvcSubjectItemIDs"].ToString()); scorInfo.nvcBatchNO = table.Rows[0]["nvcBatchNo"].ToString(); scorInfo.datScoreDate = DateTime.Now; scorInfo.nvcIDNum = cardNum; scorInfo.nvcName = table.Rows[0]["nvcName"].ToString(); scorInfo.decTotalScore = 100; JAAJ_SubItemScoreInfo itemScoreInfo = new JAAJ_SubItemScoreInfo(); itemScoreInfo.nvcTitleName = "人工考试"; itemScoreInfo.iExamineeID = int.Parse(table.Rows[0]["iexamineeid"].ToString()); for (int i = 0; i < 10; i++) { JAAJ_StepScoreInfo stepScoreInfo = new JAAJ_StepScoreInfo(); stepScoreInfo.decStepScore = 10; stepScoreInfo.decTitleScore = 10; stepScoreInfo.nvcStepName = "步骤" + i + 1 + "操作正确"; itemScoreInfo.decSubjectItemScore += stepScoreInfo.decStepScore; itemScoreInfo.JAAJ_StepScoreInfoList.Add(stepScoreInfo); scorInfo.sumScore += stepScoreInfo.decStepScore; } }