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

namespace GxPress.Repository.Implement.WorkFlow
{
    public class FlowTodoRepository : IFlowTodoRepository
    {
        private readonly Repository<FlowTodo> _repository;
        private readonly Repository<Entity.User> _userRepository;
        private readonly IFlowFieldValueRepository _flowFieldValueRepository;
        private readonly Repository<Flow> _flowRepository;
        private readonly Repository<Entity.WorkProcess.Process> _processRepository;
        private readonly string _connectionString;
        private readonly string _databaseTypeStr;
        public FlowTodoRepository(IOptionsMonitor<DatabaseOptions> dbOptionsAccessor, IFlowFieldValueRepository flowFieldValueRepository)
        {
            _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<FlowTodo>(database);
            _flowFieldValueRepository = flowFieldValueRepository;
            _userRepository = new Repository<Entity.User>(database);
            _flowRepository = new Repository<Flow>(database);
            _processRepository = new Repository<Entity.WorkProcess.Process>(database);
        }

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

        public async Task<int> InsertAsync(FlowTodo item)
        {
            var id = await _repository.InsertAsync(item);
            item = await _repository.GetAsync(id);
            if (item.Sort == 0)
            {
                item.Sort = item.Id;
                await _repository.UpdateAsync(item);
            }
            return id;
        }

        public async Task<int> GetCountByUserIdAsync(int userId)
        {
            return await _repository.CountAsync(Q
                .Where(nameof(FlowTodo.UserId), userId)
                .WhereNot(nameof(FlowTodo.ProcessId), 0)
                .WhereNot(nameof(FlowTodo.FlowId), 0).Where(nameof(FlowTodo.IsDone), false).Where(nameof(FlowTodo.Type), nameof(EnumConst.TodoTypeConst.ApproverCheck))
            );
        }

        public async Task<IEnumerable<FlowTodo>> GetListByUserIdAsync(int userId, int processId, int page, int perPage)
        {
            var query = Q
                .Where(nameof(FlowTodo.UserId), userId)
                .WhereNot(nameof(FlowTodo.ProcessId), 0)
                .WhereNot(nameof(FlowTodo.FlowId), 0).Where(nameof(FlowTodo.IsDone), false).Where(nameof(FlowTodo.IsOperate), true)
                .ForPage(page, perPage)
                .OrderByDesc(nameof(FlowTodo.Id));
            if (processId > 0)
            {
                query.Where(nameof(FlowTodo.ProcessId), processId);
            }

            return await _repository.GetAllAsync(query);
        }

        public async Task<IEnumerable<FlowTodo>> GetListByFlowIdAsync(int flowId)
        {
            string sql = $@"
            SELECT 
                   a.*
                FROM
                    tede_flow_todo a
                        INNER JOIN
                    tede_user b ON a.UserId = b.Id
                WHERE
                    a.FlowId = {flowId} ORDER BY a.sort asc,a.LastModifiedDate asc";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
               await connection
                    .QueryAsync<FlowTodo>(sql);
            return result;
        }

        public async Task<bool> DeleteAsync(int todoId)
        {
            return await _repository.DeleteAsync(todoId);
        }

        public async Task<bool> DeleteAllAsync(int flowId)
        {
            return await _repository.DeleteAsync(Q
                .Where(nameof(FlowTodo.FlowId), flowId)
            ) > 0;
        }
        /// <summary>
        /// 已审批
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="type"></param>
        /// <param name="isDone"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<(int FlowId, int TodoId)>> GetMyCheckedTodoIdFlowIdListAsync(int userId, string type, bool isDone, FlowListRequest request)
        {
            string sql = $@"
                           SELECT 
                                a.FlowId,a.Id
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                 INNER JOIN
                                 tede_user d on d.Id=a.UserId
                                 INNER JOIN
                                 tede_user e on e.Id=c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isDone)
                sql += $@"AND a.IsDone = 1";
            else sql += $@"AND a.IsDone = 0 AND a.IsOperate = 1";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') 
                                        or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%'))
                                                or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            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 result =
               await connection
                    .QueryAsync<(int FlowId, int TodoId)>(sql);
            return result;
        }

