ImportExamController.cs 33 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676
  1. using HTEXLib.COMM.Helpers;
  2. using Microsoft.AspNetCore.Authorization;
  3. using Microsoft.AspNetCore.Hosting;
  4. using Microsoft.AspNetCore.Http;
  5. using Microsoft.AspNetCore.Mvc;
  6. using Microsoft.Azure.Amqp.Framing;
  7. using Microsoft.Extensions.Configuration;
  8. using Microsoft.Extensions.Options;
  9. using NUnit.Framework;
  10. using OfficeOpenXml;
  11. using OpenXmlPowerTools;
  12. using StackExchange.Redis;
  13. using System;
  14. using System.Collections.Generic;
  15. using System.ComponentModel;
  16. using System.Linq;
  17. using System.Text.Json;
  18. using System.Threading.Tasks;
  19. using TEAMModelOS.Filter;
  20. using TEAMModelOS.Models;
  21. using TEAMModelOS.SDK;
  22. using TEAMModelOS.SDK.DI;
  23. using TEAMModelOS.SDK.Extension;
  24. using TEAMModelOS.SDK.Models;
  25. using TEAMModelOS.SDK.Models.Cosmos.School;
  26. namespace TEAMModelOS.Controllers
  27. {
  28. [ProducesResponseType(StatusCodes.Status200OK)]
  29. [ProducesResponseType(StatusCodes.Status400BadRequest)]
  30. [Route("school/exam")]
  31. [ApiController]
  32. public class ImportExamController: ControllerBase
  33. {
  34. public IWebHostEnvironment _environment { get; set; }
  35. private readonly AzureCosmosFactory _azureCosmos;
  36. private readonly SnowflakeId _snowflakeId;
  37. private readonly AzureServiceBusFactory _serviceBus;
  38. private readonly DingDing _dingDing;
  39. private readonly Option _option;
  40. private readonly AzureStorageFactory _azureStorage;
  41. private readonly AzureRedisFactory _azureRedis;
  42. public IConfiguration _configuration { get; set; }
  43. private readonly CoreAPIHttpService _coreAPIHttpService;
  44. private readonly HttpTrigger _httpTrigger;
  45. public ImportExamController(HttpTrigger httpTrigger, CoreAPIHttpService coreAPIHttpService, AzureCosmosFactory azureCosmos, AzureServiceBusFactory serviceBus, SnowflakeId snowflakeId, DingDing dingDing,
  46. IOptionsSnapshot<Option> option, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis, IConfiguration configuration, IWebHostEnvironment env)
  47. {
  48. _environment = env;
  49. _coreAPIHttpService = coreAPIHttpService;
  50. _azureCosmos = azureCosmos;
  51. _serviceBus = serviceBus;
  52. _snowflakeId = snowflakeId;
  53. _dingDing = dingDing;
  54. _option = option?.Value;
  55. _azureStorage = azureStorage;
  56. _azureRedis = azureRedis;
  57. _configuration = configuration;
  58. _httpTrigger = httpTrigger;
  59. }
  60. // [AuthToken(Roles = "teacher,admin")]
  61. [HttpPost("read-excel-virtue")]
  62. //[Authorize(Roles = "IES")]
  63. [AuthToken(Roles = "teacher,admin,business")]
  64. [RequestSizeLimit(102_400_000_00)] //最大10000m左右
  65. public async Task<IActionResult> ReadExcelVirtue([FromForm] IFormFile file, [FromForm] string periodId) {
  66. List<KeyValuePair<string, List<string>>> error = new List<KeyValuePair<string, List<string>>>();
  67. List<KeyValuePair<string, List<string>>> warn = new List<KeyValuePair<string, List<string>>>();
  68. var (id, _, _, school) = HttpContext.GetAuthTokenInfo();
  69. ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
  70. List<VirtueImport> virtueImports = new List<VirtueImport> { };
  71. using (ExcelPackage package = new ExcelPackage(file.OpenReadStream())) {
  72. ExcelWorksheets sheet = package.Workbook.Worksheets;
  73. //德育数据导入
  74. School schoolBase = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.School).ReadItemAsync<School>(school, new Azure.Cosmos.PartitionKey("Base"));
  75. Period period = schoolBase.period.Find(x => x.id.Equals(periodId));
  76. var daily_sheets = sheet.Where(z => !z.Name.Equals("栏位说明"));
  77. HashSet<IdNameCode> rightStudents = new HashSet<IdNameCode>();
  78. List<Class> classes = new List<Class>();
  79. foreach (var daily_sheet in daily_sheets)
  80. {
  81. if (DateTimeOffset.TryParse(daily_sheet.Name, out DateTimeOffset sheetNameTime))
  82. {
  83. var data = GetSubSheetData(daily_sheet, error);
  84. Dictionary<string, object> dailyData = new Dictionary<string, object> { { "subject", "德育" }, { "students", data.students } };
  85. ImportExamSubject importExamDaily = dailyData.ToJsonString().ToObject<ImportExamSubject>();
  86. var subject = period.subjects.Find(z => z.name.Equals(importExamDaily.subject));
  87. if (subject != null)
  88. {
  89. List<ResultImportStudent> examImportStudents = new List<ResultImportStudent>();
  90. //学生
  91. await ExamImportStudent(importExamDaily.students, rightStudents, school, period, examImportStudents, error, warn, classes, daily_sheet.Name);
  92. VirtueImport virtueImport = new VirtueImport {
  93. time = sheetNameTime.ToUnixTimeMilliseconds(),
  94. id = $"{}",
  95. students = examImportStudents
  96. };
  97. virtueImport.students.AddRange(examImportStudents);
  98. }
  99. else
  100. {
  101. error.Add(new KeyValuePair<string, List<string>>("subject_invalid", new List<string> { importExamDaily.subject }));// 科目不存在
  102. }
  103. }
  104. else {
  105. }
  106. }
  107. }
  108. return Ok(new { virtueImports, error,warn});
  109. }
  110. // [AuthToken(Roles = "teacher,admin")]
  111. [HttpPost("read-excel-exam")]
  112. //[Authorize(Roles = "IES")]
  113. [AuthToken(Roles = "teacher,admin,business")]
  114. [RequestSizeLimit(102_400_000_00)] //最大10000m左右
  115. public async Task<IActionResult> ReadExcel([FromForm] IFormFile file,[FromForm] string periodId) {
  116. List<KeyValuePair<string, List<string>>> error = new List<KeyValuePair<string, List<string>>>();
  117. List<KeyValuePair<string, List<string>>> warn = new List<KeyValuePair<string, List<string>>>();
  118. var (id, _, _, school) = HttpContext.GetAuthTokenInfo();
  119. ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
  120. using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
  121. {
  122. ExcelWorksheets sheet = package.Workbook.Worksheets;
  123. List<string> baseTitle = new List<string>();
  124. List<string> baseData = new List<string>();
  125. List<string> itemTitle = new List<string>();
  126. List<List<string>> itemDatas = new List<List<string>>();
  127. Dictionary<string, object> baseInfo = new Dictionary<string, object>();
  128. List<Dictionary<string, object>> itemInfo = new List<Dictionary<string, object>>();
  129. HashSet<string> subjects = new HashSet<string>();
  130. //科目标题的栏位序列
  131. int subjectTitelIndex = -1;
  132. //读取Exam_
  133. var exam_sheet = sheet.Where(z => z.Name.StartsWith("Exam_")).FirstOrDefault();
  134. if (exam_sheet!=null )
  135. {
  136. var rows = exam_sheet.Dimension.Rows;
  137. var columns = exam_sheet.Dimension.Columns;
  138. for (int r = 1; r <= rows; r++) {
  139. List<string> itemData = null;
  140. if (r >= 4) {
  141. itemData= new List<string>();
  142. }
  143. for (int c = 1; c <= columns; c++) {
  144. var value = exam_sheet.GetValue(r, c);
  145. if (r == 1)
  146. {
  147. if (!string.IsNullOrWhiteSpace($"{value}"))
  148. {
  149. baseTitle.Add($"{value}");
  150. }
  151. else
  152. {
  153. break;
  154. }
  155. }
  156. else if (r == 2)
  157. {
  158. if (c > baseTitle.Count)
  159. {
  160. break;
  161. }
  162. else
  163. {
  164. baseData.Add($"{value}");
  165. }
  166. }
  167. else if (r == 3)
  168. {
  169. if (!string.IsNullOrWhiteSpace($"{value}"))
  170. {
  171. if ($"{value}".Equals("subject")) {
  172. subjectTitelIndex= c;
  173. }
  174. itemTitle.Add($"{value}");
  175. }
  176. else
  177. {
  178. break;
  179. }
  180. }
  181. else {
  182. if (c > itemTitle.Count)
  183. {
  184. break;
  185. }
  186. else
  187. {
  188. itemData.Add($"{value}");
  189. }
  190. }
  191. }
  192. if (itemData != null) {
  193. itemDatas.Add(itemData);
  194. }
  195. }
  196. for (int i = 0; i < baseTitle.Count; i++)
  197. {
  198. baseInfo.Add(baseTitle[i], baseData[i]);
  199. }
  200. for (int i = 0; i < itemDatas.Count; i++)
  201. {
  202. Dictionary<string, object> item = new Dictionary<string, object>();
  203. for (int j = 0; j < itemTitle.Count; j++)
  204. {
  205. if (itemTitle[j].Equals("index"))
  206. {
  207. if (int.TryParse(itemDatas[i][j], out int index))
  208. {
  209. item.Add(itemTitle[j], index);
  210. }
  211. else {
  212. error.Add(new KeyValuePair<string, List<string>>("index_invalid",new List<string> { itemDatas[i][j] } ));// 题目序列只能是正整数
  213. }
  214. }
  215. else if (itemTitle[j].Equals("score") )
  216. {
  217. if (double.TryParse(itemDatas[i][j], out double score))
  218. {
  219. item.Add(itemTitle[j], score);
  220. }
  221. else if (string.IsNullOrWhiteSpace(itemDatas[i][j])) {
  222. item.Add(itemTitle[j], 0);
  223. }
  224. else
  225. {
  226. error.Add(new KeyValuePair<string, List<string>>("score_invalid", new List<string> { itemDatas[i][j] }));// 配分只能是数字
  227. }
  228. }
  229. else {
  230. item.Add(itemTitle[j], itemDatas[i][j]);
  231. }
  232. }
  233. itemInfo.Add(item);
  234. subjects.Add(itemDatas[i][subjectTitelIndex-1]);
  235. }
  236. }
  237. List<Dictionary<string, object>> subjectDatas = new List<Dictionary<string, object>>();
  238. if (subjects.Count > 0) {
  239. foreach (var subject in subjects) {
  240. var subject_sheet = sheet.Where(z => z.Name.StartsWith($"Sub_{subject}")).FirstOrDefault();
  241. var data = GetSubSheetData(subject_sheet,error);
  242. var items = itemInfo.FindAll(x => x["subject"].Equals(subject));
  243. Dictionary<string, object> subjectData = new Dictionary<string, object> { { "subject", subject } ,{ "items",items },{ "students", data.students } };
  244. subjectDatas.Add(subjectData);
  245. }
  246. }
  247. baseInfo.Add("periodId", periodId);
  248. baseInfo.Add("school", school);
  249. baseInfo.Add("subjects", subjectDatas);
  250. var importExam =baseInfo.ToJsonString().ToObject<ImportExam>();
  251. var validData = importExam.Valid();
  252. ExamImport examImport = null;
  253. if (validData.isVaild)
  254. {
  255. string sql = $"select value c from c where c.name ='{importExam.name}'";
  256. var result = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.School).GetList<ExamImport>(sql, $"ExamImport-{importExam.school}", pageSize: 1);
  257. if (string.IsNullOrEmpty(result.continuationToken))
  258. {
  259. warn.Add(new KeyValuePair<string, List<string>>("name_duplicate", new List<string> { importExam.name }));//评测名称重复
  260. }
  261. if (result.list.IsNotEmpty())
  262. {
  263. examImport = result.list[0];
  264. warn.Add(new KeyValuePair<string, List<string>>("name_update", new List<string> { importExam.name }));//同名评测数据更新
  265. }
  266. if (examImport == null)
  267. {
  268. examImport = new ExamImport { name = importExam.name, type = importExam.type, school = importExam.school };
  269. if (DateTimeOffset.TryParse(importExam.time, out DateTimeOffset dateTime))
  270. {
  271. examImport.time = dateTime.ToUnixTimeMilliseconds();
  272. }
  273. else
  274. {
  275. error.Add(new KeyValuePair<string, List<string>>("time_format", new List<string> { importExam.time }));// 时间格式错误
  276. }
  277. }
  278. School schoolBase = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.School).ReadItemAsync<School>(importExam.school, new Azure.Cosmos.PartitionKey("Base"));
  279. Period period = schoolBase.period.Find(x => x.id.Equals(importExam.periodId));
  280. if (period != null)
  281. {
  282. //用于处理多学科,不需要重复查询学习基础信息
  283. //id 学生id code 行政班id name 学生姓名
  284. HashSet<IdNameCode> rightStudents = new HashSet<IdNameCode>();
  285. List<Class> classes = new List<Class>();
  286. foreach (var item in importExam.subjects)
  287. {
  288. var subject = period.subjects.Find(z => z.name.Equals(item.subject));
  289. if (subject != null)
  290. {
  291. List<ResultImportStudent> examImportStudents = new List<ResultImportStudent>();
  292. //学生
  293. await ExamImportStudent(item.students, rightStudents, school, period, examImportStudents, error, warn, classes, item.subject);
  294. examImport.subjects.Add(new ExamImportSubject { id = subject.id, name = subject.name, students = examImportStudents, }) ;
  295. }
  296. else
  297. {
  298. error.Add(new KeyValuePair<string, List<string>>("subject_invalid", new List<string> { item.subject }));// 科目不存在
  299. }
  300. }
  301. }
  302. else
  303. {
  304. error.Add(new KeyValuePair<string, List<string>>("period_invalid",new List<string> { importExam.periodId }));// 学段不存在
  305. }
  306. }
  307. else {
  308. validData.errors.SelectMany(z => z.Value).ToList().ForEach(x => {
  309. error.Add(new KeyValuePair<string, List<string>>(x, new List<string> { }));
  310. });
  311. }
  312. List<KeyValuePair<string, HashSet<string>>> errorData = new List<KeyValuePair<string, HashSet<string>>>();
  313. error.ForEach(x => {
  314. var value = errorData.Find(z => z.Key.Equals(x.Key));
  315. if (!string.IsNullOrWhiteSpace(value.Key))
  316. {
  317. x.Value.ForEach(z => { value.Value.Add(z); });
  318. }
  319. else {
  320. errorData.Add(new KeyValuePair<string, HashSet<string>>(x.Key,x.Value.ToHashSet()));
  321. }
  322. });
  323. return Ok(new { code = 400, error = errorData, examImport });
  324. }
  325. }
  326. private async Task ExamImportStudent(List<ImportResultStudent> importExamStudents, HashSet<IdNameCode> rightStudents,string school,
  327. Period period, List<ResultImportStudent> examImportStudents, List<KeyValuePair<string, List<string>>> error,
  328. List<KeyValuePair<string, List<string>>> warn, List<Class> classes, string sheetName)
  329. {
  330. // 学号确定的学生
  331. var hasIdStudents = importExamStudents.Where(z => !string.IsNullOrWhiteSpace(z.id));
  332. var needSearch = hasIdStudents.ExceptBy(rightStudents.Select(x => x.id), hasId => hasId.id).ToList();
  333. List<ImportResultStudent> notExistIds = new List<ImportResultStudent>();
  334. //通过id在数据库和rightStudents 都找不到的学生
  335. List<ImportResultStudent> evenNotExistIds = new List<ImportResultStudent>();
  336. if (needSearch.Any())
  337. {
  338. ///在rightStudents找不到的学生需要查询数据库。
  339. string stuidsql = $"select c.id,c.classId as code , c.name from c where c.id in ({string.Join(",", needSearch.Select(x => $"'{x.id}'"))}) and c.periodId='{period.id}' ";
  340. var sturesult = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).GetList<IdNameCode>(stuidsql, $"Base-{school}");
  341. if (sturesult.list.IsNotEmpty())
  342. {
  343. sturesult.list.ForEach(x => {
  344. var exist = rightStudents.Where(y => y.id.Equals(x.id));
  345. if (!exist.Any())
  346. {
  347. rightStudents.Add(x);
  348. }
  349. });
  350. //needSearch与 sturesult.list(数据库)查找的对比,仍然没有找到的学生。
  351. var notInDb = needSearch.ExceptBy(sturesult.list.Select(x => x.id), y => y.id);
  352. if (notInDb.Any())
  353. {
  354. evenNotExistIds.AddRange(notInDb);
  355. }
  356. }
  357. else
  358. {
  359. evenNotExistIds.AddRange(needSearch);
  360. }
  361. }
  362. foreach (var item in hasIdStudents)
  363. {
  364. var existStudent = rightStudents.Where(n => n.id.Equals(item.id));
  365. if (existStudent .Any())
  366. {
  367. long time = 0;
  368. if (DateTimeOffset.TryParse(item.time, out DateTimeOffset dateTime))
  369. {
  370. time = dateTime.ToUnixTimeMilliseconds();
  371. }
  372. examImportStudents.Add(new ResultImportStudent
  373. {
  374. id = item.id,
  375. name = existStudent.First().name,
  376. classId = existStudent.First().code,
  377. score = item.score,
  378. scores = item.scores,
  379. time = time,
  380. items = item.items,
  381. });
  382. }
  383. }
  384. //id信息在数据库找不到的 且班级和姓名不全,
  385. var hasNameClassid = evenNotExistIds.Where(x => !string.IsNullOrWhiteSpace(x.name) && !string.IsNullOrWhiteSpace(x.classId));
  386. var notexistStudent = evenNotExistIds.Except(hasNameClassid);
  387. if (notexistStudent.Any())
  388. {
  389. error.Add(new KeyValuePair<string, List<string>>("student_notexist", notexistStudent.Select(x => $"id:{x.id}_name:{x.name}_classId:{x.classId}").ToList()));//没有班级和姓名不全,且id信息在数据库找不到的
  390. }
  391. //没有学号 ,且姓名和班级信息不全的
  392. var studentInvalid = importExamStudents.Where(z => string.IsNullOrWhiteSpace(z.id) && (string.IsNullOrWhiteSpace(z.name) || string.IsNullOrWhiteSpace(z.classId)));
  393. if (studentInvalid.Any())
  394. {
  395. error.Add(new KeyValuePair<string, List<string>>("student_invalid", studentInvalid.Select(x => $"id:{x.id}_name:{x.name}_classId:{x.classId}").ToList()));// 学生信息不全
  396. }
  397. //没有id ,但是存在姓名和班级信息
  398. var classesStu = importExamStudents.Where(z => !string.IsNullOrWhiteSpace(z.classId) && !string.IsNullOrWhiteSpace(z.name) && string.IsNullOrWhiteSpace(z.id)).ToHashSet();
  399. if (hasNameClassid.Any())
  400. {
  401. hasNameClassid.ToList().ForEach(x => classesStu.Add(x));
  402. }
  403. var group = classesStu.GroupBy(x => x.classId);
  404. //不能被拆分的以班级名称查找。
  405. foreach (var classGroupStudents in group)
  406. {
  407. Class clzz = null;
  408. bool isClassName = false;
  409. string className = string.Empty;
  410. var cls = classGroupStudents.Key.Split("-");
  411. var classStudents = classGroupStudents.ToList();
  412. var duplicateStudents = new List<ImportResultStudent>();
  413. classStudents.GroupBy(z => z.name).Select(z => new { key = z.Key, list = z.ToList() }).ToList().ForEach(y => {
  414. if (y.list.Count > 1)
  415. {
  416. duplicateStudents.AddRange(y.list);
  417. error.Add(new KeyValuePair<string, List<string>>("stuname_duplicate", y.list.Select(z => $"{sheetName}-{z.name}").ToList()));
  418. }
  419. });
  420. if (duplicateStudents.IsNotEmpty())
  421. {
  422. duplicateStudents.ForEach(z => { classStudents.Remove(z); });
  423. }
  424. if (cls.Length == 2 && int.TryParse(cls[0], out int year) && year > 2000 && int.TryParse(cls[1], out int no) && no > 0)
  425. {
  426. var existInTemp = classes.FindAll(z => z.year == year && z.no.Equals(cls[1]));
  427. if (existInTemp.Any())
  428. {
  429. clzz = existInTemp.First();
  430. }
  431. else
  432. {
  433. string sqlClazz = $"select value c from c where c.year={year} and c.no ='{cls[1]}' and c.periodId='{period.id}' ";
  434. var classresult = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.School).GetList<Class>(sqlClazz, $"Class-{school}", pageSize: 1);
  435. if (classresult.list.IsNotEmpty())
  436. {
  437. clzz = classresult.list[0];
  438. classes.Add(clzz);
  439. }
  440. else
  441. {
  442. className = classGroupStudents.Key;
  443. isClassName = true;
  444. }
  445. }
  446. }
  447. else
  448. {
  449. className = classGroupStudents.Key;
  450. isClassName = true;
  451. }
  452. if (isClassName)
  453. {
  454. var existInTemp = classes.FindAll(z => z.name.Equals(className));
  455. if (existInTemp.Any())
  456. {
  457. clzz = existInTemp.First();
  458. }
  459. else
  460. {
  461. string sqlClazz = $"select value c from c where c.name ='{className}' and c.periodId='{period.id}' ";
  462. var classresult = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.School).GetList<Class>(sqlClazz, $"Class-{school}", pageSize: 1);
  463. if (classresult.list.IsNotEmpty())
  464. {
  465. clzz = classresult.list[0];
  466. classes.Add(clzz);
  467. }
  468. else
  469. {
  470. error.Add(new KeyValuePair<string, List<string>>("class_invalid", new List<string> { $"{sheetName}-{className}" }));// 班级错误
  471. }
  472. }
  473. }
  474. if (clzz != null)
  475. {
  476. string stuClassIdsql = $"select c.id,c.classId as code , c.name from c where c.name in ({string.Join(",", classStudents.Select(x => $"'{x.name}'"))}) and c.classId ='{clzz.id}' and c.periodId='{period.id}' ";
  477. var sturesult = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).GetList<IdNameCode>(stuClassIdsql, $"Base-{school}");
  478. //检查重名
  479. if (sturesult.list.IsNotEmpty())
  480. {
  481. var groupNames = sturesult.list.GroupBy(x => x.name).Select(x => new { key = x.Key, list = x.ToList() }).Where(y => y.list.Count > 1);
  482. if (groupNames.Any())
  483. {
  484. warn.Add(new KeyValuePair<string, List<string>>("stuname_duplicate", groupNames.Select(x => $"{sheetName}-{x.key}").ToList()));//学生姓名重复
  485. }
  486. foreach (var stu in classesStu)
  487. {
  488. var student = sturesult.list.FindAll(x => x.name.Equals(stu.name));
  489. if (student.Any())
  490. {
  491. long time = 0;
  492. if (DateTimeOffset.TryParse(stu.time, out DateTimeOffset dateTime))
  493. {
  494. time = dateTime.ToUnixTimeMilliseconds();
  495. }
  496. examImportStudents.Add(new ResultImportStudent
  497. {
  498. id = student[0].id,
  499. name = student[0].name,
  500. classId = student[0].code,
  501. score = stu.score,
  502. scores = stu.scores,
  503. time = time,
  504. items = stu.items
  505. });
  506. }
  507. }
  508. }
  509. else
  510. {
  511. error.Add(new KeyValuePair<string, List<string>>("student_invalid", classGroupStudents.ToList().Select(x => $"{sheetName}-{x.name}").ToList()));
  512. }
  513. }
  514. }
  515. }
  516. private (List<Dictionary<string, object>> students, List<KeyValuePair<string, List<string>>> error) GetSubSheetData(ExcelWorksheet subject_sheet, List<KeyValuePair<string, List<string>>> error) {
  517. List<Dictionary<string, object>> students = new List<Dictionary<string, object>>();
  518. List<string> titles = new List<string>();
  519. List<List<string>> datas = new List<List<string>>();
  520. if (subject_sheet != null)
  521. {
  522. var rows = subject_sheet.Dimension.Rows;
  523. var columns = subject_sheet.Dimension.Columns;
  524. for (int r = 1; r <= rows; r++)
  525. {
  526. List<string> data = new List<string>();
  527. for (int c = 1; c <= columns; c++)
  528. {
  529. var value = subject_sheet.GetValue(r, c);
  530. if (r == 1)
  531. {
  532. if (!string.IsNullOrWhiteSpace($"{value}"))
  533. {
  534. titles.Add($"{value}");
  535. }
  536. else
  537. {
  538. break;
  539. }
  540. }
  541. else
  542. {
  543. if (c > titles.Count)
  544. {
  545. break;
  546. }
  547. else
  548. {
  549. data.Add($"{value}");
  550. }
  551. }
  552. }
  553. if (data.Any())
  554. {
  555. datas.Add(data);
  556. }
  557. }
  558. }
  559. for (int i = 0; i < datas.Count; i++)
  560. {
  561. Dictionary<string, object> item = new Dictionary<string, object>();
  562. List<KeyValuePair<int, int>> _index_order = new List<KeyValuePair<int, int>>();
  563. List<ItemVlaue> items = new List<ItemVlaue>();
  564. for (int j = 0; j < titles.Count; j++)
  565. {
  566. if (int.TryParse(titles[j], out int index))
  567. {
  568. _index_order.Add(new KeyValuePair<int, int>(index, j));
  569. }
  570. else if (titles[j].StartsWith("item_"))
  571. {
  572. double score = 0;
  573. if (double.TryParse(datas[i][j], out score))
  574. {
  575. items.Add(new ItemVlaue() { code = titles[j].Replace("item_", ""), value = score });
  576. }
  577. else if (string.IsNullOrWhiteSpace(datas[i][j]))
  578. {
  579. items.Add(new ItemVlaue() { code = titles[j].Replace("item_", ""), value = score });
  580. }
  581. else {
  582. error.Add(new KeyValuePair<string, List<string>>("score_invalid",new List<string> { datas[i][j] }));// 得分只能是数字
  583. }
  584. }
  585. else
  586. {
  587. if (titles[j].Equals("score") )
  588. {
  589. if (double.TryParse(datas[i][j], out double score))
  590. {
  591. item.Add(titles[j], score);
  592. }
  593. else if (string.IsNullOrWhiteSpace(datas[i][j]))
  594. {
  595. item.Add(titles[j], 0);
  596. }
  597. else {
  598. error.Add(new KeyValuePair<string, List<string>>("score_invalid", new List<string> { datas[i][j] }));// 得分只能是数字
  599. }
  600. }
  601. else {
  602. item.Add(titles[j], datas[i][j]);
  603. }
  604. }
  605. }
  606. var orders = _index_order.OrderBy(x => x.Key).ToList();
  607. List<double> scores = new List<double>();
  608. for (int j = 0; j < orders.Count; j++)
  609. {
  610. double score = 0;
  611. if (double.TryParse($"{datas[i][orders[j].Value]}", out score))
  612. {
  613. scores.Add(score);
  614. }
  615. else if (string.IsNullOrWhiteSpace($"{datas[i][orders[j].Value]}"))
  616. {
  617. scores.Add(score);
  618. }
  619. else
  620. {
  621. error.Add(new KeyValuePair<string, List<string>>("score_invalid", new List<string> { datas[i][orders[j].Value] }));// 得分只能是数字
  622. }
  623. }
  624. if (scores.IsNotEmpty())
  625. {
  626. item.Add("scores", scores);
  627. }
  628. if (items.IsNotEmpty())
  629. {
  630. item.Add("items", items);
  631. }
  632. students.Add(item);
  633. }
  634. return (students, error);
  635. }
  636. }
  637. }