Files

269 lines
13 KiB
C#
Executable File

using Microsoft.Extensions.DependencyInjection;
using Opsoft.Data;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.BaseData.IRepositories;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.BaseData.Model;
using Opsoft.NetCore.O3App.Shared;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Opsoft.NetCore.O3App.Domain;
namespace Opsoft.NetCore.O3App.Repositories.BaseData
{
[MapTo(typeof(IUserMessageRepository), ServiceLifetime.Transient)]
public class UserMessageRepository : RepositoryBase<SYS_USERMESSAGE>, IUserMessageRepository
{
public UserMessageRepository() : base()
{
}
/// <summary>
/// 获取全部用户的消息
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public async Task<List<UserMessages>> GetAllUserMessage(object data)
{
dynamic getData = JsonOperator.JsonDeserialize<dynamic>(data.ToString());
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = string.Format(@"SELECT X.FINTERID,
X.FTITLE,
X.FINFO,
X.FFROMUSER,
X.FDATETIME,
X.FISREAD
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY T2.FIsRead desc,T1.FDateTime) AS RowNumber,
T1.FINTERID,
T1.FTITLE,
T1.FINFO,
T1.FFROMUSER,
T1.FDATETIME,
T2.FISREAD
FROM SYS_USERMESSAGE T1
LEFT JOIN SYS_USERMESSAGEENTRY T2 ON T1.FInterID=T2.FInterID AND T2.FREADUSER = '{0}'
WHERE (T1.FToUser='{0}' or T1.FIsAll=1) AND ISNULL(T2.FISREAD,0)=0) AS X
WHERE X.RowNumber BETWEEN ({1}-1)*({2}+1) AND {1}*{2}
ORDER BY RowNumber DESC",
LoginInfo.UserID, getData.pageNum, getData.pageCount);
var retData = await context.Database.SqlQueryAsync<UserMessages>(sql);
return retData;
}
/// <summary>
/// 获取未读消息数量
/// </summary>
/// <returns></returns>
public async Task<List<GetAllUserMessageUnReadCountDto>> GetAllUserMessageUnReadCount()
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
//FISREAD=0 表示未读
string sql = $@"select count(*) NUM from (SELECT T2.FIsRead
FROM SYS_USERMESSAGE T1
LEFT JOIN SYS_USERMESSAGEENTRY T2 ON T1.FInterID=T2.FInterID AND T2.FREADUSER = '{LoginInfo.UserID}'
where (ISNULL(T2.FISREAD,0)=0 AND (T1.FTOUSER= '{LoginInfo.UserID}' OR T1.FISALL=1))
) as UnreadNum";
var retData = await context.Database.SqlQueryAsync<GetAllUserMessageUnReadCountDto>(sql);
return retData;
}
/// <summary>
/// 获取已读消息
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public async Task<List<UserMessages>> GetAllUserMessageRead(object data)
{
dynamic getData = JsonOperator.JsonDeserialize<dynamic>(data.ToString());
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = string.Format(@"SELECT X.FINTERID,
X.FTITLE,
X.FINFO,
X.FFROMUSER,
X.FDATETIME,
X.FISREAD
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY T2.FIsRead desc,T1.FDateTime) AS RowNumber,
T1.FINTERID,
T1.FTITLE,
T1.FINFO,
T1.FFROMUSER,
T1.FDATETIME,
T2.FISREAD
FROM SYS_USERMESSAGE T1
LEFT JOIN SYS_USERMESSAGEENTRY T2 ON T1.FInterID=T2.FInterID AND T2.FREADUSER = '{0}'
WHERE (T1.FToUser='{0}' or T1.FIsAll=1) and T2.FISREAD=1) AS X
WHERE X.RowNumber BETWEEN ({1}-1)*({2}+1) AND {1}*{2}
ORDER BY RowNumber DESC",
LoginInfo.UserID, getData.pageNum, getData.pageCount);
var retData = await context.Database.SqlQueryAsync<UserMessages>(sql);
return retData;
}
/// <summary>
/// 消息标记为已读
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public async Task<List<UpdateMessageStatus>> UpdateMessageStatus(object data)
{
dynamic getData = JsonOperator.JsonDeserialize<dynamic>(data.ToString());
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = $" IF NOT EXISTS (SELECT UID FROM SYS_USERMESSAGEENTRY WHERE FINTERID='{getData.fInterid}' AND FREADUSER='{LoginInfo.UserID}')"+
$" BEGIN INSERT INTO SYS_USERMESSAGEENTRY(FInterID, UID, FDateTime, FReadUser, FIsRead) VALUES('{getData.fInterid}', NewID(), getDate(), '{LoginInfo.UserID}', 1) END" +
$" ELSE BEGIN update SYS_USERMESSAGEENTRY SET FISREAD=1 WHERE FINTERID='{getData.fInterid}' AND FREADUSER='{LoginInfo.UserID}' END";
await context.Database.SqlNonQueryAsync(sql);
string sql1 = $"select FREADUSER,FDATETIME,FISREAD from SYS_USERMESSAGEENTRY WHERE FINTERID='{getData.fInterid}' AND FREADUSER='{LoginInfo.UserID}'";
var retData = await context.Database.SqlQueryAsync<UpdateMessageStatus>(sql1);
return retData;
}
/// <summary>
/// 新闻列表显示方法
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public async Task<List<SYS_NEWS>> GetAllNewsBulletinUnRead(object data)
{
dynamic getData = JsonOperator.JsonDeserialize<dynamic>(data.ToString());
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = string.Format(@"SELECT
FNAME,
FCONTENT,
FISOUTALL,
FISINALL,
FCHECKDATE
FROM SYS_ENANNOU
WHERE FNAME BETWEEN ({1}-1)*({2}+1) AND {1}*{2}
ORDER BY FNAME DESC ",
LoginInfo.UserID, getData.pageNum, getData.pageCount);
var retData = await context.Database.SqlQueryAsync<SYS_NEWS>(sql);
return retData;
}
/// <summary>
/// 新闻列表显示方法
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public async Task<List<EnAnnou>> Notice(GetNotice SData)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
#region 自定义变量AND传参
List<EnAnnou> retData = new List<EnAnnou>();
DataTable dtResult = null;
string SQL = string.Empty;
#endregion
switch (SData.basicType.ToInt())
{
case 1:
SQL = $@"SELECT TOP 3
FIMAGE,
FNAME,
C_YMD,
UID
FROM SYS_ENANNOU
WHERE FSTATUS=40 AND FIMAGE IS NOT NULL
ORDER BY FCHECKDATE DESC";
dtResult = context.Database.SqlQuery(SQL);
foreach (DataRow dr in dtResult.Rows)
{
retData.Add(new EnAnnou
{
FName = dr["FNAME"].ToStringEx(),
FImageBase64=Convert.ToBase64String((byte[])dr["FIMAGE"]),
C_YMD=dr["C_YMD"].ToStringEx(),
UID=dr["UID"].ToStringEx()
}) ;
}
break;
case 2:
int pageSize = SData.pageSize.ToInt();
int pageIndex = SData.pageIndex.ToInt();
SQL = $@"SELECT
S.*
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY FCHECKDATE DESC) AS RowNumber,
FIMAGE,
FNAME,
C_YMD,
UID
FROM SYS_ENANNOU
WHERE FSTATUS=40
)S
WHERE S.RowNumber BETWEEN ({pageSize}-1)*({pageIndex}+1) AND ({pageSize}*{pageIndex})
ORDER BY S.RowNumber";
dtResult = context.Database.SqlQuery(SQL);
foreach (DataRow dr in dtResult.Rows)
{
retData.Add(new EnAnnou
{
FName = dr["FNAME"].ToStringEx(),
FImageBase64 = Convert.ToBase64String((byte[])(dr["FIMAGE"])),
C_YMD = dr["C_YMD"].ToStringEx(),
UID = dr["UID"].ToStringEx()
});
}
break;
case 3:
SQL = $@"SELECT
FIMAGE,
FNAME,
FCONTENT,
C_YMD,
UID
FROM SYS_ENANNOU
WHERE FSTATUS=40 AND UID='{SData.UID.ToStringEx()}'";
dtResult = context.Database.SqlQuery(SQL);
if (dtResult.Rows.Count > 0)
{
foreach (DataRow dr in dtResult.Rows)
{
retData.Add(new EnAnnou
{
FName = dr["FNAME"].ToStringEx(),
FContent = dr["FCONTENT"].ToStringEx(),
FImageBase64 = Convert.ToBase64String((byte[])(dr["FIMAGE"])),
C_YMD = dr["C_YMD"].ToStringEx(),
UID = dr["UID"].ToStringEx()
});
}
}
else
{
throw new O3AppException("该新闻公告不存在!!");
}
break;
}
return await Task.Run(() => retData);
}
}
}