gxPress/guangxi-project/GxPress/Repository/GxPress.Repository.Implement/WorkFlow/FlowRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using GxPress.Common.AppOptions;
using GxPress.Common.Tools;
using GxPress.Entity.WorkFlow;
using GxPress.Repository.Interface;
using GxPress.Repository.Interface.WorkFlow;
using GxPress.Repository.Interface.WorkProcess;
using GxPress.Request.App.Flow;
using Microsoft.Extensions.Options;
using Datory;
using GxPress.EnumConst;
using Dapper;
using GxPress.Common.Page;

namespace GxPress.Repository.Implement.WorkFlow
{
    public class FlowRepository : IFlowRepository
    {
        private readonly Repository<Flow> _repository;
        private readonly IMapper _mapper;
        private readonly IFlowFieldValueRepository _flowFieldValueRepository;
        private readonly IFlowTodoRepository _flowTodoRepository;
        private readonly IProcessRepository _processRepository;
        private readonly IProcessFieldRepository _processFieldRepository;
        private readonly IProcessGroupRepository _processGroupRepository;
        private readonly IProcessNodeRepository _processNodeRepository;
        private readonly IProcessRequestLimitRepository _processRequestLimitRepository;
        private readonly IRuleConditionRepository _conditionRuleRepository;
        private readonly IRuleCarbonCopyRepository _ruleCarbonCopyRepository;
        private readonly IRuleApproverCheckRepository _ruleApproverCheckRepository;
        private readonly IDepartmentRepository _departmentRepository;
        private readonly IRoleRepository _roleRepository;
        private readonly IUserRepository _userRepository;
        private readonly string _connectionString;
        private readonly string _databaseTypeStr;
        public FlowRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IMapper mapper,
            IFlowFieldValueRepository flowFieldValueRepository,
            IFlowTodoRepository flowTodoRepository,
            IProcessRepository processRepository,
            IProcessFieldRepository processFieldRepository,
            IProcessGroupRepository processGroupRepository,
            IProcessRequestLimitRepository processRequestLimitRepository,
            IProcessNodeRepository processNodeRepository,
            IRuleConditionRepository conditionRuleRepository,
            IRuleCarbonCopyRepository ruleCarbonCopyRepository,
            IRuleApproverCheckRepository ruleApproverCheckRepository,
            IDepartmentRepository departmentRepository,
            IRoleRepository roleRepository,
            IUserRepository userRepository)
        {
            _databaseTypeStr = dbOptionsAccessor.CurrentValue.DatabaseType;
            _connectionString = dbOptionsAccessor.CurrentValue.ConnectionString;
            var databaseType = StringUtils.ToEnum<DatabaseType>(dbOptionsAccessor.CurrentValue.DatabaseType, DatabaseType.MySql);
            var database = new Database(databaseType, dbOptionsAccessor.CurrentValue.ConnectionString);
            _repository = new Repository<Flow>(database);
            _mapper = mapper;
            _flowFieldValueRepository = flowFieldValueRepository;
            _flowTodoRepository = flowTodoRepository;
            _processRepository = processRepository;
            _processFieldRepository = processFieldRepository;
            _processGroupRepository = processGroupRepository;
            _processRequestLimitRepository = processRequestLimitRepository;
            _processNodeRepository = processNodeRepository;
            _conditionRuleRepository = conditionRuleRepository;
            _ruleCarbonCopyRepository = ruleCarbonCopyRepository;
            _ruleApproverCheckRepository = ruleApproverCheckRepository;
            _departmentRepository = departmentRepository;
            _roleRepository = roleRepository;
            _userRepository = userRepository;
        }

        public IDatabase Database => _repository.Database;
        public string TableName => _repository.TableName;
        public List<TableColumn> TableColumns => _repository.TableColumns;

        public async Task<IEnumerable<Flow>> GetListAsync(int processId)
        {
            return await _repository.GetAllAsync(Q
                .Where(nameof(Flow.ProcessId), processId)
            );
        }

        public async Task<int> InsertAsync(Flow flow)
        {
            var id = await _repository.InsertAsync(flow);
            var no = StringUtils.GetFlowNo(DateTime.Now, id);
            await _repository.UpdateAsync(Q
                .Set(nameof(Flow.No), no)
                .Where(nameof(Flow.Id), id)
            );
            return id;
        }

        public async Task<Flow> GetAsync(int flowId)
        {
            return await _repository.GetAsync(flowId);
        }

