protected void btnAdd_Click(object sender, EventArgs e) { DalOperationAboutGradeCheck dal = new DalOperationAboutGradeCheck(); string dtime = DateTime.Now.ToString(); using (TransactionScope scope = new TransactionScope()) { try { foreach (Control ctlTable in PlaceHolder1.Controls) { foreach (Control ctlTableRow in ctlTable.Controls) { foreach (Control ctlTableCell in ctlTableRow.Controls) { foreach (Control ctl in ctlTableCell.Controls) { if (ctl.GetType().ToString().Trim() == "System.Web.UI.WebControls.TextBox") { StudentsGradeCheckDetail model = new StudentsGradeCheckDetail(); model.studentNo = studentNo; model.updateTime = Convert.ToDateTime(dtime); model.gradeCheckDetailValue = ((TextBox)ctl).Text.Trim(); model.gradeCheckId = int.Parse(ctl.ID.Split("_".ToCharArray())[1]); dal.AddGradeCheckDetailByStudentNo(model); } } } } } StudentsGradeCheckConfirm model1 = new StudentsGradeCheckConfirm { studentNo = studentNo, updateTime = Convert.ToDateTime(dtime), isAccord = int.Parse(ddlIsAccord.SelectedValue), remark = remark.Text.Trim() }; dal.AddStudentGradeCheckConfirm(model1); scope.Complete(); Javascript.RefreshParentWindow("添加成绩审核记录成功!", "/Administrator/StudentManager.aspx?fragment=7&studentNo=" + studentNo, Page); } catch (Exception ex) { MongoDBLog.LogRecord(ex); Javascript.GoHistory(-1, "添加成绩审核记录失败!", Page); } finally { dal.conn.Close(); } } }
/// <summary> /// 读取Excel数据并返回BllImportGradeCheckExcelData类型的集合给Dal进行处理 /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="_termYear">学年</param> /// <returns>返回GradeCheckExcelData实体类</returns> public static GradeCheckExcelData BllImportGradeCheckExcelData(string filePath,string _termYear) { //设定一个自定义GradeCheckExcelData类型,用于返回全部的Excel数据 GradeCheckExcelData gradeCheckExcelData = null; //出错的行号 int exceptionRowNo = 0; //出错的列号 int exceptionColNo = 0; //自定义出错信息 string errorInfo = "导入的Excel数据中存在学生学年与指定学年不匹配的记录!"; List<StudentsGradeCheckConfirm> _listStudentsGradeCheckConfirm = new List<StudentsGradeCheckConfirm>(); List<StudentsGradeCheckDetail> _listStudentsGradeCheckDetail = new List<StudentsGradeCheckDetail>(); int _errorNo = 0; try { using (FileStream file = new FileStream(filePath, FileMode.Open)) { //建立WorkBook HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(file); HSSFSheet Sheet = HSSFWorkbook.GetSheetAt(0); IEnumerator rows = Sheet.GetRowEnumerator(); //导入时间 DateTime _now = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //获取“是否符合学位申请条件”与“不及格科目”两个特定列的列号 int isAccordColumnNo = -1; int remarkColumnNo = -1; //Hashtable ht = new Hashtable(); Dictionary<string, string> dic = new Dictionary<string, string>(); int _columnsCount=0; while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; StudentsGradeCheckConfirm _studentsGradeCheckConfirm = new StudentsGradeCheckConfirm(); _studentsGradeCheckConfirm.updateTime = _now; //HttpContext.Current.Response.Write(_columnsCount + "<Br/>"); if (exceptionRowNo == 0) { _columnsCount = row.PhysicalNumberOfCells; for (int j = 0; j < _columnsCount; j++) { string _value = row.GetCell(j).ToString().Trim(); //HttpContext.Current.Response.Write(_value+"<Br/>"); switch (_value) { case "是否符合学位申请条件": isAccordColumnNo = j; break; case "不及格科目": remarkColumnNo = j; break; default: break; } if (j > 5 && _value != "是否符合学位申请条件" && _value != "不及格科目") { //ht.Add("_cols"+j.ToString().Trim(), _value); dic.Add("_cols" + j.ToString().Trim(), _value); } } } //HttpContext.Current.Response.Write(_columnsCount); //HttpContext.Current.Response.End(); //HttpContext.Current.Response.Write(ht.Count); //foreach (DictionaryEntry _item in ht) //{ // HttpContext.Current.Response.Write(_item.Key + ":" + _item.Value + "<br/>"); // ; //} //HttpContext.Current.Response.Write(isAccordColumnNo+"<br/>"); //HttpContext.Current.Response.Write(remarkColumnNo); //HttpContext.Current.Response.End(); //从第二行开始读取数据(并且第一列序号数据不为空) if (exceptionRowNo > 0 && !string.IsNullOrEmpty(row.GetCell(0).ToString().Trim())) { string _studentNo = string.Empty; for (int j = 0; j < _columnsCount; j++) { //HttpContext.Current.Response.Write(j.ToString().Trim()); exceptionColNo = j; string _value = row.GetCell(j).ToString().Trim(); switch (j) { //序号 case 0: //HttpContext.Current.Response.Write(_value + "<Br/>"); break; //学号 case 1: //HttpContext.Current.Response.Write(_value + "<Br/>"); _studentsGradeCheckConfirm.studentNo = _value; _studentNo = _value; break; //姓名 case 2: //HttpContext.Current.Response.Write(_value + "<Br/>"); break; //班级 case 3: //HttpContext.Current.Response.Write(_value + "<Br/>"); break; //年级 case 4: //HttpContext.Current.Response.Write(_value + "<Br/>"); //HttpContext.Current.Response.End(); //判断学年是否正确 if (_value.Length >= 2 && _value.Substring(0, 2) != _termYear) { throw new Exception(errorInfo + "当前选择的学年为:" + _termYear + ",读取到的学年值为:" + _value + ",请检查!"); } break; //班主任 case 5: break; default: break; } if (j > 5) { if (isAccordColumnNo == j) { //判断是否符合学位申请列的数据 if ((_value != "符合" && _value != "不符合")) { throw new Exception("是否符合学位申请条件列的值只能为“符合”或者“不符合”,当前读取到的值为“" + _value +"”"); } _studentsGradeCheckConfirm.isAccord = (_value == "符合" ? 1 : 0); } else if (remarkColumnNo == j) { _studentsGradeCheckConfirm.remark = _value; } //动态变化列 else { //HttpContext.Current.Response.Write(j+"_"+ht[(object)("_cols" + j).ToString().Trim()].ToString().Trim()+"<br/>"); //_errorNo = j; //if (!dic.ContainsKey("_cols" + j)) //{ // HttpContext.Current.Response.Write(j+"_"+exceptionRowNo+"_"+"<br/>"); //} _listStudentsGradeCheckDetail.Add(new StudentsGradeCheckDetail { updateTime = _now, studentNo = _studentNo, //gradeCheckItemName = ht[(object)("_cols"+j).ToString().Trim()].ToString().Trim(), gradeCheckItemName = dic["_cols"+j].ToString().Trim(), termYear = _termYear, gradeCheckDetailValue = _value, colNo = j + 1 }); if (j == 7) { //HttpContext.Current.Response.Write(_value); //HttpContext.Current.Response.End(); } } } } _listStudentsGradeCheckConfirm.Add(_studentsGradeCheckConfirm); } exceptionRowNo += 1; } gradeCheckExcelData = new GradeCheckExcelData { listStudentsGradeCheckConfirm = _listStudentsGradeCheckConfirm, listStudentsGradeCheckDetail = _listStudentsGradeCheckDetail }; } // HttpContext.Current.Response.End(); } catch (Exception ex) { //HttpContext.Current.Response.Write(_errorNo); MongoDBLog.LogRecord(ex); HttpContext.Current.Response.Write("<script type='text/javascript'>alert('很抱歉,读取Excel文件数据失败!此次操作未更改任何数据库数据,相关信息如下:\\n\\n出错的单元格行号为:" + (exceptionRowNo + 1) + "\\n出错的单元格列号为:" + (exceptionColNo+1) + "\\n可能的原因为:" + ex.Message + "请检查Excel文件数据,修改后重新上传!');history.go(-1);</script>"); } return gradeCheckExcelData; }
/// <summary> /// 根据学号和成绩审核时间删除学位确认记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public int DeleteStudentGradeCheckConfirmByStudentNoAndUpdateTime(StudentsGradeCheckConfirm model) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from usta_StudentsGradeCheckConfirm "); strSql.Append(" where updateTime=@updateTime AND studentNo=@studentNo"); SqlParameter[] parameters = { new SqlParameter("@studentNo", SqlDbType.NChar,10), new SqlParameter("@updateTime", SqlDbType.DateTime)}; parameters[0].Value = model.studentNo; parameters[1].Value = model.updateTime; return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters); }
/// <summary> /// 添加一条确认记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public int AddStudentGradeCheckConfirm(StudentsGradeCheckConfirm model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into usta_StudentsGradeCheckConfirm("); strSql.Append("studentNo,updateTime,isAccord,remark)"); strSql.Append(" values ("); strSql.Append("@studentNo,@updateTime,@isAccord,@remark)"); SqlParameter[] parameters = { new SqlParameter("@studentNo", SqlDbType.NChar,10), new SqlParameter("@updateTime", SqlDbType.DateTime), new SqlParameter("@isAccord", SqlDbType.TinyInt,1), new SqlParameter("@remark", SqlDbType.NVarChar,500)}; parameters[0].Value = model.studentNo; parameters[1].Value = model.updateTime; parameters[2].Value = model.isAccord; parameters[3].Value = model.remark; return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters); }
/// <summary> /// 根据学号和成绩审核时间更新是否符合学位申请 /// </summary> /// <param name="model"></param> /// <returns></returns> public int UpdateStudentGradeCheckConfirmByStudentNoAndUpdateTime(StudentsGradeCheckConfirm model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update usta_StudentsGradeCheckConfirm set "); strSql.Append("isAccord=@isAccord,remark=@remark"); strSql.Append(" where updateTime=@updateTime AND studentNo=@studentNo"); SqlParameter[] parameters = { new SqlParameter("@studentNo", SqlDbType.NChar,10), new SqlParameter("@updateTime", SqlDbType.DateTime), new SqlParameter("@isAccord", SqlDbType.TinyInt,1), new SqlParameter("@remark", SqlDbType.NVarChar,500)}; parameters[0].Value = model.studentNo; parameters[1].Value = model.updateTime; parameters[2].Value = model.isAccord; parameters[3].Value = model.remark; return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), parameters); }