/// <summary>
/// 目前只取面向全部人员类型
/// TODO 后期添加其他类型
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public async Task <Page <OaMessageMyList> > MyListAsync(OaMessageMyListSearch search)
{
Page <OaMessageMyList> page = new Page <OaMessageMyList>()
{
PageIndex = search.PageIndex,
PageSize = search.PageSize
};
string sqlwhere = " ";
if (search.IsRead == 1)
{
sqlwhere += "AND om.IsRead=1 ";
}
else if (search.IsRead == 0)
{
sqlwhere += "AND om.IsRead=0 ";
}
string sql = $@"SELECT * FROM ( SELECT om.`Id`,om.`MsgType`,om.`TargetType`,om.`Title`,om.`IsLocal`,om.`Link`,om.`CreateTime`,(omur.`Id` > 0) AS IsRead FROM oa_message om
LEFT JOIN oa_message_user_read omur ON omur.`MessageId`=om.`Id` AND omur.`UserId`= @userid
WHERE om.`IsEnable`=1 AND om.`IsDel`=0 AND om.`FaceUserType`=0 ) om WHERE 1=1 {sqlwhere}
ORDER BY om.`CreateTime` DESC LIMIT @offset,@pageSize";
page.Items = await this.Connection.QueryAsync <OaMessageMyList>(sql, new { userid = search.UserId, offset = search.OffSet(), pageSize = search.PageSize });
page.TotalItems = await this.ExecuteScalarAsync <int>($@"SELECT COUNT(1) FROM
( SELECT om.`Id`,om.`MsgType`,om.`TargetType`,om.`Title`,om.`IsLocal`,om.`Link`,om.`CreateTime`,(omur.`Id` > 0) AS IsRead FROM oa_message om
LEFT JOIN oa_message_user_read omur ON omur.`MessageId`=om.`Id` AND omur.`UserId`= @userid
WHERE om.`IsEnable`=1 AND om.`IsDel`=0 AND om.`FaceUserType`=0 ) om WHERE 1=1 {sqlwhere}", new { userid = search.UserId });
return(page);
}
/// <summary>
/// 目前只取面向全部人员类型
/// TODO 后期添加其他类型
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public async Task <Page <OaMessageMyList> > MyListAsync(OaMessageMyListSearch search)
{
Page <OaMessageMyList> page = new Page <OaMessageMyList>()
{
PageIndex = search.PageIndex,
PageSize = search.PageSize
};
string sqlwhere = " ";
if (search.IsRead == 1)
{
sqlwhere += "AND omu.IsRead=1 ";
}
else if (search.IsRead == 0)
{
sqlwhere += "AND omu.IsRead=0 ";
}
string sql = $@"SELECT om.`Id`,om.`MsgType`,om.`TargetType`,om.`Title`,om.`IsLocal`,om.`Link`,om.`CreateTime`,omu.IsRead FROM oa_message_user omu
INNER JOIN oa_message om ON om.Id=omu.MessageId
WHERE omu.Id in(
SELECT MAX(omu2.Id) Id FROM oa_message_user omu2 WHERE omu2.UserId=@userid GROUP BY omu2.MessageId
)
{sqlwhere}
ORDER BY om.CreateTime DESC
LIMIT @offset,@pageSize
";
page.Items = await this.Connection.QueryAsync <OaMessageMyList>(sql, new { userid = search.UserId, offset = search.OffSet(), pageSize = search.PageSize });
page.TotalItems = await this.ExecuteScalarAsync <int>($@"SELECT COUNT(omu.Id) FROM oa_message_user omu
INNER JOIN oa_message om ON om.Id=omu.MessageId
WHERE omu.Id in(
SELECT MAX(omu2.Id) Id FROM oa_message_user omu2 WHERE omu2.UserId=@userid GROUP BY omu2.MessageId
)
{sqlwhere}", new { userid = search.UserId });
return(page);
}