        public async Task<IEnumerable<(int TodoId, int FlowId)>> GetTodoIdFlowIdListAsync(int userId, string type, bool isDone, FlowListRequest request)
        {
            string sql = $@"SELECT 
                                a.Id,a.FlowId
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                 INNER JOIN
                                 tede_user d on d.Id=a.UserId
                                 INNER JOIN
                                 tede_user e on e.Id=c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isDone)
                sql += $@"AND a.IsDone = 1";
            else sql += $@"AND a.IsDone = 0 AND a.IsOperate = 1";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') 
                                        or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%'))
                                                or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            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 result =
               await connection
                    .QueryAsync<(int TodoId, int FlowId)>(sql);
            return result;

            // var query = Q
            //     .Select(nameof(FlowTodo.Id), nameof(FlowTodo.FlowId))
            //     .WhereNot(nameof(FlowTodo.ProcessId), 0)
            //     .Where(nameof(FlowTodo.UserId), userId)
            //     .Where(nameof(FlowTodo.Type), type)
            //     .Where(nameof(FlowTodo.IsDone), isDone)
            //     .ForPage(request.Page, request.PerPage)
            //     .OrderByDesc(nameof(FlowTodo.Id));
            // if (type == nameof(TodoTypeConst.ApproverCheck) && !isDone)
            // {
            //     query.Where(nameof(FlowTodo.IsOperate), true);
            // }
            // if (request.ProcessIds != null && request.ProcessIds.Any() && !request.ProcessIds.Contains(0))
            // {
            //     query.WhereIn(nameof(FlowTodo.ProcessId), request.ProcessIds);
            // }
            // #region 新增
            // if (!string.IsNullOrWhiteSpace(request.Keyword))
            // {
            //     //获取用户
            //     var users = await _userRepository.GetAllAsync(Q.WhereLike(nameof(Entity.User.Name), $"%{request.Keyword}%"));
            //     if (users.Count > 0)
            //     {
            //         //获取
            //         var flow = await _flowRepository.GetAllAsync(Q.WhereIn(nameof(Flow.UserId), users.Select(n => n.Id)));
            //         var flowTodosApproverCheck = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "ApproverCheck"));
            //         var flowTodosCarbonCopy = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "CarbonCopy"));
            //         var flowIds = new List<int>();
            //         if (flow.Count > 0)
            //             flowIds.AddRange(flow.Select(n => n.Id));
            //         //审批人
            //         if (flowTodosApproverCheck.Count > 0)
            //             flowIds.AddRange(flowTodosApproverCheck.Select(n => n.FlowId));
            //         //抄送人
            //         if (flowTodosCarbonCopy.Count > 0)
            //             flowIds.AddRange(flowTodosCarbonCopy.Select(n => n.FlowId));
            //         query.WhereIn(nameof(FlowTodo.FlowId), flowIds);
            //     }
            //     else
            //     {
            //         var flowFieldValues = await _flowFieldValueRepository.GetAllAsync(Q.WhereLike(nameof(FlowFieldValue.StringValue), $"%{request.Keyword}%"));
            //         query.WhereIn(nameof(Flow.Id), flowFieldValues.Select(n => n.FlowId));
            //         if (flowFieldValues.Count() == 0)
            //         {
            //             var processList = await _processRepository.GetAllAsync(Q.WhereLike(nameof(Entity.WorkProcess.Process.Name), $"%{request.Keyword}%"));
            //             query.WhereIn(nameof(Flow.ProcessId), processList.Select(n => n.Id));
            //         }
            //         else
            //             query.WhereIn(nameof(Flow.Id), flowFieldValues.Select(n => n.FlowId));
            //     }

            // }
            // #endregion

