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);
}
