using Edu.Common.Enum;
using Edu.Model.Entity.OKR;
using Edu.Model.ViewModel.OKR;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;

namespace Edu.Repository.OKR
{
    /// <summary>
    /// OKR目标仓储层
    /// </summary>
    public class RB_OKR_ObjectiveRepository : BaseRepository<RB_OKR_Objective>
    {

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetPageList(int pageIndex, int pageSize, out long rowsCount, RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and Status <>5";
            if (demodel.Group_Id > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (!string.IsNullOrEmpty(demodel.Title))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Title)} like '%{demodel.Title}%'";
            }
            if (demodel.ProgressState > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.ProgressState)} ={(int)demodel.ProgressState}";
            }

            string sql = $@" select * from RB_OKR_Objective where {where} order by Id desc";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out rowsCount, sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetList(RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and Status <>5";
            if (demodel.Group_Id > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.Id > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Id)} ={demodel.Id}";
            }
            if (!string.IsNullOrEmpty(demodel.ObjectiveIds))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Id)} in({demodel.ObjectiveIds})";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (!string.IsNullOrEmpty(demodel.Title))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Title)} like '%{demodel.Title}%'";
            }
            if (demodel.ProgressState > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.ProgressState)} ={(int)demodel.ProgressState}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (!string.IsNullOrEmpty(demodel.CreateByIds))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} in({demodel.CreateByIds})";
            }
            if (demodel.Status > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Status)} ={demodel.Status}";
            }
            if (demodel.selectStatus > 0)
            {
                if (demodel.selectStatus == 1)
                {
                    where += $@" and {nameof(RB_OKR_Objective_ViewModel.Status)} in (2,3)";
                }
            }
            string sql = $@" select * from RB_OKR_Objective where {where} order by Sort asc";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取objective数量 统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetObjectiveNumList(RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and Status =2";
            if (demodel.Group_Id > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (!string.IsNullOrEmpty(demodel.CreateByIds))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} in({demodel.CreateByIds})";
            }

            string sql = $@" select CreateBy,Count(0) as ObjectiveNum from RB_OKR_Objective where {where} group by CreateBy";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取objective状态 统计
        /// </summary>
        /// <param name="rB_OKR_Objective_ViewModel"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetObjectiveStateList(RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and Status =2";
            if (demodel.Group_Id > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (!string.IsNullOrEmpty(demodel.CreateByIds))
            {
                where += $@" and {nameof(RB_OKR_Objective_ViewModel.CreateBy)} in({demodel.CreateByIds})";
            }

            string sql = $@" select ProgressState,CreateBy,Count(0) as ObjectiveNum from RB_OKR_Objective where {where} group by ProgressState,CreateBy";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取父级列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetParentList(RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and o.Status <>5";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (!string.IsNullOrEmpty(demodel.Title))
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Title)} like '%{demodel.Title}%'";
            }
            if (demodel.ProgressState > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.ProgressState)} ={(int)demodel.ProgressState}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (demodel.Status > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Status)} ={demodel.Status}";
            }
            if (!string.IsNullOrEmpty(demodel.ObjectiveIds))
            {
                where += $@" and r.{nameof(RB_OKR_ObjectiveRelation.ObjectiveId)} in({demodel.ObjectiveIds})";

                string sql = $@" select o.*,r.ObjectiveId,r.ParentId,e.EmName,e.EmPhoto from RB_OKR_Objective o
inner join rb_okr_objectiverelation r on o.Id = r.ParentId 
left join rb_employee e on o.CreateBy = e.EmployeeId 
where {where} order by o.Id desc";
                return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
            }
            if (!string.IsNullOrEmpty(demodel.ParentIds))
            {
                where += $@" and r.{nameof(RB_OKR_ObjectiveRelation.ParentId)} in({demodel.ParentIds})";

                string sql = $@" select o.*,r.ObjectiveId,r.ParentId,e.EmName,e.EmPhoto from RB_OKR_Objective o
inner join rb_okr_objectiverelation r on o.Id = r.ObjectiveId 
left join rb_employee e on o.CreateBy = e.EmployeeId 
where {where} order by o.Id desc";
                return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
            }
            return new List<RB_OKR_Objective_ViewModel>();
        }

        public List<RB_OKR_Objective_ViewModel> GetListForEmName(RB_OKR_Objective_ViewModel demodel)
        {
            string where = $@" 1=1 and o.Status <>5";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.Id > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Id)} ={demodel.Id}";
            }
            if (!string.IsNullOrEmpty(demodel.ObjectiveIds))
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Id)} in({demodel.ObjectiveIds})";
            }
            if (demodel.PeriodId > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.PeriodId)} ={demodel.PeriodId}";
            }
            if (!string.IsNullOrEmpty(demodel.Title))
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Title)} like '%{demodel.Title}%'";
            }
            if (demodel.ProgressState > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.ProgressState)} ={(int)demodel.ProgressState}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (demodel.Status > 0)
            {
                where += $@" and o.{nameof(RB_OKR_Objective_ViewModel.Status)} ={demodel.Status}";
            }

            string sql = $@" select o.*,e.EmName,e.EmPhoto from RB_OKR_Objective o
