public List <SystemMessageES> List(int pageSize, int pageIndex, ArticleAccountType articleAccountType, Guid AccountId, bool isZH) { string GetDefault(string key) { return(Resources.ResourceManager.GetString(key, new System.Globalization.CultureInfo(isZH ? "zh" : "en"))); } string GetIntro(string input) { if (string.IsNullOrEmpty(input)) { return(input); } var reg = new Regex(@"<[^<]+>"); var reg2 = new Regex(@"&[^&]+;"); var s = reg.Replace(input, ""); s = reg2.Replace(s, ""); s = s.Substring(0, Math.Min(s.Length, 50)); return(s); } var list = new ArticleDAC().List(pageSize, pageIndex, articleAccountType, AccountId); list.ForEach(a => { a.Title = a.Type == SystemMessageESType.Article ? a.Title : GetDefault(VerifyTypeList[int.Parse(a.Attach)]); a.Intro = a.Type == SystemMessageESType.Article ? GetIntro(a.Body) : (string.IsNullOrWhiteSpace(a.Body) ? (GetDefault(VerifyDefaultList[int.Parse(a.Attach)])) : a.Body); if (a.Type == SystemMessageESType.Verify) { switch ((VerifyRecordType)int.Parse(a.Attach)) { case VerifyRecordType.UserLv1Verified: if (string.IsNullOrWhiteSpace(a.Body)) { a.Body = Resources.ResourceManager.GetString("User_KYC_LV1_DefaultBody"); } break; case VerifyRecordType.UserLv1Reject: break; case VerifyRecordType.UserLv2Verified: if (string.IsNullOrWhiteSpace(a.Body)) { a.Body = Resources.ResourceManager.GetString("User_KYC_LV2_DefaultBody"); } break; case VerifyRecordType.UserLv2Reject: break; default: throw new ArgumentOutOfRangeException(); } } }); return(list); }
/// <summary> /// 分页获取公告列表 /// </summary> /// <param name="pageSize"></param> /// <param name="pageIndex">从1开始</param> /// <param name="articleAccountType"></param> /// <param name="accountId"></param> /// <returns></returns> public List <SystemMessageES> AllList(ArticleAccountType articleAccountType, Guid accountId) { using (var con = ReadConnection()) { return(con.Query <SystemMessageES>($@"select g.* from ( SELECT a.Id, 0 as Type, a.Title, a.Body, a.CreateTime, NULL AS Attach, CASE WHEN b.Id IS NULL THEN 0 ELSE 1 END AS [Read] FROM Articles a LEFT JOIN ( SELECT c.* FROM ReadRecords c WHERE c.AccountId = @AccountId) b ON b.TargetId = a.Id AND b.Type = 0 WHERE a.AccountType = @articleAccountType) g where g.[Read] = 0 ", new { articleAccountType, AccountId = accountId }).AsList()); } }
/// <summary> /// 分页获取公告列表 /// </summary> /// <param name="pageSize"></param> /// <param name="pageIndex">从1开始</param> /// <param name="articleAccountType"></param> /// <param name="accountId"></param> /// <returns></returns> public List <SystemMessageES> List(int pageSize, int pageIndex, ArticleAccountType articleAccountType, Guid accountId) { using (var con = ReadConnection()) { return(con.Query <SystemMessageES>($@"SELECT g.* FROM ( SELECT a.Id, 0 as Type, a.Title, a.Body, a.CreateTime, NULL AS Attach, CASE WHEN b.Id IS NULL THEN 0 ELSE 1 END AS [Read] FROM Articles a LEFT JOIN ( SELECT c.* FROM ReadRecords c WHERE c.AccountId = @AccountId) b ON b.TargetId = a.Id AND b.Type = 0 WHERE a.AccountType = @articleAccountType ) g ORDER BY g.[Read], g.CreateTime DESC OFFSET {pageSize * (pageIndex - 1)} ROWS FETCH NEXT {pageSize} ROWS ONLY", new { articleAccountType, AccountId = accountId }).AsList()); } }
public GetFirstTitleAndNotReadCountOM GetFirstTitleAndNotReadCount(ArticleAccountType articleAccountType, Guid AccountId, bool isZH) { var tuple = new ArticleDAC().GetFirstTitleAndNotReadCount(articleAccountType, AccountId); var model = tuple.Item1; return(new GetFirstTitleAndNotReadCountOM { SysCount = tuple.Item2, Title = model == null ? null : model.Type == SystemMessageESType.Article ? model.Title : Resources.ResourceManager.GetString(VerifyTypeList[int.Parse(model.Attach)], new System.Globalization.CultureInfo(isZH ? "zh" : "en")), Timestamp = model?.CreateTime.ToUnixTime().ToString(), Count = MessagesComponent.GetMessagesCountByStatus(AccountId, UserType.User, MessageStatus.Normal) }); }
public int GetUnreadCount(ArticleAccountType articleAccountType, Guid accountId) { const string sql = @"SELECT COUNT(1) FROM ( (SELECT * FROM Articles WHERE AccountType=@articleAccountType) a LEFT JOIN (SELECT distinct TargetId FROM ReadRecords WHERE AccountId=@accountId) b ON a.Id = b.TargetId ) WHERE b.TargetId is null"; using (var con = ReadConnection()) { var count = con.ExecuteScalar <int>(sql, new { articleAccountType, AccountId = accountId }); return(count); } }
public Tuple <SystemMessageES, int> GetFirstTitleAndNotReadCount(ArticleAccountType articleAccountType, Guid accountId) { using (var con = ReadConnection()) { var model = con.QueryFirstOrDefault <SystemMessageES>($@"SELECT TOP 1 g.* FROM ( SELECT a.Id, 0 as Type, a.Title, a.Body, a.CreateTime, NULL AS Attach, CASE WHEN b.Id IS NULL THEN 0 ELSE 1 END AS [Read] FROM Articles a LEFT JOIN ( SELECT c.* FROM ReadRecords c WHERE c.AccountId = @AccountId) b ON b.TargetId = a.Id AND b.Type = 0 WHERE a.AccountType = @articleAccountType ) g ORDER BY g.[Read], g.CreateTime DESC", new { articleAccountType, AccountId = accountId }); var count = con.ExecuteScalar <int>(@"SELECT COUNT(*) FROM ( SELECT a.Id, 0 as Type, a.Title, a.Body, a.CreateTime, NULL AS Attach, CASE WHEN b.Id IS NULL THEN 0 ELSE 1 END AS [Read] FROM Articles a LEFT JOIN ( SELECT c.* FROM ReadRecords c WHERE c.AccountId = @AccountId) b ON b.TargetId = a.Id AND b.Type = 0 WHERE a.AccountType = @articleAccountType ) g WHERE g.[Read] = 0", new { articleAccountType, AccountId = accountId }); return(new Tuple <SystemMessageES, int>(model, count)); } }