StaffPerformance/wispro.sp.api/Controllers/PerformanceItemController.cs

using DynamicExpresso;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using wispro.sp.api.Services;
using wispro.sp.api.Utility;
using wispro.sp.entity;
using wispro.sp.share;
using wispro.sp.utility;

namespace wispro.sp.api.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class PerformanceItemController : ControllerBase
    {
        spDbContext Context;
        IFileTaskService fileTaskService;


        public PerformanceItemController(spDbContext context, IFileTaskService _fileTaskService)
        {
            Context = context;
            fileTaskService = _fileTaskService;

        }

        public ApiSaveResponse RemoveDBNotFinishDate(int year, int month)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            var itemList = Context.PerformanceItems.Include(p => p.ItemStaffs).Where(p => p.CalMonth.Year == year &&
                                                      p.CalMonth.Month == month &&
                                                      p.DoItem == "处理审查意见" &&
                                                      p.FinishedDate == null);
            using var t = Context.Database.BeginTransaction();
            try
            {
                foreach (var item in itemList.ToList())
                {
                    if (item != null)
                    {
                        Context.ItemStaffs.RemoveRange(item.ItemStaffs);
                        Context.PerformanceItems.Remove(item);
                    }
                }

                Context.SaveChanges();
                t.Commit();
                return ret;
            }
            catch (Exception ex)
            {
                t.Rollback();
                ret.Success = false;
                ret.ErrorMessage = ex.Message;
                return ret;
            }
        }


        public bool IsExist(PerformanceItem item)
        {
            var results = Context.PerformanceItems.Where<PerformanceItem>(x =>
                       x.CaseNo == item.CaseNo &&
                       x.DoItem == item.DoItem &&
                       x.CaseStage == item.CaseStage &&
                       x.CalMonth.Year == item.CalMonth.Year &&
                       x.CalMonth.Month == item.CalMonth.Month);

            if (results.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public ApiSaveResponse New(PerformanceItem item)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            using (Context.Database.BeginTransaction())
            {
                try
                {
                    #region 已算绩效的案号只匹配案号前边的数字 20230310
                    string[] strNos = item.CaseNo.Split(new char[] {'-'});
                    string strNo = item.CaseNo;
                    if(strNos.Length > 0 )
                    {
                        strNo = strNos[0];
                        if(strNos.Length > 1)
                        {
                            if (strNos[1].ToUpper() == "TS")
                            {
                                strNo = $"{strNos[0]}-{strNos[1]}";
                            }
                        }
                        
                    }

                    var results = Context.PerformanceItems.Where<PerformanceItem>(x =>
                       x.CaseNo.StartsWith(strNo) && x.DoItem == item.DoItem && x.DoItem != "提出报告" && x.CaseStage == item.CaseStage);
                   

                    if (item.DoItem.ToUpper() == "提交IDS")
                    {
                        //提交IDS 添加完成日期做完判断是否已算绩效的条件
                        results = Context.PerformanceItems.Where<PerformanceItem>(x => x.CaseNo.StartsWith(strNo) && x.DoItem == item.DoItem && x.FinishedDate == item.FinishedDate);
                    }

                    #endregion

                    var items = results.Include(pi => pi.CalMonth).FirstOrDefault<PerformanceItem>();

                    if (items != null)
                    {

                        item.AgentFeedbackMemo = "已算绩效";
                        item.DoItemMemo = $"{items.DoItemMemo}\r\n{items.CalMonth.Year}-{items.CalMonth.Month}已计算！";
                        item.BasePoint = 0;
                    }

                    if (item.CalMonth != null)
                    {
                        var calMonth = Context.CalMonths.Where<CalMonth>(c => c.Year == item.CalMonth.Year && c.Month == item.CalMonth.Month).FirstOrDefault();

                        if (calMonth == null)
                        {
                            Context.CalMonths.Add(item.CalMonth);

                            if (item.BasePoint > 0)
                            {
                                _calItemJX(item, Context);
                            }
                            else
                            {
                                var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                                Context.StaffStatistics.RemoveRange(oldJxList);
                            }

                            Context.SaveChanges();
                        }
                        else
                        {
                            item.CalMonth = calMonth;
                        }

                        item.CalMonthId = item.CalMonth.Id;
                        item.CalMonth = null;
                    }

                    if (!string.IsNullOrEmpty(item.Customer.Name))
                    {
                        var temCustomer = Context.Customers.Where<Customer>(c => c.Name == item.Customer.Name).FirstOrDefault();

                        if (temCustomer == null)
                        {
                            temCustomer = new Customer() { Name = item.Customer.Name };
                            //item.Customer.Id = 0;

                            Context.Customers.Add(temCustomer);
                            Context.SaveChanges();
                            item.Customer = temCustomer;
                            //item.CustomerId = item.Customer.Id;
                        }
                        else
                        {
                            item.Customer = temCustomer;
                        }

                        item.CustomerId = item.Customer.Id;
                        item.Customer = null;
                    }
                    else
                    {
                        item.Customer = null;
                    }


                    var ItemStaffs = item.ItemStaffs;
                    item.ItemStaffs = null;

                    Context.PerformanceItems.Add(item);
                    Context.SaveChanges();

                    foreach (ItemStaff itemStaff in ItemStaffs)
                    {
                        itemStaff.ItemId = item.Id;
                        itemStaff.Item = null;

                        if (itemStaff.DoPersonId == 0 && itemStaff.DoPerson != null)
                        {
                            var temStaff = Context.Staffs.FirstOrDefault<Staff>(s => s.Name == itemStaff.DoPerson.Name);

                            if (temStaff != null)
                            {
                                itemStaff.DoPersonId = temStaff.Id;
                                itemStaff.DoPerson = null;
                            }
                            else
                            {
                                Context.Staffs.Add(itemStaff.DoPerson);
                                Context.SaveChanges();
                                itemStaff.DoPersonId = itemStaff.DoPerson.Id;
                                itemStaff.DoPerson = null;
                            }
                        }
                    }

                    Context.ItemStaffs.AddRange(ItemStaffs);
                    Context.SaveChanges();

                    Context.Database.CommitTransaction();
                }
                catch (Exception ex)
                {
                    ret.Success = false;
                    ret.ErrorMessage = ex.Message;
                    Context.Database.RollbackTransaction();
                }
            }


            return ret;
        }

        /// <summary>
        /// 更新绩效记录信息
        /// </summary>
        /// <param name="id">绩效记录编号</param>
        /// <param name="field">栏位，多个位以|杠隔开</param>
        /// <param name="value">栏位值，多个以|杠隔开</param>
        /// <returns></returns>
        public ApiSaveResponse UpdateFieldValue(int id, string field, string value)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            var item = Context.PerformanceItems.Include(p => p.Customer).FirstOrDefault<PerformanceItem>(p => p.Id == id);

            if (item == null)
            {
                ret.Success = false;
                ret.ErrorMessage = $"不存在的{id}";

                return ret;
            }

            if (string.IsNullOrEmpty(field))
            {
                ret.Success = false;
                ret.ErrorMessage = $"参数不对！";

                return ret;
            }

            string[] fields = field.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

            string[] values = new string[] { null };
            if (!string.IsNullOrEmpty(value))
            {
                values = value.Split(new char[] { '|' });
            }

            if (fields.Length != values.Length) {
                ret.Success = false;
                ret.ErrorMessage = "栏位和值对不匹配";
            }
            else
            {
                for (int i = 0; i < fields.Length; i++)
                {
                    string temField = fields[i];
                    string temValue = values[i];
                    switch (temField)
                    {
                        case "AgentFeedbackMemo":
                            item.AgentFeedbackMemo = temValue;

                            break;
                        case "CaseCoefficient":
                            item.CaseCoefficient = temValue;

                            //此处添加保存到流程系统的代码
                            break;
                        case "DoItemCoefficient":
                            item.DoItemCoefficient = temValue;

                            //此处添加保存到流程系统的代码
                            break;
                        case "WordCount":
                            int wordCount;
                            if (int.TryParse(temValue, out wordCount))
                            {
                                item.WordCount = wordCount;
                            }
                            else
                            {
                                item.WordCount = null;
                                //ret.Success = false;
                                //ret.ErrorMessage = "所给的栏位值不能转换成数字！";
                                //return ret;
                            }
                            break;
                        case "ReturnCasseNo":
                            item.ReturnCasseNo = temValue;
                            break;
                    }
                }

                if (item.AgentFeedbackMemo != "特殊点数申诉")
                {
                    Utility.Utility.CalBasePoint(item, Context.BasePointRules.ToList());

                    if (item.BasePoint > 0)
                    {
                        _calItemJX(item, Context);
                    }
                    else
                    {
                        var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                        Context.StaffStatistics.RemoveRange(oldJxList);
                    }

                    Context.SaveChanges();
                }
            }

            return ret;
        }
        public ListApiResponse<PerformanceItem> Query(int pageIndex, int pageSize)
        {
            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s =>
                    (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Name == User.Identity.Name).Count() > 0 || s.Reviewer.Name == User.Identity.Name)
                    && s.CalMonth.Status != 4);

            ret.TotalCount = results.Count();

            List<PerformanceItem> retList = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer)
                .Include(pi => pi.Customer)
                .Include(pi => pi.CalMonth)
                .OrderByDescending(o => o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize).ToList<PerformanceItem>();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {
                foreach (ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.Item = null;
                }

                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;

                item.Customer.PerformanceItems = null;
                item.CalMonth.PerformanceItems = null;
            }
            #endregion

            ret.Results = retList;

            return ret;
        }

        public PerformanceItem Get(int Id)
        {
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s => s.Id == Id);

            PerformanceItem item = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer)
                .Include(pi => pi.Customer)
                .Include(pi => pi.CalMonth)
                .OrderByDescending(o => o.Id)
                .FirstOrDefault();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (ItemStaff itemStaff in item.ItemStaffs)
            {
                itemStaff.DoPerson.ItemStaffs = null;
                itemStaff.DoPerson.ReviewerItems = null;
                itemStaff.Item = null;
            }

            if (item.Reviewer != null)
            {
                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;
            }

            item.Customer.PerformanceItems = null;
            item.CalMonth.PerformanceItems = null;
            #endregion

            return item;
        }
        /// <summary>
        /// 获取给定用户的绩效清单
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <param name="type">获取类型；0：处理中；1：所有；4：已归档</param>
        /// <returns></returns>
        public ListApiResponse<PerformanceItem> GetMyList(int userid, int type, int pageIndex = 1, int pageSize = 10)
        {

            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s =>
                    (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id == userid).Count() > 0 || s.Reviewer.Id == userid)
                    && s.CalMonth.Status == type);


            ret.TotalCount = results.Count();

            List<PerformanceItem> retList = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer)
                .Include(pi => pi.Customer)
                .Include(pi => pi.CalMonth)
                .OrderByDescending(o => o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize)
                .ToList<PerformanceItem>();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {
                foreach (ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.Item = null;
                }

                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;

                item.Customer.PerformanceItems = null;
                item.CalMonth.PerformanceItems = null;
            }
            #endregion

            ret.Results = retList;

            return ret;
        }

        /// <summary>
        /// 获取个人难度系数
        /// </summary>
        /// <param name="calMonth">绩效月份</param>
        /// <param name="userId">用户Id</param>
        /// <param name="isReivewer">是否审核个人难度系数</param>
        /// <param name="HXqf">合写算0.5件</param>
        /// <returns></returns>
        private NanduStatics PersonNanduStatics(CalMonth calMonth, int userId, spDbContext spDb, bool isReivewer = false,bool HXqf=false)
        {
            NanduStatics retObj = new NanduStatics();

            IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
            var list = spDb.CaseCeoffcients;

            foreach (var cx in list.ToList<CaseCeoffcient>())
            {
                CaseXiShu.Add(cx.Ceoffcient, cx.Value);
            }

            string jxType = isReivewer ? "新申请审核" : "新申请处理";

            var results = spDb.StaffStatistics.Include(p => p.Item).Where(s => s.CalMonthId == calMonth.Id && s.jxType == jxType && s.StaffId == userId);

            if (!isReivewer)
            {
                results = spDb.StaffStatistics.Include(p => p.Item).Where(s => s.CalMonthId == calMonth.Id && (s.jxType == jxType || s.jxType == "专案处理") && s.StaffId == userId);
            }

            #region 循环计算
            decimal  iCount = 0;
            decimal  d = 0.0M;
            var retList = results.ToList();

            foreach (var item in retList)
            {
                if (item.Item != null)
                {
                    //if(item.Item.CaseNo == "PACN2220066")
                    //{
                    //    System.Diagnostics.Debug.WriteLine("");
                    //}
                    //合写人数
                    decimal  CaseNumber = 1M;

                    if (item.Item.ItemStaffs.Count > 1 && !isReivewer )
                    {
                        if (item.Item .ItemStaffs == null)
                        {
                            item.Item .ItemStaffs = spDb.ItemStaffs.Include(p => p.DoPerson).Where(p => p.ItemId == item.ItemId).ToList();
                        }

                        try
                        {
                            CaseNumber = CaseNumber / (item.Item.ItemStaffs.Where(i => i.DoPerson.IsCalPerformsnce == true || (i.DoPerson.Status != "试用期" && i.DoPerson.IsCalPerformsnce == false)).Count());
                        }
                        catch { }
                    }

                    string strCaseCeoffcient = item.Item.CaseCoefficient;

                    if (string.IsNullOrEmpty(strCaseCeoffcient))
                    {
                        strCaseCeoffcient = "B";
                    }

                    #region 严重延期降系数
                    if (item.Item.isDanger() && string.IsNullOrEmpty(item.Item.OverDueMemo))
                    {
                        switch (item.Item.CaseCoefficient)
                        {
                            case "S":
                                strCaseCeoffcient = "A";
                                break;
                            case "A":
                                strCaseCeoffcient = "B";
                                break;
                            case "B":
                                strCaseCeoffcient = "C";
                                break;
                            case "C":
                                strCaseCeoffcient = "D";
                                break;
                        }
                    }
                    #endregion

                    switch (strCaseCeoffcient)
                    {
                        case "S":
                            if (item.Item.Type == "专案")
                            {
                                retObj.S += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.S += CaseNumber;
                            }

                            break;
                        case "A":
                            if (item.Item.Type == "专案")
                            {
                                retObj.A += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.A += CaseNumber;
                            }
                            break;
                        case "B":
                            if (item.Item.Type == "专案")
                            {
                                retObj.B += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.B += CaseNumber;
                            }
                            break;
                        case "C":
                            if (item.Item.Type == "专案")
                            {
                                retObj.C += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.C += CaseNumber;
                            }
                            break;
                        case "D":
                            if (item.Item.Type == "专案")
                            {
                                retObj.D += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.D += CaseNumber;
                            }
                            break;
                        default:
                            if (item.Item.Type == "专案")
                            {
                                retObj.B += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.B += CaseNumber;
                            }
                            break;

                    }

                    
                }

            }
            #endregion

            d = retObj.S * (decimal)CaseXiShu["S"] + retObj.A * (decimal)CaseXiShu["A"] + retObj.B * (decimal)CaseXiShu["B"] + retObj.C * (decimal)CaseXiShu["C"] + retObj.D * (decimal)CaseXiShu["D"];
            iCount = retObj.S + retObj.A + retObj.B + retObj.C + retObj.D;
            retObj.NanduXS =(double) (d / iCount);

            return retObj;

        }


        /// <summary>
        /// 获取总难度系数
        /// </summary>
        /// <param name="calMonth"></param>
        /// <returns></returns>
        private NanduStatics DegreeOfDifficulty(CalMonth calMonth, spDbContext spDb)
        {
            NanduStatics retObj = new NanduStatics();

            IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
            var list = spDb.CaseCeoffcients.ToList();

            foreach (var cx in list)
            {
                CaseXiShu.Add(cx.Ceoffcient, cx.Value);
            }

            //var results = spDb.PerformanceItems.Where<PerformanceItem>(p => 
            //p.CalMonthId == calMonth.Id && 
            //(p.Type == "新申请" || p.Type == "专案") && p.BasePoint > 0.0);

            var results = spDb.StaffStatistics
                .Where(p => 
                                    p.CalMonthId == calMonth.Id  && 
                                    (p.Item.Type == "新申请" || p.Item.Type == "专案"))
                .Select(p => p.Item).Distinct();

            #region 循环计算
            
            var retList = results.ToList();

            foreach (var item in retList)
            {
                decimal CaseNumber = 1;

                if(item.ItemStaffs == null)
                {
                    item.ItemStaffs = spDb.ItemStaffs.Include(p=>p.DoPerson).Where(p =>p.ItemId == item.Id).ToList();
                }
                if (item.ItemStaffs.Count > 1)
                {
                    var i = item.ItemStaffs.Where(i => (i.DoPerson.Status != "试用期" && i.DoPerson.IsCalPerformsnce == false)).Count();
                    if ( i> 0)
                    {
                        CaseNumber = CaseNumber *(item.ItemStaffs.Count-i)/item.ItemStaffs.Count;
                    }
                }

                string strCaseCeoffcient = item.CaseCoefficient;

                if (string.IsNullOrEmpty(strCaseCeoffcient))
                {
                    strCaseCeoffcient = "B";
                }

                #region 严重延期降系数
                if (item.isDanger() && string.IsNullOrEmpty(item.OverDueMemo))
                {
                    switch (item.CaseCoefficient)
                    {
                        case "S":
                            strCaseCeoffcient = "A";
                            break;
                        case "A":
                            strCaseCeoffcient = "B";
                            break;
                        case "B":
                            strCaseCeoffcient = "C";
                            break;
                        case "C":
                            strCaseCeoffcient = "D";
                            break;
                    }
                }
                #endregion

                switch (strCaseCeoffcient)
                {
                    case "S":
                        if (item.Type == "专案")
                        {
                            retObj.S += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.S += CaseNumber;
                        }

                        break;
                    case "A":
                        if (item.Type == "专案")
                        {
                            retObj.A += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.A += CaseNumber;
                        }
                        break;
                    case "B":
                        if (item.Type == "专案")
                        {
                            retObj.B += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.B += CaseNumber;
                        }
                        break;
                    case "C":
                        if (item.Type == "专案")
                        {
                            retObj.C += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.C += CaseNumber;
                        }
                        break;
                    case "D":
                        if (item.Type == "专案")
                        {
                            retObj.D += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.D += CaseNumber;
                        }
                        break;
                    default:
                        if (item.Type == "专案")
                        {
                            retObj.B += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.B += CaseNumber;
                        }
                        break;

                }


                System.Diagnostics.Debug.WriteLine($"{item.CaseNo}\t{item.DoItem}\t{item.DoItemCoefficient}\t{item.WordCount}\t{strCaseCeoffcient}\t{item.CaseCoefficient}\t{item.BasePoint}");

            }
            #endregion

            var d = retObj.S * (decimal)CaseXiShu["S"] + retObj.A * (decimal)CaseXiShu["A"] + retObj.B * (decimal)CaseXiShu["B"] + retObj.C * (decimal)CaseXiShu["C"] + retObj.D * (decimal)CaseXiShu["D"];
            var iCount = retObj.S + retObj.A + retObj.B + retObj.C + retObj.D; 
            retObj.NanduXS = (double)(d /iCount);

            return retObj;

        }

        public List<string> GetFeedbackString(int itemId)
        {
            PerformanceItem item = Context.PerformanceItems.FirstOrDefault<PerformanceItem>(p => p.Id == itemId);

            if (item != null)
            {
                return Utility.Utility.GetFeedbackMemos(item, Context.BasePointRules.ToList());
            }

            return new List<string>();
        }

        public FileProcessTask GetStaticsReport(int Year,int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}代理人全部考评维度绩效点数加和.xlsx";
                var attachfileSavePath = utility.ConfigHelper.GetSectionValue("AttachFileSavePath");
                var filePath = Path.Combine(attachfileSavePath, filename);

                var fileTask = new FileProcessTask()
                {
                    Id = Guid.NewGuid().ToString(),
                    FileName = filename,
                    FilePath = filePath,
                    Processed = 0
                };

                fileTaskService.Add(fileTask);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(ExportStaticReport));
                t.Start(result);

                return fileTask;
            }

            return null;
        }


        private void ExportStaticReport(object exportDataResult)
        {
            ExportDataResult result = (ExportDataResult)exportDataResult;
            FileProcessTask file = result.fileTask;
            CalMonth calMonth = result.calMonth;

            //var StatisticsResults = _CalMyStatistics(calMonth);
            string strSQL = @"SELECT  Staff.Name as 代理人,StaffGrade.Grade as 代理人等级,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '新申请处理' and CalMonthId = @CalMonthId ) as '新申请处理',
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '新申请审核' and CalMonthId = @CalMonthId) as '新申请审核',
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '专案处理' and CalMonthId = @CalMonthId) as '专案处理',
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '专案审核' and CalMonthId = @CalMonthId) as '专案审核',
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = 'OA处理' and CalMonthId = @CalMonthId) as OA处理,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = 'OA审核' and CalMonthId = @CalMonthId) as OA审核,	  
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '其它处理' and CalMonthId = @CalMonthId) as 其它处理,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '其它审核' and CalMonthId = @CalMonthId) as 其它审核,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId]  and CalMonthId = @CalMonthId) as 总计
              FROM[spDB].[dbo].[StaffStatistics] as A
              inner join Staff on Staff.Id = A.StaffId
              Left join StaffGrade on Staff.StaffGradeId = StaffGrade.Id
              where CalMonthId = @CalMonthId
              group by A.StaffId, Staff.Name,StaffGrade.Grade";

            DataTable dt = new DataTable();
            spDbContext spDb = new spDbContext();
            using (var conn = spDb.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.Text;
                    
                cmd.Parameters.Add(new SqlParameter("CalMonthId", calMonth.Id));
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }

            NPOIExcel.DataTableToExcel(dt, result.fileTask.FilePath);
            result.fileTask.Finished = true;
        }

        private List<StaffStatistics> _CalMyStatistics(CalMonth calMonth, int? userid = null)
        {
            var retList = Context.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id);

            if (userid != null)
            {
                retList = retList.Where(s => s.StaffId == userid);
            }

            var retData2 = retList.GroupBy(s => new { s.CalMonthId, s.StaffId, s.jxType })
                            .Select(g => new
                            {
                                StaffId = g.Key.StaffId,
                                CalMonthId = g.Key.CalMonthId,
                                jxType = g.Key.jxType,
                                FinianlPoint = g.Sum(s => s.FinianlPoint),
                                totalBasePoint = g.Sum(s => s.totalBasePoint),
                                totalActuallyPoint = g.Sum(s => s.totalActuallyPoint)
                            });

            List<StaffStatistics> temList = new List<StaffStatistics>();
            foreach (var key in retData2)
            {
                temList.Add(new StaffStatistics()
                {
                    StaffId = key.StaffId,
                    CalMonthId = key.CalMonthId,
                    jxType = key.jxType,
                    totalBasePoint = key.totalBasePoint,
                    totalActuallyPoint = key.totalActuallyPoint,
                    FinianlPoint = key.FinianlPoint

                });
            }

            return temList;

            #region old code
            ////未归档，从绩效记录中统计数据
            //var results = Context.PerformanceItems.Where<PerformanceItem>(s => s.CalMonth.Id == calMonth.Id);

            //if (userid != null)
            //{
            //    results = Context.PerformanceItems.Where<PerformanceItem>(s =>
            //        (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id == userid).Count() > 0 || s.Reviewer.Id == userid)
            //        && s.CalMonth.Id == calMonth.Id);
            //}

            //List<PerformanceItem> ItemList = results
            //    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
            //    .Include(pi => pi.Reviewer).ThenInclude(p=>p.StaffGrade)
            //    .Include(pi => pi.Customer)
            //    .OrderByDescending(o => o.Id)
            //    .ToList<PerformanceItem>();

            //List<StaffStatistics> retList = new List<StaffStatistics>();
            //List<VerifyCoefficient> verifyCoefficients = Context.VerifyCoefficients.ToList<VerifyCoefficient>();

            //var Rules = Context.BasePointRules.ToList();

            //foreach (PerformanceItem item in ItemList)
            //{

            //    //if (item.BasePoint == null)
            //    //{

            //    if (item.AgentFeedbackMemo != "特殊点数申诉")
            //    {
            //        Utility.Utility.CalBasePoint(item, Rules);

            //        Context.SaveChanges();
            //    }
            //    //}

            //    if (item.BasePoint != null && item.BasePoint.Value > 0)
            //    {
            //        double doPersonBasePoint = item.BasePoint.Value;

            //        List<StaffStatistics> itemStatistics = _calItemJX(calMonth, verifyCoefficients, item,Context);

            //        List<StaffStatistics> temItemStatics;

            //        if (userid != null)
            //        {
            //            temItemStatics = itemStatistics.Where<StaffStatistics>(s => s.StaffId == userid).ToList();
            //        }
            //        else
            //        {
            //            temItemStatics = itemStatistics;
            //        }

            //        foreach (StaffStatistics retUserValue in temItemStatics)
            //        {
            //            var temValue = retList.Where<StaffStatistics>(s => s.StaffId == retUserValue.StaffId && s.jxType == retUserValue.jxType && s.CalMonthId == calMonth.Id).FirstOrDefault();
            //            if (temValue != null)
            //            {
            //                temValue.totalBasePoint += retUserValue.totalBasePoint;
            //                temValue.totalActuallyPoint += retUserValue.totalActuallyPoint;
            //            }
            //            else
            //            {
            //                retList.Add(retUserValue);
            //            }
            //        }
            //    }

            //}

            //if (userid != null)
            //{
            //    retList = retList.Where<StaffStatistics>(s => s.StaffId == userid.Value).ToList();
            //}

            //return retList;
            #endregion

        }

        private void _RefreshBasePoint()
        {
            spDbContext spDb = new spDbContext();

            var calMonth = spDb.CalMonths.FirstOrDefault(c => c.Status == 0);
            _RefreshBasePoint(calMonth, spDb);

            StatisticsLevelCount(calMonth.Year, calMonth.Month);

        }

        private void _RefreshBasePoint(CalMonth calMonth, spDbContext spDb)
        {
            if (calMonth != null)
            {
                var lstItem = spDb.PerformanceItems
                    .Include(p => p.Customer)
                    .Include(p => p.ItemStaffs)
                    .Include(p => p.Reviewer)
                    .Include(p => p.CalMonth)
                    .Where(p => p.CalMonthId == calMonth.Id).ToList();

                var rules = spDb.BasePointRules.ToList();

                foreach (var item in lstItem)
                {
                    try
                    {
                        Utility.Utility.CalBasePoint(item, rules);

                        if (item.BasePoint > 0)
                        {
                            _calItemJX(item, spDb);
                        }
                        else
                        {
                            var oldJxList = spDb.StaffStatistics.Where(p => p.ItemId == item.Id);
                            spDb.StaffStatistics.RemoveRange(oldJxList);
                        }

                        spDb.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                    }
                }

                _CalJXPoint(calMonth, spDb);
            }
        }

        public ApiSaveResponse RefreshBasePoint()
        {
            System.Threading.Thread t = new Thread(_RefreshBasePoint);
            t.Start();

            return new ApiSaveResponse() {
                Success = true
            };
        }

        public void RefreshPoint(PerformanceItem item)
        {
            //spDbContext spDb = new spDbContext();

            var rules = Context.BasePointRules.ToList();

            if (item.AgentFeedbackMemo != "特殊点数申诉")
            {
                
                Utility.Utility.CalBasePoint(item, rules);

                if (item.BasePoint > 0)
                {
                    _calItemJX(item, Context);
                }
                else
                {
                    var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                    Context.StaffStatistics.RemoveRange(oldJxList);
                }

                Context.SaveChanges();
            }

        }

        public ApiSaveResponse RefreshFromIPEasyById(int itemId)
        {
            var Item = Context.PerformanceItems.Include(p => p.Customer).Include(p => p.ItemStaffs).ThenInclude(p => p.DoPerson).FirstOrDefault(p => p.Id == itemId);
            if (Item != null)
            {
                new Job.UpdateJXDataFromIPEasyJob().UpdateFromIPEasy(Item, Context);
            }

            Utility.Utility.CalBasePoint(Item, Context.BasePointRules.ToList());

            if (Item.BasePoint > 0)
            {
                _calItemJX(Item, Context);
            }
            else
            {
                var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == Item.Id);
                Context.StaffStatistics.RemoveRange(oldJxList);
            }
            Context.SaveChanges();

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse RefreshFromIPEasy_Batch(int type)
        {
            new Job.UpdateJXDataFromIPEasyJob().RefreshFromIPEasy(type);

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse RefreshFromIPEasy(string CaseNo, string DoItem, string caseStage)
        {
            var Item = Context.PerformanceItems.Include(p => p.Customer).FirstOrDefault(p => p.CaseNo == CaseNo && p.DoItem == DoItem && p.CaseStage == caseStage);
            if (Item != null)
            {
                new Job.UpdateJXDataFromIPEasyJob().UpdateFromIPEasy(Item, Context);
            }

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse CompareExcel2DB()
        {

            System.Threading.Thread t = new Thread(new ThreadStart(_CompareExcel2DB));
            t.Start();

            return new ApiSaveResponse()
            {
                Success = true
            };
        }



        public FileProcessTask CurrentData2Excel(int Year,int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}线下绩效核算数据.xlsx";
                var attachfileSavePath = utility.ConfigHelper.GetSectionValue("AttachFileSavePath");
                var filePath = Path.Combine(attachfileSavePath, filename);

                var fileTask = new FileProcessTask()
                {
                    Id = Guid.NewGuid().ToString(),
                    FileName = filename,
                    FilePath = filePath,
                    Processed = 0
                };

                fileTaskService.Add(fileTask);

                ExportDataResult result = new ExportDataResult() {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(Export2ExcelThread));
                t.Start(result);

                return fileTask;
            }

            return null;

        }

        internal class ExportDataResult
        {
            public FileProcessTask fileTask { get; set; }
            public CalMonth calMonth { get; set; }
        }

        private void Export2ExcelThread(object attfile)
        {
            ExportDataResult result = (ExportDataResult)attfile;
            FileProcessTask file = result.fileTask;

            spDbContext spDb = new spDbContext();
            List<PerformanceItem> items = spDb.PerformanceItems
                .Include(p => p.Reviewer).ThenInclude(p => p.StaffGrade)
                .Include(p => p.PreOastaff)
                .Include(p => p.Customer)
                .Include(p => p.CalMonth)
                .Include(p => p.ItemStaffs).ThenInclude(s => s.DoPerson).ThenInclude(s => s.StaffGrade)
                .Where(p => p.CalMonth.Id == result.calMonth.Id && !p.CaseNo.StartsWith("J")).OrderBy(p => p.CaseNo).ThenBy(p => p.DoItem).ToList();

            DataTable dt = new DataTable();
            #region 栏位名称
            dt.Columns.Add("我方文号", typeof(string));
            dt.Columns.Add("申请类型", typeof(string));
            dt.Columns.Add("业务类型", typeof(string));
            dt.Columns.Add("备注（填表注意事项）", typeof(string));
            dt.Columns.Add("处理事项", typeof(string));
            dt.Columns.Add("案件阶段", typeof(string));
            dt.Columns.Add("案件系数", typeof(string));
            dt.Columns.Add("处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理人", typeof(string));
            dt.Columns.Add("处理人等级", typeof(string));
            dt.Columns.Add("基本点数", typeof(string));
            dt.Columns.Add("核稿系数", typeof(string));
            dt.Columns.Add("核稿绩效", typeof(string));
            dt.Columns.Add("处理人", typeof(string));
            dt.Columns.Add("核稿人", typeof(string));
            dt.Columns.Add("客户名称", typeof(string));
            dt.Columns.Add("申请人", typeof(string));
            dt.Columns.Add("处理事项完成日", typeof(string));
            dt.Columns.Add("定稿日", typeof(string));
            dt.Columns.Add("返稿日", typeof(string));
            dt.Columns.Add("案件类型", typeof(string));
            dt.Columns.Add("案件状态", typeof(string));
            dt.Columns.Add("处理事项备注", typeof(string));
            dt.Columns.Add("处理状态", typeof(string));
            dt.Columns.Add("案件名称", typeof(string));
            dt.Columns.Add("委案日期", typeof(string));
            dt.Columns.Add("客户期限", typeof(string));
            dt.Columns.Add("内部期限", typeof(string));
            dt.Columns.Add("初稿日", typeof(string));
            dt.Columns.Add("翻译字数", typeof(string));
            dt.Columns.Add("备注（发文严重超期是否属客观原因，若为否，请填写原因", typeof(string));
            dt.Columns.Add("绩效类型", typeof(string));
            dt.Columns.Add("案件备注", typeof(string));
            dt.Columns.Add("处理人绩效系数", typeof(string));
            dt.Columns.Add("系统核算绩效", typeof(string));
            dt.Columns.Add("实际处理人", typeof(string));

            #endregion

            var verifyCeoffients = spDb.VerifyCoefficients.ToList();
            var Rules = spDb.BasePointRules.ToList();

            file.Size = items.Count;
            foreach (var p in items)
            {


                List<ItemStaff> temItemStaffs = p.ItemStaffs.ToList();

                string strCaseMemo = "";
                string strHandler = "";

                temItemStaffs = new List<ItemStaff>();
                foreach (var iStaff in p.ItemStaffs)
                {
                    strHandler = string.IsNullOrEmpty(strHandler) ? iStaff.DoPerson.Name : $"{strHandler},{iStaff.DoPerson.Name}";
                    if (iStaff.DoPerson.Status == "试用期")
                    {
                        strCaseMemo = string.IsNullOrEmpty(strCaseMemo) ? $"{iStaff.DoPerson.Name}在试用期" : $"{strCaseMemo},{iStaff.DoPerson.Name}在试用期";
                    }
                    else
                    {
                        if (!iStaff.DoPerson.IsCalPerformsnce)
                            strCaseMemo = string.IsNullOrEmpty(strCaseMemo) ? $"{iStaff.DoPerson.Name}不计算绩效" : $"{strCaseMemo},{iStaff.DoPerson.Name}不计算绩效";
                    }

                    if (iStaff.DoPersonId == p.ReviewerId)
                    {
                        strCaseMemo = String.IsNullOrEmpty(strCaseMemo) ? "核稿人与处理人相同" : $"{strCaseMemo},核稿人与处理人相同";
                    }
                }



                var staffStatics = spDb.StaffStatistics.Include(s => s.Staff).ThenInclude(s => s.StaffGrade).Where(s => s.ItemId == p.Id && s.jxType.Contains("处理"));
                var reviewStatic = spDb.StaffStatistics.Include(s => s.Staff).ThenInclude(s => s.StaffGrade).FirstOrDefault(s => s.ItemId == p.Id && s.jxType.Contains("审核"));

                foreach (var iStaff in staffStatics.ToList())
                {
                    DataRow row = dt.NewRow();
                    row["我方文号"] = p.CaseNo;
                    row["申请类型"] = p.ApplicationType;
                    row["实际处理人"] = strHandler;

                    if (p.ApplicationName != null && p.ApplicationName.Contains("OPPO") && p.ApplicationType == "实用新型" && p.Type == "新申请")
                    {
                        row["申请类型"] = "发明";
                        row["案件备注"] = $"{row["案件备注"]}\r\n【OPPO案件实用新型修改为发明】";
                    }
                    row["处理事项"] = p.DoItem;
                    row["业务类型"] = p.BusinessType;
                    row["备注（填表注意事项）"] = p.AgentFeedbackMemo;

                    if (p.Type == "专案")
                    {
                        row["备注（填表注意事项）"] = $"{row["备注（填表注意事项）"]}【主管给定点数】专案";
                        row["处理事项"] = "提出报告";

                    }

                    if (p.ItemStaffs.Count() > 1)
                    {
                        row["备注（填表注意事项）"] = $"{row["备注（填表注意事项）"]},{strHandler}合写";
                    }

                    row["案件阶段"] = p.CaseStage;

                    row["案件系数"] = p.CaseCoefficient;
                    if (p.isDanger() && string.IsNullOrEmpty(p.OverDueMemo) && p.Type == "新申请")
                    {
                        switch (p.CaseCoefficient)
                        {
                            case "S":
                                row["案件系数"] = "A";
                                break;
                            case "A":
                                row["案件系数"] = "B";
                                break;
                            case "B":
                                row["案件系数"] = "C";
                                break;
                            case "C":
                                row["案件系数"] = "D";
                                break;
                        }

                        row["案件备注"] = $"{row["案件备注"]}\r\n严重延期降系数【{p.CaseCoefficient}->{row["案件系数"]}】";
                    }

                    row["处理事项系数"] = p.DoItemCoefficient;
                    row["前一次OA处理事项系数"] = "";
                    row["前一次OA处理人"] = p.PreOastaff?.Name;
                    row["处理人"] = iStaff.Staff.Name;
                    row["基本点数"] = iStaff.totalBasePoint;
                    row["处理人等级"] = iStaff.Staff.StaffGrade.Grade;
                    row["处理人绩效系数"] = iStaff.Staff.StaffGrade.Coefficient;

                    if (reviewStatic != null)
                    {
                        row["核稿人"] = reviewStatic.Staff.Name;

                        var v = verifyCeoffients.FirstOrDefault(p => p.CheckerId == reviewStatic.Staff.StaffGradeId && p.DoPersonId == iStaff.Staff.StaffGradeId);
                        if (v != null)
                        {
                            row["核稿绩效"] = ((decimal)iStaff.totalBasePoint * (decimal)v.Coefficient);
                            row["核稿系数"] = v.Coefficient;
                        }


                    }

                    row["客户名称"] = p.Customer?.Name;
                    row["申请人"] = p.ApplicationName;
                    row["处理事项完成日"] = p.FinishedDate?.ToString("yyyy-MM-dd");
                    row["定稿日"] = p.FinalizationDate?.ToString("yyyy-MM-dd");
                    row["返稿日"] = p.ReturnDate?.ToString("yyyy-MM-dd");
                    row["案件类型"] = p.CaseType;
                    row["案件状态"] = p.CaseState;
                    row["处理事项备注"] = p.DoItemMemo;
                    row["处理状态"] = p.DoItemState;
                    row["案件名称"] = p.CaseName;
                    row["委案日期"] = p.EntrustingDate?.ToString("yyyy-MM-dd");
                    row["客户期限"] = p.CustomerLimitDate?.ToString("yyyy-MM-dd");
                    row["内部期限"] = p.InternalDate?.ToString("yyyy-MM-dd");
                    row["初稿日"] = p.FirstDraftDate?.ToString("yyyy-MM-dd");
                    row["翻译字数"] = p.WordCount;
                    row["备注（发文严重超期是否属客观原因，若为否，请填写原因"] = p.OverDueMemo;
                    row["绩效类型"] = p.Type;
                    row["案件备注"] = $"{p.CaseMemo}\r\n{row["案件备注"]}";
                    if (!string.IsNullOrEmpty(strCaseMemo))
                    {
                        row["案件备注"] = $"{strCaseMemo}\r\n{row["案件备注"]}";
                    }

                    dt.Rows.Add(row);
                }

                file.Processed += 1;

            }

            NPOIExcel.DataTableToExcel(dt, file.FilePath);
            file.Finished = true;
        }

        private void _CompareExcel2DB()
        {

            DataTable excelDT = NPOIExcel.ExcelToDataTable("c:\\temp\\220112-工程师绩效总表-12月-v2F.xlsx", true, true, 0, 2);
            DataTable retTable = new DataTable();
            retTable.Columns.Add("我方文号");
            retTable.Columns.Add("申请类型");
            retTable.Columns.Add("业务类型");
            retTable.Columns.Add("备注（填表注意事项）");
            retTable.Columns.Add("备注（填表注意事项）【系统】");
            retTable.Columns.Add("处理事项");
            retTable.Columns.Add("处理事项【系统】");
            retTable.Columns.Add("案件阶段");
            retTable.Columns.Add("案件阶段【系统】");
            retTable.Columns.Add("案件系数");
            retTable.Columns.Add("案件系数【系统】");
            retTable.Columns.Add("处理事项系数");
            retTable.Columns.Add("处理事项系数【系统】");
            retTable.Columns.Add("处理人等级");
            retTable.Columns.Add("处理人等级【系统】");
            retTable.Columns.Add("处理人系数");
            retTable.Columns.Add("处理人系数【系统】");
            retTable.Columns.Add("基本点数");
            retTable.Columns.Add("基本点数【系统】");
            retTable.Columns.Add("核稿系数");
            retTable.Columns.Add("核稿系数【系统】");
            retTable.Columns.Add("核稿绩效");
            retTable.Columns.Add("核稿绩效【系统】");
            retTable.Columns.Add("处理人");
            retTable.Columns.Add("处理人【系统】");
            retTable.Columns.Add("核稿人");
            retTable.Columns.Add("核稿人【系统】");
            retTable.Columns.Add("严重超期备注原因");
            retTable.Columns.Add("严重超期备注原因【系统】");

            retTable.Columns.Add("是否一致");
            retTable.Columns.Add("不一致原因");

            spDbContext spDb = new spDbContext();
            List<PerformanceItem> items = spDb.PerformanceItems
                .Include(p => p.Reviewer).ThenInclude(p => p.StaffGrade)
                .Include(p => p.Customer)
                .Include(p => p.ItemStaffs).ThenInclude(s => s.DoPerson).ThenInclude(s => s.StaffGrade)
                .Where(p => p.CalMonth.Status == 0 && !p.CaseNo.StartsWith("J")).OrderBy(p => p.CaseNo).ThenBy(p => p.DoItem).ToList();

            excelDT.DefaultView.Sort = "我方文号,处理事项";
            DataTable temDt = excelDT.DefaultView.ToTable();

            int iTable = 0;
            int iList = 0;

            CalMonth calMonth = spDb.CalMonths.FirstOrDefault(p => p.Status == 0);
            var verifyCoefficients = spDb.VerifyCoefficients.ToList();
            var Rules = spDb.BasePointRules.ToList();
            while (iTable < temDt.Rows.Count && iList < items.Count)
            {
                System.Diagnostics.Debug.WriteLine($"Excel：{iTable}/{temDt.Rows.Count}\t{iList}/{items.Count}");

                DataRow row = temDt.Rows[iTable];
                PerformanceItem item = items[iList];

                if (row["我方文号"].ToString() == item.CaseNo && (row["处理事项"].ToString() == item.DoItem || (row["备注（填表注意事项）"].ToString() == "发明一次OA授权" && item.DoItem == "发明一次OA授权")))
                {
                    var temRow = retTable.NewRow();

                    int iBegin = iTable;
                    int iEnd = iTable;

                    #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                    if (iEnd < temDt.Rows.Count - 1)
                    {
                        while (temDt.Rows[iEnd]["我方文号"].ToString() == temDt.Rows[iEnd + 1]["我方文号"].ToString() &&
                            temDt.Rows[iEnd]["处理事项"].ToString() == temDt.Rows[iEnd + 1]["处理事项"].ToString())
                        {
                            iEnd++;
                        }
                    }

                    temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                    temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                    temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                    temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                    temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                    temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                    temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                    temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                    temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                    temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                    temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                    temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                    temRow["处理人"] = temDt.Rows[iTable]["处理人"];

                    temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                    for (int i = iBegin; i <= iEnd; i++)
                    {
                        temRow["备注（填表注意事项）"] = string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ? temDt.Rows[i]["备注（填表注意事项）"] : $"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                        //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";
                        temRow["核稿系数"] = string.IsNullOrEmpty(temRow["核稿系数"].ToString()) ? temDt.Rows[i]["核稿系数"] : $"{temRow["核稿系数"]},{temDt.Rows[i]["核稿系数"]}";
                        if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                        {
                            temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                            temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                        }
                        temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"]}";
                    }

                    iTable = iEnd;
                    #endregion

                    Utility.Utility.CalBasePoint(item, Rules);

                    List<StaffStatistics> retPoints = new List<StaffStatistics>();
                    try
                    {
                        retPoints = _calItemJX(verifyCoefficients, item, spDb);
                    }
                    catch { }


                    temRow["案件阶段【系统】"] = item.CaseStage;
                    temRow["案件系数【系统】"] = item.CaseCoefficient;
                    temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                    temRow["核稿人【系统】"] = item.Reviewer?.Name;
                    temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                    temRow["基本点数【系统】"] = item.BasePoint?.ToString();
                    temRow["严重超期备注原因"] = item.OverDueMemo;

                    if (retPoints != null && retPoints.Count > 0)
                    {
                        temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId && p.jxType.Contains("审核"))?.totalBasePoint;

                        foreach (var itemStaff in item.ItemStaffs)
                        {
                            //temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";

                            temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                            temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                            if (item.ReviewerId != null)
                            {
                                #region 取审核人等级审核等级系数
                                VerifyCoefficient vcoefficient
                                    = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                        v.CheckerId == item.Reviewer.StaffGradeId
                                        && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                                .FirstOrDefault<VerifyCoefficient>();
                                #endregion

                                if (vcoefficient != null)
                                {
                                    temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                                }
                            }

                        }
                    }

                    temRow["是否一致"] = "";
                    temRow["不一致原因"] = "";

                    if (temRow["案件阶段【系统】"].ToString().Trim() != temRow["案件阶段"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "案件阶段" : $"{temRow["不一致原因"]},案件阶段";
                    }
                    if (temRow["案件系数【系统】"].ToString().Trim() != temRow["案件系数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "案件系数" : $"{temRow["不一致原因"]},案件系数";
                    }
                    if (temRow["处理事项系数【系统】"].ToString().Trim() != temRow["处理事项系数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "处理事项系数" : $"{temRow["不一致原因"]},处理事项系数";
                    }
                    if (temRow["核稿人【系统】"].ToString().Trim() != temRow["核稿人"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "核稿人" : $"{temRow["不一致原因"]},核稿人";
                    }

                    if (temRow["基本点数【系统】"].ToString().Trim() != temRow["基本点数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "基本点数" : $"{temRow["不一致原因"]},基本点数";
                    }
                    if (temRow["严重超期备注原因【系统】"].ToString().Trim() != temRow["严重超期备注原因"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "严重超期备注原因" : $"{temRow["不一致原因"]},严重超期备注原因";
                    }

                    if (!string.IsNullOrEmpty(temRow["不一致原因"].ToString()))
                    {
                        temRow["是否一致"] = "不一致";
                    }

                    retTable.Rows.Add(temRow);

                    iTable++;
                    iList++;
                }
                else
                {
                    string strDT = $"{row["我方文号"]}-{row["处理事项"]}";
                    string strList = $"{item.CaseNo}-{item.DoItem}";

                    if (strDT.CompareTo(strList) > 0)
                    {
                        var temRow = retTable.NewRow();

                        Utility.Utility.CalBasePoint(item, Rules);

                        List<StaffStatistics> retPoints = new List<StaffStatistics>();
                        try
                        {
                            retPoints = _calItemJX(verifyCoefficients, item, spDb);
                        }
                        catch { }

                        temRow["我方文号"] = item.CaseNo;
                        temRow["处理事项"] = item.DoItem;
                        temRow["申请类型"] = item.ApplicationType;
                        temRow["业务类型"] = item.BusinessType;
                        temRow["案件阶段【系统】"] = item.CaseStage;
                        temRow["案件系数【系统】"] = item.CaseCoefficient;
                        temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                        temRow["核稿人【系统】"] = item.Reviewer?.Name;
                        temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                        temRow["基本点数【系统】"] = item.BasePoint?.ToString();
                        temRow["严重超期备注原因"] = item.OverDueMemo;

                        if (item.ReviewerId != null)
                        {
                            temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId)?.totalBasePoint;
                        }

                        foreach (var itemStaff in item.ItemStaffs)
                        {
                            //if (itemStaff.DoPerson.Status != "试用期")
                            //{
                            //    if (retPoints != null && retPoints.Count > 0)
                            //    {
                            //        temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";
                            //    }
                            //}


                            temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                            temRow["处理人系数【系统】"] = string.IsNullOrEmpty(temRow["处理人系数【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Coefficient : $"{temRow["处理人系数【系统】"]},{itemStaff.DoPerson.StaffGrade?.Coefficient}";
                            temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                            if (item.ReviewerId != null)
                            {
                                #region 取审核人等级审核等级系数
                                VerifyCoefficient vcoefficient
                                    = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                        v.CheckerId == item.Reviewer.StaffGradeId
                                        && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                                .FirstOrDefault<VerifyCoefficient>();
                                #endregion

                                if (vcoefficient != null)
                                {
                                    temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                                }
                            }

                        }

                        temRow["是否一致"] = "不一致";
                        temRow["不一致原因"] = "Excel中没有，系统中有";

                        retTable.Rows.Add(temRow);

                        iList++;
                    }
                    else
                    {
                        var temRow = retTable.NewRow();

                        int iBegin = iTable;
                        int iEnd = iTable;

                        #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                        if (iEnd < temDt.Rows.Count - 1)
                        {
                            while (temDt.Rows[iEnd]["我方文号"].ToString() == temDt.Rows[iEnd + 1]["我方文号"].ToString() &&
                            temDt.Rows[iEnd]["处理事项"].ToString() == temDt.Rows[iEnd + 1]["处理事项"].ToString())
                            {
                                iEnd++;
                            }
                        }

                        temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                        temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                        temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                        temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                        temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                        temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                        temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                        temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                        temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                        temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                        temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                        temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                        temRow["核稿绩效"] = temDt.Rows[iTable]["核稿绩效"];
                        temRow["处理人"] = temDt.Rows[iTable]["处理人"];

                        temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                        for (int i = iBegin + 1; i <= iEnd; i++)
                        {
                            temRow["备注（填表注意事项）"] = string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ? temDt.Rows[i]["备注（填表注意事项）"] : $"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                            //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";

                            temRow["核稿绩效"] = string.IsNullOrEmpty(temRow["核稿绩效"].ToString()) ? temDt.Rows[i]["核稿绩效"] : $"{temRow["核稿绩效"]},{temDt.Rows[i]["核稿绩效"]}";
                            if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                            {
                                temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                                temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                            }
                            temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"]}";
                        }

                        iTable = iEnd;
                        #endregion

                        temRow["是否一致"] = "不一致";
                        temRow["不一致原因"] = "系统中没有，Excel中有";
                        retTable.Rows.Add(temRow);
                        iTable++;
                    }

                }
            }

            if (iList <= items.Count)
            {
                while (iList < items.Count) {
                    var item = items[iList];
                    var temRow = retTable.NewRow();

                    Utility.Utility.CalBasePoint(item, Rules);

                    List<StaffStatistics> retPoints = new List<StaffStatistics>();
                    try
                    {
                        retPoints = _calItemJX(verifyCoefficients, item, spDb);
                    }
                    catch { }

                    temRow["我方文号"] = item.CaseNo;
                    temRow["处理事项"] = item.DoItem;
                    temRow["申请类型"] = item.ApplicationType;
                    temRow["业务类型"] = item.BusinessType;
                    temRow["案件阶段【系统】"] = item.CaseStage;
                    temRow["案件系数【系统】"] = item.CaseCoefficient;
                    temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                    temRow["核稿人【系统】"] = item.Reviewer?.Name;
                    temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                    temRow["基本点数【系统】"] = "";// item.BasePoint?.ToString();
                    temRow["严重超期备注原因"] = item.OverDueMemo;

                    if (item.ReviewerId != null)
                    {
                        temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId)?.totalBasePoint;
                    }

                    foreach (var itemStaff in item.ItemStaffs)
                    {
                        //if (itemStaff.DoPerson.Status != "试用期")
                        //{
                        //    if (retPoints != null && retPoints.Count > 0)
                        //    {
                        //        temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";
                        //    }
                        //}


                        temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                        temRow["处理人系数【系统】"] = string.IsNullOrEmpty(temRow["处理人系数【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Coefficient : $"{temRow["处理人系数【系统】"]},{itemStaff.DoPerson.StaffGrade?.Coefficient}";
                        temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                        if (item.ReviewerId != null)
                        {
                            #region 取审核人等级审核等级系数
                            VerifyCoefficient vcoefficient
                                = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                    v.CheckerId == item.Reviewer.StaffGradeId
                                    && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                            .FirstOrDefault<VerifyCoefficient>();
                            #endregion

                            if (vcoefficient != null)
                            {
                                temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                            }
                        }

                    }

                    temRow["是否一致"] = "不一致";
                    temRow["不一致原因"] = "Excel中没有，系统中有";

                    iList++;
                    retTable.Rows.Add(temRow);

                }
            }
            else
            {
                while (iTable < temDt.Rows.Count)
                {
                    var temRow = retTable.NewRow();

                    int iBegin = iTable;
                    int iEnd = iTable;

                    #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                    while (temDt.Rows[iTable]["我方文号"].ToString() == temDt.Rows[iTable + 1]["我方文号"].ToString() &&
                        temDt.Rows[iTable]["处理事项"].ToString() == temDt.Rows[iTable + 1]["处理事项"].ToString())
                    {
                        iEnd++;
                    }

                    temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                    temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                    temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                    temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                    temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                    temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                    temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                    temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                    temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                    temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                    temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                    temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                    temRow["核稿绩效"] = temDt.Rows[iTable]["核稿绩效"];
                    temRow["处理人"] = temDt.Rows[iTable]["处理人"];

                    temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                    for (int i = iBegin + 1; i <= iEnd; i++)
                    {
                        temRow["备注（填表注意事项）"] = string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ? temDt.Rows[i]["备注（填表注意事项）"] : $"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                        //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";

                        temRow["核稿绩效"] = string.IsNullOrEmpty(temRow["核稿绩效"].ToString()) ? temDt.Rows[i]["核稿绩效"] : $"{temRow["核稿绩效"]},{temDt.Rows[i]["核稿绩效"]}";

                        if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                        {
                            temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                            temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                        }

                        temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["严重超期备注原因"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["严重超期备注原因"]}";
                    }

                    iTable = iEnd;
                    #endregion

                    temRow["是否一致"] = "不一致";
                    temRow["不一致原因"] = "系统中没有，Excel中有";
                    retTable.Rows.Add(temRow);
                    iTable++;
                }
            }


            NPOIExcel.DataTableToExcel(retTable, "c:\\temp\\202112-系统线下绩效记录对比.xlsx");


        }



        public List<StaffStatistics> CalItemJX(int itemid)
        {
            var Item = Context.PerformanceItems.Include(p => p.CalMonth)
                .Include(p => p.ItemStaffs).ThenInclude(p => p.DoPerson).ThenInclude(s => s.StaffGrade)
                .Include(p => p.Reviewer)
                .Include(p => p.Customer)
                .FirstOrDefault(p => p.Id == itemid);
            List<VerifyCoefficient> verifyCoefficients = Context.VerifyCoefficients.ToList<VerifyCoefficient>();

            return _calItemJX(verifyCoefficients, Item, Context);

        }


        private void _calItemJX(PerformanceItem Item, spDbContext spDb)
        {
            List<VerifyCoefficient> verifyCoefficients = spDb.VerifyCoefficients.ToList<VerifyCoefficient>();

            var jxList = _calItemJX(verifyCoefficients, Item, spDb);

            var oldJxList = spDb.StaffStatistics.Where(p => p.ItemId == Item.Id);
            spDb.StaffStatistics.RemoveRange(oldJxList);
            spDb.SaveChanges();
            foreach (var jx in jxList)
            {
                if (jx.totalBasePoint == 0)
                {
                    continue;
                }

                jx.ItemId = Item.Id;
                spDb.StaffStatistics.Add(jx);

                spDb.SaveChanges();
            }
        }

        /// <summary>
        /// 序号	情况		                          处理逻辑
        ///  1	    处理人试用期算绩效 一人处理           正常算绩效给处理人
        ///  2	    处理人试用期不算绩效 一人处理         算给核稿人，如果没有核稿人，这条数据忽略
        ///  3	    处理人试用期算绩效 与人合写           合写人算绩效、与合写人一人一半
        ///  4	    处理人试用期不算绩效 与人合写         合写人算绩效、全部绩效算给合写人
        ///  5	    处理人试用期不算绩效 与人合写         合写人不算算绩效、这条数据忽略
        ///  6	    处理人试用期算绩效 与人合写           合写人不算绩效、试用期人员拿一半
        ///  7	    处理人不算绩效 一人处理               这条数据忽略
        ///  8	    处理人不算绩效 与人合写               合写人算绩效、合写人拿一半
        ///  9	    处理人不算绩效 与人合写               合写人不算绩效、这条数据忽略
        /// </summary>
        /// <param name="verifyCoefficients"></param>
        /// <param name="item"></param>
        /// <param name="spDb"></param>
        /// <returns></returns>
        private List<StaffStatistics> _calItemJX(List<VerifyCoefficient> verifyCoefficients, PerformanceItem item, spDbContext spDb)
        {
            
            System.Collections.Hashtable doPersonsBL = new System.Collections.Hashtable();

            
            List<StaffStatistics> itemStatistics = new List<StaffStatistics>();

            if (item.ReviewerId  != null && item.Reviewer == null)
            {
                item.Reviewer  = spDb.Staffs.Include(s => s.StaffGrade).FirstOrDefault(p => p.Id == item.ReviewerId);
                //spDb.Entry(item.Reviewer).Reference(b => b.StaffGrade).Load();
            }

            List<ItemStaff> temIStaffs = new List<ItemStaff>();

            #region 将试用期且不算绩效的人员从处理人清单中剔除
            int syqUsers = 0;
            foreach(var itemStaff in item.ItemStaffs)
            {
                if (itemStaff.DoPerson == null)
                {
                    itemStaff.DoPerson = spDb.Staffs.Include(s => s.StaffGrade).FirstOrDefault(p => p.Id == itemStaff.DoPersonId);
                }

                if(itemStaff.DoPerson.Status == "试用期" && !itemStaff.DoPerson.IsCalPerformsnce )
                {
                    syqUsers += 1;
                }
                else
                {
                    temIStaffs.Add(itemStaff);
                }
            }
            #endregion

            #region 处理人全部是试用期人员，则将核稿人当成处理人（处理人为试用期人）
            if (syqUsers == item.ItemStaffs.Count)
            {
                if(item.Reviewer != null)
                {
                    temIStaffs.Add(new ItemStaff() { DoPersonId = item.ReviewerId.Value, ItemId = item.Id });
                }
                
            }
            #endregion

            bool isPJFP = true;
            double total = temIStaffs.Count();

            

            if (total >1 && temIStaffs.Where<ItemStaff>(p => p.PerformancePoint != null || p.PerformancePoint == 0).Count() > 0)
            {
                total = temIStaffs.Select(i => i.PerformancePoint.Value).Sum();
                isPJFP = false;
            }

            foreach (ItemStaff itemStaff in temIStaffs)
            {
                if(itemStaff.DoPerson == null)
                {
                    itemStaff.DoPerson = spDb.Staffs.Include(s=>s.StaffGrade).FirstOrDefault(p=>p.Id==itemStaff.DoPersonId);
                }

                if (itemStaff.DoPerson.IsCalPerformsnce)
                {
                    #region 计算各处理人的绩效点数
                    double handlerBasePoint;
                    if (item.Type != "专案")
                    {
                        if (isPJFP)
                        {
                            handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                        }
                        else
                        {
                            handlerBasePoint = (double)((decimal)item.BasePoint.Value * (decimal)itemStaff.PerformancePoint.Value / (decimal)total);
                        }
                    }
                    else
                    {
                        if (itemStaff.PerformancePoint == null)
                        {
                            if (isPJFP)
                            {
                                handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                            }
                            else
                            {
                                handlerBasePoint = (double)((decimal)item.BasePoint.Value * (decimal)itemStaff.PerformancePoint.Value / (decimal)total);
                            }
                        }
                        else
                        {
                            if (item.ItemStaffs.Count == 1 && itemStaff.PerformancePoint == 0)
                            {
                                handlerBasePoint = item.BasePoint.Value;
                            }
                            else
                            {
                                handlerBasePoint = itemStaff.PerformancePoint.Value;
                            }

                        }
                    }

                    string handlerJxType = $"{item.Type}处理";
                    var temStatic = itemStatistics.Where<StaffStatistics>(s => s.StaffId == itemStaff.DoPersonId && s.jxType == handlerJxType && s.CalMonth.Id == item.CalMonthId).FirstOrDefault();
                    if (temStatic != null)
                    {
                        temStatic.totalBasePoint += handlerBasePoint;

                        if (item.Type == "OA" || item.Type == "新申请")
                        {
                            temStatic.totalActuallyPoint += (double)((decimal)handlerBasePoint * (decimal)itemStaff.DoPerson.StaffGrade.Coefficient);
                        }
                        else
                        {
                            temStatic.totalActuallyPoint += handlerBasePoint;
                        }
                    }
                    else
                    {
                        if (itemStaff.DoPerson.StaffGradeId != null && itemStaff.DoPerson.IsCalPerformsnce)
                        {
                            if (item.Type == "OA" || item.Type == "新申请")
                            {
                                if (itemStaff.DoPerson.StaffGrade == null)
                                {
                                    itemStaff.DoPerson.StaffGrade = spDb.StaffGrades.FirstOrDefault(s => s.Id == itemStaff.DoPerson.StaffGradeId);
                                }

                                temStatic = new StaffStatistics()
                                {
                                    //CalMonth = calMonth,
                                    CalMonthId = item.CalMonthId,
                                    StaffId = itemStaff.DoPersonId,
                                    totalActuallyPoint = (double)((decimal)handlerBasePoint * (decimal)itemStaff.DoPerson.StaffGrade.Coefficient),
                                    totalBasePoint = handlerBasePoint,
                                    jxType = handlerJxType
                                };

                                itemStatistics.Add(temStatic);
                                
                            }
                            else
                            {
                                temStatic = new StaffStatistics()
                                {
                                    //CalMonth = calMonth,
                                    CalMonthId = item.CalMonthId,
                                    StaffId = itemStaff.DoPersonId,
                                    totalBasePoint = handlerBasePoint,
                                    totalActuallyPoint = handlerBasePoint,
                                    jxType = handlerJxType
                                };

                                itemStatistics.Add(temStatic);
                            }
                        }
                    }
                    #endregion

                    #region 计算审核人绩效点数，核稿人绩效点数按照核稿人与个处理人的核稿系数计算后加总，没有找到核稿系数（比如同级别），核稿系数为0
                    if (item.ReviewerId != null && item.Type != "专案" &&
                        temIStaffs.FirstOrDefault(s => s.DoPersonId == item.ReviewerId) == null)
                    {
                        if(item.Reviewer == null)
                        {
                            item.Reviewer = spDb.Staffs.FirstOrDefault(s=>s.Id == item.ReviewerId);
                        }

                        if (item.Reviewer.IsCalPerformsnce)
                        {
                            #region 取审核人等级审核等级系数
                            VerifyCoefficient vcoefficient
                                = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                    v.CheckerId == item.Reviewer.StaffGradeId
                                    && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                            .FirstOrDefault<VerifyCoefficient>();
                            #endregion

                            if (vcoefficient != null)
                            {
                                double reviewerBasePoint = (double)((decimal)handlerBasePoint * (decimal)vcoefficient.Coefficient);


                                string temJxType = $"{item.Type}审核";

                                var temReviewerStatic = itemStatistics.Where<StaffStatistics>(s => s.StaffId == item.ReviewerId && s.jxType == temJxType && s.CalMonthId == item.CalMonthId).FirstOrDefault();
                                if (temReviewerStatic != null)
                                {
                                    temReviewerStatic.totalBasePoint += reviewerBasePoint;
                                    temReviewerStatic.totalActuallyPoint += reviewerBasePoint;
                                }
                                else
                                {
                                    if (itemStaff.DoPerson.Status != "试用期" && item.Reviewer.IsCalPerformsnce)  //判断是否在职
                                    {
                                        temReviewerStatic = new StaffStatistics()
                                        {

                                            CalMonthId = item.CalMonthId,
                                            StaffId = item.ReviewerId.Value,
                                            totalBasePoint = reviewerBasePoint,
                                            totalActuallyPoint = reviewerBasePoint,
                                            jxType = temJxType
                                        };

                                        itemStatistics.Add(temReviewerStatic);
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                }
            }

            return itemStatistics;
        }

        private void _CalJXPoint(CalMonth calMonth,spDbContext spDb)
        {
            NanduStatics gspjXS = DegreeOfDifficulty(calMonth,spDb);
            calMonth.NanduXS = gspjXS.NanduXS;
            calMonth.S = (double)gspjXS.S;
            calMonth.A = (double)gspjXS.A;
            calMonth.B = (double)gspjXS.B;
            calMonth.C = (double)gspjXS.C;
            calMonth.D = (double)gspjXS.D;

            var retList = spDb.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id).ToList();

            #region 新申请处理+专案处理 奖励点数
            double jlPoint = 0;
            if (calMonth.S.HasValue)
            {
                jlPoint = jlPoint + (calMonth.S.Value * 1.5);
            }

            if (calMonth.A.HasValue)
            {
                jlPoint = jlPoint + (calMonth.A.Value * 0.5);
            }
            #endregion

            #region 难度系数
            IDictionary<int, NanduStatics> staffXiShu = new Dictionary<int, NanduStatics>();
            IDictionary<int, NanduStatics> ReivewerXiShu = new Dictionary<int, NanduStatics>();
            foreach (StaffStatistics ss in retList)
            {

                if (ss.jxType == "新申请处理" || ss.jxType == "专案处理")
                {
                    #region 新申请处理+专案处理
                    if (!staffXiShu.ContainsKey(ss.StaffId))
                    {
                        NanduStatics nandu = PersonNanduStatics(calMonth, ss.StaffId,spDb,false,true);
                        staffXiShu.Add(ss.StaffId, nandu);
                    }

                    ss.FinianlPoint = ss.totalActuallyPoint * staffXiShu[ss.StaffId].NanduXS / gspjXS.NanduXS;
                    ss.NanduXS = staffXiShu[ss.StaffId].NanduXS;
                    ss.S = (double)staffXiShu[ss.StaffId].S;
                    ss.A = (double)staffXiShu[ss.StaffId].A;
                    ss.B = (double)staffXiShu[ss.StaffId].B;
                    ss.C = (double)staffXiShu[ss.StaffId].C;
                    ss.D = (double)staffXiShu[ss.StaffId].D;
                    #endregion
                }
                else
                {
                    if (ss.jxType == "新申请审核")
                    {
                        if (!ReivewerXiShu.ContainsKey(ss.StaffId))
                        {
                            NanduStatics nandu = PersonNanduStatics(calMonth, ss.StaffId,spDb, true);
                            ReivewerXiShu.Add(ss.StaffId, nandu);
                        }

                        ss.FinianlPoint = ss.totalBasePoint * ReivewerXiShu[ss.StaffId].NanduXS / gspjXS.NanduXS;
                        ss.NanduXS = ReivewerXiShu[ss.StaffId].NanduXS;
                        ss.S = (double)ReivewerXiShu[ss.StaffId].S;
                        ss.A = (double)ReivewerXiShu[ss.StaffId].A;
                        ss.B = (double)ReivewerXiShu[ss.StaffId].B;
                        ss.C = (double)ReivewerXiShu[ss.StaffId].C;
                        ss.D = (double)ReivewerXiShu[ss.StaffId].D;
                        spDb.SaveChanges();
                    }
                    else
                    {
                        ss.FinianlPoint = ss.totalActuallyPoint;
                        spDb.SaveChanges();
                    }

                }
            }

            var xsqList = retList.Where(ss => ss.jxType == "新申请处理" || ss.jxType == "专案处理").ToList();
            var totalFPBLPoint = xsqList.Sum(p=>p.FinianlPoint); //新申请处理+专案 分配比率总点数
            var totalActionPoint = xsqList.Sum(p=>p.totalActuallyPoint); //新申请处理+专案 原始总点数

            foreach (StaffStatistics ss in xsqList)
            {
                
                ss.FinianlPoint = ss.FinianlPoint / totalFPBLPoint * (totalActionPoint + jlPoint);
                spDb.SaveChanges();
                
            }
            #endregion
        }
        public void CalJXPoint(int year, int month)
        {
            CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();

            if (calMonth != null)
            {
                using (var t = Context.Database.BeginTransaction())
                {
                    try
                    {
                        var result = Context.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id);
                        Context.StaffStatistics.RemoveRange(result);

                        var itemList = Context.PerformanceItems
                            .Include(s => s.ItemStaffs)
                            .Include(p=>p.Customer)
                            .Include(p=>p.Reviewer)
                            .Where(s => s.CalMonthId == calMonth.Id).ToList();

                        var rules = Context.BasePointRules.ToList();

                        foreach (var item in itemList)
                        {
                            Utility.Utility.CalBasePoint(item, rules);
                            Context.SaveChanges();

                            if (item.BasePoint > 0)
                            {
                                _calItemJX(item,Context);
                            }
                            else
                            {
                                var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                                Context.StaffStatistics.RemoveRange(oldJxList);
                            }

                        }

                        _CalJXPoint(calMonth, Context);
                        t.Commit();
                    }
                    catch (Exception ex)
                    {
                        t.Rollback();
                    }
                }
            }
        }

        public ProcessTask FinishedCalMonth(int year,int month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == year && c.Month == month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}线下绩效核算数据.xlsx";
                var attachfileSavePath = utility.ConfigHelper.GetSectionValue("AttachFileSavePath");
                var filePath = Path.Combine(attachfileSavePath, filename);

                var fileTask = new FileProcessTask()
                {
                    Id = Guid.NewGuid().ToString(),
                    FileName = filename,
                    FilePath = filePath,
                    Processed = 0
                };

                fileTaskService.Add(fileTask);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(_FinishedCalMonth));
                t.Start(result);

                return fileTask;
            }

            return null;
        }
        /// <summary>
        /// 新申请/专案： 
        ///     调整点数 = 分配比例点数/分配比例点数之和*总点数
        ///     等级调整点数+项目组点数之和 =  基础点数 * 代理人等级系数 + 项目组案件调整点数
        ///     分配比例点数 = 个人平均难度系数 / 总平均难度系数 *（等级调整点数+项目组点数之和）
        ///     总点数=等级调整点数+项目组点数之和+奖励点数
        ///     奖励点数= S案数量*1.5 + A案数量 * 0.5
        ///     难度系数=(S案数量*2.5 + A案数量* 1.5 + B案数量*1.0 + C案数量*0.7+ D案数量*0.4) / 总案件数量
        ///     总案件数量= S案数量 + A案数量 + B案数量 + C案数量+ D案数量
        /// OA ：  
        ///     调整点数=  基础点数 * 等级系数
        /// 其他：  
        ///     直接加和基础点数 不需要乘以系数
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        private void _FinishedCalMonth(object proecssTask)
        {
            ExportDataResult result = (ExportDataResult)proecssTask;
            CalMonth calMonth = result.calMonth;
            
            if (calMonth != null || calMonth.Status !=4)
            {
                spDbContext spDb = new spDbContext();
                calMonth = spDb.CalMonths.FirstOrDefault(c=>c.Id == calMonth.Id);
                using (var t = spDb.Database.BeginTransaction())
                {
                    try
                    {
                        _RefreshBasePoint(calMonth, spDb);
                        //_CalJXPoint(calMonth, Context);

                        calMonth.Status = 4;
                        spDb.SaveChanges();
                        
                        //等级核算案件统计
                         _StatisticsLevelCount(calMonth.Year, calMonth.Month, spDb);

                        #region 将待审核的申诉全部修改为已审核
                        var aList = spDb.AppealRecords.Where(a => a.State == 0).ToList();
                        aList.ForEach(a =>a.State = 1);
                        spDb.SaveChanges();
                        #endregion

                        t.Commit();
                        result.fileTask.Finished = true;

                    }
                    catch (Exception ex)
                    {
                        t.Rollback();
                        result.fileTask.ErrorMessage = ex.Message;
                        result.fileTask.Finished = true;
                    }
                }
            }
            else
            {
                result.fileTask.ErrorMessage = "指定月份没有数据或者已归档!";
                result.fileTask.Finished = true;
            }

        }

        /// <summary>
        /// 计算指定用户，指定年月的绩效统计信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public List<StaffStatistics> CalMyStatistics(int year,int month, int? userid=null)
        {
            //object retList;
            //string strKey = $"CalMyStatistics:{year}-{month}-{userid}";
            
            //if(!MyMemoryCache.TryGetValue(strKey,out retList))
            //{
                CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();

                if (calMonth == null)
                {
                    return null;
                }
                else
                {
                    return _CalMyStatistics(calMonth, userid);

                    #region old code
                    //if (calMonth.Status == 4)
                    //{
                    //    //已归档，归档数据库中直接取出记录
                    //    if (userid == null)
                    //    {
                    //        retList = Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id).ToList<StaffStatistics>();
                    //    }
                    //    else
                    //    {
                    //        retList = Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id && s.StaffId == userid).ToList<StaffStatistics>();
                    //    }
                    //}
                    //else
                    //{
                    //    try
                    //    {
                    //        retList = _CalMyStatistics(calMonth, userid);
                    //    }
                    //    catch(Exception ex)
                    //    {
                    //        StreamWriter sw = System.IO.File.AppendText("c:\\temp\\log.txt");
                    //        sw.WriteLine($"{ex.Message}");
                    //        sw.Flush();
                    //        sw.Close();
                    //        sw.Dispose();
                    //    }
                    //}

                    //// Set cache options.
                    //var cacheEntryOptions = new MemoryCacheEntryOptions()
                    //    // Keep in cache for this time, reset time if accessed.
                    //    .SetSlidingExpiration(TimeSpan.FromHours(1));

                    //foreach(var temObj in (List<StaffStatistics>)retList)
                    //{
                    //    temObj.CalMonth.PerformanceItems = null;
                    //}

                    //// Save data in cache.
                    //MyMemoryCache.SetValue(strKey, retList);



                    //return (List<StaffStatistics>)retList;
                    #endregion
                }
            //}
            //else
            //{
            //    var temList = (List<StaffStatistics>)retList;
            //    foreach (var temObj in temList)
            //    {
            //        temObj.CalMonth.PerformanceItems = null;
            //    }

            //    return temList;
            //}
           
        }
        
        private string GetExpress(IList<FieldCondition> conditions)
        {
            string str = "";

            foreach(var c in conditions)
            {
                if (string.IsNullOrEmpty(str))
                {
                    str = c.ToExpressString("s");
                }
                else
                {
                    if(c.LogicOperate == LogicEnum.And)
                    {
                        str = $"({str}) && {c.ToExpressString("s")}";
                    }
                    else
                    {
                        str = $"({str}) || {c.ToExpressString("s")}";
                    }
                }
            }

            return str;
        }

        [HttpGet,HttpPost]
        public FileProcessTask ExportData(QueryFilter queryFilter)
        {
            var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-绩效数据下载.xlsx";
            var attachfileSavePath = utility.ConfigHelper.GetSectionValue("AttachFileSavePath");
            var filePath = Path.Combine(attachfileSavePath, filename);

            var fileTask = new FileProcessTask()
            {
                Id = Guid.NewGuid().ToString(),
                FileName = filename,
                FilePath = filePath,
                Processed = 0
            };

            fileTaskService.Add(fileTask);
            ThreadObject threadObject = new ThreadObject()
            {
                queryFilter = queryFilter,
                fileTask = fileTask
            };

            System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(ExportDataThread));
            t.Start(threadObject);

            return fileTask;
        }

        internal class ThreadObject
        {
            public QueryFilter queryFilter { get; set; }
            public FileProcessTask fileTask { get; set; }
        }
        
        private void ExportDataThread(object  tObj)
        {
            QueryFilter queryFilter = ((ThreadObject)tObj).queryFilter;
            FileProcessTask fileTask = ((ThreadObject)tObj).fileTask;

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

            var retList = response
                .Include(p=>p.Customer)
                .Include(p=>p.ItemStaffs).ThenInclude(p=>p.DoPerson).ThenInclude(p=>p.StaffGrade)
                .Include(p=>p.Reviewer).ThenInclude(p=>p.StaffGrade)
                .Include(p=>p.PreOastaff)
                .Include(p=>p.CalMonth)
                .ToList<PerformanceItem>();

            

            DataTable dt = new DataTable();
            #region 添加栏位
            dt.Columns.Add("我方文号",typeof(string));
            dt.Columns.Add("申请类型", typeof(string));
            dt.Columns.Add("业务类型", typeof(string));
            dt.Columns.Add("备注（填表注意事项）", typeof(string));
            dt.Columns.Add("处理事项", typeof(string));
            dt.Columns.Add("案件阶段", typeof(string));
            dt.Columns.Add("案件系数", typeof(string));
            dt.Columns.Add("处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理人", typeof(string));
            dt.Columns.Add("处理人等级", typeof(string));
            dt.Columns.Add("基本点数", typeof(string));
            dt.Columns.Add("核稿系数", typeof(string));
            dt.Columns.Add("核稿绩效", typeof(string));
            dt.Columns.Add("处理人", typeof(string));
            dt.Columns.Add("核稿人", typeof(string));
            dt.Columns.Add("客户名称", typeof(string));
            dt.Columns.Add("申请人", typeof(string));
            dt.Columns.Add("处理事项完成日", typeof(string));
            dt.Columns.Add("定稿日", typeof(string));
            dt.Columns.Add("返稿日", typeof(string));
            dt.Columns.Add("案件类型", typeof(string));
            dt.Columns.Add("案件状态", typeof(string));
            dt.Columns.Add("处理事项备注", typeof(string));
            dt.Columns.Add("处理状态", typeof(string));
            dt.Columns.Add("案件名称", typeof(string));
            dt.Columns.Add("委案日期", typeof(string));
            dt.Columns.Add("客户期限", typeof(string));
            dt.Columns.Add("内部期限", typeof(string));
            dt.Columns.Add("初稿日", typeof(string));
            dt.Columns.Add("备注（发文严重超期是否属客观原因，若为否，请填写原因）", typeof(string));
            dt.Columns.Add("备注", typeof(string));
            #endregion

            List<VerifyCoefficient> verifyCoefficients = new spDbContext().VerifyCoefficients.ToList();
            fileTask.Size = retList.Count;

            foreach (var item in retList)
            {
                fileTask.Processed += 1;

                if (item.CaseNo.StartsWith("J"))
                {
                    continue;
                }

                try
                {
                    var row = dt.NewRow();
                    row["我方文号"] = item.CaseNo;
                    row["申请类型"] = item.ApplicationType;
                    row["业务类型"] = item.BusinessType;
                    row["备注（填表注意事项）"] = item.AgentFeedbackMemo;
                    row["处理事项"] = item.DoItem;
                    row["案件阶段"] = item.CaseStage;
                    row["案件系数"] = item.CaseCoefficient;
                    row["处理事项系数"] = item.DoItemCoefficient;
                    row["前一次OA处理事项系数"] = "";

                    if (item.PreOastaffId.HasValue)
                    {
                        row["前一次OA处理人"] = item.PreOastaff?.Name;
                    }

                    string strISLevels = "";
                    string strISNames = "";

                    foreach (var istaff in item.ItemStaffs)
                    {
                        strISLevels = string.IsNullOrEmpty(strISLevels)?istaff.DoPerson.StaffGrade?.Grade : $"{strISLevels},{istaff.DoPerson.StaffGrade?.Grade}";
                        strISNames = string.IsNullOrEmpty(strISNames)?istaff.DoPerson.Name : $"{strISNames},{istaff.DoPerson.Name}";
                    }

                    row["处理人等级"] = strISLevels;
                    row["基本点数"] = item.BasePoint;
                    row["处理人"] = strISNames;
                    row["核稿人"] = item.Reviewer?.Name;

                    if (item.ReviewerId != null && item.BasePoint.HasValue)
                    {
                        
                        System.Diagnostics.Debug.WriteLine($"{item.ReviewerId}");
                        var jxList = new spDbContext().StaffStatistics.Where<StaffStatistics>(s => s.ItemId == item.Id).ToList();//_calItemJX( verifyCoefficients, item, new spDbContext());

                        row["核稿系数"] = "";
                        var temJx = jxList.FirstOrDefault<StaffStatistics>(s => s.jxType.Contains("审核") && s.StaffId == item.ReviewerId);
                        if (temJx != null)
                        {
                            row["核稿绩效"] = temJx.totalBasePoint;
                        }
                    }

                    row["客户名称"] = item.Customer?.Name;
                    row["申请人"] = item.ApplicationName;
                    row["处理事项完成日"] = item.FinishedDate?.ToString("yyyy-MM-dd");
                    row["定稿日"] = item.FinalizationDate?.ToString("yyyy-MM-dd");
                    row["返稿日"] = item.ReturnDate?.ToString("yyyy-MM-dd");
                    row["案件类型"] = item.CaseType;
                    row["案件状态"] = item.CaseState;
                    row["处理事项备注"] = item.DoItemState;
                    row["处理状态"] = item.DoItemState;
                    row["案件名称"] = item.CaseName;
                    row["委案日期"] = item.EntrustingDate?.ToString("yyyy-MM-dd");
                    row["客户期限"] = item.CustomerLimitDate?.ToString("yyyy-MM-dd");
                    row["内部期限"] = item.InternalDate?.ToString("yyyy-MM-dd"); ;
                    row["初稿日"] = item.FirstDraftDate?.ToString("yyyy-MM-dd");
                    row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] = item.OverDueMemo;
                    row["备注"] = item.DoItemMemo;

                    dt.Rows.Add(row);
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
           
            utility.NPOIExcel.DataTableToExcel(dt,fileTask.FilePath);


            fileTask.Finished = true;
        }

        [HttpPost]
        public ListApiResponse<PerformanceItem> QueryFilter(QueryFilter queryFilter)
        {

            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

            int totals = response.ToList<PerformanceItem>().Count;

            if (totals > 0 && totals < (queryFilter.PageIndex - 1) * queryFilter.PageSize)
            {
                response = response
                    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                    .Include(pi => pi.Reviewer)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.CalMonth)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts);
                    //.Pager<PerformanceItem>(1, queryFilter.PageSize, out totals);
            }
            else
            {
                response = response
                    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                    .Include(pi => pi.Reviewer)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.CalMonth)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts);
                    //.Pager<PerformanceItem>(queryFilter.PageIndex, queryFilter.PageSize, out totals);
            }
            ret.TotalCount = totals;



            var retList = response.ToList<PerformanceItem>().Skip((queryFilter.PageIndex-1) *queryFilter.PageSize).Take(queryFilter.PageSize);


            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {

                foreach (ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.DoPerson.Customers = null;
                    itemStaff.Item = null;
                }

                if (item.Reviewer != null)
                {
                    item.Reviewer.ReviewerItems = null;
                    item.Reviewer.Customers = null;
                    item.Reviewer.ItemStaffs = null;
                }

                if (item.WorkflowUser != null)
                {
                    item.WorkflowUser.ReviewerItems = null;
                    item.WorkflowUser.Customers = null;
                    item.WorkflowUser.ItemStaffs = null;
                }

                if (item.Customer != null)
                {
                    item.Customer.PerformanceItems = null;
                    item.Customer.ResponseMan = null;
                }

                if (item.CalMonth != null)
                {
                    item.CalMonth.PerformanceItems = null;
                }
            }
            #endregion

            ret.Results = retList.ToList();

            //string str= System.Text.Json.JsonSerializer.Serialize(ret);

            return ret;
        }

        private IQueryable<PerformanceItem> NewMethod(QueryFilter queryFilter)
        {
            string strExpress = "";

            string strCalMonth = "";
            if (queryFilter.CalMonthId.HasValue)
            {
                strCalMonth = $"s.CalMonthId == {queryFilter.CalMonthId}";
            }
            else
            {
                if(queryFilter.jxType == jxType.finished)
                {
                    strCalMonth = $"s.CalMonth.Status == 4";
                }
                else
                {
                    strCalMonth = $"s.CalMonth.Status != 4";
                }

                
                //strCalMonth = $"s.CalMonth.Status == {Convert.ToInt32(queryFilter.jxType)}";
            }

            if (!string.IsNullOrEmpty(strExpress))
            {
                strExpress = $"{strExpress} && {strCalMonth}";
            }
            else
            {
                strExpress = strCalMonth;
            }

            if (queryFilter.ConditionTree != null)
            {
                string strTem = GetExpress(queryFilter.ConditionTree);

                if (!string.IsNullOrEmpty(strTem))
                {
                    strExpress = $"{strExpress} && ({strTem})";
                }
            }

            var interpreter = new Interpreter();
            Expression<Func<PerformanceItem, bool>> dynamicWhere = interpreter.ParseAsExpression<Func<PerformanceItem, bool>>(strExpress, "s");

            IQueryable<PerformanceItem> response;
            if (queryFilter.userId > 0)
            {
                if (queryFilter.DoingOrReview == 0)
                {
                    response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere).Where(s => (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id == queryFilter.userId).Count() > 0));
                }
                else
                {
                    response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere).Where(s => s.ReviewerId == queryFilter.userId);
                }
            }
            else
            {
                response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere);

            }

            return response;
        }

        public ApiSaveResponse AddProjectContents(ProjectContents projectContents)
        {
            ApiSaveResponse retResponse = new ApiSaveResponse();
            retResponse.Success = true;

            if (projectContents != null && projectContents.ProjectWorkContents != null && projectContents.ProjectWorkContents.Count > 0)
            {
                using (var t = Context.Database.BeginTransaction())
                {
                    try
                    {
                        CalMonth calMonth = Context.CalMonths.FirstOrDefault<CalMonth>(c => c.Status == 0);

                        if (calMonth == null)
                        {
                            retResponse.Success = false;
                            retResponse.ErrorMessage = "不存在正在处理的绩效月度！";
                            return retResponse;
                        }
                        else
                        {
                            projectContents.ProjectContentRecord.CalMonthId = calMonth.Id;
                            projectContents.ProjectContentRecord.CalMonth = null;

                            var staff = Context.Staffs.FirstOrDefault(s=>s.Name == User.Identity.Name);

                            projectContents.ProjectContentRecord.StaffId = staff.Id;
                            projectContents.ProjectContentRecord.State = 0;
                        }

                        var project = Context.ProjectInfos.FirstOrDefault(p => p.CaseNo == projectContents.ProjectContentRecord.ProjectNo && p.CaseState ==0);

                        if (project != null)
                        {
                            var pRecord = Context.ProjectContentRecords.FirstOrDefault(p=>p.ProjectNo == projectContents.ProjectContentRecord.ProjectNo
                                && p.StaffId == projectContents.ProjectContentRecord.StaffId
                                && p.CalMonthId == projectContents.ProjectContentRecord.CalMonthId);


                            if(pRecord != null)
                            {
                                retResponse.Success = false;
                                retResponse.ErrorMessage = $"您已提交专案【{projectContents.ProjectContentRecord.ProjectNo}】{pRecord.CalMonth.Year}年{pRecord.CalMonth.Month}月的工作内容！";
                                return retResponse;
                            }

                            Context.ProjectContentRecords.Add(projectContents.ProjectContentRecord);

                            foreach (var doItem in projectContents.ProjectWorkContents)
                            {
                                doItem.ContentRecordId = projectContents.ProjectContentRecord.Id;
                                Context.ProjectWorkContents.Add(doItem);
                            }

                            t.Commit();
                        }
                        else
                        {
                            retResponse.Success = false;
                            retResponse.ErrorMessage = "专案不存在或专案已完成！";
                            return retResponse;
                        }
                        
                    }
                    catch (Exception ex)
                    {
                        retResponse.Success = false;
                        retResponse.ErrorMessage = ex.Message;
                        t.Rollback();
                        return retResponse;
                    }
                }
            }

            return retResponse;
        }

        public PerformanceItem GetCaseInfo(string CaseNo)
        {
            var retObj = Context.PerformanceItems.OrderByDescending(p=>p.CalMonthId).FirstOrDefault<PerformanceItem>(p=>p.CaseNo == CaseNo.Trim());

            if(retObj == null)
            {
                retObj = new IPEasyController(Context).GetCaseInfo(CaseNo);
            }

            return retObj;
            
        }

        public PerformanceItem GetItemInfo(string CaseNo, string DoItem)
        {
            var retObj = Context.PerformanceItems.FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim() && p.DoItem == DoItem.Trim());

            if (retObj == null)
            {
                retObj = new IPEasyController(Context).GetItemInfo(CaseNo,DoItem);
            }

            return retObj;
        }

        public PerformanceItem GetItemInfoByCaseStage(string CaseNo, string DoItem,string caseStage,bool UpdateFromIPEasy=true)
        {
            var retObj = Context.PerformanceItems.Include(p=>p.Customer).FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim() 
            && p.DoItem == DoItem.Trim() && p.CaseStage == caseStage);

            if (retObj == null || UpdateFromIPEasy)
            {
                var temObj = new Job.UpdateJXDataFromIPEasyJob().GetItemFromIPEasyDB(
                    new PerformanceItem() {CaseNo =CaseNo,DoItem =DoItem,CaseStage = caseStage },
                    Context 
                    );// IPEasyUtility.GetPerformanceRecord(CaseNo, DoItem, caseStage);

                
                System.Dynamic.ExpandoObject temExpandoObject = (System.Dynamic.ExpandoObject)temObj;

                retObj = new PerformanceItem();
                retObj.CaseNo = temObj.CaseNo.ToString();
                retObj.CaseName = temObj.CaseName.ToString();

                try
                {
                    if (temObj.ApplicationType != null)
                        retObj.ApplicationType = temObj.ApplicationType.ToString();

                    if (temObj.CaseMemo != null)
                        retObj.CaseMemo = temObj.CaseMemo.ToString();

                    if (temObj.BusinessType != null)
                        retObj.BusinessType = temObj.BusinessType.ToString();

                    if (temObj.DoItem != null)
                        retObj.DoItem = temObj.DoItem.ToString();

                    if (temObj.CaseStage != null)
                        retObj.CaseStage = temObj.CaseStage.ToString();

                    if (temObj.CaseType != null)
                        retObj.CaseType = temObj.CaseType.ToString();

                    if (temObj.DoItemState != null)
                        retObj.DoItemState = temObj.DoItemState.ToString();

                    if (temObj.DoItemCoefficient != null)
                        retObj.DoItemCoefficient = temObj.DoItemCoefficient.ToString();

                    if (temObj.CaseCoefficient != null)
                        retObj.CaseCoefficient = temObj.CaseCoefficient.ToString();

                    if (temObj.EntrustingDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.EntrustingDate.ToString()))
                            retObj.EntrustingDate = DateTime.Parse(temObj.EntrustingDate.ToString());
                    }

                    if(temObj.Country != null)
                    {
                        retObj.Country = temObj.Country.ToString();
                    }
                        

                    if (temObj.InternalDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.InternalDate.ToString()))
                            retObj.InternalDate = DateTime.Parse(temObj.InternalDate.ToString());
                    }

                    if (temObj.CustomerLimitDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.CustomerLimitDate.ToString()))
                            retObj.CustomerLimitDate = DateTime.Parse(temObj.CustomerLimitDate.ToString());
                    }


                    if (temObj.ReturnDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.ReturnDate.ToString()))
                            retObj.ReturnDate = DateTime.Parse(temObj.ReturnDate.ToString());
                    }

                    if (temObj.FinalizationDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinalizationDate.ToString()))
                            retObj.FinalizationDate = DateTime.Parse(temObj.FinalizationDate.ToString());
                    }
                    if (temObj.FinishedDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinishedDate.ToString()))
                            retObj.FinishedDate = DateTime.Parse(temObj.FinishedDate.ToString());
                    }
                }
                catch { }

                int temWordCount;

                if (temObj.WordCount != null)
                {
                    if (int.TryParse(temObj.WordCount, out temWordCount))
                    {
                        retObj.WordCount = temWordCount;
                    }
                }

                try
                {
                    if (temObj.CustomerName != null)
                    {
                        string strCustomer = temObj.CustomerName.ToString();
                        retObj.Customer = new Customer() { Name = strCustomer };
                        var temCustomer = Context.Customers.Where(c => c.Name == strCustomer.Trim()).FirstOrDefault();
                        if (temCustomer != null)
                        {
                            retObj.CustomerId = temCustomer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.Reviewer != null)
                    {
                        string strReViewer = temObj.Reviewer.ToString().Replace("-君龙", ""); 
                        var temReviewer = Context.Staffs.Where(s => s.Name == strReViewer.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.ReviewerId = temReviewer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.WorkflowUser != null)
                    {
                        string strWorkflowUser = temObj.WorkflowUser.ToString().Replace("-君龙", "");
                        var temReviewer = Context.Staffs.Where(s => s.Name == strWorkflowUser.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.WorkflowUserId  = temReviewer.Id;
                        }
                    }
                }
                catch { }

                if (temObj.DoPersons != null)
                {
                    string DoPersons = temObj.DoPersons.ToString();

                    string[] Persons = DoPersons.Split(new char[] { ',' });
                    List<ItemStaff> itemStaffs = new List<ItemStaff>();
                    foreach (var doPerson in Persons)
                    {
                        string strName = doPerson.Replace("-君龙","");
                        itemStaffs.Add(new ItemStaff() { DoPerson = new Staff() { Name = strName } });
                    }
                    retObj.ItemStaffs = itemStaffs;
                }
                
            }

            return retObj;
        }

       

        public PerformanceItem GetItemInfoByMonthId(string CaseNo, string DoItem, string caseStage,int calMonthId)
        {
            var retObj = Context.PerformanceItems.Include(p => p.Customer).FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim()
              && p.DoItem == DoItem.Trim()  && p.CalMonthId == calMonthId);

            if(retObj != null)
            {
                if(string.IsNullOrEmpty (retObj.CaseStage) && string.IsNullOrEmpty(caseStage))
                {
                    return retObj;
                }
                else
                {
                    if(retObj.CaseStage == caseStage)
                    {
                        return retObj;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            else
            {
                return null;
            }
        }

        public bool MovePerformance2ProjectInfo()
        {
            var response = Context.PerformanceItems.Include(p=>p.ItemStaffs)
                .Where<PerformanceItem>(p => p.CaseNo.StartsWith("S") && p.CalMonth.Status == 0);

            
            var pList = response.ToList<PerformanceItem>();

            foreach(var p in pList)
            {
                ProjectInfo project = new ProjectInfo();
                project.CaseNo = p.CaseNo;
                project.CaseName = p.CaseName;
                project.CaseState = 0;
                project.CaseType = p.CaseType;
                project.CustomerId = p.CustomerId;
                project.ReviewerId = p.ReviewerId;

                Context.ProjectInfos.Add(project);

                
                foreach(ItemStaff iStaff in p.ItemStaffs)
                {
                    Context.ItemStaffs.Remove(iStaff);
                }

                Context.PerformanceItems.Remove(p);

                Context.SaveChanges();
                
            }

            return true;
        }

        #region 等级考核计算
        /// <summary>
        /// 等级考核案件量统计
        /// </summary>
        /// <param name="calMonthId"></param>
        /// <returns></returns>
        private List<StaffStatisticsforLevel> StatisticsCount(int calMonthId,spDbContext spDb )
        {
            var retlist = new List<StaffStatisticsforLevel>();

            #region 新申请
            var newList = spDb.PerformanceItems.Include(p => p.ItemStaffs).ThenInclude(p=>p.DoPerson)
                .Include(p=>p.CalMonth)
                .Where(p=>p.CalMonthId == calMonthId && p.BasePoint >0
                    && (p.Type =="新申请" || p.Type =="专案" || (p.Type == "其它" && p.WordCount >0 && (p.DoItem =="新申请" || p.DoItem == "翻译")))
                ).ToList();

            foreach(var p in newList)
            {
                #region 严重延期降系数
                string strCaseCeoffcient = p.CaseCoefficient;
                if (string.IsNullOrEmpty(strCaseCeoffcient))
                {
                    strCaseCeoffcient = "B";
                }
                if (p.isDanger() && string.IsNullOrEmpty(p.OverDueMemo))
                {
                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            strCaseCeoffcient = "A";
                            break;
                        case "A":
                            strCaseCeoffcient = "B";
                            break;
                        case "B":
                            strCaseCeoffcient = "C";
                            break;
                        case "C":
                            strCaseCeoffcient = "D";
                            break;
                    }
                }
                #endregion
                var pointList = spDb.StaffStatistics.Include(i=>i.Staff).Where(i=>i.ItemId == p.Id).ToList();

                #region 新申请处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));

                int doPersons = doList.Count();
                if (p.ItemStaffs.Count() > doList.Count())
                {
                    doPersons = p.ItemStaffs.Count;
                    foreach (ItemStaff itemStaff in p.ItemStaffs)
                    {
                        Staff staff = itemStaff.DoPerson;
                        if (staff == null)
                        {
                            staff = spDb.Staffs.FirstOrDefault(s => s.Id == itemStaff.DoPersonId);
                        }

                        

                        if (staff.Status  == "试用期")
                        {
                            doPersons =doPersons- 1; 
                        }
                    }
                }

                if(doPersons >0)
                {
                    //System.Diagnostics.Debug.WriteLine("");                
                

                foreach(var h in doList)
                {
                    var temObj = retlist.FirstOrDefault(s=>s.StaffId == h.StaffId && s.Type == "新申请" && s.isReview == false);
                    if(temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = h.StaffId;
                        
                        temObj.GradeId = h.Staff.StaffGradeId.Value ;

                        if(temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }

                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "新申请";
                        retlist.Add(temObj);
                    }

                    switch (strCaseCeoffcient)
                    {
                        case "S":
                            if(p.Type == "专案")
                            {
                                temObj.S += h.totalBasePoint.Value;
                            }
                            else
                            {
                                temObj.S+=(double)(1.0M/ (decimal)doPersons);
                            }
                            break;
                        case "A":
                            if (p.Type == "专案")
                            {
                                temObj.A += h.totalBasePoint.Value;
                            }
                            else
                            {
                                temObj.A += (double)(1.0M / (decimal)doPersons);
                            }
                            break;
                        case "B":
                            if (p.Type == "专案")
                            {
                                temObj.B += h.totalBasePoint.Value;
                            }
                            else
                            {
                                temObj.B += (double)(1.0M / (decimal)doPersons);
                            }
                            break;
                        case "C":
                            if (p.Type == "专案")
                            {
                                temObj.C += h.totalBasePoint.Value;
                            }
                            else
                            {
                                temObj.C += (double)(1.0M / (decimal)doPersons);
                            }
                            break;
                        case "D":
                            if (p.Type == "专案")
                            {
                                temObj.D += h.totalBasePoint.Value;
                            }
                            else
                            {
                                temObj.D += (double)(1.0M / (decimal)doPersons);
                            }
                            break;
                    }
                    
                }
                }
                #endregion

                #region 新申请审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if(reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "新申请" && s.isReview == true);

                    if(temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;

                        if (temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "新申请";
                        temObj.isReview = true;
                        retlist.Add(temObj);
                    }

                    switch (strCaseCeoffcient)
                    {
                        case "S":
                            temObj.S += 1;
                            break;
                        case "A":
                            temObj.A += 1;
                            break;
                        case "B":
                            temObj.B += 1;
                            break;
                        case "C":
                            temObj.C += 1;
                            break;
                        case "D":
                            temObj.D += 1;
                            break;
                    }
                }
                #endregion
            }
            #endregion

            #region 国内OA
            var cnOA = spDb.PerformanceItems.Include(x=>x.ItemStaffs)
                .Where(x => x.CalMonthId == calMonthId && x.Type == "OA" && x.CaseNo.Contains("CN") && x.BasePoint > 0.3
                ).ToList();

            //因为不是所有的OA案件都是答复审查意见，所以有些应该没有一通或首次转入信息，去掉这个条件：
            //&& (x.CaseStage == "一通" || x.DoItemCoefficient.Contains("首次转入"))

            foreach (var p in cnOA)
            {
                var pointList = spDb.StaffStatistics.Include(i => i.Staff).Where(i=> i.ItemId == p.Id).ToList();

                #region 国内OA处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));
                foreach (var h in doList)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == h.StaffId && s.Type == "国内OA" && s.isReview == false);
                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = h.StaffId;
                        temObj.GradeId = h.Staff.StaffGradeId.Value ;
                        if (temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "国内OA";
                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "A":
                            temObj.A += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "B":
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "C":
                            temObj.C += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "D":
                            temObj.D += (double)(1.0M / (decimal)doList.Count());
                            break;
                        default:
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                    }

                }
                #endregion

                #region 国内OA审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if (reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "国内OA" && s.isReview == true);

                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;
                        if (temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "国内OA";
                        temObj.isReview = true;

                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += 1;
                            break;
                        case "A":
                            temObj.A += 1;
                            break;
                        case "B":
                            temObj.B += 1;
                            break;
                        case "C":
                            temObj.C += 1;
                            break;
                        case "D":
                            temObj.D += 1;
                            break;
                    }
                }
                #endregion
            }
            #endregion

            #region 涉外OA
            var gwOA = spDb.PerformanceItems.Include(x => x.ItemStaffs)
                .Where(x => x.CalMonthId == calMonthId && x.Type == "OA" && !x.CaseNo.Contains("CN") && x.BasePoint>0.5
                && (x.DoItemCoefficient.Contains("实质"))).ToList();

            foreach (var p in gwOA)
            {
                var pointList = spDb.StaffStatistics.Include(i => i.Staff).Where(i => i.ItemId == p.Id).ToList();

                #region 涉外OA处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));
                foreach (var h in doList)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == h.StaffId && s.Type == "涉外OA" && s.isReview == false);
                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = h.StaffId;
                        temObj.GradeId = h.Staff.StaffGradeId.Value;
                        if (temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "涉外OA";
                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "A":
                            temObj.A += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "B":
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "C":
                            temObj.C += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "D":
                            temObj.D += (double)(1.0M / (decimal)doList.Count());
                            break;
                        default:
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                    }

                }
                #endregion

                #region 涉外OA审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if (reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "涉外OA" && s.isReview == true);

                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;
                        if (temObj.GradeId == 0)
                        {
                            System.Diagnostics.Debug.WriteLine("");
                        }
                        temObj.CalMonthId = calMonthId;
                        temObj.Type = "涉外OA";
                        temObj.isReview = true;

                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += 1;
                            break;
                        case "A":
                            temObj.A += 1;
                            break;
                        case "B":
                            temObj.B += 1;
                            break;
                        case "C":
                            temObj.C += 1;
                            break;
                        case "D":
                            temObj.D += 1;
                            break;
                        default:
                            temObj.C += 1;
                            break;

                    }
                }
                #endregion
            }
            #endregion

            return retlist;
        }

        /// <summary>
        /// 指定等级人员等级考核案件量统计汇总
        /// </summary>
        /// <param name="levelCaseCounts"></param>
        /// <param name="GradeCode"></param>
        /// <param name="calReviw"></param>
        /// <returns></returns>
        private LevelStatisticsResult CalLevelStatistics(List<StaffStatisticsforLevel> levelCaseCounts,string GradeCode,bool calReviw)
        {
            var retList = new List<LevelStatistics>();
            
            var temList = levelCaseCounts.Where(x=>x.Grade.Grade.StartsWith(GradeCode)).ToList();

            foreach (var s in temList)
            {
                var temObj = retList.FirstOrDefault(i=>i.StaffId == s.StaffId);

                if(temObj == null)
                {
                    temObj = new LevelStatistics();
                    temObj.StaffId = s.StaffId;
                    temObj.StaffName = s.Staff.Name;
                    temObj.Grade = s.Grade.Grade;
                    //var Department = Context.DepartmentPositions.FirstOrDefault(x => x.StaffId == s.StaffId);
                    temObj.Department = Context.DepartmentPositions.Include(x => x.department).FirstOrDefault(x => x.StaffId == s.StaffId)?.department.Name;
                    
                    retList.Add(temObj);
                }

                if (!s.isReview)
                {
                    if(s.Type == "国内OA")
                    {
                        temObj.dS += (double)((decimal)s.S / 2M);
                        temObj.dA += (double)((decimal)s.A / 2M);
                        temObj.dB += (double)((decimal)s.B / 2M);
                        temObj.dC += (double)((decimal)s.C / 2M);
                        temObj.dD += (double)((decimal)s.D / 2M);
                    }
                    else
                    {
                        temObj.dS += s.S;
                        temObj.dA += s.A;
                        temObj.dB += s.B;
                        temObj.dC += s.C;
                        temObj.dD += s.D;
                    }
                }
                else
                {
                    if (calReviw)
                    {
                        if (s.Type == "国内OA")
                        {
                            temObj.vS += (double)((decimal)s.S / 2M);
                            temObj.vA += (double)((decimal)s.A / 2M);
                            temObj.vB += (double)((decimal)s.B / 2M);
                            temObj.vC += (double)((decimal)s.C / 2M);
                            temObj.vD += (double)((decimal)s.D / 2M);
                        }
                        else
                        {
                            temObj.vS += s.S;
                            temObj.vA += s.A;
                            temObj.vB += s.B;
                            temObj.vC += s.C;
                            temObj.vD += s.D;
                        }
                    }
                }
            }

            return new LevelStatisticsResult() {Grade =GradeCode ,  Statistics = retList};

        }

        /// /// <summary>
        /// 获取季度等级考核结果
        /// </summary>
        /// <param name="year">考核年度</param>
        /// <param name="quarter">考核季度，取值为1、2、3、4</param>
        /// <param name="GradeCode">等级</param>
        /// <returns></returns>
        public LevelStatisticsResult CalAgentLevel(int year,int quarter,string GradeCode)
        {
            LevelStatisticsResult result = new LevelStatisticsResult();
            DateTime tempDate=DateTime.Parse($"{year}-01-01");
            var Months = new List<int>() { 1,2,3};
            switch (quarter)
            {
                case 1:
                    if(year == 2022)
                    {
                        Months = new List<int>() { 2, 3 };
                    }
                    else
                    {
                        Months = new List<int>() { 1, 2, 3 };
                    }
                    tempDate = DateTime.Parse($"{year}-01-01");
                    break;
                case 2:
                    Months = new List<int>() { 4,5,6 };
                    tempDate = DateTime.Parse($"{year}-04-01");
                    break;
                case 3:
                    Months = new List<int>() { 7,8,9};
                    tempDate = DateTime.Parse($"{year}-07-01");
                    break;
                case 4:
                    Months = new List<int>() { 10,11,12 };
                    tempDate = DateTime.Parse($"{year}-10-01");
                    break;
            }

            //var mList = Context.CalMonths.Where(c=>c.Year == year && Months.Contains(c.Month)).ToList();
            //foreach (var m in mList)
            //{
            //    StatisticsLevelCount(m.Id);
            //}
            var resp = Context.StaffStatisticsforLevels
                .Include(p => p.Staff)
                .Include(p => p.Grade)
                .Where(p => Months.Contains(p.CalMonth.Month) && p.CalMonth.Year == year && p.Staff.RegularDate.Value < tempDate && p.Staff.IsGradeAssess);
            var temtList = resp.ToList();

            bool isReview = (GradeCode == "A");
            result = CalLevelStatistics(temtList, GradeCode, isReview);

            if (!isReview)
            {
                string UpgradCode = "A";
                switch (GradeCode)
                {
                    case "B":
                        UpgradCode = "A";
                        break;
                    case "C":
                        UpgradCode = "B";
                        break;
                    case "D":
                        UpgradCode = "C";
                        break;
                }

                var temResult = CalLevelStatistics(temtList, UpgradCode, isReview);
                result.UpgradeBase = temResult.doAverage();
            }

            if (result.Statistics.Count > 0)
            {
                var temAverage = result.pAverage(result.Statistics[0].StaffId);
            }
            return result;
        }

        /// <summary>
        /// 重新统计指定月份的人员案件数量
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        public bool StatisticsLevelCount(int year,int month)
        {
            try
            {
                _StatisticsLevelCount(year,month,Context);

                return true;
            }
            catch { return false; }
        }

        private void _StatisticsLevelCount(int year, int month,spDbContext spDb)
        {
            var temObj = spDb.CalMonths.FirstOrDefault(l => l.Year == year && l.Month == month);

            if (temObj != null)
            {
                var temList = spDb.StaffStatisticsforLevels.Where(l => l.CalMonthId == temObj.Id);
                spDb.StaffStatisticsforLevels.RemoveRange(temList);

                var NewList = StatisticsCount(temObj.Id, spDb);

                spDb.StaffStatisticsforLevels.AddRange(NewList);
                spDb.SaveChanges();
            }
        }
        #endregion
    }
}