tede/gx_api/GxPress/Service/GxPress.Service.Implement/Reply/ReplyService.Praise.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Dapper;
using Datory;
using GxPress.Common.Tools;
using GxPress.EnumConst;
using GxPress.Request.Reply;
using GxPress.Result.Reply;
using System.Transactions;
namespace GxPress.Service.Implement.Reply
{
    /// <summary>
    /// 点赞
    /// </summary>
    public partial class ReplyService
    {
        /// <summary>
        /// 获取话题本赞
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<ReplyResult>> GetNotePraiseAsync(ReplyRequest request)
        {
            var topicNoteConstValue = AllTypeConst.TopicNote.GetHashCode();
            string sqlStr = string.Empty;
            if (!string.IsNullOrEmpty(request.KeyWord))
            {
                sqlStr = $@" AND (
                    b.Title LIKE '%{request.KeyWord}%'
                    OR b.Content LIKE '%{request.KeyWord}%' or c.Name like '%{request.KeyWord}%')";
            }
            string sql = $@"
                                SELECT 
                                        a.Id,
                                        a.SourceId,
                                        a.TypeValue,
                                        a.IsRead,
                                        b.Title,
                                        b.Content,
                                        c.Name as UserName,
                                        c.AvatarUrl,
                                        b.CreatedDate,
                                        a.UserId,
                                        b.IsTopic,
                                        d.Name
                                    FROM
                                        tede_analyze a
                                            INNER JOIN
                                        tede_note b ON a.SourceId = b.Id
                                            INNER JOIN
                                        tede_user c ON c.Id = a.UserId
                                            INNER JOIN
                                        tede_user d ON d.Id = b.UserId
                                            INNER JOIN
                                        tede_middle e ON e.MiddleId = b.Id
                                    WHERE
                                        a.TypeValue ={topicNoteConstValue} AND e.IsDelete = 0
                                            AND  e.FolderType ={topicNoteConstValue}
                                            AND a.CommentId = 0
                                            AND e.UserId = b.UserId
                                            AND a.AnalyzeType = 1 
                                            AND b.UserId ={request.UserId}
                                            And b.IsTopic=1
                                            {sqlStr}
                                    ORDER BY a.CreatedDate DESC
                                            ";
            var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<ReplyResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.CommentContent = "点赞了你的" + ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Title = string.IsNullOrEmpty(item.Title) ? GetTitleText(item.Content) : item.Title;
                item.Remark = ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Content = string.Empty;
            }
            return result;
        }
        /// <summary>
        /// 获取评论点赞
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<ReplyResult>> GetCommentPraiseAsync(ReplyRequest request)
        {
            var sqlStr = string.Empty;
            if (!string.IsNullOrEmpty(request.KeyWord))
            {
                sqlStr = $@" AND (a.Content LIKE '%{request.KeyWord}%'
                                OR c.Name LIKE '%{request.KeyWord}%')";
            }
            string sql = $@"
                        SELECT 
                            a.Id,
                            a.Content,
                            a.TypeValue,
                            b.IsRead,
                            b.UserId,
                            c.Name AS UserName,
                            c.AvatarUrl,
                            c.Id AS UserId,
                            d.Name,
                            b.CreatedDate
                        FROM
                            tede_comment a
                                INNER JOIN
                            tede_analyze b ON a.Id = b.CommentId
                                INNER JOIN
                            tede_user c ON c.Id = b.UserId
                                INNER JOIN
                            tede_user d ON d.Id = a.UserId
                        WHERE
                            a.TypeValue IN ({AllTypeConst.Topic.GetHashCode()},{AllTypeConst.Notice.GetHashCode()},{AllTypeConst.Note.GetHashCode()})
                                AND a.UserId = {request.UserId}
                                {sqlStr}
                                AND b.AnalyzeType = 2
                        ORDER BY a.CreatedDate DESC";
            var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<ReplyResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.CommentContent = "点赞了你的" + ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Title = item.Content;
                item.IsComment = true;
                item.Remark = ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
            }
            return result;
        }
        /// <summary>
        /// 获取通知点赞
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<ReplyResult>> GetNoticePraiseAsync(ReplyRequest request)
        {
            var sqlStr = string.Empty;
            if (!string.IsNullOrEmpty(request.KeyWord))
            {
                sqlStr = $@" AND (b.Title LIKE '%{request.KeyWord}%'
                    OR b.Content LIKE '%{request.KeyWord}%'
                                OR c.Name LIKE '%{request.KeyWord}%')";
            }
            string sql = $@"
                          SELECT 
                                    a.Id,
                                    a.SourceId,
                                    a.TypeValue,
                                    a.IsRead,
                                    b.Title,
                                    b.Content,
                                    c.Name,
                                    c.AvatarUrl,
                                    b.CreatedDate,
                                    a.UserId,
                                    d.Name AS UserName
                                FROM
                                    tede_analyze a
                                        INNER JOIN
                                    tede_notice b ON a.SourceId = b.Id
                                        INNER JOIN
                                    tede_user c ON c.Id = a.UserId
                                        INNER JOIN
                                    tede_user d ON d.Id = b.UserId
                                        INNER JOIN
                                    tede_middle e ON e.MiddleId = b.Id
                                WHERE
                                    b.UserId = {request.UserId} AND e.UserId = b.UserId
                                        AND a.TypeValue ={AllTypeConst.Notice.GetHashCode()}
                                        AND e.IsDelete = 0
                                        AND a.CommentId = 0
                                        AND a.AnalyzeType = 1 
                                        {sqlStr}
                        ORDER BY a.CreatedDate DESC";
            var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<ReplyResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.CommentContent = "点赞了你的" + ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Title = string.IsNullOrEmpty(item.Title) ? GetTitleText(item.Content) : item.Title;
                item.Remark = ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Content = string.Empty;
            }
            return result;
        }
        /// <summary>
        /// 获取话题点赞
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<ReplyResult>> GetTopicPraiseAsync(ReplyRequest request)
        {
            var sqlStr = string.Empty;
            if (!string.IsNullOrEmpty(request.KeyWord))
            {
                sqlStr = $@" AND ( b.Title LIKE '%{request.KeyWord}%'
                    OR b.Content LIKE '%{request.KeyWord}%'
                                OR c.Name LIKE '%{request.KeyWord}%')";
            }
            string sql = $@"
                          SELECT 
                                    a.Id,
                                    a.SourceId,
                                    a.TypeValue,
                                    a.IsRead,
                                    b.Title,
                                    b.Content,
                                    c.Name as UserName,
                                    c.AvatarUrl,
                                    b.CreatedDate,
                                    a.UserId,
                                    d.Name,
                                    g.Id as GroupId,
                                    g.Name as GroupName
                                FROM
                                    tede_analyze a
                                        INNER JOIN
                                    tede_topic b ON a.SourceId = b.Id
                                        INNER JOIN
                                    tede_user c ON c.Id = a.UserId
                                        INNER JOIN
                                    tede_user d ON d.Id = b.UserId
                                        INNER JOIN
                                    tede_middle e ON e.MiddleId = b.Id
                                        INNER JOIN
                                    tede_group g ON g.Id = b.GroupId
                                WHERE
                                    a.TypeValue ={AllTypeConst.Topic.GetHashCode()} AND e.IsDelete = 0
                                        AND a.CommentId = 0
                                        AND e.UserId = b.UserId
                                        AND a.AnalyzeType = 1
                                        AND b.UserId = {request.UserId}
                                        {sqlStr}
                        ORDER BY a.CreatedDate DESC";
            var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var result = await connection.QueryAsync<ReplyResult>(sql);
            foreach (var item in result)
            {
                item.AvatarUrl = StringUtils.AddDomainMin(item.AvatarUrl);
                item.CommentContent = "点赞了你的" + ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.Title = string.IsNullOrEmpty(item.Title) ? GetTitleText(item.Content) : item.Title;
                item.Remark = ((AllTypeConst)item.TypeValue).GetDescriptionOriginal();
                item.IsGroup = true;
                item.Content = string.Empty;
            }
            return result;
        }
        /// <summary>
        /// 未读点赞数量
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<int> GetUReadPraiseCountAsync(int userId)
        {
            var sql = $@" SELECT 
                        COUNT(1)
                    FROM
                        tede_analyze a
                            INNER JOIN
                        tede_note b ON a.SourceId = b.Id
                            INNER JOIN
                        tede_user c ON c.Id = a.UserId
                            INNER JOIN
                        tede_user d ON d.Id = b.UserId
                            INNER JOIN
                        tede_middle e ON e.MiddleId = b.Id
                    WHERE
                        a.TypeValue = 4 AND e.IsDelete = 0
                            AND a.CommentId = 0
                            AND e.UserId = b.UserId
                            AND a.AnalyzeType = 1
                            AND b.UserId = {userId}
                            AND a.IsRead = 0";
            var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var count = await connection.ExecuteScalarAsync<int>(sql);
            sql = $@" SELECT 
                        count(1)
                        FROM
                            tede_comment a
                                INNER JOIN
                            tede_analyze b ON a.Id = b.CommentId
                                INNER JOIN
                            tede_user c ON c.Id = b.UserId
                                INNER JOIN
                            tede_user d ON d.Id = a.UserId
                        WHERE
                            a.TypeValue IN ({AllTypeConst.Topic.GetHashCode()},{AllTypeConst.Notice.GetHashCode()},{AllTypeConst.Note.GetHashCode()})
                                AND a.UserId ={userId}
                                AND b.AnalyzeType = 2
                                AND b.IsRead = 0";
            count += await connection.ExecuteScalarAsync<int>(sql);
            sql = $@"  SELECT 
                            COUNT(1)
                        FROM
                            tede_analyze a
                                INNER JOIN
                            tede_notice b ON a.SourceId = b.Id
                                INNER JOIN
                            tede_user c ON c.Id = a.UserId
                                INNER JOIN
                            tede_user d ON d.Id = b.UserId
                                INNER JOIN
                            tede_middle e ON e.MiddleId = b.Id
                        WHERE
                            b.UserId ={userId} AND e.UserId = b.UserId
                                AND a.TypeValue ={AllTypeConst.Notice.GetHashCode()}
                                AND e.IsDelete = 0
                                AND a.CommentId = 0
                                AND a.AnalyzeType = 1";
            count += await connection.ExecuteScalarAsync<int>(sql);
            sql = $@"  SELECT 
                                count(1)
                                FROM
                                    tede_analyze a
                                        INNER JOIN
                                    tede_topic b ON a.SourceId = b.Id
                                        INNER JOIN
                                    tede_user c ON c.Id = a.UserId
                                        INNER JOIN
                                    tede_user d ON d.Id = b.UserId
                                        INNER JOIN
                                    tede_middle e ON e.MiddleId = b.Id
                                        INNER JOIN
                                    tede_group g ON g.Id = b.GroupId
                                WHERE
                                    a.TypeValue = {AllTypeConst.Topic.GetHashCode()} AND e.IsDelete = 0
                                        AND a.CommentId = 0
                                        AND e.UserId = b.UserId
                                        AND a.AnalyzeType = 1
                                        AND b.UserId ={userId}";
            count += await connection.ExecuteScalarAsync<int>(sql);
            return count;
        }

        /// <summary>
        /// 修改未读点赞数量
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<bool> UpdateUReadPraiseAsync(int userId)
        {
            try
            {


                using (var tran = new TransactionScope())
                {
                    var sql = $@"SELECT 
                       a.Id
                    FROM
                        tede_analyze a
                            INNER JOIN
                        tede_note b ON a.SourceId = b.Id
                            INNER JOIN
                        tede_user c ON c.Id = a.UserId
                            INNER JOIN
                        tede_user d ON d.Id = b.UserId
                            INNER JOIN
                        tede_middle e ON e.MiddleId = b.Id
                    WHERE
                        a.TypeValue = 4 AND e.IsDelete = 0
                            AND a.CommentId = 0
                            AND e.UserId = b.UserId
                            AND a.AnalyzeType = 1
                            AND b.UserId = {userId}
                            AND a.IsRead = 0";
                    var database = new Database(DatabaseType.MySql, ConfigHelper.GetValue("Database:ConnectionString"));
                    var connection = database.GetConnection();
                    var intList = await connection.QueryAsync<int>(sql);
                    var sqlAnalyzeStr = string.Empty;
                    foreach (var item in intList)
                        sqlAnalyzeStr += $"{item},";
                    sqlAnalyzeStr = !string.IsNullOrEmpty(sqlAnalyzeStr) ? sqlAnalyzeStr.Remove(sqlAnalyzeStr.Length - 1, 1) : string.Empty;
                    if (!string.IsNullOrEmpty(sqlAnalyzeStr))
                    {
                        sql = $@"UPDATE tede_analyze 
                            SET 
                                IsRead = 1
                                    WHERE Id IN ({sqlAnalyzeStr}) and id>0";
                        await connection.ExecuteScalarAsync<int>(sql);
                    }
                    sql = $@"
                    SELECT 
                        b.Id
                        FROM
                            tede_comment a
                                INNER JOIN
                            tede_analyze b ON a.Id = b.CommentId
                                INNER JOIN
                            tede_user c ON c.Id = b.UserId
                                INNER JOIN
                            tede_user d ON d.Id = a.UserId
                        WHERE
                            a.TypeValue IN ({AllTypeConst.Topic.GetHashCode()},{AllTypeConst.Notice.GetHashCode()},{AllTypeConst.Note.GetHashCode()})
                                AND a.UserId ={userId}
                                AND b.AnalyzeType = 2
                                AND b.IsRead = 0";
                    intList = await connection.QueryAsync<int>(sql);
                    sqlAnalyzeStr = string.Empty;
                    foreach (var item in intList)
                        sqlAnalyzeStr += $"{item},";
                    sqlAnalyzeStr = !string.IsNullOrEmpty(sqlAnalyzeStr) ? sqlAnalyzeStr.Remove(sqlAnalyzeStr.Length - 1, 1) : string.Empty;
                    if (!string.IsNullOrEmpty(sqlAnalyzeStr))
                    {

                        sql = $@"  UPDATE tede_analyze 
                            SET 
                                IsRead = 1 
                                    WHERE Id IN ({sqlAnalyzeStr}) and id>0";
                        await connection.ExecuteScalarAsync<int>(sql);
                    }
                    sql = $@"
                    SELECT 
                            a.Id
                        FROM
                            tede_analyze a
                                INNER JOIN
                            tede_notice b ON a.SourceId = b.Id
                                INNER JOIN
                            tede_user c ON c.Id = a.UserId
                                INNER JOIN
                            tede_user d ON d.Id = b.UserId
                                INNER JOIN
                            tede_middle e ON e.MiddleId = b.Id
                        WHERE
                            b.UserId ={userId} AND e.UserId = b.UserId
                                AND a.TypeValue ={AllTypeConst.Notice.GetHashCode()}
                                AND e.IsDelete = 0
                                AND a.CommentId = 0
                                AND a.AnalyzeType = 1";
                    intList = await connection.QueryAsync<int>(sql);
                    sqlAnalyzeStr = string.Empty;
                    foreach (var item in intList)
                        sqlAnalyzeStr += $"{item},";
                    sqlAnalyzeStr = !string.IsNullOrEmpty(sqlAnalyzeStr) ? sqlAnalyzeStr.Remove(sqlAnalyzeStr.Length - 1, 1) : string.Empty;
                    if (!string.IsNullOrEmpty(sqlAnalyzeStr))
                    {
                        sql = $@" 
             UPDATE tede_analyze 
                            SET 
                                IsRead = 1
                                    WHERE Id IN ({sqlAnalyzeStr}) and id>0";
                        await connection.ExecuteScalarAsync<int>(sql);
                    }

                    sql = $@"
                           SELECT 
                                a.Id
                                FROM
                                    tede_analyze a
                                        INNER JOIN
                                    tede_topic b ON a.SourceId = b.Id
                                        INNER JOIN
                                    tede_user c ON c.Id = a.UserId
                                        INNER JOIN
                                    tede_user d ON d.Id = b.UserId
                                        INNER JOIN
                                    tede_middle e ON e.MiddleId = b.Id
                                        INNER JOIN
                                    tede_group g ON g.Id = b.GroupId
                                WHERE
                                    a.TypeValue = {AllTypeConst.Topic.GetHashCode()} AND e.IsDelete = 0
                                        AND a.CommentId = 0
                                        AND e.UserId = b.UserId
                                        AND a.AnalyzeType = 1
                                        AND b.UserId ={userId}";
                    intList = await connection.QueryAsync<int>(sql);
                    sqlAnalyzeStr = string.Empty;
                    foreach (var item in intList)
                        sqlAnalyzeStr += $"{item},";
                    sqlAnalyzeStr = !string.IsNullOrEmpty(sqlAnalyzeStr) ? sqlAnalyzeStr.Remove(sqlAnalyzeStr.Length - 1, 1) : string.Empty;
                    if (!string.IsNullOrEmpty(sqlAnalyzeStr))
                    {
                        sql = $@" 
              UPDATE tede_analyze 
                            SET 
                                IsRead = 1
                                    WHERE Id IN ({sqlAnalyzeStr}) and id>0";
                        await connection.ExecuteScalarAsync<int>(sql);
                    }
                    tran.Complete();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}