tede/gx_api/GxPress/Service/GxPress.Service.Implement/CommonSqlKata/CommonSqlKataService.cs

using GxPress.EnumConst;
using GxPress.Service.Interface.CommonSqlKata;
namespace GxPress.Service.Implement.CommonSqlKataService
{
    public class CommonSqlKataService : ICommonSqlKataService
    {
        /// <summary>
        /// 通用
        /// </summary>
        /// <param name="noticeTypeValue"></param>
        /// <param name="userId"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public SqlKata.Query GetCommonQueryAsync(int noticeTypeValue, int userId, SqlKata.Query query, string whereColumnSql)
        {
            var PraiseCount = new SqlKata.Query("tede_analyze");
            PraiseCount.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            PraiseCount.Where("tede_analyze.TypeValue", noticeTypeValue);
            PraiseCount.Where("tede_analyze.AnalyzeType", 1);
            PraiseCount.AsCount();
            query.Select(PraiseCount, "PraiseCount");
            var IsLaud = new SqlKata.Query("tede_analyze");
            IsLaud.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            IsLaud.Where("tede_analyze.TypeValue", noticeTypeValue);
            IsLaud.Where("tede_analyze.UserId", userId);
            IsLaud.Where("tede_analyze.AnalyzeType", 1);
            IsLaud.Limit(1);
            IsLaud.AsCount();
            query.Select(IsLaud, "IsLaud");
            var CommentCount = new SqlKata.Query("tede_comment");
            CommentCount.WhereColumns("tede_comment.ArticleId", "=", whereColumnSql);
            CommentCount.Where("tede_comment.pid", 0);
            CommentCount.Where("tede_comment.TypeValue", noticeTypeValue);
            CommentCount.AsCount();
            query.Select(CommentCount, "CommentCount");
            var RetransmissionCount = new SqlKata.Query("tede_analyze");
            RetransmissionCount.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            RetransmissionCount.Where("tede_analyze.TypeValue", noticeTypeValue);
            RetransmissionCount.Where("tede_analyze.UserId", userId);
            RetransmissionCount.Where("tede_analyze.AnalyzeType", 4);
            RetransmissionCount.AsCount();
            query.Select(RetransmissionCount, "RetransmissionCount");
            var IsRetransmission = new SqlKata.Query("tede_analyze");
            IsRetransmission.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            IsRetransmission.Where("tede_analyze.TypeValue", noticeTypeValue);
            IsRetransmission.Where("tede_analyze.UserId", userId);
            IsRetransmission.Where("tede_analyze.AnalyzeType", 4);
            IsRetransmission.Limit(1);
            IsRetransmission.AsCount();
            query.Select(IsRetransmission, "IsRetransmission");
            var IsCollect = new SqlKata.Query("tede_analyze");
            IsCollect.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            IsCollect.Where("tede_analyze.TypeValue", noticeTypeValue);
            IsCollect.Where("tede_analyze.UserId", userId);
            IsCollect.Where("tede_analyze.AnalyzeType", 3);
            IsCollect.Limit(1);
            IsCollect.AsCount();
            query.Select(IsCollect, "IsCollect");
            var CollectCount = new SqlKata.Query("tede_analyze");
            CollectCount.WhereColumns("tede_analyze.SourceId", "=", whereColumnSql);
            CollectCount.Where("tede_analyze.TypeValue", noticeTypeValue);
            CollectCount.Where("tede_analyze.UserId", userId);
            CollectCount.Where("tede_analyze.AnalyzeType", 3);
            CollectCount.AsCount();
            query.Select(CollectCount, "CollectCount"); 
            return query;
        }
        /// <summary>
        /// 收件箱
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public SqlKata.Query NoticeQueryAsync(SqlKata.Query query, int userId)
        {
            var enjoyUser = new SqlKata.Query("tede_user_middle");
            enjoyUser.Where("MiddleType", 10);
            enjoyUser.WhereColumns("DataSourceId", "=", "tede_middle.MiddleId");
            enjoyUser.SelectRaw("GROUP_CONCAT(SourceName Separator'、')");
            query.Select(enjoyUser, "EnjoyUser");
            var flowUserName = new SqlKata.Query("tede_user");
            flowUserName.SelectRaw("GROUP_CONCAT(Name Separator'、')");
            var flowTodo = new SqlKata.Query("tede_flow_todo");
            flowTodo.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            flowTodo.Where("tede_flow_todo.type", "ApproverCheck");
            flowTodo.Select("tede_flow_todo.UserId");
            flowUserName.WhereIn("tede_user.Id", flowTodo);
            query.Select(flowUserName, "FlowUserName");
            var uCheckedCount = new SqlKata.Query("tede_flow_todo");
            uCheckedCount.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            uCheckedCount.Where("tede_flow_todo.type", "ApproverCheck");
            uCheckedCount.AsCount();
            query.Select(uCheckedCount, "UCheckedCount");
            var checkedCount = new SqlKata.Query("tede_flow_todo");
            checkedCount.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            checkedCount.Where("tede_flow_todo.type", "ApproverCheck");
            checkedCount.Where("tede_flow_todo.IsRead", true);
            checkedCount.AsCount();
            query.Select(checkedCount, "CheckedCount");
            var isFlowRead = new SqlKata.Query("tede_flow_todo");
            isFlowRead.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            isFlowRead.WhereColumns("tede_flow_todo.UserId", "=", "tede_middle.UserId");
            isFlowRead.Where("tede_flow_todo.type", "CarbonCopy");
            isFlowRead.Limit(1);
            isFlowRead.Select("IsRead");
            query.Select(isFlowRead, "IsFlowRead");
            var isFlowChecked = new SqlKata.Query("tede_flow_todo");
            isFlowChecked.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            isFlowChecked.Where("tede_flow_todo.type", "ApproverCheck");
            isFlowChecked.Where("tede_flow_todo.IsRead", true);
            isFlowChecked.Limit(1);
            isFlowChecked.Select("IsRead");
            query.Select(isFlowChecked, "IsFlowChecked");
            var IsMyFlowRead = new SqlKata.Query("tede_flow");
            IsMyFlowRead.WhereColumns("tede_flow.Id", "=", "tede_notice.SourceId");
            IsMyFlowRead.WhereColumns("tede_flow.UserId", "=", "tede_notice.UserId");
            IsMyFlowRead.Select("IsRead");
            query.Select(IsMyFlowRead, "IsMyFlowRead");
            var IsDone = new SqlKata.Query("tede_flow_todo");
            IsDone.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            IsDone.WhereColumns("tede_flow_todo.UserId", "=", "tede_middle.UserId");
            IsDone.Where("tede_flow_todo.type", "ApproverCheck");
            IsDone.Limit(1);
            IsDone.Select("IsDone");
            query.Select(IsDone, "IsDone");
            var IsChecked = new SqlKata.Query("tede_flow_todo");
            IsChecked.WhereColumns("tede_flow_todo.UserId", "=", "tede_middle.UserId");
            IsChecked.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            IsChecked.Where("tede_flow_todo.type", "ApproverCheck");
            IsChecked.Limit(1);
            IsChecked.Select("IsChecked");
            query.Select(IsChecked, "IsChecked");
            var DoneType = new SqlKata.Query("tede_flow_todo");
            DoneType.WhereColumns("tede_flow_todo.UserId", "=", "tede_middle.UserId");
            DoneType.WhereColumns("tede_flow_todo.FlowId", "=", "tede_notice.SourceId");
            DoneType.Where("tede_flow_todo.type", "ApproverCheck");
            DoneType.Limit(1);
            DoneType.Select("DoneType");
            query.Select(DoneType, "DoneType");
            var userName = new SqlKata.Query("tede_user");
            var tedeFlow = new SqlKata.Query("tede_flow");
            tedeFlow.WhereColumns("tede_flow.Id", "=", "tede_notice.SourceId");
            tedeFlow.Select("tede_flow.UserId");
            userName.WhereIn("tede_user.Id", tedeFlow);
            userName.Limit(1);
            userName.Select("Name");
            query.Select(userName, "Name");
            var IsFlowAdmin = new SqlKata.Query("tede_flow");
            IsFlowAdmin.WhereColumns("tede_flow.Id", "=", "tede_notice.SourceId");
            IsFlowAdmin.Where("tede_flow.UserId", userId);
            IsFlowAdmin.AsCount();
            query.Select(IsFlowAdmin, "IsFlowAdmin");
            var FileCount = new SqlKata.Query().From("tede_middle as a");
            FileCount.WhereColumns("a.ParentId", "=", "tede_middle.Id");
            FileCount.Where("a.IsDelete", false);
            FileCount.WhereNot("a.NoticeAddresseeType", NoticeTypeConst.Draft.GetHashCode()); ;
            FileCount.AsCount();
            query.Select(FileCount, "FileCount");
            var ReadCount = new SqlKata.Query("tede_addressee");
            ReadCount.WhereColumns("tede_addressee.SourceId", "=", "tede_middle.MiddleId");
            ReadCount.Where("tede_addressee.IsRead", true);
            ReadCount.AsCount();
            query.Select(ReadCount, "ReadCount");
            var AllCount = new SqlKata.Query("tede_addressee");
            AllCount.WhereColumns("tede_addressee.SourceId", "=", "tede_middle.MiddleId");
            AllCount.AsCount();
            query.Select(AllCount, "AllCount");
            var IsRead = new SqlKata.Query("tede_addressee");
            IsRead.WhereColumns("tede_addressee.SourceId", "=", "tede_middle.MiddleId");
            IsRead.Where("tede_addressee.IsRead", true);
            IsRead.Where("tede_addressee.UserId", userId);
            IsRead.Limit(1);
            IsRead.Select("IsRead");
            query.Select(IsRead, "IsRead");
            return query;
        }
    }
}