using System.Collections.Generic; using System.Threading.Tasks; using AutoMapper; using GxPress.Common.AppOptions; using GxPress.Common.Tools; using GxPress.Entity; using Microsoft.Extensions.Options; using Datory; using GxPress.Request.Notice; using SqlKata; using GxPress.Result.Notice; using Dapper; using System; using GxPress.Repository.Interface; using GxPress.Result.App.Missive; using GxPress.Request.App.Missive; namespace GxPress.Repository.Implement { public class NoticeAddresseeRepository : IAddresseeRepository { private readonly Repository _repository; private readonly IMapper _mapper; private readonly string _connectionString; private readonly string _databaseTypeStr; public NoticeAddresseeRepository(IOptionsMonitor dbOptionsAccessor, IMapper mapper) { _databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType; _connectionString = dbOptionsAccessor.CurrentValue.ConnectionString; var databaseType = StringUtils.ToEnum(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql); var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString); _repository = new Repository(database); _mapper = mapper; } public IDatabase Database => _repository.Database; public string TableName => _repository.TableName; public List TableColumns => _repository.TableColumns; /// /// 根据通知ID获取收件人 /// /// /// public async Task> GetNoticeAddresseeByNoticeIdAsync(int noticeId) { return await _repository.GetAllAsync(Q.Where(nameof(Addressee.SourceId), noticeId)); } public async Task DeleteAsync(int noticeId, int sourceType, int isAdmin) { var query = new SqlKata.Query(); if (isAdmin == 0) query.Where(nameof(Addressee.IsAdmin), false); if (isAdmin == 1) query.Where(nameof(Addressee.IsAdmin), true); query.Where(nameof(Addressee.SourceId), noticeId); query.Where(nameof(Addressee.SourceType), sourceType); return await _repository.DeleteAsync(query) > 0; } public async Task InsertAsync(Addressee noticeAddressee) { return await _repository.InsertAsync(noticeAddressee); } /// /// 插入收件人 /// /// /// public async Task InsertAsync(List noticeAddressees) { if (noticeAddressees.Count == 0) return false; var sql = "INSERT INTO `tede_addressee`(`Guid`,`CreatedDate`,`LastModifiedDate`,`SourceId`,`SourceType`,`UserId`,`UserName`,`IsRead`,`NoticeAddresseeType`,`IsTop`,`IsAdmin`,`IsDelete`)VALUES"; foreach (var item in noticeAddressees) { var guId = Guid.NewGuid().ToString(); var createdDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); var isRead = item.IsRead == true ? 1 : 0; var isTop = item.IsTop == true ? 1 : 0; var isAdmin = item.IsAdmin == true ? 1 : 0; var isDelete = item.IsDelete == true ? 1 : 0; sql += $"('{guId}','{createdDate}','{createdDate}',{item.SourceId},{item.SourceType},{item.UserId},'',{isRead},{item.NoticeAddresseeType.GetHashCode()},{isTop},{isAdmin},{isDelete}),"; } sql = sql.Remove(sql.Length - 1, 1); var databaseType = StringUtils.ToEnum(_databaseTypeStr, DatabaseType.MySql); var database = new Database(databaseType, _connectionString); var connection = database.GetConnection(); var items = await connection.ExecuteAsync(sql); return items > 0; } /// /// 查询通知的未读已读 /// /// /// public async Task> GetNoticeAddresseeByUserIdIdAsync(NoticeReadListRequest request) { var query = new SqlKata.Query("tede_addressee"); query.Join("tede_user as b", "b.Id", "tede_addressee.UserId"); query.Where("tede_addressee.SourceId", request.NoticeId); if (!string.IsNullOrEmpty(request.Key)) query.WhereLike("b.Name", $"%{request.Key}%"); if (request.ReadType > 0) query.Where("tede_addressee.IsRead", request.ReadType == 1); query.OrderByDesc("tede_addressee.LastModifiedDate"); var departmentNameQuery = new SqlKata.Query("tede_department"); var departmentIdsQuery = new SqlKata.Query("tede_department_user"); departmentIdsQuery.WhereColumns("tede_department_user.userId", "=", "tede_addressee.UserId"); departmentIdsQuery.Select("DepartmentId"); departmentNameQuery.WhereIn(nameof(Entity.Department.Id), departmentIdsQuery); departmentNameQuery.Limit(1); departmentNameQuery.Select("Name"); query.Select(departmentNameQuery, "DepartmentName"); query.Select("tede_addressee.{SourceId as NoticeId,LastModifiedDate,UserId}"); query.Select("b.{AvatarUrl,Name}"); var result = await _repository.GetAllAsync(query); foreach (var item in result) item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl); return result; } /// /// 获取收件人 /// /// /// public async Task> GetUserByNoticeIdAsync(int noticeId) { var sql = $"SELECT b.* FROM tede_addressee a inner join tede_user b on a.UserId=b.Id where a.SourceId={noticeId} and a.IsAdmin=0;"; var databaseType = StringUtils.ToEnum(_databaseTypeStr, DatabaseType.MySql); var database = new Database(databaseType, _connectionString); var connection = database.GetConnection(); var items = await connection .QueryAsync(sql); return items; } public async Task CountAsync(Query query) { return await _repository.CountAsync(query); } public async Task> GetAllAsync(Query query) { return await _repository.GetAllAsync(query); } public async Task UpdateAsync(Query query) { return await _repository.UpdateAsync(query) > 0; } public async Task ExistsAsync(Query query) { return await _repository.ExistsAsync(query); } /// /// 站内信未读 /// /// /// public async Task> GetMissiveReadListAsync(MissiveReadListRequest request) { var query = new SqlKata.Query("tede_addressee"); query.Join("tede_user as b", "b.Id", "tede_addressee.UserId"); query.Where("tede_addressee.SourceId", request.MissiveId); if (!string.IsNullOrEmpty(request.Key)) query.WhereLike("b.Name", $"%{request.Key}%"); if (request.ReadType > 0) query.Where("tede_addressee.IsRead", request.ReadType == 1); query.OrderByDesc("tede_addressee.LastModifiedDate"); var departmentNameQuery = new SqlKata.Query("tede_department"); var departmentIdsQuery = new SqlKata.Query("tede_department_user"); departmentIdsQuery.WhereColumns("tede_department_user.userId", "=", "tede_addressee.UserId"); departmentIdsQuery.Select("DepartmentId"); departmentNameQuery.WhereIn(nameof(Entity.Department.Id), departmentIdsQuery); departmentNameQuery.Limit(1); departmentNameQuery.Select("Name"); query.Select(departmentNameQuery, "DepartmentName"); query.Select("tede_addressee.{SourceId as MissiveId,LastModifiedDate,UserId}"); query.Select("b.{AvatarUrl,Name}"); var result = await _repository.GetAllAsync(query); foreach (var item in result) item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl); return result; } } }