        public async Task<int> GetCountByUserIdAsync(int userId, bool checking, FlowListRequest request)
        {
            string sql = $@"SELECT 
                                count(1)
                            FROM
                                ccpph.tede_flow a
                            WHERE 1=1 ";
            if (!checking)
                sql += $@" and a.State IN ('Denied','Checked')";
            else
                sql += $@" and a.State Not IN ('Denied','Checked')";
            sql += $@" AND a.UserId = {userId}
                                    AND a.ProcessId IN (SELECT 
                                        id
                                    FROM
                                        ccpph.tede_process)";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (a.ProcessId IN (SELECT 
                                        Id
                                    FROM
                                        tede_process
                                    WHERE
                                        name LIKE '%{request.Keyword}%')
                                    OR a.Id IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%') 
                                        OR a.UserId IN (SELECT 
                                        Id
                                    FROM
                                        ccpph.tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') or a.Id In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%')) 
                                                                        or a.Id In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processIdValue = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processIdValue += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processIdValue))
                {
                    processIdValue = processIdValue.Remove(processIdValue.Length - 1, 1);
                    sql += $" and a.ProcessId in({processIdValue})";
                }
            }
            sql += "ORDER BY a.CreatedDate DESC";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            return await connection.ExecuteScalarAsync<int>(sql);
        }
        /// <summary>
        /// 新增 未读数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="checking"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<int> GetUReadCountByUserIdAsync(int userId, bool checking, FlowListRequest request)
        {
            var query = Q
                .Where(nameof(Flow.UserId), userId)
                .WhereNot(nameof(Flow.ProcessId), 0);

            if (checking)
            {
                query.WhereNotIn(nameof(Flow.State), new List<string>
                {
                    //FlowState.Meeting.GetValue(),
                    FlowState.Checked.GetValue(),
                    FlowState.Denied.GetValue()
                });
            }
            else
            {
                query.WhereIn(nameof(Flow.State), new List<string>
                {
                    //FlowState.Meeting.GetValue(),
                    FlowState.Checked.GetValue(),
                    FlowState.Denied.GetValue()
                });
            }

            if (request.ProcessIds != null && request.ProcessIds.Any())
            {
                query.WhereIn(nameof(Flow.ProcessId), request.ProcessIds);
            }

            // if (!string.IsNullOrEmpty(request.Keyword))
            // {
            //     var like = $"%{request.Keyword}%";
            //     query.WhereLike(nameof(Flow.ProcessId), like);
            // }
            #region 新增
            if (!string.IsNullOrEmpty(request.Keyword))
            {
                if (int.TryParse(request.Keyword, out int dd))
                {
                    var like = $"%{request.Keyword}%";
                    query.WhereLike(nameof(Flow.ProcessId), like);
                }
            }
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                //获取用户
                var users = await _userRepository.GetAllAsync(Q.WhereLike(nameof(Entity.User.Name), $"%{request.Keyword}%"));
                query.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id));
            }
            #endregion 
            return await _repository.CountAsync(query);
        }
        public async Task<IEnumerable<Flow>> GetListByUserIdAsync(int userId, bool checking, FlowListRequest request)
        {
            string sql = $@"SELECT 
                                a.*
                            FROM
                                ccpph.tede_flow a  
                            WHERE 1=1 ";
            if (!checking)
                sql += $@" and a.State IN ('Denied','Checked')";
            else
                sql += $@" and a.State Not IN ('Denied','Checked')";
            sql += $@" AND a.UserId = {userId}
                                    AND a.ProcessId IN (SELECT 
                                        id
                                    FROM
                                        ccpph.tede_process)";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (a.ProcessId IN (SELECT 
                                        Id
                                    FROM
                                        tede_process
                                    WHERE
                                        name LIKE '%{request.Keyword}%')
                                    OR a.Id IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                        OR a.UserId IN (SELECT 
                                        Id
                                    FROM
                                        ccpph.tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') or a.Id In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%')) 
                                                                    or a.Id In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processIdValue = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processIdValue += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processIdValue))
                {
                    processIdValue = processIdValue.Remove(processIdValue.Length - 1, 1);
                    sql += $" and a.ProcessId in({processIdValue})";
                }
            }
            sql += $"ORDER BY a.CreatedDate DESC limit {(request.Page - 1) * request.PerPage},{request.PerPage}";

            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var items = await connection.QueryAsync<Flow>(sql);
            //result.Total = await connection.ExecuteScalarAsync<int>(AssembleSqlCount(request));
            return items;
        }

        public async Task<IEnumerable<Flow>> GetListByIdListAsync(IEnumerable<int> flowIdList, int page, int perPage)
        {
            return await _repository.GetAllAsync(Q
                .WhereIn(nameof(Flow.Id), flowIdList)
                .WhereNot(nameof(Flow.ProcessId), 0)
                .ForPage(page, perPage)
                .OrderByDesc(nameof(Flow.Id))
            );
        }

        public async Task<int> GetCountAsync(int processId, string state, string startDate, string endDate)
        {
            var query = Q
                .WhereNot(nameof(Flow.ProcessId), 0);

            if (processId > 0)
            {
                query.Where(nameof(Flow.ProcessId), processId);
            }

            if (!string.IsNullOrEmpty(state) && !StringUtils.EqualsIgnoreCase(state, "all"))
            {
                query.Where(nameof(Flow.State), state);
            }

            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
            {
                query.WhereBetween(nameof(Flow.CreatedDate), StringUtils.ToDateTime(startDate), StringUtils.ToDateTime(endDate));
            }

            return await _repository.CountAsync(query);
        }

        public async Task<IEnumerable<Flow>> GetListAllAsync(int page, int perPage, int processId, string state, string startDate, string endDate)
        {
            var query = Q
                .WhereNot(nameof(Flow.ProcessId), 0)
                .ForPage(page, perPage)
                .OrderByDesc(nameof(Flow.Id));

            if (processId > 0)
            {
                query.Where(nameof(Flow.ProcessId), processId);
            }

            if (!string.IsNullOrEmpty(state) && !StringUtils.EqualsIgnoreCase(state, "all"))
            {
                query.Where(nameof(Flow.State), state);
            }

            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
            {
                query.WhereBetween(nameof(Flow.CreatedDate), StringUtils.ToDateTime(startDate), StringUtils.ToDateTime(endDate));
            }

            return await _repository.GetAllAsync(query);
        }
        public async Task<PagedList<Flow>> GetFlowListAllAsync(int page, int perPage, int processId, string state, string startDate, string endDate)
        {
            var result = new PagedList<Flow>();
            var query = new SqlKata.Query("tede_flow");
            query.WhereNot(nameof(Flow.ProcessId), 0);
            if (processId > 0)
                query.Where(nameof(Flow.ProcessId), processId);
            if (!string.IsNullOrEmpty(state) && !StringUtils.EqualsIgnoreCase(state, "all"))
            {
                if (state.Equals("Meeting"))
                {
                    var queryParameter = new SqlKata.Query("tede_flow_todo");
                    queryParameter.WhereColumns("tede_flow_todo.FlowId", "=", "tede_flow.Id");
                    queryParameter.Where("tede_flow_todo.DoneType", 1);
                    queryParameter.Limit(1);
                    queryParameter.Select("tede_flow_todo.FlowId");
                    query.Where(nameof(Entity.WorkFlow.Flow.Id), "=", queryParameter);
                }
                else if (state.Equals("Checking"))
                    query.Where(n => n.Where(nameof(Flow.State), 4).OrWhere(nameof(Entity.WorkFlow.Flow.State), state));
                else
                    query.Where(nameof(Flow.State), state);
            }
            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                query.WhereBetween(nameof(Flow.CreatedDate), StringUtils.ToDateTime(startDate), StringUtils.ToDateTime(endDate));
            result.Total = await _repository.CountAsync(query);
            query.ForPage(page, perPage).OrderByDesc(nameof(Flow.Id));
            result.Items = await _repository.GetAllAsync(query);
            return result;
        }
        public async Task<bool> DeleteAsync(int flowId)
        {
            return await _repository.DeleteAsync(flowId);
        }



        public async Task UpdateStateAsync(int flowId, string state)
        {
            await _repository.UpdateAsync(Q
                .Set(nameof(Flow.State), state)
                .Where(nameof(Flow.Id), flowId)
            );
        }

        public async Task UpdateProcessNodeIdAsync(int flowId, int processNodeId)
        {
            await _repository.UpdateAsync(Q
                .Set(nameof(Flow.ProcessNodeId), processNodeId)
                .Where(nameof(Flow.Id), flowId)
            );
        }

        public async Task<int> CountAsync(SqlKata.Query query)
        {
            return await _repository.CountAsync(query);
        }
    }
}