left join rb_employee e on o.CreateBy = e.EmployeeId 
where {where} order by o.Sort asc";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }


        /// <summary>
        /// 获取最大的Sort
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetObjectiveMaxSort(int group_Id, int periodId, int id)
        {
            string sql = $@"SELECT MAX(Sort) Sort FROM rb_okr_objective where Group_Id={group_Id} and PeriodId={periodId} AND CreateBy ={id}";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 设置排序
        /// </summary>
        /// <param name="periodId"></param>
        /// <param name="createBy"></param>
        /// <param name="startSort"></param>
        /// <param name="endSort"></param>
        /// <returns></returns>
        public bool UpdateOtherSort(int periodId, int createBy, int id, int startSort, int endSort)
        {
            string sql = $@"UPDATE rb_okr_objective SET Sort =Sort +1 WHERE PeriodId={periodId} AND CreateBy ={createBy} AND Id <> {id} ";
            if (startSort > 0)
            {
                sql += $@" AND Sort > {startSort} ";
            }
            if (endSort > 0)
            {
                sql += $@" and Sort < {endSort} ";
            }
            return Execute(sql) > 0;
        }

        /// <summary>
        /// 设置排序
        /// </summary>
        /// <param name="periodId"></param>
        /// <param name="createBy"></param>
        /// <param name="id"></param>
        /// <param name="startSort"></param>
        /// <param name="endSort"></param>
        /// <returns></returns>
        public bool UpdateOtherSortDesc(int periodId, int createBy, int id, int startSort, int endSort)
        {
            string sql = $@"UPDATE rb_okr_objective SET Sort =Sort -1 WHERE PeriodId={periodId} AND CreateBy ={createBy} AND Id <> {id} ";
            if (startSort > 0 && endSort > 0)
            {
                sql += $@" AND Sort > {startSort} and Sort <= {endSort}";
            }
            return Execute(sql) > 0;
        }

        /// <summary>
        /// 获取待审核的目标人员
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetOKRMyEmployeeAuditList(int group_Id, int periodId, string v)
        {
            string sql = $@" select CreateBy,GROUP_CONCAT( DISTINCT PeriodId) AS PeriodIds from RB_OKR_Objective WHERE Group_Id={group_Id} and `Status` =1 and CreateBy in ({v})";
            //HK 03-01修改
            if (periodId > 0)
            {
                sql += $" and PeriodId ={periodId}";
            }
            sql += " GROUP BY CreateBy ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取我的对齐人员
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetOKRMyEmployeeAlignList(int group_Id, int periodId, int id)
        {
            string sql = $@"select o2.CreateBy from RB_OKR_Objective o1
LEFT JOIN rb_okr_objectiverelation re on o1.Id = re.ObjectiveId
LEFT JOIN RB_OKR_Objective o2 on re.ParentId = o2.Id
WHERE o1.Group_Id={group_Id} and o1.`Status` <>5 and o2.`Status` <>5 and o1.PeriodId ={periodId} and o1.CreateBy in ({id})  GROUP BY o2.CreateBy";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取个人维度打分以及目标量
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetOKRPersonalAlignList(int group_Id, int periodId, string CreateBys)
        {
            string where = string.Empty;
            if (!string.IsNullOrWhiteSpace(CreateBys))
            {
                where = $" and a.CreateBy in({CreateBys})";
            }
            string sql = $@"SELECT COUNT(*) as ChildNum,a.CreateBy ,SUM(Score) as Score from rb_okr_objective as a LEFT JOIN  rb_okr_objectiverelation as b on a.Id=b.ObjectiveId 
WHERE a.Group_Id={group_Id} and a.`Status` <>5  and a.PeriodId ={periodId} {where}
GROUP BY a.CreateBy";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取个人维度对齐量
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetOKRPersonalRelationAlignList(int group_Id, int periodId, string CreateBys, int id)
        {
            string sql = $@"SELECT COUNT(0) as ChildNum,o2.CreateBy FROM 
rb_okr_objectiverelation or1
LEFT JOIN rb_okr_objective o1 on or1.ObjectiveId = o1.Id
LEFT JOIN rb_okr_objective o2 on or1.ParentId = o2.Id
WHERE o1.CreateBy in({CreateBys})  and o2.CreateBy ={id} and o1.`Status`=2 and  o1.Group_Id={group_Id}   and o1.PeriodId ={periodId} and  o2.Group_Id={group_Id}   and o2.PeriodId ={periodId}  and o2.`Status`=2 group by o1.CreateBy  ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }



        /// <summary>
        /// 获取个人维度对齐量
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetOKRPersonalRelationAlignList_V2(int group_Id, int periodId, string CreateBys)
        {
            string sql = $@"SELECT COUNT(0) as ChildNum,o1.CreateBy FROM 
rb_okr_objectiverelation or1
LEFT JOIN rb_okr_objective o1 on or1.ObjectiveId = o1.Id
WHERE o1.CreateBy in({CreateBys})  and o1.`Status`=2 and  o1.Group_Id={group_Id}   and o1.PeriodId ={periodId} group by o1.CreateBy  ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }



        /// <summary>
        /// 获取员工查询SQL
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public string GetEmployeeSqlReposiroty(OKRPersonalDimension query, DynamicParameters parameters)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder where2 = new StringBuilder();

            StringBuilder wheremanager = new StringBuilder();
            StringBuilder whereteacher = new StringBuilder();
            StringBuilder whereassist = new StringBuilder();
            StringBuilder whereperiodId = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(OKRPersonalDimension.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(OKRPersonalDimension.School_Id), query.School_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.Dept_Ids))
                {
                    where.AppendFormat(" AND B.{0} in({1}) ", nameof(OKRPersonalDimension.Dept_Id), query.Dept_Ids);
                }
                if (query.Dept_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(OKRPersonalDimension.Dept_Id), query.Dept_Id);
                }
                if (query.Post_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(OKRPersonalDimension.Post_Id), query.Post_Id);
                }
                if (query.LeaveStatus > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(OKRPersonalDimension.LeaveStatus), query.LeaveStatus);
                }

                if (query.PeriodId > 0)
                {
                    whereperiodId.AppendFormat(" AND a.{0}={1} ", nameof(OKRPersonalDimension.PeriodId), query.PeriodId);
                }
                if (!string.IsNullOrWhiteSpace(query.Dept_Ids))
                {
                    where.AppendFormat(" AND B.{0} in ({1}) ", nameof(OKRPersonalDimension.Dept_Id), query.Dept_Ids);
                }
                //if (!string.IsNullOrEmpty(query.StartEntryTime))
                //{
                //    where.AppendFormat(" AND b.{0}>='{1}' ", nameof(Employee_ViewModel.EntryTime), query.StartEntryTime);
                //}
                //if (!string.IsNullOrEmpty(query.EndEntryTime))
                //{
                //    where.AppendFormat(" AND b.{0}<='{1} 23:59:59' ", nameof(Employee_ViewModel.EntryTime), query.EndEntryTime);
                //}
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.Id as AccountId,A.Account,orro.ObjectiveNCount,orro.Score,A.Group_Id,A.School_Id,A.EmployeeName,A.UserIcon,A.LeaveStatus,A.AccountType
      ,IFNULL(G.GroupName,'') AS GroupName,IFNULL(s.SName,'') AS SchoolName
      ,IFNULL(d.DeptId,0) AS Dept_Id,IFNULL(d.DeptName,'') AS DeptName
