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));
}
}