            // return await _repository.GetAllAsync<(int TodoId, int FlowId)>(query);
        }

        public async Task<int> GetReadCountAsync(int userId, string type, FlowListRequest request, bool isRead)
        {
            string sql = $@"SELECT 
                               count(1)
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                  INNER JOIN
                                tede_user d ON d.Id = c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isRead)
                sql += $@"AND a.IsRead = 1";
            else sql += $@"AND a.IsRead = 0";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%')) or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
               await connection
                    .ExecuteScalarAsync<int>(sql);
            return result;
        }

        public async Task<int> GetCountAsync(int userId, string type, bool isDone, FlowListRequest request)
        {
            string sql = $@"SELECT 
                              count(1)
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                 INNER JOIN
                                 tede_user d on d.Id=a.UserId
                                 INNER JOIN
                                 tede_user e on e.Id=c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isDone)
                sql += $@"AND a.IsDone = 1";
            else sql += $@"AND a.IsDone = 0 AND a.IsOperate = 1";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') 
                                        or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%'))
                                                or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
               await connection
                    .ExecuteScalarAsync<int>(sql);
            return result;
        }
        /// <summary>
        /// 已审批数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="type"></param>
        /// <param name="isDone"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<int> GetMyCheckedCountAsync(int userId, string type, bool isDone, FlowListRequest request)
        {
            string sql = $@"
                    
                           SELECT 
                              count(1)
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                INNER JOIN
                                 tede_user d on d.Id=a.UserId
                                 INNER JOIN
                                 tede_user e on e.Id=c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isDone)
                sql += $@" AND a.IsDone = 1";
            else sql += $@" AND a.IsDone = 0 AND a.IsOperate = 1";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%'))
                                                                or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result =
               await connection
                    .ExecuteScalarAsync<int>(sql);
            return result;
        }
        /// <summary>
        /// 未读数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="type"></param>
        /// <param name="isDone"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        #region  
        public async Task<int> GetUReadCountAsync(int userId, string type, bool isDone, FlowListRequest request)
        {
            var query = Q
                .Select(nameof(FlowTodo.FlowId))
                .WhereNot(nameof(FlowTodo.ProcessId), 0)
                .Where(nameof(FlowTodo.UserId), userId)
                .Where(nameof(FlowTodo.Type), type)
                .Where(nameof(FlowTodo.IsDone), isDone)
                .OrderByDesc(nameof(FlowTodo.Id));
            if (!isDone && type == nameof(TodoTypeConst.ApproverCheck))
            {
                query.Where(nameof(FlowTodo.IsOperate), true);
            }
            if (request.ProcessIds != null && request.ProcessIds.Any())
            {
                query.WhereIn(nameof(FlowTodo.ProcessId), request.ProcessIds);
            }
            #region 新增
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                //获取用户
                var users = await _userRepository.GetAllAsync(Q.WhereLike(nameof(Entity.User.Name), $"%{request.Keyword}%"));
                //获取
                var flow = await _flowRepository.GetAllAsync(Q.WhereIn(nameof(Flow.UserId), users.Select(n => n.Id)));
                query.WhereIn(nameof(FlowTodo.FlowId), flow.Select(n => n.Id));
            }
            #endregion
            return await _repository.CountAsync(query);
        }
        #endregion
        public async Task<IEnumerable<(int TodoId, int FlowId)>> GetTodoIdFlowIdListAsync(int userId, string type, FlowListRequest request, bool isRead)
        {
            string sql = $@"SELECT 
                                a.Id,a.FlowId
                            FROM
                                tede_flow_todo a
                                    INNER JOIN
                                tede_process b ON a.ProcessId = b.id
                                    INNER JOIN
                                tede_flow c ON c.Id = a.FlowId
                                 INNER JOIN
                                tede_user d ON d.Id = c.UserId
                            WHERE
                                a.UserId = {userId}
                                    AND a.Type = '{type}'";
            if (isRead)
                sql += $@"AND a.IsRead = 1";
            else sql += $@"AND a.IsRead = 0";
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                sql += $@" AND (b.Name LIKE '%{request.Keyword}%'
                                    OR a.FlowId IN (SELECT 
                                        FlowId
                                    FROM
                                        tede_flow_field_value
                                    WHERE
                                        StringValues LIKE '%{request.Keyword}%')
                                    OR c.UserId IN (SELECT 
                                        Id
                                    FROM
                                        tede_user
                                    WHERE
                                        Name LIKE '%{request.Keyword}%') or a.FlowId In(SELECT 
                                                                                    FlowId
                                                                                FROM
                                                                                    tede_flow_todo
                                                                                WHERE
                                                                                    UserId IN (SELECT 
                                                                                            Id
                                                                                        FROM
                                                                                            tede_user
                                                                                        WHERE
                                                                                            Name LIKE '%{request.Keyword}%')) 
                                                                                            or a.FlowId In(SELECT 
                                                                    FlowId
                                                                FROM
                                                                    tede_flow_message
                                                                WHERE
                                                                    Message LIKE '%{request.Keyword}%'))";
            }
            if (request.ProcessIds.Count > 0)
            {
                var processId = "";
                foreach (var item in request.ProcessIds)
                {
                    if (item == 0)
                        continue;
                    processId += $"{item},";
                }
                if (!string.IsNullOrWhiteSpace(processId))
                {
                    processId = processId.Remove(processId.Length - 1, 1);
                    sql += $" AND b.Id IN ({processId})";
                }
            }
            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 result =
               await connection
                    .QueryAsync<(int TodoId, int FlowId)>(sql);
            return result;
            // var query = Q
            //     .WhereNot(nameof(FlowTodo.ProcessId), 0)
            //     .Where(nameof(FlowTodo.UserId), userId)
            //     .Where(nameof(FlowTodo.Type), type)
            //     .ForPage(request.Page, request.PerPage)
            //     .OrderByDesc(nameof(FlowTodo.Id));

            // if (request.ProcessIds != null && request.ProcessIds.Any() && !request.ProcessIds.Contains(0))
            // {
            //     query.WhereIn(nameof(FlowTodo.ProcessId), request.ProcessIds);
            // }
            // #region 新增
            // if (!string.IsNullOrWhiteSpace(request.Keyword))
            // {
            //     //获取用户
            //     var users = await _userRepository.GetAllAsync(Q.WhereLike(nameof(Entity.User.Name), $"%{request.Keyword}%"));
            //     if (users.Count > 0)
            //     {
            //         //获取
            //         var flow = await _flowRepository.GetAllAsync(Q.WhereIn(nameof(Flow.UserId), users.Select(n => n.Id)));
            //         var flowTodosApproverCheck = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "ApproverCheck"));
            //         var flowTodosCarbonCopy = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "CarbonCopy"));
            //         var flowIds = new List<int>();
            //         if (flow.Count > 0)
            //             flowIds.AddRange(flow.Select(n => n.Id));
            //         //审批人
            //         if (flowTodosApproverCheck.Count > 0)
            //             flowIds.AddRange(flowTodosApproverCheck.Select(n => n.FlowId));
            //         //抄送人
            //         if (flowTodosCarbonCopy.Count > 0)
            //             flowIds.AddRange(flowTodosCarbonCopy.Select(n => n.FlowId));
            //         query.WhereIn(nameof(FlowTodo.FlowId), flowIds);
            //     }
            //     else
            //     {
            //         var allFlowIdList = await _repository.GetAllAsync<int>(query.Select(nameof(FlowTodo.FlowId)));
            //         var flowIdList = await _flowFieldValueRepository.GetFlowIdListAsync(allFlowIdList, request.Keyword);
            //         // if (flowIdList == null || !flowIdList.Any())
            //         // {
            //         //     return new List<(int TodoId, int FlowId)>();
            //         // }
            //         if (flowIdList.Count() == 0)
            //         {
            //             var processList = await _processRepository.GetAllAsync(Q.WhereLike(nameof(Entity.WorkProcess.Process.Name), $"%{request.Keyword}%"));
            //             query.WhereIn(nameof(FlowTodo.ProcessId), processList.Select(n => n.Id));
            //             var result = await _repository.GetAllAsync(query);
            //             return result.Select(n => (n.Id, n.FlowId));
            //         }
            //         else
            //         {
            //             var result = await _repository.GetAllAsync(query.WhereIn(nameof(FlowTodo.FlowId), flowIdList));
            //             return result.Select(n => (n.Id, n.FlowId));
            //         }
            //         //return await _repository.GetAllAsync<(int TodoId, int FlowId)>(query.Select(nameof(FlowTodo.Id),nameof(FlowTodo.FlowId)).WhereIn(nameof(FlowTodo.FlowId), flowIdList));
            //     }
            // }
            // #endregion
            // // if (!string.IsNullOrEmpty(request.Keyword))
            // // {
            // //     var allFlowIdList = await _repository.GetAllAsync<int>(query.Select(nameof(FlowTodo.FlowId)));
            // //     var flowIdList = await _flowFieldValueRepository.GetFlowIdListAsync(allFlowIdList, request.Keyword);
            // //     if (flowIdList == null || !flowIdList.Any())
            // //     {
            // //         return new List<(int TodoId, int FlowId)>();
            // //     }

            // //     return await _repository.GetAllAsync<(int TodoId, int FlowId)>(query.Select(nameof(FlowTodo.Id)).WhereIn(nameof(FlowTodo.FlowId), flowIdList));
            // // }

            // return await _repository.GetAllAsync<(int TodoId, int FlowId)>(query.Select(nameof(FlowTodo.Id), nameof(FlowTodo.FlowId)));
        }

        public async Task<int> GetCountAsync(int userId, string type, FlowListRequest request)
        {
            var query = Q
                .Select(nameof(FlowTodo.FlowId))
                .WhereNot(nameof(FlowTodo.ProcessId), 0)
                .Where(nameof(FlowTodo.UserId), userId)
                .Where(nameof(FlowTodo.Type), type)
                .OrderByDesc(nameof(FlowTodo.Id));

            if (request.ProcessIds != null && request.ProcessIds.Any() && !request.ProcessIds.Contains(0))
            {
                query.WhereIn(nameof(FlowTodo.ProcessId), request.ProcessIds);
            }
            #region 新增
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                //获取用户
                var users = await _userRepository.GetAllAsync(Q.WhereLike(nameof(Entity.User.Name), $"%{request.Keyword}%"));
                if (users.Count > 0)
                {
                    //获取
                    var flow = await _flowRepository.GetAllAsync(Q.WhereIn(nameof(Flow.UserId), users.Select(n => n.Id)));
                    var flowTodosApproverCheck = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "ApproverCheck"));
                    var flowTodosCarbonCopy = await _repository.GetAllAsync(Q.WhereIn(nameof(FlowTodo.UserId), users.Select(n => n.Id)).Where(nameof(FlowTodo.Type), "CarbonCopy"));
                    var flowIds = new List<int>();
                    if (flow.Count > 0)
                        flowIds.AddRange(flow.Select(n => n.Id));
                    //审批人
                    if (flowTodosApproverCheck.Count > 0)
                        flowIds.AddRange(flowTodosApproverCheck.Select(n => n.FlowId));
                    //抄送人
                    if (flowTodosCarbonCopy.Count > 0)
                        flowIds.AddRange(flowTodosCarbonCopy.Select(n => n.FlowId));
                    query.WhereIn(nameof(FlowTodo.FlowId), flowIds);
                }
                else
                {
                    var allFlowIdList = await _repository.GetAllAsync<int>(query.Select(nameof(FlowTodo.FlowId)));
                    var flowIdList = await _flowFieldValueRepository.GetFlowIdListAsync(allFlowIdList, request.Keyword);
                    // if (flowIdList == null || !flowIdList.Any())
                    // {
                    //     return 0;
                    // }
                    if (flowIdList.Count() == 0)
                    {
                        var processList = await _processRepository.GetAllAsync(Q.WhereLike(nameof(Entity.WorkProcess.Process.Name), $"%{request.Keyword}%"));
                        query.WhereIn(nameof(FlowTodo.ProcessId), processList.Select(n => n.Id));

                    }
                    else
                    {
                        return 0;
                    }

                }
            }
            #endregion
            // if (!string.IsNullOrEmpty(request.Keyword))
            // {
            //     var allFlowIdList = await _repository.GetAllAsync<int>(query.Select(nameof(FlowTodo.FlowId)));
            //     var flowIdList = await _flowFieldValueRepository.GetFlowIdListAsync(allFlowIdList, request.Keyword);
            //     if (flowIdList == null || !flowIdList.Any())
            //     {
            //         return 0;
            //     }

            //     return await _repository.CountAsync(query.WhereIn(nameof(FlowTodo.FlowId), flowIdList));
            // }

            return await _repository.CountAsync(query);
        }

        public async Task<FlowTodo> GetAsync(int todoId)
        {
            return await _repository.GetAsync(todoId);
        }
        public async Task<FlowTodo> GetAsync(SqlKata.Query query)
        {
            return await _repository.GetAsync(query);
        }
        /// <summary>
        /// 获取未处理的
        /// </summary>
        /// <param name="flowId"></param>
        /// <returns></returns>
        public async Task<FlowTodo> GetUCheckedAsync(int flowId)
        {
            string sql = $@" SELECT 
                                    a.*
                                    FROM
                                        tede_flow_todo a
                                            INNER JOIN
                                        tede_user b ON a.UserId = b.Id
                                    WHERE
                                        a.FlowId ={flowId} AND a.IsDone = 0
                                            AND a.Type = 'ApproverCheck'
                                            AND IsChecked = 0 order by a.Sort limit 1";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryFirstOrDefaultAsync<FlowTodo>(sql);
            return result;
        }
        public async Task<bool> UpdateAsync(FlowTodo todo)
        {
            return await _repository.UpdateAsync(todo);
        }

        public async Task<bool> UpdateSortAsync(FlowTodo todo)
        {
            string sql = $"UPDATE `ccpph`.`tede_flow_todo` SET `Sort` = {todo.Sort} WHERE `Id` = {todo.Id}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteAsync(sql);
            return result > 0;
        }
        public async Task<bool> UpdateAsync(SqlKata.Query query)
        {
            return await _repository.UpdateAsync(query) > 0;
        }

        public async Task<bool> UpdateReadAsync(int userId, int flowId)
        {
            string sql = $"UPDATE `ccpph`.`tede_flow_todo` SET `IsRead` = 1  WHERE `FlowId` = {flowId} and `UserId`={userId} ";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteAsync(sql);
            return result > 0;
        }

        public async Task<bool> UpdateFlowReadAsync(int userId, int flowId)
        {
            string sql = $"UPDATE `ccpph`.`tede_flow` SET `IsRead` = 1 WHERE `Id` = {flowId} and `UserId`={userId}";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteAsync(sql);
            return result > 0;
        }
        public async Task UpdateCCIsDoneAsync(int userId, int flowId)
        {
            await UpdateReadAsync(userId, flowId);
            await UpdateFlowReadAsync(userId, flowId);
        }

        public async Task DeleteCheckingByFlowIdAsync(int flowId)
        {
            await _repository.DeleteAsync(Q
                .Where(nameof(FlowTodo.FlowId), flowId)
                .Where(nameof(FlowTodo.Type), nameof(TodoTypeConst.ApproverCheck))
                .Where(nameof(FlowTodo.IsDone), false)
            );
        }

        public async Task<IEnumerable<int>> GetAllCheckedUserIdListAsync(int flowId)
        {
            return await _repository.GetAllAsync<int>(Q
                .Select(nameof(FlowTodo.UserId))
                .Where(nameof(FlowTodo.FlowId), flowId)
                .Where(nameof(FlowTodo.Type), nameof(TodoTypeConst.ApproverCheck))
                .Where(nameof(FlowTodo.IsChecked), true)
                .Where(nameof(FlowTodo.IsDone), true)
            );
        }
        /// <summary>
        /// 获取操作成功数量
        /// </summary>
        /// <param name="flowId"></param>
        /// <returns></returns>
        public async Task<int> CheckedCountAsync(int flowId)
        {
            string sql = $@"SELECT 
                                count(1)
                                FROM
                                    tede_flow_todo a
                                        INNER JOIN
                                    tede_user b ON a.UserId = b.Id
                                WHERE
                                    a.FlowId ={flowId} AND a.IsDone = 1
                                        AND a.Type = 'ApproverCheck'
                                        AND IsChecked = 1";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
        /// <summary>
        /// 获取所有数量
        /// </summary>
        /// <param name="flowId"></param>
        /// <returns></returns>
        public async Task<int> AllCountAsync(int flowId)
        {
            string sql = $@"SELECT 
                                count(1)
                                FROM
                                    tede_flow_todo a
                                        INNER JOIN
                                    tede_user b ON a.UserId = b.Id
                                WHERE
                                    a.FlowId ={flowId} 
                                    AND a.Type = 'ApproverCheck'";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
        public async Task<int> CountAsync(SqlKata.Query query)
        {
            return await _repository.CountAsync(query);
        }
        /// <summary>
        /// 抄送未读数量
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public async Task<int> FlowCcUReadCountAsync(int userId)
        {
            string sql = $"SELECT count(1) FROM tede_flow_todo  a where a.UserId={userId} and a.Type='CarbonCopy' and a.IsRead=0 and a.IsDone=0 and a.ProcessId=(select Id from tede_process where Id=a.ProcessId)";
            var databaseType = StringUtils.ToEnum<DatabaseType>(_databaseTypeStr, DatabaseType.MySql);
            var database = new Database(databaseType, _connectionString);
            var connection = database.GetConnection();
            var result = await connection.ExecuteScalarAsync<int>(sql);
            return result;
        }
        public async Task<IEnumerable<FlowTodo>> GetAllAsync(Query query)
        {
            return await _repository.GetAllAsync(query);
        }
    }
}