FROM 
(
		SELECT A.Id,A.Account,A.`Password`, 1 as AccountType,B.MId as AccountId
               ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
               ,b.School_Id,IFNULL(B.MName,'') AS EmployeeName,B.MHead AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
               ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
               ,B.LeaveStatus,B.LeaveTime,B.MTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM rb_manager AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.MId AND A.AccountType=1
		WHERE 1=1 {0} {1}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`, 2 as AccountType,B.TId as AccountId
              ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
              ,b.School_Id,IFNULL(B.TeacherName,'') AS EmployeeName,B.TeacherIcon AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
              ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
              ,B.LeaveStatus,B.LeaveTime,B.TeacherTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM rb_teacher AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.TId AND A.AccountType=2
		WHERE 1=1 {0} {2}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`, 3 as AccountType,B.AId as AccountId
              ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
              ,b.School_Id,IFNULL(B.AssistName,'') AS EmployeeName,B.AssistIcon AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
              ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
              ,B.LeaveStatus,B.LeaveTime,B.AssistTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM  rb_assist AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.AId AND A.AccountType=3
		WHERE 1=1 {0} {3}
) AS A LEFT JOIN rb_group AS g ON A.Group_Id=g.GId
       LEFT JOIN rb_school AS s ON A.School_Id=s.SId
       LEFT JOIN rb_department AS d ON A.Dept_Id=d.DeptId
      LEFT JOIN (SELECT a.CreateBy,COUNT(*) as ObjectiveNCount,SUM(Score) as Score from rb_okr_objective as a LEFT JOIN  rb_okr_objectiverelation as b on a.Id=b.ObjectiveId  where a.Status<>5 {4}  GROUP BY a.CreateBy) as 
