TopicRepository.cs 38 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830
  1. using System.Collections.Generic;
  2. using System.Linq;
  3. using System.Threading.Tasks;
  4. using AutoMapper;
  5. using Dapper;
  6. using GxPress.Common.AppOptions;
  7. using GxPress.Common.Page;
  8. using GxPress.Common.Tools;
  9. using GxPress.Entity;
  10. using GxPress.Repository.Interface;
  11. using GxPress.Request.App.Topic;
  12. using GxPress.Result.App.Topic;
  13. using Microsoft.Extensions.Options;
  14. using Datory;
  15. using GxPress.Entity.Topic;
  16. using GxPress.Result;
  17. using SqlKata;
  18. using GxPress.Result.Job;
  19. using GxPress.EnumConst;
  20. using Newtonsoft.Json;
  21. namespace GxPress.Repository.Implement
  22. {
  23. public class TopicRepository : ITopicRepository
  24. {
  25. private readonly Repository<Entity.Topic.Topic> _repository;
  26. private readonly Repository<TopicAddressee> _topicAddresseeRepository;
  27. private readonly Repository<User> _userRepository;
  28. private readonly IMapper _mapper;
  29. private readonly string _connectionString;
  30. private readonly string _databaseTypeStr;
  31. private readonly Repository<GroupUser> _groupUserRepository;
  32. private readonly Repository<Entity.Visit.Visit> _visitRepository;
  33. public TopicRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper)
  34. {
  35. _databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType;
  36. _connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
  37. var databaseType = StringUtils.ToEnum<DatabaseType>(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql);
  38. var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString);
  39. _repository = new Repository<Entity.Topic.Topic>(database);
  40. _topicAddresseeRepository = new Repository<TopicAddressee>(database);
  41. _userRepository = new Repository<User>(database);
  42. _groupUserRepository = new Repository<GroupUser>(database);
  43. _visitRepository = new Repository<Entity.Visit.Visit>(database);
  44. _mapper = mapper;
  45. }
  46. public TopicRepository()
  47. {
  48. }
  49. public IDatabase Database => _repository.Database;
  50. public string TableName => _repository.TableName;
  51. public List<TableColumn> TableColumns => _repository.TableColumns;
  52. /// <summary>
  53. /// 根据userId获取话题
  54. /// </summary>
  55. /// <param name="request"></param>
  56. /// <returns></returns>
  57. public async Task<PagedList<Entity.Topic.Topic>> GetTopicByUserIdAsync(TopicPageSearchRequest request)
  58. {
  59. var query = Q.NewQuery();
  60. if (request.UserId > 0)
  61. {
  62. query.Where(nameof(Entity.Topic.Topic.UserId), request.UserId);
  63. }
  64. if (!string.IsNullOrEmpty(request.Keyword))
  65. {
  66. var like = $"%{request.Keyword}%";
  67. query.WhereLike(nameof(Entity.Topic.Topic.Title), like);
  68. }
  69. var pagedList = new PagedList<Entity.Topic.Topic>
  70. {
  71. Total = await _repository.CountAsync(query)
  72. };
  73. var list = await _repository.GetAllAsync(query.ForPage(request.Page, request.PerPage));
  74. pagedList.Items = list;
  75. return pagedList;
  76. }
  77. /// <summary>
  78. /// APP列表显示用
  79. /// </summary>
  80. /// <param name="contentJsonDataList"></param>
  81. /// <returns></returns>
  82. public List<ContentJsonData> GetListAsync(List<ContentJsonData> contentJsonDataList)
  83. {
  84. var result = new List<ContentJsonData>();
  85. if (contentJsonDataList.Count > 0)
  86. {
  87. //查询文本
  88. var txtType = contentJsonDataList.FirstOrDefault(n => n.Type == 1);
  89. if (txtType != null)
  90. result.Add(txtType);
  91. //拼接图片
  92. var imgType = contentJsonDataList.FindAll(n => n.Type == 2);
  93. if (imgType.Any())
  94. {
  95. foreach (var item in imgType)
  96. {
  97. //判断图片数量
  98. if (result.Count(n => n.Type == 2) > 8)
  99. break;
  100. result.Add(item);
  101. }
  102. }
  103. }
  104. return result;
  105. }
  106. /// <summary>
  107. /// 根据GroupId获取通知
  108. /// </summary>
  109. /// <param name="request"></param>
  110. /// <returns></returns>
  111. public async Task<PagedList<TopicListPageResult>> FindTopicByGroupIdAsync(TopicDetailListRequest request)
  112. {
  113. var topicConts = GxPress.EnumConst.AllTypeConst.Topic.GetHashCode();
  114. //用户收件人
  115. var resultData = new PagedList<TopicListPageResult>();
  116. var showKey = $@"SELECT a.*,(SELECT
  117. COUNT(1)
  118. FROM
  119. tede_analyze
  120. WHERE
  121. TypeValue ={topicConts}
  122. AND SourceId = a.Id
  123. AND AnalyzeType = 1) AS PraiseCount,
  124. (SELECT
  125. COUNT(1)
  126. FROM
  127. tede_analyze
  128. WHERE
  129. UserId = {request.UserId} AND TypeValue = {topicConts}
  130. AND SourceId = a.Id
  131. AND AnalyzeType = 1
  132. LIMIT 0 , 1) AS IsPraise,
  133. (SELECT
  134. COUNT(1)
  135. FROM
  136. tede_comment
  137. WHERE
  138. ArticleId = a.Id and pid=0
  139. AND TypeValue = 1) AS CommentCount,
  140. (SELECT
  141. COUNT(1)
  142. FROM
  143. tede_analyze
  144. WHERE
  145. UserId = {request.UserId} AND TypeValue = {topicConts}
  146. AND SourceId = a.Id
  147. AND AnalyzeType = 4) AS RetransmissionCount,
  148. (SELECT
  149. COUNT(1)
  150. FROM
  151. tede_analyze
  152. WHERE
  153. UserId = {request.UserId} AND TypeValue = {topicConts}
  154. AND SourceId = a.Id
  155. AND AnalyzeType = 4
  156. LIMIT 0 , 1) AS IsRetransmission,
  157. (SELECT
  158. COUNT(1)
  159. FROM
  160. tede_analyze
  161. WHERE
  162. UserId = {request.UserId} AND TypeValue = {topicConts}
  163. AND SourceId = a.Id
  164. AND AnalyzeType = 4
  165. LIMIT 0 , 1) AS IsCollect,
  166. (SELECT
  167. COUNT(1)
  168. FROM
  169. tede_analyze
  170. WHERE
  171. UserId = {request.UserId} AND TypeValue = {topicConts}
  172. AND SourceId = a.Id
  173. AND AnalyzeType = 4) AS CollectCount,b.Name,b.AvatarUrl,c.Name FROM";
  174. var countSql = $"SELECT count(1) FROM tede_topic a inner join tede_user b on a.UserId=b.Id inner join tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
  175. var sql =
  176. $"{showKey} tede_topic a inner join tede_user b on a.UserId=b.Id inner join tede_Department c on c.Id=b.DepartmentId where a.GroupId={request.GroupId}";
  177. var strValue = "";
  178. if (!string.IsNullOrEmpty(request.Key))
  179. strValue += $" and (a.Title like '%{request.Key}%' or a.TextContent like '%{request.Key}%' or b.Name like '%{request.Key}%')";
  180. sql += $"{strValue} order by a.CreatedDate DESC LIMIT @page , @pageSize";
  181. countSql += $"{strValue}";
  182. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  183. var database = new Database(databaseType, _connectionString);
  184. var connection = database.GetConnection();
  185. var result =
  186. connection
  187. .Query<TopicListPageResult, User, Department, TopicListPageResult>(sql,
  188. (topicListPageResult, user, department) =>
  189. {
  190. topicListPageResult.DepartmentName = department.Name;
  191. topicListPageResult.UserName = user.Name;
  192. topicListPageResult.AvatarUrl = StringUtils.AddDomainMin(user.AvatarUrl);
  193. return topicListPageResult;
  194. }, new { page = (request.Page - 1) * request.PerPage, pageSize = request.PerPage }, splitOn: "Name");
  195. resultData.Total = await connection.ExecuteScalarAsync<int>(countSql);
  196. var topicListPageResults = result as TopicListPageResult[] ?? result.ToArray();
  197. resultData.Items = topicListPageResults;
  198. return resultData;
  199. }
  200. /// <summary>
  201. ///最新 小组话题分页列表 匿名用户登录获取
  202. /// </summary>
  203. /// <param name="request"></param>
  204. /// <returns></returns>
  205. public async Task<PagedList<TopicListPageResult>> GetAnonymousGroupTopicPageAsync(TopicDetailListRequest request)
  206. {
  207. var topicConst = AllTypeConst.Topic.GetHashCode();
  208. var result = new PagedList<TopicListPageResult>();
  209. var sqlStr = @" AND a.GroupId IN (SELECT
  210. Id
  211. FROM
  212. tede_group
  213. WHERE
  214. IsShow = 1) ";
  215. sqlStr += $" and a.IsDraft={(request.IsDraft == true ? 1 : 0)}";
  216. if (!string.IsNullOrEmpty(request.Key))
  217. {
  218. sqlStr += $@"AND (a.Title LIKE '%{request.Key}%'
  219. OR a.TextContent LIKE '%{request.Key}%')";
  220. }
  221. string sql = $@"SELECT
  222. a.Id,
  223. a.Title,
  224. a.UserId,
  225. a.ReadCount,
  226. a.CreatedDate,
  227. a.Content,
  228. a.GroupId,
  229. (SELECT
  230. COUNT(1)
  231. FROM
  232. tede_analyze
  233. WHERE
  234. TypeValue ={topicConst} AND SourceId = a.Id
  235. AND AnalyzeType = 1) AS PraiseCount,
  236. (SELECT
  237. COUNT(1)
  238. FROM
  239. tede_analyze
  240. WHERE
  241. UserId ={request.UserId} AND TypeValue = {topicConst}
  242. AND SourceId = a.Id
  243. AND AnalyzeType = 1
  244. LIMIT 0 , 1) AS IsPraise,
  245. (SELECT
  246. COUNT(1)
  247. FROM
  248. tede_comment
  249. WHERE
  250. ArticleId = a.Id AND pid = 0
  251. AND TypeValue = 1) AS CommentCount,
  252. (SELECT
  253. COUNT(1)
  254. FROM
  255. tede_analyze
  256. WHERE
  257. UserId ={request.UserId} AND TypeValue ={topicConst}
  258. AND SourceId = a.Id
  259. AND AnalyzeType = 4) AS RetransmissionCount,
  260. (SELECT
  261. COUNT(1)
  262. FROM
  263. tede_analyze
  264. WHERE
  265. UserId ={request.UserId} AND TypeValue ={topicConst}
  266. AND SourceId = a.Id
  267. AND AnalyzeType = 4
  268. LIMIT 0 , 1) AS IsRetransmission,
  269. (SELECT
  270. COUNT(1)
  271. FROM
  272. tede_analyze
  273. WHERE
  274. UserId ={request.UserId} AND TypeValue = {topicConst}
  275. AND SourceId = a.Id
  276. AND AnalyzeType = 4
  277. LIMIT 0 , 1) AS IsCollect,
  278. (SELECT
  279. COUNT(1)
  280. FROM
  281. tede_analyze
  282. WHERE
  283. UserId ={request.UserId} AND TypeValue = {topicConst}
  284. AND SourceId = a.Id
  285. AND AnalyzeType = 4) AS CollectCount,
  286. b.Name as GroupName,
  287. c.Name as UserName,
  288. c.AvatarUrl,
  289. d.Id as DepartmentId,
  290. d.Name as DepartmentName
  291. FROM
  292. tede_topic a
  293. INNER JOIN
  294. tede_group b ON a.GroupId = b.Id
  295. INNER JOIN
  296. tede_user c ON c.Id = a.UserId
  297. INNER JOIN
  298. tede_department d ON d.Id = c.DepartmentId
  299. WHERE
  300. a.GroupId > 0 and a.IsDraft=0
  301. {sqlStr}
  302. ORDER BY a.CreatedDate DESC limit @page,@pageSize";
  303. string countSql = $@"SELECT
  304. count(1)
  305. FROM
  306. tede_topic a
  307. INNER JOIN
  308. tede_group b ON a.GroupId = b.Id
  309. INNER JOIN
  310. tede_user c ON c.Id = a.UserId
  311. WHERE
  312. a.GroupId > 0 and a.IsDraft=0
  313. {sqlStr}
  314. ORDER BY a.CreatedDate DESC";
  315. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  316. var database = new Database(databaseType, _connectionString);
  317. var connection = database.GetConnection();
  318. result.Items =
  319. await connection
  320. .QueryAsync<TopicListPageResult>(sql, new { page = (request.Page - 1) * request.PerPage, pageSize = request.PerPage });
  321. foreach (var item in result.Items)
  322. {
  323. item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
  324. }
  325. result.Total = await connection.ExecuteScalarAsync<int>(countSql);
  326. return result;
  327. }
  328. /// <summary>
  329. ///最新 小组话题分页列表
  330. /// </summary>
  331. /// <param name="request"></param>
  332. /// <returns></returns>
  333. public async Task<PagedList<TopicListPageResult>> GetGroupTopicPageAsync(TopicDetailListRequest request)
  334. {
  335. var topicConst = AllTypeConst.Topic.GetHashCode();
  336. var result = new PagedList<TopicListPageResult>();
  337. var sqlStr = $" and a.IsDraft={(request.IsDraft == true ? 1 : 0)}";
  338. if (request.IsDraft)
  339. sqlStr += $" and a.UserId={request.UserId}";
  340. if (request.GroupIds.Count() == 0)
  341. {
  342. sqlStr += $@" AND a.GroupId IN (SELECT
  343. GroupId
  344. FROM
  345. tede_group_user
  346. WHERE
  347. UserId ={request.UserId})";
  348. }
  349. if (request.GroupIds.Count > 0)
  350. {
  351. var groupIds = new List<int>();
  352. foreach (var item in request.GroupIds)
  353. {
  354. if (item > 0)
  355. groupIds.Add(item);
  356. //全网
  357. if (item == -1)
  358. groupIds.AddRange(await GetPublicGroupAsync());
  359. //我的小组
  360. if (item == -2)
  361. groupIds.AddRange(await GetIntoGroupAsync(request.UserId));
  362. }
  363. var groupIdStr = string.Empty;
  364. foreach (var item in groupIds)
  365. groupIdStr += $"{item},";
  366. if (!string.IsNullOrEmpty(groupIdStr))
  367. {
  368. groupIdStr = groupIdStr.Remove(groupIdStr.Length - 1, 1);
  369. sqlStr += $@" AND a.GroupId IN (SELECT
  370. GroupId
  371. FROM
  372. tede_group_user
  373. WHERE
  374. UserId ={request.UserId} AND GroupId In ({groupIdStr}))";
  375. }
  376. }
  377. if (!string.IsNullOrEmpty(request.Key))
  378. {
  379. sqlStr += $@"AND (a.Title LIKE '%{request.Key}%'
  380. OR a.TextContent LIKE '%{request.Key}%')";
  381. }
  382. string sql = $@"SELECT
  383. a.Id,
  384. a.Title,
  385. a.UserId,
  386. a.ReadCount,
  387. a.CreatedDate,
  388. a.Content,
  389. a.GroupId,
  390. (SELECT
  391. COUNT(1)
  392. FROM
  393. tede_analyze
  394. WHERE
  395. TypeValue ={topicConst} AND SourceId = a.Id
  396. AND AnalyzeType = 1) AS PraiseCount,
  397. (SELECT
  398. COUNT(1)
  399. FROM
  400. tede_analyze
  401. WHERE
  402. UserId ={request.UserId} AND TypeValue = {topicConst}
  403. AND SourceId = a.Id
  404. AND AnalyzeType = 1
  405. LIMIT 0 , 1) AS IsPraise,
  406. (SELECT
  407. COUNT(1)
  408. FROM
  409. tede_comment
  410. WHERE
  411. ArticleId = a.Id AND pid = 0
  412. AND TypeValue={topicConst}) AS CommentCount,
  413. (SELECT
  414. COUNT(1)
  415. FROM
  416. tede_analyze
  417. WHERE
  418. UserId ={request.UserId} AND TypeValue ={topicConst}
  419. AND SourceId = a.Id
  420. AND AnalyzeType = 4) AS RetransmissionCount,
  421. (SELECT
  422. COUNT(1)
  423. FROM
  424. tede_analyze
  425. WHERE
  426. UserId ={request.UserId} AND TypeValue ={topicConst}
  427. AND SourceId = a.Id
  428. AND AnalyzeType = 4
  429. LIMIT 0 , 1) AS IsRetransmission,
  430. (SELECT
  431. COUNT(1)
  432. FROM
  433. tede_analyze
  434. WHERE
  435. UserId ={request.UserId} AND TypeValue = {topicConst}
  436. AND SourceId = a.Id
  437. AND AnalyzeType = 4
  438. LIMIT 0 , 1) AS IsCollect,
  439. (SELECT
  440. COUNT(1)
  441. FROM
  442. tede_analyze
  443. WHERE
  444. UserId ={request.UserId} AND TypeValue = {topicConst}
  445. AND SourceId = a.Id
  446. AND AnalyzeType = 4) AS CollectCount,
  447. b.Name as GroupName,
  448. c.Name as UserName,
  449. c.AvatarUrl,
  450. d.Id as DepartmentId,
  451. d.Name as DepartmentName
  452. FROM
  453. tede_topic a
  454. INNER JOIN
  455. tede_group b ON a.GroupId = b.Id
  456. INNER JOIN
  457. tede_user c ON c.Id = a.UserId
  458. INNER JOIN
  459. tede_department d ON d.Id = c.DepartmentId
  460. WHERE
  461. a.GroupId > 0
  462. {sqlStr}
  463. ORDER BY a.CreatedDate DESC limit {(request.Page - 1) * request.PerPage},{request.Page * request.PerPage}";
  464. string countSql = $@"SELECT
  465. count(1)
  466. FROM
  467. tede_topic a
  468. INNER JOIN
  469. tede_group b ON a.GroupId = b.Id
  470. INNER JOIN
  471. tede_user c ON c.Id = a.UserId
  472. WHERE
  473. a.GroupId > 0
  474. {sqlStr}";
  475. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  476. var database = new Database(databaseType, _connectionString);
  477. var connection = database.GetConnection();
  478. result.Items = await connection.QueryAsync<TopicListPageResult>(sql);
  479. foreach (var item in result.Items)
  480. {
  481. item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
  482. }
  483. result.Total = await connection.ExecuteScalarAsync<int>(countSql);
  484. //回去小组草稿数量
  485. var query = Q.NewQuery();
  486. query.WhereIn(nameof(Entity.Topic.Topic.GroupId), request.GroupIds);
  487. query.Where(nameof(Entity.Topic.Topic.IsDraft), true);
  488. query.Where(nameof(Entity.Topic.Topic.UserId), request.UserId);
  489. result.DraftCount = await CountAsync(query);
  490. return result;
  491. }
  492. /// <summary>
  493. /// 获取共享小组
  494. /// </summary>
  495. /// <returns></returns>
  496. public async Task<IEnumerable<int>> GetPublicGroupAsync()
  497. {
  498. string sql = @"select Id from tede_group where IsShow=1";
  499. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  500. var database = new Database(databaseType, _connectionString);
  501. var connection = database.GetConnection();
  502. var result = await connection.QueryAsync<int>(sql);
  503. return result;
  504. }
  505. /// <summary>
  506. /// 我加入的小组
  507. /// </summary>
  508. /// <param name="userId"></param>
  509. /// <returns></returns>
  510. public async Task<IEnumerable<int>> GetIntoGroupAsync(int userId)
  511. {
  512. string sql = $"select a.Id from tede_group a inner join tede_group_user b on a.Id=b.GroupId where b.UserId={userId}";
  513. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  514. var database = new Database(databaseType, _connectionString);
  515. var connection = database.GetConnection();
  516. return await connection.QueryAsync<int>(sql);
  517. }
  518. /// <summary>
  519. /// 根据小组ID获取话题数量
  520. /// </summary>
  521. /// <param name="groupId"></param>
  522. /// <returns></returns>
  523. public async Task<int> GetGroupTopicCountAsync(int groupId)
  524. {
  525. return await _repository.CountAsync(Q.Where(nameof(Entity.Topic.Topic.GroupId), groupId));
  526. }
  527. /// <summary>
  528. /// 根据ID获取话题
  529. /// </summary>
  530. /// <param name="id"></param>
  531. /// <returns></returns>
  532. public async Task<Entity.Topic.Topic> GetAsync(int id)
  533. {
  534. return await _repository.GetAsync(id);
  535. }
  536. public async Task<int> CountAsync(Query query)
  537. {
  538. return await _repository.CountAsync(query);
  539. }
  540. public async Task<IEnumerable<Entity.Topic.Topic>> GetAllAsync(Query query)
  541. {
  542. return await _repository.GetAllAsync(query);
  543. }
  544. public async Task<int> InsertAsync(Entity.Topic.Topic topic)
  545. {
  546. return await _repository.InsertAsync(topic);
  547. }
  548. public async Task<bool> DeleteAsync(int id)
  549. {
  550. return await _repository.DeleteAsync(id);
  551. }
  552. public async Task<bool> UpdateAsync(Entity.Topic.Topic topic)
  553. {
  554. return await _repository.UpdateAsync(topic);
  555. }
  556. /// <summary>
  557. /// 执行话题
  558. /// </summary>
  559. /// <returns></returns>
  560. public async Task<List<JobTopicResult>> ExecuteTopic()
  561. {
  562. string sql = @"select a.*,b.UserId,b.Id,c.Name,d.AvatarUrl from tede_topic a
  563. inner join tede_topic_addressee b on a.Id=b.TopicId
  564. inner join tede_user c on c.Id=b.UserId
  565. inner join tede_user d on d.Id=a.UserId
  566. where b.IsUpload=0
  567. order by a.CreatedDate desc limit 0,100";
  568. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  569. var database = new Database(databaseType, _connectionString);
  570. var connection = database.GetConnection();
  571. var result = await connection.QueryAsync<JobTopicResult, TopicAddressee, User, User, JobTopicResult>(sql, (jobTopicResult, topicAddressee, user, userDto) =>
  572. {
  573. jobTopicResult.UserId = topicAddressee.UserId;
  574. jobTopicResult.Name = user.Name;
  575. jobTopicResult.AvatarUrl = userDto.AvatarUrl;
  576. jobTopicResult.TopicAddresseeId = topicAddressee.Id;
  577. return jobTopicResult;
  578. }, splitOn: "UserId,Name,AvatarUrl");
  579. connection.Dispose();
  580. return result.ToList();
  581. }
  582. /// <summary>
  583. /// 新版 获取笔记话题列表
  584. /// </summary>
  585. /// <param name="request"></param>
  586. /// <returns></returns>
  587. public async Task<PagedList<TopicListPageResult>> GetTopicPage(TopicPageSearchRequest request, string sql, string countSql)
  588. {
  589. var result = new PagedList<TopicListPageResult>();
  590. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  591. var database = new Database(databaseType, _connectionString);
  592. var connection = database.GetConnection();
  593. result.Items = await connection.QueryAsync<TopicListPageResult>(sql);
  594. result.Total = await connection.ExecuteScalarAsync<int>(countSql);
  595. connection.Dispose();
  596. foreach (var item in result.Items)
  597. {
  598. item.FolderResult.FolderName = item.FolderName;
  599. item.FolderResult.Id = item.ParentId;
  600. item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
  601. }
  602. return result;
  603. }
  604. public async Task<PagedList<TopicListPageResult>> GetTopicByGroupAsync(TopicPageSearchRequest request)
  605. {
  606. var topicConstValue = AllTypeConst.Topic.GetHashCode();
  607. var sql = $@"SELECT
  608. a.*,
  609. (SELECT
  610. COUNT(1)
  611. FROM
  612. tede_analyze
  613. WHERE
  614. TypeValue ={topicConstValue} AND SourceId = a.Id
  615. AND AnalyzeType = 1) AS PraiseCount,
  616. (SELECT
  617. COUNT(1)
  618. FROM
  619. tede_analyze
  620. WHERE
  621. UserId = {request.UserId} AND TypeValue = {topicConstValue}
  622. AND SourceId = a.Id
  623. AND AnalyzeType = 1
  624. LIMIT 0 , 1) AS IsPraise,
  625. (SELECT
  626. COUNT(1)
  627. FROM
  628. tede_comment
  629. WHERE
  630. ArticleId = a.Id AND TypeValue = {topicConstValue}
  631. AND Pid = 0) AS CommentCount,
  632. (SELECT
  633. COUNT(1)
  634. FROM
  635. tede_analyze
  636. WHERE
  637. UserId = {request.UserId} AND TypeValue = {topicConstValue}
  638. AND SourceId = a.Id
  639. AND AnalyzeType = 4) AS RetransmissionCount,
  640. (SELECT
  641. COUNT(1)
  642. FROM
  643. tede_analyze
  644. WHERE
  645. UserId = {request.UserId} AND TypeValue = {topicConstValue}
  646. AND SourceId = a.Id
  647. AND AnalyzeType = 4
  648. LIMIT 0 , 1) AS IsRetransmission,
  649. (SELECT
  650. COUNT(1)
  651. FROM
  652. tede_analyze
  653. WHERE
  654. UserId = {request.UserId} AND TypeValue = {topicConstValue}
  655. AND SourceId = a.Id
  656. AND AnalyzeType = 3
  657. LIMIT 0 , 1) AS IsCollect,
  658. b.Id as GroupId,
  659. b.Name,
  660. c.Name,
  661. c.AvatarUrl
  662. FROM
  663. tede_topic a
  664. INNER JOIN
  665. tede_group b ON a.GroupId = b.id
  666. INNER JOIN
  667. tede_user c ON a.UserId = c.Id
  668. WHERE 1=1 ";
  669. if (request.TopicGroupIds.Count > 0)
  670. {
  671. var groupStr = "(";
  672. foreach (var item in request.TopicGroupIds)
  673. {
  674. groupStr += $"{item},";
  675. }
  676. groupStr = groupStr.Remove(groupStr.Length - 1, 1);
  677. groupStr += ")";
  678. sql += $@" and a.GroupId IN {groupStr}";
  679. }
  680. else
  681. sql += $@" and a.GroupId > 0";
  682. if (request.IsAdmin)
  683. {
  684. sql += $@" AND a.UserId={request.UserId}";
  685. }
  686. else
  687. {
  688. sql += $@" AND b.Id IN (SELECT
  689. GroupId
  690. FROM
  691. tede_group_user
  692. WHERE
  693. UserId = {request.UserId})";
  694. }
  695. if (!string.IsNullOrWhiteSpace(request.Keyword))
  696. {
  697. sql += $@" AND (c.Name LIKE '%{request.Keyword}%'
  698. OR a.Title LIKE '%{request.Keyword}%'
  699. OR a.TextContent LIKE '%{request.Keyword}%')";
  700. }
  701. if (!string.IsNullOrEmpty(request.Sort))
  702. sql += $@" ORDER BY a.ReadCount DESC";
  703. else
  704. sql += $@" ORDER BY a.CreatedDate DESC";
  705. sql += $@" LIMIT {(request.Page - 1) * request.PerPage} , {request.PerPage}";
  706. var result = new PagedList<TopicListPageResult>();
  707. var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
  708. var database = new Database(databaseType, _connectionString);
  709. var connection = database.GetConnection();
  710. result.Items = await connection.QueryAsync<TopicListPageResult, Entity.Group, User, TopicListPageResult>(sql, (topicListPageResult, group, user) =>
  711. {
  712. topicListPageResult.AvatarUrl = user != null ? StringUtils.AddDomainMin(user.AvatarUrl) : "";
  713. topicListPageResult.GroupName = group != null ? group.Name : "";
  714. topicListPageResult.UserName = user != null ? user.Name : "";
  715. return topicListPageResult;
  716. }, splitOn: "Id,GroupId,Name");
  717. sql = $@"SELECT
  718. count(1)
  719. FROM
  720. tede_topic a
  721. INNER JOIN
  722. tede_group b ON a.GroupId = b.id
  723. INNER JOIN
  724. tede_user c ON a.UserId = c.Id
  725. WHERE 1=1 ";
  726. if (request.TopicGroupIds.Count > 0)
  727. {
  728. var groupStr = "(";
  729. foreach (var item in request.TopicGroupIds)
  730. {
  731. groupStr += $"{item},";
  732. }
  733. groupStr = groupStr.Remove(groupStr.Length - 1, 1);
  734. groupStr += ")";
  735. sql += $@" and a.GroupId IN {groupStr}";
  736. }
  737. else
  738. sql += $@" and a.GroupId > 0";
  739. if (request.IsAdmin)
  740. {
  741. sql += $@" AND a.UserId={request.UserId}";
  742. }
  743. else
  744. {
  745. sql += $@" AND b.Id IN (SELECT
  746. GroupId
  747. FROM
  748. tede_group_user
  749. WHERE
  750. UserId = {request.UserId})";
  751. }
  752. if (!string.IsNullOrWhiteSpace(request.Keyword))
  753. {
  754. sql += $@" AND (c.Name LIKE '%{request.Keyword}%'
  755. OR a.Title LIKE '%{request.Keyword}%'
  756. OR a.TextContent LIKE '%{request.Keyword}%')";
  757. }
  758. result.Total = await connection.ExecuteScalarAsync<int>(sql);
  759. return result;
  760. }
  761. /// <summary>
  762. /// 删除草稿箱
  763. /// </summary>
  764. /// <param name="userId"></param>
  765. /// <param name="groupId"></param>
  766. /// <returns></returns>
  767. public async Task<bool> ClearDraftAsync(int userId, int groupId)
  768. {
  769. var query = Q.NewQuery();
  770. query.Where(nameof(Entity.Topic.Topic.UserId), userId);
  771. query.Where(nameof(Entity.Topic.Topic.IsDraft), true);
  772. query.Where(nameof(Entity.Topic.Topic.GroupId), groupId);
  773. return await _repository.DeleteAsync(query) > 0;
  774. }
  775. public async Task<bool> UpdateTopicTextContentAsync()
  776. {
  777. var query = Q.NewQuery();
  778. query.Where(nameof(Entity.Topic.Topic.TextContent), "=", "");
  779. query.OrderByDesc(nameof(Entity.Topic.Topic.CreatedDate));
  780. query.Limit(100);
  781. var result = await _repository.GetAllAsync(query);
  782. foreach (var item in result)
  783. {
  784. if (!string.IsNullOrEmpty(item.Content))
  785. {
  786. var contentJsonData = JsonConvert.DeserializeObject<List<ContentJsonData>>(item.Content);
  787. foreach (var data in contentJsonData)
  788. {
  789. if (data.Type == AllTypeConst.Text.GetHashCode())
  790. item.TextContent += data.Text;
  791. }
  792. if (!string.IsNullOrEmpty(item.TextContent))
  793. await _repository.UpdateAsync(item);
  794. else
  795. {
  796. item.TextContent = "[]";
  797. await _repository.UpdateAsync(item);
  798. }
  799. }
  800. }
  801. return true;
  802. }
  803. }
  804. }