ActivitySticsController.cs 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448
  1. using Azure.Cosmos;
  2. using Microsoft.AspNetCore.Http;
  3. using Microsoft.AspNetCore.Mvc;
  4. using Microsoft.Extensions.Options;
  5. using System;
  6. using System.Collections.Generic;
  7. using System.Linq;
  8. using System.Threading.Tasks;
  9. using TEAMModelOS.Models;
  10. using TEAMModelOS.SDK.DI;
  11. using TEAMModelOS.SDK.Models;
  12. using System.Text.Json;
  13. using TEAMModelBI.Tool;
  14. using System.Text;
  15. using TEAMModelOS.SDK.Extension;
  16. using TEAMModelBI.Models;
  17. using TEAMModelOS.SDK;
  18. namespace TEAMModelBI.Controllers.Census
  19. {
  20. [Route("activity")]
  21. [ApiController]
  22. public class ActivitySticsController : ControllerBase
  23. {
  24. private readonly AzureCosmosFactory _azureCosmos;
  25. private readonly AzureStorageFactory _azureStorage;
  26. private readonly DingDing _dingDing;
  27. private readonly Option _option;
  28. private readonly CoreAPIHttpService _coreAPIHttpService;
  29. public readonly List<string> types = new List<string> { "Exam", "Survey", "Vote", "Homework" };
  30. public ActivitySticsController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option, CoreAPIHttpService coreAPIHttpService)
  31. {
  32. _azureCosmos = azureCosmos;
  33. _dingDing = dingDing;
  34. _azureStorage = azureStorage;
  35. _option = option?.Value;
  36. _coreAPIHttpService = coreAPIHttpService;
  37. }
  38. /// <summary>
  39. /// 统计活动 传醍摩豆则查询相关的学校活动
  40. /// </summary>
  41. /// <param name="jsonElement"></param>
  42. /// <returns></returns>
  43. [ProducesDefaultResponseType]
  44. [HttpPost("get-count")]
  45. public async Task<IActionResult> GetCount(JsonElement jsonElement)
  46. {
  47. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  48. if(!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();
  49. long start = 0, end = 0;
  50. if (bool.Parse($"{term}") == true)
  51. {
  52. (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
  53. }
  54. var cosmosClient = _azureCosmos.GetCosmosClient();
  55. List<object> activityCount = new List<object>();
  56. if (!string.IsNullOrEmpty($"{tmdId}"))
  57. {
  58. List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
  59. foreach (var itemId in schoolIds)
  60. {
  61. School school = new();
  62. var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("Base"));
  63. if (response.Status == 200)
  64. {
  65. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  66. school = json.ToObject<School>();
  67. }
  68. ActivityCount tempCount = new ActivityCount() { id = itemId, name = school.name != null ? school.name : itemId };
  69. foreach (var type in types)
  70. {
  71. StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{itemId}' ");
  72. if (bool.Parse($"{term}") == true)
  73. {
  74. sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
  75. }
  76. long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });
  77. tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
  78. }
  79. activityCount.Add(tempCount);
  80. }
  81. }
  82. else
  83. {
  84. foreach (var type in types)
  85. {
  86. StringBuilder sqlTxt = new StringBuilder($"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' ");
  87. if (bool.Parse($"{term}") == true)
  88. {
  89. sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
  90. }
  91. long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });
  92. activityCount.Add(new KeyValuePair<string, object>(type, totals));
  93. }
  94. }
  95. return Ok(new { state = 200, activityCount });
  96. }
  97. /// <summary>
  98. /// 统计所有的评量活动,问卷调查,投票活动,作业
  99. /// </summary>
  100. /// <returns></returns>
  101. [HttpPost("get-allactivity")]
  102. public async Task<IActionResult> GetAllActivity()
  103. {
  104. try
  105. {
  106. var cosmosClient = _azureCosmos.GetCosmosClient();
  107. List<KeyValuePair<string, long>> typeCount = new List<KeyValuePair<string, long>>();
  108. foreach (var type in types)
  109. {
  110. string querySql = $"SELECT Count(c.id) as totals FROM c where c.pk='{type}' ";
  111. long totals = await CommonFind.FindTotals(cosmosClient, querySql, new List<string>() { "Common" });
  112. KeyValuePair<string, long> valuePair = new KeyValuePair<string, long>(type, totals);
  113. typeCount.Add(valuePair);
  114. }
  115. return Ok(new { state = 200, typeCount });
  116. }
  117. catch (Exception ex)
  118. {
  119. await _dingDing.SendBotMsg($"BI,{_option.Location} /activity/get-allactivity \n {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
  120. return BadRequest();
  121. }
  122. }
  123. /// <summary>
  124. /// 统计顾问关联的学校活动数量:评测、问卷、投票、作业
  125. /// </summary>
  126. /// <param name="jsonElement"></param>
  127. /// <returns></returns>
  128. [ProducesDefaultResponseType]
  129. [HttpPost("get-assistactivity")]
  130. public async Task<IActionResult> GetAssistSchoolActivity(JsonElement jsonElement)
  131. {
  132. if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
  133. var cosmosClient = _azureCosmos.GetCosmosClient();
  134. List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
  135. List<ActivityCount> activityCounts = new List<ActivityCount>();
  136. foreach (var itemId in schoolIds)
  137. {
  138. School school = new();
  139. var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("Base"));
  140. if (response.Status == 200)
  141. {
  142. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  143. school = json.ToObject<School>();
  144. }
  145. ActivityCount activityCount = new ActivityCount() { id = itemId, name = school.name != null ? school.name : itemId };
  146. foreach (var type in types)
  147. {
  148. string activitySql = $"SELECT Count(c.id) as totals FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
  149. long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });
  150. activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
  151. }
  152. activityCounts.Add(activityCount);
  153. }
  154. return Ok(new { state = 200, activityCounts });
  155. }
  156. /// <summary>
  157. /// 统计当前学期的活动,传醍摩豆账户则统计该账户当前学期
  158. /// </summary>
  159. /// <param name="jsonElement"></param>
  160. /// <returns></returns>
  161. [HttpPost("get-termactivity")]
  162. public async Task<IActionResult> GetTermActivity(JsonElement jsonElement)
  163. {
  164. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  165. var cosmosClient = _azureCosmos.GetCosmosClient();
  166. var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
  167. if (!string.IsNullOrEmpty($"{tmdId}"))
  168. {
  169. List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
  170. List<ActivityCount> activityCounts = new List<ActivityCount>();
  171. foreach (var schoolId in schoolIds)
  172. {
  173. School school = new();
  174. var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
  175. if (response.Status == 200)
  176. {
  177. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  178. school = json.ToObject<School>();
  179. }
  180. ActivityCount activityCount = new ActivityCount() { id = schoolId, name = school.name != null ? school.name : schoolId };
  181. foreach (var type in types)
  182. {
  183. string activitySql = $"SELECT COUNT(c.id) AS totals FROM c WHERE c.pk='{type}' AND c.school='{school}' and c.createTime >= {start} and c.createTime <= {end}";
  184. long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });
  185. activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
  186. }
  187. activityCounts.Add(activityCount);
  188. }
  189. return Ok(new { state = 200, activityCounts });
  190. }
  191. else
  192. {
  193. List<KeyValuePair<string, object>> typeCount = new List<KeyValuePair<string, object>>();
  194. foreach (var type in types)
  195. {
  196. string querySql = $"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' and c.createTime >= {start} and c.createTime <= {end}";
  197. long totals = await CommonFind.FindTotals(cosmosClient, querySql, new List<string>() { "Common" });
  198. KeyValuePair<string, object> valuePair = new KeyValuePair<string, object>(type, totals);
  199. typeCount.Add(valuePair);
  200. }
  201. return Ok(new { state = 200, typeCount });
  202. }
  203. }
  204. /// <summary>
  205. /// 统计区域的活动
  206. /// </summary>
  207. /// <param name="jsonElement"></param>
  208. /// <returns></returns>
  209. [HttpPost("get-area")]
  210. public async Task<IActionResult> GetAreaActovoty(JsonElement jsonElement)
  211. {
  212. if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
  213. var cosmosClient = _azureCosmos.GetCosmosClient();
  214. List<RecSchool> schools = new();
  215. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.scale from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
  216. {
  217. schools.Add(item);
  218. }
  219. long exemAreaCount = 0;
  220. long surveyAreaCount = 0;
  221. long voteAreaCount = 0;
  222. long homeworkAreaCount = 0;
  223. List<ActivityCount> activityCount = new();
  224. foreach (var school in schools)
  225. {
  226. ActivityCount tempCount = new ActivityCount() { id = school.id, name = school.name != null ? school.name : school.id };
  227. foreach (var type in types)
  228. {
  229. StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ");
  230. long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });
  231. switch (type)
  232. {
  233. case "Exam":
  234. exemAreaCount += totals;
  235. break;
  236. case "Survey":
  237. surveyAreaCount += totals;
  238. break;
  239. case "Vote":
  240. voteAreaCount += totals;
  241. break;
  242. case "Homework":
  243. homeworkAreaCount += totals;
  244. break;
  245. }
  246. tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
  247. }
  248. activityCount.Add(tempCount);
  249. }
  250. return Ok(new { state = 200, exemAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, activityCount });
  251. }
  252. /// <summary>
  253. /// 依据区统计区级相关信息包括活动统计
  254. /// </summary>
  255. /// <param name="jsonElement"></param>
  256. /// <returns></returns>
  257. [HttpPost("get-areastics")]
  258. public async Task<IActionResult> GetAreaStics(JsonElement jsonElement)
  259. {
  260. if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
  261. var cosmosClient = _azureCosmos.GetCosmosClient();
  262. List<RecSchool> schools = new();
  263. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.scale from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
  264. {
  265. schools.Add(item);
  266. }
  267. //区级人员
  268. int countArea = 0;
  269. //区级评审人员
  270. int appraiseArea = 0;
  271. long examAreaCount = 0; //试卷活动
  272. long surveyAreaCount = 0; //问卷活动
  273. long voteAreaCount = 0; //投票活动
  274. long homeworkAreaCount = 0; //作业活动
  275. List<SchoolInfos> schoolInfos = new();
  276. List<ActivityCount> activityCount = new();
  277. int totalTime = 0;
  278. foreach (var school in schools)
  279. {
  280. int count = 0;
  281. int appraise = 0;
  282. await foreach (var info in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<bool>(queryText: $"select value(array_contains(c.permissions,'train-appraise')) from c", requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Teacher-{school.id}") }))
  283. {
  284. if (info)
  285. {
  286. appraise += 1;
  287. appraiseArea += 1;
  288. }
  289. countArea += 1;
  290. count += 1;
  291. }
  292. //查询学校的总学时
  293. totalTime += await CommonFind.FindTotals(cosmosClient, "SELECT sum(c.totalTime) as totals FROM c", "Teacher", $"TeacherTrain-{school.id}");
  294. //查询学校参训人数
  295. int tempCount = await CommonFind.FindTotals(cosmosClient, $"select array_length(c.members) as totals from c where c.type='yxtrain'", "School", $"GroupList-{school.id}");
  296. //学校学生人数
  297. long stuCount = await CommonFind.FindTotals(cosmosClient, $"select count(c.id) as totals from c", "Student", $"Base-{school.id}");
  298. schoolInfos.Add(new SchoolInfos { schoolId = school.id, schoolName = school.name, picture = school.picture, scale = school.scale, teacherCount = count, studentCount = stuCount, appraiseCount = appraise, trainCount = tempCount });
  299. ActivityCount tempActivity = new ActivityCount() { id = school.id, name = school.name != null ? school.name : school.id };
  300. foreach (var type in types)
  301. {
  302. StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ");
  303. long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });
  304. switch (type)
  305. {
  306. case "Exam":
  307. examAreaCount += totals;
  308. break;
  309. case "Survey":
  310. surveyAreaCount += totals;
  311. break;
  312. case "Vote":
  313. voteAreaCount += totals;
  314. break;
  315. case "Homework":
  316. homeworkAreaCount += totals;
  317. break;
  318. }
  319. tempActivity.census.Add(new KeyValuePair<object, long>(type, totals));
  320. }
  321. activityCount.Add(tempActivity);
  322. }
  323. return Ok(new { state = 200, schoolCount = schools.Count, countArea, totalTime, appraiseArea, examAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, schools = schoolInfos, schoolActivity = activityCount });
  324. }
  325. /// <summary>
  326. /// 依据活动Id查询活动详情信息 数据管理工具——查询工具
  327. /// </summary>
  328. /// <param name="jsonElement"></param>
  329. /// <returns></returns>
  330. [HttpPost("get-info")]
  331. public async Task<IActionResult> GetInfo(JsonElement jsonElement)
  332. {
  333. if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
  334. jsonElement.TryGetProperty("activity", out JsonElement activity);
  335. jsonElement.TryGetProperty("isPersonal", out JsonElement isPersonal);
  336. //if (jsonElement.TryGetProperty("", out JsonElement code)) return BadRequest();
  337. var cosmosClient = _azureCosmos.GetCosmosClient();
  338. List<object> infos = new List<object>();
  339. StringBuilder sqlTxt = new StringBuilder($"select * from c where c.id='{id}'");
  340. if (!string.IsNullOrEmpty($"{activity}"))
  341. {
  342. sqlTxt.Append($" and c.pk='{activity}'");
  343. }
  344. if (!string.IsNullOrEmpty($"{isPersonal}"))
  345. {
  346. if (bool.Parse($"{isPersonal}") == true)
  347. {
  348. sqlTxt.Append($" and c.scope='private'");
  349. }
  350. else
  351. {
  352. sqlTxt.Append($" and c.scope='school'");
  353. }
  354. }
  355. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { }))
  356. {
  357. using var json = await JsonDocument.ParseAsync(item.ContentStream);
  358. foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
  359. {
  360. infos.Add(obj.ToObject<object>());
  361. }
  362. }
  363. return Ok(new { state = 200, infos });
  364. }
  365. public record RecSchool
  366. {
  367. public string id { get; set; }
  368. public string name { get; set; }
  369. public string picture { get; set; }
  370. public int scale { get; set; }
  371. }
  372. private class SchoolInfos
  373. {
  374. public string schoolId { get; set; }
  375. public string schoolName { get; set; }
  376. public string picture { get; set; }
  377. //规模
  378. public int scale { get; set; }
  379. //教师人数
  380. public int teacherCount { get; set; }
  381. //学生人数
  382. public long studentCount { get; set; }
  383. //评审人数
  384. public int appraiseCount { get; set; }
  385. //参训人数
  386. public int trainCount { get; set; }
  387. }
  388. public record ActivityCount
  389. {
  390. public string id { get; set; }
  391. public string name { get; set; }
  392. public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
  393. }
  394. }
  395. }