orro on orro.CreateBy=a.Id 
WHERE  1=1 {5}
", where.ToString(), wheremanager.ToString(), whereteacher.ToString(), whereassist.ToString(), whereperiodId.ToString(), where2.ToString());
            return builder.ToString();
        }


        /// <summary>
        /// 获取员工okr列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<OKRPersonalDimension> GetAllEmployeeOKRPPageRepository(int pageIndex, int pageSize, out long rowsCount, OKRPersonalDimension query)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = GetEmployeeSqlReposiroty(query, parameters);
            var list = GetPage<OKRPersonalDimension>(pageIndex, pageSize, out rowsCount, sql, parameters).ToList();
            return list;
        }

        /// <summary>
        /// 获取员工okr列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<OKRPersonalDimension> GetAllEmployeeOKRPList(OKRPersonalDimension query)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = GetEmployeeSqlReposiroty(query, parameters);
            var list = Get<OKRPersonalDimension>(sql, parameters).ToList();
            return list;
        }

        #region 数据分布明细
        public List<RB_OKR_Objective_ViewModel> GetObjectiveNumPageList(int pageIndex, int pageSize, out long count, int periodId, int startValue, int endValue, int orderBy, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select e.EmployeeId AS CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,SUM(CASE WHEN o.Id is null THEN 0 ELSE 1 END) as ObjectiveNum 
from rb_employee e 
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy AND o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")} group by e.EmployeeId
)t where t.ObjectiveNum >={startValue} {(endValue >= 0 ? "and t.ObjectiveNum <=" + endValue : "")} order by t.ObjectiveNum {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveNumListForDept(int periodId, string deptIds, int group_Id)
        {
            string sql = $@" 
select t.RB_Department_Id,count(0) as ObjectiveNum,
sum(case when t.ObjectiveNum=0 then 1 else 0 end) as ObjectiveNum1,
sum(case when t.ObjectiveNum>0 and t.ObjectiveNum<=2 then 1 else 0 end) as ObjectiveNum2,
sum(case when t.ObjectiveNum>2 and t.ObjectiveNum<=5 then 1 else 0 end) as ObjectiveNum3,
sum(case when t.ObjectiveNum>5 then 1 else 0 end) as ObjectiveNum4 
from(
select e.EmployeeId,e.RB_Department_Id,SUM(CASE WHEN o.Id is null THEN 0 ELSE 1 END) as ObjectiveNum 
from rb_employee e 
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy AND o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id={group_Id} and e.IsLeave =0 and e.RB_Department_Id in ({deptIds}) group by e.EmployeeId
)t group by t.RB_Department_Id
";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveStatePageList(int pageIndex, int pageSize, out long count, int periodId, int startValue, int orderBy, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select o.CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,count(0) as ObjectiveNum 
from  RB_OKR_Objective o
LEFT JOIN rb_employee e on e.EmployeeId = o.CreateBy 
where o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2 and o.ProgressState ={startValue} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and o.CreateBy in (" + userIds + ")" : "")} group by o.CreateBy
)t order by t.ObjectiveNum {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveStatePageList_V2(int pageIndex, int pageSize, out long count, int periodId, int orderBy, int orderByNum, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select e.EmployeeId AS CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
sum(case when o.IsUpdated <> 1 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.IsUpdated = 1 and o.ProgressState = 1 then 1 else 0 end) as ObjectiveNum2, 
sum(case when o.IsUpdated = 1 and o.ProgressState = 2 then 1 else 0 end) as ObjectiveNum3,
sum(case when o.IsUpdated = 1 and o.ProgressState = 3 then 1 else 0 end) as ObjectiveNum4 
from  rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")} group by e.EmployeeId
)t order by t.ObjectiveNum{orderByNum} {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveStateListForDept(int periodId, string deptIds, int group_Id)
        {
            string sql = $@" 
select e.RB_Department_Id,
sum(case when o.IsUpdated <> 1 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.IsUpdated = 1 and o.ProgressState = 1 then 1 else 0 end) as ObjectiveNum2, 
sum(case when o.IsUpdated = 1 and o.ProgressState = 2 then 1 else 0 end) as ObjectiveNum3,
sum(case when o.IsUpdated = 1 and o.ProgressState = 3 then 1 else 0 end) as ObjectiveNum4 
from  rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 and e.RB_Department_Id in ({deptIds}) group by e.RB_Department_Id
";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveProgressPageList(int pageIndex, int pageSize, out long count, int periodId, int startValue, int endValue, int orderBy, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select o.CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,count(0) as ObjectiveNum 
from  RB_OKR_Objective o
LEFT JOIN rb_employee e on e.EmployeeId = o.CreateBy 
where o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2 and o.Progress >={startValue} {(endValue >= 0 ? "and o.Progress <=" + endValue : "")} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and o.CreateBy in (" + userIds + ")" : "")} group by o.CreateBy
)t order by t.ObjectiveNum {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveProgressPageList_V2(int pageIndex, int pageSize, out long count, int periodId, int orderBy, int orderByNum, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select e.EmployeeId AS CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
sum(case when o.Progress=0 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.Progress>0 and o.Progress <=30 then 1 else 0 end) as ObjectiveNum2,
sum(case when o.Progress>30 and o.Progress <=60 then 1 else 0 end) as ObjectiveNum3,
sum(case when o.Progress>60 and o.Progress <=90 then 1 else 0 end) as ObjectiveNum4,
sum(case when o.Progress>90 then 1 else 0 end) as ObjectiveNum5 
from  rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")} group by e.EmployeeId
)t order by t.ObjectiveNum{orderByNum} {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveProgressListForDept(int periodId, string deptIds, int group_Id)
        {
            string sql = $@" 
select e.RB_Department_Id,
sum(case when o.Progress=0 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.Progress>0 and o.Progress <=30 then 1 else 0 end) as ObjectiveNum2,
sum(case when o.Progress>30 and o.Progress <=60 then 1 else 0 end) as ObjectiveNum3,
sum(case when o.Progress>60 and o.Progress <=90 then 1 else 0 end) as ObjectiveNum4,
sum(case when o.Progress>90 then 1 else 0 end) as ObjectiveNum5 
from  rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 and e.RB_Department_Id in ({deptIds}) group by e.RB_Department_Id
";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveScorePageList(int pageIndex, int pageSize, out long count, int periodId, int startValue, int endValue, int orderBy, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select o.CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,count(0) as ObjectiveNum 
from  RB_OKR_Objective o
LEFT JOIN rb_employee e on e.EmployeeId = o.CreateBy 
where o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2 and o.Score >={startValue} {(endValue >= 0 ? "and o.Score <=" + endValue : "")} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and o.CreateBy in (" + userIds + ")" : "")} group by o.CreateBy
)t order by t.ObjectiveNum {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveScorePageList_V2(int pageIndex, int pageSize, out long count, int periodId, int orderBy, int orderByNum, string userIds, int group_Id)
        {
            string sql = $@" 
select * from(
select e.EmployeeId AS CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
sum(case when o.Score=0 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.Score>0 and o.Score<=0.3 then 1 else 0 end) as ObjectiveNum2, 
sum(case when o.Score>0.3 and o.Score<=0.6 then 1 else 0 end) as ObjectiveNum3, 
sum(case when o.Score>0.6 and o.Score<=0.9 then 1 else 0 end) as ObjectiveNum4, 
sum(case when o.Score>0.9 then 1 else 0 end) as ObjectiveNum5 
from   rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")} group by e.EmployeeId
)t order by t.ObjectiveNum{orderByNum} {(orderBy == 1 ? " asc" : " desc")}
";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        public List<RB_OKR_Objective_ViewModel> GetObjectiveScoreListForDept(int periodId, string deptIds, int group_Id)
        {
            string sql = $@" 
select e.RB_Department_Id,
sum(case when o.Score=0 then 1 else 0 end) as ObjectiveNum1, 
sum(case when o.Score>0 and o.Score<=0.3 then 1 else 0 end) as ObjectiveNum2, 
sum(case when o.Score>0.3 and o.Score<=0.6 then 1 else 0 end) as ObjectiveNum3, 
sum(case when o.Score>0.6 and o.Score<=0.9 then 1 else 0 end) as ObjectiveNum4, 
sum(case when o.Score>0.9 then 1 else 0 end) as ObjectiveNum5 
from   rb_employee e
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy and o.Group_Id ={group_Id} and o.PeriodId ={periodId} AND o.Status =2
where e.RB_Group_id ={group_Id} and e.IsLeave =0 and e.RB_Department_Id in ({deptIds}) group by e.RB_Department_Id
";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        #endregion

        #region 注意事项

        /// <summary>
        /// 未填写人数
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetNotFilledList(int groupId, int periodId, string userIds)
        {
            string sql = $@"SELECT COUNT(0) FROM(
SELECT CreateBy FROM rb_okr_objective WHERE Group_Id={groupId} and Status <>5 and PeriodId={periodId} {(!string.IsNullOrEmpty(userIds)? "and CreateBy in("+ userIds + ")":"")} GROUP BY CreateBy
)t ";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 未更新人数
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetNotUpdatedList(int groupId, int periodId, string userIds)
        {
            string sql = $@"SELECT COUNT(0) FROM(
SELECT CreateBy FROM rb_okr_objective WHERE Group_Id={groupId} and Status <>5 and Progress =0 and PeriodId={periodId} and CreateBy in({userIds}) GROUP BY CreateBy
)t ";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 未打分人数
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetNotScoreList(int groupId, int periodId, string userIds, int type = 1)
        {
            string sql = $@"SELECT COUNT(0) FROM(
SELECT CreateBy FROM rb_okr_objective WHERE Group_Id={groupId} and Status <>5 {(type==1? "and Score =0" : "and Score >0")} and PeriodId={periodId} {(!string.IsNullOrEmpty(userIds) ? "and CreateBy in(" + userIds + ")" : "")} GROUP BY CreateBy
)t ";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 获取未填写人分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetNotFilledPageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName FROM 
rb_employee e
left join rb_okr_objective o on e.EmployeeId = o.CreateBy and o.Status <>5 and o.PeriodId={periodId}
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds}) and o.Id is null
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取未更新人分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetNotpdatedPageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName FROM 
rb_employee e
left join rb_okr_objective o on e.EmployeeId = o.CreateBy 
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds}) and o.Status <>5 and o.PeriodId ={periodId} and o.Progress =0 group by e.EmployeeId
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取未打分人分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetNotScorePageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName FROM 
rb_employee e
left join rb_okr_objective o on e.EmployeeId = o.CreateBy 
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds}) and o.Status <>5 and o.PeriodId ={periodId} and o.Score =0 group by e.EmployeeId
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取人员填写分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetWritePageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds, int orderBy)
        {
            string sql = $@"
select * from(
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (SELECT o.CreateBy FROM rb_okr_objective o WHERE o.Group_Id ={groupId} and o.Status <>5 and o.PeriodId={periodId} and o.CreateBy in({userIds}) GROUP BY o.CreateBy) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds})
)tt order by tt.Status {(orderBy == 1 ? "asc" : "desc")}
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取人员对齐分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetAlignPageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds, int orderBy)
        {
            string sql = $@"
select * from(
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (
SELECT o.CreateBy FROM  rb_okr_objective o
INNER JOIN rb_okr_objectiverelation re on re.ObjectiveId = o.Id
WHERE o.Group_Id={groupId} and o.Status <>5 and o.PeriodId={periodId} and o.CreateBy in({userIds}) GROUP BY o.CreateBy
) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds})
)tt order by tt.Status {(orderBy == 1 ? "asc" : "desc")}
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取人员最近更新分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetUpdatePageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds, string date, int orderBy)
        {
            string sql = $@"
select * from(
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
CASE WHEN t.UserId IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (
SELECT UserId FROM rb_okr_updateprogress WHERE Group_Id={groupId} and PeriodId={periodId} and UpdateTime >= '{date}' and UserId in ({userIds}) GROUP BY UserId
) t ON e.EmployeeId = t.UserId
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds})
)tt order by tt.Status {(orderBy == 1 ? "asc" : "desc")}
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取人员未打分分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetScorePageList(int pageIndex, int pageSize, out long count, int groupId, int periodId, string userIds, int orderBy)
        {
            string sql = $@"
select * from(
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (SELECT o.CreateBy FROM rb_okr_objective o WHERE o.Group_Id ={groupId} and o.Status <>5 and o.PeriodId={periodId} and o.Score >0 and o.CreateBy in({userIds}) GROUP BY o.CreateBy) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.EmployeeId in({userIds})
)tt order by tt.Status {(orderBy == 1 ? "asc" : "desc")}
 ";
            return GetPage<RB_OKR_Objective_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }


        /// <summary>
        /// 获取人员填写列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="deptIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetWriteList(int groupId, int periodId, string deptIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,e.RB_Department_Id,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (SELECT o.CreateBy FROM rb_okr_objective o WHERE o.Group_Id ={groupId} and o.Status <>5 and o.PeriodId={periodId} GROUP BY o.CreateBy) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.RB_Department_Id in({deptIds})
 ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取人员对齐分页列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="deptIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetAlignList(int groupId, int periodId, string deptIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,e.RB_Department_Id,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (
