/// <summary>
/// 导入题目（不存在时返回null）
/// </summary>
/// <param name="request">Http请求</param>
/// <param name="fileType">文件类型</param>
/// <param name="uploadType">上传方式</param>
/// <param name="content">文件内容</param>
/// <param name="file">上传文件</param>
/// <returns>题目数据是否插入成功集合（全部失败时为null）</returns>
public static IMethodResult AdminImportProblem(HttpRequestBase request, String fileType, String uploadType, String content, HttpPostedFileBase file)
{
if (!AdminManager.HasPermission(PermissionType.SuperAdministrator))
{
throw new NoPermissionException();
}
if (!String.Equals("1", fileType))
{
return(MethodResult.FailedAndLog("File type is INVALID!"));
}
if (String.Equals("1", uploadType))//从文件上传
{
if (file == null)
{
return(MethodResult.FailedAndLog("No file was uploaded!"));
}
StreamReader sr = new StreamReader(file.InputStream);
content = sr.ReadToEnd();
}
//转换题库模型
List <ProblemEntity> problems = null;
List <Byte[]> datas = null;
List <Dictionary <String, Byte[]> > images = null;
Dictionary <String, Byte[]> imagefiles = new Dictionary <String, Byte[]>();
if (!ProblemImport.TryImportFreeProblemSet(content, out problems, out datas, out images))
{
return(MethodResult.FailedAndLog("File content is INVALID!"));
}
if (problems == null || problems.Count == 0)
{
return(MethodResult.FailedAndLog("No problem was imported!"));
}
//处理题目及图片路径
for (Int32 i = 0; i < problems.Count; i++)
{
problems[i].IsHide = true;
problems[i].LastDate = DateTime.Now;
if (images[i] == null)
{
continue;
}
String uploadRoot = ConfigurationManager.UploadDirectoryUrl;
foreach (KeyValuePair <String, Byte[]> pair in images[i])
{
if (pair.Value == null || !pair.Key.Contains("."))
{
continue;
}
String oldUrl = pair.Key;
String fileNewName = MD5Encrypt.EncryptToHexString(oldUrl + DateTime.Now.ToString("yyyyMMddHHmmssffff"), true) + pair.Key.Substring(pair.Key.LastIndexOf('.'));
String newUrl = uploadRoot + fileNewName;
problems[i].Description = problems[i].Description.Replace(oldUrl, newUrl);
problems[i].Input = problems[i].Input.Replace(oldUrl, newUrl);
problems[i].Output = problems[i].Output.Replace(oldUrl, newUrl);
problems[i].Hint = problems[i].Hint.Replace(oldUrl, newUrl);
imagefiles[fileNewName] = pair.Value;
}
}
//将题目插入到数据库
List <Int32> pids = ProblemRepository.Instance.InsertEntities(problems);
if (pids == null || pids.Count == 0)
{
return(MethodResult.FailedAndLog("Failed to import problem!"));
}
//保存题目数据
Dictionary <Int32, Boolean> dataadded = new Dictionary <Int32, Boolean>();
for (Int32 i = 0; i < pids.Count; i++)
{
if (pids[i] < 0)
{
continue;
}
try
{
if (datas[i] != null)
{
IMethodResult ret = ProblemDataManager.InternalAdminSaveProblemData(pids[i], datas[i]);
if (!ret.IsSuccess)
{
return(ret);
}
dataadded[pids[i]] = true;
}
}
catch
{
dataadded[pids[i]] = false;
}
ProblemCache.IncreaseProblemSetCountCache(); //更新缓存
ProblemCache.IncreaseProblemIDMaxCache(); //更新缓存
ProblemCache.RemoveProblemSetCache(GetProblemPageIndex(pids[i])); //删除缓存
}
//保存题目图片
foreach (KeyValuePair <String, Byte[]> pair in imagefiles)
{
try
{
UploadsManager.InternalAdminSaveUploadFile(pair.Value, pair.Key);
}
catch { }
}
return(MethodResult.SuccessAndLog <Dictionary <Int32, Boolean> >(dataadded, "Admin import problem, id = {0}", String.Join(",", pids)));
}
/// <summary>
/// 数据校验
/// </summary>
/// <param name="inputExcelFile">待校验的文件名</param>
/// <param name="textError">用户提交文件出现错误是，返回的错误描述信息。如：“您上传的文件格式不正确，邀请文件应该有 5 列”</param>
/// <param name="textSuccessTitle">返回如处理成功后的主题。如：“成功 5 个，失败 2 个。”</param>
/// <param name="textSuccessDetail">返回如处理成功后的其中错误行的具体描述，每行以回车换行分开。如：“第 1 行，手机号不正确\n第 6 行，邮箱格式不正确。”</param>
/// <returns>返回一个 ProblemImport 集合，表示所有通过校验的数据。若无任何数据通过校验，则集合的 .Count 为零 </returns>
/// <remarks>调用者应该先判断 textError 是否为空，若为非空，则表示有错，直接显示此错误即可。否则显示 textSuccessTitle，同时需判断详细描述 textSuccessDetail 是否有值，有值则显示之。 </remarks>
List <ProblemImport> CheckExcelDataForInvition(string inputExcelFile, ref int successNum, ref int falseNum, ref string textError, ref string textSuccessTitle, ref string textSuccessDetail)
{
int totalNum = 0, colCount = 0, validColNum = 5, sheetNum = 0, maxRows = 1000;
List <ProblemImport> returnInfo = new List <ProblemImport>();
System.Data.DataTable tableExcel;
long testLong;
falseNum = 0;
successNum = 0;
textError = "";
textSuccessTitle = "";
textSuccessDetail = "";
byte[] invisibleByte = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 63, 127, 128 };
char[] invisibleChar = System.Text.Encoding.ASCII.GetChars(invisibleByte);
//获取 Excel 数据到变量
tableExcel = ExcelHelper.FileToDataTable(inputExcelFile, out sheetNum);
if (tableExcel == null)
{
tableExcel = new System.Data.DataTable();
}
totalNum = tableExcel.Rows.Count;
colCount = tableExcel.Columns.Count;
//if (sheetNum > 1)
//{
// textError = "您上传的文件有含有多个工作表，请您按照模板的格式要求来填写数据，确保文件只有一个工作表。";
//}
//else
if ((totalNum == 0) || (colCount == 0))
{
textError = "未能读取到数据，请确保您上传的 Excel 文件的第一个工作表内含有数据。";
}
else if (totalNum < 2)
{
textError = "未能读取到有效数据，邀请文件应该至少有 2 行数据，第一行为表头（标题、类型、层级、科目、章节、答案、正确答案、分析、过关必刷），其余的行为有效数据。";
}
else if (colCount < validColNum)
{
textError = "您上传的文件格式不正确，邀请文件应该有 8 列，分别为：标题、类型、层级、科目、章节、答案、正确答案、分析、过关必刷.";
}
else if (totalNum > maxRows)
{
textError = "您上传的文件包含有太多的数据行。对于不需要的数据，您必须在 Excel 中整行选中后再右键选择删除；否则如果只是选中某些单元格再按 Delete 键，Excel 仍然认为这些格子有数据，只不过是数据是空字符。";
}
else
{
//去掉最后行的空数据
int emptyRows = 0;
bool findText = false;
for (int iRow = totalNum - 1; iRow >= 0; iRow--)
{
for (int loopCol = 0; loopCol < validColNum; loopCol++)
{
string cellValue = "";
if (tableExcel.Rows[iRow][loopCol] != null)
{
cellValue = tableExcel.Rows[iRow][loopCol].ToString().Trim(invisibleChar);
}
if (!string.IsNullOrEmpty(cellValue))
{
findText = true;
break;
}
else if (loopCol == (validColNum - 1))
{
emptyRows++;
}
}
if (findText)
{
break;
}
}
totalNum = totalNum - emptyRows;
if (totalNum < 2)
{
textError = "未能读取到有效数据，邀请文件应该至少有 2 行数据，第一行为表头（标题、类型、层级、科目、章节、答案、正确答案、分析、过关必刷），其余的行为有效数据。";
}
}
if (!string.IsNullOrEmpty(textError))
{
//nothing。在上面已经做了错误识别处理
}
else //校验第一行的表头
{
Problem headRow = new Problem();
//姓名
string title = tableExcel.Rows[0][0].ToString().Trim(invisibleChar);
string category = tableExcel.Rows[0][1].ToString().Trim(invisibleChar);
string belong = tableExcel.Rows[0][2].ToString().Trim(invisibleChar);
string subject = tableExcel.Rows[0][3].ToString().Trim(invisibleChar);
string chapter = tableExcel.Rows[0][4].ToString().Trim(invisibleChar);
string answers = tableExcel.Rows[0][5].ToString().Trim(invisibleChar);
string correct = tableExcel.Rows[0][6].ToString().Trim(invisibleChar);
string analysis = tableExcel.Rows[0][7].ToString().Trim(invisibleChar);
string isImportant = tableExcel.Rows[0][8].ToString().Trim(invisibleChar);
if (title.IndexOf("标题") < 0)
{
textError = "请确保文件表头（首行）第一列为“标题”";
}
else if (category.IndexOf("类型") < 0)
{
textError = "请确保文件表头（首行）第二列为“类型”";
}
else if (belong.IndexOf("层级") < 0)
{
textError = "请确保文件表头（首行）第三列为“层级”";
}
else if (subject.IndexOf("科目") < 0)
{
textError = "请确保文件表头（首行）第四列为“科目”";
}
else if (chapter.IndexOf("章节") < 0)
{
textError = "请确保文件表头（首行）第五列为“章节”";
}
else if (answers.IndexOf("答案") < 0)
{
textError = "请确保文件表头（首行）第六列为“答案”";
}
else if (correct.IndexOf("正确答案") < 0)
{
textError = "请确保文件表头（首行）第七列为“正确答案”";
}
else if (analysis.IndexOf("分析") < 0)
{
textError = "请确保文件表头（首行）第八列为“分析”";
}
else if (isImportant.IndexOf("过关必刷") < 0)
{
textError = "请确保文件表头（首行）第九列为“过关必刷”";
}
}
//返回--无需做详细校验
if (!string.IsNullOrEmpty(textError))
{
return(returnInfo);
}
//获取分类、层级、科目、章节列表
var catelist = _ProblemCatoryBiz.GetAll();
var belonglist = _BelongBiz.GetAll();
var subjectlist = _SubjectInfoBiz.GetAll();
var chapterlist = _ChapterBiz.GetAll();
//以下开始详细校验
string[] errorRows = new string[totalNum];
for (int iRow = totalNum - 1; iRow > 0; iRow--)//记得第一行是表头，无需校验。
{
ProblemImport oneRow = new ProblemImport();
errorRows[iRow] = "";
//标题
oneRow.Title = tableExcel.Rows[iRow][0].ToString().Trim(invisibleChar);
if (string.IsNullOrEmpty(oneRow.Title))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，标题为空";
}
//else if (!catelist.Any(p=>p.Title == oneRow.Title))
//{ errorRows[iRow] = "第 " + (iRow + 1) + " 行，类型超长"; }
//类型
if (string.IsNullOrEmpty(errorRows[iRow]))
{
oneRow.Category = tableExcel.Rows[iRow][1].ToString().Trim(invisibleChar);
if (string.IsNullOrEmpty(oneRow.Category))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，类型为空";
}
else if (!catelist.Any(p => p.Title == oneRow.Category))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，类型不在所选范围之内，不存在该类型";
}
}
//层级
if (string.IsNullOrEmpty(errorRows[iRow]))
{
oneRow.Belong = tableExcel.Rows[iRow][2].ToString().Trim(invisibleChar);
if (string.IsNullOrEmpty(oneRow.Belong))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，层级为空";
}
else if (!belonglist.Any(p => p.Title == oneRow.Belong))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，层级不在所选范围之内，不存在该层级";
}
}
//科目
if (string.IsNullOrEmpty(errorRows[iRow]))
{
// 获取层级信息
var _belongInfo = belonglist.Where(p => p.Title == oneRow.Belong).FirstOrDefault();
oneRow.Subject = tableExcel.Rows[iRow][3].ToString().Trim(invisibleChar);
if (string.IsNullOrEmpty(oneRow.Subject))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，科目为空";
}
else if (!subjectlist.Any(p => p.Title == oneRow.Subject))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，科目不在所选范围之内，不存在该科目";
}
else if (!subjectlist.Any(p => p.Title == oneRow.Subject && p.BelongId == _belongInfo.BelongId))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，当前层级中不包含此科目";
}
}
//章节
if (string.IsNullOrEmpty(errorRows[iRow]))
{
// 获取层级信息
var _belongInfo = belonglist.Where(p => p.Title == oneRow.Belong).FirstOrDefault();
//获取科目信息
var _subjectInfo = subjectlist.Where(p => p.Title == oneRow.Subject && p.BelongId == _belongInfo.BelongId).FirstOrDefault();
oneRow.Chapter = tableExcel.Rows[iRow][4].ToString().Trim(invisibleChar);
if (string.IsNullOrEmpty(oneRow.Chapter))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，章节为空";
}
else if (!chapterlist.Any(p => p.Title == oneRow.Chapter))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，章节不在所选范围之内，不存在该章节";
}
else if (!chapterlist.Any(p => p.Title == oneRow.Chapter && p.SubjectInfoId == _subjectInfo.SubjectInfoId))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，当前科目中不包含此章节";
}
}
//答案
if (string.IsNullOrEmpty(errorRows[iRow]))
{
oneRow.Answers = tableExcel.Rows[iRow][5].ToString().Trim(invisibleChar);
//如果题目不是简答题，要判断答案列表是否为空
if (oneRow.Category.Trim() != "回答" && string.IsNullOrEmpty(oneRow.Answers))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，答案列表为空";
}
}
//正确答案
if (string.IsNullOrEmpty(errorRows[iRow]))
{
int _tParse = 0;
oneRow.Correct = tableExcel.Rows[iRow][6].ToString().Trim(invisibleChar);
//如果题目不是简答题，要判断答案列表是否为空
if (oneRow.Category.Trim() != "回答" && string.IsNullOrEmpty(oneRow.Correct))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，正确答案为空";
}
else if (oneRow.Category.Trim() == "单选" && !int.TryParse(oneRow.Correct, out _tParse))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，正确答案为非数字";
}
else if (oneRow.Category.Trim() == "判断" && !(oneRow.Correct.Trim() == "正确" || oneRow.Correct.Trim() == "错误"))
{
errorRows[iRow] = "第 " + (iRow + 1) + " 行，判断题类型需要在正确答案这一栏填‘正确’或者‘错误’";
}
}
//分析
if (string.IsNullOrEmpty(errorRows[iRow]))
{
oneRow.Analysis = tableExcel.Rows[iRow][7].ToString().Trim(invisibleChar);
}
//过关必刷
if (string.IsNullOrEmpty(errorRows[iRow]))
{
oneRow.IsImportant = tableExcel.Rows[iRow][8].ToString().Trim(invisibleChar);
}
//添加到返回集合中
if (string.IsNullOrEmpty(errorRows[iRow]))
{
returnInfo.Add(oneRow);
}
}//for 循环结束
//处理返回详细错误描述
System.Text.StringBuilder errInfo = new System.Text.StringBuilder();
for (int iRow = 0; iRow < totalNum; iRow++)
{
if (!string.IsNullOrEmpty(errorRows[iRow]))
{
falseNum++;
//errInfo.AppendLine(errorRows[iRow]);
errInfo.Append(errorRows[iRow] + "<br>");
}
}
if (falseNum > 0)
{
textSuccessDetail = errInfo.ToString();
errInfo.Clear();
}
//返回的标题 去掉第一行标题
totalNum--;
successNum = totalNum - falseNum;
textSuccessTitle = "成功 " + successNum + " 个，失败 " + falseNum + " 个。";
return(returnInfo);
}