SELECT o.CreateBy FROM  rb_okr_objective o
INNER JOIN rb_okr_objectiverelation re on re.ObjectiveId = o.Id
WHERE o.Group_Id={groupId} and o.Status <>5 and o.PeriodId={periodId} GROUP BY o.CreateBy
) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.RB_Department_Id in({deptIds})
 ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取人员最近更新分页列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetUpdateList(int groupId, int periodId, string deptIds, string date)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,e.RB_Department_Id,
CASE WHEN t.UserId IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (
SELECT UserId FROM rb_okr_updateprogress WHERE Group_Id={groupId} and PeriodId={periodId} and UpdateTime >= '{date}' GROUP BY UserId
) t ON e.EmployeeId = t.UserId
WHERE e.RB_Group_id={groupId} and e.RB_Department_Id in({deptIds})
 ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取人员未打分分页列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="periodId"></param>
        /// <param name="deptIds"></param>
        /// <returns></returns>
        public List<RB_OKR_Objective_ViewModel> GetScoreList(int groupId, int periodId, string deptIds)
        {
            string sql = $@"
SELECT e.EmployeeId as CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,e.RB_Department_Id,
CASE WHEN t.CreateBy IS NOT NULL THEN 1 ELSE 2 END AS Status FROM 
rb_employee e
LEFT JOIN (SELECT o.CreateBy FROM rb_okr_objective o WHERE o.Group_Id ={groupId} and o.Status <>5 and o.PeriodId={periodId} and o.Score >0 GROUP BY o.CreateBy) t ON e.EmployeeId = t.CreateBy
WHERE e.RB_Group_id={groupId} and e.RB_Department_Id in({deptIds})
 ";
            return Get<RB_OKR_Objective_ViewModel>(sql).ToList();
        }

        #endregion

        #region 概率查询

        /// <summary>
        /// 对齐人数
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetAlignPeopleNum(int group_Id, int periodId, string userIds)
        {
            string sql = $@"SELECT COUNT(0) FROM(
SELECT o.CreateBy FROM  rb_okr_objective o
INNER JOIN rb_okr_objectiverelation re on re.ObjectiveId = o.Id
WHERE o.Group_Id={group_Id} and o.Status <>5 and o.PeriodId={periodId} {(!string.IsNullOrEmpty(userIds) ? "and o.CreateBy in(" + userIds + ")" : "")} GROUP BY o.CreateBy
)t;";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 最近7天更新进度人数
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="periodId"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetRecently7DayPeopleNum(int group_Id, int periodId, string userIds,string date)
        {
            string sql = $@"SELECT COUNT(0) FROM(
SELECT UserId FROM rb_okr_updateprogress WHERE Group_Id={group_Id} and PeriodId={periodId} and UpdateTime >= '{date}' {(!string.IsNullOrEmpty(userIds) ? "and UserId in(" + userIds + ")" : "")} GROUP BY UserId
)t;";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
        #endregion


        #region 数据看板
        public List<OKRPersonalDimension> GetOKRGRDateDistributionPage(int pageIndex, int pageSize, out long count,string EmployeeName, int periodId, int startValue, int endValue, int orderBy, string userIds, int group_Id)
        {
            //            string sql = $@" 
            //select * from(
            //select e.EmployeeId AS CreateBy,e.EmName,e.EmPhoto,e.DeptName,e.PostName,SUM(CASE WHEN o.Id is null THEN 0 ELSE 1 END) as ObjectiveNum 
            //from rb_employee e 
            //LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy AND o.PeriodId ={periodId} AND o.Status =2
            //where e.RB_Group_id={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")} group by e.EmployeeId
            //)t where t.ObjectiveNum >={startValue} {(endValue >= 0 ? "and t.ObjectiveNum <=" + endValue : "")} order by t.ObjectiveNum {(orderBy == 1 ? " asc" : " desc")}
            //";

            string orderbyInfo = string.Empty;
            if (orderBy == 1)
            {
                orderbyInfo = " order by t.ObjectiveNCount asc";
            }
            else if (orderBy == 2)
            {
                orderbyInfo = " order by t.ObjectiveNCount desc";
            }
            else if (orderBy == 3)
            {
                orderbyInfo = " order by t.OBjectiveRelation desc";
            }
            else if (orderBy == 4)
            {
                orderbyInfo = " order by t.OBjectiveRelation desc";
            }
            else if (orderBy == 5)
            {
                orderbyInfo = " order by t.DayNum desc";
            }
            else if (orderBy == 6)
            {
                orderbyInfo = " order by t.DayNum desc";
            }
            else if (orderBy == 7)
            {
                orderbyInfo = " order by t.ScoreCount desc";
            }
            else if (orderBy == 8)
            {
                orderbyInfo = " order by t.ScoreCount desc";
            }

            string sql = $@" 
select * from(
select e.EmployeeId AS Id,e.EmName as EmployeeName ,e.EmPhoto as UserIcon,e.DeptName,e.PostName,SUM(CASE WHEN o.Id is null THEN 0 ELSE 1 END) as ObjectiveNCount ,
sum(case when o.Score>0 then 1 else 0 end) as ScoreCount,  IFNULL(obj.OBjectiveRelation,0) as  OBjectiveRelation,IFNULL(okrp.daysum,-1) as DayNum
from rb_employee e 
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy AND o.PeriodId ={periodId} AND o.Status =2 and  o.Group_Id={group_Id}
LEFT JOIN (SELECT COUNT(0) as OBjectiveRelation,o1.CreateBy FROM 
rb_okr_objectiverelation or1
LEFT JOIN rb_okr_objective o1 on or1.ObjectiveId = o1.Id
WHERE  o1.`Status`=2 and  o1.Group_Id={group_Id}   and o1.PeriodId ={periodId} group by o1.CreateBy) as obj  on e.EmployeeId =obj.CreateBy
LEFT JOIN (SELECT  DATEDIFF(NOW(), MAX(UpdateTime)) as daysum,UserId from rb_okr_updateprogress  GROUP BY UserId) as okrp on e.EmployeeId=okrp.UserId
where e.RB_Group_id={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(userIds) ? "and e.EmployeeId in (" + userIds + ")" : "")}   {(!string.IsNullOrEmpty(EmployeeName) ? "and e.EmName like'%" + EmployeeName + "%'" : "")} group by e.EmployeeId
)t where 1=1  {orderbyInfo} ";
            var list= GetPage<OKRPersonalDimension>(pageIndex, pageSize, out count, sql).ToList();
            return list;
        }





        public List<OKRPersonalDimension> GetOKRGRDateDistributionDept( int periodId,  string deptIdStr, int group_Id)
        {
            string orderbyInfo = string.Empty;

            string sql = $@" 
select * from(
select e.EmployeeId AS Id,e.EmName as EmployeeName ,e.RB_Department_Id as  Dept_Id,e.EmPhoto as UserIcon,e.DeptName,e.PostName,SUM(CASE WHEN o.Id is null THEN 0 ELSE 1 END) as ObjectiveNCount ,
sum(case when o.Score>0 then 1 else 0 end) as ScoreCount,  IFNULL(obj.OBjectiveRelation,0) as  OBjectiveRelation,IFNULL(okrp.daysum,-1) as DayNum
from rb_employee e 
LEFT JOIN RB_OKR_Objective o on e.EmployeeId = o.CreateBy AND o.PeriodId ={periodId} AND o.Status =2 and  o.Group_Id={group_Id}
LEFT JOIN (SELECT COUNT(0) as OBjectiveRelation,o1.CreateBy FROM 
rb_okr_objectiverelation or1
LEFT JOIN rb_okr_objective o1 on or1.ObjectiveId = o1.Id
WHERE  o1.`Status`=2 and  o1.Group_Id={group_Id}   and o1.PeriodId ={periodId} group by o1.CreateBy) as obj  on e.EmployeeId =obj.CreateBy
LEFT JOIN (SELECT  DATEDIFF(NOW(), MAX(UpdateTime)) as daysum,UserId from rb_okr_updateprogress  GROUP BY UserId) as okrp on e.EmployeeId=okrp.UserId
where e.RB_Group_id={group_Id} and e.IsLeave =0 {(!string.IsNullOrEmpty(deptIdStr) ? "and e.RB_Department_Id in (" + deptIdStr + ")" : "")} group by e.EmployeeId
)t where 1=1  ";
            var list = Get<OKRPersonalDimension>(sql).ToList();
            return list;
        }
        #endregion
    }
}
